<?PHP

session_start();

include 'connect.php';

if(isset($_SESSION['adminemail']))
{
$user=$_SESSION['adminemail'];

$gtp=mysqli_query($conn,"select * from admin where username='$user'");
$gtp1=mysqli_fetch_assoc($gtp);

$aca=mysqli_query($conn,"select * from financial where user='$user' order by ID desc");
$aca1=mysqli_fetch_assoc($aca);

$financial=$aca1['financial'];


echo '

<style type="text/css">

.main{

width:98%;

margin:auto;

font-family:arial;

font-size:12px;

}

table

{

font-family:arial;

font-size:12px;

} 

</style>';



if($_GET['supplier'])

{



$supplier=$_GET['supplier'];

$fromdate=$_GET['fromdate'];

$todate=$_GET['todate'];



if($fromdate && $todate)

{



$frm=date('Y-m-d',strtotime($fromdate));

$to=date('Y-m-d',strtotime($todate));



echo '<div class="main">';



echo"<h3 style='padding-top:10px; text-align:center;'>Supplier Ledger</h3>";





$sp=mysqli_query($conn,"select * from supplier where ID='$supplier'");

$sp1=mysqli_fetch_assoc($sp);



echo "

<table style='width:97%; margin:auto;'><tr><td>Supplier : <b>".$sp1['name']."</b></td><td style='text-align:right;'>Period : <b>".$fromdate." to ".$todate."</b></td></tr></table>

<center><table border='1' style='width:98%; border-collapse:collapse; margin:auto; border:1px solid #999;'>

<thead><th width='10%'>Date</th><th width='40%'>Description</th><th width='15%'>Debit</th><th width='15%'>Credit</th><th width='15%'>Closing Balance</th></thead>";




$py=mysqli_query($conn,"select sum(amount) as amount from payment where date<'$frm' and date between '$aca1[fromdate]' and '$aca1[todate]' and supplier='$supplier'");

$py1=mysqli_fetch_assoc($py);



$sono=0;

$po=mysqli_query($conn,"select sum(total) as amount from purchase where date<'$frm' and date between '$aca1[fromdate]' and '$aca1[todate]' and supplier='$supplier' group by invoiceno");

while($po1=mysqli_fetch_assoc($po))

{

$sono=$sono+$po1['amount'];

}


		$adjs=0;
		$cgst=0;
		$sgst=0;
		$loi=mysqli_query($conn,"select * from purchase where supplier='$supplier' and date<'$frm' and date between '$aca1[fromdate]' and '$aca1[todate]' group by invoiceno");
		while($loi1=mysqli_fetch_assoc($loi))
		{
			$adjs=$adjs+$loi1['adjustment'];
			$cgst=$cgst+$loi1['cgst_amt'];
			$sgst=$sgst+$loi1['sgst_amt'];
		}



$opl=mysqli_query($conn,"select * from creditor_ledger where date<'$frm' and supplier='$supplier' and date between '$aca1[fromdate]' and '$aca1[todate]'");

$opl1=mysqli_fetch_assoc($opl);


$opcr=0;

$opdb=0;

if($opl1['paymenttype']=='debit')

{

$opdb=$opl1['amount'];

}

elseif($opl1['paymenttype']=='credit')

{

$opcr=$opl1['amount'];

}


$dnt=mysqli_query($conn,"select sum(amount) as amount from debit_note where supplier='$supplier' and date between '$aca1[fromdate]' and '$aca1[todate]' and date<'$frm'");

$dnt1=mysqli_fetch_assoc($dnt);


$tex=$py1['amount']+$opcr+$dnt1['amount'];

$trc=$sono+$opdb+$adjs;



if($trc>$tex)

{

$bl=$trc-$tex;



echo"<tr><td colspan='2'>B/F</td><td style='text-align:right;'>".number_format($bl,2,'.','')."</td><td></td><td style='text-align:right;'>".number_format($bl,2,'.','')."</td></tr>";



$db=$bl;

$cr=0;



}

elseif($tex>$trc)

{

$bl=$tex-$trc;



echo"<tr><td colspan='2'>B/F</td><td></td><td style='text-align:right;'>".number_format($bl,2,'.','')."</td><td style='text-align:right;'>-".number_format($bl,2,'.','')."</td></tr>";



$db=0;

$cr=$bl;



}

else

{

$bl=0;

$cr=0;

$db=0;

}





$sql=mysqli_query($conn,"


select time as time, invoiceno as id, date as date, concat('Purchase #: ', invoiceno) as details, NULL as credit, sum(total) as debit, cgst_amt as cgt, sgst_amt as sgt, 'purchase' as type from purchase where supplier='$supplier' and date between '$frm' and '$to' and date between '$aca1[fromdate]' and '$aca1[todate]' group by invoiceno

UNION

select time as time, ID as id, date as date, concat('Payment: ', description, ' #: ', ID) as details, amount as credit, NULL as debit, null as cgt, null as sgt, 'payment' as type from payment where supplier='$supplier' and date between '$frm' and '$to' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select time as time, ID as id, date as date, concat('Opening Balance #: ', ID) as details, amount as credit, NULL as debit, null as cgt, null as sgt, 'opening' as type from creditor_ledger where supplier='$supplier' and paymenttype='credit' and date between '$frm' and '$to' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select time as time, ID as id, date as date, concat('Opening Balance #: ', ID) as details, NULL as credit, amount as debit, null as cgt, null as sgt, 'opening' as type from creditor_ledger where supplier='$supplier' and paymenttype='debit' and date between '$frm' and '$to' and date between '$aca1[fromdate]' and '$aca1[todate]'


order by date asc") or die(mysqli_error($conn));



/*$cr=0;

$db=0;*/

while($result=mysqli_fetch_assoc($sql))

{



$credit=$result['credit'];

if($result['type']=='purchase')
{
$debit=$result['debit']+$result['adjs'];
}
else
{
$debit=$result['debit'];
}



if($debit)$debit=''.number_format($debit,2,'.','');

if($credit)$credit=''.number_format($credit,2,'.','');



$cr=$cr+$result['credit'];

$db=$db+$debit;



$bl=$db-$cr;



echo"<tr><td>".date('d-m-Y',strtotime($result['date']))."</td><td>".$result['details']."</td><td style='text-align:right;'>".$debit."</td><td style='text-align:right;'>".$credit."</td><td style='text-align:right;'>".number_format($bl,2,'.','')."</td></tr>";



}





echo"<tr><td colspan='2' style='text-align:right;'>Total</td><td style='text-align:right;'>".number_format($db,2,'.','')."</td><td style='text-align:right;'>".number_format($cr,2,'.','')."</td><td></td></tr>";



if($db>$cr)

{

$cb=$db-$cr;



echo"<tr><td colspan='2' style='text-align:right;'>Closing Balance</td><td style='text-align:right;'>".number_format($cb,2,'.','')."</td><td style='text-align:right;'></td><td></td></tr>";

}

elseif($cr>$db)

{

$cb=$cr-$db;



echo"<tr><td colspan='2' style='text-align:right;'>Closing Balance</td><td style='text-align:right;'></td><td style='text-align:right;'>".number_format($cb,2,'.','')."</td><td></td></tr>";

}

else

{

echo"<tr><td colspan='2' style='text-align:right;'>Closing Balance</td><td style='text-align:right;'>NIL</td><td style='text-align:right;'>NIL</td><td></td></tr>";

}



echo"</table><br /><br />";





echo"</div>";



}



else

{



echo '<div class="main">';



echo"<h3 style='padding-top:10px; text-align:center;'>Supplier Ledger</h3>";





$sp=mysqli_query($conn,"select * from supplier where ID='$supplier'");

$sp1=mysqli_fetch_assoc($sp);



echo "

<p style='padding-left:15px;'>Supplier : <b>".$sp1['name']."</b></p>

<center><table border='1' style='width:98%; border-collapse:collapse; margin:auto; border:1px solid #999;'>

<thead><th width='10%'>Date</th><th width='40%'>Description</th><th width='15%'>Debit</th><th width='15%'>Credit</th><th width='15%'>Closing Balance</th></thead>";



$sql=mysqli_query($conn,"


select time as time, invoiceno as id, date as date, concat('Purchase #: ', invoiceno) as details, NULL as credit, sum(total) as debit, cgst_amt as cgt, sgst_amt as sgt, 'purchase' as type from purchase where supplier='$supplier' and date between '$aca1[fromdate]' and '$aca1[todate]' group by invoiceno

UNION

select time as time, ID as id, date as date, concat('Payment: ', description, ' #: ', ID) as details, amount as credit, NULL as debit, null as cgt, null as sgt, 'payment' as type from payment where supplier='$supplier' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select time as time, ID as id, date as date, concat('Opening Balance #: ', ID) as details, amount as credit, NULL as debit, null as cgt, null as sgt, 'opening' as type from creditor_ledger where supplier='$supplier' and paymenttype='credit' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select time as time, ID as id, date as date, concat('Opening Balance #: ', ID) as details, NULL as credit, amount as debit, null as cgt, null as sgt, 'opening' as type from creditor_ledger where supplier='$supplier' and paymenttype='debit' and date between '$aca1[fromdate]' and '$aca1[todate]'


order by date asc") or die(mysqli_error($conn));



$cr=0;

$db=0;

while($result=mysqli_fetch_assoc($sql))

{



$credit=$result['credit'];

if($result['type']=='purchase')
{
$debit=$result['debit']+$result['adjs'];
}
else
{
$debit=$result['debit'];
}



if($debit)$debit=''.number_format($debit,2,'.','');

if($credit)$credit=''.number_format($credit,2,'.','');



$cr=$cr+$result['credit'];

$db=$db+$debit;



$bl=$db-$cr;



echo"<tr><td>".date('d-m-Y',strtotime($result['date']))."</td><td>".$result['details']."</td><td style='text-align:right;'>".$debit."</td><td style='text-align:right;'>".$credit."</td><td style='text-align:right;'>".number_format($bl,2,'.','')."</td></tr>";



}





echo"<tr><td colspan='2' style='text-align:right;'>Total</td><td style='text-align:right;'>".number_format($db,2,'.','')."</td><td style='text-align:right;'>".number_format($cr,2,'.','')."</td><td></td></tr>";



if($db>$cr)

{

$cb=$db-$cr;



echo"<tr><td colspan='2' style='text-align:right;'>Closing Balance</td><td style='text-align:right;'>".number_format($cb,2,'.','')."</td><td style='text-align:right;'></td><td></td></tr>";

}

elseif($cr>$db)

{

$cb=$cr-$db;



echo"<tr><td colspan='2' style='text-align:right;'>Closing Balance</td><td style='text-align:right;'></td><td style='text-align:right;'>".number_format($cb,2,'.','')."</td><td></td></tr>";

}

else

{

echo"<tr><td colspan='2' style='text-align:right;'>Closing Balance</td><td style='text-align:right;'>NIL</td><td style='text-align:right;'>NIL</td><td></td></tr>";

}



echo"</table><br /><br />";





echo"</div>";		



}//fromdate==''



}//get supplier





else

{

echo"

<table style='margin:auto;'><tr><td><h4 style='font-size:15px;'>Supplier Ledger</h4></td></tr></table>

<center><table border='1' style='width:98%; border-collapse:collapse; margin:auto; border:1px solid #999;'>

<thead><th width='6%'>S.No</th><th width='40%'>Ledger Name</th><th width='18%'>Total Purchased</th><th width='18%'>Total Paid</th><th width='18%'>Closing Balance</th></thead>";

$sno=0;

$totb=0;

$totcre=0;

$totdeb=0;

$get="select * from supplier order by name ASC";

$get1=mysqli_query($conn,$get) or die("Could Not Get Supplier Details");

	while($getsite5=mysqli_fetch_assoc($get1))

		{

		

		

		$sl=mysqli_query($conn,"select sum(total) as amount from purchase where supplier='$getsite5[ID]' and date between '$aca1[fromdate]' and '$aca1[todate]'");

		$sl1=mysqli_fetch_assoc($sl);

		

		$re=mysqli_query($conn,"select sum(amount) as amount from payment where supplier='$getsite5[ID]' and date between '$aca1[fromdate]' and '$aca1[todate]'");

		$re1=mysqli_fetch_assoc($re);

		

		$obl=mysqli_query($conn,"select * from creditor_ledger where supplier='$getsite5[ID]' and date between '$aca1[fromdate]' and '$aca1[todate]'");

		$num=mysqli_num_rows($obl);

		$obl1=mysqli_fetch_assoc($obl);


		
		$adjs=0;
		$cgst=0;
		$sgst=0;
		$loi=mysqli_query($conn,"select * from purchase where supplier='$getsite5[ID]' and date between '$aca1[fromdate]' and '$aca1[todate]' group by invoiceno");
		while($loi1=mysqli_fetch_assoc($loi))
		{
			$adjs=$adjs+$loi1['adjustment'];
			$cgst=$cgst+$loi1['cgst_amt'];
			$sgst=$sgst+$loi1['sgst_amt'];
		}

		

		$credit=0;

		$debit=0;

		if($obl1['paymenttype']=='credit')

		{

		$credit=$obl1['amount'];

		$debit=0;

		}

		elseif($obl1['paymenttype']=='debit')

		{

		$credit=0;

		$debit=$obl1['amount'];

		}

		

		$deb=$sl1['amount']+$debit+$adjs;

		$cre=$re1['amount']+$credit;

		

		$cbl=$sl1['amount']-$re1['amount']-$credit+$debit+$adjs;

		

		/*if($cbl!=0)

		{*/

		

		if($deb!='0' && $deb!='')

		{

		$deb=number_format($deb,2,'.','');

		}

		else

		{

		$deb='';

		}

		

		if($cre!='0' && $cre!='')

		{

		$cre=number_format($cre,2,'.','');

		}

		else

		{

		$cre='';

		}		

		if($cbl!=0)
		{
		$sno=$sno+1;

		echo "<tr><td>".$sno.".</td><td>".$getsite5['name']."</a></td><td style='text-align:right;'>".$deb."</td><td style='text-align:right;'>".$cre."</td><td style='text-align:right;'>".number_format($cbl,2,'.','')."</td></tr>";

		

        $totcre=$totcre+$cre;

		$totdeb=$totdeb+$deb;

		$totb=$totb+$cbl;
		}

		//}

			 

		}

echo '<tr><td colspan="2" style="text-align:right;">Total</td><td style="text-align:right;">'.number_format($totdeb,2,'.','').'</td><td style="text-align:right;">'.number_format($totcre,2,'.','').'</td><td style="text-align:right;">'.number_format($totb,2,'.','').'</td></tr></table></center>';
	


}//supplier==''



}



else



{







 // Not logged in



  print "<META HTTP-EQUIV = 'Refresh' Content = '1; URL =login.php'>";



}






?>
