<?php

include("config.inc.php");
require_once('Spreadsheet/Excel/Writer.php');


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

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

mysql_select_db($HTTP_SESSION_VARS['dbName'],$connection) or die("Could not find DB");

$fromDate  = $_GET['fromDate'];
$toDate    = $_GET['toDate'];
$date_type = $_GET['date_type'];
$order_By  = $_GET['order_By'];
$banks     = $_GET['banks'];
$activity =$_GET['act'];
$collected =$_GET['coll'];
$uncollected =$_GET['uncoll'];
$fromLedger=$_GET['fromLedger'];
$toLedger=$_GET['toLedger'];
$OrderSalesMan=$_GET['OrderSalesMan'];
$Salesmen=$_GET['salesmen'];
//header('Content-Type: text/html; charset=utf-8');
if($date_type == "date") $date_label = "Date";
if($date_type == "chk_date") $date_label = "Check Date";

if($order_By == "date") $order_by_labels = "Order By $date_label";
if($order_By == "bank_and_date") $order_by_labels = "Order By Bank & $date_label";

$query_setup  = "select COMPANY_LOGO as CompanyLogo, COMPANY_NAME as CompanyName, Company_memo as CompanyAddress from setup";
$result_setup = mysql_query($query_setup, $connection); 
$row_setup    = mysql_fetch_array($result_setup);
extract($row_setup);


//Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
 
//Sending HTTP headers
$workbook->send('Exp_ListOfChecks.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('Export');
$worksheet->setLandscape();
 
//set all columns same width
$columnWidth = 10;


//Title Format 
$sheetTitleFormat =& $workbook->addFormat(array('bold'=>1,
  'size'=>10));

//Column Title Format  
$columnTitleFormat =& $workbook->addFormat(array('bold'=>1,
  'top'=>1,
  'bottom'=>1 ,
  'size'=>15,
  'color'=>0x0A,
  ));
  
 
  
// Regular Format
$regularFormat =& $workbook->addFormat(array('size'=>8,
  'align'=>'left'  ));
// Regular Format
$squareborderFormat =& $workbook->addFormat(array('size'=>9,
  'top'=>1,  
  'bottom'=>1,
  'right'=>1,
  'left'=>1,
  ));  
  
$lrBorderFormat =& $workbook->addFormat(array('size'=>9,
  'right'=>1,
  'left'=>1,
  ));  
  
$TitleFormat =& $workbook->addFormat(array('size'=>11,
  'align'=>'left',
  'bold'=>1
  ));

$boldFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ,
  'bold'=>1
  ));  

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

	
     
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,5,"List Of Checks", $TitleFormat);
$worksheet->write(1,11,"Printed Date:", $boldFormat);
$worksheet->write(1,13,$printdate[2]."/".$printdate[1]."/".$printdate[0], $boldFormat);
$worksheet->write(2,11,"Time:", $boldFormat);		
$worksheet->write(2,13,$dt[1]." ".$dt[2], $boldFormat);

$fromDate = explode("/", $fromDate);
$fromDate = $fromDate[2]."-".$fromDate[1]."-".$fromDate[0];

$toDate = explode("/", $toDate);
$toDate = $toDate[2]."-".$toDate[1]."-".$toDate[0];

$worksheet->setColumn(0, 0, 11);
$worksheet->write(4,0,"Date", $boldFormat); 		
$worksheet->setColumn(1, 1, 15);
$worksheet->write(4,1,"Check No.", $boldFormat);
$worksheet->setColumn(2, 2, 20);
$worksheet->write(4,2,"Bank Name", $boldFormat); 	
$worksheet->setColumn(3, 3, 40);	
$worksheet->write(4,3,"Account", $boldFormat); 
$worksheet->write(4,4,"Currency", $boldFormat); 
$worksheet->setColumn(4, 4, 12);		
$worksheet->write(4,5,"Amount", $boldFormat);
$worksheet->setColumn(6, 6, 20);
$worksheet->write(4,6,"Collect Date", $boldFormat); 		
$worksheet->write(4,7,"Trans Reference", $boldFormat); 

$rownum=5;

