<?php
require_once (PATH_TABLES . "activity/ActivityHeaderTable.php") ;
require_once (PATH_TABLES . "hr/DepartmentInterPermissionTable.php") ;
require_once (PATH_MODELS . "base/ActivityMasterBase.php") ;
require_once (PATH_MODELS . "admin/UserAbsentHistoryClass.php");
require_once (PATH_MODELS . "admin/UserClass.php");
require_once (PATH_MODELS . "hr/DepartmentClass.php");
require_once (PATH_MODELS . "activity/JobCentreClass.php");
require_once (PATH_MODELS . "activity/CustomerClass.php");
require_once (PATH_MODELS . "activity/CustomerContactClass.php");
require_once (PATH_MODELS . "activity/ActivityEmailApprovalClass.php");
class ActivityHeaderClass extends ActivityMasterBase {

	function __construct($db) {
		$this->db = $db ;
		$this->tbl = ActivityHeaderTable::C_TABLE ;
		$this->fldid = ActivityHeaderTable::C_ID ;
		$this->flddesc = ActivityHeaderTable::C_TITLE ;
		$this->fldorg = ActivityHeaderTable::C_ORG_ID ;
	}
	
	function __destruct() {
	}
	public function getTotalUnaprovedReport($jobCentreId, $line){
		$sql = "SELECT  H.".ActivityHeaderTable::C_ID." AS TOTAL FROM " . ActivityHeaderTable::C_TABLE . " H ";
		$sql .= " LEFT OUTER JOIN ".ActivityTimingDetailTable::C_TABLE." D ";
		$sql .= " ON (D.".ActivityTimingDetailTable::C_ID." = H.".ActivityHeaderTable::C_ID.") ";
		$sql .= " WHERE H." . ActivityHeaderTable::C_JOB_ID . " = " . $jobCentreId . " ";
		$sql .= " AND D." . ActivityTimingDetailTable::C_ASSIGN_ID . " = " . $line . " ";
		$sql .= " AND H." . ActivityHeaderTable::C_SIGNED . " != 1 ";
		$sql .=  " GROUP BY H.".ActivityHeaderTable::C_ID."";
		$row = $this->db->getTable($sql);
		
		$mandays = 0;
		if(!is_null($row) && count($row)>0){
			$mandays = count($row);
		}
		
        return $mandays;
	}
	public function getBillingDaysPerPhase($jobCentreId, $line){
		$sql = "SELECT SUM(D.".ActivityTimingDetailTable::C_TOTAL_OVERRIDE.") AS TOTAL FROM " . ActivityHeaderTable::C_TABLE . " H ";
		$sql .= " LEFT OUTER JOIN ".ActivityTimingDetailTable::C_TABLE." D ";
		$sql .= " ON (D.".ActivityTimingDetailTable::C_ID." = H.".ActivityHeaderTable::C_ID.") ";
		$sql .= " WHERE H." . ActivityHeaderTable::C_JOB_ID . " = " . $jobCentreId . " ";
		$sql .= " AND D." . ActivityTimingDetailTable::C_ASSIGN_ID . " = " . $line . " ";
		$sql .= " AND H." . ActivityHeaderTable::C_BILL . " <> 2 ";
       
		$row = $this->db->getTable($sql);
		
		$mandays = 0;
		if(!is_null($row) && count($row)>0){
			$mandays = floatval($row[0]['TOTAL']);
		}
        return $mandays;
	}
	
	public function getJobCentreServiceReport($id) {
		$sql = "SELECT * FROM " . ActivityHeaderTable::C_TABLE . " WHERE " . ActivityHeaderTable::C_JOB_ID . " = " . $id . " ";
        
        $rows = $this->db->getTable($sql);
        
        if(!is_null($rows) && count($rows) > 0){
            return $rows;
        }
        
        return null;
	}

