<?php

session_start();
header("Content-type: text/html; charset=".$_SESSION['encodingmode']);
if($_SESSION['dbName']==""){
header("location:../index.php");
}

$options=$_GET["options"];
$Document=$_GET["document"];

if ($options == 1) {
	$ReportName  = 'List of Top '. $toplimit . ' Clients';
	$ReportTitle = 'List of Top '. $toplimit . ' Clients';
} 
else {
	$ReportName  = 'List of Top '. $toplimit . ' Suppliers';
	$ReportTitle = 'List of Top '. $toplimit . ' Suppliers';
}

print <<< HERE
<HTML>
<HEAD>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<TITLE> $ReportName </TITLE>

<style type="text/css">
table.params td { font-size: 12px; 
                  font-family:Times-New-Roman;
                 }

			 
table.master th { font-size: 14px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
				 }
				 
table.master td { font-size: 12px; 
                  font-family:Times-New-Roman;
				  padding-left:3px;
                 }

						
table.master tr.group { font-size: 12px; 
						font-weight: bold;
						font-family:Times-New-Roman;
					  }				 
			 

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

if(	$_SESSION['encodingmode'] == 'utf8' || $_SESSION['encodingmode'] == 'utf-8'){
	mysql_query("SET NAMES 'utf8'"); 
	mysql_query('SET CHARACTER SET utf8'); 
	mysql_set_charset('utf8', $connection);
}
$querylog=" SELECT print as printaccess, ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter ".
		  " from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' and Upper(Login_Schema) = Upper('".$_SESSION['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, report_color1, report_color2, Company_Name As CompanyName from setup";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultsetup)){
  extract($rows);
}

//Read From Module Table
$querymodule=" Select ifNull(Module_Project,0) As Module_Project, ifNull(Module_Costcenter,0) As Module_CostCenter, ".
			 " ifNull(Module_ProjectParent,0) As Module_ProjectParent, ifNull(Module_CostCenterParent,0) As Module_CostCenterParent ".	
             " 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 (($Module_ProjectParent=1 and project.project_code like concat(ifnull(loginstk.project_code,project.project_code),'%')) or ($Module_ProjectParent=0 and project.project_code = loginstk.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 (($Module_CostCenterParent=1 and costcent.costcent_code like concat(ifnull(loginstk.costcent_code,costcent.costcent_code),'%')) or ($Module_CostCenterParent=0 and costcent.costcent_code = loginstk.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);		  

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


$SelectedOptions = "Filter by: <br>";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."<br>";	
if ($toplimit != '')	  $SelectedOptions .= "-> Top Limit: ". $toplimit . "<br>";

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

$fromdate  	   = GetSQLValueString($fromdate, 'date');
$todate        = GetSQLValueString($todate, 'date');   

if (empty($toplimit)) $toplimit = 10;
   

print <<< HERE
<table width="100%" border="0" align="left" >
<tr><td>
<table width="100%" border="0" align="center">
<tr>
  <td width='30%'><b>$CompanyName</b></td>
  <td width='50%' align='center'><font size=4><b>$ReportTitle</b></font></td>
  <td width='10%'><font size=1><b> Printed Date: </b> <br><b>Time: </b>
  <td width='10%'><font size=1>
HERE;

echo $printdate[2]."/".$printdate[1]."/".$printdate[0];
echo "<br>".$dt[1]." ".$dt[2];
  
print <<<HERE
</font></td>
</tr>
<tr>
   <td colspan='4' width='100%'> <hr width='100%' color='black' size='1'> </td>
</tr>
</table>


<table class="params" width="100%" border="0" align="center" >
<tr>
  <td width="100%" valign='top'><b>$SelectedOptions</b></td>
</tr>
<tr>
  <td colspan='15'><hr width='100%' color='black' size='1'></td>
</tr>
</table>
HERE;

echo "<table  border='0' width='850px;' align='left' class='master' style='table-layout:fixed;' cellpadding='1' cellspacing='0'>
	  <col width=80>	
	  <col width=120>
	  <col width=250>
	  <col width=120>
	  <col width=120>
	  <col width=100>
	  <col width=120>
	  <col width=160>";
	  
 
echo"<tr>
		<th align='left'>Exp/Loc</th>
		<th align='center'>Ledger Number</th>
		<th align='left'>Ledger Name</th>
		<th align='center'>Address</th>
		<th align='center'>Phone / Fax</th>
		<th align='center'>TIN</th>
		<th align='center'>$curr</th>";
		if ($options == 1) {
		echo"<th align='center'>Salesman</th>";
		}
