Point of Sale Credit Card Processing with FoxPro and Cold Fusion

1. Point of Sale Credit Card Processing with FoxPro and Cold Fusion Arden O. Weiss ardenweiss@verizon.net December 9, 2008

2. System Overview: Programs: • FoxPro DOS 2.6 – Point of Sale Program • Visual FoxPro 7 – Reports, Labels, Website update • Cold Fusion 5.0 – Credit Card Processing • Radmin 3.0 – Remote Access to Office & Sales Floor PC • PHP – Website –www.chevychasewine.com Databases: • DBF/CDX Files – Point of Sale Backend • ODBC DBF Drivers – Credit Card Processing • MySQL 3.0 – Website Database Backend 12/09/2008

3. Hardware Components: Windows 2000 RAID File Server Win98 POS PCswith CC Swipers, Sales Slip Printers, Pin Pads, POS Keyboards Accounting PC Windows 2000 Credit Card Server with CF-5 Sales Floor PCand SecondaryFile Server CreditCardMachine Radmin 3.0 Remote Access LicenseChecker Office PC 12/09/2008

4. What Each Component Does: • File Server… Stores the Point-of-Sale “POS” DBFs and programs (both the production and training versions of program) plus other files used by the Office and Floor Sales PC. • Credit Card…Stores the SAGE.DBF and runs a CF5 templateServerin a tight loop with a 1 second pause between loops waiting for the POS program to submit a Credit Card transaction approval request. Sends a CFHTTP request to the Gateway, waits for a response and then posts either an “APR” or “DEN” code plus other response data to the SAGE.DBF for the POS program to pick up and process (see Slide 13). • POS PCs…… Runs the Point-of-Sale “POS” program and posts results to the DBFs on both the File Server and the Credit Card Server. During a Credit Card sale, waits for the a response to be posted to the SAGE.DBF before completing the sale. If no response is received (internet down, etc.) sale is completed offline. 12/09/2008

