Best practice: Mails (same mail via BCC to several users, status mail once per day)

[Blama]

Hi Isomorphic,

I'm looking for some best practices regarding mails:

1. I understood your "1 response row --> 1 mail" principle. If my mail is always the same and I want to BCC it to several addresses, I'll have to LISTAGG the addresses to one DSField in my SQL, right? Or is there another way using SmartGWT facilities?
2. I really like the ease of setup and design possibilities for mails. Impressive. With it, I'm able to react to user action (e.g. "add entry").
   Is it currently possible to use your mail system for daily automated mail (e.g. "send a list of all employees to manager every day at 8am.") when nobody is using the system ("a SmartGWT-mail cron-job")? Or do I have to use e.g. Oracle database jobs (and their mail system)?
3. 1. My query generating the mail data is slow and will stay that way. The mails are generated on user action and the UI waits for the response to arrive. Is there some way commit, return the normal response (user can continue working) and have the mails generated asynchronously?
   2. If not, is it possible to display a progress indicator ("3/11 mails sent")?
4. Do you have a syntax recommendation for mail addresses? Of course, the address has to be valid. But what about the real name to the address, like
   1. john.odoe@example.test
   2. 'John O Doe' <john.odoe@example.test>
   3. 'John O'Doe' <john.odoe@example.test>
   4. 'John O''Doe' <john.odoe@example.test>
   5. "John "The Unknown" O'Doe" <"john.odoe@example.test">
   6. ...
   
   If there is some limitation, I'd like to regex-constraint the user names according to it.

Thank you & Best regards,
Blama

[Isomorphic]

1. If your CC / BCC lists are static (ie, you always copy the same people), you can just embed those addresses in your mail template. If they vary with each email then, yes, you will need to assemble a comma-separated list somehow

2. You can run any DataSource operation (including operations that send email, of course) from arbitrary standalone Java code. Or, if you aren't scheduling these daily emails from Java code that can directly call a DSRequest, you can run any DataSource operation from any system that can send a REST request

3. Your best option here would be to spin the slow query off as a standalone DSRequest, as mentioned above. We do have some built-in support for the Quartz job scheduler that may help in this area, but it is not fully productized - please email us directly if you are interested in sponsoring further development of this support

Thanks,
Isomorphic Software Support

[Blama]

Hi Isomorphic,

thanks for the answers, very helpful.

Regarding 2+3: The following should be possible, shouldn't it?

1. I'll create a DB-entry as "backlog" for every action where I'll need to write a mail (allowing the request to finish fast and un-block the UI).
2. A servlet will check the (short) backlog with normal SmartGWT DSRequests and call the write-mail-operation binding.
3. The servlet will be hit by a cron job executing a wget to the servlet.
4. After sending the mail, the backlog entry will be removed.

Regarding mails that use multiple resultset-rows for one mail:
I'll need to write some aggregating mails (e.g: "This happened since our last mail"). I suppose this is where I need multiple=false for.

Could you give pointers how a template for this should look like? I'd like to do this:

1. Take the mail-address from the 1st row only (as it will be the same always)
2. Compose mail with a table (html-mail with many <tr>) as contents, where every result row is used as table-row.

Is that all done with velocity?

Thank you & Best regards,
Blama

[Isomorphic]

Yes, your approach to an email backlog seems reasonable - hitting a servlet from a cron job gives you a nice mechanism for altering the timing and frequency of email runs without changing any program logic.

The "multiple" flag will not really do what you need here - it is a simple flag you can use when the email you want to send is not directly related to the resultset you just fetched, and all it does is trim the resultset presented to the email system down to just the first record.

In your case, what you will need to do is have the operation handled by a DMI that first executes the SQL or Hibernate or whatever operation to get the multi-record dataset, and then transforms that dataset into a single record with multiple sub-records. These sub-records can then be accessed in your email template by use of the Velocity #foreach directive. Your DMI would look something like:

public DSResponse sendmail(DSRequest req) {
    DSResponse resp = req.execute();
    List dataList = resp.getDataList();
    Map newData = new HashMap();
    // Possibly assign some properties from record 0 of the dataList here, 
    // or build up a CC list, etc
    List<Map> subRecords = new ArrayList<Map>();
    for (int i = 0; i < dataList.size(); i++) {
        Map record = (Map)dataList.get(i);
        Map subrec = new HashMap();
        // Assign subrec values from record here
        subRecords.add(subrec);
    }
    newData.put("subRecords", subRecords);
    resp.setData(newData);
    return resp;
}

[Blama]

Hi Isomorphic,

thank you very much. That would have been hard to get right by trial and error.

Best regards,
Blama

[Blama]

Hi Isomorphic,

I did as suggested, but I'm hitting problems here.

Setup: ds.xml:

<operationBinding operationType="fetch" operationId="myId" serverMethod="myMethod"
	requiresRole="serverOnly">
	<mail contentType="text/html">
		<from>$customerSettings.customerMailFrom</from>
		<to>$userMailContent.userEmailEntry</to>
		<bcc>$customerSettings.customerMailBCC</bcc>
		<subject>subj</subject>
		<templateFile>mails/myMail.html</templateFile>
	</mail>
</operationBinding>

This works, the serverMethod is hit. It looks like your code, but leads to the following problems.

• The second your request.execute() returns, the mail-writing starts.
  The needed context (see below) is available, but it is not possible to transform the data before.
• To work around this, I did this
  

  DSRequest intermediateRequest = new DSRequest("SAME_DS", DataSource.OP_FETCH);
  intermediateRequest.setSortBy(request.getSortByFields());
  intermediateRequest.setAdvancedCriteria(request.getAdvancedCriteria());
  intermediateRequest.setOperationId(request.getOperationId() + "Data"); //Simple fetch-operationBinding with no <mail>-tag
  DSResponse intermediateResult = intermediateRequest.execute();

  I then transformed the result as suggested and return with return intermediateResult.setData(newData);
  
• Now the correct resultSet is returned to the builtin mailWriter, but my requestContext is somehow missing.
  The $customerSettings-variables used in the templatefile and in the operationBinding are filled by my SQLDataSource-subclass:
  

  @Override
  public DSResponse executeFetch(DSRequest request) throws Exception {
  	request.addToTemplateContext("esc", new EscapeTool());
  	request.addToTemplateContext("customerSettings", new CustomerSettings());
  	return super.executeFetch(request);
  }

  This is hit for intermediateRequest, so intermediateRequest has the objects in its context. Nevertheless the replacement fails.

I think the error is that the framework wants to directly write the mails.
I'd be also OK if the requestContext of intermediateRequest would be available for the mails, but I think the former is the way to go.

Best regards,
Blama

[Blama]

Hi Isomorphic,

I tried to cheat my data into the result another way (making sure that the base request does not return rows, execute it, then assemble my dataset and return it), but this does not work, either.