echo"</tr>";

				
$query = '';
$query = $query." Select ExpLoc, Ledger_Number, Ledger_Name, TIN, JournalDtAmount1, JournalDtAmount12, JournalDtAmount1 as OrdByAmount, Address, Phone1, Phone2, Fax, SalesmanName ".
				" From (  ";
	//echo "xxxxxxxxxxxxxx".$options."<br>";			
if ($options == 1) {
	if (($projectexist > 0 && $FI_RepRestrictProject == 1) || ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)) {
		$query = $query." Select if(Client_Export=1, 'EXP', 'LOC') as ExpLoc,";
		if ($Document==1) {
			$query = $query." LEDGER.DOCUMENT AS LEDGER_NUMBER,  ";
		} else {
			$query = $query." JournalDt.Ledger_Number, ";
		}
		$query = $query." Clients.Ledger_Name, Client_FinancialNo as TIN, ".
						" Sum(if(JournalDt_dbcd='D',1,-1)*Cstjour_amount1) AS JournalDtAmount1, Sum(if(JournalDt_dbcd='D',1,-1)*Cstjour_amount2) AS JournalDtAmount12, concat(clients.Client_Address, '-', clients.Client_Address2, '-', clients.Client_Address3) as Address, clients.Client_Telephone1 as Phone1, clients.Client_Telephone2 as Phone2, clients.Client_Fax as Fax, salesmen.ledger_name as SalesmanName ".
						" From JournalDt ".
						//" Inner Join Journal ON Journal.Journal_Id = JournalDt.Journal_Id and Journal.Journal_Type IN ('SI','SR','CV','DV','JS') ".
						" Inner Join Journal ON Journal.Journal_Id = JournalDt.Journal_Id and ".
						" ((Journal.Journal_Type IN ('SI','DV','JS') and (journaldt.journaldt_dbcd='D')) ".
						" or (Journal.Journal_Type IN ('SR','CV') and (journaldt.journaldt_dbcd='C') )) ".
						" Inner Join ";
		$query = $query." Clients ON Clients.Ledger_Number = JournalDt.Ledger_Number ";
		if ($Document==1) {
			$query = $query." INNER JOIN LEDGER ON LEDGER.LEDGER_NUMBER=CLIENTS.LEDGER_NUMBER AND LEDGER.DOCUMENT IS NOT NULL ";
		}
		$query = $query." INNER JOIN salesmen ON Clients.Salesmen_Code = salesmen.ledger_number";
		$query = $query." Left outer Join CstJour As CstJour on Journaldt.JournalDt_ID = Cstjour.JournalDt_ID";
	
	} else {
		$query = $query." Select if(Client_Export=1, 'EXP', 'LOC') as ExpLoc,";
		if ($Document==1) {
			$query = $query." LEDGER.DOCUMENT AS LEDGER_NUMBER,  ";
		} else {
		    $query = $query." JournalDt.Ledger_Number, ";
		}
		$query = $query." Clients.Ledger_Name, Client_FinancialNo as TIN, ".
						" Sum(if(JournalDt_dbcd='D',1,-1)*JournalDt_Amount1) AS JournalDtAmount1, Sum(if(JournalDt_dbcd='D',1,-1)*JournalDt_Amount2) AS JournalDtAmount12, concat(clients.Client_Address, '-', clients.Client_Address2, '-', clients.Client_Address3) as Address, clients.Client_Telephone1 as Phone1, clients.Client_Telephone2 as Phone2, clients.Client_Fax as Fax, salesmen.ledger_name as SalesmanName ".
						" From JournalDt ".
						//" Inner Join Journal ON Journal.Journal_Id = JournalDt.Journal_Id and Journal.Journal_Type IN ('SI','SR','CV','DV','JS') ".
						" Inner Join Journal ON Journal.Journal_Id = JournalDt.Journal_Id and ".
						" ((Journal.Journal_Type IN ('SI','DV','JS') and (journaldt.journaldt_dbcd='D')) ".
						" or (Journal.Journal_Type IN ('SR','CV') and (journaldt.journaldt_dbcd='C') )) ".
						" Inner Join Clients ON Clients.Ledger_Number = JournalDt.Ledger_Number ";
		$query = $query." INNER JOIN salesmen ON Clients.Salesmen_Code = salesmen.ledger_number";
		if ($Document==1) {
			$query = $query." INNER JOIN LEDGER ON LEDGER.LEDGER_NUMBER=CLIENTS.LEDGER_NUMBER AND LEDGER.DOCUMENT IS NOT NULL ";
		}
		
	}
}
else {				
	if (($projectexist > 0 && $FI_RepRestrictProject == 1) || ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)) {
		$query = $query." Select if(Supplier_Export=1, 'EXP', 'LOC') as ExpLoc,";
		if ($Document==1) {
			$query = $query." LEDGER.DOCUMENT AS LEDGER_NUMBER,  ";
		} else {
		    $query = $query." JournalDt.Ledger_Number, ";
		}
		$query = $query."  Suppliers.Ledger_Name, Suppliers.Supplier_VatNo as TIN, ".
						" Sum(if(JournalDt_dbcd='D',-1,1)*Cstjour_amount1) AS JournalDtAmount1, Sum(if(JournalDt_dbcd='D',-1,1)*Cstjour_amount2) AS JournalDtAmount12,  suppliers.Supplier_Address as Address, suppliers.Supplier_Telephone1 as Phone1, suppliers.Supplier_Telephone2 as Phone2, suppliers.Supplier_Fax as Fax, '' as SalesmanName ".
						" From JournalDt ".
						" Inner Join Journal ON Journal.Journal_Id = JournalDt.Journal_Id and Journal.Journal_Type IN ('PI','PR','CV','DV','FV','JP') ".
						" Inner Join Suppliers ON Suppliers.Ledger_Number = JournalDt.Ledger_Number ";
		if ($Document==1) {
			$query = $query." INNER JOIN LEDGER ON LEDGER.LEDGER_NUMBER=Suppliers.LEDGER_NUMBER AND LEDGER.DOCUMENT IS NOT NULL ";
		}				
		$query = $query." Left outer Join CstJour As CstJour on Journaldt.JournalDt_ID = Cstjour.JournalDt_ID ";
		
	} else {
		$query = $query." Select if(Supplier_Export=1, 'EXP', 'LOC') as ExpLoc,";
		if ($Document==1) {
			$query = $query." LEDGER.DOCUMENT AS LEDGER_NUMBER,  ";
		} else {
		    $query = $query." JournalDt.Ledger_Number, ";
		}
		$query = $query."  Suppliers.Ledger_Name, Suppliers.Supplier_VatNo as TIN, ".
						" Sum(if(JournalDt_dbcd='D',-1,1)*JournalDt_Amount1) AS JournalDtAmount1, Sum(if(JournalDt_dbcd='D',-1,1)*JournalDt_Amount2) AS JournalDtAmount12,  suppliers.Supplier_Address as Address, suppliers.Supplier_Telephone1 as Phone1, suppliers.Supplier_Telephone2 as Phone2, suppliers.Supplier_Fax as Fax, '' as SalesmanName ".
						" From JournalDt ".
						" Inner Join Journal ON Journal.Journal_Id = JournalDt.Journal_Id and Journal.Journal_Type IN ('PI','PR','CV','DV','FV','JP') ".
						" Inner Join Suppliers ON Suppliers.Ledger_Number = JournalDt.Ledger_Number ";
		if ($Document==1) {
			$query = $query." INNER JOIN LEDGER ON LEDGER.LEDGER_NUMBER=Suppliers.LEDGER_NUMBER AND LEDGER.DOCUMENT IS NOT NULL ";
		}
		
	}
}	


