Process script in batch by order number to get accurate updates

[thenorman138]

I have a script that 'works' but I had an oversight in logic and I'm trying to rectify it.

 

Basically, the below script selects orders from a db2 database, and every item on each order. What I'm doing is adding date intervals to the products for a time period that we believe that product to be on shelves. A customer should only have a number of records for any individual prodcut that's equal to number of stores they have, within the expiration window.

 

An example: If I'm customer 123 and I have 5 stores. Today I placed an order for skus 1,2, and 3 and I bought 2 of each. That's 2 records per product. Tomorrow I order 6 more of each product; that should add 3 records per product, and then overwrite the oldest records for those products 3 times and update the expiration date to one additional day. So within 2 days I placed multiple orders for the same 3 products, but the piece count was more than my number of stores so that's where the update comes in. At the end of the day, for any individual sku within an expiration window I should never have more records for each sku than number of locations.

 

Anyway, this works in terms of inserting, and it works for updates IF the record already exists when I do the select. But my problem is this: If the select has an order from yesterday and today, both of which containing items that should be updated, it's only going to select them all and insert because at the time of selecting, they didn't exist.

 

My solution to this in my mind is to break this up and process the script in a batch by order number, so that way I'm only inserting records for one order number, then going back and selecting the next order and doing it again. This way, every order number has the opportunity to be selected so that I can accurately determine if it needs to be updated or inserted. I feel like this should be fairly simple but I just don't know the best way to go about it.

 

Basically, I would select my order numbers, check my destination table for the items, then insert/update, then do it all again for the next order number, rather than selecting an array of order numbers and just working from that which could result in too many records being inserted rather than updated.

 

I can clarify if needed, but any help is much appreciated.

 

<?php


//Select orders from DB2 side
$detailStatCheck = "
        SELECT 
             inv as INVOICE,
             stat as STATUS,
             cust AS CUSTOMER,
             frm AS BODY,
             cov AS MTRL,
             col AS ATTR ,
             shpdte AS SHIPPED,
             qty AS QUANTITY,
             p.stores as STORES
        FROM goods g
        inner join plant p on g.cust = p.cstno
        WHERE to_date(char(shpdte), 'YYYYMMDD') >= '2018-01-01'
    ";


    //prepare and execute to select orders
    try {
        $detailCheck = $DB2conn->prepare($detailStatCheck);
        $detailRslt = $detailCheck->execute();
        $count2 = $detailCheck->fetch();
        print_r($count2);
    } catch(PDOException $ex) {
        echo "QUERY FAILED!: " .$ex->getMessage();
    }




    //Create prepared INSERT statement
    $insertPlacement = "
        INSERT ignore INTO placedOrders (sku, category, CUSTOMER_id, start_date, expire_date, locations, order_num)
        SELECT 
            id, 
            category, 
            :CUSTOMER, 
            DATE_ADD(:SHIPPED),INTERVAL 7 DAY) as start_date,
            DATE_ADD(DATE_FORMAT(CONVERT(:SHIPPED, CHAR(20)), '%Y-%m-%d'),INTERVAL 127 DAY) as expire_date, 
            :STORES,
            :INVOICE  
        FROM sku s  
        WHERE  s.BODY=:BODY AND s.MTRL1=:MTRL AND s.ATTR1=:ATTR
    ";


    //check for existing records that are expired, which would trigger insert
    $expiredCheck = "
SELECT 
sku,
CUSTOMER_id,
expire_date
FROM placedOrders p
INNER JOIN sku s
ON p.sku = s.id
WHERE p.CUSTOMER_id = :CUSTOMER
AND   s.BODY = :BODY
AND   s.MTRL1 = :MTRL
AND   s.ATTR1 = :ATTR
AND   p.expire_date <= current_date()
";


//check for existing records that are not expired, triggering update
    $validCheck = "
