asterisk2billing.org
http://forum.asterisk2billing.org/

Failed to log refill & payment in checkout_process.php
http://forum.asterisk2billing.org/viewtopic.php?f=34&t=8128		 Page 1 of 1
Author:  pbx_kc [ Mon Sep 13, 2010 5:02 am ]
Post subject:  Failed to log refill & payment in checkout_process.php
Hi,

I think the checkout_process.php fail to create records into cc_logrefill and cc_logpayment table. Here is some part of the epayment log I found:

Quote:
........
[12/09/2010 00:06:41]:[checkout_process.php line:316-transactionID=66 CARD FOUND IN DB (25)]
[12/09/2010 00:06:41]:[checkout_process.php line:328-transactionID=66 Update_table cc_card : credit = credit+'10' - CLAUSE : id='25']
[12/09/2010 00:06:41]:[checkout_process.php line:344-transactionID=66 Add_table cc_logrefill : date, credit, card_id, description,agent_id - VALUES '2010-09-12 00:06:41', '10', '25', 'paypal',NULL]
[12/09/2010 00:06:41]:[checkout_process.php line:350-transactionID=66 Add_table cc_logpayment : date, payment, card_id, id_logrefill, description,agent_id - VALUES '2010-09-12 00:06:41', '10', '25', '', 'paypal',NULL ]
........


If you look at the empty data field just before 'paypal' in the last line which seems like it failed to return a ID from new inserted cc_logrefill record hence no cc_logrefill inserted. That made me wonder what actually is trying to do so I had a look at checkout_process.php.

Around line: 344 -350 (see log above) I found this piece of code which I believe is the code that actually insert the new refill and payment log into the table:

Quote:
$field_insert = "date, credit, card_id, description,agent_id";
$value_insert = "'$nowDate', '".$amount_without_vat."', '$id', '".$transaction_data[0][4]."',$id_agent_insert";
$instance_sub_table = new Table("cc_logrefill", $field_insert);
$id_logrefill = $instance_sub_table -> Add_table ($DBHandle, $value_insert, null, null, 'id'); <== (*** Is this suppose to be single quote or double? ***)
write_log(LOGFILE_EPAYMENT, basename(__FILE__).' line:'.__LINE__."-transactionID=$transactionID"." Add_table cc_logrefill : $field_insert - VALUES $value_insert");

$field_insert = "date, payment, card_id, id_logrefill, description,agent_id";
$value_insert = "'$nowDate', '".$amount_paid."', '$id', '$id_logrefill', '".$transaction_data[0][4]."',$id_agent_insert ";
$instance_sub_table = new Table("cc_logpayment", $field_insert);
$id_payment = $instance_sub_table -> Add_table ($DBHandle, $value_insert, null, null,"id");
write_log(LOGFILE_EPAYMENT, basename(__FILE__).' line:'.__LINE__."-transactionID=$transactionID"." Add_table cc_logpayment : $field_insert - VALUES $value_insert");


Then I look at the database table structure:

Quote:
cc_logrefill Table:

Field name,Type,Allow nulls?,Key,Default value,Extras
id, bigint(20), No, Primary, NULL, auto_increment
date, timestamp, No, None, CURRENT_TIMESTAMP
credit, decimal(15,5), No, None, NULL
card_id, bigint(20), No, None, NULL
description, mediumtext, Yes, None, NULL
refill_type, tinyint(4), No, None, 0
added_invoice, tinyint(4), No, None, 0



Quote:
cc_logpayment Table:

Field name, Type, Allow nulls?, Key, Default value, Extras
id, int(11), No, Primary, NULL, auto_increment
date, timestamp, No, None, CURRENT_TIMESTAMP
payment, decimal(15,5), No, None, NULL
card_id, bigint(20), No, None, NULL
id_logrefill, bigint(20), Yes, None, NULL
description, mediumtext, Yes, None, NULL
added_refill, smallint(6), No, None, 0
payment_type, tinyint(4), No, None, 0
added_commission, tinyint(4), No, None, 0


