Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?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;
- ?>
Add Comment
Please, Sign In to add comment