<?php
header("Content-type: text/html; charset=windows-1256");

include("config.inc.php");

require_once('Spreadsheet/Excel/Writer.php');

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




//Set Time Execution For the Report
//ini_set('max_execution_time', $config['ReportTimeExecution']);
set_time_limit(0);

$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");


$querysetup="select DecimalBase, DecimalBase1, DecimalBase2, 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);
}

$querylog=" select ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter, ".
		  " 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) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}

//Read From Module Table
$querymodule=" Select ifNull(Module_BelongsToGroup,0) As Module_BelongsToGroup, ifnull(Module_ConfirmJV,0) as Module_ConfirmJV  From Module ";
$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
while($rowmodule=mysql_fetch_array($resultmodule)){
	extract($rowmodule);
}


$queryproject=" select distinct project.project_code, project_name ".
              " from project left join ".$_SESSION['dbLabel'].".loginstk on project.project_code like concat(ifnull(loginstk.project_code,project.project_code),'%') ".
			  " where user_account='".$_SESSION['useraccount']."' ".
			  " and (loginstk.project_code is not null and loginstk.project_code != '') ".
			  " order by project.project_code";
$projectresult = mysql_query($queryproject,$connection) or die(mysql_error());
$projectexist   = mysql_num_rows($projectresult);

$querycostcent=" select distinct costcent.costcent_code, costcent_name ".
               " from costcent left join ".$_SESSION['dbLabel'].".loginstk on costcent.costcent_code like concat(ifnull(loginstk.costcent_code,costcent.costcent_code),'%')".
			   " where user_account='".$_SESSION['useraccount']."' ".
			   " and (loginstk.costcent_code is not null and loginstk.costcent_code != '') ".
			   " order by costcent.costcent_code";
$costcentresult = mysql_query($querycostcent,$connection) or die(mysql_error());
$costcentexist  = mysql_num_rows($costcentresult);	

//maroun
$querymasterproject=" select distinct Masterproject from ".$_SESSION['dbLabel'].".loginstk ".
					" where loginstk.user_account='".$_SESSION['useraccount']."' ".
					" and (Masterproject is not null and Masterproject != '') ";
														 
$projectmasterresult = mysql_query($querymasterproject,$connection) or die(mysql_error());
$projectmasterexist   = mysql_num_rows($projectmasterresult);

//maroun
$querymastercostcenter=" select distinct Mastercostcenter from ".$_SESSION['dbLabel'].".loginstk ".
					   " where loginstk.user_account='".$_SESSION['useraccount']."' ".
					   " and (Mastercostcenter is not null and Mastercostcenter != '') ";


$costcentermasterresult = mysql_query($querymastercostcenter,$connection) or die(mysql_error());
$costcentermasterexist   = mysql_num_rows($costcentermasterresult);

//maroun
$querysalesmen=" select distinct clientsalesmen 
			from ".$_SESSION['dbLabel'].".loginstk ".
			  " where user_account='".$_SESSION['useraccount']."' 
			  and clientsalesmen<>'' 
			  or clientsalesmen <> null
			  "  ;
$salesmenresult = mysql_query($querysalesmen,$connection) or die(mysql_error());
$salesmenexist   = mysql_num_rows($salesmenresult);


$queryledger= " select distinct ledger_number, ledger_include ".
              " from ".$_SESSION['dbLabel'].".ledgerrepstk  ".
			  " where user_account='".$_SESSION['useraccount']."' ".
			  " and (ledger_number is not null and ledger_number != '') ".
			  " order by ledger_number";
$ledgerresult = mysql_query($queryledger,$connection) or die(mysql_error());
$ledgerexist  = mysql_num_rows($ledgerresult);	
$rows=mysql_fetch_array($ledgerresult) ;
$ledgerinclude = $rows[1];
if (empty($ledgerinclude)) $ledgerinclude = 0;

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



$dispfromproject  = $fromproject;
$dispfromcst = $fromcst;
$dispfromledger = $fromledger;
$disptoledger = $toledger;
$dispfromdate = $fromdate;
$disptodate = $todate;


if (empty($fromledger))   
   $fromledger = 'null';
else
   $fromledger = "'".$fromledger."'";   
   
if (empty($toledger))   
   $toledger = 'null';
else
   $toledger = "'".$toledger."'";      

if (empty($fromproject))   
   $fromproject = 'null';
else
   $fromproject = "'".$fromproject."'";    
   
  
if (empty($fromcst))   
   $fromcst = 'null';
else
   $fromcst = "'".$fromcst."'";    
   
if (empty($excludecst))   
   $excludecst = 'null';
else
   $excludecst = "'".$excludecst."'";   
     

if (!empty($fromdate))
   $fromdate = explodeword($fromdate);
if (!empty($todate))
   $todate = explodeword($todate);  

if (empty($fromdate))   
   $fromdate = 'null';
else
   $fromdate = "'".$fromdate."'"; 
   
if (empty($todate))   
   $todate = 'null';
else
   $todate = "'".$todate."'";
   
$jvtypelen = 0;
if (!empty($jvtype))
   $jvtypelen = strlen($jvtype);
   
if (empty($jvtype))
   $jvtype = 'null';      



if (empty($fromacctype))   
   $fromacctype = 'null';
else
   $fromacctype = "'".$fromacctype."'"; 
   
if (empty($frombustype))   
   $frombustype = 'null';
else
   $frombustype = "'".$frombustype."'";     

  if (empty($cltcst))   
   $cltcst = 'null';
else
   $cltcst = "'".$cltcst."'";       
     
if (empty($cltprj))   
   $cltprj = 'null';
else
   $cltprj = "'".$cltprj."'";      
   
   
