<?php 
include('functions.php');
$s=new db_query();
//GET Heirarchy from users wise
$deptid=  $s->GetFieldValue('www_users'," id='".$_SESSION['UserID']."' " ,'mst_dpt_heirarchy');
$r=GETUpperDeptUser($_SESSION['UserID'],$deptid,$_SESSION['ACGCompanyId']);
$userlist=  UserDeptList($r);
  if(count($userlist)>0)
$userlist=implode(",",$userlist).",".$_SESSION['UserID'];
else
$userlist="'".$_SESSION['UserID']."'";

$LowerDeptList=GETLowerDeptUser($_SESSION['UserID'],$deptid,$_SESSION['ACGCompanyId']);

 $lowerlist=  UserDeptList($LowerDeptList);
  //echo "<br>--------222--------<br>";
 //print_r($LowerDeptList);
 if(count($lowerlist)>0)
$lowerlist=implode(",",$lowerlist).",".$_SESSION['UserID'];
else
$lowerlist="'".$_SESSION['UserID']."'";



if(isset($_POST['GO']))
{
	$fin_year=$_REQUEST['fin_year'];
	$fromdate=ConvertSQLDate($_REQUEST['fdate']);
	$todate=ConvertSQLDate($_REQUEST['todate']);
	$month=explode('-',$_REQUEST['month']);
	$filter=array();
	if($fin_year!='000')
	{
	$filter[]="fin_year='".$fin_year."'";
	}
	if($_REQUEST['todate']!='' && $_REQUEST['fdate']!='' )
	{
	$filter[]="published_data between '".$fromdate."' and '".$todate."'";
	}
	if($month[0]!='All')
	{
	$filter[]="month='".$month[0]."'";
	$filter[]="fin_year like '".$month[1]."%'";
	}
	
	if(count($filter)>0)
	{
	if($month[0]!='All'){
	$searchdata=implode(" and ", $filter);
	 $sql="select * from crmsales_target where $searchdata and mst_usersid in($lowerlist)";
	}
	else
	{
	$searchdata=implode(" and ", $filter);
	 $sql="select DISTINCT(mst_usersid),fin_year from crmsales_target where $searchdata and mst_usersid in($lowerlist)";
	}
	}
	else
	{
	$sql="select * from crmsales_target where month='".date("m")."' and mst_usersid in($lowerlist)";	
	}
	$_SESSION['query']=$sql;
}
?>

<link rel="stylesheet" href="css/new_mxerp.css">
<div class="body_contain">
<div class="tab_div">
<h1>Users Target V/S Achievement</h1>
<!-- Start Table 1 --><div>
<form name="searchform" method="post" action="">
<div class="centre" id="slidingDiv">
<table width="100%" border="0" cellpadding="3" cellspacing="0">
<tr class="row1">
    <td class="tabledata1">Financial year</td>
<?php $FinY=DB_query("SELECT *,YEAR(BookPeriodStart) AS SYear,YEAR(BookPeriodEnd) AS EYEAR FROM companies where CompanyId='".$_SESSION['ACGCompanyId']."'",$db);
$FinYRow=DB_fetch_array($FinY);?>
    <td><select name="fin_year" style="width:150px;">
	<option value="000">Select</option>
	<option value="<?php echo $FinYRow['SYear'].'-'.$FinYRow['EYEAR']; ?>"<?php if($_REQUEST['fin_year']==$FinYRow['SYear'].'-'.$FinYRow['EYEAR']){?>selected="selected" <?php } ?>     ><?php echo $FinYRow['SYear'].'-'.$FinYRow['EYEAR']; ?></option>
	</select></td>
   <td class="tabledata1">Month</td>
    <td><select name="month" style="width:150px;">
	<option value="All"<?php echo($_REQUEST['month']=='All'?'selected':''); ?>>All</option>
