invisio v2.0

<?php
session_start();
include ('config.php');

$fdate = $_POST['fdate']; 
$_GET['fdate'] = $fdate;

$tdate = $_POST['tdate'];
$_GET['tdate'] = $tdate;

$stat = 'Inactive';

$bank = $_POST['bank'];
$_GET['bank'] = $bank;

include "bankopening.php";

$sql2 = $js->prepare('SELECT * FROM bankaccounts WHERE bankid = :bank ');
	$sql2->execute(array(':bank' => $bank));
	while($row2 = $sql2->fetch(PDO::FETCH_ASSOC))
	{
		$bankname = $row2['bankname'];
		$account = $row2['accountno'];
		$bankbr = $row2['bankbranch'];
	}

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<style type="text/css">
@page {
    size: A4;
    margin: .6in;
	size:landscape;
}

@media print {
  #table{
    max-width: 2480px;
    width:100%;
  }
  #table td{
    width: auto;
    overflow: hidden;
    word-wrap: break-word;
  }
	.noprint {display:none;}
}
</style>
<script>
function myFunction() {
    window.print();
}
</script>

<title>SOA - <?php echo $account?> From <?php echo date('d-m-Y', strtotime($fdate));?> To <?php echo date('d-m-Y', strtotime($tdate));?></title>
</head>
<body>
<div align="center" style="background-color:#FFF">
	<img src="images/logodmc.png"><HR  style="border-color:#5053FD" />
	<h3>Statement of Account from  <?php echo date('d-m-Y', strtotime($fdate));?> To <?php echo date('d-m-Y', strtotime($tdate));?></h3>
	<table style="border-collapse: collapse; "
		id="table" border="1" cellpadding="5">
		<tr style="background-color:#555; color:#fff">
			<th>Bank Name</th>	
			<th>Account No</th>
			<th>Currency</th>
			<th>Branch</th>
		</tr>
		<tr style="background-color:#ccc;">
			<td><?php echo $bankname ?></td>
			<td><?php echo $account ?></td>
			<td>OMR</td>
			<td><?php echo $bankbr ?></td>
		</tr>
	</table><br>
	<table style="margin-left:350px; width:35%; height:40px;">
		<tr style="background-color:#EEEEEE;">
		<td align="center"><strong>Opening Balance as on <?php echo date('d-m-Y', strtotime($fdate));?></strong></td>
		<td align='right' style="padding-right:10px; color:red;"><strong><?php echo $pclosing?></strong></td>
	</tr>
	</table>
	<br>
<table
		style="border-collapse: collapse; "
		id="table" border="1" cellpadding="5">
	<thead>
		<tr style="background-color:#593E26; color:#fff;">
			<th style="width:15%">Date</th>
			<th>Particulars</th>
            <th>Paid To</th>
			<th>Mode</th>
			<th>Reference No</th>
			<th>Debit</th>
			<th>Credit</th>
			<th>Balance</th>
		</tr>
	</thead>
	
<?php
$totcredit = 0;
$totdebit = 0;
$c=1;
$begin = new DateTime( $fdate );
$end   = new DateTime( $tdate );

