More than 1 calculation

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
dharbitindy
Veteran Member
Posts: 32
Joined: 2019-05-26 18:38

More than 1 calculation

Post by dharbitindy » 2019-06-18 04:17

Could someone give a short example of how to do a calculation on more than one field in the before update / after insert? For instance, the following works fine, but how can I add to it for another field? Or can I? part_weight is from another table called quoting, and lbs_per_m is in the existing results table.

function results_before_update(&$data, $memberInfo, &$args){

$part_weight = sqlValue(
"SELECT part_weight FROM quoting where quoting_ID='{$data['quoting_ID']}'"
);
$data['lbs_per_m'] = $part_weight * 1000 / 16;

Thank you,
David

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 611
Joined: 2018-04-01 10:12

Re: More than 1 calculation

Post by pböttcher » 2019-06-18 06:25

Hi,

you can do as many calculation as complex as you wish. If you need some values from the DB, retrieve them via sql or sqlvalue.
The $data array hold all your field that will be updated in the DB for this table.
But you could even update via SQL any other data in your database. It's your choice. If you browse through the forum you will find lot's of examples.

dharbitindy
Veteran Member
Posts: 32
Joined: 2019-05-26 18:38

Re: More than 1 calculation

Post by dharbitindy » 2019-06-18 13:09

I tried this below, but it doesn't work. Trying to find examples, but having problems finding...

function results_before_insert(&$data, $memberInfo, &$args){

$matl_price = sqlValue(
"SELECT matl_price FROM quoting where quotingID='{$data['quoting_ID']}'"
);
$data['matl_cost'] = $data['lbs_per_m'] * $matl_price;

$part_weight = sqlValue(
"SELECT part_weight FROM quoting where quotingID='{$data['quoting_ID']}'"
);
$data['lbs_per_m'] = $part_weight * 1000 / 16;


return TRUE;
}

Thank you.

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 611
Joined: 2018-04-01 10:12

Re: More than 1 calculation

Post by pböttcher » 2019-06-18 15:18

What is not working? Are you getting wrong results, no results, ???
Can you share your table definitions with the relations?

dharbitindy
Veteran Member
Posts: 32
Joined: 2019-05-26 18:38

Re: More than 1 calculation

Post by dharbitindy » 2019-06-18 17:28

I certainly will. Thank you! (Putting in a link to pictures) Tried to put in message, but said they are too large? Less than 500K

https://drive.google.com/drive/folders/ ... sp=sharing

David

dharbitindy
Veteran Member
Posts: 32
Joined: 2019-05-26 18:38

Re: More than 1 calculation

Post by dharbitindy » 2019-06-18 17:46

Added some more pics to above link for explanation, and to clarify the above, getting no results, or 0.00. Not wrong results.

Thank you,
David

User avatar
landinialejandro
Posts: 21
Joined: 2016-03-06 00:59

Re: More than 1 calculation

Post by landinialejandro » 2019-06-18 22:33

dharbitindy wrote:
2019-06-18 17:46
Added some more pics to above link for explanation, and to clarify the above, getting no results, or 0.00. Not wrong results.

Thank you,
David
Hi!, i think do you have a problem with a varible
try this code after return, debug and check the result in browser

Code: Select all

ob_start();
        print_r($data);
        print_r($matl_price);
        print_r($part_weight);
    $c=ob_get_clean();
    echo "<pre>".htmlspecialchars($c)."</pre>";

dharbitindy
Veteran Member
Posts: 32
Joined: 2019-05-26 18:38

Re: More than 1 calculation

Post by dharbitindy » 2019-06-19 00:06

Hi, I put this in, but not seeing anything new in browser?

function results_before_insert(&$data, $memberInfo, &$args){

$part_weight = sqlValue(
"SELECT part_weight FROM quoting where quoting_ID='{$data['quoting_ID']}'"
);
$data['lbs_per_m'] = $part_weight * 1000 / 16;

$matl_price = sqlValue(
"SELECT matl_price FROM quoting where quoting_ID='{$data['quoting_ID']}'"
);
$data['matl_cost'] = $data['lbs_per_m'] * $matl_Price;

return TRUE;

ob_start();
print_r($data);
print_r($matl_price);
print_r($part_weight);
$c=ob_get_clean();
echo "<pre>".htmlspecialchars($c)."</pre>";

}

Thank you,
David

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 611
Joined: 2018-04-01 10:12

Re: More than 1 calculation

Post by pböttcher » 2019-06-19 07:35

Hi,

I think the issue is because you try to get the matl_price from the quoting table, but instead the matl_price in the quoting table is a lookup to the materials table. So you need actually to pick the matl_price from the materials table.

so you may try this

Code: Select all

$from = get_sql_from('quoting');
$matl_price = sqlvalue("SELECT price_lb from ".$from." and quoting.quoting_ID='{$data['quoting_ID']}'");
$data['matl_cost'] = $data['lbs_per_m'] * $matl_Price;

dharbitindy
Veteran Member
Posts: 32
Joined: 2019-05-26 18:38

Re: More than 1 calculation

Post by dharbitindy » 2019-06-19 15:38

I was wondering if that may be what the problem was, but wasn't sure how to structure the code. Thanks so much, and I'll give it a whirl and see what happens. I really appreciate you taking the time to look at this for me, and I have no problem paying for your time at some point if you are interested. I think that once I get familiar with how the code should be structured, I'll have way fewer questions. I come from a VB.net world and I'm just not familiar with the PHP, Java, Ajax, etc., but again, thank you!

David

User avatar
landinialejandro
Posts: 21
Joined: 2016-03-06 00:59

Re: More than 1 calculation

Post by landinialejandro » 2019-06-19 21:39

dharbitindy wrote:
2019-06-19 00:06
Hi, I put this in, but not seeing anything new in browser?

function results_before_insert(&$data, $memberInfo, &$args){

$part_weight = sqlValue(
"SELECT part_weight FROM quoting where quoting_ID='{$data['quoting_ID']}'"
);
$data['lbs_per_m'] = $part_weight * 1000 / 16;

$matl_price = sqlValue(
"SELECT matl_price FROM quoting where quoting_ID='{$data['quoting_ID']}'"
);
$data['matl_cost'] = $data['lbs_per_m'] * $matl_Price;

return TRUE;

ob_start();
print_r($data);
print_r($matl_price);
print_r($part_weight);
$c=ob_get_clean();
echo "<pre>".htmlspecialchars($c)."</pre>";

}

Thank you,
David
i'm sorry is before return...

dharbitindy
Veteran Member
Posts: 32
Joined: 2019-05-26 18:38

Re: More than 1 calculation

Post by dharbitindy » 2019-06-20 04:10

pböttcher wrote:
2019-06-19 07:35
Hi,

I think the issue is because you try to get the matl_price from the quoting table, but instead the matl_price in the quoting table is a lookup to the materials table. So you need actually to pick the matl_price from the materials table.

so you may try this

Code: Select all

$from = get_sql_from('quoting');
$matl_price = sqlvalue("SELECT price_lb from ".$from." and quoting.quoting_ID='{$data['quoting_ID']}'");
$data['matl_cost'] = $data['lbs_per_m'] * $matl_Price;
Unfortunately that didn't work either. What I did do was to rename the matl_price field to price_lb like the materials table and surprisingly, I did get at least a number in the matl_cost field in the results table. Here is the weird part. The calculated number that shows in the results is the price_lb * the record number??? I'm now wondering that maybe I need to erase the mysql database and re-input the data? I'm not sure what is going on with the data. This is only after I changed the code back to this below. The above code with the $matl_price showed $0.00 like before, is when I decided to try "like fields" between the materials table and the quoting table. I did also delete all data (in localhost) and re-enter before saving to see if that was causing issues. I'm sure that I'm missing something obvious, but I just can't see it at the moment?

$price_lb = sqlValue(
"SELECT price_lb FROM quoting where quoting_ID='{$data['quoting_ID']}'"
);
$data['matl_cost'] = $data['lbs_per_m'] * $price_lb;

Thanks again,
David

dharbitindy
Veteran Member
Posts: 32
Joined: 2019-05-26 18:38

Re: More than 1 calculation

Post by dharbitindy » 2019-06-20 12:47

I've attached a picture showing the new results to elaborate on my last post.

Thank you,
David
Attachments
ResultsCalc.JPG
ResultsCalc.JPG (128.36 KiB) Viewed 855 times

dharbitindy
Veteran Member
Posts: 32
Joined: 2019-05-26 18:38

Re: More than 1 calculation

Post by dharbitindy » 2019-06-20 12:49

The above picture didn't show the "SELECT" statement.

$price_lb = sqlValue(
"SELECT price_lb FROM quoting where quoting_ID='{$data['quoting_ID']}'"
);
$data['matl_cost'] = $data['lbs_per_m'] * $price_lb;

dharbitindy
Veteran Member
Posts: 32
Joined: 2019-05-26 18:38

Re: More than 1 calculation

Post by dharbitindy » 2019-06-23 23:45

I cannot say enough good about pböttcher. Super helpful, and knowledgeable. Fortunate to have him here in the forums!

Best regards,
David

Nicolas
Veteran Member
Posts: 37
Joined: 2015-12-08 08:08
Location: Barcelona
Contact:

Re: More than 1 calculation

Post by Nicolas » 2019-06-24 10:24

Hi there, with appgini 5.51

I've done this on the hooks file, for your refference I have an invoice with several lines which are filled on each new invoice or modification
function invoices_after_update($data, $memberInfo, &$args){
$data['T1'] = $data['C1'] * $data['V1'];
$data['T2'] = $data['C2'] * $data['V2'];
$data['T12'] = $data['T1'] + $data['T2'];
$data['T3'] = $data['C3'] * $data['V3'];
$data['T4'] = $data['C4'] * $data['V4'];
$data['T34'] = $data['T3'] + $data['T4'];
$data['I4Val'] = ($data['T34'] * $data['I4Porc'] /100);
$data['T5IVA'] = ($data['T34'] + $data['I4Val']);
$data['TotalFac'] = $data['T12'] + $data['T5IVA'];
$data['InvMarg100'] = (($data['T12'] + $data['T34'] -$data['InvMargen']) / ($data['T12']+$data['T34'])) * 100 ;
if (('td.invoices-FechaPago') == '01/01/1900'){
UpdateAgingNotPaid ("");
}
else{
UpdateAgingPaid ("");
};
Year("");
Month("");
Quarter("");

return TRUE;
}

Hope this helps and work for you!

Post Reply