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

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

include("config.inc.php");

ini_set('max_execution_time', $config['ReportTimeExecution']);

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

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


						
$querysetup= " select DecimalBase, DecimalBase1, DecimalBase2, base1, base2, grid_color1, grid_color2, Company_Name As CompanyName, ".
             " Date_Format(Setup_VatStartDate,'%Y%m%d') As Setup_VatStartDate, ifNull(Setup_VatPercentage,0) As Setup_VatPercentage from setup ";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rowsetup=mysql_fetch_array($resultsetup)){
	extract($rowsetup);
}
	//User Access
$querylog=" select ifNull(login_belongstogrp,0) As FI_BelongsToGrp from ".$HTTP_SESSION_VARS['dbLabel'].".login where user_account='".$HTTP_SESSION_VARS['useraccount']."' and Upper(Login_Schema) = Upper('".$HTTP_SESSION_VARS['dbName']."')  ";
$resultlog=mysql_query($querylog,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}

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

 
//Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
 
//Sending HTTP headers
$workbook->send('Exp_TransactionJv.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
  ));  
$italicFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'left'  ,
  'italic'=>1,
  'bold'=>1
  
  ));  

 $worksheet->setColumn (0, 0, 12);	
 $worksheet->setColumn (1, 1, 23.5);	
 $worksheet->setColumn (5, 5, 12);	
 $worksheet->setColumn (6, 6, 12);	
 $worksheet->setColumn (7, 7, 12);	


 
                $worksheet->write(1,0,$CompanyName, $TitleFormat);
				$worksheet->write(1,5,"Transaction Journal Voucher", $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);



	if ($fromref=='')
		$fromref = 'null';
	if ($toref=='')
		$toref = 'null';
	if ($fromfolio=='')
		$fromfolio = 'null';
	if ($tofolio=='')
		$tofolio = 'null';
		
	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';     


			
	$query = " select Journal_refgovernment, Journal_folio, ".
             " Journal_typeref,Journal_type, Date_Format(journal.Journal_date,'%d/%m/%Y'),Journaldt_valuedate, ".
             " journaldt.ledger_number,ledger.ledger_name,journaldt.manual_no,journaldt_dbcd, ".
			 " round(journaldt_amount,".$DecimalBase."),round(journaldt_amount1,".$DecimalBase1."),round(journaldt_amount2,".$DecimalBase2."),journaldt.Currency_code, journaldt_desc, journaldt_desc1, ".
			 " round(Amnt1_db ,".$DecimalBase1."), round(Amnt1_cd ,".$DecimalBase1."), round(Amnt2_db ,".$DecimalBase2."), round(Amnt2_cd,".$DecimalBase2."), journaldt.journaldt_id ".
			 " from Journal ".
			 " inner join journaldt on journaldt.journal_id=journal.journal_id ".
			 " inner join ledger on ledger.ledger_number=journaldt.ledger_number ".
			 " where ( ($option = 2 and  journal_refgovernment between ifnull($fromref,0) and ifnull($toref,9999999999999)) or ".
			 " ($option = 3 and Journal_folio between ifnull($fromfolio,0) and ifnull($tofolio,9999999999999)) or ".
			 " ($option = 1 and 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')) )) ".
			 " and  (($alltypes = 1 and 1 = 1) or ($alltypes = 0 and $jvtypelen = 0 and 1 = 1) or ($alltypes = 0 and $exclude = 0 and $jvtypelen > 0 and Journal_type in ($jvtype)) or ".
			 "      ($alltypes = 0 and $exclude = 1 and $jvtypelen > 0 and Journal_type not in ($jvtype)) ) ";

//(($jvtypelen > 0 and Journal.Journal_type in ($jvtype)) or ($jvtypelen = 0 and 1 = 1))	 
//echo $query;
$t_Ref='';
$rownum=4;

$vTotAmt_DbB1 = 0;
$vTotAmt_DbB2 = 0;
$vTotAmt_CrB1 = 0;
$vTotAmt_CrB2 = 0;



