<?php include("../../include/dbconnect.php");

$result=mysqli_query($conn,"select distinct name from company where compcode = '$compcode' ") or die(mysqli_error());
$row = mysqli_fetch_row($result);
$compname = $row[0];

mysqli_query($conn,"truncate incentive");
mysqli_query($conn,"truncate incentive2");

?>
<title>Incentive Details</title>
<form>
<body bgcolor="#ffffff"  topmargin=0>
<font face="Arial, Helvetica, sans-serif">
<?

$startdt = us($stdt);
$enddt = us($edt);
?>
</font>

<?

for ($grpind=0;$grpind < count($area);$grpind++)
{
	$gresult = mysqli_query($conn,"select city from buy where city = '$area[$grpind]'") or die(mysqli_error());
	$grow = mysqli_fetch_row($gresult);
	$gname = $grow[0];
	?>
    <table>
		<tr>
		<td align=left colspan="100%"><font face="Arial, Helvetica, sans-serif" color="#CC0000"><b><u><?echo($gname);?></u></b></font></td></tr>
	</table>
	<?
	
	$nettotal=0;
	$acresult = mysqli_query($conn,"select distinct bcode,name from buy where city = '$area[$grpind]' and buyertype ='Buyer' and incentive != 'No' order by name") or die(mysqli_error());
	while($acrow = mysqli_fetch_row($acresult))
	{
		$acctcode = $acrow[0];
		$bname=$acrow[1];
		$bcode = $acrow[0];
		$tdbamt = 0;
	
		$kresult = mysqli_query($conn,"select sum(cramt) from ledger where compcode = '$compcode' && acctcode = '$acctcode'   and cramt >0   and date >= '$startdt' and date <='$enddt'  and taxtype = 'Receipts' order by date ");
		$krow = mysqli_fetch_row($kresult);			
		if( $krow[0] >0)
		{
			echo("$bname:$krow[0]<br>");
			
			$result = mysqli_query($conn,"select sum(cramt) from ledger where compcode = '$compcode' && acctcode = '$acctcode' and date <= '$startdt' and taxtype = 'Receipts' order by date ");
			$row = mysqli_fetch_row($result);
			$credit_amount = $row[0];		
				  
			$result = mysqli_query($conn,"select sum(dbamt) from ledger where compcode = '$compcode' && acctcode = '$acctcode' and date <= '$startdt' order by date ");
			$row = mysqli_fetch_row($result);
			$debit_amount = $row[0];			
			if($debit_amount - $credit_amount >=0)
			{
					?>			
												<tr>
					<td align=left colspan="100%"><font face="Arial, Helvetica, sans-serif"><b><?=$description?></b></font></td></tr>
					
					<?
			
					$sl=1;
					$above180=0;
					$above90=0;
					$above60 =0;
					$above30 = 0;
					$below30 =0;
					
					$msresult = mysqli_query($conn,"select distinct invno,date,dbamt from ledger where compcode = '$compcode' && acctcode = '$acctcode' and dbamt >0 and date < '$startdt'  order by date ");
					while($msrow = mysqli_fetch_row($msresult))
					{
						$invno = $msrow[0];
						$date = $msrow[1];
						$dbamt = $msrow[2];
						$credit_amount = $credit_amount - $dbamt;	
			//echo("$credit_amount = $credit_amount - $dbamt<br>");
						$days = datediff($enddt,$date);
						if($credit_amount <= 0)
						{
							  $dbamt = $credit_amount*-1;
							  $credit_amount =0;
							  if($dbamt>0)
			mysqli_query($conn,"insert into incentive (invno,invdate,bcode,amount,area,name) values('$invno','$date','$bcode','$dbamt','$gname','$bname')") or die(mysqli_error());	
							  

						} // If loop 
						$sl++;
					}  // End of while loop

					$msresult = mysqli_query($conn,"select distinct invno,date,dbamt from ledger where compcode = '$compcode' && acctcode = '$acctcode' and dbamt >0 and date >='$startdt' and date <='$enddt'  order by date ");
					while($msrow = mysqli_fetch_row($msresult))
					{
						$invno = $msrow[0];
						$date = $msrow[1];
						$dbamt = $msrow[2];
							  if($dbamt>0)
			mysqli_query($conn,"insert into incentive (invno,invdate,bcode,amount,area,name) values('$invno','$date','$bcode','$dbamt','$gname','$bname')") or die(mysqli_error());	
					}
			} // If dbamt - cramt >0
		} // if(KROW
	} // While
} // FOR

