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: 14
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: 525
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: 14
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: 525
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: 14
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: 14
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: 14
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 212 times

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 525
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: 14
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: 14
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: 525
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: 14
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: 525
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: 14
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: 14
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 4 times

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 525
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: 14
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!!!

Post Reply