def repairs(): """ 添加设备维修任务 :return: """ if request.method == 'GET': data = db_session.query(Repair).all() return json.dumps({ 'code': '10001', 'message': '操作成功', 'data': data }, cls=AlchemyEncoder, ensure_ascii=False) if request.method == 'POST': json_data = request.values data = Repair(EquipmentCode=json_data.get('EquipmentCode'), No=get_no(json_data.get('ApplyTime')), Worker=current_user.Name, ApplyTime=json_data.get('ApplyTime'), FaultExpound=json_data.get('FaultExpound')) equipment = db_session.query(Equipment).filter_by( EquipmentCode=json_data.get('EquipmentCode')).first() equipment.Status = '待接单' db_session.add_all([data, equipment]) db_session.commit() db_session.close() return json.dumps({ 'code': '10000', 'message': '操作成功' }, cls=AlchemyEncoder, ensure_ascii=False)
def error_handler(e): """全局捕获异常""" db_session.rollback() re_path = request.path re_func = request.url_rule.endpoint.split('.')[1] re_method = request.method # root_path = os.path.abspath(os.path.dirname(os.path.dirname(__file__))) # file_path = os.path.join(root_path, 'logs\\logs.txt') ip = socket.gethostbyname(socket.gethostname()) now_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') result = f"{now_time} -- {ip} -- {re_path} -- {re_func} -- {re_method} -- {e}" print(result) db_session.add( RunError(Time=now_time, IP=ip, Path=re_path, Func=re_func, Method=re_method, Error=e)) db_session.commit() db_session.close() my_log(e) return json.dumps({ 'code': '2000', 'msg': result }, cls=MyEncoder, ensure_ascii=False)
def energy_contrast(): try: hours = [ '00:00:00', '01:00:00', '02:00:00', '03:00:00', '04:00:00', '05:00:00', '06:00:00', '07:00:00', '08:00:00', '09:00:00', '10:00:00', '11:00:00', '12:00:00', '13:00:00', '14:00:00', '15:00:00', '16:00:00', '17:00:00', '18:00:00', '19:00:00', '20:00:00', '21:00:00', '22:00:00', '23:00:00', '24:00:00' ] # yesterday = (datetime.now() - timedelta(1)).strftime('%Y-%m-%d ') yesterday = request.values.get('date') now = datetime.now().strftime('%Y-%m-%d ') now_hour = datetime.now().strftime('%H:00:00') rows = [] for i in range(0, len(hours)): if hours[i] != '24:00:00': yesterday_start_time = '"' + yesterday + ' ' + hours[i] + '"' yesterday_end_time = '"' + yesterday + ' ' + hours[i + 1] + '"' now_start_time = '"' + now + hours[i] + '"' now_end_time = '"' + now + hours[i + 1] + '"' sql1 = f'select sum(IncremenValue) as value from IncrementElectricTable where CollectionDate between {yesterday_start_time} and {yesterday_end_time}' result1 = db_session.execute(sql1).fetchall() yesterday_value = result1[0]['value'] if result1[0][ 'value'] is not None else 0 if hours[i] <= now_hour: sql2 = f'select sum(IncremenValue) as value from IncrementElectricTable where CollectionDate between {now_start_time} and {now_end_time}' result2 = db_session.execute(sql2).fetchall() today_value = result2[0]['value'] if result2[0][ 'value'] is not None else 0 else: today_value = ' ' data = { '时间': hours[i], '今日能耗': today_value, '对比日能耗': yesterday_value } rows.append(data) db_session.close() return json.dumps({ 'code': '200', 'mes': '查询成功', 'data': rows }, cls=MyEncoder, ensure_ascii=False) except Exception as e: print(str(e)) return json.dumps({ 'code': '200', 'mes': '查询失败', 'error': str(e) }, ensure_ascii=False)
def get_index_equipment(): try: query_type_data = db_session.query( Equipment.EquipmentType).filter_by().all() query_floor_data = db_session.query( Equipment.Floor).filter_by().order_by(Equipment.Floor.asc()).all() equipment_type = list(set(i[0] for i in query_type_data)) equipment_floor = list(set(i[0] for i in query_floor_data)) data = [] for item_floor in equipment_floor: result = {"楼层": item_floor} for item_type in equipment_type: query_result = db_session.query(Equipment).filter_by( Floor=item_floor, EquipmentType=item_type).all() result[item_type] = len(query_result) data.append(result) db_session.close() return json.dumps({ 'code': '200', 'mes': '查询成功', 'data': data }, ensure_ascii=False) except InvalidRequestError as e: print('/IndexEquipment', '1') db_session.rollback() print('/IndexEquipment', '2') re_path = request.path re_func = request.url_rule.endpoint.split('.')[1] re_method = request.method # root_path = os.path.abspath(os.path.dirname(os.path.dirname(__file__))) # file_path = os.path.join(root_path, 'logs\\logs.txt') ip = socket.gethostbyname(socket.gethostname()) now_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S') db_session.add( RunError(Time=now_time, IP=ip, Path=re_path, Func=re_func, Method=re_method, Error=str(e))) db_session.commit() my_log(str(e)) return json.dumps({'code': '200', 'mes': '事务回滚'}, ensure_ascii=False) except Exception as e: print(str(e)) return json.dumps({ 'code': '200', 'mes': '查询失败', 'error': str(e) }, ensure_ascii=False)
def repair_tasks(p): """ 更新维修任务表的状态 :param p: 更改工单状态 :return: """ if p == 'jiedan': json_data = request.values data = db_session.query(Repair).filter_by( No=json_data.get('No')).first() data.Status = '维修中' data.ReceiveWorker = current_user.Name data.ReceiveTime = json_data.get('Time') equipment = db_session.query(Equipment).filter_by( EquipmentCode=json_data.get('EquipmentCode')).first() equipment.Status = '维修中' db_session.add_all([data, equipment]) db_session.commit() db_session.close() return json.dumps({ 'code': '10001', 'message': '操作成功' }, cls=AlchemyEncoder, ensure_ascii=False) if p == 'over': json_data = request.values data = db_session.query(Repair).filter_by( No=json_data.get('No')).first() task_data = RepairTask(EquipmentCode=data.EquipmentCode, No=data.No, Status='维修完成', Worker=data.Worker, ReceiveWorker=data.ReceiveWorker, Content=json_data.get('Content'), ApplyTime=data.ApplyTime, ReceiveTime=data.ReceiveTime, EndTime=json_data.get('EndTime')) equipment = db_session.query(Equipment).filter_by( EquipmentCode=json_data.get('EquipmentCode')).first() equipment.Status = '运行中' db_session.add_all([task_data, equipment]) db_session.delete(data) db_session.commit() db_session.close() return json.dumps({ 'code': '10001', 'message': '操作成功' }, cls=AlchemyEncoder, ensure_ascii=False)
def count_energy(tags, start_time, end_time): try: result = 0.0 for tag in tags: sql = f'select sum(IncremenValue) as value from IncrementElectricTable where Address="{tag}" and CollectionDate between {start_time} and {end_time} ' data = db_session.execute(sql).fetchall() if data[0]['value'] is not None: result += float(data[0]['value']) else: result += 0.0 return result except Exception as err: db_session.rollback() db_session.close() my_log(err)
def get_pie(): start_time = "'" + request.values.get('StartTime') + "'" end_time = "'" + request.values.get('EndTime') + "'" if request.values.get('energy_type') == '水': sql = f'select sum(IncremenValue) as value from IncrementWaterTable where CollectionDate between {start_time} and {end_time} ' result = db_session.execute(sql).fetchall() value_data = 0 if result[0]['value'] is None else result[0]['value'] data = [{'设备类型': '设备能耗', '能耗': value_data}] return json.dumps({ 'code': '200', 'mes': '查询成功', 'data': data }, ensure_ascii=False) if request.values.get('energy_type') == '电': sql1 = f'select sum(IncremenValue) as value from IncrementElectricTable where CollectionDate between {start_time} and {end_time} and AreaName like "%照明%"' sql2 = f'select sum(IncremenValue) as value from IncrementElectricTable where CollectionDate between {start_time} and {end_time} and AreaName like "%空調%"' result1 = db_session.execute(sql1).fetchall() result2 = db_session.execute(sql2).fetchall() light_data = 0 if result1[0]['value'] is None else result1[0]['value'] kt_data = 0 if result2[0]['value'] is None else result2[0]['value'] data = [{ '设备类型': '照明设备', '能耗': light_data }, { '设备类型': '制冷设备', '能耗': kt_data }] db_session.close() return json.dumps({ 'code': '200', 'mes': '查询成功', 'data': data }, ensure_ascii=False) else: return json.dumps({ 'code': '200', 'mes': '查询成功', 'data': [] }, ensure_ascii=False)
def count_floor_energy(tags, start_time, end_time, water_day_total, total_energy): try: total = 0.0 i = 0 floorData = [] print('total_energy_2: ', total_energy) for tag in tags: i += 1 floor_total_energy = 0.0 AreaName = f'厚德楼{i}楼' for item in tag: print('采集点: ', item) sql = f'select sum(IncremenValue) as value from IncrementElectricTable where Address="{item}" and CollectionDate between {start_time} and {end_time} ' data = db_session.execute(sql).fetchall() if data[0]['value'] is not None: floor_total_energy += float(data[0]['value']) total += floor_total_energy else: floor_total_energy += 0.0 total += 0.0 ratio = '%.2f' % (floor_total_energy / total_energy * 100) if i == 7 or i == 8: floorData.append({ 'areaName': AreaName, 'electricity': floor_total_energy, 'water': water_day_total, 'ratio': ratio }) else: floorData.append({ 'areaName': AreaName, 'electricity': floor_total_energy, 'water': 0.0, 'ratio': ratio }) return floorData except Exception as err: db_session.rollback() db_session.close() my_log(err) return []
def keep_plans(): """保养计划""" try: json_data = request.get_json() # equipments = ['XXF-2', 'XXF-1', 'PYF-1'] equipments = json_data.get('EquipmentCode') if len(equipments) == 1: equipment_code = equipments[0] else: equipment_code = ' '.join(equipments) work_time = add_date(json_data.get('WeekTime'), json_data.get('StartTime')) work_type = json_data.get('Type') week_time = '单次' if work_type == '单次' else json_data.get('WeekTime') data = KeepPlan(EquipmentCode=equipment_code, No=get_no(json_data.get('ApplyTime')), Worker=current_user.Name, ApplyTime=json_data.get('ApplyTime'), Type=json_data.get('Type'), StartTime=json_data.get('StartTime'), Describe=json_data.get('Describe'), WorkTime=work_time, WeekTime=week_time) db_session.add(data) db_session.commit() db_session.close() return json.dumps({ 'code': '10001', 'message': '操作成功' }, cls=AlchemyEncoder, ensure_ascii=False) except Exception as e: logger.error(e) insertSyslog("error", "保养计划表添加错误:" + str(e), current_user.Name) return json.dumps({ 'code': '20002', 'message': str(e) }, cls=AlchemyEncoder, ensure_ascii=False)
def my_log(e): """ 程序日志记录 :param e:捕获异常参数` """ root_path = os.path.abspath(os.path.dirname(os.path.dirname(__file__))) file_path = os.path.join(root_path, 'run_log\\logs.txt') call_func = sys._getframe().f_back.f_code.co_name # user = login_user if login_user is None else 'no login' with open(file_path, 'a', encoding='utf-8') as f: print(f'{datetime.datetime.now()} -- {call_func} --- {e}' + "\n\n") f.write(f'{datetime.datetime.now()} -- {call_func} --- {e}' + "\n\n") f.close() db_session.add( RunError( Time=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), Func=call_func, Error=e)) db_session.commit() db_session.close() return '日志写入成功'
def keep_tasks(): """保养任务表""" try: query_data = db_session.query(KeepPlan).filter_by(Status='待保养').all() if request.method == 'GET': # 每页多少条 limit = int(request.values.get('limit', '5')) # 当前页 offset = int(request.values.get('offset', '1')) for item in query_data: q = db_session.query(KeepTask).filter_by(No=item.No).first() if not q and get_time_stamp(item.WorkTime): data = KeepTask(EquipmentCode=item.EquipmentCode, No=item.No, Worker=item.Worker, Status=item.Status, ApplyTime=item.ApplyTime, StartTime=item.StartTime, WorkTime=item.WorkTime, WeekTime=item.WeekTime, Type=item.Type) db_session.add(data) db_session.commit() # if item.Type == '单次': # pass # db_session.delete(item) # db_session.commit() data = db_session.query(KeepTask).order_by( KeepTask.ApplyTime.desc()).limit(limit).offset( (offset - 1) * limit) total = db_session.query(KeepTask).count() return json.dumps( { 'code': '10001', 'message': '操作成功', 'data': { 'rows': data.all(), 'total': total } }, cls=AlchemyEncoder, ensure_ascii=False) if request.method == 'POST': json_data = request.values no = json_data.get('No') end_time = json_data.get('EndTime') content = json_data.get('Content') item = db_session.query(KeepTask).filter_by(No=no).first() data = KeepRecord(EquipmentCode=item.EquipmentCode, No=no, Worker=item.Worker, Status='已完成', Type=item.Type, KeepWorker=current_user.Name, ApplyTime=item.ApplyTime, StartTime=item.StartTime, Describe=item.Describe, Content=content, WeekTime=item.WeekTime, EndTime=end_time) db_session.delete(item) db_session.commit() keep_plan = db_session.query(KeepPlan).filter_by(No=no).first() if keep_plan and keep_plan.Type == '周期': keep_plan.WorkTime = add_date(keep_plan.WeekTime, keep_plan.WorkTime) db_session.add_all([data, keep_plan]) db_session.commit() db_session.close() return json.dumps({ 'code': '10001', 'message': '操作成功' }, cls=AlchemyEncoder, ensure_ascii=False) else: db_session.delete(keep_plan) db_session.add(data) db_session.commit() db_session.close() return json.dumps({ 'code': '10001', 'message': '操作成功' }, cls=AlchemyEncoder, ensure_ascii=False) except Exception as e: logger.error(e) insertSyslog("error", "保养任务表修改错误:" + str(e), current_user.Name) return json.dumps({ 'code': '20002', 'message': str(e) }, cls=AlchemyEncoder, ensure_ascii=False)
def energys(): try: energy_type = request.values.get('energy_type') start_time = '"' + request.values.get('start_time') + '"' end_time = '"' + request.values.get('end_time') + '"' # start_time = '"' + '2020-01-31 23:00:00' + '"' # end_time = '"' + '22020-10-31 23:00:00' + '"' data = [] if energy_type == '电': sql = f'select AreaName,Address, sum(IncremenValue) as value from IncrementElectricTable where CollectionDate between {start_time} and {end_time} group by Address' results = db_session.execute(sql).fetchall() db_session.close() # print(results) for result in results: value = '%.2f' % result[2] if result[2] is not None else '0.0' data.append({ "AreaName": result[0], "Address": result[1], "Value": value, "StartTime": request.values.get('start_time'), "EndTime": request.values.get('end_time'), "Unit": "KW/h" }) else: sql = f'select AreaName,Address, sum(IncremenValue) as value from IncrementWaterTable where CollectionDate between {start_time} and {end_time} group by AreaName' results = db_session.execute(sql).fetchall() db_session.close() # print(results) for result in results: value = '%.2f' % float( result[2]) if result[2] is not None else '0.0' data.append({ "AreaName": result[0], "Address": result[1], "Value": value, "StartTime": request.values.get('start_time'), "EndTime": request.values.get('end_time'), "Unit": "m³" }) # data = [(result[0], result[1], '%.2f' % result[2], request.values.get('start_time'), request.values.get('end_time'), 'm³') for result in results] db_session.close() return json.dumps({ 'code': '200', 'mes': '查询成功', 'data': data }, ensure_ascii=False) except InvalidRequestError as e: print('/energy', '1') db_session.rollback() print('/energy', '2') re_path = request.path re_func = request.url_rule.endpoint.split('.')[1] re_method = request.method # root_path = os.path.abspath(os.path.dirname(os.path.dirname(__file__))) # file_path = os.path.join(root_path, 'logs\\logs.txt') ip = socket.gethostbyname(socket.gethostname()) now_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S') db_session.add( RunError(Time=now_time, IP=ip, Path=re_path, Func=re_func, Method=re_method, Error=str(e))) my_log(str(e)) return json.dumps({'code': '200', 'mes': '事务回滚'}, ensure_ascii=False) except Exception as e: # db_session.rollback() print(str(e)) return json.dumps({ 'code': '200', 'mes': '查询失败', 'error': str(e) }, ensure_ascii=False)
redis_coon.hset(REDIS_TABLENAME, 'indicator', json_data) start_m = datetime.now().strftime("%Y-%m-01 00:00:00") end_m = datetime.now().strftime("%Y-%m-01 00:00:00") # 当前月用电量总和 now_month_sql = f'select sum(IncremenValue) as value from IncrementElectricTable and CollectionDate between {start_m} and {end_m} ' # 楼层实时用能数据 L_tags = [['COM2.KT1F.总有功电量', 'COM2.LIGHT1F.总有功电量'], ['COM2.KT2F.总有功电量', 'COM2.LIGHT2F.总有功电量'], ['COM2.KT3F.总有功电量', 'COM2.LIGHT3F.总有功电量'], ['COM2.KT4F.总有功电量', 'COM2.LIGHT4F.总有功电量'], ['COM2.KT5F.总有功电量', 'COM2.LIGHT5F.总有功电量'], ['COM2.KT6F.总有功电量', 'COM2.LIGHT6F.总有功电量'], ['COM2.KT7F.总有功电量', 'COM2.LIGHT7F.总有功电量'], ['COM2.KT8F.总有功电量', 'COM2.LIGHT8F.总有功电量'], ['COM2.KT9F.总有功电量', 'COM2.LIGHT9F.总有功电量'], ['COM2.KT10F.总有功电量', 'COM2.LIGHT10F.总有功电量'], ['COM2.KT11F.总有功电量', 'COM2.LIGHT11F.总有功电量'], ['COM2.KT12F.总有功电量', 'COM2.LIGHT12F.总有功电量']] print('today_energy: ', today_energy) floor_data = count_floor_energy(L_tags, today_start_time, today_end_time, water_day_total, today_energy) data = json.dumps(floor_data, ensure_ascii=False) redis_coon.hset(REDIS_TABLENAME, 'floorData', data) print('结束计算能耗数据') time.sleep(180) except Exception as e: db_session.rollback() db_session.close() my_log(e)