?>		
</table>
<?

$bresult = mysqli_query($conn,"select distinct bcode from incentive  order by bcode ") or die(mysqli_error());
while($brow = mysqli_fetch_row($bresult))
{

			$adresult = mysqli_query($conn,"select name from buy where bcode = '$brow[0]' order by name") or die(mysqli_error());
			$adrow = mysqli_fetch_row($adresult);
	
	$ksresult = mysqli_query($conn,"select date,cramt from ledger where compcode = '$compcode' && acctcode = '$brow[0]' and cramt >0   and date >= '$startdt' and date <='$enddt'  and taxtype = 'Receipts' order by date ") or die(mysqli_error());
	while($ksrow = mysqli_fetch_row($ksresult))
	{
		$payment_date = $ksrow[0];
		$credit_amount = $ksrow[1];
		$cramt = $ksrow[1];
		$nsresult = mysqli_query($conn,"select  invno,invdate,amount,adjusted_amount,bcode,area,name,record from incentive where bcode = '$brow[0]' and   amount - adjusted_amount >0 order by invdate ") or die(mysqli_error());
		while($nsrow = mysqli_fetch_row($nsresult))
		{
			$invno = $nsrow[0];
			$invdate = $nsrow[1];
			$invamt = $nsrow[2];
			$dbamt =  $nsrow[2]-$nsrow[3];
			$bcode = $nsrow[4];
			$gname = $nsrow[5];
			$bname = $nsrow[6];
			$record = $nsrow[7];
			$days = datediff($payment_date,$invdate);
			if($dbamt >0 )
			{

				  if($credit_amount >= $dbamt)
				  {
					  $adamt = $dbamt;
					  $credit_amount = $credit_amount - $dbamt;
				  }
				  elseif($credit_amount < $dbamt)
				  {
					  if($credit_amount < 0)
					  {
						  $credit_amount = $credit_amount*-1;
					  }
					  $adamt = $credit_amount;
					  $credit_amount =0;
				  }
				  if($adamt>0)
				  {
	
							mysqli_query($conn,"insert into incentive2 (invno,invdate,bcode,amount,adjusted_amount,payment,days,payment_date,area,name) values('$invno','$invdate',
	'$bcode','$invamt','$adamt','$cramt','$days','$payment_date','$gname','$bname')") or die(mysqli_error());
									  
					  $adamt = $adamt+$nsrow[3];
					  mysqli_query($conn,"update incentive set payment = '$cramt',adjusted_amount = $adamt,days = '$days',payment_date = '$payment_date' where record = '$record' ") or die(mysqli_error());
					 
					  $days =0;
				  }
	
			} // if $dbamt >0 loop
			
			

		} // buyer whie loop
	}
}

		

	




	
	
?>
</font>
<TABLE  width="1294" BORDER=1 CELLPADDING=4 RULES=COLS  >

<COLGROUP></COLGROUP>
<COLGROUP></COLGROUP>
<COLGROUP></COLGROUP>
<COLGROUP></COLGROUP>
<COLGROUP></COLGROUP>
<COLGROUP></COLGROUP>

				
<thead>
<tr>
<td height="34" colspan="100%" ALIGN ="left"><h4 align="center"><font face="Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif"><?echo($compname);?></font></h4></td></tr>
<tr>
      <td colspan="100%"><font face="Arial, Helvetica, sans-serif">
      <div align="center"><font face="Arial, Helvetica, sans-serif">Incentive Details from  <? echo($stdt);?> To <?echo($edt);?></font></div></td>
    </tr>
<tr  bgcolor="#838AAD">

<td width="151" align=center><font face="Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif">Area</font></td>

<td width="130" align=center><div align="right"><font face="Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif">Total Collection</font></div></td>

