Forum OpenACS Q&A: Payment Service Contract available

Collapse
1: Payment Service Contract available
Posted by Bart Teeuwisse on 06/05/02 08:28 PM

The Payment Service Contract is available for download from the HEAD of the OpenACS CVS.

The Payment Service Contract offers packages a generic interface to credit card gateways like Authorize.net, PayflowPro and so forth.

The two initial implementations of the service contract are for Authorize.net and PayflowPro. Both can be used with the new version the Ecommerce package (4.5b). Verisign offers test accounts for PayflowPro which makes the PayflowPro implementation an excellent package to test the Payment Service Contract with.

The package ID of the Payment Service Contract is payment-gateway. To download the service contract and the implementations use

cvs login -p:server:openacs.org/cvsroot
cvs co payment-gateway authorize-gateway payflowpro

from the packages directory of your OpenACS 4.x installation.

Last but not least, Janine Sisk wrote a sample implementation for Oracle of the Payment Service Contract, which can be downloaded as follows:

cvs login -p:server:openacs.org/cvsroot
cvs co <your-server>/packages/sample-gateway

from the parent directory of <your-server> (usually /web).

For more information see the documentation of the Authorize.net implementation and the Ecommerce package.

Collapse
2: Response to Payment Service Contract available (response to 1)
Posted by Hamilton Chua on 06/06/02 04:21 AM 
  
Hello  
  
I encountered a problem installing the Payment Gateway Package.  
I opened the payment-gatewy-sc-create.sql file and found that the  
last statement ....  
 
*********** 
select acs_sc_msg_type__new (  
           'PaymentGateway.Info.InputType',  
	   ''  
);  
*********** 
  
was causing this error  
  
**************  
Request Error     
     
psql:payment-gateway-sc-create.sql:128: ERROR:  Cannot insert a     
duplicate key into unique index acs_sc_msg_types_name_un     
*************** 
 
when trying to install the payment-gateway package.
Collapse
3: Response to Payment Service Contract available (response to 1)
Posted by Bart Teeuwisse on 06/06/02 04:49 AM
Fixed. There was duplicate Input type lingering around for the Info operation.
Collapse
4: Response to Payment Service Contract available (response to 1)
Posted by Hamilton Chua on 06/07/02 06:09 AM 
 
Hello,  
  
Thanks very much for fixing the issue so fast.  
  
Here are some more observations/items.  
  
- I could not find documentation for the payment gateway module and  
the payflowpro module  
  
- I mounted the payflowpro module on the sitemap and went to the  
admin section. I encountered this error.  
  
*******************  
Request Error  
invalid command name "apm_tcl_or_sql_name_from_package_key"  
    while executing  
"apm_tcl_or_sql_name_from_package_key $package_key"  
    invoked from within  
"set cleaned_package_key [apm_tcl_or_sql_name_from_package_key  
$package_key]"  
*****************  
  
I am in the process of figuring out why the error above is occuring.  
I believe it may have something to do with the fact that I am using  
the beta (targz) release as my acs-core.  
  
Thanks Again,
Collapse
5: Response to Payment Service Contract available (response to 1)
Posted by Bart Teeuwisse on 06/07/02 06:23 AM
The calls to apm_tcl_or_sql_name_from_package_key are irrelevant and have been removed. You can either remove them from the .tcl files in the admin directory of sync your version with the CVS repository.

The apm_tcl_or_sql_name_from_package_key procedure is an internal Furfly procedure and not part of the OpenACS core. My appologies for the inconvenience.

Collapse
6: Response to Payment Service Contract available (response to 1)
Posted by jay he on 06/07/02 08:04 AM
Bart,
What's the procedure I should follow to set up payflowpro and test it?
I updated the ecommerce package to 4.5b. Then I install payflowpro and payment-gateway packages and mount them in the site-map. Got the same error as above-mentioned. I updated the code and fixed the bug.

What's the next step?

