Auto-balance field

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

Auto-balance field

Post by AEmpeno » 2018-08-21 18:36

Hello,

I have "Receiving" table, "Batches" table and "Batchesdetails" table.

Receiving table fields:
+id
+date
+SupplierName(Lookup field - supplier table)
+SupplyName (lookup field - supplies table)
+SupplyLotCode
+AmountReceived
+Balance (need calculated field)

Batches table fields:
+id
+ManuDate
+ExpDate
+ProductName (lookup field - products table)
+LotCode
+ProductionJars
+Pak
+ProductionCases

Batchesdetails table fields:
+id
+batchid (lookup field - batches table)
+ProductName (lookup field - batches table)
+LotCode (lookup field - batches table)
+SupplierName (Lookup field - supplier table)
+SupplyCode (lookup field - receiving table)
+QuantityUsed

I need to get the field "balance" under receiving table.
For example:

Receiving Table
Date Supplier Name Supply Name/Description Supply Lot Code Amount Received Balance

01/01/2018 Fleishmann White Vinegar 14396566 4,871.000 ?

Batches Details
ProductName Lot Code Supplier Name Supply Name/Description Supply Lot Code QuantityUsed
Asparagus 33 oz. CALKGB116H Fleishmann White Vinegar 14396566 270.000


I would like to see 4,601.000 under the balance field as the remaining balance of lot code 14396566.

I appreciate any help or inputs. Thanks!

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

Re: Auto-balance field

Post by pböttcher » 2018-08-21 19:51

Hi,

I guess you want to do that after an insert /or update has happend in the Batchesdetails table. If this is the case you need to add to the
hooks/Batchesdetails.php -> Batchesdetails_after_insert function:

Code: Select all

