phpengine/omlinkimporter.php

<?php
/**
 * @author Open Mind
 * @date 11/04/2018
 * @time 09:48
 */

const DISTINTA_COLONNE = 20;
const ARTICLES_COLONNE = 15;

class CSVImporter
{
  private $currentFileName;
  private $folderToWatch;
  private $dbMysql;

  public function __construct($folderPath, $databaseName, $dbusername, $dbpassword)
  {
    $this->currentFileName = '';
    $this->folderToWatch = $folderPath;
    $this->dbMysql = new mysqli('localhost', $dbusername, $dbpassword, $databaseName);
    if ($this->dbMysql->connect_error) {
      die('Database connection error (' . $this->dbMysql->connect_errno . ') ' . $this->dbMysql->connect_error);
    }
  }

  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 DISTINTA_COLONNE:
            $this->importOnDatabase($fname, $allData);
            break;
          case ARTICLES_COLONNE:
            $this->mysqlLoadCSV(addslashes(realpath($fname)), 'articles');
            break;
          default:
            echo "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;
  }

  private function importOnDatabase(string $fileName, array $data)
  {
    $this->dbMysql->query("TRUNCATE TABLE `distinte`");
    foreach ($data as $dataRow) {
      $descr = $this->dbMysql->escape_string($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);
  }
}

echo PHP_EOL . 'Scanning import folder for new CSV to import...' . PHP_EOL . PHP_EOL;

$impoter = new CSVImporter('import', 'omlink_lamborghini', 'root', 'giancarlo');
$impoter->ScanAndStoreData();

echo PHP_EOL . '... end import' . PHP_EOL;