Calculation Hook for reducing balance automatically

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
jnewtcomp
Posts: 19
Joined: 2019-07-25 08:05

Calculation Hook for reducing balance automatically

Post by jnewtcomp » 2019-07-27 14:12

Hello,

I have tried to use the examples on this website and have failed to complete the task. I am trying to auto update a balance in inventory from a consumable tools check out field. Listed below are my tables.

Consumable_Tools
ID
Item
Date_Issued
Employee
Order_Number
Part_Number
Operation
Qty_Issued <--- Qty to remove from Inventory
Size
Unit_of_Measure
MFG_Part_Number
Stock_QTY <----Link to inventory
Minimum_Stock
Notes

Inventory
ID
Item
Manufacturer
MFG_Part_Number
Tool_Type
Size
Base_Material
Coating
Location
Supplier
Stock_QTY <----- balance to be updated from Consumable_Tools
Minimum_Stock
Condition
Notes

Thank you in advanced for any help you can give.

Jim

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

Re: Calculation Hook for reducing balance automatically

Post by pböttcher » 2019-07-28 21:11

Hi,

you can do this in the hooks/Consumable_Tools.php file.

In the after_insert, after_update and bevor_delete function you need to add you calculation.

so, eg. in the after_insert function you can try

Code: Select all

$new_qty=sqlvalue("SELECT i.Stock_QTY-{$data['Qty_Issued']} from Inventory i where i.id = {$data['Stock_QTY']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.id ={$data['Stock_QTY']}");

jnewtcomp
Posts: 19
Joined: 2019-07-25 08:05

Re: Calculation Hook for reducing balance automatically

Post by jnewtcomp » 2019-07-29 13:15

Thank you for your reply, I placed this code after_insert, after_update and after_Delete.

The qty did not change no matter where i placed this code. I really thank you for the help but I am obviously making a mistake some place. please help.

Thank you!!!!!

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

Re: Calculation Hook for reducing balance automatically

Post by pböttcher » 2019-07-29 14:36

Hi,

can you post the complete function as you have it now. And a screenshot of the Consumable_Tools Stock_QTY defintion in AppGini

jnewtcomp
Posts: 19
Joined: 2019-07-25 08:05

Re: Calculation Hook for reducing balance automatically

Post by jnewtcomp » 2019-07-29 14:52

this is currently my Consumable_Tools.php file

<?php
// For help on using hooks, please refer to https://bigprof.com/appgini/help/workin ... tion/hooks

function Consumable_Tools_init(&$options, $memberInfo, &$args){

return TRUE;
}

function Consumable_Tools_header($contentType, $memberInfo, &$args){
$header='';

switch($contentType){
case 'tableview':
$header='';
break;

case 'detailview':
$header='';
break;

case 'tableview+detailview':
$header='';
break;

case 'print-tableview':
$header='';
break;

case 'print-detailview':
$header='';
break;

case 'filters':
$header='';
break;
}

return $header;
}

function Consumable_Tools_footer($contentType, $memberInfo, &$args){
$footer='';

switch($contentType){
case 'tableview':
$footer='';
break;

case 'detailview':
$footer='';
break;

case 'tableview+detailview':
$footer='';
break;

case 'print-tableview':
$footer='';
break;

case 'print-detailview':
$footer='';
break;

case 'filters':
$footer='';
break;
}

return $footer;
}

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

return TRUE;
}

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

return TRUE;
}

$new_qty=sqlvalue("SELECT i.Stock_QTY-{$data['Qty_Issued']} from Inventory i where i.id = {$data['Stock_QTY']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.id ={$data['Stock_QTY']}");


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

return TRUE;
}


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

return TRUE;
}

$new_qty=sqlvalue("SELECT i.Stock_QTY-{$data['Qty_Issued']} from Inventory i where i.id = {$data['Stock_QTY']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.id ={$data['Stock_QTY']}");


function Consumable_Tools_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){

return TRUE;
}

function Consumable_Tools_after_delete($selectedID, $memberInfo, &$args){

}

$new_qty=sqlvalue("SELECT i.Stock_QTY-{$data['Qty_Issued']} from Inventory i where i.id = {$data['Stock_QTY']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.id ={$data['Stock_QTY']}");


function Consumable_Tools_dv($selectedID, $memberInfo, &$html, &$args){

}

function Consumable_Tools_csv($query, $memberInfo, &$args){

return $query;
}
function Consumable_Tools_batch_actions(&$args){

return array();
}

