<?php


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

print <<< HERE
<HTML>
<HEAD>
<TITLE> Client Status Report </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1256" />
<META NAME="Author" CONTENT="">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">

<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;
                 }
table.detail th { font-size: 14px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
                }				 
				 
table.detail td { font-size: 12px; 
                  font-family:Times-New-Roman;
                 }				 
				 
table.detail td.sr  { font-size: 10px; 
					  font-style: italic; 
                      font-family:Times-New-Roman;
                    }
				 
table.totals td.labels { font-size: 14px; 
                         font-weight: bold;
                         font-family:Times-New-Roman;
						 border-width: 1px;
						 border-style: none solid solid solid;
  						 border-color: #666666;
                       }					  
table.totals td.amounts { font-size: 14px; 
                          font-family:Times-New-Roman;
						  border-width: 1px;
						  border-style: none none solid none;
  						  border-color: #666666;
				        }
table.totals td.totalborder {
								font-size: 14px; 
								font-family:Times-New-Roman;
								border-width: 1px;
								border-style: none solid solid none;
								border-color: #666666;
							}						
				 
h4 { border-top-style: double; 
	 border-top-color: black; 
	 border-top-width: 1px;
   }
   
   
</style>

<script>
function displayActiveClients(SalesmanCode, SalesmanName, month, year){
	var str = "Rep_Active_Clients.php?SalesmanCode="+SalesmanCode+"&SalesmanName="+SalesmanName+"&month="+month+"&year="+year;
	window.open(str, 'mywindow','width=850,height=550,toolbar=no,location=no,directories=no,status=yes,menubar=no,scrollbars=yes,copyhistory=no,resizable=yes');
}
function displayNonActive(SalesmanCode, SalesmanName, month, year){
	var str = "Rep_NonActive_Clients.php?SalesmanCode="+SalesmanCode+"&SalesmanName="+SalesmanName+"&month="+month+"&year="+year;
	window.open(str, 'mywindow','width=850,height=550,toolbar=no,location=no,directories=no,status=yes,menubar=no,scrollbars=yes,copyhistory=no,resizable=yes');
}
function displayNewClients(SalesmanCode, SalesmanName, month, year){
		var str = "Rep_New_Clients.php?SalesmanCode="+SalesmanCode+"&SalesmanName="+SalesmanName+"&month="+month+"&year="+year;
	    window.open(str, 'mywindow','width=850,height=550,toolbar=no,location=no,directories=no,status=yes,menubar=no,scrollbars=yes,copyhistory=no,resizable=yes');
}
function displayDeactivatedClients(SalesmanCode, SalesmanName, month, year){
	var str = "Rep_Deactivated_Clients.php?SalesmanCode="+SalesmanCode+"&SalesmanName="+SalesmanName+"&month="+month+"&year="+year;
	window.open(str, 'mywindow','width=850,height=550,toolbar=no,location=no,directories=no,status=yes,menubar=no,scrollbars=yes,copyhistory=no,resizable=yes');
}
function displayExportedClients(SalesmanCode, SalesmanName, month, year){
	var str = "Rep_Exported_Clients.php?SalesmanCode="+SalesmanCode+"&SalesmanName="+SalesmanName+"&month="+month+"&year="+year;
	window.open(str, 'mywindow','width=850,height=550,toolbar=no,location=no,directories=no,status=yes,menubar=no,scrollbars=yes,copyhistory=no,resizable=yes');
}
function displayActivatedClients(SalesmanCode, SalesmanName, month, year){
	var str = "Rep_Activated_Clients.php?SalesmanCode="+SalesmanCode+"&SalesmanName="+SalesmanName+"&month="+month+"&year="+year;
	window.open(str, 'mywindow','width=850,height=550,toolbar=no,location=no,directories=no,status=yes,menubar=no,scrollbars=yes,copyhistory=no,resizable=yes');
}
function displaySemiActiveClients(SalesmanCode, SalesmanName, month, year){
	var str = "Rep_SemiActive_Clients.php?SalesmanCode="+SalesmanCode+"&SalesmanName="+SalesmanName+"&month="+month+"&year="+year;
	window.open(str, 'mywindow','width=850,height=550,toolbar=no,location=no,directories=no,status=yes,menubar=no,scrollbars=yes,copyhistory=no,resizable=yes');
}
function displaySemiActiveClients2(SalesmanCode, SalesmanName, month, year){
	var str = "Rep_SemiActive_Clients2.php?SalesmanCode="+SalesmanCode+"&SalesmanName="+SalesmanName+"&month="+month+"&year="+year;
	window.open(str, 'mywindow','width=850,height=550,toolbar=no,location=no,directories=no,status=yes,menubar=no,scrollbars=yes,copyhistory=no,resizable=yes');
}
function displayPassiveActiveClients(SalesmanCode, SalesmanName, month, year){
	var str = "Rep_PassiveActive_Clients.php?SalesmanCode="+SalesmanCode+"&SalesmanName="+SalesmanName+"&month="+month+"&year="+year;
	window.open(str, 'mywindow','width=850,height=550,toolbar=no,location=no,directories=no,status=yes,menubar=no,scrollbars=yes,copyhistory=no,resizable=yes');
}
</script>

</HEAD>

<BODY>
HERE;

include("config.inc.php");


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


//Read From Login Table
$querylog=" select ifNull(FI_DeliverySalesPrice,0) As FI_DeliverySalesPrice ".
          " 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);
}

//Read From Setup Table							
$querysetup= " select DecimalBase, DecimalBase1, DecimalBase2, base1, base2, grid_color1, grid_color2, Company_memo As CompanyMemo, ".
             " 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);
}

//Read From SalesSetup Table
$querySalesSetup="Select SalesTTC From SalesSetup";
$resultSalesSetup=mysql_query($querySalesSetup,$connection) or die(mysql_error());
while($rowSalesSetup=mysql_fetch_array($resultSalesSetup)){
	extract($rowSalesSetup);
}

//Read From Module Table
$querymodule=" Select ifNull(Module_AdditionalTaxes,0) As Module_AdditionalTaxes, ".
             " ifNull(Module_ShowLayoutProj,0) As Module_ShowLayoutProj, ifNull(Module_ShowLayoutCost,0) As Module_ShowLayoutCost From Module";
$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
while($rowmodule=mysql_fetch_array($resultmodule)){
	extract($rowmodule);
}
	
$now=current_server_time($connection);
$dt=split(" ",$now[0]);
$printdate=split("-",$dt[0]);

