Index: openacs-4/packages/ecommerce/sql/oracle/ecommerce-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/sql/oracle/ecommerce-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/ecommerce/sql/oracle/ecommerce-create.sql 20 Apr 2001 20:51:12 -0000 1.1 +++ openacs-4/packages/ecommerce/sql/oracle/ecommerce-create.sql 9 Jul 2001 23:28:10 -0000 1.2 @@ -4,6 +4,9 @@ -- by eveander@arsdigita.com, April 1999 -- and ported by Jerry Asher (jerry@theashergroup.com) -- and Walter McGinnis (wtem@olywa.net) +-- +-- ported to OpenACS by Gilbert wong (gwong@orchardlabs.com) +-- July 2001 -- Besides the tables defined here, you also need to import -- zip_codes, which contains the following columns: @@ -23,65 +26,84 @@ -- a column for marking when a logged entry was for a deleted row. -- product display templates -create sequence ec_template_id_sequence start with 2; +create sequence ec_template_id_seq start 2; +create view ec_template_id_sequence as select nextval('ec_template_id_seq') as nextval; +-- 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 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'; + -- I should have named this product_templates because now we -- have other kinds of templates. create table ec_templates ( template_id integer not null primary key, template_name varchar(200), template varchar(4000), - last_modified date not null, - last_modifying_user not null references users, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create table ec_templates_audit ( template_id integer, template_name varchar(200), template varchar(4000), - last_modified date, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -- A trigger is used to move information from the main table to -- the audit table -create or replace trigger ec_templates_audit_tr -before update or delete on ec_templates -for each row +create function ec_templates_audit_tr () +returns opaque as ' begin insert into ec_templates_audit ( template_id, template_name, template, last_modified, last_modifying_user, modified_ip_address ) values ( - :old.template_id, - :old.template_name, :old.template, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.template_id, + old.template_name, old.template, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_templates_audit_tr +before update or delete on ec_templates +for each row execute procedure ec_templates_audit_tr (); + -- this is where the ec_templates insert was - - -- product categories and subcategories and subsubcategories -create sequence ec_category_id_sequence; +create sequence ec_category_id_seq; +create view ec_category_id_sequence as select nextval('ec_category_id_seq') as nextval; create table ec_categories ( category_id integer not null primary key, -- pretty, human-readable category_name varchar(100), - sort_key number, - last_modified date not null, - last_modifying_user not null references users, + sort_key numeric, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); @@ -90,40 +112,44 @@ create table ec_categories_audit ( category_id integer, category_name varchar(100), - sort_key number, - last_modified date, + sort_key numeric, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -create or replace trigger ec_categories_audit_tr -before update or delete on ec_categories -for each row +create function ec_categories_audit_tr () +returns opaque as ' begin insert into ec_categories_audit ( category_id, category_name, sort_key, last_modified, last_modifying_user, modified_ip_address ) values ( - :old.category_id, :old.category_name, :old.sort_key, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.category_id, old.category_name, old.sort_key, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; -create sequence ec_subcategory_id_sequence; +create trigger ec_categories_audit_tr +before update or delete on ec_categories +for each row execute procedure ec_categories_audit_tr (); +create sequence ec_subcategory_id_seq; +create view ec_subcategory_id_sequence as select nextval('ec_subcategory_id_seq') as nextval; + + create table ec_subcategories ( subcategory_id integer not null primary key, - category_id not null references ec_categories, + category_id integer not null references ec_categories, -- pretty, human-readable subcategory_name varchar(100), - sort_key number, - last_modified date not null, - last_modifying_user not null references users, + sort_key numeric, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); @@ -134,88 +160,94 @@ subcategory_id integer, category_id integer, subcategory_name varchar(100), - sort_key number, - last_modified date, + sort_key numeric, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -create or replace trigger ec_subcategories_audit_tr -before update or delete on ec_subcategories -for each row +create function ec_subcategories_audit_tr () +returns opaque as ' begin insert into ec_subcategories_audit ( subcategory_id, category_id, subcategory_name, sort_key, last_modified, last_modifying_user, modified_ip_address ) values ( - :old.subcategory_id, :old.category_id, - :old.subcategory_name, :old.sort_key, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.subcategory_id, old.category_id, + old.subcategory_name, old.sort_key, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_subcategories_audit_tr +before update or delete on ec_subcategories +for each row execute procedure ec_subcategories_audit_tr (); + -- a view with category_name also -create or replace view ec_subcategories_augmented +create view ec_subcategories_augmented as select subs.*, cats.category_name from ec_subcategories subs, ec_categories cats where subs.category_id = cats.category_id; -create sequence ec_subsubcategory_id_sequence; +create sequence ec_subsubcategory_id_seq; +create view ec_subsubcategory_id_sequence as select nextval('ec_subsubcategory_id_seq') as nextval; create table ec_subsubcategories ( subsubcategory_id integer not null primary key, - subcategory_id not null references ec_subcategories, + subcategory_id integer not null references ec_subcategories, -- pretty, human-readable subsubcategory_name varchar(100), - sort_key number, - last_modified date not null, - last_modifying_user not null references users, + sort_key numeric, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create index ec_subsubcategories_idx on ec_subsubcategories (subcategory_id); create index ec_subsubcategories_idx2 on ec_subsubcategories (sort_key); + create table ec_subsubcategories_audit ( subsubcategory_id integer, subcategory_id integer, subsubcategory_name varchar(100), - sort_key number, - last_modified date, + sort_key numeric, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -create or replace trigger ec_subsubcategories_audit_tr -before update or delete on ec_subsubcategories -for each row +create function ec_subsubcategories_audit_tr () +returns opaque as ' begin insert into ec_subsubcategories_audit ( subsubcategory_id, subcategory_id, subsubcategory_name, sort_key, last_modified, last_modifying_user, modified_ip_address ) values ( - :old.subsubcategory_id, :old.subcategory_id, - :old.subsubcategory_name, :old.sort_key, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.subsubcategory_id, old.subcategory_id, + old.subsubcategory_name, old.sort_key, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_subsubcategories_audit_tr +before update or delete on ec_subsubcategories +for each row execute procedure ec_subsubcategories_audit_tr (); + -- a view with full subcategory and category info as well -create or replace view ec_subsubcategories_augmented +create view ec_subsubcategories_augmented as select subsubs.*, subs.subcategory_name, cats.category_id, cats.category_name from ec_subsubcategories subsubs, ec_subcategories subs, ec_categories cats @@ -225,7 +257,7 @@ -- this should be replaced by the object_id sequence -- grep for it in files... --- create sequence ec_product_id_sequence start with 1; +-- 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 @@ -237,72 +269,97 @@ -- series. -- wtem@olywa.net, 2001-03-24 -begin - acs_object_type.create_type ( - supertype => 'acs_object', - object_type => 'ec_product', - pretty_name => 'Product', - pretty_plural => 'Products', - table_name => 'EC_PRODUCTS', - id_column => 'PRODUCT_ID', - package_name => 'ECOMMERCE' - ); -end; -/ -show errors; +-- begin +-- acs_object_type__create_type ( +-- supertype => 'acs_object', +-- object_type => 'ec_product', +-- pretty_name => 'Product', +-- pretty_plural => 'Products', +-- table_name => 'EC_PRODUCTS', +-- id_column => 'PRODUCT_ID', +-- package_name => 'ECOMMERCE' +-- ); +-- end; +-- / +-- show errors; +create function inline_0 () +returns integer as ' +begin + + PERFORM acs_object_type__create_type ( + ''ec_product'', + ''Product'', + ''Products'', + ''acs_object'', + ''ec_products'', + ''product_id'', + ''ecommerce'', + ''f'', + null, + null + ); + + return 0; + +end;' language 'plpgsql'; + +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 ec_products ( - product_id constraint ec_products_product_id_pk - primary key - constraint ec_products_product__id_fk - references acs_objects, + product_id integer constraint ec_products_product_id_fk + references acs_objects(object_id) + on delete cascade + constraint 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 date default sysdate not null, + creation_date timestamp default now() 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 ec_product_user_class_prices - price number, + price numeric, -- for stuff that can't be shipped like services - no_shipping_avail_p char(1) default 'f' check(no_shipping_avail_p in ('t', 'f')), + no_shipping_avail_p boolean default 'f', -- leave this blank if shipping is calculated using -- one of the more complicated methods available - shipping number, + 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 number, + 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 number, + 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 char(1) default 't' check (present_p in ('t','f')), + present_p boolean default 't', -- whether the item should show up at all in the user pages - active_p char(1) default 't' check (active_p in ('t','f')), + 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 date default sysdate not null, + available_date timestamp default now() not null, announcements varchar(4000), - announcements_expire date, + announcements_expire timestamp, -- if there's a web site with more info about the product url varchar(300), - template_id references ec_templates, + template_id integer references 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 @@ -316,50 +373,49 @@ -- 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 date not null, - last_modifying_user not null references users, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); -create or replace view ec_products_displayable +create view ec_products_displayable as select * from ec_products where active_p='t'; -create or replace view ec_products_searchable +create view ec_products_searchable as select * from ec_products where active_p='t' and present_p='t'; create table ec_products_audit ( product_id integer, product_name varchar(200), - creation_date date, + creation_date timestamp, one_line_description varchar(400), detailed_description varchar(4000), search_keywords varchar(4000), - price number, - shipping number, - shipping_additional number, - weight number, + price numeric, + shipping numeric, + shipping_additional numeric, + weight numeric, dirname varchar(200), - present_p char(1) default 't' check (present_p in ('t','f')), - active_p char(1) default 't' check (active_p in ('t','f')), - available_date date, + present_p boolean default 't', + active_p boolean default 't', + available_date timestamp, announcements varchar(4000), - announcements_expire date, + announcements_expire timestamp, url varchar(300), template_id integer, stock_status char(1) check (stock_status in ('o','q','m','s','i')), - last_modified date, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -create or replace trigger ec_products_audit_tr -before update or delete on ec_products -for each row +create function ec_products_audit_tr () +returns opaque as ' begin insert into ec_products_audit ( product_id, product_name, creation_date, @@ -374,32 +430,36 @@ 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 + 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 ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_products_audit_tr +before update or delete on ec_products +for each row execute procedure 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. create table ec_product_purchase_comb ( product_id integer not null primary key references ec_products, - product_0 references ec_products, - product_1 references ec_products, - product_2 references ec_products, - product_3 references ec_products, - product_4 references ec_products + product_0 integer references ec_products, + product_1 integer references ec_products, + product_2 integer references ec_products, + product_3 integer references ec_products, + product_4 integer references ec_products ); create index ec_product_purchase_comb_idx0 on ec_product_purchase_comb(product_0); @@ -408,76 +468,80 @@ create index ec_product_purchase_comb_idx3 on ec_product_purchase_comb(product_3); create index ec_product_purchase_comb_idx4 on ec_product_purchase_comb(product_4); -create sequence ec_sale_price_id_sequence start with 1; +create sequence ec_sale_price_id_seq start 1; +create view ec_sale_price_id_sequence as select nextval('ec_sale_price_id_seq') as nextval; create table ec_sale_prices ( sale_price_id integer not null primary key, - product_id not null references ec_products, - sale_price number, - sale_begins date not null, - sale_ends date not null, + product_id integer not null references ec_products, + sale_price numeric, + sale_begins timestamp not null, + sale_ends timestamp not null, -- like Introductory Price or Sale Price or Special Offer sale_name varchar(30), -- if non-null, the user has to know this code to get the sale price offer_code varchar(20), - last_modified date not null, - last_modifying_user not null references users, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create index ec_sale_prices_by_product_idx on ec_sale_prices(product_id); -create or replace view ec_sale_prices_current +create view ec_sale_prices_current as select * from ec_sale_prices -where sysdate-sale_begins>=0 -and sysdate-sale_ends<=0; +where now() >= sale_begins +and now() <= sale_ends; create table ec_sale_prices_audit ( sale_price_id integer, product_id integer, - sale_price number, - sale_begins date, - sale_ends date, + sale_price numeric, + sale_begins timestamp, + sale_ends timestamp, sale_name varchar(30), offer_code varchar(20), - last_modified date, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -create or replace trigger ec_sale_prices_audit_tr -before update or delete on ec_sale_prices -for each row +create function ec_sale_prices_audit_tr () +returns opaque as ' begin insert into ec_sale_prices_audit ( sale_price_id, product_id, sale_price, sale_begins, sale_ends, sale_name, offer_code, last_modified, last_modifying_user, modified_ip_address ) values ( - :old.sale_price_id, :old.product_id, :old.sale_price, - :old.sale_begins, :old.sale_ends, :old.sale_name, :old.offer_code, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.sale_price_id, old.product_id, old.sale_price, + old.sale_begins, old.sale_ends, old.sale_name, old.offer_code, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_sale_prices_audit_tr +before update or delete on ec_sale_prices +for each row execute procedure ec_sale_prices_audit_tr (); + + create table ec_product_series_map ( -- this is the product_id of a product that happens to be -- a series - series_id not null references ec_products, + series_id integer not null references ec_products, -- this is the product_id of a product that is one of the -- components of the above series - component_id not null references ec_products, + component_id integer not null references ec_products, primary key (series_id, component_id), - last_modified date not null, - last_modifying_user not null references users, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); @@ -486,35 +550,39 @@ create table ec_product_series_map_audit ( series_id integer, component_id integer, - last_modified date, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -create or replace trigger ec_product_series_map_audit_tr -before update or delete on ec_product_series_map -for each row +create function ec_product_series_map_audit_tr () +returns opaque as ' begin insert into 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 + old.series_id, old.component_id, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; -create sequence ec_address_id_sequence start with 1; +create trigger ec_product_series_map_audit_tr +before update or delete on ec_product_series_map +for each row execute procedure ec_product_series_map_audit_tr (); + +create sequence ec_address_id_seq start 1; +create view ec_address_id_sequence as select nextval('ec_address_id_seq') as nextval; + create table ec_addresses ( address_id integer not null primary key, - user_id not null references users, + user_id integer not null references users, address_type varchar(20) not null, -- e.g., billing attn varchar(100), line1 varchar(100), @@ -537,11 +605,12 @@ create index ec_addresses_by_user_idx on ec_addresses (user_id); -create sequence ec_creditcard_id_sequence start with 1; +create sequence ec_creditcard_id_seq start 1; +create view ec_creditcard_id_sequence as select nextval('ec_creditcard_id_seq') as nextval; create table ec_creditcards ( creditcard_id integer not null primary key, - user_id not null references users, + user_id integer not null references users, -- Cybercash does not ask for this but we'll store it anyway creditcard_type char(1), -- no spaces; always 16 digits (oops; except for AMEX, which is 15) @@ -556,54 +625,58 @@ billing_zip_code varchar(80), -- if it ever failed (conclusively), set this to 't' so we -- won't give them the option of using it again - failed_p char(1) default 'f' check (failed_p in ('t','f')) + failed_p boolean default 'f' ); create index ec_creditcards_by_user_idx on ec_creditcards (user_id); -create sequence ec_user_class_id_sequence start with 1; +create sequence ec_user_class_id_seq start 1; +create view ec_user_class_id_sequence as select nextval('ec_user_class_id_seq') as nextval; create table ec_user_classes ( user_class_id integer not null primary key, -- human-readable user_class_name varchar(200), -- e.g., student - last_modified date not null, - last_modifying_user not null references users, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create table ec_user_classes_audit ( user_class_id integer, user_class_name varchar(200), -- e.g., student - last_modified date, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -create or replace trigger ec_user_classes_audit_tr -before update or delete on ec_user_classes -for each row +create function ec_user_classes_audit_tr () +returns opaque as ' begin insert into ec_user_classes_audit ( user_class_id, user_class_name, last_modified, last_modifying_user, modified_ip_address ) values ( - :old.user_class_id, :old.user_class_name, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.user_class_id, old.user_class_name, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_user_classes_audit_tr +before update or delete on ec_user_classes +for each row execute procedure ec_user_classes_audit_tr (); + + create table ec_product_user_class_prices ( - product_id not null references ec_products, - user_class_id not null references ec_user_classes, - price number, - last_modified date not null, - last_modifying_user not null references users, + product_id integer not null references ec_products, + user_class_id integer not null references ec_user_classes, + price numeric, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null, primary key (product_id, user_class_id) ); @@ -615,49 +688,52 @@ create table ec_product_u_c_prices_audit ( product_id integer, user_class_id integer, - price number, - last_modified date, + price numeric, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -create or replace trigger ec_product_u_c_prices_audit_tr -before update or delete on ec_product_user_class_prices -for each row +create function ec_product_u_c_prices_audit_tr () +returns opaque as ' begin insert into ec_product_u_c_prices_audit ( product_id, user_class_id, price, last_modified, last_modifying_user, modified_ip_address ) values ( - :old.product_id, :old.user_class_id, - :old.price, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.product_id, old.user_class_id, + old.price, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_product_u_c_prices_audit_tr +before update or delete on ec_product_user_class_prices +for each row execute procedure ec_product_u_c_prices_audit_tr (); -create sequence ec_recommendation_id_sequence start with 1; +create sequence ec_recommendation_id_seq start 1; +create view ec_recommendation_id_sequence as select nextval('ec_recommendation_id_seq') as nextval; + create table ec_product_recommendations ( recommendation_id integer not null primary key, - product_id not null references ec_products, + product_id integer not null references ec_products, -- might be null if the product is recommended for everyone - user_class_id references ec_user_classes, + user_class_id integer references ec_user_classes, -- html format recommendation_text varchar(4000), - active_p char(1) default 't' check (active_p in ('t','f')), + active_p boolean default 't', -- where it's displayed (top level if all three are blank): - category_id references ec_categories, - subcategory_id references ec_subcategories, - subsubcategory_id references ec_subsubcategories, - last_modified date not null, - last_modifying_user not null references users, + category_id integer references ec_categories, + subcategory_id integer references ec_subcategories, + subsubcategory_id integer references ec_subsubcategories, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); @@ -672,19 +748,18 @@ product_id integer, user_class_id integer, recommendation_text varchar(4000), - active_p char(1) default 't' check (active_p in ('t','f')), + active_p boolean default 't', category_id integer, subcategory_id integer, subsubcategory_id integer, - last_modified date, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -create or replace trigger ec_product_recommend_audit_tr -before update or delete on ec_product_recommendations -for each row +create function ec_product_recommend_audit_tr () +returns opaque as ' begin insert into ec_product_recommend_audit ( recommendation_id, product_id, @@ -694,50 +769,58 @@ last_modified, last_modifying_user, modified_ip_address ) values ( - :old.recommendation_id, :old.product_id, - :old.user_class_id, :old.recommendation_text, - :old.active_p, :old.category_id, - :old.subcategory_id, :old.subsubcategory_id, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.recommendation_id, old.product_id, + old.user_class_id, old.recommendation_text, + old.active_p, old.category_id, + old.subcategory_id, old.subsubcategory_id, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; -create or replace view ec_recommendations_cats_view as +create trigger ec_product_recommend_audit_tr +before update or delete on ec_product_recommendations +for each row execute procedure ec_product_recommend_audit_tr (); + +create view ec_recommendations_cats_view as select recs.*, - nvl(cats.category_id,nvl(subs.category_id,subsubs.category_id)) as the_category_id, - nvl(cats.category_name,nvl(subs.category_name,subsubs.category_name)) as the_category_name, - nvl(subs.subcategory_id,subsubs.subcategory_id) as the_subcategory_id, - nvl(subs.subcategory_name,subsubs.subcategory_name) as the_subcategory_name, + coalesce(cats.category_id,subs.category_id,subsubs.category_id) as the_category_id, + coalesce(cats.category_name,subs.category_name,subsubs.category_name) as the_category_name, + coalesce(subs.subcategory_id,subsubs.subcategory_id) as the_subcategory_id, + coalesce(subs.subcategory_name,subsubs.subcategory_name) as the_subcategory_name, subsubs.subsubcategory_id as the_subsubcategory_id, subsubs.subsubcategory_name as the_subsubcategory_name from - ec_product_recommendations recs, - ec_categories cats, - ec_subcategories_augmented subs, - ec_subsubcategories_augmented subsubs -where - recs.category_id = cats.category_id(+) -and - recs.subcategory_id = subs.subcategory_id(+) -and - recs.subsubcategory_id = subsubs.subcategory_id(+); + ec_product_recommendations recs + LEFT JOIN + ec_categories cats using (category_id) + LEFT JOIN + ec_subcategories_augmented subs using (subcategory_id) + LEFT JOIN + ec_subsubcategories_augmented subsubs on (recs.subsubcategory_id = subsubs.subcategory_id); +-- ec_categories cats, +-- ec_subcategories_augmented subs, +-- ec_subsubcategories_augmented subsubs +-- where + -- recs.category_id = cats.category_id(+) +-- and + -- recs.subcategory_id = subs.subcategory_id(+) +-- and + -- recs.subsubcategory_id = subsubs.subcategory_id(+); + -- one row per customer-user; all the extra info that the ecommerce -- system needs create table ec_user_class_user_map ( - user_id not null references users, - user_class_id not null references ec_user_classes, + user_id integer not null references users, + user_class_id integer not null references ec_user_classes, primary key (user_id, user_class_id), - user_class_approved_p char(1) - check (user_class_approved_p - in ('t','f')), - last_modified date not null, - last_modifying_user not null references users, + user_class_approved_p boolean, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); @@ -747,38 +830,40 @@ create table ec_user_class_user_map_audit ( user_id integer, user_class_id integer, - user_class_approved_p char(1) check (user_class_approved_p in ('t','f')), - last_modified date, + user_class_approved_p boolean, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -create or replace trigger ec_user_class_user_audit_tr -before update or delete on ec_user_class_user_map -for each row +create function ec_user_class_user_audit_tr () +returns opaque as ' begin insert into ec_user_class_user_map_audit ( user_id, user_class_id, user_class_approved_p, last_modified, last_modifying_user, modified_ip_address ) values ( - :old.user_id, :old.user_class_id, :old.user_class_approved_p, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.user_id, old.user_class_id, old.user_class_approved_p, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_user_class_user_audit_tr +before update or delete on ec_user_class_user_map +for each row execute procedure ec_user_class_user_audit_tr (); + -- this specifies that product_a links to product_b on the display page for product_a create table ec_product_links ( - product_a not null references ec_products, - product_b not null references ec_products, - last_modified date not null, - last_modifying_user not null references users, + product_a integer not null references ec_products, + product_b integer not null references ec_products, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null, primary key (product_a, product_b) ); @@ -788,44 +873,47 @@ create table ec_product_links_audit ( product_a integer, product_b integer, - last_modified date, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -create or replace trigger ec_product_links_audit_tr -before update or delete on ec_product_links -for each row +create function ec_product_links_audit_tr () +returns opaque as ' begin insert into ec_product_links_audit ( product_a, product_b, last_modified, last_modifying_user, modified_ip_address ) values ( - :old.product_a, :old.product_b, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.product_a, old.product_b, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; -create sequence ec_product_comment_id_sequence start with 1; +create trigger ec_product_links_audit_tr +before update or delete on ec_product_links +for each row execute procedure ec_product_links_audit_tr (); +create sequence ec_product_comment_id_seq start 1; +create view ec_product_comment_id_sequence as select nextval('ec_product_comment_id_seq') as nextval; + -- comments made by users on the products create table ec_product_comments ( comment_id integer not null primary key, - product_id not null references ec_products, - user_id not null references users, + product_id integer not null references ec_products, + user_id integer not null references users, user_comment varchar(4000), one_line_summary varchar(300), - rating number, + rating numeric, -- in some systems, the administrator will have to approve comments first - approved_p char(1) check (approved_p in ('t','f')), - comment_date date, - last_modified date not null, - last_modifying_user not null references users, + approved_p boolean, + comment_date timestamp, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); @@ -839,48 +927,51 @@ user_id integer, user_comment varchar(4000), one_line_summary varchar(300), - rating number, - approved_p char(1), - last_modified date, + rating numeric, + approved_p boolean, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -create or replace trigger ec_product_comments_audit_tr -before update or delete on ec_product_comments -for each row +create function ec_product_comments_audit_tr () +returns opaque as ' begin insert into ec_product_comments_audit ( comment_id, product_id, user_id, user_comment, one_line_summary, rating, approved_p, last_modified, last_modifying_user, modified_ip_address ) values ( - :old.comment_id, :old.product_id, :old.user_id, - :old.user_comment, :old.one_line_summary, :old.rating, :old.approved_p, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.comment_id, old.product_id, old.user_id, + old.user_comment, old.one_line_summary, old.rating, old.approved_p, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_product_comments_audit_tr +before update or delete on ec_product_comments +for each row execute procedure ec_product_comments_audit_tr (); -create sequence ec_product_review_id_sequence start with 1; +create sequence ec_product_review_id_seq start 1; +create view ec_product_review_id_sequence as select nextval('ec_product_review_id_seq') as nextval; + -- reviews made by professionals of the products create table ec_product_reviews ( review_id integer not null primary key, - product_id not null references ec_products, + product_id integer not null references ec_products, author_name varchar(100), publication varchar(100), - review_date date, + review_date timestamp, -- in HTML format - review clob, - display_p char(1) check (display_p in ('t','f')), - last_modified date not null, - last_modifying_user not null references users, + review text, + display_p boolean, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); @@ -892,19 +983,18 @@ product_id integer, author_name varchar(100), publication varchar(100), - review_date date, + review_date timestamp, -- in HTML format - review clob, - display_p char(1) check (display_p in ('t','f')), - last_modified date, + review text, + display_p boolean, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -create or replace trigger ec_product_reviews_audit_tr -before update or delete on ec_product_reviews -for each row +create function ec_product_reviews_audit_tr () +returns opaque as ' begin insert into ec_product_reviews_audit ( review_id, product_id, @@ -914,25 +1004,28 @@ last_modified, last_modifying_user, modified_ip_address ) values ( - :old.review_id, :old.product_id, - :old.author_name, :old.publication, :old.review_date, - :old.review, - :old.display_p, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.review_id, old.product_id, + old.author_name, old.publication, old.review_date, + old.review, + old.display_p, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_product_reviews_audit_tr +before update or delete on ec_product_reviews +for each row execute procedure ec_product_reviews_audit_tr (); + -- a product can be in more than one category create table ec_category_product_map ( - product_id not null references ec_products, - category_id not null references ec_categories, - publisher_favorite_p char(1) check (publisher_favorite_p in ('t','f')), - last_modified date not null, - last_modifying_user not null references users, + product_id integer not null references ec_products, + category_id integer not null references ec_categories, + publisher_favorite_p boolean, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null, primary key (product_id, category_id) ); @@ -943,40 +1036,43 @@ create table ec_category_product_map_audit ( product_id integer, category_id integer, - publisher_favorite_p char(1) check (publisher_favorite_p in ('t','f')), - last_modified date, + publisher_favorite_p boolean, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -- ec_category_product_map_audit_tr abbreviated as -- ec_cat_prod_map_audit_tr -create or replace trigger ec_cat_prod_map_audit_tr -before update or delete on ec_category_product_map -for each row +create function ec_cat_prod_map_audit_tr () +returns opaque as ' begin insert into ec_category_product_map_audit ( product_id, category_id, publisher_favorite_p, last_modified, last_modifying_user, modified_ip_address ) values ( - :old.product_id, :old.category_id, - :old.publisher_favorite_p, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.product_id, old.category_id, + old.publisher_favorite_p, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_cat_prod_map_audit_tr +before update or delete on ec_category_product_map +for each row execute procedure ec_cat_prod_map_audit_tr (); + + create table ec_subcategory_product_map ( - product_id not null references ec_products, - subcategory_id not null references ec_subcategories, - publisher_favorite_p char(1) check (publisher_favorite_p in ('t','f')), - last_modified date not null, - last_modifying_user not null references users, + product_id integer not null references ec_products, + subcategory_id integer not null references ec_subcategories, + publisher_favorite_p boolean, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null, primary key (product_id, subcategory_id) ); @@ -988,39 +1084,42 @@ create table ec_subcat_prod_map_audit ( product_id integer, subcategory_id integer, - publisher_favorite_p char(1) check (publisher_favorite_p in ('t','f')), - last_modified date, + publisher_favorite_p boolean, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -- ec_subcat_prod_map_audit_tr -create or replace trigger ec_subcat_prod_map_audit_tr -before update or delete on ec_subcategory_product_map -for each row +create function ec_subcat_prod_map_audit_tr () +returns opaque as ' begin insert into ec_subcat_prod_map_audit ( product_id, subcategory_id, publisher_favorite_p, last_modified, last_modifying_user, modified_ip_address ) values ( - :old.product_id, :old.subcategory_id, - :old.publisher_favorite_p, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.product_id, old.subcategory_id, + old.publisher_favorite_p, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_subcat_prod_map_audit_tr +before update or delete on ec_subcategory_product_map +for each row execute procedure ec_subcat_prod_map_audit_tr (); + + create table ec_subsubcategory_product_map ( - product_id not null references ec_products, - subsubcategory_id not null references ec_subsubcategories, - publisher_favorite_p char(1) check (publisher_favorite_p in ('t','f')), - last_modified date not null, - last_modifying_user not null references users, + product_id integer not null references ec_products, + subsubcategory_id integer not null references ec_subsubcategories, + publisher_favorite_p boolean, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null, primary key (product_id, subsubcategory_id) ); @@ -1031,32 +1130,34 @@ create table ec_subsubcat_prod_map_audit ( product_id integer, subsubcategory_id integer, - publisher_favorite_p char(1) check (publisher_favorite_p in ('t','f')), - last_modified date, + publisher_favorite_p boolean, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -create or replace trigger ec_subsubcat_prod_map_audit_tr -before update or delete on ec_subsubcategory_product_map -for each row +create function ec_subsubcat_prod_map_audit_tr () +returns opaque as ' begin insert into ec_subsubcat_prod_map_audit ( product_id, subsubcategory_id, publisher_favorite_p, last_modified, last_modifying_user, modified_ip_address ) values ( - :old.product_id, :old.subsubcategory_id, - :old.publisher_favorite_p, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.product_id, old.subsubcategory_id, + old.publisher_favorite_p, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_subsubcat_prod_map_audit_tr +before update or delete on ec_subsubcategory_product_map +for each row execute procedure ec_subsubcat_prod_map_audit_tr (); + -- A template can have more than 1 category associated -- with it, but a category can have at most one template. -- When a product is added in a given category, its template @@ -1066,8 +1167,8 @@ -- the admin can always associate a product with any category -- they want. create table ec_category_template_map ( - category_id not null primary key references ec_categories, - template_id not null references ec_templates + category_id integer not null primary key references ec_categories, + template_id integer not null references ec_templates ); create index ec_category_template_map_idx on ec_category_template_map (template_id); @@ -1084,10 +1185,10 @@ default_value varchar(100), -- column type for oracle (i.e. text, varchar(50), integer, ...) column_type varchar(100), - creation_date date, - active_p char(1) default 't' check (active_p in ('t','f')), - last_modified date not null, - last_modifying_user not null references users, + creation_date timestamp, + active_p boolean default 't', + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); @@ -1096,17 +1197,16 @@ field_name varchar(100), default_value varchar(100), column_type varchar(100), - creation_date date, - active_p char(1) default 't' check (active_p in ('t','f')), - last_modified date, + creation_date timestamp, + active_p boolean default 't', + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -create or replace trigger ec_custom_prod_fields_audit_tr -before update or delete on ec_custom_product_fields -for each row +create function ec_custom_prod_fields_audit_tr () +returns opaque as ' begin insert into ec_custom_product_fields_audit ( field_identifier, field_name, @@ -1115,70 +1215,77 @@ last_modified, last_modifying_user, modified_ip_address ) values ( - :old.field_identifier, :old.field_name, - :old.default_value, :old.column_type, - :old.creation_date, :old.active_p, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.field_identifier, old.field_name, + old.default_value, old.column_type, + old.creation_date, old.active_p, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_custom_prod_fields_audit_tr +before update or delete on ec_custom_product_fields +for each row execute procedure ec_custom_prod_fields_audit_tr (); + -- more columns are added to this table (by Tcl scripts) when the -- administrator adds custom product fields -- the columns in this table have the name of the field_identifiers -- in ec_custom_product_fields -- this table stores the values create table ec_custom_product_field_values ( - product_id not null primary key references ec_products, - last_modified date not null, - last_modifying_user not null references users, + product_id integer not null primary key references ec_products, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create table ec_custom_p_field_values_audit ( product_id integer, - last_modified date, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -create or replace trigger ec_custom_p_f_values_audit_tr -before update or delete on ec_custom_product_field_values -for each row +create function ec_custom_p_f_values_audit_tr () +returns opaque as ' begin insert into ec_custom_p_field_values_audit ( product_id, last_modified, last_modifying_user, modified_ip_address ) values ( - :old.product_id, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.product_id, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_custom_p_f_values_audit_tr +before update or delete on ec_custom_product_field_values +for each row execute procedure ec_custom_p_f_values_audit_tr(); -create sequence ec_user_session_sequence; + +create sequence ec_user_session_seq; +create view ec_user_session_sequence as select nextval('ec_user_session_seq') as nextval; + create table ec_user_sessions ( user_session_id integer not null constraint ec_session_id_pk primary key, -- often will not be known - user_id references users, + user_id integer references users, ip_address varchar(20) not null, - start_time date, + start_time timestamp, http_user_agent varchar(4000) ); create index ec_user_sessions_idx on ec_user_sessions(user_id); create table ec_user_session_info ( - user_session_id not null references ec_user_sessions, - product_id references ec_products, - category_id references ec_categories, + user_session_id integer not null references ec_user_sessions, + product_id integer references ec_products, + category_id integer references ec_categories, search_text varchar(200) ); @@ -1194,8 +1301,8 @@ -- for a product. But I think this will be a little faster. create table ec_user_session_offer_codes ( - user_session_id not null references ec_user_sessions, - product_id not null references ec_products, + user_session_id integer not null references ec_user_sessions, + product_id integer not null references ec_products, offer_code varchar(20) not null, primary key (user_session_id, product_id) ); @@ -1204,38 +1311,39 @@ create index ec_u_s_offer_codes_by_u_s_id on ec_user_session_offer_codes(user_session_id); create index ec_u_s_offer_codes_by_p_id on ec_user_session_offer_codes(product_id); -create sequence ec_order_id_sequence start with 3000000; +create sequence ec_order_id_seq start 3000000; +create view ec_order_id_sequence as select nextval('ec_order_id_seq') as nextval; create table ec_orders ( - order_id integer not null primary key, + order_id integer not null primary key, -- can be null, until they've checked out or saved their basket - user_id references users, - user_session_id references ec_user_sessions, - order_state varchar(50) default 'in_basket' not null, - tax_exempt_p char(1) default 'f' check(tax_exempt_p in ('t', 'f')), - shipping_method varchar(20), -- express or standard or pickup or 'no shipping' + user_id integer references users, + user_session_id integer references ec_user_sessions, + order_state varchar(50) default 'in_basket' not null, + tax_exempt_p boolean default 'f', + shipping_method varchar(20), -- express or standard or pickup or 'no shipping' shipping_address integer references ec_addresses(address_id), -- store credit card info in a different table - creditcard_id integer references ec_creditcards(creditcard_id), + creditcard_id integer references ec_creditcards(creditcard_id), -- information recorded upon FSM state changes -- we need this to figure out if order is stale -- and should be offered up for removal - in_basket_date date, - confirmed_date date, - authorized_date date, - voided_date date, - expired_date date, + in_basket_date timestamp, + confirmed_date timestamp, + authorized_date timestamp, + voided_date timestamp, + expired_date timestamp, -- base shipping, which is added to the amount charged for each item - shipping_charged number, - shipping_refunded number, - shipping_tax_charged number, - shipping_tax_refunded number, + shipping_charged numeric, + shipping_refunded numeric, + shipping_tax_charged numeric, + shipping_tax_refunded numeric, -- entered by customer service cs_comments varchar(4000), reason_for_void varchar(4000), - voided_by references users, + voided_by integer references users, -- if the user chooses to save their shopping cart - saved_p char(1) check (saved_p in ('t','f')), + saved_p boolean check (user_id is not null or user_session_id is not null) ); @@ -1249,44 +1357,45 @@ -- note that an order could essentially become uninteresting for financial -- accounting if all the items underneath it are individually voided or returned -create or replace view ec_orders_reportable +create view ec_orders_reportable as select * from ec_orders where order_state <> 'in_basket' and order_state <> 'void'; -- orders that have items which still need to be shipped -create or replace view ec_orders_shippable +create view ec_orders_shippable as select * from ec_orders where order_state in ('authorized_plus_avs','authorized_minus_avs','partially_fulfilled'); -- this is needed because orders might be only partially shipped -create sequence ec_shipment_id_sequence; +create sequence ec_shipment_id_seq; +create view ec_shipment_id_sequence as select nextval('ec_shipment_id_seq') as nextval; create table ec_shipments ( shipment_id integer not null primary key, - order_id not null references ec_orders, + order_id integer not null references ec_orders, -- usually, but not necessarily, the same as the shipping_address -- in ec_orders because a customer may change their address between -- shipments. -- a trigger fills address_id in automatically if it's null - address_id not null references ec_addresses, - shipment_date date not null, - expected_arrival_date date, + address_id integer not null references ec_addresses, + shipment_date timestamp not null, + expected_arrival_date timestamp, carrier varchar(50), -- e.g., 'fedex' tracking_number varchar(24), -- only if we get confirmation from carrier that the goods -- arrived on a specific date - actual_arrival_date date, + actual_arrival_date timestamp, -- arbitrary info from carrier, e.g., 'Joe Smith signed for it' actual_arrival_detail varchar(4000), -- for things that aren't really shipped like services - shippable_p char(1) default 't' check(shippable_p in ('t', 'f')), - last_modified date, + shippable_p boolean default 't', + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20) ); @@ -1296,40 +1405,41 @@ -- fills address_id into ec_shipments if it's missing -- (using the shipping_address associated with the order) -create or replace trigger ec_shipment_address_update_tr -before insert on ec_shipments -for each row +create function ec_shipment_address_update_tr () +returns opaque as ' declare v_address_id ec_addresses.address_id%TYPE; begin - select shipping_address into v_address_id from ec_orders where order_id=:new.order_id; - IF :new.address_id is null THEN - :new.address_id := v_address_id; + select into v_address_id shipping_address + from ec_orders where order_id=new.order_id; + IF new.address_id is null THEN + new.address_id := v_address_id; END IF; -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_shipment_address_update_tr +before insert on ec_shipments +for each row execute procedure ec_shipment_address_update_tr (); create table ec_shipments_audit ( shipment_id integer, order_id integer, address_id integer, - shipment_date date, - expected_arrival_date date, + shipment_date timestamp, + expected_arrival_date timestamp, carrier varchar(50), tracking_number varchar(24), - actual_arrival_date date, + actual_arrival_date timestamp, actual_arrival_detail varchar(4000), - last_modified date, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -create or replace trigger ec_shipments_audit_tr -before update or delete on ec_shipments -for each row +create function ec_shipments_audit_tr () +returns opaque as ' begin insert into ec_shipments_audit ( shipment_id, order_id, address_id, @@ -1340,65 +1450,69 @@ last_modified, last_modifying_user, modified_ip_address ) values ( - :old.shipment_id, :old.order_id, :old.address_id, - :old.shipment_date, - :old.expected_arrival_date, - :old.carrier, :old.tracking_number, - :old.actual_arrival_date, :old.actual_arrival_detail, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.shipment_id, old.order_id, old.address_id, + old.shipment_date, + old.expected_arrival_date, + old.carrier, old.tracking_number, + old.actual_arrival_date, old.actual_arrival_detail, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_shipments_audit_tr +before update or delete on ec_shipments +for each row execute procedure ec_shipments_audit_tr (); -create sequence refund_id_sequence; +create sequence refund_id_seq; +create view refund_id_sequence as select nextval('refund_id_seq') as nextval; create table ec_refunds ( refund_id integer not null primary key, - order_id not null references ec_orders, + order_id integer not null references ec_orders, -- not really necessary because it's in ec_financial_transactions - refund_amount number not null, - refund_date date not null, - refunded_by not null references users, + refund_amount numeric not null, + refund_date timestamp not null, + refunded_by integer not null references users, refund_reasons varchar(4000) ); create index ec_refunds_by_order_idx on ec_refunds (order_id); -- these are the items that make up each order -create sequence ec_item_id_sequence start with 1; +create sequence ec_item_id_seq start 1; +create view ec_item_id_sequence as select nextval('ec_item_id_seq') as nextval; create table ec_items ( item_id integer not null primary key, - order_id not null references ec_orders, - product_id not null references ec_products, + order_id integer not null references ec_orders, + product_id integer not null references ec_products, color_choice varchar(4000), size_choice varchar(4000), style_choice varchar(4000), - shipment_id references ec_shipments, + shipment_id integer references ec_shipments, -- this is the date that user put this item into their shopping basket - in_cart_date date, - voided_date date, - voided_by references users, - expired_date date, + in_cart_date timestamp, + voided_date timestamp, + voided_by integer references users, + expired_date timestamp, item_state varchar(50) default 'in_basket', -- NULL if not received back - received_back_date date, + received_back_date timestamp, -- columns for reporting (e.g., what was done, what was made) - price_charged number, - price_refunded number, - shipping_charged number, - shipping_refunded number, - price_tax_charged number, - price_tax_refunded number, - shipping_tax_charged number, - shipping_tax_refunded number, + price_charged numeric, + price_refunded numeric, + shipping_charged numeric, + shipping_refunded numeric, + price_tax_charged numeric, + price_tax_refunded numeric, + shipping_tax_charged numeric, + shipping_tax_refunded numeric, -- like Our Price or Sale Price or Introductory Price price_name varchar(30), -- did we go through a merchant-initiated refund? - refund_id references ec_refunds, + refund_id integer references ec_refunds, -- comments entered by customer service (CS) cs_comments varchar(4000) ); @@ -1407,20 +1521,20 @@ create index ec_items_by_order on ec_items(order_id); create index ec_items_by_shipment on ec_items(shipment_id); -create or replace view ec_items_reportable +create view ec_items_reportable as select * from ec_items where item_state in ('to_be_shipped', 'shipped', 'arrived'); -create or replace view ec_items_refundable +create view ec_items_refundable as select * from ec_items where item_state in ('shipped','arrived') and refund_id is null; -create or replace view ec_items_shippable +create view ec_items_shippable as select * from ec_items @@ -1435,12 +1549,17 @@ -- The purpose: payment is recognized when an item ships so this sums the various -- parts of payment (price, shipping, tax) for all the items in each shipment -create or replace view ec_items_money_view +-- gilbertw - there is a note in OpenACS 3.2.5 from DRB: +-- DRB: this view is never used and blows out Postgres, which thinks +-- it's too large even with a block size of (gulp) 16384! +-- gilbertw - this view is used now. + +create view ec_items_money_view as -select i.shipment_id, i.order_id, s.shipment_date, nvl(sum(i.price_charged),0) - nvl(sum(i.price_refunded),0) as bal_price_charged, -nvl(sum(i.shipping_charged),0) - nvl(sum(i.shipping_refunded),0) as bal_shipping_charged, -nvl(sum(i.price_tax_charged),0) - nvl(sum(i.price_tax_refunded),0) + nvl(sum(i.shipping_tax_charged),0) - - nvl(sum(i.shipping_tax_refunded),0) as bal_tax_charged +select i.shipment_id, i.order_id, s.shipment_date, coalesce(sum(i.price_charged),0) - coalesce(sum(i.price_refunded),0) as bal_price_charged, +coalesce(sum(i.shipping_charged),0) - coalesce(sum(i.shipping_refunded),0) as bal_shipping_charged, +coalesce(sum(i.price_tax_charged),0) - coalesce(sum(i.price_tax_refunded),0) + coalesce(sum(i.shipping_tax_charged),0) + - coalesce(sum(i.shipping_tax_refunded),0) as bal_tax_charged from ec_items i, ec_shipments s where i.shipment_id=s.shipment_id and i.item_state <> 'void' @@ -1463,65 +1582,78 @@ -- I. temporary table to hold the order_ids that have to have their -- state updated as a result of the item_state changes -create global temporary table ec_state_change_order_ids ( - order_id integer -); +-- gilbertw - this table is not needed in PostgreSQL +--create global temporary table ec_state_change_order_ids ( +-- order_id integer +--); + +-- gilbertw - this trigger is not needed -- II. row-level trigger which updates ec_state_change_order_ids -- so we know which rows to update in ec_orders -create or replace trigger ec_order_state_before_tr -before update on ec_items -for each row -begin - insert into ec_state_change_order_ids (order_id) values (:new.order_id); -end; -/ -show errors +-- create function ec_order_state_before_tr () +-- returns opaque as ' +-- begin +-- insert into ec_state_change_order_ids (order_id) values (new.order_id); +-- return new; +-- end;' language 'plpgsql'; +-- create trigger ec_order_state_before_tr +-- before update on ec_items +-- for each row execute procedure ec_order_state_before_tr (); + -- III. System level trigger to update all the rows that were changed -- in the before trigger. -create or replace trigger ec_order_state_after_tr -after update on ec_items -declare - CURSOR orders_cursor IS - SELECT unique order_id - FROM ec_state_change_order_ids; - v_order_id ec_orders.order_id%TYPE; +-- gilbertw - I took the trigger procedure from OpenACS 3.2.5. +create function ec_order_state_after_tr () +returns opaque as ' +declare + -- v_order_id integer; n_items integer; n_shipped_items integer; n_received_back_items integer; n_void_items integer; n_nonvoid_items integer; begin - FOR orders_cursor_rec IN orders_cursor LOOP - v_order_id := orders_cursor_rec.order_id; + select count(*) into n_items from ec_items where order_id=NEW.order_id; + select count(*) into n_shipped_items from ec_items + where order_id=NEW.order_id + and item_state=''shipped'' or item_state=''arrived''; + select count(*) into n_received_back_items + from ec_items where order_id=NEW.order_id + and item_state=''received_back''; + select count(*) into n_void_items from ec_items + where order_id=NEW.order_id and item_state=''void''; - select count(*) into n_items from ec_items where order_id=v_order_id; - select count(*) into n_shipped_items from ec_items where order_id=v_order_id and item_state='shipped' or item_state='arrived'; - select count(*) into n_received_back_items from ec_items where order_id=v_order_id and item_state='received_back'; - select count(*) into n_void_items from ec_items where order_id=v_order_id and item_state='void'; + IF n_items = n_void_items THEN + update ec_orders set order_state=''void'', voided_date=now() + where order_id=NEW.order_id; + ELSE + n_nonvoid_items := n_items - n_void_items; + IF n_nonvoid_items = n_received_back_items THEN + update ec_orders set order_state=''returned'' + where order_id=NEW.order_id; + ELSE + IF n_nonvoid_items = n_received_back_items + n_shipped_items THEN + update ec_orders set order_state=''fulfilled'' + where order_id=NEW.order_id; + ELSE + IF n_shipped_items >= 1 or n_received_back_items >=1 THEN + update ec_orders set order_state=''partially_fulfilled'' + where order_id=v_order_id; + END IF; + END IF; + END IF; + END IF; + return new; +end;' language 'plpgsql'; - IF n_items = n_void_items THEN - update ec_orders set order_state='void', voided_date=sysdate where order_id=v_order_id; - ELSE - n_nonvoid_items := n_items - n_void_items; - IF n_nonvoid_items = n_received_back_items THEN - update ec_orders set order_state='returned' where order_id=v_order_id; - ELSIF n_nonvoid_items = n_received_back_items + n_shipped_items THEN - update ec_orders set order_state='fulfilled' where order_id=v_order_id; - ELSIF n_shipped_items >= 1 or n_received_back_items >=1 THEN - update ec_orders set order_state='partially_fulfilled' where order_id=v_order_id; - END IF; - END IF; - END LOOP; - delete from ec_state_change_order_ids; -end; -/ -show errors +create trigger ec_order_state_after_tr +after update on ec_items +for each row execute procedure ec_order_state_after_tr (); - -- this is a 1-row table -- it contains all settings that the admin can change from the admin pages -- most of the configuration is done using the parameters .ini file @@ -1535,39 +1667,39 @@ -- so down) can't be executed twice admin_setting_id integer not null primary key, -- the following columns are related to shipping costs - base_shipping_cost number, - default_shipping_per_item number, - weight_shipping_cost number, - add_exp_base_shipping_cost number, - add_exp_amount_per_item number, - add_exp_amount_by_weight number, + base_shipping_cost numeric, + default_shipping_per_item numeric, + weight_shipping_cost numeric, + add_exp_base_shipping_cost numeric, + add_exp_amount_per_item numeric, + add_exp_amount_by_weight numeric, -- default template to use if the product isn't assigned to one -- (until the admin changes it, it will be 1, which will be -- the preloaded template) - default_template integer default 1 not null references ec_templates, - last_modified date not null, - last_modifying_user not null references users, - modified_ip_address varchar(20) not null + default_template integer default 1 not null + references ec_templates, + last_modified timestamp not null, + last_modifying_user integer not null references users, + modified_ip_address varchar(20) not null ); create table ec_admin_settings_audit ( admin_setting_id integer, - base_shipping_cost number, - default_shipping_per_item number, - weight_shipping_cost number, - add_exp_base_shipping_cost number, - add_exp_amount_per_item number, - add_exp_amount_by_weight number, - default_template integer, - last_modified date, - last_modifying_user integer, - modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + base_shipping_cost numeric, + default_shipping_per_item numeric, + weight_shipping_cost numeric, + add_exp_base_shipping_cost numeric, + add_exp_amount_per_item numeric, + add_exp_amount_by_weight numeric, + default_template integer, + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' ); -create or replace trigger ec_admin_settings_audit_tr -before update or delete on ec_admin_settings -for each row +create function ec_admin_settings_audit_tr () +returns opaque as ' begin insert into ec_admin_settings_audit ( admin_setting_id, base_shipping_cost, default_shipping_per_item, @@ -1577,67 +1709,74 @@ last_modified, last_modifying_user, modified_ip_address ) values ( - :old.admin_setting_id, :old.base_shipping_cost, :old.default_shipping_per_item, - :old.weight_shipping_cost, :old.add_exp_base_shipping_cost, - :old.add_exp_amount_per_item, :old.add_exp_amount_by_weight, - :old.default_template, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.admin_setting_id, old.base_shipping_cost, + old.default_shipping_per_item, + old.weight_shipping_cost, old.add_exp_base_shipping_cost, + old.add_exp_amount_per_item, old.add_exp_amount_by_weight, + old.default_template, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_admin_settings_audit_tr +before update or delete on ec_admin_settings +for each row execute procedure ec_admin_settings_audit_tr (); + -- this is where the ec_amdin_settings insert was -- this is populated by the rules the administrator sets in packages/ecommerce/www/admin]/sales-tax.tcl create table ec_sales_tax_by_state ( -- Jerry - usps_abbrev not null primary key references states, + usps_abbrev char(2) not null primary key references states, -- this a decimal number equal to the percentage tax divided by 100 - tax_rate number not null, + tax_rate numeric not null, -- charge tax on shipping? - shipping_p char(1) not null check (shipping_p in ('t','f')), - last_modified date not null, - last_modifying_user not null references users, + shipping_p boolean not null, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create table ec_sales_tax_by_state_audit ( usps_abbrev char(2), - tax_rate number, - shipping_p char(1) check (shipping_p in ('t','f')), - last_modified date, + tax_rate numeric, + shipping_p boolean, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -- Jerry - I removed usps_abbrev and/or state here -create or replace trigger ec_sales_tax_by_state_audit_tr -before update or delete on ec_sales_tax_by_state -for each row +create function ec_sales_tax_by_state_audit_tr () +returns opaque as ' begin insert into ec_sales_tax_by_state_audit ( usps_abbrev, tax_rate, shipping_p, last_modified, last_modifying_user, modified_ip_address ) values ( - :old.usps_abbrev, :old.tax_rate, - :old.shipping_p, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.usps_abbrev, old.tax_rate, + old.shipping_p, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_sales_tax_by_state_audit_tr +before update or delete on ec_sales_tax_by_state +for each row execute procedure ec_sales_tax_by_state_audit_tr (); + -- these tables are used if MultipleRetailersPerProductP is 1 in the -- parameters .ini file -create sequence ec_retailer_sequence start with 1; +create sequence ec_retailer_seq start 1; +create view ec_retailer_sequence as select nextval('ec_retailer_seq') as nextval; create table ec_retailers ( retailer_id integer not null primary key, @@ -1651,7 +1790,7 @@ city varchar(100), -- state -- Jerry - usps_abbrev char(2) references states, + usps_abbrev char(2) references states, -- big enough to hold zip+4 with dash zip_code varchar(10), phone varchar(30), @@ -1669,8 +1808,8 @@ price_guarantee_policy varchar(4000), delivery_policy varchar(4000), installation_policy varchar(4000), - last_modified date not null, - last_modifying_user not null references users, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); @@ -1681,32 +1820,31 @@ secondary_contact_name varchar(100), primary_contact_info varchar(4000), secondary_contact_info varchar(4000), - line1 varchar(100), - line2 varchar(100), - city varchar(100), - usps_abbrev char(2), - zip_code varchar(10), - phone varchar(30), - fax varchar(30), - country_code char(2), - reach varchar(15) check (reach in ('national','local','international','regional','web')), - url varchar(200), - nexus_states varchar(200), + line1 varchar(100), + line2 varchar(100), + city varchar(100), + usps_abbrev char(2), + zip_code varchar(10), + phone varchar(30), + fax varchar(30), + country_code char(2), + reach varchar(15) check (reach in ('national','local','international','regional','web')), + url varchar(200), + nexus_states varchar(200), financing_policy varchar(4000), return_policy varchar(4000), price_guarantee_policy varchar(4000), delivery_policy varchar(4000), installation_policy varchar(4000), - last_modified date, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -- Jerry - I removed usps_abbrev and/or state here -create or replace trigger ec_retailers_audit_tr -before update or delete on ec_retailers -for each row +create function ec_retailers_audit_tr () +returns opaque as ' begin insert into ec_retailers_audit ( retailer_id, retailer_name, @@ -1723,30 +1861,33 @@ last_modified, last_modifying_user, modified_ip_address ) values ( - :old.retailer_id, :old.retailer_name, - :old.primary_contact_name, :old.secondary_contact_name, - :old.primary_contact_info, :old.secondary_contact_info, - :old.line1, :old.line2, - :old.city, :old.usps_abbrev, - :old.zip_code, :old.phone, - :old.fax, :old.country_code, - :old.reach, :old.url, - :old.nexus_states, :old.financing_policy, - :old.return_policy, :old.price_guarantee_policy, - :old.delivery_policy, :old.installation_policy, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.retailer_id, old.retailer_name, + old.primary_contact_name, old.secondary_contact_name, + old.primary_contact_info, old.secondary_contact_info, + old.line1, old.line2, + old.city, old.usps_abbrev, + old.zip_code, old.phone, + old.fax, old.country_code, + old.reach, old.url, + old.nexus_states, old.financing_policy, + old.return_policy, old.price_guarantee_policy, + old.delivery_policy, old.installation_policy, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_retailers_audit_tr +before update or delete on ec_retailers +for each row execute procedure ec_retailers_audit_tr (); -create sequence ec_retailer_location_sequence start with 1; +create sequence ec_retailer_location_seq start 1; +create view ec_retailer_location_sequence as select nextval('ec_retailer_location_seq') as nextval; create table ec_retailer_locations ( retailer_location_id integer not null primary key, - retailer_id not null references ec_retailers, + retailer_id integer not null references ec_retailers, location_name varchar(300), primary_contact_name varchar(100), secondary_contact_name varchar(100), @@ -1758,7 +1899,7 @@ -- state -- Jerry -- usps_abbrev reinstated by wtem@olywa.net - usps_abbrev char(2) references states, + usps_abbrev char(2) references states, -- big enough 0to hold zip+4 with dash zip_code varchar(10), phone varchar(30), @@ -1773,8 +1914,8 @@ price_guarantee_policy varchar(4000), delivery_policy varchar(4000), installation_policy varchar(4000), - last_modified date not null, - last_modifying_user not null references users, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); @@ -1786,31 +1927,30 @@ secondary_contact_name varchar(100), primary_contact_info varchar(4000), secondary_contact_info varchar(4000), - line1 varchar(100), - line2 varchar(100), - city varchar(100), - usps_abbrev char(2), - zip_code varchar(10), - phone varchar(30), - fax varchar(30), - country_code char(2), - url varchar(200), + line1 varchar(100), + line2 varchar(100), + city varchar(100), + usps_abbrev char(2), + zip_code varchar(10), + phone varchar(30), + fax varchar(30), + country_code char(2), + url varchar(200), financing_policy varchar(4000), return_policy varchar(4000), price_guarantee_policy varchar(4000), delivery_policy varchar(4000), installation_policy varchar(4000), - last_modified date, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -- Jerry - I removed usps_abbrev and/or state here -create or replace trigger ec_retailer_locations_audit_tr -before update or delete on ec_retailer_locations -for each row +create function ec_retailer_locations_audit_tr () +returns opaque as ' begin insert into ec_retailer_locations_audit ( retailer_location_id, retailer_id, location_name, @@ -1826,57 +1966,61 @@ last_modified, last_modifying_user, modified_ip_address ) values ( - :old.retailer_location_id, - :old.retailer_id, :old.location_name, - :old.primary_contact_name, :old.secondary_contact_name, - :old.primary_contact_info, :old.secondary_contact_info, - :old.line1, :old.line2, - :old.city, :old.usps_abbrev, - :old.zip_code, :old.phone, - :old.fax, :old.country_code, - :old.url, :old.financing_policy, - :old.return_policy, :old.price_guarantee_policy, - :old.delivery_policy, :old.installation_policy, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.retailer_location_id, + old.retailer_id, old.location_name, + old.primary_contact_name, old.secondary_contact_name, + old.primary_contact_info, old.secondary_contact_info, + old.line1, old.line2, + old.city, old.usps_abbrev, + old.zip_code, old.phone, + old.fax, old.country_code, + old.url, old.financing_policy, + old.return_policy, old.price_guarantee_policy, + old.delivery_policy, old.installation_policy, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_retailer_locations_audit_tr +before update or delete on ec_retailer_locations +for each row execute procedure ec_retailer_locations_audit_tr (); -create sequence ec_offer_sequence start with 1; +create sequence ec_offer_seq start 1; +create view ec_offer_sequence as select nextval('ec_offer_seq') as nextval; + create table ec_offers ( offer_id integer not null primary key, - product_id not null references ec_products, - retailer_location_id not null references ec_retailer_locations, + product_id integer not null references ec_products, + retailer_location_id integer not null references ec_retailer_locations, store_sku integer, retailer_premiums varchar(500), - price number not null, - shipping number, - shipping_unavailable_p char(1) check (shipping_unavailable_p in ('t','f')), + price numeric not null, + shipping numeric, + shipping_unavailable_p boolean, -- 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')), - special_offer_p char(1) check (special_offer_p in ('t','f')), + special_offer_p boolean, special_offer_html varchar(500), - offer_begins date not null, - offer_ends date not null, - deleted_p char(1) default 'f' check (deleted_p in ('t','f')), - last_modified date not null, - last_modifying_user not null references users, + offer_begins timestamp not null, + offer_ends timestamp not null, + deleted_p boolean default 'f', + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); -create or replace view ec_offers_current +create view ec_offers_current as select * from ec_offers where deleted_p='f' -and sysdate-offer_begins>=0 -and sysdate-offer_ends<=0; +and now() >= offer_begins +and now() <= offer_ends; create table ec_offers_audit ( @@ -1885,28 +2029,27 @@ retailer_location_id integer, store_sku integer, retailer_premiums varchar(500), - price number, - shipping number, - shipping_unavailable_p char(1) check (shipping_unavailable_p in ('t','f')), + price numeric, + shipping numeric, + shipping_unavailable_p boolean, stock_status char(1) check (stock_status in ('o','q','m','s','i')), - special_offer_p char(1) check (special_offer_p in ('t','f')), + special_offer_p boolean, special_offer_html varchar(500), - offer_begins date, - offer_ends date, - deleted_p char(1) default 'f' check (deleted_p in ('t','f')), - last_modified date, + offer_begins timestamp, + offer_ends timestamp, + deleted_p boolean default 'f', + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), -- This differs from the deleted_p column! -- deleted_p refers to the user request to stop offering -- delete_p indicates the row has been deleted from the main offers table - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -create or replace trigger ec_offers_audit_tr -before update or delete on ec_offers -for each row +create function ec_offers_audit_tr () +returns opaque as ' begin insert into ec_offers_audit ( offer_id, @@ -1920,43 +2063,47 @@ last_modified, last_modifying_user, modified_ip_address ) values ( - :old.offer_id, - :old.product_id, :old.retailer_location_id, - :old.store_sku, :old.retailer_premiums, - :old.price, :old.shipping, - :old.shipping_unavailable_p, :old.stock_status, - :old.special_offer_p, :old.special_offer_html, - :old.offer_begins, :old.offer_ends, - :old.deleted_p, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.offer_id, + old.product_id, old.retailer_location_id, + old.store_sku, old.retailer_premiums, + old.price, old.shipping, + old.shipping_unavailable_p, old.stock_status, + old.special_offer_p, old.special_offer_html, + old.offer_begins, old.offer_ends, + old.deleted_p, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_offers_audit_tr +before update or delete on ec_offers +for each row execute procedure ec_offers_audit_tr (); + -- Gift certificate stuff ---- ------------------------------ -create sequence ec_gift_cert_id_sequence start with 1000000; +create sequence ec_gift_cert_id_seq start 1000000; +create view ec_gift_cert_id_sequence as select nextval('ec_gift_cert_id_seq') as nextval; create table ec_gift_certificates ( gift_certificate_id integer primary key, gift_certificate_state varchar(50) not null, - amount number not null, + amount numeric not null, -- a trigger will update this to f if the -- entire amount is used up (to speed up -- queries) - amount_remaining_p char(1) default 't' check (amount_remaining_p in ('t','f')), - issue_date date, - authorized_date date, - claimed_date date, + amount_remaining_p boolean default 't', + issue_date timestamp, + authorized_date timestamp, + claimed_date timestamp, -- customer service rep who issued it - issued_by references users, + issued_by integer references users, -- customer who purchased it - purchased_by references users, - expires date, - user_id references users, + purchased_by integer references users, + expires timestamp, + user_id integer references users, -- if it's unclaimed, claim_check will be filled in, -- and user_id won't be filled in -- claim check should be unique (one way to do this @@ -1966,11 +2113,11 @@ certificate_to varchar(100), certificate_from varchar(100), recipient_email varchar(100), - voided_date date, - voided_by references users, + voided_date timestamp, + voided_by integer references users, reason_for_void varchar(4000), - last_modified date not null, - last_modifying_user not null references users, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null, check (user_id is not null or claim_check is not null) ); @@ -1987,19 +2134,19 @@ -- and financial transactions. ec_financial_transactions stores the corresponding -- gift_certificate_id. -create or replace view ec_gift_certificates_approved +create view ec_gift_certificates_approved as select * from ec_gift_certificates where gift_certificate_state in ('authorized','authorized_plus_avs','authorized_minus_avs'); -create or replace view ec_gift_certificates_purchased +create view ec_gift_certificates_purchased as select * from ec_gift_certificates where gift_certificate_state in ('authorized_plus_avs','authorized_minus_avs'); -create or replace view ec_gift_certificates_issued +create view ec_gift_certificates_issued as select * from ec_gift_certificates @@ -2010,31 +2157,30 @@ create table ec_gift_certificates_audit ( gift_certificate_id integer, gift_certificate_state varchar(50), - amount number, - issue_date date, - authorized_date date, + amount numeric, + issue_date timestamp, + authorized_date timestamp, issued_by integer, purchased_by integer, - expires date, + expires timestamp, user_id integer, claim_check varchar(50), certificate_message varchar(200), certificate_to varchar(100), certificate_from varchar(100), recipient_email varchar(100), - voided_date date, + voided_date timestamp, voided_by integer, reason_for_void varchar(4000), - last_modified date, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -create or replace trigger ec_gift_certificates_audit_tr -before update or delete on ec_gift_certificates -for each row +create function ec_gift_certificates_audit_tr () +returns opaque as ' begin insert into ec_gift_certificates_audit ( gift_certificate_id, amount, @@ -2045,52 +2191,59 @@ last_modified, last_modifying_user, modified_ip_address ) values ( - :old.gift_certificate_id, :old.amount, - :old.issue_date, :old.authorized_date, :old.issued_by, :old.purchased_by, :old.expires, - :old.user_id, :old.claim_check, :old.certificate_message, - :old.certificate_to, :old.certificate_from, - :old.recipient_email, :old.voided_date, :old.voided_by, :old.reason_for_void, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.gift_certificate_id, old.amount, + old.issue_date, old.authorized_date, old.issued_by, old.purchased_by, old.expires, + old.user_id, old.claim_check, old.certificate_message, + old.certificate_to, old.certificate_from, + old.recipient_email, old.voided_date, old.voided_by, old.reason_for_void, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_gift_certificates_audit_tr +before update or delete on ec_gift_certificates +for each row execute procedure ec_gift_certificates_audit_tr (); + create table ec_gift_certificate_usage ( - gift_certificate_id not null references ec_gift_certificates, - order_id references ec_orders, - amount_used number, - used_date date, - amount_reinstated number, - reinstated_date date + gift_certificate_id integer not null references ec_gift_certificates, + order_id integer references ec_orders, + amount_used numeric, + used_date timestamp, + amount_reinstated numeric, + reinstated_date timestamp ); create index ec_gift_cert_by_id on ec_gift_certificate_usage (gift_certificate_id); --------- customer service -------------------- -create sequence ec_issue_id_sequence; -create sequence ec_action_id_sequence; -create sequence ec_interaction_id_sequence; -create sequence ec_user_ident_id_sequence; +create sequence ec_issue_id_seq; +create view ec_issue_id_sequence as select nextval('ec_issue_id_seq') as nextval; +create sequence ec_action_id_seq; +create view ec_action_id_sequence as select nextval('ec_action_id_seq') as nextval; +create sequence ec_interaction_id_seq; +create view ec_interaction_id_sequence as select nextval('ec_interaction_id_seq') as nextval; +create sequence ec_user_ident_id_seq; +create view ec_user_ident_id_sequence as select nextval('ec_user_ident_id_seq') as nextval; -- this contains the bits of info a cs rep uses to identify -- a user -- often user_id is not known and the customer service rep -- will have to get other info in order to identify the user create table ec_user_identification ( user_identification_id integer not null primary key, - date_added date, - user_id references users, - email varchar(100), - first_names varchar(100), - last_name varchar(100), + date_added timestamp, + user_id integer references users, + email varchar(100), + first_names varchar(100), + last_name varchar(100), -- this is varchar(80) in community-core.sql, so I'll be consistent - postal_code varchar(80), - other_id_info varchar(2000) + postal_code varchar(80), + other_id_info varchar(2000) ); -- should index everything because this all columns may potentially @@ -2103,87 +2256,98 @@ -- puts date_added into ec_user_identification if it's missing -create or replace trigger ec_user_identificate_date_tr -before insert on ec_user_identification -for each row +create function ec_user_identificate_date_tr () +returns opaque as ' begin - IF :new.date_added is null THEN - :new.date_added := sysdate; + IF new.date_added is null THEN + new.date_added := now(); END IF; -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_user_identificate_date_tr +before insert on ec_user_identification +for each row execute procedure ec_user_identificate_date_tr (); + create table ec_customer_serv_interactions ( interaction_id integer not null primary key, - customer_service_rep references users, - user_identification_id not null references ec_user_identification, - interaction_date date, + customer_service_rep integer references users, + user_identification_id integer not null + references ec_user_identification, + interaction_date timestamp, interaction_originator varchar(20) not null, -- e.g. customer, customer-service-rep, automatic - interaction_type varchar(30) not null, -- e.g. email, phone_call + interaction_type varchar(30) not null, -- e.g. email, phone_call -- will be filled in if the customer-originated interaction is -- an email - interaction_headers varchar(4000) + interaction_headers varchar(4000) ); create index ec_csin_by_user_ident_id on ec_customer_serv_interactions(user_identification_id); -create or replace trigger ec_cs_interaction_inserts -before insert on ec_customer_serv_interactions -for each row +-- gilbertw - used the code in OpenACS 3.2.5 as a reference +create function ec_cs_interaction_inserts () +returns opaque as ' begin - IF INSERTING AND :new.interaction_date is null THEN - :new.interaction_date := sysdate; + IF new.interaction_date is null THEN + new.interaction_date := now(); END IF; -end; -/ -show errors + return new; +end;' language 'plpgsql'; -create or replace view ec_customer_service_reps +create trigger ec_cs_interaction_inserts +before insert on ec_customer_serv_interactions +for each row execute procedure ec_cs_interaction_inserts (); + +create view ec_customer_service_reps as select * from cc_users -where user_id in (select customer_service_rep from ec_customer_serv_interactions) +where user_id in (select customer_service_rep + from ec_customer_serv_interactions) or user_id in (select issued_by from ec_gift_certificates_issued); create table ec_customer_service_issues ( issue_id integer not null primary key, - user_identification_id not null references ec_user_identification, + user_identification_id integer not null references ec_user_identification, -- may be null if this issue isn't associated with an order order_id integer references ec_orders, -- may be null if this issue isn't associated with a gift certificate gift_certificate_id integer references ec_gift_certificates, - open_date date not null, - close_date date, + open_date timestamp not null, + close_date timestamp, -- customer service reps who closed the issue - closed_by references users, + closed_by integer references users, -- we never really delete issues - deleted_p char(1) default 'f' check (deleted_p in ('t','f')) + deleted_p boolean default 'f' ); create index ec_csi_by_user_ident_id on ec_customer_service_issues(user_identification_id); create index ec_csi_by_open_date on ec_customer_service_issues(open_date); -- because an issue can have more than one issue_type create table ec_cs_issue_type_map ( - issue_id not null references ec_customer_service_issues, + issue_id integer not null references ec_customer_service_issues, issue_type varchar(40) not null -- e.g. billing, web site ); create index ec_csitm_by_issue_id on ec_cs_issue_type_map(issue_id); create index ec_csitm_by_issue_type on ec_cs_issue_type_map(issue_type); -create or replace trigger ec_cs_issue_inserts -before insert on ec_customer_service_issues -for each row +-- gilbertw - used code OpenACS 3.2.5 as a reference +-- removed INSERTING +create function ec_cs_issue_inserts () +returns opaque as ' begin - IF INSERTING AND :new.open_date is null THEN - :new.open_date := sysdate; + IF new.open_date is null THEN + new.open_date := now(); END IF; -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_cs_issue_inserts +before insert on ec_customer_service_issues +for each row execute procedure ec_cs_issue_inserts (); + create table ec_customer_service_actions ( action_id integer not null primary key, issue_id integer not null references ec_customer_service_issues, @@ -2195,7 +2359,7 @@ create index ec_csa_by_issue on ec_customer_service_actions(issue_id); create table ec_cs_action_info_used_map ( - action_id not null references ec_customer_service_actions, + action_id integer not null references ec_customer_service_actions, info_used varchar(100) not null ); @@ -2205,52 +2369,56 @@ -- this table contains picklist choices for the customer service data -- entry people -create sequence ec_picklist_item_id_sequence; +create sequence ec_picklist_item_id_seq; +create view ec_picklist_item_id_sequence as select nextval('ec_picklist_item_id_seq') as nextval; create table ec_picklist_items ( picklist_item_id integer not null primary key, -- pretty, human-readable picklist_item varchar(100), -- which picklist this item is in picklist_name varchar(100), - sort_key number, - last_modified date not null, - last_modifying_user not null references users, + sort_key numeric, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create table ec_picklist_items_audit ( picklist_item_id integer, picklist_item varchar(100), picklist_name varchar(100), - sort_key number, - last_modified date, + sort_key numeric, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -create or replace trigger ec_picklist_items_audit_tr -before update or delete on ec_picklist_items -for each row +create function ec_picklist_items_audit_tr () +returns opaque as ' begin insert into ec_picklist_items_audit ( picklist_item_id, picklist_item, picklist_name, sort_key, last_modified, last_modifying_user, modified_ip_address ) values ( - :old.picklist_item_id, :old.picklist_item, - :old.picklist_name, :old.sort_key, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.picklist_item_id, old.picklist_item, + old.picklist_name, old.sort_key, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_picklist_items_audit_tr +before update or delete on ec_picklist_items +for each row execute procedure ec_picklist_items_audit_tr (); + -- Canned responses for customer support -create sequence ec_canned_response_id_sequence; +create sequence ec_canned_response_id_seq; +create view ec_canned_response_id_sequence as select nextval('ec_canned_response_id_seq') as nextval; create table ec_canned_responses ( response_id integer not null primary key, @@ -2263,7 +2431,8 @@ -- templates 1-6 are pre-defined (see the insert statements in -- ecommerce-defaults.sql) -- the wording of each can be changed at [ec_url_concat [ec_url] /admin]/email-templates/ -create sequence ec_email_template_id_sequence start with 7; +create sequence ec_email_template_id_seq start 7; +create view ec_email_template_id_sequence as select nextval('ec_email_template_id_seq') as nextval; create table ec_email_templates ( email_template_id integer not null primary key, @@ -2281,8 +2450,8 @@ -- ec_cs_issue_type_map for the issue that will be created -- when the message is sent issue_type_list varchar(100), - last_modified date not null, - last_modifying_user not null references users, + last_modified timestamp not null, + last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); @@ -2294,15 +2463,14 @@ variables varchar(1000), when_sent varchar(1000), issue_type_list varchar(100), - last_modified date, + last_modified timestamp, last_modifying_user integer, modified_ip_address varchar(20), - delete_p char(1) default 'f' check (delete_p in ('t','f')) + delete_p boolean default 'f' ); -create or replace trigger ec_email_templates_audit_tr -before update or delete on ec_email_templates -for each row +create function ec_email_templates_audit_tr () +returns opaque as ' begin insert into ec_email_templates_audit ( email_template_id, title, @@ -2312,21 +2480,24 @@ last_modified, last_modifying_user, modified_ip_address ) values ( - :old.email_template_id, :old.title, - :old.subject, :old.message, - :old.variables, :old.when_sent, - :old.issue_type_list, - :old.last_modified, - :old.last_modifying_user, :old.modified_ip_address + old.email_template_id, old.title, + old.subject, old.message, + old.variables, old.when_sent, + old.issue_type_list, + old.last_modified, + old.last_modifying_user, old.modified_ip_address ); -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger ec_email_templates_audit_tr +before update or delete on ec_email_templates +for each row execute procedure ec_email_templates_audit_tr (); + -- 6 default templates are predefined ecommerce-defaults. -- The templates are -- used in procedures which send out the email, so the template_ids --- shouldn't be changed, although the text can be edited at +-- shouldn t be changed, although the text can be edited at -- [ec_url_concat [ec_url] /admin]/email-templates/ -- -- email_template_id used for @@ -2346,33 +2517,34 @@ -- when the user is signed up for a subsubcategory list, the subcategory_id -- and category_id are also filled in (which makes it easier to refer -- to the mailing list later). --- "cat" stands for "categorization" +-- cat stands for categorization create table ec_cat_mailing_lists ( - user_id not null references users, - category_id references ec_categories, - subcategory_id references ec_subcategories, - subsubcategory_id references ec_subsubcategories + user_id integer not null references users, + category_id integer references ec_categories, + subcategory_id integer references ec_subcategories, + subsubcategory_id integer references ec_subsubcategories ); create index ec_cat_mailing_list_idx on ec_cat_mailing_lists(user_id); create index ec_cat_mailing_list_idx2 on ec_cat_mailing_lists(category_id); create index ec_cat_mailing_list_idx3 on ec_cat_mailing_lists(subcategory_id); create index ec_cat_mailing_list_idx4 on ec_cat_mailing_lists(subsubcategory_id); -create sequence ec_spam_id_sequence; +create sequence ec_spam_id_seq; +create view ec_spam_id_sequence as select nextval('ec_spam_id_seq') as nextval; create table ec_spam_log ( spam_id integer not null primary key, - spam_date date, + spam_date timestamp, spam_text varchar(4000), -- the following are all criteria used in choosing the users to be spammed - mailing_list_category_id references ec_categories, - mailing_list_subcategory_id references ec_subcategories, - mailing_list_subsubcategory_id references ec_subsubcategories, - user_class_id references ec_user_classes, - product_id references ec_products, - last_visit_start_date date, - last_visit_end_date date + mailing_list_category_id integer references ec_categories, + mailing_list_subcategory_id integer references ec_subcategories, + mailing_list_subsubcategory_id integer references ec_subsubcategories, + user_class_id integer references ec_user_classes, + product_id integer references ec_products, + last_visit_start_date timestamp, + last_visit_end_date timestamp ); create index ec_spam_log_by_cat_mail_idx on ec_spam_log (mailing_list_category_id); @@ -2386,52 +2558,53 @@ -- CREDIT CARD STUFF ------------------------ --------------------------------------------- -create sequence ec_transaction_id_sequence start with 4000000; +create sequence ec_transaction_id_seq start 4000000; +create view ec_transaction_id_sequence as select nextval('ec_transaction_id_seq') as nextval; create table ec_financial_transactions ( transaction_id integer not null primary key, -- order_id or gift_certificate_id must be filled in - order_id references ec_orders, - -- The following two rows were added 1999-08-11. They're + order_id integer references ec_orders, + -- The following two rows were added 1999-08-11. They re -- not actually needed by the system right now, but -- they might be useful in the future (I can envision them -- being useful as factory functions are automated). - shipment_id references ec_shipments, - refund_id references ec_refunds, + shipment_id integer references ec_shipments, + refund_id integer references ec_refunds, -- this refers to the purchase of a gift certificate, not the use of one - gift_certificate_id references ec_gift_certificates, + gift_certificate_id integer references ec_gift_certificates, -- creditcard_id is in here even though order_id has a creditcard_id associated with -- it in case a different credit card is used for a refund or a partial shipment. - -- a trigger fills the creditcard_id in if it's not specified - creditcard_id not null references ec_creditcards, - transaction_amount number not null, - -- charge doesn't imply that a charge will actually occur; it's just + -- a trigger fills the creditcard_id in if it s not specified + creditcard_id integer not null references ec_creditcards, + transaction_amount numeric not null, + -- charge doesn t imply that a charge will actually occur; it s just -- an authorization to charge - -- in the case of a refund, there's no such thing as an authorization + -- in the case of a refund, theres no such thing as an authorization -- to refund, so the refund really will occur transaction_type varchar(6) not null check (transaction_type in ('charge','refund')), - -- it starts out null, becomes 't' when we want to capture it, or becomes - -- 'f' it is known that we don't want to capture the transaction (although - -- the 'f' is mainly just for reassurance; we only capture ones with 't') + -- it starts out null, becomes t when we want to capture it, or becomes + -- f it is known that we don't want to capture the transaction (although + -- the f is mainly just for reassurance; we only capture ones with t) -- There's no need to set this for refunds. Refunds are always to be captured. - to_be_captured_p char(1) check (to_be_captured_p in (null,'t','f')), - inserted_date date not null, - authorized_date date, + to_be_captured_p boolean, + inserted_date timestamp not null, + authorized_date timestamp, -- set when to_be_captured_p becomes 't'; used in cron jobs - to_be_captured_date date, - marked_date date, - settled_date date, - refunded_date date, - refund_settled_date date, + to_be_captured_date timestamp, + marked_date timestamp, + settled_date timestamp, + refunded_date timestamp, + refund_settled_date timestamp, -- generated by us talking to Cybercash - disputed_p char(1) check (disputed_p in ('t','f')), + disputed_p boolean, -- date on which we discovered the dispute - dispute_discovery_date date, + dispute_discovery_date timestamp, -- if the consumer's bank got his money back from us forcibly - charged_back_p char(1) check (charged_back_p in ('t','f')), + charged_back_p boolean, -- if the transaction failed, this will keep the cron jobs from continuing -- to retry it - failed_p char(1) default 'f' check (failed_p in ('t','f')), + failed_p boolean default 'f', check (order_id is not null or gift_certificate_id is not null) ); @@ -2441,7 +2614,7 @@ -- reportable transactions: those which have not failed which are to -- be captured (note: refunds are always to be captured) -create or replace view ec_fin_transactions_reportable +create view ec_fin_transactions_reportable as select * from ec_financial_transactions where (transaction_type='charge' and to_be_captured_p='t' and failed_p='f') @@ -2450,28 +2623,31 @@ -- fills creditcard_id into ec_financial_transactions if it's missing -- (using the credit card associated with the order) -create or replace trigger fin_trans_ccard_update_tr -before insert on ec_financial_transactions -for each row +create function fin_trans_ccard_update_tr () +returns opaque as ' declare v_creditcard_id ec_creditcards.creditcard_id%TYPE; begin - IF :new.order_id is not null THEN - select creditcard_id into v_creditcard_id from ec_orders where order_id=:new.order_id; - IF :new.creditcard_id is null THEN - :new.creditcard_id := v_creditcard_id; + IF new.order_id is not null THEN + select into v_creditcard_id creditcard_id + from ec_orders where order_id=new.order_id; + IF new.creditcard_id is null THEN + new.creditcard_id := v_creditcard_id; END IF; END IF; -end; -/ -show errors + return new; +end;' language 'plpgsql'; +create trigger fin_trans_ccard_update_tr +before insert on ec_financial_transactions +for each row execute procedure fin_trans_ccard_update_tr (); + create table ec_cybercash_log ( - transaction_id not null references ec_financial_transactions, + transaction_id integer not null references ec_financial_transactions, -- The types of transactions that will be logged here are -- mauthonly, postauth, return, void, retry, query txn_attempted_type varchar(25), - txn_attempted_time date, + txn_attempted_time timestamp, -- Everything below this line is returned by CyberCash. Note -- that not all columns will have values (for instance, cc_time -- is only returned when doing a query, aux_msg is not returned @@ -2486,7 +2662,7 @@ txn_type varchar(25), -- we take the time returned by CyberCash and chop off the -- sub-second precision - cc_time date, + cc_time timestamp, merch_txn varchar(25), cust_txn varchar(25), origin char(1), @@ -2499,7 +2675,7 @@ avs_code varchar(3), ref_code varchar(25), batch_id varchar(25), - amount number + amount numeric ); -- END CREDIT CARD STUFF ---------------------------- @@ -2508,29 +2684,30 @@ -- this is to record any problems that may have occurred so that the site administrator -- can be alerted on the admin pages -create sequence ec_problem_id_sequence; +create sequence ec_problem_id_seq; +create view ec_problem_id_sequence as select nextval('ec_problem_id_seq') as nextval; create table ec_problems_log ( problem_id integer not null primary key, - problem_date date, + problem_date timestamp, problem_details varchar(4000), -- if it's related to an order - order_id references ec_orders, + order_id integer references ec_orders, -- if it's related to a gift certificate - gift_certificate_id references ec_gift_certificates, - resolved_date date, - resolved_by references users + gift_certificate_id integer references ec_gift_certificates, + resolved_date timestamp, + resolved_by integer references users ); -- keeps track of automatic emails (based on templates) that are sent out create table ec_automatic_email_log ( - user_identification_id not null references ec_user_identification, - email_template_id not null references ec_email_templates, - order_id references ec_orders, - shipment_id references ec_shipments, - gift_certificate_id references ec_gift_certificates, - date_sent date + user_identification_id integer not null references ec_user_identification, + email_template_id integer not null references ec_email_templates, + order_id integer references ec_orders, + shipment_id integer references ec_shipments, + gift_certificate_id integer references ec_gift_certificates, + date_sent timestamp ); create index ec_auto_email_by_usr_id_idx on ec_automatic_email_log (user_identification_id); @@ -2551,297 +2728,301 @@ -- just the price of an order, not shipping, tax, or gift certificates -- this is actually price_charged minus price_refunded -create or replace function ec_total_price (v_order_id IN integer) return number -IS - CURSOR price_cursor IS - SELECT nvl(sum(price_charged),0) - nvl(sum(price_refunded),0) - FROM ec_items - WHERE order_id=v_order_id - and item_state <> 'void'; - - price number; +create function ec_total_price (integer) +returns numeric as ' +DECLARE + v_order_id alias for $1; + price numeric; BEGIN - OPEN price_cursor; - FETCH price_cursor INTO price; - CLOSE price_cursor; + select into price + coalesce(sum(price_charged),0) - coalesce(sum(price_refunded),0) + FROM ec_items + WHERE order_id=v_order_id + and item_state <> ''void''; - RETURN price; -END; -/ -show errors +END;' language 'plpgsql'; -- just the shipping of an order, not price, tax, or gift certificates -- this is actually total shipping minus total shipping refunded -create or replace function ec_total_shipping (v_order_id IN integer) return number -IS - CURSOR order_shipping_cursor IS - SELECT nvl(shipping_charged,0) - nvl(shipping_refunded,0) - FROM ec_orders - WHERE order_id=v_order_id; - - CURSOR item_shipping_cursor IS - SELECT nvl(sum(shipping_charged),0) - nvl(sum(shipping_refunded),0) - FROM ec_items - WHERE order_id=v_order_id - and item_state <> 'void'; - - order_shipping number; - item_shipping number; +create function ec_total_shipping (integer) +returns numeric as ' +DECLARE + v_order_id alias for $1; + order_shipping numeric; + item_shipping numeric; BEGIN - OPEN order_shipping_cursor; - FETCH order_shipping_cursor INTO order_shipping; - CLOSE order_shipping_cursor; + select into order_shipping + coalesce(shipping_charged,0) - coalesce(shipping_refunded,0) + FROM ec_orders + WHERE order_id=v_order_id; - OPEN item_shipping_cursor; - FETCH item_shipping_cursor INTO item_shipping; - CLOSE item_shipping_cursor; + select into item_shipping + coalesce(sum(shipping_charged),0) - coalesce(sum(shipping_refunded),0) + FROM ec_items + WHERE order_id=v_order_id + and item_state <> ''void''; - RETURN order_shipping + item_shipping; -END; -/ -show errors + return order_shipping + item_shipping; +END;' language 'plpgsql'; -- OK -- just the tax of an order, not price, shipping, or gift certificates -- this is tax minus tax refunded -create or replace function ec_total_tax (v_order_id IN integer) return number -IS - CURSOR order_tax_cursor IS - SELECT nvl(shipping_tax_charged,0) - nvl(shipping_tax_refunded,0) - FROM ec_orders - WHERE order_id=v_order_id; - - CURSOR item_price_tax_cursor IS - SELECT nvl(sum(price_tax_charged),0) - nvl(sum(price_tax_refunded),0) - FROM ec_items - WHERE order_id=v_order_id - and item_state <> 'void'; - - CURSOR item_shipping_tax_cursor IS - SELECT nvl(sum(shipping_tax_charged),0) - nvl(sum(shipping_tax_refunded),0) - FROM ec_items - WHERE order_id=v_order_id; - - order_tax number; - item_price_tax number; - item_shipping_tax number; +create function ec_total_tax (integer) +returns numeric as ' +DECLARE + v_order_id alias for $1; + order_tax numeric; + item_price_tax numeric; + item_shipping_tax numeric; BEGIN - OPEN order_tax_cursor; - FETCH order_tax_cursor INTO order_tax; - CLOSE order_tax_cursor; + select into order_tax + coalesce(shipping_tax_charged,0) - coalesce(shipping_tax_refunded,0) + FROM ec_orders + WHERE order_id=v_order_id; - OPEN item_price_tax_cursor; - FETCH item_price_tax_cursor INTO item_price_tax; - CLOSE item_price_tax_cursor; + select into item_price_tax + coalesce(sum(price_tax_charged),0) - coalesce(sum(price_tax_refunded),0) + FROM ec_items + WHERE order_id=v_order_id + and item_state <> ''void''; - OPEN item_shipping_tax_cursor; - FETCH item_shipping_tax_cursor INTO item_shipping_tax; - CLOSE item_shipping_tax_cursor; + select into item_shipping_tax + coalesce(sum(shipping_tax_charged),0) - coalesce(sum(shipping_tax_refunded),0) + FROM ec_items + WHERE order_id=v_order_id; - RETURN order_tax + item_price_tax + item_shipping_tax; -END; -/ -show errors + return order_tax + item_price_tax + item_shipping_tax; +END;' language 'plpgsql'; -- OK -- just the price of a shipment, not shipping, tax, or gift certificates -- this is the price charged minus the price refunded of the shipment -create or replace function ec_shipment_price (v_shipment_id IN integer) return number -IS - shipment_price number; +create function ec_shipment_price (integer) +returns numeric as ' +DECLARE + v_shipment_id alias for $1; + shipment_price numeric; BEGIN - SELECT nvl(sum(price_charged),0) - nvl(sum(price_refunded),0) into shipment_price + SELECT into shipment_price coalesce(sum(price_charged),0) - coalesce(sum(price_refunded),0) FROM ec_items WHERE shipment_id=v_shipment_id - and item_state <> 'void'; + and item_state <> ''void''; RETURN shipment_price; -END; -/ -show errors +END;' language 'plpgsql'; -- OK -- just the shipping charges of a shipment, not price, tax, or gift certificates -- note: the base shipping charge is always applied to the first shipment in an order. -- this is the shipping charged minus the shipping refunded -create or replace function ec_shipment_shipping (v_shipment_id IN integer) return number -IS - item_shipping number; - base_shipping number; +create function ec_shipment_shipping (integer) +returns numeric as ' +DECLARE + v_shipment_id alias for $1; + item_shipping numeric; + base_shipping numeric; v_order_id ec_orders.order_id%TYPE; min_shipment_id ec_shipments.shipment_id%TYPE; BEGIN - SELECT order_id into v_order_id FROM ec_shipments where shipment_id=v_shipment_id; - SELECT min(shipment_id) into min_shipment_id FROM ec_shipments where order_id=v_order_id; + SELECT into v_order_id order_id + FROM ec_shipments where shipment_id=v_shipment_id; + SELECT into min_shipment_id min(shipment_id) + FROM ec_shipments where order_id=v_order_id; IF v_shipment_id=min_shipment_id THEN - SELECT nvl(shipping_charged,0) - nvl(shipping_refunded,0) into base_shipping FROM ec_orders where order_id=v_order_id; + SELECT into base_shipping + coalesce(shipping_charged,0) - coalesce(shipping_refunded,0) + FROM ec_orders where order_id=v_order_id; ELSE base_shipping := 0; END IF; - SELECT nvl(sum(shipping_charged),0) - nvl(sum(shipping_refunded),0) into item_shipping FROM ec_items where shipment_id=v_shipment_id and item_state <> 'void'; + SELECT into item_shipping + coalesce(sum(shipping_charged),0) - coalesce(sum(shipping_refunded),0) + FROM ec_items where shipment_id=v_shipment_id + and item_state <> ''void''; RETURN item_shipping + base_shipping; -END; -/ -show errors +END;' language 'plpgsql'; -- OK -- just the tax of a shipment, not price, shipping, or gift certificates -- note: the base shipping tax charge is always applied to the first shipment in an order. -- this is the tax charged minus the tax refunded -create or replace function ec_shipment_tax (v_shipment_id IN integer) return number -IS - item_price_tax number; - item_shipping_tax number; - base_shipping_tax number; +create function ec_shipment_tax (integer) +returns numeric as ' +DECLARE + v_shipment_id alias for $1; + item_price_tax numeric; + item_shipping_tax numeric; + base_shipping_tax numeric; v_order_id ec_orders.order_id%TYPE; min_shipment_id ec_shipments.shipment_id%TYPE; BEGIN - SELECT order_id into v_order_id FROM ec_shipments where shipment_id=v_shipment_id; - SELECT min(shipment_id) into min_shipment_id FROM ec_shipments where order_id=v_order_id; + SELECT into v_order_id order_id + FROM ec_shipments where shipment_id=v_shipment_id; + SELECT into min_shipment_id min(shipment_id) + FROM ec_shipments where order_id=v_order_id; IF v_shipment_id=min_shipment_id THEN - SELECT nvl(shipping_tax_charged,0) - nvl(shipping_tax_refunded,0) into base_shipping_tax FROM ec_orders where order_id=v_order_id; + SELECT into base_shipping_tax + coalesce(shipping_tax_charged,0) - coalesce(shipping_tax_refunded,0) + FROM ec_orders where order_id=v_order_id; ELSE base_shipping_tax := 0; END IF; - SELECT nvl(sum(price_tax_charged),0) - nvl(sum(price_tax_refunded),0) into item_price_tax FROM ec_items where shipment_id=v_shipment_id and item_state <> 'void'; - SELECT nvl(sum(shipping_tax_charged),0) - nvl(sum(shipping_tax_refunded),0) into item_shipping_tax FROM ec_items where shipment_id=v_shipment_id and item_state <> 'void'; + SELECT into item_price_tax + coalesce(sum(price_tax_charged),0) - coalesce(sum(price_tax_refunded),0) + FROM ec_items where shipment_id=v_shipment_id and item_state <> ''void''; + SELECT into item_shipping_tax + coalesce(sum(shipping_tax_charged),0) - coalesce(sum(shipping_tax_refunded),0) + FROM ec_items where shipment_id=v_shipment_id and item_state <> ''void''; RETURN item_price_tax + item_shipping_tax + base_shipping_tax; -END; -/ -show errors +END;' language 'plpgsql'; -- OK -- the gift certificate amount used on one order -create or replace function ec_order_gift_cert_amount (v_order_id IN integer) return number -IS - CURSOR gift_cert_amount_cursor IS - SELECT nvl(sum(amount_used),0) - nvl(sum(amount_reinstated),0) - FROM ec_gift_certificate_usage - WHERE order_id=v_order_id; - - gift_cert_amount number; +create function ec_order_gift_cert_amount (integer) +returns numeric as ' +DECLARE + v_order_id alias for $1; + gift_cert_amount numeric; BEGIN - OPEN gift_cert_amount_cursor; - FETCH gift_cert_amount_cursor INTO gift_cert_amount; - CLOSE gift_cert_amount_cursor; + select into gift_cert_amount + coalesce(sum(amount_used),0) - coalesce(sum(amount_reinstated),0) + FROM ec_gift_certificate_usage + WHERE order_id=v_order_id; return gift_cert_amount; -END; -/ -show errors +END;' language 'plpgsql'; - -- OK -- tells how much of the gift certificate amount used on the order is to be applied -- to a shipment (it's applied chronologically) -create or replace function ec_shipment_gift_certificate (v_shipment_id IN integer) return number -IS +create function ec_shipment_gift_certificate (integer) +returns numeric as ' +DECLARE + v_shipment_id alias for $1; v_order_id ec_orders.order_id%TYPE; - gift_cert_amount number; - past_ship_amount number; + gift_cert_amount numeric; + past_ship_amount numeric; BEGIN - SELECT order_id into v_order_id FROM ec_shipments WHERE shipment_id=v_shipment_id; + SELECT into v_order_id order_id + FROM ec_shipments WHERE shipment_id=v_shipment_id; gift_cert_amount := ec_order_gift_cert_amount(v_order_id); - SELECT nvl(sum(ec_shipment_price(shipment_id)) + sum(ec_shipment_shipping(shipment_id))+sum(ec_shipment_tax(shipment_id)),0) into past_ship_amount FROM ec_shipments WHERE order_id = v_order_id and shipment_id <> v_shipment_id; + SELECT into past_ship_amount + coalesce(sum(ec_shipment_price(shipment_id)) + sum(ec_shipment_shipping(shipment_id))+sum(ec_shipment_tax(shipment_id)),0) + FROM ec_shipments WHERE order_id = v_order_id and shipment_id <> v_shipment_id; IF past_ship_amount > gift_cert_amount THEN return 0; ELSE - return least(gift_cert_amount - past_ship_amount, nvl(ec_shipment_price(v_shipment_id) + ec_shipment_shipping(v_shipment_id) + ec_shipment_tax(v_shipment_id),0)); + return least(gift_cert_amount - past_ship_amount, coalesce(ec_shipment_price(v_shipment_id) + ec_shipment_shipping(v_shipment_id) + ec_shipment_tax(v_shipment_id),0)); END IF; -END; -/ -show errors +END;' language 'plpgsql'; +--CHECK OUTER JOIN BELOW + -- OK -- this can be used for either an item or order -- given price and shipping, computes tax that needs to be charged (or refunded) -- order_id is an argument so that we can get the usps_abbrev (and thus the tax rate), -create or replace function ec_tax (v_price IN number, v_shipping IN number, v_order_id IN integer) return number -IS +create function ec_tax (numeric, numeric, integer) +returns numeric as ' +DECLARE + v_price alias for $1; + v_shipping alias for $2; + v_order_id alias for $3; taxes ec_sales_tax_by_state%ROWTYPE; tax_exempt_p ec_orders.tax_exempt_p%TYPE; BEGIN - SELECT tax_exempt_p INTO tax_exempt_p + SELECT into tax_exempt_p tax_exempt_p FROM ec_orders WHERE order_id = v_order_id; - IF tax_exempt_p = 't' THEN + IF tax_exempt_p = ''t'' THEN return 0; END IF; - SELECT t.* into taxes - FROM ec_orders o, ec_addresses a, ec_sales_tax_by_state t - WHERE o.shipping_address=a.address_id - AND a.usps_abbrev=t.usps_abbrev(+) - AND o.order_id=v_order_id; + --SELECT t.* into taxes + --FROM ec_orders o, ec_addresses a, ec_sales_tax_by_state t + --WHERE o.shipping_address=a.address_id + --AND a.usps_abbrev=t.usps_abbrev(+) + --AND o.order_id=v_order_id; - IF nvl(taxes.shipping_p,'f') = 'f' THEN - return nvl(taxes.tax_rate,0) * v_price; + SELECT into taxes t.* + FROM ec_orders o + JOIN + ec_addresses a on (o.shipping_address=a.address_id) + LEFT JOIN + ec_sales_tax_by_state t using (usps_abbrev) + WHERE o.order_id=v_order_id; + + + IF coalesce(taxes.shipping_p,''f'') = ''f'' THEN + return coalesce(taxes.tax_rate,0) * v_price; ELSE - return nvl(taxes.tax_rate,0) * (v_price + v_shipping); + return coalesce(taxes.tax_rate,0) * (v_price + v_shipping); END IF; -END; -/ -show errors +END;' language 'plpgsql'; -- OK -- total order cost (price + shipping + tax - gift certificate) -- this should be equal to the amount that the order was authorized for -- (if no refunds have been made) -create or replace function ec_order_cost (v_order_id IN integer) return number -IS - v_price number; - v_shipping number; - v_tax number; - v_certificate number; +create function ec_order_cost (integer) +returns numeric as ' +DECLARE + v_order_id alias for $1; + v_price numeric; + v_shipping numeric; + v_tax numeric; + v_certificate numeric; BEGIN v_price := ec_total_price(v_order_id); v_shipping := ec_total_shipping(v_order_id); v_tax := ec_total_tax(v_order_id); v_certificate := ec_order_gift_cert_amount(v_order_id); return v_price + v_shipping + v_tax - v_certificate; -END; -/ -show errors +END;' language 'plpgsql'; -- OK -- total shipment cost (price + shipping + tax - gift certificate) -create or replace function ec_shipment_cost (v_shipment_id IN integer) return number -IS - v_price number; - v_shipping number; - v_certificate number; - v_tax number; +create function ec_shipment_cost (integer) +returns numeric as ' +DECLARE + v_shipment_id alias for $1; + v_price numeric; + v_shipping numeric; + v_certificate numeric; + v_tax numeric; BEGIN v_price := ec_shipment_price(v_shipment_id); v_shipping := ec_shipment_shipping(v_shipment_id); v_tax := ec_shipment_tax(v_shipment_id); v_certificate := ec_shipment_gift_certificate(v_shipment_id); return v_price + v_shipping - v_certificate + v_tax; -END; -/ -show errors +END;' language 'plpgsql'; -- OK -- total amount refunded on an order so far -create or replace function ec_total_refund (v_order_id IN integer) return number -IS - v_order_refund number; - v_items_refund number; +create function ec_total_refund (integer) +returns numeric as ' +DECLARE + v_order_id alias for $1; + v_order_refund numeric; + v_items_refund numeric; BEGIN - select nvl(shipping_refunded,0) + nvl(shipping_tax_refunded,0) into v_order_refund from ec_orders where order_id=v_order_id; - select sum(nvl(price_refunded,0)) + sum(nvl(shipping_refunded,0)) + sum(nvl(price_tax_refunded,0)) + sum(nvl(shipping_tax_refunded,0)) into v_items_refund from ec_items where order_id=v_order_id; + select into v_order_refund + coalesce(shipping_refunded,0) + coalesce(shipping_tax_refunded,0) + from ec_orders where order_id=v_order_id; + select into v_items_refund + sum(coalesce(price_refunded,0)) + sum(coalesce(shipping_refunded,0)) + sum(coalesce(price_tax_refunded,0)) + sum(coalesce(shipping_tax_refunded,0)) from ec_items where order_id=v_order_id; return v_order_refund + v_items_refund; -END; -/ -show errors +END;' language 'plpgsql'; -------------- end price calculations ----------------- ------------------------------------------------------- @@ -2850,145 +3031,115 @@ ----------- gift certificate procedures --------------- ------------------------------------------------------- -CREATE global temporary TABLE ec_gift_cert_usage_ids ( - gift_certificate_id INTEGER NOT NULL -); - -create or replace trigger ec_gift_cert_amount_remains_tr - before update OF amount_used, amount_reinstated on ec_gift_certificate_usage - FOR each row -BEGIN - INSERT INTO ec_gift_cert_usage_ids (gift_certificate_id) - VALUES (:NEW.gift_certificate_id); -END; -/ -show errors - -CREATE OR replace trigger ec_cert_amount_remains_tr_2 - after UPDATE OF amount_used, amount_reinstated ON ec_gift_certificate_usage +-- gilbertw - removed global temporary table ec_gift_cert_usage_ids +-- copied code from openacs 3.2.5 + +-- +-- BMA (PGsql port) +-- Postgres is way cooler than Oracle with MVCC, which allows it +-- to have triggers updating the same table. Thus, we get rid of this +-- trio crap and we have a simple trigger for everything. + +create function trig_ec_cert_amount_remains() +returns opaque +as ' DECLARE - bal_amount_used number; - original_amount number; - gift_cert_amount_remains_p CHAR; - cursor gift_certificate_cursor is SELECT DISTINCT gift_certificate_id - FROM ec_gift_cert_usage_ids; + bal_amount_used numeric; + original_amount numeric; BEGIN - FOR gift_certificate_rec IN gift_certificate_cursor LOOP - SELECT nvl(sum(amount_used), 0) - nvl(sum(amount_reinstated),0) - INTO bal_amount_used - FROM ec_gift_certificate_usage - WHERE gift_certificate_id=gift_certificate_rec.gift_certificate_id; - - SELECT amount - INTO original_amount - FROM ec_gift_certificates - WHERE gift_certificate_id=gift_certificate_rec.gift_certificate_id; - - IF bal_amount_used >= original_amount THEN - gift_cert_amount_remains_p := 'f'; - ELSE - gift_cert_amount_remains_p := 't'; - END IF; - - - UPDATE ec_gift_certificates - SET amount_remaining_p = gift_cert_amount_remains_p + select amount into original_amount + from ec_gift_certificates where gift_certificate_id= NEW.certificate_id for update; + + select coalesce(sum(amount_used), 0) - coalesce(sum(amount_reinstated), 0) + into bal_amount_used + from ec_gift_certificate_usage + where gift_certificate_id= NEW.gift_certificate_id; + + UPDATE ec_gift_certificates + SET amount_remaining_p = case when amount > bal_amount_used then ''t'' else ''f'' end WHERE gift_certificate_id = gift_certificate_rec.gift_certificate_id; - END LOOP; - DELETE from ec_gift_cert_usage_ids; + return new; END; -/ -show errors +' language 'plpgsql'; +create trigger ec_cert_amount_remains +before update on ec_gift_certificate_usage +for each row +execute procedure trig_ec_cert_amount_remains(); + -- OK -- calculates how much a user has in their gift certificate account -create or replace function ec_gift_certificate_balance (v_user_id IN integer) return number -IS - -- these only look at unexpired gift certificates where amount_remaining_p is 't', - -- hence the word 'subset' in their names - - CURSOR original_amount_subset_cursor IS - SELECT nvl(sum(amount),0) - FROM ec_gift_certificates_approved - WHERE user_id=v_user_id - AND amount_remaining_p='t' - AND expires-sysdate > 0; - - CURSOR amount_used_subset_cursor IS - SELECT nvl(sum(u.amount_used),0) - nvl(sum(u.amount_reinstated),0) as total_amount_used - FROM ec_gift_certificates_approved c, ec_gift_certificate_usage u - WHERE c.gift_certificate_id=u.gift_certificate_id - AND c.user_id=v_user_id - AND c.amount_remaining_p='t' - AND c.expires-sysdate > 0; - - original_amount number; - total_amount_used number; +create function ec_gift_certificate_balance (integer) +returns numeric as ' +DECLARE + v_user_id alias for $1; + original_amount numeric; + total_amount_used numeric; + -- these only look at unexpired gift certificates + -- where amount_remaining_p is t, + -- hence the word subset in their names BEGIN - OPEN original_amount_subset_cursor; - FETCH original_amount_subset_cursor INTO original_amount; - CLOSE original_amount_subset_cursor; + SELECT coalesce(sum(amount),0) + into original_amount + FROM ec_gift_certificates_approved + WHERE user_id=v_user_id + AND amount_remaining_p=''t'' + AND expires > now(); - OPEN amount_used_subset_cursor; - FETCH amount_used_subset_cursor INTO total_amount_used; - CLOSE amount_used_subset_cursor; + SELECT coalesce(sum(u.amount_used),0) - + coalesce(sum(u.amount_reinstated),0) + into total_amount_used + FROM ec_gift_certificates_approved c, ec_gift_certificate_usage u + WHERE c.gift_certificate_id=u.gift_certificate_id + AND c.user_id=v_user_id + AND c.amount_remaining_p=''t'' + AND c.expires > now(); RETURN original_amount - total_amount_used; -END; -/ -show errors +END;' language 'plpgsql'; - -- OK -- Returns price + shipping + tax - gift certificate amount applied -- for one order. -- Requirement: ec_orders.shipping_charged, ec_orders.shipping_tax_charged, -- ec_items.price_charged, ec_items.shipping_charged, ec_items.price_tax_chaged, -- and ec_items.shipping_tax_charged should already be filled in. -create or replace function ec_order_amount_owed (v_order_id IN integer) return number -IS - pre_gc_amount_owed number; - gc_amount number; +create function ec_order_amount_owed (integer) +returns numeric as ' +DECLARE + v_order_id alias for $1; + pre_gc_amount_owed numeric; + gc_amount numeric; BEGIN pre_gc_amount_owed := ec_total_price(v_order_id) + ec_total_shipping(v_order_id) + ec_total_tax(v_order_id); gc_amount := ec_order_gift_cert_amount(v_order_id); RETURN pre_gc_amount_owed - gc_amount; -END; -/ -show errors +END;' language 'plpgsql'; -- OK -- the amount remaining in an individual gift certificate -create or replace function gift_certificate_amount_left (v_gift_certificate_id IN integer) return number -IS - CURSOR amount_used_cursor IS - SELECT nvl(sum(amount_used),0) - nvl(sum(amount_reinstated),0) - FROM ec_gift_certificate_usage - WHERE gift_certificate_id = v_gift_certificate_id; - - CURSOR original_amount_cursor IS - SELECT amount - FROM ec_gift_certificates - WHERE gift_certificate_id = v_gift_certificate_id; - +create function gift_certificate_amount_left (integer) +returns numeric as ' +DECLARE + v_gift_certificate_id alias for $1; original_amount number; amount_used number; BEGIN - OPEN amount_used_cursor; - FETCH amount_used_cursor INTO amount_used; - CLOSE amount_used_cursor; + SELECT coalesce(sum(amount_used),0) - coalesce(sum(amount_reinstated),0) + into amount_used + FROM ec_gift_certificate_usage + WHERE gift_certificate_id = v_gift_certificate_id; - OPEN original_amount_cursor; - FETCH original_amount_cursor INTO original_amount; - CLOSE original_amount_cursor; + SELECT amount + into original_amount + FROM ec_gift_certificates + WHERE gift_certificate_id = v_gift_certificate_id; RETURN original_amount - amount_used; -END; -/ -show errors +END;' language 'plpgsql'; -- I DON'T USE THIS PROCEDURE ANYMORE BECAUSE THERE'S A MORE -- FAULT-TOLERANT TCL VERSION @@ -3000,58 +3151,59 @@ -- ec_items.price_charged, ec_items.shipping_charged, ec_items.price_tax_charged, -- ec_items.shipping_tax_charged should already be filled in. -- Call this within a transaction. -create or replace procedure ec_apply_gift_cert_balance (v_order_id IN integer, v_user_id IN integer) -IS - CURSOR gift_certificate_to_use_cursor IS - SELECT * - FROM ec_gift_certificates_approved - WHERE user_id = v_user_id - AND (expires is null or sysdate - expires < 0) - AND amount_remaining_p = 't' - ORDER BY expires; - amount_owed number; - gift_certificate_balance number; - certificate ec_gift_certificates_approved%ROWTYPE; -BEGIN - gift_certificate_balance := ec_gift_certificate_balance(v_user_id); - amount_owed := ec_order_amount_owed(v_order_id); +--create or replace procedure ec_apply_gift_cert_balance (v_order_id IN integer, v_user_id IN integer) +--IS +-- CURSOR gift_certificate_to_use_cursor IS +-- SELECT * +-- FROM ec_gift_certificates_approved +-- WHERE user_id = v_user_id +-- AND (expires is null or now() < expires ) +-- AND amount_remaining_p = ''t'' +-- ORDER BY expires; +-- amount_owed number; +-- gift_certificate_balance number; +-- certificate ec_gift_certificates_approved%ROWTYPE; +--BEGIN +-- gift_certificate_balance := ec_gift_certificate_balance(v_user_id); +-- amount_owed := ec_order_amount_owed(v_order_id); +-- +-- OPEN gift_certificate_to_use_cursor; +-- WHILE amount_owed > 0 and gift_certificate_balance > 0 +-- LOOP +-- FETCH gift_certificate_to_use_cursor INTO certificate; +-- +-- INSERT into ec_gift_certificate_usage +-- (gift_certificate_id, order_id, amount_used, used_date) +-- VALUES +-- (certificate.gift_certificate_id, v_order_id, least(gift_certificate_amount_left(certificate.gift_certificate_id), amount_owed), now()); +-- +-- gift_certificate_balance := ec_gift_certificate_balance(v_user_id); +-- amount_owed := ec_order_amount_owed(v_order_id); +-- END LOOP; +-- CLOSE gift_certificate_to_use_cursor; +--END ec_apply_gift_cert_balance; +--/ +--show errors - OPEN gift_certificate_to_use_cursor; - WHILE amount_owed > 0 and gift_certificate_balance > 0 - LOOP - FETCH gift_certificate_to_use_cursor INTO certificate; - - INSERT into ec_gift_certificate_usage - (gift_certificate_id, order_id, amount_used, used_date) - VALUES - (certificate.gift_certificate_id, v_order_id, least(gift_certificate_amount_left(certificate.gift_certificate_id), amount_owed), sysdate); - - gift_certificate_balance := ec_gift_certificate_balance(v_user_id); - amount_owed := ec_order_amount_owed(v_order_id); - END LOOP; - CLOSE gift_certificate_to_use_cursor; -END ec_apply_gift_cert_balance; -/ -show errors - - -- OK -- reinstates all gift certificates used on an order (as opposed to -- individual items), e.g. if the order was voided or an auth failed -create or replace procedure ec_reinst_gift_cert_on_order (v_order_id IN integer) -IS +create function ec_reinst_gift_cert_on_order (integer) +returns integer as ' +DECLARE + v_order_id alias for $1; BEGIN insert into ec_gift_certificate_usage (gift_certificate_id, order_id, amount_reinstated, reinstated_date) - select gift_certificate_id, v_order_id, nvl(sum(amount_used),0)-nvl(sum(amount_reinstated),0), sysdate + select gift_certificate_id, v_order_id, coalesce(sum(amount_used),0)-coalesce(sum(amount_reinstated),0), now() from ec_gift_certificate_usage where order_id=v_order_id group by gift_certificate_id; -END; -/ -show errors + return 0; +END;' language 'plpgsql'; + -- Given an amount to refund to an order, this tells -- you how much of that is to be refunded in cash (as opposed to -- reinstated in gift certificates). Then you know you have to @@ -3062,15 +3214,18 @@ -- Call this before inserting the amounts that are being refunded -- into the database. -create or replace function ec_cash_amount_to_refund (v_amount IN number, v_order_id IN integer) return number -IS - amount_paid number; - items_amount_paid number; - order_amount_paid number; - amount_refunded number; - curr_gc_amount number; - max_cash_refundable number; - cash_to_refund number; +create function ec_cash_amount_to_refund (numeric, integer) +returns numeric as ' +DECLARE + v_amount alias for $1; + v_order_id alias for $2; + amount_paid numeric; + items_amount_paid numeric; + order_amount_paid numeric; + amount_refunded numeric; + curr_gc_amount numeric; + max_cash_refundable numeric; + cash_to_refund numeric; BEGIN -- the maximum amount of cash refundable is equal to -- the amount paid (in cash + certificates) for shipped items only (since @@ -3079,9 +3234,9 @@ -- minus the current gift certificate amount applied to this order -- or 0 if the result is negative - select sum(nvl(price_charged,0)) + sum(nvl(shipping_charged,0)) + sum(nvl(price_tax_charged,0)) + sum(nvl(shipping_tax_charged,0)) into items_amount_paid from ec_items where order_id=v_order_id and shipment_id is not null and item_state <> 'void'; + select sum(coalesce(price_charged,0)) + sum(coalesce(shipping_charged,0)) + sum(coalesce(price_tax_charged,0)) + sum(coalesce(shipping_tax_charged,0)) into items_amount_paid from ec_items where order_id=v_order_id and shipment_id is not null and item_state <> ''void''; - select nvl(shipping_charged,0) + nvl(shipping_tax_charged,0) into order_amount_paid from ec_orders where order_id=v_order_id; + select coalesce(shipping_charged,0) + coalesce(shipping_tax_charged,0) into order_amount_paid from ec_orders where order_id=v_order_id; amount_paid := items_amount_paid + order_amount_paid; amount_refunded := ec_total_refund(v_order_id); @@ -3091,94 +3246,99 @@ cash_to_refund := least(max_cash_refundable, v_amount); RETURN cash_to_refund; -END; -/ -show errors; +END;' language 'plpgsql'; -- The amount of a given gift certificate used on a given order. -- This is a helper function for ec_gift_cert_unshipped_amount. -create or replace function ec_one_gift_cert_on_one_order (v_gift_certificate_id IN integer, v_order_id IN integer) return number -IS - bal_amount_used number; +create function ec_one_gift_cert_on_one_order (integer, integer) +returns numeric as ' +DECLARE + v_gift_certificate_id alias for $1; + v_order_id alias for $2; + bal_amount_used numeric; BEGIN - select nvl(sum(amount_used),0)-nvl(sum(amount_reinstated),0) into bal_amount_used + select coalesce(sum(amount_used),0)-coalesce(sum(amount_reinstated),0) into bal_amount_used from ec_gift_certificate_usage where order_id=v_order_id and gift_certificate_id=v_gift_certificate_id; RETURN bal_amount_used; -END ec_one_gift_cert_on_one_order; -/ -show errors +END;' language 'plpgsql'; -- The amount of all gift certificates used on a given order that -- expire before* a given gift certificate (*in the event that two -- expire at precisely the same time, the one with a higher -- gift_certificate_id is defined to expire last). -- This is a helper function for ec_gift_cert_unshipped_amount. -create or replace function ec_earlier_certs_on_one_order (v_gift_certificate_id IN integer, v_order_id IN integer) return number -IS - bal_amount_used number; +create function ec_earlier_certs_on_one_order (integer, integer) +returns numeric as ' +DECLARE + v_gift_certificate_id alias for $1; + v_order_id alias for $2; + bal_amount_used numeric; BEGIN - select nvl(sum(u.amount_used),0)-nvl(sum(u.amount_reinstated),0) into bal_amount_used + select coalesce(sum(u.amount_used),0)-coalesce(sum(u.amount_reinstated),0) into bal_amount_used from ec_gift_certificate_usage u, ec_gift_certificates g, ec_gift_certificates g2 where u.gift_certificate_id=g.gift_certificate_id and g2.gift_certificate_id=v_gift_certificate_id and u.order_id=v_order_id and (g.expires < g2.expires or (g.expires = g2.expires and g.gift_certificate_id < g2.gift_certificate_id)); return bal_amount_used; -END; -/ -show errors +END;' language 'plpgsql'; -- The amount of a gift certificate that is applied to the upshipped portion of an order. -- This is a helper function for ec_gift_cert_unshipped_amount. -create or replace function ec_cert_unshipped_one_order (v_gift_certificate_id IN integer, v_order_id IN integer) return number -IS - total_shipment_cost number; - earlier_certs number; - total_tied_amount number; +create function ec_cert_unshipped_one_order (integer, integer) +returns numeric as ' +DECLARE + v_gift_certificate_id alias for $1; + v_order_id alias for $2; + total_shipment_cost numeric; + earlier_certs numeric; + total_tied_amount numeric; BEGIN - select nvl(sum(nvl(ec_shipment_price(shipment_id),0) + nvl(ec_shipment_shipping(shipment_id),0) + nvl(ec_shipment_tax(shipment_id),0)),0) into total_shipment_cost + select coalesce(sum(coalesce(ec_shipment_price(shipment_id),0) + coalesce(ec_shipment_shipping(shipment_id),0) + coalesce(ec_shipment_tax(shipment_id),0)),0) into total_shipment_cost from ec_shipments where order_id=v_order_id; earlier_certs := ec_earlier_certs_on_one_order(v_gift_certificate_id, v_order_id); IF total_shipment_cost <= earlier_certs THEN total_tied_amount := ec_one_gift_cert_on_one_order(v_gift_certificate_id, v_order_id); - ELSIF total_shipment_cost > earlier_certs + ec_one_gift_cert_on_one_order(v_gift_certificate_id, v_order_id) THEN - total_tied_amount := 0; ELSE + IF total_shipment_cost > earlier_certs + ec_one_gift_cert_on_one_order(v_gift_certificate_id, v_order_id) THEN + total_tied_amount := 0; + ELSE total_tied_amount := ec_one_gift_cert_on_one_order(v_gift_certificate_id, v_order_id) - (total_shipment_cost - earlier_certs); + END IF; END IF; RETURN total_tied_amount; -END; -/ -show errors +END;' language 'plpgsql'; +--HERE + -- Returns the amount of a gift certificate that is applied to the unshipped portions of orders --- (this amount is still considered "outstanding" since revenue, and thus gift certificate usage, --- isn't recognized until the items ship). -create or replace function ec_gift_cert_unshipped_amount (v_gift_certificate_id IN integer) return number -IS - tied_but_unshipped_amount number; +-- (this amount is still considered outstanding since revenue, and thus gift certificate usage, +-- isnt recognized until the items ship). +create function ec_gift_cert_unshipped_amount (integer) +returns numeric as ' +DECLARE + v_gift_certificate_id alias for $1; + tied_but_unshipped_amount numeric; BEGIN - select nvl(sum(ec_cert_unshipped_one_order(v_gift_certificate_id,order_id)),0) into tied_but_unshipped_amount + select coalesce(sum(ec_cert_unshipped_one_order(v_gift_certificate_id,order_id)),0) into tied_but_unshipped_amount from ec_orders where order_id in (select unique order_id from ec_gift_certificate_usage where gift_certificate_id=v_gift_certificate_id); return tied_but_unshipped_amount; -END; -/ -show errors; +END;' language 'plpgsql'; ---------- end gift certificate procedures ------------ ------------------------------------------------------- -@@ pl-sql-utilities.sql -@@ ec-product-package-create.sql -@@ ecommerce-defaults.sql +\i pl-sql-utilities.sql +\i ec-product-package-create.sql +\i ecommerce-defaults.sql