<link rel="stylesheet" href="css/new_mxerp.css">

<div class="body_contain">
<div class="tab_div"> 

<form name="search" action="" method="post">
<h1>Department Month Wise Offer</h1>
<table width="100%" cellspacing="0" cellpadding="3" border="0" align="center">
  <tbody>
  <tr class="row1">
    <td width="13%" style="text-align:right;"><strong>Department</strong></td>
    <td width="35%"><select id="dept" name="dept" onchange="get_designation(this.value)">
    <option value="0">Select Department</option>
       <?php  $dept1=DB_query("SELECT * FROM `deptmaster` order by deptname",$db);?>

                 <?php   while($DeptRec1=DB_fetch_array($dept1)) { ?>
          <option value="<?php echo $DeptRec1['id']; ?>" <?php if($DeptRec1['id']==$_REQUEST['dept']){ echo "selected" ;}  ?>><?php echo $DeptRec1['deptname']; ?></option>
          <?php } ?>
      </select></td>
    <td width="13%" style="text-align:right;"><strong>Designation</strong></td>
    <td> 
      <select id="design" name="design" onchange="get_applicant(this.value)">              
      <option value="0">Select Designation</option>
      <?php  $design1=DB_query("SELECT * FROM `desigmaster` order by designame",$db);?>
              <?php   while($designRec1=DB_fetch_array($design1)) { ?>
          <option value="<?php echo $designRec1['id']; ?>" <?php if($designRec1['id']==$_REQUEST['design']){ echo "selected" ;}  ?>><?php echo $designRec1['designame']; ?></option>
          <?php } ?>        
         </select>
</td>
</tr>
<tr class="row1">
  <td style="text-align:center;" colspan="6"><input type="submit" onclick="return frmvalidate();" value="Search" class="search" name="Search">
  </td>
</tr>
</tbody>
</table>
<p align="right"><a href="dpt_monthwise_offer_print.php" target="_blank"><input type="button"  value="Print" class="search" ></a></p>
<table width="100%" cellpadding="3" cellspacing="0" border="1" style="border:#808080 1px solid;">  
	<tr>		
	  <th width="8%"><strong>S. No.</strong></th>
	  <th width="80"><strong>Jan</strong></th>
	  <th width="80"><strong>Feb</strong></th>
	  <th width="80"><strong>March</strong></th>
	  <th width="80"><strong>April</strong></th>
	  <th width="80"><strong>May</strong></th>
	  <th width="80"><strong>June</strong></th>
	  <th width="80"><strong>July</strong></th>
	  <th width="80"><strong>August</strong></th>
	  <th width="80"><strong>sept</strong></th>
	  <th width="80"><strong>Oct</strong></th>
	  <th width="80"><strong>Nov</strong></th>
	  <th width="80" ><strong>Dec</strong></th>
	  <th width="80"><strong>Total</strong></th>
	</tr>
<?php
//if(in_array($_SESSION['UserID'],$Explode))
//{

if($_REQUEST['Search']!=''){

  //$filter[]="1=1";
if($_REQUEST['dept']!='0')
  {
  $filter[]="department='".$_REQUEST['dept']."'";
  }
  if($_REQUEST['design']!='0' && $_REQUEST['design']!='')
  {
  $filter[]="designation='".$_REQUEST['design']."'";
  }
  
}
if($filter!='')
  {
  
    $filter1 = implode(" and ",$filter);
    
   $q="select distinct department from hr_applcnt_rgstn where $filter1";
    }
else
{
  $q="select distinct department from hr_applcnt_rgstn";
}
$_SESSION['q']=$q;

if($_REQUEST['dept']!='0')
  {
  $_SESSION['dept']=$_REQUEST['dept'];
  }
  else
  {
  	$_SESSION['dept']='';

  }
  if($_REQUEST['design']!='0' && $_REQUEST['design']!='')
  {
   $_SESSION['design']=$_REQUEST['design'];
  }
  else
  {
  	$_SESSION['design']='';
  }

