From d79e5d857f467b37652145577fe9a2c4065a77c5 Mon Sep 17 00:00:00 2001
From: 沈斌 <bluelazysb@hotmail.com>
Date: Mon, 08 Jan 2018 16:27:09 +0800
Subject: [PATCH] updated
---
test.js | 16 +++++--
config.js | 1
create_data.js | 79 +++++++++++++++++++++++++++++++++++++++
3 files changed, 91 insertions(+), 5 deletions(-)
diff --git a/config.js b/config.js
index 34cb424..4b0962d 100644
--- a/config.js
+++ b/config.js
@@ -1,6 +1,7 @@
var config = {
mysql: {
+ connectionLimit : 20,
host : "47.96.26.152",
database: "monitor",
user : "root",
diff --git a/create_data.js b/create_data.js
new file mode 100644
index 0000000..10ac6b3
--- /dev/null
+++ b/create_data.js
@@ -0,0 +1,79 @@
+var mysql = require('mysql');
+var moment = require('moment');
+var config = require('./config');
+
+var pool = mysql.createPool(config.mysql);
+
+
+var start_date = moment().add(-1, 'days').format('YYYY-MM-DD');//'2017-11-11';
+var end_date = moment().add(-1, 'days').format('YYYY-MM-DD');//'2018-01-03';
+
+console.log(moment().format('YYYY-MM-DD HH:mm:ss') + " => sync minutely data from " + start_date + " to " + end_date);
+
+var days = moment(end_date).diff(moment(start_date), 'days');
+for (var i = 0; i <= days; i++) {
+ var date_day = moment(start_date).add(i, "days");
+ for(var j = 0; j < 24; j++) {
+ var date_hour = moment(date_day).add(j, "hours").format("YYYY-MM-DD HH:00:00");
+ for(var k = 0; k < 60; k++) {
+ var start = moment(date_hour).add(k, 'minutes').format("YYYY-MM-DD HH:mm:00");
+ var end = moment(date_hour).add(k+1, 'minutes').format("YYYY-MM-DD HH:mm:00");
+ //console.log(start + " : " + end);
+
+ fetchDeviceMinutelyData(start, end);
+ }
+ }
+}
+
+function fetchDeviceMinutelyData(start, end) {
+ pool.getConnection(function (err, connection) {
+ var devices = {};
+ var sql = "SELECT mac, mac_key, avg(mac_value) as avg_val, min(mac_value) as min_val, max(mac_value) as max_val FROM history WHERE time >= '" + start + "' AND time < '" + end + "' GROUP BY mac, mac_key ORDER BY mac";
+ //console.log(sql);
+ connection.query(sql, function (error, results, fields) {
+ if (error) throw error;
+
+ results.forEach(function(data) {
+ var mac = data.mac;
+ var key = data.mac_key;
+ var val = data.avg_val;
+ if(devices[mac] == null) {
+ devices[mac] = {
+ mac: mac,
+ time: moment(start).format('YYYY-MM-DD HH:mm:00')
+ };
+ }
+ devices[mac][key] = [val.toFixed(3), data.min_val, data.max_val]
+ });
+
+ var device_data = [];
+ for(var mac in devices) {
+ device_data.push(devices[mac]);
+ }
+
+ // console.log(JSON.stringify(device_data));
+ if(device_data.length > 0) {
+ insertDeviceData(device_data, connection);
+ }
+
+ connection.release();
+ });
+ });
+}
+
+function insertDeviceData(docs, connection) {
+ var sql = "INSERT INTO data (mac, time, json) VALUES ";
+
+ docs.forEach(function(doc) {
+ var mac = doc.mac;
+ var time = doc.time;
+ delete doc.mac;
+ delete doc.time;
+ sql += "('" + mac + "', '" + time + "', '" + JSON.stringify(doc) + "'),";
+ });
+ sql = sql.substr(0, sql.length -1);
+
+ connection.query(sql, function (error, results, fields) {
+ if (error) throw error;
+ });
+}
\ No newline at end of file
diff --git a/test.js b/test.js
index cc14439..0573f20 100644
--- a/test.js
+++ b/test.js
@@ -2,11 +2,17 @@
var moment = require('moment');
-var a = moment();
-var b = a.format('YYYY-MM-DD');
-var c = a.add(-1, 'days').format('YYYY-MM-DD');
-console.log(b);
-console.log(c);
+
+var doc = {a:1, b:2, c:3};
+delete doc.a;
+console.log(doc);
+
+
+// var a = moment();
+// var b = a.format('YYYY-MM-DD');
+// var c = a.add(-1, 'days').format('YYYY-MM-DD');
+// console.log(b);
+// console.log(c);
//
// var bb = a.format('YYYY-MM-DD HH:00:00');
// var cc = a.add(1, 'hours').format('YYYY-MM-DD HH:00:00');
--
Gitblit v1.8.0