Finishing up


As promised, here are some of the results from 2 weeks of observing completing IPNs. Below is a nice looking figure that shows when pending transactions are completed per day based on the date the transaction was initiated. Notably, there is a huge peak on the first day, but also on the third and fifth days, which is the time indicated on the PayPal website for the completing of echeck payments (one of our significant pending transactions).

part1

You can also see this trend in the next two plots where the major peaks on the total pending transactions completing line up with the peaks on only echeck payments completing. Also the significance of echeck payments completing is shown via the units in plot 3, which represent the percent of echeck payments that completed out of the total each day represented in plot 2.

part2

part3

Look at how many of the pending transactions are completed with IPNs! Two weeks seems to be an appropriate upper limit for wait time after the pending transaction was created.

completing

Obviously our efforts to obtain the IPN to complete payments are not fruitless.

So next we actually want to do something with the IPN that’s not simply looking at it. So back to the initial goal of updating the fee amount, our next step would be recording the updated fee in the tables. We could easily just write a table insert for IPNs that looked something like

UPDATE paypal_auths 
SET paypal_fee=#{fee_amt} 
WHERE paypal_transaction_id=#{txn_id}

but this isn’t a completely effective way to do it because we will never know which transactions were pending or which ones are expected to update. To see this we’ve added two columns to the table, pending reason and date completed. We need to update the table to reflect this, and normally you could just make a change like this:

ALTER TABLE paypal_auths
ADD (pending_reason CHAR(1) CHARACTER SET ascii, date_completed DATETIME);

using a preset value to distinguish what single characters represent which kind of pending reason (defined below) so we only need to use one char per row for the pending reason.

ADDRESS - a
AUTHORIZATION - z
ECHECK - e
INTL - i
MULTI_CURRENCY - c
ORDER - o
PAYMENTREVIEW - r
UNILATERAL - u
UPGRADE - y
VERIFY - v
OTHER - x

Bandcamp has support in code for migrations to the table. A schema is a snapshot of the specs of a table. A new schema is made every time there is a change to the table and the tables are then diff’d to the new layout. This is useful because each engineer has their own database to update and being able to simply update your code with the most recent schema and restart the app to update your table is very efficient.

Once the table is ready to be updated, we need to properly process the IPNs by implementing the specs described in What is an IPN? And that completed looks like this inside the IPN listener:

query_string = request.body.string

# Contact PP again to verify the IPN
verify = PayPalApi.do_notify_validate(query_string) # makes an api call to paypal
if !verify.has_key?("VERIFIED")
    if !verify.has_key("INVALID")
        BC::Log.warn("This IPN is not verified: #{verify}.  txn_id=#{txn_id}")
        respond('Not Found', 404)
    end
    BC::Log.warn("This IPN is INVALID. txn_id=#{txn_id}")
    respond('OK', 200)
end

if request["payment_status"] != "Completed"
    respond('OK', 200)
end

q_txn_id = SQL.escape(txn_id)
bc_transaction = SQL.query("SELECT seller_paypal_email, sub_total, currency, 
                                   paypal_pending_reason, paypal_pending_completed_date
                            FROM   paypal_auths WHERE paypal_transaction_id = #{q_txn_id}").first

if !bc_transaction
    respond('OK', 200)
else
    if request["receiver_email"] != bc_transaction.seller_paypal_email
        respond('OK', 200)
    end

    if request["mc_gross"].to_f != bc_transaction.sub_total.to_f
        respond('OK', 200)
    end

    if request["mc_currency"] != bc_transaction.currency
        respond('OK', 200)
    end

    if !bc_transaction.paypal_pending_completed_date && bc_transaction.paypal_pending_reason
        # Update paypal_auth
        fee_amt = request.params["mc_fee"]
        q_fee_amt = SQL.escape(fee_amt)
        sql = "UPDATE paypal_auths 
               SET completed_date = UTC_TIMESTAMP(), fee_amt = #{q_fee_amt}
               WHERE paypal_transaction_id=#{q_txn_id}"
        SQL.update(sql)
    end
end

Success! We are now inserting the newly acquired fee amount via IPNs retrieved for pending payments. We can even see those changes in the CSVs! This is great, but is that it?

We also want to indicate in the band’s sales report where the fee is expected to be returned. Right now an empty fee_amt cell means the item is part of a cart of items and 0 means the fee could be pending, or is really 0. So we want to add a marker to note that the fee is pending and expected to be filled in eventually, and have 0 only mean there is no fee. This marker is going to be an asterisk that will be added to the CSV upon download by using this logic:

if item[:pending_reason] && !item[:completed_date] && item[:fee_amt].to_f == 0.0
    item[:fee_amt] = '*'
end

If you have a band, you will see this change by downloading your sales and importing them into a spreadsheet program. It should look something like this:

with_asterisk

And 14 weeks later, it looks like that’s it, my final project is completed.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s