    public function getActivityList($userID , $fromDate="", $toDate="", $top = 0,  $orgID = "", $status = "0", $orderby='') {
		if(!$userID)
			return array();
        
		$cls 		= new ActivityHeaderClass($this->db) ;
		$userCls 	= new UserClass($this->db);
		$JobCls 	= new JobCentreClass($this->db);
		$CustCls 	= new CustomerClass($this->db);
		$CustContactCls 	= new CustomerContactClass($this->db);
        //$statusCls 	= new ActivityStatusClass($this->db);
		$clsEmail    = new ActivityEmailApprovalClass($this->db); 

		$datas 	= array() ;
		$filter = "";
		$filter = $this->db->fieldParam(ActivityHeaderTable::C_ORG_ID);
		if ($orgID == "")
			$datas[]= $this->db->valueParam(ActivityHeaderTable::C_ORG_ID,$_SESSION[SE_ORGID]) ;
		else
			$datas[]= $this->db->valueParam(ActivityHeaderTable::C_ORG_ID,$orgID) ;
	
		if(trim($fromDate," ") != "" && trim($toDate," ") != "") {
			$fromDateFormat = DateTime::createFromFormat('d/m/Y', $fromDate)->format('Y-m-d');
			$toDateFormat 	= DateTime::createFromFormat('d/m/Y', $toDate)->format('Y-m-d');
			$filter 	   .= " and " . $this->db->btwAndFieldParam(ActivityHeaderTable::C_DATE,
					$fromDateFormat, $toDateFormat);
        } else {
        }

	
		$listUserId = array();
		$listUserId[] = $userID;
		$filter    .= " and " . $this->db->inStmFieldParam(ActivityHeaderTable::C_CREATE_BY,$listUserId);


		if($status != "0"){
			if($status == "1"){
				$filter .= " and (" . ActivityHeaderTable::C_STATUS . " = " . ActStatus::Draft . ")";
			} else if($status == "2"){
				$filter .= " and (" . ActivityHeaderTable::C_STATUS . " = " . ActStatus::Confirm . ")";
			}
		}
		//Log::write($filter);
		$NUM_CLAIM_SHOW = 5;
		if($top >0) {
			if($orderby != "")
				$rows = $cls->getTableTop($filter,$orderby,$datas,$NUM_CLAIM_SHOW) ;
			else
				$rows = $cls->getTableTop($filter,ActivityHeaderTable::C_CREATE_DATE,$datas,$NUM_CLAIM_SHOW) ;
		} else {
			if($orderby != "")
				$rows = $cls->getTableTop($filter,$orderby,$datas) ;
			else
				$rows = $cls->getTableTop($filter,ActivityHeaderTable::C_CREATE_DATE,$datas) ;
		}

		$list = array() ;

		foreach ($rows as $row) {
			$data = array() ;

			if ($row[ActivityHeaderTable::C_SITE]==1) {
				$data['activity_deduct']='On Site';
			} else if ($row[ActivityHeaderTable::C_SITE]==2){
				$data['activity_deduct']='Off Site';
			}else{
				$data['activity_deduct']='Off Site';
			}

			$data['id'] 			= $row[ActivityHeaderTable::C_ID];
			$data['code'] 			= $row[ActivityHeaderTable::C_CODE];
			$data['title'] 			= $row[ActivityHeaderTable::C_TITLE];
			$dte 					= date_create($row[ActivityHeaderTable::C_DATE]);
			$data['date']			= date_format($dte, 'd/m/Y') ;
			$data['desc']			= $row[ActivityHeaderTable::C_DESC];
			//$data['activity_type']	= $typeCls->getRecord($row[ActivityHeaderTable::C_TYPE])[ActivityTypeTable::C_DESC];
			$data['bill']			= $row[ActivityHeaderTable::C_BILL];
			$data['site']			= $row[ActivityHeaderTable::C_SITE];
			$data['jobnumber']		= $JobCls->getRecord($row[ActivityHeaderTable::C_JOB_ID])[JobCentreTable::C_JOB_NUMBER];
			//$data['status']		= $statusCls->getRecord($row[ActivityHeaderTable::C_STATUS])[ActivityStatusTable::C_DESC];

			$rowEmailStatus = $clsEmail->getRecord($row[ActivityHeaderTable::C_ID])[ActivityEmailApprovalTable::C_REMINDER_NO];
			$rowEmailStatus = (!is_null($rowEmailStatus) ? $rowEmailStatus : 0);

			$customer_rep = $CustContactCls->getRecord($row[ActivityHeaderTable::C_CONTACT_ID])[CustomerContactTable::C_NAME];

			$status = "";
			if($row[ActivityHeaderTable::C_SIGNED] == ServiceStatus::Draft) {
				$status = "<strong style=\"color:blue\">Draft</strong>";
			} else if($row[ActivityHeaderTable::C_SIGNED] == ServiceStatus::Approved) {
				if( $row[ActivityHeaderTable::C_AUTOMATED_SIGNED] == ServiceStatus::Approved){

					$status = "<strong style=\"color:green\">System Approved</strong>";
				}else{
					$status = "<strong style=\"color:green\">Approved by ".$customer_rep."</strong>";
				}
				
			} else if($row[ActivityHeaderTable::C_SIGNED] == ServiceStatus::Waiting) {

				if($rowEmailStatus == 2){
					$status = "<strong class=\"text-warning\">Waiting for ".$customer_rep." to Approve - Sent Reminder</strong>";
				}else if($rowEmailStatus == 3){
					$status = "<strong class=\"text-warning\">Waiting for ".$customer_rep." to Approve - Sent Final Reminder</strong>";
				}else{
					$status = "<strong class=\"text-warning\">Waiting for ".$customer_rep." to Approve</strong>";
				}
				
			}

			$data['status_lbl']		= $status;
			$data['signed_label']		= $row[ActivityHeaderTable::C_SIGNED_REMARKS];
			$data['status']		    = $row[ActivityHeaderTable::C_SIGNED];
			$data['project_id']		= $row[ActivityHeaderTable::C_PROJECT_ID];
			$data['customer_id']	= $row[ActivityHeaderTable::C_CUSTOMER_ID];
			$data['cust_rep']		= $row[ActivityHeaderTable::C_CONTACT_ID];
			$row_cust				= $CustCls->getRecord($row[ActivityHeaderTable::C_CUSTOMER_ID]);

			$data['customer_name']  = "";
			if(!is_null($row_cust) && count($row_cust)>0){
				$data['customer_name'] = $row_cust[CustomerTable::C_CODE].' - '.$row_cust[CustomerTable::C_NAME];
			}
			
			$data['location_id']	= $row[ActivityHeaderTable::C_LOCATION_ID];
			$data['create_by']		= $userCls->getRecord($row[ActivityHeaderTable::C_USER_ID])[UserTable::C_FULL_NAME];
            $data['signed']         = $row[ActivityHeaderTable::C_SIGNED];
			
			$list[] = $data;
			unset($data);
		}
	
		unset($rows) ;
		unset($cls) ;
		return $list;
	}


