File: /home/cafsindia/cpaqua.cafsinfotech.in/dump/application/controllers/Submitted_log.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 Submitted_log extends Action_controller{
public function __construct(){
parent::__construct('submitted_log');
$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;
$team_qry = 'select prime_team_id,team_name from cw_team where trans_status = 1';
$team_info = $this->db->query("CALL sp_a_run ('SELECT','$team_qry')");
$team_result = $team_info->result();
$team_info->next_result();
$team_list[""] = "---- Select ----";
foreach ($team_result as $key => $team) {
$prime_team_id = $team->prime_team_id;
$team_name = $team->team_name;
$team_list[$prime_team_id] = $team_name;
}
$data['team_list'] = $team_list;
$from_query = 'select * from cw_form_setting where prime_module_id IN("project_and_drawing_master","tonnage_approval") and label_name in("client_name","project_name","project_manager","received_date","detailer_name","team_leader_name") ORDER BY input_for,field_sort asc';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$from_query')");
$form_result = $form_data->result();
$form_data->next_result();
$fliter_list = $this->get_filter_data($form_result);
$data['fliter_list'] = $fliter_list;
$this->load->view("$this->control_name/manage",$data);
}
public function get_filter_data($form_result){
$filter = array();
foreach($form_result as $setting){
$prime_form_id = (int)$setting->prime_form_id;
$prime_module_id = $setting->prime_module_id;
$input_view_type = (int)$setting->input_view_type;
$input_for = (int)$setting->input_for;
$field_type = (int)$setting->field_type;
$label_id = $setting->label_name;
$label_name = ucwords($setting->view_name);
$field_length = $setting->field_length;
$field_decimals = $setting->field_decimals;
$pick_list_type = (int)$setting->pick_list_type;
$pick_list = $setting->pick_list;
$pick_table = $setting->pick_table;
$auto_prime_id = $setting->auto_prime_id;
$auto_dispaly_value = $setting->auto_dispaly_value;
$field_isdefault = (int)$setting->field_isdefault;
$file_type = (int)$setting->file_type;
$mandatory_field = (int)$setting->mandatory_field;
$unique_field = (int)$setting->unique_field;
$search_show = (int)$setting->search_show;
$array_list = array();
if($label_id != 'role' && $label_id != 'employee_code' && $label_id != 'emp_name'){
if(($field_type === 5) || ($field_type === 7)){
if($pick_list_type === 1){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
if($pick_table == "cw_category"){
$qry = " and prime_category_id != 1";
}else{
$qry = "";
}
$pick_query = "select $pick_list from $pick_table where trans_status = 1 $qry";
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$array_list[""] = "---- $label_name ----";
foreach($pick_result as $pick){
$pick_key = $pick->$pick_list_val_1;
$pick_val = $pick->$pick_list_val_2;
$array_list[$pick_key] = $pick_val;
}
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query = "select $pick_list_val_1,$pick_list_val_2 from $pick_table where $pick_list_val_3 = 1";
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$array_list[""] = "---- $label_name ----";
foreach($pick_result as $pick){
$pick_key = $pick->$pick_list_val_1;
$pick_val = $pick->$pick_list_val_2;
$array_list[$pick_key] = $pick_val;
}
}
}
if(($input_view_type === 1) || ($input_view_type === 2)){
$filter[] = array('label_id'=> $label_id, 'field_isdefault'=> $field_isdefault, 'array_list'=> $array_list, 'field_type'=> $field_type);
}
}
}
return $filter;
}
public function 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);
}
$excel2 = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xls');//('Excel5');
$excel2 = $excel2->load('./excel_download/detailer_report.xls');//default excel template
$fliter_label = $this->input->post("fliter_label");
$fliter_type = $this->input->post("fliter_type");
$field_type = $this->input->post("field_type");
$filter_cond = $this->input->post("filter_cond");
$fliter_val = $this->input->post("fliter_val");
$multipick_val = $this->input->post("multipick_val");
$process_month = $this->input->post("process_month");
$get_month = explode('-', $process_month);
$month_year = $get_month[1];
$month_name = $get_month[0];
$month_name = date("F", mktime($month_name, 1));
$multi_val = (int)$multipick_val-1;
$filter_cond_arr = array_filter($this->input->post("filter_cond"));
$fliter_query = "";
$rev_fliter_query = "";
if(count($filter_cond_arr)){
// detailing & revised filter
$filter_cond = urldecode($filter_cond);
$fliter_val = explode(',', $fliter_val);
$fliter_val_count = count($fliter_val);
$filter_cond = explode(',', $filter_cond);
$field_types = explode(',', $field_type);
$fliter_type = explode(',', $fliter_type);
$fliter_label = explode(',', $fliter_label);
$filter_count = count($fliter_label);
for($i=0;$i<=(int)$filter_count;$i++){
$db_name = $fliter_label[$i];
$table_name = $fliter_type[$i];
$db_cond = $filter_cond[$i];
$db_value = $fliter_val[$i];
$field_type = $field_types[$i];
if(($db_cond) && ($db_value)){
if((int)$field_type === 7){
$search_val = $db_value;
if($db_cond === "LIKE" || $db_cond === "="){ $search_val = "IN($db_value)";
$db_cond = "";
$db_name = "prime_team_id";
$table_qry = " and cw_team";
}else{
$table_qry = " and cw_project_and_drawing_master";
}
}else
if((int)$field_type === 4){
$search_val = date('Y-m-d',strtotime($db_value));
$search_val = $search_val;
$table_qry = " and cw_project_and_drawing_master";
}else{
if($db_name === 'detailer_name' || $db_name === 'team_leader_name'){
$search_val = $db_value;
if($db_cond === "LIKE"){ $search_val = "$db_value%";}
$table_qry = " and cw_tonnage_approval";
}else{
$search_val = $db_value;
if($db_cond === "LIKE"){ $search_val = "$db_value%";}
$table_qry = " and cw_project_and_drawing_master";
}
}
if((int)$table_name === 1){ $fliter_query .= $table_qry.".". $db_name ." ". $db_cond .' '.$search_val.''; }
}
}
// revision filter
for($i=0;$i<=(int)$filter_count;$i++){
$db_name = $fliter_label[$i];
$table_name = $fliter_type[$i];
$db_cond = $filter_cond[$i];
$db_value = $fliter_val[$i];
$field_type = $field_types[$i];
if(($db_cond) && ($db_value)){
if((int)$field_type === 7){
$search_val = $db_value;
if($db_cond === "LIKE" || $db_cond === "="){ $search_val = "IN($db_value)";
$db_cond = "";
$db_name = "team";
$table_qry = " and cw_co_register_log";
}else{
$table_qry = " and cw_co_register_log";
}
}else
if((int)$field_type === 5){
if($db_name === "client_name"){
$db_name = $db_name;
$table_qry = " and cw_project_and_drawing_master";
}else
if($db_name === "project_manager"){
$db_name = "rdd_no";
$table_qry = " and cw_co_register_log";
}else
if($db_name === "detailer_name"){
$db_name = "employee_code";
$table_qry = " and cw_co_register_log";
}else{
$table_qry = " and cw_co_register_log";
}
}else
if((int)$field_type === 4){
$search_val = date('Y-m-d',strtotime($db_value));
$search_val = $search_val;
if($db_name === "received_date"){
$db_name = "entry_date";
$table_qry = " and cw_co_register_log";
}
}else{
$search_val = $db_value;
if($db_cond === "LIKE"){ $search_val = "$db_value%";}
$table_qry = " and cw_co_register_log";
}
if((int)$table_name === 1){ $rev_fliter_query .= $table_qry.".". $db_name ." ". $db_cond .' '.$search_val.''; }
}
}
}
$verticalStyle = array(
'borders' => array(
'allborders' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_DOTTED
)
),
'alignment' => array(
'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
)
);
$RightBorder = array(
'borders' => array(
'right' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK
)
),
'alignment' => array(
'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
)
);
$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,
)
);
$FooterStyle = 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_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,
)
);
$header_first = 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(
'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
)
);
$logged_emp_code = $this->session->userdata('logged_emp_code');
$logged_role = $this->session->userdata('logged_role');
$logged_team = $this->session->userdata('logged_team');
if((int)$logged_role === 5){
$fil_qry =' and cw_employees.team in('.$logged_team.')';
}else
if((int)$logged_role === 4){
$fil_qry =' and cw_employees.team in('.$logged_team.')';
}else
if((int)$logged_role === 3){
$fil_qry =' and cw_employees.team in('.$logged_team.')';
}else{
$fil_qry ='';
}
$pro_qry = [];
$pro_qry[] = array("return"=>"detailing_qry","qry"=>'SELECT cw_time_sheet.employee_code,cw_project_and_drawing_master_drawings.prime_project_and_drawing_master_drawings_id as drawing_id,cw_time_sheet.entry_date,cw_project_and_drawing_master.rdd_no,cw_project_and_drawing_master.project_name,cw_uspm.uspm,cw_client.client_name,cw_project_and_drawing_master.received_date,cw_project_and_drawing_master_drawings.drawing_no,cw_project_and_drawing_master_drawings.drawing_description,SUM(cw_tonnage_approval.actual_tonnage) as actual_tonnage,prime_team_id,cw_employees.emp_name as detailer_name,cw_tonnage_approval.team_leader_name,cw_time_sheet_time_line.first_check_minor,cw_time_sheet_time_line.first_check_major,cw_time_sheet_time_line.second_check_major,cw_time_sheet_time_line.second_check_minor,cw_time_sheet_time_line.qa_major,cw_time_sheet_time_line.qa_minor,cw_branch.branch,REPLACE(tons_remark,"=",":") as tons_remark,cw_time_sheet_time_line.project,cw_team_leader.emp_name as tl_name,cw_pm_name.emp_name as pm_name,cw_tonnage_approval.detailer_name as detailer_code 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 left join cw_employees as cw_team_leader on cw_team_leader.employee_code = cw_tonnage_approval.team_leader_name left join cw_employees as cw_pm_name on cw_pm_name.employee_code = cw_tonnage_approval.project_manager_name where cw_tonnage_approval.work_type = 1 and cw_tonnage_approval.trans_status =1 and DATE_FORMAT(`entry_date`, "%m-%Y") = "'.$process_month.'" and cw_project_and_drawing_master.trans_status =1 and cw_time_sheet_time_line.trans_status = 1 and cw_time_sheet.trans_status = 1 '.$fil_qry.''.$fliter_query.' group by cw_time_sheet_time_line.project,cw_tonnage_approval.drawing_no,entry_date order by entry_date');
$pro_qry[] = array("return"=>"team_qry","qry"=>'select prime_team_id,team_name,GROUP_CONCAT(emp_name) as emp_name from cw_team inner join cw_employees on find_in_set(cw_team.prime_team_id,cw_employees.team) where cw_employees.role = 5 and cw_team.trans_status = 1 group by prime_team_id');
$pro_qry[] = array("return"=>"time_qry","qry"=>'select emp_role,cw_time_sheet.employee_code,cw_employees.emp_name as checker_name,cw_time_sheet_time_line.drawing_no,cw_time_sheet_time_line.project,CASE WHEN emp_role = 5 THEN SEC_TO_TIME(SUM(TIME_TO_SEC(discussion))+SUM(TIME_TO_SEC(correction_time))+SUM(TIME_TO_SEC(detailing_time))+SUM(TIME_TO_SEC(study))+SUM(TIME_TO_SEC(rfi))+SUM(TIME_TO_SEC(checking))+SUM(TIME_TO_SEC(other_works))+SUM(TIME_TO_SEC(bar_listing_time))+SUM(TIME_TO_SEC(change_order_time))+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))+SUM(TIME_TO_SEC(aec))+SUM(TIME_TO_SEC(credit))+SUM(TIME_TO_SEC(revision_time))) ELSE "00:00:00" END as detailer_time,CASE WHEN emp_role = 4 THEN SEC_TO_TIME(SUM(TIME_TO_SEC(discussion))+SUM(TIME_TO_SEC(correction_time))+SUM(TIME_TO_SEC(detailing_time))+SUM(TIME_TO_SEC(study))+SUM(TIME_TO_SEC(rfi))+SUM(TIME_TO_SEC(checking))+SUM(TIME_TO_SEC(other_works))+SUM(TIME_TO_SEC(bar_listing_time))+SUM(TIME_TO_SEC(change_order_time))+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))+SUM(TIME_TO_SEC(aec))+SUM(TIME_TO_SEC(credit))+SUM(TIME_TO_SEC(revision_time))) ELSE "00:00:00" END as checker_time 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 WHERE cw_time_sheet_time_line.trans_status = 1 and cw_time_sheet.trans_status =1 and cw_time_sheet_time_line.work_type = 1 and emp_role in(5,4) GROUP by emp_role,cw_time_sheet_time_line.project,cw_time_sheet_time_line.drawing_no,employee_code');
$pro_qry[] = array("return"=>"revision_qry","qry"=>'select cw_employees.emp_name,cw_co_register.co_number,cw_project_and_drawing_master.rdd_no,cw_project_and_drawing_master.project_name,cw_client.client_name,cw_co_register_log.prime_co_register_id,cw_co_register_log.team,cw_co_register_log.entry_date,cw_co_register_log.employee_code,cw_project_and_drawing_master_drawings.drawing_no as drawing_name,cw_co_register_log.drawing_no,SEC_TO_TIME(SUM(TIME_TO_SEC(cw_co_register_log.billable_hours))) as billable_hours from cw_co_register_log inner join cw_co_register on cw_co_register.prime_co_register_id = cw_co_register_log.prime_co_register_id inner join cw_project_and_drawing_master on cw_project_and_drawing_master.prime_project_and_drawing_master_id = cw_co_register_log.rdd_no inner join cw_project_and_drawing_master_drawings on cw_project_and_drawing_master_drawings.prime_project_and_drawing_master_drawings_id = cw_co_register_log.drawing_no inner join cw_client on cw_client.prime_client_id = cw_project_and_drawing_master.client_name inner join cw_employees on cw_employees.employee_code = cw_co_register_log.employee_code where cw_co_register_log.trans_status = 1 and DATE_FORMAT(`entry_date`, "%m-%Y") = "'.$process_month.'" and cw_co_register.trans_status = 1 and cw_project_and_drawing_master.trans_status = 1 and cw_co_register_log.billable_hours >"00:00:00" '.$fil_qry.''.$rev_fliter_query.' group by cw_co_register_log.prime_co_register_id,cw_co_register_log.employee_code,cw_co_register_log.drawing_no');
$pro_qry[] = array("return"=>"revised_qry","qry"=>'select project_manager_name,cw_project_and_drawing_master.rdd_no,cw_uspm.uspm,cw_client.client_name,cw_project_and_drawing_master.project_name,negative_date,cw_project_and_drawing_master_drawings.drawing_no,sum(cw_tonnage_approval.actual_tonnage) as actual_tonnage,sum(increase_tonnage) as increase_tonnage,sum(reduce_tonnage) as reduce_tonnage,sum(revised_tonnage) as revised_tonnage 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_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 inner join cw_tonnage_approval_tonnage_approval_line on cw_tonnage_approval_tonnage_approval_line.prime_tonnage_approval_id = cw_tonnage_approval.prime_tonnage_approval_id where cw_tonnage_approval.work_type = 1 and cw_tonnage_approval.trans_status =1 and DATE_FORMAT(`negative_date`, "%m-%Y") = "'.$process_month.'" and cw_project_and_drawing_master.trans_status =1 and cw_time_sheet_time_line.trans_status = 1 and cw_time_sheet.trans_status = 1 and cw_tonnage_approval_tonnage_approval_line.trans_status = 1 '.$fil_qry.''.$fliter_query.' group by cw_tonnage_approval.drawing_no,negative_date order by negative_date');
$page_info_rslt = $this->run_multi_qry($pro_qry);
$detailing_qry_result = json_decode(json_encode($page_info_rslt->rslt->detailing_qry),true);
$team_emp_name_qry_result = json_decode(json_encode($page_info_rslt->rslt->team_qry),true);
$revision_qry_result = json_decode(json_encode($page_info_rslt->rslt->revision_qry),true);
$tons_rslt = json_decode(json_encode($page_info_rslt->rslt->revised_qry),true);
$time_qry_result = json_decode(json_encode($page_info_rslt->rslt->time_qry),true);
$detailing_result = array();
foreach ($detailing_qry_result as $key => $value) {
$detailing_result[$value['prime_team_id']][] = $value;
}
$time_result = array();
foreach ($time_qry_result as $key => $value) {
$time_result[$value['emp_role']][$value['project']][$value['drawing_no']][$value['employee_code']] = $value;
}
$team_emp_name_result = array();
foreach ($team_emp_name_qry_result as $key => $value) {
$team_emp_name_result[$value['prime_team_id']] = $value;
}
$excel2->getActiveSheet()->setCellValue('A'."1", "US DETAILING PROJECTS - NEW SUBMISSIONS DURING ".strtoupper($month_name)."-".$month_year)->mergeCells('A1:W1')->getStyle('A1:W1')->applyFromArray($header_first);
$i = 3;
foreach ($detailing_result as $team_id => $team_wise_data) {
$team_total = "";
$team_with_emp = $team_emp_name_result[$team_id]['team_name']." >>> ".$team_emp_name_result[$team_id]['emp_name'];
$excel2->getActiveSheet()->setCellValue("A$i", $team_with_emp)->mergeCells("A".$i.":W".$i)->getStyle('A'.$i.':W'.$i)->applyFromArray($teamStyle);
$team_name_arr[] = $i;
$i++;
$total_first_check_minor = 0;
$total_first_check_major = 0;
$total_second_check_major = 0;
$total_qa_major = 0;
$total_qa_minor = 0;
$total_actual_tons = 0;
$no_of_draw = 0;
foreach ($team_wise_data as $team_data){
$project_id = $team_data['project'];
$employee_code = $team_data['employee_code'];
$total_first_check_minor += $team_data['first_check_minor'];
$total_first_check_major += $team_data['first_check_major'];
$total_second_check_major += $team_data['second_check_major'];
$total_qa_major += $team_data['qa_major'];
$total_qa_minor += $team_data['qa_minor'];
$total_actual_tons += $team_data['actual_tonnage'];
$no_of_draw += 1;
$drawing_id = $team_data['drawing_id'];
$entry_date_wise = $team_data['entry_date'];
$team_leader_name = $team_data['team_leader_name'];
$detailer_name = $team_data['detailer_code'];
$received_date = date('d-M-Y',strtotime($team_data['received_date']));
$entry_dates = date('d-M-Y',strtotime($team_data['entry_date']));
if($received_date === '01-01-1970'){
$received_date = "";
}
if($entry_dates === '01-01-1970'){
$entry_dates = "";
}
$drawing_description_replace = $team_data['drawing_description'];
$drawing_description = str_replace("xdbquot",'"',$drawing_description_replace);
$drawing_description = str_replace("xquot","'",$drawing_description);
$drawing_description = str_replace("xxamp","&",$drawing_description);
$checker_role = $time_result[4][$project_id][$drawing_id][$team_leader_name]['emp_role'];
$detailer_role = $time_result[5][$project_id][$drawing_id][$detailer_name]['emp_role'];
$checker_time = $time_result[4][$project_id][$drawing_id][$team_leader_name]['checker_time'];
if(!$checker_time){
$checker_time = "";
}
$detailer_time = $time_result[5][$project_id][$drawing_id][$detailer_name]['detailer_time'];
if(!$detailer_time){
$detailer_time = "";
}
$total_times = array();
$total_times[] = $detailer_time;
$total_times[] = $checker_time;
$total_for_time = $this->AddPlayTime($total_times);
$project_name_rpl = $team_data['project_name'];
$project_name = str_replace("xdbquot",'"',$project_name_rpl);
$project_name = str_replace("xquot","'",$project_name);
$project_name = str_replace("xxamp","&",$project_name);
$receive_date = new DateTime($received_date);
$receive_date = Date::PHPToExcel($receive_date);
$entry_dates = new DateTime($entry_dates);
$entry_dates = Date::PHPToExcel($entry_dates);
$excel2->getActiveSheet()->setCellValue("A$i", $team_data['rdd_no'])->getStyle("A".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("B$i", $team_data['uspm'])->getStyle("B".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("C$i", $team_data['client_name'])->getStyle("C".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("D$i", $project_name)->getStyle("D".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("E$i", $receive_date)->getStyle('E'.$i)->getNumberFormat()->setFormatCode("dd-mmm-yyyy");
$excel2->getActiveSheet()->setCellValue("F$i", $team_data['drawing_no'])->getStyle("F".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("G$i", 1)->getStyle("G".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("H$i", $drawing_description)->getStyle("H".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("I$i", $entry_dates)->getStyle('I'.$i)->getNumberFormat()->setFormatCode("dd-mmm-yyyy");
$excel2->getActiveSheet()->setCellValue("J$i", $team_data['actual_tonnage'])->getStyle("J".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("K$i", $team_data['detailer_name'])->getStyle("K".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("L$i", $detailer_time)->getStyle("L".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("M$i", $team_data['tl_name'])->getStyle("M".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("N$i", $checker_time)->getStyle("N".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("O$i", $total_for_time)->getStyle("O".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("P$i", $team_data['first_check_major'])->getStyle("P".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("Q$i", $team_data['first_check_minor'])->getStyle("Q".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("R$i", $team_data['second_check_major'])->getStyle("R".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("S$i", $team_data['qa_major'])->getStyle("S".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("T$i", $team_data['qa_minor'])->getStyle("T".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("U$i", $team_data['pm_name'])->getStyle("U".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("V$i", $team_data['branch'])->getStyle("V".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("W$i", $team_data['tons_remark'])->getStyle("W".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->getStyle("E".$i.":E".$i)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->getStyle("I".$i.":I".$i)->applyFromArray($verticalStyle);
$i++;
}
/*$excel_columns = range('A','W');
foreach ($excel_columns as $key => $value){//Apply Border for contents
$excel2->getActiveSheet()->getStyle($value."4:$value".$i)->applyFromArray($verticalStyle);
}*/
//$excel2->getActiveSheet()->getStyle("W4:W".$i)->applyFromArray($RightBorder);
//->getStyle('A'.$i)->applyFromArray($verticalStyle)
$excel2->getActiveSheet()->setCellValue("A$i", "")->getStyle('A'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("B$i", "")->getStyle('B'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("C$i", "")->getStyle('C'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("D$i", "")->getStyle('D'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("E$i", "")->getStyle('E'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("F$i", "")->getStyle('F'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("G$i", $no_of_draw)->getStyle('G'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("H$i", "")->getStyle('H'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("I$i", "")->getStyle('I'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("J$i", $total_actual_tons)->getStyle('J'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("K$i", "")->getStyle('K'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("L$i", "")->getStyle('L'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("M$i", "")->getStyle('M'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("N$i", "")->getStyle('N'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("O$i", "")->getStyle('O'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("P$i", $total_first_check_major)->getStyle('P'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("Q$i", $total_first_check_minor)->getStyle('Q'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("R$i", $total_second_check_major)->getStyle('R'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("S$i", $total_qa_major)->getStyle('S'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("T$i", $total_qa_minor)->getStyle('T'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("U$i", "")->getStyle('U'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("V$i", "")->getStyle('V'.$i)->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("W$i", "")->getStyle('W'.$i)->applyFromArray($FooterStyle);
$i = $i+1;
}
$z = $i-1; //right end border
$excel2->getActiveSheet()->getStyle('W2:W'.$z)->applyFromArray($RightBorder);
// $detiling_sheet_count = count($time_sheet_inside);
$excel_sheet = $excel2->setActiveSheetIndex(0);
$revision_result = array();
foreach ($revision_qry_result as $key => $value) {
$revision_result[$value['team']][] = $value;
}
$excel2->getActiveSheet(1)->setCellValue('A'."1", "US DETAILING PROJECTS - REVISIONS DURING ".strtoupper($month_name)."-".$month_year)->mergeCells('A1:H1')->getStyle('A1:H1')->applyFromArray($header_first);
$m=3;
//print_r($revision_result); die;
foreach ($revision_result as $team_id => $revisionData) {
$rev_team = $team_emp_name_result[$team_id]['team_name']." >>> ".$team_emp_name_result[$team_id]['emp_name'];
$excel2->getActiveSheet()->setCellValue("A$m", $rev_team)->mergeCells("A".$m.":H".$m)->getStyle("A".$m.":H".$m)->applyFromArray($teamStyle);
$team_name_rev[] = $m;
$counter_rev = $m;
$m++;
$total_billable_hrs = array();
foreach($revisionData as $revData){
$total_billable_hrs[] = $revData['billable_hours'];
$total_hours_billable = $this->AddPlayTime($total_billable_hrs);
$project_name_rpl = $revData['project_name'];
$project_name = str_replace("xdbquot",'"',$project_name_rpl);
$project_name = str_replace("xquot","'",$project_name);
$project_name = str_replace("xxamp","&",$project_name);
$entry_dates = date('d-M-Y',strtotime($revData['entry_date']));
$entry_dates = new DateTime($entry_dates);
$entry_dates = Date::PHPToExcel($entry_dates);
$excel2->getActiveSheet()->setCellValue("A$m", $revData['co_number'])->getStyle("A".$m)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("B$m", $revData['rdd_no'])->getStyle("B".$m)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("C$m", $revData['client_name'])->getStyle("C".$m)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("D$m", $project_name)->getStyle("D".$m)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("E$m", $revData['drawing_name'])->getStyle("E".$m)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("F$m", $entry_dates)->getStyle('F'.$m)->getNumberFormat()->setFormatCode("dd-mmm-yyyy");
$excel2->getActiveSheet()->setCellValue("G$m", $revData['billable_hours'])->getStyle("G".$m)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("H$m", $revData['emp_name'])->getStyle("H".$m)->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->getStyle("F".$m.":F".$m)->applyFromArray($verticalStyle);
$counter_rev++;
$m++;
}
$team_total = $counter_rev+1;
$excel2->getActiveSheet()->setCellValue("A$m", "")->getStyle("A$m")->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("B$m", "")->getStyle("B$m")->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("C$m", "")->getStyle("C$m")->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("D$m", "")->getStyle("D$m")->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("E$m", "")->getStyle("E$m")->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("F$m", "TOTAl")->getStyle("F$m")->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("G$m", $total_hours_billable)->getStyle("G$m")->applyFromArray($FooterStyle);
$excel2->getActiveSheet()->setCellValue("H$m", "")->getStyle("H$m")->applyFromArray($FooterStyle);
$m++;
}
/*$excel_columns = range('A','H');
foreach ($excel_columns as $key => $value){//Apply Border for contents
$excel2->getActiveSheet()->getStyle($value."4:$value".$m)->applyFromArray($verticalStyle);
}*/
$excel2->getActiveSheet()->getStyle("H1:H".$m)->applyFromArray($RightBorder);
// $excel_sheet = $excel2->setActiveSheetIndex(1);
$tons = 2;
foreach ($tons_rslt as $key => $value) {
$project_manager_name = $value['project_manager_name'];
$project_name_rpl = $value['project_name'];
$project_name = str_replace("xdbquot",'"',$project_name_rpl);
$project_name = str_replace("xquot","'",$project_name);
$project_name = str_replace("xxamp","&",$project_name);
$negative_date = date('d-M-Y',strtotime($value['negative_date']));
$negative_date = new DateTime($negative_date);
$negative_date = Date::PHPToExcel($negative_date);
$excel2->getActiveSheet()->setCellValue("A$tons", $value['rdd_no'])->getStyle("A$tons")->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("B$tons", $value['uspm'])->getStyle("B$tons")->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("C$tons", $value['client_name'])->getStyle("C$tons")->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("D$tons", $project_name)->getStyle("D$tons")->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("E$tons", $negative_date)->getStyle('E'.$tons)->getNumberFormat()->setFormatCode("dd-mmm-yyyy");
$excel2->getActiveSheet()->setCellValue("F$tons", $value['drawing_no'])->getStyle("F$tons")->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("G$tons", $value['actual_tonnage'])->getStyle("G$tons")->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("H$tons", $value['increase_tonnage'])->getStyle("H$tons")->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("I$tons", $value['reduce_tonnage'])->getStyle("I$tons")->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("J$tons", $value['revised_tonnage'])->getStyle("J$tons")->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->setCellValue("K$tons", $value['rev_pm_name'])->getStyle("K$tons")->applyFromArray($verticalStyle);
$excel2->getActiveSheet()->getStyle("E".$tons.":E".$tons)->applyFromArray($verticalStyle);
$tons++;
}
/*$excel_columns = range('A','K');
foreach ($excel_columns as $key => $value){//Apply Border for contents
$excel2->getActiveSheet()->getStyle($value."4:$value".$tons)->applyFromArray($verticalStyle);
}*/
// $ton_val_count = count($tons_val);
$excel_sheet = $excel2->setActiveSheetIndex(0);
$objWriter = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($excel2);
ob_start();
$objWriter->save("php://output");
$xlsData = ob_get_contents();
ob_end_clean();
$response = array(
'success' => true,
'op' => 'ok',
'file' => "data:application/vnd.ms-excel;base64,".base64_encode($xlsData)
);
die(json_encode($response));
}
public function AddPlayTime($times) {
$minutes = 0; //declare minutes either it gives Notice: Undefined variable
// loop throught all the times
foreach ($times as $time) {
list($hour, $minute) = explode(':', $time);
$minutes += (int)$hour * 60;
$minutes += (int)$minute;
}
$hours = floor((int)$minutes / 60);
$minutes -= (int)$hours * 60;
// returns the time already formatted
return sprintf('%02d:%02d', $hours, $minutes);
}
}
?>