CSV with 1 minute rollup auto emailed via Meta Source

[MaP]

Re: Get roll up in scripted data source

So I've been playing with scripting in a meta source and have been able to print a few values, or print values and times but haven't quite pieced together the whole picture. I am aiming to make a csv report for the past 24 hours of around 6 different points (executed around midnight) with the data rolled up into 1 minute averages. I have already made a custom ftl and an excel report configured.

Some of the random attempts at understanding below (stolen from other forum posts) - ->

var stdDevInput = []
PointValueQuery.query( [ p341.getDataPointWrapper().getId() ], CONTEXT.getTimestamp() - 600000, CONTEXT.getTimestamp(), false, function( value, index ) {
  stdDevInput.push( value.DoubleValue);
});
print(stdDevInput.length);

/*/var d = new Date().getTime();
var endDate = CONTEXT.getTimestamp();
var startDate = endDate - CONTEXT.millisInPast(DAY, 10); //handles DST shenanigans
var results = [];
var callback = function(idValueTime) {
  results.push(idValueTime);};
var pointsToQuery = [ p341.getDataPointWrapper().getId() ];
PointValueQuery.rollupQuery( pointsToQuery, startDate, endDate, callback, LAST, 1, DAY);
//print(results);
//var pointies = DataPointQuery.query('like(name, *Ptot_kW*)&limit(3)');
//var points = DataPointQuery.query("and(like(deviceName,BachmannPLC*),eq(name,Ptot_kW))&limit(5)");
//var values = p341.pointValuesSince(CONTEXT.getTimestamp() - 300000);
//print(values[0].time);
//print(values);
//print(points);

 var values = [];
var valueCallback = function( value, index ) { //function definition in com.infiniteautomation.mango.db.query.PVTQueryCallback
    values.push(value);
  };
for( var point in CONTEXT_POINTS ) {
  PointValueQuery.query( [ p341.getDataPointWrapper().getId() ], 0, CONTEXT.getRuntime(), true, valueCallback);
}
print(values);
print (results);
*/

[MaP]

var endDate = CONTEXT.getTimestamp();
var startDate = endDate - CONTEXT.millisInPast(DAY, 1); //handles DST shenanigans
var results = [];
var callback = function(idValueTime) {
  results.push(idValueTime);};
var pointsToQuery = [ p341.getDataPointWrapper().getId() ];
PointValueQuery.rollupQuery( pointsToQuery, startDate, endDate, callback, AVERAGE, 1, MINUTE);
print(results.length);

seems to be the method for returning the rolled up data for 1 point - Length is 1440 (60 minutes * 24 hours), and results contains the array of values.
Having an array of the timestamps to construct the csv seems like the next logical point. Then the email which may be covered in other posts.
I imagine if the script is executed via cron at midnight this will retrieve the appropriate range.

[MattFox]

This may be of some assistance for the email part.

https://forum.infiniteautomation.com/topic/4034/exporting-excel-reports-to-csv/2

This post by Jared shows a way you can pull data via the API into a csv format. You could likely combine this with an http request to get your csv blob, attach it to the email and fire at will... Might need one of the infinite chaps to confirm...
https://forum.infiniteautomation.com/topic/4050/datapoint-values-into-a-var-javascript/9

I'm not near my computer right now so I'm unable to be of further assistance. If nobody else has jumped in to assist when I wake up I'll give you a hand.

Fox

[phildunlap]

Thanks for the good links Fox!

I think if he wants to use a file attachment he'll need to write out to a file to then attach it. One could probably also use a ByteArrayAttachment, something like,

var recipients = ["you@domain.extension"];
var emailContent = new com.serotonin.web.mail.EmailContent(null, 
  "Email body content", com.serotonin.m2m2.Common.UTF8);
var emailAttachment = new com.serotonin.web.mail.EmailAttachment.ByteArrayAttachment(
  "attached.txt", "attached text".getBytes());

emailContent.addAttachment(emailAttachment);
com.serotonin.m2m2.rt.maint.work.EmailWorkItem.queueEmail(recipients, "Attachment test", emailContent, null);

and here's a thread with a simple example of writing out to a file:
https://forum.infiniteautomation.com/topic/4208/creating-files-in-filestore-from-excel-post-process-script

[MattFox]

I should be thanking you Phil! I can definitely use this myself in the near future combined with some other skulduggery I'm pulling hahaha. Hopefully this will give MaP what they need to pull off their task.

[MaP]

Thanks for the assistance!

On another aside, the client has requested the current csv report (non rolled up) include the Timestamp much the same as the Excel Report.

Is there an easy way to include the timestamp in the name of the report?

[MattFox]

Simple, when you add the email attachment, you set the name in the first parameter field.

 var emailAttachment = new com.serotonin.web.mail.EmailAttachment.FileAttachment("Data_nonRolled"+values[values.length-1].time+".csv",toAttach);