if ($projectexist >	0 && $FI_RepRestrictProject == 1)
	$query = $query." Inner join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
					"			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
					"              and (project_code is not null and project_code != '') ".
					"			 ) Q1 on (($Module_ProjectParent=1 and CstJour.project_code like concat(Q1.project_code,'%')) or ($Module_ProjectParent=0 and CstJour.project_code = Q1.project_code )) ";
							 
if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
	$query = $query." 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 (($Module_CostCenterParent=1 and CstJour.costcent_code like concat(Q2.Costcent_Code,'%')) or ($Module_CostCenterParent=0 and CstJour.Costcent_Code = Q2.costcent_code )) ";							 


/*if ($ledgerexist > 0)
	$query = $query." Inner join ( select distinct ledger_number from ".$_SESSION['dbLabel'].".ledgerstk ".
					"			   where user_account='".$_SESSION['useraccount']."' ".
					"              and (ledger_number is not null and ledger_number != '') ".
					"			 )  Q3 on (($ledgerinclude = 1 and JournalDt.Ledger_Number like concat(Q3.ledger_number,'%') ) or ($ledgerinclude = 0 and JournalDt.Ledger_Number Not like concat(Q3.ledger_number,'%') )) ";							 
*/
							 
$query = $query." Where Date_format(JournalDt.Journal_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(JournalDt.Journal_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(JournalDt.Journal_Date,'%Y-%m-%d'))  ";
if($options == 1){
	if($_GET['clients_type'] == "1"){
			$query = $query." and client_financialno is not null and client_financialno <> '' and client_financialno <> 0";
		}
		else
			if($_GET['clients_type'] == "0"){
				$query = $query." and (client_financialno is null or client_financialno ='' or client_financialno = 0) ";
			}
			else
			{
				$query = $query."";
			}
	
}
else{
	if($_GET['clients_type'] == "1"){
			$query = $query." and Supplier_vatno is not null and Supplier_vatno <> '' and Supplier_vatno <> 0";
		}
		else
			if($_GET['clients_type'] == "0"){
				$query = $query." and (Supplier_vatno is null or Supplier_vatno ='' or Supplier_vatno = 0) ";
			}
			else
			{
				$query = $query."";
			}
}
if($ledgerinclude== 1 && $ledgerexist > 0) {
	$query = $query." And Exists ( select 1 from ".$HTTP_SESSION_VARS['dbLabel'].".ledgerrepstk ".
					"              where ledgerrepstk.user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
					"              and (ledger_number is not null and ledger_number != '') ".
					"              and JournalDt.ledger_number like concat(ledgerrepstk.ledger_number,'%') ".
					"            ) ";
}
if($ledgerinclude== 0 && $ledgerexist > 0){
	$query = $query." And Not Exists ( select 1 from ".$HTTP_SESSION_VARS['dbLabel'].".ledgerrepstk ".
					"                  where ledgerrepstk.user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
					"          	       and (ledger_number is not null and ledger_number != '') ".
					"                  and JournalDt.ledger_number like concat(ledgerrepstk.ledger_number,'%') ".
					"                ) ";	
}
if ($Document==1) {
	   $query = $query." Group By ExpLoc, LEDGER.DOCUMENT ";
	} else {
	   $query = $query." Group By ExpLoc, JournalDt.Ledger_Number ";
	}