sqlvalue("update Receiving set Balance=Balance-(select sum(QuantityUsed) from Batchesdetails where batchid=".$data['batchid'].") where id = ".$data['SupplyCode']");
and the same to the Batchesdetails_after_update function.
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: Auto-balance field

Post by AEmpeno » 2018-08-21 21:10

Im getting this error message.

Parse error: syntax error, unexpected 'Receiving' (T_STRING), expecting ',' or ')' in C:\xampp\htdocs\inventory\hooks\batchesdetails.php on line 79

and this is what i have in my hook -batchesdetails.php

function batchesdetails_after_insert($data, $memberInfo, &$args){
sqlvalue(update Receiving set Balance=Balance-(select sum(QuantityUsed) from Batchesdetails where batchid=".$data['batchid'].") where id = ".$data['SupplyLotCode']");

return TRUE;

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

Re: Auto-balance field

Post by pböttcher » 2018-08-23 10:36

Hi,
I think you missen the double Quote befreit the update
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: Auto-balance field

Post by AEmpeno » 2018-09-09 01:19

I'm trying to use the code below for the attached photo. Will this work? How can I make the field name 'SupplyLotCode' as the common field for receiving table and batchesdetails table.



<?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 receiving table */
$od_from = get_sql_from('receiving');
if(!$od_from){
header('HTTP/1.0 401 Unauthorized');
exit;
}

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

$QuantityUsed = sqlValue("select sum(QuantityUsed)from batchesdetails where BatchID='{$id}'");

$balance_Quantity = $QuantityUsed;
sql("update receiving set Balance='{$balance_Quantity}' where id='{$id}'", $eo);

echo number_format($balance_Quantity, 2);
Attachments
receiving.png
receiving.png (174.66 KiB) Viewed 13337 times

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

Re: Auto-balance field

Post by AEmpeno » 2018-09-10 18:02

Here is my updated ajax code.
Also, my fieldname "supplylotcode" in batchesdetails table is a lookup field from receiving table. If anyone has an idea how to fix my code please let me know. Appreciate any help.



<?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 receiving table */
$od_from = get_sql_from('receiving');
if(!$od_from){
header('HTTP/1.0 401 Unauthorized');
exit;
}

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

$AmountReceived = sqlValue("select AmountReceived from receiving where SupplyLotCode='{$SupplyLotCode}'");
$QuantityUsed = sqlValue("select sum(QuantityUsed)from batchesdetails where SupplyLotCode='{$SupplyLotCode}'");


$balance_Quantity = $AmountReceived - $QuantityUsed;
sql("update receiving set Balance='{$balance_Quantity}' where SupplyLotCode='{$SupplyLotCode}'", $eo);

echo number_format($balance_Quantity, 2);

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

Re: Auto-balance field

Post by pböttcher » 2018-09-11 13:37

Hi,

it would be helpful if you could explain the error you are encountering.

I assume that you do not get the correct $QuantityUsed, but you tabledefinition at the top of the post do not show the SupplyLotCode in the batchesdetails table.
Batchesdetails table fields:
+id
+batchid (lookup field - batches table)
+ProductName (lookup field - batches table)
+LotCode (lookup field - batches table)
+SupplierName (Lookup field - supplier table)
+SupplyCode (lookup field - receiving table)
+QuantityUsed
If it exists you can try

Code: Select all

$ID = intval($_REQUEST['id']);
$QuantityUsed = sqlValue("select sum(QuantityUsed)from batchesdetails where SupplyLotCode='{$ID}'");
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: Auto-balance field

Post by AEmpeno » 2018-09-11 19:08

Thanks you. The only problem I'm having right now is the balance still shows "0.00"
I did add the "supplylotcode" lately to try new fieldname so it will be easy to remember and to make the name same as in receiving table.

Below is my new script. I'm still getting "0.00" as balance.

<?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 receiving table */
$od_from = get_sql_from('receiving');
if(!$od_from){
header('HTTP/1.0 401 Unauthorized');
exit;
}

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

$QuantityUsed = sqlValue("SELECT sum(QuantityUsed) FROM batchesdetails where SupplyLotCode='{$ID}'");
$AmountReceived = sqlValue("SELECT AmountReceived FROM receiving where SupplyLotCode='{$SupplyLotCode}'");

$balance_Quantity = $AmountReceived - $QuantityUsed;
sql("update receiving set Balance='{$balance_Quantity}' where SupplyLotCode='{$SupplyLotCode}'", $eo);

echo number_format($balance_Quantity, 2);

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

Re: Auto-balance field

Post by pböttcher » 2018-09-12 05:42

Hi,

you removed the

Code: Select all

$SupplyLotCode = intval($_REQUEST['SupplyLotCode']);
you need to have both

Code: Select all

$SupplyLotCode = intval($_REQUEST['SupplyLotCode']);
$ID = intval($_REQUEST['id']);
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: Auto-balance field

Post by AEmpeno » 2018-09-12 13:46

Hello,
Thank you for your reply. I added your code yet there's no changes on my balance. I'm still getting "0.00".

Here is my new code:

Code: Select all


	$SupplyLotCode = intval($_REQUEST['SupplyLotCode']);
	$ID = intval($_REQUEST['id']);
	if(!$SupplyLotCode) exit;
	
	$QuantityUsed = sqlValue("SELECT sum(QuantityUsed) FROM batchesdetails WHERE SupplyLotCode='{$ID}'");
	$AmountReceived = sqlValue("SELECT AmountReceived FROM receiving WHERE SupplyLotCode='{$SupplyLotCode}'");
	
	$balance_Quantity = $AmountReceived - $QuantityUsed;
	sql("update receiving SET Balance='{$balance_Quantity}' WHERE SupplyLotCode='{$SupplyLotCode}'", $eo);
	
	echo number_format($balance_Quantity, 2);
	
	

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

Re: Auto-balance field

Post by pböttcher » 2018-09-12 14:15

Hi,

can you provide you tablestructure for the batchesdetails, Batches and receiving table. Also, from where are you calling this code?
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: Auto-balance field

Post by AEmpeno » 2018-09-12 14:52

Hello,

Here is my table structure

Receiving table fields:
+id
+date
+SupplierName(Lookup field - supplier table)
+SupplyName (lookup field - supplies table)
+SupplyLotCode
+AmountReceived
+UoM
+Storage
+Balance (need calculated field)

Batches table fields:
+id
+ManuDate
+ExpDate
+ProductName (lookup field - products table)
+LotCode
+ProductionJars
+Pak
+ProductionCases
+Origin (lookup field - origin table)
+ContainerSize (lookup field - containersize table)
+ProductType (lookup field - producttype table)
+ProductSize (lookup field - productsize table)

Batchesdetails table fields:
+id
+batchid (lookup field - batches table)
+ProductName (lookup field - batches table)
+LotCode (lookup field - batches table)
+SupplierName (Lookup field - supplier table)
+SupplyCode (lookup field - receiving table)
+QuantityUsed

The code is saved in ajax-receiving-balance inside the hooks folder.

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

Re: Auto-balance field

Post by pböttcher » 2018-09-12 16:37

Hi,

the field SupplyLotCode is not part of the Batchesdetails as definied in your SQL statement

SELECT sum(QuantityUsed) FROM batchesdetails WHERE SupplyLotCode='{$ID}'

Can you check if that is the case.

Also, from where are you calling the ajax-receiving-balance ?
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: Auto-balance field

Post by AEmpeno » 2018-09-12 19:12

Sorry it was a typo error.

It should be
+SupplyLotCode (lookup field - receiving table)

I created receiving-dv.js and inserted the code below.

Code: Select all


/* update balance total automatically every 5 seconds */
if($j('[name=SelectedID]').val().length) setInterval(update_Balance, 5000);
	
/* load balance total on loading the page */
update_Balance();

});

