Index: openacs-4/packages/ecommerce/tcl/ecommerce-scheduled-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-scheduled-procs.xql,v diff -u -r1.4 -r1.5 --- openacs-4/packages/ecommerce/tcl/ecommerce-scheduled-procs.xql 19 Mar 2002 00:34:25 -0000 1.4 +++ openacs-4/packages/ecommerce/tcl/ecommerce-scheduled-procs.xql 26 May 2002 04:36:50 -0000 1.5 @@ -1,262 +1,173 @@ + - - + + select product_id from ec_products - - + + + + + + select i2.product_id as correlated_product_id, + count(*) as n_product_occurrences + from ec_items i2 + where i2.order_id in (select o2.order_id + from ec_orders o2 + where o2.user_id in (select user_id + from ec_orders o + where o.order_id in (select i.order_id + from ec_items i + where product_id = :product_id))) + and i2.product_id <> :product_id + group by i2.product_id + order by n_product_occurrences desc + + + + + + select count(*) + from ec_product_purchase_comb + where product_id = :product_id + + + + + + insert into ec_product_purchase_comb + (product_id, [join $insert_cols ", "]) + values + (:product_id, [join $insert_vals ", "]) + + + + + + update ec_product_purchase_comb + set [join $update_items ", "] + where product_id = :product_id + + + + + + update ec_orders + set order_state = 'failed_authorization' + where order_id = :order_id + + + + + + update ec_financial_transactions + set failed_p = 't' + where transaction_id = :transaction_id + + + + + + update ec_financial_transactions + set failed_p = 't', to_be_captured_p = 'f' + where transaction_id = :transaction_id + + + + + + update ec_gift_certificates + set gift_certificate_state = 'failed_authorization' + where gift_certificate_id = :gift_certificate_id + + + + + + select order_id + from ec_orders o + where order_state = 'authorized' + and (0 = (select count(*) + from ec_automatic_email_log log + where log.order_id = o.order_id and email_template_id = 1)) + + + + + + select gift_certificate_id + from ec_gift_certificates g + where gift_certificate_state = 'authorized' + and (0 = (select count(*) + from ec_automatic_email_log log + where log.gift_certificate_id = g.gift_certificate_id + and email_template_id = 4)) + + + + + + select gift_certificate_id + from ec_gift_certificates g + where gift_certificate_state = 'authorized' + and (0 = (select count(*) + from ec_automatic_email_log log + where log.gift_certificate_id = g.gift_certificate_id + and email_template_id = 5)) + + + + + + select order_id + from ec_orders + where order_state = 'failed_authorization' + + + + + + update ec_orders + set order_state = 'in_basket', saved_p = 't' + where order_id = :order_id + + - - - - - select i2.product_id as correlated_product_id, - count(*) as n_product_occurrences - from ec_items i2 - where i2.order_id in (select o2.order_id - from ec_orders o2 - where o2.user_id in (select user_id - from ec_orders o - where o.order_id in (select i.order_id - from ec_items i - where product_id = :product_id))) - and i2.product_id <> :product_id - group by i2.product_id - order by n_product_occurrences desc - - - - - - - - select count(*) from ec_product_purchase_comb where product_id=:product_id - - - - - - - - insert into ec_product_purchase_comb - (product_id, [join $insert_cols ", "]) - values - (:product_id, [join $insert_vals ", "]) - - - - - - - - - update ec_product_purchase_comb - set [join $update_items ", "] - where product_id=:product_id - - - - - - - - - select max(transaction_id) - from ec_financial_transactions - where order_id = :order_id - - - - - - - - - select avs_code - from ec_cybercash_log - where transaction_id = :transaction_id - and avs_code != '' - and txn_attempted_type != 'query' - and txn_attempted_time = (select MAX(txn_attempted_time) - from ec_cybercash_log log2 - where log2.transaction_id = :transaction_id) - - - - - - - - update ec_orders set order_state=:new_order_state where order_id=:order_id - - - - - - - select transaction_id from ec_financial_transactions where gift_certificate_id=:gift_certificate_id - - - - - - - update ec_financial_transactions set failed_p='t', to_be_captured_p='f' where transaction_id=:transaction_id - - - - - - - update ec_gift_certificates set gift_certificate_state='failed_authorization' where gift_certificate_id=:gift_certificate_id - - - - - - - - select order_id - from ec_orders o - where (order_state='authorized_plus_avs' or order_state='authorized_minus_avs') - and (0=(select count(*) from ec_automatic_email_log log where log.order_id=o.order_id and email_template_id=1)) - - - - - - - - - select gift_certificate_id - from ec_gift_certificates g - where (gift_certificate_state='authorized_plus_avs' or gift_certificate_state='authorized_minus_avs') - and (0=(select count(*) from ec_automatic_email_log log where log.gift_certificate_id=g.gift_certificate_id and email_template_id=4)) - - - - - - - - - select gift_certificate_id - from ec_gift_certificates g - where (gift_certificate_state='authorized_plus_avs' or gift_certificate_state='authorized_minus_avs') - and (0=(select count(*) from ec_automatic_email_log log where log.gift_certificate_id=g.gift_certificate_id and email_template_id=5)) - - - - - - - - select order_id from ec_orders where order_state='failed_authorization' - - - - - - - update ec_orders set order_state='in_basket', saved_p='t' where order_id=:order_id - - - - - - - - update ec_creditcards - set creditcard_number=null - where creditcard_id in (select unique 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'))) - - - - - - - - update ec_financial_transactions set failed_p='t' where transaction_id=:transaction_id - - - - - - - - select transaction_id, order_id from ec_financial_transactions - where to_be_captured_p='t' - and authorized_date is not null - and marked_date is null - and failed_p='f' - - - - - - - - update ec_financial_transactions set failed_p='t' where transaction_id=:transaction_id - - - - - - - financial_transactions set marked_date=sysdate where transaction_id=:transaction_id - - - - - - - - select transaction_id, order_id from ec_financial_transactions - where marked_date is not null - and settled_date is null - and failed_p='f' - - - - - - - - update ec_financial_transactions set failed_p='t' where transaction_id=:transaction_id - - - - - - - update ec_financial_transactions set failed_p='t' where transaction_id=:transaction_id - - - - - - - select transaction_id, order_id from ec_financial_transactions - where refunded_date is not null - and refund_settled_date is null - and failed_p='f' - - - - - - - update ec_financial_transactions set failed_p='t' where transaction_id=:transaction_id - - - - + + + update ec_financial_transactions + set failed_p = 't' + 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, 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_financial_transactions f, ec_creditcards c, persons p, ec_addresses a + where to_be_captured_p = 't' + and marked_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 + + + + + + update ec_financial_transactions + set failed_p = 't' + where transaction_id = :transaction_id + + +