Index: openacs-4/packages/ecommerce/tcl/ecommerce-scheduled-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-scheduled-procs-postgresql.xql,v
diff -u -r1.5 -r1.6
--- openacs-4/packages/ecommerce/tcl/ecommerce-scheduled-procs-postgresql.xql	24 Nov 2001 03:50:55 -0000	1.5
+++ openacs-4/packages/ecommerce/tcl/ecommerce-scheduled-procs-postgresql.xql	26 May 2002 04:36:49 -0000	1.6
@@ -1,132 +1,148 @@
 <?xml version="1.0"?>
 
 <queryset>
-  <rdbms><type>postgresql</type><version>7.1</version></rdbms>
+  <rdbms>
+    <type>postgresql</type>
+    <version>7.1</version>
+  </rdbms>
 
-  <fullquery name="ec_sweep_for_cybercash_zombies.confirmed_orders_select">      
+  <fullquery name="ec_sweep_for_payment_zombies.transactions_select">      
     <querytext>
-      select order_id, ec_order_cost(order_id) as total_order_price
-      from ec_orders
+      select o.order_id, ec_order_cost(o.order_id) as total_order_price, 
+	  f.transaction_id, f.inserted_date, f.transaction_amount, 
+	  c.creditcard_type as card_type, p.first_names || ' ' || p.last_name as card_name, 
+	  c.creditcard_number as card_number, substring(creditcard_expire for 2) as card_exp_month, substring(creditcard_expire from 4 for 2) as card_exp_year, c.creditcard_type,
+          a.zip_code as billing_zip,
+          a.line1 as billing_address, 
+	  a.city as billing_city, 
+          coalesce(a.usps_abbrev, a.full_state_name) as billing_state, 
+          a.country_code as billing_country
+      from ec_orders o, ec_financial_transactions f, ec_creditcards c, persons p , ec_addresses a
       where order_state = 'confirmed' 
       and (current_timestamp - confirmed_date) > timespan_days(1/96::float)
+      and f.failed_p = 'f'
+      and f.order_id = o.order_id
+      and f.creditcard_id = c.creditcard_id 
+      and c.user_id = p.person_id
+      and c.billing_address = a.address_id
     </querytext>
   </fullquery>
   
-  <fullquery name="ec_sweep_for_cybercash_zombies.problems_log_insert">      
+  <fullquery name="ec_sweep_for_payment_zombies.problems_log_insert">      
     <querytext>
       insert into ec_problems_log
       (problem_id, problem_date, problem_details, order_id)
       values
       (ec_problem_id_sequence.nextval, current_timestamp, :problem_details, :order_id)
     </querytext>
   </fullquery>
-  
-  <fullquery name="ec_sweep_for_cybercash_zombie_gift_certificates.gift_certificate_select">      
+
+  <fullquery name="ec_sweep_for_payment_zombie_gift_certificates.transactions_select">      
     <querytext>
-      select g.gift_certificate_id, t.transaction_id
-      from ec_gift_certificates g, ec_financial_transactions t
-      where g.gift_certificate_id=t.gift_certificate_id
-      and g.gift_certificate_state = 'confirmed' 
+      select g.gift_certificate_id, f.transaction_id, f.transaction_amount, f.inserted_date,
+	  c.creditcard_type, c.creditcard_number as card_number, substring(creditcard_expire for 2) as card_exp_month, substring(creditcard_expire from 4 for 2) as card_exp_year,
+          p.first_names || ' ' || p.last_name as card_name, 
+          a.zip_code as billing_zip,
+          a.line1 as billing_address, 
+	  a.city as billing_city, 
+          coalesce(a.usps_abbrev, a.full_state_name) as billing_state, 
+          a.country_code as billing_country
+      from ec_gift_certificates g, ec_financial_transactions f, ec_creditcards c, persons p, ec_addresses a
+      where g.gift_certificate_state = 'confirmed' 
       and (current_timestamp - g.issue_date) > timespan_days(1/96::float)
+      and f.failed_p = 'f'
+      and g.gift_certificate_id = f.gift_certificate_id
+      and f.creditcard_id = c.creditcard_id 
+      and c.user_id = p.person_id 
+      and c.billing_address = a.address_id
     </querytext>
   </fullquery>
   
