<?php include_once('../../database_connection.php');

ini_set('error_reporting', E_ALL);
ini_set('display_errors', true);

require_once __DIR__.'/../src/SimpleXLSX.php';

echo '<h1>XLSX to HTML</h1>';
function checkdup($name,$connection)
{
	$sql="select * from inventory where name='$name'";
	$result=odbc_exec($connection,$sql);
	if(odbc_num_rows($result)>0)
	{
		return true;
	}
	else
	{
		return false;
	}
}

if (isset($_FILES['file'])) {
	
	if ( $xlsx = SimpleXLSX::parse( $_FILES['file']['tmp_name'] ) ) {

		echo '<h2>Parsing Result</h2>';
		echo '<table border="1" cellpadding="3" style="border-collapse: collapse">';

		$dim = $xlsx->dimension();
		$cols = $dim[0];

		foreach ( $xlsx->rows() as $k => $r ) {
			//		if ($k == 0) continue; // skip first row
			//if($k==3)break;
$Name="";
$Active="1";
$CompanyID="0";	
$Under="";
$Details="";
$price="0";			
			echo '<tr>';
			for ( $i = 0; $i < $cols; $i ++ ) {
				$coldata=( isset( $r[ $i ] ) ? $r[ $i ] : '&nbsp;' );
				echo '<td>' . $coldata . '</td>';
if($i==1)$Name=$coldata;
if($i==2)$Active=$coldata;
if($i==3)$CompanyID=$coldata;	
if($i==4)$Under=$coldata;
if($i==5)$Details=$coldata;
if($i==6)$price=$coldata;
if($i==7)$cost=$coldata;



			}
			echo '</tr>';
			//get ID 
			$ID=(getmaxId("inventory","ID",$connection)+1);

			$KeyField="key-0001";
		    $where="ProductBaseType='Item'";	
           	$KeyField=generateCode("inventory","KeyField",$where,$connection)+1;
           	//getnamesingle("InvoiceMaster","count(*)","TableID","71",$connection)+1;

           	$KeyField2=str_pad($KeyField,5,"0",STR_PAD_LEFT);
           	$KeyField="SRV-".$KeyField2;
 
			// get KeyField 
					if ($k == 0 || $Active=="" || $Name=="") continue; // skip first row
					if(checkdup($Name,$connection)==false)
					{
						echo $Name."<br>";
		$sql="insert into inventory (ID,Degree,[group],AdditionalInfo1,ProductBaseType,Name,Under,Details,StandardPrice1,StandardPrice2,MaxQtyLevel,KeyField,Active,CompanyID) 
values($ID,2,0,'','Service','".utf8_encode($Name)."',$Under,'".utf8_encode($Details)."',$cost,$price,1,'$KeyField',$Active,$CompanyID)";
		odbc_exec($connection,$sql);
				}
		}
		echo '</table>';
	} else {
		echo SimpleXLSX::parseError();
	}
}
echo '<h2>Upload form</h2>
<form method="post" enctype="multipart/form-data">
*.XLSX <input type="file" name="file"  />&nbsp;&nbsp;<input type="submit" value="Parse" />
</form>';
