Campaigns related list failing

Send reminder is not sending mail for Events

cron/SendReminder.service

@@ -40,6 +40,7 @@ if(empty($from)) {
global $adb;
global $log;
+global $site_URL;
$log =& LoggerManager::getLogger('SendReminder');
$log->debug(" invoked SendReminder ");
@@ -49,7 +50,7 @@ if(empty($current_language))
$app_strings = return_application_language($current_language);
//modified query for recurring events -Jag
-$query="SELECT vtiger_crmentity.crmid, vtiger_crmentity.smownerid, vtiger_seactivityrel.crmid AS setype,vtiger_activity.*,vtiger_activity_reminder.reminder_time,
+$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
@@ -70,9 +71,14 @@ if($adb->num_rows($result) >= 1)
// 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'];
@@ -113,9 +119,9 @@ if($adb->num_rows($result) >= 1)
$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=0";
-			$user_result = $adb->pquery($query_user, array($activity_id));
+			$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)
{
@@ -123,7 +129,7 @@ if($adb->num_rows($result) >= 1)
{
if($user_result_row['email1']!='' || $user_result_row['email1'] !=NULL)
{
-						$to_addr[] = $user_result_row['email1'];
+						$to_addr[$user_result_row['smid']] = $user_result_row['email1'];
}
$invitedUsersList[] = $user_result_row['smid'];
}
@@ -140,42 +146,55 @@ if($adb->num_rows($result) >= 1)
$dateTime = new DateTimeField($result_set['date_start'] .' '. $result_set['time_start']);
$dateTimeInOwnerFormat = $dateTime->getDisplayDateTimeValue($ownerFocus);
-			// Retriving the Subject and message from reminder table
-			$sql = "select active,notificationsubject,notificationbody from vtiger_notificationscheduler where schedulednotificationid=8";
-			$result_main = $adb->pquery($sql, array());
-
-			$subject = $app_strings['Reminder']. $result_set['activitytype'] . " @ $dateTimeInOwnerFormat ] ($ownerTimeZone)".
-						$adb->query_result($result_main,0,'notificationsubject');
-
-			//Set the mail body/contents here
-			$contents = nl2br($adb->query_result($result_main,0,'notificationbody')) ."\n\n ".
-							$app_strings['Subject']." : ".$activity_sub."\n ". $parent_content ." ".
-							$app_strings['Date & Time']." : $dateTimeInOwnerFormat ($ownerTimeZone)\n\n ".
-							$app_strings['Visit_Link']." <a href='".$site_URL."/index.php?view=Detail&module=Calendar&record=".$activity_id."'>".$app_strings['Click here']."</a>";
-
+			$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 = $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');
+				$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');
+				$contents = getMergedDescription($list, $activity_id, 'Events',true);
$subject = vtranslate('Reminder', 'Calendar').': '.$result_set['subject'] . " @ $dateTimeInOwnerFormat";
}
-			
+			$contents = $contents."<br/> ".vtranslate('LBL_CLICK_HERE_TO_VIEW', 'Calendar')."&nbsp;<a href=$site_URL/$recordDetailViewLink>".vtranslate('LBL_RECORD', 'Calendar')."</a>";
if(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=1 where activity_id=?";
-				$upd_params = array($activity_id);
+				$upd_query = "UPDATE vtiger_activity_reminder SET reminder_sent = ?";
+				$upd_params = array(1);
if($recur_id!=0)
{
@@ -212,8 +231,8 @@ function send_email($to,$from,$subject,$contents,$mail_server,$mail_server_usern
$mail = new PHPMailer();
-	$mail->Subject = $subject;
-	$mail->Body    = nl2br($contents);//"This is the HTML message body <b>in bold!</b>";
+	$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());
@@ -267,7 +286,7 @@ function send_email($to,$from,$subject,$contents,$mail_server,$mail_server_usern
function getParentInfo($value)
{
global $adb;
- 	$parent_module = getSalesEntityType($value);
+	$parent_module = getSalesEntityType($value);
if($parent_module == "Leads")
{
$sql = "select * from vtiger_leaddetails where leadid=?";
@@ -279,7 +298,7 @@ function getParentInfo($value)
}
elseif($parent_module == "Accounts")
{
-		$sql = "select * from  vtiger_account where accountid=?";
+		$sql = "select * from vtiger_account where accountid=?";
$result = $adb->pquery($sql, array($value));
$account_name = $adb->query_result($result,0,"accountname");
@@ -287,12 +306,12 @@ function getParentInfo($value)
}
elseif($parent_module == "Potentials")
{
-		$sql = "select * from  vtiger_potential where potentialid=?";
+		$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;
+	return $parent_module ." : ".$parent_name;
}
-?>
\ No newline at end of file
+?>