	public function getActivityApi($userID){
		$sql = "select * from " . $this->tbl;
		$datas = array();
		$rows = $this->db->getTable($sql) ;
		$userCls 	= new UserClass($this->db);

		if (is_null($rows) || count($rows) == 0)
			return null ;
		else 
			foreach ($rows as $row) {
                    $item = array() ;
                    $item[ActivityHeaderTable::C_ID] = $row[ActivityHeaderTable::C_ID];
                    $item[ActivityHeaderTable::C_TITLE] = $row[ActivityHeaderTable::C_TITLE];
                    $item[ActivityHeaderTable::C_DESC] = $row[ActivityHeaderTable::C_DESC];
                    $item[ActivityHeaderTable::C_STATUS]= $row[ActivityHeaderTable::C_STATUS];
                    $item["USERNAME"]= $userCls->getRecord($row[ActivityHeaderTable::C_USER_ID])[UserTable::C_FULL_NAME];
                    $item[ActivityHeaderTable::C_CODE]= $row[ActivityHeaderTable::C_CODE];
                    $item[ActivityHeaderTable::C_JOB_ID]= $row[ActivityHeaderTable::C_JOB_ID];
                    $item[ActivityHeaderTable::C_BILL]= $row[ActivityHeaderTable::C_BILL];
                    $item[ActivityHeaderTable::C_SITE]= $row[ActivityHeaderTable::C_SITE];
                    $item[ActivityHeaderTable::C_DATE]= date_format(date_create($row[ActivityHeaderTable::C_DATE]), 'd/m/Y');
                    $item[ActivityHeaderTable::C_USER_ID]= $row[ActivityHeaderTable::C_USER_ID];
                    $item[ActivityHeaderTable::C_COY_ID]= $row[ActivityHeaderTable::C_COY_ID];
                    $item[ActivityHeaderTable::C_CUSTOMER_ID]= $row[ActivityHeaderTable::C_CUSTOMER_ID];
                    $item[ActivityHeaderTable::C_CONTACT_ID]= $row[ActivityHeaderTable::C_CONTACT_ID];
                    $item[ActivityHeaderTable::C_LOCATION_ID]= $row[ActivityHeaderTable::C_LOCATION_ID];
                    $item[ActivityHeaderTable::C_PROJECT_ID]= $row[ActivityHeaderTable::C_PROJECT_ID];
                    $item[ActivityHeaderTable::C_DEDUCT]= $row[ActivityHeaderTable::C_DEDUCT];
                    $item[ActivityHeaderTable::C_PROJECT_STATUS]= $row[ActivityHeaderTable::C_PROJECT_STATUS];
                    $item[ActivityHeaderTable::C_TT_TOTAL]= $row[ActivityHeaderTable::C_TT_TOTAL];
                    $item[ActivityHeaderTable::C_TT_TOTAL_OVERRIDE]= $row[ActivityHeaderTable::C_TT_TOTAL_OVERRIDE];
                    $item[ActivityHeaderTable::C_SIGNED]= $row[ActivityHeaderTable::C_SIGNED];
                    
                    $datas[]=$item;
                } 
			return $datas ; 
}

    
    public function getActivityByProject($projectId, $onlyConfirm = false, $onlySigned = false){
        $sql = "SELECT * FROM " . ActivityHeaderTable::C_TABLE . " WHERE " . ActivityHeaderTable::C_PROJECT_ID . " = " . $projectId . " ";
        
        if($onlyConfirm){
            $sql .= "AND " . ActivityHeaderTable::C_STATUS . " = " . ActStatus::Confirm . " ";
        }
        if($onlySigned){
            $sql .= "AND " . ActivityHeaderTable::C_SIGNED . " = " . ActSigned::Signed . " ";
        }
        
        $rows = $this->db->getTable($sql);
        
        return $rows;
    }
    
