<?
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{color:black;font-family:verdana;font-size:11}
 th{color:black;font-family:verdana;font-size:11}
</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 AUTOMOTIVE MARKETING P LTD </b><BR>
	 <?print $brrow['braddr'].",".$brrow['brcity']?><br></u>
PAN: <?print $brrow['tin']?>, GST: <?print $brrow['cstno']?>&nbsp;
</td></tr>
<tr><td COLSPAN=2 ALIGN=CENTER>
<?print "<h4><br>Reconciliation Purchase VECV vs PSN </H4>";

print " from ".implode('-', array_reverse(explode('-', $dfrom))).' to '.implode('-', array_reverse(explode('-', $dto)));
?>
</td></tr>
</table>

<?
        
    
  
 $tbl="sparepurdwreco".$brcode.$user;
mysqli_query($db,"drop table $tbl");
mysqli_query($db,"CREATE TABLE $tbl (invno varchar(200),invdate date,cust varchar(400),brcode varchar(22),invtype varchar(33),28b4 double(10,2) default 0, 14sgst double(10,2) default 0, 14cgst double(12,2) default 0,28igst double(12,2) default 0,18b4 double(12,2) default 0,9sgst double(12,2) default 0,9cgst double(12,2) default 0,18igst double(12,2) default 0,12b4 double(12,2) default 0,6sgst double(12,2) default 0,6cgst double(12,2) default 0,12igst double(12,2) default 0,5b4 double(12,2) default 0,2_5sgst double(12,2) default 0,2_5cgst double(12,2) default 0,5igst double(12,2) default 0,0gst double(12,2) default 0,hand double(12,2) default 0,total double(12,2) default 0,salestype varchar(200),pbillno varchar(200) default 0,purdate date )");
mysqli_query($db,"delete from $tbl");

//      spare purchase start #################################################

$sq2="select  grno,entrydate,suplier,sum(supqty*price) ap,sum(supqty*price)*cgstp/100 sg,sum(supqty*price)*cgstp/100 cg,sum(hand) hnd,sum(tot) tt ,pbillno,purdate from sparepurchase  where brcode='$brcode' and entrydate>='$dfrom' and entrydate<='$dto'  and sgstp='14.00' group by grno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
 $tot=0;
 
 $tot=$res2['ap']+$res2['sg']+$res2['cg'];
 
 
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,28b4,14sgst,14cgst,hand,total,salestype,pbillno,purdate) values('$res2[grno]','$res2[entrydate]','$res2[suplier]','$brcode','SP','$res2[ap]','$res2[sg]','$res2[cg]','$res2[hnd]','$tot','spare','$res2[pbillno]','$res2[purdate]')";
   mysqli_query($db,$sq14) or die('saleinv28cg13 err'.mysqli_error($db));
}


$sq2="select  grno,entrydate,suplier,sum(supqty*price) ap,sum(supqty*price)*cgstp/100 sg,sum(supqty*price)*cgstp/100 cg,sum(hand) hnd,sum(tot) tt ,pbillno,purdate from sparepurchase  where brcode='$brcode' and entrydate>='$dfrom' and entrydate<='$dto'  and sgstp='9.00' group by grno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
 
  $tot=0;
 
 $tot=$res2['ap']+$res2['sg']+$res2['cg'];
 
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,18b4,9sgst,9cgst,hand,total,salestype,pbillno,purdate) values('$res2[grno]','$res2[entrydate]','$res2[suplier]','$brcode','SP','$res2[ap]','$res2[sg]','$res2[cg]','$res2[hnd]','$tot','spare','$res2[pbillno]','$res2[purdate]')";
   mysqli_query($db,$sq14) or die('saleinv18cg err'.mysqli_error($db));
}


$sq2="select  grno,entrydate,suplier,sum(supqty*price) ap,sum(supqty*price)*cgstp/100 sg,sum(supqty*price)*cgstp/100 cg,sum(hand) hnd,sum(tot) tt ,pbillno,purdate from sparepurchase  where brcode='$brcode' and entrydate>='$dfrom' and entrydate<='$dto'  and sgstp='6.00' group by grno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
 
 $tot=0;
 
 $tot=$res2['ap']+$res2['sg']+$res2['cg'];
 
 
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,12b4,6sgst,6cgst,hand,total,salestype,pbillno,purdate) values('$res2[grno]','$res2[entrydate]','$res2[suplier]','$brcode','SP','$res2[ap]','$res2[sg]','$res2[cg]','$res2[hnd]','$tot','spare','$res2[pbillno]','$res2[purdate]')";
   mysqli_query($db,$sq14) or die('saleinv18cg err'.mysqli_error($db));
}

$sq2="select  grno,entrydate,suplier,sum(supqty*price) ap,sum(supqty*price)*cgstp/100 sg,sum(supqty*price)*cgstp/100 cg,sum(hand) hnd,sum(tot) tt ,pbillno,purdate from sparepurchase  where brcode='$brcode' and entrydate>='$dfrom' and entrydate<='$dto'  and sgstp='2.50' group by grno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
 
 $tot=0;
 
 $tot=$res2['ap']+$res2['sg']+$res2['cg'];
 
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,5b4,2_5sgst,2_5cgst,hand,total,salestype,pbillno,purdate) values('$res2[grno]','$res2[entrydate]','$res2[suplier]','$brcode','SP','$res2[ap]','$res2[sg]','$res2[cg]','$res2[hnd]','$tot','spare','$res2[pbillno]','$res2[purdate]')";
   mysqli_query($db,$sq14) or die('saleinv18cg err'.mysqli_error($db));
}

