• Recent
    • Tags
    • Popular
    • Register
    • Login

    Please Note This forum exists for community support for the Mango product family and the Radix IoT Platform. Although Radix IoT employees participate in this forum from time to time, there is no guarantee of a response to anything posted here, nor can Radix IoT, LLC guarantee the accuracy of any information expressed or conveyed. Specific project questions from customers with active support contracts are asked to send requests to support@radixiot.com.

    Radix IoT Website Mango 3 Documentation Website Mango 4 Documentation Website Mango 5 Documentation Website

    CSV with 1 minute rollup auto emailed via Meta Source

    User help
    3
    21
    3.3k
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • MattFoxM
      MattFox
      last edited by

      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.

      Do not follow where the path may lead; go instead where there is no path.
      And leave a trail - Muriel Strode

      1 Reply Last reply Reply Quote 0
      • M
        MaP
        last edited by 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?

        1 Reply Last reply Reply Quote 0
        • MattFoxM
          MattFox
          last edited by 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

          Do not follow where the path may lead; go instead where there is no path.
          And leave a trail - Muriel Strode

          1 Reply Last reply Reply Quote 0
          • M
            MaP
            last edited by

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

            1 Reply Last reply Reply Quote 0
            • MattFoxM
              MattFox
              last edited by 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,

              Do not follow where the path may lead; go instead where there is no path.
              And leave a trail - Muriel Strode

              1 Reply Last reply Reply Quote 0
              • M
                MaP
                last edited by

                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?

                MattFoxM 1 Reply Last reply Reply Quote 0
                • MattFoxM
                  MattFox @MaP
                  last edited by 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?

                  Do not follow where the path may lead; go instead where there is no path.
                  And leave a trail - Muriel Strode

                  1 Reply Last reply Reply Quote 0
                  • M
                    MaP
                    last edited by

                    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?

                    MattFoxM 1 Reply Last reply Reply Quote 0
                    • MattFoxM
                      MattFox @MaP
                      last edited by 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.

                      Do not follow where the path may lead; go instead where there is no path.
                      And leave a trail - Muriel Strode

                      1 Reply Last reply Reply Quote 0
                      • M
                        MaP
                        last edited by 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);
                        */
                        
                        1 Reply Last reply Reply Quote 0
                        • MattFoxM
                          MattFox
                          last edited by 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.

                          Do not follow where the path may lead; go instead where there is no path.
                          And leave a trail - Muriel Strode

                          1 Reply Last reply Reply Quote 0
                          • M
                            MaP
                            last edited by

                            This post is deleted!
                            1 Reply Last reply Reply Quote 0
                            • M
                              MaP
                              last edited by

                              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)
                              
                              1 Reply Last reply Reply Quote 0
                              • MattFoxM
                                MattFox
                                last edited by 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();
                                

                                Do not follow where the path may lead; go instead where there is no path.
                                And leave a trail - Muriel Strode

                                1 Reply Last reply Reply Quote 0
                                • M
                                  MaP
                                  last edited by

                                  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);
                                  */
                                  
                                  1 Reply Last reply Reply Quote 0
                                  • phildunlapP
                                    phildunlap
                                    last edited by

                                    @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

                                    1 Reply Last reply Reply Quote 0
                                    • MattFoxM
                                      MattFox
                                      last edited by

                                      Thanks Phil, I should have asked you about that months ago. Having no timestamp with the values turned me away from using it.

                                      Do not follow where the path may lead; go instead where there is no path.
                                      And leave a trail - Muriel Strode

                                      1 Reply Last reply Reply Quote 0
                                      • First post
                                        Last post