-- accounts-receivables-create.sql -- -- @author Dekka Corp. -- @ported from sql-ledger and combined with parts from OpenACS ecommerce package -- @license GNU GENERAL PUBLIC LICENSE, Version 2, June 1991 -- @cvs-id -- CREATE SEQUENCE qar_invoiceid; SELECT nextval ('qar_invoiceid'); -- CREATE SEQUENCE qar_orderitemsid; SELECT nextval ('qar_orderitemsid'); -- CREATE TABLE qar_invoice ( id int DEFAULT nextval ( 'qar_invoiceid' ), trans_id int, parts_id int, description text, qty float4, allocated float4, sellprice float, fxsellprice float, discount float4, assemblyitem bool DEFAULT 'f', unit varchar(5), project_id int, deliverydate date, serialnumber text ); CREATE TABLE qar_ar ( id int DEFAULT nextval ( 'qal_id' ), invnumber text, transdate date DEFAULT current_date, customer_id int, taxincluded bool, amount float, netamount float, paid float, datepaid date, duedate date, invoice bool DEFAULT 'f', shippingpoint text, terms int2 DEFAULT 0, notes text, curr char(3), ordnumber text, employee_id int, till varchar(20), quonumber text, intnotes text, department_id int default 0, shipvia text, language_code varchar(6), ponumber text ); -- CREATE TABLE qar_oe ( id int default nextval('qal_id'), ordnumber text, transdate date default current_date, vendor_id int, customer_id int, amount float8, netamount float8, reqdate date, taxincluded bool, shippingpoint text, notes text, curr char(3), employee_id int, closed bool default 'f', quotation bool default 'f', quonumber text, intnotes text, department_id int default 0, shipvia text, language_code varchar(6), ponumber text, terms int2 DEFAULT 0 ); CREATE TRIGGER qci_check_inventory AFTER UPDATE ON qar_oe FOR EACH ROW EXECUTE PROCEDURE qci_check_inventory(); -- CREATE TABLE qar_orderitems ( trans_id int, parts_id int, description text, qty float4, sellprice float8, discount float4, unit varchar(5), project_id int, reqdate date, ship float4, serialnumber text, id int default nextval('qar_orderitemsid') ); -- -- CREATE TABLE qar_recurring ( id int, reference text, startdate date, nextdate date, enddate date, repeat int2, unit varchar(6), howmany int, payment bool default 'f' ); -- CREATE TABLE qar_recurringemail ( id int, formname text, format text, message text ); -- CREATE TABLE qar_recurringprint ( id int, formname text, format text, printer text ); -- create index qar_ar_id_key on qar_ar (id); create index qar_ar_transdate_key on qar_ar (transdate); create index qar_ar_invnumber_key on qar_ar (invnumber); create index qar_ar_ordnumber_key on qar_ar (ordnumber); create index qar_ar_customer_id_key on qar_ar (customer_id); create index qar_ar_employee_id_key on qar_ar (employee_id); create index qar_ar_quonumber_key on qar_ar (quonumber); -- create index qar_invoice_id_key on qar_invoice (id); create index qar_invoice_trans_id_key on qar_invoice (trans_id); -- create index qar_oe_id_key on qar_oe (id); create index qar_oe_transdate_key on qar_oe (transdate); create index qar_oe_ordnumber_key on qar_oe (ordnumber); create index qar_oe_employee_id_key on qar_oe (employee_id); create index qar_orderitems_trans_id_key on qar_orderitems (trans_id); create index qar_orderitems_id_key on qar_orderitems (id); -- CREATE FUNCTION qar_del_recurring() returns opaque as ' BEGIN DELETE FROM qar_recurring WHERE id = old.id; DELETE FROM qar_recurringemail WHERE id = old.id; DELETE FROM qar_recurringprint WHERE id = old.id; RETURN NULL; END; ' language 'plpgsql'; --end function CREATE TRIGGER qar_del_recurring AFTER DELETE ON qar_ar FOR EACH ROW EXECUTE PROCEDURE qar_del_recurring(); -- end trigger CREATE TRIGGER qar_del_recurring AFTER DELETE ON qal_gl FOR EACH ROW EXECUTE PROCEDURE qar_del_recurring(); -- end trigger -- accounts-ledger maintenance CREATE TRIGGER qar_del_department AFTER DELETE ON qar_ar FOR EACH ROW EXECUTE PROCEDURE qal_del_department(); -- end trigger CREATE TRIGGER qar_del_department AFTER DELETE ON qar_oe FOR EACH ROW EXECUTE PROCEDURE qal_del_department(); -- end trigger CREATE TRIGGER qar_del_exchangerate BEFORE DELETE ON qar_ar FOR EACH ROW EXECUTE PROCEDURE qal_del_exchangerate(); -- end trigger -- -- CREATE TRIGGER qar_del_exchangerate BEFORE DELETE ON qar_oe FOR EACH ROW EXECUTE PROCEDURE qal_del_exchangerate(); -- end trigger CREATE TRIGGER qar_check_department AFTER INSERT OR UPDATE ON qar_ar FOR EACH ROW EXECUTE PROCEDURE qal_check_department(); -- end trigger CREATE TRIGGER qar_check_department AFTER INSERT OR UPDATE ON qar_oe FOR EACH ROW EXECUTE PROCEDURE qal_check_department(); -- end trigger -- following from ecommerce package create table qar_ec_user_session_offer_codes ( user_session_id integer not null references qal_ec_user_sessions, product_id integer not null references qci_ec_products, offer_code varchar(20) not null, primary key (user_session_id, product_id) ); -- create some indices create index qar_ec_u_s_offer_codes_by_u_s_id on qar_ec_user_session_offer_codes(user_session_id); create index qar_ec_u_s_offer_codes_by_p_id on qar_ec_user_session_offer_codes(product_id); create sequence qar_ec_order_id_seq start 3000000; create view qar_ec_order_id_sequence as select nextval('qar_ec_order_id_seq') as nextval; create sequence qar_ec_creditcard_id_seq start 1; create view qar_ec_creditcard_id_sequence as select nextval('qar_ec_creditcard_id_seq') as nextval; create table qar_ec_creditcards ( creditcard_id integer not null primary key, user_id integer not null references users, -- Some credit card gateways do 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 the credit card gateway, -- we may bash this to NULL creditcard_number varchar(16), -- just the last four digits for subsequent UI creditcard_last_four char(4), -- ##/## creditcard_expire char(5), -- this used to reference ec_addresses, but now needs to reference contacts.. -- billing_address integer references qal_ec_addresses(address_id), billing_address integer, -- 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 qar_ec_creditcards_by_user_idx on qar_ec_creditcards (user_id); -- Gift certificate stuff ---- ------------------------------ create sequence qar_ec_gift_cert_id_seq start 1000000; create view qar_ec_gift_cert_id_sequence as select nextval('qar_ec_gift_cert_id_seq') as nextval; create table qar_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 timestamptz, authorized_date timestamptz, claimed_date timestamptz, -- customer service rep who issued it issued_by integer references users, -- customer who purchased it purchased_by integer references users, expires timestamptz, 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 timestamptz, voided_by integer references users, reason_for_void varchar(4000), last_modified timestamptz 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 qar_ec_gc_by_state on qar_ec_gift_certificates(gift_certificate_state); create index qar_ec_gc_by_amount_remaining on qar_ec_gift_certificates(amount_remaining_p); create index qar_ec_gc_by_user on qar_ec_gift_certificates(user_id); create index qar_ec_gc_by_claim_check on qar_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. qar_ec_financial_transactions stores the corresponding -- gift_certificate_id. create view qar_ec_gift_certificates_approved as select * from qar_ec_gift_certificates where gift_certificate_state in ('authorized'); create view qar_ec_gift_certificates_purchased as select * from qar_ec_gift_certificates where gift_certificate_state in ('authorized'); create view qar_ec_gift_certificates_issued as select * from qar_ec_gift_certificates where gift_certificate_state in ('authorized') and issued_by is not null; create table qar_ec_gift_certificates_audit ( gift_certificate_id integer, gift_certificate_state varchar(50), amount numeric, issue_date timestamptz, authorized_date timestamptz, issued_by integer, purchased_by integer, expires timestamptz, 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 timestamptz, voided_by integer, reason_for_void varchar(4000), last_modified timestamptz, last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' ); create function qar_ec_gift_certificates_audit_tr () returns opaque as ' begin insert into qar_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 qar_ec_gift_certificates_audit_tr after update or delete on qar_ec_gift_certificates for each row execute procedure qar_ec_gift_certificates_audit_tr (); create table qar_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 qal_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' -- used to reference ec_addresses, now needs to reference contacts -- shipping_address integer references qal_ec_addresses(address_id), shipping_address integer, -- store credit card info in a different table creditcard_id integer references qar_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 timestamptz, confirmed_date timestamptz, authorized_date timestamptz, voided_date timestamptz, expired_date timestamptz, -- 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 qar_ec_orders_by_user_idx on qar_ec_orders (user_id); create index qar_ec_orders_by_user_sess_idx on qar_ec_orders (user_session_id); create index qar_ec_orders_by_credit_idx on qar_ec_orders (creditcard_id); create index qar_ec_orders_by_addr_idx on qar_ec_orders (shipping_address); create index qar_ec_orders_by_conf_idx on qar_ec_orders (confirmed_date); create index qar_ec_orders_by_state_idx on qar_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 qar_ec_orders_reportable as select * from qar_ec_orders where order_state <> 'in_basket' and order_state <> 'void'; -- orders that have items which still need to be shipped create view qar_ec_orders_shippable as select * from qar_ec_orders where order_state in ('authorized','partially_fulfilled'); create sequence refund_id_seq; create view refund_id_sequence as select nextval('refund_id_seq') as nextval; create table qar_ec_refunds ( refund_id integer not null primary key, order_id integer not null references qar_ec_orders, -- not really necessary because it's in qar_ec_financial_transactions refund_amount numeric not null, refund_date timestamptz not null, refunded_by integer not null references users, refund_reasons varchar(4000) ); create index qar_ec_refunds_by_order_idx on qar_ec_refunds (order_id); create table qar_ec_gift_certificate_usage ( gift_certificate_id integer not null references qar_ec_gift_certificates, order_id integer references qar_ec_orders, amount_used numeric, used_date timestamptz, amount_reinstated numeric, reinstated_date timestamp ); create index qar_ec_gift_cert_by_id on qar_ec_gift_certificate_usage (gift_certificate_id); ----------- end gift certificate procedures ----------- ------------------------------------------------------- -- CREDIT CARD STUFF ------------------------ --------------------------------------------- create sequence qar_ec_transaction_id_seq start 4000000; create view qar_ec_transaction_id_sequence as select nextval('qar_ec_transaction_id_seq') as nextval; create table qar_ec_financial_transactions ( transaction_id varchar(20) not null primary key, -- The charge transaction that a refund transaction refunded. refunded_transaction_id varchar(20) references qar_ec_financial_transactions, -- order_id or gift_certificate_id must be filled in order_id integer references qar_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 ecst_ec_shipments, refund_id integer references qar_ec_refunds, -- this refers to the purchase of a gift certificate, not the use of one gift_certificate_id integer references qar_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 qar_ec_creditcards, transaction_amount numeric not null, refunded_amount numeric, -- 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 timestamptz not null, authorized_date timestamptz, -- set when to_be_captured_p becomes 't'; used in cron jobs to_be_captured_date timestamptz, marked_date timestamptz, refunded_date timestamptz, -- 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 qar_ec_finan_trans_by_order_idx on qar_ec_financial_transactions (order_id); create index qar_ec_finan_trans_by_cc_idx on qar_ec_financial_transactions (creditcard_id); create index qar_ec_finan_trans_by_gc_idx on qar_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 qar_ec_fin_transactions_reportable as select * from qar_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 qar_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 qar_ec_creditcards.creditcard_id%TYPE; begin IF new.order_id is not null THEN select into v_creditcard_id creditcard_id from qar_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 qar_ec_financial_transactions for each row execute procedure fin_trans_ccard_update_tr (); -- END CREDIT CARD STUFF ---------------------------- ----------------------------------------------------- -- these are the items that make up each order create sequence qar_ec_item_id_seq start 1; create view qar_ec_item_id_sequence as select nextval('qar_ec_item_id_seq') as nextval; create table qar_ec_items ( item_id integer not null primary key, order_id integer not null references qar_ec_orders, product_id integer not null references qci_ec_products, color_choice varchar(4000), size_choice varchar(4000), style_choice varchar(4000), -- this should probably be changed at some point to not require the relation -- or move the reference to ecst_ec_shipments, to not require shipping-tracking shipment_id integer references ecst_ec_shipments, -- this is the date that user put this item into their shopping basket in_cart_date timestamptz, voided_date timestamptz, voided_by integer references users, expired_date timestamptz, item_state varchar(50) default 'in_basket', -- NULL if not received back received_back_date timestamptz, -- 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 qar_ec_refunds, -- comments entered by customer service (CS) cs_comments varchar(4000) ); create index qar_ec_items_by_product on qar_ec_items(product_id); create index qar_ec_items_by_order on qar_ec_items(order_id); create index qar_ec_items_by_shipment on qar_ec_items(shipment_id); create view qar_ec_items_reportable as select * from qar_ec_items where item_state in ('to_be_shipped', 'shipped', 'arrived'); create view qar_ec_items_refundable as select * from qar_ec_items where item_state in ('shipped','arrived') and refund_id is null; create view qar_ec_items_shippable as select * from qar_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 qar_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 qar_ec_items where order_id=NEW.order_id; select count(*) into n_shipped_items from qar_ec_items where order_id=NEW.order_id and item_state=''shipped'' or item_state=''arrived''; select count(*) into n_received_back_items from qar_ec_items where order_id=NEW.order_id and item_state=''received_back''; select count(*) into n_void_items from qar_ec_items where order_id=NEW.order_id and item_state=''void''; IF n_items = n_void_items THEN update qar_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 qar_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 qar_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 qar_ec_orders set order_state=''partially_fulfilled'' where order_id=NEW.order_id; END IF; END IF; END IF; END IF; return new; end;' language 'plpgsql'; create trigger qar_ec_order_state_after_tr after update on qar_ec_items for each row execute procedure qar_ec_order_state_after_tr (); -- 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 sequence qar_ec_offer_seq start 1; create view qar_ec_offer_sequence as select nextval('qar_ec_offer_seq') as nextval; create table qar_ec_offers ( offer_id integer not null primary key, product_id integer not null references qci_ec_products, retailer_location_id integer not null references qar_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 timestamptz not null, offer_ends timestamptz not null, deleted_p boolean default 'f', last_modified timestamptz not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create view qar_ec_offers_current as select * from qar_ec_offers where deleted_p='f' and now() >= offer_begins and now() <= offer_ends; create table qar_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 timestamptz, offer_ends timestamptz, deleted_p boolean default 'f', last_modified timestamptz, 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 qar_ec_offers_audit_tr () returns opaque as ' begin insert into qar_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 qar_ec_offers_audit_tr after update or delete on qar_ec_offers for each row execute procedure qar_ec_offers_audit_tr (); --------------- price calculations ------------------- ------------------------------------------------------- -- just the price of an order, not shipping, tax, or gift certificates -- this is actually price_charged minus price_refunded create function qar_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 qar_ec_items WHERE order_id=v_order_id and item_state <> ''void''; return price; 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 qar_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 qar_ec_orders WHERE order_id=v_order_id; select into item_shipping coalesce(sum(shipping_charged),0) - coalesce(sum(shipping_refunded),0) FROM qar_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 qar_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 qar_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 qar_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 qar_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 qar_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 qar_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 qar_ec_shipment_shipping (integer) returns numeric as ' DECLARE v_shipment_id alias for $1; item_shipping numeric; base_shipping numeric; v_order_id qar_ec_orders.order_id%TYPE; min_shipment_id qar_ec_shipments.shipment_id%TYPE; BEGIN SELECT into v_order_id order_id FROM qar_ec_shipments where shipment_id=v_shipment_id; SELECT into min_shipment_id min(s.shipment_id) from qar_ec_shipments s, qar_ec_items i, qci_ec_products p where s.order_id = v_order_id and s.shipment_id = i.shipment_id and i.product_id = p.product_id and p.no_shipping_avail_p = ''f''; IF v_shipment_id=min_shipment_id THEN SELECT into base_shipping coalesce(shipping_charged,0) - coalesce(shipping_refunded,0) FROM qar_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 qar_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 qar_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 qar_ec_orders.order_id%TYPE; min_shipment_id qar_ec_shipments.shipment_id%TYPE; BEGIN SELECT into v_order_id order_id FROM qar_ec_shipments where shipment_id=v_shipment_id; SELECT into min_shipment_id min(s.shipment_id) from qar_ec_shipments s, qar_ec_items i, qar_ec_products p where s.order_id = v_order_id and s.shipment_id = i.shipment_id and i.product_id = p.product_id and p.no_shipping_avail_p = ''f''; IF v_shipment_id=min_shipment_id THEN SELECT into base_shipping_tax coalesce(shipping_tax_charged,0) - coalesce(shipping_tax_refunded,0) FROM qar_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 qar_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 qar_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 qar_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 qar_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 qar_ec_shipment_gift_certificate (integer) returns numeric as ' DECLARE v_shipment_id alias for $1; v_order_id qar_ec_orders.order_id%TYPE; gift_cert_amount numeric; past_ship_amount numeric; BEGIN SELECT into v_order_id order_id FROM qar_ec_shipments WHERE shipment_id=v_shipment_id; gift_cert_amount := qar_ec_order_gift_cert_amount(v_order_id); SELECT into past_ship_amount coalesce(sum(qar_ec_shipment_price(shipment_id)) + sum(qar_ec_shipment_shipping(shipment_id))+sum(qar_ec_shipment_tax(shipment_id)),0) FROM qar_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(qar_ec_shipment_price(v_shipment_id) + qar_ec_shipment_shipping(v_shipment_id) + qar_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 qar_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 qar_ec_sales_tax_by_state%ROWTYPE; tax_exempt_p qar_ec_orders.tax_exempt_p%TYPE; BEGIN SELECT into tax_exempt_p tax_exempt_p FROM qar_ec_orders WHERE order_id = v_order_id; IF tax_exempt_p = ''t'' THEN return 0; END IF; --SELECT t.* into taxes --FROM qar_ec_orders o, qar_ec_addresses a, qar_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 qar_ec_orders o JOIN qar_ec_addresses a on (o.shipping_address=a.address_id) LEFT JOIN qar_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 qar_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 := qar_ec_total_price(v_order_id); v_shipping := qar_ec_total_shipping(v_order_id); v_tax := qar_ec_total_tax(v_order_id); v_certificate := qar_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 qar_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 := qar_ec_shipment_price(v_shipment_id); v_shipping := qar_ec_shipment_shipping(v_shipment_id); v_tax := qar_ec_shipment_tax(v_shipment_id); v_certificate := qar_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 qar_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 qar_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 qar_ec_items where order_id=v_order_id; return v_order_refund + v_items_refund; END;' language 'plpgsql'; -------------- end price calculations ----------------- ------------------------------------------------------- -- -- 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_qar_ec_cert_amount_remains() returns opaque as ' DECLARE bal_amount_used numeric; original_amount numeric; BEGIN select amount into original_amount from qar_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 qar_ec_gift_certificate_usage where gift_certificate_id= NEW.gift_certificate_id; UPDATE qar_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 qar_ec_cert_amount_remains after update on qar_ec_gift_certificate_usage for each row execute procedure trig_qar_ec_cert_amount_remains(); -- OK -- calculates how much a user has in their gift certificate account create function qar_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 qar_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 qar_ec_gift_certificates_approved c, qar_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: qar_ec_orders.shipping_charged, qar_ec_orders.shipping_tax_charged, -- qar_ec_items.price_charged, qar_ec_items.shipping_charged, qar_ec_items.price_tax_chaged, -- and qar_ec_items.shipping_tax_charged should already be filled in. create function qar_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 := qar_ec_total_price(v_order_id) + qar_ec_total_shipping(v_order_id) + qar_ec_total_tax(v_order_id); gc_amount := qar_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 numeric; total_amount_used numeric; BEGIN SELECT coalesce(sum(amount_used),0) - coalesce(sum(amount_reinstated),0) into total_amount_used FROM qar_ec_gift_certificate_usage WHERE gift_certificate_id = v_gift_certificate_id; SELECT amount into original_amount FROM qar_ec_gift_certificates WHERE gift_certificate_id = v_gift_certificate_id; RETURN original_amount - total_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: qar_ec_orders.shipping_charged, qar_ec_orders.shipping_tax_charged, -- qar_ec_items.price_charged, qar_ec_items.shipping_charged, qar_ec_items.price_tax_charged, -- qar_ec_items.shipping_tax_charged should already be filled in. -- Call this within a transaction. --create or replace procedure qar_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 qar_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 qar_ec_gift_certificates_approved%ROWTYPE; --BEGIN -- gift_certificate_balance := qar_ec_gift_certificate_balance(v_user_id); -- amount_owed := qar_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 qar_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 := qar_ec_gift_certificate_balance(v_user_id); -- amount_owed := qar_ec_order_amount_owed(v_order_id); -- END LOOP; -- CLOSE gift_certificate_to_use_cursor; --END qar_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 qar_ec_reinst_gift_cert_on_order (integer) returns integer as ' DECLARE v_order_id alias for $1; BEGIN insert into qar_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 qar_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 qar_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 qar_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 qar_ec_orders where order_id=v_order_id; amount_paid := items_amount_paid + order_amount_paid; amount_refunded := qar_ec_total_refund(v_order_id); curr_gc_amount := qar_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 qar_ec_gift_cert_unshipped_amount. create function qar_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 qar_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 qar_ec_gift_cert_unshipped_amount. create function qar_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 qar_ec_gift_certificate_usage u, qar_ec_gift_certificates g, qar_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 qar_ec_gift_cert_unshipped_amount. create function qar_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(qar_ec_shipment_price(shipment_id),0) + coalesce(qar_ec_shipment_shipping(shipment_id),0) + coalesce(qar_ec_shipment_tax(shipment_id),0)),0) into total_shipment_cost from qar_ec_shipments where order_id=v_order_id; earlier_certs := qar_ec_earlier_certs_on_one_order(v_gift_certificate_id, v_order_id); IF total_shipment_cost <= earlier_certs THEN total_tied_amount := qar_ec_one_gift_cert_on_one_order(v_gift_certificate_id, v_order_id); ELSE IF total_shipment_cost > earlier_certs + qar_ec_one_gift_cert_on_one_order(v_gift_certificate_id, v_order_id) THEN total_tied_amount := 0; ELSE total_tied_amount := qar_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 qar_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(qar_ec_cert_unshipped_one_order(v_gift_certificate_id,order_id)),0) into tied_but_unshipped_amount from qar_ec_orders where order_id in (select distinct order_id from qar_ec_gift_certificate_usage where gift_certificate_id=v_gift_certificate_id); return tied_but_unshipped_amount; END;' language 'plpgsql'; ---------- end gift certificate procedures ------------ ------------------------------------------------------- ------------ tax related calculations -------- ----------------------------------------- -- -- -- this is populated by the rules the administrator sets in packages/ecommerce/www/admin]/sales-tax.tcl create table qar_ec_sales_tax_by_state ( -- Jerry usps_abbrev char(2) not null primary key references us_states(abbrev), -- 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 timestamptz not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null ); create table qar_ec_sales_tax_by_state_audit ( usps_abbrev char(2), tax_rate numeric, shipping_p boolean, last_modified timestamptz, 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 qar_ec_sales_tax_by_state_audit_tr () returns opaque as ' begin insert into qar_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 qar_ec_sales_tax_by_state_audit_tr after update or delete on qar_ec_sales_tax_by_state for each row execute procedure qar_ec_sales_tax_by_state_audit_tr ();