How to Test PayPal Instant Payment Notification (IPN)
Some of the questions I see most in the PayPal developer forum regarding IPN is how to initially configure it and test it. In a previous blog I posted a PHP IPN template that you can use to get passed most of the configuration hurdles. Testing your solution is another story, though. Here are the steps I like to take to ensure flawless IPN integration.
1 – Local IPN Testing
The first thing I do is create a local HTML form that consists of hidden fields which resemble the same thing PayPal’s system would post to my server when an IPN occurs. Here’s a sample.
[sourcecode language=”html”]
[/sourcecode]
When testing this way, the IPN script will run through your Invalid IPN code because the post did not come from PayPal’s server. As such, you’ll need to adjust your IPN script to force a Valid response. If you’re using my IPN template you can adjust line 56 to…
[sourcecode language=”php”]
if(strcmp ($res, “VERIFIED”) == 0 || 1 == 1)
[/sourcecode]
Then, simply set the action of the form to your IPN listener and then submit it. This way you’ll be able to see the on-screen result of your IPN script and can easily troubleshoot any errors that may exist. You can add or remove any fields you’d like from the test form and you can adjust the names according to what fields you expect to get from the IPN you’re testing for. Once you’re able to successfully run your test IPN within a browser without any errors you can remove the forced Validity and move on to the next step.
2 – PayPal Developer IPN Simulator
PayPal provides an IPN Simulator from within your developer account. You can use the simulator to easily send test IPN’s to your server for a number of different scenarios. At this point you’ll just need to make sure your IPN script is configured to run on the sandbox servers (see the notes at the top of the IPN template) and then these IPN Simulator posts will validate because they are indeed coming from PayPal’s server. Once these simulated IPN’s are working as expected you’re ready to launch.
3 – Deploy
At this point you’re ready to set your IPN script to production mode instead of sandbox mode and then upload it to your live web server. You can enable IPN within your PayPal profile or you can also include the NotifyURL field in your standard payment buttons or API requests to PayPal.
Real-Life Example of the Advantage of PayPal’s Dispute Resolution Center
When you use PayPal as your credit card processing merchant you are protected in many different ways. Standard protection such as Address Verification System (AVS) and Credit Card Security Digits (CVV2) are provided and in most cases should be enough to avoid any problems. However, mistakes happen and sometimes merchandise can be shipped when it should not have been. PayPal’s Dispute Resolution Center can help insure that you will not lose your product in the event of a charge-back due to fraudulant activity. Here’s one example…
The only ATM machine near my home for my bank is located inside a local Price Chopper grocery store. I stopped by to get some cash out of my account one day and while in a rush made a very stupid mistake and left my debit card in the ATM machine. I got back home just about 30 minutes later and I realized what I had done. I immediately went back up to the Price Chopper and went to the customer service desk. They had my debit card there for me and gave it back. Whew!
The next day I logged into my online banking just to make sure nothing funny had happened. Sure enough, I discovered a $3,000 charge to a kitchen and bath web site for a bunch of cabinets that was NOT mine! I immediately called my bank and submitted a dispute on the transaction. They gave me a temporary refund of the funds, explained that an investigation would ensue and that it could take up to 90 days before it was completely settled. I asked “What about this kitchen and bath company? Is anybody going to notify them that this was a fraudulant charge?” I was insured that yes, they would indeed be informed, but it might not be for weeks.
Well, at this point I was feeling pretty stupid about leaving my debit card in the ATM machine and I was also curious about the seller and who might have done this with my card. I personally called the kitchen & bath company and informed them of the fraudulant transaction. Now, even though I made a dumb mistake and left my card in the machine, this is where the seller messed up. They proceeded to ship the merchandise to an address in New Jersey even though the billing address on the credit card (which was included in the order, apparently) was in the Kansas City area, where I’m from. This was a big mistake. Had I not personally called to inform the seller that the fraudulant transaction had taken place the merchandie would have reached its destination and the fraudsters could have very well have gotten away with the product. By the time the bank’s investigation made it back to the seller it would have been too late. The credit card company would then take the funds back from the seller to cover what they put back into my account. Because of the fact that the seller had shipped to an address other than the billing address on the credit card (an AVS mis-match, or as PayPal would call it, and Unconfirmed address) they would not have been covered by any type of seller protection from that credit card merchant. At this point they would have been out their product and the $3,000. Needless to say, they were very pleased that I called and informed them of the problem in time for them to put a stop-shipment on the merchandise and get it routed back to their warehouse. Again, had I not called and informed the seller of this problem they would have had no idea it was fraudulant until the bank contacted them up to 90 days later. With PayPal’s Dispute Resolution Center this would not have been a problem.
The Dispute Resolution Center is an area of your PayPal account where you can easily manage any disputes/chargebacks that might occur from transactions you have processed. In the example above, if the seller had used PayPal to process the credit card they would have received a new dispute notification when I initially filed the charge-back with my bank. Then, even if I hadn’t called them to inform them of the problem they would have gotten notification from the PayPal dispute area and would have still had time to put a stop-shipment on the goods. Also, with PayPal’s new Expanded Seller Protection program the seller may have been eligble even after shipping to the Unconfirmed address. If not, there would have been red flags in the transaction details page within the PayPal account warning the seller not to ship the merchandise without further investigation.
This is just one example of how using PayPal as your merchant processor can save valuable time and keep dispute losses to a minimum.
PayPal Instant Payment Notification (IPN) PHP Template
NOTE: This template has been turned into an installable solution available at PayPalDeveloper.com. The template here will still work fine, but you might like the full solution better.
PayPal’s IPN feature is a great way to easily integrate standard payment buttons or automate back-end procedures for Payments Pro solutions. While it’s actually very simple, the process can be confusing when you first begin developing and I’ve seen people give up before they even truly get started.
The following script is a basic PHP template that handles everything you need to get started with PayPal Instant Payment Notification. There are a couple of simple options at the top of the script for handling test servers and SSL. The template handles verifying the POST data with PayPal’s servers to ensure it’s valid and then stores every possible IPN value in PHP variables that are ready for use. If a particular field was not included in the IPN received then it’s simply populated with an empty value, but it’s still available within the script.
You can begin at the very bottom and simply do whatever you want with the store data, whether it’s update a database, format an email and send it to customers, send a text message notification to customers or yourself, etc.
I’ve also included a SQL file you can use to easily create database tables for use with PayPal’s IPN system. Later I’ll be completing this entire solution as an easily installed all-in-one package.
Happy scripting!
[sourcecode language=”php”]
$value)
{
$value = urlencode(stripslashes($value));
$req .= “&$key=$value”;
$IPNDecoded .= $key . ” = ” . urldecode($value) .”
“;
}
// post back to PayPal system to validate using SSL or not based on flag set above.
if($ssl)
{
$header = ”;
$header .= “POST /cgi-bin/webscr HTTP/1.0rn”;
$header .= “Host: ” . $ppHost . “:443rn”;
$header .= “Content-Type: application/x-www-form-urlencodedrn”;
$header .= “Content-Length: ” . strlen($req) . “rnrn”;
$fp = fsockopen (‘ssl://’ . $ppHost, 443, $errno, $errstr, 30);
}
else
{
$header = ”;
$header .= “POST /cgi-bin/webscr HTTP/1.0rn”;
$header .= “Host: ” . $ppHost . “:80rn”;
$header .= “Content-Type: application/x-www-form-urlencodedrn”;
$header .= “Content-Length: ” . strlen($req) . “rnrn”;
$fp = fsockopen ($ppHost, 80, $errno, $errstr, 30);
}
if (!$fp)
{
$IsValid = false;
}
else
{
// Response from PayPal was good. Now check to see if it returned verified or invalid. Simply set $IsValud to true/false accordingly.
fputs ($fp, $header . $req);
while(!feof($fp))
{
$res = fgets ($fp, 1024);
if(strcmp ($res, “VERIFIED”) == 0)
$IsValid = true;
elseif (strcmp ($res, “INVALID”) == 0)
$IsValid = false;
}
fclose ($fp);
}
// Buyer Information
$address_city = isset($_POST[‘address_city’]) ? $_POST[‘address_city’] : ”;
$address_country = isset($_POST[‘address_country’]) ? $_POST[‘address_country’] : ”;
$address_country_code = isset($_POST[‘address_country_code’]) ? $_POST[‘address_country_code’] : ”;
$address_name = isset($_POST[‘address_name’]) ? $_POST[‘address_name’] : ”;
$address_state = isset($_POST[‘address_state’]) ? $_POST[‘address_state’] : ”;
$address_status = isset($_POST[‘address_status’]) ? $_POST[‘address_status’] : ”;
$address_street = isset($_POST[‘address_street’]) ? $_POST[‘address_street’] : ”;
$address_zip = isset($_POST[‘address_zip’]) ? $_POST[‘address_zip’] : ”;
$first_name = isset($_POST[‘first_name’]) ? $_POST[‘first_name’] : ”;
$last_name = isset($_POST[‘last_name’]) ? $_POST[‘last_name’] : ”;
$payer_business_name = isset($_POST[‘payer_business_name’]) ? $_POST[‘payer_business_name’] : ”;
$payer_email = isset($_POST[‘payer_email’]) ? $_POST[‘payer_email’] : ”;
$payer_id = isset($_POST[‘payer_id’]) ? $_POST[‘payer_id’] : ”;
$payer_status = isset($_POST[‘payer_status’]) ? $_POST[‘payer_status’] : ”;
$contact_phone = isset($_POST[‘contact_phone’]) ? $_POST[‘contact_phone’] : ”;
$residence_country = isset($_POST[‘residence_country’]) ? $_POST[‘residence_country’] : ”;
// Basic Information
$notify_version = isset($_POST[‘notify_version’]) ? $_POST[‘notify_version’] : ”;
$charset = isset($_POST[‘charset’]) ? $_POST[‘charset’] : ”;
$business = isset($_POST[‘business’]) ? $_POST[‘business’] : ”;
$item_name = isset($_POST[‘item_name’]) ? $_POST[‘item_name’] : ”;
$item_number = isset($_POST[‘item_number’]) ? $_POST[‘item_number’] : ”;
$quantity = isset($_POST[‘quantity’]) ? $_POST[‘quantity’] : ”;
$receiver_email = isset($_POST[‘receiver_email’]) ? $_POST[‘receiver_email’] : ”;
$receiver_id = isset($_POST[‘receiver_id’]) ? $_POST[‘receiver_id’] : ”;
// Cart Items
$num_cart_items = isset($_POST[‘num_cart_items’]) ? $_POST[‘num_cart_items’] : ”;
$i = 1;
$cart_items = array();
while(isset($_POST[‘item_number’ . $i]))
{
$item_number = isset($_POST[‘item_number’ . $i]) ? $_POST[‘item_number’ . $i] : ”;
$item_name = isset($_POST[‘item_name’ . $i]) ? $_POST[‘item_name’ . $i] : ”;
$quantity = isset($_POST[‘quantity’ . $i]) ? $_POST[‘quantity’ . $i] : ”;
$mc_gross = isset($_POST[‘mc_gross_’ . $i]) ? $_POST[‘mc_gross_’ . $i] : ”;
$mc_handling = isset($_POST[‘mc_handling’ . $i]) ? $_POST[‘mc_handling’ . $i] : ”;
$mc_shipping = isset($_POST[‘mc_shipping’ . $i]) ? $_POST[‘mc_shipping’ . $i] : ”;
$custom = isset($_POST[‘custom’ . $i]) ? $_POST[‘custom’ . $i] : ”;
$option_name1 = isset($_POST[‘option_name1_’ . $i]) ? $_POST[‘option_name1_’ . $i] : ”;
$option_selection1 = isset($_POST[‘option_selection1_’ . $i]) ? $_POST[‘option_selection1_’ . $i] : ”;
$option_name2 = isset($_POST[‘option_name2_’ . $i]) ? $_POST[‘option_name2_’ . $i] : ”;
$option_selection2 = isset($_POST[‘option_selection2_’ . $i]) ? $_POST[‘option_selection2_’ . $i] : ”;
$current_item = array(
‘item_number’ => $item_number,
‘item_name’ => $item_name,
‘quantity’ => $quantity,
‘mc_gross’ => $mc_gross,
‘mc_handling’ => $mc_handling,
‘mc_shipping’ => $mc_shipping,
‘custom’ => $custom,
‘option_name1’ => $option_name1,
‘option_selection1’ => $option_selection1,
‘option_name2’ => $option_name2,
‘option_selection2’ => $option_selection2
);
array_push($cart_items, $current_item);
$i++;
}
// Advanced and Custom Information
$custom = isset($_POST[‘custom’]) ? $_POST[‘custom’] : ”;
$invoice = isset($_POST[‘invoice’]) ? $_POST[‘invoice’] : ”;
$memo = isset($_POST[‘memo’]) ? $_POST[‘memo’] : ”;
$option_name1 = isset($_POST[‘option_name1’]) ? $_POST[‘option_name1’] : ”;
$option_selection1 = isset($_POST[‘option_selection1’]) ? $_POST[‘option_selection1’] : ”;
$option_name2 = isset($_POST[‘option_name2’]) ? $_POST[‘option_name2’] : ”;
$option_selection2 = isset($_POST[‘option_selection2’]) ? $_POST[‘option_selection2’] : ”;
$tax = isset($_POST[‘tax’]) ? $_POST[‘tax’] : ”;
// Website Payments Standard, Website Payments Pro, and Refund Information
$auth_id = isset($_POST[‘auth_id’]) ? $_POST[‘auth_id’] : ”;
$auth_exp = isset($_POST[‘auth_exp’]) ? $_POST[‘auth_exp’] : ”;
$auth_amount = isset($_POST[‘auth_amount’]) ? $_POST[‘auth_amount’] : ”;
$auth_status = isset($_POST[‘auth_status’]) ? $_POST[‘auth_status’] : ”;
// Fraud Management Filters
$i = 1;
$fraud_management_filters = array();
while(isset($_POST[‘fraud_management_filters_’ . $i]))
{
$filter_name = isset($_POST[‘fraud_management_filter_’ . $i]) ? $_POST[‘fraud_management_filter_’ . $i] : ”;
array_push($fraud_management_filters, $filter_name);
$i++;
}
$mc_gross = isset($_POST[‘mc_gross’]) ? $_POST[‘mc_gross’] : ”;
$mc_handling = isset($_POST[‘mc_handling’]) ? $_POST[‘mc_handling’] : ”;
$mc_shipping = isset($_POST[‘mc_shipping’]) ? $_POST[‘mc_shipping’] : ”;
$mc_fee = isset($_POST[‘mc_fee’]) ? $_POST[‘mc_fee’] : ”;
$num_cart_items = isset($_POST[‘num_cart_items’]) ? $_POST[‘num_cart_items’] : ”;
$parent_txn_id = isset($_POST[‘parent_txn_id’]) ? $_POST[‘parent_txn_id’] : ”;
$payment_date = isset($_POST[‘payment_date’]) ? $_POST[‘payment_date’] : ”;
$payment_status = isset($_POST[‘payment_status’]) ? $_POST[‘payment_status’] : ”;
$payment_type = isset($_POST[‘payment_type’]) ? $_POST[‘payment_type’] : ”;
$pending_reason = isset($_POST[‘pending_reason’]) ? $_POST[‘pending_reason’] : ”;
$protection_eligibility = isset($_POST[‘protection_eligibility’]) ? $_POST[‘protection_eligibility’] : ”;
$reason_code = isset($_POST[‘reason_code’]) ? $_POST[‘reason_code’] : ”;
$remaining_settle = isset($_POST[‘remaining_settle’]) ? $_POST[‘remaining_settle’] : ”;
$shipping_method = isset($_POST[‘shipping_method’]) ? $_POST[‘shipping_method’] : ”;
$shipping = isset($_POST[‘shipping’]) ? $_POST[‘shipping’] : ”;
$tax = isset($_POST[‘tax’]) ? $_POST[‘tax’] : ”;
$transaction_entity = isset($_POST[‘transaction_entity’]) ? $_POST[‘transaction_entity’] : ”;
$txn_id = isset($_POST[‘txn_id’]) ? $_POST[‘txn_id’] : ”;
$txn_type = isset($_POST[‘txn_type’]) ? $_POST[‘txn_type’] : ”;
// Currency and Currency Exchange Information
$exchange_rate = isset($_POST[‘exchange_rate’]) ? $_POST[‘exchange_rate’] : ”;
$mc_currency = isset($_POST[‘mc_currency’]) ? $_POST[‘mc_currency’] : ”;
$settle_amount = isset($_POST[‘settle_amount’]) ? $_POST[‘settle_amount’] : ”;
$settle_currency = isset($_POST[‘settle_currency’]) ? $_POST[‘settle_currency’] : ”;
// Auction Variables
$auction_buyer_id = isset($_POST[‘auction_buyer_id’]) ? $_POST[‘auction_buyer_id’] : ”;
$auction_closing_date = isset($_POST[‘auction_closing_date’]) ? $_POST[‘auction_closing_date’] : ”;
$auction_multi_item = isset($_POST[‘auction_multi_item’]) ? $_POST[‘auction_multi_item’] : ”;
$for_auction = isset($_POST[‘for_auction’]) ? $_POST[‘for_auction’] : ”;
// Mass Payments
$i = 1;
$mass_payments = array();
while(isset($_POST[‘masspay_txn_id_’ . $i]))
{
$masspay_txn_id = isset($_POST[‘masspay_txn_id_’ . $i]) ? $_POST[‘masspay_txn_id_’ . $i] : ”;
$mc_currency = isset($_POST[‘mc_currency_’ . $i]) ? $_POST[‘mc_currency_’ . $i] : ”;
$mc_fee = isset($_POST[‘mc_fee_’ . $i]) ? $_POST[‘mc_fee_’ . $i] : ”;
$mc_gross = isset($_POST[‘mc_gross_’ . $i]) ? $_POST[‘mc_gross_’ . $i] : ”;
$receiver_email = isset($_POST[‘receiver_email_’ . $i]) ? $_POST[‘receiver_email_’ . $i] : ”;
$status = isset($_POST[‘status_’ . $i]) ? $_POST[‘status_’ . $i] : ”;
$unique_id = isset($_POST[‘unique_id_’ . $i]) ? $_POST[‘unique_id_’ . $i] : ”;
$current_payment_data_set = array(
‘masspay_txn_id’ => $masspay_txn_id,
‘mc_currency’ => $mc_currency,
‘mc_fee’ => $mc_fee,
‘mc_gross’ => $mc_gross,
‘receiver_email’ => $receiver_email,
‘status’ => $status,
‘unique_id’ => $unique_id
);
array_push($mass_payments, $current_payment_data_set);
$i++;
}
// Recurring Payments Information
$initial_payment_status = isset($_POST[‘initial_payment_status’]) ? $_POST[‘initial_payment_status’] : ”;
$initial_payment_txn_id = isset($_POST[‘initial_payment_txn_id’]) ? $_POST[‘initial_payment_txn_id’] : ”;
$recurring_payment_id = isset($_POST[‘recurring_payment_id’]) ? $_POST[‘recurring_payment_id’] : ”;
$product_name = isset($_POST[‘product_name’]) ? $_POST[‘product_name’] : ”;
$product_type = isset($_POST[‘product_type’]) ? $_POST[‘product_type’] : ”;
$period_type = isset($_POST[‘period_type’]) ? $_POST[‘period_type’] : ”;
$payment_cycle = isset($_POST[‘payment_cycle’]) ? $_POST[‘payment_cycle’] : ”;
$outstanding_balance = isset($_POST[‘outstanding_balance’]) ? $_POST[‘outstanding_balance’] : ”;
$amount_per_cycle = isset($_POST[‘amount_per_cycle’]) ? $_POST[‘amount_per_cycle’] : ”;
$initial_payment_amount = isset($_POST[‘initial_payment_amount’]) ? $_POST[‘initial_payment_amount’] : ”;
$profile_status = isset($_POST[‘profile_status’]) ? $_POST[‘profile_status’] : ”;
$amount = isset($_POST[‘amount’]) ? $_POST[‘amount’] : ”;
$time_created = isset($_POST[‘time_created’]) ? $_POST[‘time_created’] : ”;
$next_payment_date = isset($_POST[‘next_payment_date’]) ? $_POST[‘next_payment_date’] : ”;
$rp_invoice_id = isset($_POST[‘rp_invoice_id’]) ? $_POST[‘rp_invoice_id’] : ”;
// Subscription Variables
$subscr_date = isset($_POST[‘subscr_date’]) ? $_POST[‘subscr_date’] : ”;
$subscr_effective = isset($_POST[‘subscr_effective’]) ? $_POST[‘subscr_effective’] : ”;
$period1 = isset($_POST[‘period1’]) ? $_POST[‘period1’] : ”;
$period2 = isset($_POST[‘period2’]) ? $_POST[‘period2’] : ”;
$period3 = isset($_POST[‘period3’]) ? $_POST[‘period3’] : ”;
$amount1 = isset($_POST[‘amount1’]) ? $_POST[‘amount1’] : ”;
$amount2 = isset($_POST[‘amount2’]) ? $_POST[‘amount2’] : ”;
$amount3 = isset($_POST[‘amount3’]) ? $_POST[‘amount3’] : ”;
$mc_amount1 = isset($_POST[‘mc_amount1’]) ? $_POST[‘mc_amount1’] : ”;
$mc_amount2 = isset($_POST[‘mc_amount2’]) ? $_POST[‘mc_amount2’] : ”;
$mc_amount3 = isset($_POST[‘mc_amount3’]) ? $_POST[‘mc_amount3’] : ”;
$mc_currency = isset($_POST[‘mc_currency’]) ? $_POST[‘mc_currency’] : ”;
$recurring = isset($_POST[‘recurring’]) ? $_POST[‘recurring’] : ”;
$reattempt = isset($_POST[‘reattempt’]) ? $_POST[‘reattempt’] : ”;
$retry_at = isset($_POST[‘retry_at’]) ? $_POST[‘retry_at’] : ”;
$recur_times = isset($_POST[‘recur_times’]) ? $_POST[‘recur_times’] : ”;
$username = isset($_POST[‘username’]) ? $_POST[‘username’] : ”;
$password = isset($_POST[‘password’]) ? $_POST[‘password’] : ”;
$subscr_id = isset($_POST[‘subscr_id’]) ? $_POST[‘subscr_id’] : ”;
// Dispute Notification Variables
$case_id = isset($_POST[‘case_id’]) ? $_POST[‘case_id’] : ”;
$case_type = isset($_POST[‘case_type’]) ? $_POST[‘case_type’] : ”;
$case_creation_date = isset($_POST[‘case_creation_date’]) ? $_POST[‘case_creation_date’] : ”;
?>
[/sourcecode]
And here is a SQL file you can use to easily create the database tables.
[sourcecode language=”sql”]
— phpMyAdmin SQL Dump
— version 3.1.1
— http://www.phpmyadmin.net
—
— Host: localhost
— Generation Time: Feb 20, 2009 at 11:17 PM
— Server version: 5.0.67
— PHP Version: 5.2.6
SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”;
—
— Database: `paypal_ipn`
—
— ——————————————————–
—
— Table structure for table `paypal_disputes`
—
CREATE TABLE IF NOT EXISTS `paypal_disputes` (
`id` tinyint(10) NOT NULL auto_increment,
`txn_id` varchar(25) collate utf8_bin NOT NULL,
`case_id` varchar(25) collate utf8_bin NOT NULL,
`case_type` varchar(25) collate utf8_bin NOT NULL,
`case_creation_date` varchar(100) collate utf8_bin NOT NULL,
`payment_date` varchar(100) collate utf8_bin NOT NULL,
`receipt_id` varchar(25) collate utf8_bin NOT NULL,
`verify_sign` varchar(255) collate utf8_bin NOT NULL,
`payer_email` varchar(127) collate utf8_bin NOT NULL,
`payer_id` varchar(20) collate utf8_bin NOT NULL,
`invoice` varchar(127) collate utf8_bin NOT NULL,
`reason_code` varchar(25) collate utf8_bin NOT NULL,
`custom` varchar(255) collate utf8_bin NOT NULL,
`notify_version` varchar(25) collate utf8_bin NOT NULL,
`creation_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;
—
— Dumping data for table `paypal_disputes`
—
— ——————————————————–
—
— Table structure for table `paypal_ipn_log`
—
CREATE TABLE IF NOT EXISTS `paypal_ipn_log` (
`id` tinyint(4) NOT NULL auto_increment,
`created_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
`ipn_data_serialized` text collate utf8_bin NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;
—
— Dumping data for table `paypal_ipn_log`
—
— ——————————————————–
—
— Table structure for table `paypal_mass_payments`
—
CREATE TABLE IF NOT EXISTS `paypal_mass_payments` (
`id` tinyint(4) NOT NULL auto_increment,
`masspay_txn_id` varchar(25) collate utf8_bin NOT NULL,
`mc_currency` varchar(50) collate utf8_bin NOT NULL,
`mc_fee` double NOT NULL,
`mc_gross` double NOT NULL,
`receiver_email` varchar(127) collate utf8_bin NOT NULL,
`status` varchar(25) collate utf8_bin NOT NULL,
`unique_id` varchar(20) collate utf8_bin NOT NULL,
`creation_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;
—
— Dumping data for table `paypal_mass_payments`
—
— ——————————————————–
—
— Table structure for table `paypal_orders`
—
CREATE TABLE IF NOT EXISTS `paypal_orders` (
`id` tinyint(10) unsigned NOT NULL auto_increment,
`receiver_email` varchar(127) collate utf8_bin NOT NULL,
`payment_status` varchar(25) collate utf8_bin NOT NULL,
`pending_reason` varchar(25) collate utf8_bin NOT NULL,
`payment_date` varchar(100) collate utf8_bin NOT NULL,
`mc_gross` double NOT NULL,
`mc_fee` double NOT NULL,
`tax` double NOT NULL,
`mc_currency` varchar(10) collate utf8_bin NOT NULL,
`txn_id` varchar(25) collate utf8_bin NOT NULL,
`txn_type` varchar(25) collate utf8_bin NOT NULL,
`first_name` varchar(75) collate utf8_bin NOT NULL,
`last_name` varchar(75) collate utf8_bin NOT NULL,
`address_street` varchar(200) collate utf8_bin NOT NULL,
`address_city` varchar(50) collate utf8_bin NOT NULL,
`address_state` varchar(40) collate utf8_bin NOT NULL,
`address_zip` varchar(20) collate utf8_bin NOT NULL,
`address_country` varchar(64) collate utf8_bin NOT NULL,
`address_status` varchar(25) collate utf8_bin NOT NULL,
`payer_email` varchar(127) collate utf8_bin NOT NULL,
`payer_status` varchar(25) collate utf8_bin NOT NULL,
`payment_type` varchar(25) collate utf8_bin NOT NULL,
`notify_version` varchar(50) collate utf8_bin NOT NULL,
`verify_sign` varchar(255) collate utf8_bin NOT NULL,
`address_name` varchar(130) collate utf8_bin NOT NULL,
`protection_eligibility` varchar(50) collate utf8_bin NOT NULL,
`ipn_status` varchar(25) collate utf8_bin NOT NULL,
`subscr_id` varchar(25) collate utf8_bin NOT NULL,
`custom` varchar(255) collate utf8_bin NOT NULL,
`reason_code` varchar(25) collate utf8_bin NOT NULL,
`contact_phone` varchar(25) collate utf8_bin NOT NULL,
`item_name` varchar(127) collate utf8_bin NOT NULL,
`item_number` varchar(127) collate utf8_bin NOT NULL,
`invoice` varchar(127) collate utf8_bin NOT NULL,
`for_auction` tinyint(10) NOT NULL,
`auction_buyer_id` varchar(75) collate utf8_bin NOT NULL,
`auction_closing_date` varchar(100) collate utf8_bin NOT NULL,
`auction_multi_item` double NOT NULL default ‘1’,
`creation_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
`address_country_code` varchar(2) collate utf8_bin NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;
—
— Dumping data for table `paypal_orders`
—
— ——————————————————–
—
— Table structure for table `paypal_order_items`
—
CREATE TABLE IF NOT EXISTS `paypal_order_items` (
`id` tinyint(10) NOT NULL auto_increment,
`order_id` tinyint(10) NOT NULL,
`subscr_id` varchar(25) collate utf8_bin NOT NULL,
`item_name` varchar(130) collate utf8_bin NOT NULL,
`item_number` varchar(130) collate utf8_bin NOT NULL,
`os0` varchar(200) collate utf8_bin NOT NULL,
`on0` varchar(75) collate utf8_bin NOT NULL,
`os1` varchar(200) collate utf8_bin NOT NULL,
`on1` varchar(75) collate utf8_bin NOT NULL,
`quantity` double NOT NULL default ‘0’,
`custom` varchar(255) collate utf8_bin NOT NULL,
`mc_gross` double NOT NULL default ‘0’,
`mc_handling` double NOT NULL default ‘0’,
`mc_shipping` double NOT NULL default ‘0’,
`creation_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;
—
— Dumping data for table `paypal_order_items`
—
— ——————————————————–
—
— Table structure for table `paypal_recurring_payments`
—
CREATE TABLE IF NOT EXISTS `paypal_recurring_payments` (
`id` tinyint(10) NOT NULL auto_increment,
`mc_gross` double NOT NULL default ‘0’,
`protection_eligibility` varchar(50) collate utf8_bin NOT NULL,
`payment_date` varchar(100) collate utf8_bin NOT NULL,
`payment_status` varchar(25) collate utf8_bin NOT NULL,
`mc_fee` double NOT NULL default ‘0’,
`notify_version` varchar(25) collate utf8_bin NOT NULL,
`payer_status` varchar(25) collate utf8_bin NOT NULL,
`currency_code` varchar(10) collate utf8_bin NOT NULL,
`verify_sign` varchar(255) collate utf8_bin NOT NULL,
`amount` double NOT NULL default ‘0’,
`txn_id` varchar(25) collate utf8_bin NOT NULL,
`payment_type` varchar(25) collate utf8_bin NOT NULL,
`receiver_email` varchar(130) collate utf8_bin NOT NULL,
`receiver_id` varchar(15) collate utf8_bin NOT NULL,
`txn_type` varchar(25) collate utf8_bin NOT NULL,
`mc_currency` varchar(25) collate utf8_bin NOT NULL,
`residence_country` varchar(2) collate utf8_bin NOT NULL,
`receipt_id` varchar(50) collate utf8_bin NOT NULL,
`transaction_subject` varchar(150) collate utf8_bin NOT NULL,
`shipping` double NOT NULL default ‘0’,
`product_type` varchar(50) collate utf8_bin NOT NULL,
`time_created` varchar(100) collate utf8_bin NOT NULL,
`rp_invoice_id` varchar(127) collate utf8_bin NOT NULL,
`ipn_status` varchar(25) collate utf8_bin NOT NULL,
`creation_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;
—
— Dumping data for table `paypal_recurring_payments`
—
— ——————————————————–
—
— Table structure for table `paypal_recurring_payment_profiles`
—
CREATE TABLE IF NOT EXISTS `paypal_recurring_payment_profiles` (
`id` tinyint(10) NOT NULL auto_increment,
`payment_cycle` varchar(50) collate utf8_bin NOT NULL,
`txn_type` varchar(30) collate utf8_bin NOT NULL,
`last_name` varchar(75) collate utf8_bin NOT NULL,
`first_name` varchar(75) collate utf8_bin NOT NULL,
`next_payment_date` varchar(100) collate utf8_bin NOT NULL,
`residence_country` varchar(2) collate utf8_bin NOT NULL,
`initial_payment_amount` double NOT NULL default ‘0’,
`rp_invoice_id` varchar(127) collate utf8_bin NOT NULL,
`currency_code` varchar(10) collate utf8_bin NOT NULL,
`time_created` varchar(100) collate utf8_bin NOT NULL,
`verify_sign` varchar(255) collate utf8_bin NOT NULL,
`period_type` varchar(25) collate utf8_bin NOT NULL,
`payer_status` varchar(25) collate utf8_bin NOT NULL,
`payer_email` varchar(130) collate utf8_bin NOT NULL,
`receiver_email` varchar(130) collate utf8_bin NOT NULL,
`payer_id` varchar(20) collate utf8_bin NOT NULL,
`product_type` varchar(50) collate utf8_bin NOT NULL,
`payer_business_name` varchar(130) collate utf8_bin NOT NULL,
`shipping` double NOT NULL default ‘0’,
`amount_per_cycle` double NOT NULL default ‘0’,
`profile_status` varchar(25) collate utf8_bin NOT NULL,
`notify_version` varchar(25) collate utf8_bin NOT NULL,
`amount` double NOT NULL default ‘0’,
`outstanding_balance` double NOT NULL default ‘0’,
`recurring_payment_id` varchar(50) collate utf8_bin NOT NULL,
`product_name` varchar(130) collate utf8_bin NOT NULL,
`ipn_status` varchar(25) collate utf8_bin NOT NULL,
`creation_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;
—
— Dumping data for table `paypal_recurring_payment_profiles`
—
— ——————————————————–
—
— Table structure for table `paypal_subscriptions`
—
CREATE TABLE IF NOT EXISTS `paypal_subscriptions` (
`id` tinyint(10) NOT NULL auto_increment,
`custom` varchar(255) collate utf8_bin NOT NULL,
`subscr_id` varchar(25) collate utf8_bin NOT NULL,
`sub_event` varchar(50) collate utf8_bin NOT NULL,
`subscr_date` varchar(100) collate utf8_bin NOT NULL,
`subscr_effective` varchar(100) collate utf8_bin NOT NULL,
`period1` varchar(50) collate utf8_bin NOT NULL,
`period2` varchar(50) collate utf8_bin NOT NULL,
`period3` varchar(50) collate utf8_bin NOT NULL,
`amount1` double NOT NULL default ‘0’,
`amount2` double NOT NULL default ‘0’,
`amount3` double NOT NULL default ‘0’,
`mc_amount1` double NOT NULL default ‘0’,
`mc_amount2` double NOT NULL default ‘0’,
`mc_amount3` double NOT NULL default ‘0’,
`recurring` varchar(10) collate utf8_bin NOT NULL,
`reattempt` varchar(10) collate utf8_bin NOT NULL,
`retry_at` varchar(100) collate utf8_bin NOT NULL,
`recur_times` varchar(25) collate utf8_bin NOT NULL,
`username` varchar(70) collate utf8_bin NOT NULL,
`password` varchar(30) collate utf8_bin NOT NULL,
`txn_id` varchar(25) collate utf8_bin NOT NULL,
`payer_email` varchar(130) collate utf8_bin NOT NULL,
`residence_country` varchar(2) collate utf8_bin NOT NULL,
`mc_currency` varchar(10) collate utf8_bin NOT NULL,
`verify_sign` varchar(255) collate utf8_bin NOT NULL,
`payer_status` varchar(25) collate utf8_bin NOT NULL,
`first_name` varchar(75) collate utf8_bin NOT NULL,
`last_name` varchar(75) collate utf8_bin NOT NULL,
`receiver_email` varchar(130) collate utf8_bin NOT NULL,
`payer_id` varchar(15) collate utf8_bin NOT NULL,
`notify_version` varchar(25) collate utf8_bin NOT NULL,
`item_name` varchar(130) collate utf8_bin NOT NULL,
`item_number` varchar(130) collate utf8_bin NOT NULL,
`ipn_status` varchar(25) collate utf8_bin NOT NULL,
`creation_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;
—
— Dumping data for table `paypal_subscriptions`
—
— ——————————————————–
—
— Table structure for table `paypal_subscription_payments`
—
CREATE TABLE IF NOT EXISTS `paypal_subscription_payments` (
`id` tinyint(10) NOT NULL auto_increment,
`first_name` varchar(75) collate utf8_bin NOT NULL,
`last_name` varchar(75) collate utf8_bin NOT NULL,
`payer_email` varchar(130) collate utf8_bin NOT NULL,
`memo` text collate utf8_bin NOT NULL,
`item_name` varchar(130) collate utf8_bin NOT NULL,
`item_number` varchar(130) collate utf8_bin NOT NULL,
`os0` varchar(200) collate utf8_bin NOT NULL,
`on0` varchar(65) collate utf8_bin NOT NULL,
`os1` varchar(200) collate utf8_bin NOT NULL,
`on1` varchar(65) collate utf8_bin NOT NULL,
`quantity` double NOT NULL default ‘0’,
`payment_date` varchar(100) collate utf8_bin NOT NULL,
`payment_type` varchar(25) collate utf8_bin NOT NULL,
`txn_id` varchar(25) collate utf8_bin NOT NULL,
`mc_gross` double NOT NULL default ‘0’,
`mc_fee` double NOT NULL default ‘0’,
`payment_status` varchar(25) collate utf8_bin NOT NULL,
`pending_reason` varchar(25) collate utf8_bin NOT NULL,
`txn_type` varchar(30) collate utf8_bin NOT NULL,
`tax` double NOT NULL default ‘0’,
`mc_currency` varchar(25) collate utf8_bin NOT NULL,
`reason_code` varchar(25) collate utf8_bin NOT NULL,
`custom` varchar(255) collate utf8_bin NOT NULL,
`address_country` varchar(50) collate utf8_bin NOT NULL,
`subscr_id` varchar(25) collate utf8_bin NOT NULL,
`payer_status` varchar(25) collate utf8_bin NOT NULL,
`ipn_status` varchar(25) collate utf8_bin NOT NULL,
`creation_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;
—
— Dumping data for table `paypal_subscription_payments`
—
[/sourcecode]
PayPal’s eBay Payment Holding Policy, Why I Agree, and How to Work with it.
I seem to be alone on an island with the idea that I actually agree with PayPal’s new system for new or low volume eBay sellers. I understand it can be frustrating and especially confusing for new sellers, however, I believe it will solve more problems than it causes and if you work with the system properly everybody can be happy. I’ll discuss more about the reasons this system has been introduced later. For now, let’s talk about how we can work with the system, because like it or not, if we’re going to sell on eBay, we have to.
USBSwiper 3.3 Released
Don’t let the small jump from 3.2 to 3.3 fool you. The new USBSwiper is packed with feature requests! Take a look at what’s included…
PCI Compliance Updates
In previous versions, credit card data was encrypted, however, it was still stored internally within USBSwiper. With version 3.3 this is no longer the case. Card data is only stored until it’s successfully processed, at which point the data is wiped clean from USBSwiper and only “display” fields are shown (ie. xxxx-xxxx-xxxx-4532).
Invoice Items Updates
The drop-down list in the items area of invoices now sorts alphabetically instead of numerically on the internal ID. Also, the custom item ID field can be edited directly on the Invoices screen to add items rather than through the drop-down list or the search area. This way you can quickly add items if you know your own ID or if you have a bar-code system in place.
Authorization Features Now Included
USBSwiper 3.3 includes the ability to authorize a credit card prior to capturing the final order amount. This feature was requested mostly from restaurant owners who would typically need to apply a tip after authorization the customer’s credit card. You may configure USBSwiper to automatically authorize all new invoices or you may set each individual invoice to authorize only when necessary. The authorization is just for $1.00 and immediately releases the authorization back to the bank. It’s simply used to ensure the card is valid.
Sales Tax, Discount Codes, Handling Fee Updates
These features have been completely revamped to allow multiple options for each. You may now set up a complete table of all sales tax rates you might need. For example, if you charge different rates for different counties/cities you can easily include them. Also, inventory items may be marked as Taxable to ensure only the items you need to tax are included in the sales tax calculations. Discount Codes and Handling Fees can be maintained much like sales tax. You can add as many different codes as you like and each can be either flat-rate or percentage based. You may set defaults for each within your Setup screen and you may adjust invoice individually as well.
Currency Updates
Version 3.3 now allows you to adjust the currency processed on the invoice. You may set a default currency within your Setup screen and you may also adjust the currency on each individual invoice as needed.
More General Updates
- Internal notes from USBSwiper are now included in requests to PayPal so they can be seen in the PayPal transaction details.
- Partial refunds and the ability to leave refund notes has been included. Multiple partial refunds are also available up to the original full amount.
- Added option to print invoices without displaying the options dialog so you don’t have to say OK when you print an invoice. This option can be set within your Setup screen via a check box.
- Fixed invoice items so that if you edit the name(s) on the invoice screen it reflects on receipts and in PayPal transaction details. Previously, it would still use the name that was stored in Products.
- Added last 4 digits of credit card number and the PayPal transaction ID to all receipts.
- Added shipping address information on Invoices. If shipping information is included it’s passed to PayPal accordingly.
- Re-activating a software license no longer creates additional Setup records, therefore, you can re-activate without causing confguration problems.
- Fixed a bug where a validation error would occur when switching to Key mode after having already swiped a credit card.
- Added options to the payment type field to help maintain all types of transactions: Cash, Check, Money Order, Other.
