0 Replies Latest reply on Jan 11, 2018 1:54 PM by patn RSS
    patn Explorer

    QueryNamedPropertyHistory with Row Control/Limiting

    Following on from my post QueryPropertyHistory Service with Row Control/Limiting, we incorporated this function in to a service which extends and adds the capability to specify Property names too, just like QueryNamedPropertyHistory.


    We also created a similar service for QueryNamedPropertyHistoryRowLimited, below. This will need the additional input configuring for propetryNames, as per the default service QueryNamedPropertyHistory.



    /* *** QueryNamedPropertyHistoryRowLimited *** */

    //Does simple step-interpolation of a fixed number of points between startDate and endDate

    //set default values for properties undefined

    var ed = endDate;

    var sd = startDate;

    var mi = maxItems;


    //if endDate is undefined, choose NOW as the endDate

    if (ed == null) {

        ed = new Date();



    //if startDate is undefined or greater than the endDate, choose 24 hours ago as the startDate

    if ((sd == null) || (sd > ed)) {

        var dateValue = new Date();

        sd = dateAddHours(dateValue, -24);



    //if maxItems is null or undefined, set to 100 rows of data as default

    if ((mi == null) || (mi == undefined)) {

        mi = 100;



    //calculate the time period between rows of data

    var startUTC = Date.UTC(sd.getFullYear(), sd.getMonth(), sd.getUTCDate(), sd.getUTCHours(), sd.getUTCMinutes(), sd.getUTCSeconds());  

    var endUTC = Date.UTC(ed.getFullYear(), ed.getMonth(), ed.getUTCDate(), ed.getUTCHours(), ed.getUTCMinutes(), ed.getUTCSeconds());

    var timeSlice = Math.round((endUTC -startUTC) / (mi - 1));


    //setup the date for query on the first loop iteration

    var loopDateUTC;

    var count = 0;


    //define the sort order, based on user selection

    if(oldestFirst) {

        loopDateUTC = startUTC;

    } else {

        loopDateUTC = endUTC;



    //loop through for maxItems-1 iterations and get one row for the named properties each time

    for (count=0; count < (mi); count++) {


        var loopDate = new Date();



        var params = {

            oldestFirst: false /* BOOLEAN */,

            maxItems: 1 /* NUMBER */,

            endDate: loopDate /* DATETIME */,

            propertyNames: propertyNames /* INFOTABLE */,

            query: query /* QUERY */,

            startDate: undefined /* DATETIME */



        //if this is the first iteration, create the output table from the first result set

        if (count == 0) {

            //get one row of data to obtain the structure

            var outputTable = me.QueryNamedPropertyHistory(params);

            outputTable.RemoveAllRows();    //clear table rows for readding later



        //get one row of data to add to our table

        var propertyHistory = me.QueryNamedPropertyHistory(params);

        var row = propertyHistory.getRow(0);


        //set the datetime of the row to match the Query endDate

        row.timestamp = loopDate;    //because we know that timestamp will be our column name



         //prepare for next iteration

        if(oldestFirst) {

            loopDateUTC = loopDateUTC + (timeSlice);

        } else {

            loopDateUTC = loopDateUTC - (timeSlice);




    var result = outputTable;