<?php

//ini_set('display_errors', 1);

use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\SMTP;
use PHPMailer\PHPMailer\Exception;

//ini_set('display_errors', 1);
ini_set('max_execution_time', 0);
ini_set('memory_limit', '2056M');
//echo phpinfo(); exit;
include("includes/config_cmd.php");
include("includes/smtp.php");
require("includes/phpmailer/src/PHPMailer.php");
require("includes/phpmailer/src/SMTP.php");
require("includes/phpmailer/src/Exception.php");

// Define the CSV files and their expected headers
//$fileHeaderMap = [
//    'WSCust.csv' => ['ID', 'LastName', 'FirstName', 'PHONE'],
//    'WSNextStep.csv' => ['Descript'],
//    'WSRepairPrice.csv' => ['Descript', 'Amt', 'Cost', 'Vendor'],
//    'user.csv' => ['user_name', 'user_status'],
//    'WSRep.csv' => ['ID', 'Descript'],
//    'WSWig.csv' => ['Wig_Num'],
//    'WSWentTo.csv' => ['Descript', 'Rush','Cat','Password','Days'],
//    'WSItem.csv' => ['Wig_Num', 'Company', 'Length', 'Color', 'Size', 'Retail'],
//
//];
//if (isset($_POST['import'])) {
$csvDir = 'CSVFiles/'; // Directory where CSV files are located
//    $errors = [];
//
//    // Loop through each file in the directory
//    foreach ($fileHeaderMap as $fileName => $expectedHeaders) {
//        $filePath = $csvDir . $fileName;
//
//        // Check if file exists
//        if (!file_exists($filePath)) {
//            $errors[] = "File missing or name incorrect: $fileName";
//            continue;
//        }
//
//        // Open the CSV file for reading
//        if (($handle = fopen($filePath, 'r')) !== false) {
//            // Read the first line to get the headers
//            $headers = fgetcsv($handle);
//            fclose($handle);
//
//            // Check if headers match the expected headers
//            if ($headers !== $expectedHeaders) {
//                $errors[] = "Header mismatch in $fileName";
//            }
//        } else {
//            $errors[] = "Unable to open file: $fileName";
//        }
//    }
//
//    // Display results
//    if (!empty($errors)) {
//        foreach ($errors as $error) {
////            echo "<p style='color:red;'>$error</p>";
//                        echo "<p class='alert alert-danger text-center' style='color:red; '>$error</p>";
//
//        }
//    } else {
////        echo "<p style='color:green;'>All CSV files and headers are correct.</p>";
//                echo "<p class='alert alert-success text-center' style='color:green; '>All CSV files and headers are correct.</p>";
//
//    }
//////////////////////////////
//$cron_qry = "select * from cron";
//$cresult = $conn->query($cron_qry);
//$crow = $cresult->fetch_object();
//if ($crow->status == 1) {
//  updateCron($conn, 2); 
//$csvDir = 'CSVFiles/';
//$files = array_diff(scandir($csvDir), array('.', '..'));
$files = glob("$csvDir*.csv");
foreach ($files as $file) {
    $tblName = basename($file, ".csv");
    emptyTable($conn, $tblName);
    switch ($tblName) {
        case "Customer": 
            echo '1:Customer,';
            $LagTableID = insertUpdateLogTable($conn, $tblName);
            $i = 0;
            if (($handle = fopen($file, "r")) !== FALSE) {
                $row_arr = array();
                while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
                    if ($i > 0) {

                        $CustID = htmlspecialchars(trim($data[0]));
                        $Custname = htmlspecialchars(trim($data[1]));
                        $Address = htmlspecialchars(trim($data[2]));
                        $City = htmlspecialchars(trim($data[3]));
                        $State = htmlspecialchars(trim($data[4]));
                        $Zip = htmlspecialchars(trim($data[5]));
                        $Phone = htmlspecialchars(trim($data[6]));
                        $Contact = htmlspecialchars(trim($data[7]));
                        $NewRecord = htmlspecialchars(trim($data[8]));
                        $DateCreate = htmlspecialchars(trim($data[9]));
                        $ComputerId = htmlspecialchars(trim($data[10]));

                        $row_arr[] = "('" . $CustID . "','" . $Custname . "','" . $Address . "','" . $City . "','" . $State . "','" . $Zip . "','" . $Phone . "','" . $Contact . "','" . $NewRecord . "','" . $DateCreate . "','" . $ComputerId . "')";
                    }
                    $i++;
                }
                if (count($row_arr) > 0) { 
                    for ($i = 0; $i <= count($row_arr); $i += 1000) {
                        $new_arr = array_slice($row_arr, $i, 1000);
                        $row_str = implode(",", $new_arr);
                        try {
                            $qry = "INSERT INTO Customer(CustID,Custname,Address,City,State,Zip,Phone,Contact,NewRecord,DateCreate,ComputerId) values $row_str";
                            try {
                                $conn->query($qry);
                            } catch (mysqli_sql_exception $e) {
                                $message = "Data error/special characters in line# " . $i;
                                $log_qry = "insert into InsertErrorLogs(table_name,message) values('" . $tblName . "','" . $message . "')";
                                $conn->query($log_qry);
//                                $mail = new PHPMailer(true);
//                                send_mail($mail, $tblName, $i, FROM_EMAIL_ADDRESS, SMTP_USERNAME, SMTP_PASSWORD, FROM_NAME);
                            }
                        } catch (mysqli_sql_exception $e) {
                            $message = "Data error in line# " . $i . ", Exception:" . $e->getMessage();
                            $log_qry = "insert into InsertErrorLogs(table_name,message) values('" . $tblName . "','" . $message . "')";
                            $conn->query($log_qry);
//                            $mail = new PHPMailer(true);
//                            send_mail($mail, $tblName, $i, FROM_EMAIL_ADDRESS, SMTP_USERNAME, SMTP_PASSWORD, FROM_NAME);
                        }
                    }
                }
            }
            insertUpdateLogTable($conn, $tblName, $LagTableID);
            break;

        case "Description":
            echo '2:Description,';
            $LagTableID = insertUpdateLogTable($conn, $tblName);
            $i = 0;
            if (($handle = fopen($file, "r")) !== FALSE) {
                $row_arr = array();
                while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
                    if ($i > 0) {

                        $DescriptionID = $conn->real_escape_string(trim($data[0]));
                        $Description = $conn->real_escape_string(trim($data[1]));
                        $Res1 = $conn->real_escape_string(trim($data[2]));
                        $Res2 = $conn->real_escape_string(trim($data[3]));
                        $Res3 = $conn->real_escape_string(trim($data[4]));
                        $Inputmask = $conn->real_escape_string(trim($data[5]));
                        $Concentrator = htmlspecialchars(trim($data[6]));
                        $NewRecord = htmlspecialchars(trim($data[7]));
                        $DateCreate = htmlspecialchars(trim($data[8]));
                        $ComputerId = htmlspecialchars(trim($data[9]));
                        $SplitTests = htmlspecialchars(trim($data[10]));
                        $DefaultDueDateMonts = htmlspecialchars(trim($data[11]));

                        $row_arr[] = "('" . $DescriptionID . "','" . $Description . "','" . $Res1 . "','" . $Res2 . "','" . $Res3 . "','" . $Inputmask . "','" . $Concentrator . "','" . $NewRecord . "','" . $DateCreate . "','" . $ComputerId . "','" . $SplitTests . "','" . $DefaultDueDateMonts . "')";
                    }
                    $i++;
                }
                if (count($row_arr) > 0) {
                    for ($i = 0; $i <= count($row_arr); $i += 1000) {
                        $new_arr = array_slice($row_arr, $i, 1000);
                        $row_str = implode(",", $new_arr);
                        try {
                            $qry = "INSERT INTO description(DescriptionID,Description,Res1,Res2,Res3,Inputmask,Concentrator,NewRecord,DateCreate,ComputerId,SplitTests,DefaultDueDateMonts) values $row_str";
                            try {
                                $conn->query($qry);
                            } catch (mysqli_sql_exception $e) {
                                $message = "Data error/special characters in line# " . $i;
                                $log_qry = "insert into InsertErrorLogs(table_name,message) values('" . $tblName . "','" . $message . "')";
                                $conn->query($log_qry);
//                                $mail = new PHPMailer(true);
//                                send_mail($mail, $tblName, $i, FROM_EMAIL_ADDRESS, SMTP_USERNAME, SMTP_PASSWORD, FROM_NAME);
                            }
                        } catch (mysqli_sql_exception $e) {
                            $message = "Data error in line# " . $i . ", Exception:" . $e->getMessage();
                            $log_qry = "insert into InsertErrorLogs(table_name,message) values('" . $tblName . "','" . $message . "')";
                            $conn->query($log_qry);
//                            $mail = new PHPMailer(true);
//                            send_mail($mail, $tblName, $i, FROM_EMAIL_ADDRESS, SMTP_USERNAME, SMTP_PASSWORD, FROM_NAME);
                        }
                    }
                }
            }
            insertUpdateLogTable($conn, $tblName, $LagTableID);
            break;

        case "Equipment":
            echo '2:Equipment,';
            $LagTableID = insertUpdateLogTable($conn, $tblName);
            $i = 0;
            if (($handle = fopen($file, "r")) !== FALSE) {
                $row_arr = array();
                while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
                    if ($i > 0) {

                        $EquipmentID = htmlspecialchars(trim($data[0]));
                        $LocationId = htmlspecialchars(trim($data[1]));
                        $DescriptionID = htmlspecialchars(trim($data[2]));
                        $ManID = htmlspecialchars(trim($data[3]));
                        $ModelID = htmlspecialchars(trim($data[4]));
                        $Serial = htmlspecialchars(trim($data[5]));
                        $Notes = htmlspecialchars(trim($data[6]));
                        $ControlNo = htmlspecialchars(trim($data[7]));
                        $DateCreate = htmlspecialchars(trim($data[8]));
                        $ComputerId = htmlspecialchars(trim($data[9]));

                        $row_arr[] = "('" . $EquipmentID . "','" . $LocationId . "','" . $DescriptionID . "','" . $ManID . "','" . $ModelID . "','" . $Serial . "','" . $Notes . "','" . $ControlNo . "','" . $DateCreate . "','" . $ComputerId . "')";
                    }
                    $i++;
                }
                if (count($row_arr) > 0) {
                    for ($i = 0; $i <= count($row_arr); $i += 1000) {
                        $new_arr = array_slice($row_arr, $i, 1000);
                        $row_str = implode(",", $new_arr);
                        try {
                            $qry = "INSERT INTO Equipment(EquipmentID,LocationId,DescriptionID,ManID,ModelID,Serial,Notes,ControlNo,DateCreate,ComputerId) values $row_str";
                            try {
                                $conn->query($qry);
                            } catch (mysqli_sql_exception $e) {
                                $message = "Data error/special characters in line# " . $i;
                                $log_qry = "insert into InsertErrorLogs(table_name,message) values('" . $tblName . "','" . $message . "')";
                                $conn->query($log_qry);
//                                $mail = new PHPMailer(true);
//                                send_mail($mail, $tblName, $i, FROM_EMAIL_ADDRESS, SMTP_USERNAME, SMTP_PASSWORD, FROM_NAME);
                            }
                        } catch (mysqli_sql_exception $e) {
                            $message = "Data error in line# " . $i . ", Exception:" . $e->getMessage();
                            $log_qry = "insert into InsertErrorLogs(table_name,message) values('" . $tblName . "','" . $message . "')";
                            $conn->query($log_qry);
//                            $mail = new PHPMailer(true);
//                            send_mail($mail, $tblName, $i, FROM_EMAIL_ADDRESS, SMTP_USERNAME, SMTP_PASSWORD, FROM_NAME);
                        }
                    }
                }
            }
            insertUpdateLogTable($conn, $tblName, $LagTableID);
            break;

        case "EquipmentMaintenanceDates":
            echo '3:EquipmentMaintenanceDates,';
            $LagTableID = insertUpdateLogTable($conn, $tblName);
            $i = 0;
            if (($handle = fopen($file, "r")) !== FALSE) {
                $row_arr = array();
                while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
                    if ($i > 0) {

                        $EquipmentMaintenanceDatesID = htmlspecialchars(trim($data[0]));
                        $LocationId = htmlspecialchars(trim($data[1]));
                        $EquipmentMaintenanceDates = htmlspecialchars(trim($data[2]));
                        $EquipmentMaintenanceDescription = htmlspecialchars(trim($data[3]));
                        $SixMonthDate = htmlspecialchars(trim($data[4]));
                        $TwelevMonthDate = htmlspecialchars(trim($data[5]));
                        $NewRecord = htmlspecialchars(trim($data[6]));
                        $DateCreate = htmlspecialchars(trim($data[7]));
                        $Closed = htmlspecialchars(trim($data[8]));

                        $row_arr[] = "('" . $EquipmentMaintenanceDatesID . "','" . $LocationId . "','" . $EquipmentMaintenanceDates . "','" . $EquipmentMaintenanceDescription . "','" . $SixMonthDate . "','" . $TwelevMonthDate . "','" . $NewRecord . "','" . $DateCreate . "','" . $Closed . "')";
                    }
                    $i++;
                }
                if (count($row_arr) > 0) {
                    for ($i = 0; $i <= count($row_arr); $i += 1000) {
                        $new_arr = array_slice($row_arr, $i, 1000);
                        $row_str = implode(",", $new_arr);
                        try {
                            $qry = "INSERT INTO equipmentmaintenancedates(EquipmentMaintenanceDatesID,LocationId,EquipmentMaintenanceDates,EquipmentMaintenanceDescription,SixMonthDate,TwelevMonthDate,NewRecord,DateCreate,Closed) values $row_str";
                            try {
                                $conn->query($qry);
                            } catch (mysqli_sql_exception $e) {
                                $message = "Data error/special characters in line# " . $i;
                                $log_qry = "insert into InsertErrorLogs(table_name,message) values('" . $tblName . "','" . $message . "')";
                                $conn->query($log_qry);
//                                $mail = new PHPMailer(true);
//                                send_mail($mail, $tblName, $i, FROM_EMAIL_ADDRESS, SMTP_USERNAME, SMTP_PASSWORD, FROM_NAME);
                            }
                        } catch (mysqli_sql_exception $e) {
                            $message = "Data error in line# " . $i . ", Exception:" . $e->getMessage();
                            $log_qry = "insert into InsertErrorLogs(table_name,message) values('" . $tblName . "','" . $message . "')";
                            $conn->query($log_qry);
//                            $mail = new PHPMailer(true);
//                            send_mail($mail, $tblName, $i, FROM_EMAIL_ADDRESS, SMTP_USERNAME, SMTP_PASSWORD, FROM_NAME);
                        }
                    }
                }
            }
            insertUpdateLogTable($conn, $tblName, $LagTableID);
            break;

        case "Locations":
            echo '4:Locations,';
            $LagTableID = insertUpdateLogTable($conn, $tblName);
            $i = 0;
            if (($handle = fopen($file, "r")) !== FALSE) {
                $row_arr = array();
                while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
                    if ($i > 0) {

                        $LocationId = htmlspecialchars(trim($data[0]));
                        $CustID = htmlspecialchars(trim($data[1]));
                        $LocationName = htmlspecialchars(trim($data[2]));
                        $Address = htmlspecialchars(trim($data[3]));
                        $City = htmlspecialchars(trim($data[4]));
                        $State = htmlspecialchars(trim($data[5]));
                        $Zip = htmlspecialchars(trim($data[6]));
                        $Phone = htmlspecialchars(trim($data[7]));
                        $Contact = htmlspecialchars(trim($data[8]));
                        $NewRecord = htmlspecialchars(trim($data[9]));
                        $DateCreate = htmlspecialchars(trim($data[10]));
                        $ComputerId = htmlspecialchars(trim($data[11]));
                        $row_arr[] = "('" . $LocationId . "','" . $CustID . "','" . $LocationName . "','" . $Address . "','" . $City . "','" . $State . "','" . $Zip . "','" . $Phone . "','" . $Contact . "','" . $NewRecord . "','" . $DateCreate . "','" . $ComputerId . "')";
                    }
                    $i++;
                }
                if (count($row_arr) > 0) {
                    for ($i = 0; $i <= count($row_arr); $i += 1000) {
                        $new_arr = array_slice($row_arr, $i, 1000);
                        $row_str = implode(",", $new_arr);
                        try {
                            $qry = "INSERT INTO  locations(LocationId, CustID, LocationName, Address, City, State, Zip, Phone, Contact, NewRecord, DateCreate, ComputerId) values $row_str";
                            try {
                                $conn->query($qry);
                            } catch (mysqli_sql_exception $e) {
                                $message = "Data error/special characters in line# " . $i;
                                $log_qry = "insert into InsertErrorLogs(table_name,message) values('" . $tblName . "','" . $message . "')";
                                $conn->query($log_qry);
//                                $mail = new PHPMailer(true);
//                                send_mail($mail, $tblName, $i, FROM_EMAIL_ADDRESS, SMTP_USERNAME, SMTP_PASSWORD, FROM_NAME);
                            }
                        } catch (mysqli_sql_exception $e) {
                            $message = "Data error in line# " . $i . ", Exception:" . $e->getMessage();
                            $log_qry = "insert into InsertErrorLogs(table_name,message) values('" . $tblName . "','" . $message . "')";
                            $conn->query($log_qry);
//                            $mail = new PHPMailer(true);
//                            send_mail($mail, $tblName, $i, FROM_EMAIL_ADDRESS, SMTP_USERNAME, SMTP_PASSWORD, FROM_NAME);
                        }
                    }
                }
            }
            insertUpdateLogTable($conn, $tblName, $LagTableID);
            break;

        case "ManTable":
            echo '4:ManTable,';
            $LagTableID = insertUpdateLogTable($conn, $tblName);
            $i = 0;
            if (($handle = fopen($file, "r")) !== FALSE) {
                $row_arr = array();
                while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
                    if ($i > 0) {

                        $ManID = htmlspecialchars(trim($data[0]));
                        $Manufacturer = htmlspecialchars(trim($data[1]));
                        $NewRecord = htmlspecialchars(trim($data[2]));
                        $DateCreate = htmlspecialchars(trim($data[3]));
                        $ComputerId = htmlspecialchars(trim($data[4]));
                        $row_arr[] = "('" . $ManID . "','" . $Manufacturer . "','" . $NewRecord . "','" . $DateCreate . "','" . $ComputerId . "')";
                    }
                    $i++;
                }
                if (count($row_arr) > 0) {
                    for ($i = 0; $i <= count($row_arr); $i += 1000) {
                        $new_arr = array_slice($row_arr, $i, 1000);
                        $row_str = implode(",", $new_arr);
                        try {
                            $qry = "INSERT INTO  ManTable(ManID, Manufacturer, NewRecord, DateCreate, ComputerId) values $row_str";
                            try {
                                $conn->query($qry);
                            } catch (mysqli_sql_exception $e) {
                                $message = "Data error/special characters in line# " . $i;
                                $log_qry = "insert into InsertErrorLogs(table_name,message) values('" . $tblName . "','" . $message . "')";
                                $conn->query($log_qry);
//                                $mail = new PHPMailer(true);
//                                send_mail($mail, $tblName, $i, FROM_EMAIL_ADDRESS, SMTP_USERNAME, SMTP_PASSWORD, FROM_NAME);
                            }
                        } catch (mysqli_sql_exception $e) {
                            $message = "Data error in line# " . $i . ", Exception:" . $e->getMessage();
                            $log_qry = "insert into InsertErrorLogs(table_name,message) values('" . $tblName . "','" . $message . "')";
                            $conn->query($log_qry);
//                            $mail = new PHPMailer(true);
//                            send_mail($mail, $tblName, $i, FROM_EMAIL_ADDRESS, SMTP_USERNAME, SMTP_PASSWORD, FROM_NAME);
                        }
                    }
                }
            }
            insertUpdateLogTable($conn, $tblName, $LagTableID);
            break;

        case "ModelTable":
            echo '4:ModelTable,';
            $LagTableID = insertUpdateLogTable($conn, $tblName);
            $i = 0;
            if (($handle = fopen($file, "r")) !== FALSE) {
                $row_arr = array();
                while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
                    if ($i > 0) {

                        $ModelID = htmlspecialchars(trim($data[0]));
                        $Model = htmlspecialchars(trim($data[1]));
                        $NewRecord = htmlspecialchars(trim($data[2]));
                        $DateCreate = htmlspecialchars(trim($data[3]));
                        $ComputerId = htmlspecialchars(trim($data[4]));
                        $row_arr[] = "('" . $ModelID . "','" . $Model . "','" . $NewRecord . "','" . $DateCreate . "','" . $ComputerId . "')";
                    }
                    $i++;
                }
                if (count($row_arr) > 0) {
                    for ($i = 0; $i <= count($row_arr); $i += 1000) {
                        $new_arr = array_slice($row_arr, $i, 1000);
                        $row_str = implode(",", $new_arr);
                        try {
                            $qry = "INSERT INTO  ModelTable(ModelID, Model, NewRecord, DateCreate, ComputerId) values $row_str";
                            try {
                                $conn->query($qry);
                            } catch (mysqli_sql_exception $e) {
                                $message = "Data error/special characters in line# " . $i;
                                $log_qry = "insert into InsertErrorLogs(table_name,message) values('" . $tblName . "','" . $message . "')";
                                $conn->query($log_qry);
//                                $mail = new PHPMailer(true);
//                                send_mail($mail, $tblName, $i, FROM_EMAIL_ADDRESS, SMTP_USERNAME, SMTP_PASSWORD, FROM_NAME);
                            }
                        } catch (mysqli_sql_exception $e) {
                            $message = "Data error in line# " . $i . ", Exception:" . $e->getMessage();
                            $log_qry = "insert into InsertErrorLogs(table_name,message) values('" . $tblName . "','" . $message . "')";
                            $conn->query($log_qry);
//                            $mail = new PHPMailer(true);
//                            send_mail($mail, $tblName, $i, FROM_EMAIL_ADDRESS, SMTP_USERNAME, SMTP_PASSWORD, FROM_NAME);
                        }
                    }
                }
            }
            insertUpdateLogTable($conn, $tblName, $LagTableID);
            break;

        case "EquipmentMaintenance":
            echo '7:EquipmentMaintenance,';
            $LagTableID = insertUpdateLogTable($conn, $tblName);
            $i = 0;
            if (($handle = fopen($file, "r")) !== FALSE) {
                $row_arr = array();
                while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
                    if ($i > 0) {
                        $EquipmentMaintenanceId = htmlspecialchars(trim($data[0]));
                        $EquipmentID = htmlspecialchars(trim($data[1]));
                        $MaintenanceDate = htmlspecialchars(trim($data[2]));
                        $RoomNumber = htmlspecialchars(trim($data[3]));
                        $Res = htmlspecialchars(trim($data[4]));
                        $Hours = htmlspecialchars(trim($data[5]));
                        $Pass = htmlspecialchars(trim($data[6]));
                        $Comment = htmlspecialchars(trim($data[7]));
                        $DateDue = htmlspecialchars(trim($data[8]));
                        $HoursDue = htmlspecialchars(trim($data[9]));
                        $Result1 = htmlspecialchars(trim($data[10]));
                        $Result2 = htmlspecialchars(trim($data[11]));
                        $Result3 = htmlspecialchars(trim($data[12]));
                        $MaintenanceType = htmlspecialchars(trim($data[13]));
                        $NewRecord = htmlspecialchars(trim($data[14]));
                        $DateCreate = htmlspecialchars(trim($data[15]));
                        $ComputerId = htmlspecialchars(trim($data[16]));
                        $RecordCreated = htmlspecialchars(trim($data[17]));
                        $CommentLong = htmlspecialchars(trim($data[18]));
                        $EquipmentMaintenanceDatesID = htmlspecialchars(trim($data[19]));
                        $LogOnID = htmlspecialchars(trim($data[20]));
                        $ActionRequired = htmlspecialchars(trim($data[21]));
                        $row_arr[] = "('" . $EquipmentMaintenanceId . "','" . $EquipmentID . "','" . $MaintenanceDate . "','" . $RoomNumber . "','" . $Res . "','" . $Hours . "','" . $Pass . "','" . $Comment . "','" . $DateDue . "','" . $HoursDue . "','" . $Result1 . "','" . $Result2 . "','" . $Result3 . "','" . $MaintenanceType . "','" . $NewRecord . "','" . $DateCreate . "','" . $ComputerId . "','" . $RecordCreated . "','" . $CommentLong . "','" . $EquipmentMaintenanceDatesID . "','" . $LogOnID . "','" . $ActionRequired . "')";
                    }
                    $i++;
                }
                if (count($row_arr) > 0) {
                    for ($i = 0; $i <= count($row_arr); $i += 1000) {
                        $new_arr = array_slice($row_arr, $i, 1000);
                        $row_str = implode(",", $new_arr);
                        try {
                            $qry = "INSERT INTO  equipmentmaintenance(EquipmentMaintenanceId, EquipmentID, MaintenanceDate, RoomNumber, Res, Hours, Pass, Comment, DateDue, HoursDue,
    Result1, Result2, Result3, MaintenanceType, NewRecord, DateCreate, ComputerId, RecordCreated, CommentLong,
    EquipmentMaintenanceDatesID, LogOnID, ActionRequired) values $row_str";
                            try {
                                $conn->query($qry);
                            } catch (mysqli_sql_exception $e) {
                                $message = "Data error/special characters in line# " . $i;
                                $log_qry = "insert into InsertErrorLogs(table_name,message) values('" . $tblName . "','" . $message . "')";
                                $conn->query($log_qry);
//                                $mail = new PHPMailer(true);
//                                send_mail($mail, $tblName, $i, FROM_EMAIL_ADDRESS, SMTP_USERNAME, SMTP_PASSWORD, FROM_NAME);
                            }
                        } catch (mysqli_sql_exception $e) {
                            $message = "Data error in line# " . $i . ", Exception:" . $e->getMessage();
                            $log_qry = "insert into InsertErrorLogs(table_name,message) values('" . $tblName . "','" . $message . "')";
                            $conn->query($log_qry);
//                            $mail = new PHPMailer(true);
//                            send_mail($mail, $tblName, $i, FROM_EMAIL_ADDRESS, SMTP_USERNAME, SMTP_PASSWORD, FROM_NAME);
                        }
                    }
                }
            }
            insertUpdateLogTable($conn, $tblName, $LagTableID);
            break;

