<?php
session_start();

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

include("config.inc.php");

if($exp ==1){
	$filename = "exp_journal" . date('Ymd') . ".xls";
	header("Content-Disposition: attachment; filename=\"$filename\"");
	header("Content-Type: application/vnd.ms-excel");
} 

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

$query_setup  = "select company_name, company_memo, DecimalBase, DecimalBase1, DecimalBase2, Setup_VatPercentage, base1, base2, setup_invoiceremarks from setup";
$result_setup = mysql_query($query_setup, $connection); 
$row_setup    = mysql_fetch_array($result_setup);
extract($row_setup);

$query_login="select login_belongstogrp from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."')  ";
$resultlogin=mysql_query($query_login,$connection) or die(mysql_error());

$row_login=mysql_fetch_row($resultlogin);
$belongstogroup=$row_login[0];

$query_language  = "select user_account, Language from ".$_SESSION['dbLabel'].".login  where user_account = '".$_SESSION['useraccount']."'";
$result_language = mysql_query($query_language, $connection); 
$row_language    = mysql_fetch_array($result_language);
extract($row_language);

$query_journal_labels  = "select Language, label0 as Ref1, TypeRef as TypeRef1, label3 as Folio1, label2 as Date1, label1 as Type1, Project as Project1, Disp_2_1 as Costcenter2, Remark as Remark1, ManualNo as ManualNo1, Disp_1 as LedgerNo1, LedgerName as LedgerName1, Disp_4_1 as DescX, Disp_3 as Curr1, D_C as DC1, Disp_5 as Amount1, Disp_1_3 as ValueDate1, ManualNo as ManualNo1, Desc1 as Desc1,
                          NoRecords, Perpage, Page, Disp_2_2 as Perce, Disp_1_1 as CheckNo, Disp_1_2 as Bankk, Label4 as Debitt, Label5 as Creditt, Disp_4 as Balancee, JournalVoucher, PreparedBy, ControlledBy, ApprovedBy
                          from ".$_SESSION['dbLabel'].".journal_label
                          where Language = '".$Language."'";
$result_journal_labels = mysql_query($query_journal_labels, $connection);
$row_journal_labels    = mysql_fetch_array($result_journal_labels);
extract($row_journal_labels);
print <<<HERE
<html>
<head>
<title>JOURNAL VOUCHER REPORT</title>
</head>
<style>
.data{
	
	 FONT-WEIGHT: normal;
	 FONT-SIZE: 8pt;	 
	
	 FONT-FAMILY: arial, sans-serif;
	}
	
.dataitalic{
	
	 FONT-STYLE: italic;
	 FONT-WEIGHT: normal;
	 FONT-SIZE: 8pt;	 
	
	 FONT-FAMILY: arial, sans-serif;
	}

.headeritalic{
	
	 FONT-STYLE: italic;
	 FONT-WEIGHT: bold;
	 FONT-SIZE: 9pt;	 
	
	 FONT-FAMILY: arial, sans-serif;
	}
	
.header{
	
	 FONT-WEIGHT: bold;
	 FONT-SIZE: 9pt;	 
	
	 FONT-FAMILY: arial, sans-serif;
	}
	
.footerbox{
				
							   
	border-left: 0 solid #000000; border-top: 1 solid #000000; border-bottom: 0 solid #000000;
	FONT-WEIGHT: bold;
	FONT-SIZE: 14px;	 
	color:#2D2D2D;
	font-family:Times-New-Roman;
	BACKGROUND-COLOR: #FFFFFF; 
	
	height: 20;
	 	}
</style>
<body>
HERE;