-  <fullquery name="ec_sweep_for_cybercash_zombie_gift_certificates.financial_transactions_update">      
+  <fullquery name="ec_sweep_for_payment_zombie_gift_certificates.transaction_success_update">      
     <querytext>
       update ec_financial_transactions
-      set authorized_date=current_timestamp,
-      to_be_captured_p='t'
+      set transaction_id = :pgw_transaction_id, authorized_date=current_timestamp, to_be_captured_p='t'
       where transaction_id = :transaction_id
     </querytext>
   </fullquery>
   
-  <fullquery name="ec_sweep_for_cybercash_zombie_gift_certificates.gift_certificate_state_update">      
+  <fullquery name="ec_sweep_for_payment_zombie_gift_certificates.certificate_success_update">      
     <querytext>
       update ec_gift_certificates
-      set authorized_date = current_timestamp,
-      gift_certificate_state = :cc_result
+      set authorized_date = current_timestamp, gift_certificate_state = 'authorized'
       where gift_certificate_id = :gift_certificate_id
     </querytext>
   </fullquery>
   
-  <fullquery name="ec_expire_old_carts.expired_carts_update">      
+  <fullquery name="ec_sweep_for_payment_zombie_gift_certificates.problems_log_insert">      
     <querytext>
-      update ec_orders set order_state='expired', expired_date=current_timestamp where order_state='in_basket' and current_timestamp-in_basket_date > timespan_days([util_memoize {ad_parameter -package_id [ec_id] CartDuration ecommerce} [ec_cache_refresh]]::float)
-    </querytext>
-  </fullquery>
-  
-  <fullquery name="ec_expire_old_carts.item_state_update">      
-    <querytext>
-      update ec_items set item_state='expired', expired_date=current_timestamp where item_state='in_basket' and order_id in (select order_id from ec_orders where order_state='expired')
-    </querytext>
-  </fullquery>
-  
-  <fullquery name="ec_unauthorized_transactions.unauthorized_transactions_select">      
-    <querytext>
-      select transaction_id, order_id from ec_financial_transactions
-      where to_be_captured_p='t'
-      and current_timestamp-to_be_captured_date > timespan_days(1/48::float)
-      and authorized_date is null
-      and failed_p='f'
-    </querytext>
-  </fullquery>
-  
-  <fullquery name="ec_unauthorized_transactions.problems_log_insert">      
-    <querytext>
       insert into ec_problems_log
       (problem_id, problem_date, problem_details, order_id)
       values
-      (ec_problem_id_sequence.nextval, current_timestamp, 'invalid input to ec_creditcard_authorization in ec_unauthorized_transactions', :order_id)
+      (ec_problem_id_sequence.nextval, current_timestamp, :problem_details, :order_id)
     </querytext>
   </fullquery>
 
-  <fullquery name="ec_unauthorized_transactions.problems_insert">      
+  <fullquery name="ec_expire_old_carts.expired_carts_update">      
     <querytext>
-      insert into ec_problems_log
-      (problem_id, problem_date, problem_details, order_id)
-      values
-      (ec_problem_id_sequence.nextval, current_timestamp, :problem_details, :order_id)
+      update ec_orders 
+      set order_state='expired', expired_date=current_timestamp 
+      where order_state='in_basket' 
+      and current_timestamp-in_basket_date > timespan_days(:cart_duration::float)
     </querytext>
   </fullquery>
   
-  <fullquery name="ec_unauthorized_transactions.transaction_success_update">      
+  <fullquery name="ec_expire_old_carts.item_state_update">      
     <querytext>
-      update ec_financial_transactions set authorized_date=current_timestamp where transaction_id=:transaction_id
+      update ec_items 
+      set item_state='expired', expired_date=current_timestamp 
+      where item_state='in_basket' and order_id in (select order_id 
+          from ec_orders 
+          where order_state='expired')
     </querytext>
   </fullquery>
-
   
-  <fullquery name="ec_unauthorized_transactions.problems_log_insert">      
+  <fullquery name="ec_remove_creditcard_data.creditcard_update">      
     <querytext>
