<?php


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

print <<< HERE

<HTML>
<HEAD>
<TITLE>List Of Purchases JV</TITLE>

<style type="text/css">

table.master th { font-size: 14px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
				  border-top: 2 solid #000000;
				  border-bottom: 1 solid #000000;
				 }
table.master td { font-size: 14px; 
                  font-family:Times-New-Roman;
				  
                 }


</style>


</HEAD>


HERE;

include("config.inc.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");

//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);
	}


if ($printaccess==0 or $printaccess=='')
echo'<BODY oncontextmenu="return false;">';
else
echo'<BODY>';
						
$querysetup= " select DecimalBase, DecimalBase1, DecimalBase2, base1, base2, grid_color1, grid_color2, ".
             " 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]);




	echo "<table border=0 width=100% cellspacing=0 cellpadding=0><tr><td align='left'>";
	echo "<table border=0 width='850' cellspacing=0 cellpadding=0 class='master' style='table-layout:fixed;' >
		  <col width=80>
		  <col width=90>
		  <col width=80>
		  <col width=100>
		  <col width=240>
		  <col width=100>
		  <col width=110>
		  <col 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>List Of Purchases JV</font></td><td colspan='4' align='right' ><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]. " ".$dt[3];
	echo "</font></td></tr>";

    $dispfromdate = $fromdate;
	$disptodate = $todate;
	$dispfromcostcent = $fromcostcent;
	$dispfromproject = $fromproject;
	
	if ($fromref=='')
		$fromref = 'null';
	if ($toref=='')
		$toref = 'null';
	if ($fromclient=='')
		$fromclient = 'null';
	if ($toclient=='')
		$toclient = '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."'";	
	   
	
	if (empty($fromproject))   
	   $fromproject = 'null';
	else
	   $fromproject = "'".$fromproject."'";    
	   
    if (empty($fromcostcent))   
	   $fromcostcent = 'null';
	else
	   $fromcostcent = "'".$fromcostcent."'";   

	   
			
	$query = " select Date_Format(PurchaseJV_Date,'%d/%m/%Y'), PurchaseJV_Reference, ".
             " Journal_refgovernment, PurchasesJV.Supplier_Code, Suppliers.Ledger_name, PurchasesJV.Currency_Code, ".
			 " round(PurchaseJV_VAT ,".$DecimalBase."), round(PurchaseJV_VATBase1 ,".$DecimalBase1."), round(PurchaseJV_VATBase2 ,".$DecimalBase2."),".
			 " round(PurchaseJV_Total,".$DecimalBase."),round(PurchaseJV_TotalBase1,".$DecimalBase1."),round(PurchaseJV_TotalBase2,".$DecimalBase2."), PurchaseJV_Id ".
			 " from PurchasesJV ".
			 " inner join suppliers on suppliers.Ledger_number= PurchasesJV.Supplier_Code ".
			 " left join journal on journal.Journal_id=PurchasesJV.Journal_Id ".
			 " where ( ($option = 2 and  PurchaseJV_Reference between ifnull($fromref,0) and ifnull($toref,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 ( PurchasesJV.Supplier_Code between ifnull(".$fromclient.",'0') and ifnull(".$toclient.",'9999999999999'))".
			 //" and (($varcurrency = 2 and PurchasesJV.Currency_Code= '".$base1."') or ($varcurrency = 3 and PurchasesJV.Currency_Code = '".$base2."') or ($varcurrency = 1 and 1=1)) ".
			 " and upper(ifNull(PurchasesJV.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(PurchasesJV.project_code,'-1'))) ".
			 " and upper(ifNull(PurchasesJV.Costcenter_Code,'-1')) like ifNull(upper(".$fromcostcent."), upper(ifNull(PurchasesJV.Costcenter_Code,'-1')))   ".
			 " order by if($option=2, PurchaseJV_Reference, Null), if($option=1, PurchaseJV_Date, Null )";
		