//        case "EquipmentMaintenance":
//            echo '4:EquipmentMaintenance,';
//            $LagTableID = insertUpdateLogTable($conn, $tblName);
//            $i = 0;
//            if (($handle = fopen($file, "r")) !== FALSE) {
//                $row_arr = array();
//                while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
//                    if ($i > 0) {
//
//                        $EquipmentMaintenanceId = htmlspecialchars(trim($data[0]));
//                        $EquipmentID = htmlspecialchars(trim($data[1]));
//                        $MaintenanceDate = htmlspecialchars(trim($data[2]));
//                        $RoomNumber = htmlspecialchars(trim($data[3]));
//                        $Res = htmlspecialchars(trim($data[4]));
//                        $Hours = htmlspecialchars(trim($data[5]));
//                        $Pass = htmlspecialchars(trim($data[6]));
//                        $Comment = htmlspecialchars(trim($data[7]));
//                        $DateDue = htmlspecialchars(trim($data[8]));
//                        $HoursDue = htmlspecialchars(trim($data[9]));
//                        $Result1 = htmlspecialchars(trim($data[10]));
//                        $Result2 = htmlspecialchars(trim($data[11]));
//                        $Result3 = htmlspecialchars(trim($data[12]));
//                        $MaintenanceType = htmlspecialchars(trim($data[13]));
//                        $NewRecord = htmlspecialchars(trim($data[14]));
//                        $DateCreate = htmlspecialchars(trim($data[15]));
//                        $ComputerId = htmlspecialchars(trim($data[16]));
//                        $RecordCreated = htmlspecialchars(trim($data[17]));
//                        $CommentLong = htmlspecialchars(trim($data[18]));
//                        $EquipmentMaintenanceDatesID = htmlspecialchars(trim($data[19]));
//                        $LogOnID = htmlspecialchars(trim($data[20]));
//                        $ActionRequired = htmlspecialchars(trim($data[21]));
//
//                        $row_arr[] = "('$EquipmentMaintenanceId','$EquipmentID','$MaintenanceDate','$RoomNumber','$Res','$Hours','$Pass','$Comment','$DateDue','$HoursDue','$Result1','$Result2','$Result3','$MaintenanceType','$NewRecord','$DateCreate','$ComputerId','$RecordCreated','$CommentLong','$EquipmentMaintenanceDatesID','$LogOnID','$ActionRequired')";
//                    }
//                    $i++;
//                }
//                if (count($row_arr) > 0) {
//                    for ($i = 0; $i <= count($row_arr); $i += 1000) {
//                        $new_arr = array_slice($row_arr, $i, 1000);
//                        $row_str = implode(",", $new_arr);
//                        try {
//                            $qry = "INSERT INTO EquipmentMaintenance(EquipmentMaintenanceId, EquipmentID, MaintenanceDate, RoomNumber, Res, Hours, Pass, Comment, DateDue, HoursDue,
//    Result1, Result2, Result3, MaintenanceType, NewRecord, DateCreate, ComputerId, RecordCreated, CommentLong,
//    EquipmentMaintenanceDatesID, LogOnID, ActionRequired) values $row_str";
//                            try {
//                                $conn->query($qry);
//                            } catch (mysqli_sql_exception $e) {
//                                $message = "Data error/special characters in line# " . $i;
//                                $log_qry = "insert into InsertErrorLogs(table_name,message) values('" . $tblName . "','" . $message . "')";
//                                $conn->query($log_qry);
////                                $mail = new PHPMailer(true);
////                                send_mail($mail, $tblName, $i, FROM_EMAIL_ADDRESS, SMTP_USERNAME, SMTP_PASSWORD, FROM_NAME);
//                            }
//                        } catch (mysqli_sql_exception $e) {
//                            $message = "Data error in line# " . $i . ", Exception:" . $e->getMessage();
//                            $log_qry = "insert into InsertErrorLogs(table_name,message) values('" . $tblName . "','" . $message . "')";
//                            $conn->query($log_qry);
////                            $mail = new PHPMailer(true);
////                            send_mail($mail, $tblName, $i, FROM_EMAIL_ADDRESS, SMTP_USERNAME, SMTP_PASSWORD, FROM_NAME);
//                        }
//                    }
//                }
//            }
//            insertUpdateLogTable($conn, $tblName, $LagTableID);
//            break;
    }
}