for($i = $begin; $i <= $end; $i->modify('+1 day'))
{
    $sdates = $i->format("Y-m-d");

//echo $sdates."<br>";
	
	$result = $js->prepare('SELECT * FROM `income` WHERE `creditdate` = :sdates AND `banks` = :bank  ');
	$result->execute(array(':sdates' => $sdates,
							':bank' => $bank));
		while($row = $result->fetch(PDO::FETCH_ASSOC)) 
		{
			$incid = $row['id'];
			$custid = $row['custid'];
			
			if($custid == 'OTHERS')
			{
				$custname = 'OTHER INCOME';	
			}
			else
			{
				$sqlc = $js->prepare('SELECT * FROM customer_entry WHERE custid = :custid ');
				$sqlc->execute(array(':custid' => $custid));
				while($rowc = $sqlc->fetch(PDO::FETCH_ASSOC))
				{
					$custname = $rowc['custname'];
				}
			}
			$sdate = date('d-m-Y', strtotime($row['creditdate']));
			$totalpayment = $row['incamount'];
			$incheads = $row['incheads'];
			$totcredit += $totalpayment;
			$gross = $totalpayment;
			$remarks = $row['premarks'];
			$mode = $row['pmode'];
			/*$vat = $row['vat'];
			$status = $row['status'];
			$gross = $totalpayment + ($totalpayment * $vat / 100);
			$totcredit += $gross;*/
			
			
			if($c % 2 == '0')
			{
				echo "<tr style='background-color:#EEEEEE'>";
			}
			else
			{
				echo "<tr style='background-color:#CCC'>";
			}
				echo "<td align='center'>$sdate</td>";
				echo "<td>Paid By $custname - $incheads";
				echo "<td>Bank Muscat <br> 0440 0684 5761 0012</td>";
				echo "<td>$mode</td>";
				echo "<td align='center'>$remarks</td>";
				
				echo "<td></td>";
				echo "<td align='right' style='padding-right:10px; color:#0179C1'><a href='printincomevoucher.php?id=$incid' target='_blank'>$gross</a></td>";
				/*echo "<td align='right' style='padding-right:10px; color:#0179C1'>$gross</td>";*/
				$balance = $pclosing + $totcredit - $totdebit;
				$piecest = explode(".", $balance);
			$mainmenut = $piecest[0];
			$submenut =  $piecest[1];
			$strt= strlen($submenut);
			if($strt == '1')
				$submenut .= '00';
			elseif($strt == '2')
				$submenut .= '0';
			elseif($strt == '0')
				$submenut .= '000';
			else{}
			if($mainmenut == '0')
				$mainmenut = '00';
			$balance = $mainmenut.".".$submenut;
			$roundbalance = round($balance, 3);
			echo "<td  align='right' style='padding-right:11px; color:green'><strong>$roundbalance</strong></td>";
			echo "</tr>";
				$c++;
		}
		
	$resultip = $js->prepare('SELECT * FROM `invoicepayments` WHERE `paydate` = :sdates AND `banks` = :bank ORDER BY `paydate`  ');
	$resultip->execute(array(':sdates' => $sdates,
							':bank' => $bank));
		while($rowip = $resultip->fetch(PDO::FETCH_ASSOC)) 
		{
			$ipinvid = $rowip['invoiceid'];
			$payid = $rowip['id'];
			
			$sqlcin = $js->prepare('SELECT * FROM invoiced WHERE id = :ipinvid ');
				$sqlcin->execute(array(':ipinvid' => $ipinvid));
				while($rowcin = $sqlcin->fetch(PDO::FETCH_ASSOC))
				{
					$cust = $rowcin['cust'];
					$rremarks = $rowcin['descr'];
				}
			
			
				$sqlc = $js->prepare('SELECT * FROM customer_entry WHERE id = :cust ');
				$sqlc->execute(array(':cust' => $cust));
				while($rowc = $sqlc->fetch(PDO::FETCH_ASSOC))
				{
					$custname = $rowc['custname'];
				}
			$sdate = date('d-m-Y', strtotime($rowip['paydate']));
			$totalpaymentin = $rowip['payamount'];

$totcredit += $totalpaymentin;
			$gross = $totalpaymentin;
			
			$mode = $rowip['mode'];
			$ref = $rowip['remarks'];
			/*$vat = $row['vat'];
			$status = $row['status'];
			$gross = $totalpayment + ($totalpayment * $vat / 100);
			$totcredit += $gross;*/
			
			
			if($c % 2 == '0')
			{
				echo "<tr style='background-color:#EEEEEE'>";
			}
			else
			{
				echo "<tr style='background-color:#CCC'>";
			}
				echo "<td align='center'>$sdate</td>";
				echo "<td>Paid By $custname - $rremarks";
				echo "<td>$bankname <br> $account</td>";
				
				echo "<td>$mode</td>";
				echo "<td>$ref</td>"; 
				echo "<td></td>";
				echo "<td align='right' style='padding-right:10px; color:#0179C1'><a href='print_invoicereceipt.php?payment=$payid' target='_blank'>$gross</a></td>";
				/*echo "<td align='right' style='padding-right:10px; color:#0179C1'>$gross</td>";*/
				$balance = $pclosing + $totcredit - $totdebit;
				$piecest = explode(".", $balance);
			$mainmenut = $piecest[0];
			$submenut =  $piecest[1];
			$strt= strlen($submenut);
			if($strt == '1')
				$submenut .= '00';
			elseif($strt == '2')
				$submenut .= '0';
			elseif($strt == '0')
				$submenut .= '000';
			else{}
			if($mainmenut == '0')
				$mainmenut = '00';
			$balance = $mainmenut.".".$submenut;
			$roundbalance = round($balance, 3);
			echo "<td  align='right' style='padding-right:11px; color:green'><strong>$roundbalance</strong></td>";
			echo "</tr>";
				$c++;
		}

$resultq = $js->prepare('SELECT * FROM `expenses` WHERE expdate = :sdates AND banks = :bank ');
	$resultq->execute(array(':sdates' => $sdates,
							':bank' => $bank)); 
	while($rowq = $resultq->fetch(PDO::FETCH_ASSOC)) 
	{
		$expid = $rowq['id'];
		$sdate = date('d-m-Y', strtotime($rowq['expdate']));
		$voucherno = $rowq['expid'];
		$paidto = $rowq['vatpaidto'];
		$ramount = $rowq['expamount'];
		$totdebit += $ramount;
		$balance = $pclosing + $totcredit - $totdebit;
				
		$expheads = $rowq['expheads'];
		$result1 = $js->prepare('SELECT * FROM `heads` WHERE headid = :expheads');
		$result1->execute(array(':expheads' => $expheads));	
		while($row1 = $result1->fetch(PDO::FETCH_ASSOC))
		{
			$heads = $row1['name'];	
		}
		
		$expsubheads = $rowq['expsubheads'];
		if($expsubheads != '')
		{
			$result2 = $js->prepare('SELECT * FROM `subheads` WHERE subid = :expsubheads');
			$result2->execute(array(':expsubheads' => $expsubheads));	
			while($row2 = $result2->fetch(PDO::FETCH_ASSOC))
			{
				$subheads = $row2['subname'];	
				$against = $heads." for ".$subheads;
			}
		}
		
		else
		{
			$against = $heads;
		}
		
		$mode = $rowq['pmode'];
		$remarks = $rowq['premarks'];
		if($c % 2 == '0')
		{
			echo "<tr style='background-color:#EEEEEE'>";
		}
		else
		{
			echo "<tr style='background-color:#CCC'>";
		}
			echo "<td align='center'>$sdate <br>$voucherno</td>";
			echo "<td>Paid Against $against</td>";
			echo "<td>$paidto</td>";
			echo "<td>$mode</td>";
			echo "<td align='center'>$remarks</td>";
			
			echo "<td align='right' style='padding-right:10px; color:#E62229'><a href='printexpensevoucher.php?id=$expid' target='_blank'>$ramount</a></td>";
			/*echo "<td align='right' style='padding-right:10px; color:#E62229'>$ramount</td>";*/
			echo "<td></td>";
			$balance = $pclosing + $totcredit - $totdebit;
			$piecest = explode(".", $balance);
		$mainmenut = $piecest[0];
		$submenut =  $piecest[1];
		$strt= strlen($submenut);
		if($strt == '1')
			$submenut .= '00';
		elseif($strt == '2')
			$submenut .= '0';
		elseif($strt == '0')
			$submenut .= '000';
		else{}
		if($mainmenut == '0')
			$mainmenut = '00';
		$balance = $mainmenut.".".$submenut;
		$roundbalance = round($balance, 3);
		echo "<td  align='right' style='padding-right:11px; color:green'><strong>$roundbalance</strong></td>";
		echo "</tr>";
			$c++;
			
	}
	
	
	$resultqex = $js->prepare('SELECT * FROM `customer_expense` WHERE paydate = :sdates AND banks = :bank ');
	$resultqex->execute(array(':sdates' => $sdates,
							':bank' => $bank)); 
	while($rowqq = $resultqex->fetch(PDO::FETCH_ASSOC)) 
	{
		$sdate = date('d-m-Y', strtotime($rowqq['paydate']));
		$voucherno = $rowqq['expid'];
		$qcust = $rowqq['custid'];
		$mode = $rowqq['pmode'];
		$remarks = $rowqq['premarks'];
		$pdescr = $rowqq['pdescription'];
		$payhead = strtoupper($rowqq['payhead']);
		$py = $rowqq['payhead'];
		if($py == 'hotel')
		{
			$hid = $rowqq['paysubhead'];
			
			$resultserp = $js->prepare('SELECT * FROM `hotelreservations` WHERE id = :hid ORDER BY `cindate` ');
		  	$resultserp->execute(array(':hid' => $hid));
			while($rowser = $resultserp->fetch(PDO::FETCH_ASSOC))
			{
				$qhot = $rowser['hotel'];
			}
			$sqlcqh = $js->prepare('SELECT * FROM hotelvendor WHERE id = :qhot');
			$sqlcqh->execute(array(':qhot' => $qhot));
			while($rowcqh = $sqlcqh->fetch(PDO::FETCH_ASSOC))
			{
				$qhotname = $rowcqh['hotelname'];
			}
		}
		elseif($py == 'transportation')
		{
			$qhotname	= $rowqq['paidto'];
		}
		elseif($py == 'others')
		{
			$hid = $rowqq['paysubhead'];
			$remarks = $rowqq['pdescription'];
			$resultserpo = $js->prepare('SELECT * FROM `otherservices` WHERE id = :hid ');
		  	$resultserpo->execute(array(':hid' => $hid));
			while($rowsero = $resultserpo->fetch(PDO::FETCH_ASSOC))
			{
				$qhotname = $rowsero['services'];
			}
			
		}
		else{}
		
		$sqlcq = $js->prepare('SELECT * FROM customer_entry WHERE id = :qcust ');
		$sqlcq->execute(array(':qcust' => $qcust));
		while($rowcq = $sqlcq->fetch(PDO::FETCH_ASSOC))
		{
			$qcustname = $rowcq['custname'];
		}
		$paidto = $rowqq['paidto'];
		$ramount = $rowqq['payamount'];
		$totdebit += $ramount;
		$balance = $pclosing + $totcredit - $totdebit;
				
		$expheads = $rowq['expheads'];
		$result1 = $js->prepare('SELECT * FROM `heads` WHERE headid = :expheads');
		$result1->execute(array(':expheads' => $expheads));	
		while($row1 = $result1->fetch(PDO::FETCH_ASSOC))
		{
			$heads = $row1['name'];	
		}
		
		$expsubheads = $rowq['expsubheads'];
		if($expsubheads != '')
		{
			$result2 = $js->prepare('SELECT * FROM `subheads` WHERE subid = :expsubheads');
			$result2->execute(array(':expsubheads' => $expsubheads));	
			while($row2 = $result2->fetch(PDO::FETCH_ASSOC))
			{
				$subheads = $row2['subname'];	
				$against = $heads." for ".$subheads;
			}
		}
		
		else
		{
			$against = $heads;
		}
		
		
		if($c % 2 == '0')
		{
			echo "<tr style='background-color:#EEEEEE'>";
		}
		else
		{
			echo "<tr style='background-color:#CCC'>";
		}
			echo "<td align='center'>$sdate <br>$voucherno</td>";
			if($payhead == 'HOTEL')
			{
				echo "<td>Paid to $qhotname Against $qcustname </td>";
			}
			elseif($payhead == 'OTHERS')
			{
				echo "<td>Paid for $qhotname  Against $qcustname </td>";	
			}
			elseif($payhead == 'TRANSPORTATION')
			{
				echo "<td>Paid Against TRANSPORTATION CHARGES of $qcustname</td>";	 
			}
			else{
				echo "<td>Paid Against $pdescr of $qcustname</td>";	
				}
		//	echo "<td>Paid for $payhead  Against $qcustname To $qhotname</td>";
			echo "<td>$paidto</td>";
			echo "<td>$mode</td>";
			echo "<td align='center'>$remarks</td>";
			
			echo "<td align='right' style='padding-right:10px; color:#E62229'><a href='print_custexpvoucher.php?id=$voucherno' target='_blank'>$ramount</a></td>";
			/*echo "<td align='right' style='padding-right:10px; color:#E62229'>$ramount</td>";*/
			echo "<td></td>";
			$balance = $pclosing + $totcredit - $totdebit;
			$piecest = explode(".", $balance);
		$mainmenut = $piecest[0];
		$submenut =  $piecest[1];
		$strt= strlen($submenut);
		if($strt == '1')
			$submenut .= '00';
		elseif($strt == '2')
			$submenut .= '0';
		elseif($strt == '0')
			$submenut .= '000';
		else{}
		if($mainmenut == '0')
			$mainmenut = '00';
		$balance = $mainmenut.".".$submenut;
		$roundbalance = round($balance, 3);
		echo "<td  align='right' style='padding-right:11px; color:green'><strong>$roundbalance</strong></td>";
		echo "</tr>";
			$c++;
			
	}

}

$invid = '';
	$services = '';
	$mode = '';
				
//echo "total credit".$totcredit."<br>";
//echo "total debit".$totdebit;
$closing = $totcredit - $totdebit;

?>

</table>
<div class="noprint">
	<p align="center">
    	<a href="#" onClick="myFunction()" style="border:5px solid; background-color:#093; color:#FFF; padding:8px; text-decoration:none;">PRINT</a>
        
        <a href="excel_datewisestatement.php?fdate=<?php echo $fdate?>&&tdate=<?php echo $tdate?>&&bank=<?php echo $bank?>" style="border:5px solid; background-color:#626262; color:#FFF; padding:8px; text-decoration:none;">EXPORT TO XLS</a>

<a href="search_statement.php" style="border:5px solid; background-color:#F00; color:#FFF; padding:8px; text-decoration:none;">CLOSE</a>
    </p>
</div>
</div>