echo "<table width=800>
<tr>
<td width=50%><img src='LogoFile.php' width=180 /></td>
<td width=50% style='text-align: center; font-family: Arial; font-size: 40px;  color: #a6a6a6;'>Journal Voucher</td>
</tr>
</table>
<table width=800>
<tr style='height: 20px'>
<td></td>
</tr>
</table>
<table width=800>
<tr>
<td width=500 style='font-family: Arial; font-size: 14px'><i><b><u>$company_name</u></b></i>
<br><b>
<pre>$company_memo</pre>
</td>
</tr>";
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;
}


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


		echo "<table width=100%>";
	
	/*echo "<tr><td height='60' colspan=2><img src='img/imgheader1.jpg' width='115' height='50' /></td><td colspan=4 align='center'><b><font size=5><u>Journal Voucher</u></font></td>
				<td align='right' colspan=3><font size='2'><b>Printed&nbsp;Date&nbsp;&amp;&nbsp;Time:</b>&nbsp;&nbsp;";
	echo $printdate[2]."/".$printdate[1]."/".$printdate[0];
	echo "<br>".$dt[1]." ".$dt[2];
	echo "</font></td></tr>";
	
	echo"</td></tr>";*/
	

	
	
	$query_journal="select Journal_id as id,Journal_refgovernment,Journal_folio,Journal_type,Journal_typeref,
											date_format(Journal_date,'%d-%m-%Y')
											Project_code,Costcent_Code,Journal_Remark,manual_no,
											(select sum(Journaldt_amount1) from journaldt where Journaldt_dbcd='D' and journal_id=id ),
											(select sum(Journaldt_amount1) from journaldt where Journaldt_dbcd='C' and journal_id=id ),
											(select sum(Journaldt_amount2) from journaldt where Journaldt_dbcd='D' and journal_id=id ),
											(select sum(Journaldt_amount2) from journaldt where Journaldt_dbcd='C' and journal_id=id ),
                                               user_account 											
											from journal where journal_id=".$journal_id."
											 and ifNull(journal.Journal_BelongsToGroup,0) >= ".$belongstogroup;
	//echo $query_journal;
	$result_journal=mysql_query($query_journal,$connection);
	$row_journal=mysql_fetch_row($result_journal);
	
	$journal_refgovernment=$row_journal[1];
	$journal_folio=$row_journal[2];
	$jvtype=$row_journal[3];
	$journal_typeref=$row_journal[4];
	$journal_date=$row_journal[5];
	$project_code=$row_journal[6];
	$costcent_code=$row_journal[7];
	$journal_remark=$row_journal[8];
	$manual_no=$row_journal[9];				
	$totaldb1=$row_journal[10];
	$totalcd1=$row_journal[11];
	$totaldb2=$row_journal[12];
	$totalcd2=$row_journal[13];
	$juser=$row_journal[13];

	$totaldb1=number_format($totaldb1, $DecimalBase1, '.', ',');
	$totalcd1=number_format($totalcd1, $DecimalBase1, '.', ',');
	$totaldb2=number_format($totaldb2, $DecimalBase2, '.', ',');
	$totalcd2=number_format($totalcd2, $DecimalBase2, '.', ',');



$query_username  = "select user_name as jusername from ".$_SESSION['dbLabel'].".users  where user_account = '$juser'";
$result_username = mysql_query($query_username, $connection); 
$row_username   = mysql_fetch_array($result_username);
extract($row_username);



	
	echo"<tr height=25px ><td style='border-top: 2 solid #000000; border-bottom: 0 solid #000000' >&nbsp;</td>
						<td align='right' class='header' style='border-top: 2 solid #000000; border-bottom: 0 solid #000000' >$Ref1: &nbsp;</td>
						<td class='data' valign='center' style='border-top: 2 solid #000000; border-bottom: 0 solid #000000' >$journal_refgovernment</td>
						<td align='right' class='header' style='border-top: 2 solid #000000; border-bottom: 0 solid #000000' >$TypeRef1 &nbsp;</td>
						<td class='data' valign='center' style='border-top: 2 solid #000000; border-bottom: 0 solid #000000' >$journal_typeref</td>
						<td align='right' class='header' style='border-top: 2 solid #000000; border-bottom: 0 solid #000000' >$Folio1 &nbsp;</td>
						<td class='data' valign='center' style='border-top: 2 solid #000000; border-bottom: 0 solid #000000' >$journal_folio</td>
						<td align='right' class='header' style='border-top: 2 solid #000000; border-bottom: 0 solid #000000' >$Date1: &nbsp;</td>
						<td class='data' valign='center' style='border-top: 2 solid #000000; border-bottom: 0 solid #000000' >$journal_date</td>
						</tr>";
	
	echo"<tr><td colspan=9><table border=0 width=100% cellspacing=0>
							<col width=8%>
							<col width=10%>
							<col width=20%>
							<col width=5%>
							<col width=8%>
							<col width=8%>
							<col width=15%>
							<col width=15%>
							<col width=15%>";
	
