<?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;

text-transform:capitalize;

} 

</style>';



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





echo "

<table style='padding-left:30px;'><tr><td><b>Period :</b> ".$fromdate." to ".$todate."</td></tr></table>

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

<thead><th width='10%'>Date</th><th width='30%'>Description</th><th style='width:12%;'>Type</th><th style='width:10%;'>Voucher No</th><th width='12%'>Debit</th><th width='12%'>Credit</th><th width='12%'>Closing Balance</th></thead>";





$ex=mysqli_query($conn,"select sum(amount) as amount from expense where date<'$frm' and date between '$aca1[fromdate]' and '$aca1[todate]' and paymentmode='Cash'");

$ex1=mysqli_fetch_assoc($ex);


$rc=mysqli_query($conn,"select sum(amount) as amount from receipt where date<'$frm' and date between '$aca1[fromdate]' and '$aca1[todate]' and paymentmode='Cash'");

$rc1=mysqli_fetch_assoc($rc);



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

$pym1=mysqli_fetch_assoc($pym);



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

$opl1=mysqli_fetch_assoc($opl);



$jur=mysqli_query($conn,"select sum(amount) as amount from journal where date<'$frm' and date between '$aca1[fromdate]' and '$aca1[todate]' and debit='petty cash'");

$jur1=mysqli_fetch_assoc($jur);



$juri=mysqli_query($conn,"select sum(amount) as amount from journal where date<'$frm' and date between '$aca1[fromdate]' and '$aca1[todate]' and credit='petty cash'");

$juri1=mysqli_fetch_assoc($juri);



$opcr=0;

$opdb=0;

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

{

$opdb=$opl1['amount'];

}

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

{

$opcr=$opl1['amount'];

}



$tex=$ex1['amount']+$py1['amount']+$opcr+$juri1['amount']+$pym1['amount'];

$trc=$rc1['amount']+$jur1['amount']+$opdb;



if($trc>$tex)

