Index: openacs-4/packages/ecommerce/www/admin/orders/items-return-4-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/items-return-4-postgresql.xql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/ecommerce/www/admin/orders/items-return-4-postgresql.xql 29 Jan 2002 00:11:46 -0000 1.2 +++ openacs-4/packages/ecommerce/www/admin/orders/items-return-4-postgresql.xql 26 May 2002 04:36:50 -0000 1.3 @@ -1,103 +1,174 @@ - postgresql7.1 + postgresql7.1 - - + + insert into ec_refunds - (refund_id, order_id, refund_amount, refund_date, refunded_by, refund_reasons) - values - (:refund_id, :order_id, :cash_amount_to_refund, current_timestamp, :customer_service_rep,:reason_for_return) - - - + (refund_id, order_id, refund_amount, refund_date, refunded_by, refund_reasons) + values + (:refund_id, :order_id, :cash_amount_to_refund, current_timestamp, :customer_service_rep,:reason_for_return) + + - - - - select ec_tax(:price_bind_variable,0,:order_id) - - + + + select coalesce(price_tax_charged,0) as price_tax_charged, + coalesce(shipping_tax_charged,0) as shipping_tax_charged + from ec_items + where item_id=:item_id + + - - - - select ec_tax(0,:shipping_bind_variable,:order_id) - - + + + select ec_tax(:price_bind_variable, 0, :order_id) + + + + + + select coalesce(shipping_tax_charged,0) + from ec_orders + where order_id=:order_id + + - - - + + + select ec_tax(0,:shipping_bind_variable,:order_id) + + + + + select ec_tax(0,:base_shipping_to_refund,:order_id) - - + + - - - + + + select transaction_id as charged_transaction_id, marked_date + from ec_financial_transactions + where order_id = :order_id + and transaction_type = 'charge' + and (transaction_amount - :refund_amount) < 0.01::numeric + and (transaction_amount - :refund_amount) > 0::numeric + and refunded_amount is null + and marked_date is not null + and failed_p = 'f' + order by transaction_id + limit 1 + + + + + insert into ec_financial_transactions - (transaction_id, order_id, refund_id, creditcard_id, transaction_amount, transaction_type, inserted_date) - values - (:transaction_id, :order_id, :refund_id, :creditcard_id, :cash_amount_to_refund, 'refund', current_timestamp) - - - + (transaction_id, refunded_transaction_id, order_id, refund_id, creditcard_id, transaction_amount, transaction_type, inserted_date, to_be_captured_date) + values + (:refund_transaction_id, :charged_transaction_id, :order_id, :refund_id, :creditcard_id, :refund_amount, 'refund', current_timestamp, :scheduled_hour) + + - - - + + + update ec_financial_transactions + set refunded_amount = coalesce(refunded_amount, 0) + :refund_amount + where transaction_id = :charged_transaction_id + + + + + + select transaction_id as charged_transaction_id, (transaction_amount - coalesce(refunded_amount, 0)) as unrefunded_amount, marked_date + from ec_financial_transactions + where order_id = :order_id + and transaction_type = 'charge' + and (transaction_amount - coalesce(refunded_amount, 0)) > 0.01::numeric + and marked_date is not null + and failed_p = 'f' + order by (transaction_amount - coalesce(refunded_amount, 0)) desc + limit 1 + + + + + + insert into ec_financial_transactions + (transaction_id, refunded_transaction_id, order_id, refund_id, creditcard_id, transaction_amount, transaction_type, inserted_date, to_be_captured_date) + values + (:refund_transaction_id, :charged_transaction_id, :order_id, :refund_id, :creditcard_id, :unrefunded_amount, 'refund', current_timestamp, :scheduled_hour) + + + + + + update ec_financial_transactions + set refunded_amount = coalesce(refunded_amount, 0) + :unrefunded_amount + where transaction_id = :charged_transaction_id + + + + + select ec_order_gift_cert_amount(:order_id) - - + + - - - + + insert into ec_problems_log - (problem_id, problem_date, problem_details, order_id) - values - (ec_problem_id_sequence.nextval, current_timestamp, :errorstring, :order_id) - - - + (problem_id, problem_date, problem_details, order_id) + values + (ec_problem_id_sequence.nextval, current_timestamp, :errorstring, :order_id) + + - - - - select gift_certificate_amount_left(:cert) - - + + + select u.gift_certificate_id, coalesce(sum(u.amount_used),0) - coalesce(sum(u.amount_reinstated),0) as reinstateable_amount + from ec_gift_certificate_usage u, ec_gift_certificates c + where u.gift_certificate_id = c.gift_certificate_id + and u.order_id = :order_id + group by u.gift_certificate_id, c.expires + order by expires desc, gift_certificate_id desc + + - - - + + insert into ec_gift_certificate_usage - (gift_certificate_id, order_id, amount_reinstated, reinstated_date) - values - (:cert, :order_id, :iteration_reinstate_amount, current_timestamp) - - - + (gift_certificate_id, order_id, amount_reinstated, reinstated_date) + values + (:gift_certificate_id, :order_id, least(:certificate_amount_to_reinstate, :reinstateable_amount), current_timestamp) + + - - - + + insert into ec_problems_log - (problem_id, problem_date, problem_details, order_id) - values - (ec_problem_id_sequence.nextval, current_timestamp, :errorstring, :order_id) - - - + (problem_id, problem_date, problem_details, order_id) + values + (ec_problem_id_sequence.nextval, current_timestamp, :errorstring, :order_id) + + - - - - update ec_financial_transactions set refunded_date=current_timestamp where transaction_id=:transaction_id - - + + + update ec_financial_transactions + set refunded_date = current_timestamp + where transaction_id=:pgw_transaction_id + + - + + + update ec_financial_transactions + set to_be_captured_date = current_timestamp + where transaction_id = :transaction_id + + +