From d28e393c134e12e131a26740ef99cb76278ef907 Mon Sep 17 00:00:00 2001
From: 沈斌 <bluelazysb@hotmail.com>
Date: Wed, 24 Jan 2018 14:38:38 +0800
Subject: [PATCH] updated
---
create_minutely_data.js | 89 ++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 89 insertions(+), 0 deletions(-)
diff --git a/create_minutely_data.js b/create_minutely_data.js
index e69de29..fdfb3db 100644
--- a/create_minutely_data.js
+++ b/create_minutely_data.js
@@ -0,0 +1,89 @@
+var mysql = require('mysql');
+var moment = require('moment');
+var config = require('./config');
+var mongoClient = require('mongodb').MongoClient;
+
+var mysqlClient = mysql.createConnection(config.mysql);
+
+mysqlClient.connect(function(error) {
+ if (error) return;
+ //console.log("connected to 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);
+ }
+ }
+}
+
+mysqlClient.end();
+
+function fetchDeviceMinutelyData(start, end) {
+ 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);
+ mysqlClient.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(moment(start, 'YYYY-MM-DD HH:mm:00').valueOf() + 8*60*60*1000).toDate()
+ };
+ }
+ devices[mac][key] = [val, 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);
+ }
+ });
+}
+
+function insertDeviceData(docs) {
+ mongoClient.connect(config.mongo, {
+ poolSize: 10,
+ keepAlive: 300000,
+ auto_reconnect: true,
+ reconnectTries: 1000,
+ reconnectInterval: 5000,
+ connectTimeoutMS: 30000
+ }, function(err, client) {
+ if (err) {
+ console.log(err.message);
+ return;
+ }
+ client.db('monitor').collection('data_minutely').insertMany(docs, function(err2, doc2) {
+ if (err2) {
+ console.log(err2.message);
+ return;
+ }
+ client.close();
+ });
+ });
+}
\ No newline at end of file
--
Gitblit v1.8.0