Email Queries, SES & Certificates: Query — Driver Events

Driver Events

The following parameters apply if the Event Table "Driver" is selected in the mailQuery job configuration.

When the mailQuery job is run, the system date is used as the event "Date" for any new record that are created.

Client Events

The following parameters apply to events if the selected "Event Table" is 'Client' is selected in job configuration.

When the mailQuery job is run, the system date is used as the event "Date" for any new record that are created.

Configurable

• eventclientid (Mandatory) — The client id the event should be assigned to automatic
• event_value (Optional) — Value for the Event Value field NULL
• event_description (Optional) — Value for the Event Description field Selected posting class description
• eventactionid (Optional) — Value for the Event Action field None
• eventduedate (Optional) — Value for the Event Due Date field NULL
• eventlastedit (Optional) — Last Edit
• eventuserid_edit (Optional) — You can over-write this record with a particular user's record, so their name will be displayed in the Clients / Events tab and the event will display in their KPI's.
• eventcompletedflag (Optional) — The default value of 'yes' is populated into this field. This means the "Completed" check box of the event will be checked on creation. yes

Not Configurable

• attachment_id (Not available) — This will be populated if a report attachment is created, otherwise it will be 'NULL'. NULL

Event Validation

The "Event Type" field is blank for new records, if you forget to select this or remove it from an existing job and the "Create Event" check box is selected, the following warning will appear;

Scheduler behaviour where ‘Auto send emails’ = ‘OFF’

‘Create Event’ flag OFF

Under this setting, the expectation is the following;

• Emails generated from the query are allocated a batch_no,
• Sent to the Mail Queue with ‘pending’ status, and
• No driver event is created.
  It will then be up to the User to select the created batch_no and manually send the emails from the Mail Queue. Once complete, the status of the batch will be updated to ‘sent’.

‘Create Event’ flag ON

Under this setting, the expectation is the following;

• Emails generated from the query are allocated a batch_no,
• Sent to the Mail Queue with ‘pending’ status, and
• One driver event is created with icon
  Email will not be able to be sent from the Drivers/Event tab as it is in ‘pending’ status. The ‘Send’ option will disappear.

Mail Query for Channels

Mail queries can be set up to send channel specific content. The two types of channel specific content are:

• Channel specific logo on Office(xls) report attachments
• Channel specific content for the subject and body of the email

To use the channel specific logo on an Office(xls) attachment simply include the field ** in the SELECT part of the mail query. To the left of attachment if you don't want this field on the report.

To use channel specific mail content from gbtemplates simply include channelid in the table join to gb_templates.

Warning: If gbtemplates records have been set up for channels you MUST include channelid in the table join otherwise you will get duplicate mail

Example:

SELECT t1.contract_id AS mail_group_by, t2.home_email AS recipient_1_to_address, t4.mail_sender_name AS sender_name, t4.mail_sender_address AS sender_address, t4.mail_subject AS subject, t4.mail_message AS message, t4.mail_message_format as message_format, t1.contract_id AS event_contract_id, 'Email to driver' as event_description, t1.contract_id, t1.reg_no, t1.description, t2.given_name AS first_name, DATE_FORMAT(t1.reg_renewal, '%D %M %Y') AS 'reg_renewal', t1.contract_id AS contract, t1.reg_no AS registration, t1.description AS contract_description, t2.given_name,-- Note: Assuming the template_id is itself linked to gb_template_attachments, those fixed attachments will be included in the outgoing email t4.template_id as template_id_attachments,-- Note: channel_id only needs to be included when an Office(xls) report attachment is being created and you want it to use a channel specific logo t3.channel_id,-- Note: Each attachment produced by the mailQuery can be named by including attachment_name in the mailQuery. CONCAT('Your Reg is: ',t1.reg_no) as attachment_name,-- Report attachment fields start here '' AS _attachment_, t3.client_id AS client_id, t1.reg_no AS reg_no, t1.contract_start AS contract_startFROM fm_contracts AS t1INNER JOIN fm_drivers AS t2 ON t2.driver_id = t1.driver_idINNER JOIN fm_clients AS t3 ON t3.client_id = t1.client_id-- Note: channel_id must be included in the join condition if additional gb_templates records exist for channels otherwise the mail will double upINNER JOIN gb_templates AS t4 ON t4.name = 'pdf_contract_event_email_message' AND t4.sub_name = 'REGDUE' AND t4.channel_id = t3.channel_idWHERE t1.reg_renewal= DATE_ADD(CURDATE, INTERVAL 45 DAY)AND t2.home_email != ''AND (t1.suspend_date = '0000-00-00' OR t1.suspend_date > DATE_ADD(CURDATE, INTERVAL 45 DAY))ORDER BY t2.driver_id ASC;

System Errors

Details about error code and its troubleshooting information.

Troubleshooting

Tip: Visit the main Troubleshooting page for a list of all the available problem-solving tips.