Index: openacs-4/packages/inventory-control/sql/postgresql/inventory-control-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/inventory-control/sql/postgresql/inventory-control-create.sql,v
diff -u -N -r1.2 -r1.3
--- openacs-4/packages/inventory-control/sql/postgresql/inventory-control-create.sql	8 Jul 2006 22:32:38 -0000	1.2
+++ openacs-4/packages/inventory-control/sql/postgresql/inventory-control-create.sql	9 Jul 2006 22:14:24 -0000	1.3
@@ -8,18 +8,8 @@
 
 -- following from SL
 
-CREATE TABLE qci_makemodel (
-  parts_id integer,
-  make text,
-  model text
-);
-
-create index qci_makemodel_parts_id_key on qci_makemodel (parts_id);
-create index qci_makemodel_make_key on qci_makemodel (lower(make));
-create index qci_makemodel_model_key on qci_makemodel (lower(model));
-
-
 CREATE TABLE qci_warehouse (
+-- TODO: associate the warehouse with a contacts record
   id int default nextval('id'),
   description text
 );
@@ -32,71 +22,14 @@
   orderitems_id int,
   qty float4,
   shippingdate date,
+-- following for auditing
   employee_id int
 );
 
 
-CREATE FUNCTION qci_check_inventory() RETURNS OPAQUE AS '
-
-declare
-  itemid int;
-  row_data inventory%rowtype;
-
-begin
-
-  if not old.quotation then
-    for row_data in select * from qci_inventory where trans_id = old.id loop
-      select into itemid id from orderitems where trans_id = old.id and id = row_data.orderitems_id;
-
-      if itemid is null then
-	delete from qci_inventory where trans_id = old.id and orderitems_id = row_data.orderitems_id;
-      end if;
-    end loop;
-  end if;
-return old;
-end;
-' language 'plpgsql';
--- end function
-
-
-
-
-
 --  following from ecommerce package
+-- TODO move products db into CR
 
