<?php
use Box\Spout\Reader\ReaderFactory;
use Box\Spout\Common\Type;
//require_once ('connect.php');
//require('../config/pdocon.php');
$dsn = "mysql:host=remotemysql.com;dbname=YwicdZ1XXj";
$user = "YwicdZ1XXj";
$passwd = "3paN4VTH1O";
$connect = new PDO($dsn, $user, $passwd);
//$connect = new PDO('pgsql:host=localhost;dbname=postgres', 'postgres', 'Kaushik@1610');
//$connect = new PDO('sqlite:../db/db_student.sqlite3');	
$connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
require_once ('Spout/Autoloader/autoload.php');
//====================================================================
$filePath = ('item_mst.xlsx');
$reader = ReaderFactory::create(Type::XLSX);
$reader->open($filePath);
$extension = pathinfo($filePath, PATHINFO_EXTENSION);
$result = $connect->query("DELETE FROM item_mst");
if (($extension == 'xlsx' || $extension == 'xls')){
$count = 0;
	foreach ($reader->getSheetIterator() as $sheet){
		foreach ($sheet->getRowIterator() as $row){
			if($count > 0){
				$id = $count;
				$partno = $row[1];
				$description_one = $row[2];
				$description_two = $row[3];
				$hsn_code = $row[4];
				$list = $row[5];
				$moq = $row[6];
				$boxqty = $row[7];
				$cost = $row[8];
				$disc_per = $row[9];
				$disc_amt = $row[10];
				$mrp = $row[11];
				$sgst_per = $row[12];
				$sgst_amt = $row[13];
				$cgst_per = $row[14];
				$cgst_amt = $row[15];
				$igst_per = $row[16];
				$igst_amt = $row[17];
				$op_stk = $row[18];
				$stk_qty = $row[19];
				$stk_val = $row[20];
				$unit = $row[21];
				$ly_sales = $row[22];
				$cy_sales = $row[23];
				$pending = $row[24];
				$dispatch1 = $row[25];
				$min_qty = $row[26];
				$max_qty = $row[27];
				$reord_qty = $row[28];
				$segment = $row[29];
				$remarks = $row[30];
				$qry = "INSERT INTO item_mst(id,partno,description_one,description_two,hsn_code,list,moq,boxqty,cost,disc_per,disc_amt,mrp,sgst_per,sgst_amt,cgst_per,cgst_amt,igst_per,igst_amt,op_stk,stk_qty,stk_val,unit,ly_sales,cy_sales,pending,dispatch1,min_qty,max_qty,reord_qty,segment,remarks) VALUES ('$id','$partno','$description_one','$description_two','$hsn_code','$list','$moq','$boxqty','$cost','$disc_per','$disc_amt','$mrp','$sgst_per','$sgst_amt','$cgst_per','$cgst_amt','$igst_per','$igst_amt','$op_stk','$stk_qty','$stk_val','$unit','$ly_sales','$cy_sales','$pending','$dispatch1','$min_qty','$max_qty','$reord_qty','$segment','$remarks')";
				$result = $connect->query($qry);
			}
			$count++;
		}
	}
}
$reader->close();
header ("Location: index.php");
//====================================================================
/*

if(!empty($_FILES['excelfile']['name']))
{
    // Get File extension eg. 'xlsx' to check file is excel sheet
    $pathinfo = pathinfo($_FILES['excelfile']['name']);
    // check file has extension xlsx, xls and also check
    // file is not empty
    if (($pathinfo['extension'] == 'xlsx' || $pathinfo['extension'] == 'xls') && $_FILES['excelfile']['size'] > 0 ){
        
		$file = $_FILES['excelfile']['tmp_name'];

        // Read excel file by using ReadFactory object.
        $reader = ReaderFactory::create(Type::XLSX);
		//$reader=ReaderFactory::create(Type::XLS);
        //$reader=ReaderFactory::create(Type::CSV);
        // Open file
        $reader->open($file);
        $count = 0;

        // Number of sheet in excel file
        foreach ($reader->getSheetIterator() as $sheet){

            // Number of Rows in Excel sheet
            foreach ($sheet->getRowIterator() as $row){
                // It reads data after header. In the my excel sheet,
                // header is in the first row.
                if($count > 0){
                    // Data of excel sheet
                    $partno = $row[0];
                    $description = $row[1];
					$list = $row[2];
					$moq = $row[3];
					$box = $row[4];
                    //Here, You can insert data into database.
                    $qry = "INSERT INTO item_mst(partno,description,list,moq,box) VALUES ('$partno','$description','$list','$moq','$box')";
                    $res = mysqli_query($con,$qry);
                }
                $count++;
            }
        }

        if($res){
            echo "Your file Uploaded Successfull";
        }
        else
        {
            echo "Your file Uploaded Failed";
        }

        // Close excel file
        $reader->close();
    }
    else
    {
        echo "Please Choose only Excel file";
    }
}
else
{
    echo "File is Empty"."<br>";
    echo "Please Choose Excel file";
}
*/
?>
