<?
session_start();
$brcode=$_SESSION['brcode'];
$user=$_SESSION['user'];
if(strlen($brcode)<=0)
{
 print "<script> alert('Session timed out'); parent.document.location='../index.html'; </script>";
 return;
}
include("opendb.php");
include("test.php");
if(isset($_POST['show']))
{
?>
<style>
td,th,body{font-size:14;font-family:'arial narrow';letter-spacing:1}
</style>
<center>
<?
$d1=$_REQUEST['d1'];
$m1=$_REQUEST['m1'];
$y1=$_REQUEST['y1'];

$d2=$_REQUEST['d2'];
$m2=$_REQUEST['m2'];
$y2=$_REQUEST['y2'];
 $dfrom=$y1.'-'.$m1.'-'.$d1;
 $dto=$y2.'-'.$m2.'-'.$d2;

$ordby=$_REQUEST['ordby'];

$brres=mysqli_query($db,"select * from branch where brcode='$brcode'");
$brrow=mysqli_fetch_array($brres);
?>
<table>
<TR><TD COLSPAN=2 ALIGN=CENTER><b> PSN AUTOMOBILES P LTD </b><BR>
	 <?print $brrow[braddr].",".$brrow[brcity].",<br><u> PH - ".$brrow[brphone]?></u>
TIN: <?print $brrow[tin]?>, CST: <?print $brrow[cstno]?>&nbsp;
</td></tr>
<tr><td COLSPAN=2 ALIGN=CENTER>
<?print "<h4><br>SPARE INVOICE REPORT - VAT</H4>";

print " from ".datetohtml($dfrom).' to '.datetohtml($dto);
?>
</td></tr>
</table>

<?

        $grand_gtot1=0;
        $grand_ttx=0;
        $grand_tcess=0;
        $grand_ttot=0;
        $grand_ttrnd=0;
        $grand_rndtot=0;
        mysqli_query($db,"delete from excisestock_kerala");
        mysqli_query($db,"delete from excisestock1_kerala");
        mysqli_query($db,"delete from excisesales1_kerala");
        mysqli_query($db,"delete from excisepurchase_kerala1");
        
        

mysqli_query($db,"insert into excisestock_kerala (partcode,stkqty,pqty,saleqty) select partcode,sum(qty),0,0 from exciseopening_kerala group by partcode ");

mysqli_query($db,"insert into excisestock_kerala (partcode,pqty,stkqty,saleqty) select partcode,sum(qty),0,0 from excisepurchase_kerala  group by partcode  ");

mysqli_query($db,"insert into excisestock_kerala (partcode,saleqty,pqty,stkqty) select partcode,sum(qty),0,0 from excisesale_kerala where partcode in (select partcode from exciseopening_kerala) group by partcode  ");


mysqli_query($db,"insert into excisestock1_kerala (partcode,sqty,pqty,eqty,saleqty) select partcode,sum(stkqty),sum(pqty),0,sum(saleqty) from excisestock_kerala group by partcode ");

//mysqli_query($db,"delete from  excisestock1 where pqty=0 ");

mysqli_query($db,"update excisestock1_kerala set eqty=sqty where sqty<=pqty ");

mysqli_query($db,"update excisestock1_kerala set eqty=pqty where pqty<sqty ");


print "ok";


/*
 $re=mysqli_query($db,"select partcode,sum(eqty) from excisestock1_kerala where saleqty>0 group by partcode order by partcode") or die("ERR:".mysqli_error($db));

while($row=mysqli_fetch_array($re))
{
	$t='stk';
	
	$no=$row[1];
	//	$no=$row[supqty]+$row[inqty]-$row[outqty];
	//print
	
	 $sql="select partcode,partname,qty,rate,cgstp,cgstamt,sgstp,sgstamt,igstp,igstamt,invdate,invno,type,brcode from excisesale_kerala where partcode='$row[partcode]'  order by invdate asc";
	
	$chk=mysqli_query($db,$sql)or die(mysqli_error($db));
	if(mysqli_num_rows($chk)<=0)
	{
		//$sql="select invdate,qty,100,0,0,0,0 from excisepurchase where partcode='$row[partcode]'  order by invdate desc";
		$sql="select invdate,qty,qty*rate,0,0,cgstp from excisesale_kerala where partcode='$row[partcode]'  order by invdate desc";
		$chk=mysqli_query($db,$sql)or die(mysqli_error($db));
		$t='stk';
		$chk=mysqli_query($db,$sql)or die(mysqli_error($db));
		$t='stk';
	}
	if ($no>0)
	{
		while($row1=mysqli_fetch_array($chk))
		{
			$pcode=$row[partcode];
			//print
			 $pname=htmlspecialchars(addslashes($row1[partname]));
			$dt=$row1[invdate];
			$pr=$row1[rate];
			$qt=$row1[qty];
			
			
			if($no>0)
			{ 
				if($qt>$no)
				{
					$qt = $no;
				}
			//	$sq="insert into $tbl2 values('$pcode','$pname','$dt', '$qt','$pr','$dto')";
			//	mysqli_query($db,$sq) or die('err2'.mysqli_error($db));
				
			 print $sq="insert into excisesales1_kerala(partcode,partname,qty,rate,cgstp,cgstamt,sgstp,sgstamt,igstp,igstamt,invdate,invno,type,brcode) values('$row1[partcode]','".addslashes($row1[partname])."','$qt','$row1[rate]','$row1[cgstp]','$row1[cgstamt]','$row1[sgstp]','$row1[sgstamt]','$row1[igstp]','$row1[igstamt]','$row1[invdate]','$row1[invno]','$row1[type]','$row1[brcode]')";
				mysqli_query($db,$sq) or die('err2'.mysqli_error($db));
			}
			else
			{	
				break;
			}
			$no-=$qt;
		}
	
		
	}
}

*/
//purchasae

 $re=mysqli_query($db,"select partcode,sum(eqty),sum(saleqty) from excisestock1_kerala where eqty>0 group by partcode order by partcode") or die("ERR:".mysqli_error($db));

while($row=mysqli_fetch_array($re))
{
	$t='stk';
	if ($row[1]>$row[2])
	{
	 $no=$row[2];   
	}
	else
	{
	$no=$row[1];
	
	
	}
	
	//comment below line if sale is considered.
		$no=$row[1];
	
	//	$no=$row[supqty]+$row[inqty]-$row[outqty];
	//print
	
	 $sql="select dealercode,dealername,invno,invdate,type,partcode,partname,region,qty,ed,edunit,tax,invamt from excisepurchase_kerala where partcode='$row[partcode]'  order by invdate desc";
	
	$chk=mysqli_query($db,$sql)or die(mysqli_error($db));
	if(mysqli_num_rows($chk)<=0)
	{
		//$sql="select invdate,qty,100,0,0,0,0 from excisepurchase where partcode='$row[partcode]'  order by invdate desc";
		$sql="select invdate,qty,qty*rate,0,0,cgstp from excisesale_kerala where partcode='$row[partcode]'  order by invdate desc";
		$chk=mysqli_query($db,$sql)or die(mysqli_error($db));
		$t='stk';
		$chk=mysqli_query($db,$sql)or die(mysqli_error($db));
		$t='stk';
	}
	if ($no>0)
	{
		while($row1=mysqli_fetch_array($chk))
		{
			$pcode=$row[partcode];
			//print
			 $pname=htmlspecialchars(addslashes($row1[partname]));
			$dt=$row1[invdate];
			$pr=$row1[rate];
			$qt=$row1[qty];
			
			
			if($no>0)
			{ 
				if($qt>$no)
				{
					$qt = $no;
				}
			//	$sq="insert into $tbl2 values('$pcode','$pname','$dt', '$qt','$pr','$dto')";
			//	mysqli_query($db,$sq) or die('err2'.mysqli_error($db));
				
			$net = trim($row1[12]);
 $net = str_replace(',', '', $net);
 
  $qty = trim($row1[8]);
 $qty = str_replace(',', '', $qty);
 
 
 $ed= trim($row1[9]);
 $ed = str_replace(',', '', $ed);
 
 $tax= trim($row1[11]);
 $tax = str_replace(',', '', $tax);
 
 $edu= trim($row1[10]);
 $edu = str_replace(',', '', $edu);
 
 $edu = str_replace(',', '', $edu);
	  	print $sq="insert into excisepurchase_kerala1 values ('$row1[0]','".addslashes($row1[1])."','$row1[2]','$row1[3]','$row1[4]','$row1[5]','".addslashes($row1[6])."','$row1[7]',$qt,$ed,$edu,$tax,$net)";
				mysqli_query($db,$sq) or die('err2'.mysqli_error($db));
			}
			else
			{	
				break;
			}
			$no-=$qt;
		}
	
		
	}
}

?>
<link rel="stylesheet" href="../style.css" type="text/css" />
<style>
.noprint, .print {display:none;}
@media screen {
.noprint, .printns {display:block;}
.print {display:none;}
}
@media print {
.print {display:block;}
.noprint, .printns, {display:none;}
}
	th{background-color:steelblue;color:white}
	td{font-size:12}
.right{text-align:right}
</style>
<center>
<table border=1>
<th>Partcode<th>Partname<th>qty<th>
<?

$trantbl=mysqli_query($db,"select * from excisesales1_kerala order by partcode ") or die("ERR:".mysqli_error($db));

while($tran=mysqli_fetch_array($trantbl))
{
	print "<tr><td>$tran[0]</td><td>$tran[1]</td><td align=right>$tran[2]</td><td align=right>$tran[3]</td><td align=right>$tran[4]</td><td align=right>$tran[5]</td><td align=right>$tran[6]</td><td align=right>$tran[7]</td><td align=right>$tran[8]</td><td align=right>$tran[9]</td><td align=right>$tran[10]</td><td align=right>$tran[11]</td><td 
align=right>$tran[12]</td><td align=right>$tran[13]</td></tr>";
}
?>
</table>
<center>
<table border=1>
<th>222Partcode<th>Partname<th>qty<th>
<?

$trantbl=mysqli_query($db,"select * from excisepurchase_kerala1 order by partcode ") or die("ERR:".mysqli_error($db));

while($tran=mysqli_fetch_array($trantbl))
{
	print "<tr><td>$tran[0]</td><td>$tran[1]</td><td align=right>$tran[2]</td><td align=right>$tran[3]</td><td align=right>$tran[4]</td><td align=right>$tran[5]</td><td align=right>$tran[6]</td><td align=right>$tran[7]</td><td align=right>$tran[8]</td><td align=right>$tran[9]</td><td align=right>$tran[10]</td><td align=right>$tran[11]</td><td 
align=right>$tran[12]</td></tr>";
}

//mysqli_query($db,"drop table $tbl");










return;
}// end if
?>