I guess I need to set up verisign toolkit. The README file lists the following steps:

INSTALLATION

* Unpack the SDK from VeriSign.

* Edit the variables at the top of Makefile and verisign.c.

* Run 'make install'.

* Edit your AOLserver nsd.ini to include

  ns_param  verisign        PATH_TO_VERISIGN_MODULE/verisign.so

  under the modules section.

* Restart AOLserver.

I go to www.verisign.com and create a test account for payflowpro. After I login to https://manager.verisign.com, I download the SDK in "Linux - libc6 / glibc2 / ELF kernels 2.0.36 and above" version.

I also take a look at its developer's guide. I unpack the sdk in /usr/local. Then I set export PFPRO_CERT_PATH=/usr/local/verisign/payflowpro/linux/
certs/ as nsadmin.

Then I ./test.sh and got the result as the following:
----------------------------------------------------
***> Performing 'pfpro' binary test transaction.....
RESULT=1&PNREF=V00000000000&RESPMSG=User authentication failedDone with 'pfpro'
binary test transaction...
---------------------------------------------------

But in the readme file, it says the result should be
response = RESULT=0&PNREF=VXYZ00912465&ERRCODE=00&AUTHCODE=09TEST&AVSADDR=Y&
AVSZIP=Y&

Where did I make a mistake? I tested several times and couldn't figure out. I guess the problem may be I don't have a CSR certificate.

Any ideas? Thanks,

Jay

Collapse
7: Response to Payment Service Contract available (response to 1)
Posted by jay he on 06/07/02 08:17 AM
One more question:

In verisign site, they have to three ways to create a server key:
one with password, one without and one with international edition.

I guess in order to secure the server, I should create a server key with password but I don't want to type in password every time I restart the server.

Any suggestions?

Thanks,

Jay

Collapse
8: Response to Payment Service Contract available (response to 1)
Posted by Bart Teeuwisse on 06/07/02 09:58 AM
Jay, don't worry about the three ways to create a server key. I'm getting the same error message yet I can use verisign.so just fine. Be sure to edit the Makefile for this library and the verisign.c file.

For test purposes you'll have to change

char *hostAddress = "payflow.verisign.com";

to

char *hostAddress = "test-payflow.verisign.com";

Collapse
9: Response to Payment Service Contract available (response to 1)
Posted by jay he on 06/07/02 04:37 PM
Thanks. Bart.

I went ahead to make install for versign. And copy versign.so to /usr/local/aolserver/bin (I left it in the /web/birdnotes/packages/payflowpro/verisign-modules/ for the first time, which didn't work 'cause it caused a fatal error when loading).

I change nsd.tcl with "ns_param  verisign ${bindir}/verisign.so" under "ns_section ns/server/${server}/modules"

After restarted the server, I tested the ecommerce package. When in ecommerce/finalize-order page, I got the following error:
"
[07/Jun/2002:10:41:41][4944.3076][-conn0-] Warning: propagating abortion from /web/birdnotes/packages/ecommerce/www/finalize-order.tcl (status 500): 'Sorry')"

I will take a look and give more info when necessary.

Jay

Collapse
10: Response to Payment Service Contract available (response to 1)
Posted by Bart Teeuwisse on 06/07/02 06:59 PM
Jay, did you set the payment gateway Ecommerce package parameter to payflowpro? You'll find the parameter among the payment information parameters.

/Bart

Collapse
11: Response to Payment Service Contract available (response to 1)
Posted by Bart Teeuwisse on 06/07/02 08:01 PM
Jay, one more thing, for test purposes you also want to set the ActionOnPartialAVS parameter of the PayflowPro Gateway to success so that transactions don't get rejected when they fail the address verification.

Could you please re-sync with the OpenACS CVS? The author had developed the PayflowPro Gateway for Oracle and didn't include the PostgreSQL .xql files.

/Bart

