File: /home/cafsindia/cpaqua.cafsinfotech.in/dump/application/controllers/Checker_performance_report.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Checker_performance_report extends Action_controller{
public function __construct(){
parent::__construct('checker_performance_report');
$this->collect_base_info();
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$data['encKey'] = $this->generateKey();
$data['quick_link'] = $this->quick_link;
$data['table_head'] = $this->table_head;
$data['master_pick'] = $this->master_pick;
$data['fliter_list'] = $this->fliter_list;
$logged_team = $this->session->userdata('logged_team');
$logged_role = $this->session->userdata('logged_role');
$logged_emp_code = $this->session->userdata('logged_emp_code');
//Get Employee
if((int)$logged_role === 4){
$emp_qry = 'SELECT employee_code,emp_name FROM cw_employees where role = 4 and employee_code = "'.$logged_emp_code.'" and employee_status = 1 and trans_status = 1';
}else
if((int)$logged_role === 3){
$emp_qry = 'SELECT employee_code,emp_name FROM cw_employees where role = 4 and team in('.$logged_team.') and employee_status = 1 and trans_status = 1';
}
else{
$emp_qry = 'SELECT employee_code,emp_name FROM cw_employees where role = 4 and employee_status = 1 and trans_status = 1';
}
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_result = $emp_info->result();
$emp_info->next_result();
$employee_code_list[""] = "---- Select ----";
foreach($emp_result as $emp_rlst){
$employee_code = $emp_rlst->employee_code;
$emp_name = $emp_rlst->emp_name;
$employee_code_list[$employee_code] = $employee_code." - ".$emp_name;
}
$data['employee_code_list'] = $employee_code_list;
$this->load->view("$this->control_name/manage",$data);
}
public function excel_export(){
$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);
}
$employee_code = $this->input->post("employee_code");
$from_month = $this->input->post("from_month");
$to_month = $this->input->post("to_month");
$role = $this->input->post("role");
//require_once APPPATH."/third_party/PHPExcel.php";
$objPHPExcel = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xls');//('Excel5');
$objPHPExcel = $objPHPExcel->load('./excel_download/detailer_report.xls');//default excel template
$excel_columns = array('A','B','C','D','E','F','G','H','I','J');
$verticalStyle = array(
'borders' => array(
'bottom' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
),
'top' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
),
'left' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
),
'right' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
)
),
'font' => array(
'bold' => true,
'color' => array('rgb' => '000'),
),
'fill' => array(
'type' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
'color' => array('rgb' => '99CC00')
),
'alignment' => array(
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
)
);
$styleArray = array(
'borders' => array(
'allborders' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_DOTTED
)
)
);
$Right_arr = array(
'borders' => array(
'right' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK
)
)
);
$bottom_arr = array(
'borders' => array(
'bottom' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK
)
)
);
$top_arr = array(
'borders' => array(
'top' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK
)
)
);
$left_arr = array(
'borders' => array(
'left' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK
)
)
);
$FooterStyle = array(
'borders' => array(
'bottom' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK
),
'top' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
),
'left' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
),
'right' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
)
),
'font' => array(
'bold' => true,
'color' => array('rgb' => '000'),
),
'fill' => array(
'type' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
'color' => array('rgb' => 'FFFF00')
),
'alignment' => array(
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
)
);
$from_date = '01-'.$from_month;
$to_date = '01-'.$to_month;
$date = new DateTime($to_date);
$date->modify('last day of this month');
$get_last_date = $date->format('Y-m-d');
$from_date = date('Y-m-d',strtotime($from_date));
$to_date = date('Y-m-d',strtotime($get_last_date));
$all_emp_qry = 'select reporting,emp_name,employee_code from cw_employees where cw_employees.trans_status = 1';
$all_emp_info = $this->db->query("CALL sp_a_run ('SELECT','$all_emp_qry')");
$all_emp_rslt = $all_emp_info->result_array();
$all_emp_info->next_result();
$all_emp_rslt = array_reduce($all_emp_rslt, function($result, $arr){
$result[$arr['employee_code']] = $arr;
return $result;
}, array());
$emp_team_qry = 'select cw_team.team_name as team_name,cw_employees.team as team,date_of_joining,reporting,emp_name from cw_employees inner join cw_team on cw_team.prime_team_id = cw_employees.team where employee_code = "'.$employee_code.'" and cw_employees.trans_status = 1 and cw_team.trans_status = 1';
$emp_team_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_team_qry')");
$emp_team_result = $emp_team_info->result();
$emp_team_info->next_result();
$team_name = $emp_team_result[0]->team_name;
$team_id = $emp_team_result[0]->team;
$reporting = $emp_team_result[0]->reporting;
$emp_name = $emp_team_result[0]->emp_name;
$team_leader_name = $all_emp_rslt[$reporting]['emp_name'];
// $team_leader_report = $all_emp_rslt[$reporting]['reporting'];
$pm_name = $all_emp_rslt[$reporting]['emp_name'];
$date_of_joining = $emp_team_result[0]->date_of_joining;
$current_date = date('Y-m-d');
$diff = abs(strtotime($current_date) - strtotime($date_of_joining));
$years = floor($diff / (365*60*60*24));
$months = floor(($diff - $years * 365*60*60*24) / (30*60*60*24));
$days = floor(($diff - $years * 365*60*60*24 - $months*30*60*60*24)/ (60*60*24));
$calculate_date_month = $years." Years,".$months." Months";
$detailer_wise_qry = 'SELECT cw_tonnage_approval.team,sum(qa_major) as qa_major,sum(qa_minor) as qa_minor,cw_tonnage_approval.project,cw_tonnage_approval.detailer_name,sum(cw_tonnage_approval.actual_tonnage) as actual_tonnage,cw_employees.emp_name FROM cw_time_sheet inner join cw_time_sheet_time_line on cw_time_sheet_time_line.prime_time_sheet_id = cw_time_sheet.prime_time_sheet_id inner join cw_project_and_drawing_master on cw_project_and_drawing_master.prime_project_and_drawing_master_id = cw_time_sheet_time_line.project inner join cw_tonnage_approval on cw_tonnage_approval.prime_time_sheet_time_line_id = cw_time_sheet_time_line.prime_time_sheet_time_line_id inner join cw_employees on cw_employees.employee_code = cw_tonnage_approval.detailer_name WHERE cw_time_sheet.entry_date >= "'.$from_date.'" and cw_time_sheet.entry_date <= "'.$to_date.'" and cw_tonnage_approval.team in('.$team_id.') and cw_time_sheet.trans_status = 1 and cw_time_sheet_time_line.trans_status = 1 and cw_project_and_drawing_master.trans_status = 1 and cw_tonnage_approval.trans_status = 1 GROUP BY cw_tonnage_approval.detailer_name,cw_tonnage_approval.project';
$detailer_wise_info = $this->db->query("CALL sp_a_run ('SELECT','$detailer_wise_qry')");
$detailer_wise_rslt = $detailer_wise_info->result_array();
$detailer_wise_info->next_result();
$detailer_wise_count= count($detailer_wise_rslt);
if((int)$detailer_wise_count === 0){
echo json_encode(array('success' => false, 'message' => "No Available Data"));
exit(0);
}
$detailer_wise_rslt = array_reduce($detailer_wise_rslt, function($result, $arr){
$result[$arr['detailer_name']][$arr['project']] = $arr;
return $result;
}, array());
$target_qry = 'select cw_team_target_detailer_wise_target.detailer_name,cw_team_target.team,SUM(cw_team_target_detailer_wise_target.target_value) as target from cw_team_target inner join cw_team_target_detailer_wise_target on cw_team_target_detailer_wise_target.prime_team_target_id = cw_team_target.prime_team_target_id where cw_team_target.team in('.$team_id.') and cw_team_target.trans_status = 1 and cw_team_target_detailer_wise_target.trans_status = 1 and date_format(str_to_date(CONCAT("01-", from_date), "%d-%m-%Y"), "%m-%Y") >= "'.$from_month.'" and date_format(str_to_date(CONCAT("01-", to_date), "%d-%m-%Y"), "%m-%Y") <= "'.$to_month.'" group by cw_team_target.team,cw_team_target_detailer_wise_target.detailer_name';
$target_info = $this->db->query("CALL sp_a_run ('SELECT','$target_qry')");
$target_result_arr = $target_info->result_array();
$target_info->next_result();
$target_result = array_reduce($target_result_arr, function($result, $arr){
$result[$arr['team']][$arr['detailer_name']] = $arr;
return $result;
}, array());
// echo "<pre>";
// print_r($target_result);die;
if($target_result_arr){
$total_target = array_sum(array_column($target_result_arr, 'target'));
}
$expect_ton = $total_target/(22*8);
$expect_ton = $this->two_decimal($expect_ton);
$d_team_wise_qry = 'SELECT cw_employees.emp_name,sum(qa_major) as qa_major,sum(qa_minor) as qa_minor,cw_time_sheet.employee_code,cw_time_sheet_time_line.team,cw_time_sheet_time_line.project FROM cw_time_sheet inner join cw_time_sheet_time_line on cw_time_sheet_time_line.prime_time_sheet_id = cw_time_sheet.prime_time_sheet_id inner join cw_project_and_drawing_master on cw_project_and_drawing_master.prime_project_and_drawing_master_id = cw_time_sheet_time_line.project inner join cw_employees on cw_employees.employee_code = cw_time_sheet.employee_code WHERE cw_time_sheet.entry_date >= "'.$from_date.'" and cw_time_sheet.entry_date <= "'.$to_date.'" and cw_time_sheet_time_line.team in('.$team_id.') and cw_time_sheet.trans_status = 1 and cw_time_sheet_time_line.trans_status = 1 and cw_project_and_drawing_master.trans_status = 1 and cw_time_sheet_time_line.emp_role = 5 and cw_employees.trans_status = 1 GROUP BY cw_time_sheet.employee_code';
$d_team_wise_info = $this->db->query("CALL sp_a_run ('SELECT','$d_team_wise_qry')");
$d_team_wise_rslt = $d_team_wise_info->result_array();
$d_team_wise_info->next_result();
$co_reg_qry = 'SELECT cw_co_register_log.team,cw_co_register_log.employee_code,SEC_TO_TIME(SUM(TIME_TO_SEC(cw_co_register_log.billable_hours))) as actual_billable_time,cw_employees.emp_name from cw_co_register_log inner join cw_employees on cw_employees.employee_code = cw_co_register_log.employee_code where cw_co_register_log.entry_date >= "'.$from_date.'" and cw_co_register_log.entry_date <= "'.$to_date.'" and cw_co_register_log.team = "'.$team_id.'" and cw_co_register_log.trans_status = 1 GROUP BY cw_co_register_log.employee_code';
$co_reg_info = $this->db->query("CALL sp_a_run ('SELECT','$co_reg_qry')");
$co_reg_rslt = $co_reg_info->result_array();
$co_reg_info->next_result();
$co_reg_rslt = array_reduce($co_reg_rslt, function($result, $arr){
$result[$arr['employee_code']][$arr['team']] = $arr;
return $result;
}, array());
$submit_log_qry = 'select count(DISTINCT cw_tonnage_approval.drawing_no) as sheet_count,cw_tonnage_approval.project,cw_tonnage_approval.drawing_no,cw_tonnage_approval.detailer_name,cw_tonnage_approval.team from cw_tonnage_approval inner join cw_project_and_drawing_master on cw_project_and_drawing_master.prime_project_and_drawing_master_id = cw_tonnage_approval.project inner join cw_uspm on cw_uspm.prime_uspm_id = cw_project_and_drawing_master.project_manager inner join cw_client on cw_client.prime_client_id = cw_project_and_drawing_master.client_name inner join cw_project_and_drawing_master_drawings on cw_project_and_drawing_master_drawings.prime_project_and_drawing_master_drawings_id = cw_tonnage_approval.drawing_no inner join cw_employees on cw_employees.employee_code = cw_tonnage_approval.team_leader_name inner join cw_team on find_in_set(cw_team.prime_team_id,cw_tonnage_approval.team) inner join cw_time_sheet_time_line on cw_time_sheet_time_line.prime_time_sheet_time_line_id = cw_tonnage_approval.prime_time_sheet_time_line_id inner join cw_branch on cw_branch.prime_branch_id = cw_employees.branch inner join cw_time_sheet on cw_time_sheet.prime_time_sheet_id = cw_time_sheet_time_line.prime_time_sheet_id where cw_tonnage_approval.work_type = 1 and cw_tonnage_approval.trans_status =1 and cw_time_sheet.entry_date >= "'.$from_date.'" and cw_time_sheet.entry_date <= "'.$to_date.'" and cw_tonnage_approval.team in("'.$team_id.'") group by cw_tonnage_approval.drawing_no,entry_date';
$submit_log_info = $this->db->query("CALL sp_a_run ('SELECT','$submit_log_qry')");
$submit_log_result = $submit_log_info->result_array();
$submit_log_info->next_result();
$pro_submit_result = array_reduce($submit_log_result, function($result, $arr){
$result[$arr['team']][$arr['detailer_name']][$arr['project']][$arr['drawing_no']][] = $arr;
return $result;
}, array());
$sheet_count_array = array();
foreach ($submit_log_result as $key => $value) {
$detailer_name = $value['detailer_name'];
$sheet_count = $value['sheet_count'];
$sheet_count_array[$detailer_name][] = $sheet_count;
}
$time_sheet_qry = 'SELECT cw_time_sheet.prime_time_sheet_id as time_sheet_id,REPLACE(total_time," ","") as time_total,count(*) as working_days,DATE_FORMAT(cw_time_sheet.entry_date, "%m-%Y") as entry_date,IF(SEC_TO_TIME( SUM(time_to_sec(REPLACE(total_time," ",""))))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(REPLACE(total_time," ","")))),"%H:%i:%s"),"") as total_time FROM cw_time_sheet inner join cw_time_sheet_time_line on cw_time_sheet_time_line.prime_time_sheet_id = cw_time_sheet.prime_time_sheet_id inner join cw_work_type on cw_work_type.prime_work_type_id = cw_time_sheet_time_line.work_type WHERE cw_time_sheet.entry_date >= "'.$from_date.'" and cw_time_sheet.entry_date <= "'.$to_date.'" and employee_code = "'.$employee_code.'" and cw_time_sheet.trans_status = 1 and cw_time_sheet_time_line.trans_status = 1 GROUP BY cw_time_sheet.prime_time_sheet_id';
$time_sheet_info = $this->db->query("CALL sp_a_run ('SELECT','$time_sheet_qry')");
$time_sheet_rslt = $time_sheet_info->result_array();
$time_sheet_info->next_result();
$time_sheet_rslt = array_reduce($time_sheet_rslt, function($result, $arr){
$result[$arr['time_sheet_id']] = $arr;
return $result;
}, array());
$total_entry_time = array_column($time_sheet_rslt, 'time_total');
$total_entry_time = $this->AddPlayTime($total_entry_time);
$total_entry_qry = 'SELECT DATE_FORMAT(cw_time_sheet.entry_date, "%m-%Y") as entry_date,SEC_TO_TIME(SUM(TIME_TO_SEC(emails))+SUM(TIME_TO_SEC(study))+SUM(TIME_TO_SEC(checking))+SUM(TIME_TO_SEC(discussion))+SUM(TIME_TO_SEC(was))+SUM(TIME_TO_SEC(correction_time))+SUM(TIME_TO_SEC(rfi))+SUM(TIME_TO_SEC(aec))+SUM(TIME_TO_SEC(billable_hours))+SUM(TIME_TO_SEC(co_checking))+SUM(TIME_TO_SEC(bar_listing_time))+SUM(TIME_TO_SEC(other_works))) as booking_hours FROM cw_time_sheet INNER JOIN cw_time_sheet_time_line on cw_time_sheet_time_line.prime_time_sheet_id = cw_time_sheet.prime_time_sheet_id WHERE cw_time_sheet.entry_date >= "'.$from_date.'" and cw_time_sheet.entry_date <= "'.$to_date.'" and employee_code = "'.$employee_code.'" and cw_time_sheet.trans_status = 1 and cw_time_sheet_time_line.trans_status = 1 GROUP by DATE_FORMAT(cw_time_sheet.entry_date, "%m-%Y")';
$total_entry_info = $this->db->query("CALL sp_a_run ('SELECT','$total_entry_qry')");
$total_entry_rslt = $total_entry_info->result_array();
$total_entry_info->next_result();
$total_entry_rslt = array_reduce($total_entry_rslt, function($result, $arr){
$result[$arr['entry_date']] = $arr['booking_hours'];
return $result;
}, array());
$booking_hrs = $this->AddPlayTime($total_entry_rslt);
$leave_qry = 'select employee_code,leave_month,leave_count from cw_employee_leaves where trans_status and date_format(str_to_date(CONCAT("01-", leave_month), "%d-%m-%Y"), "%Y-%m-%d") >= "'.$from_date.'" and date_format(str_to_date(CONCAT("01-", leave_month), "%d-%m-%Y"), "%Y-%m-%d") <= "'.$to_date.'" group by employee_code,leave_month';
$leave_info = $this->db->query("CALL sp_a_run ('SELECT','$leave_qry')");
$leave_rslt = $leave_info->result_array();
$leave_info->next_result();
$leave_rslt = array_reduce($leave_rslt, function($result, $arr){
$result[$arr['employee_code']][$arr['leave_month']] = $arr['leave_count'];
return $result;
}, array());
if($leave_rslt[$employee_code]){
$leave_days = array_sum($leave_rslt[$employee_code]);
}
if(!$leave_days){
$leave_days = "0";
}
$export_arr = array();
$export_arr['exe_header']['exe_header'] = 'Checker Performance Report';
$export_arr['exe_empty']['exe_empty'] = "";
$export_arr['emp_name']['emp_detail'] = 'Name of Checker : ';
$export_arr['emp_name']['emp_details'] = $emp_name;
$export_arr['team_name']['emp_detail'] = 'Team :';
$export_arr['team_name']['emp_details'] = $team_name;
$export_arr['project_manager']['emp_detail'] = 'Project Manager :';
$export_arr['project_manager']['emp_details'] = $pm_name;
$export_arr['report_period']['emp_detail'] = 'Report Taken Period :';
$export_arr['report_period']['emp_details'] = $from_month.' to '.$to_month;
$export_arr['leave_taken']['emp_detail'] = 'Leave taken :';
$export_arr['leave_taken']['emp_details'] = $leave_days;
$export_arr['emp_name']['header_key'] = '';
$export_arr['emp_name']['header_val'] = '';
$export_arr['team_name']['header_key'] = '';
$export_arr['team_name']['header_val'] = '';
$export_arr['project_manager']['header_key'] = '';
$export_arr['project_manager']['header_val'] = '';
$export_arr['report_period']['header_key'] = '';
$export_arr['report_period']['header_val'] = '';
$export_arr['leave_taken']['header_key'] = '';
$export_arr['leave_taken']['header_val'] = '';
$export_arr['emp_name']['header_keys'] = 'Team Target Tons :';
$export_arr['emp_name']['header_vals'] = $total_target;
$export_arr['team_name']['header_keys'] = 'Average Team Expected Tons per Hour :';
$export_arr['team_name']['header_vals'] = $expect_ton;
$export_arr['project_manager']['header_keys'] = 'Experience :';
$export_arr['project_manager']['header_vals'] = $calculate_date_month;
$export_arr['report_period']['header_keys'] = 'Login Hours :';
$export_arr['report_period']['header_vals'] = '';
$export_arr['leave_taken']['header_keys'] = 'Booking Hours :';
$export_arr['leave_taken']['header_vals'] = '';
$export_arr['empty_one']['empty_one'] = '';
$export_arr['empty_two']['empty_two'] = '';
$export_arr['values']['detailer_name'] = 'Detailers';
$export_arr['values']['detailed_tons'] = 'Detailed Tons';
$export_arr['values']['claimed_hours'] = 'Claimed Hours';
$export_arr['values']['production_tons'] = 'Production Tons';
$export_arr['values']['sheets_detailed'] = 'Sheets Detailed';
$export_arr['values']['total_error'] = 'Total Check Print Error';
$export_arr['values']['avg_sheet'] = 'Avg Count Per Sheet';
$export_arr['values']['production_target'] = 'Production Target';
$export_arr['values']['achived_status'] = 'Achived Status';
$export_arr['values']['taken_leave'] = 'Leave Taken';
$sum_get_leave = 0;
$sum_tonnage = 0;
$sum_product_tons = 0;
$sum_qa_error = 0;
$sum_avg_count = 0;
$sum_target_val = 0;
$sum_achived_val = 0;
$sum_total_sheet_count = 0;
foreach ($d_team_wise_rslt as $key => $d_team_wise) {
$d_team_code = $d_team_wise['employee_code'];
$d_team_id = $d_team_wise['team'];
$detailer_wise = $detailer_wise_rslt[$d_team_code];
$total_sheet = array();
$d_emp_name = $d_team_wise['emp_name'];
foreach ($detailer_wise as $key => $detailer_rslt) {
$project_id = $detailer_rslt['project'];
// $sheet_count_arr = $submit_log_result[$d_team_id][$d_team_code][$project_id];
$sheet_count_arr = $sheet_count_array[$d_team_code];
if($sheet_count_arr){
$sheet_count = count($sheet_count_arr);
}
$total_sheet[$d_team_code][] = $sheet_count;
}
$tonnage = array_column($detailer_wise, 'actual_tonnage');
if($tonnage){
$tonnage_value = array_sum($tonnage);
}
$bill_hours = $co_reg_rslt[$d_team_code][$d_team_id]['actual_billable_time'];
$total_sheet_count = $total_sheet[$d_team_code][0];
if($total_sheet_count){
$total_sheet_count = $total_sheet_count;
}else{
$total_sheet_count = "0";
}
$rev_bill_hours = $this->decimalHours($bill_hours);
$rev_bill_ton = $rev_bill_hours * 1.5;
$rev_bill_ton = $this->two_decimal($rev_bill_ton);
$product_tons = $rev_bill_ton + $tonnage_value;
$product_tons = $this->two_decimal($product_tons);
$qa_minor = $d_team_wise['qa_minor'];
$qa_major = $d_team_wise['qa_major'];
$qa_error = $qa_minor+$qa_major;
if($qa_error && $total_sheet_count){
if((int)$total_sheet_count !== 0){
$avg_count = $qa_error/$total_sheet_count;
}else{
$avg_count = 0;
}
$avg_count = $this->two_decimal($avg_count);
}
if(!$avg_count){
$avg_count = 0;
}
$target_val = $target_result[$d_team_id][$d_team_code]['target'];
$achived_val = $product_tons - $target_val;
$achived_val = $this->two_decimal($achived_val);
$sum_tonnage += $tonnage_value;
$sum_product_tons += $product_tons;
$sum_qa_error += $qa_error;
$sum_avg_count += $avg_count;
$sum_target_val += $target_val;
$sum_achived_val += $achived_val;
$sum_total_sheet_count += $total_sheet_count;
$sum_bill_hours[] = $bill_hours;
$sum_bill_hrs = $this->AddPlayTime($sum_bill_hours);
$detailer_column = 'value_'.$d_team_code;
if($leave_rslt[$d_team_code]){
$get_leave = array_sum($leave_rslt[$d_team_code]);
}
if(!$get_leave){
$get_leave = "0";
}
$sum_get_leave += $get_leave;
$export_arr[$detailer_column]['detailer_name'] = $d_emp_name;
$export_arr[$detailer_column]['detailed_tons'] = $tonnage_value;
$export_arr[$detailer_column]['claimed_hours'] = $bill_hours;
$export_arr[$detailer_column]['production_tons'] = $product_tons;
$export_arr[$detailer_column]['sheets_detailed'] = $total_sheet_count;
$export_arr[$detailer_column]['total_error'] = $qa_error;
$export_arr[$detailer_column]['avg_sheet'] = $avg_count;
$export_arr[$detailer_column]['production_target'] = $target_val;
$export_arr[$detailer_column]['achived_status'] = $achived_val;
$export_arr[$detailer_column]['taken_leave'] = $get_leave;
}
// die;
$detailer_sum = 'total';
$export_arr[$detailer_sum]['detailer_name'] = "TOTAL";
$export_arr[$detailer_sum]['detailed_tons'] = $sum_tonnage;
$export_arr[$detailer_sum]['claimed_hours'] = $sum_bill_hrs;
$export_arr[$detailer_sum]['production_tons'] = $sum_product_tons;
$export_arr[$detailer_sum]['sheets_detailed'] = $sum_total_sheet_count;
$export_arr[$detailer_sum]['total_error'] = $sum_qa_error;
$export_arr[$detailer_sum]['avg_sheet'] = $sum_avg_count;
$export_arr[$detailer_sum]['production_target'] = $sum_target_val;
$export_arr[$detailer_sum]['achived_status'] = $sum_achived_val;
$export_arr[$detailer_sum]['taken_leave'] = $sum_get_leave;
$empty_column = "space_column_".$project_id."_".$i;
$export_arr[$empty_column]['detailer_name'] = "";
$export_arr[$empty_column]['detailed_tons'] = "";
$export_arr[$empty_column]['claimed_hours'] = "";
$export_arr[$empty_column]['production_tons'] = "";
$export_arr[$empty_column]['sheets_detailed'] = "";
$export_arr[$empty_column]['total_error'] = "";
$export_arr[$empty_column]['avg_sheet'] = "";
$export_arr[$empty_column]['production_target'] = "";
$export_arr[$empty_column]['achived_status'] = "";
$export_arr[$empty_column]['taken_leave'] = "";
if($d_team_wise_rslt){
$detailer_count = count($d_team_wise_rslt);
}
$project_wise_qry = 'SELECT cw_time_sheet_time_line.team,sum(qa_major) as qa_major,sum(qa_minor) as qa_minor,cw_time_sheet_time_line.project,cw_project_and_drawing_master.project_name,cw_time_sheet_time_line.drawing_no FROM cw_time_sheet inner join cw_time_sheet_time_line on cw_time_sheet_time_line.prime_time_sheet_id = cw_time_sheet.prime_time_sheet_id inner join cw_project_and_drawing_master on cw_project_and_drawing_master.prime_project_and_drawing_master_id = cw_time_sheet_time_line.project WHERE cw_time_sheet.entry_date >= "'.$from_date.'" and cw_time_sheet.entry_date <= "'.$to_date.'" and cw_time_sheet_time_line.team in( "'.$team_id.'") and cw_time_sheet.trans_status = 1 and cw_time_sheet_time_line.trans_status = 1 and cw_project_and_drawing_master.trans_status = 1 GROUP BY cw_time_sheet_time_line.project,cw_time_sheet_time_line.drawing_no';
$project_wise_info = $this->db->query("CALL sp_a_run ('SELECT','$project_wise_qry')");
$project_wise_rslt = $project_wise_info->result_array();
$project_wise_info->next_result();
$project_wise_rslt = array_reduce($project_wise_rslt, function($result, $arr){
$result[$arr['project']] = $arr;
return $result;
}, array());
$detailer_qry = 'SELECT cw_tonnage_approval.team,sum(qa_major) as qa_major,sum(qa_minor) as qa_minor,cw_time_sheet_time_line.project,cw_project_and_drawing_master.project_name,sum(cw_tonnage_approval.actual_tonnage) as actual_tonnage,cw_time_sheet_time_line.drawing_no,cw_tonnage_approval.detailer_name,SEC_TO_TIME(SUM(TIME_TO_SEC(study))+SUM(TIME_TO_SEC(detailing_time))+SUM(TIME_TO_SEC(discussion))+SUM(TIME_TO_SEC(checking))+SUM(TIME_TO_SEC(correction_time))) as detailing_new,SEC_TO_TIME(SUM(TIME_TO_SEC(study))+SUM(TIME_TO_SEC(aec))+SUM(TIME_TO_SEC(checking))+SUM(TIME_TO_SEC(correction_time))+SUM(TIME_TO_SEC(cw_time_sheet_time_line.non_billable_hours))+SUM(TIME_TO_SEC(cw_time_sheet_time_line.billable_hours))) as revision_new FROM cw_time_sheet inner join cw_time_sheet_time_line on cw_time_sheet_time_line.prime_time_sheet_id = cw_time_sheet.prime_time_sheet_id inner join cw_project_and_drawing_master on cw_project_and_drawing_master.prime_project_and_drawing_master_id = cw_time_sheet_time_line.project inner join cw_tonnage_approval on cw_tonnage_approval.prime_time_sheet_time_line_id = cw_time_sheet_time_line.prime_time_sheet_time_line_id WHERE cw_time_sheet.entry_date >= "'.$from_date.'" and cw_time_sheet.entry_date <= "'.$to_date.'" and cw_tonnage_approval.team in( "'.$team_id.'") and cw_time_sheet.trans_status = 1 and cw_time_sheet_time_line.trans_status = 1 and cw_project_and_drawing_master.trans_status = 1 and cw_tonnage_approval.trans_status = 1 GROUP BY cw_time_sheet_time_line.project,cw_time_sheet_time_line.drawing_no';
$detailer_info = $this->db->query("CALL sp_a_run ('SELECT','$detailer_qry')");
$detailer_rslt = $detailer_info->result_array();
$detailer_info->next_result();
$detailer_rslt = array_reduce($detailer_rslt, function($result, $arr){
$result[$arr['project']][$arr['drawing_no']] = $arr;
return $result;
}, array());
$element_qry = 'SELECT prime_element_master_id as element_id,element_name from cw_element_master where trans_status = 1 group by prime_element_master_id order by prime_element_master_id';
$element_info = $this->db->query("CALL sp_a_run ('SELECT','$element_qry')");
$element_rslt = $element_info->result_array();
$element_info->next_result();
$element_rslt = array_reduce($element_rslt, function($result, $arr){
$result[$arr['element_id']]= $arr;
return $result;
}, array());
if($element_rslt){
$element_rslt_count = count($element_rslt);
}
$get_project_qry = 'SELECT cw_client.client_name,project_name,prime_project_and_drawing_master_id as project_id,job_category from cw_project_and_drawing_master inner join cw_client on cw_client.prime_client_id = cw_project_and_drawing_master.client_name where cw_project_and_drawing_master.trans_status = 1';
$get_project_info = $this->db->query("CALL sp_a_run ('SELECT','$get_project_qry')");
$get_project_rslt = $get_project_info->result_array();
$get_project_info->next_result();
$get_project_rslt = array_reduce($get_project_rslt, function($result, $arr){
$result[$arr['project_id']] = $arr;
return $result;
}, array());
$job_cat_qry = 'SELECT prime_job_category_id,job_category from cw_job_category where trans_status = 1';
$job_cat_info = $this->db->query("CALL sp_a_run ('SELECT','$job_cat_qry')");
$job_cat_rslt = $job_cat_info->result_array();
$job_cat_info->next_result();
$job_cat_rslt = array_reduce($job_cat_rslt, function($result, $arr){
$result[$arr['prime_job_category_id']]= $arr['job_category'];
return $result;
}, array());
$element_wise_qry = 'SELECT cw_element_master.element_name,prime_project_and_drawing_master_drawings_id as drawing_id,drawing_no,prime_project_and_drawing_master_id as project,cw_project_and_drawing_master_drawings.element_name as element_id,prime_project_and_drawing_master_drawings_id as drawing_id FROM cw_project_and_drawing_master_drawings inner join cw_element_master on cw_element_master.prime_element_master_id = cw_project_and_drawing_master_drawings.element_name WHERE cw_element_master.trans_status = 1 GROUP BY prime_project_and_drawing_master_id,prime_project_and_drawing_master_drawings_id,cw_project_and_drawing_master_drawings.element_name and cw_element_master.trans_status = 1 order by cw_project_and_drawing_master_drawings.element_name asc';
$element_wise_info = $this->db->query("CALL sp_a_run ('SELECT','$element_wise_qry')");
$element_wise_rslt = $element_wise_info->result_array();
$element_wise_info->next_result();
$element_wise_rslt = array_reduce($element_wise_rslt, function($result, $arr){
$result[$arr['project']][$arr['drawing_id']][$arr['element_id']] = $arr;
return $result;
}, array());
$co_register_qry = 'SELECT team,cw_co_register_log.rdd_no,cw_co_register_log.drawing_no,employee_code,SEC_TO_TIME(SUM(TIME_TO_SEC(cw_co_register_log.billable_hours))) as actual_billable_time from cw_co_register_log where cw_co_register_log.entry_date >= "'.$from_date.'" and cw_co_register_log.entry_date <= "'.$to_date.'" and team = "'.$team_id.'" and cw_co_register_log.trans_status = 1 GROUP BY cw_co_register_log.rdd_no,cw_co_register_log.drawing_no';
$co_register_info = $this->db->query("CALL sp_a_run ('SELECT','$co_register_qry')");
$co_register_rslt = $co_register_info->result_array();
$co_register_info->next_result();
$co_register_rslt = array_reduce($co_register_rslt, function($result, $arr){
$result[$arr['rdd_no']][$arr['drawing_no']] = $arr;
return $result;
}, array());
$count_qry = 'select count(*) as count,cw_time_sheet.employee_code,project,cw_time_sheet_time_line.work_type,cw_time_sheet_time_line.drawing_no,cw_time_sheet_time_line.project from cw_time_sheet inner join cw_time_sheet_time_line on cw_time_sheet_time_line.prime_time_sheet_id = cw_time_sheet.prime_time_sheet_id inner join cw_work_type on cw_work_type.prime_work_type_id = cw_time_sheet_time_line.work_type inner join cw_employees on cw_employees.employee_code = cw_time_sheet.employee_code where cw_time_sheet.employee_code = "'.$employee_code.'" and cw_time_sheet.trans_status = 1 and cw_time_sheet_time_line.trans_status = 1 and cw_time_sheet.entry_date >= "'.$from_date.'" and cw_time_sheet.entry_date <= "'.$to_date.'" and drawing_no !=0 group by project,cw_time_sheet_time_line.work_type,cw_time_sheet_time_line.project,cw_time_sheet_time_line.drawing_no';
$count_info = $this->db->query("CALL sp_a_run ('SELECT','$count_qry')");
$count_rslt = $count_info->result_array();
$count_info->next_result();
$count_rslt = array_reduce($count_rslt, function($result, $arr){
$result[$arr['work_type']][$arr['project']][$arr['drawing_no']][]= $arr;
return $result;
}, array());
$rev_hrs_sum = 0;
$act_ton_sum = 0;
$revision_count = 0;
$pro_qa_minor = 0;
$pro_qa_major = 0;
$pro_sheet_count= 0;
$pro_team_tons = 0;
$detailing_new = 0;
$revision_new = 0;
$sum_ton = array();
$sum_hrs = array();
$sum_sheet = array();
$sum_qa_check = array();
$sum_rev_count = array();
$sum_pro_team_tons = array();
$sum_detailing_new = array();
$sum_revision_new = array();
$i = $detailer_count+2+10;
$header_arr = array();
foreach ($project_wise_rslt as $project_id => $project_wise) {
$header_arr[] = $i+1;
$project_col_name = 'project_name_'.$i;
$job_cat_id = $get_project_rslt[$project_id]['job_category'];
$get_detailer_name = str_replace("xxamp","&",$get_project_rslt[$project_id]['project_name']);
$get_detailer_name = str_replace("xquot","'",$get_detailer_name);
$get_detailer_name = str_replace("xdbquot",'"',$get_detailer_name);
$export_arr[$project_col_name]['detailer_name'] = $get_detailer_name;
$export_arr[$project_col_name]['detailed_tons'] = "";
$export_arr[$project_col_name]['claimed_hours'] = "";
$export_arr[$project_col_name]['production_tons'] = "Complexity :";
$export_arr[$project_col_name]['sheets_detailed'] = $job_cat_rslt[$job_cat_id];
$export_arr[$project_col_name]['total_error'] = "";
$export_arr[$project_col_name]['avg_sheet'] = "";
$export_arr[$project_col_name]['production_target'] = "Client :";
$get_achived_status = str_replace("xxamp","&",$get_project_rslt[$project_id]['client_name']);
$get_achived_status = str_replace("xquot","'",$get_achived_status);
$get_achived_status = str_replace("xdbquot",'"',$get_achived_status);
$export_arr[$project_col_name]['achived_status'] = $get_achived_status;
$export_arr[$project_col_name]['taken_leave'] = "";
$project_column = "pro_wise_".$project_id."_".$i;
$export_arr[$project_column]['detailer_name'] = "Elements";
$export_arr[$project_column]['detailed_tons'] = "Detailed Tons";
$export_arr[$project_column]['claimed_hours'] = "Claimed Hours";
$export_arr[$project_column]['production_tons'] = "Production Tons";
$export_arr[$project_column]['sheets_detailed'] = "Sheets Checked (N)";
$export_arr[$project_column]['total_error'] = "Sheets Checked (Rev)";
$export_arr[$project_column]['avg_sheet'] = "Checking Hours Per Sheet (N)";
$export_arr[$project_column]['production_target'] = "Checking Hours Per Sheet (Rev)";
$export_arr[$project_column]['achived_status'] = "Tons per Sheet (New Only)";
$export_arr[$project_column]['taken_leave'] = "QA Error Count";
$i++;
$pro_tonnage_value_sum = 0;
$product_pro_ton_sum = 0;
$pro_total_sheet_count_sum = 0;
$rev_count_val_sum = 0;
$pro_team_tons_sum = 0;
$qa_check_val_sum = 0;
foreach ($element_rslt as $element_id => $element_wise) {
$ele_id = $element_wise['element_id']."_".$i;
$element_column = "element_wise_".$ele_id;
$excel_element = $element_wise['element_name'];
$get_arr = $element_wise_rslt[$project_id];
foreach ($get_arr as $drawing_id => $get_data) {
$ton_ele_id = $get_data[$element_id]['element_id'];
$detail_code = $detailer_rslt[$project_id][$drawing_id]['detailer_name'];
$act_ton_sum = $detailer_rslt[$project_id][$drawing_id]['actual_tonnage'];
$pro_team = $detailer_rslt[$project_id][$drawing_id]['team'];
$rev_hrs_sum = $co_register_rslt[$project_id][$drawing_id]['actual_billable_time'];
$pro_qa_minor = $detailer_rslt[$project_id][$drawing_id]['qa_minor'];
$pro_qa_major = $detailer_rslt[$project_id][$drawing_id]['qa_major'];
$detailing_new = $detailer_rslt[$project_id][$drawing_id]['detailing_new'];
$revision_new = $detailer_rslt[$project_id][$drawing_id]['revision_new'];
$revision_count_arr = $count_rslt[2][$project_id][$drawing_id];
if($revision_count_arr){
$revision_count = count($revision_count_arr);
}
$pro_qa_check = $pro_qa_minor+$pro_qa_major;
$pro_sheet_count_arr = $pro_submit_result[$pro_team][$detail_code][$project_id][$drawing_id];
if($pro_sheet_count_arr){
$pro_sheet_count = count($pro_sheet_count_arr);
}
if((int)$ton_ele_id >0){
if(!$rev_hrs_sum){
$rev_hrs_sum = '00:00:00';
}
if(!$detailing_new){
$detailing_new = '00:00:00';
}
if(!$revision_new){
$revision_new = '00:00:00';
}
if($act_ton_sum){
$sum_ton[$project_id][$ton_ele_id][] = $act_ton_sum;
}
$sum_hrs[$project_id][$ton_ele_id][] = $rev_hrs_sum;
$sum_sheet[$project_id][$ton_ele_id][] = $pro_sheet_count;
$sum_qa_check[$project_id][$ton_ele_id][] = $pro_qa_check;
$sum_rev_count[$project_id][$ton_ele_id][] = $revision_count;
$sum_detailing_new[$project_id][$ton_ele_id][] = $detailing_new;
$sum_revision_new[$project_id][$ton_ele_id][] = $revision_new;
}
}
if($sum_ton[$project_id][$element_id]){
$pro_tonnage_value = array_sum($sum_ton[$project_id][$element_id]);
}
if($sum_qa_check[$project_id][$element_id]){
$qa_check_val = array_sum($sum_qa_check[$project_id][$element_id]);
}
if($sum_rev_count[$project_id][$element_id]){
$rev_count_val = array_sum($sum_rev_count[$project_id][$element_id]);
}
$revision_hours = $this->AddPlayTime($sum_hrs[$project_id][$element_id]);
$new_detailing_hours= $this->AddPlayTime($sum_detailing_new[$project_id][$element_id]);
$new_revision_hours = $this->AddPlayTime($sum_revision_new[$project_id][$element_id]);
$rev_pro_hrs = $this->decimalHours($revision_hours);
$rev_pro_tons = $rev_pro_hrs * 1.5;
$rev_pro_tons = $this->two_decimal($rev_pro_tons);
$product_pro_ton = $rev_pro_tons + $pro_tonnage_value;
$product_pro_ton = $this->two_decimal($product_pro_ton);
if($sum_sheet[$project_id][$element_id]){
$pro_total_sheet_count = array_sum($sum_sheet[$project_id][$element_id]);
}
if($qa_check_val && $pro_total_sheet_count){
if((int)$pro_total_sheet_count !== 0){
$pro_avg_count = $qa_check_val/$pro_total_sheet_count;
}else{
$pro_avg_count = 0;
}
$pro_avg_count = $this->two_decimal($pro_avg_count);
}
if(!$pro_avg_count){
$pro_avg_count = 0;
}
if($pro_tonnage_value && $pro_total_sheet_count){
if((int)$pro_total_sheet_count !== 0){
$pro_team_tons = $pro_tonnage_value/$pro_total_sheet_count;
}else{
$pro_team_tons = 0;
}
$pro_team_tons = $this->two_decimal($pro_team_tons);
}else{
$pro_team_tons = 0;
}
$pro_team_tons_sum += $pro_team_tons;
$pro_tonnage_value_sum += $pro_tonnage_value;
$sum_revision_hours[$project_id][] = $revision_hours;
$sum_new_rev_hrs[$project_id][] = $new_revision_hours;
$sum_new_detail[$project_id][] = $new_detailing_hours;
$pro_total_sheet_count_sum += $pro_total_sheet_count;
$rev_count_val_sum += $rev_count_val;
$rev_pro_hrs_pro = $this->decimalHours($revision_hours);
$rev_pro_tons_pro = $rev_pro_hrs_pro * 1.5;
$rev_pro_tons_pro = $this->two_decimal($rev_pro_tons_pro);
$product_pro_ton_pro = $rev_pro_tons_pro + $pro_tonnage_value;
$product_pro_ton_pro = $this->two_decimal($product_pro_ton_pro);
$product_pro_ton_sum += $product_pro_ton_pro;
$qa_check_val_sum += $qa_check_val;
$export_arr[$element_column]['detailer_name'] = $excel_element;
$export_arr[$element_column]['detailed_tons'] = $pro_tonnage_value;
$export_arr[$element_column]['claimed_hours'] = $revision_hours;
$export_arr[$element_column]['production_tons'] = $product_pro_ton;
$export_arr[$element_column]['sheets_detailed'] = $pro_total_sheet_count;
$export_arr[$element_column]['total_error'] = $rev_count_val;
$export_arr[$element_column]['avg_sheet'] = $new_detailing_hours;
$export_arr[$element_column]['production_target'] = $new_revision_hours;
$export_arr[$element_column]['achived_status'] = $pro_team_tons;
$export_arr[$element_column]['taken_leave'] = $qa_check_val;
$i++;
}
$i = $i+2;
$footer_arr[] = $i;
$i++;
if($qa_check_val_sum && $element_rslt_count){
if((int)$element_rslt_count !== 0){
$avg_qa_check_val = $qa_check_val_sum/$element_rslt_count;
}else{
$avg_qa_check_val = 0;
}
}else{
$avg_qa_check_val = 0;
}
if($pro_team_tons_sum && $element_rslt_count){
if((int)$element_rslt_count !== 0){
$avg_pro_team_tons = $pro_team_tons_sum/$element_rslt_count;
}else{
$avg_pro_team_tons = 0;
}
}else{
$avg_pro_team_tons = 0;
}
$sum_revision_val = $this->AddPlayTime($sum_revision_hours[$project_id]);
$sum_new_detail_val = $this->AddPlayTime($sum_new_detail[$project_id]);
$sum_new_rev_val = $this->AddPlayTime($sum_new_rev_hrs[$project_id]);
$pro_wise_total = 'pro_wise_col_'.$i;
$export_arr[$pro_wise_total]['detailer_name'] = "Overall";
$export_arr[$pro_wise_total]['detailed_tons'] = $pro_tonnage_value_sum;
$export_arr[$pro_wise_total]['claimed_hours'] = $sum_revision_val;
$export_arr[$pro_wise_total]['production_tons'] = $this->two_decimal($product_pro_ton_sum);
$export_arr[$pro_wise_total]['sheets_detailed'] = $pro_total_sheet_count_sum;
$export_arr[$pro_wise_total]['total_error'] = $rev_count_val_sum;
$export_arr[$pro_wise_total]['avg_sheet'] = $this->two_decimal($sum_new_detail_val);
$export_arr[$pro_wise_total]['production_target'] = $sum_new_rev_val;
$export_arr[$pro_wise_total]['achived_status'] = $this->two_decimal($avg_pro_team_tons);
$export_arr[$pro_wise_total]['taken_leave'] = $this->two_decimal($avg_qa_check_val);
$space_column = "space_column_".$project_id."_".$i;
$export_arr[$space_column]['detailer_name'] = "";
$export_arr[$space_column]['detailed_tons'] = "";
$export_arr[$space_column]['claimed_hours'] = "";
$export_arr[$space_column]['production_tons'] = "";
$export_arr[$space_column]['sheets_detailed'] = "";
$export_arr[$space_column]['total_error'] = "";
$export_arr[$space_column]['avg_sheet'] = "";
$export_arr[$space_column]['production_target'] = "";
$export_arr[$space_column]['achived_status'] = "";
$export_arr[$space_column]['taken_leave'] = "";
}
$final_footer = $i-1;
$total_cell = $detailer_count+1+10;
// ->getDefaultStyle()->applyFromArray($styleArray)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT)
foreach ($excel_columns as $key => $value) { //Apply Border for contents
$objPHPExcel->getActiveSheet()->getStyle($value."2:$value".$final_footer)->applyFromArray($styleArray);
}
$objPHPExcel->setActiveSheetIndex(0)->setTitle('Checker Performance Report')->fromArray(array_values($export_arr), null, 'A1')->getStyle('A1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle("A1:J1")->applyFromArray($verticalStyle);
$objPHPExcel->getActiveSheet()->getCell('F6')->setValue($total_entry_time);
$objPHPExcel->getActiveSheet()->getCell('F7')->setValue($booking_hrs);
$objPHPExcel->getActiveSheet()->getStyle("A10:J10")->applyFromArray($verticalStyle);
$objPHPExcel->getActiveSheet()->getStyle("A".$total_cell.":J".$total_cell)->applyFromArray($FooterStyle);
foreach ($header_arr as $key => $value) {
$sub_header = $value+1;
$objPHPExcel->getActiveSheet()->getStyle("A".$value.":J".$value)->applyFromArray($verticalStyle);
$objPHPExcel->getActiveSheet()->getStyle("A".$sub_header.":J".$sub_header)->applyFromArray($verticalStyle);
}
foreach ($footer_arr as $key => $value) {
$objPHPExcel->getActiveSheet()->getStyle("A".$value.":J".$value)->applyFromArray($FooterStyle);
}
$objPHPExcel->getActiveSheet()->getStyle("A1:A".$final_footer)->applyFromArray($left_arr);
$objPHPExcel->getActiveSheet()->getStyle("J1:J".$final_footer)->applyFromArray($Right_arr);
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
$control_name = $employee_code."_".$emp_name."_".$this->control_name.'_'.$from_month.'_'.$to_month;
$filename = $control_name.".xls";
$objWriter = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($objPHPExcel);
ob_start();
$objWriter->save("php://output");
$xlsData = ob_get_contents();
ob_end_clean();
$response = array(
'success'=>true,
'filename'=>$filename,
'op' => 'ok',
'file' => "data:application/vnd.ms-excel;base64,".base64_encode($xlsData)
);
die(json_encode($response));
}
public function time_to_min($time){
$timeArr = explode(':', $time);
$decTime = ((int)$timeArr[0]*60) + (int)($timeArr[1]) + (int)($timeArr[2]/60);
return $decTime;
}
function decimalHours($time){
$hms = explode(":", $time);
return ((int)$hms[0] + ((int)$hms[1]/60) + ((int)$hms[2]/3600));
}
function AddPlayTime($times) {
$minutes = 0; //declare minutes either it gives Notice: Undefined variable
// loop throught all the times
foreach ($times as $time) {
list($hour, $minute) = explode(':', $time);
$minutes += (int)$hour * 60;
$minutes += (int)$minute;
}
$hours = floor((int)$minutes / 60);
$minutes -= $hours * 60;
// returns the time already formatted
return sprintf('%02d:%02d', $hours, $minutes);
}
}
?>