I use this a lot in my CloverETL graphs to send output to end users.
Most often, I format a table and then send it as the content of an email message.
I have attached a simple example graph that will send me the output of the DataGenerator. It uses record functions to get the datatype and the names of the input fields, so it should work with any metadata.
<?xml version="1.0" encoding="UTF-8"?>
<Graph author="phb05" created="Mon Nov 06 10:52:51 EST 2017" guiVersion="4.1.0" id="1509983652265" licenseCode="CLP1DHEALT49098262BY" licenseType="Commercial" modified="Tue Nov 07 13:29:27 EST 2017" modifiedBy="phb05" name="RecordFunctions" revision="1.41" showComponentDetails="true">
<Global>
<Metadata id="Metadata0" previewAttachmentCharset="ISO-8859-1">
<Record fieldDelimiter="|" name="metadata" previewAttachmentCharset="ISO-8859-1" recordDelimiter="\r\n" type="delimited">
<attr name="description"><![CDATA[Description of Metadata]]></attr>
<Field format="###,####.##" length="12" name="field1" scale="2" trim="true" type="decimal"/>
<Field format="dd.MM.yyyy" name="field2" trim="true" type="date"/>
<Field name="field3" type="string"/>
<Field label="Label" name="field4" type="string"/>
<Field name="field5" type="decimal"/>
</Record>
</Metadata>
<Metadata id="Metadata1" previewAttachmentCharset="ISO-8859-1">
<Record fieldDelimiter="|" name="outputStream" previewAttachmentCharset="ISO-8859-1" recordDelimiter="\r\n" type="delimited">
<Field name="outputStream" type="string"/>
</Record>
</Metadata>
<GraphParameters>
<GraphParameterFile fileURL="workspace.prm"/>
</GraphParameters>
<Dictionary/>
</Global>
<Phase number="0">
<Node guiName="DataGenerator" guiX="123" guiY="119" id="DATA_GENERATOR" recordsNumber="5" type="DATA_GENERATOR">
<attr name="generate"><![CDATA[//#CTL2
// Generates output record.
function integer generate() {
$out.0.field1 = random();
$out.0.field4 = randomString(3,5);
$out.0.field3 = randomString(3,5);
$out.0.field2 = randomDate(2017-01-01,2017-12-31);
$out.0.field5 = randomLong(0,100);
return ALL;
}
// Called during component initialization.
// function boolean init() {}
// Called during each graph run before the transform is executed. May be used to allocate and initialize resources
// required by the generate. All resources allocated within this method should be released
// by the postExecute() method.
// function void preExecute() {}
// Called only if generate() throws an exception.
// function integer generateOnError(string errorMessage, string stackTrace) {
// }
// Called during each graph run after the entire transform was executed. Should be used to free any resources
// allocated within the preExecute() method.
// function void postExecute() {}
// Called to return a user-defined error message when an error occurs.
// function string getMessage() {}
]]></attr>
</Node>
<Node groupAccumulatorMetadataId="Metadata1" guiName="Format to HTML" guiX="301" guiY="119" id="FORMAT_TO_HTML" type="ROLLUP">
<attr name="transform"><![CDATA[//#CTL2
integer columncounter = 0;
string outputString = "";
// Called for the first data record in a new group. Starts the parsing of the new group.
// This will collect the column headings and the meta data description
function void initGroup(outputStream groupAccumulator) {
// The metadata description is stored in the record properties.
map[string, string] properties = getRecordProperties($in.0);
string[] listOfKeys = getKeys(properties );
// If there is no description, fill the value
outputString = "<h2>" + nvl(properties["description"],"metadata description" ) +"</h2>";
// start the table
outputString = outputString + "<table> <tr>";
// Now get the column headings
for(columncounter = 0; columncounter < length($in.0); columncounter++)
{
outputString = outputString + "<th>"+nvl(getFieldLabel($in.0,columncounter),getFieldName($in.0,columncounter))+"</th>";
}
outputString = outputString + " </tr>";
} // end initGroup
// Called for each data record in the group (including the first one and the last one).
// Implicitly returns false => updateTransform() is not called. When returns true, calls updateTransform().
function boolean updateGroup(outputStream groupAccumulator) {
return true;
}
// Called for the last data records in all groups sequentially, but only after all incoming data records have been parsed.
// Implicitly returns true => transform() is called for the whole group.
function boolean finishGroup(outputStream groupAccumulator) {
return true;
}
// Called to transform data records that have been parsed so far into user-specified number of output data record(s).
// Counter (incremented by 1 starting from 0) stores the number of previous calls to this method for the current group update.
// Group accumulator can optionally be used.
// Function implicitly returns SKIP to skip sending any data records to output.
// Returning ALL causes each data record to be sent to all output port(s).
// Can also return a number of the output port to which individual data record should be sent.
function integer updateTransform(integer counter, outputStream groupAccumulator) {
outputString = outputString + "<tr>";
for(columncounter = 0; columncounter < length($in.0); columncounter++)
{
if (isNull($in.0,columncounter))
{
outputString = outputString + "<td/>";
}
else
if (getFieldType($in.0, columncounter) == "date") //format the date
{
outputString = outputString + "<td>"+ date2str(getDateValue($in.0, columncounter),"MM/dd/yyyy") + "</td>";
}
else
{
outputString = outputString + "<td>"+getValueAsString($in.0,columncounter)+"</td>";
}
} // end for next
outputString = outputString + "</tr>";
return SKIP;
}
// Called to transform the whole group of incoming data record(s) into user-specified number of output data record(s).
// Counter (incremented by 1 starting from 0) stores the number of previous calls to this method for the current group update.
// Group accumulator can optionally be used.
// Function implicitly returns SKIP to skip sending any data records to output.
// Returning ALL causes each data record to be sent to all output port(s).
// Can also return a number of the output port to which individual data record should be sent.
function integer transform(integer counter, outputStream groupAccumulator) {
// only a single output data record will be generated
if (counter > 0)
{
return SKIP;
}
outputString = outputString + "</table>";
$out.0.0 = outputString;
return ALL;
}
// Called during component initialization.
// function void init() {}
// Called during each graph run before the transform is executed. May be used to allocate and initialize resources
// required by the transform. All resources allocated within this method should be released
// by the postExecute() method.
// function void preExecute() {}
// Called only if initGroup(DataRecord) throws an exception.
//function void initGroupOnError(string errorMessage, string stackTrace, recordName1 groupAccumulator) {
//}
// Called only if updateGroup(DataRecord) throws an exception.
//function boolean updateGroupOnError(string errorMessage, string stackTrace, recordName1 groupAccumulator) {
//}
// Called only if finishGroup(DataRecord) throws an exception.
//function boolean finishGroupOnError(string errorMessage, string stackTrace, recordName1 groupAccumulator) {
//}
// Called only if updateTransform(integer, DataRecord) throws an exception.
//function integer updateTransformOnError(string errorMessage, string stackTrace, integer counter, recordName1 groupAccumulator) {
//}
// Called only if transform(integer, DataRecord) throws an exception.
//function integer transformOnError(string errorMessage, string stackTrace, integer counter, recordName1 groupAccumulator) {
//}
// Called during each graph run after the entire transform was executed. Should be used to free any resources
// allocated within the preExecute() method.
// function void postExecute() {}
// Called to return a user-defined error message when an error occurs.
// function string getMessage() {}
]]></attr>
<attr name="guiDescription"><![CDATA[Use the metadata description for column headings]]></attr>
</Node>
<Edge debugMode="true" fromNode="DATA_GENERATOR:0" guiBendpoints="" guiRouter="Manhattan" id="Edge0" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (out)" toNode="FORMAT_TO_HTML:0"/>
<Edge debugMode="true" fromNode="FORMAT_TO_HTML:0" guiBendpoints="" guiRouter="Manhattan" id="Edge2" inPort="Port 0 (Body port)" metadata="Metadata1" outPort="Port 0 (out)" toNode="MERGE_THE_HTML_STREAMS1:0"/>
</Phase>
<Phase number="51">
<Node createEmptyFiles="false" fileURL="port:0.outputStream:discrete" footer="<footer> </br> ${SANDBOX_CODE}::${JOB_FILE} <br> ${EVENT_SCHEDULE_DESCRIPTION} </footer> </html>" guiName="Merge the html streams" guiX="482" guiY="119" header="<html> <head> <style> table, th, td { border: 1px solid black; border-collapse: collapse; padding: 15px; } </style> </head> " id="MERGE_THE_HTML_STREAMS1" mask="$outputStream" type="STRUCTURE_WRITER">
<attr name="guiDescription"><![CDATA[Add HTML formatting and any styles]]></attr>
</Node>
<Node guiName="paul.bartosik@healthresearch.org" guiX="684" guiY="119" id="PAUL_BARTOSIK_HEALTHRESEARCH_ORG2" message="MessageBody=<html>\r\n<head>\r\n<style>\r\ntable, th, td {\r\n border: 1px solid black;\r\n border-collapse: collapse;\r\n padding: 15px;\r\n}\r\n</style>\r\n</head>\r\n<br/>\r\n$outputStream\r\n</html> Subject=Meta data to table To=paul.bartosik@healthresearch.org From=hri-support@healthresearch.org " smtpPort="25" smtpServer="neptune.health.state.ny.us" type="EMAIL_SENDER"/>
<Edge fromNode="MERGE_THE_HTML_STREAMS1:0" guiBendpoints="" guiRouter="Manhattan" id="Edge39" inPort="Port 0 (in)" metadata="Metadata1" outPort="Port 0 (out)" toNode="PAUL_BARTOSIK_HEALTHRESEARCH_ORG2:0"/>
</Phase>
</Graph>
Most often, I format a table and then send it as the content of an email message.
I have attached a simple example graph that will send me the output of the DataGenerator. It uses record functions to get the datatype and the names of the input fields, so it should work with any metadata.
<?xml version="1.0" encoding="UTF-8"?>
<Graph author="phb05" created="Mon Nov 06 10:52:51 EST 2017" guiVersion="4.1.0" id="1509983652265" licenseCode="CLP1DHEALT49098262BY" licenseType="Commercial" modified="Tue Nov 07 13:29:27 EST 2017" modifiedBy="phb05" name="RecordFunctions" revision="1.41" showComponentDetails="true">
<Global>
<Metadata id="Metadata0" previewAttachmentCharset="ISO-8859-1">
<Record fieldDelimiter="|" name="metadata" previewAttachmentCharset="ISO-8859-1" recordDelimiter="\r\n" type="delimited">
<attr name="description"><![CDATA[Description of Metadata]]></attr>
<Field format="###,####.##" length="12" name="field1" scale="2" trim="true" type="decimal"/>
<Field format="dd.MM.yyyy" name="field2" trim="true" type="date"/>
<Field name="field3" type="string"/>
<Field label="Label" name="field4" type="string"/>
<Field name="field5" type="decimal"/>
</Record>
</Metadata>
<Metadata id="Metadata1" previewAttachmentCharset="ISO-8859-1">
<Record fieldDelimiter="|" name="outputStream" previewAttachmentCharset="ISO-8859-1" recordDelimiter="\r\n" type="delimited">
<Field name="outputStream" type="string"/>
</Record>
</Metadata>
<GraphParameters>
<GraphParameterFile fileURL="workspace.prm"/>
</GraphParameters>
<Dictionary/>
</Global>
<Phase number="0">
<Node guiName="DataGenerator" guiX="123" guiY="119" id="DATA_GENERATOR" recordsNumber="5" type="DATA_GENERATOR">
<attr name="generate"><![CDATA[//#CTL2
// Generates output record.
function integer generate() {
$out.0.field1 = random();
$out.0.field4 = randomString(3,5);
$out.0.field3 = randomString(3,5);
$out.0.field2 = randomDate(2017-01-01,2017-12-31);
$out.0.field5 = randomLong(0,100);
return ALL;
}
// Called during component initialization.
// function boolean init() {}
// Called during each graph run before the transform is executed. May be used to allocate and initialize resources
// required by the generate. All resources allocated within this method should be released
// by the postExecute() method.
// function void preExecute() {}
// Called only if generate() throws an exception.
// function integer generateOnError(string errorMessage, string stackTrace) {
// }
// Called during each graph run after the entire transform was executed. Should be used to free any resources
// allocated within the preExecute() method.
// function void postExecute() {}
// Called to return a user-defined error message when an error occurs.
// function string getMessage() {}
]]></attr>
</Node>
<Node groupAccumulatorMetadataId="Metadata1" guiName="Format to HTML" guiX="301" guiY="119" id="FORMAT_TO_HTML" type="ROLLUP">
<attr name="transform"><![CDATA[//#CTL2
integer columncounter = 0;
string outputString = "";
// Called for the first data record in a new group. Starts the parsing of the new group.
// This will collect the column headings and the meta data description
function void initGroup(outputStream groupAccumulator) {
// The metadata description is stored in the record properties.
map[string, string] properties = getRecordProperties($in.0);
string[] listOfKeys = getKeys(properties );
// If there is no description, fill the value
outputString = "<h2>" + nvl(properties["description"],"metadata description" ) +"</h2>";
// start the table
outputString = outputString + "<table> <tr>";
// Now get the column headings
for(columncounter = 0; columncounter < length($in.0); columncounter++)
{
outputString = outputString + "<th>"+nvl(getFieldLabel($in.0,columncounter),getFieldName($in.0,columncounter))+"</th>";
}
outputString = outputString + " </tr>";
} // end initGroup
// Called for each data record in the group (including the first one and the last one).
// Implicitly returns false => updateTransform() is not called. When returns true, calls updateTransform().
function boolean updateGroup(outputStream groupAccumulator) {
return true;
}
// Called for the last data records in all groups sequentially, but only after all incoming data records have been parsed.
// Implicitly returns true => transform() is called for the whole group.
function boolean finishGroup(outputStream groupAccumulator) {
return true;
}
// Called to transform data records that have been parsed so far into user-specified number of output data record(s).
// Counter (incremented by 1 starting from 0) stores the number of previous calls to this method for the current group update.
// Group accumulator can optionally be used.
// Function implicitly returns SKIP to skip sending any data records to output.
// Returning ALL causes each data record to be sent to all output port(s).
// Can also return a number of the output port to which individual data record should be sent.
function integer updateTransform(integer counter, outputStream groupAccumulator) {
outputString = outputString + "<tr>";
for(columncounter = 0; columncounter < length($in.0); columncounter++)
{
if (isNull($in.0,columncounter))
{
outputString = outputString + "<td/>";
}
else
if (getFieldType($in.0, columncounter) == "date") //format the date
{
outputString = outputString + "<td>"+ date2str(getDateValue($in.0, columncounter),"MM/dd/yyyy") + "</td>";
}
else
{
outputString = outputString + "<td>"+getValueAsString($in.0,columncounter)+"</td>";
}
} // end for next
outputString = outputString + "</tr>";
return SKIP;
}
// Called to transform the whole group of incoming data record(s) into user-specified number of output data record(s).
// Counter (incremented by 1 starting from 0) stores the number of previous calls to this method for the current group update.
// Group accumulator can optionally be used.
// Function implicitly returns SKIP to skip sending any data records to output.
// Returning ALL causes each data record to be sent to all output port(s).
// Can also return a number of the output port to which individual data record should be sent.
function integer transform(integer counter, outputStream groupAccumulator) {
// only a single output data record will be generated
if (counter > 0)
{
return SKIP;
}
outputString = outputString + "</table>";
$out.0.0 = outputString;
return ALL;
}
// Called during component initialization.
// function void init() {}
// Called during each graph run before the transform is executed. May be used to allocate and initialize resources
// required by the transform. All resources allocated within this method should be released
// by the postExecute() method.
// function void preExecute() {}
// Called only if initGroup(DataRecord) throws an exception.
//function void initGroupOnError(string errorMessage, string stackTrace, recordName1 groupAccumulator) {
//}
// Called only if updateGroup(DataRecord) throws an exception.
//function boolean updateGroupOnError(string errorMessage, string stackTrace, recordName1 groupAccumulator) {
//}
// Called only if finishGroup(DataRecord) throws an exception.
//function boolean finishGroupOnError(string errorMessage, string stackTrace, recordName1 groupAccumulator) {
//}
// Called only if updateTransform(integer, DataRecord) throws an exception.
//function integer updateTransformOnError(string errorMessage, string stackTrace, integer counter, recordName1 groupAccumulator) {
//}
// Called only if transform(integer, DataRecord) throws an exception.
//function integer transformOnError(string errorMessage, string stackTrace, integer counter, recordName1 groupAccumulator) {
//}
// Called during each graph run after the entire transform was executed. Should be used to free any resources
// allocated within the preExecute() method.
// function void postExecute() {}
// Called to return a user-defined error message when an error occurs.
// function string getMessage() {}
]]></attr>
<attr name="guiDescription"><![CDATA[Use the metadata description for column headings]]></attr>
</Node>
<Edge debugMode="true" fromNode="DATA_GENERATOR:0" guiBendpoints="" guiRouter="Manhattan" id="Edge0" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (out)" toNode="FORMAT_TO_HTML:0"/>
<Edge debugMode="true" fromNode="FORMAT_TO_HTML:0" guiBendpoints="" guiRouter="Manhattan" id="Edge2" inPort="Port 0 (Body port)" metadata="Metadata1" outPort="Port 0 (out)" toNode="MERGE_THE_HTML_STREAMS1:0"/>
</Phase>
<Phase number="51">
<Node createEmptyFiles="false" fileURL="port:0.outputStream:discrete" footer="<footer> </br> ${SANDBOX_CODE}::${JOB_FILE} <br> ${EVENT_SCHEDULE_DESCRIPTION} </footer> </html>" guiName="Merge the html streams" guiX="482" guiY="119" header="<html> <head> <style> table, th, td { border: 1px solid black; border-collapse: collapse; padding: 15px; } </style> </head> " id="MERGE_THE_HTML_STREAMS1" mask="$outputStream" type="STRUCTURE_WRITER">
<attr name="guiDescription"><![CDATA[Add HTML formatting and any styles]]></attr>
</Node>
<Node guiName="paul.bartosik@healthresearch.org" guiX="684" guiY="119" id="PAUL_BARTOSIK_HEALTHRESEARCH_ORG2" message="MessageBody=<html>\r\n<head>\r\n<style>\r\ntable, th, td {\r\n border: 1px solid black;\r\n border-collapse: collapse;\r\n padding: 15px;\r\n}\r\n</style>\r\n</head>\r\n<br/>\r\n$outputStream\r\n</html> Subject=Meta data to table To=paul.bartosik@healthresearch.org From=hri-support@healthresearch.org " smtpPort="25" smtpServer="neptune.health.state.ny.us" type="EMAIL_SENDER"/>
<Edge fromNode="MERGE_THE_HTML_STREAMS1:0" guiBendpoints="" guiRouter="Manhattan" id="Edge39" inPort="Port 0 (in)" metadata="Metadata1" outPort="Port 0 (out)" toNode="PAUL_BARTOSIK_HEALTHRESEARCH_ORG2:0"/>
</Phase>
</Graph>
Please sign in to leave a comment.
Comments 1