SendReminder.service

<?php
////////////////////////////////////////////////////
// PHPMailer - PHP email class
//
// Class for sending email using either
// sendmail, PHP mail(), or SMTP.  Methods are
// based upon the standard AspEmail(tm) classes.
//
// Copyright (C) 2001 - 2003  Brent R. Matzelle
//
// License: LGPL, see LICENSE
////////////////////////////////////////////////////

/**
 * PHPMailer - PHP email transport class
 * @package PHPMailer
 * @author Brent R. Matzelle
 * @copyright 2001 - 2003 Brent R. Matzelle
 */


//file modified by richie

require_once('include/utils/utils.php');
//require_once("modules/Emails/class.smtp.php");
//require_once("modules/Emails/class.phpmailer.php");
require_once("modules/Emails/mail.php");
require_once('include/logging.php');
require_once("config.php");

$current_user = Users::getActiveAdminUser();
// Set the default sender email id
global $HELPDESK_SUPPORT_EMAIL_ID;
$from = $HELPDESK_SUPPORT_EMAIL_ID;
if(empty($from)) {
	// default configuration is empty?
	$from = "reminders@localserver.com";
}

// Get the list of activity for which reminder needs to be sent

global $adb;
global $log;
global $site_URL;
$log =Logger::getLogger('SendReminder');
$log->debug(" invoked SendReminder ");

// retrieve the translated strings.
if(empty($current_language))
	$current_language = 'en_us';
$app_strings = return_application_language($current_language);

//modified query for recurring events -Jag
$query="SELECT vtiger_crmentity.crmid, vtiger_crmentity.description, vtiger_crmentity.smownerid, vtiger_seactivityrel.crmid AS setype,vtiger_activity.*,vtiger_activity_reminder.reminder_time,
		vtiger_activity_reminder.reminder_sent,vtiger_activity_reminder.recurringid FROM vtiger_activity
		INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid=vtiger_activity.activityid
		INNER JOIN vtiger_activity_reminder ON vtiger_activity.activityid=vtiger_activity_reminder.activity_id
		LEFT OUTER JOIN vtiger_seactivityrel ON vtiger_seactivityrel.activityid = vtiger_activity.activityid
		WHERE DATE_FORMAT(vtiger_activity.date_start,'%Y-%m-%d, %H:%i:%s') >= '".date('Y-m-d')."' AND vtiger_crmentity.crmid != 0 AND
		(vtiger_activity.eventstatus is NULL OR vtiger_activity.eventstatus NOT IN ('Held','Cancelled'))
		AND (vtiger_activity.status is NULL OR vtiger_activity.status NOT IN ('Completed', 'Deferred', 'Cancelled'))
		AND vtiger_activity_reminder.reminder_sent = 0 AND vtiger_activity_reminder.reminder_time != 0
		GROUP BY vtiger_activity.activityid";
$result = $adb->pquery($query, array());

