<?php
/**
 * Récupération des fichiers INSEE pour mise a disposition et suivi
 */

require_once __DIR__ . '/../application/bin.bootstrap.php';

// --- Options
$displayUsage = false;
try {
    $opts = new Zend_Console_Getopt(array(
        'help|?'    => "Displays usage information.",
        'file-w'    => "Télécharger à nouveau le fichier en mode manuel pour écrasement de l'ancien",
        'type=w'    => "Only look for one type of file",
        'cron'      => "Mandatory option for launch the cli in cron",
        'from=w'    => "restricted not opendata"
    ));
    $opts->parse();
    $optionsNb = count($opts->getOptions());
} catch (Zend_Console_Getopt_Exception $e) {
    $displayUsage = true;
}

// --- Aide / Options
if ($optionsNb == 0 || isset($opts->help)) {
    $displayUsage = true;
}

// --- Usage
if ($displayUsage) {
    echo "Recuperation des fichiers INSEE FTPs.\n";
    echo $opts->getUsageMessage();
    exit;
}

$BASEPATH = realpath(APPLICATION_PATH . '/../');
$STORAGEBASE = $c->profil->path->shared.'/sources/insee';
$FTP_URL = "ftp://81.255.68.110:21/download/";
$FTP_OPTION = "-k --ssl --disable-epsv --user client_syracuse23:cLiEnT_SYRAcuse23*37654 --cacert $BASEPATH/certs/ca.pem --cert $BASEPATH/certs/client.pem --key $BASEPATH/certs/key.pem";

echo date('Y-m-d H:i:s') . " - Debut execution\n";

$storageDir = $STORAGEBASE . '/' . date('Y');
if (!file_exists($storageDir)) {
    mkdir($storageDir, 0777, true);
}

$oneShot = false;
if ($opts->file) {
    $oneShot = true;
}

if (file_exists('insee.lock')) {
    echo date('Y-m-d H:i:s') . "Fichier lock.\n";
    exit;
}

if ($opts->cron || $oneShot) {
    $execRef = new Scores_Exec_Ref('INSEE');
    $execId = $execRef->start();

    file_put_contents('insee.lock', "");

    /*
    $command = "curl $FTP_OPTION $FTP_URL";
    exec($command, $contents);
    print_r($contents); exit;
    */

    // --- Liste des fichiers
    $command = "curl -s $FTP_OPTION $FTP_URL | grep -e '^-' | awk '{ print $9 }'";
    exec($command, $contents);
    if (count($contents) == 0) {
        echo date('Y-m-d H:i:s') . " - Impossible de lister le répertoire dans le FTP distant.\n";
    }

    // --- Traitement pour chaque fichier
    $unitTotal = count($contents);
    $unitInc = $unitExec = 0;
    if ($unitTotal > 0) {
        foreach ($contents as $file) {
            echo date('Y-m-d H:i:s') . " - Lecture $file\n";
            $fileSql = "SELECT * FROM sdv1.flux_insee WHERE file=:file ORDER BY dateInsert DESC LIMIT 0,1";
            $fileStmt = $conn->prepare($fileSql);
            $fileStmt->bindValue('file', $file);
            $fileStmt->execute();
            $fileToDownload = false;
            // -- Fichier non présent en base
            if ($fileStmt->rowCount() == 0) {
                $unitInc++;
                $fileToDownload = true;
            }
            // --- Fichier déjà présent en base
            else {
                $fileRow = $fileStmt->fetch(\PDO::FETCH_OBJ);
                echo date('Y-m-d H:i:s') . " - Fichier $file en base";
                if (file_exists($fileRow->directory.'/'.$fileRow->file)) {
                    echo " exist\n";
                } else {
                    $unitInc++;
                    $fileToDownload = true;
                    echo " non exist\n";
                }
            }

            if ($fileToDownload) {
                // --- Téléchargement du fichier
                echo date('Y-m-d H:i:s') . " - Telechargement $file\n";
                $command = "cd $storageDir; curl -s -S $FTP_OPTION -O " . $FTP_URL . $file;
                exec($command);
                // --- Test integrity
                exec('unzip -t '.$storageDir.'/'.$file, $output, $integrity);
                if ($integrity == 0 && file_exists($storageDir.'/'.$file)) {
                    $type = 'insee';
                    $ftpSize = filesize($storageDir.'/'.$file);
                    $ftpDate = date("YmdHis", filemtime($storageDir.'/'.$file));

                    echo date('Y-m-d H:i:s') . " - Ecriture du fichier $storageDir/$file\n";
                    // --- Enregistrement dans la base de données
                    $data = array(
                        'type'      => $type,
                        'file'      => $file,
                        'directory' => $storageDir,
                        'ftpSize'   => $ftpSize,
                        'ftpDate'   => $ftpDate,
                        'dateInsert'=> date('YmdHis'),
                    );
                    if ($fileStmt->rowCount() == 0) {
                        try {
                            $conn->insert('sdv1.flux_insee', $data);
                        } catch (\Doctrine\DBAL\DBALException $e) {
                            echo date('Y-m-d H:i:s') . " - Erreur insertion $file = ".$e->getMessage()."\n";
                        }
                    } else {
                        try {
                            $conn->update('sdv1.flux_insee', $data, array('id' => $fileRow->id));
                        } catch (\Doctrine\DBAL\DBALException $e) {
                            echo date('Y-m-d H:i:s') . " - Erreur update $file = ".$e->getMessage()."\n";
                        }
                    }
                    $unitExec++;
                    if ($execId !== null) {
                        $execRef->increment($execId, $unitExec);
                    }
                } else {
                    echo date('Y-m-d H:i:s') . " - Impossible de télécharger $file\n";
                }
            }
        }
    }
    unlink('insee.lock');

    if ($execId !== null) {
        $execRef->total($execId, $unitInc);
        $execRef->end($execId);
    }
}

echo date('Y-m-d H:i:s') . " - Fin execution\n";