-- -- ecommerce.sql -- -- by eveander@arsdigita.com, April 1999 -- -- Besides the tables defined here, you also need to import -- zip_codes, which contains the following columns: -- ZIP_CODE NOT NULL VARCHAR2(10) -- STATE_CODE CHAR(2) -- CITY_NAME VARCHAR2(32) -- COUNTY_NAME VARCHAR2(32) -- LONGITUDE NUMBER(9,6) -- LATITUDE NUMBER(9,6) -- Each table in ecommerce has a column for user_id, ip_address, -- creation_date and last_modified to assist in auditing user -- inserts, updates, and deletes. -- Audit tables store information about entries in the main ec_ tables. -- They have an column for each column of the main table, plus -- a column for marking when a logged entry was for a deleted row. -- product display templates create sequence ec_template_id_sequence; -- 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 datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create table ec_templates_audit ( template_id integer, template_name varchar(200), template varchar(4000), last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); -- A trigger is used to move information from the main table to -- the audit table create function trig_ec_templates_audit_tr() returns opaque as ' declare 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 ); return new; end; ' language 'plpgsql'; create trigger ec_templates_audit_tr after update or delete on ec_templates for each row execute procedure trig_ec_templates_audit_tr(); -- This inserts the default template into the ec_templates table insert into ec_templates ( template_id, template_name, template, last_modified, last_modifying_user, modified_ip_address ) values ( 1,'Default', '' || '\n' || '<%= $product_name %>' || '\n' || '' || '\n' || '' || '\n' || '\n' || '

<%= $product_name %>

' || '\n' || '\n' || '' || '\n' || '' || '\n' || '' || '\n' || '' || '\n' || '' || '\n' || '
' || '\n' || ' ' || '\n' || ' ' || '\n' || ' ' || '\n' || ' ' || '\n' || ' ' || '\n' || '
<%= [ec_linked_thumbnail_if_it_exists $dirname] %>' || '\n' || ' <%= $one_line_description %>' || '\n' || '
' || '\n' || ' <%= [ec_price_line $db $product_id $user_id $offer_code] %>' || '\n' || '
' || '\n' || '		' || '\n' || '<%= [ec_add_to_cart_link $db $product_id] %>' || '\n' || '
' || '\n' || '\n' || '

' || '\n' || '<%= $detailed_description %>' || '\n' || '\n' || '<%= [ec_display_product_purchase_combinations $db $product_id] %>' || '\n' || '\n' || '<%= [ec_product_links_if_they_exist $db $product_id] %>' || '\n' || '\n' || '<%= [ec_professional_reviews_if_they_exist $db $product_id] %>' || '\n' || '\n' || '<%= [ec_customer_comments $db $product_id $comments_sort_by] %>' || '\n' || '\n' || '

' || '\n' || '\n' || '<%= [ec_mailing_list_link_for_a_product $db $product_id] %>' || '\n' || '\n' || '<%= [ec_footer $db] %>' || '\n' || '' || '\n' || '', current_timestamp, 1, 'none'); -- product categories and subcategories and subsubcategories 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 float4, last_modified datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create index ec_categories_sort_idx on ec_categories (sort_key); create table ec_categories_audit ( category_id integer, category_name varchar(100), sort_key float4, last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_categories_audit_tr() returns opaque as ' declare 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 ); return new; end; ' language 'plpgsql'; create trigger ec_categories_audit_tr after update or delete on ec_categories for each row execute procedure trig_ec_categories_audit_tr(); create sequence ec_subcategory_id_sequence; create table ec_subcategories ( subcategory_id integer not null primary key, category_id integer not null references ec_categories, -- pretty, human-readable subcategory_name varchar(100), sort_key float4, last_modified datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create index ec_subcategories_idx on ec_subcategories (category_id); create index ec_subcategories_idx2 on ec_subcategories (sort_key); create table ec_subcategories_audit ( subcategory_id integer, category_id integer, subcategory_name varchar(100), sort_key float4, last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_subcategories_audit_tr() returns opaque as ' declare 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 ); return new; end; ' language 'plpgsql'; create trigger ec_subcategories_audit_tr after update or delete on ec_subcategories for each row execute procedure trig_ec_subcategories_audit_tr(); -- a view with category_name also create view ec_subcategories_augmented as select subs.*, cats.category_name from ec_subcategories subs, ec_categories cats where subs.category_id = cats.category_id; create sequence ec_subsubcategory_id_sequence; create table ec_subsubcategories ( subsubcategory_id integer not null primary key, subcategory_id integer not null references ec_subcategories, -- pretty, human-readable subsubcategory_name varchar(100), sort_key float4, last_modified datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create index ec_subsubcategories_idx on ec_subsubcategories (subcategory_id); create index ec_subsubcategories_idx2 on ec_subsubcategories (sort_key); create table ec_subsubcategories_audit ( subsubcategory_id integer, subcategory_id integer, subsubcategory_name varchar(100), sort_key float4, last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_subsubcategories_audit_tr() returns opaque as ' declare 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 ); return new; end; ' language 'plpgsql'; create trigger ec_subsubcategories_audit_tr after update or delete on ec_subsubcategories for each row execute procedure trig_ec_subsubcategories_audit_tr(); -- a view with full subcategory and category info as well create view ec_subsubcategories_augmented as select subsubs.*, subs.subcategory_name, cats.category_id, cats.category_name from ec_subsubcategories subsubs, ec_subcategories subs, ec_categories cats where subsubs.subcategory_id = subs.subcategory_id and subs.category_id = cats.category_id; create sequence ec_product_id_sequence; -- This table contains the products and also the product series. -- A product series has the same fields as a product (it actually -- *is* a product, since it's for sale, has its own price, etc.). -- The only difference is that it has other products associated -- with it (that are part of it). So information about the -- whole series is kept in this table and the product_series_map -- table below keeps track of which products are inside each -- series. create table ec_products ( product_id integer not null primary key, sku varchar(100), product_name varchar(200), creation_date datetime default current_timestamp not null, one_line_description varchar(400), detailed_description varchar(4000), search_keywords varchar(4000), -- this is the regular price for the product. If user -- classes are charged a different price, it should be -- specified in ec_product_user_class_prices price numeric, -- leave this blank if shipping is calculated using -- one of the more complicated methods available shipping numeric, -- fill this in if shipping is calculated by: above price -- for first item (with this product_id), and the below -- price for additional items (with this product_id) shipping_additional numeric, -- fill this in if shipping is calculated using weight -- use whatever units you want (lbs/kg), just be consistent -- and make your shipping algorithm take the units into -- account weight float4, -- holds pictures, sample chapters, etc. dirname varchar(200), -- whether this item should show up in searches (e.g., if it's -- a volume of a series, you might not want it to) present_p char(1) check (present_p in ('f','t')) default 't', -- whether the item should show up at all in the user pages active_p char(1) check (active_p in ('f','t')) default 't', -- the date the product becomes available for sale (it can be listed -- before then, it's just not buyable) available_date datetime default current_timestamp not null, announcements varchar(4000), announcements_expire datetime, -- if there's a web site with more info about the product url varchar(300), template_id integer references ec_templates, -- o = out of stock, q = ships quickly, m = ships -- moderately quickly, s = ships slowly, i = in stock -- with no message about the speed of the shipment (shipping -- messages are in parameters .ini file) stock_status char(1) check (stock_status in ('o','q','m','s','i')), -- comma-separated lists of available colors, sizes, and styles for the user -- to choose upon ordering color_list varchar(4000), size_list varchar(4000), style_list varchar(4000), -- the user ID and IP address of the creator of the product last_modified datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create view ec_products_displayable as select * from ec_products where active_p='t'; create view ec_products_searchable as select * from ec_products where active_p='t' and present_p='t'; create table ec_products_audit ( product_id integer, product_name varchar(200), creation_date datetime, one_line_description varchar(400), detailed_description varchar(4000), search_keywords varchar(4000), price numeric, shipping numeric, shipping_additional numeric, weight float4, dirname varchar(200), present_p char(1) check (present_p in ('f','t')) default 't', active_p char(1) check (present_p in ('f','t')) default 't', available_date datetime, announcements varchar(4000), announcements_expire datetime, url varchar(300), template_id integer, stock_status char(1) check (stock_status in ('o','q','m','s','i')), last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_product_audit_tr() returns opaque as ' declare begin insert into ec_products_audit ( product_id, product_name, creation_date, one_line_description, detailed_description, search_keywords, shipping, shipping_additional, weight, dirname, present_p, active_p, available_date, announcements, announcements_expire, url, template_id, stock_status, last_modified, last_modifying_user, modified_ip_address ) values ( OLD.product_id, OLD.product_name, OLD.creation_date, OLD.one_line_description, OLD.detailed_description, OLD.search_keywords, OLD.shipping, OLD.shipping_additional, OLD.weight, OLD.dirname, OLD.present_p, OLD.active_p, OLD.available_date, OLD.announcements, OLD.announcements_expire, OLD.url, OLD.template_id, OLD.stock_status, OLD.last_modified, OLD.last_modifying_user, OLD.modified_ip_address ); return new; end; ' language 'plpgsql'; create trigger ec_products_audit_tr after update or delete on ec_products for each row execute procedure trig_ec_product_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 ); create index ec_product_purchase_comb_idx0 on ec_product_purchase_comb(product_0); create index ec_product_purchase_comb_idx1 on ec_product_purchase_comb(product_1); create index ec_product_purchase_comb_idx2 on ec_product_purchase_comb(product_2); create index ec_product_purchase_comb_idx3 on ec_product_purchase_comb(product_3); create index ec_product_purchase_comb_idx4 on ec_product_purchase_comb(product_4); create sequence ec_sale_price_id_sequence; 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 datetime not null, sale_ends datetime 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 datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create index ec_sale_prices_by_product_idx on ec_sale_prices(product_id); -- TODO: fix the date comparisons here. create view ec_sale_prices_current as select * from ec_sale_prices where current_timestamp>=sale_begins and current_timestamp<=sale_ends; create table ec_sale_prices_audit ( sale_price_id integer, product_id integer, sale_price numeric, sale_begins datetime, sale_ends datetime, sale_name varchar(30), offer_code varchar(20), last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_sale_prices_audit_tr() returns opaque as ' declare 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 ); return new; end; ' language 'plpgsql'; create trigger ec_sale_prices_audit_tr after update or delete on ec_sale_prices for each row execute procedure trig_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, -- 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, primary key (series_id, component_id), last_modified datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create index ec_product_series_map_idx2 on ec_product_series_map(component_id); create table ec_product_series_map_audit ( series_id integer, component_id integer, last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_product_series_map_audit_tr() returns opaque as ' declare 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 ); return new; end; ' language 'plpgsql'; create trigger ec_product_series_map_audit_tr after update or delete on ec_product_series_map for each row execute procedure trig_ec_product_series_map_audit_tr(); create sequence ec_address_id_sequence; create table ec_addresses ( address_id integer not null primary key, user_id integer not null references users, address_type varchar(20) not null, -- e.g., billing attn varchar(100), line1 varchar(100), line2 varchar(100), city varchar(100), -- state usps_abbrev char(2) references states, -- big enough to hold zip+4 with dash zip_code varchar(10), phone varchar(30), -- for international addresses country_code char(2) references country_codes(iso), -- this can be the province or region for an international address full_state_name varchar(30), -- D for day, E for evening phone_time varchar(10) ); create index ec_addresses_by_user_idx on ec_addresses (user_id); create sequence ec_creditcard_id_sequence; create table ec_creditcards ( creditcard_id integer not null primary key, user_id integer not null references users, -- Cybercash does not ask for this but we'll store it anyway creditcard_type char(1), -- no spaces; always 16 digits (oops; except for AMEX, which is 15) -- depending on admin settings, after we get success from Cybercash, -- we may bash this to NULL -- we make this a VARCHAR so it doesn't get padded by Oracle creditcard_number varchar(16), -- just the last four digits for subsequent UI creditcard_last_four char(4), -- ##/## creditcard_expire char(5), billing_zip_code varchar(80), -- if it ever failed (conclusively), set this to 't' so we -- won't give them the option of using it again failed_p char(1) check (failed_p in ('f','t')) default 'f' ); create index ec_creditcards_by_user_idx on ec_creditcards (user_id); create sequence ec_user_class_id_sequence; 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 datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create table ec_user_classes_audit ( user_class_id integer, user_class_name varchar(200), -- e.g., student last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_user_classes_audit_tr() returns opaque as ' declare 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 ); return new; end; ' language 'plpgsql'; create trigger ec_user_classes_audit_tr after update or delete on ec_user_classes for each row execute procedure trig_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, primary key (product_id, user_class_id), last_modified datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create index ec_product_user_class_idx on ec_product_user_class_prices(user_class_id); -- ec_product_user_class_prices_audit abbreviated as -- ec_product_u_c_prices_audit create table ec_product_u_c_prices_audit ( product_id integer, user_class_id integer, price numeric, last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_product_u_c_prices_audit_tr() returns opaque as ' declare 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 ); return new; end; ' language 'plpgsql'; create trigger ec_product_u_c_prices_audit_tr after update or delete on ec_product_user_class_prices for each row execute procedure trig_ec_product_u_c_prices_audit_tr(); create sequence ec_recommendation_id_sequence; create table ec_product_recommendations ( recommendation_id integer not null primary key, product_id integer not null references ec_products, -- might be null if the product is recommended for everyone user_class_id integer references ec_user_classes, -- html format recommendation_text varchar(4000), active_p char(1) check (active_p in ('f','t')) default 't', -- 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 datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create index ec_product_recommendation_idx on ec_product_recommendations(category_id); create index ec_product_recommendation_idx2 on ec_product_recommendations(subcategory_id); create index ec_product_recommendation_idx3 on ec_product_recommendations(subsubcategory_id); create index ec_product_recommendation_idx4 on ec_product_recommendations(user_class_id); create index ec_product_recommendation_idx5 on ec_product_recommendations(active_p); create table ec_product_recommend_audit ( recommendation_id integer, product_id integer, user_class_id integer, recommendation_text varchar(4000), active_p char(1) check (active_p in ('f','t')) default 't', category_id integer, subcategory_id integer, subsubcategory_id integer, last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_product_recommend_audit_tr() returns opaque as ' declare begin insert into ec_product_recommend_audit ( recommendation_id, product_id, user_class_id, recommendation_text, active_p, category_id, subcategory_id, subsubcategory_id, 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 ); return new; end; ' language 'plpgsql'; create trigger ec_product_recommend_audit_tr after update or delete on ec_product_recommendations for each row execute procedure trig_ec_product_recommend_audit_tr(); -- This is going to be a BIG problem for Postgres -- For now I'm commenting this out -- ben@mit.edu -- --create view ec_recommendations_cats_view as --select -- recs.*, -- COALESCE(cats.category_id,COALESCE(subs.category_id,subsubs.category_id)) as the_category_id, -- COALESCE(cats.category_name,COALESCE(subs.category_name,subsubs.category_name)) as the_category_name, -- COALESCE(subs.subcategory_id,subsubs.subcategory_id) as the_subcategory_id, -- COALESCE(subs.subcategory_name,subsubs.subcategory_name) as the_subcategory_name, -- subsubs.subsubcategory_id as the_subsubcategory_id, -- subsubs.subsubcategory_name as the_subsubcategory_name --from -- ec_product_recommendations recs, -- ec_categories cats, -- ec_subcategories_augmented subs, -- ec_subsubcategories_augmented subsubs --where -- recs.category_id = cats.category_id(+) --and -- recs.subcategory_id = subs.subcategory_id(+) --and -- recs.subsubcategory_id = subsubs.subcategory_id(+); -- one row per customer-user; all the extra info that the ecommerce -- system needs -- untested other than loading into psql dcwickstrom@earthlink.net -- 21 Feb, 2001. create view ec_recommendations_cats_view as select recs.*, (select (case when subs.subcategory_id is null then subsubs.subcategory_id else subs.subcategory_id end) from ec_subcategories_augmented subs, ec_subsubcategories_augmented subsubs where recs.subcategory_id = subs.subcategory_id and recs.subsubcategory_id = subsubs.subcategory_id) as the_subcategory_id, (select (case when subs.subcategory_name is null then subsubs.subcategory_name else subs.subcategory_name end) from ec_subcategories_augmented subs, ec_subsubcategories_augmented subsubs where recs.subcategory_id = subs.subcategory_id and recs.subsubcategory_id = subsubs.subcategory_id) as the_subcategory_name, (select (case when cats.category_id is null then (case when subs.category_id is null then subsubs.category_id else subs.category_id end) else cats.category_id end) from 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) as the_category_id, (select (case when cats.category_name is null then (case when subs.category_name is null then subsubs.category_name else subs.category_name end) else cats.category_name end) from 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) as the_category_name, (select subsubcategory_id from ec_subsubcategories_augmented where subcategory_id = recs.subsubcategory_id) as the_subsubcategory_id, (select subsubcategory_name from ec_subsubcategories_augmented where subcategory_id = recs.subsubcategory_id) as the_subsubcategory_name from ec_product_recommendations recs; create table ec_user_class_user_map ( user_id integer not null references users, user_class_id integer not null references ec_user_classes, primary key (user_id, user_class_id), user_class_approved_p char(1) check (user_class_approved_p in ('f','t')), last_modified datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create index ec_user_class_user_map_idx on ec_user_class_user_map (user_class_id); create index ec_user_class_user_map_idx2 on ec_user_class_user_map (user_class_approved_p); create table ec_user_class_user_map_audit ( user_id integer, user_class_id integer, user_class_approved_p char(1) check (user_class_approved_p in ('f','t')), last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_user_class_user_audit_tr() returns opaque as ' declare 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 ); return new; end; ' language 'plpgsql'; create trigger ec_user_class_user_audit_tr after update or delete on ec_user_class_user_map for each row execute procedure trig_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 datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null, primary key (product_a, product_b) ); create index ec_product_links_idx on ec_product_links (product_b); create table ec_product_links_audit ( product_a integer, product_b integer, last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_product_links_audit() returns opaque as ' declare 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 ); return new; end; ' language 'plpgsql'; create trigger ec_product_links_audit_tr after update or delete on ec_product_links for each row execute procedure trig_ec_product_links_audit(); create sequence ec_product_comment_id_sequence; -- 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, user_comment varchar(4000), one_line_summary varchar(300), rating float4, -- in some systems, the administrator will have to approve comments first approved_p char(1) check (approved_p is null or approved_p in ('f','t')), comment_date datetime, last_modified datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create index ec_product_comments_idx on ec_product_comments(product_id); create index ec_product_comments_idx2 on ec_product_comments(user_id); create index ec_product_comments_idx3 on ec_product_comments(approved_p); create table ec_product_comments_audit ( comment_id integer, product_id integer, user_id integer, user_comment varchar(4000), one_line_summary varchar(300), rating float4, approved_p char(1) check (approved_p is null or approved_p in ('f','t')), last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_product_comments_audit_tr() returns opaque as ' declare 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 ); return new; end; ' language 'plpgsql'; create trigger ec_product_comments_audit_tr after update or delete on ec_product_comments for each row execute procedure trig_ec_product_comments_audit_tr(); create sequence ec_product_review_id_sequence; -- 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, author_name varchar(100), publication varchar(100), review_date datetime, -- in HTML format review text, display_p char(1) check (display_p in ('f','t')), last_modified datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create index ec_product_reviews_idx on ec_product_reviews (product_id); create index ec_product_reviews_idx2 on ec_product_reviews (display_p); create table ec_product_reviews_audit ( review_id integer, product_id integer, author_name varchar(100), publication varchar(100), review_date datetime, -- in HTML format review text, display_p char(1) check (display_p in ('f','t')), last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_product_reviews_audit_tr() returns opaque as ' declare begin insert into ec_product_reviews_audit ( review_id, product_id, author_name, publication, review_date, review, display_p, 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 ); return new; end; ' language 'plpgsql'; create trigger ec_product_reviews_audit_tr after update or delete on ec_product_reviews for each row execute procedure trig_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 char(1) check (publisher_favorite_p is null or publisher_favorite_p in ('f','t')), last_modified datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null, primary key (product_id, category_id) ); create index ec_category_product_map_idx on ec_category_product_map (category_id); create index ec_category_product_map_idx2 on ec_category_product_map (publisher_favorite_p); create table ec_category_product_map_audit ( product_id integer, category_id integer, publisher_favorite_p char(1) check (publisher_favorite_p in ('f','t')), last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); -- ec_category_product_map_audit_tr abbreviated as -- ec_cat_prod_map_audit_tr create function trig_ec_cat_prod_map_audit_tr() returns opaque as ' declare 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 ); return new; end; ' language 'plpgsql'; create trigger ec_cat_prod_map_audit_tr after update or delete on ec_category_product_map for each row execute procedure trig_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 char(1) check (publisher_favorite_p in ('f','t')), last_modified datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null, primary key (product_id, subcategory_id) ); create index ec_subcat_product_map_idx on ec_subcategory_product_map (subcategory_id); create index ec_subcat_product_map_idx2 on ec_subcategory_product_map (publisher_favorite_p); -- ec_subcategory_product_map_audit abbreviated as create table ec_subcat_prod_map_audit ( product_id integer, subcategory_id integer, publisher_favorite_p char(1) check (publisher_favorite_p in ('f','t')), last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_subcat_prod_map_audit_tr() returns opaque as ' declare 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 ); return new; end; ' language 'plpgsql'; -- ec_subcat_prod_map_audit_tr create trigger ec_subcat_prod_map_audit_tr after update or delete on ec_subcategory_product_map for each row execute procedure trig_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 char(1) check (publisher_favorite_p in ('f','t')), last_modified datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null, primary key (product_id, subsubcategory_id) ); create index ec_subsubcat_prod_map_idx on ec_subsubcategory_product_map (subsubcategory_id); create index ec_subsubcat_prod_map_idx2 on ec_subsubcategory_product_map (publisher_favorite_p); create table ec_subsubcat_prod_map_audit ( product_id integer, subsubcategory_id integer, publisher_favorite_p char(1) check (publisher_favorite_p in ('f','t')), last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_subsubcat_prod_map_audit_tr() returns opaque as ' declare 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 ); return new; end; ' language 'plpgsql'; create trigger ec_subsubcat_prod_map_audit_tr after update or delete on ec_subsubcategory_product_map for each row execute procedure trig_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 -- will default to the one associated with its category (if -- the product is in more than 1 category, it'll get the -- template associated with one of its categories), although -- 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 ); create index ec_category_template_map_idx on ec_category_template_map (template_id); -- I could in theory make some hairy system that lets them specify -- what kind of form element each field will have, does -- error checking, etc., but I don't think it's necessary since it's -- just the site administrator using it. So here's a very simple -- table to store the custom product fields: create table ec_custom_product_fields ( field_identifier varchar(100) not null primary key, field_name varchar(100), default_value varchar(100), -- column type for oracle (i.e. text, varchar(50), integer, ...) column_type varchar(100), creation_date datetime, active_p char(1) check (active_p in ('f','t')) default 't', last_modified datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create table ec_custom_product_fields_audit ( field_identifier varchar(100), field_name varchar(100), default_value varchar(100), column_type varchar(100), creation_date datetime, active_p char(1) check (active_p in ('f','t')) default 't', last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_custom_prod_fields_audit_tr() returns opaque as ' declare begin insert into ec_custom_product_fields_audit ( field_identifier, field_name, default_value, column_type, creation_date, active_p, 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 ); return new; end; ' language 'plpgsql'; create trigger ec_custom_prod_fields_audit_tr after update or delete on ec_custom_product_fields for each row execute procedure trig_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 datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create table ec_custom_p_field_values_audit ( product_id integer, last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_custom_p_f_values_audit_tr() returns opaque as ' declare 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 ); return new; end; ' language 'plpgsql'; create trigger ec_custom_p_f_values_audit_tr after update or delete on ec_custom_product_field_values for each row execute procedure trig_ec_custom_p_f_values_audit_tr(); create sequence ec_user_session_sequence; create table ec_user_sessions ( user_session_id integer not null primary key, -- often will not be known user_id integer references users, ip_address varchar(20) not null, start_time datetime, 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, search_text varchar(200) ); create index ec_user_session_info_idx on ec_user_session_info (user_session_id); create index ec_user_session_info_idx2 on ec_user_session_info (product_id); create index ec_user_session_info_idx3 on ec_user_session_info (category_id); -- If a user comes to product.tcl with an offer_code in the url, -- I'm going to shove it into this table and then check this -- table each time I try to determine the price for the users' -- products. The alternative is to store the offer_codes in a -- cookie and look at that each time I try to determine the price -- 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, offer_code varchar(20) not null, primary key (user_session_id, product_id) ); -- create some indices create index ec_u_s_offer_codes_by_u_s_id on ec_user_session_offer_codes(user_session_id); create index ec_u_s_offer_codes_by_p_id on ec_user_session_offer_codes(product_id); create sequence ec_order_id_sequence; create table ec_orders ( 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, shipping_method varchar(20), -- express or standard shipping_address integer references ec_addresses(address_id), -- store credit card info in a different table 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 datetime, confirmed_date datetime, authorized_date datetime, voided_date datetime, expired_date datetime, -- 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, -- entered by customer service cs_comments varchar(4000), reason_for_void varchar(4000), voided_by integer references users, -- if the user chooses to save their shopping cart saved_p char(1) check (saved_p is null or saved_p in ('f','t')), check (user_id is not null or user_session_id is not null) ); create index ec_orders_by_user_idx on ec_orders (user_id); create index ec_orders_by_user_sess_idx on ec_orders (user_session_id); create index ec_orders_by_credit_idx on ec_orders (creditcard_id); create index ec_orders_by_addr_idx on ec_orders (shipping_address); create index ec_orders_by_conf_idx on ec_orders (confirmed_date); create index ec_orders_by_state_idx on ec_orders (order_state); -- 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 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 as select * from ec_orders where order_state in ('authorized_plus_avs','authorized_minus_avs','partially_fulfilled'); -- this is needed because orders might be only partially shipped create sequence ec_shipment_id_sequence; create table ec_shipments ( shipment_id integer not null primary key, order_id integer not null references ec_orders, -- usually, but not necessarily, the same as the shipping_address -- in ec_orders because a customer may change their address between -- shipments. -- a trigger fills address_id in automatically if it's null address_id integer not null references ec_addresses, shipment_date datetime not null, expected_arrival_date datetime, 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 datetime, -- arbitrary info from carrier, e.g., 'Joe Smith signed for it' actual_arrival_detail varchar(4000), last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20) ); create index ec_shipments_by_order_id on ec_shipments(order_id); create index ec_shipments_by_shipment_date on ec_shipments(shipment_date); -- fills address_id into ec_shipments if it's missing -- (using the shipping_address associated with the order) create function trig_ec_shipment_address_update_tr() returns opaque as ' declare v_address_id ec_addresses.address_id%TYPE; begin select shipping_address into v_address_id from ec_orders where order_id= NEW.order_id; IF NEW.address_id is null THEN NEW.address_id := v_address_id; return NEW; END IF; return new; end; ' language 'plpgsql'; create trigger ec_shipment_address_update_tr before insert on ec_shipments for each row execute procedure trig_ec_shipment_address_update_tr(); create table ec_shipments_audit ( shipment_id integer, order_id integer, address_id integer, shipment_date datetime, expected_arrival_date datetime, carrier varchar(50), tracking_number varchar(24), actual_arrival_date datetime, actual_arrival_detail varchar(4000), last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_shipments_audit_tr() returns opaque as ' declare begin insert into ec_shipments_audit ( shipment_id, order_id, address_id, shipment_date, expected_arrival_date, carrier, tracking_number, actual_arrival_date, actual_arrival_detail, 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 ); return new; end; ' language 'plpgsql'; create trigger ec_shipments_audit_tr after update or delete on ec_shipments for each row execute procedure trig_ec_shipments_audit_tr(); create sequence refund_id_sequence; create table ec_refunds ( refund_id integer not null primary key, order_id integer not null references ec_orders, -- not really necessary because it's in ec_financial_transactions refund_amount numeric not null, refund_date datetime not null, refunded_by integer not null references users, refund_reasons varchar(4000) ); create index ec_refunds_by_order_idx on ec_refunds (order_id); -- these are the items that make up each order create sequence ec_item_id_sequence; create table ec_items ( item_id integer not null primary key, product_id integer not null references ec_products, color_choice varchar(4000), size_choice varchar(4000), style_choice varchar(4000), order_id integer not null references ec_orders, shipment_id integer references ec_shipments, -- this is the date that user put this item into their shopping basket in_cart_date datetime, voided_date datetime, voided_by integer references users, expired_date datetime, item_state varchar(50) default 'in_basket', -- NULL if not received back received_back_date datetime, -- 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, -- 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, -- comments entered by customer service (CS) cs_comments varchar(4000) ); create index ec_items_by_product on ec_items(product_id); 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 as select * from ec_items where item_state in ('to_be_shipped', 'shipped', 'arrived'); create 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 as select * from ec_items where item_state in ('to_be_shipped'); -- This view displays: -- order_id -- shipment_date -- bal_price_charged sum(price_charged - price_refunded) for all items in the shipment -- bal_shipping_charged -- bal_tax_charged -- 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 -- DRB: this view is never used and blows out Postgres, which thinks -- it's too large even with a block size of (gulp) 16384! -- create view ec_items_numeric_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 -- from ec_items i, ec_shipments s -- where i.shipment_id=s.shipment_id -- and i.item_state <> 'void' -- group by i.order_id, i.shipment_id, s.shipment_date; -- a set of triggers to update order_state based on what happens -- to the items in the order -- partially_fulfilled: some but not all non-void items have shipped -- fulfilled: all non-void items have shipped -- returned: all non-void items received_back -- void: all items void -- We're not interested in partial returns. -- this is hellish because you can't select a count of the items -- in a given item_state from ec_items when you're updating ec_items, -- so we have to do a horrid "trio" (temporary table, row level trigger, -- system level trigger) as discussed in -- http://photo.net/doc/site-wide-search.html (we use a temporary -- table instead of a package because they're better) -- BMA for ACS/pg: heh heh heh, except in Postgres, things are much -- better, and we don't need this trio stuff. Which is quite convenient -- given that statement level triggers don't exist yet. create function trig_ec_order_state_after_tr() returns opaque as ' declare v_order_id integer; n_items integer; n_shipped_items integer; n_received_back_items integer; n_void_items integer; n_nonvoid_items integer; begin 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''; IF n_items = n_void_items THEN update ec_orders set order_state=''void'', voided_date=current_timestamp 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= NEW.order_id; END IF; END IF; END IF; END IF; RETURN NEW; end; ' language 'plpgsql'; create trigger ec_order_state_after_tr after update on ec_items for each row execute procedure trig_ec_order_state_after_tr(); -- this is a 1-row table -- it contains all settings that the admin can change from the admin pages -- most of the configuration is done using the parameters .ini file create table ec_admin_settings ( -- this is here just so that the insert statement (a page or -- 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, -- 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 datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create table ec_admin_settings_audit ( admin_setting_id integer, base_shipping_cost 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 datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_admin_settings_audit_tr() returns opaque as ' declare begin insert into ec_admin_settings_audit ( admin_setting_id, base_shipping_cost, default_shipping_per_item, weight_shipping_cost, add_exp_base_shipping_cost, add_exp_amount_per_item, add_exp_amount_by_weight, default_template, 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 ); return new; end; ' language 'plpgsql'; create trigger ec_admin_settings_audit_tr after update or delete on ec_admin_settings for each row execute procedure trig_ec_admin_settings_audit_tr(); -- put one row into ec_admin_settings so that I don't have to use 0or1row insert into ec_admin_settings ( admin_setting_id, default_template, last_modified, last_modifying_user, modified_ip_address ) values ( 1, 1, current_timestamp, 1, 'none'); -- this is populated by the rules the administrator sets in /admin/ecommerce/sales-tax.tcl create table ec_sales_tax_by_state ( usps_abbrev char(2) not null primary key references states, -- this a decimal number equal to the percentage tax divided by 100 tax_rate float4 not null, -- charge tax on shipping? shipping_p char(1) check (shipping_p in ('f','t')) not null, last_modified datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create table ec_sales_tax_by_state_audit ( usps_abbrev char(2), tax_rate float4, shipping_p char(1) check (shipping_p in ('f','t')), last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_sales_tax_by_state_audit_tr() returns opaque as ' declare 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 ); return new; end; ' language 'plpgsql'; create trigger ec_sales_tax_by_state_audit_tr after update on ec_sales_tax_by_state for each row execute procedure trig_ec_sales_tax_by_state_audit_tr(); -- these tables are used if MultipleRetailersPerProductP is 1 in the -- parameters .ini file create sequence ec_retailer_sequence; create table ec_retailers ( retailer_id integer not null primary key, retailer_name varchar(300), primary_contact_name varchar(100), secondary_contact_name varchar(100), primary_contact_info varchar(4000), secondary_contact_info varchar(4000), line1 varchar(100), line2 varchar(100), city varchar(100), -- state usps_abbrev char(2) references states, -- big enough to hold zip+4 with dash zip_code varchar(10), phone varchar(30), fax varchar(30), -- for international addresses country_code char(2) references country_codes(iso), --national, local, international reach varchar(15) check (reach in ('national','local','international','regional','web')), url varchar(200), -- space-separated list of states in which tax must be collected 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 datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create table ec_retailers_audit ( retailer_id integer, retailer_name varchar(300), primary_contact_name varchar(100), 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), financing_policy varchar(4000), return_policy varchar(4000), price_guarantee_policy varchar(4000), delivery_policy varchar(4000), installation_policy varchar(4000), last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_retailers_audit_tr() returns opaque as ' declare begin insert into ec_retailers_audit ( retailer_id, retailer_name, primary_contact_name, secondary_contact_name, primary_contact_info, secondary_contact_info, line1, line2, city, usps_abbrev, zip_code, phone, fax, country_code, reach, url, nexus_states, financing_policy, return_policy, price_guarantee_policy, delivery_policy, installation_policy, 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 ); return new; end; ' language 'plpgsql'; create trigger ec_retailers_audit_tr after update or delete on ec_retailers for each row execute procedure trig_ec_retailers_audit_tr(); create sequence ec_retailer_location_sequence; create table ec_retailer_locations ( retailer_location_id integer not null primary key, retailer_id integer not null references ec_retailers, location_name varchar(300), primary_contact_name varchar(100), secondary_contact_name varchar(100), primary_contact_info varchar(4000), secondary_contact_info varchar(4000), line1 varchar(100), line2 varchar(100), city varchar(100), -- state usps_abbrev char(2) references states, -- big enough to hold zip+4 with dash zip_code varchar(10), phone varchar(30), fax varchar(30), -- for international addresses country_code char(2) references country_codes(iso), 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 datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create table ec_retailer_locations_audit ( retailer_location_id integer, retailer_id integer, location_name varchar(300), primary_contact_name varchar(100), 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), financing_policy varchar(4000), return_policy varchar(4000), price_guarantee_policy varchar(4000), delivery_policy varchar(4000), installation_policy varchar(4000), last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_retailer_locations_audit_tr() returns opaque as ' declare begin insert into ec_retailer_locations_audit ( retailer_location_id, retailer_id, location_name, primary_contact_name, secondary_contact_name, primary_contact_info, secondary_contact_info, line1, line2, city, usps_abbrev, zip_code, phone, fax, country_code, url, financing_policy, return_policy, price_guarantee_policy, delivery_policy, installation_policy, 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 ); return new; end; ' language 'plpgsql'; create trigger ec_retailer_locations_audit_tr after update or delete on ec_retailer_locations for each row execute procedure trig_ec_retailer_locations_audit_tr(); create sequence ec_offer_sequence; 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, store_sku integer, retailer_premiums varchar(500), price numeric not null, shipping numeric, shipping_unavailable_p char(1) check (shipping_unavailable_p in ('f','t')), -- o = out of stock, q = ships quickly, m = ships -- moderately quickly, s = ships slowly, i = in stock -- with no message about the speed of the shipment (shipping -- messages are in parameters .ini file) stock_status char(1) check (stock_status in ('o','q','m','s','i')), special_offer_p char(1) check (special_offer_p in ('f','t')), special_offer_html varchar(500), offer_begins datetime not null, offer_ends datetime not null, deleted_p char(1) check (deleted_p in ('f','t')) default 'f', last_modified datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create view ec_offers_current as select * from ec_offers where deleted_p='f' and current_timestamp>= offer_begins and current_timestamp<= offer_ends; create table ec_offers_audit ( offer_id integer, product_id integer, retailer_location_id integer, store_sku integer, retailer_premiums varchar(500), price numeric, shipping numeric, shipping_unavailable_p char(1) check (shipping_unavailable_p in ('f','t')), stock_status char(1) check (stock_status in ('o','q','m','s','i')), special_offer_p char(1) check (special_offer_p in ('f','t')), special_offer_html varchar(500), offer_begins datetime, offer_ends datetime, deleted_p char(1) check (delete_p in ('f','t')) default 'f', last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), -- This differs from the deleted_p column! -- deleted_p refers to the user request to stop offering -- delete_p indicates the row has been deleted from the main offers table delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_offers_audit_tr() returns opaque as ' declare begin insert into ec_offers_audit ( offer_id, product_id, retailer_location_id, store_sku, retailer_premiums, price, shipping, shipping_unavailable_p, stock_status, special_offer_p, special_offer_html, offer_begins, offer_ends, deleted_p, 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 ); return new; end; ' language 'plpgsql'; create trigger ec_offers_audit_tr after update or delete on ec_offers for each row execute procedure trig_ec_offers_audit_tr(); -- Gift certificate stuff ---- ------------------------------ create sequence ec_gift_cert_id_sequence; create table ec_gift_certificates ( gift_certificate_id integer primary key, gift_certificate_state varchar(50) not null, amount numeric not null, -- a trigger will update this to f if the -- entire amount is used up (to speed up -- queries) amount_remaining_p char(1) check (amount_remaining_p in ('f','t')) default 't', issue_date datetime, authorized_date datetime, claimed_date datetime, -- customer service rep who issued it issued_by integer references users, -- customer who purchased it purchased_by integer references users, expires datetime, user_id integer references users, -- if it's unclaimed, claim_check will be filled in, -- and user_id won't be filled in -- claim check should be unique (one way to do this -- is to always begin it with "$gift_certificate_id-") claim_check varchar(50), certificate_message varchar(200), certificate_to varchar(100), certificate_from varchar(100), recipient_email varchar(100), voided_date datetime, voided_by integer references users, reason_for_void varchar(4000), last_modified datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null, check (user_id is not null or claim_check is not null) ); create index ec_gc_by_state on ec_gift_certificates(gift_certificate_state); create index ec_gc_by_amount_remaining on ec_gift_certificates(amount_remaining_p); create index ec_gc_by_user on ec_gift_certificates(user_id); create index ec_gc_by_claim_check on ec_gift_certificates(claim_check); -- note: there's a trigger in ecommerce-plsql.sql which updates amount_remaining_p -- when a gift certificate is used -- note2: there's a 1-1 correspondence between user-purchased gift certificates -- and financial transactions. ec_financial_transactions stores the corresponding -- gift_certificate_id. create view ec_gift_certificates_approved as select * from ec_gift_certificates where gift_certificate_state in ('authorized','authorized_plus_avs','authorized_minus_avs'); create 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 as select * from ec_gift_certificates where gift_certificate_state in ('authorized') and issued_by is not null; create table ec_gift_certificates_audit ( gift_certificate_id integer, gift_certificate_state varchar(50), amount numeric, issue_date datetime, authorized_date datetime, issued_by integer, purchased_by integer, expires datetime, 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 datetime, voided_by integer, reason_for_void varchar(4000), last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_gift_certificates_audit_tr() returns opaque as ' declare begin insert into ec_gift_certificates_audit ( gift_certificate_id, amount, issue_date, authorized_date, issued_by, purchased_by, expires, user_id, claim_check, certificate_message, certificate_to, certificate_from, recipient_email, voided_date, voided_by, reason_for_void, 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 ); return new; end; ' language 'plpgsql'; create trigger ec_gift_certificates_audit_tr after update or delete on ec_gift_certificates for each row execute procedure trig_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 datetime, amount_reinstated numeric, reinstated_date datetime ); create index ec_gift_cert_by_id on ec_gift_certificate_usage (gift_certificate_id); --------- customer service -------------------- create sequence ec_issue_id_sequence; create sequence ec_action_id_sequence; create sequence ec_interaction_id_sequence; create sequence ec_user_ident_id_sequence; -- 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 datetime, user_id integer references users, email varchar(100), first_names varchar(100), last_name varchar(100), -- this is varchar(80) in community-core.sql, so I'll be consistent postal_code varchar(80), other_id_info varchar(2000) ); -- should index everything because this all columns may potentially -- be searched through every time a new interaction is recorded create index ec_user_ident_by_user_id on ec_user_identification(user_id); create index ec_user_ident_by_email on ec_user_identification(email); create index ec_user_ident_by_first_names on ec_user_identification(first_names); create index ec_user_ident_by_last_name on ec_user_identification(last_name); create index ec_user_ident_by_postal_code on ec_user_identification(postal_code); -- puts date_added into ec_user_identification if it's missing create function trig_ec_user_identificate_date_tr() returns opaque as ' declare begin IF NEW.date_added is null THEN NEW.date_added := current_timestamp; RETURN NEW; END IF; return new; end; ' language 'plpgsql'; create trigger ec_user_identificate_date_tr before insert on ec_user_identification for each row execute procedure trig_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 datetime, interaction_originator varchar(20) not null, -- e.g. customer, customer-service-rep, automatic 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) ); create index ec_csin_by_user_ident_id on ec_customer_serv_interactions(user_identification_id); create function trig_ec_cs_interaction_inserts() returns opaque as ' declare begin IF NEW.interaction_date is null THEN NEW.interaction_date := current_timestamp; return NEW; END IF; return new; end; ' language 'plpgsql'; create trigger ec_cs_interaction_inserts before insert on ec_customer_serv_interactions for each row execute procedure trig_ec_cs_interaction_inserts(); create view ec_customer_service_reps as select user_id, first_names, last_name, email, password, url, last_visit, second_to_last_visit, user_state from users 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, -- may be null if this issue isn't associated with an order order_id integer references ec_orders, gift_certificate_id integer references ec_gift_certificates, open_date datetime not null, close_date datetime, -- customer service reps who closed the issue closed_by integer references users, -- we never really delete issues deleted_p char(1) check (deleted_p in ('f','t')) default 'f' ); create index ec_csi_by_user_ident_id on ec_customer_service_issues(user_identification_id); create index ec_csi_by_open_date on ec_customer_service_issues(open_date); -- because an issue can have more than one issue_type create table ec_cs_issue_type_map ( issue_id integer not null references ec_customer_service_issues, issue_type varchar(40) not null -- e.g. billing, web site ); create index ec_csitm_by_issue_id on ec_cs_issue_type_map(issue_id); create index ec_csitm_by_issue_type on ec_cs_issue_type_map(issue_type); create function trig_ec_cs_issues_inserts() returns opaque as ' declare begin IF NEW.open_date is null THEN NEW.open_date := current_timestamp; return NEW; END IF; return new; end; ' language 'plpgsql'; create trigger ec_cs_issue_inserts before insert on ec_customer_service_issues for each row execute procedure trig_ec_cs_issues_inserts(); create table ec_customer_service_actions ( action_id integer not null primary key, issue_id integer not null references ec_customer_service_issues, interaction_id integer not null references ec_customer_serv_interactions, action_details varchar(4000), follow_up_required varchar(4000) ); 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, info_used varchar(100) not null ); create index ec_csaium_by_action_id on ec_cs_action_info_used_map(action_id); create index ec_csaium_by_info_used on ec_cs_action_info_used_map(info_used); -- this table contains picklist choices for the customer service data -- entry people 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 float4, last_modified datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create table ec_picklist_items_audit ( picklist_item_id integer, picklist_item varchar(100), picklist_name varchar(100), sort_key float4, last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_picklist_items_audit_tr() returns opaque as ' declare 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 ); return new; end; ' language 'plpgsql'; create trigger ec_picklist_items_audit_tr after update or delete on ec_picklist_items for each row execute procedure trig_ec_picklist_items_audit_tr(); -- Canned responses for customer support create sequence ec_canned_response_id_sequence; create table ec_canned_responses ( response_id integer not null primary key, one_line varchar(100) not null, response_text varchar(4000) not null ); ----------------------------------------------- -- templates 1-6 are pre-defined (see the insert statements below the -- table definitions) -- the wording of each can be changed at /admin/ecommerce/email-templates/ create sequence ec_email_template_id_sequence; create table ec_email_templates ( email_template_id integer not null primary key, title varchar(100), subject varchar(200), message varchar(4000), -- this lists the variable names that customer service can -- use in this particular email -- for their info only variables varchar(1000), -- for informational purposes only, when the email is -- sent when_sent varchar(1000), -- for customer service issues, this is a tcl list of all -- the issue_types that should be inserted into -- ec_cs_issue_type_map for the issue that will be created -- when the message is sent issue_type_list varchar(100), last_modified datetime not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create table ec_email_templates_audit ( email_template_id integer, title varchar(100), subject varchar(200), message varchar(4000), variables varchar(1000), when_sent varchar(1000), issue_type_list varchar(100), last_modified datetime, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) check (delete_p in ('f','t')) default 'f' ); create function trig_ec_email_templates_audit_tr() returns opaque as ' declare begin insert into ec_email_templates_audit ( email_template_id, title, subject, message, variables, when_sent, issue_type_list, 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 ); return new; end; ' language 'plpgsql'; create trigger ec_email_templates_audit_tr after update or delete on ec_email_templates for each row execute procedure trig_ec_email_templates_audit_tr(); -- The following templates are predefined. 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 -- /admin/ecommerce/email-templates/ -- -- email_template_id used for -- ----------------- --------- -- 1 new order -- 2 order shipped -- 3 delayed credit denied -- 4 new gift certificate order -- 5 gift certificate recipient -- 6 gift certificate order failure -- set scan off insert into ec_email_templates (email_template_id, title, subject, message, variables, when_sent, issue_type_list, last_modified, last_modifying_user, modified_ip_address) values (1, 'New Order', 'Your Order', 'Thank you for your order. We received your order' || '\n' || 'on confirmed_date_here.' || '\n' || '\n' || 'The following is your order information:' || '\n' || '\n' || 'item_summary_here' || '\n' || '\n' || 'Shipping Address:' || '\n' || 'address_here' || '\n' || '\n' || 'price_summary_here' || '\n' || '\n' || 'Thank you.' || '\n' || '\n' || 'Sincerely,' || '\n' || 'customer_service_signature_here', 'confirmed_date_here, address_here, item_summary_here, price_here, shipping_here, tax_here, total_here, customer_service_signature_here', 'This email will automatically be sent out after an order has been authorized.', '{new order}', current_timestamp, 1, 'none'); insert into ec_email_templates (email_template_id, title, subject, message, variables, when_sent, issue_type_list, last_modified, last_modifying_user, modified_ip_address) values (2, 'Order Shipped', 'Your Order Has Shipped', 'We shipped the following items on shipped_date_here:' || '\n' || '\n' || 'item_summary_here' || '\n' || '\n' || 'Your items were shipped to:' || '\n' || '\n' || 'address_here' || '\n' || '\n' || 'sentence_about_whether_this_completes_the_order_here' || '\n' || '\n' || 'You can track your package by accessing' || '\n' || '"Your Account" at system_url_here' || '\n' || '\n' || 'Sincerely,' || '\n' || 'customer_service_signature_here', 'shipped_date_here, item_summary_here, address_here, sentence_about_whether_this_completes_the_order_here, system_url_here, customer_service_signature_here', 'This email will automatically be sent out after an order or partial order has shipped.', '{order shipped}', current_timestamp, 1, 'none'); insert into ec_email_templates (email_template_id, title, subject, message, variables, when_sent, issue_type_list, last_modified, last_modifying_user, modified_ip_address) values (3, 'Delayed Credit Denied', 'Your Order', 'At this time we are not able to receive' || '\n' || 'authorization to charge your account. We' || '\n' || 'have saved your order so that you can come' || '\n' || 'back to system_url_here' || '\n' || 'and resubmit it.' || '\n' || '\n' || 'Please go to your shopping cart and' || '\n' || 'click on "Retrieve Saved Cart".' || '\n' || '\n' || 'Thank you.' || '\n' || '\n' || 'Sincerely,' || '\n' || 'customer_service_signature_here', 'system_url_here, customer_service_signature_here', 'This email will automatically be sent out after a credit card authorization fails if it didn''t fail at the time the user initially submitted their order.', 'billing', current_timestamp, 1, 'none'); insert into ec_email_templates (email_template_id, title, subject, message, variables, when_sent, issue_type_list, last_modified, last_modifying_user, modified_ip_address) values (4, 'New Gift Certificate Order', 'Your Order', 'Thank you for your gift certificate order at system_name_here!' || '\n' || '\n' || 'The gift certificate will be sent to:' || '\n' || '\n' || 'recipient_email_here' || '\n' || '\n' || 'Your order details:' || '\n' || '\n' || 'Gift Certificate certificate_amount_here' || '\n' || 'Shipping 0.00' || '\n' || 'Tax 0.00' || '\n' || '------------ ------------' || '\n' || 'TOTAL certificate_amount_here' || '\n' || '\n' || 'Sincerely,' || '\n' || 'customer_service_signature_here', 'system_name_here, recipient_email_here, certificate_amount_here, customer_service_signature_here', 'This email will be sent after a customer orders a gift certificate.', '{gift certificate}', current_timestamp, 1, 'none'); insert into ec_email_templates (email_template_id, title, subject, message, variables, when_sent, issue_type_list, last_modified, last_modifying_user, modified_ip_address) values (5, 'Gift Certificate Recipient', 'Gift Certificate', 'It''s our pleasure to inform you that someone' || '\n' || 'has purchased a gift certificate for you at' || '\n' || 'system_name_here!' || '\n' || '\n' || 'Use the claim check below to retrieve your gift' || '\n' || 'certificate at system_url_here' || '\n' || '\n' || 'amount_and_message_summary_here' || '\n' || '\n' || 'Claim Check: claim_check_here' || '\n' || '\n' || 'To redeem it, just go to' || '\n' || 'system_url_here' || '\n' || 'choose the items you wish to purchase,' || '\n' || 'and proceed to Checkout. You''ll then have' || '\n' || 'the opportunity to type in your claim code' || '\n' || 'and redeem your certificate! Any remaining' || '\n' || 'balance must be paid for by credit card.' || '\n' || '\n' || 'Sincerely,' || '\n' || 'customer_service_signature_here', 'system_name_here, system_url_here, amount_and_message_summary_here, claim_check_here, customer_service_signature_here', 'This is sent to recipients of gift certificates.', '{gift certificate}', current_timestamp, 1, 'none'); insert into ec_email_templates (email_template_id, title, subject, message, variables, when_sent, issue_type_list, last_modified, last_modifying_user, modified_ip_address) values (6, 'Gift Certificate Order Failure', 'Your Gift Certificate Order', 'We are sorry to report that the authorization' || '\n' || 'for the gift certificate order you placed' || '\n' || 'at system_name_here could not be made.' || '\n' || 'Your order has been canceled. Please' || '\n' || 'come back and try your order again at:' || '\n' || '\n' || 'system_url_here' || '\n' || 'For your records, here is the order' || '\n' || 'that you attempted to place:' || '\n' || '\n' || 'Would have been sent to: recipient_email_here' || '\n' || 'amount_and_message_summary_here' || '\n' || 'We apologize for the inconvenience.' || '\n' || 'Sincerely,' || '\n' || 'customer_service_signature_here', 'system_name_here, system_url_here, recipient_email_here, amount_and_message_summary_here, customer_service_signature_here', 'This is sent to customers who tried to purchase a gift certificate but got no immediate response from CyberCash and we found out later the auth failed.', '{gift certificate}', current_timestamp, 1, 'none'); -- set scan on -- users can sign up for mailing lists based on category, subcategory, -- or subsubcategory (the appropriate level of categorization on which -- to base mailing lists depends on how the site administrator has -- set up their system) -- 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" 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 ); create index ec_cat_mailing_list_idx on ec_cat_mailing_lists(user_id); create index ec_cat_mailing_list_idx2 on ec_cat_mailing_lists(category_id); create index ec_cat_mailing_list_idx3 on ec_cat_mailing_lists(subcategory_id); create index ec_cat_mailing_list_idx4 on ec_cat_mailing_lists(subsubcategory_id); create sequence ec_spam_id_sequence; create table ec_spam_log ( spam_id integer not null primary key, spam_date datetime, 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 datetime, last_visit_end_date datetime ); create index ec_spam_log_by_cat_mail_idx on ec_spam_log (mailing_list_category_id); create index ec_spam_log_by_cat_mail_idx2 on ec_spam_log (mailing_list_subcategory_id); create index ec_spam_log_by_cat_mail_idx3 on ec_spam_log (mailing_list_subsubcategory_id); create index ec_spam_log_by_user_cls_idx on ec_spam_log (user_class_id); create index ec_spam_log_by_product_idx on ec_spam_log (product_id); -- CREDIT CARD STUFF ------------------------ --------------------------------------------- create sequence ec_transaction_id_sequence; 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 -- 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, -- this refers to the purchase of a gift certificate, not the use of one gift_certificate_id integer references ec_gift_certificates, -- creditcard_id is in here even though order_id has a creditcard_id associated with -- it in case a different credit card is used for a refund or a partial shipment. -- a trigger fills the creditcard_id in if it's not specified creditcard_id integer not null references ec_creditcards, transaction_amount numeric not null, -- charge doesn't imply that a charge will actually occur; it's just -- an authorization to charge -- in the case of a refund, there's no such thing as an authorization -- 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') -- There's no need to set this for refunds. Refunds are always to be captured. to_be_captured_p char(1) check (to_be_captured_p is null or to_be_captured_p in ('f','t')), inserted_date datetime not null, authorized_date datetime, -- set when to_be_captured_p becomes 't'; used in cron jobs to_be_captured_date datetime, marked_date datetime, settled_date datetime, refunded_date datetime, refund_settled_date datetime, -- generated by us talking to Cybercash disputed_p char(1) check (disputed_p is null or disputed_p in ('f','t')), -- date on which we discovered the dispute dispute_discovery_date datetime, -- if the consumer's bank got his numeric back from us forcibly charged_back_p char(1) check (charged_back_p is null or charged_back_p in ('f','t')), -- if the transaction failed, this will keep the cron jobs from continuing -- to retry it failed_p char(1) check (failed_p in ('f','t')) default 'f', check ((order_id notnull) or (gift_certificate_id notnull)) ); create index ec_finan_trans_by_order_idx on ec_financial_transactions (order_id); create index ec_finan_trans_by_cc_idx on ec_financial_transactions (creditcard_id); create index ec_finan_trans_by_gc_idx on ec_financial_transactions (gift_certificate_id); -- 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 as select * from ec_financial_transactions where (transaction_type='charge' and to_be_captured_p='t' and failed_p='f') or (transaction_type='refund' and failed_p='f'); -- fills creditcard_id into ec_financial_transactions if it's missing -- (using the credit card associated with the order) create function trig_fin_trans_ccard_update_tr() returns opaque as ' declare v_creditcard_id ec_creditcards.creditcard_id%TYPE; begin IF NEW.order_id is not null THEN select creditcard_id into v_creditcard_id from ec_orders where order_id= NEW.order_id; IF NEW.creditcard_id is null THEN NEW.creditcard_id := v_creditcard_id; return NEW; END IF; END IF; return NEW; end; ' language 'plpgsql'; create trigger fin_trans_ccard_update_tr before insert on ec_financial_transactions for each row execute procedure trig_fin_trans_ccard_update_tr(); create table ec_cybercash_log ( transaction_id integer not null references ec_financial_transactions, -- The types of transactions that will be logged here are -- mauthonly, postauth, return, void, retry, query txn_attempted_type varchar(25), txn_attempted_time datetime, -- 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 -- when doing a query, ref-code never seems to be returned, -- batch-id is only returned when querying for settled or setlret). -- Note: when doing a non-query, there is no txn_type returned -- by CyberCash, so this value will be inserted by talk_to_cybercash -- procedure. The reason for doing this is consistency; for -- example the attempted transaction type may be mauthonly, but later -- when querying for this authorization, the txn_type is auth. So, -- auth will be inserted into txn_type when doing an mauthonly. txn_type varchar(25), -- we take the time returned by CyberCash and chop off the -- sub-second precision cc_time datetime, merch_txn varchar(25), cust_txn varchar(25), origin char(1), txn_status varchar(25), errloc varchar(25), errmsg varchar(200), aux_msg varchar(200), auth_code varchar(25), action_code varchar(25), avs_code varchar(3), ref_code varchar(25), batch_id varchar(25), amount numeric ); -- END CREDIT CARD STUFF ---------------------------- ----------------------------------------------------- -- this is to record any problems that may have occurred so that the site administrator -- can be alerted on the admin pages create sequence ec_problem_id_sequence; create table ec_problems_log ( problem_id integer not null primary key, problem_date datetime, problem_details varchar(4000), -- if it's related to an order order_id integer references ec_orders, -- if it's related to a gift certificate gift_certificate_id integer references ec_gift_certificates, resolved_date datetime, resolved_by integer references users ); -- keeps track of automatic emails (based on templates) that are sent out create table ec_automatic_email_log ( user_identification_id 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 datetime ); create index ec_auto_email_by_usr_id_idx on ec_automatic_email_log (user_identification_id); create index ec_auto_email_by_temp_idx on ec_automatic_email_log (email_template_id); create index ec_auto_email_by_order_idx on ec_automatic_email_log (order_id); create index ec_auto_email_by_shipment_idx on ec_automatic_email_log (shipment_id); create index ec_auto_email_by_gc_idx on ec_automatic_email_log (gift_certificate_id);