?>
<?php
	$i=1;
	
	$query=DB_query($q,$db);
	if(DB_num_rows($query)>0)
	{
	while($row=DB_fetch_array($query))
	{
	
?>
<?php
$ct=DB_num_rows(DB_query("SELECT COUNT(a.status) AS total,b.department ,DATE_FORMAT(DATETIME,'%m-%y') AS MONTH,b.id FROM hr_applcnt_rgstn_log_status a INNER JOIN hr_applcnt_rgstn b ON b.id=a.awd_application_id WHERE a.status='offer' AND  b.department='". $row['department']."'  GROUP BY b.department" ,$db));
if($ct>0){   ?>

<tr bgcolor="#e4e4e4">
	<td><?php echo $i; ?></td>
	<td colspan="15"><strong><?php echo  $sitename=$s->getField('deptmaster','id',$row['department'],'deptname'); ?></strong>
	<?php //echo $row['deptname']; ?></td>
</tr>
<tr>
	<td colspan="16" valign="top" style="padding:0;">
	 	<table width="100%" cellpadding="3" cellspacing="0" border="1" style="border:#808080 1px solid; border-collapse:collapse;">		
			<tr>	
				<td width="8%" valign="top" style="padding:0;">
 	    		<div id="amc-tab">
					<table width="100%" cellpadding="3" cellspacing="0" border="0">
						<tr>
							<td>Offered</td>
						</tr>
						<tr >
							<td>Reject</td>
						</tr>
						<tr>
							<td>ACK</td>
						</tr>						
					</table>
				</div>
				</td>

<?php for($ty=1;$ty<=13;$ty++)
{
//foreach ($month as $key => $value) {
?>
		<td valign="top" style="padding:0;">
 	    <div id="amc-tab">
<?php
$total='';
//echo "SELECT COUNT(a.status) AS total,b.department ,DATE_FORMAT(DATETIME,'%m-%y') AS MONTH,b.id FROM hr_applcnt_rgstn_log_status a INNER JOIN hr_applcnt_rgstn b ON b.id=a.awd_application_id WHERE a.status='offer' AND  b.department='". $row['department']."'  GROUP BY b.department";

   $detai=DB_query("SELECT COUNT(a.status) AS total,b.department ,DATE_FORMAT(DATETIME,'%m-%y') AS MONTH,b.id FROM hr_applcnt_rgstn_log_status a INNER JOIN hr_applcnt_rgstn b ON b.id=a.awd_application_id WHERE a.status='offer' AND  b.department='". $row['department']."'  GROUP BY b.department" ,$db);
	while($dt=DB_fetch_assoc($detai)){
	$mont= explode('-', $dt['MONTH']);	
	$jatotal=$dt['total'];
	 $total=$total+$dt['total'];
} ?>
	    <table width="100%" cellpadding="3" cellspacing="0" border="0">
		<tr>
			<td width="80"><? if($ty==$mont[0]) { echo $jatotal;   } else { echo 0; }
							if($ty==13) { echo $total;  } ?></td>			
		</tr>
<?php
   $detail=DB_query("SELECT COUNT(a.status) AS total,b.department ,DATE_FORMAT(DATETIME,'%m-%y') AS MONTH,b.id FROM hr_applcnt_rgstn_log_status a INNER JOIN hr_applcnt_rgstn b ON b.id=a.awd_application_id WHERE a.status='off_reject' AND  b.department='". $row['department']."'  GROUP BY b.department" ,$db);
	while($dt1=DB_fetch_assoc($detail)){
	$mont1= explode('-', $dt1['MONTH']);	
	$rejtotal=$dt1['total'];
	$total1=$total+$dt1['total'];

} ?>
		<tr>
		<td width="80"><? if($ty==$mont1[0]) { echo $rejtotal;   } else echo 0;  
			if($ty==13) { echo $total1;  } ?></td>
		</tr>
<?php
$total12='';	
   $detail1=DB_query("SELECT COUNT(a.status) AS total,b.department ,DATE_FORMAT(DATETIME,'%m-%y') AS MONTH,b.id FROM hr_applcnt_rgstn_log_status a INNER JOIN hr_applcnt_rgstn b ON b.id=a.awd_application_id WHERE a.status='off_accept' AND  b.department='". $row['department']."'  GROUP BY b.department" ,$db);
	while($dt11=DB_fetch_assoc($detail1)){
	$mont11= explode('-', $dt11['MONTH']);	
	$acttotal=$dt11['total'];
	$total12=$total12+$dt11['total'];
} ?>
		<tr>
		<td width="80"><? if($ty==$mont11[0]) { echo $acttotal;   } else echo 0;
				if($ty==13) { echo $total12;  } ?></td>
		</tr>
		
	</table>
  </div>
</td>
<?php  } ?>
</tr>

</table>
  </td>
</tr>
<?php } ?>

<?php $i++;  } } ?>

</table>
</form>
</div>
</div>
