phpengine/src/Modules/Dimatec/MigrationData.php

<?php
/**
 * Created by PhpStorm.
 * User: Giancarlo
 * Date: 19/09/2018
 * Time: 12:10
 */

namespace Modules\Dimatec;

use DB\SQL;

// DB2 Connection
define('DB2DSN', "odbc:Driver={iSeries Access ODBC Driver};DefaultLibraries=SMEDAT_DIM;SYSTEM=");
define('DB2UID', 'icam');
define('DB2PWD', 'icam');

// MAV DB Connection
define('MAVDBDSN', "sqlsrv:Database=icamdbstore;Encrypt=false;TrustServerCertificate=false;Server=");
define('MAVDBUID', 'sa');
define('MAVDBPWD', 'sa');

class MigrationData
{

  const STATUS_NEW = 0;
  CONST STATUS_PENDING = 50;
  CONST STATUS_DONE = 80;

  /** @var SQL $dbHandle DB2 */
  private $dbHandle;

  /** @var SQL $dbHandle MSSQL Server */
  private $dbMAVHandle;

  /** @var \SoapClient $soapClient MAV client */
  private $soapClient;

  // SOAPACTION
  private $soapAction = 'http://servizi_xchange.org/ImportLists';

  // Internal variables
  private $finalXml;
  private $listXml;

  // The ids of current articles to move from DB2 to MAV
  private $db2RowsId = [];

  public function ClearCachedIDs()
  {
    global $System;
    $System->getCache()->clear('db2RowsId');
  }

  /**
   * Create the connection client to a SOAP server
   * @param string $serverAddress IP Address
   * @throws \Exception
   */
  public function SoapConnector(string $serverAddress)
  {
    global $frameworkUtils;
    if ($frameworkUtils->isServerAlive($serverAddress, 80, 2) > 0)
      throw new \Exception('Magazzino Verticale non raggiungibile');

    $this->soapClient = new \SoapClient("http://$serverAddress/servizi_xchange.asmx?wsdl", array(
      'trace' => true,
      'exceptions' => true,
      'cache_wsdl' => false,
    ));
  }

  /**
   * Get all new missions from DB2 without status 0
   * @param string $operationType
   */
  public function selectAvailableMissions(string $operationType)
  {
    $filter = $this->fixOperationTypeValue($operationType);

    $this->db2RowsId = array_column($this->query("SELECT MXIDDM FROM SMEDAT_DIM.INTMAV0F WHERE $filter AND MXSTAT=0"), 'MXIDDM');
  }

  /**
   * Set mission status on AS400
   * @param $listaId
   */
  public function InitializeMissionsToPending($listaId)
  {
    $ids = join(", ", $this->db2RowsId);
    $this->query("UPDATE SMEDAT_DIM.INTMAV0F SET MXSTAT=" . MigrationData::STATUS_PENDING . ", MXNOLS='$listaId' WHERE MXIDDM IN ($ids)");
  }

  /**
   * Set all missions in STATUS_PENDING status as STATUS_DONE
   */
  public function RollbackPendingMissionsToNew()
  {
    // Stranamente se non facci oquesto count pdostatement genera un server error 500 intrappabile!!
    $pendingCount = count($this->query('SELECT * FROM SMEDAT_DIM.INTMAV0F WHERE MXSTAT=' . MigrationData::STATUS_PENDING));
    if ($pendingCount > 0) {
      $this->query(
        "UPDATE SMEDAT_DIM.INTMAV0F SET MXSTAT=" . MigrationData::STATUS_NEW . " WHERE MXSTAT=" . MigrationData::STATUS_PENDING
      );
    }
  }

  /**
   * @param string $labelId
   * @param string $articleCode
   * @param string $commessa
   */
  public function MarkMissionDone(string $labelId, string $articleCode, string $commessa)
  {
    $this->query(
      "UPDATE SMEDAT_DIM.INTMAV0F SET MXSTAT=" . MigrationData::STATUS_DONE . " WHERE MXNCON='$labelId' AND MXCORD='$commessa' AND MXARTI='$articleCode' AND MXSTAT=" . MigrationData::STATUS_PENDING
    );
  }

  /**
   * Set barcode on all missions cached by 'selectAvailableMissions'
   * @param string $labelId
   * @param string $commessa filter
   */
  public function SetMissionsBarcodes(string $labelId, string $commessa)
  {
    $this->query("UPDATE SMEDAT_DIM.INTMAV0F SET MXNCON='$labelId' WHERE MXCORD='$commessa'");
  }

  /**
   * MAV DB constructor.
   */
  public function MAVDatabaseConnector()
  {
    global $f3;
    $this->dbMAVHandle = new SQL(
      MAVDBDSN . $f3->get('VERTICALSTORAGE.MV_ADDRESS'),
      MAVDBUID, MAVDBPWD,
      array(\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION));
  }

