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.
Jars Total
Jars Total
- Attachments
-
- sales.png (137.57 KiB) Viewed 2678 times
Re: Jars Total
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);
<?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);
Re: Jars Total
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.
should be
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
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<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 readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Jars Total
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.
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.
Re: Jars Total
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);
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);
Re: Jars Total
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);
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);
Re: Jars Total
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
But you need to handle the data in your ajax call to put into the right fields.
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)));
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.