• M
    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);
    */
    

    posted in User help read more
  • M
    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)
    

    posted in User help read more
  • M
    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);
    */
    

    posted in User help read more
  • M
    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?

    posted in User help read more
  • M
    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?

    posted in User help read more
  • M
    MaP

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

    posted in User help read more
  • M
    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?

    posted in User help read more
  • M
    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.

    posted in User help read more
  • M
    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);
    */
    

    posted in User help read more