<?PHP



session_start();



include 'connect.php';



if(isset($_SESSION['adminemail']))



{



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['customer'])

{



$customer=$_GET['customer'];

$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;'>Customer Ledger</h3>";





$cp=mysql_query("select * from party where ID='$customer'");

$cp1=mysql_fetch_array($cp);



echo "

<table style='width:97%; margin:auto;'><tr><td>Customer : <b>".$cp1['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=mysql_query("select sum(amount) as amount from receipt where date<'$frm' and party='$customer'");

$py1=mysql_fetch_array($py);



$sl=mysql_query("select sum(total) as amount from invoice where date<'$frm' and party='$customer'");

$sl1=mysql_fetch_array($sl);

		

$cgst=0;

$sgst=0;

$igst=0;

$adjs=0;

$d=mysql_query("select * from invoice where date<'$frm' and party='$customer' group by invoiceno");

while($d1=mysql_fetch_array($d))

{

$cgst=$cgst+$d1['cgst_amt'];

$sgst=$sgst+$d1['sgst_amt'];

$igst=$igst+$d1['igst_amt'];

}



$opl=mysql_query("select * from debitor_ledger where date<'$frm' and customer='$customer'");

$opl1=mysql_fetch_array($opl);



$opcr=0;

$opdb=0;

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

{

$opdb=$opl1['amount'];

}

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

{

$opcr=$opl1['amount'];

}



$tex=$sl1['amount']+$cgst+$sgst+$igst+$opcr;

$trc=$py1['amount']+$opdb;



$tex=number_format($tex,0,'.','').'.00';

$trc=number_format($trc,0,'.','').'.00';



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=mysql_query("



select invoiceno as id, date as date, time as time, concat('Invoice #: ') as details, sum(total) as credit, NULL as debit, cgst_amt as cgs, sgst_amt as sgs, igst_amt as igs, 'invoice' as type from invoice where party='$customer' and date between '$frm' and '$to' group by invoiceno

UNION

select ID as id, date as date, time as time, concat('Receipt:', description , ' #: ', ID) as details, NULL as credit, amount as debit, null as cgs, null as sgs, null as igs, 'receipt' as type from receipt where party='$customer' and date between '$frm' and '$to'

UNION

select ID as id, date as date, time as time, concat('Opening Balance #: ', ID) as details, amount as credit, NULL as debit, null as cgs, null as sgs, null as igs, 'opening' as type from debitor_ledger where customer='$customer' and paymenttype='credit' and date between '$frm' and '$to'

UNION

select ID as id, date as date, time as time, concat('Opening Balance #: ', ID) as details, NULL as credit, amount as debit, null as cgs, null as sgs, null as igs, 'opening' as type from debitor_ledger where customer='$customer' and paymenttype='debit' and date between '$frm' and '$to'



order by date asc, time asc");



/*$cr=0;

$db=0;*/

while($result=mysql_fetch_array($sql))

{



if($result['type']=='invoice')

{

$cred=$result['credit']+$result['cgs']+$result['sgs']+$result['igs']+$result['adjs'];

}

else

{

$cred=$result['credit'];

}



$debit=$result['debit'];



if($debit)$debit=''.number_format($debit,0,'.','').'.00';



if($cred!=0)

{

$credit=number_format($cred,0,'.','').'.00';

}

else

{

$credit='';

}



$cr=$cr+$credit;

$db=$db+$debit;



$bl=$db-$cr;



if($result['type']=='invoice')

{

	$sl=mysql_query("select * from invoice where invoiceno='$result[id]'");

	$sl1=mysql_fetch_array($sl);

	

	if($sl1['billtype']=='manual')

	{

	$iino=$sl1['invno'];

	}

	else

	{

	$iino=$sl1['billno'];

	}

}



echo"<tr><td>".date('d-m-Y',strtotime($result['date']))."</td><td>".$result['details']."";



if($result['type']=='invoice')

{

	echo"$iino";

}



echo"</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>";

}

elseif($cr==$db)

{

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;'>Customer Ledger</h3>";





$cp=mysql_query("select * from party where ID='$customer'");

$cp1=mysql_fetch_array($cp);



echo "

<p style='padding-left:15px;'>Customer : <b>".$cp1['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=mysql_query("



select invoiceno as id, date as date, time as time, concat('Invoice #: ') as details, sum(total) as credit, NULL as debit, cgst_amt as cgs, sgst_amt as sgs, igst_amt as igs, 'invoice' as type from invoice where party='$customer' group by invoiceno

UNION

select ID as id, date as date, time as time, concat('Receipt: ', description, ' #: ', ID) as details, NULL as credit, amount as debit, null as cgs, null as sgs, null as igs, 'receipt' as type from receipt where party='$customer'

UNION

select ID as id, date as date, time as time, concat('Opening Balance #: ', ID) as details, amount as credit, NULL as debit, null as cgs, null as sgs, null as igs, 'opening' as type from debitor_ledger where customer='$customer' and paymenttype='credit'

UNION

select ID as id, date as date, time as time, concat('Opening Balance #: ', ID) as details, NULL as credit, amount as debit, null as cgs, null as sgs, null as igs, 'opening' as type from debitor_ledger where customer='$customer' and paymenttype='debit'



order by date asc, time asc") or die(mysql_error());



$cr=0;

$db=0;

while($result=mysql_fetch_array($sql))

{



if($result['type']=='invoice')

{

$cred=$result['credit']+$result['cgs']+$result['sgs']+$result['igs']+$result['adjs'];

}

else

{

$cred=$result['credit'];

}



$debit=$result['debit'];



if($debit)$debit=''.number_format($debit,0,'.','').'.00';



if($cred!=0)

{

$credit=number_format($cred,0,'.','').'.00';

}

else

{

$credit='';

}



$cr=$cr+$credit;

$db=$db+$debit;



$bl=$db-$cr;



if($result['type']=='invoice')

{

	$sl=mysql_query("select * from invoice where invoiceno='$result[id]'");

	$sl1=mysql_fetch_array($sl);

	

	$iino=$sl1['billno'];	

}



echo"<tr><td>".date('d-m-Y',strtotime($result['date']))."</td><td>".$result['details']."";



if($result['type']=='invoice')

{

	echo"$iino";

}



echo"</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>";

}

elseif($cr==$db)

{

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 customer



else

{



echo"

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

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

<thead><th width='40%'>Ledger Name</th><th width='14%'>Total Received</th><th width='14%'>Total Invoiced</th><th width='14%'>Closing Balance</th></thead>";



$totb=0;

$totcre=0;

$totdeb=0;

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

$get1=mysql_query($get) or die("Could Not Get Customer Details");

	while($getsite5=mysql_fetch_array($get1))

		{

		

		$sl=mysql_query("select sum(total) as amount from invoice where party='$getsite5[ID]'");

		$sl1=mysql_fetch_array($sl);

		

		$re=mysql_query("select sum(amount) as amount from receipt where party='$getsite5[ID]'");

		$re1=mysql_fetch_array($re);

		

		$cgst=0;

		$sgst=0;

		$igst=0;

		$adjs=0;

		$d=mysql_query("select * from invoice where party='$getsite5[ID]' group by invoiceno");

		while($d1=mysql_fetch_array($d))

		{

		$cgst=$cgst+$d1['cgst_amt'];

		$sgst=$sgst+$d1['sgst_amt'];

		$igst=$igst+$d1['igst_amt'];

		$adjs=$adjs+$d1['adjustment'];

		}

		

		$obl=mysql_query("select * from debitor_ledger where customer='$getsite5[ID]'");

		$num=mysql_num_rows($obl);

		$obl1=mysql_fetch_array($obl);

		

		$credit=0;

		$debit=0;

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

		{

		$credit=$obl1['amount'];

		$debit=0;

		}

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

		{

		$credit=0;

		$debit=$obl1['amount'];

		}		

				

		//$cbl=$re1['amount']+$totdis+$totdis1-$sl1['amount']-$lbl1['amount']-$credit+$debit;

		

		//echo"$sl1[amount]";

		

		$cre=$sl1['amount']+$credit+$cgst+$sgst+$igst+$adjs;

		$deb=$re1['amount']+$debit;

				

		/*if($cbl!=0)

		{*/

		

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

		{

		$deb=number_format($deb,0,'.','').'.00';

		}

		else

		{

		$deb='';

		}

		

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

		{

		$cre=number_format($cre,0,'.','').'.00';

		}

		else

		{

		$cre='';

		}		

		

		$cbl=$deb-$cre;

		

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

		

        $totcre=$totcre+$cre;

		$totdeb=$totdeb+$deb;

		$totb=$totb+$cbl;

		//}

			 

		}

echo '<tr><td 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>';





}//customer==''



}



else



{







 // Not logged in



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



}







?>

