File: /home/cafsindia/cpaqua.cafsinfotech.in/dump/application/controllers/Project_report.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
include_once('./application/libraries/xlsxwriter.class.php');
require_once("Action_controller.php");
class Project_report extends Action_controller{
public function __construct(){
parent::__construct('project_report');
$this->collect_base_info();
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
//ESITMATION USED FOR
$est_qry = 'SELECT prime_est_used_for,est_used_for FROM cw_est_used_for WHERE trans_status = 1 ';
$est_info = $this->db->query("CALL sp_a_run ('SELECT','$est_qry')");
$est_rslt = $est_info->result();
$est_info->next_result();
$est_list[""] = "---- EST USED ----";
foreach($est_rslt as $for){
$prime_id = $for->prime_est_used_for;
$est_status = $for->est_used_for;
$est_list[$prime_id] = $est_status;
}
//BID STATUS
$bid_qry = 'SELECT prime_bid_status_id,bid_status FROM cw_bid_status WHERE trans_status = 1 ';
$bid_info = $this->db->query("CALL sp_a_run ('SELECT','$bid_qry')");
$bid_rslt = $bid_info->result();
$bid_info->next_result();
$bid_list[""] = "---- BID STATUS ----";
foreach($bid_rslt as $for){
$prime_id = $for->prime_bid_status_id;
$bid_status = $for->bid_status;
$bid_list[$prime_id] = $bid_status;
}
$data['bid_sts'] = $bid_list;
$data['est_used_for'] = $est_list;
$data['enckey'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
// REA AUTOCOMPLETE
public function rea_suggest_name(){
$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);
}
$search_term = $this->input->post_get('term');
$final_qry = 'SELECT rea,project_name FROM cw_project_list WHERE trans_status = 1 AND rea like "%'.$search_term.'%"';
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
foreach($final_result as $rslt){
$rea = $rslt->rea;
$pro = $rslt->project_name;
$suggestions[] = array('value' => "$rea", 'label' =>"$rea - $pro");
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
// STATE AUTOCOMPLETE
public function state_suggest_name(){
$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);
}
$search_term = $this->input->post_get('term');
$final_qry = 'SELECT prime_state_list_id,state_name FROM cw_state_list WHERE trans_status = 1 AND state_name like "%'.$search_term.'%"';
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
foreach($final_result as $rslt){
$state = $rslt->state_name;
$state_id = $rslt->prime_state_list_id;
$suggestions[] = array('value' => "$state", 'label' =>"$state",'id' => "$state_id");
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search",'id' => "");
}
echo json_encode($suggestions);
}
// CITY AUTOCOMPLETE
public function city_suggest_name(){
$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);
}
$search_term = $this->input->post_get('term');
$final_qry = 'SELECT prime_city_list_id,city_name FROM cw_city_list WHERE trans_status = 1 AND city_name like "%'.$search_term.'%"';
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
foreach($final_result as $rslt){
$city = $rslt->city_name;
$city_id = $rslt->prime_city_list_id;
$suggestions[] = array('value' => "$city", 'label' =>"$city",'id' => "$city_id");
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search",'id' => "");
}
echo json_encode($suggestions);
}
//CHECKING FOLLOWING FILTER AS DATA
public function project_details(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$employee_code = $this->input->post('employee_code');
$from_month = date('Y-m-d',strtotime('01-'.$this->input->post('from_month')));
$to_month = date('Y-m-d',strtotime('01-'.$this->input->post('to_month')));
$rea = $this->input->post('rea');
$hidState = $this->input->post('hidState');
$hidCity = $this->input->post('hidCity');
$bid_received_date = date('Y-m-d',strtotime($this->input->post('bid_received_date')));
$bid_date = date('Y-m-d',strtotime($this->input->post('bid_date')));
$sub_date = date('Y-m-d',strtotime($this->input->post('sub_date')));
$bid_status = $this->input->post('bid_status');
$est_used = $this->input->post('est_used');
# FILTER
$filter_query = "";
if($rea){
$filter_query .= 'AND rea = "'.$rea.'"';
}
if($hidState){
$filter_query .= 'AND state = "'.$hidState.'"';
}
if($hidCity){
$filter_query .= 'AND city = "'.$hidCity.'"';
}
if($bid_received_date !== "1970-01-01"){
$filter_query .= 'AND bid_received_date = "'.$bid_received_date.'"';
}
if($bid_date !== "1970-01-01"){
$filter_query .= 'AND bid_date = "'.$bid_date.'"';
}
if($sub_date !== "1970-01-01"){
$filter_query .= 'AND submitted_date = "'.$sub_date.'"';
}
if($bid_status){
$filter_query .= 'AND bid_status = "'.$bid_status.'"';
}
if($est_used){
$filter_query .= 'AND est_used = "'.$est_used.'"';
}
# PROJECT LIST
$pro_list_qry = 'SELECT count(*) as data FROM cw_project_list WHERE trans_status = 1 '.$filter_query.'';
#AND DATE_FORMAT(entry_date,"%Y-%m-%d") BETWEEN "'.$from_month.'" AND "'.$to_month.'"
$pro_list_info = $this->db->query("CALL sp_a_run ('SELECT','$pro_list_qry')");
$pro_list_rslt = $pro_list_info->result();
$pro_list_info->next_result();
$rslt = $pro_list_rslt[0]->data;
if($rslt > 0){
echo json_encode(array('success' => TRUE,'message' => 'Generating Excel!'));
}else{
echo json_encode(array('success' => FALSE,'message' => 'No Data Available!'));
}
}
//EXCEL GENERATE FOR ESTIMATOR
public function generate_report($encData){
$_POST = $this->cryptoDecrypt(base64_decode(urldecode($encData)));
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$from_date = date('Y-m-d',strtotime($this->input->post('from_date')));
$to_date = date('Y-m-d',strtotime($this->input->post('to_date')));
$rea = $this->input->post('rea');
$state = $this->input->post('hidState');
$city = $this->input->post('hidCity');
$bid_date = date('Y-m-d',strtotime($this->input->post('bid_date')));
$sub_date = date('Y-m-d',strtotime($this->input->post('sub_date')));
$bid_status = $this->input->post('bid_status');
$est_used = $this->input->post('est_used');
$bid_received_date = date('Y-m-d',strtotime($this->input->post('bid_received_date')));
$report_from = date('M y', strtotime($from_date));
$report_to = date('M y', strtotime($to_date));
$report_taken = $report_from." to ".$report_to;
# FILTER
$filter_query = "";
if($rea){
$filter_query .= 'AND rea = "'.$rea.'"';
}
if($state){
$filter_query .= 'AND state = "'.$state.'"';
}
if($city){
$filter_query .= 'AND city = "'.$city.'"';
}
if($bid_received_date !== "1970-01-01" && $bid_received_date !== ""){
$filter_query .= 'AND bid_received_date = "'.$bid_received_date.'"';
}
if($sub_date !== "1970-01-01" && $bid_date !== ""){
$filter_query .= 'AND bid_date = "'.$bid_date.'"';
}
if($sub_date !== "1970-01-01" && $sub_date !== ""){
$filter_query .= 'AND submitted_date = "'.$sub_date.'"';
}
if($bid_status){
$filter_query .= 'AND bid_status = "'.$bid_status.'"';
}
if($est_used){
$filter_query .= 'AND est_used = "'.$est_used.'"';
}
# EXCEL GENERATE STARTS
$writer = new XLSXWriter();
# 1ST ROW
$writer->writeSheetHeader('Sheet1',array("$report_taken - US ESTIMATION PROJECTS" => "string"),['widths'=>[10,20,15,25,9,9,9,9,9,9,9,9,9,9,9],'font'=>'Calibri','font-size'=>9,'halign' => 'center','font-style' => 'bold']);
$writer->markMergedCell('Sheet1', 0, 0, 0, 14);
# 3ND ROW
$header = array("EST NO" => "string","PROJECT NAME" => "string","STATE,CITY" => "string","CLIENT" => "string","BID RECEIVED DATE" => "DD/MM/YYYY","ACTUAL DATE" => "DD/MM/YYYY","SUBMITTED DATE" => "DD/MM/YYYY","BILLABLE TONNAGE" => "integer","BILLABLE CO HRS" => "0.00","BID STATUS" => "string","EST USED" => "string","TEAM LEADER" =>"string","WORKING HOURS" => "0.00","TON/HR" => "0.00","MONTH" => "string");
$writer->writeSheetHeader('Sheet1',$header,['wrap_text' => true,'font'=>'Calibri','font-size'=>9,'font-style'=>'bold', 'fill'=>'#00b050', 'halign'=>'center', 'border'=>'left,right,top,bottom','border-style' => 'thin','border-color' => '#000000']);
# MULTI QRY FOR RETRIEVE DATA
$pro_list_qry = 'SELECT rea,project_name,state,city,client_name,bid_received_date,bid_date,submitted_date,SUM(line.billable_ton) AS billable_ton,SUM(line.billable_hour) AS billable_hour,bid_status,est_used,TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(row_total_time))),"%H:%i:%s") AS working_hour,emp_name as team_leader FROM cw_project_list INNER JOIN cw_employees on cw_employees.employee_code = cw_project_list.master_checker INNER JOIN cw_est_time_sheet_est_time_line AS line ON line.project = cw_project_list.prime_project_list_id WHERE cw_project_list.trans_status = 1 AND cw_employees.trans_status = 1 AND line.trans_status = 1 AND DATE_FORMAT(bid_received_date,"%Y-%m-%d") BETWEEN "'.$from_date.'" AND "'.$to_date.'" '.$filter_query.' GROUP BY prime_project_list_id';
$state_list_qry = 'SELECT prime_state_list_id,state_name FROM cw_state_list WHERE trans_status = 1';
$city_list_qry = 'SELECT prime_city_list_id,city_name FROM cw_city_list WHERE trans_status = 1';
$est_list_qry = 'SELECT prime_est_used_for,est_used_for FROM cw_est_used_for WHERE trans_status = 1';
$bid_list_qry = 'SELECT prime_bid_status_id,bid_status FROM cw_bid_status WHERE trans_status = 1';
$mon_list_qry = 'SELECT month_year,start_date,end_date FROM cw_month_cycle WHERE trans_status = 1';
$search_pro_qry = [];
$search_pro_qry[] = array("return"=>"pro_list_rslt","qry"=>$pro_list_qry);
$search_pro_qry[] = array("return"=>"state_list_rslt","qry"=>$state_list_qry);
$search_pro_qry[] = array("return"=>"city_list_rslt","qry"=>$city_list_qry);
$search_pro_qry[] = array("return"=>"est_list_rslt","qry"=>$est_list_qry);
$search_pro_qry[] = array("return"=>"bid_list_rslt","qry"=>$bid_list_qry);
$search_pro_qry[] = array("return"=>"mon_list_rslt","qry"=>$mon_list_qry);
$search_info_rslt = $this->run_multi_qry($search_pro_qry);
$project_rslt = $search_info_rslt->rslt->pro_list_rslt;
$state_rslt = $search_info_rslt->rslt->state_list_rslt;
$city_rslt = $search_info_rslt->rslt->city_list_rslt;
$est_rslt = $search_info_rslt->rslt->est_list_rslt;
$bid_rslt = $search_info_rslt->rslt->bid_list_rslt;
$mon_rslt = $search_info_rslt->rslt->mon_list_rslt;
# STATE LIST
$state_array = [];
foreach($state_rslt as $value){
$state_array[$value->prime_state_list_id] = $value->state_name;
}
# CITY LIST
$city_array = [];
foreach($city_rslt as $value){
$city_array[$value->prime_city_list_id] = $value->city_name;
}
# EST LIST
$est_array = [];
foreach($est_rslt as $value){
$est_array[$value->prime_est_used_for] = $value->est_used_for;
}
# BID LIST
$bid_array = [];
foreach($bid_rslt as $value){
$bid_array[$value->prime_bid_status_id] = $value->bid_status;
}
# MONTH LIST
$mon_array = [];
foreach($mon_rslt as $value){
$mon_array[$value->month_year] = $value;
}
# MONTH CYCLE
$month_arr = array();
$mon_array_count = 0;
$sub_month_arr = [];
foreach($mon_array as $month => $value){
$sub_month_arr[$value->start_date][$value->end_date] = $value->month_year;
$fromDate = strtotime("01-" . date('m-Y', strtotime($from_date)));
$toDate = strtotime("01-" . date('m-Y', strtotime($to_date)));
$mon_year = strtotime("01-" . $month);
if($mon_year >= $fromDate && $mon_year <= $toDate){
$start_date = $value->start_date;
$end_date = $value->end_date;
if($start_date && $end_date){
$month_arr[] = [
'month' => $month,
'start_date' => $start_date,
'end_date' => $end_date
];
$mon_array_count++;
}
}
}
# REPLACE FIRST START AND LAST END DATE
if($mon_array_count > 0){
$start_date = date('Y-m-d', strtotime($from_date));
$end_date = date('Y-m-d', strtotime($to_date));
$month_arr[0]['start_date'] = $start_date;
$month_arr[$mon_array_count - 1]['end_date'] = $end_date;
}
# GROUP PROJECT DATA BASED ON MONTH WISE
$projectData = [];
$tot_pro_arr = [];
$i = 0;
foreach($month_arr as $mon_year){
$month = $mon_year['month'];
$start_month = strtotime($mon_year['start_date']);
$end_month = strtotime($mon_year['end_date']);
foreach($project_rslt as $pro){
$bid_rec_date = strtotime($pro->bid_received_date);
$bid_date = $pro->bid_date === '1970-01-01' ? '' : $pro->bid_date;
$sub_date = $pro->sub_date === '1970-01-01' ? '' : $pro->submitted_date;
if($sub_date){
$sub_month_key = $this->getmonth($mon_array, $sub_date);
$submonth = date('M y ',strtotime("01-".$this->getmonth($mon_array,$sub_date)));
$bill_ton = $pro->billable_ton;
$bill_hr = $pro->billable_hour;
$work_hr = $this->decimalHours($pro->working_hour);
# SUBMITTED DATE WISE TOTAL CALCULATE
if($i === 0){
$tot_pro_arr[$sub_month_key]['total_bill_ton'] += $bill_ton;
$tot_pro_arr[$sub_month_key]['total_bill_hr'] += $bill_hr;
$tot_pro_arr[$sub_month_key]['total_work_hr'] += $work_hr;
}
}else{
$submonth = '';
$bill_ton = '';
$bill_hr = '';
$work_hr = '';
}
if((int)$this->decimalHours(date('H:i:s',strtotime($work_hr))) !== 0){
$ton_hr = $this->normalizeValue((float)$bill_ton / $this->decimalHours(date('H:i:s',strtotime($work_hr))));
}else{
$ton_hr = 0;
}
# BID RECEIVE DATE WISE DATA DISPLAY
if($bid_rec_date >= $start_month && $bid_rec_date <= $end_month){
$projectData[$month][] = array(
'rea' => $pro->rea,
'project' => $pro->project_name,
'state_city' => $state_array[$pro->state]." - ".$city_array[$pro->city],
'client' => $pro->client_name,
'bid_rec_date' => $pro->bid_received_date,
'bid_date' => $bid_date,
'sub_date' => $sub_date,
'bill_ton' => $bill_ton,
'bill_hr' => $bill_hr,
'bid_sts' => $bid_array[$pro->bid_status],
'est_used' => $est_array[$pro->est_used],
'tl' => $pro->team_leader,
'work_hr' => $work_hr,
'ton_hr' => $ton_hr,
'month' => $submonth,
'frm' => $mon_year['start_date'],
'to' => $mon_year['end_date']
);
}
}
$i++;
}
# TOTAL COUNT OF PROJECT DATA MONTH WISE DISPLAY
foreach($projectData as $month => $pro){
$writer->writeSheetRow('Sheet1',['']);
$start_frm = '';
$end_to = '';
$tot_bill_ton = 0;
$tot_bill_hr = 0;
$tot_wrk_hr = 0;
$total_ton_hr = 0;
$total_pro_count = 0 ;
foreach($pro as $key => $val){
$start_frm = $val['frm'];
$end_to = $val['to'];
unset($val['frm']);
unset($val['to']);
if($val['rea']){
$total_pro_count++;
}
$writer->writeSheetRow('Sheet1', $val, array('wrap_text'=> true,'halign' => 'center','font' => 'Calibri','font-size' => 10,'border'=>'left,right,top,bottom','border-style' => 'dotted','border-color' => '#000000'));
}
$writer->writeSheetRow('Sheet1',['']);
if($sub_month_arr[$start_frm][$end_to]){
$submonth = $sub_month_arr[$start_frm][$end_to];
$tot_bill_ton = $tot_pro_arr[$submonth]['total_bill_ton']?$tot_pro_arr[$submonth]['total_bill_ton']:'0';
$tot_bill_hr = $tot_pro_arr[$submonth]['total_bill_hr']?$tot_pro_arr[$submonth]['total_bill_hr']:'0';
$tot_wrk_hr = $tot_pro_arr[$submonth]['total_work_hr']?$tot_pro_arr[$submonth]['total_work_hr']:'0';
if((int)$tot_wrk_hr !== 0){
$total_ton_hr = $this->normalizeValue($tot_bill_ton / $tot_wrk_hr);;
}else{
$total_ton_hr = 0;
}
}
$frm = date('d F y',strtotime($start_frm));
$to = date('d F y',strtotime($end_to));
$writer->writeSheetRow('Sheet1',[$total_pro_count,'Number of Work taken','',$frm.' to '.$to.' Total :','','','',$tot_bill_ton,$tot_bill_hr,'','','',$tot_wrk_hr,$total_ton_hr,''], array('fill' => '#d8e4bc','halign' => 'center','font' => 'Calibri','font-size' => 10,'border'=>'left,right,top,bottom','border-style' => 'thin','border-color' => '#000000','font-style' => 'bold'));
}
# GENERATE THE EXCEL FILE
$filename = 'excel_download/projectReport.xlsx';
$writer->writeToFile($filename);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="'.$filename.'"');
readfile($filename);
}
//RETRIEVE MONTH BASED ON SUB DATE
public function getmonth($mon_array,$sub_date){
$sub_date = strtotime($sub_date);
foreach($mon_array as $mon){
$start_date = strtotime($mon->start_date);
$end_date = strtotime($mon->end_date);
if($sub_date >= $start_date && $sub_date <= $end_date){
return $mon->month_year;
}
}
}
//INF / NAN REPLACE ZERO
public function normalizeValue($value){
$value = (float)$value;
if(is_nan($value) || is_infinite($value) || $value === "inf" || $value === "nan"){
return 0;
}
return $value;
}
//CONVERT TIME TO DECIMAL
public function decimalHours($time){
$hms = explode(":", $time);
return round((int)$hms[0] + (int)($hms[1]/60) + (int)($hms[2]/3600),2);
}
}
?>