<?php
require_once (PATH_CONTROLLERS . "Common.php") ;
require_once (PATH_MODELS . "admin/UserClass.php");
require_once (PATH_MODELS . "admin/CompanyClass.php");
require_once (PATH_MODELS . "activity/ActivityEmailApprovalClass.php");
require_once (PATH_MODELS . "activity/ActivityEmailApprovalReminderClass.php");
require_once (PATH_MODELS . "activity/ActivityHeaderClass.php");
require_once (PATH_MODELS . "activity/ActivityAttachmentClass.php");
require_once (PATH_MODELS . "activity/CustomerContactClass.php");
class ReminderApi extends ActivityBase {
	private $type = "" ;
	function __construct() {
		$this->db = $_SESSION[SE_DB] ;
		$this->orgid = $_SESSION[SE_ORGID] ;
	}
	function __destruct() {
		unset($this->db) ;
	}
	public function processRequest($params) {
		$this->type = REQ_VIEW ;
		
		try {
			$this->db->open() ;
			if (isset($params) && count($params) > 0) {
				if (isset($params['type']))
					$this->type = $params['type'] ;
			}
			switch ($this->type) {
				case REQ_REMINDER:
					//Log::write("access reminder");
					$this->reminder($params) ;
					break ;
				default:
					$this->sendJsonResponse(Status::Error,"invalid request.","",$this->type) ;
					break ;
			}
			$this->db->close() ;
			return true ;
		} catch (Exception $e) {
			$this->db->close() ;
			die ($e->getMessage()) ;
		}
	}
	private function getUnits($interval,$unit_id)
    {
		//if interval less than 1 
		$interval =  $interval < 1 ? 1 : $interval ;

		if($unit_id == 1){
			$data = $interval > 1 ? $interval." minutes" : $interval." minute"; 
			return $data ;
		}else if($unit_id == 2){
			$data = $interval > 1 ? $interval." days" : $interval." day"; 
			return $data ;
		}else if($unit_id == 3){
			$data = $interval > 1 ? $interval." weeks" : $interval." week"; 
			return $data ;
		}else if($unit_id == 4){
			$data = $interval > 1 ? $interval." months" : $interval." month"; 
			return $data ;
		}else{
			$data = $interval > 1 ? $interval." days" : $interval." day"; 
			return $data ;
		}
	}
	private function sendemailapprover($actId,$custEmail,$ccEmail,$mail_remarks,$stage = "",$header = ""){
		$actAttachCls = new ActivityAttachmentClass($this->db);
		$companyCls = new CompanyClass($this->db);
		$userCls = new UserClass($this->db);
		$clsHeader = new ActivityHeaderClass($this->db);
		$cusContactCls      = new CustomerContactClass($this->db);


		$actHeaderRow   = $clsHeader->getRecord($actId);
		$userId         = $actHeaderRow[ActivityHeaderTable::C_USER_ID];
		$company_id     = $actHeaderRow[ActivityHeaderTable::C_COY_ID];
		$cusRepId     	= $actHeaderRow[ActivityHeaderTable::C_CONTACT_ID];
		$row_com        = $companyCls->getRecord($company_id) ;
		$opt            = new CompanyOptions() ;
		$opt->loadXml($row_com[CompanyTable::C_OPTIONS]) ;
		$op         = $opt->getOption() ;

		$fromEmail = $op[CompanyOptions::C_EMAIL_SETTING][CompanyOptions::C_EMAIL_SETTING_SENDER];

		$userRow        = $userCls->getRecord($userId);

		$fromEmailStr = $fromEmail;
		
		$fromEmails   = explode(";", $fromEmailStr);

		$actCode = $actHeaderRow[ActivityHeaderTable::C_CODE];

		$path = PATH_ACTIVITY_OUTPUT . $actId . "/" . $actCode . ".pdf";
		$attachmentList = array();
		$attachmentList[] = $path;

		$ad_attachment = $actAttachCls->getDocs($actHeaderRow[ActivityHeaderTable::C_ID]);

		$dir = PATH_ACTIVITY_ADITIONAL_OUTPUT . $actHeaderRow[ActivityHeaderTable::C_ID] . "/";

		if(!is_null($ad_attachment) && count($ad_attachment)>0){
			foreach ($ad_attachment as $doc) {
				$docfile = $dir . $doc[ActivityAttachmentTable::C_DOC_NAME];
				if (file_exists($docfile)) {
					$attachmentList[] = $docfile;
				}
			}
		}
		
		$approverName = $cusContactCls->getRecord($cusRepId)[CustomerContactTable::C_NAME];

		$page = Util::convertLink("PortalServiceReportApproval",$userId);

		$url = $this->getBaseURL();
		$link = $url.'/code/PortalAuthService.php?c='.$page.'&t=6666&id='.$actHeaderRow[ActivityHeaderTable::C_ID].'&fn='.$userId;
            
		$images = $url.'/web/image/logo-sbg.jpg';

		if($stage == 'approval'){
			$email_body = file_get_contents(PATH_LIB . "new_email_template_reminder_approval.txt");
		}else{
			$email_body = file_get_contents(PATH_LIB . "new_email_template_reminder.txt");
		}
		
	
		$email_body = str_replace("{WEB_URL}", $link, $email_body);
		// $email_body = str_replace("{COMPANY_NAME}", trim($row_com[CompanyTable::C_DESC]), $email_body);
		//$email_body = str_replace("{CONSULTANT}", trim($userRow[UserTable::C_FULL_NAME]), $email_body);
		//$email_body = str_replace("{APPROVER_NAMES}", trim($approverName), $email_body);
		//$email_body = str_replace("{REQUEST_DATE}", date_create($actHeaderRow[ActivityHeaderTable::C_DATE])->format('d/m/Y'), $email_body);
		//$email_body = str_replace("{SERVICE_CODE}", $actHeaderRow[ActivityHeaderTable::C_CODE], $email_body);
		$email_body = str_replace("{REMARKS_BODY}", nl2br($mail_remarks), $email_body);
		
		$email_body = str_replace("{CONSULTANT_EMAIL}", trim($userRow[UserTable::C_EMAIL]), $email_body);
		// $email_body = str_replace("{ENTER}", "<br>", $email_body);
		//$email_body = str_replace("{IMAGE}", $images, $email_body);
		
		$result =  $this->sendEmail("[NO-REPLY] Service Report Approval ".$header, 
								$email_body,
								$fromEmails, 
								explode(";",$custEmail), 
								explode(";",$ccEmail),
								$attachmentList,
								$company_id,
								AppType::Activity,
								$actId);

		return;

	}
	private function generateMesgString($activity_id,$last_mesg,$new_mesg="",$last_send,$last_subject){
		
		$clsHeader = new ActivityHeaderClass($this->db);
		$cusContactCls      = new CustomerContactClass($this->db);

		$actHeaderRow   = $clsHeader->getRecord($activity_id);
		
		$cusRepId     	= $actHeaderRow[ActivityHeaderTable::C_CONTACT_ID];

		$approverName = $cusContactCls->getRecord($cusRepId)[CustomerContactTable::C_NAME];
		$string = '<p style="line-height: 1.5; word-break: break-word; text-align: left; font-family: Verdana, Geneva, sans-serif; font-size: 15px; mso-line-height-alt: 23px; margin: 0;"><span style="font-size: 15px;">Dear '.$approverName.'</span></p>';
		
		$string .= '<p style="line-height: 1.5; word-break: break-word; text-align: left; font-family: Verdana, Geneva, sans-serif; mso-line-height-alt: 18px; margin: 0;">&nbsp;</p>
		<p style="line-height: 1.5; word-break: break-word; text-align: left; font-family: Verdana, Geneva, sans-serif; font-size: 15px; mso-line-height-alt: 23px; margin: 0;"><span style="font-size: 15px;">'.$new_mesg.'</span></p>';
		
		$string .= '<br><hr>';
			
		$string .= '<p style="line-height: 1.5; word-break: break-word; text-align: left; font-family: Verdana, Geneva, sans-serif; font-size: 15px; mso-line-height-alt: 23px; margin: 0;"><span style="font-size: 12px;"><strong>Last Sent : '.$last_send.'</strong></span></p>';
		
		$string .= '<p style="line-height: 1.5; word-break: break-word; text-align: left; font-family: Verdana, Geneva, sans-serif; font-size: 15px; mso-line-height-alt: 23px; margin: 0;"><span style="font-size: 12px;"><strong>Subject : '.$last_subject.'</strong></span></p><br>';

		$string .=$last_mesg;
		

		return $string;
	}
    private function reminder($params)
    {
		$cslEmail = new ActivityEmailApprovalClass($this->db);
        $clsEmailReminder = new ActivityEmailApprovalReminderClass($this->db);
		$clsOrg     = new OrganizationClass($this->db) ;
		$clsHeader = new ActivityHeaderClass($this->db);
		$ws = $_SESSION[SE_REMOTE_IP] ;

		$rowOrg     = $clsOrg->getRecord(0) ;

        $use_reminder     		= 0;
		$reminder_mesg1 		= "";
		$reminder_mesg2 		= "";
		$reminder_mesg3 		= "";
		$interval 				= "1 day";
		$last_minute_approve 	= "1 day";

        if (!is_null($rowOrg)) {
            if (!is_null($rowOrg[OrganizationTable::C_OPTIONS]) && $rowOrg[OrganizationTable::C_OPTIONS] != "") {

                $opt = new OrganizationOptions() ;
                $opt->loadXml($rowOrg[OrganizationTable::C_OPTIONS]) ;
                $op = $opt->getOption() ;

                $use_reminder   = $op[OrganizationOptions::C_SETTING][OrganizationOptions::C_USE_REMINDER] ;
				
				$reminder_mesg2 = $op[OrganizationOptions::C_SETTING][OrganizationOptions::C_REMINDER_MESSAGE2] ;
				$reminder_mesg3 = $op[OrganizationOptions::C_SETTING][OrganizationOptions::C_REMINDER_MESSAGE3] ;
				$reminder_mesg4 = $op[OrganizationOptions::C_SETTING][OrganizationOptions::C_REMINDER_MESSAGE4] ;

				$interval = $this->getUnits($op[OrganizationOptions::C_SETTING][OrganizationOptions::C_REMINDER_INTERVAL],$op[OrganizationOptions::C_SETTING][OrganizationOptions::C_REMINDER_INTERVAL_UNIT] );
				$last_minute_approve = $this->getUnits($op[OrganizationOptions::C_SETTING][OrganizationOptions::C_REMINDER_INTERVAL_APPROVAL],$op[OrganizationOptions::C_SETTING][OrganizationOptions::C_REMINDER_INTERVAL_APPROVAL_UNIT] );
			

            }
        }

		if($use_reminder ==0 ){
			
			$this->sendJsonResponse(Status::Ok,"Reminder Api Service is inactive","",$this->type);
			return;
		}

		

		ReminderLog::write("use reminder");
		ReminderLog::write("interval : ".$interval);
		ReminderLog::write("last interval for approval : ".$last_minute_approve);
		// ReminderLog::write("msg : ".$reminder_mesg1);
		// ReminderLog::write("msg2 : ".$reminder_mesg2);
		// ReminderLog::write("msg3 : ".$reminder_mesg3);
		try {
			$this->db->beginTran() ;
			$rows = $this->getRows();
			ReminderLog::write("reminder need to be sent : " .count($rows));
			
			//ReminderLog::write(date_create('now')->format('d/m/Y H:i'));
			if(!is_null($rows) && count($rows)>0){
				foreach ($rows as $row) {
					if($row[ActivityEmailApprovalReminderTable::C_REMINDER_NO] == 3){
						
						$last_send = new DateTime($row[ActivityEmailApprovalReminderTable::C_LAST_SEND] );
						$last_send->modify("+".$last_minute_approve."");
						$now    = new DateTime();

						if($last_send < $now){
							ReminderLog::write("---------------".$row["service_no"]."------------------");
							ReminderLog::write("approve ".$row["service_no"]." by systems");
							ReminderLog::write("Title : ".$row["service_title"]);
							ReminderLog::write("Last Sent : ".date_create($row[ActivityEmailApprovalReminderTable::C_LAST_SEND])->format('d/m/Y H:i:s'));
							ReminderLog::write("Last Sent : ".date_create('now')->format('d/m/Y H:i:s'));
							ReminderLog::write("-------------------------------------------------------");

							$mail_remarks = $this->generateMesgString($row[ActivityEmailApprovalReminderTable::C_ID],$row[ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS_HTML],$reminder_mesg4,$row[ActivityEmailApprovalReminderTable::C_LAST_SEND],"[NO-REPLY] Service Report Approval  - Reminder",false);
							//$mail_remarks = $reminder_mesg4."
							//". $row[ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS];
							
							$value = array();
							$value[] = $this->db->fieldValue(ActivityEmailApprovalTable::C_EMAIL_REMARKS_APPROVAL,$reminder_mesg4) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalTable::C_EMAIL_REMARKS_HTML,$mail_remarks) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalTable::C_REMINDER_NO,4) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalTable::C_LAST_SEND,date_create('now')->format('Y-m-d H:i:s')) ;
							$cslEmail->updateContent($row[ActivityEmailApprovalReminderTable::C_EMAIL_APPROVAL_ID],$value) ;

							//set to reminder also
							$value = array();
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_ID,$row[ActivityEmailApprovalReminderTable::C_ID]) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_APPROVAL_ID,$row[ActivityEmailApprovalReminderTable::C_EMAIL_APPROVAL_ID]) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_TO,$row[ActivityEmailApprovalReminderTable::C_EMAIL_TO]) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_CC,$row[ActivityEmailApprovalReminderTable::C_EMAIL_CC]) ;
							
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS,$row[ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS]) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS1,$row[ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS1]) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS2,$row[ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS2]) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS3,$row[ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS3]) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS_APPROVAL,$reminder_mesg4) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS_HTML,$mail_remarks) ;

							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_REMINDER_NO,4) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_LAST_SEND,date_create('now')->format('Y-m-d H:i:s')) ;
							$clsEmailReminder->addRecord($value) ;

							$datas= [];
							$datas[] = $this->db->fieldValue(ActivityHeaderTable::C_AUTOMATED_SIGNED,1) ; 
							$datas[] = $this->db->fieldValue(ActivityHeaderTable::C_SIGNED,1) ; 
							$datas[] = $this->db->fieldValue(ActivityHeaderTable::C_SIGNED_DATE,date_create('now')->format('Y-m-d H:i:s')) ; 
							$datas[] = $this->db->fieldValue(ActivityHeaderTable::C_SIGNED_REMARKS,"Signed by systems ") ; 
							$clsHeader->updateRecord($row["service_id"],$datas);

							$this->sendemailapprover($row[ActivityEmailApprovalReminderTable::C_ID],$row[ActivityEmailApprovalReminderTable::C_EMAIL_TO],$row[ActivityEmailApprovalReminderTable::C_EMAIL_CC],$mail_remarks,"approval"," - Approval Notification");

						}else{
							//ReminderLog::write("it's not time yet approve on their behalf");
						}

					} else if($row[ActivityEmailApprovalReminderTable::C_REMINDER_NO] == 2){
						
						$last_send = new DateTime($row[ActivityEmailApprovalReminderTable::C_LAST_SEND] );
						$last_send->modify("+".$interval."");
						$now    = new DateTime();

						if($last_send < $now){
							
							ReminderLog::write("---------------".$row["service_no"]."------------------");
							ReminderLog::write("escalate service report reminder ".$row["service_no"]." final approval 3 (final reminder)");
							ReminderLog::write("Title : ".$row["service_title"]);
							ReminderLog::write("Last Sent : ".date_create($row[ActivityEmailApprovalReminderTable::C_LAST_SEND])->format('d/m/Y H:i:s'));
							ReminderLog::write("Last Sent : ".date_create('now')->format('d/m/Y H:i:s'));
							ReminderLog::write("-------------------------------------------------------");

							$mail_remarks = $this->generateMesgString($row[ActivityEmailApprovalReminderTable::C_ID],$row[ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS_HTML],$reminder_mesg3,$row[ActivityEmailApprovalReminderTable::C_LAST_SEND],"[NO-REPLY] Service Report Approval  - Reminder",false);

							// $mail_remarks = $reminder_mesg3."
							// 				". $row[ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS];

							$value = array();
							$value[] = $this->db->fieldValue(ActivityEmailApprovalTable::C_EMAIL_REMARKS3,$reminder_mesg3) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalTable::C_EMAIL_REMARKS_HTML,$mail_remarks) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalTable::C_REMINDER_NO,3) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalTable::C_LAST_SEND,date_create('now')->format('Y-m-d H:i:s')) ;
							$cslEmail->updateContent($row[ActivityEmailApprovalReminderTable::C_EMAIL_APPROVAL_ID],$value) ;

							//set to reminder also
							$value = array();
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_ID,$row[ActivityEmailApprovalReminderTable::C_ID]) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_APPROVAL_ID,$row[ActivityEmailApprovalReminderTable::C_EMAIL_APPROVAL_ID]) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_TO,$row[ActivityEmailApprovalReminderTable::C_EMAIL_TO]) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_CC,$row[ActivityEmailApprovalReminderTable::C_EMAIL_CC]) ;
							
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS,$row[ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS]) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS1,$row[ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS1]) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS2,$row[ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS2]) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS3,$reminder_mesg3) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS_HTML,$mail_remarks) ;

							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_REMINDER_NO,3) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_LAST_SEND,date_create('now')->format('Y-m-d H:i:s')) ;
							$clsEmailReminder->addRecord($value) ;

							$this->sendemailapprover($row[ActivityEmailApprovalReminderTable::C_ID],$row[ActivityEmailApprovalReminderTable::C_EMAIL_TO],$row[ActivityEmailApprovalReminderTable::C_EMAIL_CC],$mail_remarks,""," - Final Reminder");

						}else{
							//ReminderLog::write("it's not time yet final approval 3");
						}

					} else if($row[ActivityEmailApprovalReminderTable::C_REMINDER_NO] == 1){

						$last_send = new DateTime($row[ActivityEmailApprovalReminderTable::C_LAST_SEND] );
						$last_send->modify("+".$interval."");
						$now    = new DateTime();

						if($last_send < $now){
							
							ReminderLog::write("---------------".$row["service_no"]."------------------");
							ReminderLog::write("escalate service report reminder ".$row["service_no"]." to number 2 (reminder)");
							ReminderLog::write("Title : ".$row["service_title"]);
							ReminderLog::write("Last Sent : ".date_create($row[ActivityEmailApprovalReminderTable::C_LAST_SEND])->format('d/m/Y H:i:s'));
							ReminderLog::write("Last Sent : ".date_create('now')->format('d/m/Y H:i:s'));
							ReminderLog::write("-------------------------------------------------------");
							// $mail_remarks = $reminder_mesg2."
							// 				". $row[ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS];

							$mail_remarks = $this->generateMesgString($row[ActivityEmailApprovalReminderTable::C_ID],$row[ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS_HTML],$reminder_mesg2,$row[ActivityEmailApprovalReminderTable::C_LAST_SEND],"[NO-REPLY] Service Report Approval",false);

							$value = array();
							$value[] = $this->db->fieldValue(ActivityEmailApprovalTable::C_EMAIL_REMARKS2,$reminder_mesg2) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalTable::C_EMAIL_REMARKS_HTML,$mail_remarks) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalTable::C_REMINDER_NO,2) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalTable::C_LAST_SEND,date_create('now')->format('Y-m-d H:i:s')) ;
							$cslEmail->updateContent($row[ActivityEmailApprovalReminderTable::C_EMAIL_APPROVAL_ID],$value) ;

							//set to reminder also
							$value = array();
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_ID,$row[ActivityEmailApprovalReminderTable::C_ID]) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_APPROVAL_ID,$row[ActivityEmailApprovalReminderTable::C_EMAIL_APPROVAL_ID]) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_TO,$row[ActivityEmailApprovalReminderTable::C_EMAIL_TO]) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_CC,$row[ActivityEmailApprovalReminderTable::C_EMAIL_CC]) ;
							
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS,$row[ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS]) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS1,$row[ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS1]) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS2,$reminder_mesg2) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_EMAIL_REMARKS_HTML,$mail_remarks) ;

							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_REMINDER_NO,2) ;
							$value[] = $this->db->fieldValue(ActivityEmailApprovalReminderTable::C_LAST_SEND,date_create('now')->format('Y-m-d H:i:s')) ;
							$clsEmailReminder->addRecord($value) ;

							$this->sendemailapprover($row[ActivityEmailApprovalReminderTable::C_ID],$row[ActivityEmailApprovalReminderTable::C_EMAIL_TO],$row[ActivityEmailApprovalReminderTable::C_EMAIL_CC],$mail_remarks,""," - Reminder");
						}else{
							//ReminderLog::write("it's not time yet escalate to number 2");
						}
					}
				}
			}else{
				ReminderLog::write("No reminder on database");
			}

			$this->db->commitTran() ;

			$this->sendJsonResponse(Status::Ok,"Reminder Api Service","",$this->type);
			return;

		} catch (Exception $e) {
			$this->db->rollbackTran() ;
			Log::write('[Service Reminder] ' . $e->getMessage());
			$this->sendJsonResponse(Status::Error,"Sorry, we are unable to process your system as there is a error in database operation.","",$this->type) ;
		}
    }

	private function getRows()
	{
		$sql = " select r.* ";
		$sql .= ", e.".ActivityEmailApprovalTable::C_EMAIL_ID." as 'approval_email_id' ";
		$sql .= ", h.".ActivityHeaderTable::C_ID." as 'service_id' ";
		$sql .= ", h.".ActivityHeaderTable::C_CODE." as 'service_no' ";
		$sql .= ", h.".ActivityHeaderTable::C_SIGNED." as 'signed' ";
		$sql .= ", h.".ActivityHeaderTable::C_TITLE." as 'service_title' ";

		$sql .= " from ( select ".ActivityEmailApprovalReminderTable::C_ID.", MAX(".ActivityEmailApprovalReminderTable::C_REMINDER_NO.") as 'max_no'
						 from ".ActivityEmailApprovalReminderTable::C_TABLE." group by ".ActivityEmailApprovalReminderTable::C_ID." ) x ";

		$sql .= " left outer join ".ActivityEmailApprovalReminderTable::C_TABLE." r ";
		$sql .= " on ( r.".ActivityEmailApprovalReminderTable::C_ID." = x.".ActivityEmailApprovalReminderTable::C_ID." AND  r.".ActivityEmailApprovalReminderTable::C_REMINDER_NO." = x.max_no) ";

		$sql .= " left outer join ".ActivityEmailApprovalTable::C_TABLE." e ";
		$sql .= " on ( e.".ActivityEmailApprovalTable::C_EMAIL_ID." = r.".ActivityEmailApprovalReminderTable::C_EMAIL_APPROVAL_ID.") ";

		$sql .= " left outer join ".ActivityHeaderTable::C_TABLE." h ";
		$sql .= " on ( h.".ActivityHeaderTable::C_ID." = r.".ActivityEmailApprovalReminderTable::C_ID.") ";

		$sql .= " where h.".ActivityHeaderTable::C_SIGNED." != 1 ";

		$rows = $this->db->getTable($sql);
		
		return $rows;
	}
}
?>