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.2 -r1.3 --- openacs-4/packages/ecommerce/sql/oracle/ecommerce-create.sql 9 Jul 2001 23:28:10 -0000 1.2 +++ openacs-4/packages/ecommerce/sql/oracle/ecommerce-create.sql 9 Jul 2001 23:34:35 -0000 1.3 @@ -4,9 +4,6 @@ -- 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: @@ -26,84 +23,65 @@ -- a column for marking when a logged entry was for a deleted row. -- product display templates -create sequence ec_template_id_seq start 2; -create view ec_template_id_sequence as select nextval('ec_template_id_seq') as nextval; +create sequence ec_template_id_sequence start with 2; --- 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 timestamp not null, - last_modifying_user integer not null references users, + last_modified date not null, + last_modifying_user 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 timestamp, + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -- A trigger is used to move information from the main table to -- the audit table -create function ec_templates_audit_tr () -returns opaque as ' +create or replace trigger ec_templates_audit_tr +before update or delete on ec_templates +for each row 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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_seq; -create view ec_category_id_sequence as select nextval('ec_category_id_seq') as nextval; +create sequence ec_category_id_sequence; create table ec_categories ( category_id integer not null primary key, -- pretty, human-readable category_name varchar(100), - sort_key numeric, - last_modified timestamp not null, - last_modifying_user integer not null references users, + sort_key number, + last_modified date not null, + last_modifying_user not null references users, modified_ip_address varchar(20) not null ); @@ -112,44 +90,40 @@ create table ec_categories_audit ( category_id integer, category_name varchar(100), - sort_key numeric, - last_modified timestamp, + sort_key number, + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -create function ec_categories_audit_tr () -returns opaque as ' +create or replace trigger ec_categories_audit_tr +before update or delete on ec_categories +for each row 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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_sequence; -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 integer not null references ec_categories, + category_id not null references ec_categories, -- pretty, human-readable subcategory_name varchar(100), - sort_key numeric, - last_modified timestamp not null, - last_modifying_user integer not null references users, + sort_key number, + last_modified date not null, + last_modifying_user not null references users, modified_ip_address varchar(20) not null ); @@ -160,94 +134,88 @@ subcategory_id integer, category_id integer, subcategory_name varchar(100), - sort_key numeric, - last_modified timestamp, + sort_key number, + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -create function ec_subcategories_audit_tr () -returns opaque as ' +create or replace trigger ec_subcategories_audit_tr +before update or delete on ec_subcategories +for each row 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 view ec_subcategories_augmented +create or replace 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_seq; -create view ec_subsubcategory_id_sequence as select nextval('ec_subsubcategory_id_seq') as nextval; +create sequence ec_subsubcategory_id_sequence; create table ec_subsubcategories ( subsubcategory_id integer not null primary key, - subcategory_id integer not null references ec_subcategories, + subcategory_id not null references ec_subcategories, -- pretty, human-readable subsubcategory_name varchar(100), - sort_key numeric, - last_modified timestamp not null, - last_modifying_user integer not null references users, + sort_key number, + last_modified date not null, + last_modifying_user 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 numeric, - last_modified timestamp, + sort_key number, + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -create function ec_subsubcategories_audit_tr () -returns opaque as ' +create or replace trigger ec_subsubcategories_audit_tr +before update or delete on ec_subsubcategories +for each row 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 view ec_subsubcategories_augmented +create or replace 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 @@ -257,7 +225,7 @@ -- this should be replaced by the object_id sequence -- grep for it in files... --- create sequence ec_product_id_sequence start 1; +-- create sequence ec_product_id_sequence start with 1; -- This table contains the products and also the product series. -- A product series has the same fields as a product (it actually @@ -269,97 +237,72 @@ -- 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 integer constraint ec_products_product_id_fk - references acs_objects(object_id) - on delete cascade - constraint ec_products_product_id_pk - primary key, + product_id constraint ec_products_product_id_pk + primary key + constraint ec_products_product__id_fk + references acs_objects, -- above changed by wtem@olywa.net, 2001-03-24 -- integer not null primary key, sku varchar(100), product_name varchar(200), - creation_date timestamp default now() not null, + creation_date date default sysdate 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 numeric, + price number, -- for stuff that can't be shipped like services - no_shipping_avail_p boolean default 'f', + no_shipping_avail_p char(1) default 'f' check(no_shipping_avail_p in ('t', 'f')), -- leave this blank if shipping is calculated using -- one of the more complicated methods available - shipping numeric, + shipping number, -- fill this in if shipping is calculated by: above price -- for first item (with this product_id), and the below -- price for additional items (with this product_id) - shipping_additional numeric, + shipping_additional number, -- fill this in if shipping is calculated using weight -- use whatever units you want (lbs/kg), just be consistent -- and make your shipping algorithm take the units into -- account - weight numeric, + weight number, -- holds pictures, sample chapters, etc. dirname varchar(200), -- whether this item should show up in searches (e.g., if it's -- a volume of a series, you might not want it to) - present_p boolean default 't', + present_p char(1) default 't' check (present_p in ('t','f')), -- whether the item should show up at all in the user pages - active_p boolean default 't', + active_p char(1) default 't' check (active_p in ('t','f')), -- the date the product becomes available for sale (it can be listed -- before then, it's just not buyable) - available_date timestamp default now() not null, + available_date date default sysdate not null, announcements varchar(4000), - announcements_expire timestamp, + announcements_expire date, -- if there's a web site with more info about the product url varchar(300), - template_id integer references ec_templates, + template_id 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 @@ -373,49 +316,50 @@ -- 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 timestamp not null, - last_modifying_user integer not null references users, + last_modified date not null, + last_modifying_user not null references users, modified_ip_address varchar(20) not null ); -create view ec_products_displayable +create or replace view ec_products_displayable as select * from ec_products where active_p='t'; -create view ec_products_searchable +create or replace 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 timestamp, + creation_date date, one_line_description varchar(400), detailed_description varchar(4000), search_keywords varchar(4000), - price numeric, - shipping numeric, - shipping_additional numeric, - weight numeric, + price number, + shipping number, + shipping_additional number, + weight number, dirname varchar(200), - present_p boolean default 't', - active_p boolean default 't', - available_date timestamp, + 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, announcements varchar(4000), - announcements_expire timestamp, + announcements_expire date, url varchar(300), template_id integer, stock_status char(1) check (stock_status in ('o','q','m','s','i')), - last_modified timestamp, + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -create function ec_products_audit_tr () -returns opaque as ' +create or replace trigger ec_products_audit_tr +before update or delete on ec_products +for each row begin insert into ec_products_audit ( product_id, product_name, creation_date, @@ -430,36 +374,32 @@ 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 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 + 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 ); create index ec_product_purchase_comb_idx0 on ec_product_purchase_comb(product_0); @@ -468,80 +408,76 @@ 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_seq start 1; -create view ec_sale_price_id_sequence as select nextval('ec_sale_price_id_seq') as nextval; +create sequence ec_sale_price_id_sequence start with 1; create table ec_sale_prices ( sale_price_id integer not null primary key, - product_id integer not null references ec_products, - sale_price numeric, - sale_begins timestamp not null, - sale_ends timestamp not null, + product_id not null references ec_products, + sale_price number, + sale_begins date not null, + sale_ends date 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 timestamp not null, - last_modifying_user integer not null references users, + last_modified date not null, + last_modifying_user 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 view ec_sale_prices_current +create or replace view ec_sale_prices_current as select * from ec_sale_prices -where now() >= sale_begins -and now() <= sale_ends; +where sysdate-sale_begins>=0 +and sysdate-sale_ends<=0; create table ec_sale_prices_audit ( sale_price_id integer, product_id integer, - sale_price numeric, - sale_begins timestamp, - sale_ends timestamp, + sale_price number, + sale_begins date, + sale_ends date, sale_name varchar(30), offer_code varchar(20), - last_modified timestamp, + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -create function ec_sale_prices_audit_tr () -returns opaque as ' +create or replace trigger ec_sale_prices_audit_tr +before update or delete on ec_sale_prices +for each row 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 integer not null references ec_products, + series_id 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 integer not null references ec_products, + component_id not null references ec_products, primary key (series_id, component_id), - last_modified timestamp not null, - last_modifying_user integer not null references users, + last_modified date not null, + last_modifying_user not null references users, modified_ip_address varchar(20) not null ); @@ -550,39 +486,35 @@ create table ec_product_series_map_audit ( series_id integer, component_id integer, - last_modified timestamp, + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -create function ec_product_series_map_audit_tr () -returns opaque as ' +create or replace trigger ec_product_series_map_audit_tr +before update or delete on ec_product_series_map +for each row 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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_sequence start with 1; - -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 integer not null references users, + user_id not null references users, address_type varchar(20) not null, -- e.g., billing attn varchar(100), line1 varchar(100), @@ -605,12 +537,11 @@ create index ec_addresses_by_user_idx on ec_addresses (user_id); -create sequence ec_creditcard_id_seq start 1; -create view ec_creditcard_id_sequence as select nextval('ec_creditcard_id_seq') as nextval; +create sequence ec_creditcard_id_sequence start with 1; create table ec_creditcards ( creditcard_id integer not null primary key, - user_id integer not null references users, + user_id 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) @@ -625,58 +556,54 @@ 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 boolean default 'f' + failed_p char(1) default 'f' check (failed_p in ('t','f')) ); create index ec_creditcards_by_user_idx on ec_creditcards (user_id); -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 sequence ec_user_class_id_sequence start with 1; 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 timestamp not null, - last_modifying_user integer not null references users, + last_modified date not null, + last_modifying_user 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 timestamp, + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -create function ec_user_classes_audit_tr () -returns opaque as ' +create or replace trigger ec_user_classes_audit_tr +before update or delete on ec_user_classes +for each row 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 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, + 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, modified_ip_address varchar(20) not null, primary key (product_id, user_class_id) ); @@ -688,52 +615,49 @@ create table ec_product_u_c_prices_audit ( product_id integer, user_class_id integer, - price numeric, - last_modified timestamp, + price number, + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -create function ec_product_u_c_prices_audit_tr () -returns opaque as ' +create or replace trigger ec_product_u_c_prices_audit_tr +before update or delete on ec_product_user_class_prices +for each row 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 integer not null references ec_products, + product_id not null references ec_products, -- might be null if the product is recommended for everyone - user_class_id integer references ec_user_classes, + user_class_id references ec_user_classes, -- html format recommendation_text varchar(4000), - active_p boolean default 't', + active_p char(1) default 't' check (active_p in ('t','f')), -- where it's displayed (top level if all three are blank): - 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, + 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, modified_ip_address varchar(20) not null ); @@ -748,18 +672,19 @@ product_id integer, user_class_id integer, recommendation_text varchar(4000), - active_p boolean default 't', + active_p char(1) default 't' check (active_p in ('t','f')), category_id integer, subcategory_id integer, subsubcategory_id integer, - last_modified timestamp, + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -create function ec_product_recommend_audit_tr () -returns opaque as ' +create or replace trigger ec_product_recommend_audit_tr +before update or delete on ec_product_recommendations +for each row begin insert into ec_product_recommend_audit ( recommendation_id, product_id, @@ -769,58 +694,50 @@ 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 +create or replace view ec_recommendations_cats_view as select recs.*, - 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, + 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, subsubs.subsubcategory_id as the_subsubcategory_id, subsubs.subsubcategory_name as the_subsubcategory_name from - 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_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_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 integer not null references users, - user_class_id integer not null references ec_user_classes, + user_id not null references users, + user_class_id not null references ec_user_classes, primary key (user_id, user_class_id), - user_class_approved_p boolean, - last_modified timestamp not null, - last_modifying_user integer not null references users, + 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, modified_ip_address varchar(20) not null ); @@ -830,40 +747,38 @@ create table ec_user_class_user_map_audit ( user_id integer, user_class_id integer, - user_class_approved_p boolean, - last_modified timestamp, + user_class_approved_p char(1) check (user_class_approved_p in ('t','f')), + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -create function ec_user_class_user_audit_tr () -returns opaque as ' +create or replace trigger ec_user_class_user_audit_tr +before update or delete on ec_user_class_user_map +for each row 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 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, + 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, modified_ip_address varchar(20) not null, primary key (product_a, product_b) ); @@ -873,47 +788,44 @@ create table ec_product_links_audit ( product_a integer, product_b integer, - last_modified timestamp, + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -create function ec_product_links_audit_tr () -returns opaque as ' +create or replace trigger ec_product_links_audit_tr +before update or delete on ec_product_links +for each row 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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_sequence start with 1; -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 integer not null references ec_products, - user_id integer not null references users, + product_id not null references ec_products, + user_id not null references users, user_comment varchar(4000), one_line_summary varchar(300), - rating numeric, + rating number, -- in some systems, the administrator will have to approve comments first - approved_p boolean, - comment_date timestamp, - last_modified timestamp not null, - last_modifying_user integer not null references users, + 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, modified_ip_address varchar(20) not null ); @@ -927,51 +839,48 @@ user_id integer, user_comment varchar(4000), one_line_summary varchar(300), - rating numeric, - approved_p boolean, - last_modified timestamp, + rating number, + approved_p char(1), + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -create function ec_product_comments_audit_tr () -returns opaque as ' +create or replace trigger ec_product_comments_audit_tr +before update or delete on ec_product_comments +for each row 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 integer not null references ec_products, + product_id not null references ec_products, author_name varchar(100), publication varchar(100), - review_date timestamp, + review_date date, -- in HTML format - review text, - display_p boolean, - last_modified timestamp not null, - last_modifying_user integer not null references users, + review clob, + display_p char(1) check (display_p in ('t','f')), + last_modified date not null, + last_modifying_user not null references users, modified_ip_address varchar(20) not null ); @@ -983,18 +892,19 @@ product_id integer, author_name varchar(100), publication varchar(100), - review_date timestamp, + review_date date, -- in HTML format - review text, - display_p boolean, - last_modified timestamp, + review clob, + display_p char(1) check (display_p in ('t','f')), + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -create function ec_product_reviews_audit_tr () -returns opaque as ' +create or replace trigger ec_product_reviews_audit_tr +before update or delete on ec_product_reviews +for each row begin insert into ec_product_reviews_audit ( review_id, product_id, @@ -1004,28 +914,25 @@ 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 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, + 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, modified_ip_address varchar(20) not null, primary key (product_id, category_id) ); @@ -1036,43 +943,40 @@ create table ec_category_product_map_audit ( product_id integer, category_id integer, - publisher_favorite_p boolean, - last_modified timestamp, + publisher_favorite_p char(1) check (publisher_favorite_p in ('t','f')), + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -- ec_category_product_map_audit_tr abbreviated as -- ec_cat_prod_map_audit_tr -create function ec_cat_prod_map_audit_tr () -returns opaque as ' +create or replace trigger ec_cat_prod_map_audit_tr +before update or delete on ec_category_product_map +for each row 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 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, + 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, modified_ip_address varchar(20) not null, primary key (product_id, subcategory_id) ); @@ -1084,42 +988,39 @@ create table ec_subcat_prod_map_audit ( product_id integer, subcategory_id integer, - publisher_favorite_p boolean, - last_modified timestamp, + publisher_favorite_p char(1) check (publisher_favorite_p in ('t','f')), + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -- ec_subcat_prod_map_audit_tr -create function ec_subcat_prod_map_audit_tr () -returns opaque as ' +create or replace trigger ec_subcat_prod_map_audit_tr +before update or delete on ec_subcategory_product_map +for each row 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 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, + 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, modified_ip_address varchar(20) not null, primary key (product_id, subsubcategory_id) ); @@ -1130,34 +1031,32 @@ create table ec_subsubcat_prod_map_audit ( product_id integer, subsubcategory_id integer, - publisher_favorite_p boolean, - last_modified timestamp, + publisher_favorite_p char(1) check (publisher_favorite_p in ('t','f')), + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -create function ec_subsubcat_prod_map_audit_tr () -returns opaque as ' +create or replace trigger ec_subsubcat_prod_map_audit_tr +before update or delete on ec_subsubcategory_product_map +for each row 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 @@ -1167,8 +1066,8 @@ -- the admin can always associate a product with any category -- they want. create table ec_category_template_map ( - category_id integer not null primary key references ec_categories, - template_id integer not null references ec_templates + category_id not null primary key references ec_categories, + template_id not null references ec_templates ); create index ec_category_template_map_idx on ec_category_template_map (template_id); @@ -1185,10 +1084,10 @@ default_value varchar(100), -- column type for oracle (i.e. text, varchar(50), integer, ...) column_type varchar(100), - creation_date timestamp, - active_p boolean default 't', - last_modified timestamp not null, - last_modifying_user integer not null references users, + 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, modified_ip_address varchar(20) not null ); @@ -1197,16 +1096,17 @@ field_name varchar(100), default_value varchar(100), column_type varchar(100), - creation_date timestamp, - active_p boolean default 't', - last_modified timestamp, + creation_date date, + active_p char(1) default 't' check (active_p in ('t','f')), + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -create function ec_custom_prod_fields_audit_tr () -returns opaque as ' +create or replace trigger ec_custom_prod_fields_audit_tr +before update or delete on ec_custom_product_fields +for each row begin insert into ec_custom_product_fields_audit ( field_identifier, field_name, @@ -1215,77 +1115,70 @@ 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 integer not null primary key references ec_products, - last_modified timestamp not null, - last_modifying_user integer not null references users, + product_id not null primary key references ec_products, + last_modified date not null, + last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create table ec_custom_p_field_values_audit ( product_id integer, - last_modified timestamp, + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -create function ec_custom_p_f_values_audit_tr () -returns opaque as ' +create or replace trigger ec_custom_p_f_values_audit_tr +before update or delete on ec_custom_product_field_values +for each row 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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_seq; -create view ec_user_session_sequence as select nextval('ec_user_session_seq') as nextval; - +create sequence ec_user_session_sequence; 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 integer references users, + user_id references users, ip_address varchar(20) not null, - start_time timestamp, + start_time date, 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 integer not null references ec_user_sessions, - product_id integer references ec_products, - category_id integer references ec_categories, + user_session_id not null references ec_user_sessions, + product_id references ec_products, + category_id references ec_categories, search_text varchar(200) ); @@ -1301,8 +1194,8 @@ -- for a product. But I think this will be a little faster. create table ec_user_session_offer_codes ( - user_session_id integer not null references ec_user_sessions, - product_id integer not null references ec_products, + user_session_id not null references ec_user_sessions, + product_id not null references ec_products, offer_code varchar(20) not null, primary key (user_session_id, product_id) ); @@ -1311,39 +1204,38 @@ 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_seq start 3000000; -create view ec_order_id_sequence as select nextval('ec_order_id_seq') as nextval; +create sequence ec_order_id_sequence start with 3000000; 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 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' + 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' 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 timestamp, - confirmed_date timestamp, - authorized_date timestamp, - voided_date timestamp, - expired_date timestamp, + in_basket_date date, + confirmed_date date, + authorized_date date, + voided_date date, + expired_date date, -- base shipping, which is added to the amount charged for each item - shipping_charged numeric, - shipping_refunded numeric, - shipping_tax_charged numeric, - shipping_tax_refunded numeric, + shipping_charged number, + shipping_refunded number, + shipping_tax_charged number, + shipping_tax_refunded number, -- entered by customer service cs_comments varchar(4000), reason_for_void varchar(4000), - voided_by integer references users, + voided_by references users, -- if the user chooses to save their shopping cart - saved_p boolean + saved_p char(1) check (saved_p in ('t','f')), check (user_id is not null or user_session_id is not null) ); @@ -1357,45 +1249,44 @@ -- note that an order could essentially become uninteresting for financial -- accounting if all the items underneath it are individually voided or returned -create view ec_orders_reportable +create or replace 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 view ec_orders_shippable +create or replace 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_seq; -create view ec_shipment_id_sequence as select nextval('ec_shipment_id_seq') as nextval; +create sequence ec_shipment_id_sequence; create table ec_shipments ( shipment_id integer not null primary key, - order_id integer not null references ec_orders, + order_id 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 integer not null references ec_addresses, - shipment_date timestamp not null, - expected_arrival_date timestamp, + address_id not null references ec_addresses, + shipment_date date not null, + expected_arrival_date date, 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 timestamp, + actual_arrival_date date, -- 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 boolean default 't', - last_modified timestamp, + shippable_p char(1) default 't' check(shippable_p in ('t', 'f')), + last_modified date, last_modifying_user integer, modified_ip_address varchar(20) ); @@ -1405,41 +1296,40 @@ -- fills address_id into ec_shipments if it's missing -- (using the shipping_address associated with the order) -create function ec_shipment_address_update_tr () -returns opaque as ' +create or replace trigger ec_shipment_address_update_tr +before insert on ec_shipments +for each row declare v_address_id ec_addresses.address_id%TYPE; begin - 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; + 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; END IF; - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 timestamp, - expected_arrival_date timestamp, + shipment_date date, + expected_arrival_date date, carrier varchar(50), tracking_number varchar(24), - actual_arrival_date timestamp, + actual_arrival_date date, actual_arrival_detail varchar(4000), - last_modified timestamp, + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -create function ec_shipments_audit_tr () -returns opaque as ' +create or replace trigger ec_shipments_audit_tr +before update or delete on ec_shipments +for each row begin insert into ec_shipments_audit ( shipment_id, order_id, address_id, @@ -1450,69 +1340,65 @@ 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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_seq; -create view refund_id_sequence as select nextval('refund_id_seq') as nextval; +create sequence refund_id_sequence; create table ec_refunds ( refund_id integer not null primary key, - order_id integer not null references ec_orders, + order_id not null references ec_orders, -- not really necessary because it's in ec_financial_transactions - refund_amount numeric not null, - refund_date timestamp not null, - refunded_by integer not null references users, + refund_amount number not null, + refund_date date not null, + refunded_by 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_seq start 1; -create view ec_item_id_sequence as select nextval('ec_item_id_seq') as nextval; +create sequence ec_item_id_sequence start with 1; create table ec_items ( item_id integer not null primary key, - order_id integer not null references ec_orders, - product_id integer not null references ec_products, + order_id not null references ec_orders, + product_id not null references ec_products, color_choice varchar(4000), size_choice varchar(4000), style_choice varchar(4000), - shipment_id integer references ec_shipments, + shipment_id references ec_shipments, -- this is the date that user put this item into their shopping basket - in_cart_date timestamp, - voided_date timestamp, - voided_by integer references users, - expired_date timestamp, + in_cart_date date, + voided_date date, + voided_by references users, + expired_date date, item_state varchar(50) default 'in_basket', -- NULL if not received back - received_back_date timestamp, + received_back_date date, -- columns for reporting (e.g., what was done, what was made) - 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, + 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, -- like Our Price or Sale Price or Introductory Price price_name varchar(30), -- did we go through a merchant-initiated refund? - refund_id integer references ec_refunds, + refund_id references ec_refunds, -- comments entered by customer service (CS) cs_comments varchar(4000) ); @@ -1521,20 +1407,20 @@ create index ec_items_by_order on ec_items(order_id); create index ec_items_by_shipment on ec_items(shipment_id); -create view ec_items_reportable +create or replace view ec_items_reportable as select * from ec_items where item_state in ('to_be_shipped', 'shipped', 'arrived'); -create view ec_items_refundable +create or replace view ec_items_refundable as select * from ec_items where item_state in ('shipped','arrived') and refund_id is null; -create view ec_items_shippable +create or replace view ec_items_shippable as select * from ec_items @@ -1549,17 +1435,12 @@ -- 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 --- 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 +create or replace view ec_items_money_view as -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 +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 from ec_items i, ec_shipments s where i.shipment_id=s.shipment_id and i.item_state <> 'void' @@ -1582,78 +1463,65 @@ -- 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 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 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 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. - --- gilbertw - I took the trigger procedure from OpenACS 3.2.5. -create function ec_order_state_after_tr () -returns opaque as ' +create or replace trigger ec_order_state_after_tr +after update on ec_items declare - -- v_order_id integer; + CURSOR orders_cursor IS + SELECT unique order_id + FROM ec_state_change_order_ids; + + v_order_id ec_orders.order_id%TYPE; n_items integer; n_shipped_items integer; n_received_back_items integer; n_void_items integer; n_nonvoid_items integer; begin - 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''; + FOR orders_cursor_rec IN orders_cursor LOOP + v_order_id := orders_cursor_rec.order_id; - 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'; + 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'; -create trigger ec_order_state_after_tr -after update on ec_items -for each row execute procedure ec_order_state_after_tr (); + 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 + -- 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 @@ -1667,39 +1535,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 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, + 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 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 timestamp not null, - last_modifying_user integer not null references users, - modified_ip_address varchar(20) not null + 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 ); create table ec_admin_settings_audit ( admin_setting_id integer, - 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' + 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')) ); -create function ec_admin_settings_audit_tr () -returns opaque as ' +create or replace trigger ec_admin_settings_audit_tr +before update or delete on ec_admin_settings +for each row begin insert into ec_admin_settings_audit ( admin_setting_id, base_shipping_cost, default_shipping_per_item, @@ -1709,74 +1577,67 @@ 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 char(2) not null primary key references states, + usps_abbrev not null primary key references states, -- this a decimal number equal to the percentage tax divided by 100 - tax_rate numeric not null, + tax_rate number not null, -- charge tax on shipping? - shipping_p boolean not null, - last_modified timestamp not null, - last_modifying_user integer not null references users, + shipping_p char(1) not null check (shipping_p in ('t','f')), + last_modified date not null, + last_modifying_user 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 numeric, - shipping_p boolean, - last_modified timestamp, + tax_rate number, + shipping_p char(1) check (shipping_p in ('t','f')), + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -- Jerry - I removed usps_abbrev and/or state here -create function ec_sales_tax_by_state_audit_tr () -returns opaque as ' +create or replace trigger ec_sales_tax_by_state_audit_tr +before update or delete on ec_sales_tax_by_state +for each row 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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_seq start 1; -create view ec_retailer_sequence as select nextval('ec_retailer_seq') as nextval; +create sequence ec_retailer_sequence start with 1; create table ec_retailers ( retailer_id integer not null primary key, @@ -1790,7 +1651,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), @@ -1808,8 +1669,8 @@ price_guarantee_policy varchar(4000), delivery_policy varchar(4000), installation_policy varchar(4000), - last_modified timestamp not null, - last_modifying_user integer not null references users, + last_modified date not null, + last_modifying_user not null references users, modified_ip_address varchar(20) not null ); @@ -1820,31 +1681,32 @@ 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 timestamp, + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -- Jerry - I removed usps_abbrev and/or state here -create function ec_retailers_audit_tr () -returns opaque as ' +create or replace trigger ec_retailers_audit_tr +before update or delete on ec_retailers +for each row begin insert into ec_retailers_audit ( retailer_id, retailer_name, @@ -1861,33 +1723,30 @@ 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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_seq start 1; -create view ec_retailer_location_sequence as select nextval('ec_retailer_location_seq') as nextval; +create sequence ec_retailer_location_sequence start with 1; create table ec_retailer_locations ( retailer_location_id integer not null primary key, - retailer_id integer not null references ec_retailers, + retailer_id not null references ec_retailers, location_name varchar(300), primary_contact_name varchar(100), secondary_contact_name varchar(100), @@ -1899,7 +1758,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), @@ -1914,8 +1773,8 @@ price_guarantee_policy varchar(4000), delivery_policy varchar(4000), installation_policy varchar(4000), - last_modified timestamp not null, - last_modifying_user integer not null references users, + last_modified date not null, + last_modifying_user not null references users, modified_ip_address varchar(20) not null ); @@ -1927,30 +1786,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), - 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 timestamp, + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -- Jerry - I removed usps_abbrev and/or state here -create function ec_retailer_locations_audit_tr () -returns opaque as ' +create or replace trigger ec_retailer_locations_audit_tr +before update or delete on ec_retailer_locations +for each row begin insert into ec_retailer_locations_audit ( retailer_location_id, retailer_id, location_name, @@ -1966,61 +1826,57 @@ 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 integer not null references ec_products, - retailer_location_id integer not null references ec_retailer_locations, + product_id not null references ec_products, + retailer_location_id not null references ec_retailer_locations, store_sku integer, retailer_premiums varchar(500), - price numeric not null, - shipping numeric, - shipping_unavailable_p boolean, + price number not null, + shipping number, + shipping_unavailable_p char(1) check (shipping_unavailable_p in ('t','f')), -- 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 boolean, + special_offer_p char(1) check (special_offer_p in ('t','f')), special_offer_html varchar(500), - 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, + 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, modified_ip_address varchar(20) not null ); -create view ec_offers_current +create or replace view ec_offers_current as select * from ec_offers where deleted_p='f' -and now() >= offer_begins -and now() <= offer_ends; +and sysdate-offer_begins>=0 +and sysdate-offer_ends<=0; create table ec_offers_audit ( @@ -2029,27 +1885,28 @@ retailer_location_id integer, store_sku integer, retailer_premiums varchar(500), - price numeric, - shipping numeric, - shipping_unavailable_p boolean, + price number, + shipping number, + shipping_unavailable_p char(1) check (shipping_unavailable_p in ('t','f')), stock_status char(1) check (stock_status in ('o','q','m','s','i')), - special_offer_p boolean, + special_offer_p char(1) check (special_offer_p in ('t','f')), special_offer_html varchar(500), - offer_begins timestamp, - offer_ends timestamp, - deleted_p boolean default 'f', - last_modified timestamp, + offer_begins date, + offer_ends date, + deleted_p char(1) default 'f' check (deleted_p in ('t','f')), + last_modified date, 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 boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -create function ec_offers_audit_tr () -returns opaque as ' +create or replace trigger ec_offers_audit_tr +before update or delete on ec_offers +for each row begin insert into ec_offers_audit ( offer_id, @@ -2063,47 +1920,43 @@ 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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_seq start 1000000; -create view ec_gift_cert_id_sequence as select nextval('ec_gift_cert_id_seq') as nextval; +create sequence ec_gift_cert_id_sequence start with 1000000; create table ec_gift_certificates ( gift_certificate_id integer primary key, gift_certificate_state varchar(50) not null, - amount numeric not null, + amount number not null, -- a trigger will update this to f if the -- entire amount is used up (to speed up -- queries) - amount_remaining_p boolean default 't', - issue_date timestamp, - authorized_date timestamp, - claimed_date timestamp, + amount_remaining_p char(1) default 't' check (amount_remaining_p in ('t','f')), + issue_date date, + authorized_date date, + claimed_date date, -- customer service rep who issued it - issued_by integer references users, + issued_by references users, -- customer who purchased it - purchased_by integer references users, - expires timestamp, - user_id integer references users, + purchased_by references users, + expires date, + user_id 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 @@ -2113,11 +1966,11 @@ certificate_to varchar(100), certificate_from varchar(100), recipient_email varchar(100), - voided_date timestamp, - voided_by integer references users, + voided_date date, + voided_by references users, reason_for_void varchar(4000), - last_modified timestamp not null, - last_modifying_user integer not null references users, + last_modified date not null, + last_modifying_user not null references users, modified_ip_address varchar(20) not null, check (user_id is not null or claim_check is not null) ); @@ -2134,19 +1987,19 @@ -- and financial transactions. ec_financial_transactions stores the corresponding -- gift_certificate_id. -create view ec_gift_certificates_approved +create or replace view ec_gift_certificates_approved as select * from ec_gift_certificates where gift_certificate_state in ('authorized','authorized_plus_avs','authorized_minus_avs'); -create view ec_gift_certificates_purchased +create or replace view ec_gift_certificates_purchased as select * from ec_gift_certificates where gift_certificate_state in ('authorized_plus_avs','authorized_minus_avs'); -create view ec_gift_certificates_issued +create or replace view ec_gift_certificates_issued as select * from ec_gift_certificates @@ -2157,30 +2010,31 @@ create table ec_gift_certificates_audit ( gift_certificate_id integer, gift_certificate_state varchar(50), - amount numeric, - issue_date timestamp, - authorized_date timestamp, + amount number, + issue_date date, + authorized_date date, issued_by integer, purchased_by integer, - expires timestamp, + expires date, 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 timestamp, + voided_date date, voided_by integer, reason_for_void varchar(4000), - last_modified timestamp, + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -create function ec_gift_certificates_audit_tr () -returns opaque as ' +create or replace trigger ec_gift_certificates_audit_tr +before update or delete on ec_gift_certificates +for each row begin insert into ec_gift_certificates_audit ( gift_certificate_id, amount, @@ -2191,59 +2045,52 @@ 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 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 + 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 ); create index ec_gift_cert_by_id on ec_gift_certificate_usage (gift_certificate_id); --------- customer service -------------------- -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; +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; -- 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 timestamp, - user_id integer references users, - email varchar(100), - first_names varchar(100), - last_name varchar(100), + date_added date, + user_id 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 @@ -2256,98 +2103,87 @@ -- puts date_added into ec_user_identification if it's missing -create function ec_user_identificate_date_tr () -returns opaque as ' +create or replace trigger ec_user_identificate_date_tr +before insert on ec_user_identification +for each row begin - IF new.date_added is null THEN - new.date_added := now(); + IF :new.date_added is null THEN + :new.date_added := sysdate; END IF; - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 integer references users, - user_identification_id integer not null - references ec_user_identification, - interaction_date timestamp, + customer_service_rep references users, + user_identification_id not null references ec_user_identification, + interaction_date date, 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); --- gilbertw - used the code in OpenACS 3.2.5 as a reference -create function ec_cs_interaction_inserts () -returns opaque as ' +create or replace trigger ec_cs_interaction_inserts +before insert on ec_customer_serv_interactions +for each row begin - IF new.interaction_date is null THEN - new.interaction_date := now(); + IF INSERTING AND :new.interaction_date is null THEN + :new.interaction_date := sysdate; END IF; - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 +create or replace 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 integer not null references ec_user_identification, + user_identification_id 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 timestamp not null, - close_date timestamp, + open_date date not null, + close_date date, -- customer service reps who closed the issue - closed_by integer references users, + closed_by references users, -- we never really delete issues - deleted_p boolean default 'f' + deleted_p char(1) default 'f' check (deleted_p in ('t','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 integer not null references ec_customer_service_issues, + issue_id 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); --- gilbertw - used code OpenACS 3.2.5 as a reference --- removed INSERTING -create function ec_cs_issue_inserts () -returns opaque as ' +create or replace trigger ec_cs_issue_inserts +before insert on ec_customer_service_issues +for each row begin - IF new.open_date is null THEN - new.open_date := now(); + IF INSERTING AND :new.open_date is null THEN + :new.open_date := sysdate; END IF; - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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, @@ -2359,7 +2195,7 @@ create index ec_csa_by_issue on ec_customer_service_actions(issue_id); create table ec_cs_action_info_used_map ( - action_id integer not null references ec_customer_service_actions, + action_id not null references ec_customer_service_actions, info_used varchar(100) not null ); @@ -2369,56 +2205,52 @@ -- this table contains picklist choices for the customer service data -- entry people -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 sequence ec_picklist_item_id_sequence; 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 numeric, - last_modified timestamp not null, - last_modifying_user integer not null references users, + sort_key number, + last_modified date not null, + last_modifying_user 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 numeric, - last_modified timestamp, + sort_key number, + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -create function ec_picklist_items_audit_tr () -returns opaque as ' +create or replace trigger ec_picklist_items_audit_tr +before update or delete on ec_picklist_items +for each row 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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_seq; -create view ec_canned_response_id_sequence as select nextval('ec_canned_response_id_seq') as nextval; +create sequence ec_canned_response_id_sequence; create table ec_canned_responses ( response_id integer not null primary key, @@ -2431,8 +2263,7 @@ -- 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_seq start 7; -create view ec_email_template_id_sequence as select nextval('ec_email_template_id_seq') as nextval; +create sequence ec_email_template_id_sequence start with 7; create table ec_email_templates ( email_template_id integer not null primary key, @@ -2450,8 +2281,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 timestamp not null, - last_modifying_user integer not null references users, + last_modified date not null, + last_modifying_user not null references users, modified_ip_address varchar(20) not null ); @@ -2463,14 +2294,15 @@ variables varchar(1000), when_sent varchar(1000), issue_type_list varchar(100), - last_modified timestamp, + last_modified date, last_modifying_user integer, modified_ip_address varchar(20), - delete_p boolean default 'f' + delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -create function ec_email_templates_audit_tr () -returns opaque as ' +create or replace trigger ec_email_templates_audit_tr +before update or delete on ec_email_templates +for each row begin insert into ec_email_templates_audit ( email_template_id, title, @@ -2480,24 +2312,21 @@ 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 ); - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 @@ -2517,34 +2346,33 @@ -- 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 integer not null references users, - category_id integer references ec_categories, - subcategory_id integer references ec_subcategories, - subsubcategory_id integer references ec_subsubcategories + user_id not null references users, + category_id references ec_categories, + subcategory_id references ec_subcategories, + subsubcategory_id 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_seq; -create view ec_spam_id_sequence as select nextval('ec_spam_id_seq') as nextval; +create sequence ec_spam_id_sequence; create table ec_spam_log ( spam_id integer not null primary key, - spam_date timestamp, + spam_date date, spam_text varchar(4000), -- the following are all criteria used in choosing the users to be spammed - 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 + 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 ); create index ec_spam_log_by_cat_mail_idx on ec_spam_log (mailing_list_category_id); @@ -2558,53 +2386,52 @@ -- CREDIT CARD STUFF ------------------------ --------------------------------------------- -create sequence ec_transaction_id_seq start 4000000; -create view ec_transaction_id_sequence as select nextval('ec_transaction_id_seq') as nextval; +create sequence ec_transaction_id_sequence start with 4000000; create table ec_financial_transactions ( transaction_id integer not null primary key, -- order_id or gift_certificate_id must be filled in - order_id integer references ec_orders, - -- The following two rows were added 1999-08-11. They re + order_id 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 integer references ec_shipments, - refund_id integer references ec_refunds, + shipment_id references ec_shipments, + refund_id references ec_refunds, -- this refers to the purchase of a gift certificate, not the use of one - gift_certificate_id integer references ec_gift_certificates, + gift_certificate_id 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 integer not null references ec_creditcards, - transaction_amount numeric 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 not null references ec_creditcards, + transaction_amount number 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, theres no such thing as an authorization + -- in the case of a refund, there's 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 boolean, - inserted_date timestamp not null, - authorized_date timestamp, + to_be_captured_p char(1) check (to_be_captured_p in (null,'t','f')), + inserted_date date not null, + authorized_date date, -- set when to_be_captured_p becomes 't'; used in cron jobs - to_be_captured_date timestamp, - marked_date timestamp, - settled_date timestamp, - refunded_date timestamp, - refund_settled_date timestamp, + to_be_captured_date date, + marked_date date, + settled_date date, + refunded_date date, + refund_settled_date date, -- generated by us talking to Cybercash - disputed_p boolean, + disputed_p char(1) check (disputed_p in ('t','f')), -- date on which we discovered the dispute - dispute_discovery_date timestamp, + dispute_discovery_date date, -- if the consumer's bank got his money back from us forcibly - charged_back_p boolean, + charged_back_p char(1) check (charged_back_p in ('t','f')), -- if the transaction failed, this will keep the cron jobs from continuing -- to retry it - failed_p boolean default 'f', + failed_p char(1) default 'f' check (failed_p in ('t','f')), check (order_id is not null or gift_certificate_id is not null) ); @@ -2614,7 +2441,7 @@ -- reportable transactions: those which have not failed which are to -- be captured (note: refunds are always to be captured) -create view ec_fin_transactions_reportable +create or replace 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') @@ -2623,31 +2450,28 @@ -- fills creditcard_id into ec_financial_transactions if it's missing -- (using the credit card associated with the order) -create function fin_trans_ccard_update_tr () -returns opaque as ' +create or replace trigger fin_trans_ccard_update_tr +before insert on ec_financial_transactions +for each row declare v_creditcard_id ec_creditcards.creditcard_id%TYPE; begin - 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; + 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; END IF; END IF; - return new; -end;' language 'plpgsql'; +end; +/ +show errors -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 integer not null references ec_financial_transactions, + transaction_id 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 timestamp, + txn_attempted_time date, -- 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 @@ -2662,7 +2486,7 @@ txn_type varchar(25), -- we take the time returned by CyberCash and chop off the -- sub-second precision - cc_time timestamp, + cc_time date, merch_txn varchar(25), cust_txn varchar(25), origin char(1), @@ -2675,7 +2499,7 @@ avs_code varchar(3), ref_code varchar(25), batch_id varchar(25), - amount numeric + amount number ); -- END CREDIT CARD STUFF ---------------------------- @@ -2684,30 +2508,29 @@ -- 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_seq; -create view ec_problem_id_sequence as select nextval('ec_problem_id_seq') as nextval; +create sequence ec_problem_id_sequence; create table ec_problems_log ( problem_id integer not null primary key, - problem_date timestamp, + problem_date date, problem_details varchar(4000), -- if it's related to an order - order_id integer references ec_orders, + order_id references ec_orders, -- if it's related to a gift certificate - gift_certificate_id integer references ec_gift_certificates, - resolved_date timestamp, - resolved_by integer references users + gift_certificate_id references ec_gift_certificates, + resolved_date date, + resolved_by references users ); -- keeps track of automatic emails (based on templates) that are sent out create table ec_automatic_email_log ( - 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 + 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 ); create index ec_auto_email_by_usr_id_idx on ec_automatic_email_log (user_identification_id); @@ -2728,301 +2551,297 @@ -- just the price of an order, not shipping, tax, or gift certificates -- this is actually price_charged minus price_refunded -create function ec_total_price (integer) -returns numeric as ' -DECLARE - v_order_id alias for $1; - price numeric; +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; BEGIN - 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''; + OPEN price_cursor; + FETCH price_cursor INTO price; + CLOSE price_cursor; -END;' language 'plpgsql'; + RETURN price; +END; +/ +show errors -- just the shipping of an order, not price, tax, or gift certificates -- this is actually total shipping minus total shipping refunded -create function ec_total_shipping (integer) -returns numeric as ' -DECLARE - v_order_id alias for $1; - order_shipping numeric; - item_shipping numeric; +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; BEGIN - select into order_shipping - coalesce(shipping_charged,0) - coalesce(shipping_refunded,0) - FROM ec_orders - WHERE order_id=v_order_id; + OPEN order_shipping_cursor; + FETCH order_shipping_cursor INTO order_shipping; + CLOSE order_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''; + OPEN item_shipping_cursor; + FETCH item_shipping_cursor INTO item_shipping; + CLOSE item_shipping_cursor; - return order_shipping + item_shipping; -END;' language 'plpgsql'; + RETURN order_shipping + item_shipping; +END; +/ +show errors -- OK -- just the tax of an order, not price, shipping, or gift certificates -- this is tax minus tax refunded -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; +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; BEGIN - select into order_tax - coalesce(shipping_tax_charged,0) - coalesce(shipping_tax_refunded,0) - FROM ec_orders - WHERE order_id=v_order_id; + OPEN order_tax_cursor; + FETCH order_tax_cursor INTO order_tax; + CLOSE order_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_price_tax_cursor; + FETCH item_price_tax_cursor INTO item_price_tax; + CLOSE item_price_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; + OPEN item_shipping_tax_cursor; + FETCH item_shipping_tax_cursor INTO item_shipping_tax; + CLOSE item_shipping_tax_cursor; - return order_tax + item_price_tax + item_shipping_tax; -END;' language 'plpgsql'; + RETURN order_tax + item_price_tax + item_shipping_tax; +END; +/ +show errors -- 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 function ec_shipment_price (integer) -returns numeric as ' -DECLARE - v_shipment_id alias for $1; - shipment_price numeric; +create or replace function ec_shipment_price (v_shipment_id IN integer) return number +IS + shipment_price number; BEGIN - SELECT into shipment_price coalesce(sum(price_charged),0) - coalesce(sum(price_refunded),0) + SELECT nvl(sum(price_charged),0) - nvl(sum(price_refunded),0) into shipment_price FROM ec_items WHERE shipment_id=v_shipment_id - and item_state <> ''void''; + and item_state <> 'void'; RETURN shipment_price; -END;' language 'plpgsql'; +END; +/ +show errors -- 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 function ec_shipment_shipping (integer) -returns numeric as ' -DECLARE - v_shipment_id alias for $1; - item_shipping numeric; - base_shipping numeric; +create or replace function ec_shipment_shipping (v_shipment_id IN integer) return number +IS + item_shipping number; + base_shipping number; v_order_id ec_orders.order_id%TYPE; min_shipment_id ec_shipments.shipment_id%TYPE; BEGIN - 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; + 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; IF v_shipment_id=min_shipment_id THEN - SELECT into base_shipping - coalesce(shipping_charged,0) - coalesce(shipping_refunded,0) - FROM ec_orders where order_id=v_order_id; + SELECT nvl(shipping_charged,0) - nvl(shipping_refunded,0) into base_shipping FROM ec_orders where order_id=v_order_id; ELSE base_shipping := 0; END IF; - 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''; + 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'; RETURN item_shipping + base_shipping; -END;' language 'plpgsql'; +END; +/ +show errors -- 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 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; +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; v_order_id ec_orders.order_id%TYPE; min_shipment_id ec_shipments.shipment_id%TYPE; BEGIN - 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; + 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; IF v_shipment_id=min_shipment_id THEN - SELECT into base_shipping_tax - coalesce(shipping_tax_charged,0) - coalesce(shipping_tax_refunded,0) - FROM ec_orders where order_id=v_order_id; + SELECT nvl(shipping_tax_charged,0) - nvl(shipping_tax_refunded,0) into base_shipping_tax FROM ec_orders where order_id=v_order_id; ELSE base_shipping_tax := 0; END IF; - 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''; + 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'; RETURN item_price_tax + item_shipping_tax + base_shipping_tax; -END;' language 'plpgsql'; +END; +/ +show errors -- OK -- the gift certificate amount used on one order -create function ec_order_gift_cert_amount (integer) -returns numeric as ' -DECLARE - v_order_id alias for $1; - gift_cert_amount numeric; +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; BEGIN - 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; + OPEN gift_cert_amount_cursor; + FETCH gift_cert_amount_cursor INTO gift_cert_amount; + CLOSE gift_cert_amount_cursor; return gift_cert_amount; -END;' language 'plpgsql'; +END; +/ +show errors + -- 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 function ec_shipment_gift_certificate (integer) -returns numeric as ' -DECLARE - v_shipment_id alias for $1; +create or replace function ec_shipment_gift_certificate (v_shipment_id IN integer) return number +IS v_order_id ec_orders.order_id%TYPE; - gift_cert_amount numeric; - past_ship_amount numeric; + gift_cert_amount number; + past_ship_amount number; BEGIN - SELECT into v_order_id order_id - FROM ec_shipments WHERE shipment_id=v_shipment_id; + SELECT order_id into v_order_id FROM ec_shipments WHERE shipment_id=v_shipment_id; gift_cert_amount := ec_order_gift_cert_amount(v_order_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; + 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; IF past_ship_amount > gift_cert_amount THEN return 0; ELSE - 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)); + 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)); END IF; -END;' language 'plpgsql'; +END; +/ +show errors ---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 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; +create or replace function ec_tax (v_price IN number, v_shipping IN number, v_order_id IN integer) return number +IS taxes ec_sales_tax_by_state%ROWTYPE; tax_exempt_p ec_orders.tax_exempt_p%TYPE; BEGIN - SELECT into tax_exempt_p tax_exempt_p + SELECT tax_exempt_p INTO 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; - 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; + IF nvl(taxes.shipping_p,'f') = 'f' THEN + return nvl(taxes.tax_rate,0) * v_price; ELSE - return coalesce(taxes.tax_rate,0) * (v_price + v_shipping); + return nvl(taxes.tax_rate,0) * (v_price + v_shipping); END IF; -END;' language 'plpgsql'; +END; +/ +show errors -- 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 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; +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; 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;' language 'plpgsql'; +END; +/ +show errors -- OK -- total shipment cost (price + shipping + tax - gift certificate) -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; +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; 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;' language 'plpgsql'; +END; +/ +show errors -- OK -- total amount refunded on an order so far -create function ec_total_refund (integer) -returns numeric as ' -DECLARE - v_order_id alias for $1; - v_order_refund numeric; - v_items_refund numeric; +create or replace function ec_total_refund (v_order_id IN integer) return number +IS + v_order_refund number; + v_items_refund number; BEGIN - 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; + 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; return v_order_refund + v_items_refund; -END;' language 'plpgsql'; +END; +/ +show errors -------------- end price calculations ----------------- ------------------------------------------------------- @@ -3031,115 +2850,145 @@ ----------- gift certificate procedures --------------- ------------------------------------------------------- --- 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 ' +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 DECLARE - bal_amount_used numeric; - original_amount numeric; + 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; BEGIN - 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 + 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 WHERE gift_certificate_id = gift_certificate_rec.gift_certificate_id; - return new; + END LOOP; + DELETE from ec_gift_cert_usage_ids; END; -' language 'plpgsql'; +/ +show errors -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 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 +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; BEGIN - 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 original_amount_subset_cursor; + FETCH original_amount_subset_cursor INTO original_amount; + CLOSE original_amount_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(); + OPEN amount_used_subset_cursor; + FETCH amount_used_subset_cursor INTO total_amount_used; + CLOSE amount_used_subset_cursor; RETURN original_amount - total_amount_used; -END;' language 'plpgsql'; +END; +/ +show errors + -- 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 function ec_order_amount_owed (integer) -returns numeric as ' -DECLARE - v_order_id alias for $1; - pre_gc_amount_owed numeric; - gc_amount numeric; +create or replace function ec_order_amount_owed (v_order_id IN integer) return number +IS + pre_gc_amount_owed number; + gc_amount number; 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;' language 'plpgsql'; +END; +/ +show errors -- OK -- the amount remaining in an individual gift certificate -create function gift_certificate_amount_left (integer) -returns numeric as ' -DECLARE - v_gift_certificate_id alias for $1; +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; + original_amount number; amount_used number; BEGIN - 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 amount_used_cursor; + FETCH amount_used_cursor INTO amount_used; + CLOSE amount_used_cursor; - SELECT amount - into original_amount - FROM ec_gift_certificates - WHERE gift_certificate_id = v_gift_certificate_id; + OPEN original_amount_cursor; + FETCH original_amount_cursor INTO original_amount; + CLOSE original_amount_cursor; RETURN original_amount - amount_used; -END;' language 'plpgsql'; +END; +/ +show errors -- I DON'T USE THIS PROCEDURE ANYMORE BECAUSE THERE'S A MORE -- FAULT-TOLERANT TCL VERSION @@ -3151,59 +3000,58 @@ -- 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 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 +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); + 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 function ec_reinst_gift_cert_on_order (integer) -returns integer as ' -DECLARE - v_order_id alias for $1; +create or replace procedure ec_reinst_gift_cert_on_order (v_order_id IN integer) +IS BEGIN insert into ec_gift_certificate_usage (gift_certificate_id, order_id, amount_reinstated, reinstated_date) - select gift_certificate_id, v_order_id, coalesce(sum(amount_used),0)-coalesce(sum(amount_reinstated),0), now() + select gift_certificate_id, v_order_id, nvl(sum(amount_used),0)-nvl(sum(amount_reinstated),0), sysdate 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 @@ -3214,18 +3062,15 @@ -- Call this before inserting the amounts that are being refunded -- into the database. -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; +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; BEGIN -- the maximum amount of cash refundable is equal to -- the amount paid (in cash + certificates) for shipped items only (since @@ -3234,9 +3079,9 @@ -- minus the current gift certificate amount applied to this order -- or 0 if the result is negative - 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 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 coalesce(shipping_charged,0) + coalesce(shipping_tax_charged,0) into order_amount_paid from ec_orders where order_id=v_order_id; + select nvl(shipping_charged,0) + nvl(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); @@ -3246,99 +3091,94 @@ cash_to_refund := least(max_cash_refundable, v_amount); RETURN cash_to_refund; -END;' language 'plpgsql'; +END; +/ +show errors; -- The amount of a given gift certificate used on a given order. -- This is a helper function for ec_gift_cert_unshipped_amount. -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; +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; BEGIN - select coalesce(sum(amount_used),0)-coalesce(sum(amount_reinstated),0) into bal_amount_used + select nvl(sum(amount_used),0)-nvl(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;' language 'plpgsql'; +END ec_one_gift_cert_on_one_order; +/ +show errors -- 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 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; +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; BEGIN - select coalesce(sum(u.amount_used),0)-coalesce(sum(u.amount_reinstated),0) into bal_amount_used + select nvl(sum(u.amount_used),0)-nvl(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;' language 'plpgsql'; +END; +/ +show errors -- 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 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; +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; BEGIN - 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 + 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 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); - ELSE - IF total_shipment_cost > earlier_certs + ec_one_gift_cert_on_one_order(v_gift_certificate_id, v_order_id) THEN + 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 + 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;' language 'plpgsql'; +END; +/ +show errors ---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, --- 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; +-- (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; BEGIN - select coalesce(sum(ec_cert_unshipped_one_order(v_gift_certificate_id,order_id)),0) into tied_but_unshipped_amount + select nvl(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;' language 'plpgsql'; +END; +/ +show errors; ---------- end gift certificate procedures ------------ ------------------------------------------------------- -\i pl-sql-utilities.sql -\i ec-product-package-create.sql -\i ecommerce-defaults.sql +@@ pl-sql-utilities.sql +@@ ec-product-package-create.sql +@@ ecommerce-defaults.sql