$result=mysql_query($query,$connection) or die(mysql_error());
while($rows=mysql_fetch_row($result)){

	if ($t_Ref!=$rows[0]) {
		if ($t_Ref != '') {

			
		$rownum = $rownum + 1;
		$rownum = $rownum + 1;
		$worksheet->write($rownum,5,"Debit", $boldFormat);
		$worksheet->write($rownum,6,number_format($vTotAmt_DbB1, $DecimalBase1), $boldFormat);
		$worksheet->write($rownum,7,number_format($vTotAmt_DbB2, $DecimalBase2), $boldFormat);
		
		$rownum = $rownum + 1;
		$worksheet->write($rownum,5,"Credit", $boldFormat);
		$worksheet->write($rownum,6,number_format($vTotAmt_CrB1, $DecimalBase2), $boldFormat);
		$worksheet->write($rownum,7,number_format($vTotAmt_CrB2, $DecimalBase2), $boldFormat);
		
			
			$vTotAmt_DbB1 = 0;
			$vTotAmt_DbB2 = 0;
			$vTotAmt_CrB1 = 0;
			$vTotAmt_CrB2 = 0;
		
		
		}
		
		$rownum = $rownum + 1;
		$worksheet->write($rownum,0,"Reference", $boldFormat);
		$worksheet->write($rownum,1,"$rows[0]", $boldFormat);
		$worksheet->write($rownum,6,"Folio", $boldFormat);
		$worksheet->write($rownum,7,"$rows[1]", $boldFormat);
		
		$rownum = $rownum + 1;
		$worksheet->write($rownum,0,"Type", $boldFormat);
		$worksheet->write($rownum,1,"$rows[2]", $boldFormat);
		$worksheet->write($rownum,6,"Date", $boldFormat);
		$worksheet->write($rownum,7,"$rows[4]", $boldFormat);
		

		
		$vTotAmt_DbB1 = $rows[16];
		$vTotAmt_DbB2 = $rows[18];
		$vTotAmt_CrB1 = $rows[17];
		$vTotAmt_CrB2 = $rows[19];
		

				
		$i=1;
	}

	 

	if($i=='1') {
	
	$rownum = $rownum + 1;
	$rownum = $rownum + 1;
	$worksheet->write($rownum,0,"Ledger No.", $boldFormat);
	$worksheet->write($rownum,1,"Ledger Name/Description", $boldFormat);
	
		
		
		if ($varmanual ==1 )
		$worksheet->write($rownum,2,"Manual No.", $boldFormat);
	
		else
			$worksheet->write($rownum,2,"", $boldFormat);
			
			$worksheet->write($rownum,3,"D/C.", $boldFormat);
			$worksheet->write($rownum,4,"Cur.", $boldFormat);
			$worksheet->write($rownum,5,"Amount", $boldFormat);
			$worksheet->write($rownum,6,"$base1", $boldFormat);
			$worksheet->write($rownum,7,"$base2", $boldFormat);
			
		
	}	
    $rownum = $rownum + 1;
	$worksheet->write($rownum,0,"$rows[6]", $regularFormat);
	$worksheet->write($rownum,1,"$rows[7]", $regularFormat);
	

	
	if ($varmanual ==1 )
	$worksheet->write($rownum,2,"$rows[8]", $regularFormat);
		
	else
		$worksheet->write($rownum,2,"", $boldFormat);
		
		$worksheet->write($rownum,3,"$rows[9]", $regularFormat);
		$worksheet->write($rownum,4,"$rows[13]", $regularFormat);
		$worksheet->write($rownum,5,number_format($rows[10], $DecimalBase), $regularFormat);
		$worksheet->write($rownum,6,number_format($rows[11], $DecimalBase1), $regularFormat);
		$worksheet->write($rownum,7,number_format($rows[12], $DecimalBase2), $regularFormat);
		
	$rownum = $rownum + 1;
	
	$worksheet->write($rownum,1,"$rows[14]", $regularFormat);
	$worksheet->write($rownum,2,"$rows[15]", $regularFormat);
		


	if ($varcst ==1) 
	{
		$sub_query = " select cstjour.project_code,cstjour.costcent_code,Cstjour_prcnt, ".
					 " round(Cstjour_amount,".$DecimalBase."),round(Cstjour_amount1,".$DecimalBase1."), round(Cstjour_amount2,".$DecimalBase1.") ".
					 " from cstjour where cstjour.journaldt_id = '".$rows[20]."'";
					 
		$sub_result=mysql_query($sub_query,$connection) or die(mysql_error());			 
		$sub_RowCount = mysql_num_rows($sub_result);
		if (empty($sub_RowCount)) $sub_RowCount = 0;
		
		if ($sub_RowCount > 0) {
		$rownum = $rownum + 1;
		
		$worksheet->write($rownum,1,"Project", $italicFormat);
		$worksheet->write($rownum,2,"Costcenter", $italicFormat);
		$worksheet->write($rownum,3,"Percentage", $italicFormat);
		$worksheet->write($rownum,5,"Amount", $italicFormat);
			
			
			while($sub_rows=mysql_fetch_row($sub_result)){
			$rownum = $rownum + 1;
			
		$worksheet->write($rownum,1,"$sub_rows[0]", $regularFormat);
		$worksheet->write($rownum,2,"$sub_rows[1]", $regularFormat);
		$worksheet->write($rownum,3,"$sub_rows[2]", $regularFormat);
		$worksheet->write($rownum,5,"$sub_rows[3]", $regularFormat);
		$worksheet->write($rownum,6,"$sub_rows[4]", $regularFormat);
		$worksheet->write($rownum,7,"$sub_rows[5]", $regularFormat);
		
				
			}
			
	
	}
		
	}

	
	if ($varcheck ==1)
	{
		$sub_query1 = " select Chk_Number,Chk_Bank, Date_Format(Chk_ValueDate,'%d/%m/%Y'),chkjour.Currency_code, Chk_Amount  ".
					  " from chkjour where chkjour.journaldt_id = '".$rows[20]."'";
					  
		$sub_result1=mysql_query($sub_query1,$connection) or die(mysql_error());			 
		$sub_RowCount1 = mysql_num_rows($sub_result1);
		if (empty($sub_RowCount1)) $sub_RowCount1 = 0;
		
		if ($sub_RowCount1 > 0) {
		$rownum = $rownum + 1;
		
		$worksheet->write($rownum,1,"Check No.", $boldFormat);
		$worksheet->write($rownum,2,"Bank", $boldFormat);
		$worksheet->write($rownum,3,"Value Date", $boldFormat);
		$worksheet->write($rownum,4,"Currency", $boldFormat);
		$worksheet->write($rownum,5,"Amount", $boldFormat);
			
				
			while($sub_rows1=mysql_fetch_row($sub_result1)){
			
			$rownum = $rownum + 1;
			
		$worksheet->write($rownum,1,"$sub_rows1[0]", $regularFormat);
		$worksheet->write($rownum,2,"$sub_rows1[1]", $regularFormat);
		$worksheet->write($rownum,3,"$sub_rows1[2]", $regularFormat);
		$worksheet->write($rownum,4,"$sub_rows1[3]", $regularFormat);
		$worksheet->write($rownum,5,"$sub_rows1[4]", $regularFormat);
				
			}
			
		}	
	
	}

	$i=$i+1;
	$t_Ref=$rows[0];

}

if ($t_Ref != '') {

		$rownum = $rownum + 1;
		$rownum = $rownum + 1;
		$worksheet->write($rownum,5,"Debit", $boldFormat);
		$worksheet->write($rownum,6,number_format($vTotAmt_DbB1, $DecimalBase1), $boldFormat);
		$worksheet->write($rownum,7,number_format($vTotAmt_DbB2, $DecimalBase2), $boldFormat);
		
		$rownum = $rownum + 1;
		$worksheet->write($rownum,5,"Credit", $boldFormat);
		$worksheet->write($rownum,6,number_format($vTotAmt_CrB1, $DecimalBase1), $boldFormat);
		$worksheet->write($rownum,7,number_format($vTotAmt_CrB2, $DecimalBase2), $boldFormat);
	
			
	$vTotAmt_DbB1 = 0;
	$vTotAmt_DbB2 = 0;
	$vTotAmt_CrB1 = 0;
	$vTotAmt_CrB2 = 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;
}



$workbook->close(); 
?>