if($OrderSalesMan==1){
	
	$query_salesmen="SELECT distinct Salesmen_Code from clients";
	if($Salesmen!="")
		$query_salesmen .= " where Salesmen_Code = '$Salesmen'";
	//echo $query_salesmen;
	$result_salesmen = mysql_query($query_salesmen, $connection);
	while($row_salesmen = mysql_fetch_array($result_salesmen)){
		extract($row_salesmen);
		$query_chks="SELECT DATE_FORMAT(checks.Chk_ValueDate, '%d/%m/%Y') AS date,
				checks.Chk_number AS ChkNo,
				checks.Chk_bank AS BankName,
				checks.Ledger_number AS LedgerNumber,
				ledger.ledger_name AS ledgerName,
				checks.Currency_code AS ChkCurrency,
				checks.Chk_amount AS ChkAmount,
				checks.Journaldt_id1 AS idd,
				DATE_FORMAT(transdt.trans_date, '%d/%m/%Y') AS collectionDate,
				concat(setup.Clientaccount, SUBSTR(checks.Ledger_number
												FROM 5)) AS ACCOUNT,
				checks.journaldt_id1, trans.trans_reference AS TransReference
				FROM checks
				left JOIN journaldt ON checks.journaldt_id1=journaldt.journaldt_id
				LEFT JOIN chkjour ON checks.journaldt_id2=chkjour.journaldt_id
				LEFT JOIN ledger ON checks.ledger_number=ledger.ledger_number
				LEFT JOIN setup ON Checkaccount=SUBSTRING(checks.Ledger_number, 1, 4)
				LEFT JOIN clients ON clients.Ledger_number=concat(setup.ClientFirstDigit, SUBSTR(checks.Ledger_number
																					FROM 5))
				LEFT JOIN trans ON trans.journal_id = journaldt.journal_id
				LEFT JOIN transdt ON transdt.trans_id = trans.trans_id and transdt.ledger_number = journaldt.ledger_number
				where checks.Ledger_Activity='$activity' and clients.Salesmen_Code = '$Salesmen_Code'";
		if($collected==1 && $uncollected==1) $query_chks=$query_chks;
		else if($collected==1) $query_chks=$query_chks." and Journaldt_id2 is NOT NULL ";
		else if($uncollected==1) $query_chks=$query_chks." and Journaldt_id2 is NULL ";

		if($fromLedger!="" && $toLedger!="") $query_chks=$query_chks." and (checks.ledger_number between '$fromLedger' and '$toLedger') ";
		if($date_type == "date"){
			$query_chks = $query_chks. " and checks.Chk_ValueDate between '$fromDate' and '$toDate 23:59:59'";
		}
		else{
			$query_chks = $query_chks. " and checks.Chk_ValueDate between '$fromDate' and '$toDate 23:59:59'";
		}
		if($banks != ""){
			$query_chks = $query_chks. " and CChk_bank= '$banks'";
		}
		if($order_By == "date"){
			if($date_type == "date"){
				$query_chks = $query_chks." order by checks.Chk_ValueDate asc";
			}
			else{
				$query_chks = $query_chks." order by Chk_ValueDate and chk_bank asc";
			}
		}
		//echo $query_chks;exit;
		$result_chks = mysql_query($query_chks, $connection);
		if(mysql_num_rows($result_chks)>0){
            $rownum = $rownum + 1;
			if($Salesmen_Code != "")
				$query_salesmen_name="SELECT Ledger_Name from salesmen where Ledger_Number = '$Salesmen_Code'";
				$result_salesmen_name = mysql_query($query_salesmen_name, $connection);
				while($row_salesmen_name = mysql_fetch_array($result_salesmen_name))
                    extract($row_salesmen_name);
                $worksheet->write($rownum,0,"$Ledger_Name",$boldFormat);
                
                while($row_chks = mysql_fetch_array($result_chks)){
                    $rownum = $rownum + 1;
                    $collectDate="";

                    extract($row_chks);
                    
                    if($date_type == "date"){
                        $date_display = $NormalDate;
                    }
                    else{
                        $date_display = $ValueDate;
                    }
                    $worksheet->write($rownum,0,"$date",$regularFormat);
                    $worksheet->write($rownum,1,"$ChkNo",$regularFormat);
                    $worksheet->write($rownum,2,"$BankName",$regularFormat);
                    $worksheet->write($rownum,3,"$LedgerNumber - $ledgerName",$regularFormat);
                    $worksheet->write($rownum,4,"$ChkCurrency",$regularFormat);
                    $worksheet->write($rownum,5,number_format($ChkAmount,2),$regularFormat);
                    $worksheet->write($rownum,6,"$collectionDate",$regularFormat);
                    $worksheet->write($rownum,7,"$TransReference",$regularFormat);
                    
                }
                $rownum = $rownum + 1;
                $query_totals  = "select concat('Sum ', ChkCurrency) as SumOf, format(sum(ChkAmount), 2) as SumAmount 
							from ($query_chks) as Q 
							group by ChkCurrency";
                $result_totals = mysql_query($query_totals, $connection);
                $num = mysql_num_rows($result_totals);
                $count = 0;
                while($row_total = mysql_fetch_array($result_totals)){
                    extract($row_total);
                    $worksheet->write($rownum,4,"$SumOf",$boldFormat);
                    $worksheet->write($rownum,5,"$SumAmount",$boldFormat);
                }	
        }
    }
}
else{

    $query_chks="SELECT DATE_FORMAT(checks.Chk_ValueDate, '%d/%m/%Y') AS date,
                    checks.Chk_number AS ChkNo,
                    checks.Chk_bank AS BankName,
                    checks.Ledger_number AS LedgerNumber,
                    ledger.ledger_name AS ledgerName,
                    checks.Currency_code AS ChkCurrency,
                    checks.Chk_amount AS ChkAmount,
                    checks.Journaldt_id1 AS idd,
                    DATE_FORMAT(transdt.trans_date, '%d/%m/%Y') AS collectionDate,
                    concat(setup.Clientaccount, SUBSTR(checks.Ledger_number
                                                    FROM 5)) AS ACCOUNT,
                    checks.journaldt_id1, trans.trans_reference AS TransReference
                    FROM checks
                    left JOIN journaldt ON checks.journaldt_id1=journaldt.journaldt_id
                    LEFT JOIN chkjour ON checks.journaldt_id2=chkjour.journaldt_id
                    LEFT JOIN ledger ON checks.ledger_number=ledger.ledger_number
                    LEFT JOIN setup ON Checkaccount=SUBSTRING(checks.Ledger_number, 1, 4)
                    LEFT JOIN clients ON clients.Ledger_number=concat(setup.Clientaccount, SUBSTR(checks.Ledger_number
                                                                                        FROM 5))
                    LEFT JOIN trans ON trans.journal_id = journaldt.journal_id
                    LEFT JOIN transdt ON transdt.trans_id = trans.trans_id and transdt.ledger_number = journaldt.ledger_number
                    where checks.Ledger_Activity='$activity'";

    if($OrderSalesMan==1) $query_chks = $query_chks." and Salesmen_Code == '$Salesmen_Code'";
    if($collected==1 && $uncollected==1) $query_chks=$query_chks;
    else if($collected==1) $query_chks=$query_chks." and Journaldt_id2 is NOT NULL ";
    else if($uncollected==1) $query_chks=$query_chks." and Journaldt_id2 is NULL ";

    if($fromLedger!="" && $toLedger!="") $query_chks=$query_chks." and (checks.ledger_number between '$fromLedger' and '$toLedger') ";
    if($date_type == "date"){
    $query_chks = $query_chks. " and checks.Chk_ValueDate between '$fromDate' and '$toDate 23:59:59'";
    }
    else{
        $query_chks = $query_chks. " and checks.Chk_ValueDate between '$fromDate' and '$toDate 23:59:59'";
    }
    if($banks != ""){
        $query_chks = $query_chks. " and CChk_bank= '$banks'";
    }
    if($order_By == "date"){
        if($date_type == "date"){
        $query_chks = $query_chks." order by checks.Chk_ValueDate asc";
        }
        else{
            $query_chks = $query_chks." order by Chk_ValueDate and chk_bank asc";
        }
    }
    $result_chks = mysql_query($query_chks, $connection);
    while($row_chks = mysql_fetch_array($result_chks)){
        $rownum = $rownum + 1;
        $collectDate="";
                    
        
        extract($row_chks);
        
        if($date_type == "date"){
            $date_display = $NormalDate;
        }
        else{
            $date_display = $ValueDate;
        }
        $worksheet->write($rownum,0,"$date",$regularFormat);
        $worksheet->write($rownum,1,"$ChkNo",$regularFormat);
        $worksheet->write($rownum,2,"$BankName",$regularFormat);
        $worksheet->write($rownum,3,"$LedgerNumber - $ledgerName",$regularFormat);
        $worksheet->write($rownum,4,"$ChkCurrency",$regularFormat);
        $worksheet->write($rownum,5,number_format($ChkAmount,2),$regularFormat);
        $worksheet->write($rownum,6,"$collectionDate",$regularFormat);
        $worksheet->write($rownum,7,"$TransReference",$regularFormat);
    }
    $rownum = $rownum + 1;

    $query_totals  = "select concat('Sum ', ChkCurrency) as SumOf, format(sum(ChkAmount), 2) as SumAmount 
                from ($query_chks) as Q 
                group by ChkCurrency";
    $result_totals = mysql_query($query_totals, $connection);
    $num = mysql_num_rows($result_totals);
    
    while($row_total = mysql_fetch_array($result_totals)){
        extract($row_total);
        $worksheet->write($rownum,4,"$SumOf",$boldFormat);
        $worksheet->write($rownum,5,"$SumAmount",$boldFormat);
    }	
}
$workbook->close();

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 explodeword($date)
{

    $trim = explode('/',$date);
    $trim = "$trim[2]"."-"."$trim[1]"."-"."$trim[0]";
    return $trim;
}



function GetSQLValueString($theValue, $theType) 
{
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

    $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

    switch ($theType) {
        case "text":
        $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
        break;    
        case "long":
        case "int":
        $theValue = ($theValue != "") ? intval($theValue) : "Null";
        break;
        case "double":
        $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "Null";
        break;
        case "date":
        $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
        break;
    }
    return $theValue;
}
?>