jnewtcomp
Posts: 19
Joined: 2019-07-25 08:05

Re: Calculation Hook for reducing balance automatically

Post by jnewtcomp » 2019-07-29 15:10

This is inventory.php

?php
// For help on using hooks, please refer to https://bigprof.com/appgini/help/workin ... tion/hooks

function Inventory_init(&$options, $memberInfo, &$args){

return TRUE;
}

function Inventory_header($contentType, $memberInfo, &$args){
$header='';

switch($contentType){
case 'tableview':
$header='';
break;

case 'detailview':
$header='';
break;

case 'tableview+detailview':
$header='';
break;

case 'print-tableview':
$header='';
break;

case 'print-detailview':
$header='';
break;

case 'filters':
$header='';
break;
}

return $header;
}

function Inventory_footer($contentType, $memberInfo, &$args){
$footer='';

switch($contentType){
case 'tableview':
$footer='';
break;

case 'detailview':
$footer='';
break;

case 'tableview+detailview':
$footer='';
break;

case 'print-tableview':
$footer='';
break;

case 'print-detailview':
$footer='';
break;

case 'filters':
$footer='';
break;
}

return $footer;
}

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

return TRUE;
}

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

return TRUE;
}

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

return TRUE;
}

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

return TRUE;
}

function Inventory_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){

return TRUE;
}

function Inventory_after_delete($selectedID, $memberInfo, &$args){

}

function Inventory_dv($selectedID, $memberInfo, &$html, &$args){

}

function Inventory_csv($query, $memberInfo, &$args){

return $query;
}
function Inventory_batch_actions(&$args){

return array();
}

jnewtcomp
Posts: 19
Joined: 2019-07-25 08:05

Re: Calculation Hook for reducing balance automatically

Post by jnewtcomp » 2019-07-29 15:17

appgini-.jpg
appgini-.jpg (41.08 KiB) Viewed 842 times

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

Re: Calculation Hook for reducing balance automatically

Post by pböttcher » 2019-07-29 16:04

Hi,

put the code inside the function, like this.

Code: Select all

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

$new_qty=sqlvalue("SELECT i.Stock_QTY-{$data['Qty_Issued']} from Inventory i where i.InventoryID = {$data['Stock_QTY']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.InventoryID ={$data['Stock_QTY']}");

return TRUE;
}
Also you have a different PK, so I adjusted the code. Hope that works.

jnewtcomp
Posts: 19
Joined: 2019-07-25 08:05

Re: Calculation Hook for reducing balance automatically

Post by jnewtcomp » 2019-07-29 16:34

Hello pbottcher,

I have added the code as posted and when i open the browser and choose the Consumable_Tools the page is blank, I remove the code listed above and the page comes up as normal. I really appreciate your help.

jnewtcomp
Posts: 19
Joined: 2019-07-25 08:05

Re: Calculation Hook for reducing balance automatically

Post by jnewtcomp » 2019-07-29 17:11

I was able to view the Consumable_Tools interface but unable to update the qty. I have attached my PHP file here.

<?php
// For help on using hooks, please refer to https://bigprof.com/appgini/help/workin ... tion/hooks

function Consumable_Tools_init(&$options, $memberInfo, &$args){

return TRUE;
}

function Consumable_Tools_header($contentType, $memberInfo, &$args){
$header='';

switch($contentType){
case 'tableview':
$header='';
break;

case 'detailview':
$header='';
break;

case 'tableview+detailview':
$header='';
break;

case 'print-tableview':
$header='';
break;

case 'print-detailview':
$header='';
break;

case 'filters':
$header='';
break;
}

return $header;
}

function Consumable_Tools_footer($contentType, $memberInfo, &$args){
$footer='';

switch($contentType){
case 'tableview':
$footer='';
break;

case 'detailview':
$footer='';
break;

case 'tableview+detailview':
$footer='';
break;

case 'print-tableview':
$footer='';
break;

case 'print-detailview':
$footer='';
break;

case 'filters':
$footer='';
break;
}

return $footer;
}

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

return TRUE;
}

function Consumable_Tools_after_insert($data, $memberInfo, &$args){
$new_qty=sqlvalue("SELECT i.Stock_QTY-{$data['Qty_Issued']} from Inventory i where i.InventoryID = {$data['Stock_QTY']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.InventoryID ={$data['Stock_QTY']}");

return TRUE;
}


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

return TRUE;
}


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

return TRUE;
}


function Consumable_Tools_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){

return TRUE;
}

function Consumable_Tools_after_delete($selectedID, $memberInfo, &$args){

}

