<?php
if(basename($_SERVER['PHP_SELF']) == 'bankreport.php') 
  $fn=" class='current'";
include'header.php';
include'connection.php';
$custo='';
$conde='';
$p=0;
$dato=$datos=$Date;

// search request by date
if(isset($_POST['search']))
		{
			$dato=$_POST['dato'];
			$datos=$_POST['datos'];
			$pg=$_POST['pg'];
			$custo=$_POST['custo'];
				$p=1;
		}


// search request by date
if(isset($_POST['valid']))
		{
			$dato=$_POST['dato'];
			$datos=$_POST['datos'];
			$custo=$_POST['custo'];
			$val=$_POST['val'];
			$p=$_POST['p'];
			$naso=$_POST['naso'];

			$so=mysqli_query($conn, "UPDATE `deposit` SET `Valid`='$val' WHERE `Number`='$naso'");
		}


		// delete a given deposit record
if(isset($_POST['delo']))
		{
			$dato=$_POST['dato'];
			$datos=$_POST['datos'];
			$custo=$_POST['custo'];
			$val=$_POST['val'];
			$p=$_POST['p'];
			$naso=$_POST['naso'];
			$reco=$_POST['reco'];

	$so=mysqli_query($conn, "UPDATE `deposit` SET `Status`='1', `Valid`='0' WHERE `Number`='$naso'");

			$go=mysqli_query($conn, "UPDATE `payment` SET `Passed`='0' WHERE `Number`='$reco'");
		}
if($dato==$datos)
			$mpri="ON $dato";
		else
			$mpri="FROM $dato TO $datos";
?>

