Page 1 of 1
Balance due
Posted: 2015-06-27 11:29
by nazcool
I am trying to calculate balance due from two different table fields. Anyone can help me?
I am trying to do the following
$data['due'] = $data['total_cost'] - $data['payment_received'];
'total_cost' field is in a different table name 'clients'
Re: Balance due
Posted: 2015-06-29 02:35
by shasta59
Not too hard to do. Just write some code to look up the value for total_cost and you could stick that in one variable. Then you can write code to look up payment_received from the other table and stick that in a variable. Then just subtract one from the other with the result ending up in due.
You will need to identify the exact record but again not hard to do.
If I get time I will see if I can write the code for you. But, this week I am taking a break from computers and re-shingling the roof of my house, then replacing the railing on my deck with a glassed in railing. And, if the weather holds, putting a new covering on the roof of my flat roof garage to also use it as a deck. (yes it is summertime here in Calgary). I have a backyard full of materials just waiting to be used. (And a wife telling me to get going on it).
Alan
Re: Balance due
Posted: 2015-06-29 05:11
by udayvatturi
Hi,
Try this example code
Code: Select all
$total_cost=sqlValue("select <field1> from <table name 1> where tableName='{$data['<any field contion>']}'");
$payment_received=sqlValue("select <field 2><table name 2> where tableName='{$data['<any field contion>']}'");
$data['due'] = $total_cost- $payment_received;
Re: Balance due
Posted: 2017-12-19 14:32
by lectura
Halo. I have also tried this and placed it in my table name sales.php: sales_after_insert but the calculation is not picking any data. Where else do i place those codes in hooks?
Re: Balance due
Posted: 2017-12-19 19:51
by mgain2013
If I am not mistaken, this needs to be placed in Sales_before_insert and Sales_before_update locations of the hook!
Re: Balance due
Posted: 2017-12-20 11:23
by lectura
I have used the following codes at after_insert and after_update.
but the 'due' results give me -value that i input at payment i gues it simply calls the value of payment with - e.g (-200) and when i remove the total part in calculation it just display the payment value. I gues its not calling the value at total which is from the other table.
Help me out. Am new with codings
$id = makeSafe(data['id']);
$total= sqlValue("Select total from clients where id ='$total'");
$payment = sqlValue("Select payment from sales where id ='$payment'");
$due = $data['total'] - $data['payment'] ;
Sql ("UPDATE sales SET due = '$due' WHERE id ='$id' " , $eo); return TRUE;
What is required next to perform the maths?
Re: Balance due
Posted: 2017-12-27 16:23
by xbox2007
hello
i test this code with project and work with me , please check
Code: Select all
function sales_after_insert($data, $memberInfo, &$args){
sql("update sales t1 INNER JOIN clients t2 ON t1.name = t2.id SET t1.due= t2.total-t1.payment", $eo);
return TRUE;
}
Re: Balance due
Posted: 2018-01-06 09:19
by lectura
Thank you.. xbox2007
It has worked well..