echo"<tr><td class='header' style='border-top: 1 solid #000000; border-bottom: 1 solid #000000' >$LedgerNo1</td>
		<td class='header' style='border-top: 1 solid #000000; border-bottom: 1 solid #000000'>$LedgerName1</td>
		<td class='header' style='border-top: 1 solid #000000; border-bottom: 1 solid #000000'>$DescX</td>
		<td class='header' style='border-top: 1 solid #000000; border-bottom: 1 solid #000000'>D/C</td>
		<td class='header' style='border-top: 1 solid #000000; border-bottom: 1 solid #000000'>$Curr1</td>
		<td class='header' style='border-top: 1 solid #000000; border-bottom: 1 solid #000000'>$ValueDate1</td>
		<td class='header' style='border-top: 1 solid #000000; border-bottom: 1 solid #000000' align='center'>$Amount1</td>
		<td class='header' style='border-top: 1 solid #000000; border-bottom: 1 solid #000000' align='center'>$base1</td>
		<td class='header' style='border-top: 1 solid #000000; border-bottom: 1 solid #000000' align='center'>$base2</td>
		</tr>";	
							
		
		$query_journaldt="select journaldt_id,journaldt.Ledger_number,Journaldt_desc,journaldt.Currency_code,Journaldt_dbcd,Journaldt_amount,Journaldt_amount1,".
						"Journaldt_amount2,date_format(Journaldt_ValueDate,'%d-%m-%Y'),manual_no,ledger_name ".
						" from journaldt ".
						" left join ledger on ledger.ledger_number=journaldt.ledger_number ".
						"where Journal_id=".$journal_id;
//echo $query_journaldt;			

$result_journaldt=mysql_query($query_journaldt,$connection);		
$i=1;
while ($row_journaldt=mysql_fetch_row($result_journaldt)){

	$amount=number_format($row_journaldt[5], $DecimalBase1, '.', ',');
	$amount1=number_format($row_journaldt[6], $DecimalBase1, '.', ',');
	$amount2=number_format($row_journaldt[7], $DecimalBase2, '.', ',');
	$currency_code=$row_journaldt[3];



		
echo"		<tr><td class='data'>$row_journaldt[1]</td>
			<td class='data'>$row_journaldt[10]</td>
			<td class='data'>$row_journaldt[2]</td>
			<td class='data'>$row_journaldt[4]</td>
			<td class='data'>$row_journaldt[3]</td>
			<td class='data'>$row_journaldt[8]</td>
			<td class='data' align='right'>$amount</td>
			<td class='data' align='right'>$amount1</td>
			<td class='data' align='right'>$amount2</td>
			
			</tr>";
			echo"<tr><td colspan='8'></td></tr>";
			echo"<tr><td colspan='8'><table border='0' width='100%'>
										<col width=20%>
										<col width=25%>
									
										<col width=12%>
										<col width=15%>
										<col width=15%>";
										
			$query_cstjour="select Cst_increment, Journaldt_id, Costcent_code, Project_Code, Cstjour_prcnt, Cstjour_amount, Cstjour_amount1, Cstjour_amount2
							from cstjour where journaldt_id=".$row_journaldt[0];
			//echo$query_cstjour;
			$result_cstjour=mysql_query($query_cstjour,$connection);
			
			$i=1;
			$j=1;
			$b=1;
			
			while($row_cstjour=mysql_fetch_row($result_cstjour)){
			
			
			$amountcst=number_format($row_cstjour[5], $DecimalBase, '.', ',');
			$amountcst1=number_format($row_cstjour[6], $DecimalBase1, '.', ',');
			$amountcst2=number_format($row_cstjour[7], $DecimalBase2, '.', ',');
			
			
				
				
				if($i=='1')
					echo"<tr><td class='headeritalic'>Project</td><td class='headeritalic'>Cost Center</td>
						<td class='headeritalic'>Percentage</td><td class='headeritalic' align='center'>Amount</td>
						<td class='headeritalic' align='center'>$base1</td><td class='headeritalic' align='center'>$base2</td></tr>";
			
				echo"<tr><td class='dataitalic'>$row_cstjour[3] &nbsp;</td><td class='dataitalic' >$row_cstjour[2] &nbsp;</td><td class='dataitalic' align='right'>$row_cstjour[4] &nbsp;</td>
						
						<td class='dataitalic' align='right'>$amountcst &nbsp;</td><td class='dataitalic' align='right'>$amountcst1 &nbsp;</td><td class='dataitalic' align='right'>$amountcst2 &nbsp;</td></tr>";
					
					
				$i=$i+1;
			}
			
			echo"<tr><td></td></tr>";
			
			$query_chkjour="select Chk_Increment, Journaldt_id, Ledger_Number, Chk_Number, Chk_Bank, Currency_code, Chk_Amount, Chk_Amount1, Chk_Amount2, Chk_type, date_format(Chk_ValueDate,'%d-%m-%Y')
							from chkjour where journaldt_id=".$row_journaldt[0];
			
			$result_chkjour=mysql_query($query_chkjour,$connection);
			while($row_chkjour=mysql_fetch_row($result_chkjour)){
			
				$amountchk=number_format($row_chkjour[6], $DecimalBase, '.', ',');
				$amountchk1=number_format($row_chkjour[7], $DecimalBase1, '.', ',');
				$amountchk2=number_format($row_chkjour[8], $DecimalBase2, '.', ',');
				
				if($j=='1')
					echo"<tr><td class='headeritalic'>Check No</td><td class='headeritalic'>Bank</td><td class='headeritalic'>Value Date</td>
						<td class='headeritalic' align='center'>Amount</td>
						<td class='headeritalic' align='center'>$base1</td><td class='headeritalic' align='center'>$base2</td></tr>";
						
				echo"<tr><td class='dataitalic'>$row_chkjour[3]</td><td class='dataitalic'>$row_chkjour[4]</td><td class='dataitalic'>$row_chkjour[10]</td>
						<td class='dataitalic' align='right'>$amountchk</td><td class='dataitalic' align='right'>$amountchk1</td>
						<td class='dataitalic' align='right'>$amountchk2</td></tr>";
			
				$j=$j+1;
			}
			
			$query_billjour="select bill_Increment, Journaldt_id, Ledger_Number, bill_Number, bill_Bank, Currency_code, bill_Amount, bill_Amount1, bill_Amount2, bill_type, date_format(bill_ValueDate,'%d-%m-%Y')
							from billjour where journaldt_id=".$row_journaldt[0];
			
			//echo$query_billjour;
			$result_billjour=mysql_query($query_billjour,$connection);
			while($row_billjour=mysql_fetch_row($result_billjour)){
			
				$amountbill=number_format($row_billjour[6], $DecimalBase, '.', ',');
				$amountbill1=number_format($row_billjour[7], $DecimalBase1, '.', ',');
				$amountbill2=number_format($row_billjour[8], $DecimalBase2, '.', ',');
				
				if($b=='1')
					echo"<tr><td class='headeritalic'>Bill No</td><td class='headeritalic' >Description</td><td class='headeritalic'>Value Date</td>
						<td class='headeritalic' align='center'>Amount</td>
						<td class='headeritalic' align='center'>$base1</td><td class='headeritalic' align='center'>$base2</td></tr>";
						
				echo"<tr><td class='dataitalic'>$row_billjour[3]</td><td class='dataitalic' >$row_billjour[4]</td><td class='dataitalic'>$row_billjour[10]</td>
						<td class='dataitalic' align='right'>$amountbill</td><td class='dataitalic' align='right'>$amountbill1</td>
						<td class='dataitalic' align='right'>$amountbill2</td></tr>";
						
				$b=$b+1;
			}
			
			
			echo"</table></td></tr>";
			echo"<tr><td>&nbsp;</td></tr>";
			
			
}
					