$sq2="select  grno,entrydate,suplier,sum(supqty*price) ap,sum(supqty*price)*cgstp/100 sg,sum(supqty*price)*cgstp/100 cg,sum(hand) hnd,sum(tot) tt ,pbillno,purdate from sparepurchase  where brcode='$brcode' and entrydate>='$dfrom' and entrydate<='$dto'  and sgstp='0.00' and cgstp='0.00' and igstp='0.00' group by grno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
 $tot=0;
 
 $tot=$res2['ap'];
 
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,0gst,hand,total,salestype,pbillno,purdate) values('$res2[grno]','$res2[entrydate]','$res2[suplier]','$brcode','SP','$res2[ap]','$res2[hnd]','$tot','spare','$res2[pbillno]','$res2[purdate]')";
   mysqli_query($db,$sq14) or die('saleinv18cg err'.mysqli_error($db));
}




$sq2="select  grno,entrydate,suplier,sum(supqty*price) ap,sum(supqty*price)*igstp/100 ig,sum(hand) hnd,sum(tot) tt ,pbillno,purdate from sparepurchase  where brcode='$brcode' and entrydate>='$dfrom' and entrydate<='$dto'  and igstp='28.00' group by grno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
 
  $tot=0;
 
 $tot=$res2['ap']+$res2['ig'];
 
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,28b4,28igst,hand,total,salestype,pbillno,purdate) values('$res2[grno]','$res2[entrydate]','$res2[suplier]','$brcode','SP','$res2[ap]','$res2[ig]','$res2[hnd]','$tot','spare','$res2[pbillno]','$res2[purdate]')";
   mysqli_query($db,$sq14) or die('saleinv18cg err'.mysqli_error($db));
}


$sq2="select  grno,entrydate,suplier,sum(supqty*price) ap,sum(supqty*price)*igstp/100 ig,sum(hand) hnd,sum(tot) tt ,pbillno,purdate from sparepurchase  where brcode='$brcode' and entrydate>='$dfrom' and entrydate<='$dto'  and igstp='18.00' group by grno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
    
  $tot=0;
  $tot=$res2['ap']+$res2['ig'];
 
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,18b4,18igst,hand,total,salestype,pbillno,purdate) values('$res2[grno]','$res2[entrydate]','$res2[suplier]','$brcode','SP','$res2[ap]','$res2[ig]','$res2[hnd]','$tot','spare','$res2[pbillno]','$res2[purdate]')";
   mysqli_query($db,$sq14) or die('saleinv18cg err'.mysqli_error($db));
}


$sq2="select  grno,entrydate,suplier,sum(supqty*price) ap,sum(supqty*price)*igstp/100 ig,sum(hand) hnd,sum(tot) tt ,pbillno,purdate from sparepurchase  where brcode='$brcode' and entrydate>='$dfrom' and entrydate<='$dto'  and igstp='12.00' group by grno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
 
   $tot=0;
  $tot=$res2['ap']+$res2['ig'];
  
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,12b4,12igst,hand,total,salestype,pbillno,purdate) values('$res2[grno]','$res2[entrydate]','$res2[suplier]','$brcode','SP','$res2[ap]','$res2[ig]','$res2[hnd]','$tot','spare','$res2[pbillno]','$res2[purdate]')";
   mysqli_query($db,$sq14) or die('saleinv18cg err'.mysqli_error($db));
}



$sq2="select  grno,entrydate,suplier,sum(supqty*price) ap,sum(supqty*price)*igstp/100 ig,sum(hand) hnd,sum(tot) tt ,pbillno,purdate from sparepurchase  where brcode='$brcode' and entrydate>='$dfrom' and entrydate<='$dto'  and igstp='5.00' group by grno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
   $tot=0;
  $tot=$res2['ap']+$res2['ig'];
  
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,5b4,5igst,hand,total,salestype,pbillno,purdate) values('$res2[grno]','$res2[entrydate]','$res2[suplier]','$brcode','SP','$res2[ap]','$res2[ig]','$res2[hnd]','$tot','spare','$res2[pbillno]','$res2[purdate]')";
   mysqli_query($db,$sq14) or die('saleinv18cg err'.mysqli_error($db));
}





// ##############  spare purchase end        ###################################