<?php $diffdata=($FinYRow['EYEAR']-$FinYRow['SYear'])+1;
	  for($i=0;$i<$diffdata;$i++){
	  if($i==0) { ?>
	<option value="04-<?php echo $FinYRow['SYear'] ?>"<?php if($_REQUEST['month']=='04'.'-'.$FinYRow['SYear']){?>selected="selected"<?php }?>>April-<?php echo $FinYRow['SYear'] ?></option>
	<option value="05-<?php echo $FinYRow['SYear'] ?>"<?php if($_REQUEST['month']=='05'.'-'.$FinYRow['SYear']){?>selected="selected"<?php }?>>May-<?php echo $FinYRow['SYear'] ?></option>
	<option value="06-<?php echo $FinYRow['SYear'] ?>"<?php if($_REQUEST['month']=='06'.'-'.$FinYRow['SYear']){?>selected="selected"<?php }?>>June-<?php echo $FinYRow['SYear'] ?></option>
	<option value="07-<?php echo $FinYRow['SYear'] ?>"<?php if($_REQUEST['month']=='07'.'-'.$FinYRow['SYear']){?>selected="selected"<?php }?>>July-<?php echo $FinYRow['SYear'] ?></option>
	<option value="08-<?php echo $FinYRow['SYear'] ?>"<?php if($_REQUEST['month']=='08'.'-'.$FinYRow['SYear']){?>selected="selected"<?php }?>>August-<?php echo $FinYRow['SYear'] ?></option>
	<option value="09-<?php echo $FinYRow['SYear'] ?>"<?php if($_REQUEST['month']=='09'.'-'.$FinYRow['SYear']){?>selected="selected"<?php }?>>September-<?php echo $FinYRow['SYear'] ?></option>
	<option value="10-<?php echo $FinYRow['SYear'] ?>"<?php if($_REQUEST['month']=='10'.'-'.$FinYRow['SYear']){?>selected="selected"<?php }?>>October-<?php echo $FinYRow['SYear'] ?></option>
	<option value="11-<?php echo $FinYRow['SYear'] ?>"<?php if($_REQUEST['month']=='11'.'-'.$FinYRow['SYear']){?>selected="selected"<?php }?>>November-<?php echo $FinYRow['SYear'] ?></option>
	<option value="12-<?php echo $FinYRow['SYear'] ?>"<?php if($_REQUEST['month']=='12'.'-'.$FinYRow['SYear']){?>selected="selected"<?php }?>>December-<?php echo $FinYRow['SYear'] ?></option>
	<?php } else {?>
	<option value="01-<?php echo $FinYRow['EYEAR'] ?>"<?php if($_REQUEST['month']=='01'.'-'.$FinYRow['EYEAR']){?>selected="selected"<?php }?>>January-<?php echo $FinYRow['EYEAR'] ?></option>
	<option value="02-<?php echo $FinYRow['EYEAR'] ?>"<?php if($_REQUEST['month']=='02'.'-'.$FinYRow['EYEAR']){?>selected="selected"<?php }?>>February-<?php echo $FinYRow['EYEAR'] ?></option>
	<option value="03-<?php echo $FinYRow['EYEAR'] ?>"<?php if($_REQUEST['month']=='03'.'-'.$FinYRow['EYEAR']){?>selected="selected"<?php }?>>March-<?php echo $FinYRow['EYEAR'] ?></option>
	<?php } } ?>	
	</select></td>

</tr>
<tr class="row1">
    <td class="tabledata1">Start date</td>
    <td><input name="fdate" type="text" style="width:150px;" value="<?php echo $_REQUEST['fdate'] ?>" class="datepicker" /></td>
    <td class="tabledata1">End date</td>
    <td><input name="todate" type="text"  style="width:150px;" value="<?php echo $_REQUEST['todate'] ?>" class="datepicker" /></td>
  </tr>
<tr class="row1">
	<td colspan="4">
		<p align="center"><input type="submit" value="GO"  name="GO" class="search" /></p>
	</td>
</tr>
</table>
</div>
</form>
</div><!-- Close Table 1 -->
<div style="clear:both;"></div>