$query = $query." ) As Q1 ".
				" 	Order by OrdByAmount Desc ";
				
if ($toplimit != '') $query = $query." Limit ".$toplimit;


//echo $query; exit;
$result=mysql_query($query,$connection) or die(mysql_error());
$col=0;
while($rows=mysql_fetch_row($result)){
	if($col%2==0){
		$color = "style='background-color:".$report_color1."'";
	}else{
		$color = "style='background-color:".$report_color2."'";
	}
	
	if ($curr == $Base1)
		$Amount = $rows[4];
	else
		$Amount = $rows[5];
		
	echo "<tr $color>"; 
	echo "	<td align='left'   valign='top' style='border-right:1px solid black; '>". $rows[0] ."</td>";
	echo "	<td align='left'   valign='top' style='border-right:1px solid black; padding-left:2px;'>". $rows[1] ."</td>";
	echo "	<td align='left'   valign='top' style='border-right:1px solid black; padding-left:2px;'>". $rows[2] ."</td>";
	echo "	<td align='left'   valign='top' style='border-right:1px solid black; padding-left:2px;'>". $rows[7] ."</td>";
	echo "	<td align='left'   valign='top' style='border-right:1px solid black; padding-left:2px;'>". $rows[8] ." <br> ".$rows[9]." <br> ".$rows[10]."</td>";
	echo "	<td align='left'   valign='top' style='border-right:1px solid black; padding-left:2px;'>&nbsp;". $rows[3] ."</td>";
	echo "	<td align='right'  valign='top' style='border-right:1px solid black; padding-right:2px;'>". format_currency($Amount, $DecimalBase) ."</td>";
	if ($options == 1) {
	echo "	<td align='left'   valign='top' style='border-right:1px solid black; padding-left:2px;'>". $rows[11] ."</td>";
	}
	echo "</tr>";
	$col+=1;
}

echo "</table>";

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 (number_format(abs($amount),$DecimalBase) == 0)
	    return 0; 
	else if($amount < 0) 
        return "(".number_format(abs($amount),$DecimalBase).")"; 
    else return number_format($amount, $DecimalBase); 
} 

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

if ($printaccess==1)
print <<< HERE

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


?>