<div class="container-fluid main-content">
        <div class="page-title hidden-print">
          <h2 style='margin-top:-20px; margin-bottom: 5px; color:#ffcc33'>
        Finance Report
          </h2>
                 </div>
     
         <div class="row">
  <div class="col-lg-2 hidden-print">
   
   
   
  <ul class="list-group">
                  
			  <li class="list-group-item">
              <a href="paidreport.php">
                <p>
                <i class="lnr lnr-plus-circle"></i>&nbsp;Payment Report 1
                </p>
              </a></li>
                  
			  <li class="list-group-item">
              <a href="paireport.php">
                <p>
                <i class="lnr lnr-plus-circle"></i>&nbsp;Payment Report 2
                </p>
              </a></li>
                  
			  <li class="list-group-item">
              <a href="expenserepo.php">
                <p>
                <i class="lnr lnr-menu-circle"></i>&nbsp;Expenses Report 1
                </p>
              </a></li>
                  
			  <li class="list-group-item">
              <a href="expereport.php">
                <p>
                <i class="lnr lnr-menu-circle"></i>&nbsp;Expenses Report 2
                </p>
              </a></li>
                  
			  <li class="list-group-item">
              <a href="purcharepo.php">
                <p>
                <i class="lnr lnr-menu-circle"></i>&nbsp;Purchase Report
                </p>
              </a></li>
                  
			  <li class="list-group-item active">
              <a href="bankreport.php">
                <p>
                <i class="lnr lnr-menu-circle"></i>&nbsp;Bank Report
                </p>
              </a></li>
                  
			  <li class="list-group-item">
              <a href="assetreport.php">
                <p>
                <i class="lnr lnr-plus-circle"></i>&nbsp;Asset Report
                </p>
              </a></li>
                  
			  <li class="list-group-item">
              <a href="fsalerepo.php">
                <p>
                <i class="lnr lnr-menu-circle"></i>&nbsp;Finacial Statement
                </p>
              </a></li>
                  
			  <li class="list-group-item">
              <a href="finrepo.php">
                <p>
                <i class="lnr lnr-menu-circle"></i>&nbsp;Finacial Position
                </p>
              </a></li>
                  
			  <li class="list-group-item">
              <a href="holdrepo.php">
                <p>
                <i class="lnr lnr-menu-circle"></i>&nbsp;Withholding Report
                </p>
              </a></li>
                  
			  <li class="list-group-item">
              <a href="cashrepo.php">
                <p>
                <i class="lnr lnr-menu-circle"></i>&nbsp;Cashbox Report
                </p>
              </a></li>
              
              </ul><br>
              <?php
        	echo"<div id='Modaldise-a0' class='modal fade' role='dialog'>
  <div class='modal-dialog' role='document'><div class='modal-content'>
      <div class='modal-header'><form method='post' action=''>
    <h5 class='modal-title' id='exampleModalLabel'> BANK ACCOUNT BALANCE </h5>
		<button type='submit' name='load' class='btn btn-xs btn-default' style='float:right; margin-top:-20px; width:80px;' data-toggle='tooltip' data-placement='top' data-dismiss='modal'>CLOSE</button></div>
      <div class='modal-body' style='height:auto;'>";
      
         $anu=$afna=$accos=array();                $i=1; 
	$doi=mysqli_query($conn, "SELECT *FROM `baccount` ORDER BY `Number` ASC");
			while($roi=mysqli_fetch_assoc($doi)){
				$nu=$roi['Number'];
				$fna=$roi['Bank'];
				$acco=$roi['Account'];
				$cu=$roi['Currency'];
				$anu[$i]=$nu;
				$afna[$i]=$fna;
				$accos[$i]=$acco;
				
	$doin=mysqli_query($conn, "SELECT SUM(IF(`Operation`='DEPOSIT', `Amount`, 0)) AS `Depo`, SUM(IF(`Operation`='WITHDRAWAL', `Amount`, 0)) AS `With`, SUM(IF(`Operation`='DEPOSIT', `Amount`/`Rate`, 0)) AS `Udepo`, SUM(IF(`Operation`='WITHDRAWAL', `Amount`/`Rate`, 0)) AS `Uwith` FROM `deposit` WHERE `Status`='0' AND `Date`>='2022-01-01' AND  `Account`='$nu' ORDER BY `Number` ASC");
		    $roin=mysqli_fetch_assoc($doin);
		    
		    if(strpos($fna, 'FOREX') !== false)
	$balo=number_format($roin['Udepo']-$roin['Uwith'], 2);
	        else
	$balo=number_format($roin['Depo']-$roin['With'], 2);
				    
	echo"<div class='col-lg-4' style='font-size:11px;'> $acco <br> $fna <br> Balance: $balo $cu </div>";
	
	                    if($i%3==0)
	           echo"<hr style='border:1px solid teal;'>";
	                    
				$i++;           
			}
       
            echo"<div class='row'></div></div>
      <div class='modal-header' style='height:20px; margin-top:-10px; padding-top:10px; border:0px solid blue;'>
      
	
    
      </div></form>
    </div></div>
