File: /home/cafsindia/hrms_cafsinfotech_in/application_bk14FEB2026/controllers/Timeoffice_dashboard.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Timeoffice_dashboard extends Action_controller{
public function __construct(){
parent::__construct('timeoffice_dashboard');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$data['encKey'] = $this->generateKey();
$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[""] = "-- All --";
foreach($branch_result as $for){
$branch_id = $for->prime_branch_id;
$branch_name = $for->branch;
$branch_list[$branch_id] = $branch_name;
}
$data['branch_list'] = $branch_list;
$this->load->view("$this->control_name/manage",$data);
}
//GET EMPLOYEE MASTER COUNT
public function get_emp_master_count(){
$from_date = date("Y-m-01");
$to_date = date('Y-m-t');
$emp_master_qry = 'SELECT SUM(CASE WHEN cw_employees.trans_status = 1 THEN 1 ELSE 0 END) total_employees,SUM(CASE WHEN termination_status = 0 THEN 1 ELSE 0 END) active_employees,SUM(CASE WHEN termination_status = 0 and date_of_joining between "'.$from_date.'" and "'.$to_date.'" THEN 1 ELSE 0 END) joined_employees,SUM(CASE WHEN termination_status = 1 and last_working_date between "'.$from_date.'" and "'.$to_date.'" THEN 1 ELSE 0 END) left_employees,COUNT(DISTINCT(CASE WHEN department != 0 and termination_status = 0 THEN department ELSE 0 END)) total_departments,COUNT(DISTINCT(CASE WHEN designation != 0 and termination_status = 0 THEN designation ELSE 0 END)) total_sections FROM cw_employees JOIN cw_category ON cw_category.prime_category_id = cw_employees.role WHERE role != 1';
$emp_master_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_master_qry')");
$emp_master_rslt = $emp_master_info->result_array();
$emp_master_info->next_result();
echo json_encode(array("success" => TRUE,'table_data' => $emp_master_rslt[0]));
}
public function table_qry($from_date,$to_date,$branch,$select_col,$table_name,$table_join,$table_where){
//SEARCH INFO FUNCTION
$this->search_info();
$role_condition = "";
if($this->role_condition){
$role_condition = $this->role_condition;
}
//date where conditions
$period = ' att_date between "'.$from_date.'" and "'.$to_date.'" and ';
$cat_where = "";
//category where conditions
if($branch){
$cat_where = ' cw_employees.branch = "'.$branch.'" and ';
}
$table_data_qry = 'SELECT '.$select_col.' FROM '.$table_name.' '.$table_join.' where '.$period.$cat_where.$table_where.''.$role_condition.'';
$table_data_info = $this->db->query("CALL sp_a_run ('SELECT','$table_data_qry')");
$table_data_rslt = $table_data_info->result();
$table_data_info->next_result();
return $table_data_rslt;
}
//GET EMP STATISTICS INFO
public function get_emp_statistics(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$date_period = (int)$this->input->post('date_period');
$branch = $this->input->post('branch');
$tble_line = '';
$select_col = '';
$table_name = '';
$table_join = '';
$table_where = '';
$select_col = 'SUM(CASE WHEN whole_day_status = "P" THEN 1 ELSE 0 END) present,SUM(CASE WHEN whole_day_status = "U" THEN 1 ELSE 0 END) absent,SUM(CASE WHEN early_out != 0 THEN 1 ELSE 0 END) early_out,SUM(CASE WHEN late_in != 0 THEN 1 ELSE 0 END) late_in,SUM(CASE WHEN excess_out != 0 and whole_day_status = "P" THEN 1 ELSE 0 END) excess_out';
// ,SUM(CASE WHEN early_in != 0 THEN 1 ELSE 0 END) early_in
//date period 2 and 3 as same as time etnry table so default time entry
$table_name = 'cw_time_entry';
$table_join = 'inner join cw_employees on cw_employees.employee_code = cw_time_entry.employee_code';
$table_where = 'cw_time_entry.trans_status = 1';
if($date_period === 1){
$from_date = date("Y-m-d",strtotime("-1 days",strtotime(date("Y-m-d"))));
$to_date = date("Y-m-d",strtotime("-1 days",strtotime(date("Y-m-d"))));
}else
if($date_period === 2){
$from_date = date("Y-m-d");
$to_date = date('Y-m-d');
// WHEN whole_day_status = FP or whole_day_status = SP THEN 0.5
$table_name = 'cw_live_attendance';
$table_join = 'inner join cw_employees on cw_employees.employee_code = cw_live_attendance.employee_code';
$table_where = 'cw_live_attendance.trans_status = 1';
}else
if($date_period === 3){
$from_date = date("Y-m-01");
$to_date = date('Y-m-t');
}else
if($date_period === 4){
$from_date = date("Y-01-01");
$to_date = date('Y-12-t');
}
$table_rslt = $this->table_qry($from_date,$to_date,$branch,$select_col,$table_name,$table_join,$table_where);
$table_create = $this->table_create_function($from_date,$to_date,$branch,$table_rslt,"statistics_info_tab",$date_period);
echo json_encode(array("success" => TRUE,'table_data' => $table_create));
}
//GET EMP ABSENT PRESENT INFO
public function get_emp_present_absent_sts(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$branch = $this->input->post('branch');
$date_period = $this->input->post('date_period');
$tble_line = '';
$select_col = '';
$table_name = '';
$table_join = '';
$table_where = '';
$to_date = date("Y-m-d",strtotime('-1 days',strtotime(date("Y-m-d"))));
$from_date = date("Y-m-d",strtotime('-7 days',strtotime($to_date)));
$select_col = 'GROUP_CONCAT(DISTINCT table1.emp_code) as employee_codes,SUM(CASE WHEN table1.present_count >= 8 THEN 1 ELSE 0 END) present,SUM(CASE WHEN table1.absent_count >= 8 THEN 1 ELSE 0 END) absent,SUM(CASE WHEN table1.early_out_count >= 8 THEN 1 ELSE 0 END) early_out,SUM(CASE WHEN table1.late_in_count >= 8 THEN 1 ELSE 0 END) late_in,SUM(CASE WHEN table1.excess_out_count >= 8 THEN 1 ELSE 0 END) excess_out';
// ,SUM(CASE WHEN table1.early_in_count >= 3 THEN 1 ELSE 0 END) early_in
$table_name = '(SELECT cw_time_entry.employee_code as emp_code,SUM(CASE WHEN total_work_hours > 0 AND total_work_hours != 0 THEN 1 ELSE 0 END) present_count,SUM(CASE WHEN total_work_hours <= 0 THEN 1 ELSE 0 END) absent_count,SUM(CASE WHEN early_in != 0 THEN 1 ELSE 0 END) early_in_count,SUM(CASE WHEN early_out != 0 THEN 1 ELSE 0 END) early_out_count,SUM(CASE WHEN late_in != 0 THEN 1 ELSE 0 END) late_in_count,SUM(CASE WHEN excess_out != 0 THEN 1 ELSE 0 END) excess_out_count from cw_time_entry';
$table_join = 'inner join cw_employees on cw_employees.employee_code = cw_time_entry.employee_code';
// early_in_count >= 3 or
$table_where = 'cw_time_entry.trans_status = 1 GROUP BY cw_time_entry.employee_code HAVING (present_count >= 8 or absent_count >= 8) or (early_out_count >= 8 or late_in_count >= 8) or (excess_out_count >= 8)) table1';
$table_rslt = $this->table_qry($from_date,$to_date,$branch,$select_col,$table_name,$table_join,$table_where);
$table_create = $this->table_create_function($from_date,$to_date,$branch,$table_rslt,"emp_pre_abs_info_tab",$date_period );
echo json_encode(array("success" => TRUE,'table_data' => $table_create));
}
public function table_create_function($from_date,$to_date,$branch,$table_rslt,$table_id,$date_period ){
foreach($table_rslt as $rlst){
$present_count = $rlst->present;
if(!$present_count){
$present_count = 0;
}
$absent_count = $rlst->absent;
if(!$absent_count){
$absent_count = 0;
}
$early_out_count = $rlst->early_out;
if(!$early_out_count){
$early_out_count = 0;
}
$late_in_count = $rlst->late_in;
if(!$late_in_count){
$late_in_count = 0;
}
$excess_out_count = $rlst->excess_out;
if(!$excess_out_count){
$excess_out_count = 0;
}
$tble_line .= "<tr>
<td style='cursor:pointer;' onclick=get_employee_details('present_count','$table_id','$from_date','$to_date','$branch','$date_period')>$present_count</td>
<td style='cursor:pointer;' onclick=get_employee_details('absent_count','$table_id','$from_date','$to_date','$branch','$date_period')>$absent_count</td>
<!-- <td>$early_in_count</td> -->
<td style='cursor:pointer;' onclick=get_employee_details('early_out_count','$table_id','$from_date','$to_date','$branch','$date_period')>$early_out_count</td>
<td style='cursor:pointer;' onclick=get_employee_details('late_in_count','$table_id','$from_date','$to_date','$branch','$date_period')>$late_in_count</td>
<td style='cursor:pointer;' onclick=get_employee_details('excess_out_count','$table_id','$from_date','$to_date','$branch','$date_period')>$excess_out_count</td>
</tr>";
}
if($tble_line === ''){
$tble_line = "<tr><td colspan='3'></td></tr>";
}
$table_data = "<table class='table table-striped table-bordered' id='".$table_id."'>
<thead>
<tr>
<th scope='col'>Present</th>
<th scope='col'>Absent</th>
<!-- <th scope='col'>Early In</th> -->
<th scope='col'>Early Out</th>
<th scope='col'>Late In</th>
<th scope='col'>Excess Out</th>
</tr>
</thead>
<tbody>
$tble_line
</tbody>
</table>";
return $table_data;
}
public function get_employee_details(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
//SEARCH INFO FUNCTION
$this->search_info();
$branch = $this->input->post('branch');
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$table_id = $this->input->post('table_id');
$get_cell = $this->input->post('get_cell');
$date_period = $this->input->post('date_period');
if($date_period === 1){
$table_name = "cw_time_entry";
}else if($date_period === 2){
$table_name = "cw_live_attendance";
}else{
$table_name = "cw_time_entry";
}
$where_qry = "";
$cat_where = "";
if($branch){
$cat_where = ' and cw_employees.branch = "'.$branch.'" ';
}
$having = "";
if($table_id === "statistics_info_tab"){
if($get_cell === "present_count"){
$where_qry = ' and whole_day_status = "P"';
}else
if($get_cell === "absent_count"){
$where_qry = ' and whole_day_status = "U"';
}else
if($get_cell === "early_out_count"){
$where_qry = ' and early_out != 0';
}else
if($get_cell === "late_in_count"){
$where_qry = ' and late_in != 0';
}else
if($get_cell === "excess_out_count"){
$where_qry = ' and excess_out != 0';
}
}else{
if($get_cell === "present_count"){
$where_qry = ' and total_work_hours > 0 ';
$having = 'GROUP BY cw_employees.employee_code HAVING count(*) >= 8';
}else
if($get_cell === "absent_count"){
$where_qry = ' and total_work_hours = 0';
$having = 'GROUP BY cw_employees.employee_code HAVING count(*) >= 8';
}else
if($get_cell === "early_out_count"){
$where_qry = ' and early_out != 0';
$having = 'GROUP BY cw_employees.employee_code HAVING count(*) >= 8';
}else
if($get_cell === "late_in_count"){
$where_qry = ' and late_in != 0';
$having = 'GROUP BY cw_employees.employee_code HAVING count(*) >= 8';
}else
if($get_cell === "excess_out_count"){
$where_qry = ' and excess_out != 0';
$having = 'GROUP BY cw_employees.employee_code HAVING count(*) >= 8';
}
}
$table_data_qry = 'SELECT cw_employees.employee_code,cw_employees.emp_name,whole_day_status,cw_department.department FROM '.$table_name.' inner join cw_employees join cw_department on cw_employees.employee_code = '.$table_name.'.employee_code and cw_employees.department = cw_department.prime_department_id where cw_employees.trans_status = 1 and att_date between "'.$from_date.'" and "'.$to_date.'"'.$where_qry.' '.$cat_where.' '.$this->role_condition.' '.$having;
$table_data_info = $this->db->query("CALL sp_a_run ('SELECT','$table_data_qry')");
$table_data_rslt = $table_data_info->result();
$table_data_info->next_result();
$tr_line = "";
foreach($table_data_rslt as $key => $rlst){
$employee_code = $rlst->employee_code;
$emp_name = $rlst->emp_name;
$department = $rlst->department;
$whole_day_status = $rlst->whole_day_status;
$tr_line .= "<tr>
<td>$employee_code</td>
<td>$emp_name</td>
<td>$department</td>
<td>$whole_day_status</td>
</tr>";
}
$table_data = "<table class='table table-striped table-bordered' id='detail_".$table_id."'>
<thead>
<tr>
<th scope='col'>Employee Code</th>
<th scope='col'>Name</th>
<th scope='col'>Department</th>
<th scope='col'>Whole Day Status</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
echo json_encode(array("success" => TRUE,'table_data' => $table_data));
}
// EMPLOYEES COUNT DEPARTMENT WISE
public function emp_department_count_chart(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
//SEARCH INFO FUNCTION
$this->search_info();
$branch = $this->input->post('branch');
if($branch){
$cat_where = ' cw_employees.branch = "'.$branch.'" and ';
}
$role_condition = $this->role_condition;
$table_data_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT count(emp_name) as emp_count,cw_department.department as department FROM cw_employees INNER join cw_department on cw_employees.department = cw_department.prime_department_id where cw_employees.termination_status = 0 and $cat_where cw_employees.trans_status = 1 $role_condition GROUP BY cw_department.department')");
$table_data_rslt = $table_data_info->result_array();
$table_data_info->next_result();
$total_emp['name'] = 'Total Employees';
$series1['name'] = 'Department';
foreach($table_data_rslt as $key => $value){
$employees = $value['emp_count'];
$department = $value['department'];
$total_emp['data'][] = $department;
$series1['data'][] = $employees;
}
$result = array();
array_push($result,$total_emp);
array_push($result,$series1);
echo json_encode($result,JSON_NUMERIC_CHECK);
}
//GET EMP ABSENT PRESENT INFO
public function emp_leave_details_info(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','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){
$cat_where = ' cw_employees.branch = "'.$branch.'" and ';
}
//LEAVE FINANCIAL YEAR ID GET
$financial_info = $this->get_leave_financial_details();
$prime_financial_id = $financial_info[0]->prime_leave_financial_year_id;
$emp_leave_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT cw_leave_entry.employee_code,cw_employees.emp_name,SUM(leave_count) as leave_count from cw_leave_entry join cw_employees on cw_employees.employee_code = cw_leave_entry.employee_code WHERE $cat_where leave_date between '$start_date' and '$end_date' and financial_setting_id = $prime_financial_id and cw_employees.termination_status = 0 and cw_leave_entry.trans_status = 1 GROUP BY cw_leave_entry.employee_code order by leave_count DESC limit 10')");
$emp_leave_rlst = $emp_leave_info->result();
$emp_leave_info->next_result();
$tble_line = "";
foreach($emp_leave_rlst as $send_rlst){
$employee_code = $send_rlst->employee_code;
$emp_name = $send_rlst->emp_name;
$leave_count = $send_rlst->leave_count;
$tble_line .= "<tr>
<td>$emp_name</td>
<td>$employee_code</td>
<td>$leave_count</td>
</tr>";
}
if($tble_line === ""){
$tble_line = "<tr><td></td><td>No Data Available</td><td></td></tr>";
}
$table_data = "<table class='table table-striped table-bordered leave' id='emp_leave_info_tab'>
<thead>
<tr>
<th scope='col'>Employee Name</th>
<th scope='col'>Employee Code</th>
<th scope='col'>Leave Count</th>
</tr>
</thead>
<tbody>
$tble_line
</tbody>
</table>";
echo json_encode(array("success" => TRUE,'table_data' => $table_data));
}
public function emp_shift_count_chart(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$month = $this->input->post('month');
//Get 1st Monday of Month
$dateobj = new DateTime("01-$month");
$first_monday = $dateobj->modify('first monday')->format('Y-m-d');
$branch = $this->input->post('branch');
if($branch){
$cat_where = ' and cw_employees.branch = '.$branch.' ';
}
$shift_qry = ' SELECT cw_shift_import.employee_code,cw_shift_master.shift_name,COUNT(*) AS shift_count FROM cw_shift_import INNER JOIN cw_shift_master ON cw_shift_master.prime_shift_master_id = cw_shift_import.shift_name INNER JOIN cw_employees ON cw_employees.employee_code = cw_shift_import.employee_code WHERE cw_shift_import.trans_status = 1 and (cw_employees.termination_status = 0 or cw_employees.termination_status = 1 and cw_employees.last_working_date <= cw_shift_import.shift_date) AND cw_shift_master.trans_status = 1 AND cw_shift_import.shift_date = "'.$first_monday.'" '.$cat_where.' GROUP BY cw_shift_master.shift_name ' ;
$shift_master_info = $this->db->query("CALL sp_a_run ('SELECT','$shift_qry')");
$shift_master_rslt = $shift_master_info->result();
$shift_master_info->next_result();
$rows = array();
$rows1 = array();
$rows['name'] = 'TOTAL EMPLOYEES';
$rows1['name'] = 'Shift';
foreach($shift_master_rslt as $rlst){
$count = $rlst->shift_count;
$shift_name = $rlst->shift_name;
$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);
}
public function get_cw_team_data(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','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){
$cat_where = ' and cw_employees.branch = '.$branch.' ';
}
if($db_name === 'rebar_hrms_db'){
$team_qry = 'SELECT COUNT(emp_name)as count,cw_team.team as team FROM cw_employees JOIN cw_team ON cw_employees.team = cw_team.prime_team_id where cw_employees.termination_status = 0 '.$cat_where.' GROUP BY cw_team.team';
}else{
$team_qry = 'SELECT COUNT(emp_name)as count,cw_category.category_name as team FROM cw_employees JOIN cw_category ON cw_employees.role = cw_category.prime_category_id where cw_employees.termination_status = 0 '.$cat_where.' GROUP BY cw_category.category_name';
}
$team_info = $this->db->query("CALL sp_a_run ('SELECT','$team_qry')");
$team_rslt = $team_info->result();
$team_info->next_result();
$tble_line = "";
foreach($team_rslt as $send_rlst){
$team = $send_rlst->team;
$team_count = $send_rlst->count;
$tble_line .= "<tr><td>$team</td><td>$team_count</td></tr>";
}
if($tble_line === ""){
$tble_line = "<tr><td>No Data Available</td><td></td></tr>";
}
if($db_name === 'rebar_hrms_db'){
$table_data = "<table class='table table-striped table-bordered team' id='emp_leave_info_tab'><thead><tr><th scope='col'>Team</th><th scope='col'>Count</th></tr></thead><tbody>$tble_line</tbody></table>";
}else{
$table_data = "<table class='table table-striped table-bordered team' id='emp_leave_info_tab'><thead><tr><th scope='col'>Category</th><th scope='col'>Count</th></tr></thead><tbody>$tble_line</tbody></table>";
}
echo json_encode(array("success" => TRUE,'table_data' => $table_data));
}
}
?>