//      cs ret start  #####################################################
/*

$sq2="select  invno,invdate,customer,sum(aprice) ap,sum(sgstamt) sg,sum(cgstamt) cg,sum(hand) hnd,sum(tot) tt from salesretnew where brcode='$brcode' and invdate>='$dfrom' and invdate<='$dto'  and sgstp='14.00' group by invno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
 
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,28b4,14sgst,14cgst,hand,total,salestype) values('$res2[invno]','$res2[invdate]','$res2[customer]','$brcode','CSRET','$res2[ap]','$res2[sg]','$res2[cg]','$res2[hnd]','$res2[tt]','spare')";
   mysqli_query($db,$sq14) or die('saleinv28cg14 err'.mysqli_error($db));
}


$sq2="select  invno,invdate,customer,sum(aprice) ap,sum(sgstamt) sg,sum(cgstamt) cg,sum(hand) hnd,sum(tot) tt from salesretnew where brcode='$brcode' and invdate>='$dfrom' and invdate<='$dto'  and sgstp='9.00' group by invno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
 
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,18b4,9sgst,9cgst,hand,total,salestype) values('$res2[invno]','$res2[invdate]','$res2[customer]','$brcode','CSRET','$res2[ap]','$res2[sg]','$res2[cg]','$res2[hnd]','$res2[tt]','spare')";
   mysqli_query($db,$sq14) or die('saleinv28cg15 err'.mysqli_error($db));
}

$sq2="select  invno,invdate,customer,sum(aprice) ap,sum(sgstamt) sg,sum(cgstamt) cg,sum(hand) hnd,sum(tot) tt from salesretnew where brcode='$brcode' and invdate>='$dfrom' and invdate<='$dto'  and sgstp='6.00' group by invno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
 
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,12b4,6sgst,6cgst,hand,total,salestype) values('$res2[invno]','$res2[invdate]','$res2[customer]','$brcode','CSRET','$res2[ap]','$res2[sg]','$res2[cg]','$res2[hnd]','$res2[tt]','spare')";
   mysqli_query($db,$sq14) or die('saleinv28cg1 err'.mysqli_error($db));
}

$sq2="select  invno,invdate,customer,sum(aprice) ap,sum(sgstamt) sg,sum(cgstamt) cg,sum(hand) hnd,sum(tot) tt from salesretnew where brcode='$brcode' and invdate>='$dfrom' and invdate<='$dto'  and sgstp='2.50' group by invno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
 
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,5b4,2_5sgst,2_5cgst,hand,total,salestype) values('$res2[invno]','$res2[invdate]','$res2[customer]','$brcode','CSRET','$res2[ap]','$res2[sg]','$res2[cg]','$res2[hnd]','$res2[tt]','spare')";
   mysqli_query($db,$sq14) or die('saleinv28cg16 err'.mysqli_error($db));
}

$sq2="select  invno,invdate,customer,sum(aprice) ap,sum(sgstamt) sg,sum(cgstamt) cg,sum(hand) hnd,sum(tot) tt from salesretnew where brcode='$brcode' and invdate>='$dfrom' and invdate<='$dto'  and sgstp='0.00' and cgstp='0.00' and igstp='0.00' group by invno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
 
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,0gst,hand,total,salestype) values('$res2[invno]','$res2[invdate]','$res2[customer]','$brcode','CSRET','$res2[ap]','$res2[sg]','$res2[cg]','$res2[hnd]','$res2[tt]','spare')";
   mysqli_query($db,$sq14) or die('saleinv28cg2 err'.mysqli_error($db));
}


$sqig=mysqli_query($db,"select invno,invdate,customer,sum(aprice) ap,sum(igstamt) ig,sum(hand) hnd,sum(tot) tt from salesretnew where brcode='$brcode'  and invdate>='$dfrom' and invdate<='$dto' and igstp='28.00' group by invno");
 while($res2=mysqli_fetch_array($sqig))
{
 $sqig1="insert into $tbl(invno,invdate,cust,brcode,invtype,28b4,28igst,hand,total,salestype) values('$res2[invno]','$res2[invdate]','$res2[customer]','$brcode','CSRET','$res2[ap]','$res2[ig]','$res2[hnd]','$res2[hnd]','spare')";
   mysqli_query($db,$sqig1) or die('saleinv err'.mysqli_error($db));
}


$sqig=mysqli_query($db,"select invno,invdate,customer,sum(aprice) ap,sum(igstamt) ig,sum(hand) hnd,sum(tot) tt from salesretnew where brcode='$brcode'  and invdate>='$dfrom' and invdate<='$dto' and igstp='18.00' group by invno");
 while($res2=mysqli_fetch_array($sqig))
{
 $sqig1="insert into $tbl(invno,invdate,cust,brcode,invtype,18b4,18igst,hand,total,salestype) values('$res2[invno]','$res2[invdate]','$res2[customer]','$brcode','CSRET','$res2[ap]','$res2[ig]','$res2[hnd]','$res2[hnd]','spare')";
   mysqli_query($db,$sqig1) or die('saleinv err'.mysqli_error($db));
}



$sqig=mysqli_query($db,"select invno,invdate,customer,sum(aprice) ap,sum(igstamt) ig,sum(hand) hnd,sum(tot) tt from salesretnew where brcode='$brcode'  and invdate>='$dfrom' and invdate<='$dto' and igstp='12.00' group by invno");
 while($res2=mysqli_fetch_array($sqig))
{
 $sqig1="insert into $tbl(invno,invdate,cust,brcode,invtype,12b4,12igst,hand,total,salestype) values('$res2[invno]','$res2[invdate]','$res2[customer]','$brcode','CSRET','$res2[ap]','$res2[ig]','$res2[hnd]','$res2[hnd]','spare')";
   mysqli_query($db,$sqig1) or die('saleinv err'.mysqli_error($db));
}


$sqig=mysqli_query($db,"select invno,invdate,customer,sum(aprice) ap,sum(igstamt) ig,sum(hand) hnd,sum(tot) tt from salesretnew where brcode='$brcode' and invdate>='$dfrom' and invdate<='$dto' and igstp='5.00' group by invno");
 while($res2=mysqli_fetch_array($sqig))
{
 $sqig1="insert into $tbl(invno,invdate,cust,brcode,invtype,5b4,5igst,hand,total,salestype) values('$res2[invno]','$res2[invdate]','$res2[customer]','$brcode','CSRET','$res2[ap]','$res2[ig]','$res2[hnd]','$res2[hnd]','spare')";
   mysqli_query($db,$sqig1) or die('saleinv err'.mysqli_error($db));
}

*/


