File: //home/cafsindia/cpaqua.cafsinfotech.in/dump/application/controllers/Project_manager_report.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_manager_report extends Action_controller{
public function __construct(){
parent::__construct('project_manager_report');
$this->collect_base_info();
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$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_role = $this->session->userdata('logged_role');
$logged_emp_code = $this->session->userdata('logged_emp_code');
if((int)$logged_role === 3){
$emp_qry = 'SELECT employee_code,emp_name FROM cw_employees where role = 3 and employee_code = "'.$logged_emp_code.'" and employee_status = 1 and trans_status = 1';
}else{
$emp_qry = 'SELECT employee_code,emp_name FROM cw_employees where role = 3 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;
$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);
}
$employee_code = $this->input->post("employee_code");
$process_month = $this->input->post("process_month");
$emp_pro_qry = [];
$emp_pro_qry[] = array("return"=>"emp_team_qry","qry"=>'select team,date_of_joining from cw_employees where employee_code = "'.$employee_code.'" and trans_status = 1');
$emp_pro_qry[] = array("return"=>"emp_qry","qry"=>'SELECT employee_code,emp_name FROM cw_employees where role = 4 and reporting = "'.$employee_code.'" and employee_status = 1 and trans_status = 1');
$emp_page_info = $this->run_multi_qry($emp_pro_qry);
$emp_team_result = json_decode(json_encode($emp_page_info->rslt->emp_team_qry),true);
$emp_result = json_decode(json_encode($emp_page_info->rslt->emp_qry),true);
$team_id = implode('","',explode(',',$emp_team_result[0]['team']));
$date_of_joining = $emp_team_result[0]['date_of_joining'];
$pro_qry = [];
$pro_qry[] = array("return"=>"team_qry","qry"=>'select team_name from cw_team where FIND_IN_SET(prime_team_id,"'.$emp_team_result[0]['team'].'") and trans_status = 1');
$pro_qry[] = array("return"=>"target_qry","qry"=>'select SUM(cw_team_target_detailer_wise_target.target_value) as target_value 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.from_date <= "'.$process_month.'" and cw_team_target.to_date >= "'.$process_month.'" and cw_team_target.team in("'.$team_id.'") and cw_team_target.trans_status = 1 and cw_team_target_detailer_wise_target.trans_status = 1');
$pro_qry[] = array("return"=>"pro_manager_qry","qry"=>'select cw_time_sheet_time_line.prime_time_sheet_time_line_id,work_description,other_work_name,emp_name,cw_time_sheet.employee_code,entry_date,in_time,out_time,total_time,IF(study>"00:00:00",TIME_FORMAT(study, "%H:%i"),"") as study,IF(qa_checking>"00:00:00", TIME_FORMAT(qa_checking, "%H:%i"), "") as qa_checking,IF(discussion>"00:00:00", TIME_FORMAT(discussion, "%H:%i"), "") as discussion,IF(was>"00:00:00", TIME_FORMAT(was, "%H:%i"), "") as was,IF(monitoring>"00:00:00", TIME_FORMAT(monitoring, "%H:%i"), "") as monitoring,IF(rfi>"00:00:00", TIME_FORMAT(rfi, "%H:%i"), "") as rfi,IF(co_checking>"00:00:00", TIME_FORMAT(co_checking, "%H:%i"), "") as co_checking,IF(other_works>"00:00:00", TIME_FORMAT(other_works, "%H:%i"), "") as other_works,IF(bar_listing_time>"00:00:00", TIME_FORMAT(bar_listing_time, "%H:%i"), "") as bar_listing_time,IF(emails>"00:00:00", TIME_FORMAT(emails, "%H:%i"), "") as emails,cw_work_type.work_type,entry_type,client_name,project,drawing_no_pm,work_status,cw_time_sheet_time_line.work_type as work_type_time,( SELECT project_name FROM cw_project_and_drawing_master WHERE prime_project_and_drawing_master_id= cw_time_sheet_time_line.project) AS project_name,(SELECT GROUP_CONCAT(drawing_no) FROM cw_project_and_drawing_master_drawings WHERE find_in_set(prime_project_and_drawing_master_drawings_id , cw_time_sheet_time_line.drawing_no_pm)) AS drawing_name,(SELECT work_status FROM cw_work_status WHERE prime_work_status_id= cw_time_sheet_time_line.work_status) AS work_status 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 DATE_FORMAT(`entry_date`, "%m-%Y") = "'.$process_month.'" order by entry_date');
$pro_qry[] = array("return"=>"other_work_qry","qry"=>'select count(*) as work_result_count,cw_time_sheet_time_line.work_type,work_description,cw_other_works.other_works,IF(SEC_TO_TIME( SUM(time_to_sec(cw_time_sheet_time_line.other_works)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(cw_time_sheet_time_line.other_works))),"%H:%i"),"") as cummulate_works,other_work_name,IF(SEC_TO_TIME( SUM(time_to_sec(emails)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(emails))),"%H:%i"),"") as cummulate_emails 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_other_works on cw_other_works.prime_other_works_id = cw_time_sheet_time_line.other_work_name where cw_time_sheet.employee_code = "'.$employee_code.'" and cw_time_sheet.trans_status = 1 and work_type = 4 and cw_time_sheet.trans_status = 1 and cw_time_sheet_time_line.trans_status = 1 and DATE_FORMAT(`entry_date`, "%m-%Y") = "'.$process_month.'" group by cw_time_sheet_time_line.other_work_name order by cw_time_sheet_time_line.other_work_name');
$pro_qry[] = array("return"=>"date_wise_time_qry","qry"=>'select entry_date,SEC_TO_TIME(SUM(TIME_TO_SEC(study))+SUM(TIME_TO_SEC(discussion))+SUM(TIME_TO_SEC(rfi))+SUM(TIME_TO_SEC(other_works))+SUM(TIME_TO_SEC(emails))+SUM(TIME_TO_SEC(was))+SUM(TIME_TO_SEC(co_checking))+SUM(TIME_TO_SEC(qa_checking))+SUM(TIME_TO_SEC(monitoring))+SUM(TIME_TO_SEC(bar_listing_checking))) as date_wise_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 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 DATE_FORMAT(`entry_date`, "%m-%Y") = "'.$process_month.'" group by entry_date order by entry_date');
$pro_qry[] = array("return"=>"working_days_qry","qry"=>'SELECT count(*) as working_days,SEC_TO_TIME(SUM(TIME_TO_SEC(total_time))) as total_time FROM cw_time_sheet where DATE_FORMAT(`entry_date`, "%m-%Y") = "'.$process_month.'" and employee_code = "'.$employee_code.'" and trans_status = 1');
$pro_qry[] = array("return"=>"project_wise_qry","qry"=>'SELECT count(*) as project_wise_count,cw_job_category.job_category,count(cw_time_sheet_time_line.work_type) as work_type_count,cw_time_sheet_time_line.project,cw_project_and_drawing_master.project_name,cw_time_sheet_time_line.work_description,IF(SEC_TO_TIME( SUM(time_to_sec(emails)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(emails))),"%H:%i"),"") as cummulate_emails,IF(SEC_TO_TIME( SUM(time_to_sec(study)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(study))),"%H:%i"),"") as cummulate_study,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"),"") as cummulate_qa_checking,IF(SEC_TO_TIME( SUM(time_to_sec(discussion)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(discussion))),"%H:%i"),"") as cummulate_discussion,IF(SEC_TO_TIME( SUM(time_to_sec(was)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(was))),"%H:%i"),"") as cummulate_was,IF(SEC_TO_TIME( SUM(time_to_sec(monitoring)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(monitoring))),"%H:%i"),"") as cummulate_monitoring,IF(SEC_TO_TIME( SUM(time_to_sec(rfi)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(rfi))),"%H:%i"),"") as cummulate_rfi,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"),"") as cummulate_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"),"") as cummulate_bar_listing_time,IF(SEC_TO_TIME( SUM(time_to_sec(other_works)))>"00:00:00",TIME_FORMAT(SEC_TO_TIME( SUM(time_to_sec(other_works))),"%H:%i"),"") as cummulate_other_works,sum(bar_list_quantity) as cummulate_bar_list_quantity,work_type 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 left join cw_job_category on cw_job_category.prime_job_category_id = cw_project_and_drawing_master.job_category 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 DATE_FORMAT(`entry_date`, "%m-%Y") = "'.$process_month.'" group by cw_time_sheet_time_line.project,work_type order by cw_time_sheet_time_line.work_type');
$pro_qry[] = array("return"=>"complete_pending_qry","qry"=>'SELECT count(*) as work_status_count,work_status as work_status,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 where cw_time_sheet.employee_code = "'.$employee_code.'" and cw_time_sheet.trans_status = 1 and cw_time_sheet_time_line.trans_status = 1 group by project,work_status');
$pro_qry[] = array("return"=>"count_qry","qry"=>'select count(*) as count,project,cw_time_sheet_time_line.work_type,cw_time_sheet_time_line.drawing_no_pm 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 DATE_FORMAT(`entry_date`, "%m-%Y") = "'.$process_month.'" and drawing_no_pm !="" group by project,cw_time_sheet_time_line.work_type,cw_time_sheet_time_line.drawing_no_pm');
$pro_qry[] = array("return"=>"pro_ton_qry","qry"=>'SELECT sum(cw_tonnage_approval.actual_tonnage) as actual_tonnage,SEC_TO_TIME(SUM(TIME_TO_SEC(cw_tonnage_approval.actual_billable_time))) as actual_billable_time FROM cw_tonnage_approval 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_time_sheet on cw_time_sheet.prime_time_sheet_id = cw_time_sheet_time_line.prime_time_sheet_id where DATE_FORMAT(`entry_date`, "%m-%Y") = "'.$process_month.'" and project_manager_name = "'.$employee_code.'" and cw_tonnage_approval.trans_status = 1');
$pro_qry[] = array("return"=>"sub_log_app_qry","qry"=>'SELECT sum(cw_tonnage_approval.actual_tonnage) as submit_actual_tonnage FROM cw_tonnage_approval 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_time_sheet on cw_time_sheet.prime_time_sheet_id = cw_time_sheet_time_line.prime_time_sheet_id where DATE_FORMAT(`entry_date`, "%m-%Y") = "'.$process_month.'" and cw_tonnage_approval.team in("'.$team_id.'") and cw_tonnage_approval.trans_status = 1 and cw_time_sheet_time_line.trans_status = 1 and cw_time_sheet.trans_status = 1 and cw_tonnage_approval.approval_status = 2');
$pro_qry[] = array("return"=>"sub_log_rev_qry","qry"=>'SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(cw_co_register_log.billable_hours))) as act_bill_hours from cw_co_register_log where DATE_FORMAT(cw_co_register_log.entry_date, "%m-%Y") = "'.$process_month.'" and cw_co_register_log.team in("'.$team_id.'") and cw_co_register_log.trans_status = 1');
$pro_qry[] = array("return"=>"new_detail_qry","qry"=>'SELECT sum(qa_major) as qa_major,sum(qa_minor) as qa_minor,sum(cw_tonnage_approval.actual_tonnage) as actual_tonnage,SEC_TO_TIME(SUM(TIME_TO_SEC(cw_tonnage_approval.actual_billable_time))) as actual_billable_time 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.detailer_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_time_line.trans_status = 1 and cw_time_sheet_time_line.trans_status = 1 and DATE_FORMAT(`entry_date`, "%m-%Y") = "'.$process_month.'" and cw_tonnage_approval.team in("'.$team_id.'") group by cw_tonnage_approval.drawing_no,entry_date');
$pro_qry[] = array("return"=>"rev_detail_qry","qry"=>'SELECT GROUP_CONCAT(drawing_no_pm) as drawing_no_pm 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.trans_status = 1 and cw_time_sheet_time_line.trans_status = 1 and work_type = 2 and entry_type = 2 and cw_time_sheet.employee_code = "'.$employee_code.'" and DATE_FORMAT(`entry_date`, "%m-%Y") = "'.$process_month.'" and work_status = 3');
$pro_qry[] = array("return"=>"team_ton_det_qry","qry"=>'SELECT 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 team_ton_per_detail 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 DATE_FORMAT(cw_time_sheet.entry_date, "%m-%Y") = "'.$process_month.'" 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');
$page_info_rslt = $this->run_multi_qry($pro_qry);
$team_result = json_decode(json_encode($page_info_rslt->rslt->team_qry),true);
$target_result = json_decode(json_encode($page_info_rslt->rslt->target_qry),true);
$pro_manager_rslt = json_decode(json_encode($page_info_rslt->rslt->pro_manager_qry),true);
$other_work_result = json_decode(json_encode($page_info_rslt->rslt->other_work_qry),true);
$date_wise_time_rslt= json_decode(json_encode($page_info_rslt->rslt->date_wise_time_qry),true);
$working_days_result= json_decode(json_encode($page_info_rslt->rslt->working_days_qry),true);
$project_wise_result= json_decode(json_encode($page_info_rslt->rslt->project_wise_qry),true);
$count_rslt = json_decode(json_encode($page_info_rslt->rslt->count_qry),true);
$pro_ton_rslt = json_decode(json_encode($page_info_rslt->rslt->pro_ton_qry),true);
$sub_log_app_rslt = json_decode(json_encode($page_info_rslt->rslt->sub_log_app_qry),true);
$sub_log_rev_rslt = json_decode(json_encode($page_info_rslt->rslt->sub_log_rev_qry),true);
$new_detail_rslt = json_decode(json_encode($page_info_rslt->rslt->new_detail_qry),true);
$rev_detail_rslt = json_decode(json_encode($page_info_rslt->rslt->rev_detail_qry),true);
$team_ton_det_rslt = json_decode(json_encode($page_info_rslt->rslt->team_ton_det_qry),true);
$complete_pending_result = json_decode(json_encode($page_info_rslt->rslt->complete_pending_qry),true);
$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";
$team_name = array_column($team_result,'team_name');
$team_name = implode(',', $team_name);
$control_name = $this->control_name;
$process_month = $process_month;
$get_month = explode('-', $process_month);
$month_name = $get_month[0];
$month_name = date("F", mktime($month_name, 1));
$get_year = $get_month[1];
if($emp_result){
$emp_result = array_reduce($emp_result, function($result, $arr){
$result[$arr['employee_code']] = $arr;
return $result;
}, array());
}
$tl_employee_code = "";
foreach ($emp_result as $key => $value) {
$tl_employee_code .= $value['employee_code'].",";
}
$tl_employee_code = rtrim($tl_employee_code,",");
$credit_target = $target_result[0]['target_value'];
if($pro_manager_rslt){
$pro_manager_count = count($pro_manager_rslt);
}
if((int)$pro_manager_count === 0){
echo json_encode(array('success' => false, 'message' => "No Available Data"));
exit(0);
}
$emp_name = $pro_manager_rslt[0]['emp_name'];
$work_result_count = $other_work_result[0]['work_result_count'];
$other_project = json_decode(json_encode($other_work_result),true);
if($other_project){
$other_project = array_reduce($other_project, function($result, $arr){
$result[$arr['other_work_name']] = $arr;
return $result;
}, array());
}
if($date_wise_time_rslt){
$date_wise_time_rslt = array_reduce($date_wise_time_rslt, function($result, $arr){
$result[$arr['entry_date']] = $arr;
return $result;
}, array());
}
//require_once APPPATH."/third_party/PHPExcel.php";
$obj = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xls');//('Excel5');
$obj = $obj->load('./excel_download/detailer_report.xls');//default excel template
$excel[]['excel_column']= array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
$excel[]['excel_value']= array('Date','Project Name','Drawing No','Drawing Revision Status','Work Status','Emails','STY','QA CHK','DIS','WAS','MOR','RFI','STY','QA CHK','DIS','WAS','MOR','CO CHK','CHK','OTHER WORK','BOOKING HOURS','SUM OF BOOKING HOURS(Everyday)','IN','OUT','TOTAL','SHIFT');
$LeftBorder = 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_THICK
),
'right' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
)
),
'alignment' => array(
'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
)
);
$RightBorder = 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_THICK
)
),
'alignment' => array(
'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP,
)
);
$LeftArray = 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_THICK
),
'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_CENTER,
)
);
$RightArray = 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_THICK
)
),
'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_CENTER,
)
);
$TopBorder = 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
),
),
'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_CENTER,
)
);
$styleArray = 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_CENTER,
)
);
$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
)
),
'alignment' => array(
'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP,
// 'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
)
);
$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_CENTER,
)
);
$FooterLeftStyle = 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_THICK
),
'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_CENTER,
)
);
$FooterRightStyle = 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_THICK
)
),
'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_CENTER,
)
);
$LeftrightBorder = 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_THICK
),
'right' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK
)
),
'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_CENTER,
)
);
$RightBorderHead = 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_THICK
)
),
'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_CENTER,
)
);
$RightBordertwo = 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_THICK
)
),
'alignment' => array(
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
)
);
$FooterLeftStyletwo = 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_THICK
),
'right' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
)
)
);
$FooterRightStyletwo = 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_THICK
)
),
'alignment' => array(
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
)
);
$doubleColumnStyle = 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(
'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
// 'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
)
);
$doubleColumnStyleRight = 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_THICK
)
),
'font' => array(
'bold' => true,
'color' => array('rgb' => '000'),
),
'fill' => array(
'type' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
'color' => array('rgb' => '99CC00')
),
'alignment' => array(
'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
// 'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
)
);
for ($x = 0; $x <= 25; $x++) {
$excel_column = $excel[0]['excel_column'][$x];
$excel_value = $excel[1]['excel_value'][$x];
$obj->getActiveSheet()->setCellValue('A'."1", "TIME SHEET LOG FOR ".strtoupper($month_name)." -".$get_year)->mergeCells('A1:Z1')->getStyle('A1:Z1')->applyFromArray($TopBorder);
$obj->getActiveSheet()->setCellValue('A'."2", "Project Manager Name:".$emp_name)->mergeCells('A2:B2')->getStyle('A2:B2')->applyFromArray($LeftArray);
$obj->getActiveSheet()->setCellValue('C'."2", "Team's Target Tons")->mergeCells('C2:E2')->getStyle('C2:E2')->applyFromArray($styleArray);
$obj->getActiveSheet()->setCellValue('F'."2", $credit_target)->getStyle('F2')->applyFromArray($styleArray);
$obj->getActiveSheet()->setCellValue('G'."2", "New Detailing Work")->mergeCells('G2:K2')->getStyle('G2:K2')->applyFromArray($styleArray);
$obj->getActiveSheet()->setCellValue('L'."2", "")->getStyle('L2')->applyFromArray($styleArray);
$obj->getActiveSheet()->setCellValue('M'."2", "Revision Work")->mergeCells('M2:R2')->getStyle('M2:R2')->applyFromArray($styleArray);
$obj->getActiveSheet()->setCellValue('S'."2", "Listing")->getStyle('S2')->applyFromArray($styleArray);
$obj->getActiveSheet()->setCellValue('T'."2", "OTHER WORKS")->getStyle('T2')->applyFromArray($styleArray);
$obj->getActiveSheet()->setCellValue('U'."2", "Booking Hours")->getStyle('U2')->applyFromArray($doubleColumnStyle);
$obj->getActiveSheet()->setCellValue('V'."2", "OFFICE HOURS")->mergeCells('V2:X2')->getStyle('V2:X2')->applyFromArray($styleArray);
$obj->getActiveSheet()->setCellValue('Y'."2", " ")->getStyle('Y2')->applyFromArray($RightArray);
$obj->getActiveSheet()->setCellValue('Z'."2", " ")->getStyle('Z2')->applyFromArray($RightArray);
if($excel_column === 'A'){
$obj->getActiveSheet()->setCellValue($excel_column."3", $excel_value)->getStyle($excel_column.'3')->applyFromArray($LeftArray);
}else
if($excel_column === 'Z'){
$obj->getActiveSheet()->setCellValue($excel_column."3", $excel_value)->getStyle($excel_column.'3')->applyFromArray($RightArray);
}else
if($excel_column === 'T'){
$obj->getActiveSheet()->setCellValue('T'."2", $excel_value)->mergeCells('T2:'.$excel_column.'3')->getStyle('T2:'.$excel_column.'3')->applyFromArray($doubleColumnStyle);
}else
if($excel_column === 'U'){
$obj->getActiveSheet()->setCellValue('U'."2", $excel_value)->mergeCells('U2:'.$excel_column.'3')->getStyle('U2:'.$excel_column.'3')->applyFromArray($doubleColumnStyle);
}
else{
$obj->getActiveSheet()->setCellValue($excel_column."3", $excel_value)->getStyle($excel_column.'3')->applyFromArray($styleArray);
}
}
$i = 4;
$j = 0;
$k = 0;
$previous_date = "";
$time_total = array();
foreach($pro_manager_rslt as $key => $time_sheet){
$prime_time_sheet_time_line_id = $time_sheet['prime_time_sheet_time_line_id'];
//echo $prime_time_sheet_time_line_id; die;
$other_work_name = $time_sheet['other_work_name'];
$sum_value_total_hours = array();
$booking_hours = array();
$work_type_time = $time_sheet['work_type_time'];
if((int)$work_type_time === 1){
$study1 = $time_sheet['study'];
$qa_checking1 = $time_sheet['qa_checking'];
$discussion1 = $time_sheet['discussion'];
$was1 = $time_sheet['was'];
$monitoring1 = $time_sheet['monitoring'];
$study2 = "";
$qa_checking2 = "";
$discussion2 = "";
$was2 = "";
$monitoring2 = "";
}else
if((int)$work_type_time === 2){
$study2 = $time_sheet['study'];
$qa_checking2 = $time_sheet['qa_checking'];
$discussion2 = $time_sheet['discussion'];
$was2 = $time_sheet['was'];
$monitoring2 = $time_sheet['monitoring'];
$study1 = "";
$qa_checking1 = "";
$discussion1 = "";
$was1 = "";
$monitoring1 = "";
}else{
$study1 = $time_sheet['study'];
$study2 = $time_sheet['study'];
$qa_checking1 = $time_sheet['qa_checking'];
$qa_checking2 = $time_sheet['qa_checking'];
$discussion1 = $time_sheet['discussion'];
$discussion2 = $time_sheet['discussion'];
$was1 = $time_sheet['was'];
$was2 = $time_sheet['was'];
$monitoring1 = $time_sheet['monitoring'];
$monitoring2 = $time_sheet['monitoring'];
}
$booking_hours[] = $study1;
$booking_hours[] = $qa_checking1;
$booking_hours[] = $discussion1;
$booking_hours[] = $was1;
$booking_hours[] = $monitoring1;
$booking_hours[] = $time_sheet['rfi'];
$booking_hours[] = $study2;
$booking_hours[] = $qa_checking2;
$booking_hours[] = $discussion2;
$booking_hours[] = $was2;
$booking_hours[] = $monitoring2;
$booking_hours[] = $time_sheet['co_checking'];
$booking_hours[] = $time_sheet['bar_listing_time'];
$booking_hours[] = $time_sheet['other_works'];
$booking_hours[] = $time_sheet['emails'];
$total_hours = $this->AddPlayTime($booking_hours);
$sum_total_hours[] = $total_hours;
$sum_value_total_hours = $this->AddPlayTime($sum_total_hours);
$entry_date = $time_sheet['entry_date'];
$date_only = date('Y-m-d',strtotime($entry_date));
if($previous_date === $date_only){
$j ++;
}else{
$time_total[] = $time_sheet['total_time'];
$k = $i;
$j = 0;
}
$range_start = $k;
$range_end = $i;
$project_name = $time_sheet['project_name'];
$drawing_name = $time_sheet['drawing_name'];
$work_status = $time_sheet['work_status'];
$other_project_name = $other_project[$other_work_name]['other_works'];
$date_wise_time = $date_wise_time_rslt[$entry_date]['date_wise_time'];
if(!$project_name){
$project_name = $other_project_name;
}
$work_description_replace = $time_sheet['work_description'];
$work_description = str_replace("xdbquot",'"',$work_description_replace);
$work_description = str_replace("xquot","'",$work_description);
$work_description = str_replace("xxamp","&",$work_description);
$project_name = str_replace("xdbquot",'"',$project_name);
$project_name = str_replace("xquot","'",$project_name);
$project_name = str_replace("xxamp","&",$project_name);
$time_sheet_value['A'] = date('d-M-Y',strtotime($time_sheet['entry_date']));
$time_sheet_value['B'] = $project_name;
$time_sheet_value['C'] = $drawing_name;
$time_sheet_value['D'] = $work_status;
$time_sheet_value['E'] = $work_description;
$time_sheet_value['F'] = $time_sheet['emails'];
$time_sheet_value['G'] = $study1;
$time_sheet_value['H'] = $qa_checking1;
$time_sheet_value['I'] = $discussion1;
$time_sheet_value['J'] = $was1;
$time_sheet_value['K'] = $monitoring1;
$time_sheet_value['L'] = $time_sheet['rfi'];
$time_sheet_value['M'] = $study2;
$time_sheet_value['N'] = $qa_checking2;
$time_sheet_value['O'] = $discussion2;
$time_sheet_value['P'] = $was2;
$time_sheet_value['Q'] = $monitoring2;
$time_sheet_value['R'] = $time_sheet['co_checking'];
$time_sheet_value['S'] = $time_sheet['bar_listing_time'];
$time_sheet_value['T'] = $time_sheet['other_works'];
$time_sheet_value['U'] = $total_hours;
$time_sheet_value['V'] = $date_wise_time;
$time_sheet_value['W'] = $time_sheet['in_time'];
$time_sheet_value['X'] = $time_sheet['out_time'];
$time_sheet_value['Y'] = $time_sheet['total_time'];
$time_sheet_value['Z'] = "shift";
$sum_study1[] = $study1;
$sum_study2[] = $study2;
$sum_qa_checking1[] = $qa_checking1;
$sum_qa_checking2[] = $qa_checking2;
$sum_discussion1[] = $discussion1;
$sum_discussion2[] = $discussion2;
$sum_was1[] = $was1;
$sum_was2[] = $was2;
$sum_monitoring1[] = $monitoring1;
$sum_monitoring2[] = $monitoring2;
$sum_rfi[] = $time_sheet['rfi'];
$sum_co_checking[] = $time_sheet['co_checking'];
$sum_bar_listing_time[] = $time_sheet['bar_listing_time'];
$sum_other_works[] = $time_sheet['other_works'];
$sum_emails[] = $time_sheet['emails'];
$sum_value_study1 = $this->AddPlayTime($sum_study1);
$sum_value_study2 = $this->AddPlayTime($sum_study2);
$sum_value_qa_checking1 = $this->AddPlayTime($sum_qa_checking1);
$sum_value_qa_checking2 = $this->AddPlayTime($sum_qa_checking2);
$sum_value_discussion1 = $this->AddPlayTime($sum_discussion1);
$sum_value_discussion2 = $this->AddPlayTime($sum_discussion2);
$sum_value_was1 = $this->AddPlayTime($sum_was1);
$sum_value_was2 = $this->AddPlayTime($sum_was2);
$sum_value_monitoring1 = $this->AddPlayTime($sum_monitoring1);
$sum_value_monitoring2 = $this->AddPlayTime($sum_monitoring2);
$sum_value_rfi = $this->AddPlayTime($sum_rfi);
$sum_value_bar_listing_time = $this->AddPlayTime($sum_bar_listing_time);
$sum_value_other_works = $this->AddPlayTime($sum_other_works);
$sum_value_co_checking = $this->AddPlayTime($sum_co_checking);
$sum_value_emails = $this->AddPlayTime($sum_emails);
for ($x = 0; $x <= 25; $x++) {
$excel_column = $excel[0]['excel_column'][$x];
$value_of_excel = $time_sheet_value[$excel_column];
$start_cell = $excel_column.$range_start;
$end_cell = $excel_column.$range_end;
if($excel_column === 'V' || $excel_column === 'W' || $excel_column === 'X' || $excel_column === 'Y'){
$obj->getActiveSheet()->setCellValue($excel_column.$i, $value_of_excel)->mergeCells($start_cell.':'.$end_cell)->getStyle($start_cell.':'.$end_cell)->applyFromArray($verticalStyle);
}else
if($excel_column === 'A'){
$obj->getActiveSheet()->setCellValue($excel_column.$i, $value_of_excel)->mergeCells($start_cell.':'.$end_cell)->getStyle($start_cell.':'.$end_cell)->applyFromArray($LeftBorder);
$obj->getActiveSheet()->setCellValue($excel_column.$i, $value_of_excel)->getStyle($excel_column.$i)->applyFromArray($LeftBorder);
}else
if($excel_column === 'Z'){
$obj->getActiveSheet()->setCellValue($excel_column.$i, $value_of_excel)->mergeCells($start_cell.':'.$end_cell)->getStyle($start_cell.':'.$end_cell)->applyFromArray($RightBorder);
$obj->getActiveSheet()->setCellValue($excel_column.$i, $value_of_excel)->getStyle($excel_column.$i)->applyFromArray($RightBorder);
}
else{
$obj->getActiveSheet()->setCellValue($excel_column.$i, $value_of_excel)->getStyle($excel_column.$i)->applyFromArray($verticalStyle);
}
$counter = $i;
}
$i++;
$previous_date = $date_only;
}
$total_time_date_wise = $this->AddPlayTime($time_total);
$counter = $counter+1;
$obj->getActiveSheet()->setCellValue('A'.$counter, $total_sum_detail_work)->mergeCells('A'.$counter.':'.'E'.$counter)->getStyle('A'.$counter.':'.'E'.$counter)->applyFromArray($FooterLeftStyle);
$obj->getActiveSheet()->setCellValue('F'.$counter,$sum_value_emails)->getStyle('F'.$counter)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('G'.$counter,$sum_value_study1)->getStyle('G'.$counter)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('H'.$counter,$sum_value_qa_checking1)->getStyle('H'.$counter)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('I'.$counter,$sum_value_discussion1)->getStyle('I'.$counter)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('J'.$counter,$sum_value_was1)->getStyle('J'.$counter)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('k'.$counter,$sum_value_monitoring1)->getStyle('K'.$counter)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('L'.$counter,$sum_value_rfi)->getStyle('L'.$counter)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('M'.$counter,$sum_value_study2)->getStyle('M'.$counter)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('N'.$counter,$sum_value_qa_checking2)->getStyle('N'.$counter)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('O'.$counter,$sum_value_discussion2)->getStyle('O'.$counter)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('P'.$counter,$sum_value_was2)->getStyle('P'.$counter)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('Q'.$counter,$sum_value_monitoring2)->getStyle('Q'.$counter)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('R'.$counter,$sum_value_co_checking)->getStyle('R'.$counter)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('S'.$counter,$sum_value_bar_listing_time)->getStyle('S'.$counter)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('T'.$counter,$sum_value_other_works)->getStyle('T'.$counter)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('U'.$counter,$sum_value_total_hours)->getStyle('U'.$counter)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('V'.$counter,$sum_value_total_hours)->getStyle('V'.$counter)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('W'.$counter,"")->getStyle('W'.$counter)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('X'.$counter,"")->getStyle('X'.$counter)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('Y'.$counter,$total_time_date_wise)->getStyle('Y'.$counter)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('Z'.$counter,"")->getStyle('Z'.$counter)->applyFromArray($FooterRightStyle);
/* SECOND WORK SHEET */
$cummulative_sheet2 = $counter+3;
$cummulative_sheet3 = $counter+4;
$cummulative_sheet4 = $counter+5;
$cummulative_sheet5 = $counter+6;
$cummulative_sheet6 = $counter+7;
$cummulative_detail_count = $counter+8;
$process_months = '01-'.$process_month;
$process_months = date('Y-m',strtotime($process_months));
$working_days = $working_days_result[0]['working_days'];
$min_std_work_cummlate = $working_days * 8;
$min_ton_cummlate = $credit_target/$min_std_work_cummlate;
$min_ton_cummlate = $this->two_decimal($min_ton_cummlate);
$project_wise_excel[]['excel_column']= array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U');
$project_wise_excel[]['excel_value']= array('Job Category','Project Name','# New dwg','# Rev dwg','Remarks','Emails','STY','QA CHK','DIS','WAS','MOR','RFI','STY','QA CHK','DIS','WAS','MOR','CO CHK','CHK','OTHER WORK','TOTAL');
for ($x = 0; $x <= 20; $x++) {
$excel_column = $project_wise_excel[0]['excel_column'][$x];
$excel_value = $project_wise_excel[1]['excel_value'][$x];
$obj->getActiveSheet()->setCellValue('A'.$cummulative_sheet2, "Project Manager Name: ".$emp_name)->mergeCells('A'.$cummulative_sheet2.':U'.$cummulative_sheet2)->getStyle('A'.$cummulative_sheet2.':U'.$cummulative_sheet2)->applyFromArray($TopBorder);
$obj->getActiveSheet()->setCellValue('A'.$cummulative_sheet3, "Designation & Experience: ".$calculate_date_month)->mergeCells('A'.$cummulative_sheet3.':U'.$cummulative_sheet3)->getStyle('A'.$cummulative_sheet3.':U'.$cummulative_sheet3)->applyFromArray($LeftrightBorder);
$obj->getActiveSheet()->setCellValue('A'.$cummulative_sheet4, "Working Days")->getStyle('A'.$cummulative_sheet4)->applyFromArray($LeftArray);
$obj->getActiveSheet()->setCellValue('B'.$cummulative_sheet4, "Min. Standard Working Hours")->getStyle('B'.$cummulative_sheet4)->applyFromArray($styleArray);
$obj->getActiveSheet()->setCellValue('C'.$cummulative_sheet4, "Target Tons")->getStyle('C'.$cummulative_sheet4)->applyFromArray($styleArray);
$obj->getActiveSheet()->setCellValue('D'.$cummulative_sheet4, "Min Shts/Day")->getStyle('D'.$cummulative_sheet4)->applyFromArray($styleArray);
$obj->getActiveSheet()->setCellValue('E'.$cummulative_sheet4, "")->mergeCells('E'.$cummulative_sheet4.':U'.$cummulative_sheet4)->getStyle('E'.$cummulative_sheet4.':U'.$cummulative_sheet4)->applyFromArray($RightBorderHead);
$obj->getActiveSheet()->setCellValue('A'.$cummulative_sheet5, $working_days)->getStyle('A'.$cummulative_sheet5)->applyFromArray($LeftArray);
$obj->getActiveSheet()->setCellValue('B'.$cummulative_sheet5, $min_std_work_cummlate)->getStyle('B'.$cummulative_sheet5)->applyFromArray($styleArray);
$obj->getActiveSheet()->setCellValue('C'.$cummulative_sheet5, $credit_target)->getStyle('C'.$cummulative_sheet5)->applyFromArray($styleArray);
$obj->getActiveSheet()->setCellValue('D'.$cummulative_sheet5, $min_ton_cummlate)->getStyle('D'.$cummulative_sheet5)->applyFromArray($styleArray);
$obj->getActiveSheet()->setCellValue('E'.$cummulative_sheet5, "Team:".$team_name.", ".$emp_name)->getStyle('E'.$cummulative_sheet5)->applyFromArray($styleArray);
$obj->getActiveSheet()->setCellValue('F'.$cummulative_sheet5, "Emails")->getStyle('F'.$cummulative_sheet5)->applyFromArray($styleArray);
$obj->getActiveSheet()->setCellValue('G'.$cummulative_sheet5, "Detailing Work")->mergeCells('G'.$cummulative_sheet5.':K'.$cummulative_sheet5)->getStyle('G'.$cummulative_sheet5.':K'.$cummulative_sheet5)->applyFromArray($styleArray);
$obj->getActiveSheet()->setCellValue('L'.$cummulative_sheet5, "")->getStyle('L'.$cummulative_sheet5)->applyFromArray($styleArray);
$obj->getActiveSheet()->setCellValue('M'.$cummulative_sheet5, "Revision Work")->mergeCells('M'.$cummulative_sheet5.':R'.$cummulative_sheet5)->getStyle('M'.$cummulative_sheet5.':R'.$cummulative_sheet5)->applyFromArray($styleArray);
$obj->getActiveSheet()->setCellValue('S'.$cummulative_sheet5, "Listing")->getStyle('S'.$cummulative_sheet5)->applyFromArray($styleArray);
$obj->getActiveSheet()->setCellValue('T'.$cummulative_sheet5, "OTHER WORKS")->getStyle('T'.$cummulative_sheet5)->applyFromArray($styleArray);
$obj->getActiveSheet()->setCellValue('U'.$cummulative_sheet5, "TOTAL")->getStyle('U'.$cummulative_sheet5)->applyFromArray($RightArray);
if($excel_column === 'A'){
$obj->getActiveSheet()->setCellValue($excel_column.$cummulative_sheet6, $excel_value)->getStyle($excel_column.$cummulative_sheet6)->applyFromArray($LeftArray);
}else
if($excel_column === 'U'){
$obj->getActiveSheet()->setCellValue('U'.$cummulative_sheet5, $excel_value)->mergeCells('U'.$cummulative_sheet5.':'.$excel_column.$cummulative_sheet6)->getStyle('U'.$cummulative_sheet5.':'.$excel_column.$cummulative_sheet6)->applyFromArray($doubleColumnStyleRight);
}else
if($excel_column === 'F'){
$obj->getActiveSheet()->setCellValue('F'.$cummulative_sheet5, $excel_value)->mergeCells('F'.$cummulative_sheet5.':'.$excel_column.$cummulative_sheet6)->getStyle('F'.$cummulative_sheet5.':'.$excel_column.$cummulative_sheet6)->applyFromArray($doubleColumnStyle);
}else
if($excel_column === 'T'){
$obj->getActiveSheet()->setCellValue('T'.$cummulative_sheet5, $excel_value)->mergeCells('T'.$cummulative_sheet5.':'.$excel_column.$cummulative_sheet6)->getStyle('T'.$cummulative_sheet5.':'.$excel_column.$cummulative_sheet6)->applyFromArray($doubleColumnStyle);
}
else{
$obj->getActiveSheet()->setCellValue($excel_column.$cummulative_sheet6, $excel_value)->getStyle($excel_column.$cummulative_sheet6)->applyFromArray($styleArray);
}
}
$project_wise_count = $project_wise_result[0]['project_wise_count'];
if($project_wise_result){
$project_wise_result = array_reduce($project_wise_result, function($result, $arr){
$result[$arr['project']][$arr['work_type']] = $arr;
return $result;
}, array());
}
if($complete_pending_result){
$complete_pending_result = array_reduce($complete_pending_result, function($result, $arr){
$result[$arr['project']][$arr['work_status']] = $arr;
return $result;
}, array());
}
if($count_rslt){
$count_rslt = array_reduce($count_rslt, function($result, $arr){
$result[$arr['work_type']][$arr['project']][]= $arr;
return $result;
}, array());
}
$q = $cummulative_detail_count;
$r = 0;
$s = 0;
$sum_new_detail_count = 0;
$sum_new_rev_count = 0;
$sum_value_bar_list_quantity_cummlate = 0;
if((int)$project_wise_count === 0){
$cummuate_second_count = $cummulative_detail_count;
}else{
$total_credit_project_wise = array();
$total_detailing_count = 0;
$total_revision_count = 0;
foreach($project_wise_result as $key => $cummulative_time_sheet){
$emails_cummulate = array();
foreach ($cummulative_time_sheet as $aa => $value) {
$cummulate_emails1 = array();
$project_name = array();
$cummulate_emails1[$key] = $value['cummulate_emails'];
$pro_name = str_replace("xdbquot",'"',$value['project_name']);
$pro_name = str_replace("xquot","'",$pro_name);
$pro_name = str_replace("xxamp","&",$pro_name);
$project_name[$key] = $pro_name;
$emails_cummulate[] = $cummulate_emails1[$key];
$emails_project_wise = $this->AddPlayTime($emails_cummulate);
$project_name1 = $project_name[$key];
}
$cummulate_booking_hours = array();
$work_type1 = $cummulative_time_sheet[1];
$work_type2 = $cummulative_time_sheet[2];
$work_type3 = $cummulative_time_sheet[3];
$job_category1 = $work_type1['job_category'];
$work_description1 = $work_type1['work_description'];
$cummulate_study1 = $work_type1['cummulate_study'];
$cummulate_qa_checking1 = $work_type1['cummulate_qa_checking'];
$cummulate_discussion1 = $work_type1['cummulate_discussion'];
$cummulate_was1 = $work_type1['cummulate_was'];
$cummulate_monitoring1 = $work_type1['cummulate_monitoring'];
$cummulate_rfi2 = $work_type2['cummulate_rfi'];
$cummulate_study2 = $work_type2['cummulate_study'];
$cummulate_qa_checking2 = $work_type2['cummulate_qa_checking'];
$cummulate_discussion2 = $work_type2['cummulate_discussion'];
$cummulate_was2 = $work_type2['cummulate_was'];
$cummulate_monitoring2 = $work_type2['cummulate_monitoring'];
$cummulate_co_checking2 = $work_type2['cummulate_co_checking'];
$cummulate_bar_listing_time3 = $work_type3['cummulate_bar_listing_time'];
$total_emails_project_wise[] = $emails_project_wise;
$emails_total = $this->AddPlayTime($total_emails_project_wise);
$detailing_count1 = $count_rslt[1][$key];
$revision_count1 = $count_rslt[2][$key];
if($detailing_count1){
$detailing_count = count($detailing_count1);
}
if($revision_count1){
$revision_count = count($revision_count1);
}
$total_detailing_count += $detailing_count;
$total_revision_count += $revision_count;
$cummulate_booking_hours[] = $cummulate_study1;
$cummulate_booking_hours[] = $cummulate_qa_checking1;
$cummulate_booking_hours[] = $cummulate_discussion1;
$cummulate_booking_hours[] = $cummulate_was1;
$cummulate_booking_hours[] = $cummulate_monitoring1;
$cummulate_booking_hours[] = $cummulate_rfi2;
$cummulate_booking_hours[] = $cummulate_study2;
$cummulate_booking_hours[] = $cummulate_qa_checking2;
$cummulate_booking_hours[] = $cummulate_discussion2;
$cummulate_booking_hours[] = $cummulate_was2;
$cummulate_booking_hours[] = $cummulate_monitoring2;
$cummulate_booking_hours[] = $cummulate_co_checking2;
$cummulate_booking_hours[] = $cummulate_bar_listing_time3;
$cummulate_booking_hours[] = $cummulate_other_works;
$cummulate_booking_hours[] = $emails_project_wise;
$cummulate_total_hours = $this->AddPlayTime($cummulate_booking_hours);
$pending_status_counting = $complete_pending_result[$key][1]['work_status_count'];
$partial_status_counting = $complete_pending_result[$key][2]['work_status_count'];
$complete_status_counting = $complete_pending_result[$key][3]['work_status_count'];
if((int)$pending_status_counting ===0){
$pending_status_counting = 0;
}else{
$pending_status_counting = $pending_status_counting;
}
if((int)$partial_status_counting ===0){
$partial_status_counting = 0;
}else{
$partial_status_counting = $partial_status_counting;
}
if((int)$complete_status_counting ===0){
$complete_status_counting = 0;
}else{
$complete_status_counting = $complete_status_counting;
}
$total_pending = $partial_status_counting + $pending_status_counting;
$total_works_status = "(".$complete_status_counting.") Completed - (".$total_pending.") Inprogress";
$obj->getActiveSheet()->setCellValue("A$q", $job_category1)->getStyle('A'.$q)->applyFromArray($LeftBorder);
$obj->getActiveSheet()->setCellValue("B$q", $project_name1)->getStyle('B'.$q)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("C$q", $detailing_count)->getStyle('C'.$q)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("D$q", $detailing_count)->getStyle('D'.$q)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("E$q", $total_works_status)->getStyle('E'.$q)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("F$q", $emails_project_wise)->getStyle('F'.$q)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("G$q", $cummulate_study1)->getStyle('G'.$q)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("H$q", $cummulate_qa_checking1)->getStyle('H'.$q)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("I$q", $cummulate_discussion1)->getStyle('I'.$q)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("J$q", $cummulate_was1)->getStyle('J'.$q)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("K$q", $cummulate_monitoring1)->getStyle('K'.$q)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("L$q", $cummulate_rfi2)->getStyle('L'.$q)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("M$q", $cummulate_study2)->getStyle('M'.$q)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("N$q", $cummulate_qa_checking2)->getStyle('N'.$q)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("O$q", $cummulate_discussion2)->getStyle('O'.$q)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("P$q", $cummulate_was2)->getStyle('P'.$q)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("Q$q", $cummulate_monitoring2)->getStyle('Q'.$q)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("R$q", $cummulate_co_checking2)->getStyle('R'.$q)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("S$q", $cummulate_bar_listing_time3)->getStyle('S'.$q)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("T$q", $cummulate_other_works)->getStyle('T'.$q)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("U$q", $cummulate_total_hours)->getStyle('U'.$q)->applyFromArray($RightBorder);
$sum_cummulate_study1[] = $cummulate_study1;
$sum_value_cummulate_study1 = $this->AddPlayTime($sum_cummulate_study1);
$sum_cummulate_qa_checking1[] = $cummulate_qa_checking1;
$sum_value_cummulate_qa_checking1 = $this->AddPlayTime($sum_cummulate_qa_checking1);
$sum_cummulate_discussion1[] = $cummulate_discussion1;
$sum_value_cummulate_discussion1 = $this->AddPlayTime($sum_cummulate_discussion1);
$sum_cummulate_was1[] = $cummulate_was1;
$sum_value_cummulate_was1 = $this->AddPlayTime($sum_cummulate_was1);
$sum_cummulate_monitoring1[] = $cummulate_monitoring1;
$sum_value_cummulate_monitoring1 = $this->AddPlayTime($sum_cummulate_monitoring1);
$sum_cummulate_rfi2[] = $cummulate_rfi2;
$sum_value_cummulate_rfi2 = $this->AddPlayTime($sum_cummulate_rfi2);
$sum_cummulate_study2[] = $cummulate_study2;
$sum_value_cummulate_study2 = $this->AddPlayTime($sum_cummulate_study2);
$sum_cummulate_qa_checking2[] = $cummulate_qa_checking2;
$sum_value_cummulate_qa_checking2 = $this->AddPlayTime($sum_cummulate_qa_checking2);
$sum_cummulate_discussion2[] = $cummulate_discussion2;
$sum_value_cummulate_discussion2 = $this->AddPlayTime($sum_cummulate_discussion2);
$sum_cummulate_was2[] = $cummulate_was2;
$sum_value_cummulate_was2 = $this->AddPlayTime($sum_cummulate_was2);
$sum_cummulate_monitoring2[] = $cummulate_monitoring2;
$sum_value_cummulate_monitoring2 = $this->AddPlayTime($sum_cummulate_monitoring2);
$sum_cummulate_co_checking2[] = $cummulate_co_checking2;
$sum_value_cummulate_co_checking2 = $this->AddPlayTime($sum_cummulate_co_checking2);
$sum_cummulate_bar_listing_time[] = $cummulate_bar_listing_time3;
$sum_value_cummulate_bar_listing_time = $this->AddPlayTime($sum_cummulate_bar_listing_time);
$sum_cummulate_total_hours[] = $cummulate_total_hours;
$sum_value_cummulate_total_hours = $this->AddPlayTime($sum_cummulate_total_hours);
$sum_cummulate_emails1[] = $cummulative_time_sheet->cummulate_emails;
$sum_value_cummulate_emails1 = $this->AddPlayTime($sum_cummulate_emails1);
$sum_new_detail_count += $new_detail_count;
$sum_new_rev_count += $new_rev_count;
$cummuate_second_count = $q;
$q++;
}
}
if((int)$work_result_count === 0){
$m = $cummuate_second_count;
$cummuate_final_count = $cummuate_second_count;
}else{
$other_work_count = $cummuate_second_count+1;
$m = $other_work_count;
foreach($other_work_result as $key => $other_work_detail){
$other_description_replace = $other_work_detail['work_description'];
$other_description = str_replace("xdbquot",'"',$other_description_replace);
$other_description = str_replace("xquot","'",$other_description);
$other_description = str_replace("xxamp","&",$other_description);
$total_other_times = array();
$total_other_times[] = $other_work_detail['cummulate_emails'];
$total_other_times[] = $other_work_detail['cummulate_works'];
$total_other_time = $this->AddPlayTime($total_other_times);
$obj->getActiveSheet()->setCellValue("A$m", "")->getStyle('A'.$m)->applyFromArray($LeftBorder);
$obj->getActiveSheet()->setCellValue("B$m", $other_work_detail['other_works'])->getStyle('B'.$m)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("C$m", "")->getStyle('C'.$m)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("D$m", "")->getStyle('D'.$m)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("E$m", $other_description)->getStyle('E'.$m)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("F$m", $other_work_detail['cummulate_emails'])->getStyle('F'.$m)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("G$m", "")->getStyle('G'.$m)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("H$m", "")->getStyle('H'.$m)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("I$m", "")->getStyle('I'.$m)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("J$m", "")->getStyle('J'.$m)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("K$m", "")->getStyle('K'.$m)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("L$m", "")->getStyle('L'.$m)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("M$m", "")->getStyle('M'.$m)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("N$m", "")->getStyle('N'.$m)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("O$m", "")->getStyle('O'.$m)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("P$m", "")->getStyle('P'.$m)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("Q$m", "")->getStyle('Q'.$m)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("R$m", "")->getStyle('R'.$m)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("S$m", "")->getStyle('S'.$m)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("T$m", $other_work_detail['cummulate_works'])->getStyle('T'.$m)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue("U$m", $total_other_time)->getStyle('U'.$m)->applyFromArray($RightBorder);
$sum_cummulate_works[] = $other_work_detail['cummulate_works'];
$sum_value_cummulate_works = $this->AddPlayTime($sum_cummulate_works);
$sum_cummulate_emails2[] = $other_work_detail['cummulate_emails'];
$sum_value_cummulate_emails2 = $this->AddPlayTime($sum_cummulate_emails2);
$cummuate_final_count = $m;
$m++;
}
}
$sum_value_cummulate_emails[] = $emails_total;
$sum_value_cummulate_emails[] = $sum_value_cummulate_emails2;
$sum_value_cummulate_emails = $this->AddPlayTime($sum_value_cummulate_emails);
$cummuate_final_sumcount = $cummuate_final_count+1;
$cummuate_final_second_sumcount = $cummuate_final_sumcount+1;
$final_sum_total = array();
$final_sum_total[] = $sum_value_cummulate_works;
$final_sum_total[] = $sum_value_cummulate_total_hours;
$final_sum_total[] = $sum_value_cummulate_emails2;
$final_sum_total = $this->AddPlayTime($final_sum_total);
$obj->getActiveSheet()->setCellValue('A'.$cummuate_final_sumcount, "")->mergeCells('A'.$cummuate_final_sumcount.':'.'B'.$cummuate_final_sumcount)->getStyle('A'.$cummuate_final_sumcount.':'.'B'.$cummuate_final_sumcount)->applyFromArray($FooterLeftStyle);
$obj->getActiveSheet()->setCellValue('C'.$cummuate_final_sumcount,$total_detailing_count)->getStyle('C'.$cummuate_final_sumcount)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('D'.$cummuate_final_sumcount,$total_revision_count)->getStyle('D'.$cummuate_final_sumcount)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('E'.$cummuate_final_sumcount,"")->getStyle('E'.$cummuate_final_sumcount)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('F'.$cummuate_final_sumcount,$sum_value_cummulate_emails)->getStyle('F'.$cummuate_final_sumcount)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('G'.$cummuate_final_sumcount,$sum_value_cummulate_study1)->getStyle('G'.$cummuate_final_sumcount)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('H'.$cummuate_final_sumcount,$sum_value_cummulate_qa_checking1)->getStyle('H'.$cummuate_final_sumcount)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('I'.$cummuate_final_sumcount,$sum_value_cummulate_discussion1)->getStyle('I'.$cummuate_final_sumcount)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('J'.$cummuate_final_sumcount,$sum_value_cummulate_was1)->getStyle('J'.$cummuate_final_sumcount)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('k'.$cummuate_final_sumcount,$sum_value_cummulate_monitoring1)->getStyle('K'.$cummuate_final_sumcount)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('L'.$cummuate_final_sumcount,$sum_value_cummulate_rfi2)->getStyle('L'.$cummuate_final_sumcount)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('M'.$cummuate_final_sumcount,$sum_value_cummulate_study2)->getStyle('M'.$cummuate_final_sumcount)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('N'.$cummuate_final_sumcount,$sum_value_cummulate_qa_checking2)->getStyle('N'.$cummuate_final_sumcount)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('O'.$cummuate_final_sumcount,$sum_value_cummulate_discussion2)->getStyle('O'.$cummuate_final_sumcount)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('P'.$cummuate_final_sumcount,$sum_value_cummulate_was2)->getStyle('P'.$cummuate_final_sumcount)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('Q'.$cummuate_final_sumcount,$sum_value_cummulate_monitoring2)->getStyle('Q'.$cummuate_final_sumcount)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('R'.$cummuate_final_sumcount,$sum_value_cummulate_co_checking2)->getStyle('R'.$cummuate_final_sumcount)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('S'.$cummuate_final_sumcount,$sum_value_cummulate_bar_listing_time)->getStyle('S'.$cummuate_final_sumcount)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('T'.$cummuate_final_sumcount,$sum_value_cummulate_works)->getStyle('T'.$cummuate_final_sumcount)->applyFromArray($FooterStyle);
$obj->getActiveSheet()->setCellValue('U'.$cummuate_final_sumcount,$final_sum_total)->getStyle('U'.$cummuate_final_sumcount)->applyFromArray($FooterRightStyle);
/* THIRD WORK SHEET */
$report_head = $cummuate_final_second_sumcount+5;
$report_inc3 = $cummuate_final_second_sumcount+6;
$report_inc4 = $report_inc3+1;
$report_inc5 = $report_inc4+1;
$report_inc6 = $report_inc5+1;
$report_inc7 = $report_inc6+1;
$report_inc8 = $report_inc7+1;
$report_inc9 = $report_inc8+1;
$report_inc10 = $report_inc9+1;
$report_inc11 = $report_inc10+1;
$report_inc12 = $report_inc11+1;
$report_inc13 = $report_inc12+1;
$report_inc14 = $report_inc13+1;
$report_inc15 = $report_inc14+1;
$report_inc16 = $report_inc15+1;
$report_inc17 = $report_inc16+1;
$report_inc18 = $report_inc17+1;
$report_inc19 = $report_inc18+1;
$no_of_holiday = 0;
$no_of_taken_leave = 0;
$no_of_working_days = $working_days + $no_of_holiday - $no_of_taken_leave;
$min_different_booking_hrs = $this->time_to_decimal($final_sum_total);
$min_different_booking_hrs = $min_different_booking_hrs/24;
$min_different_booking_hrs = $this->decimal_to_time($min_different_booking_hrs);
$process_month = '01-'.$process_month;
$process_month = date('Y-m',strtotime($process_month));
$working_days_time = $working_days_result[0]['working_days_time'];
$start = $working_days_time;
$end = $min_different_booking_hrs;
$getnum = function($value) {
$pieces = explode(':', $value);
if(count($pieces) > 0) {
return (intval($pieces[0])*60)+intval($pieces[1]);
}
return 0;
};
$start_num = $getnum->__invoke($total_time_date_wise);
$end_num = $getnum->__invoke($sum_value_total_hours);
$diff = max($start_num, $end_num) - min($end_num, $start_num);
$diff_office_office = intval($diff / 60).':'.($diff % 60);
$actual_tonnage = $pro_ton_rslt[0]['actual_tonnage'];
$actual_billable_time = $actual_result[0]['actual_billable_time'];
$actual_billable_time = explode(':', $actual_billable_time);
$actual_billable_time = $actual_billable_time[0].':'.$actual_billable_time[1];
$decimalHours = $this->decimal_to_time($actual_billable_time);
$decimalHours = $decimalHours/24;
$rev_hrs_tons = $decimalHours * 1.5;
$rev_hrs_tons = $this->two_decimal($rev_hrs_tons);
$production_tons = $rev_hrs_tons + $actual_tonnage;
$production_tons = $this->two_decimal($production_tons);
if($production_tons<=$credit_target){
$target_status = "Not Reached";
}else{
$target_status = "Reached";
}
$submitted_log_actual_tonnage = $sub_log_app_rslt[0]['submit_actual_tonnage'];
$submitted_log_actual_billable_time = $sub_log_rev_rslt[0]['act_bill_hours'];
if($new_detail_rslt){
$checked_sheet_new_details = count($new_detail_rslt);
}
if($new_detail_rslt){
$qa_major = array_sum(array_column($new_detail_rslt, 'qa_major'));
}
if($new_detail_rslt){
$qa_minor = array_sum(array_column($new_detail_rslt, 'qa_minor'));
}
$qa_error_count = $qa_major+$qa_minor;
$get_rev_count = $rev_detail_rslt[0]['drawing_no_pm'];
if($get_rev_count){
$checked_sheet_rev_details = count(array_filter(explode(',',$get_rev_count)));
}else{
$checked_sheet_rev_details = "0";
}
if((int)$checked_sheet_new_details !== 0){
$avg_qa_error = $qa_error_count/$checked_sheet_new_details;
}else{
$avg_qa_error = 0;
}
$avg_qa_error = $this->two_decimal($avg_qa_error);
if($avg_qa_error === INF){
$avg_qa_error =0;
}
if($avg_qa_error === NAN){
$avg_qa_error =0;
}
if($new_detail_rslt){
$actual_tonnage_team = array_sum(array_column($new_detail_rslt, 'actual_tonnage'));
}
if((int)$actual_tonnage_team === 0){
$actual_tonnage_team = 0;
}
$team_rev_hours_tons = $this->decimalHours($submitted_log_actual_billable_time);
$team_rev_hours_tons = $team_rev_hours_tons * 1.5;
$team_rev_hours_tons = $this->two_decimal($team_rev_hours_tons);
$team_tot_production_tons = $team_rev_hours_tons + $submitted_log_actual_tonnage;
$team_tot_production_tons = $this->two_decimal($team_tot_production_tons);
$team_ton_per_hrs_detail = $team_tot_production_tons/1000;
$team_ton_per_hrs_detail = $this->two_decimal($team_ton_per_hrs_detail);
$team_ton_per_detail = $team_ton_det_rslt[0]['team_ton_per_detail'];
if((int)$team_ton_per_detail !== 0){
$tons_new_detailing = $team_tot_production_tons/$team_ton_per_detail;
}else{
$tons_new_detailing = 0;
}
$tons_new_detailing = round($tons_new_detailing,2);
if((int)$checked_sheet_new_details !== 0){
$team_ton_per_sheet = $actual_tonnage_team/$checked_sheet_new_details;
}else{
$team_ton_per_sheet = 0;
}
$team_ton_per_sheet = $this->two_decimal($team_ton_per_sheet);
if($team_ton_per_sheet === INF){
$team_ton_per_sheet =0;
}
if($team_ton_per_sheet === NAN){
$team_ton_per_sheet =0;
}
$off_hours = $this->time_to_decimal('08:30');
$off_hours = $no_of_working_days * $off_hours;
$off_hours = $this->decimal_to_time($off_hours);
$off_break = $this->time_to_decimal('00:45');
$off_break = $no_of_working_days * $off_break;
$off_break = $this->decimal_to_time($off_break);
$offs_hours = $this->time_to_min($off_hours);
$off_breaks = $this->time_to_min($off_break);
$off_diff = $offs_hours-$off_breaks;
$off_total_hours= $this->mins_to_hrs($off_diff);
$office_total_hour = $this->time_to_min($off_total_hours);
$bk_totals = $this->time_to_min($sum_value_total_hours);
$res3 = $office_total_hour-$bk_totals;
$balance_time = $this->mins_to_hrs($res3);
$max_off_hrs = $this->time_to_decimal($off_hours);
$max_off_bks = $this->time_to_decimal($off_break);
$max_tot_hrs = $this->time_to_decimal($total_time_date_wise);
$max_bk_allow = $max_tot_hrs * $max_off_bks;
if((int)$max_off_hrs !== 0){
$max_bk_allow = $max_bk_allow/$max_off_hrs;
}else{
$max_bk_allow = 0;
}
$max_bk_allow = $this->decimal_to_time($max_bk_allow);
$project_excel[]['excel_column']= array('C'.$report_inc3,'C'.$report_inc4,'C'.$report_inc5,'C'.$report_inc6,'C'.$report_inc7,'C'.$report_inc8,'C'.$report_inc9,'C'.$report_inc10,'C'.$report_inc11,'C'.$report_inc12,'C'.$report_inc13,'C'.$report_inc14,'C'.$report_inc15,'C'.$report_inc16,'C'.$report_inc17,'C'.$report_inc18);
$project_excel[]['excel_value']= array('No. of Working Days','Total Office hours','Total Booking hours','Difference b/t Booking & Office Hrs','X','Teams Tons Detailed (Submitted Log)','Teams Rev. hours (Submitted Log)','Teams Rev. hours in Tons','Teams Total Production Tons','Target Reached/Not Reached','Checked Sheets New (Submitted Log)','Checked Sheets Rev (Time Sheet)','Teams Tons per Hour New Detailing only','Teams Tons per Sheet','Total QA Error Count (Submitted Log)','Avg. QA Error Count per Sheet');
$project_excel[]['end_column']= array('G'.$report_inc3,'G'.$report_inc4,'G'.$report_inc5,'G'.$report_inc6,'G'.$report_inc7,'G'.$report_inc8,'G'.$report_inc9,'G'.$report_inc10,'G'.$report_inc11,'G'.$report_inc12,'G'.$report_inc13,'G'.$report_inc14,'G'.$report_inc15,'G'.$report_inc16,'G'.$report_inc17,'G'.$report_inc18);
$project_excel[]['column_cell']= array('H'.$report_inc3,'H'.$report_inc4,'H'.$report_inc5,'H'.$report_inc6,'H'.$report_inc7,'H'.$report_inc8,'H'.$report_inc9,'H'.$report_inc10,'H'.$report_inc11,'H'.$report_inc12,'H'.$report_inc13,'H'.$report_inc14,'H'.$report_inc15,'H'.$report_inc16,'H'.$report_inc17,'H'.$report_inc18);
$project_excel[]['column_value']= array($no_of_working_days,$total_time_date_wise,$sum_value_total_hours,$diff_office_office,"",$submitted_log_actual_tonnage,$submitted_log_actual_billable_time,$team_rev_hours_tons,$team_tot_production_tons,$target_status,$checked_sheet_new_details,$checked_sheet_rev_details,$tons_new_detailing,$team_ton_per_sheet,$qa_error_count,$avg_qa_error);
$project_excel[]['column_end']= array('I'.$report_inc3,'I'.$report_inc4,'I'.$report_inc5,'I'.$report_inc6,'I'.$report_inc7,'I'.$report_inc8,'I'.$report_inc9,'I'.$report_inc10,'I'.$report_inc11,'I'.$report_inc12,'I'.$report_inc13,'I'.$report_inc14,'I'.$report_inc15,'I'.$report_inc16,'I'.$report_inc17,'I'.$report_inc18);
$match_id = 'H'.$report_inc18;
for ($x = 0; $x <= 15; $x++) {
$excel_column = $project_excel[0]['excel_column'][$x];
$excel_value = $project_excel[1]['excel_value'][$x];
$end_column = $project_excel[2]['end_column'][$x];
$column_cell = $project_excel[3]['column_cell'][$x];
$column_value = $project_excel[4]['column_value'][$x];
$column_end = $project_excel[5]['column_end'][$x];
$obj->getActiveSheet()->setCellValue('C'.$report_head, "Project Manager Name: ".$emp_name)->mergeCells('C'.$report_head.':I'.$report_head)->getStyle('C'.$report_head.':I'.$report_head)->applyFromArray($TopBorder);
if($match_id === $column_cell){
$obj->getActiveSheet()->setCellValue($excel_column, $excel_value)->mergeCells($excel_column.':'.$end_column)->getStyle($excel_column.':'.$column_end)->applyFromArray($FooterLeftStyletwo);
$obj->getActiveSheet()->setCellValue($column_cell, $column_value)->mergeCells($column_cell.':'.$column_end)->getStyle($column_cell.':'.$column_end)->applyFromArray($FooterRightStyletwo);
}else{
$obj->getActiveSheet()->setCellValue($excel_column, $excel_value)->mergeCells($excel_column.':'.$end_column)->getStyle($excel_column.':'.$column_end)->applyFromArray($LeftBorder);
$obj->getActiveSheet()->setCellValue($column_cell, $column_value)->mergeCells($column_cell.':'.$column_end)->getStyle($column_cell.':'.$column_end)->applyFromArray($RightBordertwo);
}
$obj->getActiveSheet()->setCellValue('J'.$report_inc5, "Max Break Allowed")->mergeCells('J'.$report_inc5.':K'.$report_inc5)->getStyle('J'.$report_inc5.':K'.$report_inc5)->applyFromArray($LeftBorder);
$obj->getActiveSheet()->setCellValue('J'.$report_head, "Office Hrs")->getStyle('J'.$report_head)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue('K'.$report_head, "Break Hrs")->getStyle('K'.$report_head)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue('L'.$report_head, "Diff Hrs")->getStyle('L'.$report_head)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue('J'.$report_inc3, $off_hours)->getStyle('J'.$report_inc3)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue('K'.$report_inc3, $off_break)->getStyle('K'.$report_inc3)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue('L'.$report_inc3, $off_total_hours)->getStyle('L'.$report_inc3)->applyFromArray($verticalStyle);
$obj->getActiveSheet()->setCellValue('J'.$report_inc6, $max_bk_allow)->mergeCells('J'.$report_inc6.':K'.$report_inc6)->getStyle('J'.$report_inc6.':K'.$report_inc6)->applyFromArray($LeftBorder);
}
$filename= $control_name."_".$employee_code.".xls"; //save our workbook as this file name
$objWriter = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($obj);
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));
}
function decimalHours($time)
{
$hms = explode(":", $time);
return ((int)$hms[0] + (int)($hms[1]/60) + (int)($hms[2]/3600));
}
function time_to_decimal($time) {
$timeArr = explode(':', $time);
$decTime = ($timeArr[0]*60) + ($timeArr[1]) + ($timeArr[2]/60);
return $decTime;
}
function decimal_to_time($decimal) {
$decimal = (int)$decimal;
$hours = floor($decimal / 60);
$minutes = floor($decimal % 60);
$seconds = $decimal - (int)$decimal;
$seconds = round($seconds * 60);
return str_pad($hours, 2, "0", STR_PAD_LEFT) . ":" . str_pad($minutes, 2, "0", STR_PAD_LEFT) . ":" . str_pad($seconds, 2, "0", STR_PAD_LEFT);
}
public function time_to_min($time){
$timeArr = explode(':', $time);
$decTime = ((int)$timeArr[0]*60) + ((int)$timeArr[1]) + ((int)$timeArr[2]/60);
return $decTime;
}
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);
(int)$minutes += (int)$hour * 60;
(int)$minutes += (int)$minute;
}
$hours = floor($minutes / 60);
$minutes -= (int)$hours * 60;
// returns the time already formatted
return sprintf('%02d:%02d', $hours, $minutes);
}
}
?>