File: /home/cafsindia/hrms_cafsindia_com/application/controllers/Attendance_report.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Base_controller.php");
class Attendance_report extends Base_controller{
public $fliter_list;
public $leave_sts_list;
public function __construct(){
parent::__construct('attendance_report');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$this->load->model('Process_payroll_model');
$this->load->library('pdf');
$this->collect_base_info();
$leave_status_qry = 'select prime_leave_status_id,leave_status,leave_value from cw_leave_status where trans_status =1';
$leave_status_data = $this->db->query("CALL sp_a_run ('SELECT','$leave_status_qry')");
$leave_status_ary = $leave_status_data->result();
$leave_status_data->next_result();
$leave_info = array();
foreach($leave_status_ary as $leave){
$leave_info[$leave->leave_value] = $leave->leave_status;
}
$this->leave_sts_list = $leave_info;
}
// LOAD PAGE WITH TABLE DATA
public function index(){
$from_query = 'select * from cw_form_setting where prime_module_id = "employees" and field_show = "1" and field_type in (1,4,5,7) ORDER BY input_for,field_sort asc';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$from_query')");
$form_result = $form_data->result();
$form_data->next_result();
$this->fliter_list = $this->get_filter_data($form_result);
$data['fliter_list'] = $this->fliter_list;
$this->load->view("$this->control_name/manage",$data);
}
// AUTOCOMPLETE FOR SERACH EMPLOYEE
public function emp_suggest(){
$search_term = $this->input->post_get('term');
$final_qry = 'select employee_code,emp_name from cw_employees where trans_status = 1 and employee_code like "'.$search_term.'%"';
$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' => "0", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
public function get_single_emp_data(){
$month = $this->input->post('process_month');
$process_month = date("Y-m",strtotime("01-".$month));
$process_by = (int)$this->input->post('process_by');
$report_type = (int)$this->input->post('report_type');
if($report_type === 1){
if($process_by === 1){
$process_emp_id = $this->input->post('process_emp_id');
$emp_codes = "\"$process_emp_id\"";
$emp_codes = '('.rtrim($emp_codes,',').')';
$employees = array($process_emp_id);
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT employee_code,emp_name FROM `cw_employees` where employee_code in $emp_codes')");
$emp_result = $emp_info->row();
$emp_info->next_result();
$emp_name_ary = array($emp_result->employee_code => $emp_result->emp_name);
$status = 'SINGLE';
}else
if($process_by === 2){
$fliter_label = $this->input->post('fliter_label');
$fliter_type = $this->input->post('fliter_type');
$filter_cond = $this->input->post('filter_cond');
$fliter_val = $this->input->post('fliter_val');
$filter_count = count($fliter_label);
$fliter_query = "";
$search_count = 0;
for($i=0;$i<=(int)$filter_count;$i++){
$db_name = $fliter_label[$i];
$table_name = $fliter_type[$i];
$db_cond = $filter_cond[$i];
$db_value = $fliter_val[$i];
$field_type = $field_type[$i];
if(($db_cond) && ($db_value)){
$search_count++;
if((int)$field_type === 4){
$search_val = date("Y-m-d",strtotime($db_value));
}else{
$search_val = $db_value;
}
if($db_cond === "LIKE"){ $search_val = "$db_value%"; }
if((int)$table_name === 1){
$this->prime_table = "cw_employees";
$fliter_query .= ' and '. $this->prime_table .".". $db_name ." ". $db_cond .' "'.$search_val.'"';
}
}
}
$from_query = 'select GROUP_CONCAT(cw_employees.employee_code) as employee_code,GROUP_CONCAT(cw_employees.emp_name) as emp_name from cw_employees where termination_status = 0 '.$fliter_query.' and trans_status = 1 and cw_employees.role != 1';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$from_query')");
$form_result = $form_data->result();
$form_data->next_result();
$employees = $form_result[0]->employee_code;
$employee_name = $form_result[0]->emp_name;
$employees = explode(",",$employees);
$employee_name = explode(",",$employee_name);
$i = 0;
$emp_name_ary = array();
foreach($employees as $emp_code){
$process_emp_id .= "\"$emp_code\",";
$emp_name_ary[$emp_code] = $employee_name[$i];
$i++;
}
if($fliter_query === ''){
$status = 'ALL';
$emp_codes = 'ALL';
$emp_query = '';
}else{
$status = 'SINGLE';
$emp_codes = '('.rtrim($process_emp_id,',').')';
$emp_query = "cw_employees.employee_code in $emp_codes and ";
}
}else{
echo json_encode(array('success'=>false,'message'=>'Please contact admin'));
exit(0);
}
$report_result = $this->get_map_data("$process_month-20",$emp_codes,'REGISTER',$status);
$company_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_company_information` where cw_company_information.trans_status = 1')");
$company_result = $company_info->result();
$company_info->next_result();
$company_name = $company_result[0]->company_name;
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT cw_employees.employee_code,cw_employees.emp_name,cw_designation.designation,emp1.emp_name as manager_report,emp2.emp_name as tl_report,cw_employees.thumb_no,cw_branch.branch,cw_category.category_name FROM `cw_employees` join cw_designation on cw_designation.prime_designation_id = cw_employees.designation join cw_category on cw_category.prime_category_id = cw_employees.role join cw_branch on cw_branch.prime_branch_id = cw_employees.branch join cw_employees as emp1 on emp1.employee_code = cw_employees.manager_report join cw_employees as emp2 on emp2.employee_code = cw_employees.tl_report where $emp_query cw_employees.trans_status = 1 and cw_employees.termination_status = 0')");
$emp_result = $emp_info->result();
$emp_info->next_result();
$results = array_map(function($rslt){
$return_data['employee_data'] = $rslt;
$return_data['employee_code'] = $rslt->employee_code;
return $return_data;
}, $emp_result);
$emp_result = array_column($results,'employee_data','employee_code');
$get_timecard['employee'] = $emp_result;
$attendance_register = $this->get_attendance_register($report_result,$emp_result,$month,$employees,$emp_name_ary);
echo json_encode($attendance_register);
}else
if($report_type === 2){
$process_emp_id = $this->input->post('process_emp_id');
$emp_codes = "\"$process_emp_id\"";
$emp_codes = '('.rtrim($emp_codes,',').')';
$report_result = $this->get_map_data("$process_month-20",$emp_codes,'TIMECARD','SINGLE');
$get_timecard = $this->get_timecard($report_result,date("Y-n",strtotime("01-".$month)),$process_emp_id);
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT employee_code,emp_name,cw_department.department,cw_designation.designation FROM `cw_employees` left join cw_department on cw_department.prime_department_id = cw_employees.department left join cw_designation on cw_designation.prime_designation_id = cw_employees.designation where employee_code in $emp_codes')");
$emp_result = $emp_info->result();
$emp_info->next_result();
$get_timecard['employee'] = $emp_result;
echo json_encode($get_timecard);
}else{
echo json_encode(array('success'=>false,'message'=>'Please contact admin'));
}
}
public function get_map_data($process_month,$emp_codes,$action,$status){
$report_info = $this->db->query("CALL itsp_MonthlyData_New('$process_month','$emp_codes','$action','$status')");
$report_result = $report_info->result();
$report_info->next_result();
return $report_result;
}
//PROVIDE QUERY AND DROPDOWN VALUES
public function get_filter_data($form_result){
$filter = array();
foreach($form_result as $setting){
$prime_form_id = (int)$setting->prime_form_id;
$prime_module_id = $setting->prime_module_id;
$input_view_type = (int)$setting->input_view_type;
$input_for = (int)$setting->input_for;
$field_type = (int)$setting->field_type;
$label_id = $setting->label_name;
$label_name = ucwords($setting->view_name);
$field_length = $setting->field_length;
$field_decimals = $setting->field_decimals;
$pick_list_type = (int)$setting->pick_list_type;
$pick_list = $setting->pick_list;
$pick_table = $setting->pick_table;
$auto_prime_id = $setting->auto_prime_id;
$auto_dispaly_value = $setting->auto_dispaly_value;
$field_isdefault = (int)$setting->field_isdefault;
$file_type = (int)$setting->file_type;
$mandatory_field = (int)$setting->mandatory_field;
$unique_field = (int)$setting->unique_field;
$search_show = (int)$setting->search_show;
$array_list = array();
if($label_id != 'role' && $label_id != 'employee_code' && $label_id != 'emp_name'){
if(($field_type === 5) || ($field_type === 7)){
if($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];
if($pick_table == "cw_category"){
$qry = " and prime_category_id != 1";
}else{
$qry = "";
}
if($pick_table == "cw_payroll_formula"){
$pick_query = "select $pick_list from $pick_table where trans_status = 1";
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$array_list[""] = "---- $label_name ----";
foreach($pick_result as $pick){
$pick_key = $pick->$pick_list_val_1;
$pick_val = ucwords(str_replace("_"," ",$pick->$pick_list_val_2));
$array_list[$pick_key] = $pick_val;
}
}else{
if($label_id === "excemption_component"){
$pick_query = "select $pick_list from $pick_table where trans_status = 1 and tax_section = 1 $qry";
}else{
$pick_query = "select $pick_list from $pick_table where trans_status = 1 $qry";
}
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$array_list[""] = "---- $label_name ----";
foreach($pick_result as $pick){
$pick_key = $pick->$pick_list_val_1;
$pick_val = $pick->$pick_list_val_2;
$array_list[$pick_key] = $pick_val;
}
}
}else
if($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 = "select $pick_list_val_1,$pick_list_val_2 from $pick_table where $pick_list_val_3 = 1";
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$array_list[""] = "---- $label_name ----";
foreach($pick_result as $pick){
$pick_key = $pick->$pick_list_val_1;
$pick_val = $pick->$pick_list_val_2;
$array_list[$pick_key] = $pick_val;
}
}
}
if(($input_view_type === 1) || ($input_view_type === 2)){
$filter[] = array('label_id'=> $label_id, 'field_isdefault'=> $field_isdefault, 'array_list'=> $array_list, 'field_type'=> $field_type);
}
}
}
return $filter;
}
public function get_attendance_register($attendance_result,$emp_result,$month,$employees,$employee_name){
$table_content = "";
$tr_line = "";
$table_head = "<tr><td style='text-align: center;font-weight: bold;'>Category Name</td><td style='text-align: center;font-weight: bold;'>Branch</td><td style='text-align: center;font-weight: bold;'>Thumb No</td><td style='text-align: center;font-weight: bold;'>Employee Code</td><td style='text-align: center;font-weight: bold;'>Employee Name</td><td style='text-align: center;font-weight: bold;'>Designation</td><td style='text-align: center;font-weight: bold;'>Team Leader Reporting</td><td style='text-align: center;font-weight: bold;'>Manager Reporting</td><td>Date</td>";
$in_array = array();
$out_array = array();
$count = count($attendance_result);
$i = 0;
$result = array();
foreach($attendance_result as $rslt){
$result[$rslt->empcode][] = array('etype'=>$rslt->etype,'edata'=>$rslt);
}
$j = 1;
foreach($employees as $emp_code){
if($result[$emp_code]){
$result_data = array_column($result[$emp_code],'edata','etype');
$emp_data = $emp_result[$emp_code];
$emp_name = $emp_data->emp_name;
$designation = $emp_data->designation;
$manager_report = $emp_data->manager_report;
$tl_report = $emp_data->tl_report;
$thumb_no = $emp_data->thumb_no;
$branch = $emp_data->branch;
$category_name = $emp_data->category_name;
$working_hrs = $result_data['M'];
$out_array = $result_data['O'];
$in_array = $result_data['I'];
$sts_array = $result_data['A'];
$late_count = $result_data['C'];
$tr_status = '<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>STATUS</td>';
$tr_working_hrs = '<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>IN</td>';
$tr_out_array = '<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>OUT</td>';
$tr_in_array = '<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>DUR</td>';
$tr_head = "<tr>
<td style='text-align: center;font-weight: normal;'>$category_name</td>
<td style='text-align: center;font-weight: normal;'>$branch</td>
<td style='text-align: center;font-weight: normal;'>$thumb_no</td>
<td style='text-align: center;font-weight: normal;'>$emp_code</td>
<td style='text-align: center;font-weight: normal;'>$emp_name</td>
<td style='text-align: center;font-weight: normal;'>$designation</td>
<td style='text-align: center;font-weight: normal;'>$tl_report</td>
<td style='text-align: center;font-weight: normal;'>$manager_report</td>
<td></td>
";
$i = 1;
$l_count = 0;
$a_count = 0;
foreach ($in_array as $key => $value){
if($key != "att_month" && $key != "empcode" && $key != "usrtype" && $key != "etype" && $key != "rtype" ){
$out_val = $out_array->$key;
$work = $working_hrs->$key;
$sts = $sts_array->$key;
$lates = $late_count->$key;
if($lates == 'Y'){
$l_count++;
}
if($value){
$in_time = date('H:i',strtotime($value));
}else{
$in_time = "-";
}
if($out_val){
$out_time = date('H:i',strtotime($out_val));
}else{
$out_time = "-";
}
if($sts){
if($sts == 'P'){
$sts_col = "style='color:green;'";
}else
if($sts == 'L'){
$sts_col = "style='color:red;'";
$a_count++;
}else{
$sts_col = "";
}
$sts = $this->leave_sts_list[$sts];
}else{
$sts = "-";
$sts_col = "";
}
if($work){
$work_hr = sprintf("%02d",floor($work / 60)).":".sprintf("%02d",str_pad(($work % 60), 2, "0", STR_PAD_LEFT));
$work = date('H:i',strtotime($work_hr));
}else{
$work = "-";
}
$tr_working_hrs .= "<td>$value</td>";
$tr_out_array .= "<td>$out_val</td>";
$tr_in_array .= "<td style='font-weight:bold;'>$work</td>";
$tr_status .= "<td $sts_col>$sts</td>";
if($j === 1){
$head_val = ucwords(str_replace("_"," ",$key));
$table_head .="<td>$head_val</td>";
$head_val = explode("_",$key);
}
//if((int)$i > 7){
$tr_head .= "<td></td>";
//}
$i++;
}
}
$tr_head .= "<td>$a_count</td><td>$l_count</td></tr>";
$tr_working_hrs .= '<td></td><td></td></tr>';
$tr_out_array .= '<td></td><td></td></tr>';
$tr_in_array .= '<td></td><td></td></tr>';
$tr_status .= '<td></td><td></td></tr>';
$tr_line .= $tr_head.$tr_status.$tr_working_hrs.$tr_out_array.$tr_in_array;
$j++;
}
}
if($tr_line){
$table_head .="<td>Absent Days</td><td>Late Counts</td></tr>";
}else{
return array('success' => FALSE, 'message' => "No data Found");
}
$table_content = "<div style='margin:20px;'><table class='table table-striped table-bordered' id='attendance_report'>
<thead>
$table_head
</thead>
<tbody>
$tr_line
</tbody>
</table>
</div>";
$company_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT company_name FROM `cw_company_information` where cw_company_information.trans_status = 1')");
$company_result = $company_info->result();
$company_info->next_result();
$company_name = $company_result[0]->company_name;
$from_year = date('F-Y',strtotime("01-$month"));
$to_year = date('F-Y',strtotime("+1 month",strtotime("01-$month")));
$title = $company_name."[ATTENDANCE REGISTER REPORT] $from_year To $to_year";
return array('success' => TRUE, 'message' => "See Unpunched leave details",'table_content'=>$table_content,'title'=>$title);
}
public function get_timecard($report_result,$month,$employees){
$tr_line = "";
if($report_result){
$result_data = array_map(function($rslt){
$return_data['etype'] = $rslt->etype;
$return_data['edata'] = $rslt;
return $return_data;
}, $report_result);
$result_data = array_column($result_data,'edata','etype');
$day_status = $result_data['A'];
$late_count = $result_data['C'];
$working_hrs = $result_data['M'];
$late_hrs = $result_data['L'];
$out_array = $result_data['O'];
$in_array = $result_data['I'];
$ex_hr_array = $result_data['X'];
$late_count_tl = 0;
$working_hrs_tl = 0;
$ex_hrs_tl = 0;
$late_hrs_tl = 0;
$out_array_tl = 0;
$in_array_tl = 0;
foreach ($in_array as $key => $value){
if($key != "att_month" && $key != "empcode" && $key != "usrtype" && $key != "etype" && $key != "rtype" ){
$out_val = $out_array->$key;
$status = $day_status->$key;
$count = $late_count->$key;
$work = $working_hrs->$key;
$late = $late_hrs->$key;
$ex_hr = $ex_hr_array->$key;
if($value){
$time = explode(':', $value);
$minutes = ($time[0] * 60.0 + $time[1] * 1.0);
$in_array_tl = $in_array_tl + $minutes;
$in_time = date('H:i',strtotime($value));
}else{
$in_time = "-";
}
if($out_val){
$time = explode(':', $out_val);
$minutes = ($time[0] * 60.0 + $time[1] * 1.0);
$out_array_tl = $out_array_tl + $minutes;
$out_time = date('H:i',strtotime($out_val));
}else{
$out_time = "-";
}
if($late){
$late = sprintf("%02d",floor($late / 60)).":".sprintf("%02d",str_pad(($late % 60), 2, "0", STR_PAD_LEFT));
$time = explode(':', $late);
$minutes = ($time[0] * 60.0 + $time[1] * 1.0);
$late_hrs_tl = $late_hrs_tl + $minutes;
$late = date('H:i',strtotime($late));
}else{
$late = "-";
}
if($work){
$work = sprintf("%02d",floor($work / 60)).":".sprintf("%02d",str_pad(($work % 60), 2, "0", STR_PAD_LEFT));
$time = explode(':', $work);
$minutes = ($time[0] * 60.0 + $time[1] * 1.0);
$working_hrs_tl = $working_hrs_tl + $minutes;
$work = date('H:i',strtotime($work));
}else{
$work = "-";
}
if($ex_hr){
$ex_hr = sprintf("%02d",floor($ex_hr / 60)).":".sprintf("%02d",str_pad(($ex_hr % 60), 2, "0", STR_PAD_LEFT));
$time = explode(':', $ex_hr);
$minutes = ($time[0] * 60.0 + $time[1] * 1.0);
$ex_hrs_tl = $ex_hrs_tl + $minutes;
$ex_hr = date('H:i',strtotime($ex_hr));
}else{
$ex_hr = "-";
}
if($status){
$status = $this->leave_sts_list[$status];
}else{
$status = "-";
}
if($count){
if($count === 'Y'){
$late_count_tl = (int)$late_count_tl + 1;
}
$count = $count;
}else{
$count = "N";
}
$head_val = ucwords(str_replace("_"," ",$key));
$head_val = explode("_",$key);
$head_val = $this->get_head($head_val[1],$month);
$tr_line .="<tr><td>$head_val</td><td>$value</td><td>$out_val</td><td>$work</td><td>$late</td><td>$ex_hr</td><td>$count</td><td>$status</td></tr>";
}
}
$from_year = date('F-Y',strtotime("$month-01"));
$to_year = date('F-Y',strtotime("+1 month",strtotime("$month-01")));
$working_hrs_tl = number_format(abs($working_hrs_tl)/60,2);
$late_hrs_tl = number_format(abs($late_hrs_tl)/60,2);
$out_array_tl = number_format(abs($out_array_tl)/60,2);
$in_array_tl = number_format(abs($in_array_tl)/60,2);
$ex_hrs_tl = number_format(abs($ex_hrs_tl)/60,2);
$late_count_tl = $late_count_tl;
$tr_line .= "<tr style='color:red;'><th>TOTAL</th><th>$in_array_tl</th><th>$out_array_tl</th><th>$working_hrs_tl</th><th>$late_hrs_tl</th><th>$ex_hrs_tl</th><th>$late_count_tl</th><th></th></tr>";
}
if($tr_line === ''){
$tr_line = "<tr><td></td><td></td><td></td><td>No Data</td><td></td><td></td><td></td><td></td></tr>";
}
$company_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT company_name FROM `cw_company_information` where cw_company_information.trans_status = 1')");
$company_result = $company_info->result();
$company_info->next_result();
$company_name = $company_result[0]->company_name;
$title = $company_name."[TIMECARD REPORT] $from_year To $to_year";
$table_content = "<div style='margin:20px;'><table class='table table-striped table-bordered' id='attendance_report'>
<thead>
<tr>
<th>Date</th>
<th>In Time</th>
<th>Out Time</th>
<th>Working Hrs</th>
<th>Late Hrs</th>
<th>Extra Hrs</th>
<th>Late Count</th>
<th>Day Status</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table></div>";
return array('success' => TRUE, 'message' => "See attendance data",'table_content'=>$table_content,'title'=>$title);
}
public function get_head($head_val,$month){
$ran_1 = range(21,31);
$ran_2 = range(1,20);
if(in_array($head_val, $ran_1)){
return date('d/m/Y D',strtotime("-1 months", strtotime("$month-".$head_val)));
}else
if(in_array($head_val, $ran_2)){
return date('d/m/Y D',strtotime("$month-".$head_val));
}
}
}
?>