// ###############  cs  ret end   ##############################################




//   Transfer In start     ##################################################

// parts

$sqig=mysqli_query($db,"select GRNNo,GRNDate,SupplyingPlantName,sum(GRAMOUNT) ap,sum(igstamt) ig,sum(GRAMOUNT+igstamt) tt,DealerInvoiceNumber from transferin  where brcode='$brcode'  and GRNDate>='$dfrom' and GRNDate<='$dto' and igstp='28.00'  group by DealerInvoiceNumber");
 while($res2=mysqli_fetch_array($sqig))
{
 $sqig1="insert into $tbl(invno,invdate,cust,brcode,invtype,28b4,28igst,total,salestype,pbillno) values('$res2[GRNNo]','$res2[GRNDate]','$res2[SupplyingPlantName]','$brcode','TRIN','$res2[ap]','$res2[ig]','$res2[tt]','spare','$res2[DealerInvoiceNumber]')";
 //print $sqig1;
   mysqli_query($db,$sqig1) or die('saleinv err'.mysqli_error($db));
}

$sqig=mysqli_query($db,"select GRNNo,GRNDate,SupplyingPlantName,sum(GRAMOUNT) ap,sum(igstamt) ig,sum(GRAMOUNT+igstamt) tt,DealerInvoiceNumber from transferin  where brcode='$brcode'  and GRNDate>='$dfrom' and GRNDate<='$dto' and igstp='18.00'  group by DealerInvoiceNumber");
 while($res2=mysqli_fetch_array($sqig))
{
 $sqig1="insert into $tbl(invno,invdate,cust,brcode,invtype,18b4,18igst,total,salestype,pbillno) values('$res2[GRNNo]','$res2[GRNDate]','$res2[SupplyingPlantName]','$brcode','TRIN','$res2[ap]','$res2[ig]','$res2[tt]','spare','$res2[DealerInvoiceNumber]')";
 //print $sqig1;
   mysqli_query($db,$sqig1) or die('saleinv err'.mysqli_error($db));
}

$sqig=mysqli_query($db,"select GRNNo,GRNDate,SupplyingPlantName,sum(GRAMOUNT) ap,sum(igstamt) ig,sum(GRAMOUNT+igstamt) tt,DealerInvoiceNumber from transferin  where brcode='$brcode'  and GRNDate>='$dfrom' and GRNDate<='$dto' and igstp='12.00'  group by DealerInvoiceNumber");
 while($res2=mysqli_fetch_array($sqig))
{
 $sqig1="insert into $tbl(invno,invdate,cust,brcode,invtype,12b4,12igst,total,salestype,pbillno) values('$res2[GRNNo]','$res2[GRNDate]','$res2[SupplyingPlantName]','$brcode','TRIN','$res2[ap]','$res2[ig]','$res2[tt]','spare','$res2[DealerInvoiceNumber]')";
 //print $sqig1;
   mysqli_query($db,$sqig1) or die('saleinv err'.mysqli_error($db));
}


$sqig=mysqli_query($db,"select GRNNo,GRNDate,SupplyingPlantName,sum(GRAMOUNT) ap,sum(igstamt) ig,sum(GRAMOUNT+igstamt) tt,DealerInvoiceNumber from transferin  where brcode='$brcode'  and GRNDate>='$dfrom' and GRNDate<='$dto' and igstp='5.00'  group by DealerInvoiceNumber");
 while($res2=mysqli_fetch_array($sqig))
{
 $sqig1="insert into $tbl(invno,invdate,cust,brcode,invtype,5b4,5igst,total,salestype,pbillno) values('$res2[GRNNo]','$res2[GRNDate]','$res2[SupplyingPlantName]','$brcode','TRIN','$res2[ap]','$res2[ig]','$res2[tt]','spare','$res2[DealerInvoiceNumber]')";
 //print $sqig1;
   mysqli_query($db,$sqig1) or die('saleinv err'.mysqli_error($db));
}

//  transfer In end #######################################




// GST expense  start  ##################################################



//   Eicher Purchase start  #################################################

// parts

