<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<?php

session_start();
if($_SESSION['dbName']==""){
	header("location:../index.php");
}


 
if($exp ==1){
	$filename = "exp_BankCollection_" . date('Ymd') . ".xls";
	header("Content-Disposition: attachment; filename=\"$filename\"");
	header("Content-Type: application/vnd.ms-excel");
} 
print <<< HERE
<HTML>
<HEAD>
<TITLE> Bank Collection Report </TITLE>

<style type="text/css">
table.params td { font-size: 12px; 
                  font-family:Times-New-Roman;
                 }
				 

table.master th { font-size: 14px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
                
                }				 
table.master td { font-size: 13px; 
                  font-family:Times-New-Roman;
				  padding-left:3px;
				  padding-right:3px;
                  
                }
table.master tr.group { font-size: 14px; 
                        font-weight: bold;
                        font-family:Times-New-Roman;
                      }				 				

table.master tr.totals { font-size: 12px; 
                         font-weight: bold;
                         font-family:Times-New-Roman;
                       }
		 

</style>
</HEAD>


HERE;


include("config.inc.php");
include("functions_Sales.php");

$connection = mysql_connect($config['dbServer'],$config['dbUser'],$config['dbPass']) or die("Could not connect to DB");
mysql_select_db($_SESSION['dbName'],$connection) or die("Could not find DB");


$querylog="SELECT print as printaccess, ifNull(login_belongstogrp,0) As FI_BelongsToGrp from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' 	and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."')	";
$resultlog=mysql_query($querylog,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}


if ($printaccess==0 or $printaccess=='')
echo'<BODY oncontextmenu="return false;">';
else
echo'<BODY>';
 


$querysetup="select DecimalBase, DecimalBase1, DecimalBase2, report_color1, report_color2, Base1, Base2, Company_Name As CompanyName from setup";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultsetup)){
  extract($rows);
} 
 

$now=current_server_time($connection);
$dt=split(" ",$now[0]);
$printdate=split("-",$dt[0]);

print <<< HERE
<table width="100%" border="0" align="center">
<tr>
  <td align='left' colspan='1' style='font-size: 15;font-weight:bold;color:silver;'><img src='LogoFile.php'  /></td>
  <td width='10%'>&nbsp;</td>
  <td width='10%' align='center'><font size=4><b>Bank Collection</b></font></td>
  <td width='15%'>&nbsp;</td>
  <td width='10%'><font size=1><b> Printed Date: </b> <br><b>Time: </b>
  <td width='10%'><font size=1>
HERE;

echo $printdate[2]."/".$printdate[1]."/".$printdate[0];
//echo DATE("d"."/"."m"."/"."Y");
echo "<br>".$dt[1]." ".$dt[2];


if ($curr == 'currency1'){
   $curr='null';  
   $displaycur = '';
}   
else {
   $displaycur = $curr;
   $curr="'".$curr."'";
}   
 
print <<<HERE
</font></td>
</tr>
 <tr><td>&nbsp;</td></tr>
</table>


 
HERE;

