# -*- coding: UTF-8 -*-

# 连接李佳的MySQL数据库，读取数据

import pymongo
import pymysql
import datetime
import time

'''
"ins_id": 设备号，根据设备号查找设备
"time": 监测时间
"value": 监测值
"status": 状态，暂定都为True
'''
'''
2020.12.11 加入while循环+time模块+try..catch...，从而不用crontab即可实现定时循环执行
'''

while True:
    try:
        nowTime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')  # 现在时间
        print("========" + str(nowTime) + "========")

        # 建立MySQL连接
        conn = pymysql.connect(
            host="172.16.31.10",
            port=3308,
            user="root",
            password="<PASSWORD>",
            db="buffer",
            charset="utf8",
            autocommit=True
        )

        # 建立Mongodb连接
        client = pymongo.MongoClient(
            "mongodb://172.16.31.10:27017/nfca_db",
            username='nfca',
            password='<PASSWORD>'
        )
        db = client["nfca_db"]
        col_1 = db["point"]
        col_2 = db["gms_monitor"]
        col_3 = db["warning_log"]
        col_4 = db["gms_now"]
        col_5 = db['backfill_record']

        # 得到一个可以执行SQL语句的光标对象
        cursor = conn.cursor()

        # 定义要执行的SQL语句
        sql_1 = "select * from gms_monitor"

        # 执行SQL语句
        try:
            cursor.execute(sql_1)
            result = cursor.fetchall()
            res_len = len(result)
            print("连接Mysql数据库buffer成果，共找到" + str(res_len) + "条数据")
        except Exception as e:
            print(e)
            continue

        # 根据tag,找到对应id,再插入数据
        for r in result:
            point = col_1.find_one({"opc_tag": r[1]})
            id = point["_id"]
            print(id)
            point_id = point["point_id"]
            instrument = point["instrument"]
            value_min = point["value_min"]
            value_max = point["value_max"]
            alarm = False  # 默认数据处在正常范围内
            monitoring_value = r[3]
            time = r[2]
            state = r[4]
            print(state)

            # 根据point_id找点位，找到点位对应的浓密机号thickener_id 或者 搅拌机号mixer_id，根据thickener_id/mixer_id找对应的充填任务，得到对应的fill_id
            # 即point_id --> thickener_id/mixer_id --> fii_id
            thickener_id = point['thickener_id']
            fill_id = -1
            if thickener_id != 0:
                backfill_record = col_5.find({"thickener_id": thickener_id})
                for x in backfill_record:
                    if x["fill_status"] == 1:  # 只有任务处于运行状态(online)才记录
                        fill_id = x["_id"]
            else:
                mixer_id = point['mixer_id']
                backfill_record = col_5.find({"mixer_id": mixer_id})
                for x in backfill_record:
                    if x["fill_status"] == 1:  # 只有任务处于运行状态(online)才记录
                        fill_id = x["_id"]

            # 如果是泵，原始监测值会是很长一串，则将泵的原始监测值转换为0/1
            if point["instrument"] == "Valve":
                monitoring_value = (int(monitoring_value) >> 20) & 1

            if monitoring_value < value_min or monitoring_value > value_max:  # 数据超出正常范围就记录到报警日志表
                warning = {
                    "point_id": point_id,
                    "instrument": instrument,
                    "time": datetime.datetime.strptime(str(time), '%Y-%m-%d %H:%M:%S'),
                    "principal": 1,
                    "value_min": value_min,
                    "value_max": value_max,
                    "Monitoring_value": monitoring_value,
                    "fill_id": fill_id
                }
                col_3.insert_one(warning)
                alarm = True
                print(str(r[2]) + "  数据异常，插入报警日志表.")

            # 插入数据到历史数据表
            monitor = {
                "point_id": point_id,
                "instrument": instrument,
                "time": datetime.datetime.strptime(str(time), '%Y-%m-%d %H:%M:%S'),
                "Monitoring_value": monitoring_value,
                "alarm": alarm,
                "point": id,
                "fill_id": fill_id
            }
            col_2.insert_one(monitor)

            # 更新实时监测值数据表的数据
            gms_now_query = col_4.find_one({"point_id": point_id})
            if gms_now_query is None:
                col_4.insert_one(monitor)
            else:
                newvalues = {"$set":
                                 {
                                     "instrument": instrument,
                                     "time": datetime.datetime.strptime(str(time), '%Y-%m-%d %H:%M:%S'),
                                     "Monitoring_value": monitoring_value,
                                     "alarm": alarm,
                                     "state": state,
                                     "point": id,
                                     "fill_id": fill_id
                                  }
                             }
                col_4.update_one(gms_now_query, newvalues)

            # 删除buffer中的数据
            sql_2 = "DELETE FROM gms_monitor WHERE id = %d" % r[0]
            cursor.execute(sql_2)
            print(str(r[2]) + "  monitor数据插入并删除成功.")

        # 关闭光标对象
        cursor.close()

        # 关闭数据库连接
        conn.close()
        client.close()
    except Exception as e:
        print(e)
        continue

    time.sleep(5)

# -*- coding: UTF-8 -*-

# 连接李佳的MySQL数据库，读取数据

import pymongo
import pymysql
import datetime
import time

'''
"ins_id": 设备号，根据设备号查找设备
"time": 监测时间
"value": 监测值
"status": 状态，暂定都为True
'''
'''
2020.12.11 加入while循环+time模块+try..catch...，从而不用crontab即可实现定时循环执行
'''

