<?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');
    $totalSheet = count($Reader->sheets());



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



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

    $html.="<tr><th>Kode_cabang</th><th>Kode</th><th>Nama</th><th>Harg_Jual</th><th>Harg_Beli</th></tr>";



    /* For Loop for all sheets */

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



      $Reader->ChangeSheet($i);



      foreach ($Reader as $Row)

      {

        $html.="<tr>";

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

        $nama = isset($Row[1]) ? $Row[1] : '';
        $qty_gr = isset($Row[2]) ? $Row[2] : '';
        $harga_jual = isset($Row[3]) ? $Row[3] : '';
        $harga_beli = isset($Row[4]) ? $Row[4] : '';

 

         
        $html.="<td>".$kode."</td>";
        $html.="<td>".$nama."</td>";
        $html.="<td>".$qty_gr."</td>";
        $html.="<td>".$harga_jual."</td>";
        $html.="<td>".$harga_beli."</td>";

        $html.="</tr>";


        $query_del = "delete from Trans1sub where item_desc='nama'";
        $query = "insert into Trans1sub(kode_cabang,typetrans,notrans_link,item_code,item_desc,qty,harga_jual,harga_beli,user_name) values ('HO','GR','$no','$kode','$nama','$qty_gr','$harga_jual','$harga_beli','irm')";


       $mysqli->query($query_del);     
       $mysqli->query($query);

       }



    }



    $html.="</table>";

    echo $html;

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



  }else { 

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

  }



}



?>