<?php
echo nl2br("Inizio importazione\n");

$DATABASE_HOST = 'mhhtmffhr.mysql.db';
$DATABASE_USER = 'mhhtmffhr';
$DATABASE_PASS = 'fhK75Dkdndk434h';
$DATABASE_NAME = 'mhhtmffhr';
$con           = mysqli_connect($DATABASE_HOST, $DATABASE_USER, $DATABASE_PASS, $DATABASE_NAME);
if (mysqli_connect_errno()) {
 exit('Failed to connect to MySQL: ' . mysqli_connect_error());
} else {
    echo nl2br("Collegamento a DB riuscito\n");
}

/* $file = fopen("../../../timbrature/transactions.csv", "r"); */
$file = fopen("./timbrature/transactions.csv", "r");

if (($open = $file) !== false) {
    echo nl2br("File rilevato e aperto\n");

    while (($data = fgetcsv($open, 1000, ",")) !== false) {
        $array[] = $data;
    }
    fclose($open);
    /* unlink("../../../timbrature/transactions.csv"); */
     unlink("./timbrature/transactions.csv");
} else {
    echo nl2br("File non trovato o corrotto\n");
    echo nl2br("Fine importazione");
    exit;
}

for ($x = 0; $x < count($array); $x++) {
    $sql_user    = "SELECT * FROM users WHERE badge = " . $array[$x][0] . "";
    $result_user = mysqli_query($con, $sql_user);
    $user        = mysqli_fetch_assoc($result_user);

    if($user["badge"] == $array[$x][0]) {
        if($array[$x][1] == "E") { 
            $stamp_type  = "Entrata";
        } else if($array[$x][1] == "U") { 
            $stamp_type  = "Uscita";
        }
        $stamp_date  = $array[$x][2];
        $stamp_time  = $array[$x][3];
        $datetime    = date("Y-m-d H:i:s");
        $user_id     = $user['id'];

        $sql_insert = "INSERT INTO stamps VALUES (NULL,$user_id,'$stamp_type','$stamp_date','$stamp_time','','$datetime', 0, 1,'')";
        if ($con->query($sql_insert)) {
            $json = new stdClass();
            $json->user = $user_id;
            $json->tipo = "$stamp_type";
            $json->data = "$stamp_date";
            $json->ora = "$stamp_time";
            $json_stamp = json_encode($json);
            echo nl2br("Timbratura imprtata: ".$json_stamp." - ");

            $id = mysqli_insert_id($con);
            $stamp_datetime = $stamp_date . " " . $stamp_time;
            $sql_insert_event = "INSERT INTO tbl_events VALUES (NULL,'$stamp_type','$stamp_datetime','$stamp_datetime',$user_id,0,'#2196f3','Timbratura',$id)";
            if ($con->query($sql_insert_event) === true) {
                echo nl2br("Evento creato\n");
            } else {
                echo nl2br("Evento non creato\n");
            }
        }
    } else {
        echo nl2br("Badge ".$array[$x][0]." non trovato\n");
    }
}

echo "Fine importazione";
$con->close();