if($adb->num_rows($result) >= 1)
{
	//To fetch reminder frequency from cron tasks
	$reminderFrequencyQuery = 'SELECT frequency FROM vtiger_cron_task WHERE name = "SendReminder" AND handler_file = "cron/SendReminder.service"';
	$reminderResult = $adb->pquery($reminderFrequencyQuery, array());
	$reminderFrequency = $adb->query_result($reminderResult,0,'frequency');

	// Retriving the reminder email content from emailtemplates table
	$templateQuery='SELECT body FROM vtiger_emailtemplates WHERE subject=? AND systemtemplate=?';
	$templateResult = $adb->pquery($templateQuery, array('Reminder', '1'));
	$eventReminderBody=decode_html($adb->query_result($templateResult,0,'body'));

	// Retriving the reminder email content from emailtemplates table
	$templateQuery='SELECT body FROM vtiger_emailtemplates WHERE subject=? AND systemtemplate=?';
	$templateResult = $adb->pquery($templateQuery, array('Activity Reminder', '1'));
	$todoReminderBody=decode_html($adb->query_result($templateResult,0,'body'));

	while($result_set = $adb->fetch_array($result))
	{
		$date_start = $result_set['date_start'];
		$time_start = $result_set['time_start'];
		$reminder_time = $result_set['reminder_time']*60;
		$date = new DateTimeField( null );
		$userFormatedString = $date->getDisplayDate();
		$timeFormatedString = $date->getDisplayTime();
		$dBFomatedDate = DateTimeField::convertToDBFormat($userFormatedString);
		$curr_time = strtotime($dBFomatedDate." ". $timeFormatedString);
		$activity_id = $result_set['activityid'];
		$activitymode = ($result_set['activitytype'] == "Task")?"Task":"Events";
		$parent_type = $result_set['setype'];
		$activity_sub = $result_set['subject'];
		$to_addr= array();

		if($parent_type!='')
		$parent_content = getParentInfo($parent_type)."\n";
		else
		$parent_content = "";
		//code included for recurring events by jaguar starts
		$recur_id = $result_set['recurringid'];
		$current_date=date('Y-m-d');
		if($recur_id == 0)
		{
			$date_start = $result_set['date_start'];
		}
		else
		{
			$date_start = $result_set['recurringdate'];
		}
		//code included for recurring events by jaguar ends
		$date = new DateTimeField("$date_start $time_start");
		$userFormatedString = $date->getDisplayDate();
		$timeFormatedString = $date->getDisplayTime();
		$dBFomatedDate = DateTimeField::convertToDBFormat($userFormatedString);
		$activity_time = strtotime($dBFomatedDate.' '.$timeFormatedString);
		$differenceOfActivityTimeAndCurrentTime = ($activity_time - $curr_time);
		if (($differenceOfActivityTimeAndCurrentTime > 0) && (($differenceOfActivityTimeAndCurrentTime <= $reminder_time) || ($differenceOfActivityTimeAndCurrentTime <= $reminderFrequency)))
		{
			$log->debug(" InSide REMINDER");
			$query_user="SELECT vtiger_users.email1,vtiger_salesmanactivityrel.smid FROM vtiger_salesmanactivityrel inner join vtiger_users on vtiger_users.id=vtiger_salesmanactivityrel.smid WHERE vtiger_salesmanactivityrel.activityid =? AND vtiger_users.deleted=? AND vtiger_users.status=?";
			$user_result = $adb->pquery($query_user, array($activity_id, 0, 'Active'));
			$invitedUsersList = array();
			if($adb->num_rows($user_result)>=1)
			{
				while($user_result_row = $adb->fetch_array($user_result))
				{
					if($user_result_row['email1']!='' || $user_result_row['email1'] !=NULL)
					{
						$to_addr[$user_result_row['smid']] = $user_result_row['email1'];
					}
					$invitedUsersList[] = $user_result_row['smid'];
				}
			}

			$ownerId = $result_set['smownerid'];
			if (!in_array($ownerId, $invitedUsersList)) {
				$ownerId = $invitedUsersList[0];
			}
			$ownerFocus = CRMEntity::getInstance('Users');
			$ownerFocus->retrieve_entity_info($ownerId, 'Users');
			$ownerTimeZone = getTranslatedString($ownerFocus->column_fields['time_zone'], 'Users');

			$dateTime = new DateTimeField($result_set['date_start'] .' '. $result_set['time_start']);
			$dateTimeInOwnerFormat = $dateTime->getDisplayDateTimeValue($ownerFocus);

			$enddateTime = new DateTimeField($result_set['due_date'] .' '. $result_set['time_end']);
			$enddateTimeInOwnerFormat = $enddateTime->getDisplayDateTimeValue($ownerFocus);

			//get related contact names
			$cont_qry = "SELECT * FROM vtiger_cntactivityrel WHERE activityid=?";
			$cont_res = $adb->pquery($cont_qry, array($activity_id));
			$noofrows = $adb->num_rows($cont_res);
			$cont_id = array();
			if($noofrows > 0) {
				for($i=0; $i<$noofrows; $i++) {
					$cont_id[] = $adb->query_result($cont_res,$i,"contactid");
				}
			}
			$cont_name = '';
			foreach($cont_id as $key=>$id) {
				if($id != '') {
					$contact_name = Vtiger_Util_Helper::getRecordName($id);
					$cont_name .= $contact_name .', ';
				}
			}
			$cont_name = trim($cont_name,', ');
			$result_set['subject'] = decode_html($result_set['subject']);
			if($result_set['activitytype'] == "Task") {
				$enddateInOwnerFormat = $enddateTime->getDisplayDate($ownerFocus);
				$list = $todoReminderBody;
				$list = str_replace('$calendar-subject$',$result_set['subject'],$list);
				$list = str_replace('$calendar-description$',$result_set['description'],$list);
				$list = str_replace('$calendar-date_start$', $dateTimeInOwnerFormat.' '.$ownerTimeZone, $list);
				$list = str_replace('$calendar-due_date$', $enddateInOwnerFormat.' '.$ownerTimeZone, $list);

				$contents = getMergedDescription($list, $activity_id, 'Calendar',true);
				$subject = vtranslate('Activity Reminder', 'Calendar').': '.$result_set['subject'] . " @ $dateTimeInOwnerFormat";
			} else {
				$list = $eventReminderBody;
				$list = str_replace('$events-subject$',decode_html($result_set['subject']),$list);
				$list = str_replace('$events-description$',decode_html($result_set['description']),$list);
				$list = str_replace('$events-date_start$', $dateTimeInOwnerFormat.' '.$ownerTimeZone, $list);
				$list = str_replace('$events-due_date$', $enddateTimeInOwnerFormat.' '.$ownerTimeZone, $list);
				$list = str_replace('$events-contactid$', $cont_name, $list);

				$contents = getMergedDescription($list, $activity_id, 'Events',true);
				$subject = vtranslate('Reminder', 'Calendar').': '.$result_set['subject'] . " @ $dateTimeInOwnerFormat";
			}

			$recordModel = Vtiger_Record_Model::getInstanceById($activity_id, 'Calendar');
			$recordDetailViewLink = $recordModel->getDetailViewUrl();
			$contents = $contents."<br/> ".vtranslate('LBL_CLICK_HERE_TO_VIEW', 'Calendar')."&nbsp;<a href=$site_URL/$recordDetailViewLink>".vtranslate('LBL_RECORD', 'Calendar')."</a>";
			if(php7_count($to_addr) >=1)
			{
				send_email($to_addr,$from,$subject,$contents,$mail_server,$mail_server_username,$mail_server_password);
				$upd_query = "UPDATE vtiger_activity_reminder SET reminder_sent = ? WHERE activity_id = ?";
				$upd_params = array('1',$activity_id);

				if($recur_id!=0)
				{
					$upd_query.=" and recurringid =?";
					array_push($upd_params, $recur_id);
				}

				$adb->pquery($upd_query, $upd_params);

			}
		}
	}
}