$TotalQty = 0;



	
	//Read From Project Table
	$queryProject="Select Project_Memo As ProjectMemo From Project Where Project_Code = '".$ProjectCode."'";
	$resultProject=mysql_query($queryProject,$connection) or die(mysql_error());
	while($rowProject=mysql_fetch_array($resultProject)){
		extract($rowProject);
	}
	
	//Get Client Balance
	$querybalance = "SELECT IFNULL(SUM(if(journaldt_dbcd = 'D', journaldt_amount1, (-1)*journaldt_amount1)), 0) As Balance FROM journaldt where ledger_number = '".$ClientCode."'";
	//echo "<br><br>".$querybalance;
	$resultbalance=mysql_query($querybalance,$connection) or die(mysql_error());
	$rowbalance=mysql_fetch_array($resultbalance);
	$ClientBalance = $rowbalance[0];
	
	
	echo "<table border=0 width=100% cellspacing=0 cellpadding=0><tr><td align='left'>";
	echo "<table border=0 width='735' cellspacing=2 cellpadding=0 class='master' style='table-layout:fixed;";
	echo "' >
		  <col width=115>
		  <col width=320>
		  <col width=95>
		  <col width=170>";
	echo "<tr><td height='60'><img src='LogoFile.php' width='115' height='50' /></td><td align='center'><b><font size=5>";
	echo "Client Status";

	if($_GET['plang'] != 'AR'){
	echo "</font></td><td align='right' colspan=2 ><font size='2'><b>Printed&nbsp;Date&nbsp;&amp;&nbsp;Time:</b>&nbsp;&nbsp;";
	}
	else
	{
	   echo "</font></td><td align='left' style='direction: ltr' colspan=2 ><font size='2'><b>تاريخ ووقت الطبع:</b>&nbsp;&nbsp;";
	}
	echo $printdate[2]."/".$printdate[1]."/".$printdate[0];
	echo "<br>".$dt[1]." ".$dt[2];
	echo "</font></td></tr></table>";
	
	echo "<table border=0 width='735' cellspacing=2 cellpadding=0 class='master' style='table-layout:fixed;";
	echo "' >
		  <col width=110>
		  <col width=200>
		  <col width=140>
		  <col width=140>";
    
    //echo "<tr height='10'><td colspan=4><hr></td></tr>";
	echo "<tr height='5'><td colspan=4><h4></td></tr>";
	echo "<tr>";
	$month = $_GET['month'];
	if($month == 1)
	{
	   $mon = "January";
	}
	else
	  if($month == 2)
	  {
	     $mon = "February";
	  }
	  else
	     if($month == 3)
		 {
		    $mon = "March";
		 }
		 else
		    if($month == 4)
			{
			   $mon = "April";
			}
			else
			   if($month == 5)
			   {
			      $mon = "May";
			   }
			   else
			      if($month == 6)
				  {
				     $mon = "June";
				  }
				  else
				    if($month == 7)
					{
					   $mon = "July";
					}
					else
					   if($month == 8)
					   {
					       $mon = "August";
					   }
					   else
					      if($month == 9)
						  {
						     $mon = "September";
						  }
						  else
						    if($month == 10)
							{
							  $mon = "October";
							}
							else
							  if($month == 11)
							  {
							     $mon = "November";
							  }
							  else
							    if($month == 12)
								{
								   $mon = "December";
								}
	echo "  <th align='left' bgcolor='#D8D8D8'>Month</th>";
	echo "  <td align='left'>$mon</td>";
	//if($_GET)
	$year = $_GET['year'];
	echo "  <th align='left' bgcolor='#D8D8D8'>Year</th>";
	echo "  <td align='left'>$year</td>";
	echo "</tr>";
   if($_GET['choose_option'] != "all"){
	if($_GET['SalesmanCode'] != ''){
	$SalesmanCode = $_GET['SalesmanCode'];
	$salesmanName = $_GET['SalesmanName'];
	echo "<tr>";
	echo "  <th align='left' bgcolor='#D8D8D8'>Salesman Code</th>";
	echo "  <td align='left' >";
	echo $SalesmanCode;
	echo "</td>";
	echo "  <th align='left' bgcolor='#D8D8D8'>Salesman Name</th>";
	echo "  <td align='left' >$SalesmanName</td>";
	echo "</tr>";
    }
	}

	
	//echo "<tr height='10'><td colspan=4><hr></td></tr>";
	echo "<tr height='5'><td colspan=4><h4></td></tr>";
	echo "</table></td></tr>";

	
	
			   

	//echo $querydt;
	echo "<tr><td align='left'>";
	if($_GET['choose_option'] == "all"){
	echo "<table border=0 max-width='535' cellspacing=2 class='detail' style='table-layout:fixed;";
	echo "' >";
		 
	
	
		  
    $month = $_GET['month'];
	$year = $_GET['year'];
	$query_module = "select Module_Delivery from ".$config['dbName_log'].".module";
	//echo $query_module; exit;
	$result_module = mysql_query($query_module, $connection);
	$row_module = mysql_fetch_array($result_module);
	extract($row_module);
	if($Module_Delivery == 1)
	{
	    $querydt = "select clients.Ledger_number as ClientNumber, clients.Ledger_name as ClientName, sales.Sale_Date as Date
                   from clients inner join sales on clients.Ledger_number = sales.Client_code
                   where date_format(sales.Sale_Date,'%m')= ".$month." and date_format(sales.Sale_Date,'%Y')='".$year."' and sales.journal_type = 'SI' and clients.client_deactivate  <> 1
                   group by sales.Client_code";
		//echo $querydt; exit;
	}
	else
	{
	   $querydt = "select clients.Ledger_number as ClientNumber, clients.Ledger_name as ClientName, deliveries.Delivery_Date as Date
                   from clients inner join deliveries on clients.Ledger_number = deliveries.Client_code
                   where date_format(deliveries.Delivery_Date,'%m')= ".$month." and date_format(deliveries.Delivery_Date,'%Y')='".$year."' and deliveries.Journal_Type = 'DI'  and clients.client_deactivate  <> 1
                   group by deliveries.Client_code";
		//echo $querydt; exit;
	}
	
   //echo $querydt; exit;
	$result=mysql_query($querydt,$connection) or die(mysql_error());
	//echo $result;
	//ECHO $_SESSION['dbName'];
	$num = mysql_num_rows($result);
	//echo $num;
    //echo $_SESSION['dbName'];
	
	


	
	echo "</table>";
	
	echo "<table width='435' style='border-collapse: collapse; '>
	<col width=50%>
		  <col width=50%>
	<tr style='cursor: pointer;' onclick=\"displayActiveClients('".$_GET['SalesmanCode']."', '".$_GET['SalesmanName']."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>Clients sold within the month</td>";
	if($Module_Delivery == 1)
	{
	   $query_active_clients = "select count(NumberOfAvtive) as NumberOfActive from (
            select count(sales.Client_code) as NumberOfAvtive from sales left join clients on clients.ledger_number = sales.client_code
            where (sales.Sale_Date between '".$year."-".$month."-01' and '".$year."-".$month."-31 23:59:59') and sales.journal_type = 'SI' and clients.Client_deactivate <> 1
            group by sales.Client_code) as sales_active";
	}
	else
	{
	   $query_active_clients = "select count(NumberOfAvtive) as NumberOfActive from (
            select count(deliveries.Client_code) as NumberOfAvtive from deliveries left join clients on clients.ledger_number = deliveries.client_code
            where (deliveries.Delivery_Date between '".$year."-".$month."-01' and '".$year."-".$month."-31 23:59:59')  and deliveries.Journal_Type = 'DI' and clients.Client_deactivate <> 1
            group by deliveries.Client_code) as deliveries_active";
	}
	//echo $query_active_clients; exit;
	$result_active_clients = mysql_query($query_active_clients, $connection);
	$row_active_clients = mysql_fetch_array($result_active_clients);
	extract ($row_active_clients);
	echo "<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 13px; font-weight: bold'>$NumberOfActive</td>";
	echo "</tr>
	<tr style='cursor: pointer' onclick=\"displaySemiActiveClients('".$_GET['SalesmanCode']."', '".$_GET['SalesmanName']."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>Clients sold between 30 and 60 days</td>";
	if($Module_Delivery == 1)
	{
	   $query_active_clients = "select count(client_Code) as NumberOfActive from
( 
select sales.Client_code as client_Code
from sales left join clients on clients.ledger_number = sales.clients_code
where (sales.Sale_Date between ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 1 MONTH), INTERVAL 1 DAY) and concat(LAST_DAY( DATE_SUB('$year-$month-01',INTERVAL 1 MONTH)), ' 23:59:59'))
and sales.Journal_Type = 'SI'
and clients.Client_deactivate <> 1
and sales.client_code not in
(select client_code from(
select client_code
from sales
where (sale_date between ADDDATE('$year-$month-01', INTERVAL 1 DAY) and concat(LAST_DAY('$year-$month-01'), ' 23:59:59')) 
and sales.Journal_Type = 'SI' group by sales.Client_code 

) as d) group by sales.Client_code) as client_old";
	}
	else
	{
	   $query_active_clients = "select count(client_Code) as NumberOfActive from
( 
select deliveries.Client_code as client_Code
from deliveries left join clients on clients.ledger_number = deliveries.client_code
where (deliveries.Delivery_Date between ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 1 MONTH), INTERVAL 1 DAY) and concat(LAST_DAY( DATE_SUB('$year-$month-01',INTERVAL 1 MONTH)), ' 23:59:59'))
and deliveries.Journal_Type = 'DI'
and clients.Client_deactivate <> 1
and deliveries.client_code not in
(select client_code from(
select client_code
from deliveries
where (delivery_date between ADDDATE('$year-$month-01', INTERVAL 1 DAY) and concat(LAST_DAY('$year-$month-01'), ' 23:59:59')) 
and deliveries.Journal_Type = 'DI' group by deliveries.Client_code 

) as d) group by deliveries.Client_code) as client_old";
	}
	//echo $query_active_clients; exit;
	$result_active_clients = mysql_query($query_active_clients, $connection);
	$row_active_clients = mysql_fetch_array($result_active_clients);
	extract($row_active_clients);
	echo "<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 13px; font-weight: bold'>$NumberOfActive</td>";
	echo "</tr>
	<tr style='cursor: pointer' onclick=\"displaySemiActiveClients2('".$_GET['SalesmanCode']."', '".$_GET['SalesmanName']."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>Clients sold between 60 and 90 days</td>";
	if($Module_Delivery == 1)
	{
	   $query_active_clients = "select count(client_Code) as NumberOfActive from
( 
select sales.Client_code as client_Code
from sales left join clients on sales.client_code = clients.ledger_number
where (sales.Sale_Date between ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 2 MONTH), INTERVAL 1 DAY) and concat(LAST_DAY( DATE_SUB('$year-$month-01',INTERVAL 2 MONTH)), ' 23:59:59'))
and sales.Journal_Type = 'SI'
and clients.Client_deactivate <> 1
and sales.client_code not in
(select client_code from(
select client_code
from sales
where (sale_date between ADDDATE('$year-$month-01', INTERVAL 1 DAY) and concat(LAST_DAY('$year-$month-01'), ' 23:59:59')) 
and sales.Journal_Type = 'SI' group by sales.Client_code 

) as d) group by sales.Client_code) as client_old";
	}
	else
	{
	   $query_active_clients = "select count(client_Code) as NumberOfActive from
( 
select deliveries.Client_code as client_Code
from deliveries left join clients on clients.ledger_number = deliveries.client_code
where (deliveries.Delivery_Date between ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 2 MONTH), INTERVAL 1 DAY) and concat(LAST_DAY( DATE_SUB('$year-$month-01',INTERVAL 2 MONTH)), ' 23:59:59'))
and deliveries.Journal_Type = 'DI'
and clients.Client_deactivate <> 1
and deliveries.client_code not in
(select client_code from(
select client_code
from deliveries
where (delivery_date between ADDDATE('$year-$month-01', INTERVAL 1 DAY) and concat(LAST_DAY('$year-$month-01'), ' 23:59:59')) 
and deliveries.Journal_Type = 'DI' group by deliveries.Client_code 

) as d) group by deliveries.Client_code) as client_old";
	}
	//echo $query_active_clients; exit;
	$result_active_clients = mysql_query($query_active_clients, $connection);
	$row_active_clients = mysql_fetch_array($result_active_clients);
	extract($row_active_clients);
	echo "<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 13px; font-weight: bold'>$NumberOfActive</td>";
	echo "</tr>
	<tr style='cursor: pointer' onclick=\"displayPassiveActiveClients('".$_GET['SalesmanCode']."', '".$_GET['SalesmanName']."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>Clients sold above 90 days</td>";
	if($Module_Delivery == 1)
	{
	   $query_active_clients = "select count(client_Code) as NumberOfActive from (
select sales.Client_code as client_Code
from sales left join clients on clients.ledger_number = sales.client_code
where (sales.sale_date < ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 2 MONTH), INTERVAL 1 DAY))
and sales.Journal_Type = 'SI'
and clients.Client_deactivate <> 1
and sales.client_code not in (
select client_code from(
select client_code
from sales 
where (sale_date between ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 2 MONTH), INTERVAL 1 DAY) and concat(LAST_DAY('$year-$month-01'), ' 23:59:59')) and sales.Journal_Type = 'SI' group by sales.Client_code)  as c)
group by sales.Client_code) as client_old";
	}
	else
	{
	   $query_active_clients = "select count(client_Code) as NumberOfActive from (
select deliveries.Client_code as client_Code
from deliveries left join clients on clients.ledger_number = deliveries.client_code
where (deliveries.Delivery_Date < DATE_SUB('$year-$month-01',INTERVAL 2 MONTH))
and deliveries.Journal_Type = 'DI'
and clients.Client_deactivate <> 1
and deliveries.client_code not in (
select client_code from(
select client_code
from deliveries 
where (delivery_date between ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 2 MONTH), INTERVAL 1 DAY) and concat(LAST_DAY('$year-$month-01'), ' 23:59:59')) and deliveries.Journal_Type = 'DI' group by deliveries.Client_code)  as c)
group by deliveries.Client_code) as client_old";
	}
	//echo $query_active_clients; exit;
	$result_active_clients = mysql_query($query_active_clients, $connection);
	$row_active_clients = mysql_fetch_array($result_active_clients);
	extract ($row_active_clients);
	echo "<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 13px; font-weight: bold'>$NumberOfActive</td>";
	echo "</tr>";
	/*echo "
	<tr style='cursor: pointer' onclick=\"displayNonActive('".$_GET['SalesmanCode']."', '".$_GET['SalesmanName']."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>Non-Active Clients [Sales]</td>
	<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 13px; font-weight: bold'>";
	if($Module_Delivery == 1)
	{
	   $query_nonactive_clients = "SELECT COUNT(Client_code) as NumberOfNonActive FROM (
SELECT Client_code FROM (
            select sales.Client_code as Client_code from sales left join clients on clients.ledger_number = sales.client_code
            where sales.Sale_Date between DATE_SUB('".$year."-".$month."-01',INTERVAL 1 MONTH) and DATE_SUB('".$year."-".$month."-31 23:59:59',INTERVAL 1 MONTH) and clients.clients_deactivate <> 1
            and sales.journal_type = 'DI'
            group by sales.Client_code) AS CLIENTS
WHERE CLIENT_CODE NOT IN (
 select sales.Client_code as Client_code from sales left join clients on clients.ledger_number = sales.client_code
            where sales.Sale_Date between '".$year."-".$month."-01' and '".$year."-".$month."-31 23:59:59'
            and sales.journal_type = 'DI' and clients.clients_deactivate <> 1
            group by sales.Client_code)) AS CLIENTS";
	}
	else
	{
	$query_nonactive_clients = "SELECT COUNT(Client_code) as NumberOfNonActive FROM (
SELECT Client_code FROM (
            select deliveries.Client_code as Client_code from deliveries left join deliveries on clients.ledger_number = deliveries.client_code
            where deliveries.delivery_date between DATE_SUB('".$year."-".$month."-01',INTERVAL 1 MONTH) and DATE_SUB('".$year."-".$month."-31 23:59:59',INTERVAL 1 MONTH)
            and deliveries.Journal_Type = 'DI'  and clients.client_deactivate <> 1
            group by deliveries.Client_code) AS CLIENTS
WHERE CLIENT_CODE NOT IN (
 select deliveries.Client_code as Client_code from deliveries left join deliveries on clients.ledger_number = deliveries.client_code
            where deliveries.delivery_date between '".$year."-".$month."-01' and '".$year."-".$month."-31 23:59:59'
            and deliveries.Journal_Type = 'DI' and clients.client_deactivate <> 1
            group by deliveries.Client_code)) AS CLIENTS
";
}
	//echo $query_nonactive_clients; exit;
	$result_nonactive_clients = mysql_query($query_nonactive_clients, $connection);
	//echo $result_nonactive_clients; exit;
	$row_nonactive_clients = mysql_fetch_array($result_nonactive_clients);
	extract($row_nonactive_clients);
	$num = mysql_num_rows($result_nonactive_clients);
	echo $NumberOfNonActive;
	echo "</td>
	</tr>*/
	echo "
	<tr style='cursor: pointer' onclick=\"displayNewClients('".$_GET['SalesmanCode']."', '".$_GET['SalesmanName']."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>New Clients [Creation Date]</td>
	<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 12px; font-size: 13px; font-weight: bold'>";
	$query_new_clients = "select count(clients.Ledger_number) as NumberOfNew 
	                      from clients 
						  where date_format(Client_CreationDate, '%Y-%m') = '".$year."-".$month."' and clients.client_deactivate <> 1";
	//echo $query_new_clients;
	$result_new_clients = mysql_query($query_new_clients, $connection);
	$row_new_clients = mysql_fetch_array($result_new_clients);
	extract($row_new_clients);
	echo $NumberOfNew;
	echo "</td>
	</tr>
	<tr style='cursor: pointer' onclick=\"displayDeactivatedClients('".$_GET['SalesmanCode']."', '".$_GET['SalesmanName']."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>Deactivated Clients [Field]</td>
	<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 13px; font-weight: bold'>";
	$query_deactivated_clients = "select count(clients.Ledger_number) as NumberofDeactivate 
	                              from clients 
								  where Client_deactivate = 1";
	$result_deactivated_clients = mysql_query($query_deactivated_clients, $connection);
	$row_deactivated_clients = mysql_fetch_array($result_deactivated_clients);
	extract($row_deactivated_clients);
	echo $NumberofDeactivate;
	echo "</td>
	</tr>
	
	<tr style='cursor: pointer' onclick=\"displayActivatedClients('".$_GET['SalesmanCode']."', '".$_GET['SalesmanName']."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>Activated Clients [Field]</td>
	<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 13px; font-weight: bold'>";
	$query_activated_clients = "select count(clients.Ledger_number) as NumberofActivate 
	                              from clients 
								  where ifNull(Client_deactivate, 0) = 0";
	$result_activated_clients = mysql_query($query_activated_clients, $connection);
	$row_activated_clients = mysql_fetch_array($result_activated_clients);
	extract($row_activated_clients);
	echo $NumberofActivate;
	echo "</td>
	</tr>
	
	</table>
	";
	
	}
	else 
	  if($_GET['choose_option'] == "salesman"){
	     if($_GET['SalesmanCode']!='')
		 {
		     echo "<table border=0 max-width='535' cellspacing=2 class='detail' style='table-layout:fixed;";
	echo "' >";
		 
	
		  
    $month = $_GET['month'];
	$year = $_GET['year'];
	$query_module = "select Module_Delivery from ".$config['dbName_log'].".module";
	//echo $query_module; exit;
	$result_module = mysql_query($query_module, $connection);
	$row_module = mysql_fetch_array($result_module);
	extract($row_module);
	
	
   //echo $querydt; exit;
	
	//echo $num;
    //echo $_SESSION['dbName'];
	
	
	


	
	echo "</table>";
	
	echo "<table width='435' style='border-collapse: collapse; '>
	<col width=50%>
		  <col width=50%>
	<tr style='cursor: pointer' onclick=\"displayActiveClients('".$_GET['SalesmanCode']."', '".$_GET['SalesmanName']."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>Clients sold within the month</td>";
	if($Module_Delivery == 1)
	{
	   $query_active_clients = "select count(NumberOfAvtive) as NumberOfActive from (
            select count(sales.Client_code) as NumberOfAvtive from sales left join clients on clients.ledger_number = sales.client_code
            where (sales.Sale_Date between '".$year."-".$month."-01' and '".$year."-".$month."-31 23:59:59') and sales.journal_type = 'SI' and clients.Salesmen_Code = '".$_GET['SalesmanCode']."' and clients.client_deactivate <> 1
            group by sales.Client_code) as sales_active";
	}
	else
	{
	   $query_active_clients = "select count(NumberOfAvtive) as NumberOfActive from (
            select count(deliveries.Client_code) as NumberOfAvtive from deliveries left join clients on clients.ledger_number = deliveries.client_code
            where (deliveries.Delivery_Date between '".$year."-".$month."-01' and '".$year."-".$month."-31 23:59:59')  and deliveries.Journal_Type = 'DI' and clients.Salesmen_Code = '".$_GET['SalesmanCode']."' and clients.client_deactivate <> 1
            group by deliveries.Client_code) as deliveries_active";
	}
	//xxx echo $query_active_clients; exit;
	$result_active_clients = mysql_query($query_active_clients, $connection);
	$row_active_clients = mysql_fetch_array($result_active_clients);
	extract ($row_active_clients);
	echo "<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 13px; font-weight: bold'>$NumberOfActive</td>";
	echo "</tr>
	<tr style='cursor: pointer' onclick=\"displaySemiActiveClients('".$_GET['SalesmanCode']."', '".$_GET['SalesmanName']."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>Clients sold between 30 and 60 days</td>";
	if($Module_Delivery == 1)
	{
	   $query_active_clients = "select count(client_Code) as NumberOfActive from
( 
select sales.Client_code as client_Code
from sales left join clients on sales.client_code = clients.ledger_number
where (sales.Sale_Date between ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 1 MONTH), INTERVAL 1 DAY) and concat(LAST_DAY( DATE_SUB('$year-$month-01',INTERVAL 1 MONTH)), ' 23:59:59'))
and sales.Journal_Type = 'SI'
and clients.client_deactivate <> 1
and clients.salesmen_code = '".$_GET['SalesmanCode']."'
and sales.client_code not in
(select client_code from(
select client_code
from sales
where (sale_date between ADDDATE('$year-$month-01', INTERVAL 1 DAY) and concat(LAST_DAY('$year-$month-01'), ' 23:59:59')) 
and sales.Journal_Type = 'SI' group by sales.Client_code 

) as d) group by sales.Client_code) as client_old";
	}
	else
	{
	   $query_active_clients = "select count(client_Code) as NumberOfActive from
( 
select deliveries.Client_code as client_Code
from deliveries left join clients on deliveries.client_code = clients.ledger_number
where (deliveries.Delivery_Date between ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 1 MONTH), INTERVAL 1 DAY) and concat(LAST_DAY( DATE_SUB('$year-$month-01',INTERVAL 1 MONTH)), ' 23:59:59'))
and deliveries.Journal_Type = 'DI'
and clients.salesmen_code = '".$_GET['SalesmanCode']."'
and clients.client_deactivate <> 1
and deliveries.client_code not in
(select client_code from(
select client_code
from deliveries
where (delivery_date between ADDDATE('$year-$month-01', INTERVAL 1 DAY) and concat(LAST_DAY('$year-$month-01'), ' 23:59:59')) 
and deliveries.Journal_Type = 'DI' group by deliveries.Client_code 

) as d) group by deliveries.Client_code) as client_old";
	}
	//echo $query_active_clients; exit;
	$result_active_clients = mysql_query($query_active_clients, $connection);
	$row_active_clients = mysql_fetch_array($result_active_clients);
	extract ($row_active_clients);
	echo "<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 13px; font-weight: bold'>$NumberOfActive</td>";
	echo "</tr>
	<tr style='cursor: pointer' onclick=\"displaySemiActiveClients2('".$_GET['SalesmanCode']."', '".$_GET['SalesmanName']."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>Clients sold between 60 and 90 days</td>";
	if($Module_Delivery == 1)
	{
	   $query_active_clients = "select count(client_Code) as NumberOfActive from
( 
select sales.Client_code as client_Code
from sales left join clients on sales.client_code = clients.ledger_number
where (sales.Sale_Date between ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 2 MONTH)', INTERVAL 1 DAY) and concat(LAST_DAY( DATE_SUB('$year-$month-01',INTERVAL 2 MONTH)), ' 23:59:59'))
and sales.Journal_Type = 'SI'
and clients.salesmen_code = '".$_GET['SalesmanCode']."'
and clients.client_deactivate <> 1
and sales.client_code not in
(select client_code from(
select client_code
from sales
where (sale_date between ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 1 MONTH), INTERVAL 1 DAY) and concat(LAST_DAY('$year-$month-01'), ' 23:59:59')) 
and sales.Journal_Type = 'SI' group by sales.Client_code 

) as d) group by sales.Client_code) as client_old";
	}
	else
	{
	   $query_active_clients = "select count(client_Code) as NumberOfActive from
( 
select deliveries.Client_code as client_Code
from deliveries left join clients on deliveries.client_code = clients.ledger_number
where (deliveries.Delivery_Date between ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 2 MONTH), INTERVAL 1 DAY) and concat(LAST_DAY( DATE_SUB('$year-$month-01',INTERVAL 2 MONTH)), ' 23:59:59'))
and deliveries.Journal_Type = 'DI'
and clients.client_deactivate <> 1
and clients.salesmen_code = '".$_GET['SalesmanCode']."'
and deliveries.client_code not in
(select client_code from(
select client_code
from deliveries
where (delivery_date between ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 1 MONTH), INTERVAL 1 DAY) and concat(LAST_DAY('$year-$month-01'), ' 23:59:59')) 
and deliveries.Journal_Type = 'DI' group by deliveries.Client_code 

) as d) group by deliveries.Client_code) as client_old";
	}
	//echo $query_active_clients; exit;
	$result_active_clients = mysql_query($query_active_clients, $connection);
	$row_active_clients = mysql_fetch_array($result_active_clients);
	extract ($row_active_clients);
	echo "<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 13px; font-weight: bold'>$NumberOfActive</td>";
	echo "</tr>
	<tr style='cursor: pointer' onclick=\"displayPassiveActiveClients('".$_GET['SalesmanCode']."', '".$_GET['SalesmanName']."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>Clients sold above 90 days</td>";
	if($Module_Delivery == 1)
	{
	   $query_active_clients = "select count(client_Code) as NumberOfActive from (
select sales.Client_code as client_Code
from sales left join clients on sales.client_code = clients.ledger_number
where (sales.sale_date < ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 2 MONTH)), INTERVAL 1 DAY)
and sales.Journal_Type = 'SI'
and clients.salesmen_code = '".$_GET['SalesmanCode']."'
and clients.client_deactivate <> 1
and sales.client_code not in (
select client_code from(
select client_code
from sales 
where (sale_date between ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 2 MONTH), INTERVAL 1 DAY) and concat(LAST_DAY('$year-$month-01'), ' 23:59:59')) and sales.Journal_Type = 'SI' and sales.salesman_code = '".$_GET['SalesmanCode']."' group by sales.Client_code)  as c)
group by sales.Client_code) as client_old";
	}
	else
	{
	   $query_active_clients = "select count(client_Code) as NumberOfActive from (
select deliveries.Client_code as client_Code
from deliveries left join clients on deliveries.client_code = clients.ledger_number
where (deliveries.Delivery_Date < DATE_SUB('$year-$month-01',INTERVAL 2 MONTH))
and deliveries.Journal_Type = 'DI'
and clients.salesmen_code = '".$_GET['SalesmanCode']."'
and clients.client_deactivate <> 1
and deliveries.client_code not in (
select client_code from(
select client_code
from deliveries 
where (delivery_date between ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 2 MONTH), INTERVAL 1 DAY) and concat(LAST_DAY('$year-$month-01'), ' 23:59:59')) and deliveries.Journal_Type = 'DI' and deliveries.salesman_code = '".$_GET['SalesmanCode']."' group by deliveries.Client_code)  as c)
group by deliveries.Client_code) as client_old";
	}
	//echo $query_active_clients; exit;
	$result_active_clients = mysql_query($query_active_clients, $connection);
	$row_active_clients = mysql_fetch_array($result_active_clients);
	extract ($row_active_clients);
	echo "<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 13px; font-weight: bold'>$NumberOfActive</td>";
	echo "</tr>";
	/*echo "
	<tr style='cursor: pointer' onclick=\"displayNonActive('".$_GET['SalesmanCode']."', '".$_GET['SalesmanName']."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>Non-Active Clients [Sales]</td>
	<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 13px; font-weight: bold'>";
	if($Module_Delivery == 1)
	{
	   $query_nonactive_clients = "SELECT COUNT(Client_code) as NumberOfNonActive FROM (
SELECT Client_code FROM (
            select sales.Client_code as Client_code from sales left join clients on clients.ledger_number = sales.client_code
            where sales.Sale_Date between DATE_SUB('".$year."-".$month."-01',INTERVAL 1 MONTH) and DATE_SUB('".$year."-".$month."-31 23:59:59',INTERVAL 1 MONTH)
            and sales.journal_type = 'DI' and clients.Salesmen_Code = '".$SalesmanCode."'
            group by sales.Client_code) AS CLIENTS
WHERE CLIENT_CODE NOT IN (
 select sales.Client_code as Client_code from sales left join clients on clients.ledger_number = sales.client_code
            where sales.Sale_Date between '".$year."-".$month."-01' and '".$year."-".$month."-31 23:59:59'
            and sales.journal_type = 'DI' and clients.Salesmen_Code = '".$_GET['SalesmanCode']."'
            group by sales.Client_code)) AS CLIENTS";
	}
	else
	{
	$query_nonactive_clients = "SELECT COUNT(Client_code) as NumberOfNonActive FROM (
SELECT Client_code FROM (
            select deliveries.Client_code as Client_code from deliveries left join clients on clients.ledger_number = deliveries.client_code
            where deliveries.delivery_date between DATE_SUB('".$year."-".$month."-01',INTERVAL 1 MONTH) and DATE_SUB('".$year."-".$month."-31 23:59:59',INTERVAL 1 MONTH)
            and deliveries.Journal_Type = 'DI' and clients.Salesmen_Code = '".$SalesmanCode."'
            group by deliveries.Client_code) AS CLIENTS
WHERE CLIENT_CODE NOT IN (
 select deliveries.Client_code as Client_code from deliveries left join clients on clients.ledger_number = deliveries.client_code
            where deliveries.delivery_date between '".$year."-".$month."-01' and '".$year."-".$month."-31 23:59:59'
            and deliveries.Journal_Type = 'DI' and clients.Salesmen_Code = '".$_GET['SalesmanCode']."'
            group by deliveries.Client_code)) AS CLIENTS
";
}
	//echo $query_nonactive_clients; exit;
	$result_nonactive_clients = mysql_query($query_nonactive_clients, $connection);
	//echo $result_nonactive_clients; exit;
	$row_nonactive_clients = mysql_fetch_array($result_nonactive_clients);
	extract($row_nonactive_clients);
	$num = mysql_num_rows($result_nonactive_clients);
	echo $NumberOfNonActive;
	echo "</td>
	</tr>";*/
	echo "
	<tr style='cursor: pointer' onclick=\"displayNewClients('".$_GET['SalesmanCode']."', '".$_GET['SalesmanName']."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>New Clients [Creation Date]</td>
	<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 12px; font-size: 13px; font-weight: bold'>";
	$query_new_clients = "select count(clients.Ledger_number) as NumberOfNew 
	                      from clients 
						  where date_format(Client_CreationDate, '%Y-%m') = '".$year."-".$month."' and Salesmen_Code = '".$_GET['SalesmanCode']."' and Client_deactivate <> 1";
	//echo $query_new_clients;
	$result_new_clients = mysql_query($query_new_clients, $connection);
	$row_new_clients = mysql_fetch_array($result_new_clients);
	extract($row_new_clients);
	echo $NumberOfNew;
	echo "</td>
	</tr>
	<tr style='cursor: pointer' onclick=\"displayDeactivatedClients('".$_GET['SalesmanCode']."', '".$_GET['SalesmanName']."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>Deactivated Clients [Field]</td>
	<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 13px; font-weight: bold'>";
	$query_deactivated_clients = "select count(clients.Ledger_number) as NumberofDeactivate 
	                              from clients 
								  where Client_deactivate = 1 and Salesmen_Code = '".$_GET['SalesmanCode']."'";
	$result_deactivated_clients = mysql_query($query_deactivated_clients, $connection);
	$row_deactivated_clients = mysql_fetch_array($result_deactivated_clients);
	extract($row_deactivated_clients);
	echo $NumberofDeactivate;
	echo "</td>
	</tr>
	
	<tr style='cursor: pointer' onclick=\"displayActivatedClients('".$_GET['SalesmanCode']."', '".$_GET['SalesmanName']."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>Activated Clients [Field]</td>
	<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 13px; font-weight: bold'>";
	$query_activated_clients = "select count(clients.Ledger_number) as NumberofActivate 
	                              from clients 
								  where Client_deactivate = 0 and Salesmen_Code = '".$_GET['SalesmanCode']."'";
	$result_activated_clients = mysql_query($query_activated_clients, $connection);
	$row_activated_clients = mysql_fetch_array($result_activated_clients);
	extract($row_activated_clients);
	echo $NumberofActivate;
	echo "</td>
	</tr>
	
	</table>
	";
		 }
		 else
		 {
		 echo "<table width=600>";
		 echo"<col width=25%>
		  <col width=15%>
		  <col width=20%>
		  <col width=40%>";

		 $query_salesmen_delivery = "select salesmen.Ledger_number as SalesmanCode, salesmen.Ledger_name as SalesmanName 
			                            from salesmen";
	
		
		 //echo $query_salesmen_delivery; exit;
		 $result_salesmen_delivery = mysql_query($query_salesmen_delivery, $connection);
		 while($row_salesmen_delivery = mysql_fetch_array($result_salesmen_delivery)){
		 extract($row_salesmen_delivery);
		     echo "<tr>";
	echo "  <th align='left' bgcolor='#D8D8D8'>Salesman Code</th>";
	echo "  <td align='left'>";
	echo $SalesmanCode;
	echo "</td>";
	echo "  <th align='left' bgcolor='#D8D8D8'>Salesman Name</th>";
	echo "  <td align='left' >$SalesmanName</td>";
	echo "</tr>";
	echo "<tr><td colspan='4'>
	<div style='height: 20px'></div>";
	echo "<table border=0 max-width='535' cellspacing=2 class='detail' style='table-layout:fixed;";
	echo "' >";
		  	
	
	//echo $num;
    //echo $_SESSION['dbName'];
	
	echo "</table>";
	echo "<table width='435' style='border-collapse: collapse; '>
	<col width=50%>
		  <col width=50%>
	<tr style='cursor: pointer' onclick=\"displayActiveClients('".$SalesmanCode."', '".$SalesmanName."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>Clients sold within the month</td>";
	if($Module_Delivery == 1)
	{
	   $query_active_clients = "select count(NumberOfAvtive) as NumberOfActive from (
            select count(sales.Client_code) as NumberOfAvtive from sales left join clients on clients.ledger_number = sales.client_code
            where (sales.Sale_Date between '".$year."-".$month."-01' and '".$year."-".$month."-31 23:59:59') and sales.journal_type = 'SI' and clients.Salesmen_Code = '".$SalesmanCode."' and clients.client_deactivate <> 1
            group by sales.Client_code) as sales_active";
	}
	else
	{
	   $query_active_clients = "select count(NumberOfAvtive) as NumberOfActive from (
            select count(deliveries.Client_code) as NumberOfAvtive from deliveries left join clients on clients.ledger_number = deliveries.client_code
            where (deliveries.Delivery_Date between '".$year."-".$month."-01' and '".$year."-".$month."-31 23:59:59')  and deliveries.Journal_Type = 'DI' and clients.Salesmen_Code = '".$SalesmanCode."' and clients.client_deactivate <> 1
            group by deliveries.Client_code) as deliveries_active";
	}
	//echo $query_active_clients; exit;
	$result_active_clients = mysql_query($query_active_clients, $connection);
	$row_active_clients = mysql_fetch_array($result_active_clients);
	extract ($row_active_clients);
	echo "<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 13px; font-weight: bold'>$NumberOfActive</td>";
	echo "</tr>
	<tr style='cursor: pointer' onclick=\"displaySemiActiveClients('".$SalesmanCode."', '".$SalesmanName."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>Clients sold between 30 and 60 days</td>";
	if($Module_Delivery == 1)
	{
	   $query_active_clients = "select count(client_Code) as NumberOfActive from
( 
select sales.Client_code as client_Code
from sales left join clients on clients.ledger_number = sales.client_code
where (sales.Sale_Date between ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 1 MONTH), INTERVAL 1 DAY) and concat(LAST_DAY( DATE_SUB('$year-$month-01',INTERVAL 1 MONTH)), ' 23:59:59'))
and sales.Journal_Type = 'SI'
and clients.Salesmen_Code = '".$SalesmanCode."'
and clients.client_deactivate <> 1
and sales.client_code not in
(select client_code from(
select client_code
from sales
where (sale_date between ADDDATE('$year-$month-01', INTERVAL 1 DAY) and concat(LAST_DAY('$year-$month-01'), ' 23:59:59')) 
and sales.Journal_Type = 'SI' group by sales.Client_code 

) as d) group by sales.Client_code) as client_old";
	}
	else
	{
	   $query_active_clients = "select count(client_Code) as NumberOfActive from
( 
select deliveries.Client_code as client_Code
from deliveries left join clients on clients.ledger_number = deliveries.client_code
where (deliveries.Delivery_Date between ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 1 MONTH), INTERVAL 1 DAY) and concat(LAST_DAY( DATE_SUB('$year-$month-01',INTERVAL 1 MONTH)), ' 23:59:59'))
and deliveries.Journal_Type = 'DI'
and clients.Salesmen_Code = '".$SalesmanCode."'
and clients.client_deactivate <> 1
and deliveries.client_code not in
(select client_code from(
select client_code
from deliveries
where (delivery_date between ADDDATE('$year-$month-01', INTERVAL 1 DAY) and concat(LAST_DAY('$year-$month-01'), ' 23:59:59')) 
and deliveries.Journal_Type = 'DI' group by deliveries.Client_code 

) as d) group by deliveries.Client_code) as client_old";
	}
	//echo $query_active_clients; exit;
	$result_active_clients = mysql_query($query_active_clients, $connection);
	$row_active_clients = mysql_fetch_array($result_active_clients);
	extract ($row_active_clients);
	echo "<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 13px; font-weight: bold'>$NumberOfActive</td>";
	echo "</tr>
	<tr style='cursor: pointer' onclick=\"displaySemiActiveClients2('".$SalesmanCode."', '".$SalesmanName."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>Clients sold between 60 and 90 days</td>";
	if($Module_Delivery == 1)
	{
	   $query_active_clients = "select count(client_Code) as NumberOfActive from
( 
select sales.Client_code as client_Code
from sales left join clients on clients.ledger_number = sales.client_code
where (sales.Sale_Date between ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 2 MONTH), INTERVAL 1 DAY) and concat(LAST_DAY( DATE_SUB('$year-$month-01',INTERVAL 2 MONTH)), ' 23:59:59'))
and sales.Journal_Type = 'SI'
and clients.Salesmen_Code = '".$SalesmanCode."'
and clients.client_deactivate <> 1 
and sales.client_code not in
(select client_code from(
select client_code
from sales
where (sale_date between ADDDATE('$year-$month-01', INTERVAL 1 DAY) and concat(LAST_DAY('$year-$month-01'), ' 23:59:59')) 
and sales.Journal_Type = 'SI' group by sales.Client_code 

) as d) group by sales.Client_code) as client_old";
	}
	else
	{
	   $query_active_clients = "select count(client_Code) as NumberOfActive from
( 
select deliveries.Client_code as client_Code
from deliveries left join clients on clients.ledger_number = deliveries.client_code
where (deliveries.Delivery_Date between DATE_SUB('$year-$month-01',INTERVAL 2 MONTH) and concat(LAST_DAY( DATE_SUB('$year-$month-01',INTERVAL 2 MONTH)), ' 23:59:59'))
and deliveries.Journal_Type = 'DI'
and clients.Salesmen_Code = '".$SalesmanCode."'
and clients.client_deactivate <> 1
and deliveries.client_code not in
(select client_code from(
select client_code
from deliveries
where (delivery_date between ADDDATE('$year-$month-01', INTERVAL 1 DAY) and concat(LAST_DAY('$year-$month-01'), ' 23:59:59')) 
and deliveries.Journal_Type = 'DI' group by deliveries.Client_code 

) as d) group by deliveries.Client_code) as client_old";
	}
	//echo $query_active_clients; exit;
	$result_active_clients = mysql_query($query_active_clients, $connection);
	$row_active_clients = mysql_fetch_array($result_active_clients);
	extract ($row_active_clients);
	echo "<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 13px; font-weight: bold'>$NumberOfActive</td>";
	echo "</tr>
	<tr style='cursor: pointer' onclick=\"displayPassiveActiveClients('".$SalesmanCode."', '".$SalesmanName."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>Clients sold above 90 days</td>";
	if($Module_Delivery == 1)
	{
	   $query_active_clients = "select count(client_Code) as NumberOfActive from (
select sales.Client_code as client_Code
from sales left join clients on clients.ledger_number = sales.client_code
where (sales.sale_date < ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 2 MONTH), INTERVAL 1 DAY))
and sales.Journal_Type = 'SI'
and clients.Salesmen_Code = '".$SalesmanCode."'
and clients.client_deactivate <> 1
and sales.client_code not in (
select client_code from(
select client_code
from sales 
where (sale_date between ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 2 MONTH), INTERVAL 1 DAY) and concat(LAST_DAY('$year-$month-01'), ' 23:59:59')) and sales.Journal_Type = 'SI' and sales.salesman_code = '".$SalesmanCode."' group by sales.Client_code)  as c)
group by sales.Client_code) as client_old";
	}
	else
	{
	   $query_active_clients = "select count(client_Code) as NumberOfActive from (
select deliveries.Client_code as client_Code
from deliveries left join clients on clients.ledger_number = deliveries.client_code
where (deliveries.Delivery_Date < DATE_SUB('$year-$month-01',INTERVAL 2 MONTH))
and deliveries.Journal_Type = 'DI'
and clients.Salesmen_Code = '".$SalesmanCode."'
and clients.client_deactivate <> 1
and deliveries.client_code not in (
select client_code from(
select client_code
from deliveries 
where (delivery_date between ADDDATE(DATE_SUB('$year-$month-01',INTERVAL 2 MONTH), INTERVAL 1 DAY) and concat(LAST_DAY('$year-$month-01'), ' 23:59:59')) and deliveries.Journal_Type = 'DI' and deliveries.salesman_code = '".$SalesmanCode."' group by deliveries.Client_code)  as c)
group by deliveries.Client_code) as client_old";
	}
	//echo $query_active_clients; exit;
	$result_active_clients = mysql_query($query_active_clients, $connection);
	$row_active_clients = mysql_fetch_array($result_active_clients);
	extract ($row_active_clients);
	echo "<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 13px; font-weight: bold'>$NumberOfActive</td>";
	echo "</tr>
	<tr style='cursor: pointer' onclick=\"displayNonActive('".$SalesmanCode."', '".$SalesmanName."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>Non-Active Clients [Sales]</td>
	<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 13px; font-weight: bold'>";
	if($Module_Delivery == 1)
	{
	   $query_nonactive_clients = "SELECT COUNT(Client_code) as NumberOfNonActive FROM (
SELECT Client_code FROM (
            select sales.Client_code as Client_code from sales left join clients on clients.ledger_number = sales.client_code
            where sales.Sale_Date between DATE_SUB('".$year."-".$month."-01',INTERVAL 1 MONTH) and DATE_SUB('".$year."-".$month."-31 23:59:59',INTERVAL 1 MONTH)
            and sales.journal_type = 'DI' and clients.Salesmen_Code = '".$SalesmanCode."' and clients.client_deactivate <> 1
            group by sales.Client_code) AS CLIENTS
WHERE CLIENT_CODE NOT IN (
 select sales.Client_code as Client_code from sales left join clients on clients.ledger_number = sales.client_code
            where sales.Sale_Date between '".$year."-".$month."-01' and '".$year."-".$month."-31 23:59:59'
            and sales.journal_type = 'DI' and clients.Salesmen_Code = '".$SalesmanCode."' and clients.client_deactivate <> 1
            group by sales.Client_code)) AS CLIENTS";
	}
	else
	{
	$query_nonactive_clients = "SELECT COUNT(Client_code) as NumberOfNonActive FROM (
SELECT Client_code FROM (
            select deliveries.Client_code as Client_code from deliveries left join clients on clients.ledger_number = deliveries.client_code
            where deliveries.delivery_date between DATE_SUB('".$year."-".$month."-01',INTERVAL 1 MONTH) and DATE_SUB('".$year."-".$month."-31 23:59:59',INTERVAL 1 MONTH)
            and deliveries.Journal_Type = 'DI' and clients.Salesmen_Code = '".$SalesmanCode."' and clients.client_deactivate <> 1
            group by deliveries.Client_code) AS CLIENTS
WHERE CLIENT_CODE NOT IN (
 select deliveries.Client_code as Client_code from deliveries left join clients on clients.ledger_number = deliveries.client_code
            where deliveries.delivery_date between '".$year."-".$month."-01' and '".$year."-".$month."-31 23:59:59'
            and deliveries.Journal_Type = 'DI' and clients.Salesmen_Code = '".$SalesmanCode."' and clients.client_deactivate <> 1
            group by deliveries.Client_code)) AS CLIENTS