{

$bl=$trc-$tex;



echo"<tr><td colspan='3'>B/F</td><td></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='3'>B/F</td><td></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, ID as ID, date as date, name as name, null as debit, amount as credit, CONCAT(description) as details, 'Expense' as type from expense where paymentmode='Cash' 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, customer as name, amount as debit, NULL as credit, CONCAT(description) as details, 'Receipt' as type from receipt where paymentmode='Cash' 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, supplier as name, NULL as debit, amount as credit, CONCAT(description) as details, 'Payment' as type from payment where paymentmode='Cash' and date between '$frm' and '$to' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select NULL as time, ID as ID, date as date, NULL as name, NULL as debit, amount as credit, CONCAT('Opening Balance') as details, 'Opening Balance' as type from cash_ledger where paymenttype='credit' and date between '$frm' and '$to' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select NULL as time, ID as ID, date as date, NULL as name, amount as debit, NULL as credit, CONCAT('Opening Balance') as details, 'Opening Balance' as type from cash_ledger where paymenttype='debit' 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, NULL as name, NULL as debit, amount as credit, CONCAT(description) as details, 'Journal' as type from journal where credit='petty cash' 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, NULL as name, amount as debit, NULL as credit, CONCAT(description) as details, 'Journal' as type from journal where debit='petty cash' and date between '$frm' and '$to' and date between '$aca1[fromdate]' and '$aca1[todate]'

order by date asc , time asc");



/*$cr=0;

$db=0;*/

while($result=mysqli_fetch_assoc($sql))

{



$credit=$result['credit'];

$debit=$result['debit'];



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

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



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

{

$gn=mysqli_query($conn,"select * from customer where ID='$result[name]'");

$gn1=mysqli_fetch_assoc($gn);

}

elseif($result['type']=='Payment')

{

$gnn=mysqli_query($conn,"select * from supplier where ID='$result[name]'");

$gnn1=mysqli_fetch_assoc($gnn);

}

elseif($result['type']=='Wages')

{

$wgn=mysqli_query($conn,"select * from weaver where ID='$result[name]'");

$wgn1=mysqli_fetch_assoc($wgn);

}



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

$db=$db+$result['debit'];



$bl=$db-$cr;



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



if($result['type']!='Opening Balance' && $result['type']!='Journal')

{

echo"".$result['details']." ";



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

{

 echo"from ";

 echo"".$gn1['name']."";

}

elseif($result['type']=='Payment')

{

 echo"to "; 

 echo"".$gnn1['name']."";

} 

elseif($result['type']=='Wages')

{

 echo"to ";

 echo"".$wgn1['name']."";

}

elseif($result['type']=='Expense')

{

 echo"to ";

 

 echo"".$result['name']."";

} 

 



}



else

{

echo"".$result['details']." ";

}



echo" </td><td>".$result['type']."</td><td>".$result['ID']."</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='4' 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='4' 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='4' 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='4' 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;'>Cash Ledger</h3>";





echo "<center><table border='1' style='border-collapse:collapse; boder-color:#999; width:95%; margin:auto; border:1px solid #999;'>

<thead><th width='10%'>Date</th><th width='30%'>Description</th><th style='width:12%;'>Type</th><th style='width:10%;'>Voucher No</th><th width='12%'>Debit</th><th width='12%'>Credit</th><th width='12%'>Closing Balance</th></thead>";



$sql=mysqli_query($conn,"

select time as time, ID as ID, date as date, name as name, null as debit, amount as credit, CONCAT(description) as details, 'Expense' as type from expense where paymentmode='Cash' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select time as time, ID as ID, date as date, customer as name, amount as debit, NULL as credit, CONCAT(description) as details, 'Receipt' as type from receipt where paymentmode='Cash' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select time as time, ID as ID, date as date, supplier as name, NULL as debit, amount as credit, CONCAT(description) as details, 'Payment' as type from payment where paymentmode='Cash' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select NULL as time, ID as ID, date as date, NULL as name, NULL as debit, amount as credit, CONCAT('Opening Balance') as details, 'Opening Balance' as type from cash_ledger where paymenttype='credit' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select NULL as time, ID as ID, date as date, NULL as name, amount as debit, NULL as credit, CONCAT('Opening Balance') as details, 'Opening Balance' as type from cash_ledger where paymenttype='debit' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select time as time, ID as ID, date as date, NULL as name, NULL as debit, amount as credit, CONCAT(description) as details, 'Journal' as type from journal where credit='petty cash' and date between '$aca1[fromdate]' and '$aca1[todate]'

UNION

select time as time, ID as ID, date as date, NULL as name, amount as debit, NULL as credit, CONCAT(description) as details, 'Journal' as type from journal where debit='petty cash' and date between '$aca1[fromdate]' and '$aca1[todate]'

order by date asc , time asc");



$cr=0;

$db=0;

while($result=mysqli_fetch_assoc($sql))

{



$credit=$result['credit'];

$debit=$result['debit'];



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

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



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

{

$gn=mysqli_query($conn,"select * from customer where ID='$result[name]'");

$gn1=mysqli_fetch_assoc($gn);

}

elseif($result['type']=='Payment')

{

$gnn=mysqli_query($conn,"select * from supplier where ID='$result[name]'");

$gnn1=mysqli_fetch_assoc($gnn);

}

elseif($result['type']=='Wages')

{

$wgn=mysqli_query($conn,"select * from weaver where ID='$result[name]'");

$wgn1=mysqli_fetch_assoc($wgn);

}



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

$db=$db+$result['debit'];



$bl=$db-$cr;



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



if($result['type']!='Opening Balance' && $result['type']!='Journal')

{

echo"".$result['details']." ";



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

{

 echo"from ";

 echo"".$gn1['name']."";

}

elseif($result['type']=='Payment')

{

 echo"to "; 

 echo"".$gnn1['name']."";

} 

elseif($result['type']=='Wages')

{

 echo"to ";

 echo"".$wgn1['name']."";

}

elseif($result['type']=='Expense')

{

 echo"to ";

 

 echo"".$result['name']."";

} 

 



}



else

{

echo"".$result['details']." ";

}



echo" </td><td>".$result['type']."</td><td>".$result['ID']."</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='4' 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='4' 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='4' 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='4' 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==''	





}



else



{







 // Not logged in



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



}







?>

