File: /home/cafsindia/cpaqua.cafsinfotech.in/dump/application/controllers/Project_analysis.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
require('./application/libraries/phpspreadsheet/autoload.php');
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class Project_analysis extends Action_controller{
public function __construct(){
parent::__construct('project_analysis');
$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_user_role = $this->session->userdata('logged_user_role');
$logged_emp_code = $this->session->userdata('logged_emp_code');
//Get Project
$fil_qry = '';
if((int)$logged_role === 1 || (int)$logged_role === 2){
$fil_qry = '';
}else{
$logged_team = str_replace(',', '|', $logged_team);
$fil_qry = ' and team REGEXP "(^|,)('.$logged_team.')(,|$)"';
}
$project_qry = 'SELECT prime_project_and_drawing_master_id as project_id,project_name FROM cw_project_and_drawing_master where trans_status = 1 '.$fil_qry.'';
$project_info = $this->db->query("CALL sp_a_run ('SELECT','$project_qry')");
$project_rslt = $project_info->result();
$project_info->next_result();
$project_list[""] = "---- Select ----";
foreach($project_rslt as $project){
$project_id = $project->project_id;
$project_name = $project->project_name;
$project_name = str_replace("xdbquot",'"',$project_name);
$project_name = str_replace("xquot","'",$project_name);
$project_name = str_replace("xxamp","&",$project_name);
$project_list[$project_id] = $project_name;
}
$data['project_list'] = $project_list;
$element_qry = 'SELECT prime_element_master_id as element_id,element_name FROM cw_element_master where trans_status = 1';
$element_info = $this->db->query("CALL sp_a_run ('SELECT','$element_qry')");
$element_rslt = $element_info->result();
$element_info->next_result();
$element_list[""] = "---- Select ----";
foreach($element_rslt as $element){
$element_id = $element->element_id;
$element_name = $element->element_name;
$element_list[$element_id] = $element_name;
}
$data['element_list'] = $element_list;
$data['encKey'] = $this->generateKey();
$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);
}
$project_id = $this->input->post("project_name");
$element_name = $this->input->post("element_name");
$role = $this->input->post("role");
if($element_name > 0){
$join_element = ' inner join cw_project_and_drawing_master_drawings on cw_project_and_drawing_master_drawings.prime_project_and_drawing_master_drawings_id = cw_time_sheet_time_line.drawing_no';
$join_column = ',cw_project_and_drawing_master_drawings.element_name';
$join_status = ' and cw_project_and_drawing_master_drawings.element_name = "'.$element_name.'" and cw_project_and_drawing_master_drawings.trans_status = 1';
}
$check_qry = 'SELECT cw_time_sheet_time_line.project FROM cw_time_sheet_time_line inner join cw_time_sheet on cw_time_sheet.prime_time_sheet_id = cw_time_sheet_time_line.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'.$join_element.' where cw_time_sheet_time_line.trans_status = 1 and cw_time_sheet.trans_status = 1'.$join_status.' and cw_time_sheet_time_line.project = "'.$project_id.'" and cw_time_sheet_time_line.emp_role in(5,4,3) group by cw_time_sheet_time_line.emp_role,cw_time_sheet_time_line.project,cw_time_sheet.employee_code,cw_time_sheet_time_line.work_type';
$check_info = $this->db->query("CALL sp_a_run ('SELECT','$check_qry')");
$check_rslt = $check_info->result_array();
$check_info->next_result();
$check_rslt_count = count($check_rslt);
if($check_rslt_count === 0){
echo json_encode(array('success' => false, 'message' => "No Data Available"));
exit(0);
}
$excel_columns = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R');
$objPHPExcel = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xls');//('Excel5');
$objPHPExcel = $objPHPExcel->load('./excel_download/detailer_report.xls');//default excel template
$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,
)
);
$teamStyle = array(
'borders' => array(
'bottom' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK
),
'top' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK
),
'left' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK
),
'right' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK
)
),
'alignment' => array(
'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
)
);
$style = array(
'alignment' => array(
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
),
'borders' => array(
'allborders' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
)
)
);
$FinalfooterStyle = 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' => '#FF8C00')
),
'alignment' => array(
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
)
);
$get_project_qry = 'SELECT prime_project_and_drawing_master_id as project_id,project_name,cw_client.client_name,cw_general_contractor.general_contractor,cw_project_and_drawing_master.job_category,detailing_status,estimation_hours,billing_unit,received_date FROM cw_project_and_drawing_master inner join cw_client on cw_client.prime_client_id = cw_project_and_drawing_master.client_name inner join cw_general_contractor on cw_general_contractor.prime_general_contractor_id = cw_project_and_drawing_master.general_contractor where cw_project_and_drawing_master.trans_status = 1 and cw_client.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());
$complexity_qry = 'SELECT prime_job_category_id as category_id,job_category FROM cw_job_category where cw_job_category.trans_status = 1';
$complexity_info = $this->db->query("CALL sp_a_run ('SELECT','$complexity_qry')");
$complexity_rslt = $complexity_info->result_array();
$complexity_info->next_result();
$complexity_rslt = array_reduce($complexity_rslt, function($result, $arr){
$result[$arr['category_id']] = $arr['job_category'];
return $result;
}, array());
$detailing_sts_qry = 'SELECT prime_detailing_status_id as detailing_id,detailing_status FROM cw_detailing_status where cw_detailing_status.trans_status = 1';
$detailing_sts_info = $this->db->query("CALL sp_a_run ('SELECT','$detailing_sts_qry')");
$detailing_sts_rslt = $detailing_sts_info->result_array();
$detailing_sts_info->next_result();
$detailing_sts_rslt = array_reduce($detailing_sts_rslt, function($result, $arr){
$result[$arr['detailing_id']] = $arr['detailing_status'];
return $result;
}, array());
$time_line_qry = 'SELECT sum(cw_tonnage_approval.actual_tonnage) as actual_tonnage,cw_time_sheet_time_line.project as project_id,cw_time_sheet_time_line.drawing_no as drawing_id FROM cw_time_sheet_time_line inner join cw_time_sheet on cw_time_sheet.prime_time_sheet_id = cw_time_sheet_time_line.prime_time_sheet_id 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_time_line.trans_status = 1 and cw_time_sheet.trans_status = 1 and cw_tonnage_approval.trans_status = 1 and cw_tonnage_approval.work_type = 1 group by cw_time_sheet_time_line.project,cw_time_sheet_time_line.drawing_no';
$time_line_info = $this->db->query("CALL sp_a_run ('SELECT','$time_line_qry')");
$time_line_rslt = $time_line_info->result_array();
$time_line_info->next_result();
$time_line_rslt = array_reduce($time_line_rslt, function($result, $arr){
$result[$arr['project_id']][$arr['drawing_id']] = $arr;
return $result;
}, array());
$co_reg_qry = 'SELECT IF(SEC_TO_TIME( SUM(time_to_sec(cw_co_register_log.billable_hours)))>"00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(cw_co_register_log.billable_hours))),"%H:%i"),"") as billable_hours,cw_co_register_log.rdd_no as project_id,cw_co_register_log.drawing_no as drawing_id FROM cw_co_register_log where cw_co_register_log.trans_status = 1 and cw_co_register_log.rdd_no = "'.$project_id.'"';
$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();
$claimed_hrs = $co_reg_rslt[0]['billable_hours'];
$co_register_qry = 'SELECT employee_code,SEC_TO_TIME(SUM(TIME_TO_SEC(cw_co_register_log.billable_hours))) as billable_hours,cw_co_register_log.rdd_no as project_id,cw_co_register_log.drawing_no as drawing_id FROM cw_co_register_log where cw_co_register_log.trans_status = 1 and cw_co_register_log.rdd_no = "'.$project_id.'" group by cw_co_register_log.rdd_no,employee_code';
$co_register_info = $this->db->query("CALL sp_a_run ('SELECT','$co_register_qry')");
$co_register_rlt = $co_register_info->result_array();
$co_register_info->next_result();
$co_register_rlt = array_reduce($co_register_rlt, function($result, $arr){
$result[$arr['project_id']][$arr['employee_code']] = $arr;
return $result;
}, array());
// ,IF(SEC_TO_TIME(SUM(time_to_sec(invoiced_hours)))>"00:00",TIME_FORMAT(SEC_TO_TIME(SUM(time_to_sec(invoiced_hours))),"%H:%i"),"")
// ,@indexer:=instr(invoiced_hours, "."),IF(SEC_TO_TIME(SUM((left(invoiced_hours, @indexer-1) * 60 + substr(invoiced_hours, @indexer+1))*60))>"00:00:00",TIME_FORMAT(SEC_TO_TIME(SUM((left(invoiced_hours, @indexer-1) * 60 + substr(invoiced_hours, @indexer+1))*60)),"%H:%i"),"") as invoiced_hours
$invoiced_qry = 'SELECT count(*) as invoiced_sheet,cw_project_and_drawing_master.prime_project_and_drawing_master_id as project_id,SUM(invoiced_ton) as invoiced_ton,IF(SEC_TO_TIME(SUM((left(invoiced_hours, instr(invoiced_hours, ".")-1) * 60 + substr(invoiced_hours, instr(invoiced_hours, ".")+1))*60))>"00:00:00",TIME_FORMAT(SEC_TO_TIME(SUM((left(invoiced_hours, instr(invoiced_hours, ".")-1) * 60 + substr(invoiced_hours, instr(invoiced_hours, ".")+1))*60)),"%H:%i"),"") as invoiced_hours FROM cw_project_and_drawing_master_invoice_details inner join cw_project_and_drawing_master on cw_project_and_drawing_master.prime_project_and_drawing_master_id = cw_project_and_drawing_master_invoice_details.prime_project_and_drawing_master_id where cw_project_and_drawing_master_invoice_details.trans_status = 1 and cw_project_and_drawing_master.trans_status = 1 and cw_project_and_drawing_master.prime_project_and_drawing_master_id ="'.$project_id.'"';
$invoiced_info = $this->db->query("CALL sp_a_run ('SELECT','$invoiced_qry')");
$invoiced_rslt = $invoiced_info->result_array();
$invoiced_info->next_result();
$invoiced_sheet = $invoiced_rslt[0]['invoiced_sheet'];
$invoiced_ton = $invoiced_rslt[0]['invoiced_ton'];
$invoiced_hours = $invoiced_rslt[0]['invoiced_hours'];
$billing_qry = 'SELECT prime_billing_unit_id,billing_unit FROM cw_billing_unit where cw_billing_unit.trans_status = 1';
$billing_info = $this->db->query("CALL sp_a_run ('SELECT','$billing_qry')");
$billing_rslt = $billing_info->result_array();
$billing_info->next_result();
$billing_rslt = array_reduce($billing_rslt, function($result, $arr){
$result[$arr['prime_billing_unit_id']] = $arr['billing_unit'];
return $result;
}, array());
$actual_tonnage = array_column($time_line_rslt[$project_id], 'actual_tonnage');
$actual_tonnage = array_sum($actual_tonnage);
$estimated_tons = $get_project_rslt[$project_id]['estimation_hours'];
$balance_tons = $estimated_tons - $actual_tonnage;
$project = $get_project_rslt[$project_id]['project_name'];
$project = str_replace("xdbquot",'"',$project);
$project = str_replace("xquot","'",$project);
$project = str_replace("xxamp","&",$project);
$job_category_id = $get_project_rslt[$project_id]['job_category'];
$detail_status_id = $get_project_rslt[$project_id]['detailing_status'];
$billing_unit_id = $get_project_rslt[$project_id]['billing_unit'];
$sheet_arr = $time_line_rslt[$project_id];
$sheet_count = count($sheet_arr);
$pm_wise_qry = 'SELECT cw_employees.team as team_id,cw_time_sheet_time_line.emp_role,cw_employees.emp_name,cw_time_sheet.employee_code,cw_time_sheet_time_line.project,cw_time_sheet_time_line.work_type,IF(SEC_TO_TIME( SUM(time_to_sec(study)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(study))),"%H:%i:%s"),"") as study,IF(SEC_TO_TIME( SUM(time_to_sec(detailing_time)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(detailing_time))),"%H:%i:%s"),"") as detailing_time,IF(SEC_TO_TIME( SUM(time_to_sec(discussion)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(discussion))),"%H:%i:%s"),"") as discussion,IF(SEC_TO_TIME( SUM(time_to_sec(checking)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(checking))),"%H:%i:%s"),"") as checking,IF(SEC_TO_TIME( SUM(time_to_sec(correction_time)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(correction_time))),"%H:%i:%s"),"") as correction_time,IF(SEC_TO_TIME( SUM(time_to_sec(rfi)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(rfi))),"%H:%i:%s"),"") as rfi,IF(SEC_TO_TIME( SUM(time_to_sec(aec)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(aec))),"%H:%i:%s"),"") as aec,IF(SEC_TO_TIME( SUM(time_to_sec(non_billable_hours)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(non_billable_hours))),"%H:%i:%s"),"") as non_billable_hours,IF(SEC_TO_TIME( SUM(time_to_sec(billable_hours)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(billable_hours))),"%H:%i:%s"),"") as billable_hours,IF(SEC_TO_TIME( SUM(time_to_sec(change_order_time)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(change_order_time))),"%H:%i:%s"),"") as change_order_time,IF(SEC_TO_TIME( SUM(time_to_sec(co_checking)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(co_checking))),"%H:%i:%s"),"") as co_checking,IF(SEC_TO_TIME( SUM(time_to_sec(bar_listing_time)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(bar_listing_time))),"%H:%i:%s"),"") as bar_listing_time,IF(SEC_TO_TIME( SUM(time_to_sec(qa_checking)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(qa_checking))),"%H:%i:%s"),"") as qa_checking'.$join_column.' FROM cw_time_sheet_time_line inner join cw_time_sheet on cw_time_sheet.prime_time_sheet_id = cw_time_sheet_time_line.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'.$join_element.' where cw_time_sheet_time_line.trans_status = 1 and cw_time_sheet.trans_status = 1'.$join_status.' and cw_time_sheet_time_line.project = "'.$project_id.'" and cw_time_sheet_time_line.emp_role in(5,4,3) and cw_time_sheet_time_line.team not in(78) group by cw_time_sheet_time_line.emp_role,cw_time_sheet_time_line.project,cw_time_sheet.employee_code,cw_time_sheet_time_line.work_type order by cw_time_sheet_time_line.emp_role asc';
$pm_wise_info = $this->db->query("CALL sp_a_run ('SELECT','$pm_wise_qry')");
$pm_wise_rslt = $pm_wise_info->result_array();
$pm_wise_info->next_result();
$pm_wise_rslt = array_reduce($pm_wise_rslt, function($result, $arr){
$result[$arr['emp_role']][$arr['project']][$arr['employee_code']][$arr['work_type']] = $arr;
return $result;
}, array());
$team_qry = 'SELECT prime_team_id,team_name FROM cw_team where cw_team.trans_status = 1';
$team_info = $this->db->query("CALL sp_a_run ('SELECT','$team_qry')");
$team_rslt = $team_info->result_array();
$team_info->next_result();
$team_rslt = array_reduce($team_rslt, function($result, $arr){
$result[$arr['prime_team_id']] = $arr['team_name'];
return $result;
}, array());
$qc_team_qry = 'SELECT cw_employees.team as team_id,cw_time_sheet_time_line.emp_role,cw_employees.emp_name,cw_time_sheet.employee_code,cw_time_sheet_time_line.project,cw_time_sheet_time_line.work_type,IF(SEC_TO_TIME( SUM(time_to_sec(study)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(study))),"%H:%i:%s"),"") as study,IF(SEC_TO_TIME( SUM(time_to_sec(detailing_time)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(detailing_time))),"%H:%i:%s"),"") as detailing_time,IF(SEC_TO_TIME( SUM(time_to_sec(discussion)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(discussion))),"%H:%i:%s"),"") as discussion,IF(SEC_TO_TIME( SUM(time_to_sec(checking)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(checking))),"%H:%i:%s"),"") as checking,IF(SEC_TO_TIME( SUM(time_to_sec(correction_time)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(correction_time))),"%H:%i:%s"),"") as correction_time,IF(SEC_TO_TIME( SUM(time_to_sec(rfi)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(rfi))),"%H:%i:%s"),"") as rfi,IF(SEC_TO_TIME( SUM(time_to_sec(aec)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(aec))),"%H:%i:%s"),"") as aec,IF(SEC_TO_TIME( SUM(time_to_sec(non_billable_hours)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(non_billable_hours))),"%H:%i:%s"),"") as non_billable_hours,IF(SEC_TO_TIME( SUM(time_to_sec(billable_hours)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(billable_hours))),"%H:%i:%s"),"") as billable_hours,IF(SEC_TO_TIME( SUM(time_to_sec(change_order_time)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(change_order_time))),"%H:%i:%s"),"") as change_order_time,IF(SEC_TO_TIME( SUM(time_to_sec(co_checking)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(co_checking))),"%H:%i:%s"),"") as co_checking,IF(SEC_TO_TIME( SUM(time_to_sec(bar_listing_time)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(bar_listing_time))),"%H:%i:%s"),"") as bar_listing_time,IF(SEC_TO_TIME( SUM(time_to_sec(qa_checking)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(qa_checking))),"%H:%i:%s"),"") as qa_checking'.$join_column.' FROM cw_time_sheet_time_line inner join cw_time_sheet on cw_time_sheet.prime_time_sheet_id = cw_time_sheet_time_line.prime_time_sheet_id inner join cw_employees on cw_employees.employee_code = cw_time_sheet.employee_code'.$join_element.' where cw_time_sheet.trans_status = 1 and cw_time_sheet_time_line.trans_status = 1 and cw_time_sheet_time_line.project = "'.$project_id.'" and cw_time_sheet_time_line.emp_role in(5,4,3) and cw_time_sheet_time_line.team in(78)'.$join_status.' and cw_employees.trans_status = 1 group by cw_time_sheet_time_line.emp_role,cw_time_sheet_time_line.project,cw_time_sheet.employee_code,cw_time_sheet_time_line.work_type order by cw_time_sheet_time_line.emp_role asc';
$qc_team_info = $this->db->query("CALL sp_a_run ('SELECT','$qc_team_qry')");
$qc_team_rslt = $qc_team_info->result_array();
$qc_team_info->next_result();
$qc_team_rslt = array_reduce($qc_team_rslt, function($result, $arr){
$result[$arr['emp_role']][$arr['project']][$arr['employee_code']][$arr['work_type']] = $arr;
return $result;
}, array());
$element_qry = 'SELECT prime_element_master_id as element_id,element_name FROM cw_element_master where trans_status = 1';
$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['element_name'];
return $result;
}, array());
$submission_date_wise_qry = 'select cw_time_sheet.entry_date,cw_time_sheet_time_line.project from cw_time_sheet_time_line inner join cw_time_sheet on cw_time_sheet.prime_time_sheet_id = cw_time_sheet_time_line.prime_time_sheet_id 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_time_line.trans_status = 1 and cw_time_sheet.trans_status = 1 and cw_time_sheet_time_line.project = "'.$project_id.'" and cw_tonnage_approval.work_type = 1 and cw_tonnage_approval.trans_status = 1 order by cw_time_sheet.entry_date desc';
$submission_date_wise_info = $this->db->query("CALL sp_a_run ('SELECT','$submission_date_wise_qry')");
$submission_date_wise_result = $submission_date_wise_info->result_array();
$submission_date_wise_info->next_result();
$last_submission = $submission_date_wise_result[0]['entry_date'];
if($last_submission){
$last_submission = date('d-M-Y',strtotime($last_submission));
}
$export_arr = array();
$export_arr['pro_name_header']['pro_name_header'] = 'Project Analysis Report (PAR) :';
$export_arr['pro_name']['head_key'] = 'Project :';
$export_arr['pro_name']['head_value'] = $project;
$export_arr['client_name']['head_key'] = 'Client :';
$export_arr['client_name']['head_value'] = $get_project_rslt[$project_id]['client_name'];
$export_arr['general_contractor']['head_key'] = 'GC :';
$export_arr['general_contractor']['head_value'] = $get_project_rslt[$project_id]['general_contractor'];
$export_arr['job_category']['head_key'] = 'Complexity :';
$export_arr['job_category']['head_value'] = $complexity_rslt[$job_category_id];
$export_arr['detailing_sts']['head_key'] = 'Detailing Status :';
$export_arr['detailing_sts']['head_value'] = $detailing_sts_rslt[$detail_status_id];
$export_arr['pro_name']['empty_key1'] = '';
$export_arr['pro_name']['empty_val1'] = '';
$export_arr['client_name']['empty_key1'] = '';
$export_arr['client_name']['empty_val1'] = '';
$export_arr['general_contractor']['empty_key1'] = '';
$export_arr['general_contractor']['empty_val1'] = '';
$export_arr['job_category']['empty_key1'] = '';
$export_arr['job_category']['empty_val1'] = '';
$export_arr['detailing_sts']['empty_key1'] = '';
$export_arr['detailing_sts']['empty_val1'] = '';
$export_arr['pro_name']['header_key'] = 'Estimated Weight :';
$export_arr['pro_name']['header_val'] = $this->two_decimal($get_project_rslt[$project_id]['estimation_hours']);
$export_arr['client_name']['header_key'] = 'Detailed Weight :';
$export_arr['client_name']['header_val'] = $this->two_decimal($actual_tonnage);
$export_arr['general_contractor']['header_key'] = 'Balance Weight :';
$export_arr['general_contractor']['header_val'] = $this->two_decimal($balance_tons);
$export_arr['job_category']['header_key'] = 'Hours Claimed :';
$export_arr['job_category']['header_val'] = $claimed_hrs;
$export_arr['detailing_sts']['header_key'] = 'Sheets Detailed :';
$export_arr['detailing_sts']['header_val'] = $sheet_count;
$export_arr['pro_name']['empty_key2'] = '';
$export_arr['pro_name']['empty_val2'] = '';
$export_arr['client_name']['empty_key2'] = '';
$export_arr['client_name']['empty_val2'] = '';
$export_arr['general_contractor']['empty_key2'] = '';
$export_arr['general_contractor']['empty_val2'] = '';
$export_arr['job_category']['empty_key2'] = '';
$export_arr['job_category']['empty_val2'] = '';
$export_arr['detailing_sts']['empty_key2'] = '';
$export_arr['detailing_sts']['empty_val2'] = '';
$export_arr['pro_name']['header_keys'] = 'Invoiced Tons :';
$export_arr['pro_name']['header_value'] = $this->two_decimal($invoiced_ton);
$export_arr['client_name']['header_keys'] = 'Invoiced Hours :';
$export_arr['client_name']['header_value'] = $invoiced_hours;
$export_arr['general_contractor']['header_keys'] = 'Invoiced Sheets :';
$export_arr['general_contractor']['header_value'] = $invoiced_sheet;
$export_arr['job_category']['header_keys'] = 'Billing Unit :';
$export_arr['job_category']['header_value'] = $billing_rslt[$billing_unit_id];
$export_arr['pro_date']['date_head_key'] = 'Job Received Date :';
$export_arr['pro_date']['date_head_val'] = date('d-M-Y',strtotime($get_project_rslt[$project_id]['received_date']));
$export_arr['pro_date']['e_date_head'] = '';
$export_arr['pro_date']['e_date_val'] = '';
$export_arr['pro_date']['date_head_keys'] = 'Last Submission Date :';
$export_arr['pro_date']['date_head_vals'] = $last_submission;
$export_arr['empty_cell1']['empty_val1'] = '';
$export_arr['element_col']['ele_overall'] = "Element - Overall";
if((int)$element_name > 0){
$element = $element_rslt[$element_name];
$export_arr['element_col'][$element] = $element;
}
$export_arr['element_two']['element_two']= '';
$export_arr['empty_cell2']['empty_val2']= '';
$export_arr['values']['team_name'] = 'Team/Name';
$export_arr['values']['study1'] = 'STY';
$export_arr['values']['detailing'] = 'DET';
$export_arr['values']['discussion1'] = 'DIS';
$export_arr['values']['checking1'] = 'CHK(Self/TL+QA)';
$export_arr['values']['correction1'] = 'COR';
$export_arr['values']['rfi'] = 'RFI';
$export_arr['values']['study2'] = 'STY';
$export_arr['values']['aec'] = 'AEC';
$export_arr['values']['checking2'] = 'CHK(Self/TL+QA)';
$export_arr['values']['correction2'] = 'COR';
$export_arr['values']['non_bill'] = 'NBH';
$export_arr['values']['billable'] = 'BH';
$export_arr['values']['discussion2'] = 'DIS';
$export_arr['values']['change_order'] = 'PCO';
$export_arr['values']['co_checking'] = 'CO CHK';
$export_arr['values']['work'] = 'Listing Work';
$export_arr['values']['total_val'] = 'Total Hours :';
$role_name = array('3'=>'Project Manager','4'=> 'Checker','5' => 'Detailer');
$i = 12;
$header_arr = array();
$detailer_detail = array();
$detailer_revision = array();
foreach ($pm_wise_rslt as $rol_key => $pm_wise) {
$header_arr[] = $i+1;
$role_col = "emp_col_".$rol_key;
$export_arr[$role_col]['roles'] = $role_name[$rol_key];
$project_wise = $pm_wise[$project_id];
$study1_arr = array();
$study2_arr = array();
$detailing_arr = array();
$discus1_arr = array();
$discus2_arr = array();
$checking1_arr = array();
$checking2_arr = array();
$rfi_arr = array();
$cor1_arr = array();
$cor2_arr = array();
$non_bill_arr = array();
$bill_arr = array();
$change_order_arr = array();
$co_checking_arr = array();
$bar_list_arr = array();
$total_arr = array();
$i++;
foreach ($project_wise as $emp_key => $pm_value) {
$team_val = $pm_value[1]['team_id'];
if(!$team_val){
$team_val = $pm_value[2]['team_id'];
if(!$team_val){
$team_val = $pm_value[3]['team_id'];
if(!$team_val){
$team_val = $pm_value[4]['team_id'];
}
}
}
$get_team = explode(',', $team_val);
$emp_team_name = implode(', ', array_intersect_key($team_rslt, array_flip(explode(',', $team_val))));
if($rol_key === 3){
$checking1 = $pm_value[1]['qa_checking'];
$checking2 = $pm_value[2]['qa_checking'];
}else{
$checking1 = $pm_value[1]['checking'];
$checking2 = $pm_value[2]['checking'];
}
$emp_name = $pm_value[1]['emp_name'];
if(!$emp_name){
$emp_name = $pm_value[2]['emp_name'];
if(!$emp_name){
$emp_name = $pm_value[3]['emp_name'];
if(!$emp_name){
$emp_name = $pm_value[4]['emp_name'];
}
}
}
$arr_val = "values_".$rol_key."_".$project_id."_".$emp_key;
$total_sum = array();
$total_sum[] = $pm_value[1]['study'];
$total_sum[] = $pm_value[1]['detailing_time'];
$total_sum[] = $pm_value[1]['discussion'];
$total_sum[] = $checking1;
$total_sum[] = $pm_value[1]['correction_time'];
$total_sum[] = $pm_value[2]['rfi'];
$total_sum[] = $pm_value[2]['study'];
$total_sum[] = $pm_value[2]['aec'];
$total_sum[] = $checking2;
$total_sum[] = $pm_value[2]['correction_time'];
$total_sum[] = $pm_value[2]['non_billable_hours'];
$total_sum[] = $co_register_rlt[$project_id][$emp_key]['billable_hours'];
$total_sum[] = $pm_value[2]['discussion'];
$total_sum[] = $pm_value[2]['change_order_time'];
$total_sum[] = $pm_value[2]['co_checking'];
$total_sum[] = $pm_value[3]['bar_listing_time'];
$sum_tot_val = $this->AddPlayTime($total_sum);
$study1_arr[$arr_val] = $pm_value[1]['study'];
$study2_arr[$arr_val] = $pm_value[2]['study'];
$detailing_arr[$arr_val] = $pm_value[1]['detailing_time'];
$discus1_arr[$arr_val] = $pm_value[1]['discussion'];
$discus2_arr[$arr_val] = $pm_value[2]['discussion'];
$checking1_arr[$arr_val] = $checking1;
$checking2_arr[$arr_val] = $checking2;
$rfi_arr[$arr_val] = $pm_value[2]['rfi'];
$cor2_arr[$arr_val] = $pm_value[2]['correction_time'];
$cor1_arr[$arr_val] = $pm_value[1]['correction_time'];
$aec_arr[$arr_val] = $pm_value[2]['aec'];
$non_bill_arr[$arr_val] = $pm_value[2]['non_billable_hours'];
$bill_arr[$arr_val] = $co_register_rlt[$project_id][$emp_key]['billable_hours'];
$change_order_arr[$arr_val] = $pm_value[2]['change_order_time'];
$co_checking_arr[$arr_val] = $pm_value[2]['co_checking'];
$bar_list_arr[$arr_val] = $pm_value[3]['bar_listing_time'];
$total_arr[$arr_val] = $sum_tot_val;
$export_arr[$arr_val]['team_name'] = $emp_name."/".$emp_team_name;
$export_arr[$arr_val]['study1'] = $pm_value[1]['study'];
$export_arr[$arr_val]['detailing'] = $pm_value[1]['detailing_time'];
$export_arr[$arr_val]['discussion1'] = $pm_value[1]['discussion'];
$export_arr[$arr_val]['checking1'] = $checking1;
$export_arr[$arr_val]['correction1'] = $pm_value[1]['correction_time'];
$export_arr[$arr_val]['rfi'] = $pm_value[2]['rfi'];
$export_arr[$arr_val]['study2'] = $pm_value[2]['study'];
$export_arr[$arr_val]['aec'] = $pm_value[2]['aec'];
$export_arr[$arr_val]['checking2'] = $checking2;
$export_arr[$arr_val]['correction2'] = $pm_value[2]['correction_time'];
$export_arr[$arr_val]['non_bill'] = $pm_value[2]['non_billable_hours'];
$export_arr[$arr_val]['billable'] = $co_register_rlt[$project_id][$emp_key]['billable_hours'];
$export_arr[$arr_val]['discussion2'] = $pm_value[2]['discussion'];
$export_arr[$arr_val]['change_order'] = $pm_value[2]['change_order_time'];
$export_arr[$arr_val]['co_checking'] = $pm_value[2]['co_checking'];
$export_arr[$arr_val]['work'] = $pm_value[3]['bar_listing_time'];
$export_arr[$arr_val]['total_val'] = $sum_tot_val;
$i++;
}
$total_col = 'total_sum_'.$rol_key;
$study1sum = $this->AddPlayTime($study1_arr);
$study2sum = $this->AddPlayTime($study2_arr);
$detailing_sum = $this->AddPlayTime($detailing_arr);
$discus1_sum = $this->AddPlayTime($discus1_arr);
$discus2_sum = $this->AddPlayTime($discus2_arr);
$checking1_sum = $this->AddPlayTime($checking1_arr);
$checking2_sum = $this->AddPlayTime($checking2_arr);
$rfi_sum = $this->AddPlayTime($rfi_arr);
$cor2_sum = $this->AddPlayTime($cor2_arr);
$cor1_sum = $this->AddPlayTime($cor1_arr);
$aec_sum = $this->AddPlayTime($aec_arr);
$non_bill_sum = $this->AddPlayTime($non_bill_arr);
$bill_sum = $this->AddPlayTime($bill_arr);
$change_order_sum = $this->AddPlayTime($change_order_arr);
$co_checking_sum = $this->AddPlayTime($co_checking_arr);
$bar_list_sum = $this->AddPlayTime($bar_list_arr);
$total_arr_sum = $this->AddPlayTime($total_arr);
$export_arr[$total_col]['team_name'] = $role_name[$rol_key]." Hours";
$export_arr[$total_col]['study1'] = $study1sum;
$export_arr[$total_col]['detailing'] = $detailing_sum;
$export_arr[$total_col]['discussion1'] = $discus1_sum;
$export_arr[$total_col]['checking1'] = $checking1_sum;
$export_arr[$total_col]['correction1'] = $cor1_sum;
$export_arr[$total_col]['rfi'] = $rfi_sum;
$export_arr[$total_col]['study2'] = $study2sum;
$export_arr[$total_col]['aec'] = $aec_sum;
$export_arr[$total_col]['checking2'] = $checking2_sum;
$export_arr[$total_col]['correction2'] = $cor2_sum;
$export_arr[$total_col]['non_bill'] = $non_bill_sum;
$export_arr[$total_col]['billable'] = $bill_sum;
$export_arr[$total_col]['discussion2'] = $discus2_sum;
$export_arr[$total_col]['change_order'] = $change_order_sum;
$export_arr[$total_col]['co_checking'] = $co_checking_sum;
$export_arr[$total_col]['work'] = $bar_list_sum;
$export_arr[$total_col]['total_val'] = $total_arr_sum;
if($rol_key === 5){
$detailer_detail[] = $study1sum;
$detailer_detail[] = $detailing_sum;
$detailer_detail[] = $discus1_sum;
$detailer_detail[] = $checking1_sum;
$detailer_detail[] = $cor1_sum;
$detailer_revision[] = $study2sum;
$detailer_revision[] = $aec_sum;
$detailer_revision[] = $checking2_sum;
$detailer_revision[] = $cor2_sum;
$detailer_revision[] = $non_bill_sum;
$detailer_revision[] = $bill_sum;
$detailer_revision[] = $discus2_sum;
$detailer_revision[] = $change_order_sum;
$detailer_revision[] = $co_checking_sum;
$detailer_rfi = $rfi_sum;
$detailer_bar_list = $bar_list_sum;
$detail_productive_arr[] = $detailing_sum;
$detail_productive_arr[] = $checking1_sum;
$detail_productive_arr[] = $aec_sum;
$detail_productive_arr[] = $checking2_sum;
$detail_productive_arr[] = $non_bill_sum;
$detail_productive_arr[] = $bill_sum;
}
$study1sum_tot[] = $study1sum;
$detailing_sum_tot[] = $detailing_sum;
$discus1_sum_tot[] = $discus1_sum;
$checking1_sum_tot[] = $checking1_sum;
$cor1_sum_tot[] = $cor1_sum;
$rfi_sum_tot[] = $rfi_sum;
$study2sum_tot[] = $study2sum;
$aec_sum_tot[] = $aec_sum;
$checking2_sum_tot[] = $checking2_sum;
$cor2_sum_tot[] = $cor2_sum;
$non_bill_sum_tot[] = $non_bill_sum;
$bill_sum_tot[] = $bill_sum;
$discus2_sum_tot[] = $discus2_sum;
$change_order_sum_tot[] = $change_order_sum;
$co_checking_sum_tot[] = $co_checking_sum;
$bar_list_sum_tot[] = $bar_list_sum;
$total_arr_sum_tot[] = $total_arr_sum;
$i = $i+1;
$footer_arr[] = $i;
}
$study1_tot_sum = $this->AddPlayTime($study1sum_tot);
$detailing_tot_sum = $this->AddPlayTime($detailing_sum_tot);
$discus1_tot_sum = $this->AddPlayTime($discus1_sum_tot);
$checking1_tot_sum = $this->AddPlayTime($checking1_sum_tot);
$cor1_tot_sum = $this->AddPlayTime($cor1_sum_tot);
$rfi_tot_sum = $this->AddPlayTime($rfi_sum_tot);
$study2_tot_sum = $this->AddPlayTime($study2sum_tot);
$aec_tot_sum = $this->AddPlayTime($aec_sum_tot);
$checking2_tot_sum = $this->AddPlayTime($checking2_sum_tot);
$cor2_tot_sum = $this->AddPlayTime($cor2_sum_tot);
$non_bill_tot_sum = $this->AddPlayTime($non_bill_sum_tot);
$bill_tot_sum = $this->AddPlayTime($bill_sum_tot);
$discus2_tot_sum = $this->AddPlayTime($discus2_sum_tot);
$change_order_tot_sum = $this->AddPlayTime($change_order_sum_tot);
$co_checking_tot_sum = $this->AddPlayTime($co_checking_sum_tot);
$bar_list_tot_sum = $this->AddPlayTime($bar_list_sum_tot);
$total_arr_tot_sum = $this->AddPlayTime($total_arr_sum_tot);
$export_arr['qa_hours']['qa_hours'] = 'QC Hours';
$qc_study1_arr = array();
$qc_study2_arr = array();
$qc_detailing_arr = array();
$qc_discus1_arr = array();
$qc_discus2_arr = array();
$qc_checking1_arr = array();
$qc_checking2_arr = array();
$qc_rfi_arr = array();
$qc_cor1_arr = array();
$qc_cor2_arr = array();
$qc_non_bill_arr = array();
$qc_bill_arr = array();
$qc_change_order_arr = array();
$qc_co_checking_arr = array();
$qc_bar_list_arr = array();
$qc_total_arr = array();
$j = $i+1;
foreach ($qc_team_rslt as $qc_rol_key => $qc_team) {
$qc_wise = $qc_team[$project_id];
// $j++;
foreach ($qc_wise as $qc_emp_key => $qc_val) {
$arr_vals = "qcvalues_".$qc_rol_key."_".$project_id."_".$qc_emp_key;
$team_val = $qc_val[1]['team_id'];
if(!$team_val){
$team_val = $qc_val[2]['team_id'];
if(!$team_val){
$team_val = $qc_val[3]['team_id'];
if(!$team_val){
$team_val = $qc_val[4]['team_id'];
}
}
}
$get_team = explode(',', $team_val);
$emp_team_name = implode(', ', array_intersect_key($team_rslt, array_flip(explode(',', $team_val))));
if($qc_rol_key === 3){
$checking1 = $qc_val[1]['qa_checking'];
$checking2 = $qc_val[2]['qa_checking'];
}else{
$checking1 = $qc_val[1]['checking'];
$checking2 = $qc_val[2]['checking'];
}
$qc_emp_name = $qc_val[1]['emp_name'];
if(!$qc_emp_name){
$qc_emp_name = $qc_val[2]['emp_name'];
if(!$qc_emp_name){
$qc_emp_name = $qc_val[3]['emp_name'];
if(!$qc_emp_name){
$qc_emp_name = $qc_val[4]['emp_name'];
}
}
}
$qc_total_sum = array();
$qc_total_sum[] = $qc_val[1]['study'];
$qc_total_sum[] = $qc_val[1]['detailing_time'];
$qc_total_sum[] = $qc_val[1]['discussion'];
$qc_total_sum[] = $checking1;
$qc_total_sum[] = $qc_val[1]['correction_time'];
$qc_total_sum[] = $qc_val[2]['rfi'];
$qc_total_sum[] = $qc_val[2]['study'];
$qc_total_sum[] = $qc_val[2]['aec'];
$qc_total_sum[] = $checking2;
$qc_total_sum[] = $qc_val[2]['correction_time'];
$qc_total_sum[] = $qc_val[2]['non_billable_hours'];
$qc_total_sum[] = $co_register_rlt[$project_id][$qc_emp_key]['billable_hours'];
$qc_total_sum[] = $qc_val[2]['discussion'];
$qc_total_sum[] = $qc_val[2]['change_order_time'];
$qc_total_sum[] = $qc_val[2]['co_checking'];
$qc_total_sum[] = $qc_val[3]['bar_listing_time'];
$qc_sum_tot_val = $this->AddPlayTime($qc_total_sum);
$qc_study1_arr[$arr_vals] = $qc_val[1]['study'];
$qc_study2_arr[$arr_vals] = $qc_val[2]['study'];
$qc_detailing_arr[$arr_vals] = $qc_val[1]['detailing_time'];
$qc_discus1_arr[$arr_vals] = $qc_val[1]['discussion'];
$qc_discus2_arr[$arr_vals] = $qc_val[2]['discussion'];
$qc_checking1_arr[$arr_vals] = $checking1;
$qc_checking2_arr[$arr_vals] = $checking2;
$qc_rfi_arr[$arr_vals] = $qc_val[2]['rfi'];
$qc_cor2_arr[$arr_vals] = $qc_val[2]['correction_time'];
$qc_cor1_arr[$arr_vals] = $qc_val[1]['correction_time'];
$qc_aec_arr[$arr_vals] = $qc_val[2]['aec'];
$qc_non_bill_arr[$arr_vals] = $qc_val[2]['non_billable_hours'];
$qc_bill_arr[$arr_vals] = $co_register_rlt[$project_id][$qc_emp_key]['billable_hours'];
$qc_change_order_arr[$arr_vals] = $qc_val[2]['change_order_time'];
$qc_co_checking_arr[$arr_vals] = $qc_val[2]['co_checking'];
$qc_bar_list_arr[$arr_vals] = $qc_val[3]['bar_listing_time'];
$qc_total_arr[$arr_vals] = $qc_sum_tot_val;
$export_arr[$arr_vals]['team_name'] = $qc_emp_name."/".$emp_team_name;
$export_arr[$arr_vals]['study1'] = $qc_val[1]['study'];
$export_arr[$arr_vals]['detailing'] = $qc_val[1]['detailing_time'];
$export_arr[$arr_vals]['discussion1'] = $qc_val[1]['discussion'];
$export_arr[$arr_vals]['checking1'] = $checking1;
$export_arr[$arr_vals]['correction1'] = $qc_val[1]['correction_time'];
$export_arr[$arr_vals]['rfi'] = $qc_val[2]['rfi'];
$export_arr[$arr_vals]['study2'] = $qc_val[2]['study'];
$export_arr[$arr_vals]['aec'] = $qc_val[2]['aec'];
$export_arr[$arr_vals]['checking2'] = $checking2;
$export_arr[$arr_vals]['correction2'] = $qc_val[2]['correction_time'];
$export_arr[$arr_vals]['non_bill'] = $qc_val[2]['non_billable_hours'];
// $export_arr[$arr_vals]['billable'] = $qc_val[2]['billable_hours'];
$export_arr[$arr_vals]['billable'] = $co_register_rlt[$project_id][$qc_emp_key]['billable_hours'];
$export_arr[$arr_vals]['discussion2'] = $qc_val[2]['discussion'];
$export_arr[$arr_vals]['change_order'] = $qc_val[2]['change_order_time'];
$export_arr[$arr_vals]['co_checking'] = $qc_val[2]['co_checking'];
$export_arr[$arr_vals]['work'] = $qc_val[3]['bar_listing_time'];
$export_arr[$arr_vals]['total_val'] = $qc_sum_tot_val;
}
$j++;
$j+1;
}
$qc_total_col = 'qc_total_sum_'.$qc_rol_key;
$qc_study1sum = $this->AddPlayTime($qc_study1_arr);
$qc_study2sum = $this->AddPlayTime($qc_study2_arr);
$qc_detailing_sum = $this->AddPlayTime($qc_detailing_arr);
$qc_discus1_sum = $this->AddPlayTime($qc_discus1_arr);
$qc_discus2_sum = $this->AddPlayTime($qc_discus2_arr);
$qc_checking1_sum = $this->AddPlayTime($qc_checking1_arr);
$qc_checking2_sum = $this->AddPlayTime($qc_checking2_arr);
$qc_rfi_sum = $this->AddPlayTime($qc_rfi_arr);
$qc_cor2_sum = $this->AddPlayTime($qc_cor2_arr);
$qc_cor1_sum = $this->AddPlayTime($qc_cor1_arr);
$qc_aec_sum = $this->AddPlayTime($qc_aec_arr);
$qc_non_bill_sum = $this->AddPlayTime($qc_non_bill_arr);
$qc_bill_sum = $this->AddPlayTime($qc_bill_arr);
$qc_change_order_sum = $this->AddPlayTime($qc_change_order_arr);
$qc_co_checking_sum = $this->AddPlayTime($qc_co_checking_arr);
$qc_bar_list_sum = $this->AddPlayTime($qc_bar_list_arr);
$qc_total_arr_sum = $this->AddPlayTime($qc_total_arr);
$export_arr[$qc_total_col]['team_name'] = "QC Hours";
$export_arr[$qc_total_col]['study1'] = $qc_study1sum;
$export_arr[$qc_total_col]['detailing'] = $qc_detailing_sum;
$export_arr[$qc_total_col]['discussion1'] = $qc_discus1_sum;
$export_arr[$qc_total_col]['checking1'] = $qc_checking1_sum;
$export_arr[$qc_total_col]['correction1'] = $qc_cor1_sum;
$export_arr[$qc_total_col]['rfi'] = $qc_rfi_sum;
$export_arr[$qc_total_col]['study2'] = $qc_study2sum;
$export_arr[$qc_total_col]['aec'] = $qc_aec_sum;
$export_arr[$qc_total_col]['checking2'] = $qc_checking2_sum;
$export_arr[$qc_total_col]['correction2'] = $qc_cor2_sum;
$export_arr[$qc_total_col]['non_bill'] = $qc_non_bill_sum;
$export_arr[$qc_total_col]['billable'] = $qc_bill_sum;
$export_arr[$qc_total_col]['discussion2'] = $qc_discus2_sum;
$export_arr[$qc_total_col]['change_order'] = $qc_change_order_sum;
$export_arr[$qc_total_col]['co_checking'] = $qc_co_checking_sum;
$export_arr[$qc_total_col]['work'] = $qc_bar_list_sum;
$export_arr[$qc_total_col]['total_val'] = $qc_total_arr_sum;
$qc_study1sum = array($qc_study1sum);
$qc_detailing_sum = array($qc_detailing_sum);
$qc_discus1_sum = array($qc_discus1_sum);
$qc_checking1_sum = array($qc_checking1_sum);
$qc_cor1_sum = array($qc_cor1_sum);
$qc_rfi_sum = array($qc_rfi_sum);
$qc_study2sum = array($qc_study2sum);
$qc_aec_sum = array($qc_aec_sum);
$qc_checking2_sum = array($qc_checking2_sum);
$qc_cor2_sum = array($qc_cor2_sum);
$qc_non_bill_sum = array($qc_non_bill_sum);
$qc_bill_sum = array($qc_bill_sum);
$qc_discus2_sum = array($qc_discus2_sum);
$qc_change_order_sum= array($qc_change_order_sum);
$qc_co_checking_sum = array($qc_co_checking_sum);
$qc_bar_list_sum = array($qc_bar_list_sum);
$qc_total_arr_sum = array($qc_total_arr_sum);
$total_sum_study1 = array_merge($study1sum_tot,$qc_study1sum);
$total_sum_detailing = array_merge($detailing_sum_tot,$qc_detailing_sum);
$total_sum_discus1 = array_merge($discus1_sum_tot,$qc_discus1_sum);
$total_sum_checking1 = array_merge($checking1_sum_tot,$qc_checking1_sum);
$total_sum_corr1 = array_merge($cor1_sum_tot,$qc_cor1_sum);
$total_sum_rfi = array_merge($rfi_sum_tot,$qc_rfi_sum);
$total_sum_study2 = array_merge($study2sum_tot,$qc_study2sum);
$total_sum_aec = array_merge($aec_sum_tot,$qc_aec_sum);
$total_sum_checking2 = array_merge($checking2_sum_tot,$qc_checking2_sum);
$total_sum_corr2 = array_merge($cor2_sum_tot,$qc_cor2_sum);
$total_sum_nonbill = array_merge($non_bill_sum_tot,$qc_non_bill_sum);
$total_sum_bill = array_merge($bill_sum_tot,$qc_bill_sum);
$total_sum_discus2 = array_merge($discus2_sum_tot,$qc_discus2_sum);
$total_sum_change = array_merge($change_order_sum_tot,$qc_change_order_sum);
$total_sum_co_check = array_merge($co_checking_sum_tot,$qc_co_checking_sum);
$total_sum_bar_list = array_merge($bar_list_sum_tot,$qc_bar_list_sum);
$total_sum_total = array_merge($total_arr_sum_tot,$qc_total_arr_sum);
$total_sum_study1 = $this->AddPlayTime($total_sum_study1);
$total_sum_detailing = $this->AddPlayTime($total_sum_detailing);
$total_sum_discus1 = $this->AddPlayTime($total_sum_discus1);
$total_sum_checking1 = $this->AddPlayTime($total_sum_checking1);
$total_sum_corr1 = $this->AddPlayTime($total_sum_corr1);
$total_sum_rfi = $this->AddPlayTime($total_sum_rfi);
$total_sum_study2 = $this->AddPlayTime($total_sum_study2);
$total_sum_aec = $this->AddPlayTime($total_sum_aec);
$total_sum_checking2 = $this->AddPlayTime($total_sum_checking2);
$total_sum_corr2 = $this->AddPlayTime($total_sum_corr2);
$total_sum_nonbill = $this->AddPlayTime($total_sum_nonbill);
$total_sum_bill = $this->AddPlayTime($total_sum_bill);
$total_sum_discus2 = $this->AddPlayTime($total_sum_discus2);
$total_sum_change = $this->AddPlayTime($total_sum_change);
$total_sum_co_check = $this->AddPlayTime($total_sum_co_check);
$total_sum_bar_list = $this->AddPlayTime($total_sum_bar_list);
$total_sum_total = $this->AddPlayTime($total_sum_total);
$final_total = 'total_sum_val';
$export_arr[$final_total]['team_name'] = "TOTAL";
$export_arr[$final_total]['study1'] = $total_sum_study1;
$export_arr[$final_total]['detailing'] = $total_sum_detailing;
$export_arr[$final_total]['discussion1'] = $total_sum_discus1;
$export_arr[$final_total]['checking1'] = $total_sum_checking1;
$export_arr[$final_total]['correction1'] = $total_sum_corr1;
$export_arr[$final_total]['rfi'] = $total_sum_rfi;
$export_arr[$final_total]['study2'] = $total_sum_study2;
$export_arr[$final_total]['aec'] = $total_sum_aec;
$export_arr[$final_total]['checking2'] = $total_sum_checking2;
$export_arr[$final_total]['correction2'] = $total_sum_corr2;
$export_arr[$final_total]['non_bill'] = $total_sum_nonbill;
$export_arr[$final_total]['billable'] = $total_sum_bill;
$export_arr[$final_total]['discussion2'] = $total_sum_discus2;
$export_arr[$final_total]['change_order'] = $total_sum_change;
$export_arr[$final_total]['co_checking'] = $total_sum_co_check;
$export_arr[$final_total]['work'] = $total_sum_bar_list;
$export_arr[$final_total]['total_val'] = $total_sum_total;
$detailing_hrs[] = $total_sum_study1;
$detailing_hrs[] = $total_sum_detailing;
$detailing_hrs[] = $total_sum_discus1;
$detailing_hrs[] = $total_sum_checking1;
$detailing_hrs[] = $total_sum_corr1;
$revision_hrs[] = $total_sum_study2;
$revision_hrs[] = $total_sum_aec;
$revision_hrs[] = $total_sum_checking2;
$revision_hrs[] = $total_sum_corr2;
$revision_hrs[] = $total_sum_nonbill;
$revision_hrs[] = $total_sum_bill;
$revision_hrs[] = $total_sum_discus2;
$revision_hrs[] = $total_sum_change;
$revision_hrs[] = $total_sum_co_check;
$export_arr['empty1']['valuess'] = '';
$export_arr['empty2']['valuess'] = '';
$export_arr['team_overview']['keys'] = 'Team Overview';
$export_arr['detail_hrs']['keys'] = 'Sum of Total Detailing Hours :';
$export_arr['rev_hrs']['keys'] = 'Sum of Total Revision Hours :';
$export_arr['list_hrs']['keys'] = 'Sum of Total Listing Hours: ';
$export_arr['tot_rfi']['keys'] = 'Sum of Total RFIs Hours : ';
$export_arr['emp_hrs']['keys'] = '';
$export_arr['production_ton']['keys'] = 'Total Production Tons :';
$export_arr['act_ton_per']['keys'] = 'Tons per Hour (Actual) :';
$export_arr['detail_ton_per']['keys'] = 'Tons per Hour (New Detailing only) :';
$export_arr['detail_sheet']['keys'] = 'Detailed Sheets (Submitted Log) :';
$export_arr['ton_per_sheet']['keys'] = 'Detailed Tons per Sheet :';
$export_arr['detail_sheet_hr']['keys'] = 'Hours Per Sheet (New Detailing) :';
$export_arr['product_hrs']['keys'] = 'Productivity Hours % :';
$export_arr['bill_hrs']['keys'] = 'Billable Hours :';
$export_arr['non_bill_hrs']['keys'] = 'Non Billable Hours :';
$export_arr['bill_vs_rev']['keys'] = '% of Billable Vs Revision Hours :';
$detailing_hrs_val = $this->AddPlayTime($detailing_hrs);
$revision_hrs_val = $this->AddPlayTime($revision_hrs);
$hrs_per[] = $detailing_hrs_val;
$hrs_per[] = $revision_hrs_val;
$hrs_per_val = $this->AddPlayTime($hrs_per);
$rev_dec_hrs = $this->decimalHours($claimed_hrs);
$rev_hrs_tons = $rev_dec_hrs * 1.5;
$act_tons = $this->two_decimal($actual_tonnage);
$production_ton = $act_tons + $rev_hrs_tons;
$production_ton = $this->two_decimal($production_ton);
if($production_ton && $hrs_per_val){
if((int)$hrs_per_val !== 0){
$ton_per_hrs = $production_ton/$hrs_per_val;
}else{
$ton_per_hrs = 0;
}
$ton_per_hrs = $this->two_decimal($ton_per_hrs);
}else{
$ton_per_hrs = "0";
}
if($act_tons && $detailing_hrs_val){
if((int)$detailing_hrs_val !== 0){
$detail_per_hrs = $act_tons/$detailing_hrs_val;
}else{
$detail_per_hrs = 0;
}
$detail_per_hrs = $this->two_decimal($detail_per_hrs);
}else{
$detail_per_hrs = "0";
}
if($act_tons && $sheet_count){
if((int)$sheet_count !== 0){
$ton_per_sheet = $act_tons/$sheet_count;
}else{
$ton_per_sheet = 0;
}
$ton_per_sheet = $this->two_decimal($ton_per_sheet);
}else{
$ton_per_sheet = "0";
}
if($act_tons && $sheet_count){
if((int)$sheet_count !== 0){
$detail_sheet = $detailing_hrs_val/$sheet_count;
}else{
$detail_sheet = 0;
}
$detail_sheet = $this->two_decimal($detail_sheet);
}else{
$detail_sheet = "0";
}
$rev_tot = $this->time_to_min($revision_hrs_val);
$claim_tot = $this->time_to_min($claimed_hrs);
$diff_hrs = $rev_tot-$claim_tot;
$diff_non_bill = $this->mins_to_hrs($diff_hrs);
// $diff_non_bill = intdiv($diff_hrs, 60).':'. ($diff_hrs % 60);
if($claimed_hrs && $revision_hrs_val){
if((int)$revision_hrs_val !== 0){
$bill_vs_rev = $claimed_hrs/$revision_hrs_val;
}else{
$bill_vs_rev = 0;
}
$bill_vs_rev = $bill_vs_rev * 100;
$bill_vs_rev = $this->two_decimal($bill_vs_rev);
}else{
$bill_vs_rev = "0";
}
$productive_arr[] = $total_sum_detailing;
$productive_arr[] = $total_sum_checking1;
$productive_arr[] = $total_sum_aec;
$productive_arr[] = $total_sum_checking2;
$productive_arr[] = $total_sum_nonbill;
$productive_arr[] = $total_sum_bill;
$productive_hrs = $this->AddPlayTime($productive_arr);
if($productive_hrs && $hrs_per_val){
if($productive_hrs !== "00:00" && $hrs_per_val !== "00:00"){
if((int)$this->time_to_min($hrs_per_val) !== 0){
$productivity_hrs = $this->time_to_min($productive_hrs)/$this->time_to_min($hrs_per_val);
}else{
$productivity_hrs = 0;
}
(int)$productivity_hrs = (int)$productivity_hrs * 100;
$productivity_hrs = $this->two_decimal($productivity_hrs);
}else{
$productivity_hrs = "0";
}
}else{
$productivity_hrs = "0";
}
$export_arr['team_overview']['values'] = '';
$export_arr['detail_hrs']['values'] = $detailing_hrs_val;
$export_arr['rev_hrs']['values'] = $revision_hrs_val;
$export_arr['list_hrs']['values'] = $total_sum_bar_list;
$export_arr['tot_rfi']['values'] = $total_sum_rfi;
$export_arr['emp_hrs']['values'] = '';
$export_arr['production_ton']['values'] = $production_ton;
$export_arr['act_ton_per']['values'] = $ton_per_hrs;
$export_arr['detail_ton_per']['values'] = $detail_per_hrs;
$export_arr['detail_sheet']['values'] = $sheet_count;
$export_arr['ton_per_sheet']['values'] = $ton_per_sheet;
$export_arr['detail_sheet_hr']['values']= $detail_sheet;
$export_arr['product_hrs']['values'] = $productivity_hrs;
$export_arr['bill_hrs']['values'] = $claimed_hrs;
$export_arr['non_bill_hrs']['values'] = $diff_non_bill;
$export_arr['bill_vs_rev']['values'] = $bill_vs_rev;
$export_arr['team_overview']['empty_one'] = '';
$export_arr['detail_hrs']['empty_one'] = '';
$export_arr['rev_hrs']['empty_one'] = '';
$export_arr['list_hrs']['empty_one'] = '';
$export_arr['tot_rfi']['empty_one'] = '';
$export_arr['emp_hrs']['empty_one'] = '';
$export_arr['production_ton']['empty_one'] = '';
$export_arr['act_ton_per']['empty_one'] = '';
$export_arr['detail_ton_per']['empty_one'] = '';
$export_arr['detail_sheet']['empty_one'] = '';
$export_arr['ton_per_sheet']['empty_one'] = '';
$export_arr['detail_sheet_hr']['empty_one'] = '';
$export_arr['product_hrs']['empty_one'] = '';
$export_arr['bill_hrs']['empty_one'] = '';
$export_arr['non_bill_hrs']['empty_one'] = '';
$export_arr['bill_vs_rev']['empty_one'] = '';
$export_arr['team_overview']['empty_two'] = '';
$export_arr['detail_hrs']['empty_two'] = '';
$export_arr['rev_hrs']['empty_two'] = '';
$export_arr['list_hrs']['empty_two'] = '';
$export_arr['tot_rfi']['empty_two'] = '';
$export_arr['emp_hrs']['empty_two'] = '';
$export_arr['production_ton']['empty_two'] = '';
$export_arr['act_ton_per']['empty_two'] = '';
$export_arr['detail_ton_per']['empty_two'] = '';
$export_arr['detail_sheet']['empty_two'] = '';
$export_arr['ton_per_sheet']['empty_two'] = '';
$export_arr['detail_sheet_hr']['empty_two'] = '';
$export_arr['product_hrs']['empty_two'] = '';
$export_arr['bill_hrs']['empty_two'] = '';
$export_arr['non_bill_hrs']['empty_two'] = '';
$export_arr['bill_vs_rev']['empty_two'] = '';
$export_arr['team_overview']['empty_two'] = '';
$export_arr['detail_hrs']['empty_two'] = '';
$export_arr['rev_hrs']['empty_two'] = '';
$export_arr['list_hrs']['empty_two'] = '';
$export_arr['tot_rfi']['empty_two'] = '';
$export_arr['emp_hrs']['empty_two'] = '';
$export_arr['production_ton']['empty_two'] = '';
$export_arr['act_ton_per']['empty_two'] = '';
$export_arr['detail_ton_per']['empty_two'] = '';
$export_arr['detail_sheet']['empty_two'] = '';
$export_arr['ton_per_sheet']['empty_two'] = '';
$export_arr['detail_sheet_hr']['empty_two'] = '';
$export_arr['product_hrs']['empty_two'] = '';
$export_arr['bill_hrs']['empty_two'] = '';
$export_arr['non_bill_hrs']['empty_two'] = '';
$export_arr['bill_vs_rev']['empty_two'] = '';
$export_arr['team_overview']['key'] = 'Detailer Overview';
$export_arr['detail_hrs']['key'] = 'Sum of Total Detailing Hours :';
$export_arr['rev_hrs']['key'] = 'Sum of Total Revision Hours :';
$export_arr['list_hrs']['key'] = 'Sum of Total Listing Hours: ';
$export_arr['tot_rfi']['key'] = 'Sum of Total RFIs Hours : ';
$export_arr['emp_hrs']['key'] = '';
$export_arr['production_ton']['key'] = 'Total Production Tons :';
$export_arr['act_ton_per']['key'] = 'Tons per Hour (Actual) :';
$export_arr['detail_ton_per']['key'] = 'Tons per Hour (New Detailing only) :';
$export_arr['detail_sheet']['key'] = 'Detailed Sheets (Submitted Log) :';
$export_arr['ton_per_sheet']['key'] = 'Detailed Tons per Sheet :';
$export_arr['detail_sheet_hr']['key'] = 'Hours Per Sheet (New Detailing) :';
$export_arr['product_hrs']['key'] = 'Productivity Hours % :';
$export_arr['bill_hrs']['key'] = 'Billable Hours :';
$export_arr['non_bill_hrs']['key'] = 'Non Billable Hours :';
$export_arr['bill_vs_rev']['key'] = '% of Billable Vs Revision Hours :';
$detailer_detail_val = $this->AddPlayTime($detailer_detail);
$detailer_revision_val = $this->AddPlayTime($detailer_revision);
$detailer_hrs_per[] = $detailer_detail_val;
$detailer_hrs_per[] = $detailer_revision_val;
$detailer_hrs_per_val = $this->AddPlayTime($detailer_hrs_per);
$rev_dec_hrs = $this->decimalHours($claimed_hrs);
$rev_hrs_tons = $rev_dec_hrs * 1.5;
$act_tons = $this->two_decimal($actual_tonnage);
$production_ton = $act_tons + $rev_hrs_tons;
$production_ton = $this->two_decimal($production_ton);
if($production_ton && $detailer_hrs_per_val){
if((int)$detailer_hrs_per_val !== 0){
$detailer_ton_per_hrs = $production_ton/$detailer_hrs_per_val;
}else{
$detailer_ton_per_hrs = 0;
}
$detailer_ton_per_hrs = $this->two_decimal($detailer_ton_per_hrs);
}else{
$detailer_ton_per_hrs = "0";
}
if($act_tons && $detailer_detail_val){
if((int)$detailer_detail_val !== 0){
$detailer_detail_per_hrs= $act_tons/$detailer_detail_val;
}else{
$detailer_detail_per_hrs = 0;
}
$detailer_detail_per_hrs= $this->two_decimal($detailer_detail_per_hrs);
}else{
$detailer_detail_per_hrs= "0";
}
if($act_tons && $sheet_count){
if((int)$sheet_count !== 0){
$detailer_ton_per_sheet = $act_tons/$sheet_count;
}else{
$detailer_ton_per_sheet = 0;
}
$detailer_ton_per_sheet = $this->two_decimal($detailer_ton_per_sheet);
}else{
$detailer_ton_per_sheet = "0";
}
if($detailer_detail_val && $sheet_count){
if((int)$sheet_count !== 0){
$detailer_detail_sheet = $detailer_detail_val/$sheet_count;
}else{
$detailer_detail_sheet = 0;
}
$detailer_detail_sheet = $this->two_decimal($detailer_detail_sheet);
}else{
$detailer_detail_sheet = "0";
}
$detailer_rev_tot = $this->time_to_min($detailer_revision_val);
$claim_tot = $this->time_to_min($claimed_hrs);
$detailer_diff_hrs = $detailer_rev_tot-$claim_tot;
$detailer_diff_non_bill = $this->mins_to_hrs($detailer_diff_hrs);
// $detailer_diff_non_bill = intdiv($detailer_diff_hrs, 60).':'. ($detailer_diff_hrs % 60);
if($claimed_hrs && $detailer_revision_val){
if((int)$detailer_revision_val !== 0){
$bill_vs_rev = $claimed_hrs/$detailer_revision_val;
}else{
$bill_vs_rev = 0;
}
$bill_vs_rev = $bill_vs_rev * 100;
$bill_vs_rev = $this->two_decimal($bill_vs_rev);
}else{
$bill_vs_rev = "0";
}
$detail_productive_hrs = $this->AddPlayTime($detail_productive_arr);
if($detail_productive_hrs && $detailer_hrs_per_val){
if($detail_productive_hrs !== "00:00" && $detailer_hrs_per_val !== "00:00"){
if((int)$this->time_to_min($detailer_hrs_per_val) !== 0){
$detail_productivity_hrs = $this->time_to_min($detail_productive_hrs)/$this->time_to_min($detailer_hrs_per_val);
}else{
$detail_productivity_hrs = 0;
}
$detail_productivity_hrs = $detail_productivity_hrs * 100;
$detail_productivity_hrs = $this->two_decimal($detail_productivity_hrs);
}else{
$detail_productivity_hrs = "0";
}
}else{
$detail_productivity_hrs = "0";
}
$export_arr['team_overview']['value'] = '';
$export_arr['detail_hrs']['value'] = $detailer_detail_val;
$export_arr['rev_hrs']['value'] = $detailer_revision_val;
$export_arr['list_hrs']['value'] = $detailer_bar_list;
$export_arr['tot_rfi']['value'] = $detailer_rfi;
$export_arr['emp_hrs']['value'] = '';
$export_arr['production_ton']['value'] = $production_ton;
$export_arr['act_ton_per']['value'] = $detailer_ton_per_hrs;
$export_arr['detail_ton_per']['value'] = $detailer_detail_per_hrs;
$export_arr['detail_sheet']['value'] = $sheet_count;
$export_arr['ton_per_sheet']['value'] = $detailer_ton_per_sheet;
$export_arr['detail_sheet_hr']['value'] = $detailer_detail_sheet;
$export_arr['product_hrs']['value'] = $detail_productivity_hrs;
$export_arr['bill_hrs']['value'] = $claimed_hrs;
$export_arr['non_bill_hrs']['value'] = $detailer_diff_non_bill;
$export_arr['bill_vs_rev']['value'] = $bill_vs_rev;
$qc_data = count($qc_team_rslt);
$qc_header = $i+1;
if($qc_team_rslt){
$qc_arr_count = count($qc_team_rslt);
$qc_footer = $j+1;
$tot_footer = $qc_footer+1;
}else{
$qc_footer = $j+1;
$tot_footer = $qc_footer+1;
}
$team_header = $tot_footer+3;
$team_body = $team_header+17;
//->applyFromArray($styleArray)
$objPHPExcel->setActiveSheetIndex(0)->setTitle('Project Analysis')->fromArray(array_values($export_arr), null, 'A1')->getStyle('A1:Z1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
foreach ($excel_columns as $key => $value) { //Apply Border for contents
$objPHPExcel->getActiveSheet()->getStyle($value."2:$value".$tot_footer)->applyFromArray($styleArray);
}
$objPHPExcel->getActiveSheet()->getStyle("A11:R11")->applyFromArray($verticalStyle);
$objPHPExcel->getActiveSheet()->setCellValue('B'."11", "New Detailing Work")->mergeCells('B11:F11')->getStyle('B11:F11')->applyFromArray($style);
$objPHPExcel->getActiveSheet()->setCellValue('A'."11", " ")->getStyle('A11')->applyFromArray($style);
$objPHPExcel->getActiveSheet()->setCellValue('H'."11", "Revision Work")->mergeCells('H11:P11')->getStyle('H11:P11')->applyFromArray($style);
$objPHPExcel->getActiveSheet()->getCell('Q11')->setValue("Listing");
$objPHPExcel->getActiveSheet()->getStyle("A12:R12")->applyFromArray($verticalStyle);
foreach ($footer_arr as $key => $value) {
$objPHPExcel->getActiveSheet()->getStyle("A".$value.":R".$value)->applyFromArray($FooterStyle);
}
foreach ($header_arr as $key => $value) {
$objPHPExcel->getActiveSheet()->getStyle("A".$value.":R".$value)->applyFromArray($teamStyle);
}
$objPHPExcel->getActiveSheet()->getStyle("A".$qc_header.":R".$qc_header)->applyFromArray($teamStyle);
$objPHPExcel->getActiveSheet()->getStyle("A".$qc_footer.":R".$qc_footer)->applyFromArray($FooterStyle);
$objPHPExcel->getActiveSheet()->getStyle("A".$tot_footer.":R".$tot_footer)->applyFromArray($FinalfooterStyle);
$objPHPExcel->getActiveSheet()->getStyle("A".$team_header.":B".$team_header)->applyFromArray($verticalStyle);
$objPHPExcel->getActiveSheet()->getStyle("E".$team_header.":F".$team_header)->applyFromArray($verticalStyle);
$objPHPExcel->getActiveSheet()->getStyle("A".$tot_footer.":R".$tot_footer)->applyFromArray($bottom_arr);
$objPHPExcel->getActiveSheet()->getStyle("A1:A".$tot_footer)->applyFromArray($left_arr);
$objPHPExcel->getActiveSheet()->getStyle("R1:R".$tot_footer)->applyFromArray($Right_arr);
$objPHPExcel->getActiveSheet()->getStyle("A1:R1")->applyFromArray($top_arr);
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(40);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(40);
$objPHPExcel->getActiveSheet()->getStyle("A7:R7")->applyFromArray($bottom_arr);
$objPHPExcel->getActiveSheet()->getStyle("A".$team_body.":B".$team_body)->applyFromArray($bottom_arr);
$objPHPExcel->getActiveSheet()->getStyle("E".$team_body.":F".$team_body)->applyFromArray($bottom_arr);
$objPHPExcel->getActiveSheet()->getStyle("A".$team_header.":B".$team_header)->applyFromArray($top_arr);
$objPHPExcel->getActiveSheet()->getStyle("E".$team_header.":F".$team_header)->applyFromArray($top_arr);
$objPHPExcel->getActiveSheet()->getStyle("A".$team_header.":A".$team_body)->applyFromArray($left_arr);
$objPHPExcel->getActiveSheet()->getStyle("E".$team_header.":E".$team_body)->applyFromArray($left_arr);
$objPHPExcel->getActiveSheet()->getStyle("B".$team_header.":B".$team_body)->applyFromArray($Right_arr);
$objPHPExcel->getActiveSheet()->getStyle("F".$team_header.":F".$team_body)->applyFromArray($Right_arr);
$control_name = $this->control_name.'_'.$project;
$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 = ($timeArr[0]*60) + ($timeArr[1]) + ($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($minutes / 60);
$minutes -= (int)$hours * 60;
// returns the time already formatted
return sprintf('%02d:%02d', $hours, $minutes);
}
}
?>