$sqig=mysqli_query($db,"select GRNNo,GRNDate,vendorCode,sum(BTAmt) ap,sum(TaxAmt) ig,sum(hand) hnd,sum(InvAmt) tt,InvoiceNo from eicherpurchase  where brcode='$brcode'  and GRNDate>='$dfrom' and GRNDate<='$dto' and taxp='28.00'  group by GRNNo");

 while($res2=mysqli_fetch_array($sqig))
{
 $sqig1="insert into $tbl(invno,invdate,cust,brcode,invtype,28b4,28igst,hand,total,salestype,pbillno) values('$res2[GRNNo]','$res2[GRNDate]','$res2[vendorCode]','$brcode','EICHP','$res2[ap]','$res2[ig]','$res2[hnd]','$res2[tt]','spare','$res2[InvoiceNo]')";
 //print $sqig1;
   mysqli_query($db,$sqig1) or die('saleinv err'.mysqli_error($db));
}

$sqig=mysqli_query($db,"select GRNNo,GRNDate,vendorCode,sum(BTAmt) ap,sum(TaxAmt) ig,sum(hand) hnd,sum(InvAmt) tt,InvoiceNo from eicherpurchase  where brcode='$brcode'  and GRNDate>='$dfrom' and GRNDate<='$dto' and taxp='18.00'  group by GRNNo");
 while($res2=mysqli_fetch_array($sqig))
{
 $sqig1="insert into $tbl(invno,invdate,cust,brcode,invtype,18b4,18igst,hand,total,salestype,pbillno) values('$res2[GRNNo]','$res2[GRNDate]','$res2[vendorCode]','$brcode','EICHP','$res2[ap]','$res2[ig]','$res2[hnd]','$res2[tt]','spare','$res2[InvoiceNo]')";
 //print $sqig1;
   mysqli_query($db,$sqig1) or die('saleinv err'.mysqli_error($db));
}

$sqig=mysqli_query($db,"select GRNNo,GRNDate,vendorCode,sum(BTAmt) ap,sum(TaxAmt) ig,sum(hand) hnd,sum(InvAmt) tt,InvoiceNo from eicherpurchase  where brcode='$brcode'  and GRNDate>='$dfrom' and GRNDate<='$dto' and taxp='12.00'  group by GRNNo");
 while($res2=mysqli_fetch_array($sqig))
{
 $sqig1="insert into $tbl(invno,invdate,cust,brcode,invtype,12b4,12igst,hand,total,salestype,pbillno) values('$res2[GRNNo]','$res2[GRNDate]','$res2[vendorCode]','$brcode','EICHP','$res2[ap]','$res2[ig]','$res2[hnd]','$res2[tt]','spare','$res2[InvoiceNo]')";
 //print $sqig1;
   mysqli_query($db,$sqig1) or die('saleinv err'.mysqli_error($db));
}


$sqig=mysqli_query($db,"select GRNNo,GRNDate,vendorCode,sum(BTAmt) ap,sum(TaxAmt) ig,sum(hand) hnd,sum(InvAmt) tt,InvoiceNo from eicherpurchase  where brcode='$brcode'  and GRNDate>='$dfrom' and GRNDate<='$dto' and taxp='5.00'  group by GRNNo");
 while($res2=mysqli_fetch_array($sqig))
{
 $sqig1="insert into $tbl(invno,invdate,cust,brcode,invtype,5b4,5igst,hand,total,salestype,pbillno) values('$res2[GRNNo]','$res2[GRNDate]','$res2[vendorCode]','$brcode','EICHP','$res2[ap]','$res2[ig]','$res2[hnd]','$res2[tt]','spare','$res2[InvoiceNo]')";
 //print $sqig1;
   mysqli_query($db,$sqig1) or die('saleinv err'.mysqli_error($db));
}

//  Eicher Pur end #######################################

//   VECV   spare purchase start   ########################################

$sq2="select  grno,entrydate,suplier,sum(aprice) ap,sum(sgstamt) sg,sum(cgstamt) cg,sum(hand) hnd,sum(tot) tt ,pbillno,purdate,sum(taxamt) taxa from sparepurchasevecv  where brcode='$brcode' and entrydate>='$dfrom' and entrydate<='$dto'  and tax='28.00' and taxtype='CGST' group by grno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
 $gstt=$res2['taxa']/2;
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,28b4,14sgst,14cgst,hand,total,salestype,pbillno,purdate) values('$res2[grno]','$res2[entrydate]','$res2[suplier]','$brcode','VECV','$res2[ap]','$gstt','$gstt','$res2[hnd]','$res2[tt]','spare','$res2[pbillno]','$res2[purdate]')";
   mysqli_query($db,$sq14) or die('saleinv28cg13 err'.mysqli_error($db));
}


$sq2="select  grno,entrydate,suplier,sum(aprice) ap,sum(sgstamt) sg,sum(cgstamt) cg,sum(hand) hnd,sum(tot) tt ,pbillno,purdate,sum(taxamt) taxa from sparepurchasevecv  where brcode='$brcode' and entrydate>='$dfrom' and entrydate<='$dto'  and tax='18.00' and taxtype='CGST' group by grno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
  $gstt=$res2['taxa']/2;
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,18b4,9sgst,9cgst,hand,total,salestype,pbillno,purdate) values('$res2[grno]','$res2[entrydate]','$res2[suplier]','$brcode','VECV','$res2[ap]','$gstt','$gstt','$res2[hnd]','$res2[tt]','spare','$res2[pbillno]','$res2[purdate]')";
   mysqli_query($db,$sq14) or die('saleinv18cg err'.mysqli_error($db));
}