<link rel="stylesheet" href="../style.css" type="text/css" />
<style>
//	tr{background-color:white}
	th{background-color:steelblue;color:white}
	td{font-size:13}
.right{text-align:right};
</style>

<center>
<h3>SPARE INVOICE REPORT -2</H3>
<form action='<?Print $PHP_SELF?>' method='post'>
<table cellspacing=1 cellpadding=1 bgcolor='steelblue'>
<tr><td bgcolor='white' align=center>
<table class=noprint>
<tr><td>Date from </td><td>dd <input type=text name='d1' size=2 value='<?print date("d")?>'>
mm <input type=text name='m1' size=2 value='<?print date("m")?>'>
yy <input type=text name='y1' size=4 value='<?print date("Y")?>'></td></tr>

<tr><td align=right>to </td><td>dd <input type=text name='d2' size=2 value='<?print date("d")?>'>
mm <input type=text name='m2' size=2 value='<?print date("m")?>'>
yy <input type=text name='y2' size=4 value='<?print date("Y")?>'>
</td></tr>
<tr><td>Order By</td><td><select name=ordby><option>Invoice</option><option>Datewise</option></select></td></td></tr>
<tr><td colspan=2 align=center bgcolor='lightsteelblue'><input type=submit value='Show' name='show'></td></tr>

</table>
</td></tr>
</table>
</form>


