<?php
use Icewind\SMB\Server;
ini_set('memory_limit', '-1');
$server = new Server('business', 'AD\adder.add', 'Addme123');
try{
    $share = $server->getShare('filestore$');
    $content = $share->dir('Files/LPG New/Files for helpdesk/scripts');
}catch(Exception $e){
    echo "cannot read directory";
    var_dump($e);
    return 0;
}
$sysdet = [];
foreach ($content as $index=>$info) {
    try{
        $fh = $share->read($info->getPath());
    }catch(Exception $e) {
        echo "{$info->getName()} cannot read file<br><br>";
        continue;
    }
    $file = fread($fh, 4086);
    $data = explode("\r\n", $file); // separates the whole file into 3 lines - headers, data and an empty line.
    $sysdet[$index] = str_getcsv($data[1]); // assigns the data from first row to an array of the proper index
    fclose($fh);
}


array_walk($sysdet, function(&$value, $key){
    $value[20] =  strtotime(str_replace('/', '-', $value[1]));
});
array_walk($sysdet, function(&$value, $key){
    $value[21] =  substr($value[2],0,5); // we assign the barcode to the new value basing on pcname, but we still want to keep pcname.
});
usort($sysdet, function($a, $b){
    return $a[20] - $b[20]; // from earliest to latest. switch to $b[1] - $a[1] for going from latest to earliest
});

$servername = "localhost";
$username = 'website';
$password = 'JK0llp23';
$dbname = 'frs-system';

// Create connection

//$DB = \FroosleDB\DB::getInstance();

foreach ($sysdet as $item=>$value) {
    $conn = new mysqli($servername, $username, $password, $dbname);

    $mysqlquery = "update equipment_ps_license set usedby = '' where usedby = '$value[21]';";
    $mysqlquery = "update equipment_ps_license set lastupdate = '$value[1]' where usedby = '$value[21]';";
    $mysqlquery = "update equipment_ps_license set usedby = '' where license = '$value[15]';";
    $mysqlquery = "update equipment_ps_license set lastupdate = '' where license = '$value[15]';";
    $mysqlquery = "update equipment_ps_desk set pckey = '' where pckey = '$value[21]';";
    $mysqlquery .= "update equipment_ps_desk set monitor1key = '' where monitor1key = '$value[17]';";
    $mysqlquery .= "update equipment_ps_desk set monitor1key = '' where monitor2key = '$value[17]';";
    $mysqlquery .= "update equipment_ps_desk set monitor1key = '' where monitor1key = '$value[19]';";
    $mysqlquery .= "update equipment_ps_desk set monitor2key = '' where monitor2key = '$value[19]';";

    $mysqlquery .= "insert into equipment_ps_license (license, usedby, lastupdate) VALUES ('$value[15]', '$value[21]', '$value[1]') on duplicate key update usedby = '$value[21]', lastupdate = '$value[1]';";

    $mysqlquery .= "insert into equipment_ps_pcs (barcode, mac, pcname, model, cpu, gpu, os, hdd, ram, updated, notes, license) values ('$value[21]','$value[12]','$value[2]','$value[4]','$value[9]','$value[10]','$value[11]','$value[7]','$value[6]','$value[1]','none','$value[15]') on duplicate key update mac = '$value[12]', pcname = '$value[2]', model = '$value[4]', cpu = '$value[9]', gpu = '$value[10]', os = '$value[11]', hdd = '$value[7]', ram = '$value[6]', updated = '$value[1]', notes = '', license = '$value[15]';";

    $mysqlquery .= "insert into equipment_ps_barcodes (barcode, eqtype, fkey) VALUES ('$value[21]', 'pc', '$value[21]') on duplicate key update eqtype = 'pc', fkey = '$value[21]';";

    $mysqlquery .= "insert into equipment_ps_monitor (serialnumber, model, updated, notes) VALUES ('$value[17]','$value[16]','$value[1]','') on duplicate key update updated = '$value[1]';";

    if ($value[19] != null){
        $mysqlquery .= "insert into equipment_ps_monitor (serialnumber, model, updated, notes) VALUES ('$value[19]','$value[18]','$value[1]','') on duplicate key update updated = '$value[1]';";
    }
     $mysqlquery .= "insert into equipment_ps_desk (user, phonekey, pckey, monitor1key, monitor2key, updated) VALUES ('$value[14]','','$value[21]','$value[17]','$value[19]','$value[1]') on duplicate key update phonekey = '', pckey = '$value[21]', monitor1key = '$value[17]', monitor2key = '$value[19]', updated =  '$value[1]';";

    //$DB->query($mysqlquerypc[$item]);
    if ($conn->multi_query($mysqlquery) === TRUE) {
        echo "New record created successfully <br>";
    } else {
        echo "Error: ". $conn->error."<br>";
    }

    $conn->close();


}

/*
 * /$sql = "SELECT * FROM equipment_ps_pcs";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
       foreach($row as $hello=>$there){
          echo $hello." - ".$there;
           echo "<br>";
       }
       echo "<br>";
    }
} else {
    echo "0 results";
}
 *
 *
 */
/*
 * 0 pause,
 * 1 time,
 * 2 pcname,
 * 3 manufacturer,
 * 4 model,
 * 5 serialnumber,
 * 6 ram,
 * 7 hddsize,
 * 8 hddfree,
 * 9 cpu,
 * 10 gpu,
 * 11 os,
 * 12 mac
 * 13 user,
 * 14 disname,
 * 15 licensekey,
 * 16 monitor1model,
 * 17 monitor1sn,
 * 18 monitor2model,
 * 19 monitor2sn
 * 20 unix time
 * 21 barcode
 */
?>