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: 27
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

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

Re: Calculation Hook for reducing balance automatically

Post by pbottcher » 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']}");
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.

jnewtcomp
Posts: 27
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!!!!!

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

Re: Calculation Hook for reducing balance automatically

Post by pbottcher » 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
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.

jnewtcomp
Posts: 27
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: 27
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: 27
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 9558 times

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

Re: Calculation Hook for reducing balance automatically

Post by pbottcher » 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.
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.

jnewtcomp
Posts: 27
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: 27
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();
}

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

Re: Calculation Hook for reducing balance automatically

Post by pbottcher » 2019-07-29 17:44

Hi,

what is the link (lookup) from the Consumable_Tools to the Inventory? Is it the item?
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.

jnewtcomp
Posts: 27
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.

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

Re: Calculation Hook for reducing balance automatically

Post by pbottcher » 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?
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.

jnewtcomp
Posts: 27
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: 27
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 382 times

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

Re: Calculation Hook for reducing balance automatically

Post by pbottcher » 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.
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.

jnewtcomp
Posts: 27
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: 27
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();
}

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

Re: Calculation Hook for reducing balance automatically

Post by pbottcher » 2019-09-18 19:20

Hi,

what is the relation key between the Purchase_Order and the Inventory table?
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.

jnewtcomp
Posts: 27
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

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

Re: Calculation Hook for reducing balance automatically

Post by pbottcher » 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?
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.

jnewtcomp
Posts: 27
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 312 times

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

Re: Calculation Hook for reducing balance automatically

Post by pbottcher » 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;
}
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.

jnewtcomp
Posts: 27
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: 27
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