Index: openacs-4/packages/inventory-control/sql/postgresql/catalog-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/inventory-control/sql/postgresql/catalog-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/inventory-control/sql/postgresql/catalog-create.sql 3 Jul 2006 19:29:13 -0000 1.1 +++ openacs-4/packages/inventory-control/sql/postgresql/catalog-create.sql 9 Jul 2006 22:14:24 -0000 1.2 @@ -1,86 +1,398 @@ -- catalog-create.sql -- --- @author Dekka Corp. +-- @authors -- @ported from sql-ledger and combined with parts from OpenACS ecommerce package -- @license GNU GENERAL PUBLIC LICENSE, Version 2, June 1991 -- @cvs-id -- -- +-- The ecommerce.ec_products table is meshed into qci_parts here +-- because SL will be doing the most work with it. +-- accounts-receivables qar_ec_products will be a thin version +-- for the shopping basket + +-- TODO convert qci_parts to use CR + CREATE TABLE qci_parts ( - id int DEFAULT nextval ( 'id' ), - partnumber text, - description text, - unit varchar(5), - listprice float, - sellprice float, - lastcost float, - priceupdate date DEFAULT current_date, - weight float4, - onhand float4 DEFAULT 0, - notes text, - makemodel bool DEFAULT 'f', - assembly bool DEFAULT 'f', - alternate bool DEFAULT 'f', - rop float4, - inventory_accno_id int, - income_accno_id int, - expense_accno_id int, - bin text, - obsolete bool DEFAULT 'f', - bom bool DEFAULT 'f', - image text, - drawing text, - microfiche text, - partsgroup_id int, - project_id int, - avgcost float + -- was id int DEFAULT nextval ( 'id' ), now + 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, + + -- was parts.partnumber + sku varchar(100), + + -- was parts.description, + 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), + unit varchar(5), + + -- manufactured list price + listprice numeric, + + -- same as ec_products.price + sellprice numeric, + + lastcost numeric, + + -- based on ec_products.ship_satus + -- the useage is split to qci_parts.order_turnaround and qci_parts.onhand + -- q = ships quickly, m = ships moderately quickly, s = ships slowly + -- o = special, i = inconsistent/unknown/ask + -- messages are changeable + -- ec_products.ship_status was char(1) check (ships in ('o','q','m','s','i')), + order_turnaround char(1) default 'i', + + 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, + onhand numeric DEFAULT 0, + + notes varchar(4000), + + -- changing parts.makemodel to series for consistency with qci_series + series boolean DEFAULT 'f', + assembly boolean DEFAULT 'f', + alternate boolean DEFAULT 'f', + + -- rop is Re-Order Point + rop numeric, + + -- these are used if accounts-ledger is loaded + inventory_accno_id integer, + income_accno_id integer, + expense_accno_id integer, + + bin text, + + obsolete boolean DEFAULT 'f', + + -- apparently bom here is true when item is an assembly + -- TODO determine if assembly can include a service in SL + bom boolean DEFAULT 'f', + + -- these are for external referencing + -- changing from text to varchar(4000) so values can be indexed for search pkg + image varchar(4000), + drawing varchar(4000), + microfiche varchar(4000), + + -- TODO change partsgroup_id to use categories + partsgroup_id integer, + + -- TODO project_id to optionally reference existing project-manager or logger id + project_id integer, + + avgcost numeric, + + -- from ec_products.dirname, 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 + -- ec_products.active_p boolean default 't', + -- see obsolete, with reverse meaning + + -- 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, + + + -- a tcl represented list of lists, indicating parameters for + -- users to choose upon ordering.. generally something that only affects + -- how the item is configured, ie all configurations represent + -- same item, price, specifications + parameters_list varchar(4000), + variations_list varchar(4000), + + -- notify via email this list of emails when item purchased + email_on_purchase_list varchar(4000), + + -- the user ID and IP address of the creator of the product + -- priceupdate date DEFAULT current_date, + -- becomes last_modified + last_modified timestamptz not null, + last_modifying_user integer not null references users, + modified_ip_address varchar(20) not null + ); --- + CREATE TABLE qci_partsgroup ( - id int default nextval('id'), + id integer default nextval('id'), partsgroup text ); create index qci_partsgroup_id_key on qci_partsgroup (id); create unique index qci_partsgroup_key on qci_partsgroup (partsgroup); --- + CREATE TABLE qci_pricegroup ( - id int default nextval('id'), + id integer default nextval('id'), pricegroup text ); --- + CREATE TABLE qci_partscustomer ( - parts_id int, - customer_id int, - pricegroup_id int, - pricebreak float4, - sellprice float, - validfrom date, + parts_id integer, + customer_id integer, + pricegroup_id integer, + pricebreak numeric, + sellprice numeric, + validfrom date,0 validto date, curr char(3) ); --- + CREATE TABLE qci_partstax ( - parts_id int, - chart_id int + parts_id integer + chart_id integer ); --- + create index qci_parts_id_key on qci_parts (id); create index qci_parts_partnumber_key on qci_parts (lower(partnumber)); create index qci_parts_description_key on qci_parts (lower(description)); create index qci_partstax_parts_id_key on qci_partstax (parts_id); --- + create index qci_partsvendor_vendor_id_key on qci_partsvendor (vendor_id); create index qci_partsvendor_parts_id_key on qci_partsvendor (parts_id); --- + create index qci_pricegroup_pricegroup_key on qci_pricegroup (pricegroup); create index qci_pricegroup_id_key on qci_pricegroup (id); +-- following from ecommerce + +-- 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. +-- 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 (); + +-- ec_product_series_map was not fully implemented, so am +-- taking the liberty to change it to fit its name +create table qci_product_series_map ( + -- a series (was product_id) + -- changed to series because an assembly has component parts + -- but a series is a set of similar products + series_id integer not null references qci_series, + + -- this is the product_id of a product that is one of the + -- components of the above series + product_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_product_series_map_idx2 on qci_product_series_map(component_id); + +create table qci_product_series_map_audit ( + series_id integer, + product_id integer, + last_modified timestamptz, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + + +create function qci_product_series_map_audit_tr () +returns opaque as ' +begin + insert into qci_product_series_map_audit ( + series_id, product_id, + last_modified, + last_modifying_user, modified_ip_address + ) values ( + old.series_id, old.product_id, + old.last_modified, + old.last_modifying_user, old.modified_ip_address + ); + return new; +end;' language 'plpgsql'; + +create trigger qci_product_series_map_audit_tr +after update or delete on qci_product_series_map +for each row execute procedure qci_product_series_map_audit_tr (); + + +-- SL makemodel references parts_id, we have qci_product_series_map +-- and use qci_series for tracking series data +CREATE TABLE qci_series ( + series_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)); 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 -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, Index: openacs-4/packages/inventory-control/sql/postgresql/pl-sql-utilities.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/inventory-control/sql/postgresql/pl-sql-utilities.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/inventory-control/sql/postgresql/pl-sql-utilities.sql 3 Jul 2006 19:29:14 -0000 1.1 +++ openacs-4/packages/inventory-control/sql/postgresql/pl-sql-utilities.sql 9 Jul 2006 22:14:24 -0000 1.2 @@ -57,3 +57,32 @@ space_sep_list := substring(space_sep_list from first_space+1); end loop; end;' language 'plpgsql'; + +-- 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'; +