File: //home/cafsindia/cloud_cafsinfotech_in/application/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');
}
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 and prime_category_id !=1')");
$role_result = $role_info->result();
$role_info->next_result();
$category_list[""] = "---- Select Category ----";
// $category_list[1] = "All Category";
foreach($role_result as $for){
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$category_list[$role_id] = $category_name;
}
$data['category_list'] = $category_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 resignation_date between "'.$from_date.'" and "'.$to_date.'" THEN 1 ELSE 0 END) left_employees,COUNT(DISTINCT(CASE WHEN department != 0 THEN department ELSE 0 END)) total_departments,COUNT(DISTINCT(CASE WHEN section != 0 THEN section ELSE 0 END)) total_sections FROM cw_employees';
$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,$category,$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($category){
$cat_where = ' cw_employees.role = "'.$category.'" 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(){
$date_period = (int)$this->input->post('date_period');
$category = $this->input->post('category');
$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 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,$category,$select_col,$table_name,$table_join,$table_where);
$table_create = $this->table_create_function($from_date,$to_date,$category,$table_rslt,"statistics_info_tab");
echo json_encode(array("success" => TRUE,'table_data' => $table_create));
}
//GET EMP ABSENT PRESENT INFO
public function get_emp_present_absent_sts(){
$category = $this->input->post('category');
$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('-8 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 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,$category,$select_col,$table_name,$table_join,$table_where);
$table_create = $this->table_create_function($from_date,$to_date,$category,$table_rslt,"emp_pre_abs_info_tab");
echo json_encode(array("success" => TRUE,'table_data' => $table_create));
}
public function table_create_function($from_date,$to_date,$category,$table_rslt,$table_id){
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','$category')>$present_count</td>
<td style='cursor:pointer;' onclick=get_employee_details('absent_count','$table_id','$from_date','$to_date','$category')>$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','$category')>$early_out_count</td>
<td style='cursor:pointer;' onclick=get_employee_details('late_in_count','$table_id','$from_date','$to_date','$category')>$late_in_count</td>
<td style='cursor:pointer;' onclick=get_employee_details('excess_out_count','$table_id','$from_date','$to_date','$category')>$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(){
//SEARCH INFO FUNCTION
$this->search_info();
$category = $this->input->post('category');
$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');
if($from_date === $to_date){
$table_name = "cw_live_attendance";
}else{
$table_name = "cw_time_entry";
}
$where_qry = "";
$cat_where = "";
if($category){
$cat_where = ' and cw_employees.role = "'.$category.'" ';
}
$having = "";
if($get_cell === "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_time_entry.employee_code HAVING count(*) >= 8';
}else
if($get_cell === "absent_count"){
$where_qry = ' and total_work_hours = 0';
$having = 'GROUP BY cw_time_entry.employee_code HAVING count(*) >= 8';
}else
if($get_cell === "early_out_count"){
$where_qry = ' and early_out != 0';
$having = 'GROUP BY cw_time_entry.employee_code HAVING count(*) >= 8';
}else
if($get_cell === "late_in_count"){
$where_qry = ' and late_in != 0';
$having = 'GROUP BY cw_time_entry.employee_code HAVING count(*) >= 8';
}else
if($get_cell === "excess_out_count"){
$where_qry = ' and excess_out != 0';
$having = 'GROUP BY cw_time_entry.employee_code HAVING count(*) >= 8';
}
}
$table_data_qry = 'SELECT cw_employees.employee_code,cw_employees.emp_name,cw_department.department as department_name,punch_in,punch_out,total_work_hours,whole_day_status,att_date FROM '.$table_name.' inner join cw_employees on cw_employees.employee_code = '.$table_name.'.employee_code INNER join cw_department on 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;
$att_date = $rlst->att_date;
$department_name = $rlst->department_name;
$punch_in = $rlst->punch_in;
$punch_out = $rlst->punch_out;
$total_work_hours = $rlst->total_work_hours;
$whole_day_status = $rlst->whole_day_status;
$tr_line .= "<tr>
<td>$employee_code</td>
<td>$emp_name</td>
<td>$department_name</td>
<td>$att_date</td>
<td>$punch_in</td>
<td>$punch_out</td>
<td>$total_work_hours</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'>Attendance Date</th>
<th scope='col'>In time</th>
<th scope='col'>Out time</th>
<th scope='col'>Worked Hours</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(){
//SEARCH INFO FUNCTION
$this->search_info();
// $from_date = date("Y-m-01");
// $to_date = date("Y-m-d");
$category = $this->input->post('category');
// and cw_employees.date_of_joining between "'.$from_date.'" and "'.$to_date.'"
if($category){
$cat_where = ' cw_employees.role = "'.$category.'" and ';
}
$role_condition = $this->role_condition;
$table_data_qry = '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 '.$cat_where.' cw_employees.trans_status = 1 '.$role_condition.' GROUP BY cw_department.department';
$table_data_info = $this->db->query("CALL sp_a_run ('SELECT','$table_data_qry')");
$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(){
$date_period = (int)$this->input->post('date_period');
$category = $this->input->post('category');
if($category){
$cat_where = ' cw_employees.role = "'.$category.'" and ';
}
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');
}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');
}
//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_query = '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 "'.$from_date.'" and "'.$to_date.'" and financial_setting_id = '.$prime_financial_id.' and cw_leave_entry.trans_status = 1 and cw_employees.termination_status = 0 GROUP BY cw_leave_entry.employee_code order by leave_count DESC limit 10';
$emp_leave_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_leave_query')");
$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' 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));
}
// EMPLOYEES COUNT DESIGNATION WISE
public function emp_designation_count_chart(){
//SEARCH INFO FUNCTION
$this->search_info();
$category = $this->input->post('category');
// and cw_employees.date_of_joining between "'.$from_date.'" and "'.$to_date.'"
if($category){
$cat_where = ' cw_employees.role = "'.$category.'" and ';
}
$role_condition = $this->role_condition;
$query = 'SELECT count(*) as count,cw_position.position_name from cw_employees INNER join cw_position on cw_position.prime_position_id = cw_employees.designation where cw_employees.trans_status = 1 '.$role_condition.' GROUP BY cw_position.prime_position_id';
$dept_emp_count_info = $this->db->query("CALL sp_a_run ('SELECT','$query')");
$dept_emp_count_rlst = $dept_emp_count_info->result();
$dept_emp_count_info->next_result();
$rows = array();
$rows1 = array();
$rows['name'] = 'TOTAL EMPLOYEES';
$rows1['name'] = 'DESIGNATION';
foreach($dept_emp_count_rlst as $rlst){
$count = $rlst->count;
$designation = $rlst->position_name;
$rows['data'][] = $designation;
$rows1['data'][] = $count;
}
$designation_info = array();
array_push($designation_info,$rows);
array_push($designation_info,$rows1);
echo json_encode($designation_info,JSON_NUMERIC_CHECK);
}
public function emp_shift_count_chart(){
//SEARCH INFO FUNCTION
$this->search_info();
$date_period = (int)$this->input->post('date_period');
$category = $this->input->post('category');
$role_condition = $this->role_condition;
if($category){
$cat_where = ' cw_employees.role = "'.$category.'" and ';
}
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');
}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');
}
//LEAVE FINANCIAL YEAR ID GET
$financial_info = $this->get_leave_financial_details();
$prime_financial_id = $financial_info[0]->prime_leave_financial_year_id;
$shift_master_qry = 'SELECT 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 where cw_shift_import.trans_status = 1 and cw_shift_master.trans_status = 1 and cw_shift_import.shift_date between "'.$from_date.'" and "'.$to_date.'" '.$role_condition.' GROUP BY cw_shift_master.shift_name';
$shift_master_info = $this->db->query("CALL sp_a_run ('SELECT','$shift_master_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);
}
}
?>