echo"<tr height=20px><td></td></tr></table></td></tr>";
echo"<tr><td colspan=9><table border=0 width=100%>
						<col width=15%>
						<col width=25%>
						<col width=17%>
				
				<tr><td colspan=1 class='header' valign='top'>$PreparedBy : </td><td class='header' valign='top'> $ControlledBy</td>
				<td class='header' valign='top'>$ApprovedBy </td><td rowspan=2 bgcolor=#D0D0D0 class='header'>
				<table border=0 width=75%><tr><td class='header' width=10%  valign='top'>$Debitt</td>
											<td class='header' width=47% align=right>$totaldb1</td>
											<td width=28%></td>
											<td width=20% class='header' align='left'>$totaldb2</td></tr>
						<tr><td class='header' width=10%  valign='top'>$Creditt</td>
						<td class='header' width=47% align=right>$totalcd1</td>
						<td width=15%></td>
						<td width=40% class='header' align='left'>$totalcd2</td></tr></table>
				</td></tr>";	
echo"<tr><td class='dataitalic'>$jusername &nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr>";
echo"</table></td></tr>";
echo "</table>";
echo "<table width=100%>";
	echo"<tr style='height: 35px'>
	<th colspan=6></th>
	</tr>
	<tr>
	<td colspan=6 style='font-size: 13px; font-weight: none; font-family: Arial'>$setup_invoiceremarks</td>
	</tr>
	<tr style='height: 45px'>
	<th colspan=6></th>
	</tr>
	<tr>
	<th colspan=6 style='font-size: 14px; font-family: Arial'></th>
	</tr>
	";
echo"</table>";
print <<<HERE
<script>
window.print();
</script>
</body>
</html>
HERE;
?>