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 -N -r1.14 -r1.15 --- openacs-4/packages/ecommerce/sql/postgresql/ecommerce-create.sql 7 Jan 2002 02:02:16 -0000 1.14 +++ openacs-4/packages/ecommerce/sql/postgresql/ecommerce-create.sql 26 May 2002 04:36:49 -0000 1.15 @@ -28,7 +28,6 @@ -- gilbertw: acs-references is not complete yet. use old acs-geo-tables until -- acs-references is done -\i acs-geo-tables-create.sql -- product display templates create sequence ec_template_id_seq start 2; @@ -628,18 +627,17 @@ 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 + -- 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 Cybercash, + -- depending on admin settings, after we get success from the credit card gateway, -- 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), + billing_address integer references ec_addresses(address_id), -- 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' @@ -1386,7 +1384,7 @@ as select * from ec_orders -where order_state in ('authorized_plus_avs','authorized_minus_avs','partially_fulfilled'); +where order_state in ('authorized','partially_fulfilled'); -- this is needed because orders might be only partially shipped @@ -1400,7 +1398,7 @@ -- 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, + address_id integer references ec_addresses, shipment_date timestamp not null, expected_arrival_date timestamp, carrier varchar(50), -- e.g., 'fedex' @@ -2155,13 +2153,13 @@ as select * from ec_gift_certificates -where gift_certificate_state in ('authorized','authorized_plus_avs','authorized_minus_avs'); +where gift_certificate_state in ('authorized'); create view ec_gift_certificates_purchased as select * from ec_gift_certificates -where gift_certificate_state in ('authorized_plus_avs','authorized_minus_avs'); +where gift_certificate_state in ('authorized'); create view ec_gift_certificates_issued as @@ -2579,7 +2577,9 @@ 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, + transaction_id varchar(20) not null primary key, + -- The charge transaction that a refund transaction refunded. + refunded_transaction_id varchar(20) references ec_financial_transactions, -- 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 @@ -2595,7 +2595,8 @@ -- 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 + 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 @@ -2610,15 +2611,7 @@ -- 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', @@ -2659,42 +2652,6 @@ 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 ---------------------------- ----------------------------------------------------- @@ -2840,16 +2797,20 @@ create function ec_shipment_shipping (integer) returns numeric as ' DECLARE - v_shipment_id alias for $1; + 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; + SELECT into min_shipment_id min(s.shipment_id) + from ec_shipments s, ec_items i, 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) @@ -2871,7 +2832,7 @@ create function ec_shipment_tax (integer) returns numeric as ' DECLARE - v_shipment_id alias for $1; + v_shipment_id alias for $1; item_price_tax numeric; item_shipping_tax numeric; base_shipping_tax numeric; @@ -2880,8 +2841,12 @@ BEGIN SELECT into v_order_id order_id FROM ec_shipments where shipment_id=v_shipment_id; - SELECT into min_shipment_id min(shipment_id) - FROM ec_shipments where order_id=v_order_id; + SELECT into min_shipment_id min(s.shipment_id) + from ec_shipments s, ec_items i, 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) @@ -3143,12 +3108,12 @@ create function gift_certificate_amount_left (integer) returns numeric as ' DECLARE - v_gift_certificate_id alias for $1; + v_gift_certificate_id alias for $1; original_amount numeric; - amount_used numeric; + total_amount_used numeric; BEGIN SELECT coalesce(sum(amount_used),0) - coalesce(sum(amount_reinstated),0) - into amount_used + into total_amount_used FROM ec_gift_certificate_usage WHERE gift_certificate_id = v_gift_certificate_id; @@ -3157,7 +3122,7 @@ FROM ec_gift_certificates WHERE gift_certificate_id = v_gift_certificate_id; - RETURN original_amount - amount_used; + RETURN original_amount - total_amount_used; END;' language 'plpgsql'; -- I DON'T USE THIS PROCEDURE ANYMORE BECAUSE THERE'S A MORE