Jump to content

wtrk

Members
  • Posts

    3
  • Joined

  • Last visited

Posts posted by wtrk

  1. Hi everyone.

    Before I want to say, Im trying to make a project Raspberry PI 3 and Nodered.

    I'm logging my data from device to Litesql everything is ok.

    I just want to take this data from db and make a chart for a specific  time, like today, yesterday, last week, last month, last year.

     

    I found a code, and need to change this code for my project. Can some one help me. My db name is Test  and column name is Value

    Which line or lines I have to change and how ?

     

    This code is working code, which is I take in internet , and I changed just db name and source.

    // This will handle any device and any attribute as long as it is in the DB
    var p_30d  = 1000*60*60*24*30 ; //30 Days
    var p_7d  = 1000*60*60*24*7 ; //7 Days
    var p_1d   =  1000*60*60*24 ; // 1 Day
    var d = new Date();
    var current = d.getTime();
    var today0h = d.setHours(0,0,0,0);
    var day = d.getDay();
    var monday0h = today0h - (day + (day === 0 ? -6:1)) * p_1d;
    var fromdate = 0;
    var enddate = 0;
    var sql = [];
    var sourcelist = [];
    var aggrlist = [];
    var title = "";
    
    
    // Get the period and the list of data sources 
    // also set some default values if one or the other does not exist yet
    sourcelist = context.get("sourcelist");
    if (sourcelist===undefined) { // if running for the first time
        sourcelist = [];
    }
    aggrlist = context.get("aggrlist");
    if (aggrlist===undefined) { // if running for the first time
        aggrlist = [];
    }
    fromdate = context.get("fromdate");
    if (fromdate===undefined) {
        // set the period to a default if it is not selected yet
        fromdate = current-p_1d;
    }
    enddate = context.get("enddate");
    if (enddate===undefined) {
        // set the period to a default if it is not selected yet
        enddate = current;
    }
    
    switch(msg.topic) {
        case "period":
            switch(msg.payload) {
                case "today":
                    fromdate = today0h;
                    enddate = today0h+p_1d;
                    break;
                case "yesterday":
                    fromdate = today0h-p_1d;
                    enddate = today0h;
                    break;
                case "thisweek":
                    fromdate = monday0h;
                    enddate = monday0h+p_7d;
                    break;
                case "lastweek":
                    fromdate = monday0h-p_7d;
                    enddate = monday0h;
                    break;
                case "last24h":
                    fromdate = current-p_1d;
                    enddate = current;
                    break;
                case "last7d":
                    fromdate = current-p_7d;
                    enddate = current;
                    break;
                case "last30d":
                    fromdate = current-p_30d;
                    enddate = current;
                    break;
            }
            context.set("fromdate",fromdate);
            context.set("enddate",enddate);
            break;
        case "datasource":
            if (msg.payload==="delete") {
                // remove all previous data sources
                sourcelist = [];
            } else {
                sourcelist = context.get("sourcelist");
                if (sourcelist===undefined) { // if running for the first time
                    sourcelist = [];
                }
                sourcelist.push(msg.payload);
            }
            context.set("sourcelist",sourcelist);
            break;
        case "aggrsource":
            if (msg.payload==="delete") {
                // remove all previous data sources
                aggrlist = [];
            } else {
                aggrlist = context.get("aggrlist");
                if (aggrlist===undefined) { // if running for the first time
                    aggrlist = [];
                }
                aggrlist.push(msg.payload);
            }
            context.set("aggrlist",aggrlist);
            break;
        case "minus1w":
            fromdate = fromdate-p_7d;
            enddate = enddate-p_7d;
            context.set("fromdate",fromdate);
            context.set("enddate",enddate);
            break;
        case "plus1w":
            fromdate = fromdate+p_7d;
            enddate = enddate+p_7d;
            context.set("fromdate",fromdate);
            context.set("enddate",enddate);
            break;
        case "minus1d":
            fromdate = fromdate-p_1d;
            enddate = enddate-p_1d;
            context.set("fromdate",fromdate);
            context.set("enddate",enddate);
            break;
        case "plus1d":
            fromdate = fromdate+p_1d;
            enddate = enddate+p_1d;
            context.set("fromdate",fromdate);
            context.set("enddate",enddate);
            break;
    }
    
    
    // Regenerate the SQL statements
    // Run through the data source list an generate the SQL statements
    sql = [];
    if (sourcelist.length>0) {
        for (var i = 0; i < sourcelist.length; i++) {
            var parts = sourcelist[i].split("/");
            sql.push({ topic: "SELECT * FROM sensor_data WHERE device='"+parts[0]+"' AND sensor='"+parts[1]+"' AND epoch >= " + fromdate + " AND epoch <= " + enddate });
        }
    } 
    if (aggrlist.length>0) {
        for (var i = 0; i < aggrlist.length; i++) {
            var parts = aggrlist[i].split("/");
            sql.push({ topic: "SELECT * FROM sensor_aggr WHERE device='"+parts[0]+"' AND sensor='"+parts[1]+"' AND epoch >= " + fromdate + " AND epoch <= " + enddate });
        }
    } 
    if (sql.length===0) {    
        // Dummy select that returns nothing to clear the chart
        sql.push({ topic: "SELECT * FROM sensor_data WHERE device='xxxx'" });
    }
    
    // set the completed flag for the join node later
    sql[sql.length-1].complete=true;
    // pass along the email flag to redirect the flow later
    if (msg.topic==="email") {
        sql[sql.length-1].email=true;
    }
    
    // Generate report title
    if (sourcelist.length===0 && aggrlist.length===0) {
        title = "No data source";
    } else {
        if (sourcelist.length!==0) {
            title = title + sourcelist.toString()+ ", ";
        }
        if (aggrlist.length!==0) {
            title = title + aggrlist.toString()+ ", ";
        }
        title = title.substring(0,title.length-2);
        title = title + " | ";
    
        var d = new Date();
        d.setTime(fromdate);
        var yyyy = d.getFullYear();
        var mm = d.getMonth() < 9 ? "0" + (d.getMonth() + 1) : (d.getMonth() + 1); // getMonth() is zero-based
        var dd  = d.getDate() < 10 ? "0" + d.getDate() : d.getDate();
        var hh = d.getHours() < 10 ? "0" + d.getHours() : d.getHours();
        var mmm  = d.getMinutes() < 10 ? "0" + d.getMinutes() : d.getMinutes();
        var ss  = d.getSeconds() < 10 ? "0" + d.getSeconds() : d.getSeconds();
        title = title + dd + "." + mm + "." + yyyy;
        d.setTime(enddate);
        yyyy = d.getFullYear();
        mm = d.getMonth() < 9 ? "0" + (d.getMonth() + 1) : (d.getMonth() + 1); // getMonth() is zero-based
        dd  = d.getDate() < 10 ? "0" + d.getDate() : d.getDate();
        hh = d.getHours() < 10 ? "0" + d.getHours() : d.getHours();
        mmm  = d.getMinutes() < 10 ? "0" + d.getMinutes() : d.getMinutes();
        ss  = d.getSeconds() < 10 ? "0" + d.getSeconds() : d.getSeconds();
        title = title + " - " + dd + "." + mm + "." + yyyy;
    }
    sql[sql.length-1].title=title;
    
    return [ sql ];
    

     

     

     

  2. Hi everyone.

    Before I want to say, Im trying to make a project Raspberry PI 3 and Nodered.

    I'm logging my data from device to Litesql everything is ok.

     

    I just want to take this data from db and make a chart for a specific  time, like today, yesterday, last week, last mont, last year.

     

    I found a code, and need to change this code for my project in javascript. Can some one help me. My db name is Test  and column name is Value

     

    This code is working code, which is I take in internet , and I changed just db name and source.

    // This will handle any device and any attribute as long as it is in the DB
    var p_30d  = 1000*60*60*24*30 ; //30 Days
    var p_7d  = 1000*60*60*24*7 ; //7 Days
    var p_1d   =  1000*60*60*24 ; // 1 Day
    var d = new Date();
    var current = d.getTime();
    var today0h = d.setHours(0,0,0,0);
    var day = d.getDay();
    var monday0h = today0h - (day + (day === 0 ? -6:1)) * p_1d;
    var fromdate = 0;
    var enddate = 0;
    var sql = [];
    var sourcelist = [];
    var aggrlist = [];
    var title = "";
    
    
    // Get the period and the list of data sources 
    // also set some default values if one or the other does not exist yet
    sourcelist = context.get("sourcelist");
    if (sourcelist===undefined) { // if running for the first time
        sourcelist = [];
    }
    aggrlist = context.get("aggrlist");
    if (aggrlist===undefined) { // if running for the first time
        aggrlist = [];
    }
    fromdate = context.get("fromdate");
    if (fromdate===undefined) {
        // set the period to a default if it is not selected yet
        fromdate = current-p_1d;
    }
    enddate = context.get("enddate");
    if (enddate===undefined) {
        // set the period to a default if it is not selected yet
        enddate = current;
    }
    
    switch(msg.topic) {
        case "period":
            switch(msg.payload) {
                case "today":
                    fromdate = today0h;
                    enddate = today0h+p_1d;
                    break;
                case "yesterday":
                    fromdate = today0h-p_1d;
                    enddate = today0h;
                    break;
                case "thisweek":
                    fromdate = monday0h;
                    enddate = monday0h+p_7d;
                    break;
                case "lastweek":
                    fromdate = monday0h-p_7d;
                    enddate = monday0h;
                    break;
                case "last24h":
                    fromdate = current-p_1d;
                    enddate = current;
                    break;
                case "last7d":
                    fromdate = current-p_7d;
                    enddate = current;
                    break;
                case "last30d":
                    fromdate = current-p_30d;
                    enddate = current;
                    break;
            }
            context.set("fromdate",fromdate);
            context.set("enddate",enddate);
            break;
        case "datasource":
            if (msg.payload==="delete") {
                // remove all previous data sources
                sourcelist = [];
            } else {
                sourcelist = context.get("sourcelist");
                if (sourcelist===undefined) { // if running for the first time
                    sourcelist = [];
                }
                sourcelist.push(msg.payload);
            }
            context.set("sourcelist",sourcelist);
            break;
        case "aggrsource":
            if (msg.payload==="delete") {
                // remove all previous data sources
                aggrlist = [];
            } else {
                aggrlist = context.get("aggrlist");
                if (aggrlist===undefined) { // if running for the first time
                    aggrlist = [];
                }
                aggrlist.push(msg.payload);
            }
            context.set("aggrlist",aggrlist);
            break;
        case "minus1w":
            fromdate = fromdate-p_7d;
            enddate = enddate-p_7d;
            context.set("fromdate",fromdate);
            context.set("enddate",enddate);
            break;
        case "plus1w":
            fromdate = fromdate+p_7d;
            enddate = enddate+p_7d;
            context.set("fromdate",fromdate);
            context.set("enddate",enddate);
            break;
        case "minus1d":
            fromdate = fromdate-p_1d;
            enddate = enddate-p_1d;
            context.set("fromdate",fromdate);
            context.set("enddate",enddate);
            break;
        case "plus1d":
            fromdate = fromdate+p_1d;
            enddate = enddate+p_1d;
            context.set("fromdate",fromdate);
            context.set("enddate",enddate);
            break;
    }
    
    
    // Regenerate the SQL statements
    // Run through the data source list an generate the SQL statements
    sql = [];
    if (sourcelist.length>0) {
        for (var i = 0; i < sourcelist.length; i++) {
            var parts = sourcelist[i].split("/");
            sql.push({ topic: "SELECT * FROM sensor_data WHERE device='"+parts[0]+"' AND sensor='"+parts[1]+"' AND epoch >= " + fromdate + " AND epoch <= " + enddate });
        }
    } 
    if (aggrlist.length>0) {
        for (var i = 0; i < aggrlist.length; i++) {
            var parts = aggrlist[i].split("/");
            sql.push({ topic: "SELECT * FROM sensor_aggr WHERE device='"+parts[0]+"' AND sensor='"+parts[1]+"' AND epoch >= " + fromdate + " AND epoch <= " + enddate });
        }
    } 
    if (sql.length===0) {    
        // Dummy select that returns nothing to clear the chart
        sql.push({ topic: "SELECT * FROM sensor_data WHERE device='xxxx'" });
    }
    
    // set the completed flag for the join node later
    sql[sql.length-1].complete=true;
    // pass along the email flag to redirect the flow later
    if (msg.topic==="email") {
        sql[sql.length-1].email=true;
    }
    
    // Generate report title
    if (sourcelist.length===0 && aggrlist.length===0) {
        title = "No data source";
    } else {
        if (sourcelist.length!==0) {
            title = title + sourcelist.toString()+ ", ";
        }
        if (aggrlist.length!==0) {
            title = title + aggrlist.toString()+ ", ";
        }
        title = title.substring(0,title.length-2);
        title = title + " | ";
    
        var d = new Date();
        d.setTime(fromdate);
        var yyyy = d.getFullYear();
        var mm = d.getMonth() < 9 ? "0" + (d.getMonth() + 1) : (d.getMonth() + 1); // getMonth() is zero-based
        var dd  = d.getDate() < 10 ? "0" + d.getDate() : d.getDate();
        var hh = d.getHours() < 10 ? "0" + d.getHours() : d.getHours();
        var mmm  = d.getMinutes() < 10 ? "0" + d.getMinutes() : d.getMinutes();
        var ss  = d.getSeconds() < 10 ? "0" + d.getSeconds() : d.getSeconds();
        title = title + dd + "." + mm + "." + yyyy;
        d.setTime(enddate);
        yyyy = d.getFullYear();
        mm = d.getMonth() < 9 ? "0" + (d.getMonth() + 1) : (d.getMonth() + 1); // getMonth() is zero-based
        dd  = d.getDate() < 10 ? "0" + d.getDate() : d.getDate();
        hh = d.getHours() < 10 ? "0" + d.getHours() : d.getHours();
        mmm  = d.getMinutes() < 10 ? "0" + d.getMinutes() : d.getMinutes();
        ss  = d.getSeconds() < 10 ? "0" + d.getSeconds() : d.getSeconds();
        title = title + " - " + dd + "." + mm + "." + yyyy;
    }
    sql[sql.length-1].title=title;
    
    return [ sql ];
    

     

     

     

     

     

    This is I changed code but not working.

    // This will handle any device and any attribute as long as it is in the DB
    var p_30d  = 1000*60*60*24*30 ; //30 Days
    var p_7d  = 1000*60*60*24*7 ; //7 Days
    var p_1d   =  1000*60*60*24 ; // 1 Day
    var d = new Date();
    var current = d.getTime();
    var today0h = d.setHours(0,0,0,0);
    var day = d.getDay();
    var monday0h = today0h - (day + (day === 0 ? -6:1)) * p_1d;
    var fromdate = 0;
    var enddate = 0;
    var sql = [];
    //var sourcelist = [];
    var aggrlist = [];
    var title = "";
    
    
    // Get the period and the list of data sources 
    // also set some default values if one or the other does not exist yet
    sourcelist = context.get("sourcelist");
    if (sourcelist===undefined) { // if running for the first time
        sourcelist = [];
    }
    aggrlist = context.get("aggrlist");
    if (aggrlist===undefined) { // if running for the first time
        aggrlist = [];
    }
    fromdate = context.get("fromdate");
    if (fromdate===undefined) {
        // set the period to a default if it is not selected yet
        fromdate = current-p_1d;
    }
    enddate = context.get("enddate");
    if (enddate===undefined) {
        // set the period to a default if it is not selected yet
        enddate = current;
    }
    
    switch(msg.topic) {
        case "period":
            switch(msg.payload) {
                case "today":
                    fromdate = today0h;
                    enddate = today0h+p_1d;
                    break;
                case "yesterday":
                    fromdate = today0h-p_1d;
                    enddate = today0h;
                    break;
                case "thisweek":
                    fromdate = monday0h;
                    enddate = monday0h+p_7d;
                    break;
                case "lastweek":
                    fromdate = monday0h-p_7d;
                    enddate = monday0h;
                    break;
                case "last24h":
                    fromdate = current-p_1d;
                    enddate = current;
                    break;
                case "last7d":
                    fromdate = current-p_7d;
                    enddate = current;
                    break;
                case "last30d":
                    fromdate = current-p_30d;
                    enddate = current;
                    break;
            }
            context.set("fromdate",fromdate);
            context.set("enddate",enddate);
            break;
        case "datasource":
            if (msg.payload==="delete") {
                // remove all previous data sources
                sourcelist = [];
            } else {
                sourcelist = context.get("sourcelist");
                if (sourcelist===undefined) { // if running for the first time
                    sourcelist = [];
                }
                sourcelist.push(msg.payload);
            }
            context.set("sourcelist",sourcelist);
            break;
        case "aggrsource":
            if (msg.payload==="delete") {
                // remove all previous data sources
                aggrlist = [];
            } else {
                aggrlist = context.get("aggrlist");
                if (aggrlist===undefined) { // if running for the first time
                    aggrlist = [];
                }
                aggrlist.push(msg.payload);
            }
            context.set("aggrlist",aggrlist);
            break;
    }
    
    // Regenerate the SQL statements
    // Run through the data source list an generate the SQL statements
    sql = [];
    if (sourcelist.length>0) {
        for (var i = 0; i < sourcelist.length; i++) {
            var parts = sourcelist[i].split("/");
            sql.push({ topic: "SELECT Value FROM Test" +value[0]+""+value[1]+ fromdate + "  " + enddate });
           
        }
    } 
    //if (aggrlist.length>0) {
    //    for (var i = 0; i < aggrlist.length; i++) {
    //        var parts = aggrlist[i].split("/");
    //        sql.push({ topic: "SELECT Value FROM Test" +value[0]+""+value[1] + fromdate + "  " + enddate });
    //    }
    //} 
    if (sql.length===0) {    
        // Dummy select that returns nothing to clear the chart
        sql.push({ topic: "SELECT Value FROM Test" });
    }
    
    // set the completed flag for the join node later
    sql[sql.length-1].complete=true;
    // pass along the email flag to redirect the flow later
    if (msg.topic==="email") {
        sql[sql.length-1].email=true;
    }
    
    // Generate report title
    if (sourcelist.length===0 && aggrlist.length===0) {
        title = "No data source";
    } else {
        if (sourcelist.length!==0) {
            title = title + sourcelist.toString()+ ", ";
        }
        if (aggrlist.length!==0) {
            title = title + aggrlist.toString()+ ", ";
        }
        title = title.substring(0,title.length-2);
        title = title + " | ";
    
        var d = new Date();
        d.setTime(fromdate);
        var yyyy = d.getFullYear();
        var mm = d.getMonth() < 9 ? "0" + (d.getMonth() + 1) : (d.getMonth() + 1); // getMonth() is zero-based
        var dd  = d.getDate() < 10 ? "0" + d.getDate() : d.getDate();
        var hh = d.getHours() < 10 ? "0" + d.getHours() : d.getHours();
        var mmm  = d.getMinutes() < 10 ? "0" + d.getMinutes() : d.getMinutes();
        var ss  = d.getSeconds() < 10 ? "0" + d.getSeconds() : d.getSeconds();
        title = title + dd + "." + mm + "." + yyyy;
        d.setTime(enddate);
        yyyy = d.getFullYear();
        mm = d.getMonth() < 9 ? "0" + (d.getMonth() + 1) : (d.getMonth() + 1); // getMonth() is zero-based
        dd  = d.getDate() < 10 ? "0" + d.getDate() : d.getDate();
        hh = d.getHours() < 10 ? "0" + d.getHours() : d.getHours();
        mmm  = d.getMinutes() < 10 ? "0" + d.getMinutes() : d.getMinutes();
        ss  = d.getSeconds() < 10 ? "0" + d.getSeconds() : d.getSeconds();
        title = title + " - " + dd + "." + mm + "." + yyyy;
    }
    sql[sql.length-1].title=title;
    
    return [ sql ];
    

     

×
×
  • Create New...