<?php
// define('DB_SERVER', 'localhost');
// define('DB_USERNAME', 'surveyli_user3');
// define('DB_PASSWORD', 'sawtooth123');

// mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD);
// mysqli_select_db('surveyli_nielsen3');
include 'config.php';
$data_col = array("Name","All India Score","Filtered Score","Comments","Zone","Region","State","City","Dealer","Workshop","Quarter","Sorder");
$zone='';
$region='';
$city='';
$state='';
$dealer='';
$workshop='';
$quarter='';

date_default_timezone_set("Asia/Kolkata");
?>
<?php
if(isset($_REQUEST['upload'])) {
	
    if(isset($_FILES['uploadFile2']['name']) && $_FILES['uploadFile2']['name'] != "") {
        $allowedExtensions = array("xls","xlsx");
        $ext = pathinfo($_FILES['uploadFile2']['name'], PATHINFO_EXTENSION);
        if(in_array($ext, $allowedExtensions)) {
            $file_size = $_FILES['uploadFile2']['size'] / 1024;
            if($file_size < 500000) {
                $fileTmpPath = $_FILES['uploadFile2']['tmp_name'];
                $uploadFileDir = 'uploads/';
                $dest_path = $uploadFileDir .$_FILES['uploadFile2']['name'];
                
                $file = $dest_path;
                $isUploaded = move_uploaded_file($fileTmpPath, $dest_path);
                /*$destination_path = getcwd().DIRECTORY_SEPARATOR;
                $target_path = $destination_path . 'uploads/'. basename( $_FILES["uploadFile"]["name"]);
                $isUploaded = move_uploaded_file($_FILES['uploadFile']['tmp_name'], $target_path);
                $file = $target_path;*/
                if($isUploaded) {
                    include("config.php");
                    include("Classes/PHPExcel/IOFactory.php");
                    try {
                        //Load the excel(.xls/.xlsx) file
                        PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
                        $objPHPExcel = PHPExcel_IOFactory::load($file);
                    } catch (Exception $e) {
                        die('Error loading file "' . pathinfo($file, PATHINFO_BASENAME). '": ' . $e->getMessage());
                    }
                    
                    $sheet = $objPHPExcel->getSheet(0);
                    $total_rows = $sheet->getHighestRow();
                    $highest_column = $sheet->getHighestColumn();
					
					$row1=2;
					
					$single_row1 = $sheet->rangeToArray('A' . $row1 . ':' . $highest_column . $row1, NULL, TRUE, FALSE);
					//print_r($single_row1[0]);
					foreach($single_row1[0] as $key=>$value) {
						
						if($key==4)
						{
							$zone=$value;
						}
						elseif($key==5)
						{
							$region=$value;
						}
						elseif($key==6)
						{
							$state=$value;
						}
						elseif($key==7)
						{
							$city=$value;
						}
						elseif($key==8)
						{
							$dealer=$value;
						}
						elseif($key==9)
						{
							$workshop=$value;
						}
						elseif($key==10)
						{
							$quarter=$value;
						}
					}
					
                    	$Ldate=date(DATE_RFC850);
					$u=$_REQUEST['user'];
					$d=$_REQUEST['dealer_code'];
					$ut=$_REQUEST['user_type'];
					$cdate=date("Y-m-d");
					$qN=mysqli_query($mysqli,"insert into action_log values('$d','$u','$ut','$Ldate','$cdate')");		
	
                    //$sql5 = "TRUNCATE TABLE `action`";
					$qq = "delete from `action` where Zone='".$zone."' and Region='".$region."' and State='".$state."' and City='".$city."' and Dealer='".$dealer."' and Workshop='".$workshop."' and Quarter='".$quarter."'";
                    mysqli_query($mysqli,$qq);
					
					echo$qq;
					
						$colX  = "(`".implode("`, `",$data_col)."`)";
						$count_column = sizeof($data_col);
						$query = "insert into `action` $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($con,$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($mysqli,$query);
						echo$query;
						if(mysqli_affected_rows() > 0) {
							echo '<span class="msg">Database table updated!</span>';
						} 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 cross 50 KB on size!</span>';	
            }
        } else {
            echo '<span class="msg">This type of file 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>-->