ec_sweep_for_cybercash_zombie_gift_certificates

ec_sweep_for_cybercash_zombie_gift_certificates

Looks for confirmed gift certificates that aren't either failed or authorized, i.e., where we didn't hear back from CyberCash

Source code:

    # cron job to dig up confirmed but not failed or authorized gift certificates
    # over 15 minutes old ("zombies")
    # These only happen when we go off to CyberCash to authorize a transaction
    # but either we got no response or the response indicated nothing about
    # whether the card was actually valid.  It can also happen if the consumer
    # pushes reload after the gift certificate is inserted into the database but
    # before it goes through to CyberCash.
    #
    # This is similar to ec_sweep_for_cybercash_zombies except that inconclusiveness
    # is not tolerated in the case of gift certificates.  If it's inconclusive we
    # fail it and send a note telling them to reorder.
    #
    # OVERALL STRATEGY
    # (1) query CyberCash to see if they have a record of the transaction
    # (2) if CyberCash has the record and it was successful,
    #     update gift_certificate_state to authorized_plus/minus_avs
    # (3) if CyberCash doesn't have the transaction or it was inconclusive,
    #     retry order
    #     (a) if successful, update gift_certificate_state to authorized_*_avs
    #     (c) if inconclusive or failure, change gift_certificate_state failed_authorization
    
    ns_log Notice "ec_sweep_for_cybercash_zombie_gift_certficiates starting"
    set dblist [ns_db gethandle [philg_server_default_pool] 2]
    set db [lindex $dblist 0]
    set db2 [lindex $dblist 1]
    
    # Note that the outer loop uses $db2, so use $db within it
    set selection [ns_db select $db2 "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' 
    and (sysdate - g.issue_date) > 1/96"]

    while { [ns_db getrow $db2 $selection] } {
	set_variables_after_query
	ns_log Notice "ec_sweep_for_cybercash_zombies working on order $order_id"
	
	# there's a 1-1 correspondence between user-purchased gift certificates and
	# financial transactions
	set transaction_id [database_to_tcl_string $db "select transaction_id from ec_financial_transactions where gift_certificate_id=$gift_certificate_id"]
	
	# Query CyberCash:
	set cc_args [ns_set new]
	ns_set put $cc_args "order-id" "$transaction_id"
	ns_set put $cc_args "txn-type" "auth"
	
	set ttcc_output [ec_talk_to_cybercash "query" $cc_args]
	
	set txn_status [ns_set get $ttcc_output "txn_status"]
	set avs_code [ns_set get $ttcc_output "avs_code"]

	if { [empty_string_p $txn_status] } {
	    # no response; inconclusive=failure for gift certificates
	    set cybercash_status "failure"
	} elseif { $txn_status == "success" || $txn_status == "success-duplicate" } {
	    set cybercash_status "success"
	} elseif { $txn_status == "failure-q-or-cancel" || $txn_status == "pending" } {
	    # we'll retry once
	    ns_log Notice "Retrying failure-q-or-cancel gift certificate # $gift_certificate_id (transaction # $transaction_id)"
	    set cc_args [ns_set new]
	    ns_set put $cc_args "txn-type" "auth"
	    ns_set put $cc_args "order-id" "$transaction_id"
	    
	    set ttcc_output [ec_talk_to_cybercash "retry" $cc_args]
	    set txn_status [ns_set get $ttcc_output "txn_status"]
	    set errmsg [ns_set get $ttcc_output "errmsg"]
	    set avs_code [ns_set get $ttcc_output "avs_code"]

	    if {[regexp {success} $txn_status]} {
		set cybercash_status "success"
	    } else {
		set cybercash_status "failure"
	    }
	} else {
	    set cybercash_status "failure"
	}

	# Now deal with the cybercash_status:
	# 1. If success, update transaction and gift certificate to authorized, 
	#    and send gift certificate order email
	# 2. If failure, update gift certificate and transaction to failed,
	#    and send gift certificate order failure email

	if { $cybercash_status == "success" } {
	    if { [ ec_avs_acceptable_p $avs_code ] == 1 } {
		set cc_result "authorized_plus_avs"
	    } else {
		set cc_result "authorized_minus_avs"
	    }
	    # update transaction and gift certificate to authorized
	    # setting to_be_captured_p to 't' will cause ec_unmarked_transactions to come along and mark it for capture
	    ns_db dml $db "update ec_financial_transactions set authorized_date=sysdate, to_be_captured_p='t' where transaction_id=$transaction_id"
	    ns_db dml $db "update ec_gift_certificates set authorized_date=sysdate, gift_certificate_state='$cc_result' where gift_certificate_id=$gift_certificate_id"

	    # send gift certificate order email
	    ec_email_new_gift_certificate_order $gift_certificate_id

	} else {
	    # we probably don't need to do this update of to_be_captured_p because no cron jobs
	    # distinguish between null and 'f' right now, but it doesn't hurt and it might alleviate
	    # someone's concern when they're looking at ec_financial_transactions and wondering
	    # whether they should be concerned that failed_p is 't'
	    ns_db dml $db "update ec_financial_transactions set failed_p='t', to_be_captured_p='f' where transaction_id=$transaction_id"
	    ns_db dml $db "update ec_gift_certificates set gift_certificate_state='failed_authorization' where gift_certificate_id=$gift_certificate_id"

	    # send gift certificate order failure email
	    ec_email_gift_certificate_order_failure $gift_certificate_id
	}
    }

    ns_db releasehandle $db
    ns_db releasehandle $db2 
    ns_log Notice "ec_sweep_for_cybercash_zombie_gift_certificates finishing"