  /**
   * PDO constructor.
   */
  public function DB2Connector()
  {
    global $f3;
    $this->dbHandle = new SQL(
      DB2DSN . $f3->get('VERTICALSTORAGE.DB2_ADDRESS'),
      'icam', 'icam',
      array(\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION));
  }

  /**
   * @param $sql
   * @return array
   */
  public function query($sql)
  {
    return $this->dbHandle->exec($sql);
  }

  /**
   * @param $result
   * @return int row count
   */
  public function toCSV($result): int
  {
    $count = 0;
    foreach($result as $key => $value) {
      $arr = [];
      array_map(function ($v) use (&$arr) {
        $arr[] = str_pad(ltrim(rtrim($v)), 5, ' ');
      }, $value);

      echo join('|', $arr);
      echo PHP_EOL;
      $count++;
    }
    return $count;
  }

  private function soapAddHead(string $extCode, string $operation, string $name, string $description)
  {
    $this->listXml .= <<<EOF
        <ser:ListHead>
          <ser:external_list_code>$extCode</ser:external_list_code>
          <ser:operation_type>VISUALIZZAZIONE</ser:operation_type>
          <ser:operation>$operation</ser:operation>
          <ser:name>$name</ser:name>
          <ser:description>$description</ser:description>
          <ser:user_id>sa</ser:user_id>
        </ser:ListHead>

EOF;
  }

  private function soapAddArticle(string $id, int $quantity, $description, $color)
  {
    $this->listXml .= <<<EOF
        <ser:ListDetail>
          <ser:item_id>$id</ser:item_id>
          <ser:quantity>$quantity</ser:quantity>
          <ser:description>$description</ser:description>
          <ser:color>$color</ser:color>
        </ser:ListDetail>
EOF;
  }

  /**
   * Send to as400 a list of all articles contained into MAV
   * @param $data
   * @return bool
   */
  public function copyAllToAS400($data): bool
  {
    $this->dbHandle->exec("TRUNCATE TABLE SMEDAT_DIM.INTMUB0F");
    foreach($data as $key => $value) {
      $this->dbHandle->exec("INSERT INTO SMEDAT_DIM.INTMUB0F (UXSTAT, UXCOAP, UXQTGC, UXQTMA) VALUES (10, '${value['UXCOAP']}', ${value['UXQTGC']}, ${value['UXQTMA']})");
    }
  }

  /**
   *  Per comodità questa funzione serve per interpretare meglio il campo MXTIPM, indica solamente il valore 'C' come Carico, e scarico stringa vuota o spazio
   *
   * @param string $operation
   * @return string
   */
  private function fixOperationTypeValue(string $operation)
  {
    return ($operation == 'CARICO') ? "MXTIPM == 'C'" : "MXTIPM <> 'C'";
  }

  /**
   * Ritorna la query di massificazione (raggruppamento delle quantità per codice articolo e prebolla) dalla tabella INTMAVOF
   *
   * @param string $operationType values: 'CARICO' or 'SCARICO'
   * @return string
   */
  public function strQueryMassificata(string $operationType): string
  {
    $filter = $this->fixOperationTypeValue($operationType);

    $status = MigrationData::STATUS_NEW;
    return <<<EOF
SELECT MXCORD, MXARTI, MXDESC, MXRIFC, MXBCOD, SUM(MXQTMI) AS QTY
FROM SMEDAT_DIM.INTMAV0F
WHERE $filter AND MXSTAT=$status
GROUP BY MXARTI, MXCORD, MXDESC, MXRIFC, MXBCOD
ORDER BY MXCORD, MXARTI
EOF;
  }

  /**
   * Ritorna la query di massificazione (raggruppamento delle quantità per codice articolo ignorando la prebolla) dalla tabella INTMAVOF
   *
   * @param string $operationType values: 'CARICO' or 'SCARICO'
   * @return string
   */
  public function strQueryPerMAV(string $operationType): string
  {
    $filter = $this->fixOperationTypeValue($operationType);

    $status = MigrationData::STATUS_NEW;
    return <<<EOF
SELECT MXARTI, MXDESC, SUM(MXQTMI) AS QTY
FROM (SELECT * FROM SMEDAT_DIM.INTMAV0F ORDER BY MXCORD) AS tbl
WHERE $filter AND MXSTAT={$status}
GROUP BY MXARTI, MXDESC
EOF;
  }

  /**
   * Query da eseguire sul database di OM-Link, ottiene una lista massificata per quantità articolo, UDS e ordinata per esso
   *
   * @return string
   */
  public function strQueryMassificazioneByUDS(): string
  {
    return <<<EOF
SELECT `uds`, `barcode`, `descrizione`, SUM(`qta_prelevare`) AS `qta`
FROM `distinte`
WHERE `deleted`=0
GROUP BY `barcode`
ORDER BY `uds`
EOF;
  }

