<?php



require('library/php-excel-reader/excel_reader2.php');

require('library/SpreadsheetReader.php');

require('dbconfig.php');

$mysqli = mysqli_connect($servername, $username, $password, $dbname);
 
// Check connection
 
if (!$mysqli) {
 
    die("Connection failed: " . mysqli_connect_error());
 
}


if(isset($_POST['Submit'])){



  $mimes = ['application/vnd.ms-excel','text/xls','text/xlsx','application/vnd.oasis.opendocument.spreadsheet'];

  if(in_array($_FILES["file"]["type"],$mimes)){



    $uploadFilePath = 'uploadexcel/'.basename($_FILES['file']['name']);

    move_uploaded_file($_FILES['file']['tmp_name'], $uploadFilePath);

// mengambil isi file xls
$data = new Spreadsheet_Excel_Reader($_FILES['filepegawai']['name'],false);
// menghitung jumlah baris data yang ada
$jumlah_baris = $data->rowcount($sheet_index=0);
echo 'Jumlah Baris :'.$jumlah_baris;

    $Reader = new SpreadsheetReader($uploadFilePath);

    
    // $no='GR-'.date('Y/m/d H:i:s');
    $no='StockOpname';
    $totalSheet = count($Reader->sheets());



    echo "You have total ".$totalSheet." sheets".



    $html="<table border='1'>";

    $html.="<tr><th>item_code</th><th>item_desc</th><th>Qtystk</th</tr>";



    /* For Loop for all sheets */

    for($i=0;$i<$totalSheet;$i++){



      $Reader->ChangeSheet($i);

         $query_del = "delete from Trans1sub where typetrans='STK'";
         $mysqli->query($query_del);     

      foreach ($Reader as $Row)

      {

        $html.="<tr>";

        $kode = isset($Row[0]) ? $Row[0] : '';

        $nama = isset($Row[1]) ? $Row[1] : '';
        $stk = isset($Row[2]) ? $Row[2] : '';
     

 

         
        $html.="<td>".$kode."</td>";
        $html.="<td>".$nama."</td>";
        $html.="<td>".$stk."</td>";
   

        $html.="</tr>";


       
        $query = "insert into Trans1sub(kode_cabang,item_code,item_desc,qty,user_name,typetrans,notrans_link,Harga_beli,harga_jual,disc_val) values ('HO','$kode','$nama','$stk','irm','STK','StockOpname',0,0,0)";

     
           
      
       $mysqli->query($query);
       

    $query_del2 = "delete from Trans1sub where LENGTH(item_code)=0 OR item_code='item_code'"; 
    $mysqli->query($query_del2);  

       }



    }

 
    
    $html.="</table>";

    echo $html;

    echo "<br />Data Inserted in dababase";



  }else { 

    die("<br/>Sorry, File type is not allowed. Only Excel file."); 

  }



}



?>