phpengine/src/OpenMind/DataManager/CSVImporter.php

<?php
/**
 * Created by PhpStorm.
 * User: Giancarlo
 * Date: 24/07/2018
 * Time: 17:36
 */

namespace OpenMind\DataManager;

class ENUM_DIMATEC_CSV
{
  public const MXORDI = 7;
  public const MXARTI = 10;
  public const MXDESC = 12;
  public const MXQTMI = 14;
  public const MXUBIC = 16;
}


class CSVImporter
{
  const LAMBORGHINI_DISTINTA_COLONNE = 20;
  const LAMBORGHINI_ARTICLES_COLONNE = 15;
  const DIMATEC_LISTA_MOVIMENTI_AS400 = 32;

  private $folderToWatch;
  private $dbMysql;
  private $output;
  private $user;

  /**
   * @return mixed
   */
  public function getOutput()
  {
    return $this->output;
  }

  public function __construct(string $folderPath)
  {
    $this->user = (int)\F3::get('USER')->id;
    $this->folderToWatch = $folderPath;
    $this->dbMysql = \F3::get('DB');
  }

  /**
   * Create MySQL temp table for smart operations
   *
   * @param string $table
   * @return string temporary name
   */
  private function createTmpTableFrom(string $table)
  {
    $tempTableName = 'tmp' . rand(10, 200);
    $this->dbMysql->exec("CREATE TEMPORARY TABLE IF NOT EXISTS `{$tempTableName}` AS (SELECT * FROM `{$table}`)");
    return $tempTableName;
  }

  public function ScanAndStoreData()
  {
    foreach(glob("{$this->folderToWatch}/*.csv") as $fname) {
      if (is_file($fname)) {
        $allData = $this->loadCSV($fname);

        // Esce se il file non è una distinta, lo riconosce solamente dal numero di colonne del file CSV
        if ($allData == NULL)
          return;

        switch (count($allData[0])) {
          case CSVImporter::LAMBORGHINI_DISTINTA_COLONNE:
            $this->importOnDatabase($fname, $allData);
            break;
          case CSVImporter::LAMBORGHINI_ARTICLES_COLONNE:
            $this->mysqlLoadCSV(addslashes(realpath($fname)), 'articles');
            break;
          case CSVImporter::DIMATEC_LISTA_MOVIMENTI_AS400:
            //$tmpTableName = $this->createTmpTableFrom('distinte');
            $this->dynamicImporter($fname, $allData, function ($data) {
              if ($data[0] == "MXIDDM")
                return;
              try {
                $distinta = new \DB\SQL\Mapper($this->dbMysql, 'distinte');
                $distinta->idUtente = $this->user;
                $distinta->commessa = $data[ENUM_DIMATEC_CSV::MXORDI];
                $distinta->barcode = $data[ENUM_DIMATEC_CSV::MXARTI];
                $distinta->descrizione = $data[ENUM_DIMATEC_CSV::MXDESC];
                $distinta->qta_prelevare = $data[ENUM_DIMATEC_CSV::MXQTMI];
                $distinta->save();
              } catch (PDOException $e) {
                $this->output .= $e->getMessage();
              }
            });
            /*            $this->dbMysql->exec(
                          "INSERT INTO `distinta_esplosa` (commessa, barcode, descrizione, qta_prelevare) " .
                          "SELECT commessa, barcode, descrizione, SUM(qta_prelevare) as qta_prelevare " .
                          "FROM `distinte` group by barcode order by commessa"
                        );*/
            $this->markFileAsDone($fname);
            break;
          default:
            $this->output .= "Numero di colonne non riconosciute ne come DISTINTA e ne come LISTA ARTICOLI per il file '{$fname}'";
        }
      }
    }
  }

  private function loadCSV(string $path): ?array
  {
    $fh = fopen($path, "r");
    $csvData = [];
    while (($d = fgetcsv($fh, 0, ';'))) {
      if ($d)
        $csvData[] = $d;
    }
    fclose($fh);
    return $csvData;
  }

  /**
   * Iterate on $data and call $func to make operations on rows
   *
   * @param string $fileName
   * @param array $data
   * @param function(array $dataRow) $func
   */
  private function dynamicImporter(string $fileName, array $data, $func)
  {
    foreach($data as $dataRow) {
      $func($dataRow);
    }
    $this->output .= "{$fileName} successfully imported<br/>";
  }

  /**
   * @deprecated from 24/07/2018
   * @param string $fileName
   * @param array $data
   */
  private function importOnDatabase(string $fileName, array $data)
  {
    global $f3;
    $this->dbMysql->query("TRUNCATE TABLE `distinte`");
    foreach($data as $dataRow) {
      $descr = addslashes($dataRow[10]);
      $sql = <<<EOL
INSERT INTO distinte (
origine, consegna, commessa, ordine_produzione, data_creazione, time_creazione, destinazione_merci, barcode, descrizione, qta_prelevare, qta_mancante
) VALUES (
'$dataRow[16]', '$dataRow[0]', '$dataRow[2]', '$dataRow[3]', str_to_date('$dataRow[6]','%d/%m/%Y'), '$dataRow[7]', '$dataRow[15]','$dataRow[9]',
'$descr', '$dataRow[11]', 0
);
EOL;
      if (!$this->dbMysql->query($sql)) {
        echo "Error on query: {$sql}" . PHP_EOL;
        echo $this->dbMysql->error . PHP_EOL;
        return;
      }
    }
    echo "{$fileName} successfully imported" . PHP_EOL;
    $this->markFileAsDone($fileName);
  }

  private function markFileAsDone(string $csvPath)
  {
    rename($csvPath, "{$csvPath}_imported");
  }

  private function mysqlLoadCSV(string $CSVPath, string $tableName)
  {
    $this->dbMysql->query("TRUNCATE TABLE `{$tableName}`");
    $sql = <<<EOF
LOAD DATA INFILE '$CSVPath' INTO TABLE `$tableName`
FIELDS TERMINATED BY ';' ENCLOSED BY '' LINES TERMINATED BY '\\n';
EOF;
    if (!$this->dbMysql->query($sql)) {
      echo "Error on query: {$sql}" . PHP_EOL;
      echo $this->dbMysql->error . PHP_EOL;
      return;
    }
    echo "{$CSVPath} successfully imported" . PHP_EOL;
    $this->markFileAsDone($CSVPath);
  }
}