<?php
session_start();
//php_spreadsheet_export.php
require_once('calendar/classes/tc_calendar.php');


include 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;


//$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");



$servername = "13.232.34.218";
$username = "Tarun";
$password = "Tarun@1234";
$dbname = "EMPMILL12";

$connect = new mysqli($servername, $username, $password, $dbname);


if(isset($_POST["export"]))
{
  $file = new Spreadsheet();

$_SESSION["favanimal"]=2;

  $cmp=$_SESSION["favanimal"];


$cmp=$_SESSION["favanimal"];


$sql="select max(doff_date) mxdt from daily_frame_details where company_id=".$cmp;
  $resulta = $connect->query($sql);
 while($rowa = $resulta->fetch_assoc()) 
  {
	$mxdt=$rowa["mxdt"];
//	echo "mxdate".$mxdt;

  }



$query = "select * from daily_frame_details where company_id=".$cmp."  and doff_date='".$mxdt."' order by frame_no";
//echo $query;

$result = $connect->query($query);



  $active_sheet = $file->getActiveSheet();

   
   
  
 
 // $file->getActiveSheet()->getColumnDimension('A')->setWidth(10); 
 // $file->getActiveSheet()->getColumnDimension('B')->setWidth(40); 


  $active_sheet->setCellValue('A1', 'Last Upload Date ');
  $active_sheet->setCellValue('b1', 'Frame No');
  $active_sheet->setCellValue('c1', 'Speed ');
  $active_sheet->setCellValue('d1', 'Kgs/Spindle ');
    
  
  $stdat=date('d-m-Y');
 

  $count = 2;



//  foreach($result as $row)
 while($row = $result->fetch_assoc()) 


  {
	  
    $active_sheet->setCellValue('A' . $count, $mxdt);
    $active_sheet->setCellValue('B' . $count, $row["frame_no"]);
    $active_sheet->setCellValue('C' . $count, $row["frame_speed"]);
    $active_sheet->setCellValue('D' . $count, $row["kgs_spindle"]);
 	
	
	

    $count = $count + 1;
  }

//for ($i = 'A'; $i !=  $file->getActiveSheet()->getHighestColumn(); $i++) {
    $file->getActiveSheet()->getColumnDimension('A')->setAutoSize(TRUE);
    $file->getActiveSheet()->getColumnDimension('B')->setAutoSize(TRUE);
    $file->getActiveSheet()->getColumnDimension('C')->setAutoSize(TRUE);
    $file->getActiveSheet()->getColumnDimension('D')->setAutoSize(TRUE);
  

//}

//$sheet->getProtection()->setSheet(true);
$c=$count-1;

$file->getActiveSheet()->getProtection()->setSheet(true);
$file->getDefaultStyle()->getProtection()->setLocked(false);
$active_sheet->getStyle('A1:A'.$c)->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED);
$active_sheet->getStyle('B1:B'.$c)->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED);
 

  $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($file, $_POST["file_type"]);

  $file_name = 'Daily_frame_data_'.$stdat.'.' . strtolower($_POST["file_type"]);

  $writer->save($file_name);

  header('Content-Type: application/x-www-form-urlencoded');

  header('Content-Transfer-Encoding: Binary');

  header("Content-disposition: attachment; filename=\"".$file_name."\"");

  readfile($file_name);

  unlink($file_name);

  exit;

} else

$_SESSION["favanimal"]=2;
	
$cmp=2;
$query = "SELECT concat(group_code,item_code) itemcode,item_desc,uom_code,location FROM itemmaster where op_2018=1 and company_id=$cmp order by group_code,item_code";

//$statement = $connect->prepare($query);

//$statement->execute();

//$result = $statement->fetchAll();

//$result = $connect->query($query);

	
if(isset($_POST["upload"]))
{

 // $dtt=
    
//echo "uploading  111";

$dtt=$_POST["date1"];
if (strlen($dtt)==0) {
//		exit;
}	
	
	
    $file_mimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

//echo "uploading  112";
        
 //   if(isset($_FILES['file']['name']) && in_array($_FILES['file']['type'], $file_mimes)) {
      
        $arr_file = explode('.', $_FILES['file']['name']);
        $extension = end($arr_file);
		$file_type="Xlsx";

        if('csv' == $extension) {
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
        } else {
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        }
 
        $spreadsheet1 = $reader->load($_FILES['file']['tmp_name']);

		$worksheet = $spreadsheet1->getActiveSheet(2);
        $sheetData = $spreadsheet1->getActiveSheet()->toArray();

//echo "uploading";


    
 
 
	//}
 
//$query = "SELECT concat(group_code,item_code) itemcode,item_desc,uom_code,location FROM itemmaster where group_code='116' order by group_code,item_code";
//op_2018=1 and company_id=$cmp order by group_code,item_code";

//$statement = $connect->prepare($query);

//$statement->execute();

//$result = $statement->fetchAll();

//$result = $connect->query($query);


}


?>


<head>
<script language="javascript" src="calendar.js"></script>
</head>

