<?php
include 'header.php';
require_once('vendor/php-excel-reader/excel_reader2.php');
require_once('vendor/SpreadsheetReader.php');
include('xcrud/xcrud.php');
echo Xcrud::load_css();
$xcrud=Xcrud::get_instance();
$xcrud->table('vehicle_details');
$xcrud->join('customer_id','customer','customer_id');
$xcrud->relation('customer.make','meter_make','id','name');
$xcrud->relation('customer.rto','rto','id','name');
$xcrud->columns('customer.rto,dealer_name,application_no,party_name,address,customer.contact_no,class,chassis_no,engine_no,customer.sale_date,insurance_date,approval_date,reg_no,hpa,customer.meter_no,customer.make,address_proof,year_of_mfg',false);
$xcrud->fields('customer.rto,dealer_name,application_no,party_name,address,customer.contact_no,class,chassis_no,engine_no,customer.sale_date,insurance_date,approval_date,reg_no,hpa,customer.meter_no,customer.make,address_proof,year_of_mfg',false);
$xcrud->label(array('customer.contact_no'=>'Mobile No','customer.rto'=>'Office Name'));
$xcrud->unset_title();
$xcrud->unset_add();
$xcrud->unset_edit();
$xcrud->unset_remove();
?>
<div class="row">
	<div class="col-sm-12"><button class="btn btn-blue btn-block">RTO Data</button></div>
	<div style="margin-top:50px"></div>
	<form action="" method="post" enctype="multipart/form-data">
		<div class="col-sm-4">
			<input class="form-control" type="file" name="file" id="file" accept=".xls,.xlsx">
		</div>
		<div class="col-sm-3">
			<button type="submit" name="update_rto_data" class="form-control btn-success">Import</button>
		</div>
		<div class="col-sm-1">
			<a href="uploads/Registered Vehicle Details.xls" class="form-control btn-info">Sample</a>
		</div>
	</form>
	<div class="col-sm-12">
<?php
echo $xcrud->render();
?>
	</div>
</div>
</div>
</div>
</div>
<?php
if (isset($_POST["update_rto_data"]))
{
	$report=array();
	$earray=array();

	$allowedFileType = ['application/vnd.ms-excel','text/xls','text/xlsx','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];
  
	  if(in_array($_FILES["file"]["type"],$allowedFileType)){	  
			$targetPath = 'temp/'.$_FILES['file']['name'];
			move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);
			$Reader = new SpreadsheetReader($targetPath);
			$sheetCount = count($Reader->sheets());
			for($i=0;$i<$sheetCount;$i++){
				$Reader->ChangeSheet($i);
				foreach ($Reader as $Row){
					$dealer_name = preg_replace('!\s+!', ' ',$Row[2]);
					$application_no = preg_replace('!\s+!', ' ',$Row[7]);
					$class = preg_replace('!\s+!', ' ',$Row[4]);
					$chassis_no = preg_replace('!\s+!', ' ',$Row[12]);
					$address = $Row[23];
					$reg_no=preg_replace('!\s+!', ' ',$Row[10]);
					$approval_date_array=explode(" ",$Row[17]);
					$approval_date=date('Y-m-d',strtotime($approval_date_array[0]));
					
					if(!empty($chassis_no)){
						$bdd=new DB();
						//$regNoRow=$bdd->getOne("SELECT customer_id FROM `customer` WHERE chassis_no='".$chassis_no."'");
						$regNoRow=$bdd->getOne("SELECT customer_id FROM `vehicle_details` WHERE chassis_no='".$chassis_no."'");
						if(empty($regNoRow)){
							//echo "<br>Chassis No: $chassis_no not found in customer.";
							$earray=array("Note"=>"Chassis No: $chassis_no not found in RTO data.");
							array_push($report,$earray);
						}else{
							$bdd->execute1("UPDATE vehicle_details SET dealer_name='$dealer_name',application_no='$application_no',class='$class',address='$address',reg_no='$reg_no',approval_date='$approval_date' WHERE customer_id='{$regNoRow['customer_id']}'");
							//echo "<br><b>Chassis No: $chassis_no is detail updated.</b>";
							$earray=array("Note"=>"Chassis No: $chassis_no detail updated.");
							array_push($report,$earray);
						}
						$bdd->disconnect();
					}else{
						//echo "<br>Chassis number is empty in excel file";
						$earray=array("Note"=>"Chassis number is empty in excel file.");
						array_push($report,$earray);
					}
				 }
			}		
			//print_r($report);
			$_SESSION['report']=$report;
			header("Location: excel_report_rto_data.php" );
			
	  }else{ 
		$type = "error";
		$message = "Invalid File Type. Upload Excel File.";
	  }
}
include "footer.php";
echo Xcrud::load_js();
?>