    public function getTotalMandaysActivityByProject($projectId, $onlyConfirm = false, $onlySigned = false){
        $rows = $this->getActivityByProject($projectId, $onlyConfirm, $onlySigned);
        
        $totalMandays = 0;
        if(!is_null($rows) && count($rows) > 0){
            foreach($rows as $row){
                $totalMandays += $row[ActivityHeaderTable::C_TT_TOTAL_OVERRIDE] / 8;
            }
        }
        
        return $totalMandays;
    }

    public function getTotalMandaysActivityByJobCentre($jobCentreId, $onlyConfirm = false, $onlySigned = false){
        $rows = $this->getActivityByProjectJobCentre($jobCentreId, $onlyConfirm, $onlySigned);
        
        $totalMandays = 0;
        if(!is_null($rows) && count($rows) > 0){
            foreach($rows as $row){
                $totalMandays += floatval($row[ActivityHeaderTable::C_TT_TOTAL_OVERRIDE] / 8);
            }
        }
        
        return $totalMandays;
    }

    public function getActivityByProjectJobCentre($jobCentreId, $onlyConfirm = false, $onlySigned = false){
        $sql = "SELECT * FROM " . ActivityHeaderTable::C_TABLE . " WHERE " . ActivityHeaderTable::C_PROJECT_ID . " = " . $jobCentreId . " ";
        
        if($onlyConfirm){
            $sql .= "AND " . ActivityHeaderTable::C_STATUS . " = " . ActStatus::Confirm . " ";
        }
        if($onlySigned){
            $sql .= "AND " . ActivityHeaderTable::C_SIGNED . " = " . ActSigned::Signed . " ";
        }
        
        $rows = $this->db->getTable($sql);
        
        return $rows;
	}