--- Helper stuff (ben@adida.net)
--- gilbertw - I pulled this from OpenACS 3.2.5
--- there are a few calls to the Oracle least function
-create function qci_least(numeric,numeric)
-returns numeric
-as '
-DECLARE
-        first alias for $1;
-        second alias for $2;
-BEGIN
-        if first < second
-        then return first;
-        else return second;
-        end if;
-END;
-' language 'plpgsql';
-
--- gilbertw
--- timespan_days taken from OpenACS 3.2.5
--- can't cast numeric to varchar/text so I made the input varchar
-create function qci_timespan_days(float) returns interval as '
-DECLARE
-        n_days alias for $1;
-BEGIN
-        return (n_days::text || '' days'')::interval;
-END;
-' language 'plpgsql';
-
-
--- this should be replaced by the object_id sequence
--- grep for it in files...
--- create sequence ec_product_id_sequence start 1;
-
 -- This table contains the products and also the product series.
 -- A product series has the same fields as a product (it actually
 -- *is* a product, since it's for sale, has its own price, etc.).
@@ -145,149 +78,10 @@
 select inline_0 ();
 drop function inline_0 ();
 
--- wtem@olywa.net, 2001-03-24
--- we aren't going to bother to define all the attributes of an ec_product type
--- for now, because we are just using it for site-wide-search anyway
--- we have a corresponding pl/sql package for the ec_product object_type
--- it can be found at ecommerce/sql/ec-product-package-create.sql
--- and is called at the end of this script
-create table qci_ec_products (
-        product_id              integer constraint qci_ec_products_product_id_fk
-				references acs_objects(object_id)
-				on delete cascade
-				constraint qci_ec_products_product_id_pk
-				primary key,
-	-- above changed by wtem@olywa.net, 2001-03-24
-	                        -- integer not null primary key,
-        sku                     varchar(100),
-        product_name            varchar(200),
-        creation_date           timestamptz default current_timestamp not null,
-        one_line_description    varchar(400),
-        detailed_description    varchar(4000),
-        search_keywords         varchar(4000),
-        -- this is the regular price for the product.  If user
-        -- classes are charged a different price, it should be
-        -- specified in qci_ec_product_user_class_prices
-        price                   numeric, 
-        -- for stuff that can't be shipped like services
-        no_shipping_avail_p     boolean default 'f',
-        -- leave this blank if shipping is calculated using
-        -- one of the more complicated methods available
-        shipping                numeric,
-        -- fill this in if shipping is calculated by: above price
-        -- for first item (with this product_id), and the below
-        -- price for additional items (with this product_id)
-        shipping_additional     numeric,
-        -- fill this in if shipping is calculated using weight
-        -- use whatever units you want (lbs/kg), just be consistent
-        -- and make your shipping algorithm take the units into
-        -- account
-        weight                  numeric,
-        -- holds pictures, sample chapters, etc.
-	dirname                 varchar(200),
-        -- whether this item should show up in searches (e.g., if it's
-        -- a volume of a series, you might not want it to)
-        present_p               boolean default 't',
-        -- whether the item should show up at all in the user pages
-        active_p                boolean default 't',
-        -- the date the product becomes available for sale (it can be listed
-        -- before then, it's just not buyable)
-	available_date          timestamptz default current_timestamp not null,
-        announcements           varchar(4000),
-        announcements_expire    timestamptz,
-        -- if there's a web site with more info about the product
-        url                     varchar(300),
-        template_id             integer references ecca_ec_templates,
-        -- o = out of stock, q = ships quickly, m = ships
-        -- moderately quickly, s = ships slowly, i = in stock
-        -- with no message about the speed of the shipment (shipping
-        -- messages are in parameters .ini file)
-        stock_status            char(1) check (stock_status in ('o','q','m','s','i')),
-        -- comma-separated lists of available colors, sizes, and styles for the user
-        -- to choose upon ordering
-        color_list              varchar(4000),
-        size_list               varchar(4000),
-        style_list              varchar(4000),
-        -- email this list on purchase
-        email_on_purchase_list  varchar(4000),
-        -- the user ID and IP address of the creator of the product
-        last_modified           timestamptz not null,
-        last_modifying_user     integer not null references users,
-        modified_ip_address     varchar(20) not null    
-);
 
-create view qci_ec_products_displayable
-as
-select * from qci_ec_products
-where active_p='t';
 
-create view qci_ec_products_searchable
-as
-select * from qci_ec_products
-where active_p='t' and present_p='t';
 
-create table qci_ec_products_audit (
-        product_id              integer,
-        product_name            varchar(200),
-        creation_date           timestamptz,
-        one_line_description    varchar(400),
-        detailed_description    varchar(4000),
-        search_keywords         varchar(4000),
-        price                   numeric,
-        shipping                numeric,
-        shipping_additional     numeric,
-        weight                  numeric,
-        dirname                 varchar(200),
-        present_p               boolean default 't',
-        active_p                boolean default 't',
-        available_date          timestamptz,
-        announcements           varchar(4000),
-        announcements_expire    timestamptz,
-        url                     varchar(300),
-        template_id             integer,
-        stock_status            char(1) check (stock_status in ('o','q','m','s','i')),
-        last_modified           timestamptz,
-        last_modifying_user     integer,
-        modified_ip_address     varchar(20),
-        delete_p                boolean default 'f'
-);
 
-create function qci_ec_products_audit_tr ()
-returns opaque as '
-begin
-        insert into qci_ec_products_audit (
-        product_id, product_name, creation_date,
-        one_line_description, detailed_description,
-        search_keywords, shipping,
-        shipping_additional, weight,
-        dirname, present_p,
-        active_p, available_date,
-        announcements, announcements_expire, 
-        url, template_id,
-        stock_status,
-        last_modified,
-        last_modifying_user, modified_ip_address
-        ) values (
-        old.product_id, old.product_name, old.creation_date,
-        old.one_line_description, old.detailed_description,
-        old.search_keywords, old.shipping,
-        old.shipping_additional, old.weight,
-        old.dirname, old.present_p,
-        old.active_p, old.available_date,
-        old.announcements, old.announcements_expire, 
-        old.url, old.template_id,
-        old.stock_status,
-        old.last_modified,
-        old.last_modifying_user, old.modified_ip_address
-        );
-	return new;
-end;' language 'plpgsql';
-
-create trigger qci_ec_products_audit_tr
-after update or delete on qci_ec_products
-for each row execute procedure qci_ec_products_audit_tr ();
-
-
 -- people who bought product_id also bought products 0 through
 -- 4, where product_0 is the most frequently purchased, 1 is next,
 -- etc.
@@ -370,54 +164,6 @@
 for each row execute procedure qci_ec_sale_prices_audit_tr ();
 
 
-create table qci_ec_product_series_map (
-        -- this is the product_id of a product that happens to be
-        -- a series
-        series_id               integer not null references qci_ec_products,
-        -- this is the product_id of a product that is one of the
-        -- components of the above series
-        component_id            integer not null references qci_ec_products,
-        primary key (series_id, component_id),
-        last_modified           timestamptz not null,
-        last_modifying_user     integer not null references users,
-        modified_ip_address     varchar(20) not null
-);
-
-create index qci_ec_product_series_map_idx2 on qci_ec_product_series_map(component_id);
-
-create table qci_ec_product_series_map_audit (
-        series_id               integer,
-        component_id            integer,
-        last_modified           timestamptz,
-        last_modifying_user     integer,
-        modified_ip_address     varchar(20),
-        delete_p                boolean default 'f'
-);
-
-
-create function qci_ec_product_series_map_audit_tr ()
-returns opaque as '
-begin
-        insert into qci_ec_product_series_map_audit (
-        series_id, component_id,
-        last_modified,
-        last_modifying_user, modified_ip_address
-        ) values (
-        old.series_id, old.component_id,
-        old.last_modified,
-        old.last_modifying_user, old.modified_ip_address      
-        );
-	return new;
-end;' language 'plpgsql';
-
-create trigger qci_ec_product_series_map_audit_tr
-after update or delete on qci_ec_product_series_map
-for each row execute procedure qci_ec_product_series_map_audit_tr ();
-
-
-
-
-
 -- this specifies that product_a links to product_b on the display page for product_a
 create table qci_ec_product_links (
         product_a               integer not null references qci_ec_products,