$msg = "<div class='alert alert-success text-center'>CSV Import completed!</div>";

//    echo 'Import completed.'; 
//}


function emptyTable($conn, $tblName) {
    try {
        $qry = "TRUNCATE TABLE " . $tblName;
        $conn->query($qry);
    } catch (exception $e) {
        $message = "Table not found, Exception:" . $e->getMessage();
        $log_qry = "insert into InsertErrorLogs(table_name,message) values('" . $tblName . "','" . $message . "')";
        $conn->query($log_qry);
    }
}

function updateCron($conn, $status) {
    $qry = "UPDATE cron set SetTime=now(),status=$status";
    $conn->query($qry);
}

function insertUpdateLogTable($conn, $FileName, $LogTableID = '') {
    if (empty($LogTableID)) {
        $qry = "insert into LogTable(FileName,StartTime) values('" . $FileName . "',now())";
        $conn->query($qry);
        return $conn->insert_id;
    } else {
        $qry = "UPDATE LogTable set EndTime=now() where LogTableID=" . $LogTableID;
        $conn->query($qry);
    }
}

function clean_string($str) {
    $str = str_replace("�", "'", $str);
    $str = addslashes($str);
    return $str;
}

//function send_mail($mail, $tblName, $i, $from_email_address, $smtp_username, $smtp_password, $from_name) {
//    $subject = "Error in $tblName.csv";
//    $body = "Data error/special characters in line# " . $i;
//    try {
//        //Server settings
//        //$mail->SMTPDebug = SMTP::DEBUG_SERVER;                      //Enable verbose debug output
//        $mail->isSMTP();  //Send using SMTP
//        $mail->Host = 'smtp.gmail.com';   //Set the SMTP server to send through
//        $mail->SMTPAuth = true;  //Enable SMTP authentication
//        $mail->Username = $smtp_username;   //SMTP username
//        $mail->Password = $smtp_password; //SMTP password
//        //$mail->SMTPSecure = PHPMailer::ENCRYPTION_SMTPS;            //Enable implicit TLS encryption
//        $mail->SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS;
//        $mail->Port = 587;   //TCP port to connect to; use 587 if you have set `SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS`
//        //Recipients
//        $mail->setFrom($from_email_address, $from_name);
//        $mail->addAddress('pandatsachin38@gmail.com');
//        //Content
//        $mail->isHTML(true); //Set email format to HTML
//        $mail->Subject = $subject;
//        $mail->Body = $body;
//        $mail->send();
//    } catch (Exception $e) {
//        
//    }
//}
?>

<!--<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="UTF-8">
        <title>CSV Import</title>
        <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    </head>
    <body class="container mt-5">
         Display messages here 

        <h2 class="mb-4">Import CSV Files</h2>


         Import Button Form 
        <form action="cmd.php" method="post" class="mt-4">
            <div class="form-group col-5">
                <button type="submit" name="import" class="btn btn-success">Import</button>
            </div>

        </form>
    </body>
</html>-->