	public function getTotalMandaysUsedByJobCentre($jobCentreId){
		$sql = "SELECT SUM(D.".ActivityTimingDetailTable::C_TOTAL_OVERRIDE.") AS TOTAL FROM " . ActivityHeaderTable::C_TABLE . " H ";
		$sql .= " LEFT OUTER JOIN ".ActivityTimingDetailTable::C_TABLE." D ";
		$sql .= " ON (D.".ActivityTimingDetailTable::C_ID." = H.".ActivityHeaderTable::C_ID.") ";
		$sql .= " WHERE H." . ActivityHeaderTable::C_JOB_ID . " = " . $jobCentreId . " ";
		$sql .= " AND H." . ActivityHeaderTable::C_BILL . " <> 2 ";
		$row = $this->db->getTable($sql);
		
		$mandays = 0;
		if(!is_null($row) && count($row)>0){
			$mandays = floatval($row[0]['TOTAL']/8);
		}
        return $mandays;
	}

	/**Utilization query */

	public function getActualData($jobCentreId, $line){
		$sql = "SELECT H.".ActivityHeaderTable::C_CODE.", H.".ActivityHeaderTable::C_SIGNED.", H.".ActivityHeaderTable::C_ID.", ";
		$sql .= ' H.'.ActivityHeaderTable::C_CONTACT_ID.", ";
		$sql .= ' H.'.ActivityHeaderTable::C_BILL.", ";
		$sql .= ' D.*, U. '.UserTable::C_FULL_NAME;
		$sql .= " FROM " . ActivityHeaderTable::C_TABLE . " H ";
		$sql .= " LEFT OUTER JOIN ".UserTable::C_TABLE." U ";
		$sql .= " ON (U.".UserTable::C_ID." = H.".ActivityHeaderTable::C_USER_ID.") ";

		$sql .= " LEFT OUTER JOIN ".ActivityTimingDetailTable::C_TABLE." D ";
		$sql .= " ON (D.".ActivityTimingDetailTable::C_ID." = H.".ActivityHeaderTable::C_ID.") ";
		
		$sql .= " WHERE H." . ActivityHeaderTable::C_JOB_ID . " = " . $jobCentreId . " ";
		$sql .= " AND D." . ActivityTimingDetailTable::C_ASSIGN_ID . " = " . $line . " ";
       
		$rows = $this->db->getTable($sql);
		
		if(!is_null($rows) && count($rows)>0){
			return $rows;
		}
        return null;
	}

	public function getActualDaysPerPhase($jobCentreId, $line){
		$sql = "SELECT SUM(D.".ActivityTimingDetailTable::C_TOTAL_AFT_BREAK.") AS TOTAL FROM " . ActivityHeaderTable::C_TABLE . " H ";
		$sql .= " LEFT OUTER JOIN ".ActivityTimingDetailTable::C_TABLE." D ";
		$sql .= " ON (D.".ActivityTimingDetailTable::C_ID." = H.".ActivityHeaderTable::C_ID.") ";
		$sql .= " WHERE H." . ActivityHeaderTable::C_JOB_ID . " = " . $jobCentreId . " ";
		$sql .= " AND D." . ActivityTimingDetailTable::C_ASSIGN_ID . " = " . $line . " ";
       
		$row = $this->db->getTable($sql);
		
		$mandays = 0;
		if(!is_null($row) && count($row)>0){
			$mandays = floatval($row[0]['TOTAL']);
		}
        return $mandays;
	}
	
