<?php

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
date_default_timezone_set('America/New_York');

//Credentials
    $mysqlServer = "";
    $mysqlUser = "";
    $mysqlPass = "*";

    $username = "";
    $password = "";

//PDO COnnections
    $DB2conn = new PDO("odbc:DSN=;", $username, $password);;

        if($DB2conn){
            echo "DB2 connection successful";
        }else{
            echo "DB2 NOT CONNECTED!!!!!";
        }
    $DB2conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $MysqlConn = new PDO('mysql:host=;dbname=', $mysqlUser, $mysqlPass);

        if($MysqlConn){
            echo "MySQL  connection successful";
        }else{
            echo "MySQL NOT CONNECTED!!!!!";
        }

    $MysqlConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $MysqlConn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);



/*Delete records that were made placements more than 5 days ago*/
$deleteOld = '
        DELETE
        FROM Order_status
        WHERE date_updated < current_date() - INTERVAL 5 DAY';
try{
$delete = $MysqlConn->prepare($deleteOld);
$result = $delete->execute();
$count = $delete->rowcount();
echo "Records Deleted: " . $count . "n";
}
catch(PDOException $ex)
{
    echo "QUERY FAILED!: " .$ex->getMessage();
}

/*Placement process for orders already marked as Shipped*/
//PDO statement to select from order_status
$ordStatSql = 'SELECT order_id, order_status, is_placement, date_updated
                FROM order_status
                WHERE order_status = "S"
                AND date_updated IS NULL
                -- order by order_id asc
                LIMIT 800';
try{
$ordStat = $MysqlConn->prepare($ordStatSql);
$result = $ordStat->execute();
}
catch(PDOException $ex)
{
    echo "QUERY FAILED!: " .$ex->getMessage();
}

$order_ids = [];
while ($row = $ordStat->fetch(PDO::FETCH_ASSOC)) {
    $order_ids[] = $row['order_id'];
}

if (count($order_ids) > 0) {

    $placeholders = implode(',', array_fill(0, count($order_ids), '?'));
    $detailStatCheck = "
        SELECT
             invnoc as INVOICE,
             fstatc as STATUS,
             cstnoc AS DEALER,
             framec AS FRAME,
             covr1c AS COVER,
             colr1c AS COLOR ,
             extd2d AS SHIPDATE,
             orqtyc AS QUANTITY
        FROM ROGERT.GPORPCFL
        WHERE invnoc IN ($placeholders)
    ";

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

    //Create prepared INSERT statement
    $insertPlacement = "
        INSERT ignore INTO placements_new (sku_id, group_id, dealer_id, start_date, expire_date, locations, order_num)
        SELECT
            id,
            sku_group_id,
            :DEALER,
            DATE_ADD(DATE_FORMAT(CONVERT(:SHIPDATE, CHAR(20)), '%Y-%m-%d'),INTERVAL 7 DAY) as start_date,
            DATE_ADD(DATE_FORMAT(CONVERT(:SHIPDATE, CHAR(20)), '%Y-%m-%d'),INTERVAL 127 DAY) as expire_date,
            :QUANTITY,
            :INVOICE
        FROM skus s
        WHERE  s.frame=:FRAME AND s.cover1=:COVER AND s.color1=:COLOR
    ";

    //create update statement for necessary constraints
    $updatePlacement = "
        UPDATE placements_new
        SET expire_date = DATE_ADD(DATE_FORMAT(CONVERT(current_date(), CHAR(20)), '%Y-%m-%d'),INTERVAL 127 DAY)
    ";

    //perpare query to check for existing records that are expired
    $expiredCheck = "
        SELECT
            sku_id,
            dealer_id,
            expire_date
        FROM placements_new p
            INNER JOIN skus s
                ON p.sku_id = s.id
        WHERE p.dealer_id = :DEALER
            AND   s.frame = :FRAME
            AND   s.cover1 = :COVER
            AND   s.color1 = :COLOR
            AND   p.order_num = :INVOICE
            AND   p.expire_date <= current_date()
    ";

    //perpare query to check for existing records that are expired
    $validCheck = "
        SELECT
            sku_id,
            dealer_id,
            expire_date
        FROM placements_new p
            INNER JOIN skus s
                ON p.sku_id = s.id
        WHERE p.dealer_id = :DEALER
            AND   s.frame = :FRAME
            AND   s.cover1 = :COVER
            AND   s.color1 = :COLOR
            AND   p.order_num = :INVOICE
            AND   p.expire_date > current_date()
    ";

    $updateShipped = '
        UPDATE order_status S
        INNER JOIN placements_new N
        ON S.order_id = N.order_num
        set S.date_updated = current_date();
    ';


    while ($row2 = $detailCheck->fetch(PDO::FETCH_ASSOC)) {
        //print_r($count2);
        $values = [
            ":DEALER" => $row2["DEALER"],
            ":SHIPDATE" => $row2["SHIPDATE"],
            ":QUANTITY" => $row2["QUANTITY"],
            ":INVOICE" => $row2["INVOICE"],
            ":FRAME" => $row2["FRAME"],
            ":COVER" => $row2["COVER"],
            ":COLOR" => $row2["COLOR"],
        ];

        $values2 = [
            ":DEALER" => $row2["DEALER"],
            ":FRAME" => $row2["FRAME"],
            ":COVER" => $row2["COVER"],
            ":COLOR" => $row2["COLOR"],
            ":INVOICE" => $row2["INVOICE"],

        ];

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

            //Array will contain records that are valid
            $checkExistingValid = $MysqlConn->prepare($validCheck);
            $existingVldRslt = $checkExistingValid->execute($values2);
            $count4 = $checkExistingValid->fetch(PDO::FETCH_ASSOC);

            //print_r($count3);

        }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["QUANTITY"]; $i++) {
                    try{
                        $insert = $MysqlConn->prepare($insertPlacement);
                        $insertRslt = $insert->execute($values);
                    }catch(PDOException $ex){
                        echo "QUERY FAILED!!!: " . $ex->getMessage();
                    }

                }
            }elseif(!empty($count3)){
                print_r("Inserting");
                for($i=0; $i<$row2['QUANTITY']; $i++){
                    try{
                        $insert = $MysqlConn->prepare($insertPlacement);
                        $insertRslt = $insert->execute($values);
                    }catch(PDOException $ex){
                        echo "QUERY FAILED!!!: " . $ex->getMessage();
                    }
                }
            }elseif(!empty($count4)){
                print_r("updatin");
                for($i=0; $i<$row2['QUANTITY']; $i++){
                    try{
                        $update = $MysqlConn->prepare($updatePlacement);
                        $updateRslt = $update->execute($values);
                    }catch(PDOException $ex){
                        echo "QUERY FAILED!!!: " . $ex->getMessage();
                    }
                }
            }else{
                die("No action taken");
            }
         }


                try{
                $updateStatus = $MysqlConn->prepare($updateShipped);
                $statUpdateRslt = $updateStatus->execute();
                $count = $updateStatus->rowcount();
                }
                catch(PDOException $ex)
                {
                    echo "QUERY FAILED!: " .$ex->getMessage();
                }
            echo "Records Updated: " . $count . "n";


}

$DB2conn = null;
$MysqlConn = null;
?>