I couldn't figure it out what caused the failing insert new records in cc_logrefill and cc_logpayment? I query the table but it remains empty after several payment attempts using the paypal module.

What I would expect to happen is when the Paypal payment processed:
- Update customer balance
- Create a new refill record
- Create a new payment record that can offset the overdue debt balance (To Pay under Customer Balance)

Please correct me if I misinterpret the process flow for the epayment.

Can anyone help? I need to properly log a refill and payment when customer make a top up epayment so we can trace what happen in payment flow.


Many thanks. :laugh:
Author:  pbx_kc [ Mon Sep 13, 2010 5:03 am ]
Post subject:  Re: Failed to create refill & payment log (checkout_process.php)
By the way, the rest of the epayment log looks fine and I only picked the lines that I think it might be the problem. Thanks. :)
Author:  pbx_kc [ Mon Sep 13, 2010 10:42 am ]
Post subject:  Re: Failed to create refill & payment log (checkout_process.php)
Enabled the MySQL log to see what query are being executed in checkout_process.php and found the following:

Quote:
INSERT INTO cc_logrefill (date, credit, card_id, description,agent_id) values ('2010-09-13 20:53:01', '10', '25', 'paypal',NULL)
INSERT INTO cc_logpayment (date, payment, card_id, id_logrefill, description,agent_id) values ('2010-09-13 20:53:01', '10', '25', '', 'paypal',NULL)


I manually run this INSERT query and realised it's trying to insert some data into a non existence column: agent_id in both the tables.

Looks like this is what caused the insert fail.

There are 2 other similar tables cc_logrefill_agent and cc_logpayment_agent and I am not too sure if this agent_id are being used in these tables?

What should I do?


Thanks.
Author:  jroper [ Mon Sep 13, 2010 1:40 pm ]
Post subject:  Re: Failed to log refill & payment in checkout_process.php
Hi

Try this

ALTER TABLE cc_logpayment ADD agent_id BIGINT NULL ;
ALTER TABLE cc_logrefill ADD agent_id BIGINT NULL ;


Joe
Author:  zafeirop [ Tue Sep 14, 2010 7:59 am ]
Post subject:  Re: Failed to log refill & payment in checkout_process.php
Hi,

agent_id field is a required field in the DB schema.
Maybe you messed up with your installation?
Maybe you upgraded your old A2B without updating MySQL schema?

The agent_id field must be occupied by the id of the agent that the customer belongs.
If however, the customer who completed the transaction, does not belong to a particular Customer GROUP, then the agent_id must be NULL

I think that it is a DB problem.
Regards,
Tasos
Author:  pbx_kc [ Tue Sep 14, 2010 10:06 am ]
Post subject:  Re: Failed to log refill & payment in checkout_process.php
Thanks for your reply Joe and zafeirop. That make total sense.

I'll add the column now into the table and that should do the trick.

I wonder if there is a up to date A2Billing database schema documentation somewhere I can look at?
Author:  zafeirop [ Tue Sep 14, 2010 11:11 pm ]
Post subject:  Re: Failed to log refill & payment in checkout_process.php
I think that you can visit A2B Trac.
Try here:
http://www.asterisk2billing.org/cgi-bin/trac.cgi/browser/trunk/DataBase/mysql-5.x

The full schema for 1.7.0 is here:
http://www.asterisk2billing.org/cgi-bin/trac.cgi/export/2916/trunk/DataBase/mysql-5.x/a2billing-mysql-schema-v1.7.0.sql

The SQL commands are self explanatory :lol:

Regards,
Tasos
Author:  pbx_kc [ Wed Sep 15, 2010 6:00 am ]
Post subject:  Re: Failed to log refill & payment in checkout_process.php
Thanks zafeirop. Everything is now sorted.
Page 1 of 1 All times are UTC
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/