$sq2="select  grno,entrydate,suplier,sum(aprice) ap,sum(sgstamt) sg,sum(cgstamt) cg,sum(hand) hnd,sum(tot) tt ,pbillno,purdate,sum(taxamt) taxa from sparepurchasevecv  where brcode='$brcode' and entrydate>='$dfrom' and entrydate<='$dto'  and tax='12.00' and taxtype='CGST' group by grno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
  $gstt=$res2['taxa']/2;
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,12b4,6sgst,6cgst,hand,total,salestype,pbillno,purdate) values('$res2[grno]','$res2[entrydate]','$res2[suplier]','$brcode','VECV','$res2[ap]','$gstt','$gstt','$res2[hnd]','$res2[tt]','spare','$res2[pbillno]','$res2[purdate]')";
   mysqli_query($db,$sq14) or die('saleinv18cg err'.mysqli_error($db));
}

$sq2="select  grno,entrydate,suplier,sum(aprice) ap,sum(sgstamt) sg,sum(cgstamt) cg,sum(hand) hnd,sum(tot) tt ,pbillno,purdate,sum(taxamt) taxa from sparepurchasevecv  where brcode='$brcode' and entrydate>='$dfrom' and entrydate<='$dto'  and tax='5.00' and taxtype='CGST' group by grno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
  $gstt=$res2['taxa']/2;
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,5b4,2_5sgst,2_5cgst,hand,total,salestype,pbillno,purdate) values('$res2[grno]','$res2[entrydate]','$res2[suplier]','$brcode','VECV','$res2[ap]','$gstt','$gstt','$res2[hnd]','$res2[tt]','spare','$res2[pbillno]','$res2[purdate]')";
   mysqli_query($db,$sq14) or die('saleinv18cg err'.mysqli_error($db));
}
//###
$sq2="select  grno,entrydate,suplier,sum(aprice) ap,sum(sgstamt) sg,sum(cgstamt) cg,sum(hand) hnd,sum(tot) tt ,pbillno,purdate,sum(taxamt) taxa from sparepurchasevecv  where brcode='$brcode' and entrydate>='$dfrom' and entrydate<='$dto'  and tax='0.00'  group by grno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
 $gstt=$res2['taxa'];
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,0gst,hand,total,salestype,pbillno,purdate) values('$res2[grno]','$res2[entrydate]','$res2[suplier]','$brcode','VECV','$res2[ap]','$res2[hnd]','$res2[tt]','spare','$res2[pbillno]','$res2[purdate]')";
   mysqli_query($db,$sq14) or die('saleinv18cg err'.mysqli_error($db));
}




$sq2="select  grno,entrydate,suplier,sum(aprice) ap,sum(igstamt) ig,sum(hand) hnd,sum(tot) tt ,pbillno,purdate,sum(taxamt) taxa from sparepurchasevecv  where brcode='$brcode' and entrydate>='$dfrom' and entrydate<='$dto'  and tax='28.00' and taxtype='IGST' group by grno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
  $gstt=$res2['taxa'];
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,28b4,28igst,hand,total,salestype,pbillno,purdate) values('$res2[grno]','$res2[entrydate]','$res2[suplier]','$brcode','VECV','$res2[ap]','$gstt','$res2[hnd]','$res2[tt]','spare','$res2[pbillno]','$res2[purdate]')";
   mysqli_query($db,$sq14) or die('saleinv18cg err'.mysqli_error($db));
}


$sq2="select  grno,entrydate,suplier,sum(aprice) ap,sum(igstamt) ig,sum(hand) hnd,sum(tot) tt ,pbillno,purdate,sum(taxamt) taxa from sparepurchasevecv  where brcode='$brcode' and entrydate>='$dfrom' and entrydate<='$dto'  and tax='18.00' and taxtype='IGST' group by grno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
 $gstt=$res2['taxa'];
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,18b4,18igst,hand,total,salestype,pbillno,purdate) values('$res2[grno]','$res2[entrydate]','$res2[suplier]','$brcode','VECV','$res2[ap]','$gstt','$res2[hnd]','$res2[tt]','spare','$res2[pbillno]','$res2[purdate]')";
   mysqli_query($db,$sq14) or die('saleinv18cg err'.mysqli_error($db));
}


$sq2="select  grno,entrydate,suplier,sum(aprice) ap,sum(igstamt) ig,sum(hand) hnd,sum(tot) tt ,pbillno,purdate,sum(taxamt) taxa from sparepurchasevecv  where brcode='$brcode' and entrydate>='$dfrom' and entrydate<='$dto'  and tax='12.00' and taxtype='IGST' group by grno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
 $gstt=$res2['taxa'];
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,12b4,12igst,hand,total,salestype,pbillno,purdate) values('$res2[grno]','$res2[entrydate]','$res2[suplier]','$brcode','VECV','$res2[ap]','$gstt','$res2[hnd]','$res2[tt]','spare','$res2[pbillno]','$res2[purdate]')";
   mysqli_query($db,$sq14) or die('saleinv18cg err'.mysqli_error($db));
}



