Conditional subtraction of arrays

[xkevin]

Hi everyone I am having a hard time on how to subtract the two arrays with the same value.

 

I have two arrays which hold the data of my products. Sample below.

$transfer_in array

 Array
    (
    [0] => Array
        (
            [product_id] => 2
            [product_qty] => 32
            [product_pcs] => 0
        )
    [1] => Array
        (
            [product_id] => 3
            [product_qty] => 353
            [product_pcs] => 2
        )
    [2] => Array
        (
            [product_id] => 5
            [product_qty] => 11
            [product_pcs] => 1
        )
)

$transfer_out array

Array
(
    [0] => Array
        (
            [product_id] => 5
            [product_qty] => 1
            [product_pcs] => 1
        )
)

 

Now I have to subtract the two arrays with specific product_id the product. If product_id from both arrays are the same/ exist then subtract it. $transfer_in['product_qty']-$transfer_out['product_qty'] and $transfer_in['product_pcs']-$transfer_out['product_pcs']

How can I subtract each product_qty and product_pcs with same product_id on both arrays and put them as one array?

This is the expected ouput:

$output = 
Array
    (
    [0] => Array
        (
            [product_id] => 2
            [product_qty] => 32
            [product_pcs] => 0
        )
    [1] => Array
        (
            [product_id] => 3
            [product_qty] => 353
            [product_pcs] => 2
        )
    [2] => Array
        (
            [product_id] => 5
            [product_qty] => 10
            [product_pcs] => 0
        )
)

 

 

Edited November 22, 2017 by xkevin

[Barand]

Can I ask where the array data comes from originally?

 

There may be the opportunity to redesign the structures so the id is the key, which will simplify processing. Or if from a database, remove the need for the array processing completely.

[xkevin]

Can I ask where the array data comes from originally?

 

There may be the opportunity to redesign the structures so the id is the key, which will simplify processing. Or if from a database, remove the need for the array processing completely.

 

Hi Sir Barand. The array data comes from the database. I am using Select query to get the sum of products (products in and products out). Then I used foreach loop to put them into the array. 

$transfer_in[] = array(
            "product_id" => $product_in['prod_id'],
            "product_qty" => $total_qty,
            "product_pcs" => $total_remaining_pcs
);

$transfer_out[] = array(
"product_id" => $product_in['prod_id'],
"product_qty" => $total_qty,
"product_pcs" => $total_remaining_pcs
);

[Barand]

What are the table structures and queries that you are using?

 

And your current code, instead of snippets?

[xkevin]

Here is my table structure:

 

log_id | log_prod_id | log_prod_qty | log_prod_pcs_qty | log_transaction_type | log_transaction_incharge | log_transaction_details | log_date | log_admin |

 

And here is my whole code. Getting all the sum of every item.

### in ###
    $sql = "SELECT *, SUM(log_prod_qty) as total_in_qty, SUM(log_prod_pcs_qty) as total_in_pcs_qty FROM transaction_logs INNER JOIN products ON(log_prod_id = prod_id) WHERE log_transaction_type = 'transfer-in' GROUP BY log_prod_id;";
    $products_in= db::select($sql);

    foreach ($products_in as $product_in){
        $total_qty = $product_in['total_in_qty'];
        $total_remaining_pcs = $product_in['total_in_pcs_qty'];
        $equivalent_qty = $product_in['prod_equivalent_to_qty_measure']; 

        $unit_measure_in_qty = $product_in['log_prod_unit']; 
        $unit_measure_in_pcs = $product_in['log_prod_pcs_unit']; 

        if($total_remaining_pcs > $equivalent_qty){
            $total_qty += floor($total_remaining_pcs / $equivalent_qty);  // add truncated integer to qty
            $total_remaining_pcs = $total_remaining_pcs % $equivalent_qty;  // store remainder after division
        }


        $transfer_in_products[] = array(
            "product_id" => $product_in['prod_id'],
            "product_name" => $product_in['prod_name'],
            "product_qty" => $total_qty,
            "product_pcs" => $total_remaining_pcs
        );

    }


### out ###
    $sql = "SELECT *, SUM(log_prod_qty) as total_in_qty, SUM(log_prod_pcs_qty) as total_in_pcs_qty FROM transaction_logs INNER JOIN products ON(log_prod_id = prod_id) WHERE log_transaction_type = 'transfer-out' GROUP BY log_prod_id;";
    $products_out= db::select($sql);

    $transfer_out_products = array();
    foreach ($products_out as $product_out){

        $total_qty = $product_out['total_in_qty'];
        $total_remaining_pcs = $product_out['total_in_pcs_qty'];
        $equivalent_qty = $product_out['prod_equivalent_to_qty_measure']; 

        $unit_measure_in_qty = $product_out['log_prod_unit']; 
        $unit_measure_in_pcs = $product_out['log_prod_pcs_unit']; 

        if($total_remaining_pcs > $equivalent_qty){
            $total_qty += floor($total_remaining_pcs / $equivalent_qty); 
            $total_remaining_pcs = $total_remaining_pcs % $equivalent_qty;  
        }

        $transfer_out_products[] = array(
            "product_id" => $product_out['prod_id'],
            "product_name" => $product_out['prod_name'],
            "product_qty" => $total_qty,
            "product_pcs" => $total_remaining_pcs
        );

    }

And now I have this $transfer_in_products and $transfer_out_products array separately. I need to deduct the product_out from product_in of every item (each product) to get all of the remaining items.

 

After doing that I need to add them to my stocks table.

 

stock_id | stock_prod_name | stock_prod_qty int | stock_prod_pcs_qty |stock_date

 

Hope this would help everyone to understand my situation.

Edited November 24, 2017 by xkevin

[Barand]

You can calculate those net totals and add to the stocks table with a single query, as I suggested, obviating the need for any array manipulation altogether.

INSERT INTO stocks (
    stock_product_name
   ,stock_prod_qty
   ,stock_prod_pcs_qty
   ,stock_date )
SELECT
    p.product_name
   ,FLOOR(SUM(CASE 
       WHEN log_transaction_type = 'transfer-out' THEN -log_prod_pcs_qty
       ELSE log_prod_pcs_qty
       END ) / p.prod_equivalent_to_qty_measure) 
       + 
       SUM(CASE 
       WHEN log_transaction_type = 'transfer-out' THEN -log_prod_qty
       ELSE log_prod_qty
       END ) 
   ,SUM(CASE 
       WHEN log_transaction_type = 'transfer-out' THEN -log_prod_pcs_qty
       ELSE log_prod_pcs_qty
       END ) % p.prod_equivalent_to_qty_measure
   ,CURDATE()
FROM 
    transaction_logs AS l
    INNER JOIN 
    products AS p
      ON(log_prod_id = prod_id)
GROUP BY log_prod_id

NOTES:

I would expect to see a product_id (and not name) in the stocks table.

 

As you said "add" to the stocks table I ave assumed an insert. Similarly, you could update it also with single query.

 

That stocks file is redundant as you can alwaysget te stock balnces at any time by just running the select portion of the above query.