<head>
<link rel="stylesheet" type="text/css" href="style.css">
</head> 


 
 <title>Budget Data Upload</title>
  <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
  <link rel="stylesheet" href="/resources/demos/style.css">
  <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
  <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
  <script>
  $( function() {
    $( "#datepicker" ).datepicker({
      
	  showOtherMonths: true,
      selectOtherMonths: true
	  
	  
    });
	
	      $( "#datepicker" ).datepicker( "option", "dateFormat", "dd-mm-yy" );

		      $( "#anim" ).on( "change", function() {
      $( "#datepicker" ).datepicker( "option", "showAnim", "fadeIn" );
    });
		  
  } );
  </script>

<?php
	//	$vv=$_GET["value"];
		

//                    <td bgcolor="#9FE2BF">'.$dn.'</td>


				?>

		<br></br>

<p></p>



<br></br>

 
 



 
       <head>
     <title>Upload Frame Data</title>
     <meta name="viewport" content="width=device-width, initial-scale=1.0">
     <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
   </head>
 <form method="post" enctype="multipart/form-data">

</html>
   <body>
     <div class="container">
      <br />
      <h3 align="center">Upload Frame  Data</h3>
      <br />
        <div class="panel panel-default">
          <div class="panel-heading">
            <form method="post">
              <div class="row">
                <div class="col-md-6">Effective Date  : <input type="text"  autocomplete=off;   name="date1" id="datepicker"></div>
                <div class="col-md-4">
                  
			      <input type="file" name="file" />
                  <select name="file_type" class="form-control input-sm">
                    <option value="Xlsx">Xlsx</option>
                   </select>


                 </div>
                <div class="col-md-2">
					 <input type="submit" name="upload" value="Upload" />
                  <input type="submit" name="export" class="btn btn-primary btn-sm" value="Template" />
					 <input type="submit" name="C/F Data" value="cfdata" />

                </div>
              </div>
          <div class="panel-body">
          <div class="table-responsive">
           <table class="table table-striped table-bordered">
		   				

                <tr>
				
                  <th>Sl No </th>
                  <th>Date </th>
                  <th>Frame No</th>
                  <th>Speed</th>
                  <th>Kgs_spindle</th>
 				  
                </tr>
                   <?php
				   $cnt=1;
				$cnn=1;	
				$sqlm="insert into daily_frame_details ( doff_date,company_id,frame_no,frame_speed,kgs_spindle ) values ";
				   $sqla="";
  if (!empty($sheetData)) {
            for ($i=1; $i<count($sheetData); $i++) { //skipping first row
                $dt = $sheetData[$i][0];
                $bc = $sheetData[$i][1];
				$rg=$sheetData[$i][2];
				$sid= $sheetData[$i][3];

//echo $bc."<br>";
 
				$mm=1;
			 		



$mm=1;
 if ($mm>0) {
                   echo '
                  <tr>
                    <td bgcolor="#9FE2BF">'.$cnn.'</td>
                    <td bgcolor="#9FE2BF">'.$dt.'</td>
                    <td bgcolor="#9FE2BF">'.$bc.'</td>
                    <td bgcolor="#9FE2BF">'.$rg.'</td>
				    <td bgcolor="#9FE2BF">'.$sid.'</td>
 			
					
                  </tr>
                  ';

				$dtt=$_POST["date1"];
				$date1=date("Y-m-d", strtotime($dtt) );

 				
		//		budget_date,company_id,region,branch_id,desig_id,Budget_head_count,Budget_shifts,branch_code,is_active
   if ($cnn==1) {
     $sqla=$sqla." ( '".$date1."',2,'".$bc."',".$rg.",".$sid.")"  ;
 } else { 
     $sqla=$sqla.", ( '".$date1."',2,'".$bc."',".$rg.",".$sid.")"  ;
  }	 
 
 //echo $sqla;
  $cnn++; 
//echo 'count '.$cnn."<br>";

}

//echo 'count '.$cnn."<br>";

   
if ($mm==0) {
                   echo '
                  <tr>
                    <td bgcolor="red">'.$cnn.'</td>
                    <td bgcolor="red">'.$dt.'</td>
                    <td bgcolor="red">'.$bc.'</td>
                    <td bgcolor="red">'.$rg.'</td>
				    <td bgcolor="red">'.$sid.'</td>
 			
					
                  </tr>
                  ';
		$cnt=0;
}
   
   
			
				}
			 

            
 

  if ($cnt>0) {

/*	  
	         echo '
                  <tr>
                    <td bgcolor="red">'.$cnn.'</td>
                    <td bgcolor="red">'.$dt.'</td>
                    <td bgcolor="red">'.$sqla.'</td>
 			
					
                  </tr>
                  ';
*/

if (strlen($dtt)==0) {
echo '<script>alert("Please Enter Effective Date")</script>';
 
}	
else {
 

$sql1="delete from  daily_frame_details where doff_date='".$date1."'";
mysqli_query($connect, $sql1);	  

$sqla=$sqlm.$sqla;
//echo $sqla;	  
mysqli_query($connect, $sqla);	  


} 
 }


				}

 
  //
  
                ?>

              </table>
          </div>
          </div>
        </div>
     </div>
      <br />
      <br />
  </body>
</html>