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)
    {
        list($this->dbname, $this->dbuser, $this->dbpass) = [$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;
                }
            }
        }
    }

    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)
    {
        foreach ($data as $dataRow) {
            $sql = <<<EOL
INSERT INTO distinte (
origine, commessa, ordine_produzione, data_creazione, time_creazione, destinazione_merci, barcode, descrizione, qta_prelevare, qta_mancante
) VALUES (
'$dataRow[16]', '$dataRow[0]', '$dataRow[3]', str_to_date('$dataRow[6]','%d/%m/%Y'), '$dataRow[7]', '$dataRow[15]','$dataRow[9]', '$dataRow[10]', '$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)
    {
        $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;