echo "<table   width='100%' align='center' class='master'  style='table-layout:fixed; border: 1px solid black;
border-collapse: collapse;' cellpadding='1' cellspacing='1' >";
 /* echo" <col width=5%><col width=15%><col width=6%><col width=6%><col width=9%><col width=9%><col width=9%><col width=9%><col width=9%><col width=9%> <col width=9%><col width=9%><col width=9%><col width=9%><col width=9%>"; */
 echo" <col width='50'>";

 for ( $y = $fromyear; $y <= $toyear; $y += 1) {

    if ($y == $fromyear && $y == $toyear) { $vMFrom = $frommonth; $vMTo = $tomonth; }
    else if ($y == $fromyear) { $vMFrom = $frommonth; $vMTo = 12; }
    else if ($y == $toyear) { $vMFrom = 1; $vMTo = $tomonth; }
    else { $vMFrom = 1; $vMTo = 12; }
    
    for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
        echo" <col width='100'>";
    }
}
		 
		// $querySearch  = " select ifnull(sum(if(journaldt_dbcd='D',journaldt_amount1,-1*journaldt_amount1)),0), month(journal_date) monthnum, day(journal_date) daynum".
        // " from journaldt ".
        // " where ledger_number in (select ledger_number from banks)".
        // " group by daynum, monthnum asc ";
        $d=0;
        $dd=1;
        $querySearch=" select 1";
        while($d<31){
            for ( $y = $fromyear; $y <= $toyear; $y += 1) {

                if ($y == $fromyear && $y == $toyear) { $vMFrom = $frommonth; $vMTo = $tomonth; }
                else if ($y == $fromyear) { $vMFrom = $frommonth; $vMTo = 12; }
                else if ($y == $toyear) { $vMFrom = 1; $vMTo = $tomonth; }
                else { $vMFrom = 1; $vMTo = 12; }
                
                for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
                    $querySearch = $querySearch.", sum(Amount".$dd.getMonth($m).$y.") AS Amount".$dd.getMonth($m).$y;
                                                    
                }
        
            }
            $d++;
            $dd++;
        }
        $d=0;
        $dd=1;
        $querySearch.=" from (select 1";
        while($d<31){
            for ( $y = $fromyear; $y <= $toyear; $y += 1) {

                if ($y == $fromyear && $y == $toyear) { $vMFrom = $frommonth; $vMTo = $tomonth; }
                else if ($y == $fromyear) { $vMFrom = $frommonth; $vMTo = 12; }
                else if ($y == $toyear) { $vMFrom = 1; $vMTo = $tomonth; }
                else { $vMFrom = 1; $vMTo = 12; }
                
                for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
                    $fromdate=$y."-".$m."-01";
                    $querySearch = $querySearch.", if(Date_format(journal_date,'%Y-%m-%d')='".addDays($fromdate, $d, 'Y-m-d')."' , Amount, 0) AS Amount".$dd.getMonth($m).$y;
                                                    
                }
        
            }
            $d++;
            $dd++;
        }
        $querySearch.=" from 
                        (select ifnull(if(journaldt_dbcd='D',if ($curr='".$Base1."' ,journaldt_amount1,journaldt_amount2),-1*if ($curr='".$Base1."' ,journaldt_amount1,journaldt_amount2)),0) Amount,
                        journal_date
                        from journaldt ".
                        " where ledger_number in (select ledger_number from banks) and journal_date between '".$fromyear."-".$frommonth."-01 00:00:00' and '".$toyear."-".$tomonth."-31 23:59:59' )q)qq";
	
 // echo $querySearch;
$result = mysql_query($querySearch,$connection) or die(mysql_error());

 
$colorSwitcherIndex = 1;
$FR = 0;
$currentSupp= '';
$ct = 0;


echo "<tr >";
echo" <th  ></th> ";
	for ( $y = $fromyear; $y <= $toyear; $y += 1) {

    if ($y == $fromyear && $y == $toyear) { $vMFrom = $frommonth; $vMTo = $tomonth; }
    else if ($y == $fromyear) { $vMFrom = $frommonth; $vMTo = 12; }
    else if ($y == $toyear) { $vMFrom = 1; $vMTo = $tomonth; }
    else { $vMFrom = 1; $vMTo = 12; }
    
    for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
        echo" <th  style='border: 1px solid black; border-collapse: collapse;'>".getMonth($m)." ".$y."</th> ";
    }
}; 
echo" </tr>";

$rows=mysql_fetch_array($result)  ;
		$ct+=1;
		$stylefont='style15';
		 
		if($ct%2==0)
			$Color="style='background-color:".$grid_color1."'";
		else			
			$Color="style='background-color:".$grid_color2."'";	
			 
		if($mainmode=='edit' && $Currency_Id==$rows[0]){
			
			$Color="style='background-color:#1e87a5'";
			$stylefont='style28';
		}
	 




 

  
	$mycolor = "color:#000000;font-weight:700;"  ;
    $d=0;
    $dd=1;
    $i=1;
    $querySearch.=" from (select 1";
    while($d<31){
        if($d % 2 == 0)
        $bgColor = "#ABD4F1";
    else
        $bgColor ='#FFFFFF';


        echo"<tr>";
        echo"<td style=' width: 180px; background-color:$bgColor;border: 1px solid black; border-collapse: collapse;' ><font face='Arial' size='2' ><span style='$mycolor;' >&nbsp;".$dd." </span></font></td>";

        for ( $y = $fromyear; $y <= $toyear; $y += 1) {

            if ($y == $fromyear && $y == $toyear) { $vMFrom = $frommonth; $vMTo = $tomonth; }
            else if ($y == $fromyear) { $vMFrom = $frommonth; $vMTo = 12; }
            else if ($y == $toyear) { $vMFrom = 1; $vMTo = $tomonth; }
            else { $vMFrom = 1; $vMTo = 12; }
            
            for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
                echo"<td style=' width: 180px; background-color:$bgColor;border: 1px solid black; border-collapse: collapse;' ><font face='Arial' size='2' ><span style='$mycolor;' >&nbsp;".  number_format($rows[$i],2)  ." </span></font></td>";
                    $i++;                               
            }
    
        }
        $d++;
        $dd++;
echo"</tr>";
    }	 






 
echo "</table>";
 
 

function format_currency($amount, $DecimalBase) { 

    if($amount < 0) 
        return "(".number_format(abs($amount),$DecimalBase).")"; 
 
    else return number_format($amount, $DecimalBase); 
} 

function current_server_time($connection){

  $string="Select date_format(NOW(),'%Y-%m-%d %h:%i %p') ";
  $result=mysql_query($string,$connection) or die(mysql_error());
  $now=mysql_fetch_row($result);

  return $now;
}
function addDays($startdate, $numdays, $format='dMY') {
	return date($format, strtotime($startdate) + $numdays*60*60*24 + 10000);
}


function explodeword($date){

	$trim = explode('/',$date);
	$trim = "$trim[2]"."-"."$trim[1]"."-"."$trim[0]";
	return $trim;
}
 
function getMonth($month){
    return date('M', mktime(0,0,0,$month,1));
}
if ($printaccess==1)
print <<< HERE

<script language=javascript>
if (typeof(window.print) != 'undefined')
{
    window.print();
}
</script>
</BODY>
</HTML>
HERE;
?>