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