<?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 = "vowdev_sw";

$connect = new mysqli($servername, $username, $password, $dbname);


if(isset($_POST["export"]))
{
  $file = new Spreadsheet();

$_SESSION["favanimal"]=119;

  $cmp=$_SESSION["favanimal"];


$cmp=$_SESSION["favanimal"];


$sql="select max(budget_date) mxdt from budget_manpower_table";
  $resulta = $connect->query($sql);
 while($rowa = $resulta->fetch_assoc()) 
  {
	$mxdt=$rowa["mxdt"];
//	echo "mxdate".$mxdt;

  }



$query = "select bmt.branch_code,region,bmt.branch_id,branch_name,Budget_head_count,Budget_shifts,desig_id,desig from budget_manpower_table bmt,branch_master bm, designation dsg
where bmt.branch_id=bm.branch_id and bmt.desig_id=dsg.id and bmt.company_id=".$cmp."  and bmt.budget_date='".$mxdt."' order by branch_name";
//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', 'Date ');
  $active_sheet->setCellValue('b1', 'Branch Code');
  $active_sheet->setCellValue('c1', 'Region ');
  $active_sheet->setCellValue('d1', 'Site Id');
  $active_sheet->setCellValue('e1', 'Site Name');
  $active_sheet->setCellValue('f1', 'Budget Head Count');
  $active_sheet->setCellValue('g1', 'Budget Shifts');
  $active_sheet->setCellValue('h1', 'Desig Id');
  $active_sheet->setCellValue('i1', 'Designation');
   
  
  $stdat=date('d-m-Y');
 

  $count = 2;



//  foreach($result as $row)
 while($row = $result->fetch_assoc()) 


  {
	  
    $active_sheet->setCellValue('A' . $count, $stdat);
    $active_sheet->setCellValue('B' . $count, $row["branch_code"]);
    $active_sheet->setCellValue('C' . $count, $row["region"]);
    $active_sheet->setCellValue('D' . $count, $row["branch_id"]);
    $active_sheet->setCellValue('e' . $count, $row["branch_name"]);
//	  $active_sheet->setCellValue('f' . $count, $row["Budget_head_count"]);
//	  $active_sheet->setCellValue('g' . $count, $row["Budget_shifts"]);
	  $active_sheet->setCellValue('h' . $count, $row["desig_id"]);
	  $active_sheet->setCellValue('i' . $count, $row["desig"]);
	
	
	

    $count = $count + 1;
  }



//$sheet->getProtection()->setSheet(true);

$file->getActiveSheet()->getProtection()->setSheet(true);
$file->getDefaultStyle()->getProtection()->setLocked(false);
$active_sheet->getStyle('A1:A5')->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED);


  $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($file, $_POST["file_type"]);

  $file_name = time() . '.' . 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";
	
	
    $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>jQuery UI Datepicker - Dates in other months</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="green">'.$dn.'</td>


				?>

		<br></br>

<p></p>



<br></br>

 
 



 
       <head>
     <title>Upload Budget 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">Export Data From Mysql to Excel using PHPSpreadsheet</h3>
      <br />
        <div class="panel panel-default">
          <div class="panel-heading">
            <form method="post">
              <div class="row">
                <div class="col-md-6">From 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>
                    <option value="Xls">Xls</option>
                    <option value="Csv">Csv</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" />
                </div>
              </div>
          <div class="panel-body">
          <div class="table-responsive">
           <table class="table table-striped table-bordered">
		   				

                <tr>
                  <th>Date </th>
                  <th>Branch Code</th>
                  <th>Region</th>
                  <th>Site Id</th>
				  <th>Site Name</th>
				  <th>Budget Head Count</th>
				  <th>Budget Shifts</th>
				  <th>Desig Id</th>
				  <th>Designation</th>
				  
                </tr>
                   <?php
  if (!empty($sheetData)) {
            for ($i=3; $i<count($sheetData); $i++) { //skipping first row
                $dt = $sheetData[$i][0];
                $bc = $sheetData[$i][1];
				$rg=$sheetData[$i][2];
				$sid= $sheetData[$i][3];
				$snm=$sheetData[$i][4];
				$bhc=$sheetData[$i][5];
				$bsf=$sheetData[$i][6];
				$dsgid=$sheetData[$i][7];
				$dsg=$sheetData[$i][8];

				$mm=1;
				if (strlen($sid)==0) {
					$sid=0;
					$sql="select branch_id from branch_master where branch_name='".$snm."'";
					$resulta = $connect->query($sql);
					while($rowa = $resulta->fetch_assoc()) 
					{
						$sid=$rowa["branch_id"];
 
					}
					


				}
				if (strlen($dsgid)==0) {

					$dsgid=0;
					$sql="select id from designation where desig='".$dsg."'";
					$resulta = $connect->query($sql);
					while($rowa = $resulta->fetch_assoc()) 
					{
						$dsgid=$rowa["id"];
 
					}


				}

					if ($sid==0) {
						$mm=0;
					}
					if ($dsgid==0) {
						$mm=0;
					}
if ($mm>0) {
                   echo '
                  <tr>
                    <td bgcolor="green">'.$dt.'</td>
                    <td bgcolor="green">'.$bc.'</td>
                    <td bgcolor="green">'.$rg.'</td>
				    <td bgcolor="green">'.$sid.'</td>
				    <td bgcolor="green">'.$snm.'</td>
				    <td bgcolor="green">'.$bhc.'</td>
				    <td bgcolor="green">'.$bsf.'</td>
				    <td bgcolor="green">'.$dsgid.'</td>
			        <td bgcolor="green">'.$dsg.'</td>
			
					
                  </tr>
                  ';

				$dtt=$_POST["date1"];
				
				$sql="insert into budget_manpower_table ( budget_date,company_id,region,branch_id,desig_id,Budget_head_count,Budget_shifts,branch_code ) values 		
				( ";


}
   
if ($mm==0) {
                   echo '
                  <tr>
                    <td bgcolor="red">'.$dt.'</td>
                    <td bgcolor="red">'.$bc.'</td>
                    <td bgcolor="red">'.$rg.'</td>
				    <td bgcolor="red">'.$sid.'</td>
				    <td bgcolor="red">'.$snm.'</td>
				    <td bgcolor="red">'.$bhc.'</td>
				    <td bgcolor="red">'.$bsf.'</td>
				    <td bgcolor="red">'.$dsgid.'</td>
			        <td bgcolor="red">'.$dsg.'</td>
			
					
                  </tr>
                  ';
}
   
   
   
			} 
 
  }
                ?>

              </table>
          </div>
          </div>
        </div>
     </div>
      <br />
      <br />
  </body>
</html>