File: //home/cafsindia/groups_cafsindia_com/application/controllers/Increment_report.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Increment_report extends Action_controller{
public function __construct(){
parent::__construct('increment_report');
$this->collect_base_info();
}
public function index(){
$channel_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_channel` where trans_status = 1')");
$channel_result = $channel_info->result();
$channel_info->next_result();
$channel_list["0"] = "---- Channel ----";
foreach($channel_result as $for){
$channel_id = $for->prime_channel_id;
$channel_name = $for->channel_name;
$channel_list[$channel_id] = $channel_name;
}
$data['channel_list'] = $channel_list;
$branch_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_branch` where trans_status = 1')");
$branch_result = $branch_info->result();
$branch_info->next_result();
$branch_list["0"] = "---- Branch ----";
foreach($branch_result as $for){
$branch_id = $for->prime_branch_id;
$branch_name = $for->branch_name;
$branch_list[$branch_id] = $branch_name;
}
$data['branch_list'] = $branch_list;
$designation_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_designation` where trans_status = 1')");
$designation_result = $designation_info->result();
$designation_info->next_result();
$designation_list["0"] = "---- Designation ----";
foreach($designation_result as $for){
$designation_id = $for->prime_designation_id;
$designation_name = $for->designation;
$designation_list[$designation_id] = $designation_name;
}
$data['designation_list'] = $designation_list;
$level_1_report_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT distinct(a.level_1_reporting) as leve_1_report, cw_employees.emp_name as emp_name FROM `cw_employees` inner join cw_employees as a on a.level_1_reporting = cw_employees.login_code where cw_employees.trans_status = 1 and cw_employees.employee_status = 0')");
$level_1_report_result = $level_1_report_info->result();
$level_1_report_info->next_result();
$level_1_list["0"] = "---- Level One Report ----";
foreach($level_1_report_result as $for){
$level_1_code = $for->leve_1_report;
$employee_name = $for->emp_name;
$level_1_list[$level_1_code] = $level_1_code."-".$employee_name;
}
$data['level_1_list'] = $level_1_list;
$level_2_report_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT distinct(a.level_2_reporting) as leve_2_report, cw_employees.emp_name as emp_name FROM `cw_employees` inner join cw_employees as a on a.level_2_reporting = cw_employees.login_code where cw_employees.trans_status = 1 and cw_employees.employee_status = 0')");
$level_2_report_result = $level_2_report_info->result();
$level_2_report_info->next_result();
$level_2_list["0"] = "---- Level Two Report ----";
foreach($level_2_report_result as $for){
$level_2_code = $for->leve_2_report;
$employee_name = $for->emp_name;
$level_2_list[$level_2_code] = $level_2_code."-".$employee_name;
}
$data['level_2_list'] = $level_2_list;
$this->load->view("$this->control_name/manage",$data);
}
public function get_increment(){
// $from_month = date('Y-m-d',strtotime("01-".$this->input->post('from_mon_year')));
// $to_month = date('Y-m-d',strtotime("01-".$this->input->post('to_mon_year')));
$check_date = date('Y-m-d',strtotime("01-".$this->input->post('mon_year')));
$branch = implode('","', $this->input->post('branch'));
$channel = implode('","', $this->input->post('channel'));
$designation = implode('","', $this->input->post('designation'));
$level1 = implode('","', $this->input->post('level1'));
$level2 = implode('","', $this->input->post('level2'));
$emp_increment_arr = "";
$emp_promotion_arr = "";
$emp_qry = 'select cw_employees.login_code,cw_employees.emp_name,IF(cw_employees.date_of_joining IS NULL or cw_employees.date_of_joining = "0000-00-00", "0", cw_employees.date_of_joining) as date_of_joining,cw_branch.branch_name,cw_channel.channel_name,cw_designation.designation, CONCAT(TIMESTAMPDIFF( YEAR, cw_employees.date_of_joining, now() ),".", TIMESTAMPDIFF( MONTH, cw_employees.date_of_joining, now() ) % 12) as vintage,a.login_code as level1,b.login_code as level2,cw_employees.yearly_ctc from cw_employees inner join cw_branch on cw_branch.prime_branch_id = cw_employees.branch inner join cw_channel on cw_channel.prime_channel_id = cw_employees.channel_name inner join cw_designation on cw_designation.prime_designation_id = cw_employees.designation inner join cw_employees a on cw_employees.level_1_reporting = a.login_code inner join cw_employees b on cw_employees.level_2_reporting = b.login_code where cw_employees.login_code != "" and cw_employees.employee_status = 0 and cw_employees.branch in ("'.$branch.'") and cw_employees.channel_name in ("'.$channel.'") and cw_employees.designation in ("'.$designation.'") and cw_employees.level_1_reporting in ("'.$level1.'") and cw_employees.level_2_reporting in ("'.$level2.'")';
//CONCAT(TIMESTAMPDIFF( YEAR, cw_employees.date_of_joining, now() ),".", TIMESTAMPDIFF( MONTH, cw_employees.date_of_joining, now() ) % 12) as vintage
//IF(cw_employees.date_of_joining IS NULL or cw_employees.date_of_joining = "0000-00-00", "0", ROUND(timestampdiff(MONTH,cw_employees.date_of_joining, CURRENT_DATE())/12,1)) as vintage
//,c.designation as level1_designation,d.designation as level2_designation
//inner join cw_designation d on b.designation = d.prime_designation_id inner join cw_designation c on a.designation = c.prime_designation_id
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_result = $emp_info->result_array();
$emp_info->next_result();
$emp_array = array_reduce($emp_result, function ($result, $arr) {
$result[$arr['login_code']] = $arr;
return $result;
}, array());
$emp_increment_qry = 'select cw_increment.login_code,cw_increment.previous_ctc,cw_increment.hike_ctc,cw_increment.percentage,increment_month,CONCAT(TIMESTAMPDIFF( YEAR, p2.MaxPostDate, "'.$check_date.'" ),".", TIMESTAMPDIFF( MONTH, p2.MaxPostDate, "'.$check_date.'" ) % 12) as increment_vintage from cw_employees inner join cw_increment on cw_employees.login_code = cw_increment.login_code INNER JOIN (SELECT max(increment_month) MaxPostDate,login_code FROM cw_increment where cw_increment.increment_month <= "'.$check_date.'" GROUP BY login_code) p2 ON cw_increment.login_code = p2.login_code AND cw_increment.increment_month = p2.MaxPostDate where cw_employees.login_code != "" and cw_employees.employee_status = 0 and cw_employees.branch in ("'.$branch.'") and cw_employees.channel_name in ("'.$channel.'") and cw_employees.designation in ("'.$designation.'") and cw_employees.level_1_reporting in ("'.$level1.'") and cw_employees.level_2_reporting in ("'.$level2.'") and cw_increment.trans_status = 1';
$emp_increment_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_increment_qry')");
$emp_increment_result = $emp_increment_info->result_array();
$emp_increment_info->next_result();
$emp_increment_arr = array_reduce($emp_increment_result, function ($result, $arr) {
$result[$arr['login_code']] = $arr;
return $result;
}, array());
$emp_promotion_qry = 'select cw_promotion.login_code,a.designation as previous_designation,cw_designation.designation as promoted_designation,promotion_month,ROUND(timestampdiff(MONTH,p2.MaxPostDate, "'.$check_date.'")/12,1) as promotion_vintage from cw_employees inner join cw_promotion on cw_employees.login_code = cw_promotion.login_code inner join cw_designation on cw_designation.prime_designation_id = cw_promotion.promoted_designation inner join cw_designation a on a.prime_designation_id = cw_promotion.designation INNER JOIN
(SELECT max(promotion_month) MaxPostDate,login_code FROM cw_promotion where cw_promotion.promotion_month <= "'.$check_date.'" GROUP BY login_code) p2
ON cw_promotion.login_code = p2.login_code AND cw_promotion.promotion_month = p2.MaxPostDate where cw_employees.login_code != "" and cw_employees.employee_status = 0 and cw_employees.branch in ("'.$branch.'") and cw_employees.channel_name in ("'.$channel.'") and cw_employees.designation in ("'.$designation.'") and cw_employees.level_1_reporting in ("'.$level1.'") and cw_employees.level_2_reporting in ("'.$level2.'") and cw_promotion.trans_status = 1 order by promotion_month DESC';
$emp_promotion_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_promotion_qry')");
$emp_promotion_result = $emp_promotion_info->result_array();
$emp_promotion_info->next_result();
$emp_promotion_arr = array_reduce($emp_promotion_result, function ($result, $arr) {
$result[$arr['login_code']] = $arr;
return $result;
}, array());
$tr_line = "";
if($emp_array){
foreach ($emp_array as $key => $value) {
$login_code = $value['login_code'];
$emp_name = $value['emp_name'];
$date_of_joining = $value['date_of_joining'];
if($date_of_joining){
$date_of_joining = date('d-m-Y',strtotime($date_of_joining));
}else{
$date_of_joining = "";
}
$branch = $value['branch_name'];
$channel_name = $value['channel_name'];
$designation = $value['designation'];
$level1_report = $value['level1'];
$level1_designation = $value['level1_designation'];
$level2_report = $value['level2'];
$level2_designation = $value['level2_designation'];
$vintage = $value['vintage'];
$percentage = $value['percentage'];
$yearly_ctc = $value['yearly_ctc'];
// if($emp_increment_arr[$login_code] || $emp_promotion_arr[$login_code]){
if($emp_increment_arr[$login_code]){
$increment_month = date('m-Y',strtotime($emp_increment_arr[$login_code]['increment_month']));
$previous_ctc = $emp_increment_arr[$login_code]['previous_ctc'];
$hike_ctc = $emp_increment_arr[$login_code]['hike_ctc'];
$increment_vintage = $emp_increment_arr[$login_code]['increment_vintage'];
$percentage = $emp_increment_arr[$login_code]['percentage'];
}else{
$increment_month = '';
$previous_ctc = '';
$hike_ctc = '';
$increment_vintage = '';
$percentage = '';
}
if($emp_promotion_arr[$login_code]){
$promotion_month = date('m-Y',strtotime($emp_promotion_arr[$login_code]['promotion_month']));
$previous_designation = $emp_promotion_arr[$login_code]['previous_designation'];
$promoted_designation = $emp_promotion_arr[$login_code]['promoted_designation'];
$promotion_vintage = $emp_promotion_arr[$login_code]['promotion_vintage'];
}else{
$promotion_month = '';
$previous_designation = '';
$promoted_designation = '';
$promotion_vintage = '';
}
$tr_line .= "<tr onclick=increment_promotion_log('$login_code');><td>$branch</td><td>$channel_name</td><td>$login_code</td><td>$emp_name</td><td>$date_of_joining</td><td>$designation</td><td>$level1_report</td><td>$level2_report</td><td>$vintage</td><td>$yearly_ctc</td><td>$increment_month</td><td>$increment_vintage</td><td>$previous_ctc</td><td>$hike_ctc</td><td>$percentage</td><td>$promotion_month</td><td>$promotion_vintage</td><td>$previous_designation</td><td>$promoted_designation</td>
</tr>";
/*<td>$level1_designation</td><td>$level2_designation</td>*/
/*<tr onclick=increment_promotion_log('$login_code');><td>$login_code</td><td>$emp_name</td><td>$date_of_joining</td><td>$branch</td><td>$channel_name</td><td>$designation</td><td>$level1_report</td><td>$level2_report</td><td>$vintage</td><td>$increment_month</td><td>$previous_ctc</td><td>$hike_ctc</td><td>$promotion_month</td><td>$previous_designation</td><td>$promoted_designation</td>
</tr> */
// }
}
}
$table_info = "<table id = 'increment_promotion_table' class = 'table table-hover'>
<thead>
<tr>
<th>Branch</th><th>Channel</th><th>Login Code</th><th>Employee Name</th><th>DOJ</th>
<th>Designation</th><th>Level1 Report</th>
<th>Level2 Report</th><th>Vintage</th><th>Current CTC</th><th>Last Increment Month</th><th>Increment Vintage</th>
<th>Previous Ctc </th><th>Last Hike Ctc</th><th>Percentage</th>
<th>Last Promotion Month</th><th>Promotion Vintage</th><th>Previous Designation</th><th>Last Promoted Designation</th>
</tr>
</thead>
<tboady>
$tr_line
</tbody>
</table>";
/*<th>Level1 Designation</th><th>Level2 Designation</th>*/
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 increment_promotion_log(){
$login_code = $this->input->post('login_code');
// $from_month = $this->input->post('from_month');
// $to_month = $this->input->post('to_month');
// $check_date = $this->input->post('check_date');
$emp_increment_qry = 'select cw_increment.login_code,cw_employees.date_of_joining,cw_increment.previous_ctc,cw_increment.hike_ctc,cw_increment.percentage,increment_month,b.grade as current_grade,c.grade as promoted_grade,tenure from cw_increment inner join cw_employees on cw_employees.login_code = cw_increment.login_code inner join cw_grade b on b.prime_grade_id = cw_increment.current_grade inner join cw_grade c on c.prime_grade_id = cw_increment.promoted_grade where cw_employees.login_code != "" and cw_employees.employee_status = 0 and cw_increment.login_code = "'.$login_code.'" and cw_increment.trans_status = 1 ORDER BY increment_month DESC';
$emp_increment_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_increment_qry')");
$emp_increment_result = $emp_increment_info->result_array();
$emp_increment_info->next_result();
$emp_promotion_qry = 'select cw_promotion.login_code,cw_employees.date_of_joining,a.designation as previous_designation,cw_designation.designation as promoted_designation,promotion_month,b.grade as current_grade,c.grade as promoted_grade,tenure from cw_employees inner join cw_promotion on cw_employees.login_code = cw_promotion.login_code inner join cw_designation on cw_designation.prime_designation_id = cw_promotion.promoted_designation inner join cw_designation a on a.prime_designation_id = cw_promotion.designation inner join cw_grade b on b.prime_grade_id = cw_promotion.current_grade inner join cw_grade c on c.prime_grade_id = cw_promotion.promoted_grade where cw_employees.login_code != "" and cw_employees.employee_status = 0 and cw_promotion.login_code = "'.$login_code.'" and cw_promotion.trans_status = 1 ORDER BY promotion_month DESC';
$emp_promotion_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_promotion_qry')");
$emp_promotion_result = $emp_promotion_info->result_array();
$emp_promotion_info->next_result();
if($emp_promotion_result || $emp_increment_result){
echo json_encode(array('success' => TRUE,'increment_info' => $emp_increment_result,'promotion_info' => $emp_promotion_result));
}else{
echo json_encode(array('success' => FALSE,'message' => "No Data Available"));
}
}
}
?>