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 -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
