Index: openacs-4/packages/ecommerce/sql/postgresql/ec-product-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/sql/postgresql/ec-product-package-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/sql/postgresql/ec-product-package-create.sql 9 Jul 2001 23:44:19 -0000 1.1 @@ -0,0 +1,86 @@ +-- ecommerce/sql/ec-product-package-create.sql +-- +-- @author Walter McGinnis (wtem@olywa.net) +-- +-- @creation_date 2001-03-24 +-- +-- $ID:$ +-- +-- ec_product pl/sql package spec and body + +-- ported to OpenACS 4.x by Gilbert Wong (gwong@orchardlabs.com) + +-- note: this package doesn't handle custom fields... +-- in the tcl pages, you'll want to make sure that is handled separately + +-- gilbertw - PostgreSQL only supports 16 parameters +-- use an update in the tcl script on the returned object id +-- start a transaction for the new product creation +create function ec_product__new (integer,integer,integer,varchar,numeric,varchar,varchar,varchar,varchar,boolean,char,varchar,timestamp,varchar,varchar,varchar) +returns integer as ' +declare + new__product_id alias for $1; -- default null + new__creation_user alias for $2; -- default null + new__context_id alias for $3; -- default null (package_id) + new__product_name alias for $4; + new__price alias for $5; -- default null + new__sku alias for $6; -- default null + new__one_line_description alias for $7; -- default null + new__detailed_description alias for $8; -- default null + new__search_keywords alias for $9; -- default null + new__present_p alias for $10; -- default t + new__stock_status alias for $11; -- default o + new__dirname alias for $12; -- default null + new__available_date alias for $13; -- default sysdate + new__color_list alias for $14; -- default null + new__size_list alias for $15; -- default null + new__style_list alias for $16; -- default null + -- new__email_on_purchase_list alias for $17; -- default null + -- new__url alias for $17; -- default null + -- new__no_shipping_avail_p alias for $19; -- default f + -- new__shipping alias for $20; -- default null + -- new__shipping_additional alias for $21; -- default null + -- new__weight alias for $22; -- default null + -- new__active_p alias for $23; -- default t + -- new__template_id alias for $24; -- default null + -- new__announcements alias for $25; -- default null + -- new__announcements_expire alias for $26; -- default null + v_object_id integer; + begin + v_object_id := acs_object__new ( + new__product_id, + ''ec_product'', + now(), + new__creation_user, + null, + new__context_id + ); + insert into ec_products + (product_id, creation_date, last_modified, last_modifying_user, modified_ip_address, product_name, price, sku, one_line_description, detailed_description, search_keywords, present_p, stock_status, dirname, available_date, color_list, size_list, style_list) + values + (v_object_id, new__creation_date, new__creation_date, new__creation_user, new__creation_ip, new__product_name, new__price, new__sku, new__one_line_description, new__detailed_description, new__search_keywords, new__present_p, new__stock_status, new__dirname, new__available_date, new__color_list, new__size_list, new__style_list); + + -- to be used when PostgreSQL supports more than 16 parameters + -- insert into ec_products + -- (product_id, creation_date, last_modified, last_modifying_user, modified_ip_address, product_name, price, sku, one_line_description, detailed_description, search_keywords, present_p, stock_status, dirname, available_date, color_list, size_list, style_list, email_on_purchase_list, url, no_shipping_avail_p, shipping, shipping_additional, weight, active_p, template_id, announcements, announcements_expire) + -- values + -- (v_object_id, new__creation_date, new__creation_date, new__creation_user, new__creation_ip, new__product_name, new__price, new__sku, new__one_line_description, new__detailed_description, new__search_keywords, new__present_p, new__stock_status, new__dirname, new__available_date, new__color_list, new__size_list, new__style_list, new__email_on_purchase_list, new__url, new__no_shipping_avail_p, new__shipping, new__shipping_additional, new__weight, new__active_p, new__template_id, new__announcements, new__announcements_expire); + + return v_object_id; + +end;' language 'plpgsql'; + + +create function ec_product__delete (integer) +returns integer as ' +declare + delete__product_id alias for $1; +begin + delete from ec_products + where product_id = delete__product_id; + + PERFORM acs_object__delete(delete__product_id); + return 0; +end;' language 'plpgsql'; + + Index: openacs-4/packages/ecommerce/sql/postgresql/ec-product-package-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/sql/postgresql/ec-product-package-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/sql/postgresql/ec-product-package-drop.sql 9 Jul 2001 23:44:19 -0000 1.1 @@ -0,0 +1,12 @@ +-- ecommerce/sql/ec-product-package-drop.sql +-- +-- @author Walter McGinnis (wtem@olywa.net) +-- +-- @creation_date 2001-03-30 +-- +-- $ID:$ +-- +-- drop ec_product pl/sql package spec and body + +select drop_package('ec_product'); + Index: openacs-4/packages/ecommerce/sql/postgresql/ec-product-sws-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/sql/postgresql/ec-product-sws-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/sql/postgresql/ec-product-sws-drop.sql 9 Jul 2001 23:44:19 -0000 1.1 @@ -0,0 +1,50 @@ +-- ec-product-sws-drop.sql +-- +-- @author Walter McGinnis (wtem@olywa.net) +-- +-- @creation_date 2001-03-26 +-- +-- drop PL/SQL package for the ec_products object_type +-- and that sets up site-wide-search +-- +-- $ID:$ + +begin + acs_interface.remove_obj_type_impl ( + interface_name => 'sws_display' , + programming_language => 'pl/sql' , + object_type => 'ec_product' + ); + + acs_interface.remove_obj_type_impl ( + interface_name => 'sws_indexing' , + programming_language => 'pl/sql' , + object_type => 'ec_product' + ); +end; +/ + +drop package ec_product_sws; + +begin + pot_service.delete_obj_type_attr_value ( + package_key => 'ecommerce' , + object_type => 'ec_product' , + attribute => 'display_page' + ); + + pot_service.unregister_object_type ( + package_key => 'ecommerce' , + object_type => 'ec_product' + ); +end; +/ + +drop trigger ec_product_itrg; + +drop trigger ec_product_utrg; + +drop trigger ec_product_dtrg; + + + Index: openacs-4/packages/ecommerce/sql/postgresql/ec-product-sws-setup.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/sql/postgresql/ec-product-sws-setup.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/sql/postgresql/ec-product-sws-setup.sql 9 Jul 2001 23:44:19 -0000 1.1 @@ -0,0 +1,288 @@ +-- ec-product-sws-setup.sql +-- +-- @author Walter McGinnis (wtem@olywa.net) +-- +-- @creation_date 2001-03-24 +-- +-- create PL/SQL package for the ec_products object_type +-- and sets up site-wide-search +-- +-- $ID:$ + +-- note: this requires that you are using a version of the E-commerce package +-- that has ec_products as an object-type in ecommerce-create.sql +-- obviously, the site-wide-search package has to be setup, too! + +create or replace package ec_product_sws +is + function sws_title ( + object_id in acs_objects.object_id%type + ) return varchar2; + + function sws_url ( + object_id in acs_objects.object_id%type + ) return varchar2; + + function sws_summary ( + object_id in acs_objects.object_id%type + ) return varchar2; + + function sws_req_permission ( + object_id in acs_objects.object_id%type + ) return varchar2; + + function sws_site_node_id ( + object_id in acs_objects.object_id%type + ) return site_nodes.node_id%TYPE; + + function sws_application_id ( + object_id in acs_objects.object_id%type + ) return apm_packages.package_id%TYPE; + +-- procedure sws_index_proc ( +-- object_id in acs_objects.object_id%type, +-- bdata in out nocopy BLOB +-- ); + +end ec_product_sws; +/ +show errors + +create or replace package body ec_product_sws +is + function sws_title ( + object_id in acs_objects.object_id%type + ) return varchar2 + is + v_title varchar2(1000); + begin + + select product_name into v_title + from ec_products + where product_id = object_id; + + return v_title; + end; + +function sws_url ( + object_id in acs_objects.object_id%type + ) return varchar2 + is + url varchar2(3000); + begin + url := sws_service_interface.sws_url ( + object_id => object_id + ); + return url||to_char(object_id); + end; + + function sws_summary ( + object_id in acs_objects.object_id%type + ) return varchar2 + is + v_summary varchar2(4000); + begin + select one_line_description into v_summary + from ec_products + where product_id = object_id; + + return v_summary; + end sws_summary; + + -- permissions aren't explicitly used in the ecommerce package + -- at this time + function sws_req_permission ( + object_id in acs_objects.object_id%type + ) return varchar2 + is + begin + return null; + end; + + function sws_site_node_id ( + object_id in acs_objects.object_id%type + ) return site_nodes.node_id%TYPE + is + node_id site_nodes.node_id%type; + begin + node_id := sws_service_interface.sws_site_node_id ( + object_id => object_id + ); + return node_id; + end; + + function sws_application_id ( + object_id in acs_objects.object_id%type + ) return apm_packages.package_id%TYPE + is + v_application_id apm_packages.package_id%type; + begin + v_application_id := sws_service_interface.sws_application_id ( + object_id => object_id + ); + return v_application_id; + end; + + -- wtem@olywa.net, 2001-03-24 + -- there are no blobs for ec_products at this point + -- only detailed_description column varchar(4000) + -- and search_keywords column varchar(4000) + -- want to concat both and shove into blob + -- need to change "bdata in out nocopy blob" to actual copy i think + -- and figure out how to make sws search sws search acs_contents + -- instead of ec_products + -- unless sws searches all columns of ec_products + + +end ec_product_sws; +/ +show errors + +-- register the sws interface, etc + +begin + acs_interface.assoc_obj_type_with_interface ( + interface_name => 'sws_display' , + programming_language => 'pl/sql' , + object_type => 'ec_product', + object_type_imp => 'ec_product_sws' + ); +end; +/ +show errors + +-- need to correlate "where present_p = 't'" +-- to searchable_p into acs_contents +-- should check other sws enabled package that +-- don't store their content acs_contents +-- how they handle blob +-- what we would like to do is create a blob +-- by concatinating detailed_description and search_keywords +-- from ec_products +-- and shove it in acs_contents +-- the insert and update triggers below +-- need to be altered to handle the above scenario +create or replace trigger ec_product_itrg +after insert on ec_products +for each row +declare + v_string varchar2(4000); + v_bdata blob; + v_bdata2 blob; + v_size integer; +begin + insert into acs_contents ( + content_id, + searchable_p, + content + ) values ( + :new.product_id, + :new.present_p, + empty_blob() + ); + + v_string := (:new.product_name||' '||:new.one_line_description||' '||:new.detailed_description||' '||:new.search_keywords||' '||to_char(:new.price)); + + select content into v_bdata2 + from acs_contents + where content_id = :new.product_id for update; + + dbms_lob.createtemporary(v_bdata, TRUE); + + string_to_blob(v_string, v_bdata); + v_size := dbms_lob.getlength(v_bdata); + + if v_size > 0 then + dbms_lob.copy ( + dest_lob => v_bdata2, + src_lob => v_bdata, + amount => v_size + ); + end if; + end; +/ +show errors + +-- not sure about mime_type, nls_language +-- whether they should be included here +-- nls_language = :new.nls_language, +-- mime_type = :new.mime_type, + +create or replace trigger ec_product_utrg +after update on ec_products +for each row +declare + v_string varchar2(4000); + v_bdata blob; + v_bdata2 blob; + v_size integer; +begin + update acs_contents + set searchable_p = :new.present_p + where content_id = :new.product_id; + + v_string := (:new.product_name||' '||:new.one_line_description||' '||:new.detailed_description||' '||:new.search_keywords||' '||to_char(:new.price)); + + select content into v_bdata2 + from acs_contents + where content_id = :new.product_id for update; + + dbms_lob.createtemporary(v_bdata, TRUE); + + string_to_blob(v_string, v_bdata); + v_size := dbms_lob.getlength(v_bdata); + + if v_size > 0 then + dbms_lob.copy ( + dest_lob => v_bdata2, + src_lob => v_bdata, + amount => v_size + ); + end if; + +end; +/ +show errors + +create or replace trigger ec_product_dtrg +after delete on ec_products +for each row +declare +begin + delete from acs_contents + where content_id = :old.product_id; +end; +/ +show errors + +-- begin + -- acs_interface.assoc_obj_type_with_interface ( + -- interface_name => 'sws_indexing' , + -- programming_language => 'pl/sql' , + -- object_type => 'ec_product', + -- object_type_imp => 'ec_product_sws' +-- ); +-- end; +-- / +-- show errors + +begin + pot_service.register_object_type ( + package_key => 'ecommerce', + object_type => 'ec_product' + ); + + pot_service.set_obj_type_attr_value ( + package_key => 'ecommerce', + object_type => 'ec_product', + attribute => 'display_page', + attribute_value => 'product?product_id=' + ); + + sws_service.update_content_obj_type_info ('ec_product'); + sws_service.rebuild_index; +end; +/ +show errors + + Index: openacs-4/packages/ecommerce/sql/postgresql/ecommerce-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/sql/postgresql/ecommerce-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/sql/postgresql/ecommerce-create.sql 9 Jul 2001 23:44:19 -0000 1.1 @@ -0,0 +1,3344 @@ +-- +-- ecommerce-create.sql +-- +-- by eveander@arsdigita.com, April 1999 +-- and ported by Jerry Asher (jerry@theashergroup.com) +-- and Walter McGinnis (wtem@olywa.net) +-- +-- ported to OpenACS by Gilbert wong (gwong@orchardlabs.com) +-- July 2001 + +-- Besides the tables defined here, you also need to import +-- zip_codes, which contains the following columns: +-- 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_seq start 2; +create view ec_template_id_sequence as select nextval('ec_template_id_seq') as nextval; + +-- Helper stuff (ben@adida.net) +-- gilbertw - I pulled this from OpenACS 3.2.5 +-- there are a few calls to the Oracle least function +create function least(numeric,numeric) +returns numeric +as ' +DECLARE + first alias for $1; + second alias for $2; +BEGIN + if first < second + then return first; + else return second; + end if; +END; +' language 'plpgsql'; + +-- I should have named this product_templates because now we +-- have other kinds of templates. + +create table ec_templates ( + template_id integer not null primary key, + template_name varchar(200), + template varchar(4000), + last_modified timestamp not null, + last_modifying_user integer not null references users, + modified_ip_address varchar(20) not null +); + +create table ec_templates_audit ( + template_id integer, + template_name varchar(200), + template varchar(4000), + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +-- A trigger is used to move information from the main table to +-- the audit table +create function ec_templates_audit_tr () +returns opaque as ' +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 +before update or delete on ec_templates +for each row execute procedure ec_templates_audit_tr (); + +-- this is where the ec_templates insert was + +-- product categories and subcategories and subsubcategories +create sequence ec_category_id_seq; +create view ec_category_id_sequence as select nextval('ec_category_id_seq') as nextval; + +create table ec_categories ( + category_id integer not null primary key, + -- pretty, human-readable + category_name varchar(100), + sort_key numeric, + last_modified timestamp not null, + last_modifying_user integer not null references users, + 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 numeric, + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +create function ec_categories_audit_tr () +returns opaque as ' +begin + insert into ec_categories_audit ( + category_id, category_name, sort_key, + last_modified, + last_modifying_user, modified_ip_address + ) values ( + old.category_id, old.category_name, old.sort_key, + old.last_modified, + old.last_modifying_user, old.modified_ip_address + ); + return new; +end;' language 'plpgsql'; + +create trigger ec_categories_audit_tr +before update or delete on ec_categories +for each row execute procedure ec_categories_audit_tr (); + +create sequence ec_subcategory_id_seq; +create view ec_subcategory_id_sequence as select nextval('ec_subcategory_id_seq') as nextval; + + +create table ec_subcategories ( + subcategory_id integer not null primary key, + category_id integer not null references ec_categories, + -- pretty, human-readable + subcategory_name varchar(100), + sort_key numeric, + last_modified timestamp not null, + last_modifying_user integer not null references users, + 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 numeric, + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +create function ec_subcategories_audit_tr () +returns opaque as ' +begin + insert into ec_subcategories_audit ( + subcategory_id, category_id, + subcategory_name, sort_key, + last_modified, + last_modifying_user, modified_ip_address + ) values ( + old.subcategory_id, old.category_id, + old.subcategory_name, old.sort_key, + old.last_modified, + old.last_modifying_user, old.modified_ip_address + ); + return new; +end;' language 'plpgsql'; + +create trigger ec_subcategories_audit_tr +before update or delete on ec_subcategories +for each row execute procedure ec_subcategories_audit_tr (); + +-- a view with category_name also + +create view ec_subcategories_augmented +as +select subs.*, cats.category_name +from ec_subcategories subs, ec_categories cats +where subs.category_id = cats.category_id; + +create sequence ec_subsubcategory_id_seq; +create view ec_subsubcategory_id_sequence as select nextval('ec_subsubcategory_id_seq') as nextval; + +create 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 numeric, + last_modified timestamp not null, + last_modifying_user integer not null references users, + modified_ip_address varchar(20) not null +); + +create index ec_subsubcategories_idx on ec_subsubcategories (subcategory_id); +create index ec_subsubcategories_idx2 on ec_subsubcategories (sort_key); + + +create table ec_subsubcategories_audit ( + subsubcategory_id integer, + subcategory_id integer, + subsubcategory_name varchar(100), + sort_key numeric, + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +create function ec_subsubcategories_audit_tr () +returns opaque as ' +begin + insert into ec_subsubcategories_audit ( + subsubcategory_id, subcategory_id, + subsubcategory_name, sort_key, + last_modified, + last_modifying_user, modified_ip_address + ) values ( + old.subsubcategory_id, old.subcategory_id, + old.subsubcategory_name, old.sort_key, + old.last_modified, + old.last_modifying_user, old.modified_ip_address + ); + return new; +end;' language 'plpgsql'; + +create trigger ec_subsubcategories_audit_tr +before update or delete on ec_subsubcategories +for each row execute procedure ec_subsubcategories_audit_tr (); + +-- a view with full subcategory and category info as well +create 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; + + +-- this should be replaced by the object_id sequence +-- grep for it in files... +-- create sequence ec_product_id_sequence start 1; + +-- This table contains the products and also the product series. +-- A product series has the same fields as a product (it actually +-- *is* a product, since it's for sale, has its own price, etc.). +-- 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. + +-- wtem@olywa.net, 2001-03-24 +-- begin +-- acs_object_type__create_type ( +-- supertype => 'acs_object', +-- object_type => 'ec_product', +-- pretty_name => 'Product', +-- pretty_plural => 'Products', +-- table_name => 'EC_PRODUCTS', +-- id_column => 'PRODUCT_ID', +-- package_name => 'ECOMMERCE' +-- ); +-- end; +-- / +-- show errors; + +create function inline_0 () +returns integer as ' +begin + + PERFORM acs_object_type__create_type ( + ''ec_product'', + ''Product'', + ''Products'', + ''acs_object'', + ''ec_products'', + ''product_id'', + ''ecommerce'', + ''f'', + null, + null + ); + + return 0; + +end;' language 'plpgsql'; + +select inline_0 (); +drop function inline_0 (); + +-- wtem@olywa.net, 2001-03-24 +-- we aren't going to bother to define all the attributes of an ec_product type +-- for now, because we are just using it for site-wide-search anyway +-- we have a corresponding pl/sql package for the ec_product object_type +-- it can be found at ecommerce/sql/ec-product-package-create.sql +-- and is called at the end of this script +create table ec_products ( + product_id integer constraint ec_products_product_id_fk + references acs_objects(object_id) + on delete cascade + constraint ec_products_product_id_pk + primary key, + -- above changed by wtem@olywa.net, 2001-03-24 + -- integer not null primary key, + sku varchar(100), + product_name varchar(200), + creation_date timestamp default now() not null, + 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, + -- for stuff that can't be shipped like services + no_shipping_avail_p boolean default 'f', + -- leave this blank if shipping is calculated using + -- one of the more complicated methods available + shipping numeric, + -- fill this in if shipping is calculated by: above price + -- for first item (with this product_id), and the below + -- price for additional items (with this product_id) + shipping_additional numeric, + -- fill this in if shipping is calculated using weight + -- use whatever units you want (lbs/kg), just be consistent + -- and make your shipping algorithm take the units into + -- account + weight numeric, + -- holds pictures, sample chapters, etc. + dirname varchar(200), + -- whether this item should show up in searches (e.g., if it's + -- a volume of a series, you might not want it to) + present_p boolean default 't', + -- whether the item should show up at all in the user pages + active_p boolean default 't', + -- the date the product becomes available for sale (it can be listed + -- before then, it's just not buyable) + available_date timestamp default now() not null, + announcements varchar(4000), + announcements_expire timestamp, + -- 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), + -- email this list on purchase + email_on_purchase_list varchar(4000), + -- the user ID and IP address of the creator of the product + last_modified timestamp not null, + last_modifying_user integer not null references users, + 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 timestamp, + one_line_description varchar(400), + detailed_description varchar(4000), + search_keywords varchar(4000), + price numeric, + shipping numeric, + shipping_additional numeric, + weight numeric, + dirname varchar(200), + present_p boolean default 't', + active_p boolean default 't', + available_date timestamp, + announcements varchar(4000), + announcements_expire timestamp, + url varchar(300), + template_id integer, + stock_status char(1) check (stock_status in ('o','q','m','s','i')), + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +create function ec_products_audit_tr () +returns opaque as ' +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 +before update or delete on ec_products +for each row execute procedure ec_products_audit_tr (); + + +-- people who bought product_id also bought products 0 through +-- 4, where product_0 is the most frequently purchased, 1 is next, +-- etc. +create table ec_product_purchase_comb ( + product_id integer not null primary key references ec_products, + product_0 integer references ec_products, + product_1 integer references ec_products, + product_2 integer references ec_products, + product_3 integer references ec_products, + product_4 integer references ec_products +); + +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_seq start 1; +create view ec_sale_price_id_sequence as select nextval('ec_sale_price_id_seq') as nextval; + +create table ec_sale_prices ( + sale_price_id integer not null primary key, + product_id integer not null references ec_products, + sale_price numeric, + sale_begins timestamp not null, + sale_ends timestamp not null, + -- like Introductory Price or Sale Price or Special Offer + sale_name varchar(30), + -- if non-null, the user has to know this code to get the sale price + offer_code varchar(20), + last_modified timestamp not null, + last_modifying_user integer not null references users, + modified_ip_address varchar(20) not null +); + +create index ec_sale_prices_by_product_idx on ec_sale_prices(product_id); + +create view ec_sale_prices_current +as +select * from ec_sale_prices +where now() >= sale_begins +and now() <= sale_ends; + + +create table ec_sale_prices_audit ( + sale_price_id integer, + product_id integer, + sale_price numeric, + sale_begins timestamp, + sale_ends timestamp, + sale_name varchar(30), + offer_code varchar(20), + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + + +create function ec_sale_prices_audit_tr () +returns opaque as ' +begin + insert into ec_sale_prices_audit ( + sale_price_id, product_id, sale_price, + sale_begins, sale_ends, sale_name, offer_code, + last_modified, + last_modifying_user, modified_ip_address + ) values ( + old.sale_price_id, old.product_id, old.sale_price, + old.sale_begins, old.sale_ends, old.sale_name, old.offer_code, + old.last_modified, + old.last_modifying_user, old.modified_ip_address + ); + return new; +end;' language 'plpgsql'; + +create trigger ec_sale_prices_audit_tr +before update or delete on ec_sale_prices +for each row execute procedure ec_sale_prices_audit_tr (); + + +create table ec_product_series_map ( + -- this is the product_id of a product that happens to be + -- a series + series_id 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 timestamp 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 timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + + +create function ec_product_series_map_audit_tr () +returns opaque as ' +begin + insert into ec_product_series_map_audit ( + series_id, component_id, + last_modified, + last_modifying_user, modified_ip_address + ) values ( + old.series_id, old.component_id, + old.last_modified, + old.last_modifying_user, old.modified_ip_address + ); + return new; +end;' language 'plpgsql'; + +create trigger ec_product_series_map_audit_tr +before update or delete on ec_product_series_map +for each row execute procedure ec_product_series_map_audit_tr (); + + +create sequence ec_address_id_seq start 1; +create view ec_address_id_sequence as select nextval('ec_address_id_seq') as nextval; + +create table ec_addresses ( + address_id integer not null primary key, + user_id 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 + -- Jerry, we'll need to creat the states table as part of this + usps_abbrev char(2) references states, + -- big enough to hold zip+4 with dash + zip_code varchar(10), + phone varchar(30), + -- for international addresses + -- Jerry, same for country_codes + 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_seq start 1; +create view ec_creditcard_id_sequence as select nextval('ec_creditcard_id_seq') as nextval; + +create table ec_creditcards ( + creditcard_id integer not null primary key, + user_id 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 boolean default 'f' +); + +create index ec_creditcards_by_user_idx on ec_creditcards (user_id); + +create sequence ec_user_class_id_seq start 1; +create view ec_user_class_id_sequence as select nextval('ec_user_class_id_seq') as nextval; + +create table ec_user_classes ( + user_class_id integer not null primary key, + -- human-readable + user_class_name varchar(200), -- e.g., student + last_modified timestamp not null, + last_modifying_user integer not null references users, + modified_ip_address varchar(20) not null +); + +create table ec_user_classes_audit ( + user_class_id integer, + user_class_name varchar(200), -- e.g., student + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +create function ec_user_classes_audit_tr () +returns opaque as ' +begin + insert into ec_user_classes_audit ( + user_class_id, user_class_name, + last_modified, + last_modifying_user, modified_ip_address + ) values ( + old.user_class_id, old.user_class_name, + old.last_modified, + old.last_modifying_user, old.modified_ip_address + ); + return new; +end;' language 'plpgsql'; + +create trigger ec_user_classes_audit_tr +before update or delete on ec_user_classes +for each row execute procedure ec_user_classes_audit_tr (); + + +create table ec_product_user_class_prices ( + product_id integer not null references ec_products, + user_class_id integer not null references ec_user_classes, + price numeric, + last_modified timestamp not null, + last_modifying_user integer not null references users, + modified_ip_address varchar(20) not null, + primary key (product_id, user_class_id) +); + +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 timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +create function ec_product_u_c_prices_audit_tr () +returns opaque as ' +begin + insert into ec_product_u_c_prices_audit ( + product_id, user_class_id, + price, + last_modified, + last_modifying_user, modified_ip_address + ) values ( + old.product_id, old.user_class_id, + old.price, + old.last_modified, + old.last_modifying_user, old.modified_ip_address + ); + return new; +end;' language 'plpgsql'; + +create trigger ec_product_u_c_prices_audit_tr +before update or delete on ec_product_user_class_prices +for each row execute procedure ec_product_u_c_prices_audit_tr (); + + +create sequence ec_recommendation_id_seq start 1; +create view ec_recommendation_id_sequence as select nextval('ec_recommendation_id_seq') as nextval; + +create table ec_product_recommendations ( + recommendation_id integer not null primary key, + product_id integer not null references ec_products, + -- 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 boolean 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 timestamp 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 boolean default 't', + category_id integer, + subcategory_id integer, + subsubcategory_id integer, + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +create function ec_product_recommend_audit_tr () +returns opaque as ' +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 +before update or delete on ec_product_recommendations +for each row execute procedure ec_product_recommend_audit_tr (); + +create view ec_recommendations_cats_view as +select + recs.*, + coalesce(cats.category_id,subs.category_id,subsubs.category_id) as the_category_id, + coalesce(cats.category_name,subs.category_name,subsubs.category_name) as the_category_name, + coalesce(subs.subcategory_id,subsubs.subcategory_id) as the_subcategory_id, + coalesce(subs.subcategory_name,subsubs.subcategory_name) as the_subcategory_name, + subsubs.subsubcategory_id as the_subsubcategory_id, + subsubs.subsubcategory_name as the_subsubcategory_name +from + ec_product_recommendations recs + LEFT JOIN + ec_categories cats using (category_id) + LEFT JOIN + ec_subcategories_augmented subs using (subcategory_id) + LEFT JOIN + ec_subsubcategories_augmented subsubs on (recs.subsubcategory_id = subsubs.subcategory_id); + +-- ec_categories cats, +-- ec_subcategories_augmented subs, +-- ec_subsubcategories_augmented subsubs +-- where + -- recs.category_id = cats.category_id(+) +-- and + -- recs.subcategory_id = subs.subcategory_id(+) +-- and + -- recs.subsubcategory_id = subsubs.subcategory_id(+); + +-- one row per customer-user; all the extra info that the ecommerce +-- system needs + +create table ec_user_class_user_map ( + user_id 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 boolean, + last_modified timestamp 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 boolean, + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + + +create function ec_user_class_user_audit_tr () +returns opaque as ' +begin + insert into ec_user_class_user_map_audit ( + user_id, user_class_id, user_class_approved_p, + last_modified, + last_modifying_user, modified_ip_address + ) values ( + old.user_id, old.user_class_id, old.user_class_approved_p, + old.last_modified, + old.last_modifying_user, old.modified_ip_address + ); + return new; +end;' language 'plpgsql'; + +create trigger ec_user_class_user_audit_tr +before update or delete on ec_user_class_user_map +for each row execute procedure ec_user_class_user_audit_tr (); + + +-- this specifies that product_a links to product_b on the display page for product_a +create table ec_product_links ( + product_a integer not null references ec_products, + product_b integer not null references ec_products, + last_modified timestamp not null, + last_modifying_user integer not null references users, + modified_ip_address varchar(20) not null, + primary key (product_a, product_b) +); + +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 timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +create function ec_product_links_audit_tr () +returns opaque as ' +begin + insert into ec_product_links_audit ( + product_a, product_b, + last_modified, + last_modifying_user, modified_ip_address + ) values ( + old.product_a, old.product_b, + old.last_modified, + old.last_modifying_user, old.modified_ip_address + ); + return new; +end;' language 'plpgsql'; + +create trigger ec_product_links_audit_tr +before update or delete on ec_product_links +for each row execute procedure ec_product_links_audit_tr (); + +create sequence ec_product_comment_id_seq start 1; +create view ec_product_comment_id_sequence as select nextval('ec_product_comment_id_seq') as nextval; + +-- comments made by users on the products +create table ec_product_comments ( + comment_id integer not null primary key, + product_id integer not null references ec_products, + user_id integer not null references users, + user_comment varchar(4000), + one_line_summary varchar(300), + rating numeric, + -- in some systems, the administrator will have to approve comments first + approved_p boolean, + comment_date timestamp, + last_modified timestamp not null, + last_modifying_user integer not null references users, + 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 numeric, + approved_p boolean, + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +create function ec_product_comments_audit_tr () +returns opaque as ' +begin + insert into ec_product_comments_audit ( + comment_id, product_id, user_id, + user_comment, one_line_summary, rating, approved_p, + last_modified, + last_modifying_user, modified_ip_address + ) values ( + old.comment_id, old.product_id, old.user_id, + old.user_comment, old.one_line_summary, old.rating, old.approved_p, + old.last_modified, + old.last_modifying_user, old.modified_ip_address + ); + return new; +end;' language 'plpgsql'; + +create trigger ec_product_comments_audit_tr +before update or delete on ec_product_comments +for each row execute procedure ec_product_comments_audit_tr (); + + +create sequence ec_product_review_id_seq start 1; +create view ec_product_review_id_sequence as select nextval('ec_product_review_id_seq') as nextval; + +-- reviews made by professionals of the products +create table ec_product_reviews ( + review_id integer not null primary key, + product_id integer not null references ec_products, + author_name varchar(100), + publication varchar(100), + review_date timestamp, + -- in HTML format + review text, + display_p boolean, + last_modified timestamp not null, + last_modifying_user integer not null references users, + modified_ip_address varchar(20) not null +); + +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 timestamp, + -- in HTML format + review text, + display_p boolean, + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +create function ec_product_reviews_audit_tr () +returns opaque as ' +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 +before update or delete on ec_product_reviews +for each row execute procedure ec_product_reviews_audit_tr (); + + +-- a product can be in more than one category +create table ec_category_product_map ( + product_id integer not null references ec_products, + category_id integer not null references ec_categories, + publisher_favorite_p boolean, + last_modified timestamp not null, + last_modifying_user integer not null references users, + 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 boolean, + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +-- ec_category_product_map_audit_tr abbreviated as +-- ec_cat_prod_map_audit_tr +create function ec_cat_prod_map_audit_tr () +returns opaque as ' +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 +before update or delete on ec_category_product_map +for each row execute procedure ec_cat_prod_map_audit_tr (); + + +create table ec_subcategory_product_map ( + product_id integer not null references ec_products, + subcategory_id integer not null references ec_subcategories, + publisher_favorite_p boolean, + last_modified timestamp not null, + last_modifying_user integer not null references users, + 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 boolean, + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +-- ec_subcat_prod_map_audit_tr +create function ec_subcat_prod_map_audit_tr () +returns opaque as ' +begin + insert into ec_subcat_prod_map_audit ( + product_id, subcategory_id, + publisher_favorite_p, + last_modified, + last_modifying_user, modified_ip_address + ) values ( + old.product_id, old.subcategory_id, + old.publisher_favorite_p, + old.last_modified, + old.last_modifying_user, old.modified_ip_address + ); + return new; +end;' language 'plpgsql'; + +create trigger ec_subcat_prod_map_audit_tr +before update or delete on ec_subcategory_product_map +for each row execute procedure ec_subcat_prod_map_audit_tr (); + + +create table ec_subsubcategory_product_map ( + product_id integer not null references ec_products, + subsubcategory_id integer not null references ec_subsubcategories, + publisher_favorite_p boolean, + last_modified timestamp not null, + last_modifying_user integer not null references users, + modified_ip_address varchar(20) not null, + primary key (product_id, subsubcategory_id) +); + +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 boolean, + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +create function ec_subsubcat_prod_map_audit_tr () +returns opaque as ' +begin + insert into ec_subsubcat_prod_map_audit ( + product_id, subsubcategory_id, + publisher_favorite_p, + last_modified, + last_modifying_user, modified_ip_address + ) values ( + old.product_id, old.subsubcategory_id, + old.publisher_favorite_p, + old.last_modified, + old.last_modifying_user, old.modified_ip_address + ); + return new; +end;' language 'plpgsql'; + +create trigger ec_subsubcat_prod_map_audit_tr +before update or delete on ec_subsubcategory_product_map +for each row execute procedure ec_subsubcat_prod_map_audit_tr (); + +-- A template can have more than 1 category associated +-- with it, but a category can have at most one template. +-- When a product is added in a given category, its template +-- 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 timestamp, + active_p boolean default 't', + last_modified timestamp not null, + last_modifying_user integer not null references users, + modified_ip_address varchar(20) not null +); + +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 timestamp, + active_p boolean default 't', + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +create function ec_custom_prod_fields_audit_tr () +returns opaque as ' +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 +before update or delete on ec_custom_product_fields +for each row execute procedure ec_custom_prod_fields_audit_tr (); + +-- more columns are added to this table (by Tcl scripts) when the +-- administrator adds custom product fields +-- the columns in this table have the name of the field_identifiers +-- in ec_custom_product_fields +-- this table stores the values +create table ec_custom_product_field_values ( + product_id integer not null primary key references ec_products, + last_modified timestamp not null, + last_modifying_user integer not null references users, + modified_ip_address varchar(20) not null +); + +create table ec_custom_p_field_values_audit ( + product_id integer, + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +create function ec_custom_p_f_values_audit_tr () +returns opaque as ' +begin + insert into ec_custom_p_field_values_audit ( + product_id, + last_modified, + last_modifying_user, modified_ip_address + ) values ( + old.product_id, + old.last_modified, + old.last_modifying_user, old.modified_ip_address + ); + return new; +end;' language 'plpgsql'; + +create trigger ec_custom_p_f_values_audit_tr +before update or delete on ec_custom_product_field_values +for each row execute procedure ec_custom_p_f_values_audit_tr(); + + +create sequence ec_user_session_seq; +create view ec_user_session_sequence as select nextval('ec_user_session_seq') as nextval; + +create table ec_user_sessions ( + user_session_id integer not null constraint ec_session_id_pk primary key, + -- often will not be known + user_id integer references users, + ip_address varchar(20) not null, + start_time timestamp, + http_user_agent varchar(4000) +); + +create index ec_user_sessions_idx on ec_user_sessions(user_id); + +create table ec_user_session_info ( + user_session_id 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_seq start 3000000; +create view ec_order_id_sequence as select nextval('ec_order_id_seq') as nextval; + +create table ec_orders ( + order_id integer not null primary key, + -- can be null, until they've checked out or saved their basket + user_id integer references users, + user_session_id integer references ec_user_sessions, + order_state varchar(50) default 'in_basket' not null, + tax_exempt_p boolean default 'f', + shipping_method varchar(20), -- express or standard or pickup or 'no shipping' + 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 timestamp, + confirmed_date timestamp, + authorized_date timestamp, + voided_date timestamp, + expired_date timestamp, + -- base shipping, which is added to the amount charged for each item + shipping_charged 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 boolean + 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_seq; +create view ec_shipment_id_sequence as select nextval('ec_shipment_id_seq') as nextval; + +create table ec_shipments ( + shipment_id integer not null primary key, + order_id 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 timestamp not null, + expected_arrival_date timestamp, + carrier varchar(50), -- e.g., 'fedex' + tracking_number varchar(24), + -- only if we get confirmation from carrier that the goods + -- arrived on a specific date + actual_arrival_date timestamp, + -- arbitrary info from carrier, e.g., 'Joe Smith signed for it' + actual_arrival_detail varchar(4000), + -- for things that aren't really shipped like services + shippable_p boolean default 't', + last_modified timestamp, + 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 ec_shipment_address_update_tr () +returns opaque as ' +declare + v_address_id ec_addresses.address_id%TYPE; +begin + select into v_address_id shipping_address + from ec_orders where order_id=new.order_id; + IF new.address_id is null THEN + new.address_id := v_address_id; + END IF; + return new; +end;' language 'plpgsql'; + +create trigger ec_shipment_address_update_tr +before insert on ec_shipments +for each row execute procedure ec_shipment_address_update_tr (); + +create table ec_shipments_audit ( + shipment_id integer, + order_id integer, + address_id integer, + shipment_date timestamp, + expected_arrival_date timestamp, + carrier varchar(50), + tracking_number varchar(24), + actual_arrival_date timestamp, + actual_arrival_detail varchar(4000), + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +create function ec_shipments_audit_tr () +returns opaque as ' +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 +before update or delete on ec_shipments +for each row execute procedure ec_shipments_audit_tr (); + +create sequence refund_id_seq; +create view refund_id_sequence as select nextval('refund_id_seq') as nextval; + +create 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 timestamp not null, + refunded_by integer not null references users, + refund_reasons varchar(4000) +); + +create index ec_refunds_by_order_idx on ec_refunds (order_id); + +-- these are the items that make up each order +create sequence ec_item_id_seq start 1; +create view ec_item_id_sequence as select nextval('ec_item_id_seq') as nextval; + +create table ec_items ( + item_id integer not null primary key, + order_id integer not null references ec_orders, + product_id integer not null references ec_products, + color_choice varchar(4000), + size_choice varchar(4000), + style_choice varchar(4000), + shipment_id integer references ec_shipments, + -- this is the date that user put this item into their shopping basket + in_cart_date timestamp, + voided_date timestamp, + voided_by integer references users, + expired_date timestamp, + item_state varchar(50) default 'in_basket', + -- NULL if not received back + received_back_date timestamp, + -- 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 + +-- gilbertw - there is a note in OpenACS 3.2.5 from DRB: +-- DRB: this view is never used and blows out Postgres, which thinks +-- it's too large even with a block size of (gulp) 16384! +-- gilbertw - this view is used now. + +create view ec_items_money_view +as +select i.shipment_id, i.order_id, s.shipment_date, 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) + +-- I. temporary table to hold the order_ids that have to have their +-- state updated as a result of the item_state changes + +-- gilbertw - this table is not needed in PostgreSQL +--create global temporary table ec_state_change_order_ids ( +-- order_id integer +--); + +-- gilbertw - this trigger is not needed +-- II. row-level trigger which updates ec_state_change_order_ids +-- so we know which rows to update in ec_orders +-- create function ec_order_state_before_tr () +-- returns opaque as ' +-- begin +-- insert into ec_state_change_order_ids (order_id) values (new.order_id); +-- return new; +-- end;' language 'plpgsql'; + +-- create trigger ec_order_state_before_tr +-- before update on ec_items +-- for each row execute procedure ec_order_state_before_tr (); + +-- III. System level trigger to update all the rows that were changed +-- in the before trigger. + +-- gilbertw - I took the trigger procedure from OpenACS 3.2.5. +create function ec_order_state_after_tr () +returns opaque as ' +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=now() + where order_id=NEW.order_id; + ELSE + n_nonvoid_items := n_items - n_void_items; + IF n_nonvoid_items = n_received_back_items THEN + update ec_orders set order_state=''returned'' + where order_id=NEW.order_id; + ELSE + IF n_nonvoid_items = n_received_back_items + n_shipped_items THEN + update ec_orders set order_state=''fulfilled'' + where order_id=NEW.order_id; + ELSE + IF n_shipped_items >= 1 or n_received_back_items >=1 THEN + update ec_orders set order_state=''partially_fulfilled'' + where order_id=v_order_id; + END IF; + END IF; + END IF; + END IF; + return new; +end;' language 'plpgsql'; + +create trigger ec_order_state_after_tr +after update on ec_items +for each row execute procedure ec_order_state_after_tr (); + +-- this is a 1-row table +-- it contains all settings that the admin can change from the admin pages +-- most of the configuration is done using the parameters .ini file +-- wtem@olywa.net 03-10-2001 +-- the following two tables probably need an additional column to support subsites +-- in which case it will have multiple rows, one for each instance of ecommerce +-- since these are really parameters for the instance of ecommerce, +-- it might be better to move them to ad_parameters +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 timestamp not null, + last_modifying_user integer not null references users, + modified_ip_address varchar(20) not null +); + +create table ec_admin_settings_audit ( + admin_setting_id integer, + base_shipping_cost numeric, + default_shipping_per_item numeric, + weight_shipping_cost numeric, + add_exp_base_shipping_cost numeric, + add_exp_amount_per_item numeric, + add_exp_amount_by_weight numeric, + default_template integer, + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +create function ec_admin_settings_audit_tr () +returns opaque as ' +begin + insert into ec_admin_settings_audit ( + admin_setting_id, base_shipping_cost, default_shipping_per_item, + 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 +before update or delete on ec_admin_settings +for each row execute procedure ec_admin_settings_audit_tr (); + +-- this is where the ec_amdin_settings insert was + +-- this is populated by the rules the administrator sets in packages/ecommerce/www/admin]/sales-tax.tcl +create table ec_sales_tax_by_state ( + -- Jerry + usps_abbrev char(2) not null primary key references states, + -- this a decimal number equal to the percentage tax divided by 100 + tax_rate numeric not null, + -- charge tax on shipping? + shipping_p boolean not null, + last_modified timestamp not null, + last_modifying_user integer not null references users, + modified_ip_address varchar(20) not null +); + +create table ec_sales_tax_by_state_audit ( + usps_abbrev char(2), + tax_rate numeric, + shipping_p boolean, + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + + +-- Jerry - I removed usps_abbrev and/or state here +create function ec_sales_tax_by_state_audit_tr () +returns opaque as ' +begin + insert into ec_sales_tax_by_state_audit ( + usps_abbrev, tax_rate, + shipping_p, + last_modified, + last_modifying_user, modified_ip_address + ) values ( + old.usps_abbrev, old.tax_rate, + old.shipping_p, + old.last_modified, + old.last_modifying_user, old.modified_ip_address + ); + return new; +end;' language 'plpgsql'; + +create trigger ec_sales_tax_by_state_audit_tr +before update or delete on ec_sales_tax_by_state +for each row execute procedure ec_sales_tax_by_state_audit_tr (); + +-- these tables are used if MultipleRetailersPerProductP is 1 in the +-- parameters .ini file + +create sequence ec_retailer_seq start 1; +create view ec_retailer_sequence as select nextval('ec_retailer_seq') as nextval; + +create table ec_retailers ( + retailer_id integer not null primary key, + 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 + -- Jerry + 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 + -- Jerry + 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 timestamp 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 timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +-- Jerry - I removed usps_abbrev and/or state here +create function ec_retailers_audit_tr () +returns opaque as ' +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 +before update or delete on ec_retailers +for each row execute procedure ec_retailers_audit_tr (); + +create sequence ec_retailer_location_seq start 1; +create view ec_retailer_location_sequence as select nextval('ec_retailer_location_seq') as nextval; + +create 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 + -- Jerry + -- usps_abbrev reinstated by wtem@olywa.net + usps_abbrev char(2) references states, + -- big enough 0to hold zip+4 with dash + zip_code varchar(10), + phone varchar(30), + fax varchar(30), + -- for international addresses + -- Jerry + -- country_code reinstated by wtem@olywa.net + 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 timestamp 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 timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + + +-- Jerry - I removed usps_abbrev and/or state here +create function ec_retailer_locations_audit_tr () +returns opaque as ' +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 +before update or delete on ec_retailer_locations +for each row execute procedure ec_retailer_locations_audit_tr (); + + +create sequence ec_offer_seq start 1; +create view ec_offer_sequence as select nextval('ec_offer_seq') as nextval; + +create table ec_offers ( + offer_id integer not null primary key, + product_id integer not null references ec_products, + retailer_location_id integer not null references ec_retailer_locations, + store_sku integer, + retailer_premiums varchar(500), + price numeric not null, + shipping numeric, + shipping_unavailable_p boolean, + -- o = out of stock, q = ships quickly, m = ships + -- moderately quickly, s = ships slowly, i = in stock + -- with no message about the speed of the shipment (shipping + -- messages are in parameters .ini file) + stock_status char(1) check (stock_status in ('o','q','m','s','i')), + special_offer_p boolean, + special_offer_html varchar(500), + offer_begins timestamp not null, + offer_ends timestamp not null, + deleted_p boolean default 'f', + last_modified timestamp not null, + last_modifying_user integer not null references users, + modified_ip_address varchar(20) not null +); + +create view ec_offers_current +as +select * from ec_offers +where deleted_p='f' +and now() >= offer_begins +and now() <= 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 boolean, + stock_status char(1) check (stock_status in ('o','q','m','s','i')), + special_offer_p boolean, + special_offer_html varchar(500), + offer_begins timestamp, + offer_ends timestamp, + deleted_p boolean default 'f', + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + -- This differs from the deleted_p column! + -- deleted_p refers to the user request to stop offering + -- delete_p indicates the row has been deleted from the main offers table + delete_p boolean default 'f' +); + + +create function ec_offers_audit_tr () +returns opaque as ' +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 +before update or delete on ec_offers +for each row execute procedure ec_offers_audit_tr (); + +-- Gift certificate stuff ---- +------------------------------ + +create sequence ec_gift_cert_id_seq start 1000000; +create view ec_gift_cert_id_sequence as select nextval('ec_gift_cert_id_seq') as nextval; + +create 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 boolean default 't', + issue_date timestamp, + authorized_date timestamp, + claimed_date timestamp, + -- customer service rep who issued it + issued_by integer references users, + -- customer who purchased it + purchased_by integer references users, + expires timestamp, + user_id integer references users, + -- if it's unclaimed, claim_check will be filled in, + -- and user_id won't be filled in + -- claim check should be unique (one way to do this + -- 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 timestamp, + voided_by integer references users, + reason_for_void varchar(4000), + last_modified timestamp not null, + last_modifying_user integer not null references users, + modified_ip_address varchar(20) not null, + check (user_id is not null or claim_check is not null) +); + +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 timestamp, + authorized_date timestamp, + issued_by integer, + purchased_by integer, + expires timestamp, + user_id integer, + claim_check varchar(50), + certificate_message varchar(200), + certificate_to varchar(100), + certificate_from varchar(100), + recipient_email varchar(100), + voided_date timestamp, + voided_by integer, + reason_for_void varchar(4000), + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + + +create function ec_gift_certificates_audit_tr () +returns opaque as ' +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 +before update or delete on ec_gift_certificates +for each row execute procedure ec_gift_certificates_audit_tr (); + + +create table ec_gift_certificate_usage ( + gift_certificate_id integer not null references ec_gift_certificates, + order_id integer references ec_orders, + amount_used numeric, + used_date timestamp, + amount_reinstated numeric, + reinstated_date timestamp +); + +create index ec_gift_cert_by_id on ec_gift_certificate_usage (gift_certificate_id); + + +--------- customer service -------------------- + +create sequence ec_issue_id_seq; +create view ec_issue_id_sequence as select nextval('ec_issue_id_seq') as nextval; +create sequence ec_action_id_seq; +create view ec_action_id_sequence as select nextval('ec_action_id_seq') as nextval; +create sequence ec_interaction_id_seq; +create view ec_interaction_id_sequence as select nextval('ec_interaction_id_seq') as nextval; +create sequence ec_user_ident_id_seq; +create view ec_user_ident_id_sequence as select nextval('ec_user_ident_id_seq') as nextval; + +-- this contains the bits of info a cs rep uses to identify +-- a user +-- often user_id is not known and the customer service rep +-- will have to get other info in order to identify the user +create table ec_user_identification ( + user_identification_id integer not null primary key, + date_added timestamp, + user_id integer references users, + email varchar(100), + first_names varchar(100), + last_name varchar(100), + -- 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 ec_user_identificate_date_tr () +returns opaque as ' +begin + IF new.date_added is null THEN + new.date_added := now(); + 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 ec_user_identificate_date_tr (); + + +create table ec_customer_serv_interactions ( + interaction_id integer not null primary key, + customer_service_rep integer references users, + user_identification_id integer not null + references ec_user_identification, + interaction_date timestamp, + 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); + +-- gilbertw - used the code in OpenACS 3.2.5 as a reference +create function ec_cs_interaction_inserts () +returns opaque as ' +begin + IF new.interaction_date is null THEN + new.interaction_date := now(); + 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 ec_cs_interaction_inserts (); + +create view ec_customer_service_reps +as +select * from cc_users +where user_id in (select customer_service_rep + from ec_customer_serv_interactions) + 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, + -- may be null if this issue isn't associated with a gift certificate + gift_certificate_id integer references ec_gift_certificates, + open_date timestamp not null, + close_date timestamp, + -- customer service reps who closed the issue + closed_by integer references users, + -- we never really delete issues + deleted_p boolean default 'f' +); + +create index ec_csi_by_user_ident_id on ec_customer_service_issues(user_identification_id); +create index ec_csi_by_open_date on ec_customer_service_issues(open_date); + +-- because an issue can have more than one issue_type +create table ec_cs_issue_type_map ( + issue_id 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); + +-- gilbertw - used code OpenACS 3.2.5 as a reference +-- removed INSERTING +create function ec_cs_issue_inserts () +returns opaque as ' +begin + IF new.open_date is null THEN + new.open_date := now(); + 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 ec_cs_issue_inserts (); + +create table ec_customer_service_actions ( + action_id integer not null primary key, + issue_id integer not null references ec_customer_service_issues, + 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_seq; +create view ec_picklist_item_id_sequence as select nextval('ec_picklist_item_id_seq') as nextval; + +create table ec_picklist_items ( + picklist_item_id integer not null primary key, + -- pretty, human-readable + picklist_item varchar(100), + -- which picklist this item is in + picklist_name varchar(100), + sort_key numeric, + last_modified timestamp not null, + last_modifying_user integer not null references users, + modified_ip_address varchar(20) not null +); + +create table ec_picklist_items_audit ( + picklist_item_id integer, + picklist_item varchar(100), + picklist_name varchar(100), + sort_key numeric, + last_modified timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +create function ec_picklist_items_audit_tr () +returns opaque as ' +begin + insert into ec_picklist_items_audit ( + picklist_item_id, picklist_item, + picklist_name, sort_key, + last_modified, + last_modifying_user, modified_ip_address + ) values ( + old.picklist_item_id, old.picklist_item, + old.picklist_name, old.sort_key, + old.last_modified, + old.last_modifying_user, old.modified_ip_address + ); + return new; +end;' language 'plpgsql'; + +create trigger ec_picklist_items_audit_tr +before update or delete on ec_picklist_items +for each row execute procedure ec_picklist_items_audit_tr (); + +-- Canned responses for customer support +create sequence ec_canned_response_id_seq; +create view ec_canned_response_id_sequence as select nextval('ec_canned_response_id_seq') as nextval; + +create table ec_canned_responses ( + response_id integer not null primary key, + one_line varchar(100) not null, + response_text varchar(4000) not null +); + +----------------------------------------------- + +-- templates 1-6 are pre-defined (see the insert statements in +-- ecommerce-defaults.sql) +-- the wording of each can be changed at [ec_url_concat [ec_url] /admin]/email-templates/ +create sequence ec_email_template_id_seq start 7; +create view ec_email_template_id_sequence as select nextval('ec_email_template_id_seq') as nextval; + +create 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 timestamp 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 timestamp, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +create function ec_email_templates_audit_tr () +returns opaque as ' +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 +before update or delete on ec_email_templates +for each row execute procedure ec_email_templates_audit_tr (); + +-- 6 default templates are predefined ecommerce-defaults. +-- The templates are +-- used in procedures which send out the email, so the template_ids +-- shouldn t be changed, although the text can be edited at +-- [ec_url_concat [ec_url] /admin]/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 + + +-- 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_seq; +create view ec_spam_id_sequence as select nextval('ec_spam_id_seq') as nextval; + +create table ec_spam_log ( + spam_id integer not null primary key, + spam_date timestamp, + spam_text varchar(4000), + -- the following are all criteria used in choosing the users to be spammed + mailing_list_category_id integer references ec_categories, + mailing_list_subcategory_id integer references ec_subcategories, + mailing_list_subsubcategory_id integer references ec_subsubcategories, + user_class_id integer references ec_user_classes, + product_id integer references ec_products, + last_visit_start_date timestamp, + last_visit_end_date timestamp +); + +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_seq start 4000000; +create view ec_transaction_id_sequence as select nextval('ec_transaction_id_seq') as nextval; + +create table ec_financial_transactions ( + transaction_id integer not null primary key, + -- order_id or gift_certificate_id must be filled in + order_id 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, theres no such thing as an authorization + -- to refund, so the refund really will occur + transaction_type varchar(6) not null check (transaction_type in ('charge','refund')), + -- it starts out null, becomes t when we want to capture it, or becomes + -- f it is known that we don't want to capture the transaction (although + -- the f is mainly just for reassurance; we only capture ones with t) + -- There's no need to set this for refunds. Refunds are always to be captured. + to_be_captured_p boolean, + inserted_date timestamp not null, + authorized_date timestamp, + -- set when to_be_captured_p becomes 't'; used in cron jobs + to_be_captured_date timestamp, + marked_date timestamp, + settled_date timestamp, + refunded_date timestamp, + refund_settled_date timestamp, + -- generated by us talking to Cybercash + disputed_p boolean, + -- date on which we discovered the dispute + dispute_discovery_date timestamp, + -- if the consumer's bank got his money back from us forcibly + charged_back_p boolean, + -- if the transaction failed, this will keep the cron jobs from continuing + -- to retry it + failed_p boolean default 'f', + check (order_id is not null or gift_certificate_id is not null) +); + +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 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 into v_creditcard_id creditcard_id + from ec_orders where order_id=new.order_id; + IF new.creditcard_id is null THEN + new.creditcard_id := v_creditcard_id; + END IF; + END IF; + return new; +end;' language 'plpgsql'; + +create trigger fin_trans_ccard_update_tr +before insert on ec_financial_transactions +for each row execute procedure fin_trans_ccard_update_tr (); + +create table ec_cybercash_log ( + transaction_id 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 timestamp, + -- Everything below this line is returned by CyberCash. Note + -- that not all columns will have values (for instance, cc_time + -- is only returned when doing a query, aux_msg is not returned + -- 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 timestamp, + 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_seq; +create view ec_problem_id_sequence as select nextval('ec_problem_id_seq') as nextval; + +create table ec_problems_log ( + problem_id integer not null primary key, + problem_date timestamp, + 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 timestamp, + resolved_by integer references users +); + + +-- keeps track of automatic emails (based on templates) that are sent out +create table ec_automatic_email_log ( + user_identification_id integer not null references ec_user_identification, + email_template_id integer not null references ec_email_templates, + order_id integer references ec_orders, + shipment_id integer references ec_shipments, + gift_certificate_id integer references ec_gift_certificates, + date_sent timestamp +); + +create index ec_auto_email_by_usr_id_idx on ec_automatic_email_log (user_identification_id); +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); + + +-- +-- ecommerce-plsql.sql +-- +-- by eveander@arsdigita.com, April 1999 +-- + +--------------- price calculations ------------------- +------------------------------------------------------- + +-- just the price of an order, not shipping, tax, or gift certificates +-- this is actually price_charged minus price_refunded +create function ec_total_price (integer) +returns numeric as ' +DECLARE + v_order_id alias for $1; + price numeric; +BEGIN + select into price + coalesce(sum(price_charged),0) - coalesce(sum(price_refunded),0) + FROM ec_items + WHERE order_id=v_order_id + and item_state <> ''void''; + +END;' language 'plpgsql'; + + +-- just the shipping of an order, not price, tax, or gift certificates +-- this is actually total shipping minus total shipping refunded +create function ec_total_shipping (integer) +returns numeric as ' +DECLARE + v_order_id alias for $1; + order_shipping numeric; + item_shipping numeric; +BEGIN + select into order_shipping + coalesce(shipping_charged,0) - coalesce(shipping_refunded,0) + FROM ec_orders + WHERE order_id=v_order_id; + + select into item_shipping + coalesce(sum(shipping_charged),0) - coalesce(sum(shipping_refunded),0) + FROM ec_items + WHERE order_id=v_order_id + and item_state <> ''void''; + + return order_shipping + item_shipping; +END;' language 'plpgsql'; + +-- OK +-- just the tax of an order, not price, shipping, or gift certificates +-- this is tax minus tax refunded +create function ec_total_tax (integer) +returns numeric as ' +DECLARE + v_order_id alias for $1; + order_tax numeric; + item_price_tax numeric; + item_shipping_tax numeric; +BEGIN + select into order_tax + coalesce(shipping_tax_charged,0) - coalesce(shipping_tax_refunded,0) + FROM ec_orders + WHERE order_id=v_order_id; + + select into item_price_tax + coalesce(sum(price_tax_charged),0) - coalesce(sum(price_tax_refunded),0) + FROM ec_items + WHERE order_id=v_order_id + and item_state <> ''void''; + + select into item_shipping_tax + coalesce(sum(shipping_tax_charged),0) - coalesce(sum(shipping_tax_refunded),0) + FROM ec_items + WHERE order_id=v_order_id; + + return order_tax + item_price_tax + item_shipping_tax; +END;' language 'plpgsql'; + + +-- OK +-- just the price of a shipment, not shipping, tax, or gift certificates +-- this is the price charged minus the price refunded of the shipment +create function ec_shipment_price (integer) +returns numeric as ' +DECLARE + v_shipment_id alias for $1; + shipment_price numeric; +BEGIN + SELECT into shipment_price coalesce(sum(price_charged),0) - coalesce(sum(price_refunded),0) + FROM ec_items + WHERE shipment_id=v_shipment_id + and item_state <> ''void''; + + RETURN shipment_price; +END;' language 'plpgsql'; + +-- OK +-- just the shipping charges of a shipment, not price, tax, or gift certificates +-- note: the base shipping charge is always applied to the first shipment in an order. +-- this is the shipping charged minus the shipping refunded +create function ec_shipment_shipping (integer) +returns numeric as ' +DECLARE + v_shipment_id alias for $1; + item_shipping numeric; + base_shipping numeric; + v_order_id ec_orders.order_id%TYPE; + min_shipment_id ec_shipments.shipment_id%TYPE; +BEGIN + SELECT into v_order_id order_id + FROM ec_shipments where shipment_id=v_shipment_id; + SELECT into min_shipment_id min(shipment_id) + FROM ec_shipments where order_id=v_order_id; + IF v_shipment_id=min_shipment_id THEN + SELECT into base_shipping + coalesce(shipping_charged,0) - coalesce(shipping_refunded,0) + FROM ec_orders where order_id=v_order_id; + ELSE + base_shipping := 0; + END IF; + SELECT into item_shipping + coalesce(sum(shipping_charged),0) - coalesce(sum(shipping_refunded),0) + FROM ec_items where shipment_id=v_shipment_id + and item_state <> ''void''; + RETURN item_shipping + base_shipping; +END;' language 'plpgsql'; + +-- OK +-- just the tax of a shipment, not price, shipping, or gift certificates +-- note: the base shipping tax charge is always applied to the first shipment in an order. +-- this is the tax charged minus the tax refunded +create function ec_shipment_tax (integer) +returns numeric as ' +DECLARE + v_shipment_id alias for $1; + item_price_tax numeric; + item_shipping_tax numeric; + base_shipping_tax numeric; + v_order_id ec_orders.order_id%TYPE; + min_shipment_id ec_shipments.shipment_id%TYPE; +BEGIN + SELECT into v_order_id order_id + FROM ec_shipments where shipment_id=v_shipment_id; + SELECT into min_shipment_id min(shipment_id) + FROM ec_shipments where order_id=v_order_id; + IF v_shipment_id=min_shipment_id THEN + SELECT into base_shipping_tax + coalesce(shipping_tax_charged,0) - coalesce(shipping_tax_refunded,0) + FROM ec_orders where order_id=v_order_id; + ELSE + base_shipping_tax := 0; + END IF; + SELECT into item_price_tax + coalesce(sum(price_tax_charged),0) - coalesce(sum(price_tax_refunded),0) + FROM ec_items where shipment_id=v_shipment_id and item_state <> ''void''; + SELECT into item_shipping_tax + coalesce(sum(shipping_tax_charged),0) - coalesce(sum(shipping_tax_refunded),0) + FROM ec_items where shipment_id=v_shipment_id and item_state <> ''void''; + RETURN item_price_tax + item_shipping_tax + base_shipping_tax; +END;' language 'plpgsql'; + + +-- OK +-- the gift certificate amount used on one order +create function ec_order_gift_cert_amount (integer) +returns numeric as ' +DECLARE + v_order_id alias for $1; + gift_cert_amount numeric; +BEGIN + select into gift_cert_amount + coalesce(sum(amount_used),0) - coalesce(sum(amount_reinstated),0) + FROM ec_gift_certificate_usage + WHERE order_id=v_order_id; + + return gift_cert_amount; +END;' language 'plpgsql'; + + +-- OK +-- tells how much of the gift certificate amount used on the order is to be applied +-- to a shipment (it's applied chronologically) +create function ec_shipment_gift_certificate (integer) +returns numeric as ' +DECLARE + v_shipment_id alias for $1; + v_order_id ec_orders.order_id%TYPE; + gift_cert_amount numeric; + past_ship_amount numeric; +BEGIN + SELECT into v_order_id order_id + FROM ec_shipments WHERE shipment_id=v_shipment_id; + gift_cert_amount := ec_order_gift_cert_amount(v_order_id); + SELECT into past_ship_amount + coalesce(sum(ec_shipment_price(shipment_id)) + sum(ec_shipment_shipping(shipment_id))+sum(ec_shipment_tax(shipment_id)),0) + FROM ec_shipments WHERE order_id = v_order_id and shipment_id <> v_shipment_id; + + IF past_ship_amount > gift_cert_amount THEN + return 0; + ELSE + return least(gift_cert_amount - past_ship_amount, coalesce(ec_shipment_price(v_shipment_id) + ec_shipment_shipping(v_shipment_id) + ec_shipment_tax(v_shipment_id),0)); + END IF; +END;' language 'plpgsql'; + +--CHECK OUTER JOIN BELOW + +-- OK +-- this can be used for either an item or order +-- given price and shipping, computes tax that needs to be charged (or refunded) +-- order_id is an argument so that we can get the usps_abbrev (and thus the tax rate), +create function ec_tax (numeric, numeric, integer) +returns numeric as ' +DECLARE + v_price alias for $1; + v_shipping alias for $2; + v_order_id alias for $3; + taxes ec_sales_tax_by_state%ROWTYPE; + tax_exempt_p ec_orders.tax_exempt_p%TYPE; +BEGIN + SELECT into tax_exempt_p tax_exempt_p + FROM ec_orders + WHERE order_id = v_order_id; + + IF tax_exempt_p = ''t'' THEN + return 0; + END IF; + + --SELECT t.* into taxes + --FROM ec_orders o, ec_addresses a, ec_sales_tax_by_state t + --WHERE o.shipping_address=a.address_id + --AND a.usps_abbrev=t.usps_abbrev(+) + --AND o.order_id=v_order_id; + + SELECT into taxes t.* + FROM ec_orders o + JOIN + ec_addresses a on (o.shipping_address=a.address_id) + LEFT JOIN + ec_sales_tax_by_state t using (usps_abbrev) + WHERE o.order_id=v_order_id; + + + IF coalesce(taxes.shipping_p,''f'') = ''f'' THEN + return coalesce(taxes.tax_rate,0) * v_price; + ELSE + return coalesce(taxes.tax_rate,0) * (v_price + v_shipping); + END IF; +END;' language 'plpgsql'; + +-- OK +-- total order cost (price + shipping + tax - gift certificate) +-- this should be equal to the amount that the order was authorized for +-- (if no refunds have been made) +create function ec_order_cost (integer) +returns numeric as ' +DECLARE + v_order_id alias for $1; + v_price numeric; + v_shipping numeric; + v_tax numeric; + v_certificate numeric; +BEGIN + v_price := ec_total_price(v_order_id); + v_shipping := ec_total_shipping(v_order_id); + v_tax := ec_total_tax(v_order_id); + v_certificate := ec_order_gift_cert_amount(v_order_id); + + return v_price + v_shipping + v_tax - v_certificate; +END;' language 'plpgsql'; + +-- OK +-- total shipment cost (price + shipping + tax - gift certificate) +create function ec_shipment_cost (integer) +returns numeric as ' +DECLARE + v_shipment_id alias for $1; + v_price numeric; + v_shipping numeric; + v_certificate numeric; + v_tax numeric; +BEGIN + v_price := ec_shipment_price(v_shipment_id); + v_shipping := ec_shipment_shipping(v_shipment_id); + v_tax := ec_shipment_tax(v_shipment_id); + v_certificate := ec_shipment_gift_certificate(v_shipment_id); + + return v_price + v_shipping - v_certificate + v_tax; +END;' language 'plpgsql'; + +-- OK +-- total amount refunded on an order so far +create function ec_total_refund (integer) +returns numeric as ' +DECLARE + v_order_id alias for $1; + v_order_refund numeric; + v_items_refund numeric; +BEGIN + select into v_order_refund + coalesce(shipping_refunded,0) + coalesce(shipping_tax_refunded,0) + from ec_orders where order_id=v_order_id; + select into v_items_refund + sum(coalesce(price_refunded,0)) + sum(coalesce(shipping_refunded,0)) + sum(coalesce(price_tax_refunded,0)) + sum(coalesce(shipping_tax_refunded,0)) from ec_items where order_id=v_order_id; + return v_order_refund + v_items_refund; +END;' language 'plpgsql'; + +-------------- end price calculations ----------------- +------------------------------------------------------- + + +----------- gift certificate procedures --------------- +------------------------------------------------------- + +-- gilbertw - removed global temporary table ec_gift_cert_usage_ids +-- copied code from openacs 3.2.5 + +-- +-- BMA (PGsql port) +-- Postgres is way cooler than Oracle with MVCC, which allows it +-- to have triggers updating the same table. Thus, we get rid of this +-- trio crap and we have a simple trigger for everything. + +create function trig_ec_cert_amount_remains() +returns opaque +as ' +DECLARE + bal_amount_used numeric; + original_amount numeric; +BEGIN + select amount into original_amount + from ec_gift_certificates where gift_certificate_id= NEW.certificate_id for update; + + select coalesce(sum(amount_used), 0) - coalesce(sum(amount_reinstated), 0) + into bal_amount_used + from ec_gift_certificate_usage + where gift_certificate_id= NEW.gift_certificate_id; + + UPDATE ec_gift_certificates + SET amount_remaining_p = case when amount > bal_amount_used then ''t'' else ''f'' end + WHERE gift_certificate_id = gift_certificate_rec.gift_certificate_id; + return new; +END; +' language 'plpgsql'; + +create trigger ec_cert_amount_remains +before update on ec_gift_certificate_usage +for each row +execute procedure trig_ec_cert_amount_remains(); + + +-- OK +-- calculates how much a user has in their gift certificate account +create function ec_gift_certificate_balance (integer) +returns numeric as ' +DECLARE + v_user_id alias for $1; + original_amount numeric; + total_amount_used numeric; + -- these only look at unexpired gift certificates + -- where amount_remaining_p is t, + -- hence the word subset in their names +BEGIN + SELECT coalesce(sum(amount),0) + into original_amount + FROM ec_gift_certificates_approved + WHERE user_id=v_user_id + AND amount_remaining_p=''t'' + AND expires > now(); + + SELECT coalesce(sum(u.amount_used),0) - + coalesce(sum(u.amount_reinstated),0) + into total_amount_used + FROM ec_gift_certificates_approved c, ec_gift_certificate_usage u + WHERE c.gift_certificate_id=u.gift_certificate_id + AND c.user_id=v_user_id + AND c.amount_remaining_p=''t'' + AND c.expires > now(); + + RETURN original_amount - total_amount_used; +END;' language 'plpgsql'; + +-- OK +-- Returns price + shipping + tax - gift certificate amount applied +-- for one order. +-- Requirement: ec_orders.shipping_charged, ec_orders.shipping_tax_charged, +-- ec_items.price_charged, ec_items.shipping_charged, ec_items.price_tax_chaged, +-- and ec_items.shipping_tax_charged should already be filled in. + +create function ec_order_amount_owed (integer) +returns numeric as ' +DECLARE + v_order_id alias for $1; + pre_gc_amount_owed numeric; + gc_amount numeric; +BEGIN + pre_gc_amount_owed := ec_total_price(v_order_id) + ec_total_shipping(v_order_id) + ec_total_tax(v_order_id); + gc_amount := ec_order_gift_cert_amount(v_order_id); + + RETURN pre_gc_amount_owed - gc_amount; +END;' language 'plpgsql'; + +-- OK +-- the amount remaining in an individual gift certificate +create function gift_certificate_amount_left (integer) +returns numeric as ' +DECLARE + v_gift_certificate_id alias for $1; + original_amount number; + amount_used number; +BEGIN + SELECT coalesce(sum(amount_used),0) - coalesce(sum(amount_reinstated),0) + into amount_used + FROM ec_gift_certificate_usage + WHERE gift_certificate_id = v_gift_certificate_id; + + SELECT amount + into original_amount + FROM ec_gift_certificates + WHERE gift_certificate_id = v_gift_certificate_id; + + RETURN original_amount - amount_used; +END;' language 'plpgsql'; + +-- I DON'T USE THIS PROCEDURE ANYMORE BECAUSE THERE'S A MORE +-- FAULT-TOLERANT TCL VERSION +-- This applies gift certificate balance to an entire order +-- by iteratively applying unused/unexpired gift certificates +-- to the order until the order is completely paid for or +-- the gift certificates run out. +-- Requirement: ec_orders.shipping_charged, ec_orders.shipping_tax_charged, +-- ec_items.price_charged, ec_items.shipping_charged, ec_items.price_tax_charged, +-- ec_items.shipping_tax_charged should already be filled in. +-- Call this within a transaction. +--create or replace procedure ec_apply_gift_cert_balance (v_order_id IN integer, v_user_id IN integer) +--IS +-- CURSOR gift_certificate_to_use_cursor IS +-- SELECT * +-- FROM ec_gift_certificates_approved +-- WHERE user_id = v_user_id +-- AND (expires is null or now() < expires ) +-- AND amount_remaining_p = ''t'' +-- ORDER BY expires; +-- amount_owed number; +-- gift_certificate_balance number; +-- certificate ec_gift_certificates_approved%ROWTYPE; +--BEGIN +-- gift_certificate_balance := ec_gift_certificate_balance(v_user_id); +-- amount_owed := ec_order_amount_owed(v_order_id); +-- +-- OPEN gift_certificate_to_use_cursor; +-- WHILE amount_owed > 0 and gift_certificate_balance > 0 +-- LOOP +-- FETCH gift_certificate_to_use_cursor INTO certificate; +-- +-- INSERT into ec_gift_certificate_usage +-- (gift_certificate_id, order_id, amount_used, used_date) +-- VALUES +-- (certificate.gift_certificate_id, v_order_id, least(gift_certificate_amount_left(certificate.gift_certificate_id), amount_owed), now()); +-- +-- gift_certificate_balance := ec_gift_certificate_balance(v_user_id); +-- amount_owed := ec_order_amount_owed(v_order_id); +-- END LOOP; +-- CLOSE gift_certificate_to_use_cursor; +--END ec_apply_gift_cert_balance; +--/ +--show errors + +-- OK +-- reinstates all gift certificates used on an order (as opposed to +-- individual items), e.g. if the order was voided or an auth failed + +create function ec_reinst_gift_cert_on_order (integer) +returns integer as ' +DECLARE + v_order_id alias for $1; +BEGIN + insert into ec_gift_certificate_usage + (gift_certificate_id, order_id, amount_reinstated, reinstated_date) + select gift_certificate_id, v_order_id, coalesce(sum(amount_used),0)-coalesce(sum(amount_reinstated),0), now() + from ec_gift_certificate_usage + where order_id=v_order_id + group by gift_certificate_id; + + return 0; +END;' language 'plpgsql'; + +-- Given an amount to refund to an order, this tells +-- you how much of that is to be refunded in cash (as opposed to +-- reinstated in gift certificates). Then you know you have to +-- go and reinstate v_amount minus (what this function returns) +-- in gift certificates. +-- (when I say cash I'm really talking about credit card +-- payment -- as opposed to gift certificates) + +-- Call this before inserting the amounts that are being refunded +-- into the database. +create function ec_cash_amount_to_refund (numeric, integer) +returns numeric as ' +DECLARE + v_amount alias for $1; + v_order_id alias for $2; + amount_paid numeric; + items_amount_paid numeric; + order_amount_paid numeric; + amount_refunded numeric; + curr_gc_amount numeric; + max_cash_refundable numeric; + cash_to_refund numeric; +BEGIN + -- the maximum amount of cash refundable is equal to + -- the amount paid (in cash + certificates) for shipped items only (since + -- money is not paid until an item actually ships) + -- minus the amount refunded (in cash + certificates) (only occurs for shipped items) + -- minus the current gift certificate amount applied to this order + -- or 0 if the result is negative + + select sum(coalesce(price_charged,0)) + sum(coalesce(shipping_charged,0)) + sum(coalesce(price_tax_charged,0)) + sum(coalesce(shipping_tax_charged,0)) into items_amount_paid from ec_items where order_id=v_order_id and shipment_id is not null and item_state <> ''void''; + + select coalesce(shipping_charged,0) + coalesce(shipping_tax_charged,0) into order_amount_paid from ec_orders where order_id=v_order_id; + + amount_paid := items_amount_paid + order_amount_paid; + amount_refunded := ec_total_refund(v_order_id); + curr_gc_amount := ec_order_gift_cert_amount(v_order_id); + + max_cash_refundable := amount_paid - amount_refunded - curr_gc_amount; + cash_to_refund := least(max_cash_refundable, v_amount); + + RETURN cash_to_refund; +END;' language 'plpgsql'; + +-- The amount of a given gift certificate used on a given order. +-- This is a helper function for ec_gift_cert_unshipped_amount. +create function ec_one_gift_cert_on_one_order (integer, integer) +returns numeric as ' +DECLARE + v_gift_certificate_id alias for $1; + v_order_id alias for $2; + bal_amount_used numeric; +BEGIN + select coalesce(sum(amount_used),0)-coalesce(sum(amount_reinstated),0) into bal_amount_used + from ec_gift_certificate_usage + where order_id=v_order_id + and gift_certificate_id=v_gift_certificate_id; + + RETURN bal_amount_used; + +END;' language 'plpgsql'; + +-- The amount of all gift certificates used on a given order that +-- expire before* a given gift certificate (*in the event that two +-- expire at precisely the same time, the one with a higher +-- gift_certificate_id is defined to expire last). +-- This is a helper function for ec_gift_cert_unshipped_amount. +create function ec_earlier_certs_on_one_order (integer, integer) +returns numeric as ' +DECLARE + v_gift_certificate_id alias for $1; + v_order_id alias for $2; + bal_amount_used numeric; +BEGIN + select coalesce(sum(u.amount_used),0)-coalesce(sum(u.amount_reinstated),0) into bal_amount_used + from ec_gift_certificate_usage u, ec_gift_certificates g, ec_gift_certificates g2 + where u.gift_certificate_id=g.gift_certificate_id + and g2.gift_certificate_id=v_gift_certificate_id + and u.order_id=v_order_id + and (g.expires < g2.expires or (g.expires = g2.expires and g.gift_certificate_id < g2.gift_certificate_id)); + + return bal_amount_used; +END;' language 'plpgsql'; + +-- The amount of a gift certificate that is applied to the upshipped portion of an order. +-- This is a helper function for ec_gift_cert_unshipped_amount. +create function ec_cert_unshipped_one_order (integer, integer) +returns numeric as ' +DECLARE + v_gift_certificate_id alias for $1; + v_order_id alias for $2; + total_shipment_cost numeric; + earlier_certs numeric; + total_tied_amount numeric; +BEGIN + select coalesce(sum(coalesce(ec_shipment_price(shipment_id),0) + coalesce(ec_shipment_shipping(shipment_id),0) + coalesce(ec_shipment_tax(shipment_id),0)),0) into total_shipment_cost + from ec_shipments + where order_id=v_order_id; + + earlier_certs := ec_earlier_certs_on_one_order(v_gift_certificate_id, v_order_id); + + IF total_shipment_cost <= earlier_certs THEN + total_tied_amount := ec_one_gift_cert_on_one_order(v_gift_certificate_id, v_order_id); + ELSE + IF total_shipment_cost > earlier_certs + ec_one_gift_cert_on_one_order(v_gift_certificate_id, v_order_id) THEN + total_tied_amount := 0; + ELSE + total_tied_amount := ec_one_gift_cert_on_one_order(v_gift_certificate_id, v_order_id) - (total_shipment_cost - earlier_certs); + END IF; + END IF; + + RETURN total_tied_amount; +END;' language 'plpgsql'; + +--HERE + +-- Returns the amount of a gift certificate that is applied to the unshipped portions of orders +-- (this amount is still considered outstanding since revenue, and thus gift certificate usage, +-- isnt recognized until the items ship). +create function ec_gift_cert_unshipped_amount (integer) +returns numeric as ' +DECLARE + v_gift_certificate_id alias for $1; + tied_but_unshipped_amount numeric; +BEGIN + select coalesce(sum(ec_cert_unshipped_one_order(v_gift_certificate_id,order_id)),0) into tied_but_unshipped_amount + from ec_orders + where order_id in (select unique order_id from ec_gift_certificate_usage where gift_certificate_id=v_gift_certificate_id); + + return tied_but_unshipped_amount; +END;' language 'plpgsql'; + +---------- end gift certificate procedures ------------ +------------------------------------------------------- + +\i pl-sql-utilities.sql +\i ec-product-package-create.sql +\i ecommerce-defaults.sql Index: openacs-4/packages/ecommerce/sql/postgresql/ecommerce-defaults.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/sql/postgresql/ecommerce-defaults.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/sql/postgresql/ecommerce-defaults.sql 9 Jul 2001 23:44:19 -0000 1.1 @@ -0,0 +1,266 @@ +-- +-- ecommerce-defaults.sql +-- +-- by eveander@arsdigita.com, April 1999 +-- and ported by Jerry Asher (jerry@theashergroup.com) +-- and Walter McGinnis (wtem@olywa.net) +-- +-- 03-11-2001 +-- +-- derived from ecommerce.sql (renamed ecommerce-create.sql in 4.x port) +/* + * you have just started to load ecommerce-defaults.sql + * this file should be loaded after an administrator + * has been created for the ACS installation + * if you get errors loading this page during ACS installation, + * finish the installation and then load it manually with sqlplus + * with the following command from within + * the /web/service_name/packages/ecommerce/sql directory: + * + * sqlplus service_name/database_password < ecommerce-defaults.sql + * + */ + +-- 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' + || '$category_id' || '\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 $product_id $user_id $offer_code] %>' || '\n' + || '
' || '\n' + || '		' || '\n' || '<%= [ec_add_to_cart_link $product_id] %>' || '\n' || '
' || '\n' || '\n' + || '

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

' || '\n' || '\n' + || '<%= [ec_mailing_list_link_for_a_product $product_id] %>' || '\n' || '\n' + || '<%= [ec_footer] %>' || '\n' + || '' || '\n' + || '', + now(), (select grantee_id + from acs_permissions + where object_id = acs__magic_object_id('security_context_root') + and privilege = 'admin' + limit 1), + 'none'); + + + +-- 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, + now(), (select grantee_id + from acs_permissions + where object_id = acs__magic_object_id('security_context_root') + and privilege = 'admin' + limit 1), + 'none'); + + +-- The following templates are predefined ecommerce-defaults. +-- The templates are +-- used in procedures which send out the email, so the template_ids +-- shouldn't be changed, although the text can be edited at +-- [ec_url_concat [ec_url] /admin]/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}', +now(), + (select grantee_id + from acs_permissions + where object_id = acs__magic_object_id('security_context_root') + and privilege = 'admin' + limit 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}', +now(), +(select grantee_id + from acs_permissions + where object_id = acs__magic_object_id('security_context_root') + and privilege = 'admin' + limit 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', +now(), +(select grantee_id + from acs_permissions + where object_id = acs__magic_object_id('security_context_root') + and privilege = 'admin' + limit 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}', +now(), +(select grantee_id + from acs_permissions + where object_id = acs__magic_object_id('security_context_root') + and privilege = 'admin' + limit 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}', +now(), +(select grantee_id + from acs_permissions + where object_id = acs__magic_object_id('security_context_root') + and privilege = 'admin' + limit 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}', now(), + (select grantee_id + from acs_permissions + where object_id = acs__magic_object_id('security_context_root') + and privilege = 'admin' + limit 1), +'none'); + +-- set scan on Index: openacs-4/packages/ecommerce/sql/postgresql/ecommerce-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/sql/postgresql/ecommerce-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/sql/postgresql/ecommerce-drop.sql 9 Jul 2001 23:44:19 -0000 1.1 @@ -0,0 +1,267 @@ +drop function ec_gift_cert_unshipped_amount (integer); +drop function ec_cert_unshipped_one_order (integer, integer); +drop function ec_earlier_certs_on_one_order (integer, integer); +drop function ec_one_gift_cert_on_one_order (integer, integer); +drop function ec_cash_amount_to_refund (numeric, integer); +drop function ec_reinst_gift_cert_on_order (integer); +drop function gift_certificate_amount_left (integer); +drop function ec_order_amount_owed (integer); +drop function ec_gift_certificate_balance (integer); +drop trigger ec_cert_amount_remains on ec_gift_certificate_usage; +drop function trig_ec_cert_amount_remains(); +drop function ec_total_refund (integer); +drop function ec_shipment_cost (integer); +drop function ec_order_cost (integer); +drop function ec_tax (numeric, numeric, integer); +drop function ec_shipment_gift_certificate (integer); +drop function ec_order_gift_cert_amount (integer); +drop function ec_shipment_tax (integer); +drop function ec_shipment_shipping (integer); +drop function ec_shipment_price (integer); +drop function ec_total_tax (integer); +drop function ec_total_shipping (integer); +drop function ec_total_price (integer); + +drop table ec_automatic_email_log; +drop table ec_problems_log; +drop view ec_problem_id_sequence; +drop sequence ec_problem_id_seq; +drop table ec_cybercash_log; +drop trigger fin_trans_ccard_update_tr on ec_financial_transactions; +drop function fin_trans_ccard_update_tr(); +drop view ec_fin_transactions_reportable; +drop table ec_financial_transactions; +drop view ec_transaction_id_sequence; +drop sequence ec_transaction_id_seq; +drop table ec_spam_log; +drop view ec_spam_id_sequence; +drop sequence ec_spam_id_seq; +drop table ec_cat_mailing_lists; +drop trigger ec_email_templates_audit_tr on ec_email_templates; +drop function ec_email_templates_audit_tr(); +drop table ec_email_templates_audit; +drop table ec_email_templates; +drop view ec_email_template_id_sequence; +drop sequence ec_email_template_id_seq; +drop table ec_canned_responses; +drop view ec_canned_response_id_sequence; +drop sequence ec_canned_response_id_seq; +drop trigger ec_picklist_items_audit_tr on ec_picklist_items; +drop function ec_picklist_items_audit_tr(); +drop table ec_picklist_items_audit; +drop table ec_picklist_items; +drop view ec_picklist_item_id_sequence; +drop sequence ec_picklist_item_id_seq; +drop table ec_cs_action_info_used_map; +drop table ec_customer_service_actions; +drop trigger ec_cs_issue_inserts on ec_customer_service_issues; +drop function ec_cs_issue_inserts(); +drop table ec_cs_issue_type_map; +drop table ec_customer_service_issues; +drop view ec_customer_service_reps; +drop trigger ec_cs_interaction_inserts on ec_customer_serv_interactions; +drop function ec_cs_interaction_inserts(); +drop table ec_customer_serv_interactions; +drop trigger ec_user_identificate_date_tr on ec_user_identification; +drop function ec_user_identificate_date_tr(); +drop table ec_user_identification; +drop view ec_user_ident_id_sequence; +drop sequence ec_user_ident_id_seq; +drop view ec_interaction_id_sequence; +drop sequence ec_interaction_id_seq; +drop view ec_action_id_sequence; +drop sequence ec_action_id_seq; +drop view ec_issue_id_sequence; +drop sequence ec_issue_id_seq; +drop table ec_gift_certificate_usage; +drop function ec_gift_certificates_audit_tr(); +drop table ec_gift_certificates_audit; +drop view ec_gift_certificates_issued; +drop view ec_gift_certificates_purchased; +drop view ec_gift_certificates_approved; +drop table ec_gift_certificates; +drop view ec_gift_cert_id_sequence; +drop sequence ec_gift_cert_id_seq; +drop trigger ec_offers_audit_tr on ec_offers; +drop function ec_offers_audit_tr(); +drop table ec_offers_audit; +drop view ec_offers_current; +drop table ec_offers; +drop view ec_offer_sequence; +drop sequence ec_offer_seq; +drop trigger ec_retailer_locations_audit_tr on ec_retailer_locations; +drop function ec_retailer_locations_audit_tr(); +drop table ec_retailer_locations_audit; +drop table ec_retailer_locations; +drop view ec_retailer_location_sequence; +drop sequence ec_retailer_location_seq; +drop trigger ec_retailers_audit_tr on ec_retailers; +drop function ec_retailers_audit_tr(); +drop table ec_retailers_audit; +drop table ec_retailers; +drop view ec_retailer_sequence; +drop sequence ec_retailer_seq; +drop trigger ec_sales_tax_by_state_audit_tr on ec_sales_tax_by_state; +drop function ec_sales_tax_by_state_audit_tr(); +drop table ec_sales_tax_by_state_audit; +drop table ec_sales_tax_by_state; +drop trigger ec_admin_settings_audit_tr on ec_admin_settings; +drop function ec_admin_settings_audit_tr(); +drop table ec_admin_settings_audit; +drop table ec_admin_settings; +drop trigger ec_order_state_after_tr on ec_items; +drop function ec_order_state_after_tr(); +drop view ec_items_money_view; +drop view ec_items_shippable; +drop view ec_items_refundable; +drop view ec_items_reportable; +drop table ec_items; +drop view ec_item_id_sequence; +drop sequence ec_item_id_seq; +drop table ec_refunds; +drop view refund_id_sequence; +drop sequence refund_id_seq; +drop trigger ec_shipments_audit_tr on ec_shipments; +drop function ec_shipments_audit_tr(); +drop table ec_shipments_audit; +drop trigger ec_shipment_address_update_tr on ec_shipments; +drop function ec_shipment_address_update_tr(); +drop table ec_shipments; +drop view ec_shipment_id_sequence; +drop sequence ec_shipment_id_seq; +drop view ec_orders_shippable; +drop view ec_orders_reportable; +drop table ec_orders; +drop view ec_order_id_sequence; +drop sequence ec_order_id_seq; +drop table ec_user_session_offer_codes; +drop table ec_user_session_info; +drop table ec_user_sessions; +drop view ec_user_session_sequence; +drop sequence ec_user_session_seq; +drop trigger ec_custom_p_f_values_audit_tr on ec_custom_product_field_values; +drop function ec_custom_p_f_values_audit_tr(); +drop table ec_custom_p_field_values_audit; +drop table ec_custom_product_field_values; +drop trigger ec_custom_prod_fields_audit_tr on ec_custom_product_fields; +drop function ec_custom_prod_fields_audit_tr(); +drop table ec_custom_product_fields_audit; +drop table ec_custom_product_fields; +drop table ec_category_template_map; +drop trigger ec_subsubcat_prod_map_audit_tr on ec_subsubcategory_product_map; +drop function ec_subsubcat_prod_map_audit_tr(); +drop table ec_subsubcat_prod_map_audit; +drop table ec_subsubcategory_product_map; +drop trigger ec_subcat_prod_map_audit_tr on ec_subcategory_product_map; +drop function ec_subcat_prod_map_audit_tr(); +drop table ec_subcat_prod_map_audit; +drop table ec_subcategory_product_map; +drop trigger ec_cat_prod_map_audit_tr on ec_category_product_map; +drop function ec_cat_prod_map_audit_tr(); +drop table ec_category_product_map_audit; +drop table ec_category_product_map; +drop trigger ec_product_reviews_audit_tr on ec_product_reviews; +drop function ec_product_reviews_audit_tr(); +drop table ec_product_reviews_audit; +drop table ec_product_reviews; +drop view ec_product_review_id_sequence; +drop sequence ec_product_review_id_seq; +drop trigger ec_product_comments_audit_tr on ec_product_comments; +drop function ec_product_comments_audit_tr(); +drop table ec_product_comments_audit; +drop table ec_product_comments; +drop view ec_product_comment_id_sequence; +drop sequence ec_product_comment_id_seq; +drop trigger ec_product_links_audit_tr on ec_product_links; +drop function ec_product_links_audit_tr(); +drop table ec_product_links_audit; +drop table ec_product_links; +drop trigger ec_user_class_user_audit_tr on ec_user_class_user_map; +drop function ec_user_class_user_audit_tr(); +drop table ec_user_class_user_map_audit; +drop table ec_user_class_user_map; +drop view ec_recommendations_cats_view; +drop trigger ec_product_recommend_audit_tr on ec_product_recommendations; +drop function ec_product_recommend_audit_tr(); +drop table ec_product_recommend_audit; +drop table ec_product_recommendations; +drop view ec_recommendation_id_sequence; +drop sequence ec_recommendation_id_seq; +drop trigger ec_product_u_c_prices_audit_tr on ec_product_user_class_prices; +drop function ec_product_u_c_prices_audit_tr(); +drop table ec_product_u_c_prices_audit; +drop table ec_product_user_class_prices; +drop trigger ec_user_classes_audit_tr on ec_user_classes; +drop function ec_user_classes_audit_tr(); +drop table ec_user_classes_audit; +drop table ec_user_classes; +drop view ec_user_class_id_sequence; +drop sequence ec_user_class_id_seq; +drop table ec_creditcards; +drop view ec_creditcard_id_sequence; +drop sequence ec_creditcard_id_seq; +drop table ec_addresses; +drop view ec_address_id_sequence; +drop sequence ec_address_id_seq; +drop trigger ec_product_series_map_audit_tr on ec_product_series_map; +drop function ec_product_series_map_audit_tr(); +drop table ec_product_series_map_audit; +drop table ec_product_series_map; +drop trigger ec_sale_prices_audit_tr on ec_sale_prices; +drop function ec_sale_prices_audit_tr(); +drop table ec_sale_prices_audit; +drop view ec_sale_prices_current; +drop table ec_sale_prices; +drop view ec_sale_price_id_sequence; +drop sequence ec_sale_price_id_seq; +drop table ec_product_purchase_comb; +drop trigger ec_products_audit_tr on ec_products; +drop function ec_products_audit_tr(); +drop table ec_products_audit; +drop view ec_products_searchable; +drop view ec_products_displayable; +drop table ec_products; + +-- nuke all created objects +-- need to do this before nuking the type +-- delete from acs_objects where object_type = 'ec_product'; +create function inline_0 () +returns integer as ' +begin + PERFORM acs_object_type__drop_type (''ec_product'', ''f''); + return 0; +end;' language 'plpgsql'; + +select inline_0 (); +drop function inline_0 (); + +drop view ec_subsubcategories_augmented; +drop trigger ec_subsubcategories_audit_tr on ec_subsubcategories; +drop function ec_subsubcategories_audit_tr(); +drop table ec_subsubcategories_audit; +drop table ec_subsubcategories; +drop view ec_subsubcategory_id_sequence; +drop sequence ec_subsubcategory_id_seq; +drop view ec_subcategories_augmented; +drop trigger ec_subcategories_audit_tr on ec_subcategories; +drop function ec_subcategories_audit_tr(); +drop table ec_subcategories_audit; +drop table ec_subcategories; +drop view ec_subcategory_id_sequence; +drop sequence ec_subcategory_id_seq; +drop trigger ec_categories_audit_tr on ec_categories; +drop function ec_categories_audit_tr(); +drop table ec_categories_audit; +drop table ec_categories; +drop view ec_category_id_sequence; +drop sequence ec_category_id_seq; +drop trigger ec_templates_audit_tr on ec_templates; +drop function ec_templates_audit_tr(); +drop table ec_templates_audit; +drop table ec_templates; +drop function least(numeric,numeric); +drop view ec_template_id_sequence; +drop sequence ec_template_id_seq; + +\i pl-sql-utilities-drop.sql +\i ec-product-package-drop.sql Index: openacs-4/packages/ecommerce/sql/postgresql/pl-sql-utilities-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/sql/postgresql/pl-sql-utilities-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/sql/postgresql/pl-sql-utilities-drop.sql 9 Jul 2001 23:44:19 -0000 1.1 @@ -0,0 +1,4 @@ +-- defined in the acs-core +-- drop function logical_negation(boolean); +drop function one_if_within_n_days (timestamp, integer); +drop function pseudo_contains (varchar, varchar); Index: openacs-4/packages/ecommerce/sql/postgresql/pl-sql-utilities.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/sql/postgresql/pl-sql-utilities.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ecommerce/sql/postgresql/pl-sql-utilities.sql 9 Jul 2001 23:44:19 -0000 1.1 @@ -0,0 +1,77 @@ +-- +-- pl-sql.sql +-- +-- created by philg on 11/18/98 +-- +-- useful pl/sql utility procedures +-- + +-- gilbertw - logical_negation is defined in utilities-create.sql in acs-kernel +-- create function logical_negation (boolean) +-- returns boolean as ' +-- DECLARE +-- true_or_false alias for $1; +-- BEGIN +-- IF true_or_false is null THEN +-- return null; +-- ELSE +-- IF true_or_false = ''f'' THEN +-- return ''t''; +-- ELSE +-- return ''f''; +-- END IF; +-- END IF; +-- END;' language 'plpgsql'; + +-- useful for ecommerce and other situations where you want to +-- know whether something happened within last N days (assumes query_date +-- is in the past) + +create function one_if_within_n_days (timestamp, integer) +returns integer as ' +declare + query_date alias for $1; + n_days alias for $2; +begin + IF (now() - query_date) <= n_days THEN + return 1; + ELSE + return 0; + END IF; +end;' language 'plpgsql'; + +create function pseudo_contains (varchar, varchar) +returns integer as ' +DECLARE + indexed_stuff alias for $1; + space_sep_list_untrimmed alias for $2; + space_sep_list text; + upper_indexed_stuff text; + -- if you call this var START you get hosed royally + first_space integer; + score integer; +BEGIN + space_sep_list := upper(ltrim(rtrim(space_sep_list_untrimmed))); + upper_indexed_stuff := upper(indexed_stuff); + score := 0; + IF space_sep_list is null or indexed_stuff is null THEN + RETURN score; + END IF; + LOOP + first_space := position('' '' in space_sep_list); + IF first_space = 0 THEN + -- one token or maybe end of list + IF position(space_sep_list in upper_indexed_stuff) <> 0 THEN + RETURN score+10; + END IF; + RETURN score; + ELSE + -- first_space <> 0 + IF position(substring(space_sep_list from 1 to first_space-1) in upper_indexed_stuff) <> 0 THEN + score := score + 10; + END IF; + END IF; + space_sep_list := substring(space_sep_list from first_space+1); + END LOOP; +END;' language 'plpgsql'; +