File: /home/cafsindia/cpaqua.cafsinfotech.in/application/controllers/Daily_head_count.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Daily_head_count extends Action_controller{
public function __construct(){
parent::__construct('daily_head_count');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
// Get Employees Picklist
$picklist_rslt = $this->get_form_setting();
$picklist_rslt_arr = array_reduce($picklist_rslt, function($result, $arr){
$result[$arr['field_type']][] = $arr;
return $result;
}, array());
$pick_key = array_column($picklist_rslt_arr[5], 'label_name');
$pick_val = array_column($picklist_rslt_arr[5], 'view_name');
$final_pick = array_combine( $pick_key, $pick_val);
$data['select_pick_list'] = array("" => "---- label_name ----") + $final_pick;
$emp_pick_key = array_column($picklist_rslt, 'label_name');
$emp_pick_val = array_column($picklist_rslt, 'view_name');
$final_pick_val = array_combine( $emp_pick_key, $emp_pick_val);
$data['employee_list'] = array("" => "---- label_name ----") + $final_pick_val;
$data['key'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
public function get_form_setting(){
$picklist_qry = 'select label_name,view_name,pick_list,pick_list_type,pick_table,field_type from cw_form_setting where trans_status = 1 and input_view_type in(1,2) and transaction_type not in(2,3) and prime_module_id = "employees" and field_show = 1';
$picklist_info = $this->db->query("CALL sp_a_run ('SELECT','$picklist_qry')");
$picklist_rslt = $picklist_info->result_array();
$picklist_info->next_result();
return $picklist_rslt;
}
public function daily_head_count_details(){
$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);
}
$select_type = $this->input->post("select_type");
$row_based_on = $this->input->post("row_based_on");
$column_based_on = $this->input->post("column_based_on");
$detailed_header = implode(',cw_employees.',$this->input->post("detailed_header") ?? []);
if($detailed_header){
$detailed_header= "cw_employees.".$detailed_header;
}
$from_date = date('Y-m-d',strtotime($this->input->post("from_date")));
$to_date = date('Y-m-d',strtotime($this->input->post("to_date")));
$current_date = date('Y-m-d');
// $current_date = "2022-05-25";
if($column_based_on === $row_based_on){
echo json_encode(array('success' => false,'message'=>'Column Name And Row Name Same'));
exit(0);
}
$picklist_rslt = $this->get_form_setting();
$picklist_rslt = array_reduce($picklist_rslt, function($result, $arr){
$result[$arr['label_name']] = $arr;
return $result;
}, array());
$pick_list = $picklist_rslt[$column_based_on]['pick_list'];
$pick_table = $picklist_rslt[$column_based_on]['pick_table'];
$pick_list_type = $picklist_rslt[$column_based_on]['pick_list_type'];
if((int)$pick_list_type === 1){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
}else
if((int)$pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
}
$pick_query = 'select '.$pick_list_val_1.' as pick_list_val_1,'.$pick_list_val_2.' as pick_list_val_2 from '.$pick_table.' where '.$pick_table.'.trans_status = 1';
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result_array();
$pick_data->next_result();
$pick_result = array_reduce($pick_result, function($result, $arr){
$result[$arr['pick_list_val_1']] = $arr['pick_list_val_2'];
return $result;
}, array());
$pick_query_tbl ="";
if((int)$select_type === 1){
$select_by = ",cw_employees.$column_based_on as column_name,$row_based_on as row_name";
$group_by = ",$row_based_on";
$daily_select = ',sum(CASE WHEN cw_live_attendance.whole_day_status in("U","L") THEN 1 ELSE 0 END) as absent,sum(CASE WHEN cw_live_attendance.whole_day_status in("H","W") THEN 1 ELSE 0 END) as weekoff,sum(CASE WHEN cw_live_attendance.whole_day_status not in("H","W","U","L") THEN 1 ELSE 0 END) as present,count(*) as total_count';
$time_entry_select = ',sum(CASE WHEN cw_time_entry.whole_day_status in("U","L") THEN 1 ELSE 0 END) as absent,sum(CASE WHEN cw_time_entry.whole_day_status in("H","W") THEN 1 ELSE 0 END) as weekoff,sum(CASE WHEN cw_time_entry.whole_day_status not in("H","W","U","L") THEN 1 ELSE 0 END) as present,count(*) as total_count';
}else
if((int)$select_type === 2){
$select_by = ',att_date as Date,cw_day_status_legends.day_description as Whole_Day_Status,concat(cw_employees.employee_code,"_",att_date) as emp_detail';
$group_by = ",cw_employees.employee_code,att_date";
$daily_select = "";
$time_entry_select = "";
$detailed_header_value = implode('","', $this->input->post("detailed_header") ?? []);
$from_query = 'select * from cw_form_setting where prime_module_id = "employees" and label_name in ("'.$detailed_header_value.'") and field_show = "1" and trans_status = "1" ORDER BY input_for,field_sort asc';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$from_query')");
$form_result = $form_data->result_array();
$form_data->next_result();
$join_view = ",$pick_table.$pick_list_val_2 as column_name";
$inner_join = " inner join $pick_table on $pick_table.$pick_list_val_1 = cw_employees.$column_based_on ";
$table_name = "cw_employees";
$select_query = "";
$pick_query = "";
if($this->input->post("detailed_header")){
foreach($form_result as $key => $form_info){
$pick_list = $form_info['pick_list'];
$prime_form_id = $form_info['prime_form_id'];
$pick_table = $form_info['pick_table'];
$label_name = $form_info['label_name'];
$view_name = $form_info['view_name'];
$field_type = $form_info['field_type'];
$pick_list_type = $form_info['pick_list_type'];
if((int)$field_type === 5){
if((int)$pick_list_type === 1){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
}else
if((int)$pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
}
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as `$view_name` , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}else
if((int)$field_type === 4){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.',"%d-%m-%Y") as `'.$view_name.'`,';
}
else{
$select_query .= "$table_name.$label_name as `$view_name` , ";
}
}
$pick_query_tbl = $pick_query;
$select_query = rtrim($select_query,',');
if($select_query){
$select_query = ",".rtrim($select_query,' , ');
}
}
}
$grp_by ="cw_employees.$column_based_on";
if((strtotime($from_date) === strtotime($current_date)) && (strtotime($to_date) === strtotime($current_date))){
$get_data_qry = 'select cw_employees.employee_code as `Employee Code`'.$select_query.''.$select_by.''.$join_view.''.$daily_select.' from cw_live_attendance inner join cw_employees on cw_employees.employee_code = cw_live_attendance.employee_code inner join cw_day_status_legends on cw_day_status_legends.legends = cw_live_attendance.whole_day_status '.$pick_query_tbl.''.$inner_join.' where cw_live_attendance.trans_status = 1 and cw_employees.trans_status = 1 and DATE_FORMAT(cw_live_attendance.att_date, "%Y-%m-%d") BETWEEN "'.$from_date.'" and "'.$to_date.'" group by '.$grp_by.''.$group_by.'';
}else{
$get_data_qry = 'select cw_employees.employee_code as `Employee Code`'.$select_query.''.$select_by.''.$join_view.''.$time_entry_select.' from cw_time_entry inner join cw_employees on cw_employees.employee_code = cw_time_entry.employee_code inner join cw_day_status_legends on cw_day_status_legends.legends = cw_time_entry.whole_day_status '.$pick_query_tbl.''.$inner_join.' where cw_time_entry.trans_status = 1 and cw_employees.trans_status = 1 and DATE_FORMAT(cw_time_entry.att_date, "%Y-%m-%d") BETWEEN "'.$from_date.'" and "'.$to_date.'" group by '.$grp_by.''.$group_by.'';
}
$get_data_info = $this->db->query("CALL sp_a_run ('SELECT','$get_data_qry')");
$get_data_rslt = $get_data_info->result_array();
$get_data_info->next_result();
if(empty($get_data_rslt)){
echo json_encode(array('success' => FALSE, 'message' => 'No Records Found!'));
}else
if((int)$select_type === 1){
$get_data_rslt = array_reduce($get_data_rslt, function($result, $arr){
$result[$arr['column_name']][$arr['row_name']] = $arr;
return $result;
}, array());
$pick_list_row = $picklist_rslt[$row_based_on]['pick_list'];
$pick_table_row = $picklist_rslt[$row_based_on]['pick_table'];
$pick_list_type_row = $picklist_rslt[$row_based_on]['pick_list_type'];
if((int)$pick_list_type_row === 1){
$pick_list_val_row = explode(",",$pick_list_row);
$pick_list_val_1_row = $pick_list_val_row[0];
$pick_list_val_2_row = $pick_list_val_row[1];
}else
if((int)$pick_list_type_row === 2){
$pick_list_val_1_row = $pick_table_row."_id";
$pick_list_val_2_row = $pick_table_row."_value";
}
$not_in = "";
if($pick_table_row === "cw_category"){
$not_in = " and $pick_list_val_1_row not in(1)";
}
$pick_row_query = 'select '.$pick_list_val_1_row.' as pick_list_val_1_row,'.$pick_list_val_2_row.' as pick_list_val_2_row from '.$pick_table_row.' where '.$pick_table_row.'.trans_status = 1 '.$not_in.'';
$pick_row_info = $this->db->query("CALL sp_a_run ('SELECT','$pick_row_query')");
$pick_row_rslt = $pick_row_info->result_array();
$pick_row_info->next_result();
$pick_row_rslt = array_reduce($pick_row_rslt, function($result, $arr){
$result[$arr['pick_list_val_1_row']] = $arr['pick_list_val_2_row'];
return $result;
}, array());
$grand_arr = array('total'=>'grand_total');
$pick_row_rslt = $pick_row_rslt+$grand_arr;
$pick_row_count = 6*count($pick_row_rslt ?? [])+1;
$total_count_foot = 0;
$tr_line = "<tr>";
foreach ($get_data_rslt as $key => $value) {
$tr_head = "<tr>";
$first_head = "<tr><td rowspan='2'>".ucwords($column_based_on)."</td>";
$top_header = "<tr><td rowspan='1'></td>";
$tr_foot = "<tr style=color:blue;font-weight:bold;><td></td>";
$tr_line .= "<td>".$pick_result[$key]."</td>";
$total_count_sum = 0;
$absent_sum = 0;
$weekoff_sum = 0;
$present_sum = 0;
$plan_sum = 0;
foreach ($pick_row_rslt as $role_key => $role_value) {
$category_name = $role_value;
$total_count = (int)$value[$role_key]['total_count'];
$absent = (int)$value[$role_key]['absent'];
$weekoff = (int)$value[$role_key]['weekoff'];
$present = (int)$value[$role_key]['present'];
$plan = $total_count - $weekoff;
if((int)$plan > 0){
$percentage = ($absent/$plan)*100;
}
$percentage = (int)$percentage;
$total_count_sum+= $total_count;
$absent_sum += $absent;
$weekoff_sum += $weekoff;
$present_sum += $present;
$plan_sum += $plan;
if((int)$plan_sum > 0){
$percentage_sum = ((int)$absent_sum/(int)$plan_sum)*100;
}
if($role_key === "total"){
$category_name = "Grand Total";
$total_count = $total_count_sum;
$absent = $absent_sum;
$weekoff = $weekoff_sum;
$present = $present_sum;
$plan = $plan_sum;
$percentage = (int)$percentage_sum;
}
$text_bold = "style='font-weight:bold;'";
$tr_head .= "<td>ONROLL</td><td>WEEKOFF</td><td>PLAN</td><td>PRESENT</td><td>ABSENT</td><td>ABSENT%</td>";
$tr_foot .= "<td style=color:blue;font-weight:bold;></td><td style=color:blue;font-weight:bold;></td><td style=color:blue;font-weight:bold;></td><td style=color:blue;font-weight:bold;></td><td style=color:blue;font-weight:bold;></td><td style=color:blue;font-weight:bold;></td>";
$first_head .= "<td colspan='6' style='text-align:center;'>".ucwords(strtoupper($category_name))."</td>";
$tr_line .= "<td>".$total_count."</td>
<td>".$weekoff."</td>
<td>".$plan."</td>
<td>".$present."</td>
<td>".$absent."</td>
<td $text_bold>".$percentage."%</td>";
}
$top_header .= "<td colspan='".$pick_row_count."' style='text-align:center;'> DAILY HC & ATTENDANCE REPORT - ".$this->input->post("from_date")." - ".$this->input->post("to_date")."</td>";
$tr_line .= "</tr>";
$tr_head .= "</tr>";
$tr_foot .= "</tr>";
$first_head .= "</tr>";
$top_header .= "</tr>";
}
$view_content = "<div style='margin:20px;' id='tbl_scroll'><table class='table table-striped table-bordered' id='daily_head_report'>
<thead>
$top_header
$first_head
$tr_head
</thead>
<tbody>
$tr_line
</tbody>
<tfoot>
$tr_foot
</tfoot>
</table></div>";
echo json_encode(array('success' => true,'message'=>'Ok', 'table_data' => $view_content));
exit(0);
}else
if((int)$select_type === 2){
$result = array();
foreach($get_data_rslt as $arr){
$result[$arr['column_name']][$arr['emp_detail']] = $arr;
}
//STRUCTURE FOR EXCEL !
$newArray = array();
foreach($result as $key => $get_datas){
$arr = array("Component"=>$key);
$newArray[] = $arr;
foreach($get_datas as $row){
$newArray[] = $row;
}
}
if($result){
echo json_encode(array('success' => TRUE,'message'=>'Ok', 'table_data' => $newArray));
}else{
echo json_encode(array('success' => FALSE,'message'=>'No Records Found.!'));
}
}
}
}
?>