</div>";
                      
			?>
              
              
  </div>
       
        <div class="col-lg-10">
                  <div class="row hidden-print">
         
    <div class="col-lg-3" style="padding-left:40px; padding-right:60px;"> 
          <button class="btn btn-warning btn-block" type="button" data-toggle='modal' data-target='#Modaldise-a0' stle="font-size:13px;">ACCOUNT BALANCE</button></div>
           

        <form action="" method="post" class="form-horizontal ">                  
                       <div class="col-lg-3"> 					
					  <select class="form-control" name="custo">
			 <?php
	echo"<option value='' selected='selected'> SELECT ACCOUNT </option>";
	
	    for($l=1; $l<$i; $l++){
	        $nu=$anu[$l];
	        $fna=$afna[$l];
	        $acco=$accos[$l];
	    
				if($custo==$nu)
					$s="selected";
				else
					$s="";
			echo"<option value='$nu' $s> $fna $acco </option>";
			}
						?>
			   </select>  
					   </div>
		<div class="col-lg-6 hidden-print">
             <div class="col-lg-4"> 
           <div class="input-group date" data-provide="datepicker">
      <input class="form-control form-center" name="dato" type="text" value="<?php echo $dato ?>" onkeypress='return isNumberKey(event)' required>
	  <span class="input-group-addon"><i class="lnr lnr-calendar-full"></i></span>
			</div></div>


		  <div class="col-lg-4"> 
           <div class="input-group date" data-provide="datepicker">	
      <input class="form-control form-center" id="to" name="datos" type="text" value="<?php echo $datos ?>" onkeypress='return isNumberKey(event)' required>
	  <span class="input-group-addon"><i class="lnr lnr-calendar-full"></i></span>
              </div></div>                     
                       
                       <div class="col-lg-3"><?php echo"<input type='hidden' name='pg' value='$pg'>"; ?>
                        <button class="btn  btn-primary btn-block" type="submit" name="search"><i class="lnr lnr-magnifier"></i> Search</button>
                   
					  </div>
                         </div></form>               
            </div>
               <?php
					if($custo){
						$conde="AND `Account`='$custo'";
				$doin=mysqli_query($conn, "SELECT *FROM `baccount` WHERE `Number`='$custo' ORDER BY `Number` ASC");
						$roi=mysqli_fetch_assoc($doin);
							$bank=$roi['Bank'];
							$account=$roi['Account'];
							$rate=$roi['Rate'];
							$wd="30%";
					  }
					  else{
						  $bank=$account='';
						  $rate=1;
						  $wd="45%";
					  }

				if($p)
		$dok=mysqli_query($conn, "SELECT *FROM `deposit` WHERE `Status`='0' AND `Date` BETWEEN '$dato' AND '$datos' $conde ORDER BY `Date` ASC, `Number` ASC LIMIT 100000");
				else
		$dok=mysqli_query($conn, "SELECT *FROM (SELECT *FROM `deposit` WHERE `Status`='0' AND `Date` <= '$Date' $conde ORDER BY `Number` DESC LIMIT 20) SUB ORDER BY `Date` ASC");
				if($fo=mysqli_num_rows($dok)){
				?>
                 
            <div class="divFooter"><center><u><b>BANK STATEMENT REPORT <?php echo $mpri ?></b></u></center></div>
             <div class="row">
            <div class="col-lg-12">
             <span> &nbsp;&nbsp; Total Records Found : <b><?php echo" $fo " ?></b></span>
			 <span class="pull-right hidden-print"><?php echo"$bank   $account"; ?>&nbsp;&nbsp;
			 <a href="#" onclick="return window.print()" class="hidden-print" title='Click to print' data-toggle='tooltip' data-placement='top'>
			 <i class="lnr lnr-printer"></i></a></span>
            <div class="widget-container fluid-height clearfix">
            <div class="widget-content padded clearfix">

								<table class="table table-striped table-hover">     
                                      <thead>
                    <tr role="row">
                     <th class="hidden-xs" style='padding:1px;'>&nbsp; No </th>
					 <th width='8%' style='padding:1px;'>&nbsp;&nbsp;&nbsp;Date </th>
        <th style='padding:1px;'>&nbsp;Location </th>
		<th style='padding:1px;'>&nbsp;&nbsp;&nbsp;&nbsp;Account </th>
		<th style='padding:1px;' width="<?php echo $wd ?>"><div align='center'>&nbsp;&nbsp;&nbsp;Description </th><th style='padding:1px;'><div align='center'>&nbsp;&nbsp;Credit </th>
					   <th><div align='center'>&nbsp;&nbsp;Debit </th><th style='padding:1px;'><div align='center'>&nbsp;&nbsp;Balance </th><?php
					   if($rate!=1)
			echo"<th><div align='center'> Rate </th><th><div align='center'> Foreign </th>";
								?></tr></thead>
                                        <tbody>
					<?php

			// Calculate all deposits **********************************************
	$doin=mysqli_query($conn, "SELECT SUM(Amount) AS `Depo` FROM `deposit` WHERE `Status`='0' AND `Date` < '$dato' AND `Date`>='2020-01-01' AND `Operation`='DEPOSIT' AND `Account`='$custo' ORDER BY `Number` ASC");
		$roin=mysqli_fetch_assoc($doin);
			$depo=$roin['Depo'];

			// Calculate all withdrawals **********************************************
	$dout=mysqli_query($conn, "SELECT SUM(Amount) AS `With` FROM `deposit` WHERE `Status`='0' AND `Date` < '$dato' AND `Date`>='2020-01-01' AND `Operation`!='DEPOSIT' AND `Account`='$custo' ORDER BY `Number` ASC");
		$rout=mysqli_fetch_assoc($dout);
			$with=$rout['With'];

			$bal=$depo-$with;

								$i=1;			$tpin=$tpout=0;
						while($rok=mysqli_fetch_assoc($dok)){
				$code=$rok['Number'];
				$cheno=$rok['Refer'];
			$sour=$rok['Customer'];
			$dte=$rok['Date'];
			$dest=$rok['Source'];
			$bna=$rok['Operation'];				
			$pda=$rok['Descri'];				
			$item=$rok['Item'];
			$valide=$rok['Valid'];
			$reco=$rok['Record'];
			$acco=$rok['Account'];
			$rat=$rok['Rate'];                   $rato=number_format($rat);
			if($dest=='DIRECT')
			    $dest="CASH";

			$doi=mysqli_query($conn, "SELECT *FROM `baccount` WHERE `Number`='$acco' ORDER BY `Number` ASC");
				$roi=mysqli_fetch_assoc($doi);
					$fna=$roi['Bank'];
					$acco=$roi['Account'];
												$pin=$pout=0;
			$amo=$rok['Amount'];						
			$amoo=number_format($amo, 2);
			$stl="padding:1px; font-size:12px;";

					if($bna=='DEPOSIT'){
						$pin=$amo;
						$bal+=$amo;
					}
					else{
						$pout=$amo;	
						$bal-=$amo;
					}
							$balo=number_format($bal, 2);						$pino=number_format($pin, 2);					$pouto=number_format($pout, 2);
			$for=$bal/$rat;                    $foro=number_format($for, 2);

				print("<tr><td class='hidden-xs' style='$stl $clr'><div align='right'>$i&nbsp;&nbsp;</td>
			<td style='$stl $clr'><div align='center'>&nbsp;$dte&nbsp;</td>
			<td style='$stl $clr'>&nbsp; $dest </td>
				<td style='$stl $clr'><div align='left'> $acco &nbsp;</td>
						<td style='$stl $clr'><div align='left'> $pda </td>
						<td style='$stl $clr'><div align='right'> $pino &nbsp;</td><td style='$stl $clr'><div align='right'> $pouto &nbsp;</td><td style='$stl $clr'><div align='right'> $balo &nbsp;</td>");
					if($rate!=1)
					    echo"<td style='$stl $clr'><div align='right'> $rato&nbsp;&nbsp;</td><td style='$stl $clr'><div align='right'> $foro </td>";
						
						print("</tr>");
									$i++;					$tpin+=$pin;				$tpout+=$pout;
						}
						$tp=number_format($tpin, 2);			
						$tr=number_format($tpout, 2);			
						$tb=number_format($tb, 2);	
						?>
						
                    </tbody>
					 <thead>
					<tr><th class='hidden-xs'> </th><th colspan='4'><div align='center'> Total Amount </th>
					<th><div align='right'><?php echo $tp ?></th><th><div align='right'><?php echo $tr ?></th><th><div align='right'><?php echo $balo ?></th>
					
					<?php
					if($rate!=1)
	echo"<th><div align='center'> -- </th><th style='padding-right:1px;'><div align='right'> $foro </th>";
			
			?></tr>
                  </table> 
								</div></div>
								<span class="pull-right hidden-print">
			 <a href="#" onclick="return window.print()" class="hidden-print" title='Click to print' data-toggle='tooltip' data-placement='top'>
			 <i class="lnr lnr-printer"></i></a></span><br><br><br>

			<?php
				}
				else{
						echo"<div class='row'>
            <div class='col-lg-12' style='margin-top:-20px;'>
             <span> &nbsp;&nbsp; Total Records Found : <b> 0 </b></span>
            <div class='widget-container fluid-height clearfix'>
            <div class='widget-content padded clearfix'>
			<br><br><br><br><br><br>
	<div style='text-align:center; font-size:24px; color:#ff9999'>Report not available on selected date </div><br><br><br><br><br><br>";
					}
			
					?>
                                      
                
              </div>
            </div></div>
                  </div>
   <?php
   include'footer.php';
   ?>