$sq2="select  grno,entrydate,suplier,sum(aprice) ap,sum(igstamt) ig,sum(hand) hnd,sum(tot) tt ,pbillno,purdate,sum(taxamt) taxa from sparepurchasevecv  where brcode='$brcode' and entrydate>='$dfrom' and entrydate<='$dto'  and tax='5.00' and taxtype='IGST' group by grno";
$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));

while($res2=mysqli_fetch_array($re21))
{
 $gstt=$res2['taxa'];
 $sq14="insert into $tbl(invno,invdate,cust,brcode,invtype,5b4,5igst,hand,total,salestype,pbillno,purdate) values('$res2[grno]','$res2[entrydate]','$res2[suplier]','$brcode','VECV','$res2[ap]','$gstt','$res2[hnd]','$res2[tt]','spare','$res2[pbillno]','$res2[purdate]')";
   mysqli_query($db,$sq14) or die('saleinv18cg err'.mysqli_error($db));
}





// ############## VECV  spare purchase end        ##############################





//###########################################################################

//  Spare Purchase  print


print "<table border=1  bgcolor='455342' cellspacing=1 cellpadding=3>";
print "<tr><th colspan=27><h3>SPARE PURCHASE</th></tr>";
print "<tr><th colspan=9><h3>Invoice Details</th><th colspan=4><h3>28% TAX</th><th colspan=4><h3>18% TAX</th><th colspan=4><h3>12% TAX</th><th colspan=4><h3>5% TAX</th><th colspan=1><h3>0% TAX</th><th colspan=2><h3></th></tr>";

print " <th>GRN No <th>Entry Date ";
print "<th>Suplier<th>Branch<th>Bill No.<th>Pbill Date<th>VECV Total<th>PSN Total<th>Diff<th>Before Value <th>IGST <th>SGST <th>CGST <th>Before Value <th>IGST <th>SGST <th>CGST <th>Before Value <th>IGST <th>SGST <th>CGST <th>Before Value <th>IGST <th>SGST <th>CGST <th>Before Value<th>Hand ";

    $reinvno=mysqli_query($db,"select distinct invno,invdate,cust,pbillno,purdate from $tbl  where brcode='$brcode' and invtype='SP' and cust like '%eicher%' or cust like '%VE Commercial%' or cust like 'VE1000%' order by invdate,invno ")or die('err'.mysqli_error($db));
  $bta5=0;