-      insert into ec_problems_log
-      (problem_id, problem_date, problem_details, order_id)
-      values
-      (ec_problem_id_sequence.nextval, current_timestamp, :problem_details, :order_id)
+      update ec_creditcards
+      set creditcard_number=null
+      where creditcard_id in (select distinct c.creditcard_id
+          from ec_creditcards c, ec_orders o
+          where c.creditcard_id = o.creditcard_id
+          and c.creditcard_number is not null
+          and 0=(select count(*)
+             from ec_orders o2
+             where o2.creditcard_id=c.creditcard_id
+             and o2.order_state not in ('fulfilled','returned','void','expired'))
+      	  and 0=(select count(*)
+	     from ec_financial_transactions f
+	     where f.transaction_type = 'refund'
+             and f.creditcard_id = c.creditcard_id
+	     and refunded_date is null))
     </querytext>
   </fullquery>
-
   
-  <fullquery name="ec_unmarked_transactions.financial_transaction_success_update">      
+  <fullquery name="ec_unauthorized_transactions.transactions_select">      
     <querytext>
-      update ec_financial_transactions set marked_date=current_timestamp where transaction_id=:transaction_id
+      select f.transaction_id, f.order_id, f.transaction_amount, f.to_be_captured_date, 
+          p.first_names || ' ' || p.last_name as card_name, 
+          substring(creditcard_expire for 2) as card_exp_month, substring(creditcard_expire from 4 for 2) as card_exp_year, c.creditcard_number as card_number, c.creditcard_type,
+          a.zip_code as billing_zip,
+          a.line1 as billing_address, 
+	  a.city as billing_city, 
+          coalesce(a.usps_abbrev, a.full_state_name) as billing_state, 
+          a.country_code as billing_country
+      from ec_financial_transactions f, ec_creditcards c, persons p, ec_addresses a
+      where to_be_captured_p='t'
+      and current_timestamp-to_be_captured_date > timespan_days(1/48::float)
+      and authorized_date is null
+      and f.failed_p='f'
+      and f.creditcard_id=c.creditcard_id 
+      and c.user_id=p.person_id
+      and c.billing_address = a.address_id
     </querytext>
   </fullquery>
-
-  <fullquery name="ec_unrefunded_transactions.financial_transactions_update">
-    <querytext>
-      update ec_financial_transactions set refunded_date=current_timestamp where transaction_id=:transaction_id
-    </querytext>
-  </fullquery>
   
-  <fullquery name="ec_unrefunded_transactions.problems_log_insert ">
-    <querytext>
-      insert into ec_problems_log
-      (problem_id, problem_date, problem_details, order_id)
-      values
-      (ec_problem_id_sequence.nextval, current_timestamp, :problem_details, :order_id)
-    </querytext>
-  </fullquery>
-  
   <fullquery name="ec_unauthorized_transactions.problems_log_insert">      
     <querytext>
       insert into ec_problems_log
@@ -135,87 +151,67 @@
       (ec_problem_id_sequence.nextval, current_timestamp, :problem_details, :order_id)
     </querytext>
   </fullquery>
-  
-  <fullquery name="ec_unsettled_transactions.settled_date_update">      
-    <querytext>
-      update ec_financial_transactions set settled_date=current_timestamp where transaction_id=:transaction_id
-    </querytext>
-  </fullquery>
 
-  
-  <fullquery name="ec_unsettled_transactions.two_days_since_order_was_marked_p">      
+  <fullquery name="ec_unauthorized_transactions.transaction_success_update">      
     <querytext>
-      select case when marked_date + '2 days'::interval < now() then 1 else 0 end from ec_financial_transactions where transaction_id=:transaction_id
+      update ec_financial_transactions 
+      set transaction_id = :pgw_transaction_id, authorized_date=current_timestamp 
+      where transaction_id=:transaction_id
     </querytext>
   </fullquery>
-  
-  <fullquery name="ec_unsettled_transactions.problems_log_insert">      
-    <querytext>
-      insert into ec_problems_log
-      (problem_id, problem_date, problem_details, order_id)
-      values
-      (ec_problem_id_sequence.nextval, current_timestamp, :problem_details, :order_id)
-    </querytext>
-  </fullquery>
 