function Consumable_Tools_dv($selectedID, $memberInfo, &$html, &$args){

}

function Consumable_Tools_csv($query, $memberInfo, &$args){

return $query;
}
function Consumable_Tools_batch_actions(&$args){

return array();
}

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

Re: Calculation Hook for reducing balance automatically

Post by pböttcher » 2019-07-29 17:44

Hi,

what is the link (lookup) from the Consumable_Tools to the Inventory? Is it the item?

jnewtcomp
Posts: 19
Joined: 2019-07-25 08:05

Re: Calculation Hook for reducing balance automatically

Post by jnewtcomp » 2019-07-29 18:08

Yes the item is my part number / stock number from bar-code.

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

Re: Calculation Hook for reducing balance automatically

Post by pböttcher » 2019-07-29 21:08

Hi,

can you try

Code: Select all

$new_qty=sqlvalue("SELECT IFNULL(i.Stock_QTY-{$data['Qty_Issued']},0) from Inventory i where i.InventoryID = {$data['Stock_QTY']}"); 
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.InventoryID ={$data['Stock_QTY']}");
Are you having data in your inventory table?

jnewtcomp
Posts: 19
Joined: 2019-07-25 08:05

Re: Calculation Hook for reducing balance automatically

Post by jnewtcomp » 2019-07-29 21:27

I have tried this code and it does not function. I have thousands of items in inventory at this time. I have placed this code as shown below


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

$new_qty=sqlvalue("SELECT IFNULL(i.Stock_QTY-{$data['Qty_Issued']},0) from Inventory i where i.InventoryID = {$data['Stock_QTY']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.InventoryID ={$data['Stock_QTY']}");

return TRUE;
}

jnewtcomp
Posts: 19
Joined: 2019-07-25 08:05

Re: Calculation Hook for reducing balance automatically

Post by jnewtcomp » 2019-07-30 12:50

Toolcrib.zip
Zip copy of my axp
(9.33 KiB) Downloaded 10 times

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

Re: Calculation Hook for reducing balance automatically

Post by pböttcher » 2019-07-30 19:34

Thanks,

can you try this:

Code: Select all

	function Consumable_Tools_after_insert($data, $memberInfo, &$args){
		$new_qty=sqlvalue("SELECT IFNULL(i.Stock_QTY,0)-{$data['Qty_Issued']} from Inventory i where i.InventoryID = {$data['Item']}"); 
		sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.InventoryID ={$data['Item']}");	
		return TRUE;
	}



	function Consumable_Tools_before_update(&$data, $memberInfo, &$args){
		$old_qty=sqlvalue("SELECT IFNULL(Qty_Issued,0) from Consumable_Tools where Consumable_ToolID={$data['selectedID']}");
		$new_qty=sqlvalue("SELECT IFNULL(i.Stock_QTY+{$old_qty}-{$data['Qty_Issued']},0) from Inventory i where i.InventoryID = {$data['Item']}");
		sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.InventoryID ={$data['Item']}");

		return TRUE;
	}
Also I noticed that you do not have the Item as mandatory in your Consumable Tools table. So you could create an entry without Item which would cause the queries to fail. So either you should make it mandatory, or you need to add a check if $data['Item'] has a value.

jnewtcomp
Posts: 19
Joined: 2019-07-25 08:05

Re: Calculation Hook for reducing balance automatically

Post by jnewtcomp » 2019-07-30 21:03

I am very grateful for your help and for being patient with me. I must say this worked and there is no way I could have figured this out without you
Pbottcher. This worked very well. I have made the item required. Thank you for all your help!!!

jnewtcomp
Posts: 19
Joined: 2019-07-25 08:05

Re: Calculation Hook for reducing balance automatically

Post by jnewtcomp » 2019-09-17 17:26

Hello,

I need help with an additional calculation.. I have a table added called Purchase_Order that needs to update the stock_qty when adding Qty_Received. please help, I have tried modifying the script above and for some reason it does not work.

<?php
//

function Purchase_Order_init(&$options, $memberInfo, &$args){

return TRUE;
}

function Purchase_Order_header($contentType, $memberInfo, &$args){
$header='';

switch($contentType){
case 'tableview':
$header='';
break;

case 'detailview':
$header='';
break;

case 'tableview+detailview':
$header='';
break;

case 'print-tableview':
$header='';
break;

case 'print-detailview':
$header='';
break;

case 'filters':
$header='';
break;
}

return $header;
}

