Hi Elton,
I’m not trying to bug you with repeated questions about this, but I’ve discovered something which could be pertinent: the emailing process works currently and correctly attaches the report to the email if it is not a “staged” report. When it is a staged report, it returns a very tiny reportResult object. If I want to attach one of each (a staged and a non-staged report), it properly attaches the non-staged report but not the staged report.
Therefore, I think it’s possible that the staging process doesn’t have time to finish before the report is being generated. Here is the pertinent function.
function ProcessGenericEmail(five, userKey, emailPayload, reports, groups, result) {
// reports is an array of selectedReports: [{ReportID: xxx, IsFiltered: true|false},...]
// groups is an array of selectedGroups: [{StudyGroupKey: xxx, StudyGroupName: xxx},...]
let phase = 'starting generic';
try {
five.log(`ProcessGenericEmail: UserKey=${userKey}`);
// verify needed information
const hasReports = Array.isArray(reports) && reports.length > 0;
const hasGroups = Array.isArray(groups) && groups.length > 0;
// collect selected reports
phase = 'generating reports';
const attachments = [];
for (let i = 0; i < reports.length; i++) {
const rep = reports[i];
const reportID = rep.ReportID;
const runKey = five.uuid();
const reportSettings = JSON.parse(five.getVariable('ReportSettings') || '{}');
const mySettings = reportSettings[reportID] || {};
// exit if any reports need groups and no groups are selected
const needGroups = mySettings.NeedsGroups === true || mySettings.NeedsGroups === 'true' || mySettings.NeedsGroups === 1;
if (needGroups && !hasGroups) {
return five.createError(`You didn't select any groups for report: ${reportID}`);
}
// see if we need to stage the report before generating
const isStaged = mySettings.Stage === true || mySettings.Stage === 'true' || mySettings.Stage === 1;
five.log(`GenerateEmailsServer: isStaged=${isStaged}`);
if (isStaged) {
// stage report and verify count
const stageTable = 'Stage' + reportID;
five.log(`Staging report: ${reportID}`);
StageReport(five, userKey, runKey, reportID);
const qc = five.executeQuery(`SELECT COUNT(*) AS Cnt FROM \`${stageTable}\` WHERE UserKey = ? AND RunKey = ?`, 0, userKey, runKey);
const rows = (qc && (qc.values || qc.rows)) || [];
const row = rows[0] || {};
const cnt = Number(row.Cnt ?? 0);
five.log(`${stageTable} count for runKey=${runKey}: ${cnt}`);
}
// set variables and generate report
five.setVariable('RunKey', runKey);
five.setVariable('UserKey', userKey);
five.log(`Vars before report: UserKey=${five.getVariable('UserKey')}, RunKey=${five.getVariable('RunKey')}`);
const rr = five.executeAction(reportID, { UserKey: userKey, RunKey: runKey });
if (rr && rr.isOk && !rr.isOk()) {
return five.createError(`Report failed: ${reportID}`);
}
if (rr && rr.report) attachments.push(rr.report);
const ok = (rr && typeof rr.isOk === 'function') ? rr.isOk() : null;
five.log(`UserKey=${userKey}, RunKey=${runKey}, Report ${reportID}: ok=${ok}, keys=${Object.keys(rr||{}).join(',')}`);
const r = rr && rr.report ? String(rr.report) : '';
five.log(`UserKey=${userKey}, RunKey=${runKey}, report=${reportID}, report prefix=${r.substring(0, 40)}, len=${r.length}`);
}
five.log(`Attachments generated: ${attachments.length}`);
phase = 'resolving email addresses';
const sendTo = emailPayload.To || '';
const ccTo = emailPayload.Cc || '';
const bccTo = emailPayload.Bcc || '';
const fiveSendTo = ParseEmailList(five, sendTo);
if (!fiveSendTo || fiveSendTo.length === 0) {
return five.createError('No valid TO email addresses were found.');
}
const fiveCcTo = ParseEmailList(five, ccTo);
const fiveBccTo = ParseEmailList(five, bccTo);
const subject = emailPayload.Subject || '';
const body = emailPayload.Body || '';
phase = 'sending email';
const mailContext = {
SMTPAddresses: fiveSendTo,
SMTPCcAddresses: fiveCcTo,
SMTPBccAddresses: fiveBccTo,
SMTPSubject: subject,
SMTPBody: body
};
five.log(`GenerateEmailsServer: ${attachments.length} attachments`);
if (attachments.length > 0) {
mailContext.SMTPAttachments = attachments;
}
five.log(`Sending GENERIC email to: ${sendTo}`);
const mailResult = five.executeAction('ReportEmail', mailContext);
if (mailResult && mailResult.isOk && !mailResult.isOk()) {
return five.createError(mailResult);
}
// build success envelope
result = five.success(result);
result.Code = five.ErrErrorOk;
result.Message = ''; //`Email sent (${attachments.length} attachments)`;
result.Results = JSON.stringify({
EmailType: 'GENERIC',
ToCount: fiveSendTo.length,
CcCount: fiveCcTo.length,
BccCount: fiveBccTo.length,
ReportCount: reports.length,
AttachmentCount: attachments.length,
Sent: true
});
five.log(`ProcessGenericEmail: result=${JSON.stringify(result)}`);
return result;
} catch (e) {
five.log(`ProcessGenericEmail ERROR during "${phase}": ${e.message}`);
return five.createError(`Error during "${phase}": ${e.message}`);
}
}
Here is the StageReport function called from the above function:
function StageReport(five, userKey, runKey, reportID) {
const rid = String(reportID || '').trim();
if (!/^[A-Za-z0-9_]+$/.test(rid)) {
throw new Error(`StageReport: invalid reportID "${rid}"`);
}
const stageTable = `Stage${reportID}`;
const stageQuery = `rqStage${reportID}`;
let phase = 'clearing prior records for this runKey';
let sql = `DELETE FROM \`${stageTable}\` WHERE \`UserKey\` = ? AND \`RunKey\` = ?`;
let qr = five.executeQuery(sql, 0, userKey, runKey);
if (qr && qr.isOk && !qr.isOk()) {
const err =
(typeof qr.errorMessage === 'function' && qr.errorMessage()) ||
qr.error || qr.message || JSON.stringify(qr);
throw new Error(`StageReport: delete failed ${phase}: (${stageTable}): ${err}`);
}
// stage rows for the report
phase = 'staging report data';
five.log(`StageReport: phase=${phase}, qry=${stageQuery}`);
qr = five.executeQuery(stageQuery, 0, userKey, runKey, userKey, userKey);
if (qr && qr.isOk && !qr.isOk()) {
const err =
(typeof qr.errorMessage === 'function' && qr.errorMessage()) ||
qr.error || qr.message || JSON.stringify(qr);
throw new Error(`StageReport: stage query failed ${phase}: (${stageQuery}): ${err}`);
}
}
Is it possible that the stage table is not done being filled yet?
for the on-demand reports, the server function is called to do the staging and in the callback function (which I believe guarantees that the server function completed), the executeAction for the report is called.