-  <fullquery name="ec_unrefunded_transactions.unrefunded_transactions_select">      
+  <fullquery name="ec_unmarked_transactions.problems_log_insert">      
     <querytext>
-      select transaction_id, order_id from ec_financial_transactions
-      where transaction_type='refund'
-      and current_timestamp - inserted_date > timespan_days(1/48::float)
-      and refunded_date is null
-      and failed_p='f'
-    </querytext>
-  </fullquery>
-  
-  <fullquery name="ec_unrefunded_transactions.financial_transactions_update">      
-    <querytext>
-      update ec_financial_transactions set refunded_date=current_timestamp where transaction_id=:transaction_id
-    </querytext>
-  </fullquery>
-  
-  <fullquery name="ec_unrefunded_transactions.problems_log_insert">      
-    <querytext>
       insert into ec_problems_log
       (problem_id, problem_date, problem_details, order_id)
       values
       (ec_problem_id_sequence.nextval, current_timestamp, :problem_details, :order_id)
     </querytext>
   </fullquery>
   
-  <fullquery name="ec_unrefunded_transactions.financial_transaction_refund_update">      
+  <fullquery name="ec_unmarked_transactions.transaction_success_update">      
     <querytext>
-      update ec_financial_transactions set refunded_date=current_timestamp where transaction_id=:transaction_id
+      update ec_financial_transactions 
+      set marked_date=current_timestamp, transaction_id = :pgw_transaction_id 
+      where transaction_id=:transaction_id
     </querytext>
   </fullquery>
-  
-  <fullquery name="ec_unrefund_settled_transactions.financial_transaction_refund_settled_update">      
+
+  <fullquery name="ec_unrefunded_transactions.transactions_select">      
     <querytext>
-      update ec_financial_transactions set refund_settled_date=current_timestamp where transaction_id=:transaction_id
+      select f.transaction_id, f.order_id, f.transaction_amount, f.to_be_captured_date, 
+          p.first_names || ' ' || p.last_name as card_name, 
+	  c.creditcard_number as card_number, c.creditcard_type, substring(creditcard_expire for 2) as card_exp_month, substring(creditcard_expire from 4 for 2) as card_exp_year,
+          a.zip_code as billing_zip,
+	  a.line1 as billing_address, 
+	  a.city as billing_city, 
+          coalesce(a.usps_abbrev, a.full_state_name) as billing_state, 
+          a.country_code as billing_country
+      from ec_financial_transactions f, ec_creditcards c, persons p, ec_addresses a 
+      where transaction_type = 'refund'
+      and f.refunded_date is null
+      and f.failed_p='f'
+      and f.creditcard_id = c.creditcard_id 
+      and c.user_id = p.person_id
+      and c.billing_address = a.address_id
+      and current_timestamp-to_be_captured_date > timespan_days(1/48::float)
     </querytext>
   </fullquery>
   
-  <fullquery name="ec_unrefund_settled_transactions.two_days_since_order_was_refunded_p">      
+  <fullquery name="ec_unrefunded_transactions.problems_log_insert">
     <querytext>
-      select case when refunded_date + '2 days'::interval < now() then 1 else 0 end from ec_financial_transactions where transaction_id=:transaction_id
-    </querytext>
-  </fullquery>
-
-  <fullquery name="ec_unrefund_settled_transactions.problems_log_insert">
-    <querytext>
       insert into ec_problems_log
       (problem_id, problem_date, problem_details, order_id)
       values
       (ec_problem_id_sequence.nextval, current_timestamp, :problem_details, :order_id)
     </querytext>
   </fullquery>
-
-  <fullquery name="ec_calculate_product_purchase_combinations.problems_log_insert">      
+  
+  <fullquery name="ec_unrefunded_transactions.transaction_success_update">      
     <querytext>
-      insert into ec_problems_log
-      (problem_id, problem_date, problem_details, order_id)
-      values
-      (ec_problem_id_sequence.nextval, current_timestamp, :problem_details, :order_id)
+      update ec_financial_transactions 
+      set transaction_id = :pgw_transaction_id, refunded_date=current_timestamp 
+      where transaction_id=:transaction_id
     </querytext>
   </fullquery>