<?php

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




 
print <<< HERE

<HTML>
<HEAD>
<TITLE> Salesmen Collections </TITLE>

<style type="text/css">

table.master th { font-size: 14px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
				 }
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);
}

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


$queryproject=" select distinct project.project_code, project_name ".
              " from project left join ".$HTTP_SESSION_VARS['dbLabel'].".loginstk on project.project_code like concat(ifnull(loginstk.project_code,project.project_code),'%') ".
			  " where user_account='".$HTTP_SESSION_VARS['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 ".$HTTP_SESSION_VARS['dbLabel'].".loginstk on costcent.costcent_code like concat(ifnull(loginstk.costcent_code,costcent.costcent_code),'%') ".
			   " where user_account='".$HTTP_SESSION_VARS['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);




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>";
	
echo'<tr><td colspan=15>
	<table width="100%" border="0" cellspacing="0" cellpadding="0" align="center">';

	echo "<tr><td height='60' colspan='3' style='border-bottom:1px solid black;'><img src='img/imgheader1.jpg' width='115' height='50' /></td><td colspan='3' align='center' style='border-bottom:1px solid black;'><b><font size=5>Salesman Collections</font></td><td colspan='5' align='right' style='border-bottom:1px solid black;' ><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>";

echo"</table></td></tr>";	
	
	echo"<tr><td align='left'>";
	echo "<table border=0 width='570' cellspacing=0 cellpadding=0 class='master' style='table-layout:fixed;' >
		  <col width=100>
		  <col width=80>
		  <col width=130>
		  <col width=130>
		  <col width=130>"; 
	
	if (!empty($fromdate))
	   $fromdate = explodeword($fromdate);
	if (!empty($todate))
	   $todate = explodeword($todate);  

	$fromdate  = GetSQLValueString($fromdate, 'text');
	$todate    = GetSQLValueString($todate, 'text');
	$fromref   = GetSQLValueString($fromref, 'text');
	$toref     = GetSQLValueString($toref, 'text');
	
	$fromledger    = GetSQLValueString($fromledger, 'text');
	$toledger      = GetSQLValueString($toledger, 'text');
	$fromsalesman  = GetSQLValueString($fromsalesman, 'text');
	$tosalesman    = GetSQLValueString($tosalesman, 'text');
	
	$fromproject   = GetSQLValueString($fromproject, 'text');
	$toproject     = GetSQLValueString($toproject, 'text');
	$fromcostcent  = GetSQLValueString($fromcostcent, 'text');
	$tocostcent    = GetSQLValueString($tocostcent, 'text');
		

	$QueryRestrictPrj = " Inner join ( select distinct project_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
						" 			   where loginstk.user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
						"              and (project_code is not null and project_code != '') ".
						"			 ) QP  ";
						
	$QueryRestrictCst = " Inner join ( select distinct costcent_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
						"			   where loginstk.user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
						"              and (costcent_code is not null and costcent_code != '') ".
						"			 ) QC  ";
					
	$query = " Select  salesmen.ledger_number as salesmencode, salesmen.ledger_name as salesmenname,  ".
			 " journal.Journal_type, journaldt.currency_code, sum(journaldt_amount), sum(journaldt_amount1), sum(journaldt_amount2), ".
			 " if(journal.Journal_type='RV', 'Receipts', if(journal.Journal_type='CV', 'Credit Notes', if(journal.Journal_type='SI', 'Cash Invoices', 'Cash Returns'))) ".
			 " from journaldt ".
			 " inner join journal on journal.journal_id = journaldt.journal_id ".
			 " inner join clients on clients.ledger_number = journaldt.ledger_number ".
			 " left join salesmen on clients.salesmen_code = salesmen.ledger_number ";
			 
	if ($projectexist >	0 && $FI_RepRestrictProject == 1)
		$query = $query.$QueryRestrictPrj." on journal.project_code like concat(QP.project_code,'%') ";
											 
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
		$query = $query.$QueryRestrictCst." on journal.costcent_code like concat(QC.costcent_code,'%') ";
				
	$query = $query." where journal.Journal_type in ('RV','CV','SI','SR') ".
					" and ((journal.Journal_type = 'SI' and journaldt.Journaldt_dbcd = 'C') or (journal.Journal_type = 'SR' and journaldt.Journaldt_dbcd = 'D') or (journal.Journal_type in ('RV','CV') and 1 = 1)) ".
					" and 	(($option = 1 and 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')) ) ".
					" 	  or ($option = 2 and date_format(journaldt.Journaldt_ValueDate,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(journaldt.Journaldt_ValueDate,'%Y-%m-%d')) and ifNull(". $todate.",date_format(journaldt.Journaldt_ValueDate,'%Y-%m-%d')) )".
					" 	  or ($option = 3 and Journal_refgovernment between ifnull($fromref,0) and ifnull($toref,9999999999999) )) ".
					" and (journaldt.ledger_number between ifnull(".$fromledger.",'0') and ifnull(".$toledger.",'9999999999999')) ";
if($projectper == 1)					
	$query = $query." and ifNull(journal.Project_Code,'-1') like ifNull(".$fromproject.",ifNull(journal.Project_Code,'-1')) ";
else	
$query = $query." and ifNull(journal.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(journal.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(journal.Project_Code,'-1')) ";
if($costcentper == 1)
	$query = $query." and ifNull(journal.Costcent_Code,'-1') like ifNull(".$fromcostcent.",ifNull(journal.Costcent_Code,'-1')) ";
else
$query = $query." and ifNull(journal.Costcent_Code,'-1') between ifNull(".$fromcostcent.",ifNull(journal.Costcent_Code,'-1')) and ifNull( ". $tocostcent.",ifNull(journal.Costcent_Code,'-1'))  ";
$query = $query." and ifNull(salesmen.ledger_number,'-1') between ifNull(".$fromsalesman.",ifNull(salesmen.ledger_number,'-1')) and ifNull(". $tosalesman.",ifNull(salesmen.ledger_number,'-1')) ".
					" group by salesmen.ledger_number, journal.Journal_type, journaldt.currency_code ".
					" order by salesmen.ledger_number, journal.Journal_type ";

	//echo $query;

$t_LedgerNumber='';
$t_SalesmenCode='';

$TotalsBase1 = 0;
$TotalsBase2 = 0;

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

	if ($t_SalesmenCode != $rows[0] || $t_SalesmenCode == ''){
	
		if ($t_SalesmenCode != ''){
			echo"<tr>
					<th align='left' style='border-top:1px solid black;'>Totals</th>
					<td colspan='2' style='border-top:1px solid black;'>&nbsp;</td>
					<th align='right' style='border-top:1px solid black;'>".number_format($TotalsBase1, $DecimalBase1)."</th>
					<th align='right' style='border-top:1px solid black;'>".number_format($TotalsBase2, $DecimalBase2)."</th>
				</tr>";
		}
		
		echo"<tr><td colspan='5'>&nbsp;</td></tr>";
		echo"<tr>
				<th align='left'>Salesman: </th>
				<th align='left' colspan='4'>$rows[0] - $rows[1]</th>
			</tr>";
		echo"<tr><th align='left'>&nbsp;</th><th align='center'>Currency</th>
				 <th align='center'>Total Amount</th><th align='center'>Total $Base1</th><th align='center'>Total $Base2</th>
			 </tr>";
			
		$t_SalesmenCode=$rows[0];
		
		$TotalsBase1 = 0;
		$TotalsBase2 = 0;
	}
	
		
	echo"<tr>
			<td align='left'>$rows[7]</td>
			<td align='center'>$rows[3]</td>
			<td align='right'>".number_format($rows[4], $DecimalBase)."</td>
			<td align='right'>".number_format($rows[5], $DecimalBase1)."</td>
			<td align='right'>".number_format($rows[6], $DecimalBase2)."</td>
		</tr>";
	
	if ($rows[2] == 'SR') {
		$TotalsBase1 -= $rows[5];
		$TotalsBase2 -= $rows[6];
	} else {
		$TotalsBase1 += $rows[5];
		$TotalsBase2 += $rows[6];
	}	
	
}

if ($t_SalesmenCode != ''){
	echo"<tr>
			<th align='left' style='border-top:1px solid black;'>Totals</th>
			<td colspan='2' style='border-top:1px solid black;'>&nbsp;</td>
			<th align='right' style='border-top:1px solid black;'>".number_format($TotalsBase1, $DecimalBase1)."</th>
			<th align='right' style='border-top:1px solid black;'>".number_format($TotalsBase2, $DecimalBase2)."</th>
		</tr>";
}
		
echo"	</table>";
echo"</td></tr></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;
}
if ($printaccess==1)
print <<< HERE

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



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

?>