5. What Each Component Does: • Office PC……….. Used to perform numerous support tasks. Also used as a Server for the Radmin 3.0 (the remote takeover software used to access the System from afar). • Sales Floor PC… Hasaccess privileges similar to that of the Office PC. In addition, is used as a data backup device (stores a week’s worth of “am” and “pm” backup data from the file and credit card servers. It also is set up as a secondary file server in case the primary file server fails. • Accounting PC… Used by the accountant for numerous purposes. Does not have access to the POS databases and programs. • C.Card Machine… Used to ring offline sales when internet goes down. • License CheckerUsed to check drivers licenses for legal age and card validity. 12/09/2008

6. Program/Database Components: Hosted Website PHP/MySQL WEB.PRG VFP DBF ODBC STOCK.DBF SALES.DBF POS.PRG FoxPro - DOS Reports FoxPro & VFP DBF SAGE.DBF Printers Sales and Credit Card Slips ODBC SAGE.CFM Credit Card Approval Via SAGE Gateway CFHTTP CF5 12/09/2008

7. Ringing a Cash or Check Sale: • Salesman scans bottles, specifies quantities and any discounts. (data is posted to a temporary SALESTMP.DBF) • Salesman totals the sale and selects Cash or Check Transaction. • Program then updates inventory (STOCK.DBF), adds contents of SALESTMP.DBF to the SALES.DBF and prints Sales Slip. 12/09/2008

8. Ringing a Card Present Sale: • Salesman scans bottles, specifies quantities and any discounts (data is posted to a temporary SALESTMP.DBF) • Salesman totals Sale and scans the Credit Card. • If local credit card validation is successful, the POS program posts a “RTS” (ready to send code) to the SAGE.DBF – CF then sends the transaction to the Sage Gateway and waits for a reply. • Upon receiving a reply, CF posts an “APR” or “DEN” (approval or denial) Code to the SAGE.DBF • If approved, the program updates inventory (STOCK.DBF), adds the contents of SALESTMP.DBF to the SALES.DBF and prints the Sales Slip and Credit Card Slips. • If not approved, the POS program recycles back to the payment type selection screen. Sale can also be canceled at this time. 12/09/2008

9. Ringing a Card not Present Sale: • Manager scans bottles, specifies quantities and any discounts (data is posted to a temporary SALESTMP.DBF) • Manager totals sale, selects credit card type and enters the card number, and the CVC number on the back of the card. • If local credit card validation is successful, the POS program posts a “RTS” (ready to send code) to the SAGE.DBF – CF then sends the transaction to the Sage Gateway and waits for a reply. • Upon receiving a reply, CF posts an “APR” or “DEN” (approval or denial) Code to the SAGE.DBF • If approved the POS program updates inventory (STOCK.DBF), adds the contents of SALESTMP.DBF to the SALES.DBF and prints the Sales Slip and Credit Card Slips. • If not approved, the POS program recycles back to the payment type selection screen. Sale can also be canceled at this time. 12/09/2008

10. Special Items of Note: • If a debit card is present, the card is swiped and the customer enters his pin number on the keypad provided. • If the customer returns items for a refund, then negative purchase quantities are entered. If the net total of the sale is negative, a negative sale is sent to the Gateway for approval. (Negative sales are password protected so only store management can process negative sales). • If the net total of the sale is positive, then a normal positive charge is sent to the gateway for approval. • A special request to Sage was required to process negative sales. • Split sales are permitted, but not between two Credit Cards. • Card transactions are settled by Sage daily (11pm by request). • Processing time (POS, to Bank and back) is less than 2 seconds. • Why CF5 – The JDBC drivers for DBF files did not play nice with the FoxPro 2.6 for DOS DBF files (especially the index and memo files). Also, there were intermittent issues with the CDX files using the ODBC drivers; hence, the SAGE.DBF is not indexed. 12/09/2008

11. CF Code for Card Present Transaction: CFHTTP URL= "HTTPS://WWW.SAGEPAYMENTS.NET/CGIBIN/EFTBANKCARD.DLL?RETAIL_TRANSACTION" PORT="443" METHOD="POST"> <CFHTTPPARAM NAME="M_ID“ TYPE="FORMFIELD" VALUE=“XXXXXX" ENCODED="NO" > <CFHTTPPARAM NAME="M_KEY" TYPE="FORMFIELD" VALUE=“XXXXXX" ENCODED="NO" > <CFHTTPPARAM NAME="T_TRACKDATA“ TYPE="FORMFIELD" VALUE="#MTRACK1#" ENCODED="NO" > <CFHTTPPARAM NAME="T_CODE" TYPE="FORMFIELD" VALUE="#MT_CODE#“ ENCODED="NO" > <CFHTTPPARAM NAME="T_AMT" TYPE="FORMFIELD" VALUE="#MX_AMOUNT#“ ENCODED="NO" > <CFHTTPPARAM NAME="T_ORDERNUM" TYPE="FORMFIELD" VALUE="#MX_INVC_NUM##MPK#“ ENCODED="NO" > </CFHTTP> 12/09/2008

12. CF Code for Card Not Present Transaction: <CFHTTP URL= "HTTPS://WWW.SAGEPAYMENTS.NET/CGI-BIN/EFTBANKCARD.DLL?TRANSACTION" PORT="443" METHOD="POST"> <CFHTTPPARAM NAME="M_ID" TYPE="FORMFIELD" VALUE=“xxxxxx" ENCODED="NO" > <CFHTTPPARAM NAME="M_KEY" TYPE="FORMFIELD" VALUE=“xxxxxx" ENCODED="NO" > <CFHTTPPARAM NAME="C_NAME" TYPE="FORMFIELD" VALUE=" " ENCODED="NO" > <CFHTTPPARAM NAME="C_ADDRESS" TYPE="FORMFIELD" VALUE=" " ENCODED="NO" > <CFHTTPPARAM NAME="C_CITY" TYPE="FORMFIELD" VALUE=" " ENCODED="NO" > <CFHTTPPARAM NAME="C_STATE" TYPE="FORMFIELD" VALUE=" " ENCODED="NO" > <CFHTTPPARAM NAME="C_ZIP" TYPE="FORMFIELD" VALUE=" " ENCODED="NO" > <CFHTTPPARAM NAME="C_COUNTRY" TYPE="FORMFIELD" VALUE=" " ENCODED="NO" > <CFHTTPPARAM NAME="C_EMAIL" TYPE="FORMFIELD" VALUE=" " ENCODED="NO" > <CFHTTPPARAM NAME="C_CARDNUMBER" TYPE="FORMFIELD" VALUE="#mX_CARD_NUM#" ENCODED="NO" > <CFHTTPPARAM NAME="C_EXP" TYPE="FORMFIELD" VALUE="#mX_EXP_DATE#" ENCODED="NO" > <CFHTTPPARAM NAME="T_CODE" TYPE="FORMFIELD" VALUE="#mT_CODE#" ENCODED="NO" > <CFHTTPPARAM NAME="T_AMT" TYPE="FORMFIELD" VALUE="#mX_AMOUNT#" ENCODED="NO" > <CFHTTPPARAM NAME="T_ORDERNUM" TYPE="FORMFIELD" VALUE="#mX_INVC_NUM##MPK#" ENCODED="NO" > <CFHTTPPARAM NAME="C_CVV" TYPE="FORMFIELD" VALUE="#mCVC_NUM#" ENCODED="NO" > </CFHTTP> 12/09/2008

13. CF Code for Gateway Response String: <!--- PROCESS THE GATEWAY RESPONSE ----> <CFSET mRESPON_STR = CFHTTP.FILECONTENT> <CFSET mAPROV_CODE = #MID(mRESPON_STR,2,1)# > <CFSET mERROR_CODE = #MID(mRESPON_STR,3,6)# > <CFSET mERROR_MESS = #MID(mRESPON_STR,9,32)# > <CFSET mCVV_CODE = #MID(mRESPON_STR,43,1)# > <CFSET mAVS_CODE = #MID(mRESPON_STR,44,1)# > <CFSET mRSK_CODE = #MID(mRESPON_STR,45,2)# > <CFSET mTRANS_ID = #MID(mRESPON_STR,47,10)# > <CFSET mAUTH_CODE = #MID(mRESPON_STR,47,10)# > <CFSET mORDR_NUM = #MID(mRESPON_STR,58,6)# > <CFSET mPK_RESPON = #MID(mRESPON_STR,64,6)# > <CFSET MEND_TIME = #TIMEFORMAT(NOW(),"HH:MM:SS")# > 12/09/2008

14. Summary: • The system processes about 40,000 transactions/year. • DSL Line outage is the biggest problem encountered. • The secondary server was used once when we had a hardware problem with the primary server. During this problem, the system ran with the secondary server for about a day without missing a sale. • The main reason for converting from authorize.net to Sage was transaction cost differences and the fact that authorize.net followed accounting practices that drove the accountant mad (mainly how they handled refunds and voids). She loves Sage’s accounting practices/repts. • Conversion from authorize.net to sagepayments.net was painless. The code differences were minor – solely variable names and URL differences. Authorize.net uses “x_” as leading characters for their variable names, whereas Sage uses “m_”, “t_” and “c_” (see chart 12). 12/09/2008

15. Refreshing the Website MySQL Database: • Extract all records from STOCK.DBF tagged for website display. • SELECT * FROM STOCK ; • INTO DBF TO_WEB.DBF ; • WHERE TO_WEB = "Y" • Do some pre-website-posting of the extracted records (3,500). • Upload data in TO_WEB.DBF to MySQL Table via ODBC connection. • hdconn = SQLDISCONNECT(0) • hconn = SQLCONNECT("BOOZE", “user_id",“password") • lnResult = SQLEXEC(hConn, "INSERT INTO BOOZE (ITEMNO,STK_CAT,STK_TYPE,CTRY_ST,VINTAGE,DESCRIPT) VALUES (&mA,&mB,&mC,&mD,&mE,&mF)", "BOOZE") • lnResult = SQLEXEC(hConn, "UPDATE BOOZE SET SIZE=&mG,TOUT_STK=&mH,SING_PRICE=&mI,FOUR_PRICE=&mJ,SIX_PRICE=&mK,CASE_PRICE=&mL,CASE_QTY=&mM,DT_END=&mN,ON_SALE=&mO WHERE ITEMNO=&mA", "BOOZE") 12/09/2008