File: //home/cafsindia/hrms_allyindian_com/application_bk/controllers_bk07FEB2026/Home.php
<?php
/**********************************************************
Filename: Home
Description: Chart view and Chart control logic developed, highchart integration based on role.
Author: Jaffer Sathik
Created on: 10-DEC-2018
Reviewed by: Udhayakumar Anandhan (REVIEW PENDING)
Reviewed on:
Approved by:
Approved on:
-------------------------------------------------------
Modification Details: HIGHCHARTS
Modification Date: 06/12/2019
Changed by: SVK AND NEHA
Change Info: HIGHCHARTS
-------------------------------------------------------
***********************************************************/
if ( ! defined('BASEPATH')) exit('No direct script access allowed');
require_once("Action_controller.php");
class Home extends Action_controller {
public function __construct(){
parent::__construct();
$this->load->model("Homemodel");
$this->logged_id = $this->session->userdata('logged_id');
$this->logged_role = $this->session->userdata('logged_role');
$this->logged_emp_code = $this->session->userdata('logged_emp_code');
}
public function logout(){
$this->session->sess_destroy();
redirect('login');
}
//FOR UNAUTHORIZED LOGIN !
public function unauthorized(){
$logged_id = $this->session->userdata('logged_id');
$logged_user_role = $this->session->userdata('logged_user_role');
$random_number = $this->GenerateRandomNumber();
$update_qry = 'UPDATE cw_employees SET PASSWORD = "'.$random_number.'", trans_status = 0 WHERE prime_employees_id = "'.$logged_id.'"';
$query = $this->db->query("CALL sp_a_run ('UPDATE','$update_qry')");
$this->logout();
}
//CHANGING PASSWORD FOR UNAUTHORIZED LOGIN !
public function GenerateRandomNumber(){
$min = 1000;
$max = 9999;
$gen_random = rand($min, $max);
$prefix = 'UNKNOWN';
$randomNumber = $prefix . $gen_random;
return $randomNumber;
}
public function index(){
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$data['encKey'] = $this->generateKey();
$logged_id = $this->session->userdata('logged_id');
$logged_user_role = $this->session->userdata('logged_user_role');
$data['quick_header_menu'] = $this->Homemodel->get_header_menu($logged_id);
$data['quick_report_menu'] = $this->Homemodel->get_report_menu($logged_user_role);
$data['main_menu'] = $this->get_main_menu(); // FOR CALENDAR
$this->load->view('home',$data);
}
// CHECK FOR TIME OFFICE MENU
public function get_main_menu(){
$main_menu_qry = 'SELECT * FROM cw_main_menu WHERE menu_name = "Time office" and trans_status = 1 and menu_status = 1';
$main_menu_info = $this->db->query("CALL sp_a_run ('SELECT','$main_menu_qry')");
$main_menu_rslt = $main_menu_info->result_array();
$main_menu_info->next_result();
if(count($main_menu_rslt) > 0){
return 'is_exist';
}else{
return 'is_not_exist';
}
}
// TOTAL PAY DEPARTMENT WISE -> 1
public function get_dept_salary_info(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$start_date = $this->db->escape(date("Y-m-d",strtotime($this->input->post('start_date'))));
$end_date = $this->db->escape(date("Y-m-d",strtotime($this->input->post('end_date'))));
$branch = $this->input->post('branch');
if($branch){
$branch = 'and cw_employees.branch = "'.$branch.'" ';
}
//get function mapping data
$get_map_qry = 'select * from cw_payroll_function_map where trans_status=1';
$get_map_info = $this->db->query("CALL sp_a_run ('SELECT','$get_map_qry')");
$get_map_rslt = $get_map_info->result_array();
$get_map_info->next_result();
foreach($get_map_rslt as $arr){
$get_map_arr[$arr['loc_name']] = $arr;
}
$fixed_ctc_col = $get_map_arr['fixed_ctc']['db_column'];
if(!$fixed_ctc_col){
echo json_encode(array('success'=> false,'message'=>'Please map the fixed CTC column in module setting.'));
exit(0);
}
$dept_salary_info = $this->db->query("CALL sp_a_run ('SELECT','select IFNULL(SUM(cw_transactions.$fixed_ctc_col),0) as net_pay,cw_department.department from cw_employees inner join cw_transactions on cw_transactions.employee_code = cw_employees.employee_code join cw_department on cw_department.prime_department_id = cw_employees.department where cw_employees.trans_status = 1 and date_format(str_to_date(concat(\"01-\",cw_transactions.transactions_month),\"%d-%m-%Y\"),\"%Y-%m-%d\") BETWEEN '$start_date' AND '$end_date' $branch group by cw_department.department')");
$dept_salary_rlst = $dept_salary_info->result();
$dept_salary_info->next_result();
$rows = array();
$rows['name'] = "Salary";
foreach($dept_salary_rlst as $rlst){
$net_pay = $rlst->net_pay;
$department = $rlst->department;
$rows['data'][] = array("name"=>$department,"y"=>$net_pay);
}
$department = array();
array_push($department,$rows);
echo json_encode(array('series' => $department),JSON_NUMERIC_CHECK);
}
// TOTAL SALARY MONTH WISE -> 2
public function total_salary_month_chart(){
$start_date = date("Y-m-d",strtotime($this->input->get('start_date')));
$end_date = date("Y-m-d",strtotime($this->input->get('end_date')));
$branch = $this->input->get('branch');
if($branch){
$branch = 'and cw_employees.branch = "'.$branch.'" ';
}
//get function mapping data
$get_map_qry = 'select * from cw_payroll_function_map where trans_status=1';
$get_map_info = $this->db->query("CALL sp_a_run ('SELECT','$get_map_qry')");
$get_map_rslt = $get_map_info->result_array();
$get_map_info->next_result();
foreach($get_map_rslt as $arr){
$get_map_arr[$arr['loc_name']] = $arr;
}
$fixed_ctc_col = $get_map_arr['fixed_ctc']['db_column'];
$view_data = $this->db->query("CALL sp_a_run ('SELECT','SELECT ROUND(IFNULL(SUM(cw_transactions.$fixed_ctc_col),0)) as total_pay,transactions_month from cw_transactions JOIN cw_employees on cw_transactions.employee_code = cw_employees.employee_code where cw_transactions.trans_status = 1 and date_format(str_to_date(concat(\"01-\",cw_transactions.transactions_month),\"%d-%m-%Y\"),\"%Y-%m-%d\") BETWEEN \"$start_date\" AND \"$end_date\" $branch group by transactions_month order by date_format(str_to_date(concat(\"01-\",cw_transactions.transactions_month),\"%d-%m-%Y\"),\"%Y-%m-%d\")')");
$view_result = $view_data->result_array();
$view_data->next_result();
$total_salary['name'] = 'Total Salary';
$series1['name'] = 'Salary';
foreach($view_result as $key => $value){
$salary = $value['total_pay'];
$transactions_month = date("M-Y",strtotime("01-".$value['transactions_month']));
$total_salary['data'][] = $transactions_month;
$series1['data'][] = $salary;
}
$result = array();
array_push($result,$total_salary);
array_push($result,$series1);
print json_encode($result,JSON_NUMERIC_CHECK);
}
//EMPLOYEES AGE DISTRIBUTION
public function get_employee_age_distribution(){
$start_date = $this->db->escape(date("d-m-Y",strtotime($this->input->post('start_date'))));
$end_date = $this->db->escape(date("d-m-Y",strtotime($this->input->post('end_date'))));
$dept_salary_by_age_info = 'select GROUP_CONCAT(IFNULL(cw_employees.emp_age,0)) as emp_age,cw_gender.gender from cw_employees inner join cw_gender on cw_gender.prime_gender_id = cw_employees.gender where cw_employees.trans_status = 1 group by cw_employees.gender';
$dept_salary_by_age_info = $this->db->query("CALL sp_a_run ('SELECT','$dept_salary_by_age_info')");
$dept_salary_by_age_rlst = $dept_salary_by_age_info->result();
$dept_salary_by_age_info->next_result();
$department = array();
foreach($dept_salary_by_age_rlst as $rlst){
$rows = array();
$gender = $rlst->gender;
$emp_age = $rlst->emp_age;
$rows['name'] = $gender;
$rows['data'] = $emp_age;
//$rows['data'] = array("name"=>$gender,"y"=>$emp_age);
array_push($department,$rows);
}
echo json_encode(array('series' => $department),JSON_NUMERIC_CHECK);
}
//MONTH WISE EMPLOYEE COUNT ->NB[08-09-23]
public function monthwise_empcount(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$start_date = date('Y-m-d',strtotime($this->input->post('start_date')));
$end_date = date('Y-m-d',strtotime($this->input->post('end_date')));
$branch = $this->input->post('branch');
if($branch){
$branch = 'AND cw_employees.branch = "'.$branch.'" ';
}
//RECURSIVELY GETTING MONTH
$date_array = array();
while($start_date <= $end_date){
$date_array[] = $start_date;
$start_date = date('Y-m-d', strtotime("+1 month", strtotime($start_date)));
}
$chart_array = array();
foreach($date_array as $key => $date){
$date_of_month = date('Y-m-01',strtotime($date));
$active_qry = "SELECT '$date_of_month' AS date_of_month,COUNT(cw_employees.employee_code) AS employee_count,DATE_FORMAT('$date_of_month', '%m-%Y') AS month_year FROM cw_employees LEFT JOIN cw_category ON cw_category.prime_category_id = cw_employees.role WHERE DATE_FORMAT(cw_employees.date_of_joining, '%Y-%m-%d') <= LAST_DAY('$date_of_month') AND (cw_employees.last_working_date IS NOT NULL OR DATE_FORMAT(cw_employees.last_working_date, '%Y-%m-%d') >= '$date_of_month' OR cw_employees.last_working_date IS NULL) AND cw_employees.role != 1 {$branch} GROUP BY date_of_month";
$active_info = $this->db->query($active_qry);
if(!$active_info){
die("Error executing query: " . $this->db->error());
}
$active_rslt = $active_info->result_array();
$active_info->next_result();
$month_year = $active_rslt[0]['month_year'];
$emp_count = $active_rslt[0]['employee_count'];
$chart_array[$month_year] = $emp_count;
}
//FOR CHART
$rows = array();
$rows1 = array();
$rows['name'] = 'TOTAL EMPLOYEES';
$rows1['name'] = 'Count';
foreach($chart_array as $month => $count){
$active_month = date("M-Y", strtotime("01-".$month));
$rows['data'][] = $active_month;
$rows1['data'][] = $count;
}
$shift_info = array();
array_push($shift_info,$rows);
array_push($shift_info,$rows1);
echo json_encode($shift_info,JSON_NUMERIC_CHECK);
//CODE COMMENTED BECOZ RECURSIVE NOT WORKED IN AEQ LIVE
// $start_date = $this->db->escape(date('Y-m-d',strtotime($this->input->post('start_date'))));
// $end_date = $this->db->escape(date('Y-m-d',strtotime($this->input->post('end_date'))));
// $branch = $this->input->post('branch');
// if($branch){
// $branch = 'AND cw_employees.branch = "'.$branch.'" ';
// }
// //DATE RANGE -> IS TO FIND EACH MONTH LAST DATE AND FIRST DATE.
// //DOJ <= EACH MONTH LAST DATE
// //LWD >= EACH MONTH FIRST DATE
// $active_qry = " WITH RECURSIVE DateRange AS (SELECT MIN(DATE_FORMAT($start_date, '%Y-%m-01')) AS date_of_month UNION ALL SELECT DATE_FORMAT(DATE_ADD(date_of_month, INTERVAL 1 MONTH), '%Y-%m-01') FROM DateRange WHERE DATE_FORMAT(DATE_ADD(date_of_month, INTERVAL 1 MONTH), '%Y-%m-01') <= $end_date)
// SELECT date_of_month,
// COUNT(cw_employees.employee_code) AS employee_count,
// DATE_FORMAT(date_of_month, '%Y-%m') AS month_year
// FROM DateRange
// LEFT JOIN cw_employees
// ON DATE_FORMAT(cw_employees.date_of_joining, '%Y-%m-%d') <= LAST_DAY(date_of_month)
// AND(
// (cw_employees.last_working_date = '0000-00-00' AND cw_employees.last_working_date IS NOT NULL)
// OR(cw_employees.last_working_date != '0000-00-00' AND DATE_FORMAT(cw_employees.last_working_date, '%Y-%m-%d') >= date_of_month) OR cw_employees.last_working_date IS NULL)
// JOIN cw_category ON cw_category.prime_category_id = cw_employees.role
// WHERE cw_employees.role != 1 {$branch}
// GROUP BY date_of_month";
// $active_info = $this->db->query($active_qry);
// if(!$active_info){
// die("Error executing query: " . $this->db->error());
// }
// $active_rslt = $active_info->result_array();
// $active_info->next_result();
// //FOR CHART
// $rows = array();
// $rows1 = array();
// $rows['name'] = 'TOTAL EMPLOYEES';
// $rows1['name'] = 'Count';
// foreach($active_rslt as $rlst){
// $count = $rlst['employee_count'];
// $shift_name = date('M-Y', strtotime($rlst['month_year']));
// $rows['data'][] = $shift_name;
// $rows1['data'][] = $count;
// }
// $shift_info = array();
// array_push($shift_info,$rows);
// array_push($shift_info,$rows1);
// echo json_encode($shift_info,JSON_NUMERIC_CHECK);
}
//GENDER BASED DRILL DOWN WITH BRANCH !!!
public function gen_distribution_chart(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$start_date = date("Y-m-d", strtotime($this->input->post('start_date')));
$end_date = date("Y-m-d", strtotime($this->input->post('end_date')));
$branch = $this->input->post('branch');
if($branch){
$branch = 'and cw_employees.branch = "'.$branch.'" ';
}
$department_query = "SELECT IFNULL(COUNT(emp_name), 0) AS count,cw_gender.gender AS gender,
cw_gender.prime_gender_id AS id FROM cw_employees INNER JOIN cw_gender ON cw_employees.gender = cw_gender.prime_gender_id WHERE cw_employees.termination_status = 0 AND cw_employees.supplementary_status = 0 AND cw_employees.trans_status = 1 $branch GROUP BY cw_gender.gender, cw_gender.prime_gender_id";
$department_info = $this->db->query($department_query);
$department_result = $department_info->result();
$drill_list = array();
$rows = array();
$rows['name'] = "Gender Distribution";
foreach($department_result as $key => $rlst){
$gender = $rlst->gender;
$gender_count = $rlst->count;
$prime_id = $rlst->id;
$rows['data'][] = array("name" => $gender,"y" =>$gender_count,"drilldown" =>$prime_id );
$gender_results = $this->get_department_result($prime_id, $start_date, $end_date,$branch );
$drill = array();
$drill['name'] = $gender;
$drill['id'] = $prime_id;
foreach($gender_results as $key => $value){
$branch_name = $value->branch;
$sts_counts = $value->emp_count;
$drill['data'][] = array($branch_name, $sts_counts);
array_push($drill_list, $drill);
}
}
$rslt = array();
array_push($rslt, $rows);
echo json_encode(array('series' => $rslt, 'drilldown' => $drill_list), JSON_NUMERIC_CHECK);
}
public function get_department_result($prime_id,$start_date,$end_date,$branch ){
$gender_qry = 'SELECT IFNULL(count(emp_name),0) as emp_count,cw_gender.gender as gender,cw_branch.branch as branch FROM cw_employees INNER join cw_gender INNER JOIN cw_branch on cw_employees.gender = cw_gender.prime_gender_id AND cw_branch.prime_branch_id = cw_employees.branch where cw_employees.termination_status = 0 and cw_employees.supplementary_status = 0 and cw_employees.trans_status = 1 and cw_employees.gender = "'.$prime_id.'" '.$branch.' GROUP BY cw_gender.gender,cw_branch.branch';
$gender_infos = $this->db->query("CALL sp_a_run ('SELECT','$gender_qry')");
$gender_results = $gender_infos->result();
$gender_infos->next_result();
return $gender_results;
}
public function get_active_status(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$start_date = $this->db->escape(date("Y-m-d",strtotime($this->input->post('start_date'))));
$end_date = $this->db->escape(date("Y-m-d",strtotime($this->input->post('end_date'))));
$branch = $this->input->post('branch');
if($branch){
$branch = 'and cw_employees.branch = "'.$branch.'" ';
}
$active_qry = $this->db->query("CALL sp_a_run ('SELECT','SELECT IFNULL(COUNT(emp_name),0) AS emp_count,DATE_FORMAT(date_of_joining, \"%m-%Y\") as months from cw_employees where cw_employees.role != 1 AND date_format(date_of_joining,\"%Y-%m-%d\") BETWEEN '$start_date' AND '$end_date' $branch GROUP BY months ORDER BY date_format(str_to_date(concat(\"01-\",months),\"%d-%m-%Y\"),\"%Y-%m-%d\")')");
$onbard_data_rslt = $active_qry->result();
$active_qry->next_result();
$rows = array();
$rows1 = array();
$rows['name'] = 'Total Employees';
$rows1['name'] = 'Joined';
foreach($onbard_data_rslt as $rlst){
$rows['data'][] = date("M-Y",strtotime("01-".$rlst->months));
$rows1['data'][] = $rlst->emp_count;
}
$onboard_info = array();
array_push($onboard_info,$rows);
array_push($onboard_info,$rows1);
echo json_encode($onboard_info,JSON_NUMERIC_CHECK);
}
public function get_inactive_status(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$start_date = $this->db->escape(date("Y-m-d",strtotime($this->input->post('start_date'))));
$end_date = $this->db->escape(date("Y-m-d",strtotime($this->input->post('end_date'))));
$branch = $this->input->post('branch');
if($branch){
$branch = 'and cw_employees.branch = "'.$branch.'" ';
}
$active_qry = $this->db->query("CALL sp_a_run ('SELECT','SELECT IFNULL(COUNT(emp_name),0) AS emp_count,DATE_FORMAT(last_working_date, \"%m-%Y\") as months from cw_employees where cw_employees.role != 1 AND date_format(last_working_date,\"%Y-%m-%d\") BETWEEN '$start_date' AND '$end_date' $branch GROUP BY months ORDER BY date_format(str_to_date(concat(\"01-\",months),\"%d-%m-%Y\"),\"%Y-%m-%d\") ')");
$service_data_rslt = $active_qry->result();
$active_qry->next_result();
$rows = array();
$rows1 = array();
$rows['name'] = 'TOTAL EMPLOYEES';
$rows1['name'] = 'Resigned';
foreach($service_data_rslt as $rlst){
$rows['data'][] = date("M-Y",strtotime("01-".$rlst->months));
$rows1['data'][] = $rlst->emp_count;
}
$service_info = array();
array_push($service_info,$rows);
array_push($service_info,$rows1);
echo json_encode($service_info,JSON_NUMERIC_CHECK);
}
public function get_min_salary(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$month = $this->input->post('month');
$start_date = date("Y-m-01", strtotime("01-" . $month));
$end_date = date("Y-m-t", strtotime("01-" . $month));
//get function mapping data
$get_map_qry = 'select * from cw_payroll_function_map where trans_status=1';
$get_map_info = $this->db->query("CALL sp_a_run ('SELECT','$get_map_qry')");
$get_map_rslt = $get_map_info->result_array();
$get_map_info->next_result();
foreach($get_map_rslt as $arr){
$get_map_arr[$arr['loc_name']] = $arr;
}
$fixed_ctc_col = $get_map_arr['fixed_ctc']['db_column'];
$active_qry = 'SELECT ROUND(IFNULL(MIN(cw_transactions.'.$fixed_ctc_col.'),0)) AS total_pay,transactions_month,cw_position.position_name FROM cw_transactions INNER JOIN cw_employees INNER JOIN cw_position ON cw_transactions.employee_code = cw_employees.employee_code AND cw_position.prime_position_id = cw_employees.designation WHERE cw_transactions.trans_status = 1 and cw_employees.termination_status = 0 AND DATE_FORMAT(STR_TO_DATE(CONCAT("01-",cw_transactions.transactions_month),"%d-%m-%Y"),"%Y-%m-%d") BETWEEN "'.$start_date.'" AND "'.$end_date.'" GROUP BY transactions_month,cw_position.prime_position_id ORDER BY DATE_FORMAT(STR_TO_DATE(CONCAT("01-",cw_transactions.transactions_month),"%d-%m-%Y"),"%Y-%m-%d")';
$active_data_info = $this->db->query("CALL sp_a_run ('SELECT','$active_qry')");
$onbard_data_rslt = $active_data_info->result();
$active_data_info->next_result();
$rows = array();
$rows1 = array();
$rows['name'] = 'TOTAL EMPLOYEES';
$rows1['name'] = 'Minimum';
foreach($onbard_data_rslt as $rlst){
$rows['data'][] = $rlst->position_name;
$rows1['data'][] = $rlst->total_pay;
}
$onboard_info = array();
array_push($onboard_info,$rows);
array_push($onboard_info,$rows1);
echo json_encode($onboard_info,JSON_NUMERIC_CHECK);
}
public function get_max_salary(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
//get function mapping data
$get_map_qry = 'select * from cw_payroll_function_map where trans_status=1';
$get_map_info = $this->db->query("CALL sp_a_run ('SELECT','$get_map_qry')");
$get_map_rslt = $get_map_info->result_array();
$get_map_info->next_result();
foreach($get_map_rslt as $arr){
$get_map_arr[$arr['loc_name']] = $arr;
}
$fixed_ctc_col = $get_map_arr['fixed_ctc']['db_column'];
$month = $this->input->post('month');
$start_date = date("Y-m-01", strtotime("01-" . $month));
$end_date = date("Y-m-t", strtotime("01-" . $month));
$active_qry = 'SELECT ROUND(IFNULL(MAX(cw_transactions.'.$fixed_ctc_col.'),0)) AS total_pay,transactions_month,cw_position.position_name FROM cw_transactions INNER JOIN cw_employees INNER JOIN cw_position ON cw_transactions.employee_code = cw_employees.employee_code AND cw_position.prime_position_id = cw_employees.designation WHERE cw_transactions.trans_status = 1 AND DATE_FORMAT(STR_TO_DATE(CONCAT("01-",cw_transactions.transactions_month),"%d-%m-%Y"),"%Y-%m-%d") BETWEEN "'.$start_date.'" AND "'.$end_date.'" and cw_employees.termination_status = 0 GROUP BY transactions_month,cw_position.prime_position_id ORDER BY DATE_FORMAT(STR_TO_DATE(CONCAT("01-",cw_transactions.transactions_month),"%d-%m-%Y"),"%Y-%m-%d")';
$active_data_info = $this->db->query("CALL sp_a_run ('SELECT','$active_qry')");
$service_data_rslt = $active_data_info->result();
$active_data_info->next_result();
$rows = array();
$rows1 = array();
$rows['name'] = 'TOTAL EMPLOYEES';
$rows1['name'] = 'Maximum';
foreach($service_data_rslt as $rlst){
$rows['data'][] = $rlst->position_name;
$rows1['data'][] = $rlst->total_pay;
}
$service_info = array();
array_push($service_info,$rows);
array_push($service_info,$rows1);
echo json_encode($service_info,JSON_NUMERIC_CHECK);
}
public function employees_Attrition_chart(){ //NB->[11-09-23]
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$start_date = date('Y-m-d',strtotime($this->input->post('start_date')));
$end_date = date('Y-m-d',strtotime($this->input->post('end_date')));
$branch = $this->input->post('branch');
if($branch){
$branch = 'AND cw_employees.branch = "'.$branch.'" ';
}
//RECURSIVELY GETTING MONTH
$date_array = array();
while($start_date <= $end_date){
$date_array[] = $start_date;
$start_date = date('Y-m-d', strtotime("+1 month", strtotime($start_date)));
}
$chart_array = array();
foreach($date_array as $key => $date){
$date_of_month = date('Y-m-01',strtotime($date));
$active_qry = "SELECT '$date_of_month' AS date_of_month,COUNT(cw_employees.employee_code) AS employee_count,DATE_FORMAT('$date_of_month', '%Y-%m') AS month_year FROM cw_employees LEFT JOIN cw_category ON cw_category.prime_category_id = cw_employees.role WHERE DATE_FORMAT(cw_employees.date_of_joining, '%Y-%m-%d') <= LAST_DAY('$date_of_month') AND (cw_employees.last_working_date IS NOT NULL OR DATE_FORMAT(cw_employees.last_working_date, '%Y-%m-%d') >= '$date_of_month' OR cw_employees.last_working_date IS NULL) AND cw_employees.role != 1 {$branch} GROUP BY date_of_month";
$active_info = $this->db->query($active_qry);
if(!$active_info){
die("Error executing query: " . $this->db->error());
}
$active_rslt = $active_info->result_array();
$active_info->next_result();
$date_of_month = $active_rslt[0]['date_of_month'];
$month_year = $active_rslt[0]['month_year'];
$emp_count = $active_rslt[0]['employee_count'];
$chart_array[] = array('date_of_month' => $date_of_month,'active_month' => $month_year,'active_employees' => $emp_count);
}
$start_date = $this->db->escape(date("Y-m-d",strtotime($this->input->post('start_date'))));
$end_date = $this->db->escape(date("Y-m-d",strtotime($this->input->post('end_date'))));
//CALCULATE INACTIVE EMPLOYEES COUNT
$inactive_qry = $this->db->query("CALL sp_a_run ('SELECT','SELECT DATE_FORMAT(last_working_date, \"%m-%Y\") as inactive_month,IFNULL(COUNT(emp_name),0) AS inactive from cw_employees where termination_status = 1 $branch and date_format(last_working_date,\"%Y-%m-%d\") BETWEEN '$start_date' AND '$end_date' and cw_employees.role != 1 GROUP BY inactive_month ORDER BY date_format(str_to_date(concat(\"01-\",inactive_month),\"%d-%m-%Y\"),\"%Y-%m-%d\")')");
$inactive_rslt = $inactive_qry->result_array();
$inactive_qry->next_result();
//CALCULATE AVERAGE ACTIVE EMPLOYEES
$active_employees = [];
for($i = 0; $i < count($chart_array); $i++){
$active_month = $chart_array[$i]['active_month'];
$active_month = date('m-Y', strtotime($active_month));
$current_month_count = $chart_array[$i]['active_employees'];
$previous_month_count = $chart_array[$i - 1]['active_employees'];
$average = ($current_month_count + $previous_month_count) / 2;
$active_employees[] = ['active_month' => $active_month, 'active' => $average];
}
//CALCULATE ATTRITION RATE
$result = [];
foreach($active_employees as $key => $active){
$ratio = (!empty($active['active'])) ? round((($inactive_rslt[$key]['inactive'] ?? 0) / $active['active']) * 100, 2): 0;
$result[] = ['month' => $active['active_month'], 'rate' => $ratio];
}
//FOR CHART
$rows = array();
$rows1 = array();
$rows['name'] = 'TOTAL EMPLOYEES';
$rows1['name'] = 'Rate %';
foreach($result as $rlst){
$count = $rlst['rate'];
$shift_name = date("M-Y",strtotime("01-".$rlst['month']));
$rows['data'][] = $shift_name;
$rows1['data'][] = $count;
}
$shift_info = array();
array_push($shift_info,$rows);
array_push($shift_info,$rows1);
echo json_encode($shift_info,JSON_NUMERIC_CHECK);
}
/**********************************************************
//CADD
***********************************************************/
// FUNCTION FOR GENDER WISE COUNT AND PERCENTAGE
public function gen_distribution_chart_cadd(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$start_date = date("Y-m-d",strtotime($this->input->post('start_date')));
$end_date = date("Y-m-d",strtotime($this->input->post('end_date')));
$view_data = $this->db->query("CALL sp_a_run ('SELECT','SELECT COUNT(cw_gender.gender) AS gender_count,cw_gender.gender FROM cw_employees INNER join cw_gender on cw_employees.gender = cw_gender.prime_gender_id where cw_employees.trans_status = 1 and cw_employees.termination_status=0 GROUP BY cw_gender.gender')");
$view_result = $view_data->result();
$view_data->next_result();
$rows = array();
$rows['name'] = "Gender";
foreach($view_result as $rlst){
$gender = $rlst->gender;
$emp_count = $rlst->gender_count;
$rows['data'][] = array("name"=>$gender,"y"=>$emp_count);
}
$gender = array();
array_push($gender,$rows);
echo json_encode(array('series' => $gender),JSON_NUMERIC_CHECK);
}
//FUNCTION FOR AGE WISE EMPLOYEE COUNT
public function age_wise_emp_count(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$start_date = $this->db->escape(date('Y-m-d',strtotime($this->input->post('start_date'))));
$end_date = $this->db->escape(date('Y-m-d',strtotime($this->input->post('end_date'))));
$view_data = $this->db->query("CALL sp_a_run ('SELECT','SELECT cw_employees.emp_age,COUNT(cw_employees.employee_code) AS emp_count FROM cw_employees WHERE cw_employees.trans_status=1 GROUP BY cw_employees.emp_age')");
$view_result = $view_data->result_array();
$view_data->next_result();
$total_pay['name'] = 'Employee Count';
$series1['name'] = 'Age';
foreach($view_result as $key => $value){
$count = $value['emp_count'];
$age = strval($value['emp_age']);
$total_pay['data'][] = $count;
$series1['data'][] = $age;
}
$result = array();
array_push($result,$series1);
array_push($result,$total_pay);
print json_encode($result,JSON_NUMERIC_CHECK);
}
// FUNCTION FOR LOCATION WISE NETPAY
public function get_netpay_loc_chart(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$start_date = $this->db->escape(date("Y-m-d",strtotime($this->input->post('start_date'))));
$end_date = $this->db->escape(date("Y-m-d",strtotime($this->input->post('end_date'))));
$dept_salary_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT SUM(cw_transactions.net_pay)AS net_pay,cw_location.location FROM cw_transactions INNER JOIN cw_employees ON cw_employees.employee_code = cw_transactions.employee_code INNER JOIN cw_location ON cw_location.prime_location_id = cw_employees.location where cw_employees.trans_status =1 and date_format(str_to_date(concat(\"01-\",cw_transactions.transactions_month),\"%d-%m-%Y\"),\"%Y-%m-%d\") BETWEEN '$start_date' AND '$end_date' GROUP BY cw_location.location')");
$dept_salary_rlst = $dept_salary_info->result();
$dept_salary_info->next_result();
$rows = array();
$rows['name'] = "Location";
foreach($dept_salary_rlst as $rlst){
$net_pay = $rlst->net_pay;
$location = $rlst->location;
$rows['data'][] = array("name"=>$location,"y"=>$net_pay);
}
$department = array();
array_push($department,$rows);
echo json_encode(array('series' => $department),JSON_NUMERIC_CHECK);
}
// FUNCTION FOR BRAND WISE NETPAY
public function brand_netpay_info(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$start_date = $this->db->escape(date("Y-m-d",strtotime($this->input->post('start_date'))));
$end_date = $this->db->escape(date("Y-m-d",strtotime($this->input->post('end_date'))));
$brand_netpay_data = $this->db->query("CALL sp_a_run ('SELECT','SELECT SUM(cw_transactions.net_pay)AS net_pay,cw_brand.brand FROM cw_transactions INNER JOIN cw_employees ON cw_employees.employee_code = cw_transactions.employee_code INNER JOIN cw_brand ON cw_brand.prime_brand_id = cw_employees.brand WHERE cw_employees.trans_status = 1 and date_format(str_to_date(concat(\"01-\",cw_transactions.transactions_month),\"%d-%m-%Y\"),\"%Y-%m-%d\") BETWEEN '$start_date' AND '$end_date' GROUP BY cw_employees.brand')");
$brand_netpay_rlst = $brand_netpay_data->result();
$brand_netpay_data->next_result();
$rows = array();
$rows['name'] = "Brand";
foreach($brand_netpay_rlst as $rlst){
$net_pay = $rlst->net_pay;
$brand = $rlst->brand;
$rows['data'][] = array("name"=>$brand,"y"=>$net_pay);
}
$department = array();
array_push($department,$rows);
echo json_encode(array('series' => $department),JSON_NUMERIC_CHECK);
}
// FUNCTION FOR BRAND WISE EMPLOYEE COUNT
public function get_brandwise_employee_count_info(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$start_date = date("d-m-Y",strtotime($this->input->post('start_date')));
$end_date = date("d-m-Y",strtotime($this->input->post('end_date')));
$query = 'SELECT COUNT(cw_employees.employee_code) AS employee_count,cw_brand.brand FROM cw_transactions INNER JOIN cw_employees ON cw_employees.employee_code = cw_transactions.employee_code INNER JOIN cw_brand ON cw_brand.prime_brand_id = cw_employees.brand WHERE cw_employees.trans_status = 1 and cw_employees.termination_status=0 GROUP BY cw_employees.brand';
$brand_emp_count_info = $this->db->query("CALL sp_a_run ('SELECT','$query')");
$brand_emp_count_rlst = $brand_emp_count_info->result();
$brand_emp_count_info->next_result();
$rows = array();
$rows1 = array();
$rows['name'] = 'TOTAL EMPLOYEES';
$rows1['name'] = 'BRAND';
foreach($brand_emp_count_rlst as $rlst){
$count = $rlst->employee_count;
$brand = $rlst->brand;
$rows['data'][] = $brand;
$rows1['data'][] = $count;
}
$designation_info = array();
array_push($designation_info,$rows);
array_push($designation_info,$rows1);
echo json_encode($designation_info,JSON_NUMERIC_CHECK);
}
// FUNCTION FOR LOCATION WISE EMPLOYEE COUNT
public function employees_count_chart(){
$start_date = date("Y-m-d",strtotime($this->input->get('start_date')));
$end_date = date("Y-m-d",strtotime($this->input->get('end_date')));
//echo "BSK $start_date :: $end_date"; die;
$view_data = $this->db->query("CALL sp_a_run ('SELECT','SELECT COUNT(cw_employees.employee_code)AS employee_count,cw_location.location FROM cw_employees INNER JOIN cw_location ON cw_location.prime_location_id = cw_employees.location INNER JOIN cw_transactions ON cw_transactions.employee_code = cw_employees.employee_code and cw_employees.termination_status=0 and date_format(cw_employees.date_of_joining,\"%Y-%m-%d\") BETWEEN \"$start_date\" AND \"$end_date\" GROUP BY cw_location.location')");
$view_result = $view_data->result_array();
$view_data->next_result();
$total_emp['name'] = 'Total Employees';
$series1['name'] = 'Location';
foreach($view_result as $key => $value){
$employees = $value['employee_count'];
$location = $value['location'];
$total_emp['data'][] = $location;
$series1['data'][] = $employees;
}
$result = array();
array_push($result,$total_emp);
array_push($result,$series1);
print json_encode($result,JSON_NUMERIC_CHECK);
}
//CALENDAR
public function get_calendar(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$employee_code = $this->input->post('employee_code');
if(!$employee_code){
$employee_code = $this->logged_emp_code;
}
$month = $this->input->post('month');
$year = $this->input->post('year');
if($month < 10){
$month = '0'.$month;
}
$start_date = "$year-$month-01";
$end_date = "$year-$month-31";
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL,base_url()."app/api_controller.php?frm=calendar");
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS,"values=$employee_code,$start_date,$end_date");
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$output = curl_exec($ch);
curl_close($ch);
$output = json_decode($output);
if ($output->status) {
$total_days = $output->total_days;
$tos_start_date = $output->tos;
$events = $output->events;
$api_events_arr = $output->api_events_arr;
$leave_bal = $this->create_leave_bal($output->leave_bal); // LEAVE BALANCE TABLE
$info_div = $this->create_info_div($output->info_arr); // CREATE INFO DIV
echo json_encode(array('status' => true, 'message' => 'Calendar Fetch Success','events' => $events ,'api_events_arr' => $api_events_arr, 'total_days' => $total_days , 'tos' => $tos_start_date , 'info_div' => $info_div, "leave_bal" => $leave_bal));
}
else {
echo json_encode(array('status' => false , 'message' => 'Calendar Fetch Failed', 'events' => ''));
}
}
# INFO DIV BUILDING PROCESS
public function create_info_div($info_arr){
if($info_arr){
$info_div = '<span class = "">Total : <span class ="total_days">'.$info_arr->total_days.'</span></span> <span class = "" style="">Present : <span class ="total_days">'.$info_arr->present_days.'</span></span>   <span class = "">Absent : <span class ="total_days">'.$info_arr->absent_days.'</span></span>  <span class = "" style="">Onduty : <span class ="total_days">'.$info_arr->onduty_days.'</span></span><br><span class = "">Holidays :<span class ="total_days">'.$info_arr->holiday_days.'</span></span>  <span class = "">Weekoff :<span class ="total_days">'.$info_arr->weekoff_days.'</span></span>  <span class = "">Leave : <span class ="total_days">'.$info_arr->leave_days.'</span></span>  <span class = "">Invalid : <span class ="total_days">'.$info_arr->invalid_days.'</span></span>';
}
return $info_div;
}
# MY LEAVE BALACNCE TABLE BUILDING PROCESS
public function create_leave_bal($leave_bal_arr){
if($leave_bal_arr){
$leave_creation_result = $leave_bal_arr->leave_creation_result;
$leave_result = $leave_bal_arr->leave_result;
$table = "";
$table_list = "";
foreach ($leave_creation_result as $key => $value) {
$leave_name = strtolower($value->leave_name);
foreach ($leave_result as $key => $leave_value) {
$credit_name = $leave_name."_credit";
$debit_name = $leave_name."_debit";
$used_name = "used_".$leave_name;
$encash_name = "encash_".$leave_name;
$pend_name = "pending_".$leave_name;
$leave_type = $leave_value->$leave_name;
$credit = $leave_value->$credit_name;
$debit = $leave_value->$debit_name;
$encash = $leave_value->$encash_name;
$leave_used = $leave_value->$used_name;
$pending = $leave_value->$pend_name;
$total = $credit + $leave_type;
$used = $leave_used + $pending;
$balance = $total - $used - $debit -$encash;
$table_list .= "<tr>
<td>".$value->leave_description."</td>
<td>".$total."</td>
<td>".$debit."</td>
<td>".$encash."</td>
<td>".$used."</td>
<td><b style='color:green'>".$balance."</b></td>
</tr>";
}
}
if(count($leave_result) <= 0){
$table_list = '<tbody><tr><td colspan="5" style = "text-align:center;">No Data Available</td></tr></tbody>';
}
$table = "<div id='view_table_table'>
<table id='view_table' style='width:100%' class='table table-hover'>
<thead>
<tr style='color: #FFFFFF;'>
<th style='text-align:center;'>Leave Type</th>
<th style='text-align:center;'>Total</th>
<th style='text-align:center;'>Debit</th>
<th style='text-align:center;'>Encash</th>
<th style='text-align:center;'>Used</th>
<th style='text-align:center;'>Balance</th>
</tr>
</thead>
<tbody id='leave_body'>
$table_list
</tbody>
</table></div>";
}
return $table;
}
# EMPLOYEE CODE AUTO COMPLETE
public function emp_suggest(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$search_term = $this->input->post_get('term');
$final_qry = 'SELECT employee_code,emp_name FROM `cw_employees` WHERE trans_status = 1 and termination_status = 0 and (employee_code like "%'.$search_term.'%" or emp_name like "%'.$search_term.'%") '; // => [MS 25-07-2024]
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
foreach($final_result as $rslt){
$employee_code = $rslt->employee_code;
$emp_name = $rslt->emp_name;
$suggestions[] = array('value' => $employee_code, 'label' => "$employee_code - $emp_name");
}
if(empty($suggestions)){
$suggestions[] = array('value' => " ", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
}
?>