  /**
   * Compile xml soap from $data
   *
   * @param string $operationType values: 'CARICO' or 'SCARICO'
   * @param iterable $data
   * @return string return id list
   */
  public function soapCompileFrom(iterable $data, string $operationType): string
  {
    $idList = 'ID-' . uniqid();
    $dateTime = date("d/m/Y H:i:s");
    $operationType = $operationType == 'CARICO' ? 'CARICO' : 'SCARICO';

    foreach($data as $row) {

      // List Head
      $this->soapAddHead($idList, $operationType, "Lista di $operationType da as400 del $dateTime", '');
      $descArti = trim($row['descrizione']); // trim($row['commessa']) . ' ' . trim(substr($row['desccliente'], 7)) . ' ' . trim($row['descrizione']);

      // List Article
      $this->soapAddArticle($row['barcode'], $row['qta_prelevare'], $descArti, $row['color']);
    }

    $this->finalXml = <<<EOF
<?xml version="1.0" encoding="utf-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://servizi_xchange.org/">
  <soapenv:Header/>
  <soapenv:Body>
    <ser:ImportLists>
      <ser:InputLists>
        <ser:ListToImport>
$this->listXml
        </ser:ListToImport>
      </ser:InputLists>
      <ser:Output_Type>1</ser:Output_Type>
    </ser:ImportLists>
  </soapenv:Body>
</soapenv:Envelope>
EOF;

    return $idList;
  }

  /**
   * Send xml to SoapService of ICAM
   * @param $xmlSoap
   * @return bool true if successful
   */
  public function sendToIcam(): bool
  {
    global $f3;

    $ws_url = 'http://' . $f3->get('VERTICALSTORAGE.MV_ADDRESS') . '/servizi_xchange.asmx';

    $ret = $this->soapClient->__doRequest($this->finalXml, $ws_url, $this->soapAction, SOAP_1_1);

    return boolval($ret);
  }

  /**
   * Prepara la distinta da importare sul database di OM-Link
   *
   * @param $dataRows
   * @param array $uds
   * @return array
   */
  public function prepareDistinta($dataRows, array $uds)
  {
    global $f3;

    $colors = range(1, 10);

    $ret = [];

    $idUtente = $f3->get('USER')->id;

    $cols = array_column($uds, 'item_id');

    foreach($dataRows as $count => $data) {
      $color = array_shift($colors);
      array_push($colors, $color);
      $container_id = '';
      $colId = array_search(trim($data['MXARTI']), $cols);
      if ($colId !== false)
        $container_id = $uds[$colId]['container_id'];

      $ret[] = [
        'idUtente' => $idUtente,
        'origine' => $data['MXUBIC'],
        'consegna' => $data['MXIDDM'],
        'commessa' => trim($data['MXCORD']),  // Codice prebolla
        'desccliente' => trim(substr($data['MXRIFC'], 7)),
        'barcode' => trim($data['MXARTI']),
        'codcliente' => trim($data['MXBCOD']),
        'descrizione' => trim($data['MXDESC']),
        'qta_prelevare' => $data['QTY'],
        'qta_mancante' => 0,
        'color' => $color,
        'uds' => $container_id // posizione UDS
      ];
    }
    // Sort menus buy 'order' option
    usort($ret, function ($obj1, $obj2) {
      return $obj1['uds'] <=> $obj2['uds'];
    });
    return $ret;
  }

  /**
   * Importa la distinta sul database di OM-Link
   *
   * @param array $data
   */
  public function saveDistinta(array $data)
  {
    global $f3;

    $distinta = new SQL\Mapper($f3->get('DB'), 'distinte');
    foreach($data as $row) {
      $distinta->reset();
      $distinta->copyfrom($row);
      $distinta->save();
    }
  }

  /**
   * Get all UDS positions of MAV articles
   * @return array
   */
  public function getUDSs(): array
  {
    return $this->dbMAVHandle->exec('SELECT item_id, container_id, subcontainer_id from dbo.vw_item_positions');
  }

  /**
   * Transfer product list from MAV to as400
   *
   * @return int count
   */
  public function transferFromMAV_as400(): int
  {
    $this->dbHandle->query("DELETE FROM SMEDAT_DIM.INTMUB0F");
    $mavArticles = $this->dbMAVHandle->query('SELECT item_id AS UXCOAP, max_load_unit AS UXQTMA, position_quantity AS UXQTGC, container_id AS UXLOCN FROM vw_item_positions');
    $count = 0;
    foreach($mavArticles as $key => $value) {
      $success = $this->dbHandle->query(
        "INSERT INTO SMEDAT_DIM.INTMUB0F (UXSTAT, UXCOAP, UXQTGC, UXQTMA, UXLOCN) VALUES (10, '${value['UXCOAP']}', ${value['UXQTGC']}, ${value['UXQTMA']}, '${value['UXLOCN']}')"
      );
      if ($success === FALSE) {
        return -1;
      }
      $count++;
    }
    return $count;
  }

}