From 5a726aa3491ac7cd8760d55bf23c2c54dd67a571 Mon Sep 17 00:00:00 2001
From: 沈斌 <bluelazysb@hotmail.com>
Date: Mon, 08 Jan 2018 22:33:20 +0800
Subject: [PATCH] updated
---
create_data.js | 46 ++++++++++++++++++++++++++++------------------
1 files changed, 28 insertions(+), 18 deletions(-)
diff --git a/create_data.js b/create_data.js
index 10ac6b3..c81f9ac 100644
--- a/create_data.js
+++ b/create_data.js
@@ -4,9 +4,8 @@
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';
+var end_date = moment().add(-1, 'days').format('YYYY-MM-DD');//'2018-01-07';
console.log(moment().format('YYYY-MM-DD HH:mm:ss') + " => sync minutely data from " + start_date + " to " + end_date);
@@ -18,20 +17,24 @@
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);
+ fetchDeviceMinutelyData(start, end, days, (i+1)*(j+1)*(k+1));
}
}
}
-function fetchDeviceMinutelyData(start, end) {
- pool.getConnection(function (err, connection) {
+function fetchDeviceMinutelyData(start, end, d, t) {
+ pool.getConnection(function (err, conn) {
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;
+ conn.query(sql, function (error, results, fields) {
+ if (error) {
+ console.log("error1: " + error.message);
+ return;
+ }
+
+ //console.log(start + " : " + end);
results.forEach(function(data) {
var mac = data.mac;
@@ -51,29 +54,36 @@
device_data.push(devices[mac]);
}
- // console.log(JSON.stringify(device_data));
if(device_data.length > 0) {
- insertDeviceData(device_data, connection);
+ insertDeviceData(device_data);
}
- connection.release();
+ conn.release();
+
+ if(t == (d+1)*24*60) {
+ pool.end();
+ }
});
});
}
-function insertDeviceData(docs, connection) {
- var sql = "INSERT INTO data (mac, time, json) VALUES ";
-
+function insertDeviceData(docs) {
+ var data = [];
docs.forEach(function(doc) {
var mac = doc.mac;
var time = doc.time;
delete doc.mac;
delete doc.time;
- sql += "('" + mac + "', '" + time + "', '" + JSON.stringify(doc) + "'),";
+ data.push([mac, time, JSON.stringify(doc)]);
});
- sql = sql.substr(0, sql.length -1);
- connection.query(sql, function (error, results, fields) {
- if (error) throw error;
+ var connection = mysql.createConnection(config.mysql);
+ connection.query("INSERT INTO data (mac, time, json) VALUES ?", [data], function (error, results, fields) {
+ if (error) {
+ console.log("error2: " + error.message);
+ return;
+ }
+
+ connection.destroy();
});
}
\ No newline at end of file
--
Gitblit v1.8.0