wtrk Posted November 19, 2018 Share Posted November 19, 2018 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 ]; Link to comment Share on other sites More sharing options...
justsomeguy Posted November 19, 2018 Share Posted November 19, 2018 Value is a reserved word. Did you really name your column "value?" If you choose reserved words for your identifier names then you need to escape them anywhere you use them. Link to comment Share on other sites More sharing options...
justsomeguy Posted November 19, 2018 Share Posted November 19, 2018 Nevermind, it looks like only "values" is reserved. But saying that your code doesn't work isn't enough to go on, what specifically happens? Are there error messages? Link to comment Share on other sites More sharing options...
wtrk Posted November 21, 2018 Author Share Posted November 21, 2018 No error message but didnt get data and also didnt collect. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now