In the end it seems to be a bug with the point of time mail writing is started.
(I saw that there is RequestContext context member variable in DSRequest, but as it is not javadocd and has no getter/setter, I'll stop here.)

Could you look into this?

Thank you & Best regards,
Blama

[Blama]

Hi Isomorphic,

did you already have the chance to look into this one?

Thank you,
Blama

[Isomorphic]

Hi Blama
Sorry for the delay responding - we have someone looking into this and we will follow up soon.

Regards
Isomorphic Software

[Isomorphic]

We don't reproduce this in a similar local test - both the "intermediate request" and the request sent up from the client see the templateContext changes (though the intermediate request does not need to - it is only fetching data, the Velocity templating is done when the mail is sent). Please try our test - if it works for you, it seems like the issue is with whatever code populates the templateContext.

DMI:

package com.isomorphic.test;

import javax.servlet.http.HttpSession;

import com.isomorphic.datasource.DSRequest;
import com.isomorphic.datasource.DSResponse;

public class TestMail {

    public DSResponse mail(DSRequest dsRequest) throws Exception {
        DSRequest subRequest = new DSRequest("testEmail","fetch");
        subRequest.addToTemplateContext("send_to", "whatever@isomorphic.com");
        dsRequest.addToTemplateContext("send_to", "whatever@isomorphic.com");
        DSResponse resp = new DSResponse();
        resp.setData(subRequest.execute().getData());
        return resp;
    }	
}

DataSource:

<DataSource ID="testEmail" tableName="worldDS" serverType="sql" autoDeriveSchema="true">

<operationBindings>
<operationBinding operationType="fetch" operationId="mail">
    <serverObject lookupStyle="new" className="com.isomorphic.test.TestMail" methodName="mail"/>
    <mail contentType="text/html">
        <from>blah@isomorphic.com</from>
		<to>$send_to</to>
		<subject>Hello there!</subject>
		<messageTemplate>Hello $send_to!  How are things today?</messageTemplate>
	</mail>
</operationBinding>
</operationBindings>

</DataSource>

[Blama]

Hello Isomorphic,

your testcase works for me. With its help I was able to find the problem. A I wrote in #6 I add to the templateContext in my SQLDataSource subclass. But as the incoming request in the DMI is never executed, it does not hit MySQLDataSource.fetch(). So when returning the intermediate request data, the templateContext is missing. So your "dsRequest.addToTemplateContext("send_to", "whatever@isomorphic.com");" did the trick.

For future reference for other users here a builtInDS-based example for sending aggregating mails with modified data:

server.properties:

mail.system.mail.smtp.host: mail.mydomain.com
mail.system.mail.smtp.port: 587
mail.system.mail.smtp.auth: true
mail.system.mail.smtp.user: noreply.test@mydomain.com
mail.system.mail.smtp.password: pwd
mail.system.mail.smtp.starttls.enable: true

Animals.java:

package com.smartgwt.sample.server.listener;

import com.isomorphic.datasource.DSRequest;
import com.isomorphic.datasource.DSResponse;

public class Animals {
	public DSResponse mail(DSRequest dsRequest) throws Exception {
		DSRequest subRequest = new DSRequest("employees", "fetch");
		// subRequest.addToTemplateContext("send_to", "someuser@mydomain.com");
		dsRequest.addToTemplateContext("send_to", "someuser@mydomain.com");
		DSResponse resp = new DSResponse();
		resp.setData(subRequest.execute().getData());
		return resp;
	}
}

animals.ds.xml addition

	<operationBindings>
		<operationBinding operationType="fetch" operationId="mail">
			<serverObject lookupStyle="new" className="com.smartgwt.sample.server.listener.Animals" methodName="mail" />
			<mail contentType="text/html" multiple="false">
				<from>noreply.test@mydomain.com</from>
				<to>$send_to</to>
				<subject>Hello there!</subject>
				<messageTemplate>Hello $send_to! How are things today?</messageTemplate>
			</mail>
		</operationBinding>
	</operationBindings>

BuiltInDS.java addition::

boundList.setFetchOperation("mail")

Now on display of Animals, a mail is sent for the 1st entry of employees (see multiple="false", which should be removed in a real world scenario).

With the code Isomorphic showed in #4, one can assemble a list of mails, each including many entries (as a list). This inner list can then be processed by Velocity's #foreach directive.

My real-life DMI looks like this:

	public DSResponse writeStatusDatePassed(DSRequest request, HttpServletRequest servletRequest) throws Exception {
		//Needed as the request is never executed and addDefaultTemplateContext is therefore not called automatically
		MySQLDataSource.addDefaultTemplateContext(request);
		
		DSRequest intermediateRequest = new DSRequest(request.getDataSourceName(), request.getOperationType());
		//Important, as a mail is finished, if the user changes. So sort by the user already in the base request.
		intermediateRequest.setSortBy(request.getSortByFields());
		intermediateRequest.setAdvancedCriteria(request.getAdvancedCriteria());
		
		//Make sure to have this operation binding!
		intermediateRequest.setOperationId(request.getOperationId() + "Data");
		DSResponse intermediateResult = intermediateRequest.execute();

		@SuppressWarnings("unchecked")
		List<Object> allEntries = intermediateResult.getDataList();
		List<Map<String, Object>> aggregatedData = new LinkedList<Map<String, Object>>();
		Map<String, Object> userMailRecord = new HashMap<String, Object>();
		List<Map<String, Object>> listPerUser = new LinkedList<Map<String, Object>>();
		Long oldRecipientUserId = null;
		Long currentRecipientUserId = null;
		String oldRecipientFoA = null;
		String oldRecipientFullname = null;
		String oldRecipientEmailEntry = null;
		for (int i = 0; i < allEntries.size(); i++) {
			@SuppressWarnings("unchecked")
			Map<String, Object> currentRecord = (Map<String, Object>) allEntries.get(i);
			try {
				currentRecipientUserId = ((BigDecimal) currentRecord.get("CASEWORKER_USER_ID")).longValueExact();
			} catch (Exception e) {
			}
			String currentRecipientFoA = currentRecord.get("CASEWORKER_FOA").toString();
			String currentRecipientFullname = currentRecord.get("CASEWORKER_FULLNAME").toString();
			String currentRecipientEmailEntry = currentRecord.get("CASEWORKER_EMAILENTRY").toString();

			if (!currentRecipientUserId.equals(oldRecipientUserId) && oldRecipientUserId != null) {
				userMailRecord.put("userFoA", oldRecipientFoA);
				userMailRecord.put("userFullname", oldRecipientFullname);
				userMailRecord.put("userEmailEntry", oldRecipientEmailEntry);
				userMailRecord.put("list", listPerUser);
				aggregatedData.add(userMailRecord);
				userMailRecord = new HashMap<String, Object>();
				leadListPerUser = new LinkedList<Map<String, Object>>();
			}

			// Keep adding
			listPerUser.add(currentRecord);

			// When at the last entry, don't forget to add that to result
			if (i + 1 == allEntries.size()) {
				userMailRecord.put("userFoA", currentRecipientFoA);
				userMailRecord.put("userFullname", currentRecipientFullname);
				userMailRecord.put("userEmailEntry", currentRecipientEmailEntry);
				userMailRecord.put("list", listPerUser);
				aggregatedData.add(userMailRecord);
			} else {
				// Step, not needed for last entry
				oldRecipientUserId = currentRecipientUserId;
				oldRecipientEmailEntry = currentRecipientEmailEntry;
				oldRecipientFoA = currentRecipientFoA;
				oldRecipientFullname = currentRecipientFullname;
			}
		}
		return intermediateResult.setData(aggregatedData);
	}

With it, I can access the data like the following in my mailTemplate:

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
</head>
<body>
	<table cellspacing="0" cellpadding="0" border="0" width="100%">
		<tr>
			<td>
				<table cellspacing="0" cellpadding="10" border="1">
					<tr>
						<td width="660" bgcolor="#FFFFFF" border="0">Hello $!userFoA $!userFullname,<br /> <br />......
						</td>
					</tr>
					<tr>
						<td>
							<table>
								<tr>
									<th>Status</th>
									<th>Name</th>
									...
								</tr>
								#foreach( $entry in $list )
								<tr>
									<td>$entry.STATUS_SHORTNAME</td>
									<td>$entry.CUST_NAME</td>
									...
								</tr>
								#end
							</table>
						</td>
					</tr>
					<tr>
						<td width="660" bgcolor="#FFFFFF" border="0">Myfooter</td>
					</tr>
				</table>
			</td>
		</tr>
	</table>
</body>
</html>

I hope this helps and a "Thank you" to Isomorphic from my side.

Best regards,
Blama