/**
 This function is used to assign parameters to the mail object and send it.
 It takes the following as parameters.
	$to as string - to address
	$from as string - from address
	$subject as string - subject if the mail
	$contents as text - content of the mail
	$mail_server as string - sendmail server name
	$mail_server_username as string - sendmail server username
	$mail_server_password as string - sendmail server password

*/
function send_email($to,$from,$subject,$contents,$mail_server,$mail_server_username,$mail_server_password)
{
	global $adb;
	global $log;
	$log->info("This is send_mail function in SendReminder.php(vtiger home).");
	global $root_directory;

	$mail = new PHPMailer\PHPMailer\PHPMailer();


	$mail->Subject	= $subject;
	$mail->Body		= nl2br($contents);//"This is the HTML message body <b>in bold!</b>";

	$mail->IsSMTP();// set mailer to use SMTP
	$mailserverresult=$adb->pquery("select * from vtiger_systems where server_type='email'", array());
	$mail_server = $adb->query_result($mailserverresult,0,'server');
	$mail_server_username = $adb->query_result($mailserverresult,0,'server_username');
	$mail_server_password = $adb->query_result($mailserverresult,0,'server_password');
	$smtp_auth = $adb->query_result($mailserverresult,0,'smtp_auth');

	$_REQUEST['server']=$mail_server;
	$log->info("Mail Server Details => '".$mail_server."','".$mail_server_username."','".$mail_server_password."'");

	$mail->Host = $mail_server;	// specify main and backup server
	if($smtp_auth == 'true' || $smtp_auth == '1')
		$mail->SMTPAuth = true;
	else
		$mail->SMTPAuth = false;
	$mail->Username = $mail_server_username ;	// SMTP username
	$mail->Password = Vtiger_Functions::fromProtectedText($mail_server_password) ;	// SMTP password
	$mail->From = $from;
	$mail->FromName = $initialfrom;
	$log->info("Mail sending process : From Name & email id => '".$initialfrom."','".$from."'");
	foreach($to as $pos=>$addr)
	{
		$mail->AddAddress($addr);// name is optional
		$log->info("Mail sending process : To Email id = '".$addr."' (set in the mail object)");

	}
	$mail->WordWrap = 50;// set word wrap to 50 characters

	$mail->IsHTML(true);// set email format to HTML

	$mail->AltBody = "This is the body in plain text for non-HTML mail clients";

	// To Support TLS
	$serverinfo = explode("://", $mail_server);
	$smtpsecure = $serverinfo[0];
	if($smtpsecure == 'tls'){
		$mail->SMTPSecure = $smtpsecure;
		$mail->Host = $serverinfo[1];
	}
	// End

	$flag = MailSend($mail);
	$log->info("After executing the mail->Send() function.");
}

/**
 This function is used to get the Parent type and its Name
 It takes the input integer - crmid and returns the parent type and its name as string.
*/
function getParentInfo($value)
{
	global $adb;
	$parent_module = getSalesEntityType($value);
	if($parent_module == "Leads")
	{
		$sql = "select * from vtiger_leaddetails where leadid=?";
		$result = $adb->pquery($sql, array($value));
		$first_name = $adb->query_result($result,0,"firstname");
		$last_name = $adb->query_result($result,0,"lastname");

		$parent_name = $last_name.' '.$first_name;
	}
	elseif($parent_module == "Accounts")
	{
		$sql = "select * from vtiger_account where accountid=?";
		$result = $adb->pquery($sql, array($value));
		$account_name = $adb->query_result($result,0,"accountname");

		$parent_name =$account_name;
	}
	elseif($parent_module == "Potentials")
	{
		$sql = "select * from vtiger_potential where potentialid=?";
		$result = $adb->pquery($sql, array($value));
		$potentialname = $adb->query_result($result,0,"potentialname");

		$parent_name =$potentialname;
	}
	return $parent_module ." : ".$parent_name;
}
?>