<?php
// Enable error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Database configuration
include 'config.php';

$data_col = array("s_no", "Name", "All India Score", "Filtered Score", "Comments", "Zone", "Region", "Main_dealer_name", "Dealer_name", "State", "City", "Quarter");
$zone = '';
$region = '';
$city = '';
$state = '';
$maindealer = '';
$dealer = '';
$quarter = '';

date_default_timezone_set("Asia/Kolkata");

if (isset($_POST['upload3'])) {
    if (isset($_FILES['uploadFile3']['name']) && $_FILES['uploadFile3']['name'] != "") {
        $allowedExtensions = array("xls", "xlsx");
        $ext = pathinfo($_FILES['uploadFile3']['name'], PATHINFO_EXTENSION);
        if (in_array($ext, $allowedExtensions)) {
            $file_size = $_FILES['uploadFile3']['size'] / 1024;
            if ($file_size < 500000) {
                $fileTmpPath = $_FILES['uploadFile3']['tmp_name'];
                $uploadFileDir = 'key_uploads/';
                $dest_path = $uploadFileDir . $_FILES['uploadFile3']['name'];
                $isUploaded = move_uploaded_file($fileTmpPath, $dest_path);
                
                if ($isUploaded) {
                    // Include PhpSpreadsheet
                    require 'vendor/autoload.php';

                    try {
                        // Load the excel(.xls/.xlsx) file using the full namespace
                        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($dest_path);
                    } catch (Exception $e) {
                        die('Error loading file "' . pathinfo($dest_path, PATHINFO_BASENAME) . '": ' . $e->getMessage());
                    }

                    $Ldate = date(DATE_RFC850);
                    $u = $_REQUEST['user'];
                    $d = $_REQUEST['dealer_code'];
                    $ut = $_REQUEST['user_type'];
                    $cdate = date("Y-m-d");

                    $sheet = $spreadsheet->getActiveSheet();
                    $total_rows = $sheet->getHighestRow();
                    $highest_column = $sheet->getHighestColumn();

                    $row1 = 2;
                    $single_row1 = $sheet->rangeToArray('A' . $row1 . ':' . $highest_column . $row1, NULL, TRUE, FALSE);
                    
                    foreach ($single_row1[0] as $key => $value) {
                        if ($key == 5) {
                            $zone = $value;
                        } elseif ($key == 6) {
                            $region = $value;
                        } elseif ($key == 7) {
                            $maindealer = $value;
                        } elseif ($key == 8) {
                            $dealer = $value;
                        } elseif ($key == 9) {
                            $state = $value;
                        } elseif ($key == 10) {
                            $city = $value;
                        } elseif ($key == 11) {
                            $quarter = $value;
                        }
                    }

                    $qq = "DELETE FROM `action2` WHERE Zone='" . $zone . "' AND Region='" . $region . "' AND Main_dealer_name='" . $maindealer . "' AND Dealer_name='" . $dealer . "' AND State='" . $state . "' AND City='" . $city . "' AND Quarter = '" . $quarter . "'";
                    mysqli_query($conn, $qq);

                    $colX = "(`" . implode("`, `", $data_col) . "`)";
                    $count_column = sizeof($data_col);
                    $query = "INSERT INTO `action2` $colX VALUES ";
                    
                    for ($row = 2; $row <= $total_rows; $row++) {
                        $single_row = $sheet->rangeToArray('A' . $row . ':' . $highest_column . $row, NULL, TRUE, FALSE);
                        $count_row = 0;
                        $query .= "(";
                        foreach ($single_row[0] as $key => $value) {
                            $query .= "'" . html_entity_decode(preg_replace('/_x([0-9a-fA-F]{4})_/', '&#x$1;', mysqli_real_escape_string($conn, $value))) . "',";
                            $count_row++;
                        }
                        if ($count_column == $count_row) {
                            $query = substr($query, 0, -1);
                        } else {
                            $query = substr($query, 0, -4);
                        }
                        $query .= "),";
                    }
                    $query = substr($query, 0, -1);
                    mysqli_query($conn, $query);

                    if (mysqli_affected_rows($conn) > 0) {
                        echo '<span class="msg">Database table updated!</span>';
                        $zonal_arrFD = array(1 =>"All India", 2 =>"North", 3 =>"South", 4 =>"East", 5 =>"West", 6 =>"Central", 7 =>"Central 1", 8 =>"Central 2", 9 =>"Central 3", 10 =>"North 1", 11 =>"North 2", 12 =>"North 3", 13 =>"East 1", 14 =>"East 2", 15 =>"East 3", 16 =>"West 1", 17 =>"West 2", 18 =>"West 3", 19 =>"West 4", 20 =>"South 1", 21 =>"South 2", 22 =>"South 3", 23 =>"South 4");
					if($dealer!="")
					{
						$x=explode("(",$dealer);
							$x=explode(")",$x[1]);
						$d=$x[0];
						$ut=999;
                        $xcodex = 2;
					}
					else if($maindealer!="")
					{
						$x=explode("(",$maindealer);
						$x=explode(")",$x[1]);
						$d=$x[0];
						$ut=999;
                        $xcodex = 1;
					}
					else if($region!="")
					{
						$d=$region;
						$ut=array_search($region,$zonal_arrFD);
                        $xcodex = 0;
					}
					else if($zone!="")
					{
						$d=$zone;
						$ut=array_search($region,$zonal_arrFD);
                        $xcodex = 0;
					}
					else{ }
					
					$qNU=mysqli_query($conn,"select login_id from sales_user where dealer_code='$d' and user_type='$ut'");		
					if(mysqli_num_rows($qNU)>0){
					$qNU=mysqli_fetch_array($qNU); 
					$u=$qNU[0];
					$qN=mysqli_query($conn,"insert into action_log values('$d','$u','$ut','$Ldate','$cdate','$quarter',2,'$xcodex')");		
					}

                    } else {
                        echo '<span class="msg">Can\'t update database table! Try again.</span>';
                    }

                    unlink($dest_path);	
                } else {
                    echo '<span class="msg">File not uploaded!</span>';
                }
            } else {
                echo '<span class="msg">Maximum file size should not exceed 500 KB!</span>';	
            }
        } else {
            echo '<span class="msg">This type of file is not allowed!</span>';
        }
    } else {
        echo '<span class="msg">Select an excel file first!</span>';
    }
    echo "<meta http-equiv='refresh' content='0'>";
}
?>
<script>window.location.href='home.php'</script>
