def get_meter_data(self, ct_sensors: list) -> Dict[datetime.isoformat, float]:
"""Get Meter Data with Specific CT Sensors
Args:
ct_sensors (list): CT sensors
Returns:
Dict[datetime.isoformat, float]: historical power consumption data
"""
if self.args["interval"] == "hour":
power = func.round(cast(func.avg(MeterData.current) * VOLTAGE, DECIMAL), 2).label("power")
time_format = "YYYY-MM-DD HH24:00:00"
elif self.args["interval"] == "day":
power = func.round(cast(func.avg(MeterData.current) * VOLTAGE * 24, DECIMAL), 2).label("power")
time_format = "YYYY-MM-DD 00:00:00"
else:
raise Exception(f"Invalid interval: {self.args['interval']}")
date = self.simplify_date(MeterData.created, time_format)
criteria = [
MeterData.created.between(self.args["start_time"], self.args["end_time"]),
MeterData.sensor.in_(ct_sensors),
]
# CT Data
meter_ct = (
SESSION.query(MeterData.sensor, date, power).filter(*criteria).group_by(date, MeterData.sensor).subquery()
)
# Group Data By Date
meter_sum = (
SESSION.query(func.sum(meter_ct.c.power).label("power"), meter_ct.c.datetime.label("datetime"))
.group_by(meter_ct.c.datetime)
.order_by(meter_ct.c.datetime)
.all()
)
meter_data = {meter.datetime.isoformat(): float(meter.power) for meter in meter_sum}
return meter_data
def applicator(*args, **kwargs):
try:
func(*args, **kwargs)
return True
except Exception as err:
logger.error(f"DB Operation Failed.\nError: {err}")
SESSION.rollback()
def find_lib(lib_name):
session = SESSION()
try:
return session.query(PythonLibs).filter_by(name=lib_name).first()
except Exception as e:
return None
finally:
session.close()
def find_out_date():
session = SESSION()
try:
return session, session.query(NewMemberCaptcha).filter(
time.time() - NewMemberCaptcha.insert_time > 300,
NewMemberCaptcha.is_verify == 0).all()
except Exception as e:
return session, []
def search_captcha(**kwargs):
session = SESSION()
try:
return session.query(NewMemberCaptcha).filter_by(**kwargs).first()
except Exception as e:
return None
finally:
session.close()
def get_need_increase():
session = SESSION()
try:
days = session.query(Days).all()
for d in days:
yield d
except Exception as e:
pass
finally:
session.close()
def find_lang_ref(many=False, **kwargs):
session = SESSION()
try:
res = session.query(PythonLangRef).filter_by(**kwargs)
if not many:
return res.first()
else:
return res.all()
except Exception as e:
return None
finally:
session.close()
def count_my_roll(group_id, user_id):
session = SESSION()
try:
res = session.query(func.sum(RollHistory.point).label('s')).filter_by(
group_id=group_id, user_id=user_id).first()
if res:
return res[0]
return 0
except Exception as e:
return 0
finally:
session.close()
def count_toady_roll(**kwargs):
session = SESSION()
try:
today_start = time.mktime(datetime.today().date().timetuple())
today_end = today_start + 86400
res = session.query(RollHistory).filter_by(**kwargs).filter(
today_start < RollHistory.roll_time,
today_end > RollHistory.roll_time)
return res.count()
except Exception as e:
return 0
finally:
session.close()
def count_most_times(group_id):
session = SESSION()
try:
res = session.query(
RollHistory.user_id,
func.count(RollHistory.user_id).label('t')).filter_by(
group_id=group_id).group_by(RollHistory.user_id).order_by(
desc('t')).first()
if res:
return res
return 0, 0
except Exception as e:
return 0, 0
finally:
session.close()
def update_gateways():
"""Add (Refresh) Gateways to Redis"""
# Add gateways
gateways = SESSION.query(Gateway).all()
gateway_dict = {gw.token: gw.name for gw in gateways}
REDIS.set("gateways", json.dumps(gateway_dict))
logger.info(f"[INIT REDIS] add {len(gateway_dict)} gateways")
def get_temp_data(self, temp_sensors: list) -> Dict[datetime.isoformat, float]:
"""Get Temperature Data with Specific Thermo Sensors
Args:
temp_sensors (list): Thermo Sensors
Returns:
Dict[datetime.isoformat, float]: historical temperature data
"""
if self.args["interval"] == "hour":
time_format = "YYYY-MM-DD HH24:00:00"
elif self.args["interval"] == "day":
time_format = "YYYY-MM-DD 00:00:00"
else:
raise Exception(f"Invalid interval: {self.args['interval']}")
date = self.simplify_date(SensorData.created, time_format)
criteria = [
SensorData.created.between(self.args["start_time"], self.args["end_time"]),
SensorData.sensor.in_(temp_sensors),
]
# Thermo Sensor Data
sensor_thermo = (
# fmt: off
SESSION.query(func.round(
cast(func.avg(SensorData.temperature), DECIMAL), 2
).label("temperature"), date)
.filter(*criteria)
.group_by(date)
.all()
# fmt: on
)
thermo_data = {thermo.datetime.isoformat(): float(thermo.temperature) for thermo in sensor_thermo}
return thermo_data
def increase_one(user_id):
session = SESSION()
try:
d = session.query(Days).filter_by(user_id=user_id).first()
if not d:
return
d.days += 1
session.commit()
except Exception as e:
print(e)
session.rollback()
finally:
session.close()
def create_comment(self, app_url, access_token, message):
LOG.info("create_comment")
request_headers = build_request_headers(access_token)
payload = {"text": message}
LOG.debug(f"Request payload: {payload}")
response = SESSION.post(f"{app_url}{self.comment_url}/",
headers=request_headers, params=payload)
return response
def insert_point(**kwargs):
session = SESSION()
try:
roll = RollHistory(**kwargs)
session.add(roll)
session.commit()
except Exception as e:
return e
finally:
session.close()
def verify(user_id, code):
session = SESSION()
try:
if not code.isdigit():
return None
nmc = session.query(NewMemberCaptcha).filter_by(
user_id=user_id, is_verify=0, verify_code=int(code)).first()
if not nmc:
# sql = '''
# SELECT id FROM new_member_captcha WHERE verify_code={}
# '''.format(code)
# res = session.execute(sql)
# message = ""
# for r in res:
# message += str(r)
return None
# if code != str(nmc.verify_code):
# return "验证码错误"
nmc.is_verify = 1
session.commit()
return nmc.group_id
except Exception as e:
return None
finally:
session.close()
def get(self):
"""Get Sensors"""
args = self.get_parser.parse_args()
logger.info(f"[Get Sensor] User: {g.account}")
criteria = list()
for argument in args:
if args[argument]:
criteria.append(getattr(Sensor, argument) == args[argument])
return jsonify(SESSION.query(Sensor).filter(*criteria).all())
def insert_lang_ref(**kwargs):
session = SESSION()
try:
lang = session.query(PythonLangRef).filter_by(
title1=kwargs.get('title1'), title2=kwargs.get('title2')).first()
if lang:
lang.update(**kwargs)
else:
lang = PythonLangRef(**kwargs)
session.add(lang)
session.commit()
except Exception as e:
pass
finally:
session.close()
def verify_token(token):
user = SESSION.query(User).filter_by(token=token).first()
if user:
logger.info(f"[User OAUTH Success] User: {user.account}")
g.uuid = user.uuid
g.account = user.account
return True
logger.error(f"[USER OAUTH Failed] token: {token}")
g.error_message = "Access Denied"
return False
def insert_lib(**kwargs):
session = SESSION()
try:
lib = session.query(PythonLibs).filter_by(
name=kwargs.get('lib_name')).first()
if lib:
lib.update(**kwargs)
else:
lib = PythonLibs(**kwargs)
session.add(lib)
session.commit()
except Exception as e:
pass
finally:
session.close()
def get_latest_sensor_data(device_type: str, name: str,
location: str) -> dict:
if device_type == "thermo_sensor":
latest_command: SensorData = (SESSION.query(SensorData).filter(
SensorData.sensor == name).order_by(
SensorData.created.desc()).first())
if latest_command:
data = {
"temperature": latest_command.temperature,
"humidity": latest_command.humidity
}
else:
data = {"temperature": None, "humidity": None}
elif device_type == "ac":
latest_command: ControlRecord = (
SESSION.query(ControlRecord).filter(
ControlRecord.device == name).order_by(
ControlRecord.created.desc()).first())
latest_control_command = (SESSION.query(ControlRecord).filter(
ControlRecord.device == name,
ControlRecord.command.notin_(["off", "fan"])).order_by(
ControlRecord.created.desc()).first())
latest_mode: str = mode_judgment(latest_command)
data = {
"status":
"ON" if latest_mode != "off" else "OFF",
"mode":
latest_mode,
"temp":
latest_control_command.command
if latest_control_command else None,
}
appliances_status: dict = dict()
if latest_command:
appliances_status = {
"device_type": device_type,
"name": name,
"room": location,
"time": str(latest_command.created),
"data": data,
}
return appliances_status
def get(self):
"""Get Latest Sensor Data"""
args = self.get_parser.parse_args()
logger.info(
f"[GET One Sensor Data Request]\n User: {g.account}, Sensor: {args['name']}"
)
data = SESSION.query(SensorData).filter_by(
sensor=args['name']).order_by(SensorData.created.desc()).first()
if data:
return {"temperature": data.temperature, "humidity": data.humidity}
return {"message": "Failed"}, 400
def put(self):
"""Change User Password"""
logger.info(f"[Put User Request]\nUser Account:{g.account}")
user: User = SESSION.query(User).filter_by(uuid=g.uuid).first()
args = self.put_parser.parse_args()
if check_password_hash(user.password, args["original_password"]):
user.password = generate_password_hash(args["new_password"])
user.token = secrets.token_hex()
user.update(SESSION)
return {"message": "Accept", "token": user.token}
return {"message": "Reject"}, 400
def get(self):
"""Get Latest Device Status"""
logger.info(f"[GET Device Status Request]\n User: {g.account}")
args = self.get_parser.parse_args()
# TODO: Add location
data = (
SESSION.query(ControlRecord).filter_by(device=args["device"]).order_by(ControlRecord.created.desc()).first()
)
if data:
return {"status": data.command}
return {"message": "Failed"}, 400
def login(self, app_url, username, password):
LOG.info("login")
request_headers = {
"Content-Type": "application/x-www-form-urlencoded",
"accept": "application/json"
}
payload = {"username": username, "password": password}
LOG.debug(f"Request payload: {payload}")
response = SESSION.post(f"{app_url}{self.auth_url}/login",
headers=request_headers,
data=payload)
return response
def get_overview():
"""Get latest power consumption data"""
ct_sensors = SESSION.query(Sensor).filter(Sensor.device_type == "CT").all()
ct_sensor_name = [sensor.name for sensor in ct_sensors]
ct_room: dict = {sensor.name: sensor.room for sensor in ct_sensors}
power = (MeterData.current * VOLTAGE).label("power")
criteria = [MeterData.sensor.in_(ct_sensor_name), MeterData.created >= datetime.utcnow() - timedelta(minutes=5)]
ct_data = (
SESSION.query(MeterData.sensor, power)
.filter(*criteria)
.order_by(MeterData.sensor, MeterData.created.desc())
.distinct(MeterData.sensor)
.all()
)
power_overview: dict = {"total": 0.0, "room": dict()}
for data in ct_data:
power_overview["total"] += data.power
if ct_room[data.sensor] in power_overview["room"]:
power_overview["room"][ct_room[data.sensor]] += data.power
else:
power_overview["room"][ct_room[data.sensor]] = data.power
return power_overview
def insert_new_captcha(**kwargs):
'''
:param kwargs: group_id, user_id, verify_code
:return:
'''
nmc = NewMemberCaptcha(**kwargs)
session = SESSION()
try:
session.add(nmc)
session.commit()
except Exception as e:
pass
finally:
session.close()
def get(self):
"""Get Appliances"""
args = self.get_parser.parse_args()
logger.info(f"[Get Appliances] User: {g.account}")
criteria_sensor: list = [Sensor.device_type == "thermo_sensor"]
criteria_device: list = list()
for argument in args:
if args[argument]:
criteria_sensor.append(
getattr(Sensor, argument) == args[argument])
criteria_device.append(
getattr(Device, argument) == args[argument])
appliances_status = []
sensors = SESSION.query(Sensor).filter(*criteria_sensor).all()
for sensor in sensors:
appliances_status.append(
self.get_latest_sensor_data(sensor.device_type, sensor.name,
sensor.room))
devices = SESSION.query(Device).filter(*criteria_device).all()
for device in devices:
appliances_status.append(
self.get_latest_sensor_data("ac", device.name, device.room))
return appliances_status
def post(self):
"""Control Device and Save Record"""
args = self.post_parser.parse_args()
logger.info(f"[Control Device] Device: {args['device']}, Command: {args['command']}, Trigger: {g.account}")
device = SESSION.query(Device).filter_by(name=args["device"]).first()
if not device:
return {"message": "Device Not Found"}, 400
response = control_device(device, args["command"])
if response:
record = {"device": args["device"], "command": args["command"], "trigger": g.account}
if ControlRecord(**record).add(SESSION):
return {"message": "Control Success"}
return {"message": "Control Success, Save Failed"}, 500
return {"message": "Control Failed"}, 400
def update_comment(self, app_url, access_token, comment_id, **kwargs):
LOG.info("update_comment")
request_headers = build_request_headers(access_token,
content_type="application/json")
payload = {}
if "message" in kwargs:
payload["comment_text"] = kwargs["message"]
if "likes" in kwargs:
payload["likes"] = kwargs["likes"]
LOG.debug(f"Request payload: {payload}")
response = SESSION.put(f"{app_url}{self.comment_url}/{comment_id}",
headers=request_headers, json=payload)
return response