Collapse
12: Response to Payment Service Contract available (response to 1)
Posted by jay he on 06/07/02 08:43 PM
Bart. Thanks for the tips.

In the site-map, I set ActionOnPartialAVS as success for payflowpro and  set PaymentGateway in Ecommerce as payflowpro.

And I set EcommerceDataDirectory in Ecommerce as "/web/birdnotes/data/ecommerce" and create the corresponding directory on the server.

Then I started to test. First I put 4242424242424242 as visa credit card with a bogus expiration date and address. I got the authorization failed. I was redirected to ecommerce/credit-card-correction page. In the ecommerce admin page, I found that order status is confirmed. After I checked the manager.verisign.com for test account, I found this transaction is in Authorization  status.

I had my lunch and came back and found that status in ecommerce/admin for this transaction was updated to authorized. ( I guess that's the way test server works. You can't get real-time response from the test server?)

Since I don't have my qmail set up. I got an error message (couldn't execute "/var/qmail/bin/qmail-inject": ) when I tried to use a real credit card for test.

Looks like payflowpro package is working.

Jay

Collapse
13: Response to Payment Service Contract available (response to 1)
Posted by Bart Teeuwisse on 06/07/02 09:29 PM
Jay, the Verisign test server does process transactions in real time. However, if there is a problem with a card, for example because of communication problems, the ecommerce package with try again later. The status of the order is updated to authorized should the retry be succesfull.

By all means try it with a real credit card. The communication with Verisign is secure and your test account is not shared with others. You can also use this test credit card number: 4003010123456780. IntelliPay  give me this number once for testing purposes and works with any credit card gateway.

/Bart

Collapse
14: Response to Payment Service Contract available (response to 1)
Posted by jay he on 06/08/02 05:30 AM
Bart.

I checked the log file and found the following error: 

[07/Jun/2002:22:00:43][6183.7176][-conn4-] Debug: PgBindCmd: query with bind var
iables substituted =
            insert into payflowpro_result_log
            (transaction_id, txn_attempted_type, txn_attempted_time, txn_returne
d_type, errmsg, auth_code, avs_code_zip, avs_code_addr, amount)
            values
            ('V53A14524377', '''mauthonly''', sysdate, 'success', NULL, '445PNI'
, NULL, NULL, '5.000000')
[07/Jun/2002:22:00:43][6183.7176][-conn4-] Notice: Querying '
            insert into payflowpro_result_log
            (transaction_id, txn_attempted_type, txn_attempted_time, txn_returne
d_type, errmsg, auth_code, avs_code_zip, avs_code_addr, amount)
            values
            ('V53A14524377', '''mauthonly''', sysdate, 'success', NULL, '445PNI'
, NULL, NULL, '5.000000');'
[07/Jun/2002:22:00:43][6183.7176][-conn4-] Error: Ns_PgExec: result status: 7 me
ssage: ERROR:  Attribute 'sysdate' not found

[07/Jun/2002:22:00:43][6183.7176][-conn4-] Error: dbinit: error(localhost::final
planning,ERROR:  Attribute 'sysdate' not found
): '
            insert into payflowpro_result_log
            (transaction_id, txn_attempted_type, txn_attempted_time, txn_returne
d_type, errmsg, auth_code, avs_code_zip, avs_code_addr, amount)
            values
            ('V53A14524377', '''mauthonly''', sysdate, 'success', NULL, '445PNI'
, NULL, NULL, '5.000000')
          '

          '
