rado
Posts: 221
|
| Posted: 07/26/2009, 9:55 AM |
|
HI Everybody,
I have problem that I can't solve it so please give me some ideas.
Generally I have an editable grid that contains 1 or more payment items that is part of one transaction. When I do update of amount field everything is OK. SO, What I want to is after every change of amount that is done in any transaction item to do calculation and set "Total Amount" which is part of the main transaction record:
This is what I put into "AfterExecuteUpdate" event but unfortunatelly doesn't work:
$payments_TransactionID = CCGetSession("payments_TransactionID_sess");
$db = new clsDBrr_residence();
$sql ="SELECT payments_Amount FROM payments WHERE payments_TransParentID = $payments_TransactionID_sess";
$db->query($sql);
$result = $db->next_record();
$total_amount = "";
foreach (($return_value = $db->f(0)) as $item_amount) {
$total_amount = $total_amount + $item_amount;
}
$db->close();
|
 |
 |
Gena
Posts: 591
|
| Posted: 07/26/2009, 10:20 AM |
|
But what is $db->f(0) ???
Should not it be simething like
$db->f("field_name")) ?
_________________
Gena |
|
|
|
rado
Posts: 221
|
| Posted: 07/26/2009, 12:35 PM |
|
Thanks Gena,
I tried this as well but my "$total_amount" is still empty. I debug sql statement before "foreach loop" and everything is fine. Here is problem but don't know where exactly:
==============================================================================
$result = $db->next_record();
$total_amount = "";
foreach (($return_value = $db->f(payments_Amount)) as $item_amount) {
$total_amount = $total_amount + $item_amount;
}
==============================================================================
Thanks fro help.
Rado
|
|
|
|
rado
Posts: 221
|
| Posted: 07/26/2009, 2:20 PM |
|
I did some progress setting up the array before "foreach" loop, however I'm getting just first record from array. Does somebody knows where could be a problem???
$payments_TransactionID = CCGetSession("payments_TransactionID_sess");
$db = new clsDBrr_residence();
$sql ="SELECT payments_Amount FROM payments WHERE payments_TransParentID = $payments_TransactionID";
$db->query($sql);
$amounts = array();
while ($db->next_record())
{
$a = array();
$a["payments_Amount"] = $db->f("payments_Amount");
$amounts[] = $a;
}
$total_amount = "";
foreach ($amounts as $item_amount) {
$total_amount = $total_amount + $item_amount;
}
$sql ="UPDATE payments SET payments_Amount = $total_amount WHERE payments_TransactionID = $payments_TransactionID";
$db->query($sql);
$db->close();
I really appreciate any kind of help.
Rado
|
|
|
|
Gena
Posts: 591
|
| Posted: 07/26/2009, 2:20 PM |
|
ok
your SELECT wrong
$sql ="SELECT payments_Amount FROM payments WHERE payments_TransParentID = $payments_TransactionID_sess";
should be
$sql ="SELECT payments_Amount FROM payments WHERE payments_TransParentID = $payments_TransactionID";
_________________
Gena |
|
|
|
rado
Posts: 221
|
| Posted: 07/26/2009, 2:22 PM |
|
Thanks Gena,
I fixed this long time ago but not update the post.
Rado
|
|
|
|
Gena
Posts: 591
|
| Posted: 07/26/2009, 2:25 PM |
|
oops
_________________
Gena |
|
|
|
rado
Posts: 221
|
| Posted: 07/26/2009, 4:39 PM |
|
|
|
|
|
Gena
Posts: 591
|
| Posted: 07/26/2009, 4:47 PM |
|
I'm not sure have you solved your problem?
_________________
Gena |
|
|
|
Gena
Posts: 591
|
| Posted: 07/26/2009, 5:00 PM |
|
btw make it easy. your loop is strange. To calculate 1 total...
look my 1rs post here (about 2 methods how to use query)
http://forums.yessoftware.com/posts.php?post_id=105095
all you need is:
$payments_TransactionID = CCGetSession("payments_TransactionID_sess");
$db = new clsDBrr_residence();
$sql ="SELECT payments_Amount FROM payments WHERE payments_TransParentID = $payments_TransactionID";
$db->query($sql);
$total_amount = 0;
$db->query($sql);
while ($db->next_record()) {
$total_amount += $db->f("payments_Amount") ;
}
$sql ="UPDATE payments SET payments_Amount = $total_amount WHERE payments_TransactionID = $payments_TransactionID";
$db->query($sql);
$db->close();
btw I am not understand why you first calculate total on payments_Amount and then update THIS field again but with total amount???
_________________
Gena |
|
|
|
rado
Posts: 221
|
| Posted: 07/26/2009, 7:45 PM |
|
Thanks a lot Gena,
Yes you are right. I made this complicate and this code works just fine. Both (total and "payments_amount) are same field in the table, however "total amount" is filed for parent row and payment amount is for all records(payment items) that belong to parent row.
Thanks again for your help. You are the MAN.
Rado
|
|
|
|
