Help with php stock level updating

[YESMUN]

Hi guys

 

I am using appgini to setup a simply inventory system, it is working great except for a few small problems, the biggest being the way that the inventory works.

 

Currently there are only two options, in and out, in transactions increase stock and out decrease stock. I need a third option that does not adjust stock. This is so that I can transfer stock between warehouses without reducing the overall stock level.

 

here is the code for the page that runs the stock functions:

 

<?php


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


return TRUE;
}


function transactions_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 transactions_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 transactions_before_insert(&$data, $memberInfo, &$args){


return TRUE;
}


function transactions_after_insert($data, $memberInfo, &$args){
update_balances($data);
update_balances($transfer);
return TRUE;
}


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


return TRUE;
}


function transactions_after_update($data, $memberInfo, &$args){
update_balances($data);
return TRUE;
}


function transactions_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){
$selectedID = makeSafe($selectedID);
$res = sql("select * from transactions where id='{$selectedID}'", $eo);
$GLOBALS['deleted_data'] = db_fetch_assoc($res);
return TRUE;
}


function transactions_after_delete($selectedID, $memberInfo, &$args){
update_balances($GLOBALS['deleted_data']);
}


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


}


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


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


return array();
}


function update_balances($data){
/*
  retrieve the ID of the item whose balance is to be updated,
  and make it safe for use in SQL
*/
$item = makeSafe($data['item']);


/*
  Comma-separated list of all transaction types (each enclosed in single quotes) that add 
  to inventory -- others are assumed to subtract from inventory
*/
$positive_transactions = "'Incoming', 'Transfer In', 'Customer Returns'";
/*


/* get sum of all item transactions and update item balance */
if($item){
/*
  positive (incoming) transactions are added to the balance,
  others (outgoing) are subtracted from the balance
*/
$item_balance = sqlValue("select
sum(if(transaction_type in ({$positive_transactions}), quantity, -1 * quantity))
from transactions where item={$item}
");


/* update the item balance with the value calculated above */
sql("update items set balance='{$item_balance}' where id={$item}", $eo);
}
}

 

The section that seems to increase and decrease the stock is:

 

$item_balance = sqlValue("select
sum(if(transaction_type in ({$positive_transactions}), quantity, -1 * quantity))
from transactions where item={$item}
");

 

Does anyone know how I could add another option in there that would not adjust the stock level at all?

 

 

Thank you very much for any assistance you can provide.

 

[Barand]

A transfer would be a transaction involving an OUT from warehaouse A and an IN to warehouse B (same quantity in each part)

[YESMUN]

Yes it currently takes it out of warehouse A and puts it in Warehouse B, but I need an option that will not take it out of warehouse A, but will put it in to warehouse B.

 

for example:

 

Internal transfer from warehouse A to Warehouse B does not affect stock level.

 

Outgoing transfer from warehouse B will reduce the stock level.

 

Internal transfer from warehouse B to warehouse A will not affect stock level.

 

so in that code there is $positive_transactions = "'Incoming', 'Transfer In', 'Çustomer Returns'" - all of these options increase the stock level. All other options reduce stock level.

 

I need to have another option like $neutral_transactions = "'Internal Transfer'" - and then on this one I would like the stock levels not to be adjusted.

 

Does that make sense?

[Barand]

Sorry, but transferring from A to B but then having it in both locations at the same time involves a level of quantum mathematics that is way above my comprehension.

[mac_gyver]

the update_balances() code ONLY sums the transaction table quantity and updates the items table balance column. this code is both unnecessary (you should just sum the transaction quantity when you need it) and isn't where the problem is at, assuming you are using 'Transfer In' for the transaction type for the transfer to a warehouse.

 

if you are executing code that inserts a 'Transfer Out' transaction for warehouse A and inserts a 'Transfer In' transaction for warehouse B and the items balance does not end up being the same as the starting value, then there's something wrong with the insert transactions, such as a spelling mistake in the transaction type or both transactions being 'Transfer Out' (not 'Transfer In'.)

 

i see that one of your transaction types contains at least one character with a special character encoding. you should have a separate table with the transaction type names, that assigns a transaction type id (auto-increment integer column) to each one. the transaction type id should be stored in the transaction table, not the transaction type name. this will reduce the data storage requirements, speed up queries, make it easier to validate input, and eliminate the possibility of different character encoding causing values to match or not match.

[YESMUN]

Thank you very much for the info mac_gyver, I will look at modifying the way it works in the future, for this immediate project though I just need to have the option to not remove it from stock, even if it does not increase the stock somewhere else, but I need an internal transfer to not reduce the overall stock level at all.

 

Here is a screenshot of the entry form which might help, under transaction type you will see "Incoming, Transfer In, Customer Returns", Those 3 should increase the stock level as per the function which it does correctly, then there is "Outgoing, Trash", these two reduce the stock level, again with that function it is working perfectly, and lastly there is "Internal Transfer", now this last one should not affect the stock level at all but it currently reduces the stock level as it is not listed in the $positive_transactions = "'Incoming', 'Transfer In', 'Çustomer Returns'". If I list it in there then it increases the stock level, I need it to not alter the stock level at all.

 

Anyone know how this would be done based on that code?

 

here is the screenshot: 

[mac_gyver]

what is the 'Transfer In' choice used for?

 

to do this, there must be two records inserted into the transaction table. one record must account for the removal of the quantity from the 1st warehouse location and the second record must account for the addition of the quantity to the 2nd warehouse location.

 

you can currently do this using two form submissions, one with a 'Transfer Out' choice for the 1st warehouse location and one with a 'Transfer In' choice for the 2nd warehouse location.

 

if you want to use a single form submission, it will need to have a selection/option menu to pick the 2nd warehouse location and you will need to write program logic to detect the 'Transfer' choice and to cause the two records to be inserted into the transactions table. are your programming skills sufficient to do this?

[YESMUN]

No I am not yet at the level to create that.

 

Is it not possible to change that function so it operates more like

 

if transaction_type in {$positive_transactions} then +1, elseif transaction_type in {$neutral_transactions} do nothing else subtract 1?

 

 

$item_balance = sqlValue("select
sum(if(transaction_type in ({$positive_transactions}), quantity, -1 * quantity))
from transactions where item={$item}
");

[mac_gyver]

that function, as i already stated, isn't responsible for storing the data. all it does is produce the current balance and store (update) it in a different table. the problem isn't this redundant balance. the problem is storing the correct source data.

 

to correctly store the data, so that you have a record of what items are actually in each location, you must insert row(s) in the transactions table for each addition or subtraction to the quantity for any location. transferring something from one location to another involves subtracting it from the location where it was at and adding it to the location where it has been moved to. once you store the correct transaction row(s), the code you have posted that gets the current balance will work correctly as is.

 

since transferring things from one location to another is probably not used much, what is the problem with submitting the form twice, once to subtract the quantity from where the item is at, and a second time to add the quantity to where the item will be moved to? 

Edited April 27, 2018 by mac_gyver