[07/Jun/2002:22:00:43][6183.7176][-conn4-] Error: Error inserting into payflowpr
o_result_log for transaction_id V53A14524377: Database operation "dml" failed
[07/Jun/2002:22:00:43][6183.7176][-conn4-] Notice: Querying 'abort transaction;'
[07/Jun/2002:22:00:43][6183.7176][-conn4-] Notice: Ns_PgExec: Rolling back trans
action
[07/Jun/2002:22:00:43][6183.7176][-conn4-] Notice: dbinit: sql(localhost::finalp
lanning): 'abort transaction'
[07/Jun/2002:22:00:43][6183.7176][-conn4-] Error: Aborting transaction due to er
ror:
Database operation "dml" failed
[07/Jun/2002:22:00:43][6183.7176][-conn4-] Debug: QD_LOGGER = calling namespace
=
[07/Jun/2002:22:00:43][6183.7176][-conn4-] Debug: QD_LOGGER = proc_name is -ec_u
pdate_state_to_in_basket-
[07/Jun/2002:22:00:43][6183.7176][-conn4-] Debug: QD_LOGGER = generated fullname
 of dbqd.ecommerce.tcl.ecommerce-state-changes-procs.ec_update_state_to_in_baske
t.order_state_update
[07/Jun/2002:22:00:43][6183.7176][-conn4-] Debug: QD_LOGGER = PRE-QD: the SQL is

        update ec_orders
        set order_state='in_basket', confirmed_date=null
        where order_id=:order_id for dbqd.ecommerce.tcl.ecommerce-state-changes-
procs.ec_update_state_to_in_basket.order_state_update
[07/Jun/2002:22:00:43][6183.7176][-conn4-] Debug: QD_LOGGER = query dbqd.ecommerce.tcl.ecommerce-state-changes-procs.ec_update_state_to_in_basket.order_state_up
date from /web/birdnotes/packages/ecommerce/tcl/ecommerce-state-changes-procs.xql
[07/Jun/2002:22:00:43][6183.7176][-conn4-] Debug: QD_LOGGER = POST-QD: the SQL i
s
      update ec_orders
      set order_state='in_basket', confirmed_date=null
      where order_id=:order_id

[07/Jun/2002:22:00:43][6183.7176][-conn4-] Debug: PgBindCmd: sql =
      update ec_orders
      set order_state='in_basket', confirmed_date=null
      where order_id=:order_id
[07/Jun/2002:22:00:43][6183.7176][-conn4-] Debug: QD_LOGGER = PRE-QD: the SQL is

        update ec_creditcards
        set failed_p='t'
        where creditcard_id=:creditcard_id for dbqd.ecommerce.tcl.ecommerce-stat
e-changes-procs.ec_update_state_to_in_basket.creditcard_update
[07/Jun/2002:22:00:43][6183.7176][-conn4-] Debug: QD_LOGGER = query dbqd.ecommer
ce.tcl.ecommerce-state-changes-procs.ec_update_state_to_in_basket.creditcard_upd
ate from /web/birdnotes/packages/ecommerce/tcl/ecommerce-state-changes-procs
.xql
[07/Jun/2002:22:00:43][6183.7176][-conn4-] Debug: QD_LOGGER = POST-QD: the SQL is
      update ec_creditcards
      set failed_p='t'
      where creditcard_id=:creditcard_id
Looks like the sysdate in Oracle PL/SQL causes the trouble in payflowpro_result_log. I will change that to now for postGreSQL and report my new result (The retry was successful, which meant the retry ignored updating of payflowpro_result_log table?).

Jay

Collapse
15: Response to Payment Service Contract available (response to 1)
Posted by jay he on 06/08/02 06:15 AM
Bart. I think Payflowpro package works. I used the credit card number 4003010123456780 you gave me and set ActionOnPartialAVS as success. The result is my qmail error. If I turn off ActionOnPartialAVS, the result is creditcard-correction page. Here is the error log for ActionOnPartialAVS as success: 
[08/Jun/2002:00:22:08][8860.7176][-conn4-] Error: POST /ecommerce/finalize-order  couldn't execute "/var/qmail/bin/qmail-inject": no such file or directory
    while executing
"open "| /var/qmail/bin/qmail-inject -h -f$from" "w""
    (procedure "qmail" line 20)
    invoked from within
