File: //home/cafsindia/groups_cafsindia_com/application/controllers/Recruiter_analyse_report.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Recruiter_analyse_report extends Action_controller{
public function __construct(){
parent::__construct('recruiter_analyse_report');
$this->collect_base_info();
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$recruiter_emp_qry = 'SELECT login_code, emp_name FROM `cw_employees` where cw_employees.trans_status = 1 and cw_employees.employee_status = 0 and cw_employees.designation = "28"';
$recruiter_info = $this->db->query("CALL sp_a_run ('SELECT','$recruiter_emp_qry')");
$recruiter_result = $recruiter_info->result();
$recruiter_info->next_result();
$recruiter_list["0"] = "---- Select Recruiter ----";
foreach($recruiter_result as $for){
$login_code = $for->login_code;
$employee_name = $for->emp_name;
$recruiter_list[$login_code] = $login_code."-".$employee_name;
}
$data['recruiter_list'] = $recruiter_list;
$this->load->view("$this->control_name/manage",$data);
}
public function recruit_map_report(){
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$recruiter = implode('","',$this->input->post('recruiter'));
$active_emp_arr = "";
$inactive_emp_arr = "";
$recruiter_map_arr = "";
$tr_line = "";
//SELECT QUERY FOR GET A ACTIVE EMPLOYEE LIST FROM EMPLOYEES TABLE BASED ON THIS SEARCH MONTH AND THIS RECRUITER
$active_emp_qry = 'SELECT cw_branch.branch_name,cw_channel.channel_name,cw_band.band_value, CONCAT(cw_employees.hr_recruiter, " - ", a.emp_name) as hr_recruiter,count(*) as count,
SUM(CASE WHEN cw_employees.band = 1 THEN 1 ELSE 0 END) as S,
SUM(CASE WHEN cw_employees.band = 2 THEN 1 ELSE 0 END) as M,
SUM(CASE WHEN cw_employees.band = 3 THEN 1 ELSE 0 END) as L,
SUM(CASE WHEN cw_employees.band = 4 THEN 1 ELSE 0 END) as T from cw_employees inner join cw_channel on cw_channel.prime_channel_id = cw_employees.channel_name inner join cw_branch on cw_branch.prime_branch_id = cw_employees.branch inner join cw_band on cw_band.prime_band_id = cw_employees.band inner join cw_employees a on a.login_code = cw_employees.hr_recruiter where cw_employees.employee_status != 1 and cw_employees.trans_status = 1 and cw_employees.date_of_joining BETWEEN "'.$from_date.'" and "'.$to_date.'" and cw_employees.hr_recruiter in ("'.$recruiter.'") GROUP BY cw_employees.branch,cw_employees.channel_name,cw_employees.band,cw_employees.hr_recruiter';
$active_emp_info = $this->db->query("CALL sp_a_run ('SELECT','$active_emp_qry')");
$active_emp_rslt = $active_emp_info->result_array();
$active_emp_info->next_result();
$active_emp_arr = array_reduce($active_emp_rslt, function ($result, $arr) {
$result[$arr['branch_name']][$arr['channel_name']][$arr['hr_recruiter']][$arr['band_value']] = $arr;
return $result;
}, array());
//SELECT QUERY FOR GET A ACTIVE EMPLOYEE LIST FROM EMPLOYEES TABLE BASED ON THIS SEARCH MONTH AND THIS RECRUITER
$inactive_emp_qry = 'SELECT cw_branch.branch_name,cw_channel.channel_name,cw_band.band_value,CONCAT(cw_employees.hr_recruiter, " - ", a.emp_name) as hr_recruiter,count(*) as count,
SUM(CASE WHEN cw_employees.band = 1 THEN 1 ELSE 0 END) as S,
SUM(CASE WHEN cw_employees.band = 2 THEN 1 ELSE 0 END) as M,
SUM(CASE WHEN cw_employees.band = 3 THEN 1 ELSE 0 END) as L,
SUM(CASE WHEN cw_employees.band = 4 THEN 1 ELSE 0 END) as T from cw_employees inner join cw_channel on cw_channel.prime_channel_id = cw_employees.channel_name inner join cw_branch on cw_branch.prime_branch_id = cw_employees.branch inner join cw_band on cw_band.prime_band_id = cw_employees.band inner join cw_employees a on a.login_code = cw_employees.hr_recruiter where cw_employees.employee_status = 1 and cw_employees.date_of_joining BETWEEN "'.$from_date.'" and "'.$to_date.'" and cw_employees.hr_recruiter in ("'.$recruiter.'") GROUP BY cw_employees.branch,cw_employees.channel_name,cw_employees.band,cw_employees.hr_recruiter';
// echo "$inactive_emp_qry";die;
$inactive_emp_info = $this->db->query("CALL sp_a_run ('SELECT','$inactive_emp_qry')");
$inactive_emp_rslt = $inactive_emp_info->result_array();
$inactive_emp_info->next_result();
$inactive_emp_arr = array_reduce($inactive_emp_rslt, function ($result, $arr) {
$result[$arr['branch_name']][$arr['channel_name']][$arr['hr_recruiter']][$arr['band_value']] = $arr;
return $result;
}, array());
$recruiter_map_qry = 'SELECT cw_budget_recruiter_mapping.recruiter as rec_login_code,cw_budget.branch as branch,cw_budget.channel_name as channel,cw_branch.branch_name,cw_channel.channel_name,cw_band.band_value,CONCAT(cw_budget_recruiter_mapping.recruiter, " - ", cw_employees.emp_name) as recruiter,count,
SUM(CASE WHEN cw_budget_recruiter_mapping.band_value = 1 THEN count ELSE 0 END) as S,
SUM(CASE WHEN cw_budget_recruiter_mapping.band_value = 2 THEN count ELSE 0 END) as M,
SUM(CASE WHEN cw_budget_recruiter_mapping.band_value = 3 THEN count ELSE 0 END) as L,
SUM(CASE WHEN cw_budget_recruiter_mapping.band_value = 4 THEN count ELSE 0 END) as T
from cw_budget inner join cw_channel on cw_channel.prime_channel_id = cw_budget.channel_name inner join cw_branch on cw_branch.prime_branch_id = cw_budget.branch inner join cw_band on cw_band.prime_band_id = cw_budget.band inner join cw_budget_recruiter_mapping on cw_budget_recruiter_mapping.prime_budget_id = cw_budget.prime_budget_id inner join cw_employees on cw_employees.login_code = cw_budget_recruiter_mapping.recruiter where date_format(str_to_date(CONCAT("01-", cw_budget_recruiter_mapping.recruit_month), "%d-%m-%Y") , "%Y-%m") BETWEEN date_format("'.$from_date.'", "%Y-%m") and date_format("'.$to_date.'", "%Y-%m") and cw_budget_recruiter_mapping.recruiter in ("'.$recruiter.'") and cw_budget.trans_status = 1 GROUP BY cw_budget.branch,cw_budget.channel_name,cw_budget_recruiter_mapping.recruiter';
$recruiter_map_info = $this->db->query("CALL sp_a_run ('SELECT','$recruiter_map_qry')");
$recruiter_map_rslt = $recruiter_map_info->result_array();
$recruiter_map_info->next_result();
$recruiter_map_arr = array_reduce($recruiter_map_rslt, function ($result, $arr) {
$result[$arr['branch_name']][$arr['channel_name']][$arr['recruiter']][$arr['band_value']] = $arr;
return $result;
}, array());
foreach($recruiter_map_arr as $branch => $channel_arr) {
foreach($channel_arr as $channel => $recruiter_arr) {
foreach($recruiter_arr as $recruiter => $band_arr) {
$total_s_count = (int)$band_arr['S']['S'];
$active_s_count = (int)$active_emp_arr[$branch][$channel][$recruiter]['S']['S'];
$inactive_s_count = (int)$inactive_emp_arr[$branch][$channel][$recruiter]['S']['S'];
$achieve_s_count = $active_s_count + $inactive_s_count;
$pen_s_count = $total_s_count - ($active_s_count + $inactive_s_count);
$s_branch_id = (int)$band_arr['S']['branch'];
$s_channel_id = (int)$band_arr['S']['channel'];
$s_rec_login_code = $band_arr['S']['rec_login_code'];
$total_m_count = (int)$band_arr['M']['M'];
$active_m_count = (int)$active_emp_arr[$branch][$channel][$recruiter]['M']['M'];
$inactive_m_count = (int)$inactive_emp_arr[$branch][$channel][$recruiter]['M']['M'];
$achieve_m_count = $active_m_count + $inactive_m_count;
$pen_m_count = $total_m_count - ($active_m_count + $inactive_m_count);
$m_branch_id = (int)$band_arr['M']['branch'];
$m_channel_id = (int)$band_arr['M']['channel'];
$m_rec_login_code = $band_arr['M']['rec_login_code'];
$total_l_count = (int)$band_arr['L']['L'];
$active_l_count = (int)$active_emp_arr[$branch][$channel][$recruiter]['L']['L'];
$inactive_l_count = (int)$inactive_emp_arr[$branch][$channel][$recruiter]['L']['L'];
$achieve_l_count = $active_l_count + $inactive_l_count;
$pen_l_count = $total_l_count - ($active_l_count + $inactive_l_count);
$l_branch_id = (int)$band_arr['L']['branch'];
$l_channel_id = (int)$band_arr['L']['channel'];
$l_rec_login_code = $band_arr['L']['rec_login_code'];
$total_t_count = (int)$band_arr['T']['T'];
$active_t_count = (int)$active_emp_arr[$branch][$channel][$recruiter]['T']['T'];
$inactive_t_count = (int)$inactive_emp_arr[$branch][$channel][$recruiter]['T']['T'];
$achieve_t_count = $active_t_count + $inactive_t_count;
$pen_t_count = $total_t_count - ($active_t_count + $inactive_t_count);
$t_branch_id = (int)$band_arr['T']['branch'];
$t_channel_id = (int)$band_arr['T']['channel'];
$t_rec_login_code = $band_arr['T']['rec_login_code'];
$tr_line .= "<tr>
<td>$branch</td><td>$channel</td><td>$recruiter</td><td>$total_s_count</td><td onclick=show_table_data($s_branch_id,$s_channel_id,'$s_rec_login_code',1,'ACHIEVED','$from_date','$to_date');>$achieve_s_count</td><td onclick=show_table_data($s_branch_id,$s_channel_id,'$s_rec_login_code',1,'ACTIVE','$from_date','$to_date');>$active_s_count</td><td onclick=show_table_data($s_branch_id,$s_channel_id,'$s_rec_login_code',1,'INACTIVE','$from_date','$to_date');>$inactive_s_count</td><td>$pen_s_count</td><td>$total_m_count</td><td onclick=show_table_data($m_branch_id,$m_channel_id,'$m_rec_login_code,2','ACHIEVED','$from_date','$to_date');>$achieve_m_count</td><td onclick=show_table_data($m_branch_id,$m_channel_id,'$m_rec_login_code',2,'ACTIVE','$from_date','$to_date');>$active_m_count</td><td onclick=show_table_data($m_branch_id,$m_channel_id,'$m_rec_login_code',1,'INACTIVE','$from_date','$to_date');>$inactive_m_count</td><td>$pen_m_count</td><td>$total_l_count</td><td onclick=show_table_data($l_branch_id,$l_channel_id,'$l_rec_login_code',3,'ACHIEVED','$from_date','$to_date');>$achieve_l_count</td><td onclick=show_table_data($l_branch_id,$l_channel_id,'$l_rec_login_code',3,'ACTIVE','$from_date','$to_date');>$active_l_count</td><td onclick=show_table_data($l_branch_id,$l_channel_id,'$l_rec_login_code',3,'INACTIVE','$from_date','$to_date');>$inactive_l_count</td><td>$pen_l_count</td><td>$total_t_count</td><td onclick=show_table_data($t_branch_id,$t_channel_id,'$t_rec_login_code',4,'ACHIEVED','$from_date','$to_date');>$achieve_t_count</td><td onclick=show_table_data($t_branch_id,$t_channel_id,'$t_rec_login_code',4,'ACTIVE','$from_date','$to_date');>$active_t_count</td><td onclick=show_table_data($t_branch_id,$t_channel_id,'$t_rec_login_code',4,'INACTIVE','$from_date','$to_date');>$inactive_t_count</td><td>$pen_t_count</td>
</tr>";
}
}
}
$table_info = "<table id = 'recruiter_map_table' class = 'table table-hover'>
<thead>
<tr>
<th rowspan='2'>BRANCH</th>
<th rowspan='2'>CHANNEL</th>
<th rowspan='2'>RECRUITER</th>
<th colspan='5'>S</th>
<th colspan='5' style='background-color: #db9a0a !important;'>M</th>
<th colspan='5' style='background-color: #1181e3 !important;'>L</th>
<th colspan='5' style='background-color: #c4c61d !important;'>T</th>
</tr>
<tr>
<th>COMMITED</th>
<th>ACHIEVED</th>
<th>ACTIVE</th>
<th>IN ACTIVE</th>
<th>PENDING</th>
<th style='background-color: #db9a0a !important;'>COMMITED</th>
<th style='background-color: #db9a0a !important;'>ACHIEVED</th>
<th style='background-color: #db9a0a !important;'>ACTIVE</th>
<th style='background-color: #db9a0a !important;'>IN ACTIVE</th>
<th style='background-color: #db9a0a !important;'>PENDING</th>
<th style='background-color: #1181e3 !important;'>COMMITED</th>
<th style='background-color: #1181e3 !important;'>ACHIEVED</th>
<th style='background-color: #1181e3 !important;'>ACTIVE</th>
<th style='background-color: #1181e3 !important;'>IN ACTIVE</th>
<th style='background-color: #1181e3 !important;'>PENDING</th>
<th style='background-color: #c4c61d !important;'>COMMITED</th>
<th style='background-color: #c4c61d !important;'>ACHIEVED</th>
<th style='background-color: #c4c61d !important;'>ACTIVE</th>
<th style='background-color: #c4c61d !important;'>IN ACTIVE</th>
<th style='background-color: #c4c61d !important;'>PENDING</th>
</tr>
</thead>
<tboady>
$tr_line
</tbody>
</table>";
if($tr_line){
echo json_encode(array('success' => TRUE,'table_info' => $table_info));
}else{
echo json_encode(array('success' => FALSE,'message' => "No Data Available"));
}
}
public function recruiter_analyse_data(){
$branch = $this->input->post('branch');
$channel = $this->input->post('channel');
$recruiter = $this->input->post('recruiter');
$band = $this->input->post('band');
$mode = $this->input->post('mode');
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$emp_qry = "";
if($mode === "ACTIVE"){
$emp_qry = 'SELECT cw_branch.branch_name, cw_channel.channel_name, cw_band.band_value, CONCAT(cw_employees.hr_recruiter, " - ", a.emp_name) as hr_recruiter, CONCAT(cw_employees.login_code, " - ", cw_employees.emp_name) as employee, cw_employees.employee_status from cw_employees inner join cw_channel on cw_channel.prime_channel_id = cw_employees.channel_name inner join cw_branch on cw_branch.prime_branch_id = cw_employees.branch inner join cw_band on cw_band.prime_band_id = cw_employees.band inner join cw_employees a on a.login_code = cw_employees.hr_recruiter where cw_employees.branch = "'.$branch.'" and cw_employees.channel_name = "'.$channel.'" and cw_employees.band = "'.$band.'" and cw_employees.date_of_joining BETWEEN "'.$from_date.'" and "'.$to_date.'" and cw_employees.hr_recruiter = "'.$recruiter.'" and cw_employees.employee_status != 1 and cw_employees.trans_status = 1';
}else
if($mode === "INACTIVE"){
$emp_qry = 'SELECT cw_branch.branch_name,cw_channel.channel_name,cw_band.band_value, CONCAT(cw_employees.hr_recruiter, " - ", a.emp_name) as hr_recruiter, CONCAT(cw_employees.login_code, " - ", cw_employees.emp_name) as employee,cw_employees.employee_status from cw_employees inner join cw_channel on cw_channel.prime_channel_id = cw_employees.channel_name inner join cw_branch on cw_branch.prime_branch_id = cw_employees.branch inner join cw_band on cw_band.prime_band_id = cw_employees.band inner join cw_employees a on a.login_code = cw_employees.hr_recruiter where cw_employees.branch = "'.$branch.'" and cw_employees.channel_name = "'.$channel.'" and cw_employees.band = "'.$band.'" and cw_employees.date_of_joining BETWEEN "'.$from_date.'" and "'.$to_date.'" and cw_employees.hr_recruiter = "'.$recruiter.'" and cw_employees.employee_status = 1';
}else
if($mode === "ACHIEVED"){
$emp_qry = 'SELECT cw_branch.branch_name,cw_channel.channel_name,cw_band.band_value, CONCAT(cw_employees.hr_recruiter, " - ", a.emp_name) as hr_recruiter, CONCAT(cw_employees.login_code, " - ", cw_employees.emp_name) as employee,cw_employees.employee_status from cw_employees inner join cw_channel on cw_channel.prime_channel_id = cw_employees.channel_name inner join cw_branch on cw_branch.prime_branch_id = cw_employees.branch inner join cw_band on cw_band.prime_band_id = cw_employees.band inner join cw_employees a on a.login_code = cw_employees.hr_recruiter where cw_employees.branch = "'.$branch.'" and cw_employees.channel_name = "'.$channel.'" and cw_employees.band = "'.$band.'" and cw_employees.date_of_joining BETWEEN "'.$from_date.'" and "'.$to_date.'" and cw_employees.hr_recruiter = "'.$recruiter.'"';
}
// echo $emp_qry;die;
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_result = $emp_info->result_array();
$emp_info->next_result();
if($emp_result){
echo json_encode(array('success' => TRUE,'emp_info' => $emp_result));
}else{
echo json_encode(array('success' => FALSE,'message' => "No Data Available"));
}
}
}
?>