function Purchase_Order_footer($contentType, $memberInfo, &$args){
$footer='';

switch($contentType){
case 'tableview':
$footer='';
break;

case 'detailview':
$footer='';
break;

case 'tableview+detailview':
$footer='';
break;

case 'print-tableview':
$footer='';
break;

case 'print-detailview':
$footer='';
break;

case 'filters':
$footer='';
break;
}

return $footer;
}

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

return TRUE;
}

function Purchase_Order_after_insert($data, $memberInfo, &$args){
$new_qty=sqlvalue("SELECT IFNULL(i.Stock_QTY,0)-{$data['Qty_Issued']} from Inventory i where i.InventoryID = {$data['Item']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.InventoryID ={$data['Item']}");
return TRUE;
}


function Purchase_Order_before_update(&$data, $memberInfo, &$args){
$old_qty=sqlvalue("SELECT IFNULL(Qty_Received,0) from Purchase_Order where Purchase_OrderID={$data['selectedID']}");
$new_qty=sqlvalue("SELECT IFNULL(i.Stock_QTY+{$old_qty}-{$data['Qty_Received']},0) from Inventory i where i.InventoryID = {$data['Item']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.InventoryID ={$data['Item']}");

return TRUE;
}


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

return TRUE;
}

function Purchase_Order_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){

return TRUE;
}

function Purchase_Order_after_delete($selectedID, $memberInfo, &$args){

}

function Purchase_Order_dv($selectedID, $memberInfo, &$html, &$args){

}

function Purchase_Order_csv($query, $memberInfo, &$args){

return $query;
}
function Purchase_Order_batch_actions(&$args){

return array();
}

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

Re: Calculation Hook for reducing balance automatically

Post by pböttcher » 2019-09-18 19:20

Hi,

what is the relation key between the Purchase_Order and the Inventory table?

jnewtcomp
Posts: 19
Joined: 2019-07-25 08:05

Re: Calculation Hook for reducing balance automatically

Post by jnewtcomp » 2019-09-19 16:50

Purchase order is linked to the inventory. I open inventory and when I am below minimum QTY I create a purchase order that is linked to inventory. The purchase order uses Quoted, Ordered, and Closed for status. When Closed a QTY_Received is updated to the total qty received from vendor that should Update the Inventory Stock_QTY

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

Re: Calculation Hook for reducing balance automatically

Post by pböttcher » 2019-09-19 18:19

Hi,
thanks for the explanation, but what field in the purchase order is linked to which field in the inventory?

jnewtcomp
Posts: 19
Joined: 2019-07-25 08:05

Re: Calculation Hook for reducing balance automatically

Post by jnewtcomp » 2019-09-19 19:50

Part_Number under purchase_order is linked to MFG_Part_Number under Inventory.
I have placed a copy of here in zip format. The file is from a secure server with 3 layers of antivirus thus its clean.

thank you so much for your help!!!
Attachments
Toolcrib.zip
(11.15 KiB) Downloaded 3 times

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

Re: Calculation Hook for reducing balance automatically

Post by pböttcher » 2019-09-20 07:51

Hi,

try

Code: Select all

function Purchase_Order_after_insert($data, $memberInfo, &$args){
$new_qty=sqlvalue("SELECT IFNULL(i.Stock_QTY,0)-{$data['Qty_Issued']} from Inventory i where i.MFG_Part_Number= {$data['Part_Number']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.MFG_Part_Number ={$data['Part_Number']}");
return TRUE;
}


function Purchase_Order_before_update(&$data, $memberInfo, &$args){
$old_qty=sqlvalue("SELECT IFNULL(Qty_Received,0) from Purchase_Order where Purchase_OrderID={$data['selectedID']}");
$new_qty=sqlvalue("SELECT IFNULL(i.Stock_QTY+{$old_qty}-{$data['Qty_Received']},0) from Inventory i where i.MFG_Part_Number= {$data['Part_Number']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.MFG_Part_Number={$data['Part_Number']}");

return TRUE;
}

jnewtcomp
Posts: 19
Joined: 2019-07-25 08:05

Re: Calculation Hook for reducing balance automatically

Post by jnewtcomp » 2019-09-20 13:05

I have tried this and it does not change the stock qty. I tried playing with the format and alignment.

Thank you again for helping.

jnewtcomp
Posts: 19
Joined: 2019-07-25 08:05

Re: Calculation Hook for reducing balance automatically

Post by jnewtcomp » 2019-09-21 14:19

Pascal !

That last code worked perfectly! Thank you very much.

Post Reply