Fox

[MaP]

Thanks Matt, I meant through the interface as the current CSV report just utilises the mango standard module.

[MattFox]

I do not believe it is possible.
There was a similar question asked regarding the excel report:
https://forum.infiniteautomation.com/topic/4037/changing-the-excel-report-name

I feel you're better of using the applying the same code as above to give the desired results you want as it's a copy and paste with no fluff,

[MaP]

Thanks again Matt - I was hoping there was some trickery you could somehow put a escape character + concatenate time in report name input.

Sorry if this is a little painful or covered elsewhere - I have little to no java script experience so this is all a little arcane to me. I would have expected you needed to point to the location on the file system to attach the file in an email?

[MattFox]

@map correct, but using Phil's code above:

var emailAttachment = new com.serotonin.web.mail.EmailAttachment.ByteArrayAttachment(
  "attached.txt", "attached text".getBytes());

Means we can be a bit more creative with the 'body' of our file. We just generate the data and turn it into a string and then apply the method above to convert it into binary.
Want some help?

[MaP]

Help is always nice, its nice to struggle with progress but also a point where going in circles becomes painful.

Ahh so the ByteArrayAttachment doesn't create a file on the filesystem but just creates the file on the fly in the meta source and names it "attached.txt, with "attatched text" being the content of that file?

[MattFox]

@map said in CSV with 1 minute rollup auto emailed via Meta Source:

but also a point where going in circles becomes painful.

Cripes I know that all too well....

@map said in CSV with 1 minute rollup auto emailed via Meta Source:

Ahh so the ByteArrayAttachment doesn't create a file on the filesystem but just creates the file on the fly in the meta source and names it "attached.txt, with "attatched text" being the content of that file?

Bingo! Have a cigar, or a scotch, or whatever you kick back with. I'd say you've got the right idea here.

EDIT:
My advice: Test with one row to start with your headers etc and ensure you're happy with the format before going full tilt rising with everything in a given date range.

[MaP]

So far so good. Query works, Email attachment works, and can seperate rows using /r/n. Not sure what variable type is returned by the query but its returned as number,number, so i presume its a string? will Need to transpose over the query to insert /r/n i believe and then construct a corresponding time array for every minute of previous 24 hours for second colomn.

var endDate = CONTEXT.getTimestamp();
var startDate = endDate - CONTEXT.millisInPast(MINUTE, 4); //handles DST shenanigans
var results = [];
var callback = function(idValueTime) {
  results.push(idValueTime);};
var pointsToQuery = [ p341.getDataPointWrapper().getId() ];
PointValueQuery.rollupQuery( [341], startDate, endDate, callback, AVERAGE, 1, MINUTE);
print(results);


var d = new Date();
//ECMA function to convert date to locale string YYYY-MM-DD
var datestring = d.toLocaleDateString();
//Substr fX to make date DD-MM-YYYY time format
var ddmmyyyy = datestring.substr(8,2)+datestring.substr(4,4)+datestring.substr(0,4);
var CSVname = "Performance_"+ddmmyyyy+".csv";
print(CSVname);
/*
var recipients = ["funtimes@live.com"];
var emailContent = new com.serotonin.web.mail.EmailContent(null, 
  "Email body content", com.serotonin.m2m2.Common.UTF8);
var emailAttachment = new com.serotonin.web.mail.EmailAttachment.ByteArrayAttachment(
  "attached"+datestring+.csv", "Head1,Head2\r\n12,22".getBytes());

emailContent.addAttachment(emailAttachment);
com.serotonin.m2m2.rt.maint.work.EmailWorkItem.queueEmail(recipients, "Attachment test", emailContent, null);
*/

[MattFox]

Note you'll want to fire the email after your callback returns so you'd be best to place it inside it otherwise you'll be firing an empty attachment.

var callback = function(idValueTime) {
  results.push(idValueTime);
generateEmail(results);
};
function generateEmail(res)
{
var d = new Date();
//ECMA function to convert date to locale string YYYY-MM-DD
var datestring = d.toLocaleDateString();
//Substr fX to make date DD-MM-YYYY time format
var ddmmyyyy = datestring.substr(8,2)+datestring.substr(4,4)+datestring.substr(0,4);
var CSVname = "Performance_"+ddmmyyyy+".csv";
print(CSVname);
/*
var recipients = ["funtimes@live.com"];
var emailContent = new com.serotonin.web.mail.EmailContent(null, 
  "Email body content", com.serotonin.m2m2.Common.UTF8);
var emailAttachment = new com.serotonin.web.mail.EmailAttachment.ByteArrayAttachment(
  "attached"+datestring+.csv", "Head1,Head2\r\n12,22".getBytes());

emailContent.addAttachment(emailAttachment);
com.serotonin.m2m2.rt.maint.work.EmailWorkItem.queueEmail(recipients, "Attachment test", emailContent, null);
*/
}