while True:
    try:
        nowTime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')  # 现在时间
        print("========" + str(nowTime) + "========")

        # 建立MySQL连接
        conn = pymysql.connect(
            host="172.16.31.10",
            port=3308,
            user="root",
            password="<PASSWORD>",
            db="buffer",
            charset="utf8",
            autocommit=True
        )

        # 建立Mongodb连接
        client = pymongo.MongoClient(
            "mongodb://172.16.31.10:27017/nfca_db",
            username='nfca',
            password='<PASSWORD>'
        )
        db = client["nfca_db"]
        col_1 = db["point"]
        col_2 = db["gms_monitor"]
        col_3 = db["warning_log"]
        col_4 = db["gms_now"]
        col_5 = db['backfill_record']

        # 得到一个可以执行SQL语句的光标对象
        cursor = conn.cursor()

        # 定义要执行的SQL语句
        sql_1 = "select * from gms_monitor"

        # 执行SQL语句
        try:
            cursor.execute(sql_1)
            result = cursor.fetchall()
            res_len = len(result)
            print("连接Mysql数据库buffer成果，共找到" + str(res_len) + "条数据")
        except Exception as e:
            print(e)
            continue

        # 根据tag,找到对应id,再插入数据
        for r in result:
            point = col_1.find_one({"opc_tag": r[1]})
            id = point["_id"]
            print(id)
            point_id = point["point_id"]
            instrument = point["instrument"]
            value_min = point["value_min"]
            value_max = point["value_max"]
            alarm = False  # 默认数据处在正常范围内
            monitoring_value = r[3]
            time = r[2]
            state = r[4]
            print(state)

            # 根据point_id找点位，找到点位对应的浓密机号thickener_id 或者 搅拌机号mixer_id，根据thickener_id/mixer_id找对应的充填任务，得到对应的fill_id
            # 即point_id --> thickener_id/mixer_id --> fii_id
            thickener_id = point['thickener_id']
            fill_id = -1
            if thickener_id != 0:
                backfill_record = col_5.find({"thickener_id": thickener_id})
                for x in backfill_record:
                    if x["fill_status"] == 1:  # 只有任务处于运行状态(online)才记录
                        fill_id = x["_id"]
            else:
                mixer_id = point['mixer_id']
                backfill_record = col_5.find({"mixer_id": mixer_id})
                for x in backfill_record:
                    if x["fill_status"] == 1:  # 只有任务处于运行状态(online)才记录
                        fill_id = x["_id"]

            # 如果是泵，原始监测值会是很长一串，则将泵的原始监测值转换为0/1
            if point["instrument"] == "Valve":
                monitoring_value = (int(monitoring_value) >> 20) & 1

            if monitoring_value < value_min or monitoring_value > value_max:  # 数据超出正常范围就记录到报警日志表
                warning = {
                    "point_id": point_id,
                    "instrument": instrument,
                    "time": datetime.datetime.strptime(str(time), '%Y-%m-%d %H:%M:%S'),
                    "principal": 1,
                    "value_min": value_min,
                    "value_max": value_max,
                    "Monitoring_value": monitoring_value,
                    "fill_id": fill_id
                }
                col_3.insert_one(warning)
                alarm = True
                print(str(r[2]) + "  数据异常，插入报警日志表.")

            # 插入数据到历史数据表
            monitor = {
                "point_id": point_id,
                "instrument": instrument,
                "time": datetime.datetime.strptime(str(time), '%Y-%m-%d %H:%M:%S'),
                "Monitoring_value": monitoring_value,
                "alarm": alarm,
                "point": id,
                "fill_id": fill_id
            }
            col_2.insert_one(monitor)

            # 更新实时监测值数据表的数据
            gms_now_query = col_4.find_one({"point_id": point_id})
            if gms_now_query is None:
                col_4.insert_one(monitor)
            else:
                newvalues = {"$set":
                                 {
                                     "instrument": instrument,
                                     "time": datetime.datetime.strptime(str(time), '%Y-%m-%d %H:%M:%S'),
                                     "Monitoring_value": monitoring_value,
                                     "alarm": alarm,
                                     "state": state,
                                     "point": id,
                                     "fill_id": fill_id
                                  }
                             }
                col_4.update_one(gms_now_query, newvalues)

            # 删除buffer中的数据
            sql_2 = "DELETE FROM gms_monitor WHERE id = %d" % r[0]
            cursor.execute(sql_2)
            print(str(r[2]) + "  monitor数据插入并删除成功.")

        # 关闭光标对象
        cursor.close()

        # 关闭数据库连接
        conn.close()
        client.close()
    except Exception as e:
        print(e)
        continue

    time.sleep(5)

# -*- coding: UTF-8 -*- # 连接李佳的MySQL数据库，读取数据 "ins_id": 设备号，根据设备号查找设备 "time": 监测时间 "value": 监测值 "status": 状态，暂定都为True 2020.12.11 加入while循环+time模块+try..catch...，从而不用crontab即可实现定时循环执行 # 现在时间 # 建立MySQL连接 # 建立Mongodb连接 # 得到一个可以执行SQL语句的光标对象 # 定义要执行的SQL语句 # 执行SQL语句 # 根据tag,找到对应id,再插入数据 # 默认数据处在正常范围内 # 根据point_id找点位，找到点位对应的浓密机号thickener_id 或者 搅拌机号mixer_id，根据thickener_id/mixer_id找对应的充填任务，得到对应的fill_id # 即point_id --> thickener_id/mixer_id --> fii_id # 只有任务处于运行状态(online)才记录 # 只有任务处于运行状态(online)才记录 # 如果是泵，原始监测值会是很长一串，则将泵的原始监测值转换为0/1 # 数据超出正常范围就记录到报警日志表 # 插入数据到历史数据表 # 更新实时监测值数据表的数据 # 删除buffer中的数据 # 关闭光标对象 # 关闭数据库连接