";
}
	//echo $query_nonactive_clients; exit;
	$result_nonactive_clients = mysql_query($query_nonactive_clients, $connection);
	//echo $result_nonactive_clients; exit;
	$row_nonactive_clients = mysql_fetch_array($result_nonactive_clients);
	extract($row_nonactive_clients);
	$num = mysql_num_rows($result_nonactive_clients);
	echo $NumberOfNonActive;
	echo "</td>
	</tr>
	<tr style='cursor: pointer' onclick=\"displayNewClients('".$SalesmanCode."', '".$SalesmanName."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>New Clients [Creation Date]</td>
	<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 12px; font-size: 13px; font-weight: bold'>";
	$query_new_clients = "select count(clients.Ledger_number) as NumberOfNew 
	                      from clients 
						  where date_format(Client_CreationDate, '%Y-%m') = '".$year."-".$month."' and Salesmen_Code = '".$SalesmanCode."' and clients.Client_deactivate <> 1";
	//echo $query_new_clients;
	$result_new_clients = mysql_query($query_new_clients, $connection);
	$row_new_clients = mysql_fetch_array($result_new_clients);
	extract($row_new_clients);
	echo $NumberOfNew;
	echo "</td>
	</tr>
	<tr style='cursor: pointer' onclick=\"displayDeactivatedClients('".$SalesmanCode."', '".$SalesmanName."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>Deactivated Clients [Field]</td>
	<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 13px; font-weight: bold'>";
	$query_deactivated_clients = "select count(clients.Ledger_number) as NumberofDeactivate 
	                              from clients 
								  where Client_deactivate = 1 and Salesmen_Code = '".$SalesmanCode."'";
	$result_deactivated_clients = mysql_query($query_deactivated_clients, $connection);
	$row_deactivated_clients = mysql_fetch_array($result_deactivated_clients);
	extract($row_deactivated_clients);
	echo $NumberofDeactivate;
	echo "</td>
	</tr>
	<tr style='cursor: pointer' onclick=\"displayActivatedClients('".$SalesmanCode."', '".$SalesmanName."', '".$_GET['month']."', '".$_GET['year']."')\">
	<td style='border: solid 2px #000000; padding: 5px; font-size: 14px; font-weight: bold;'>Activated Clients [Field]</td>
	<td style='border: solid 2px #000000; text-align: right; padding: 5px; font-size: 13px; font-weight: bold'>";
	$query_activated_clients = "select count(clients.Ledger_number) as NumberofActivate 
	                              from clients 
								  where Client_deactivate = 0 and Salesmen_Code = '".$SalesmanCode."'";
	$result_activated_clients = mysql_query($query_activated_clients, $connection);
	$row_activated_clients = mysql_fetch_array($result_activated_clients);
	extract($row_activated_clients);
	echo $NumberofActivate;
	echo "</td>
	</tr>
	
	</table>";
	echo "<div style='height: 25px'></div>";
	echo "<div style='height: 20px'></div>";
	echo "</td></tr>";
	}
	echo "</table>";
		 }
	  }
	echo "</td></tr>";
	
	
	
	echo "<tr><td align='left'></td></tr>";
	
	echo "</table>";
	echo "<br>";
	




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 format_currency($amount, $DecimalBase) { 

    if($amount < 0) 
        return "(".number_format(abs($amount),$DecimalBase).")"; 
 
    else return number_format($amount, $DecimalBase); 
} 

print <<< HERE

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