I believe that the rolled up point value query just returns an array of all of the values only. I don't think it attached a timestamp. If you want timestamps, we'll have to go via the HTTP Request route and query the API directly to pull a rolled up point values query.

[MaP]

This post is deleted!

[MaP]

this seems to have worked i had trouble using arrays with a ,toString function where the "," was appearing in the final csv string - ie)
Head1,Head2
Val1,time1
,Val2,time2
,Val3,time3

Using Strings seems to ahve gotten the correct output but im worried a string variable might get angry when it contains 1440*7 Values/time

code so far --

//Query to Retrieve and rollup previous 24 Hours Data
var endDate = CONTEXT.getTimestamp();
var startDate = endDate - CONTEXT.millisInPast(MINUTE, 4); //handles DST shenanigans
var results = [];
var callback = function(idValueTime) {
  results.push(idValueTime);};
var pointsToQuery = [ p341.getDataPointWrapper().getId() ];
PointValueQuery.rollupQuery( [341], startDate, endDate, callback, AVERAGE, 1, MINUTE);
print(results);

//Date and time Variables
var d = new Date();
var t = new Date().getTime();
var y = d.getYear()-70;
var m = d.getMonth();
var dayinmonth = d.getDate();
var hours = d.getHours();
//ECMA function to convert date to locale string YYYY-MM-DD
var datestring = d.toLocaleDateString();
//Substr fX to make date DD-MM-YYYY time format
var ddmmyyyy = datestring.substr(8,2)+datestring.substr(4,4)+datestring.substr(0,4);
var CSVname = "Performance_"+ddmmyyyy+".csv";

//Creating DateTime For Array
// 31556926000 mS in Year
// 2629743000 ms in Month
// 86400000 ms in Day
//3600000 ms in an Hour
//print(results[0].toString()+d.toString()+"\r\n");
var coeff = 1000 * 60 * 1;
var date = new Date();
//var TimeAgo = new Date((Math.round((date.getTime()-(hours*3600000)) / coeff) * coeff)+index*60000);

//var csvData =[];
var csvData ="kW,Timestamp\r\n"

results.forEach(function(results,index){
    //csvData[index]=results+","+TimeAgo+"\r\n";
    var TimeAgo = new Date((Math.round((date.getTime()-(hours*3600000)) / coeff) * coeff)+index*60000);
    csvData = csvData+results+","+TimeAgo+"\r\n";
    
});
//csvDataString = csvData.toString();
print(csvData)

[MattFox]

Man you really have gone above and beyond, you're making it rather complex for what you need.

1. go into the user setting in the dashboard and use it to make yourself a token based bearer authentication key.
   https://help.infiniteautomation.com/mango-rest-api-authentication

Next let's incorporate your timeframes into this:

if (!Date.prototype.toISOString) {
  (function() {

    function pad(number) {
      if (number < 10) {
        return '0' + number;
      }
      return number;
    }

    Date.prototype.toISOString = function() {
      return this.getUTCFullYear() +
        '-' + pad(this.getUTCMonth() + 1) +
        '-' + pad(this.getUTCDate()) +
        'T' + pad(this.getUTCHours()) +
        ':' + pad(this.getUTCMinutes()) +
        ':' + pad(this.getUTCSeconds()) +
        '.' + (this.getUTCMilliseconds() / 1000).toFixed(3).slice(2, 5) +
        'Z';
    };

  }());
}

var xid = "internal_mango_uptime_hrs"; //for example this would be your context point XID
function GenerateEmail(data)
{
    var csvStr = "kW,Timestamp\r\n";
   for(var i=0;i<data.length; i++)
   {
      csvStr += data[ i ].value+","+data[ i ].timestamp+"\r\n";
   }
var d = new Date();

//Substr fX to make date DD-MM-YYYY time format
var ddmmyyyy = d.getDate()+"-"+(d.getMonth()+1)+"-"+d.getFullYear();
var CSVname = "Performance_"+ddmmyyyy+".csv";

var recipients = ["funtimes@live.com"];
var emailContent = new com.serotonin.web.mail.EmailContent(null, 
  "Email body content", com.serotonin.m2m2.Common.UTF8);
var emailAttachment = new com.serotonin.web.mail.EmailAttachment.ByteArrayAttachment(
  "attached"+datestring+".csv", csvStr.getBytes());

emailContent.addAttachment(emailAttachment);
com.serotonin.m2m2.rt.maint.work.EmailWorkItem.queueEmail(recipients, "Attachment test", emailContent, null);

}