	public function getActualConsultant($jobCentreId, $line){
		$sql = "SELECT DISTINCT (U.".UserTable::C_FULL_NAME.") AS NAME FROM " . ActivityHeaderTable::C_TABLE . " H ";
		
		$sql .= " LEFT OUTER JOIN ".UserTable::C_TABLE." U ";
		$sql .= " ON (U.".UserTable::C_ID." = H.".ActivityHeaderTable::C_USER_ID.") ";

		$sql .= " LEFT OUTER JOIN ".ActivityTimingDetailTable::C_TABLE." D ";
		$sql .= " ON (D.".ActivityTimingDetailTable::C_ID." = H.".ActivityHeaderTable::C_ID.") ";
		
		$sql .= " WHERE H." . ActivityHeaderTable::C_JOB_ID . " = " . $jobCentreId . " ";
		$sql .= " AND D." . ActivityTimingDetailTable::C_ASSIGN_ID . " = " . $line . " ";
       
		$rows = $this->db->getTable($sql);
		
		$name = "";
		if(!is_null($rows) && count($rows)>0){
			$sep = "";
			foreach ($rows as $row) {
				$name .= $sep .$row['NAME'];
				$sep = ", ";
			}
			
		}
        return $name;
	}

	public function getActualCustomerRep($activity_id,$rep_id){
		$sql = "SELECT * ";
	
		$sql .= " FROM ".CustomerContactTable::C_TABLE."  ";
		$sql .= " WHERE " . CustomerContactTable::C_CONTACT_ID . " = " . $rep_id . " ";
	
		$row = $this->db->getTable($sql);
		//log::write($sql);
		$name = "";
		if(!is_null($row) && count($row)>0){
	
				$name = $row[0][CustomerContactTable::C_NAME];
			
		}
               // Log::write(print_r($name,true));
		
        return $name;
	}
	
	public function getActualDate($jobCentreId, $line){
		$sql = "SELECT MAX(D.".ActivityTimingDetailTable::C_DATE.") AS DATE FROM " . ActivityHeaderTable::C_TABLE . " H ";
		$sql .= " LEFT OUTER JOIN ".ActivityTimingDetailTable::C_TABLE." D ";
		$sql .= " ON (D.".ActivityTimingDetailTable::C_ID." = H.".ActivityHeaderTable::C_ID.") ";
		$sql .= " WHERE H." . ActivityHeaderTable::C_JOB_ID . " = " . $jobCentreId . " ";
		$sql .= " AND D." . ActivityTimingDetailTable::C_ASSIGN_ID . " = " . $line . " ";
       
		$row = $this->db->getTable($sql);
		
		$date = null;
		if(!is_null($row) && count($row)>0){
			$date = date_create($row[0]['DATE'])->format('d/m/Y');
		}
        return $date;
	}
	
	public function getActualSn($jobCentreId, $line){
		$sql = "SELECT DISTINCT H.".ActivityHeaderTable::C_CODE.", H.".ActivityHeaderTable::C_SIGNED." FROM " . ActivityHeaderTable::C_TABLE . " H ";
		$sql .= " LEFT OUTER JOIN ".ActivityTimingDetailTable::C_TABLE." D ";
		$sql .= " ON (D.".ActivityTimingDetailTable::C_ID." = H.".ActivityHeaderTable::C_ID.") ";
		$sql .= " WHERE H." . ActivityHeaderTable::C_JOB_ID . " = " . $jobCentreId . " ";
		$sql .= " AND D." . ActivityTimingDetailTable::C_ASSIGN_ID . " = " . $line . " ";
       
		$rows = $this->db->getTable($sql);
		
		$data = [];
		$data['service_no'] = "";
		$data['signed'] = "No";

		$count_yes = 0;
		

		if(!is_null($rows) && count($rows)>0){
			$sep = "";
			foreach ($rows as $row) {
				$data['service_no'] .= $sep .$row[ActivityHeaderTable::C_CODE];
				$sep = ", ";

				if($row[ActivityHeaderTable::C_SIGNED] == 1){
					$count_yes++;
				}

			}
		}

		if( $count_yes == count($rows) && count($rows)>0 ){
			$data['signed'] = "Yes";
		}else if( $count_yes > 0 &&  $count_yes != count($rows) ){
			$data['signed'] = "Partially Approved";
		}
        return $data;
	}
	

	

}
?>