SELECT 
sku,
CUSTOMER_id,
expire_date
FROM placedOrders p
INNER JOIN sku s
ON p.sku = s.id
WHERE p.CUSTOMER_id = :CUSTOMER
AND   s.BODY = :BODY
AND   s.MTRL1 = :MTRL
AND   s.ATTR1 = :ATTR
AND   p.expire_date > current_date()
";


//prepare all update and insert queries
$checkExisting = $MysqlConn->prepare($expiredCheck);
$checkExistingValid = $MysqlConn->prepare($validCheck);
$insert = $MysqlConn->prepare($insertPlacement);
$update = $MysqlConn->prepare($updatePlacement);


//while we have results from the order selection
    while ($row2 = $detailCheck->fetch(PDO::FETCH_ASSOC)) {


        $executionValues = [
            ":CUSTOMER" => $row2["CUSTOMER"],
            ":SHIPPED" => $row2["SHIPPED"],
            ":STORES" => $row2["STORES"],
            ":QUANTITY" => $row2["QUANTITY"],
            ":INVOICE" => $row2["INVOICE"],
            ":BODY" => $row2["BODY"],
            ":MTRL" => $row2["MTRL"],
            ":ATTR" => $row2["ATTR"],
        ];


        $checkValues = [
         ":CUSTOMER" => $row2["CUSTOMER"],
         ":BODY" => $row2["BODY"],
         ":MTRL" => $row2["MTRL"],
         ":ATTR" => $row2["ATTR"],
        ];


        try{
         //Array will contain records that are expired
     $existingRslt = $checkExisting->execute($checkValues);
     $count3 = $checkExisting->fetch(PDO::FETCH_ASSOC);


     //Array will contain records that are valid
     $existingVldRslt = $checkExistingValid->execute($checkValues);
     $count4 = $checkExistingValid->fetch(PDO::FETCH_ASSOC); 
     }catch(PDOException $ex){
                echo "QUERY FAILED!!!: " . $ex->getMessage();
        }


        // IF records do not exist, or records exist and today is after expiration date
        if(empty($count3) && empty($count4)){
         print_r("Inserting");
        for($i=0; $i<$row2["STORES"]; $i++) { 
            try{
                $insertRslt = $insert->execute($executionValues);
            }catch(PDOException $ex){
                echo "QUERY FAILED!!!: " . $ex->getMessage();
            }


        }
    //IF records do exist but are expired, insert new
    }elseif(!empty($count3)){
     print_r("Inserting");
     for($i=0; $i<$row2['STORES']; $i++){
     try{
                    $insertRslt = $insert->execute($executionValues);
                }catch(PDOException $ex){
                    echo "QUERY FAILED!!!: " . $ex->getMessage();
                }
     }
    //IF records exist and are not expired
    }elseif(!empty($count4)){
     print_r("updating");
            for($i=0; $i<$row2['STORES']; $i++){
                try{
                    $updateRslt = $update->execute($executionValues);
                }catch(PDOException $ex){
                    echo "QUERY FAILED!!!: " . $ex->getMessage();
                }
            }
        }
     }    


?>

 

[requinix]

So after reading this a few times over the last couple days, I think I finally understand it:

 

You're pulling records from a sort of pending table that contains the information you need to process, with each one covering a customer and product. The customer is limited to a number of (unexpired) product rows according to the number of stores they have, so basically the customer is allowed only one (unexpired) product per store.

When processing the records, you (a) insert products for the customer up to the limit, if they haven't already reached that limit, then (b) the remaining records can each prolong expiration of a product by one more day, starting with the oldest/soonest to expire.

 

Using your example but only considering one product (each product works the same way so throwing more than one into the example just complicates it),

1. The customer has 2 to start and a limit of 5.

2. The customer orders 6 more. 3 can be inserted to hit the limit, with 3 more left to process.

3. Those 3 extend the expiration of the others, starting with the oldest 2 and then extending 1 of the newly-added records.

The end result is five products, #1 and #2 as they were first created but with an extra day, #3 newly added with an expiration one day later than normal, and #4 and #5 newly added with the normal expiration.

 

Right?

 

By the way, where is the update query?