var to = new Date( CONTEXT.getTimestamp() );
var from = new Date( to.valueOf() - CONTEXT.millisInPast(MINUTE, 4) ); 
var rollup="AVERAGE";
var rollupPeriod = "MINUTES";
var rollupPeriodTime = "1";
//handles DST shenanigans
print(from.toISOString());
print(to.toISOString());
var _URL="http://localhost/rest/v1/point-values/"+xid+"?from="+from.toISOString()+
"&rollup="+rollup+"&timePeriodType="+rollupPeriod+"&timePeriods="+rollupPeriodTime+"&to="+to.toISOString();
//print(_URL);
HttpBuilder.get(_URL,
{"Accept":"*/*" ,
"Authorization":"Bearer \
[BEARER_TOKEN_HERE]"

}
,{}).err(function(status, headers, content) { //setErrorCallback for linguistic completion
	 print(content); print(status);
	}).resp(function(status, headers, content) { //setResponseCallback
			print(status);	print(content);
if(content.length)
{
       GenerateEmail(content); //don't generate if there are not any values
}
	}).excp(function(exception) { //setExceptionCallback
		//throw exception.getMessage();
		print(exception.getMessage() );
		}).execute();

[MaP]

updated code snippet with 3 points and a timestamp array concatenated then emailed

//your code here
//Query to Retrieve and rollup previous 24 Hours Data
var endDate = CONTEXT.getTimestamp();
var startDate = endDate - CONTEXT.millisInPast(MINUTE, 4); //handles DST shenanigans
var results = [];
var callback = function(idValueTime) {
  results.push(idValueTime);};
//var pointsToQuery = [ p341.getDataPointWrapper().getId() ];
PointValueQuery.rollupQuery( [341,342,282], startDate, endDate, callback, AVERAGE, 1, MINUTE);
print(results);

//Date and time Variables
var d = new Date();
var t = new Date().getTime();
var y = d.getYear()-70;
var m = d.getMonth();
var dayinmonth = d.getDate();
var hours = d.getHours();
//ECMA function to convert date to locale string YYYY-MM-DD
var datestring = d.toLocaleDateString();
//Substr fX to make date DD-MM-YYYY time format
var ddmmyyyy = datestring.substr(8,2)+datestring.substr(4,4)+datestring.substr(0,4);
var CSVname = "Performance_"+ddmmyyyy+".csv";

//Creating DateTime For Array
// 31556926000 mS in Year
// 2629743000 ms in Month
// 86400000 ms in Day
//3600000 ms in an Hour
//print(results[0].toString()+d.toString()+"\r\n");
var coeff = 1000 * 60 * 1;
var date = new Date();
//var TimeAgo = new Date((Math.round((date.getTime()-(hours*3600000)) / coeff) * coeff)+index*60000);

//var csvData =[];
var csvData ="kW,Timestamp\r\n";
var TimeWatch = [];
results.forEach(function(results,index){
    //csvData[index]=results+","+TimeAgo+"\r\n";
    var TimeAgo = new Date((Math.round((date.getTime()-(hours*3600000)) / coeff) * coeff)+index*60000);
    TimeWatch[index]=TimeAgo;
    if((index+1)%3<1){
    TimeAgo = new Date((Math.round((date.getTime()-(hours*3600000)) / coeff) * coeff)+((index-2)/3)*60000);    
      csvData = csvData+results+","+TimeAgo+"\r\n";  
    }
    else{
        csvData = csvData+results+",";
    }
});
//csvDataString = csvData.toString();
print(csvData);
//print(TimeWatch);
/*
var recipients = ["funtimes84@live.com"];
var emailContent = new com.serotonin.web.mail.EmailContent(null, 
  "Email body content", com.serotonin.m2m2.Common.UTF8);
var emailAttachment = new com.serotonin.web.mail.EmailAttachment.ByteArrayAttachment(
  "PerformanceCSV"+datestring+".csv", csvDataString.getBytes());

emailContent.addAttachment(emailAttachment);
com.serotonin.m2m2.rt.maint.work.EmailWorkItem.queueEmail(recipients, "NorthamCSV_"+ddmmyyyy, emailContent, null);
*/

[phildunlap]

@mattfox said in CSV with 1 minute rollup auto emailed via Meta Source:

I don't think it attached a timestamp. If you want timestamps, we'll have to go via the HTTP Request route and query the API directly to pull a rolled up point values query.

Not so! For the rollupQuery, while it is true that if you supply a particular rollup type the callback will be handed the simple value and the inference is left to you to add the number of rollup periods from the start time to get the nth timestamp (every period will get a callback). But, if you use a rollup of ALL the whole statistics object (AnalogStatistics object in a Numeric case) is passed to the callback: https://github.com/infiniteautomation/ma-core-public/blob/main/Core/src/com/infiniteautomation/mango/statistics/AnalogStatistics.java