$cg5=0;
$sg5=0;
$ig5=0;
$bta12=0;
$cg12=0;
$sg12=0;
$ig12=0;
$bta18=0;
$cg18=0;
$sg18=0;
$ig18=0;
$bta28=0;
$cg28=0;
$sg28=0;
$ig28=0;
$bta0=0;
$gtot=0;
    $hand1=0;    
    $vecv=0;
    
    while($rowinvno=mysqli_fetch_array($reinvno))
{
    
    
    
$re=mysqli_query($db,"select sum(28b4) 28b4,sum(14sgst) 14sgst,sum(14cgst) 14cgst ,sum(28igst) 28igst,sum(18b4) 18b4,sum(9sgst) 9sgst,sum(9cgst) 9cgst ,sum(18igst) 18igst,sum(12b4) 12b4,sum(6sgst) 6sgst,sum(6cgst) 6cgst ,sum(12igst) 12igst,sum(5b4) 5b4,sum(2_5sgst) 2_5sgst,sum(2_5cgst) 2_5cgst ,sum(5igst) 5igst,sum(0gst) 0b4,sum(hand) hand,sum(total) tot from $tbl  where brcode='$brcode'   and invno='$rowinvno[0]' group by invno")or die('err2w'.mysqli_error($db));
    //else
//$re=mysqli_query($db,"select invno,invdate,customer,sum(aprice) aprice,sum(taxamt)txamt,sum(cessamt)cess ,sum(tot)tot, vatorcst,invtype,msno from spareinvoice  where brcode='$brcode' and invtype='$invrow[0]' and invdate>='$dfrom' and invdate<='$dto'  group by invdate,invno")or die('err'.mysqli_error($db));    


while($row=mysqli_fetch_array($re))
{
    $cus_result=mysqli_query($db,"select tin from customer where acname = '$row[cust]'")or die  (mysqli_error($db));
	$cus_row=mysqli_fetch_row($cus_result);
	$mtin=$cus_row[0];
	//$btgstamt=$row[3];
	//	$totcs+= $row[tot];
//	$hand1+=$row[hand];
//	$tthand+=$row[hand]; 
	
	$s=mysqli_query($db,"select amount from purchreco where brcode='$brcode'  and invno='$rowinvno[3]' ");
     $r=mysqli_fetch_array($s);
	
	 
	 $diff=$r[0]-round($row[18]);
	 if($diff>0 || $diff<0)
	 {
	  $ok='not ok';   
	 }
	 else
	 {
	     $ok='ok';
	 }
	 $dif+=$diff;
	 
    print "\n<tr bgcolor=white><td>$rowinvno[0]</td><td>".implode('-', array_reverse(explode('-', $rowinvno['invdate'])));
       print "<td>".strtoupper($rowinvno['cust'])."</td><td>$brcode</td><td>$rowinvno[3]</td><td>".implode('-', array_reverse(explode('-', $rowinvno[4])))."</td>";
       print "<td align=right>".putcoma($r[0])."</td>";
        if($diff>0 || $diff<0)
	 {
   print "<td align=right><font size='3' color='red'>".putcoma(round($row[18]))."</font></td>";
	 }
	 else
	 {
	   print "<td align=right>".putcoma(round($row[18]))."</td>";  
	 }
    print "<td align=right>".putComa($diff)."</td>";
   
    print "<td align=right>".putComa($row[0])."</td><td align=right>".putComa($row[3])."</td>";
    print "<td align=right>".putcoma($row[1])."</td><td align=right>".putComa($row[2])."</td>";
    
    print "<td align=right>".putComa($row[4])."</td><td align=right>".putComa($row[7])."</td>";
    print "<td align=right>".putcoma($row[5])."</td><td align=right>".putComa($row[6])."</td>";
    
    print "<td align=right>".putComa($row[8])."</td><td align=right>".putComa($row[11])."</td>";
    print "<td align=right>".putcoma($row[9])."</td><td align=right>".putComa($row[10])."</td>";
    
    print "<td align=right>".putComa($row[12])."</td><td align=right>".putComa($row[15])."</td>";
    print "<td align=right>".putcoma($row[13])."</td><td align=right>".putComa($row[14])."</td>";
    
    print "<td align=right>".putcoma($row[16])."</td><td align=right>".putComa($row[17])."</td></tr>";
   
   
  
 $bta5+=$row[12];
$cg5+=$row[14];
$sg5+=$row[13];
$ig5+=$row[15];

$bta12+=$row[8];
$cg12+=$row[10];
$sg12+=$row[9];
$ig12+=$row[11];

$bta18+=$row[4];
$cg18+=$row[6];
$sg18+=$row[5];
$ig18+=$row[7];

$bta28+=$row[0];
$cg28+=$row[2];
$sg28+=$row[1];
$ig28+=$row[3];

$bta0+=$row[16];
$gtot+=$row[18];
	$hand1+=$row[17];
	
$vecv+=	$r[0];
       
}

}
print "<tr><B><td colspan=6 align =right>Total</td><td align =right><font size='3' color='white'>".putcoma($vecv)."</font><td align =right><font size='3' color='white'>".putcoma($gtot)."</font></td><td><font size='3' color='white'>".putcoma($dif)."</font></td><td align =right>".putcoma($bta28)."</td><td align =right>".putcoma($ig28)."</td><td align =right>".putcoma($sg28)."</td><td align =right>".putcoma($cg28)."</td>  <td align =right>".putcoma($bta18)."</td><td align =right>".putcoma($ig18)."</td><td align =right>".putcoma($sg18)."</td><td align =right>".putcoma($cg18)."</td>   <td align =right>".putcoma($bta12)."</td><td align =right>".putcoma($ig12)."</td><td align =right>".putcoma($sg12)."</td><td align =right>".putcoma($cg12)."</td>  <td align =right>".putcoma($bta5)."</td><td align =right>".putcoma($ig5)."</td><td align =right>".putcoma($sg5)."</td><td align =right>".putcoma($cg5)."</td> <td align =right>".putcoma($bta0)."</td><td align =right>".putcoma($hand1)."</td></B></tr>";
print "</table>";



//#########################################################################


print "<table border=1  bgcolor='white' cellspacing=1 cellpadding=3>";
print "<tr><th colspan=27><h3>VECV INVOICES MISSING IN PSN SOFTWARE</th></tr>";
print "<tr><th colspan=8><h3>VECV Invoice Details</th></tr>";


    $reinvno=mysqli_query($db,"select distinct (trim(invno)),amount,invdate,brcode from purchreco where brcode='$brcode' and  invno not  in (select distinct (trim(pbillno) ) from $tbl  where brcode='$brcode' and cust like '%eich%' or cust like '%VE Commercial%' or cust like 'VE1000%' )")or die('err'.mysqli_error($db));
    
   

    while($rowinvno=mysqli_fetch_array($reinvno))
{
    print "<td align=right>".implode('-', array_reverse(explode('-', $rowinvno[2])))."</td><td align=right>$rowinvno[0]</td><td align=right>$rowinvno[1]</td><td>$rowinvno[3]</td></tr>"; 
    $t+=$rowinvno[1];
}


print "<tr><td colspan=2 align =right>Total</td><td align =right>".putcoma($t)."</td></tr>";
print "</table>";

//###########################################################################


//###########################################################################
// Transfer in END

//#########################################################################



//###########################################################################
// Transfer in END

//$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$




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> Reconciliation VECV vs PSN </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 colspan=2 align=center bgcolor='lightsteelblue'><input type=submit value='Show' name='show'></td></tr>

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


