File: /home/cafsindia/cpaqua.cafsinfotech.in/dump/application/controllers/Est_partial_perform_report.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
include_once('./application/libraries/xlsxwriter.class.php');
require_once("Action_controller.php");
class Est_partial_perform_report extends Action_controller{
public function __construct(){
parent::__construct('est_partial_perform_report');
$this->collect_base_info();
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$data['enckey'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
// AUTO COMPLETE FUNCTION
public function emp_suggest(){
$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);
}
# ROLE CONDITION
$role_based = $this->role_condition('auto');
$search_term = $this->input->post_get('term');
$final_qry = 'SELECT employee_code,emp_name FROM cw_employees WHERE trans_status = 1 AND role = "15" AND prime_employees_id != 1 '.$role_based.' AND (employee_code LIKE "'.$search_term.'%" OR emp_name 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",'emp_name' => "$employee_code - $emp_name");
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
//CHECKING FOLLOWING FILTER AS DATA
public function performance_report(){
$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);
}
$encString = $this->input->post('employee_code');
$employee_code_arr = $this->cryptoDecrypt($encString);
if(!$employee_code_arr){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$employee_code = $employee_code_arr['enc_code'];
$from_month = date('Y-m-d',strtotime('01-'.$this->input->post('from_month')));
$to_month = date('Y-m-t',strtotime('01-'.$this->input->post('to_month')));
# ROLE CONDITION
$role_based = $this->role_condition('excel');
# EST TIME LINE
$iqr_list_qry = 'SELECT count(*) as data FROM cw_est_time_sheet_est_time_line WHERE trans_status = 1 AND checker_status = 1 AND employee_code = "'.$employee_code.'" '.$role_based.' AND DATE_FORMAT(str_to_date(CONCAT("01-",mon_period),"%d-%m-%Y"),"%Y-%m-%d") BETWEEN "'.$from_month.'" AND "'.$to_month.'" ';
$iqr_list_info = $this->db->query("CALL sp_a_run ('SELECT','$iqr_list_qry')");
$iqr_list_rslt = $iqr_list_info->result();
$iqr_list_info->next_result();
$rslt = $iqr_list_rslt[0]->data;
if($rslt > 0){
echo json_encode(array('success' => TRUE,'message' => 'Generating Excel!'));
}else{
echo json_encode(array('success' => FALSE,'message' => 'No Data Available!'));
}
}
//EXCEL GENERATE FOR ESTIMATOR PRD REPORT
public function generate_report($encData){
$_POST = $this->cryptoDecrypt(base64_decode(urldecode($encData)));
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$encString = $this->input->post('employee_code');
$employee_code_arr = $this->cryptoDecrypt($encString);
if(!$employee_code_arr){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$employee_code = $employee_code_arr['enc_code'];
$from_month = date('Y-m-d',strtotime('01-'.$this->input->post('from_month')));
$to_month = date('Y-m-t',strtotime('01-'.$this->input->post('to_month')));
$report_from = date('F y', strtotime('01-'.$this->input->post('from_month')));
$report_to = date('F y', strtotime('01-'.$this->input->post('to_month')));
$report_taken = $report_from." to ".$report_to;
# MULTI QRY FOR RETRIEVE DATA
# 1.ESTIMATOR DETAILS
$emp_qry = 'SELECT emp_name,role,date_of_joining,GROUP_CONCAT(cw_team_list.team) AS team FROM cw_employees INNER JOIN cw_team_list ON FIND_IN_SET(cw_team_list.prime_team_list_id,cw_employees.estimation_team) WHERE cw_team_list.trans_status = 1 AND cw_employees.trans_status = 1 AND employee_code = "'.$employee_code.'" GROUP BY prime_employees_id';
# 2.EST TIME LINE DATA WHO AS CHECKER
$iqr_list_qry = 'SELECT @a:=@a+1 s_no,employee_code,checker,rea,project_name,client_name,assign_name,submitted_date,working_hour,weight_checking,error_lbs_plus,error_lbs_minus,accessories,grade_coating,reason_description,grade FROM cw_est_time_sheet_est_time_line AS line CROSS JOIN (SELECT @a:= 0) AS a INNER JOIN cw_project_list ON cw_project_list.prime_project_list_id = line.project INNER JOIN cw_assignment ON cw_assignment.prime_assignment_id = line.assignment WHERE line.trans_status = 1 AND line.checker_status = 1 AND cw_project_list.trans_status = 1 AND cw_assignment.trans_status = 1 AND line.employee_code = "'.$employee_code.'" AND DATE_FORMAT(str_to_date(CONCAT("01-",mon_period),"%d-%m-%Y"),"%Y-%m-%d") BETWEEN "'.$from_month.'" AND "'.$to_month.'" ';
# 3.COUNT TABLE FOR ACCESSORIES COLUMN
$count_list_qry = 'SELECT prime_count_id,count FROM cw_count WHERE trans_status = 1';
# 4.EMPLOYEE NAME
$emp_list_qry = 'SELECT employee_code,emp_name FROM cw_employees WHERE trans_status = 1';
$search_pro_qry = [];
$search_pro_qry[] = array("return"=>"emp_rslt","qry"=>$emp_qry);
$search_pro_qry[] = array("return"=>"iqr_list_rslt","qry"=>$iqr_list_qry);
$search_pro_qry[] = array("return"=>"count_list_rslt","qry"=>$count_list_qry);
$search_pro_qry[] = array("return"=>"emp_list_rslt","qry"=>$emp_list_qry);
$search_info_rslt = $this->run_multi_qry($search_pro_qry);
$emp_rslt = $search_info_rslt->rslt->emp_rslt;
$iqr_rslt = $search_info_rslt->rslt->iqr_list_rslt;
$count_rslt = $search_info_rslt->rslt->count_list_rslt;
$emp_arr_rslt = $search_info_rslt->rslt->emp_list_rslt;
$team = $emp_rslt[0]->team;
$emp_name = $emp_rslt[0]->emp_name;
$role = $emp_rslt[0]->role;
# ROLE
$cat_qry = 'SELECT category_name FROM cw_category WHERE prime_category_id = "'.$role.'" AND trans_status = 1';
$cat_info = $this->db->query("CALL sp_a_run ('SELECT','$cat_qry')");
$cat_rslt = $cat_info->result();
$cat_info->next_result();
$role = $cat_rslt[0]->category_name;
# COUNT LIST
$count_array = [];
foreach($count_rslt as $value){
$count_array[$value->prime_count_id] = $value->count;
}
# EMP NAME LIST
$emp_array = [];
foreach($emp_arr_rslt as $value){
$emp_array[$value->employee_code] = $value->emp_name;
}
# TABLE DATA
$row_array = array();
$total_work_hrs = 0;
$total_weig_chk = 0;
$total_err_plbs = 0;
$total_err_mlbs = 0;
$total_rows = 0;
$total_grade_b = 0;
$total_grade_c = 0;
foreach($iqr_rslt as $val){
$workin_hr = $this->decimalHours($val->working_hour);
if((int)$val->weight_checking !== 0){
$weight = 90 - $this->normalizeValue((($val->error_lbs_plus + $val->error_lbs_minus) / $val->weight_checking)) * 90;
}else{
$weight = 0;
}
$accessories = $count_array[$val->accessories];
$grade_coating = $count_array[$val->grade_coating];
# Total Column
$total = number_format($accessories + $grade_coating + $weight,2);
# Accessories Column
if(!$accessories){
$per_err = 95 - $total;
}else{
$per_err = 100 - $total;
}
# Grade Column
if(floatval($per_err) === 0){
$grade = "A+";
}else
if($per_err <= 2){
$grade = "A";
}else
if($per_err <= 4){
$grade = "B";
$total_grade_b = $total_grade_b + 1;
}else
if($per_err >= 4){
$grade = "C";
$total_grade_c = $total_grade_c + 1;
}else{
$grade = "";
}
$error_lbs_plus = number_format($val->error_lbs_plus,2);
# For Total Count
$total_work_hrs = $total_work_hrs + $workin_hr;
$total_weig_chk = $total_weig_chk + $val->weight_checking;
$total_err_mlbs = $total_err_mlbs + $per_err;
$row_array[] = array($val->s_no,$emp_array[$val->checker],$val->rea,$val->project_name,$val->client_name,$val->assign_name,$val->submitted_date,$workin_hr,$val->weight_checking,$error_lbs_plus,$val->error_lbs_minus,$accessories,$grade_coating,$weight,$total,$per_err,$val->reason_description,$grade);
$total_rows++;
}
if((int)$total_rows !== 0){
$total_error_mlbs = $this->normalizeValue(($total_err_mlbs / $total_rows));
}else{
$total_error_mlbs = 0;
}
if((int)$total_rows !== 0){
$total_per_err = $this->normalizeValue((($total_grade_b + $total_grade_c) / $total_rows)* 100) ;
}else{
$total_per_err = 0;
}
if($total_per_err === "0"){
$final_grade = "A+";
}else
if($total_per_err <= 20){
$final_grade = "A";
}else
if($total_per_err <= 40){
$final_grade = "B";
}else
if($total_per_err > 40){
$final_grade = "C";
}else{
$final_grade = "";
}
# EXCEL GENERATE STARTS
$writer = new XLSXWriter();
# 1ST ROW
$writer->writeSheetHeader($emp_name,['' => 'string'],['widths'=>[12,22,8,18,15,15,5,9,9,9,7,7,9,9,7,9,9,7]]);
# 2ND ROW
$writer->writeSheetRow($emp_name, array('="Rebar Design and Detail Private Limited"&CHAR(10)&"Estimator Review file"','','','','','','','','','','','','','','','','',''),array('height' => 40,'wrap_text' => true,'font' => 'Calibri','font-size' => 10,'font-style' => 'bold','halign' => 'center','border'=>'left,right,top,bottom','border-style' => 'medium','border-color' => '#000000','valign' => 'center'));
$writer->markMergedCell($emp_name, 1, 0, 1, 17);
# 3RD ROW
$second_row_style = array(['font' => 'Calibri','font-size' => 8,'border'=>'left,right,top,bottom','border-style' => 'thin','border-color' => '#000000', 'fill'=>'#00b050','valign' => 'center'],['font' => 'Calibri','font-size' => 8,'border'=>'left,right,top,bottom','border-style' => 'thin','border-color' => '#000000', 'fill'=>'#00b050','valign' => 'center'],['font' => 'Calibri','font-size' => 8,'border'=>'left,right,top,bottom','border-style' => 'thin','border-color' => '#000000', 'fill'=>'#00b050','valign' => 'center'],['font' => 'Calibri','font-size' => 8,'border'=>'left,right,top,bottom','border-style' => 'thin','border-color' => '#000000', 'fill'=>'#00b050','valign' => 'center'],['fill'=>'#00b050'],['fill'=>'#00b050'],['fill'=>'#00b050'],['fill'=>'#00b050'],['fill'=>'#00b050'],['fill'=>'#00b050'],['fill'=>'#00b050'],['fill'=>'#00b050'],['fill'=>'#00b050'],['fill'=>'#00b050'],['fill'=>'#00b050'],['fill'=>'#00b050'],['fill'=>'#00b050'],['fill'=>'#00b050']);
$writer->writeSheetRow($emp_name, array('EMPLOYEE NAME',$emp_name,'TEAM',$team,'','','','','','','','','','','','','',''),$second_row_style);
$writer->writeSheetRow($emp_name, array('EMPLOYEE NO',$employee_code,'MONTH',$report_taken,'','','','','','','','','','','','','',''),$second_row_style);
$writer->writeSheetRow($emp_name, array('ROLE',$role,'GRADE',$final_grade,'','','','','','','','','','','','','',''),$second_row_style);
# 5TH ROW
$header = array("S.NO" => "integer","CHECKER" =>"string","PROJECT#" =>"string","JOB TITLE" =>"string","CLIENT" =>"string","ASSIGNMENT"=>"string","SUB DATE" =>"DD/MM/YYYY","WORKING HOURS"=>"0.00","WEIGHT BEFORE CHECKER (LBS)"=>"integer","ERROR LBS (+)" =>"string","ERROR LBS (-)" =>"0.00","ACCESSORIES(5)" =>"integer","GRADE & COATING(5)" =>"integer","WEIGHT%" =>"0.0","TOTAL" => "string","PERCENT ERROR" =>"0.00","REASON FOR ERROR ABOVE 2%" => "string","GRADE" => "string");
$writer->writeSheetHeader($emp_name,$header,['wrap_text' => true,'font'=>'Calibri','font-size'=>9,'font-style'=>'bold', 'fill'=>'#00b050', 'halign'=>'center', 'border'=>'left,right,top,bottom','border-style' => 'thin','border-color' => '#000000','valign' => 'center']);
# 6TH ROW
$writer->writeSheetRow($emp_name,['']);
# TABLE ROW GENERATION
foreach($row_array as $row){
$writer->writeSheetRow($emp_name, $row, array('halign' => 'center','font' => 'Calibri','font-size' => 9,'border'=>'left,right,top,bottom','border-style' => 'thin','border-color' => '#000000','valign' => 'center'));
}
# TABLE TOTAL
$writer->writeSheetRow($emp_name,[$total_rows,'','','','','Total :','',$total_work_hrs,$total_weig_chk,'Average =',$total_error_mlbs,$total_grade_b,$total_grade_c,'','T Error %=',$total_per_err,'',$final_grade], array('halign' => 'center','font' => 'Calibri','font-size' => 9,'border'=>'left,right,top,bottom','border-style' => 'thin','border-color' => '#000000','font-style' => 'bold','fill' =>'#c4d79b','valign' => 'center'));
# Generate the XLSX file
$filename = 'excel_download/Est_PartialChk_Performance.xlsx';
$writer->writeToFile($filename);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="'.$filename.'"');
readfile($filename);
}
//CONVERT TIME TO DECIMAL
public function decimalHours($time){
$hms = explode(":", $time);
return round((int)$hms[0] + (int)($hms[1]/60) + (int)($hms[2]/3600),2);
}
//INF / NAN REPLACE ZERO
public function normalizeValue($value){
$value = (float)$value;
if(is_nan($value) || is_infinite($value) || $value === "inf" || $value === "nan"){
return 0;
}
return $value;
}
}
?>