"qmail $email_to $from $email_subject $email_body $extra_headers"
    (procedure "ec_sendmail_from_service" line 15)
    invoked from within
"ec_sendmail_from_service "$email" "$email_from" "$email_subject" "$email_body""
    (procedure "ec_email_new_order" line 71)
    invoked from within
"ec_email_new_order $order_id"
    invoked from within
"if {$hard_goods_cost > 0} {

    # The order contains hard goods that come at a cost.

    if {$soft_goods_cost > 0} {

        # The order contains both ha..."
    ("uplevel" body line 222)
    invoked from within
"uplevel {
          ad_page_contract {

    This script will:

    (1) put this order into the 'confirmed' state
    (2) try to authorize the user's cred..."
    (procedure "code::tcl::/web/birdnotes/packages/ecommerce/www/finaliz..." line 2)
    invoked from within
"code::tcl::$__adp_stub"
    invoked from within
"if { [file exists $__adp_stub.tcl] } {

      # ensure that data source preparation procedure exists and is up-to-date
      adp_init tcl $__adp_stub
..."
    ("uplevel" body line 3)
    invoked from within
"uplevel {

    if { [file exists $__adp_stub.tcl] } {

      # ensure that data source preparation procedure exists and is up-to-date
      adp_init t..."
    (procedure "adp_prepare" line 3)
    invoked from within
"adp_prepare "
    (procedure "template::adp_parse" line 30)
    invoked from within
"template::adp_parse [file root [ad_conn file]] {}"
    (procedure "adp_parse_ad_conn_file" line 7)
    invoked from within
"$handler"
    ("uplevel" body line 2)
    invoked from within
"uplevel $code"
    invoked from within
"ad_try {
        $handler
      } ad_script_abort val {
        # do nothing
      }"
    invoked from within
"rp_serve_concrete_file [ad_conn file]"
    (procedure "rp_serve_abstract_file" line 60)
    invoked from within
"rp_serve_abstract_file "$root/$path""
    ("uplevel" body line 2)
    invoked from within
"uplevel $code"
    invoked from within
"ad_try {
        rp_serve_abstract_file "$root/$path"
        set tcl_url2file([ad_conn url]) [ad_conn file]
        set tcl_url2path_info([ad_conn url]) [ad_conn path_inf..."
[08/Jun/2002:00:22:38][8860.2051][-sched-] Notice: Running scheduled proc search_indexer...
[08/Jun/2002:00:22:38][8860.2051][-sched-] Warning: APM: FtsEngineDriver does not exist
[08/Jun/2002:00:22:38][8860.2051][-sched-] Notice: Querying '
        select object_id, date, event
        from search_observer_queue
        order by date asc;'
[08/Jun/2002:00:22:38][8860.2051][-sched-] Notice: dbinit: sql(localhost::birdnotes): '
        select object_id, date, event
        from search_observer_queue
        order by date asc
    '
[08/Jun/2002:00:22:38][8860.2051][-sched-] Notice: Done running scheduled proc search_indexer.
I will install qmail first before I can really get this package work for me.

Thanks

Jay

Collapse
16: Response to Payment Service Contract available (response to 1)
Posted by Bart Teeuwisse on 06/10/02 09:08 AM
Jay, please upgrade to the current CVS version as I did fix the 'sysdate' and a few other Oracle only issues.
Collapse
17: Response to Payment Service Contract available (response to 1)
Posted by Hamilton Chua on 06/11/02 09:17 AM 
Hello,

Seems that we've been keeping you pretty busy. 
Here is another item that you may want to look at.

In the admin module I clicked on the Orders link and view the pending or other types of orders in the system.

The order id is clickable. When I click on it the following error appears :

*************

unable to convert date-time string "2002-06-11 15:11:02+08"
    while executing
"clock scan $ugly_date"
    (procedure "ec_formatted_full_date" line 3)
    invoked from within
"ec_formatted_full_date $confirmed_date"
    invoked from within
"doc_body_append "
    [ad_admin_header "One Order"]

    
One Order


**************

The file concerned is /ecommerce/admin/orders/one?order%5fid=the_order_id .

By the way, am using OpenACS Beta and Postgresql 7.1

Best,
Collapse
18: Response to Payment Service Contract available (response to 1)
Posted by Bart Teeuwisse on 06/11/02 10:40 AM
Hamilton, this suprises me as I'm pretty sure that dates with timezone info converted just fine to formatted date/time strings.

Your message reaches me while on the road in Europe which means that I can't take a closer look myself. Take a look at the ec_formatted_full_date proc and the man page of the Tcl clock scan instruction. Somehow the clock scan chokes on the string "2002-06-11 15:11:02+08" retrieved from PG.

Good luck.

Collapse
19: Response to Payment Service Contract available (response to 1)
Posted by Hamilton Chua on 06/11/02 11:54 AM
Acknowledge, will report what I find in a bit. Thanks very much.
Collapse
20: Response to Payment Service Contract available (response to 1)
Posted by Hamilton Chua on 06/11/02 12:22 PM 
You're right, it has something to do with clock command:
Here is an outline of my trial

tclsh
% set ugly_date "2002-06-05 15:36:36+08"
2002-06-05 15:36:36+08
% clock format [clock scan $ugly_date] -format "%B %d, %Y %r" -gmt true
unable to convert date-time string "2002-06-05 15:36:36+08"

I tried a little experiment 
% set ugly_date "2002-06-05 15:36:36"
2002-06-05 15:36:36
% clock format [clock scan $ugly_date] -format "%B %d, %Y %r" -gmt true
June 05, 2002 03:44:36 PM

It seems removing the +08 fixes the problem.
Collapse
21: Response to Payment Service Contract available (response to 1)
Posted by Hamilton Chua on 06/11/02 12:32 PM 
A temporary solution would be to reformat "o.confirmed_date" to not include the +08 in the following select


db_1row order_select "
    select o.order_state, o.creditcard_id, o.confirmed_date, o.cs_comments,
        o.shipping_method, o.shipping_address, o.in_basket_date,
        o.authorized_date, o.shipping_charged, o.voided_by, o.voided_date,
        o.reason_for_void, u.user_id, u.first_names, u.last_name, c.billing_address
    from ec_orders o, cc_users u, ec_creditcards
    where order_id=:order_id
    and o.user_id = u.user_id(+)
    and o.creditcard_id = c.creditcard_id(+)"

Your thoughts,
Collapse
22: Response to Payment Service Contract available (response to 1)
Posted by Hamilton Chua on 06/13/02 06:46 AM
Additional related item: I can't find an oracle or postgresql xql file for one.tcl
Collapse
23: Response to Payment Service Contract available (response to 1)
Posted by Hamilton Chua on 06/13/02 07:26 AM 
I got it to work but I modifed more than just the query with the confirmed date. I had to do a to_char(date_in_question, 'YYYY-MM-DD HH24:MI:SS') on the dates that caused the error.

The following needed to be modified :
confirmed_date
inserted_date
in_basket_date

I wreckon that this is a temporary fix. We may need to modify ec_formatted_date to handle dates from Postgres - specially if it is used a lot around in ecommerce.
Collapse
24: Response to Payment Service Contract available (response to 1)
Posted by Zachari Partridge on 08/26/02 08:47 PM
Hi,

was wondering if the ugly_date problem mentioned just above this
post has been fixed.  My ecommerce package from 8/12/2002 still has
the bug and there don't seem to be any changes since then.  I'm
using pg7.2.

Thanks, Zack.

Collapse
25: Response to Payment Service Contract available (response to 1)
Posted by Bart Teeuwisse on 09/12/02 04:42 AM
A fix for the ugly_date problem with dates including timezone information as returned
by PG 7.2. has been committed to CVS.