<?php
include("connect.php");
date_default_timezone_set("Asia/Kolkata");
require_once 'PHPExcel/Classes/PHPExcel.php';

$bill_no = $_POST['bill_no'];
$truckno = $_POST['TruckNo'];
$date = $_POST['Date'];
$date1=str_replace('-','',date('d-m-Y',strtotime($date)));
$truckID = $truckno ."-". $date1;
//$truckID = str_replace('-', '', $truckID);

//$query = "SELECT * FROM TruckNo WHERE TruckId = '$truckID'";
$query = "SELECT * FROM TruckNo WHERE bill_no = '$bill_no'";
$result = mysqli_query($conn, $query);
$response=array();
if (mysqli_num_rows($result) > 0) {
	//$response = "TruckID already exists!";
	$response['status']='error';
	$response['message']='Bill No already exists!';
} else {
  if (isset($_FILES["excel_file"]) && $_FILES["excel_file"]["error"] == 0) {
    $uploadDir = "upload/";

    // Generate a unique name for the uploaded file
    $fileName = uniqid() . "_" . $_FILES["excel_file"]["name"];
    
    if (move_uploaded_file($_FILES["excel_file"]["tmp_name"], $uploadDir . $fileName)) {

            //$insertQuery = "INSERT INTO TruckNo (TruckId, TruckNo, `Date`, Status) VALUES ('$truckID', '$truckno', '$date','pending')";
			$insertQuery = "INSERT INTO TruckNo (bill_no,TruckId, TruckNo, `Date`, Status) VALUES ('$bill_no','$truckID', '$truckno', '$date','pending')";
            if (mysqli_query($conn, $insertQuery)) {
              //$response = "TruckID added successfully!";
				$response['status']='success';
				$response['message']='TruckID added successfully!';
            } else {
              $response = "Error: " . mysqli_error($conn);
              exit();
            }
              $inputFileName = $uploadDir . $fileName;
              $objPHPExcel = PHPExcel_IOFactory::load($inputFileName);

              $sheet = $objPHPExcel->getActiveSheet();

              $startRow = 2;

              // Loop through rows and insert data into the database
              for ($rowIndex = $startRow; $rowIndex <= $sheet->getHighestRow(); $rowIndex++) {
                  $ChassisNo = $sheet->getCellByColumnAndRow(1, $rowIndex)->getValue(); 
                  $CylNo = $sheet->getCellByColumnAndRow(3, $rowIndex)->getValue(); 
                  $KitNo = $sheet->getCellByColumnAndRow(4, $rowIndex)->getValue(); 
                  $EngineNo = $sheet->getCellByColumnAndRow(2, $rowIndex)->getValue();
				  $color = $sheet->getCellByColumnAndRow(6, $rowIndex)->getValue();
				  $product = $sheet->getCellByColumnAndRow(5, $rowIndex)->getValue();

                  if (!empty($ChassisNo)) {
					$chassis_query = "SELECT * FROM truckdetails WHERE ChassisNo = '$ChassisNo'";
					$chassis_result = mysqli_query($conn, $chassis_query);
					if (mysqli_num_rows($chassis_result) == 0) {					
						//$sql = "INSERT INTO truckdetails (TruckNo, ChassisNo, CylNo, Cyl_Mfg, KitNo, Kit_Mfg, EngineNo,color,product) VALUES ('$truckID','$ChassisNo', '$CylNo', 'NULL', '$KitNo', 'NULL', '$EngineNo', '$color', '$product')";
						$sql = "INSERT INTO truckdetails (bill_no,TruckNo, ChassisNo, CylNo, Cyl_Mfg, KitNo, Kit_Mfg, EngineNo,color,product) VALUES ('$bill_no','$truckID','$ChassisNo', '$CylNo', 'NULL', '$KitNo', 'NULL', '$EngineNo', '$color', '$product')";
						$conn->query($sql);
					}
                  }

                  if (!empty($product)) {
                    $sql = "INSERT INTO product (name) VALUES ('$product') ON DUPLICATE KEY UPDATE name = VALUES(name)";
                    $conn->query($sql);
                  }
                  
                  if (!empty($color)) {
                      $sql = "INSERT INTO color (name) VALUES ('$color') ON DUPLICATE KEY UPDATE name = VALUES(name)";
                      $conn->query($sql);
                  }
              }
			  
			  $sql = "UPDATE transit_vehicle SET status='Close' WHERE id={$_POST['hidden_id']}";
			  $conn->query($sql);
			  
			/*$bill_dir_path=$_SERVER['DOCUMENT_ROOT'].'/scanner/uploads/bill_no/'.trim($_POST['bill_no']).'.pdf';
			move_uploaded_file($_FILES["pdf_file"]["tmp_name"],$bill_dir_path);			
			sleep(3);
			shell_exec("python compress_pdf.py $bill_dir_path");*/
					  
              unlink($uploadDir . $fileName);
              $response = "File uploaded and data processed successfully.";
			  echo "<script>window.location.href = 'InGodown.php?page=1';</script>";
          
          } else {
            $response['status']='error';
			$response['message']='Error uploading the file.';
          }
      } else {
		$response['status']='error';
		$response['message']='No file uploaded or an error occurred.';
      }
}

/*
$tableHTML = '<br><h2 class="center">Truck Details</h2><br>';
$tableHTML .= '<table class="table table-striped table-hover">';
$tableHTML .= '<tr><th>Chassis No</th><th>Engine No</th><th>Cylinder No</th><th>Cylinder Mfg</th><th>Kit No</th><th>Kit Mfg</th><th>Color</th><th>Product</th></tr>';

$selectQuery = "SELECT * FROM truckdetails WHERE TruckNo = '$truckID' AND CylNo != 'NULL'";
$selectResult = mysqli_query($conn, $selectQuery);

if (mysqli_num_rows($selectResult) > 0) {
  while ($row = mysqli_fetch_assoc($selectResult)) {
    $tableHTML .= '<tr>';
    $tableHTML .= '<td>' . $row['ChassisNo'] . '</td>';
    $tableHTML .= '<td>' . $row['EngineNo'] . '</td>';
    $tableHTML .= '<td>' . $row['CylNo'] . '</td>';
    $tableHTML .= '<td>' . $row['Cyl_Mfg'] . '</td>';
    $tableHTML .= '<td>' . $row['KitNo'] . '</td>';
    $tableHTML .= '<td>' . $row['Kit_Mfg'] . '</td>';
    $tableHTML .= '<td>' . $row['color'] . '</td>';
    $tableHTML .= '<td>' . $row['product'] . '</td>';
    $tableHTML .= '</tr>';
  }
} else {
  $tableHTML .= '<tr><th colspan="3">No data found</th></tr>';
}
$tableHTML .= '</table>';
$responseWithID =  $truckID .'|';*/


mysqli_close($conn);

echo json_encode($response);
?>
