ec_sweep_for_cybercash_zombies

ec_sweep_for_cybercash_zombies

Looks for confirmed orders 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 orders
    # over 15 minutes old ("zombies")
    # These only happen when we go off to CyberCash to authorize an order
    # but either we got no response or the response indicated nothing about
    # whether the card was actually valid.  It also happens if the consumer
    # pushes reload after the order is inserted into the database but
    # before it goes through to CyberCash.
    #
    # OVERALL STRATEGY
    # (1) query CyberCash to see if they have a record of the order
    # (2) if CyberCash has the record and it was successful,
    #     update order_state to authorized_plus/minus_avs
    # (3) if CyberCash doesn't have the order or it was inconclusive,
    #     retry order
    #     (a) if successful, update order_state to authorized_*_avs
    #     (b) if inconclusive, leave in this state
    #     (c) if a definite failure, change order_state failed_authorization
    #         (it will later be moved back to in_basket by ec_delayed_credit_denied)

    
    ns_log Notice "ec_sweep_for_cybercash_zombies 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 order_id
    from ec_orders
    where order_state = 'confirmed' 
    and (sysdate - confirmed_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"
	
	set total_order_price [database_to_tcl_string $db "select ec_order_cost($order_id) from dual"]
	
	if { $total_order_price == 0 } {
	    set auth_status_is_now "success"
	} else {
	    
	    set transaction_id [database_to_tcl_string $db "select max(transaction_id)
	    from ec_financial_transactions
	    where order_id=$order_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"]
	    
	    if { [regexp {success} $txn_status] } {
		set auth_status_is_now "success"
	    } elseif { [empty_string_p $txn_status] || [regexp {failure} $txn_status] } {
		# Retry the authorization
		set new_cc_status [ec_creditcard_authorization $db $order_id]
		if { $new_cc_status == "authorized_plus_avs" || $new_cc_status == "authorized_minus_avs" } {
		    set auth_status_is_now "success"
		} elseif { $new_cc_status == "failed_authorization" } {
		    set auth_status_is_now "failure"
		} else {
		    set auth_status_is_now "lack_of_success"
		    if { $new_cc_status == "invalid_input" } {
			ns_log Notice "invalid input to ec_creditcard_authorization in ec_sweep_for_cybercash_zombies "
		    }
		}
	    } elseif { [regexp {pending} $txn_status] } {
		# We need to retry the auth using the API call "retry"
		
		set cc_args_2 [ns_set new]
		ns_set put $cc_args_2 "order-id" "$transaction_id"
		ns_set put $cc_args_2 "txn-type" "auth"
		
		set ttcc_output_2 [ec_talk_to_cybercash "retry" $cc_args_2]
		
		if { [regexp {success} [ns_set get $ttcc_output_2 "txn_status"]] } {
		    set auth_status_is_now "success"
		} else {
		    set auth_status_is_now "lack_of_success"
		    # This proc won't do anything with it in this case.  It'll
		    # be caught next time around (ec_creditcard_authorization
		    # knows how to interpret the various failure messages).
		}
	    } else {
		# weird result, which we don't know what to do with.  We should just leave
		# the order_state alone and let it be subjected to this proc again in
		# another half-hour, by which time things may have cleared up.
		set auth_status_is_now "unknown"
		
		ns_db dml $db "insert into ec_problems_log
		(problem_id, problem_date, problem_details, order_id)
		values
		(ec_problem_id_sequence.nextval, sysdate, 'Strange CyberCash result when querying about auth: [DoubleApos [ns_set get $ttcc_output_2 "txn_status"]]', [ns_dbquotevalue $order_id integer])
		"
	    }
	    # end of non-free order section
	}
	
	
	# If the auth_status_is_now is "success" or "failure", then we want to 
	# update the order state.  Otherwise, the order
	# stays in the confirmed state.
	
	if { $auth_status_is_now == "success" } {
	    if { $total_order_price > 0 } {
		
		# get avs code from CyberCash log for most recent row containing this
		# order_id
		set sub_selection [ns_db 1row $db "select avs_code 
		from ec_cybercash_log where transaction_id=$transaction_id 
		and txn_attempted_time = (select MAX(txn_attempted_time) from ec_cybercash_log log2 where log2.transaction_id=$transaction_id)"]
		
		set_variables_after_subquery
		if { [ec_avs_acceptable_p $avs_code] == 1 } {
		    set new_order_state "authorized_plus_avs"
		} else {
		    set new_order_state "authorized_minus_avs"
		}
	    } else {
		set new_order_state "authorized_plus_avs"
	    }
	    # update the order_state
	    ec_update_state_to_authorized $db $order_id [ec_decode $new_order_state "authorized_plus_avs" "t" "f"]
	} elseif { $auth_status_is_now == "failure" } {
	    # this will get changed to in_basket by the ec_delayed_credit_denied proc
	    set new_order_state "failed_authorization"
	    ns_db dml $db "update ec_orders set order_state='$new_order_state' where order_id=$order_id"
	}
    }
    
    ns_db releasehandle $db
    ns_db releasehandle $db2 
    ns_log Notice "ec_sweep_for_cybercash_zombies finishing"