if ($fromsalesman=='') $fromsalesman = 'null';
else $fromsalesman = "'".$fromsalesman."'"; 
if ($tosalesman=='') $tosalesman = 'null';
else $tosalesman = "'".$tosalesman."'";    

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


   
//Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
$workbook->setVersion(8);
 
//Sending HTTP headers
$workbook->send('Exp_UnBalancedJournal.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('UnbalancedJournal');
$worksheet->setInputEncoding('windows-1256'); 
$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'=>9,
  'align'=>'left', 
  'textWrap'=>1 
  ));

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

$TitleFormat1 =& $workbook->addFormat(array('size'=>12,
  'align'=>'left', 
  'bold'=>'1', 
  ));
  
$regularleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ));
$regularrightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ));  
  
$boldleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));  
$boldrightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ,
  'bold'=>1
  )); 
$boldcenterFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'center'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));   
 
//$worksheet->setNumFormat('(0.00)');
 
//Elie and Tarek Format


$regularFormatNum1 =& $workbook->addFormat(array('size'=>9,
  'align'=>'left', 
  'textWrap'=>1 
  )); 
$regularleftFormatNum1 =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ));
$regularrightFormatNum1 =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ));  
  
$regularFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$regularleftFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$regularrightFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');




//Elie And  Tarek Formt 
 
 
 
$worksheet->setColumn (0, 0, 11);
$worksheet->setColumn (1, 1, 33);
$worksheet->setColumn (4, 15, 14.5);

// Merge cells from row 0, col 0 to row 2, col 2
$worksheet->setMerge(4, 2, 4, 3);
$worksheet->setMerge(5, 2, 5, 3);

$worksheet->write(1, 0, $CompanyName, $TitleFormat);
$worksheet->write(1, 5, "Unbalance Journal", $TitleFormat1);
$worksheet->write(1, 7, "Printed Date:", $TitleFormat);
$worksheet->write(1, 8, $printdate[2]."/".$printdate[1]."/".$printdate[0], $TitleFormat);
$worksheet->write(2, 7	, "Time:", $TitleFormat);
$worksheet->write(2, 8, $dt[1]." ".$dt[2], $TitleFormat);
                	
				
$worksheet->write(4, 0, "Date", $TitleFormat);
$worksheet->write(4, 1, "From", $TitleFormat);
$worksheet->write(4, 2, $dispfromdate, $regularFormat);

				
$worksheet->write(4, 4, "To", $TitleFormat);
$worksheet->write(4, 5, $disptodate, $regularFormat);
  
$RowNum    = 10;
$ColumnNum = 0;

$worksheet->write($RowNum,$ColumnNum,"Reference", $boldleftFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Date", $boldleftFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Journal Type", $boldleftFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Currency", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Balance", $boldcenterFormat);
$ColumnNum += 1;


$RowNum   += 1;
$ColumnNum = 0;

$queryjournal = "select Journal_refgovernment,Journal_date, Journal_type, debitamnt1-creditamnt1, debitamnt2- creditamnt2 from (
				select Journal_refgovernment,date_format(journal.Journal_date,'%d-%m-%Y') as Journal_date,Journal_type, sum(if(Journaldt_dbcd = 'D',Journaldt_amount1,0)) as debitamnt1,
				sum(if(Journaldt_dbcd = 'D',Journaldt_amount2,0)) as debitamnt2, sum(if(Journaldt_dbcd = 'C',Journaldt_amount1,0)) as creditamnt1,
				sum(if(Journaldt_dbcd = 'C',Journaldt_amount2,0)) as creditamnt2
				from journaldt
				inner join journal on journal.journal_id = journaldt.journal_id ";
if ($projectexist > 0 && $FI_RepRestrictProject == 1) {
		$queryjournal = $queryjournal." Inner join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
								      "			     where loginstk.user_account='".$_SESSION['useraccount']."' ".
								      "              and (loginstk.project_code is not null and loginstk.project_code != '') ". 
								      "		   ) Q1 on CstJour.project_code like concat(Q1.project_code,'%') ";
	}
				
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1) {
		$queryjournal = $queryjournal." Inner join ( select distinct costcent_code from ".$_SESSION['dbLabel'].".loginstk ".
									  "			     where loginstk.user_account='".$_SESSION['useraccount']."' ".
									  "              and (costcent_code is not null and costcent_code != '') ".
								  	  "			 ) Q2 on CstJour.costcent_code like concat(Q2.Costcent_Code,'%')";
	}				
$queryjournal = $queryjournal." where date_format(journal.Journal_date,'%Y-%m-%d') between ifnull($fromdate,date_format(journal.Journal_date,'%Y-%m-%d')) and ifnull($todate,date_format(journal.Journal_date,'%Y-%m-%d'))
				group by journaldt.journal_id ) as Q where ABS(round(debitamnt1-creditamnt1,$DecimalBase )) > $unbrange
				order by Journal_refgovernment;"; 
	

$RowNum    = 13;
$ColumnNum = 0;

$resultjournal=mysql_query($queryjournal,$connection) or die(mysql_error());
while($rowsjournal=mysql_fetch_row($resultjournal)){

   if($curr == 1){
		$amount = $rowsjournal[3];
		$currency = $Base1;
	} else if($currbase1 == 1){
		$amount = $rowsjournal[3];
		$currency = $Base1;
	} else if($currbase2 == 1){
		$amount = $rowsjournal[4];
		$currency = $Base2;
	}
	
   
	$worksheet->write($RowNum, $ColumnNum, $rowsjournal[0], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rowsjournal[1], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rowsjournal[2], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $currency, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $amount, $regularrightFormatNum1);
	$ColumnNum += 1;	
	
	$RowNum += 1;
	$ColumnNum = 0;
}
  



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 format_currency($amount, $DecimalBase) { 

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

$workbook->close();
	
?>