<?php 
if($_REQUEST['GO']!='')
{?>
	<div align="right"><a onclick='popup("salescrm_target.php?id=1&months=<?php echo $_REQUEST['month']; ?>",1000,1000)' href="#"   >Print</a></div>
<table width="100%" border="1" cellpadding="3" cellspacing="0">
  <tr class="row1">
    <td class="tabledata">User Name</td>
    <td class="tabledata">Month</td>
    <td class="tabledata">Assigned Target</td>
    <td class="tabledata">Target Achieved</td>
    <!--<td class="tabledata">Achieved %</td>-->
    <td class="tabledata">Difference</td>
  </tr>
 
<?php 
if($_REQUEST['month']!='All'){
$query_target=DB_query($sql,$db);
if(DB_num_rows($query_target)>0){
$j=0;
$sql1=DB_query($sql,$db);
while($Crm_sales=DB_fetch_array($sql1))
{
$timestamp = mktime(0, 0, 0, $Crm_sales['month'], 1, $Crm_sales['yearmonth']);
$monthdata= date("M", $timestamp);
?>

<tr class="row1">
  <td><?php echo $s->GetFieldValue('www_users',"id='".$Crm_sales['mst_usersid']."'",'realname'); ?></td>
  <td><?php echo $monthdata; ?>&nbsp;<?php echo $Crm_sales['yearmonth']; ?></td>
  <td><?php echo $Crm_sales['target']; ?></td>
<?php $t=0;
$Sales1=DB_query("select * from salesorder where Sale_Key_Exec='".$Crm_sales['mst_usersid']."' and month(orderDate)='".$Crm_sales['month']."' ",$db);
while($Sales1Row=DB_fetch_array($Sales1)){

$invoice=DB_query("SELECT SUM(Amount) AS Total FROM salesorder_details WHERE masterId='".$Sales1Row['id']."' and (ApprovalStatus='2' || ApprovalStatus='3') ",$db);
while($invoiceRow1=DB_fetch_array($invoice)){?>
<?php $t+= $invoiceRow1['Total'];?>
<?php  }}?>
  <td><?php echo $t;?></td>
<?php if($t!=0)
	$per=($Crm_sales['target']/$t)*100;
	else
	$per='';?>
 <!--<td><?php echo round($per,2); ?></td>-->
<?php if($t>$Crm_sales['target']){
	  $DiffRow=$t-$Crm_sales['target'];
	  $val="+";
	  }
	  else
	  {
	  $DiffRow=$Crm_sales['target']-$t;
	  $val="-";	
	  }?>
 <td><?php echo $val; ?>&nbsp;<?php echo $DiffRow; ?></td>
</tr>
<?php $j++; }  }
  else {?>
<tr class="row1">
  <td colspan="6" style="color:#FF0000;">No Records</td>	
</tr>
<?php } ?>
<?php } else {?>








<?php
$query_target=DB_query($sql,$db);
if(DB_num_rows($query_target)>0){
$j=0;
$sql1=DB_query($sql,$db);
while($Crm_sales=DB_fetch_array($sql1))
{
$TotalTarSQL=DB_query("select sum(target) as GrandTotal from crmsales_target where mst_usersid='".$Crm_sales['mst_usersid']."'",$db);
$TotalTarAMT=DB_fetch_array($TotalTarSQL);
$FinalGrandAMT=round($TotalTarAMT['GrandTotal']);
$timestamp = mktime(0, 0, 0, $Crm_sales['month'], 1, $Crm_sales['yearmonth']);
$monthdata= date("M", $timestamp);
?>

<tr class="row1">
  <td><?php echo $s->GetFieldValue('www_users',"id='".$Crm_sales['mst_usersid']."'",'userid'); ?></td>
  <td><?php echo 'All Month'; ?>&nbsp;<?php echo $Crm_sales['fin_year']; ?></td>
  <td><?php echo $FinalGrandAMT; ?></td>
<?php $t=0;
$Sales1=DB_query("select * from salesorder where Sale_Key_Exec='".$Crm_sales['mst_usersid']."'",$db);
while($Sales1Row=DB_fetch_array($Sales1)){
$invoice=DB_query("SELECT SUM(Amount) AS Total FROM salesorder_details WHERE masterId='".$Sales1Row['id']."' and (ApprovalStatus='2' || ApprovalStatus='3') ",$db);
while($invoiceRow1=DB_fetch_array($invoice)){?>
<?php $t+= $invoiceRow1['Total'];?>
<?php  }}?>
  <td><?php echo $t;?></td>
<?php if($t!=0)
	$per=($Crm_sales['target']/$t)*100;
	else
	$per='';?>
 <!--<td><?php echo round($per,2); ?></td>-->
<?php if($t>$FinalGrandAMT){
	  $DiffRow=$t-$FinalGrandAMT;
	  $val="+";
	  }
	  else
	  {
	  $DiffRow=$FinalGrandAMT-$t;
	  $val="-";	
	  }?>
 <td><?php echo $val; ?>&nbsp;<?php echo $DiffRow; ?> </td>
</tr>
<?php $j++; }  }
  else {?>
<tr class="row1">
  <td colspan="6" style="color:#FF0000;">No Records</td>	
</tr>
<?php } ?>



<?php } ?>
</table>
<?php } ?>
<!--else {
?>-->
<!-- Start Table 1 --><div>

<?php 
//echo "SELECT id FROM deptmaster WHERE deptcode='MKT' OR deptcode='sal'";
	//$Dept=DB_query("SELECT id FROM deptmaster WHERE deptcode='MKT' OR deptcode='sal'",$db);
	//if(DB_num_rows($Dept))
	//{
	//while($DeptRow=DB_fetch_array($Dept))
	//{
		//$DCode[]=$DeptRow['id'];
	//}
	// $Dept_id=implode(',',$DCode);
	// $query_target = DB_query("select id,userid,realname from www_users where realname!='Admin' and companyid='".$_SESSION['ACGCompanyId']."' and blocked=0 and department in($Dept_id)",$db);
  //if(DB_num_rows($query_target)>0)
  //{
?>
<!--<div align="right"><a onclick='popup("salescrm_target.php?id=<?php echo $Crm_sales['id'];?>",1000,1000)' href="#"   >Print</a></div> 
<table width="80%" border="0" cellspacing="1" cellpadding="2" align="left">
  <tr>
    <td class="tabledata">User Name</td>
    <td class="tabledata">Target amount</td>
    <td class="tabledata">Target Achieved</td>
    <td class="tabledata">Achieved %</td>
    <td class="tabledata">Difference</td>
  </tr>
  <?php 
  $i=0;
  while($query_targetRow=DB_fetch_array($query_target))
  {
  $tar=DB_query("select target from crmsales_target where mst_usersid='".$query_targetRow['id']."' and month='".date("m")."'",$db);	
  $target1=DB_fetch_array($tar);
  $target=$target1['target'];	
	
 ?>
  <tr bgcolor="#e7e7e7">
    <td>		
	<?php echo $query_targetRow['userid']; ?></td>
    <td> <?php echo $target; ?>	</td>
    <td>
	<?php 
	$t=0;
	// echo "select * from salesorder where Sale_Key_Exec='".$query_targetRow['id']."'";
 $Sales1=DB_query("select * from salesorder where Sale_Key_Exec='".$query_targetRow['id']."' ",$db);
 while($Sales1Row=DB_fetch_array($Sales1))
 {
// echo "SELECT SUM(AssValue) AS Total FROM dispatch_details WHERE SOPNo='".$Sales1Row['id']."'";
//echo "SELECT SUM(Amount) AS Total FROM salesorder_details WHERE masterId='".$Sales1Row['id']."'";
 $invoice=DB_query("SELECT SUM(Amount) AS Total FROM salesorder_details WHERE masterId='".$Sales1Row['id']."' and ApprovalStatus='Approved' ",$db);
 while($invoiceRow1=DB_fetch_array($invoice))
 {?>
	
	<?php $t+= $invoiceRow1['Total'];?>
	<?php  }
 }
 ?>
 <?php echo $t;?>
	</td>
	<?php 
	if($t!=0)
	$per=($target/$t)*100;
	else
	$per='';	
     ?>
	<td><?php echo round($per,2); ?></td>
	<?php 
	//echo "select * from crmsales_target where mst_usersid='".$query_targetRow['id']."' and  month='".date("m")."'";
	if($t>$target)
	{
	$DiffRow=$t-$target;
	$val="Pen";
	}
	else
	{
	$DiffRow=$target-$t;
	$val="App";	
	}
	?>
    <td>
	<?php echo $DiffRow; ?> &nbsp;<?php echo $val; ?></td>
	
  </tr>
  <?php $i++;  }?>
</table>
-->
<?php ?>
</div><!-- Close Table 1 -->
</div></div>