<?php
session_start();
	include('../db_connect.php');
	include('../db_table_select.php');
	$cmp=$_SESSION['SESS_CMPC'];
$fny=$_SESSION['SESS_FNY'];
$err=0;
$inserterr;
if ( isset( $_FILES['userfile'] ) )
{
  $csv_file = $_FILES['userfile']['tmp_name'];

  if ( ! is_file( $csv_file ) )
    exit('File not found.');

  	$sql = '';
	$i=0;
	
	if($_FILES["userfile"]["type"] != "application/vnd.ms-excel")
	$err=2;	
  else if (($handle = fopen( $csv_file, "r")) !== FALSE)
  {		
  		if($_GET[mode]=='book')
		{	
      while ((($data = fgetcsv($handle, 1000, ",")) !== FALSE)&&$data[1]!='')
      {	  
		  if($i!=0)
		  {	
		$next_bookid=mysqli_query($con,"SELECT bookid FROM getbookid where cmp like '%$cmp%' and fny like '$fny'");
		$row = mysqli_fetch_assoc($next_bookid);
		$bookid=$row['bookid'];
		$bookidhash=md5($bookid.$cmp.$fny);
		if(trim($data[0])=='')
		$ref=$bookid;
		else
		$ref=$data[0];		  
          $sql= "INSERT INTO `$bookingreg` SET
            `bookid` = '$bookid',
			`ref` = '$ref',
			`repdate` = STR_TO_DATE('$data[1]','%d/%m/%Y'),
			`sttime` = '$data[2]',
			`reptime` = '$data[3]',
			`city` = '$data[4]',
            `dutytype` = '$data[5]',
            `package` = '$data[6]',
			`vehicle` = '$data[7]',
			`driver` = '$data[8]',
            `vendor` = '$data[9]',
			`billedto` = '$data[10]',
			`bookedby` = '$data[11]',
            `usedby` = '$data[12]',
			`ccid` = '$data[13]',
			`guestmob` = '$data[14]',
            `repadd` = '$data[15]',
			`insstatus` = '$data[16]',
			`status` = '$data[17]',
			`remarks` = '$data[18]',
            `guestid` = '',
             itinerary='',
			`stkm` = '',
			`clgtime` = '',
			`clgkm` = '',
			`tottime` = '',
			`totkm` = '',
			`chgkms` = '',
			`parking` = '',
			`days` = '',
            `vehno` = '',
            `exhr` = '',
			`acexhr` = '',
            `exkm` = '',
            `billno` = '',
            `rdate` = '',
            `dutydate` = '',
            `guestmail` = '',
            `bookedbymob` = '',
            `bookedbyemail` = '',
            `drivermob` = '',
            `driverall` = '',
            `nightall` = '',
			`misc1` = '',
			`m1value` = '',
            `misc2` = '',
            `m2value` = '',
            `misc3` = '',
            `m3value` = '',
            `dutyrate` = '',
            `dutyamt` = '',
            `darate` = '',
            `daamt` = '',
			`narate` = '',
			`naamt` = '',
            `exhrrate` = '',
            `exhramt` = '',
            `exkmrate` = '',
            `exkmamt` = '',
            `tamount` = '',
			`fny` = '$fny',
            `cmp` = '$cmp',
            `userid` = '$_SESSION[SESS_MEMBER_ID]',
			`editby` = '".date("d/m/Y")."/".date("H:i")."/$_SESSION[SESS_FIRST_NAME]/$_SERVER[REMOTE_ADDR]"."',`bookidhash`='$bookidhash';";
			if(!mysqli_query($con,$sql))
		  {	
		  $err=1;
		  $temp=mysqli_error($con)."\n";
		  $inserterr.=$temp;
		 	
			  }
			  else
			  {
				  mysqli_query($con,"UPDATE `getbookid` SET `bookid` = (bookid+1) WHERE  cmp like '%$cmp%'  and  fny like '$fny'");
				  }
		  }//if ends
			
			
			$i++;
      }// while ends
		} //if book ends
		
		else if($_GET[mode]=='bill')
		{
      while ((($data = fgetcsv($handle, 1000, ",")) !== FALSE)&&$data[0]!='')
      {
		  if($i!=0)
		  {
				  
          $sql= "INSERT INTO `billreg` (`entryid`, `cmp`, `fny`, `bookregno`, `srno`, `billno`, `billdate`, `bookdate`, `billto`, `name`, `usedcity`, `address`, `citypin`, `bookedby`, `usedby`, `basicamount`, `tollamount`, `daamount`, `disrate`, `disamount`, `totalamt`, `srrate`, `sramount`, `educess`, `seducess`, `prcharge`, `payin`, `total`, `comments`, `expst`, `account`) VALUES (NULL, '$_SESSION[SESS_CMPC]', '$_SESSION[SESS_FNY]', '$data[15]', '$data[0]', '$data[4]', STR_TO_DATE('$data[3]','%d/%m/%Y'), ' ', '$data[5]', '$data[6]', '$data[7]', ' ', ' ', ' ', ' ', '$data[8]', '$data[9]', ' ', ' ', ' ', ' ', '$data[10]', '$data[11]', '$data[12]', '$data[13]', ' ', ' ', '$data[14]',  ' ', '$data[16]', ' ');";
/*			
			if($bill_exist=mysqli_query($con,"select billno from billreg where billno='$data[4]'"))
			if(mysqli_num_rows($bill_exist)==0)
			{
			$err=1;	
			$temp="Dublicate entraay for $data[4]\n";
		  	$inserterr.=$temp;
			}
			else */
			if(!mysqli_query($con,$sql))
		  {	
		  $err=1;
		  $temp=mysqli_error($con)."\n";
		  $inserterr.=$temp;
		 // break;
			  }
		  }//if ends
			
			
			$i++;
      }// while ends
		} //if bill ends
		
		  		else if($_GET[mode]=='fuel')
		{	
      while ((($data = fgetcsv($handle, 1000, ",")) !== FALSE)&&$data[0]!='')
      {	  
		  if($i!=0)
		  {		  
          $sql= "INSERT INTO `fuelreg` SET
            `VEHNO` = '$data[0]',
			`FDATE` = STR_TO_DATE('$data[1]','%d/%m/%Y'),
			`OPENKM` = '$data[2]',
			`CLOSEKM`='$data[3]',
			`TOTALKM` = '$data[4]',
			`LITERFILLED` = '$data[5]',
			`CPL` = '$data[6]',
                        `DRIVER` = '$data[7]',
                         `PETROCARD` = '$data[8]',
                         `AMOUNT` = '$data[9]',
                         `CARDNO` = '',
                         `FUELVOL` = '',
			`AVERAGE` = '',
			`REMARKS` = '',
			`FUELFILLEDBY` = '',
			`EXPST` = '',
			`ISCLOSED` = '',
                        `branch` = '$_SESSION[SESS_BRANCH]',
			`fny` = '$fny',
                       `cmp` = '$cmp',
                       `EUSERID` = '$_SESSION[SESS_MEMBER_ID]',
			`EntryDate` = CURDATE();";
			if(!mysqli_query($con,$sql))
		  {	
		  $err=1;
		  $temp=mysqli_error($con)."\n";
		  $inserterr.=$temp;
		 	
			}
		  }//if ends
			
			
			$i++;
      }// while ends
		} //if fuel ends
		else if($_GET[mode]=='maintain')
		{	
      while ((($data = fgetcsv($handle, 1000, ",")) !== FALSE)&&$data[0]!='')
      {	  
		  if($i!=0)
		  {		  
          $sql= "INSERT INTO `vehdetails` SET
            `VEHNO` = '$data[0]',
			`serdate` = STR_TO_DATE('$data[1]','%d/%m/%Y'),
			`agency` = STR_TO_DATE('$data[2]','%d/%m/%Y'),
			`dlvodom` = '$data[3]',
			`dueodom`='$data[4]',
			`duedate` = STR_TO_DATE('$data[5]','%d/%m/%Y'),
			`PCOST` = '$data[6]',
            `SCOST` = '$data[7]',
			`amount` = '$data[8]',
			`remarks` = '$data[9]',
            `sertype` = '',
			`dlvdate` = STR_TO_DATE('','%d/%m/%Y'),
            `serodom` = '',
            `paid` = '',
            `balance` = '',
			`contno` = '',
			`fny` = '$fny',
            `cmp` = '$cmp',
            `EUSERID` = '$_SESSION[SESS_MEMBER_ID]',
			`EntryDate` = CURDATE();";
			if(!mysqli_query($con,$sql))
		  {	
		  $err=1;
		  $temp=mysqli_error($con)."\n";
		  $inserterr.=$temp;
		 	
			}
		  }//if ends
			
			
			$i++;
      }// while ends
		} //if maintain ends
	
	else if($_GET[mode]=='payroll')
		{	
      while ((($data = fgetcsv($handle, 1000, ",")) !== FALSE)&&$data[0]!='')
      {	  
		  if($i!=0)
		  {		  
          $sql= "INSERT INTO `musterroll` SET
                   `EntryID` = 'NULL',
		  `Ecode` = '$data[0]',
                  `Etype` = '$data[1]',
		  `Pdate` = STR_TO_DATE('$data[2]','%d/%m/%Y'),
		  `Intime` = '$data[3]',
		  `Outtime` = '$data[4]',
                  `Tottime` = '$data[5]',
		  `det1` = '$data[6]',
		  `det2` = '$data[7]',
                  `det3` = '$data[8]',
                  `det4` = '$data[9]',
                  `det5` = '$data[10]',
                  `det6` = '$data[11]',
	          `det7` = '$data[12]',
	          `FNY` = '$fny',
                  `CMP` = '$cmp';";
			if(!mysqli_query($con,$sql))
		  	{	
			  $err=1;
			  $temp=mysqli_error($con)."\n";
			  $inserterr.=$temp;
		 	
			}
		  }//if ends
			
			
			$i++;
      }// while ends
		} //if payroll ends	
		
		else if($_GET[mode]=='musterroll')
		{	
      while ((($data = fgetcsv($handle, 1000, ",")) !== FALSE)&&$data[0]!='')
      {	  
		  if($i!=0)
		  {		  
          $sql= "INSERT INTO `musterroll` SET
            `Ecode` = '$data[0]',
            `Etype` = '$data[1]',
			`Pdate`=STR_TO_DATE('$data[2]','%d/%m/%Y'),
            `rdate` = STR_TO_DATE('$data[3]','%d/%m/%Y'),,
            `Intime` = '$data[4]',
            `Outtime` = '$data[5]',
            `Tottime` = '$data[6]',
            `det1` = '$data[7]',
            `det2` = '$data[8]',
			`det3` = '$data[9]',
			`det4` = '$data[10]',
			`det5` = '$data[11]',
            `det6` = '$data[12]',
            `det7` = '$data[13]',
            `det8` = '$data[14]',
            `det9` = '$data[15]',
			`fny` = '$fny',
            `cmp` = '$cmp';";
			if(!mysqli_query($con,$sql))
		  {	
		  $err=1;
		  $temp=mysqli_error($con)."\n";
		  $inserterr.=$temp;
		 	
			}
		  }//if ends
			
			
			$i++;
      }// while ends
		} //if musterroll ends	

			else if($_GET[mode]=='payroll')
		{	
      while ((($data = fgetcsv($handle, 1000, ",")) !== FALSE)&&$data[0]!='')
      {	  
		  if($i!=0)
		  {		  
          $sql= "INSERT INTO `musterroll` SET
                   `EntryID` = 'NULL',
		  `Ecode` = '$data[0]',
                  `Etype` = '$data[1]',
		  `Pdate` = STR_TO_DATE('$data[2]','%d/%m/%Y'),
		  `Intime` = '$data[3]',
		  `Outtime` = '$data[4]',
                  `Tottime` = '$data[5]',
		  `det1` = '$data[6]',
		  `det2` = '$data[7]',
                  `det3` = '$data[8]',
                  `det4` = '$data[9]',
                  `det5` = '$data[10]',
                  `det6` = '$data[11]',
	          `det7` = '$data[12]',
	          `FNY` = '$fny',
                  `CMP` = '$cmp';";
			if(!mysqli_query($con,$sql))
		  	{	
			  $err=1;
			  $temp=mysqli_error($con)."\n";
			  $inserterr.=$temp;
		 	
			}
		  }//if ends
			
			
			$i++;
      }// while ends
		} //if payroll ends	
		
		else if($_GET[mode]=='bookingsync')
		{	
			$table = $bookingreg;	
	   while ((($data = fgetcsv($handle, 1000, ",")) !== FALSE)&&$data[0]!='')
		{
			  
			if($i!=0)
			{		
  				
              $sql_qry = "select * from $table  WHERE `bookid` = '$data[0]' and syncfg = 1";
			
			$sql_res = mysqli_query($con,$sql_qry);
			echo "$sql_qry";	
			if(mysqli_num_rows($sql_res) == 1)
			{	
					
			  $result = mysqli_query($con,"SHOW COLUMNS FROM ".$table);
			  $j = 0;
			  if (mysqli_num_rows($result) > 0) {
			  $sql = "update  $table set ";
			  while ($row = mysqli_fetch_assoc($result)) {
			  $sql .= " `$row[Field]` = '$data[$j]',";
			  $j++;
			  }
			  $sql=rtrim($sql, ",");
				$sql.=" WHERE `bookid` = '$data[0]'";
				
				if($data[j]==1)
				continue;//to continue the loop if no change is done in the local database
			  }
			}//if record present
			else
				{
				 $result = mysqli_query($con,"SHOW COLUMNS FROM ".$table);
			  $j = 0;
			  if (mysqli_num_rows($result) > 0) {
			  $sql = "insert into $table set ";
			  while ($row = mysqli_fetch_assoc($result)) {
			  $sql .= " `$row[Field]` = '$data[$j]',";
			  $j++;
			  }
			  $sql=rtrim($sql, ",");
				if($data[j]==1)
				continue;//to continue the loop if no change is done in the local database
			  }
				} // if recored not present
			  if(!mysqli_query($con,$sql))
			{	
			  $err=1;
			  $temp=mysqli_error($con)."\n";
			  $inserterr.=$sql;
			  
				}
		  } // ends if($i!=0)
		  $i++;
		  } // ends while ((($data = fgetcsv($handle, 1000, ",")) !== FALSE)&&$data[0]!='')

		} //if bookingsync ends	
		
      fclose($handle);
  }//if ends

  // Insert into database
  
 if($err==0)
  {
	//echo  $_FILES['userfile']['tmp_name'];
	 header("location: adminpage.php?mode=upload-complete");

	 
	  }
	  else  if($err==1)
	    {
		//echo mysqli_error($con);
	 header("location: adminpage.php?mode=".mysqli_real_escape_string($con,$inserterr));
	 
	  }
	  
	 else  if($err==2)
	    {
		//echo mysqli_error($con);
	 header("location: adminpage.php?mode=Invalid File Format Only csv accepted..");
	 
	  } 

}// outer if ends
?>