//echo $query;


		echo "<tr height='20'><td></td></tr>";
	    echo "<tr><td><b>Date &nbsp;&nbsp; From </b></td><td>  &nbsp; $dispfromdate</td>";
		echo "<td align= 'right'><b>To</b></td><td> &nbsp; $disptodate</td>";	
		echo"<td align='right'><b>CostCenter</b></td>";
		echo"<td>$dispfromcostcent</td>";
		echo"<td align='right'><b>Project</b></td>";
		echo"<td>$dispfromproject</td>";
		echo"</tr>";
		
		echo "<tr height='30'><td></td></tr>";
		echo "<td style='border-left: 0 solid #000000; border-top: 0 solid #000000; border-bottom: 1 solid #000000;'><b>Date</b></td>";
        echo "<td align='center' style='border-left: 0 solid #000000; border-top: 0 solid #000000; border-bottom: 1 solid #000000;'><b>Ref.</b></td>";
		echo "<td align='left' style='border-left: 0 solid #000000; border-top: 0 solid #000000; border-bottom: 1 solid #000000;'><b>JV Ref.</b></td>";
		echo "<td align='left' style='border-left: 0 solid #000000; border-top: 0 solid #000000; border-bottom: 1 solid #000000;'><b>Ledger No.</b></td>";
		echo "<td align='left' style='border-left: 0 solid #000000; border-top: 0 solid #000000; border-bottom: 1 solid #000000;'><b>Name</b></td>";
		echo "<td align='center' style='border-left: 0 solid #000000; border-top: 0 solid #000000; border-bottom: 1 solid #000000;'><b>Cur.</b></td>";
		echo "<td align='center' style='border-left: 0 solid #000000; border-top: 0 solid #000000; border-bottom: 1 solid #000000;'><b>VAT</b></td>";
		echo "<td align='center' style='border-left: 0 solid #000000; border-top: 0 solid #000000; border-bottom: 1 solid #000000;'><b>Amount</b></td>";
		
		echo"<tr></tr>";

				if ($varDetail==1) {
				
			echo"<tr><td colspan='9'>";
			echo "<table border=0 width='740' cellspacing=0 cellpadding=0 class='master' style='table-layout:fixed;' >
				 <col width=80>
				 <col width=180>
				 <col width=180>
				 <col width=100>
				 <col width=100>
				 <col width=100>";
		echo"<tr>";
		echo"<td style='border-left: 0 solid #000000; border-top: 0 solid #000000; border-bottom: 1 solid #000000;'><b>Ledger No.</b></td>";
		echo"<td style='border-left: 0 solid #000000; border-top: 0 solid #000000; border-bottom: 1 solid #000000;'><b>Name</b></td>";
		echo"<td style='border-left: 0 solid #000000; border-top: 0 solid #000000; border-bottom: 1 solid #000000;'><b>Description</b></td>";
		echo"<td style='border-left: 0 solid #000000; border-top: 0 solid #000000; border-bottom: 1 solid #000000;'><b>Qty</b></td>";
		echo"<td style='border-left: 0 solid #000000; border-top: 0 solid #000000; border-bottom: 1 solid #000000;'><b>Price</b></td>";
	    echo"<td style='border-left: 0 solid #000000; border-top: 0 solid #000000; border-bottom: 1 solid #000000;'><b>Total</b></td>";
		echo"<td style='border-left: 0 solid #000000; border-top: 0 solid #000000; border-bottom: 1 solid #000000;' colspan='4'>&nbsp;</td>";
		echo"</tr>";
		
		echo "</table></tr></td>";
		}
		