<td width="119" align=center><div align="right"><font face="Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif">< 45 Days </font></div></td>
<td width="112" align=center><div align="right"><font face="Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif">Incentive 2.5%</font></div></td>
<td width="108" align=center><div align="right"><font face="Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif">45-60 Days</font></div></td>
<td width="98" align=center><div align="right"><font face="Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif">1% Incentive</font></div></td>
<td width="96" align=center><div align="right"><font face="Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif">61-90 Days</font></div></td>
<td width="114" align=center><div align="right"><font face="Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif">0.5% Incentive</font></div></td>

<td width="133" align=center><div align="right"><font face="Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif">Above 90 Days</font></div></td>
<td width="109" align=center><div align="right"><font face="Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif">Total Incentive</font></div></td>

</tr>
<tr>
<td><hr></td>


<td><hr></td>
<td><hr></td>
<td><hr></td>
<td><hr></td>
<td><hr></td>


<td><hr></td>
<td><hr></td>
<td><hr></td>
<td><hr></td>
</tr>
</thead>
<tbody>
	
<?
$total_collections = 0;
$total_incentive = 0;

$aresult = mysqli_query($conn,"select distinct area from incentive order by area ") or die(mysqli_error());
while($arow = mysqli_fetch_row($aresult))
{
	$result45 = mysqli_query($conn,"select sum(adjusted_amount) from incentive2 where days <= 45 and area = '$arow[0]' ") or die(mysqli_error());
	$row45 = mysqli_fetch_row($result45);
	$receipt_45 = $row45[0];
	$incentive_45 = $receipt_45 * 2.5/100;
	$total_collections =$total_collections+$receipt_45;
	$total_incentive = $total_incentive +$incentive_45;
	
	$result60 = mysqli_query($conn,"select sum(adjusted_amount) from incentive2 where days > 45 and days <=60 and area = '$arow[0]' ") or die(mysqli_error());
	$row60 = mysqli_fetch_row($result60);
	$receipt_60 = $row60[0];
	$incentive_60 = $receipt_60 * 1/100;
	$total_collections =$total_collections+$receipt_60;
	$total_incentive = $total_incentive +$incentive_60;	
	
	$result90 = mysqli_query($conn,"select sum(adjusted_amount) from incentive2 where days > 60 and days <= 90 and area = '$arow[0]' ") or die(mysqli_error());
	$row90 = mysqli_fetch_row($result90);
	$receipt_90 = $row90[0];
	$incentive90 = $receipt_90 *0.5/100;
	$total_collections =$total_collections+$receipt_90;
	$total_incentive = $total_incentive +$incentive90;	
	

	$result_above90 = mysqli_query($conn,"select sum(adjusted_amount) from incentive2 where days >90 and area = '$arow[0]' ") or die(mysqli_error());
	$rowabove90 = mysqli_fetch_row($result_above90);
	$receipt_above90 = $rowabove90[0];
	$total_collections =$total_collections+$receipt_above90;

?>
<tr>
<td width="151" align=center><font face="Arial, Helvetica, sans-serif">
  <div align="left"><font face="Arial, Helvetica, sans-serif">
    <?=$arow[0]?>
  </font></div></td>

<td width="130" align=center><div align="right"><font face="Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif"><?=number_format($total_collections,2)?></font></div></td>

<td width="119" align=center><div align="right"><font face="Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif"><?=$receipt_45?> </font></div></td>
<td width="112" align=center><div align="right"><font face="Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif"><?=number_format($incentive_45,2)?></font></div></td>
<td width="108" align=center><div align="right"><font face="Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif"><?=$receipt_60?></font></div></td>
<td width="98" align=center><div align="right"><font face="Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif"><?=number_format($incentive_60,2)?></font></div></td>
<td width="96" align=center><div align="right"><font face="Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif"><?=$receipt_90?></font></div></td>
<td width="114" align=center><div align="right"><font face="Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif"><?=number_format($incentive90,2)?></font></div></td>

<td width="133" align=center><div align="right"><font face="Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif"><?=$receipt_above90?></font></div></td>
<td width="109" align=center><div align="right"><font face="Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif"><?=number_format($total_incentive,2)?></font></div></td>
</tr>
<?
	$total_collections = 0;
$total_incentive = 0;	
}

?>


</tbody>
</table>

