daily pastebin goal
3%
SHARE
TWEET

Untitled

a guest Mar 8th, 2018 51 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <?php
  2.  
  3. ini_set('display_errors', 1);
  4. ini_set('display_startup_errors', 1);
  5. error_reporting(E_ALL);
  6. date_default_timezone_set('America/New_York');
  7.  
  8. //Credentials
  9.     $mysqlServer = "";
  10.     $mysqlUser = "";
  11.     $mysqlPass = "*";
  12.  
  13.     $username = "";
  14.     $password = "";
  15.  
  16. //PDO COnnections
  17.     $DB2conn = new PDO("odbc:DSN=;", $username, $password);;
  18.  
  19.         if($DB2conn){
  20.             echo "DB2 connection successful";
  21.         }else{
  22.             echo "DB2 NOT CONNECTED!!!!!";
  23.         }
  24.     $DB2conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  25.  
  26.     $MysqlConn = new PDO('mysql:host=;dbname=', $mysqlUser, $mysqlPass);
  27.  
  28.         if($MysqlConn){
  29.             echo "MySQL  connection successful";
  30.         }else{
  31.             echo "MySQL NOT CONNECTED!!!!!";
  32.         }
  33.  
  34.     $MysqlConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  35.     $MysqlConn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
  36.  
  37.  
  38.  
  39. /*Delete records that were made placements more than 5 days ago*/
  40. $deleteOld = '
  41.         DELETE
  42.         FROM Order_status
  43.         WHERE date_updated < current_date() - INTERVAL 5 DAY';
  44. try{
  45. $delete = $MysqlConn->prepare($deleteOld);
  46. $result = $delete->execute();
  47. $count = $delete->rowcount();
  48. echo "Records Deleted: " . $count . "n";
  49. }
  50. catch(PDOException $ex)
  51. {
  52.     echo "QUERY FAILED!: " .$ex->getMessage();
  53. }
  54.  
  55. /*Placement process for orders already marked as Shipped*/
  56. //PDO statement to select from order_status
  57. $ordStatSql = 'SELECT order_id, order_status, is_placement, date_updated
  58.                 FROM order_status
  59.                 WHERE order_status = "S"
  60.                 AND date_updated IS NULL
  61.                 -- order by order_id asc
  62.                 LIMIT 800';
  63. try{
  64. $ordStat = $MysqlConn->prepare($ordStatSql);
  65. $result = $ordStat->execute();
  66. }
  67. catch(PDOException $ex)
  68. {
  69.     echo "QUERY FAILED!: " .$ex->getMessage();
  70. }
  71.  
  72. $order_ids = [];
  73. while ($row = $ordStat->fetch(PDO::FETCH_ASSOC)) {
  74.     $order_ids[] = $row['order_id'];
  75. }
  76.  
  77. if (count($order_ids) > 0) {
  78.  
  79.     $placeholders = implode(',', array_fill(0, count($order_ids), '?'));
  80.     $detailStatCheck = "
  81.         SELECT
  82.              invnoc as INVOICE,
  83.              fstatc as STATUS,
  84.              cstnoc AS DEALER,
  85.              framec AS FRAME,
  86.              covr1c AS COVER,
  87.              colr1c AS COLOR ,
  88.              extd2d AS SHIPDATE,
  89.              orqtyc AS QUANTITY
  90.         FROM ROGERT.GPORPCFL
  91.         WHERE invnoc IN ($placeholders)
  92.     ";
  93.  
  94.     try {
  95.         $detailCheck = $DB2conn->prepare($detailStatCheck);
  96.         $detailRslt = $detailCheck->execute($order_ids);
  97.         $count2 = $detailCheck->fetch();
  98.         print_r($order_ids);
  99.         print_r($count2);
  100.     } catch(PDOException $ex) {
  101.         echo "QUERY FAILED!: " .$ex->getMessage();
  102.     }
  103.  
  104.     //Create prepared INSERT statement
  105.     $insertPlacement = "
  106.         INSERT ignore INTO placements_new (sku_id, group_id, dealer_id, start_date, expire_date, locations, order_num)
  107.         SELECT
  108.             id,
  109.             sku_group_id,
  110.             :DEALER,
  111.             DATE_ADD(DATE_FORMAT(CONVERT(:SHIPDATE, CHAR(20)), '%Y-%m-%d'),INTERVAL 7 DAY) as start_date,
  112.             DATE_ADD(DATE_FORMAT(CONVERT(:SHIPDATE, CHAR(20)), '%Y-%m-%d'),INTERVAL 127 DAY) as expire_date,
  113.             :QUANTITY,
  114.             :INVOICE  
  115.         FROM skus s  
  116.         WHERE  s.frame=:FRAME AND s.cover1=:COVER AND s.color1=:COLOR
  117.     ";
  118.  
  119.     //create update statement for necessary constraints
  120.     $updatePlacement = "
  121.         UPDATE placements_new
  122.         SET expire_date = DATE_ADD(DATE_FORMAT(CONVERT(current_date(), CHAR(20)), '%Y-%m-%d'),INTERVAL 127 DAY)
  123.     ";
  124.  
  125.     //perpare query to check for existing records that are expired
  126.     $expiredCheck = "
  127.         SELECT
  128.             sku_id,
  129.             dealer_id,
  130.             expire_date
  131.         FROM placements_new p
  132.             INNER JOIN skus s
  133.                 ON p.sku_id = s.id
  134.         WHERE p.dealer_id = :DEALER
  135.             AND   s.frame = :FRAME
  136.             AND   s.cover1 = :COVER
  137.             AND   s.color1 = :COLOR
  138.             AND   p.order_num = :INVOICE
  139.             AND   p.expire_date <= current_date()
  140.     ";
  141.  
  142.     //perpare query to check for existing records that are expired
  143.     $validCheck = "
  144.         SELECT
  145.             sku_id,
  146.             dealer_id,
  147.             expire_date
  148.         FROM placements_new p
  149.             INNER JOIN skus s
  150.                 ON p.sku_id = s.id
  151.         WHERE p.dealer_id = :DEALER
  152.             AND   s.frame = :FRAME
  153.             AND   s.cover1 = :COVER
  154.             AND   s.color1 = :COLOR
  155.             AND   p.order_num = :INVOICE
  156.             AND   p.expire_date > current_date()
  157.     ";
  158.  
  159.     $updateShipped = '
  160.         UPDATE order_status S
  161.         INNER JOIN placements_new N
  162.         ON S.order_id = N.order_num
  163.         set S.date_updated = current_date();
  164.     ';
  165.  
  166.  
  167.     while ($row2 = $detailCheck->fetch(PDO::FETCH_ASSOC)) {
  168.         //print_r($count2);
  169.         $values = [
  170.             ":DEALER" => $row2["DEALER"],
  171.             ":SHIPDATE" => $row2["SHIPDATE"],
  172.             ":QUANTITY" => $row2["QUANTITY"],
  173.             ":INVOICE" => $row2["INVOICE"],
  174.             ":FRAME" => $row2["FRAME"],
  175.             ":COVER" => $row2["COVER"],
  176.             ":COLOR" => $row2["COLOR"],
  177.         ];
  178.  
  179.         $values2 = [
  180.             ":DEALER" => $row2["DEALER"],
  181.             ":FRAME" => $row2["FRAME"],
  182.             ":COVER" => $row2["COVER"],
  183.             ":COLOR" => $row2["COLOR"],
  184.             ":INVOICE" => $row2["INVOICE"],
  185.  
  186.         ];
  187.  
  188.         try{
  189.             //Array will contain records that are expired
  190.             $checkExisting = $MysqlConn->prepare($expiredCheck);
  191.             $existingRslt = $checkExisting->execute($values2);
  192.             $count3 = $checkExisting->fetch(PDO::FETCH_ASSOC);
  193.  
  194.             //Array will contain records that are valid
  195.             $checkExistingValid = $MysqlConn->prepare($validCheck);
  196.             $existingVldRslt = $checkExistingValid->execute($values2);
  197.             $count4 = $checkExistingValid->fetch(PDO::FETCH_ASSOC);
  198.  
  199.             //print_r($count3);
  200.  
  201.         }catch(PDOException $ex){
  202.                 echo "QUERY FAILED!!!: " . $ex->getMessage();
  203.         }
  204.  
  205.  
  206.  
  207.             // IF records do not exist, or records exist and today is after expiration date
  208.             if(empty($count3) && empty($count4)){
  209.                 print_r("Inserting");
  210.                 for($i=0; $i<$row2["QUANTITY"]; $i++) {  
  211.                     try{
  212.                         $insert = $MysqlConn->prepare($insertPlacement);
  213.                         $insertRslt = $insert->execute($values);
  214.                     }catch(PDOException $ex){
  215.                         echo "QUERY FAILED!!!: " . $ex->getMessage();
  216.                     }
  217.  
  218.                 }
  219.             }elseif(!empty($count3)){
  220.                 print_r("Inserting");
  221.                 for($i=0; $i<$row2['QUANTITY']; $i++){
  222.                     try{
  223.                         $insert = $MysqlConn->prepare($insertPlacement);
  224.                         $insertRslt = $insert->execute($values);
  225.                     }catch(PDOException $ex){
  226.                         echo "QUERY FAILED!!!: " . $ex->getMessage();
  227.                     }
  228.                 }
  229.             }elseif(!empty($count4)){
  230.                 print_r("updatin");
  231.                 for($i=0; $i<$row2['QUANTITY']; $i++){
  232.                     try{
  233.                         $update = $MysqlConn->prepare($updatePlacement);
  234.                         $updateRslt = $update->execute($values);
  235.                     }catch(PDOException $ex){
  236.                         echo "QUERY FAILED!!!: " . $ex->getMessage();
  237.                     }
  238.                 }
  239.             }else{
  240.                 die("No action taken");
  241.             }
  242.          }
  243.  
  244.  
  245.                 try{
  246.                 $updateStatus = $MysqlConn->prepare($updateShipped);
  247.                 $statUpdateRslt = $updateStatus->execute();
  248.                 $count = $updateStatus->rowcount();
  249.                 }
  250.                 catch(PDOException $ex)
  251.                 {
  252.                     echo "QUERY FAILED!: " .$ex->getMessage();
  253.                 }
  254.             echo "Records Updated: " . $count . "n";
  255.  
  256.  
  257. }
  258.  
  259. $DB2conn = null;
  260. $MysqlConn = null;
  261. ?>
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top