$result=mysql_query($query,$connection) or die(mysql_error());
while($rows=mysql_fetch_row($result)){
		

echo"<tr></tr>";
echo"<td>$rows[0]</td>";
echo"<td>$rows[1]</td>";
echo"<td>$rows[2]</td>";
echo"<td>$rows[3]</td>";
echo"<td>$rows[4]</td>";


  if ($varcurrency == 1){
echo"<td align= 'center'>$rows[5]</td>";
echo"<td align='right'>".number_format($rows[6],$DecimalBase)."</td>";
echo"<td align='right'>".number_format($rows[9],$DecimalBase)."</td>";	
	}
	  if ($varcurrency == 2){
echo"<td align= 'center'>$base1</td>";	
echo"<td align='right'>".number_format($rows[7],$DecimalBase1)."</td>";
echo"<td align='right'>".number_format($rows[10],$DecimalBase1)."</td>";	
	}	

	  if ($varcurrency == 3){
echo"<td align= 'center'>$base2</td>";
echo"<td align='right'>".number_format($rows[8],$DecimalBase2)."</td>";
echo"<td align='right'>".number_format($rows[11],$DecimalBase2)."</td>";	
	}	 

	
	if ($varDetail==1) {
		echo"<tr><td colspan='9'>";
			echo "<table border=0 width='740' cellspacing=0 cellpadding=0 class='master' style='table-layout:fixed;' >
				 <col width=80>
				 <col width=180>
				 <col width=180>
				 <col width=100>
				 <col width=100>
				 <col width=100>";

 $queryDetail= " select PurchasesJVdt.Ledger_Number, Ledger.Ledger_Name, PurchaseJVDt_Description, purchasejvdt_quantity, ".
			   " round(purchasejvdt_price,".$DecimalBase."), purchasejvdt_discper, ".
			   " (ifNull(purchasejvdt_quantity,0) * ifNull(purchasejvdt_price,0) * (1 - ifNull(purchasejvdt_discper,0))) As PurchaseJVDt_Total,  ".
			   " (ifNull(purchasejvdt_quantity,0) * ifNull(purchasejvdt_price,0) * (1 - ifNull(purchasejvdt_discper,0))) As PurchaseJVDt_TotalBase1,  ".
			   " (ifNull(purchasejvdt_quantity,0) * ifNull(purchasejvdt_price,0) * (1 - ifNull(purchasejvdt_discper,0))) As PurchaseJVDt_TotalBase2  ".
			   " from PurchasesJVdt Inner Join Ledger on Ledger.Ledger_Number = PurchasesJVdt.Ledger_Number ".
			   " where PurchasesJVdt.PurchaseJV_Id = '".$rows[12]."'";

		//echo $queryDetail;            
		$resultDetail=mysql_query($queryDetail,$connection) or die(mysql_error());
		while($rowsDetail=mysql_fetch_row($resultDetail)){
			
			echo "<tr>";
			echo"<td><i><font size=2>$rowsDetail[0]</font></i></td>";
			echo"<td><i><font size=2>$rowsDetail[1]</font></i></td>";
			echo"<td><i><font size=2>$rowsDetail[2]</font></i></td>";
			echo"<td><i><font size=2>$rowsDetail[3]</font></i></td>";
			
			if ($varcurrency==1){
			echo"<td><i><font size=2>".number_format($rowsDetail[4],$DecimalBase)."</font></i></td>";
			echo"<td><i><font size=2>".number_format($rowsDetail[6],$DecimalBase)."</font></i></td>";
			}
			
			if ($varcurrency==2){
			echo"<td><i><font size=2>".number_format($rowsDetail[9],$DecimalBase1)."</font></i></td>";
			echo"<td><i><font size=2>".number_format($rowsDetail[7],$DecimalBase1)."</font></i></td>";
			}
			
			if ($varcurrency==3){
			echo"<td><i><font size=2>".number_format($rowsDetail[10],$DecimalBase2)."</font></i></td>";
			echo"<td><i><font size=2>".number_format($rowsDetail[8],$DecimalBase2)."</font></i></td>";
			}
			
			echo "</tr>";
	
		}
		
		echo "</table></tr></td>";
		
	}
 }
  

 $queryTotal = "select sum(PurchaseJV_Total), sum(PurchaseJV_TotalBase1), sum(PurchaseJV_TotalBase2),sum(PurchaseJV_Vat), sum(PurchaseJV_VatBase1), sum(PurchaseJV_VatBase2), PurchasesJV.Currency_Code ".
			 " from PurchasesJV ".
			 " inner join suppliers on suppliers.Ledger_number= PurchasesJV.Supplier_Code ".
			 " left join journal on journal.Journal_id=PurchasesJV.Journal_Id ".
			 " where ( ($option = 2 and  PurchaseJV_Reference between ifnull($fromref,0) and ifnull($toref,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 ( PurchasesJV.Supplier_Code between ifnull(".$fromclient.",'0') and ifnull(".$toclient.",'9999999999999'))".
			 //" and (($varcurrency = 2 and PurchasesJV.Currency_Code= '".$base1."') or ($varcurrency = 3 and PurchasesJV.Currency_Code = '".$base2."') or ($varcurrency = 1 and 1=1)) ".
			 " and upper(ifNull(PurchasesJV.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(PurchasesJV.project_code,'-1'))) ".
			 " and upper(ifNull(PurchasesJV.Costcenter_Code,'-1')) like ifNull(upper(".$fromcostcent."), upper(ifNull(PurchasesJV.Costcenter_Code,'-1')))   ".
			 " group by if($varcurrency=1, PurchasesJV.Currency_Code, Null ) ";

			// echo $queryTotal;
if ($varcurrency == 1){
echo"<tr height='12'></tr>";
echo "<tr><td><b><u>Summary</b></u></td></tr>";
}
	
$resultTotal=mysql_query($queryTotal,$connection) or die(mysql_error());
while($rowsTotal=mysql_fetch_row($resultTotal)){

 echo"<tr></tr>";
 
 if ($varcurrency == 1){
	
    echo"<tr></tr>";
	echo"<td><b>Total $rowsTotal[6]</b></td><td align='right'><b>".number_format($rowsTotal[3], $DecimalBase1)."</b></td><td></td><td align='right'><b>".number_format($rowsTotal[0], $DecimalBase1)."</b></td>";
}

 if ($varcurrency == 2){
 echo"<td colspan='5'></td><td align='center'><b>Total $base1</b></td><td align='right'><b>".number_format($rowsTotal[4], $DecimalBase1)."</b></td><td align='right'><b>".number_format($rowsTotal[1], $DecimalBase1)."</b></td>";
}
 if ($varcurrency == 3){
 echo"<td colspan='5'></td><td align='center'><b>Total $base2</b></td><td align='right'><b>".number_format($rowsTotal[5], $DecimalBase2)."</b></td><td align='right'><b>".number_format($rowsTotal[2], $DecimalBase2)."</b></td>";
} 
}
 
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;
}
if ($printaccess==1)
print <<< HERE

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