function update_Balance(){
$j('#Balance').load('hooks/ajax-receiving-balance.php', { id: $j('[name=SelectedID]').val() });

}

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

Re: Auto-balance field

Post by pböttcher » 2018-09-13 07:31

Hi,

can you try this:

Code: Select all

	$ID = intval($_REQUEST['id']);
	if(!$ID) exit;
	
	$balance_Quantity = sqlValue("SELECT r.AmountReceived-sum(b.QuantityUsed) from receiving r left join batchesdetails b on b.SupplyLotCode=r.id where r.id='{$ID}'");
	sql("update receiving SET Balance='{$balance_Quantity}' WHERE SupplyLotCode='{$SupplyLotCode}'", $eo);
	
	echo number_format($balance_Quantity, 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.

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

Re: Auto-balance field

Post by AEmpeno » 2018-09-13 15:33

Hello,

I inserted your suggested code, saved it, and refreshed the page but balance still shows "0.00"

Did I miss something?

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

Re: Auto-balance field

Post by AEmpeno » 2018-09-13 15:45

Attached is my appgini table structure - receiving, batches, batchesdetails.
Attachments
pic1.jpg
pic1.jpg (180.49 KiB) Viewed 13210 times

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

Re: Auto-balance field

Post by pböttcher » 2018-09-14 06:53

Hi,

can you change your receiving-dv.js to:

Code: Select all

$j(function () {
  if($j('[name=SelectedID]').val().length) setInterval(update_Balance, 5000);
	
/* load balance total on loading the page */
  update_Balance();


  function update_Balance(){
    $j('#Status').load('hooks/ajax-receiving-balance.php', { id: $j('[name=SelectedID]').val() });
  }
});
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: Auto-balance field

Post by AEmpeno » 2018-09-14 18:55

Hello,

Thanks pböttcher!

The calculation is showing now on the field, however if i do changes on "QuantityUsed" and save it. The "balance" field does not change unless if I open another browser and type localhost/inventory/hooks/ajax-receiving-balance.php?id=128 (it gives me the right amount) then I refreshed my page then I get the right amount on my "balance".

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

Re: Auto-balance field

Post by pböttcher » 2018-09-16 11:39

Hi,

the calculation happens only if you are looking at the details view, so if you change e.g. the QuantityUsed you will need to open the details view to get the correct data set. If you look at the table view you will see the "old/unchanged" data.

If you want to get calculation once the data changes, it would be best to handle this via the after-inser, after-update, after-delete functions in the respective hooks.
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: Auto-balance field

Post by AEmpeno » 2018-09-18 15:31

Hello,

I've been getting a warning message where I have my calculated field. Does anyone know why I'm getting this warning message? This happens when I added two more calculated fields on the same table.


Warning: Use of undefined constant label_jars - assumed 'label_jars' (this will throw an Error in a future version of PHP) in C:\xampp\htdocs\inventory\hooks\ajax-batches-balance.php on line 19

Warning: number_format() expects parameter 1 to be float, string given in C:\xampp\htdocs\inventory\hooks\ajax-batches-balance.php on line 22

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

Re: Auto-balance field

Post by pböttcher » 2018-09-18 16:37

Hi,

can you post your code
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: Auto-balance field

Post by AEmpeno » 2018-09-18 18:34

Hi pböttcher,

For some reason, it disappears. But here are my unfinished codes - ajax-batches-balance.php and batches-dv.js.

Im trying to get the OnHoldJars and OnHoldCases but no success especially for the OnHold Jars?

Code: Select all

<?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 batches table */
	$od_from = get_sql_from('batches');
	if(!$od_from){
		header('HTTP/1.0 401 Unauthorized');
		exit;
	}
	
	$id = intval($_REQUEST['id']);
	if(!$id) exit;
	
	$label_jars = sqlValue("SELECT sum(LabelJars) FROM label WHERE BatchesID='{$id}'");
	
	$balance_jars = $label_jars;
	sql("UPDATE batches SET LabeledJars='{$balance_jars}' WHERE BatchesID='{$id}'", $eo);
	
	$label_cases = sqlValue("SELECT sum(LabelCases) FROM label WHERE BatchesID='{$id}'");
	
	$balance_cases = $label_cases;
	sql("UPDATE batches SET LabeledCases='{$balance_cases}' WHERE BatchesID='{$id}'", $eo);
	

	
	$sold_jars = sqlValue("SELECT sum(Jars) FROM salesdetails WHERE BatchesID='{$id}'");
	
	$total_soldjars = $sold_jars;
	sql("UPDATE batches SET SoldJars='{$total_soldjars}' WHERE BatchesID='{$id}'", $eo);
	
	
	$sold_cases = sqlValue("SELECT sum(Cases) FROM salesdetails WHERE BatchesID='{$id}'");
	
	$total_soldjars = $sold_cases;
	sql("UPDATE batches SET SoldCases='{$total_soldjars}' WHERE BatchesID='{$id}'", $eo);
	
	
	echo number_format($balance_jars, 2);

Code: Select all

$j(function () {
	$j('#ProductionCases').prop('readonly', true);
	$j('#LabeledJars').prop('readonly', true);
	$j('#LabeledCases').prop('readonly', true);
	$j('#SoldJars').prop('readonly', true);
	$j('#SoldCases').prop('readonly', true);
	$j('#OnHandJars').prop('readonly', true);
	$j('#OnHandCases').prop('readonly', true);

	var update_ProductionCases = function () {
			var v_ProductionJars = $j('#ProductionJars').val();
			var v_Pak = $j('#Pak').val();
			
			var v_ProductionCases = v_ProductionJars*1/v_Pak*1;
			$j('#ProductionCases').val(v_ProductionCases);
	}
	var update_OnHandJars = function () {
			var v_ProductionJars = $j('#ProductionJars').val();
			var v_SoldJars = $j('#SoldJars').val();
			
			var v_OnHandJars = v_ProductionJars*1-v_SoldJars*1;
			$j('#OnHandJars').val(v_OnHandJars);
	}
		$j('#ProductionJars' ).on('change', update_ProductionCases);
		$j('#Pak' ).on('change', update_ProductionCases);
		
		$j('#ProductionJars' ).on('change', update_OnHandJars);
		$j('#SoldJars' ).on('change', update_OnHandJars);

	});
	
  function update_LabeledJars(){
    $j('#LabeledJars').load('hooks/ajax-batches-balance.php', { id: $j('[name=SelectedID]').val() });
  }
  	
	/* update balance jars total automatically every 5 seconds */
	if($j('[name=SelectedID]').val().length) setInterval(update_LabeledJars, 5000);
	
	/* load balance jars on loading the page */
	update_LabeledJars();
Attachments
batches.png
batches.png (39.91 KiB) Viewed 13094 times

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

Re: Auto-balance field

Post by pböttcher » 2018-09-19 11:52

Hi,
thanks, can you please explain what you mean by "it disappears". Does the calculated value show up? When does it disappear?
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: Auto-balance field

Post by AEmpeno » 2019-04-04 18:25

Hello,

You mentioned from previous conversation that "if you want to get calculation once the data changes, it would be best to handle this via the after-insert, after-update, after-delete functions in the respective hooks." Will you please guide me on this?

It's been a year since I started this project and my data on my project are getting huge now. I have several child tables and every time there is data entry or change on each table ... I have to refresh each parent table just to reflect the changes on calculated field.

Pretty much, I am looking for a script where there any changes on table, the detail view and table view would automatically update too.

Best, Lon

Post Reply