Jars Total

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
AEmpeno
Veteran Member
Posts: 72
Joined: 2018-01-04 18:48

Jars Total

Post by AEmpeno » 2018-08-23 23:00

I have "Sales" table and "Sales Details" table. Under "Sales Details" table I have two fields - "Jars" and "Cases"

I need to calculate the Total Jars and Total Cases and reflect the total to my "Sales" table under "TotalJars" and "TotalCases" fields.

Please see attachment.
Attachments
sales.png
sales.png (137.57 KiB) Viewed 2674 times

AEmpeno
Veteran Member
Posts: 72
Joined: 2018-01-04 18:48

Re: Jars Total

Post by AEmpeno » 2018-09-07 03:40

I need help please. Below is my ajax script code; Im having an issue to display the Total Cases, I'm only seeing 0.00. I also tested per my video course showed but I'm getting "0.00" as the total cases. Will someone please help or point me the error in my script. Thanks so much.

<?php
$currDir = dirname(__FILE__) . '/..';
include("$currDir/defaultLang.php");
include("$currDir/language.php");
include("$currDir/lib.php");

/* grant access to all users who have acess to the sales table */
$od_from = get_sql_from('sales');
if(!$od_from){
header('HTTP/1.0 401 Unauthorized');
exit;
}

$id = intval($_REQUEST['id']);
if(!$id) exit;

$sales_SubTotal = sqlValue("select sum((Cases * Pak) + Jars) from salesdetails where SalesID='{$id}'");

$sales_TotalCases = $SubTotal;
sql("update sales set TotalCases='{$sales_TotalCases}' where SalesID='{$id}'", $eo);

echo number_format($sales_TotalCases, 2);

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Jars Total

Post by jsetzer » 2018-09-07 07:21

What is the result of the call?

To narrow things down, you should set $sales_TotalCases to 999 or something and then see, if this gets written to the database and if this is the return value of your script.

You should check $eo variable after sql(...) call.

Maybe there is a problem in the SQL update statement: if TotalCases is a numeric field (integer, float), you should leave out the ' ' characters.

Code: Select all

TotalCases='{$sales_TotalCases}'

should be

Code: Select all

TotalCases={$sales_TotalCases} 
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Jars Total

Post by pbottcher » 2018-09-07 08:49

Hi,

I think you missasing your $sales_TotalCases, the $SubTotal is not set in your script.

I guess you want to use

$sales_TotalCases = $sales_SubTotal;

instead.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

AEmpeno
Veteran Member
Posts: 72
Joined: 2018-01-04 18:48

Re: Jars Total

Post by AEmpeno » 2018-09-07 21:14

Thanks so much. It works!

So if I wanted to add another column for total jars should I just add or insert this code below?

$sales_TotalJars = sqlValue("select sum(Jars) from salesdetails where SalesID='{$id}'");

$TotalJars = $sales_TotalJars;
sql("update sales set TotalJars='{$TotalJars}' where SalesID='{$id}'", $eo);

echo number_format($sales_TotalJars, 2);

AEmpeno
Veteran Member
Posts: 72
Joined: 2018-01-04 18:48

Re: Jars Total

Post by AEmpeno » 2018-09-07 21:31

So I tried this code and both total jars and total cases are showing their total sums however Im getting this:

Total Jars 25.003,964.00
TotalCases 3964.00

<?php
$currDir = dirname(__FILE__) . '/..';
include("$currDir/defaultLang.php");
include("$currDir/language.php");
include("$currDir/lib.php");

/* grant access to all users who have acess to the sales table */
$od_from = get_sql_from('sales');
if(!$od_from){
header('HTTP/1.0 401 Unauthorized');
exit;
}

$id = intval($_REQUEST['id']);
if(!$id) exit;

$sales_TotalJars = sqlValue("select sum(Jars)from salesdetails where SalesID='{$id}'");

$sales_TotalJars = $sales_TotalJars;
sql("update sales set TotalJars='{$sales_TotalJars}' where SalesID='{$id}'", $eo);

echo number_format($sales_TotalJars, 2);

$sales_TotalCases = sqlValue("select sum(Cases)from salesdetails where SalesID='{$id}'");

$sales_TotalCases = $sales_TotalCases;
sql("update sales set TotalCases='{$sales_TotalCases}' where SalesID='{$id}'", $eo);

echo number_format($sales_TotalCases, 2);

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Jars Total

Post by pbottcher » 2018-09-08 16:36

Hi,

if you want to handle more than one dataset, you need to return the data in a different format, eg. json

The echo is used as return of your call, so use it only once.

you could use

Code: Select all

	echo json_encode(array(
		'sales_TotalJars' => number_format($sales_TotalJars, 2),
		'sales_TotalCases' => number_format($sales_TotalCases, 2)));
But you need to handle the data in your ajax call to put into the right fields.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

Post Reply