MyEMS开源能源管理系统核心代码解读003（下）
接上回详细内容如下：
`
#################################################################################################################### # Step 18: for each tenant in list, get energy input data from energy database #################################################################################################################### energy_tenant_hourly = dict() if tenant_list is not None and len(tenant_list) > 0: try: for tenant in tenant_list: tenant_id = str(tenant['id'])
query = (" SELECT start_datetime_utc, energy_category_id, actual_value " " FROM tbl_tenant_input_category_hourly " " WHERE tenant_id = %s " " AND start_datetime_utc >= %s " " AND start_datetime_utc < %s " " ORDER BY start_datetime_utc ") cursor_energy_db.execute(query, (tenant_id, start_datetime_utc, end_datetime_utc,)) rows_energy_values = cursor_energy_db.fetchall() if rows_energy_values is None or len(rows_energy_values) == 0: energy_tenant_hourly[tenant_id] = None else: energy_tenant_hourly[tenant_id] = dict() for row_energy_value in rows_energy_values: current_datetime_utc = row_energy_value[0] if current_datetime_utc not in energy_tenant_hourly[tenant_id]: energy_tenant_hourly[tenant_id][current_datetime_utc] = dict() energy_category_id = row_energy_value[1] actual_value = row_energy_value[2] energy_tenant_hourly[tenant_id][current_datetime_utc][energy_category_id] = actual_value except Exception as e: error_string = "Error in step 18 of space_energy_input_category.worker " + str(e) if cursor_energy_db: cursor_energy_db.close() if cnx_energy_db: cnx_energy_db.close() print(error_string) return error_string
#################################################################################################################### # Step 19: for each child space in list, get energy input data from energy database #################################################################################################################### energy_child_space_hourly = dict() if child_space_list is not None and len(child_space_list) > 0: try: for child_space in child_space_list: child_space_id = str(child_space['id'])
query = (" SELECT start_datetime_utc, energy_category_id, actual_value " " FROM tbl_space_input_category_hourly " " WHERE space_id = %s " " AND start_datetime_utc >= %s " " AND start_datetime_utc < %s " " ORDER BY start_datetime_utc ") cursor_energy_db.execute(query, (child_space_id, start_datetime_utc, end_datetime_utc,)) rows_energy_values = cursor_energy_db.fetchall() if rows_energy_values is None or len(rows_energy_values) == 0: energy_child_space_hourly[child_space_id] = None else: energy_child_space_hourly[child_space_id] = dict() for row_energy_value in rows_energy_values: current_datetime_utc = row_energy_value[0] if current_datetime_utc not in energy_child_space_hourly[child_space_id]: energy_child_space_hourly[child_space_id][current_datetime_utc] = dict() energy_category_id = row_energy_value[1] actual_value = row_energy_value[2] energy_child_space_hourly[child_space_id][current_datetime_utc][energy_category_id] \ = actual_value except Exception as e: error_string = "Error in step 19 of space_energy_input_category.worker " + str(e) if cursor_energy_db: cursor_energy_db.close() if cnx_energy_db: cnx_energy_db.close() print(error_string) return error_string
#################################################################################################################### # Step 20: determine common time slot to aggregate ####################################################################################################################
common_start_datetime_utc = start_datetime_utc common_end_datetime_utc = end_datetime_utc
print("Getting common time slot of energy values for all meters") if energy_meter_hourly is not None and len(energy_meter_hourly) > 0: for meter_id, energy_hourly in energy_meter_hourly.items(): if energy_hourly is None or len(energy_hourly) == 0: common_start_datetime_utc = None common_end_datetime_utc = None break else: if common_start_datetime_utc < min(energy_hourly.keys()): common_start_datetime_utc = min(energy_hourly.keys()) if common_end_datetime_utc > max(energy_hourly.keys()): common_end_datetime_utc = max(energy_hourly.keys())
print("Getting common time slot of energy values for all virtual meters") if common_start_datetime_utc is not None and common_end_datetime_utc is not None: if energy_virtual_meter_hourly is not None and len(energy_virtual_meter_hourly) > 0: for meter_id, energy_hourly in energy_virtual_meter_hourly.items(): if energy_hourly is None or len(energy_hourly) == 0: common_start_datetime_utc = None common_end_datetime_utc = None break else: if common_start_datetime_utc < min(energy_hourly.keys()): common_start_datetime_utc = min(energy_hourly.keys()) if common_end_datetime_utc > max(energy_hourly.keys()): common_end_datetime_utc = max(energy_hourly.keys())
print("Getting common time slot of energy values for all offline meters") if common_start_datetime_utc is not None and common_end_datetime_utc is not None: if energy_offline_meter_hourly is not None and len(energy_offline_meter_hourly) > 0: for meter_id, energy_hourly in energy_offline_meter_hourly.items(): if energy_hourly is None or len(energy_hourly) == 0: common_start_datetime_utc = None common_end_datetime_utc = None break else: if common_start_datetime_utc < min(energy_hourly.keys()): common_start_datetime_utc = min(energy_hourly.keys()) if common_end_datetime_utc > max(energy_hourly.keys()): common_end_datetime_utc = max(energy_hourly.keys())
print("Getting common time slot of energy values for all combined equipments") if common_start_datetime_utc is not None and common_end_datetime_utc is not None: if energy_combined_equipment_hourly is not None and len(energy_combined_equipment_hourly) > 0: for combined_equipment_id, energy_hourly in energy_combined_equipment_hourly.items(): if energy_hourly is None or len(energy_hourly) == 0: common_start_datetime_utc = None common_end_datetime_utc = None break else: if common_start_datetime_utc < min(energy_hourly.keys()): common_start_datetime_utc = min(energy_hourly.keys()) if common_end_datetime_utc > max(energy_hourly.keys()): common_end_datetime_utc = max(energy_hourly.keys())
print("Getting common time slot of energy values for all equipments") if common_start_datetime_utc is not None and common_end_datetime_utc is not None: if energy_equipment_hourly is not None and len(energy_equipment_hourly) > 0: for equipment_id, energy_hourly in energy_equipment_hourly.items(): if energy_hourly is None or len(energy_hourly) == 0: common_start_datetime_utc = None common_end_datetime_utc = None break else: if common_start_datetime_utc < min(energy_hourly.keys()): common_start_datetime_utc = min(energy_hourly.keys()) if common_end_datetime_utc > max(energy_hourly.keys()): common_end_datetime_utc = max(energy_hourly.keys())
print("Getting common time slot of energy values for all shopfloors") if common_start_datetime_utc is not None and common_end_datetime_utc is not None: if energy_shopfloor_hourly is not None and len(energy_shopfloor_hourly) > 0: for shopfloor_id, energy_hourly in energy_shopfloor_hourly.items(): if energy_hourly is None or len(energy_hourly) == 0: common_start_datetime_utc = None common_end_datetime_utc = None break else: if common_start_datetime_utc < min(energy_hourly.keys()): common_start_datetime_utc = min(energy_hourly.keys()) if common_end_datetime_utc > max(energy_hourly.keys()): common_end_datetime_utc = max(energy_hourly.keys())
print("Getting common time slot of energy values for all stores") if common_start_datetime_utc is not None and common_end_datetime_utc is not None: if energy_store_hourly is not None and len(energy_store_hourly) > 0: for store_id, energy_hourly in energy_store_hourly.items(): if energy_hourly is None or len(energy_hourly) == 0: common_start_datetime_utc = None common_end_datetime_utc = None break else: if common_start_datetime_utc < min(energy_hourly.keys()): common_start_datetime_utc = min(energy_hourly.keys()) if common_end_datetime_utc > max(energy_hourly.keys()): common_end_datetime_utc = max(energy_hourly.keys())
print("Getting common time slot of energy values for all tenants") if common_start_datetime_utc is not None and common_end_datetime_utc is not None: if energy_tenant_hourly is not None and len(energy_tenant_hourly) > 0: for tenant_id, energy_hourly in energy_tenant_hourly.items(): if energy_hourly is None or len(energy_hourly) == 0: common_start_datetime_utc = None common_end_datetime_utc = None break else: if common_start_datetime_utc < min(energy_hourly.keys()): common_start_datetime_utc = min(energy_hourly.keys()) if common_end_datetime_utc > max(energy_hourly.keys()): common_end_datetime_utc = max(energy_hourly.keys())
print("Getting common time slot of energy values for all child spaces") if common_start_datetime_utc is not None and common_end_datetime_utc is not None: if energy_child_space_hourly is not None and len(energy_child_space_hourly) > 0: for child_space_id, energy_hourly in energy_child_space_hourly.items(): if energy_hourly is None or len(energy_hourly) == 0: common_start_datetime_utc = None common_end_datetime_utc = None break else: if common_start_datetime_utc < min(energy_hourly.keys()): common_start_datetime_utc = min(energy_hourly.keys()) if common_end_datetime_utc > max(energy_hourly.keys()): common_end_datetime_utc = max(energy_hourly.keys())
if (energy_meter_hourly is None or len(energy_meter_hourly) == 0) and \ (energy_virtual_meter_hourly is None or len(energy_virtual_meter_hourly) == 0) and \ (energy_offline_meter_hourly is None or len(energy_offline_meter_hourly) == 0) and \ (energy_combined_equipment_hourly is None or len(energy_combined_equipment_hourly) == 0) and \ (energy_equipment_hourly is None or len(energy_equipment_hourly) == 0) and \ (energy_shopfloor_hourly is None or len(energy_shopfloor_hourly) == 0) and \ (energy_store_hourly is None or len(energy_store_hourly) == 0) and \ (energy_tenant_hourly is None or len(energy_tenant_hourly) == 0) and \ (energy_child_space_hourly is None or len(energy_child_space_hourly) == 0): # There isn't any energy data print("There isn't any energy data") # continue the for space loop to the next space print("continue the for space loop to the next space") if cursor_energy_db: cursor_energy_db.close() if cnx_energy_db: cnx_energy_db.close() return None
print("common_start_datetime_utc: " + str(common_start_datetime_utc)) print("common_end_datetime_utc: " + str(common_end_datetime_utc))
#################################################################################################################### # Step 21: aggregate energy data in the common time slot by energy categories and hourly ####################################################################################################################
print("Step 21: aggregate energy data in the common time slot by energy categories and hourly") aggregated_values = list() try: current_datetime_utc = common_start_datetime_utc while common_start_datetime_utc is not None \ and common_end_datetime_utc is not None \ and current_datetime_utc <= common_end_datetime_utc: aggregated_value = dict() aggregated_value['start_datetime_utc'] = current_datetime_utc aggregated_value['meta_data'] = dict()
if meter_list is not None and len(meter_list) > 0: for meter in meter_list: meter_id = str(meter['id']) energy_category_id = meter['energy_category_id'] actual_value = energy_meter_hourly[meter_id].get(current_datetime_utc, Decimal(0.0)) aggregated_value['meta_data'][energy_category_id] = \ aggregated_value['meta_data'].get(energy_category_id, Decimal(0.0)) + actual_value
if virtual_meter_list is not None and len(virtual_meter_list) > 0: for virtual_meter in virtual_meter_list: virtual_meter_id = str(virtual_meter['id']) energy_category_id = virtual_meter['energy_category_id'] actual_value = energy_virtual_meter_hourly[virtual_meter_id].get(current_datetime_utc, Decimal(0.0)) aggregated_value['meta_data'][energy_category_id] = \ aggregated_value['meta_data'].get(energy_category_id, Decimal(0.0)) + actual_value
if offline_meter_list is not None and len(offline_meter_list) > 0: for offline_meter in offline_meter_list: offline_meter_id = str(offline_meter['id']) energy_category_id = offline_meter['energy_category_id'] actual_value = energy_offline_meter_hourly[offline_meter_id].get(current_datetime_utc, Decimal(0.0)) aggregated_value['meta_data'][energy_category_id] = \ aggregated_value['meta_data'].get(energy_category_id, Decimal(0.0)) + actual_value
if combined_equipment_list is not None and len(combined_equipment_list) > 0: for combined_equipment in combined_equipment_list: combined_equipment_id = str(combined_equipment['id']) meta_data_dict = \ energy_combined_equipment_hourly[combined_equipment_id].get(current_datetime_utc, None) if meta_data_dict is not None and len(meta_data_dict) > 0: for energy_category_id, actual_value in meta_data_dict.items(): aggregated_value['meta_data'][energy_category_id] = \ aggregated_value['meta_data'].get(energy_category_id, Decimal(0.0)) + actual_value
if equipment_list is not None and len(equipment_list) > 0: for equipment in equipment_list: equipment_id = str(equipment['id']) meta_data_dict = energy_equipment_hourly[equipment_id].get(current_datetime_utc, None) if meta_data_dict is not None and len(meta_data_dict) > 0: for energy_category_id, actual_value in meta_data_dict.items(): aggregated_value['meta_data'][energy_category_id] = \ aggregated_value['meta_data'].get(energy_category_id, Decimal(0.0)) + actual_value
if shopfloor_list is not None and len(shopfloor_list) > 0: for shopfloor in shopfloor_list: shopfloor_id = str(shopfloor['id']) meta_data_dict = energy_shopfloor_hourly[shopfloor_id].get(current_datetime_utc, None) if meta_data_dict is not None and len(meta_data_dict) > 0: for energy_category_id, actual_value in meta_data_dict.items(): aggregated_value['meta_data'][energy_category_id] = \ aggregated_value['meta_data'].get(energy_category_id, Decimal(0.0)) + actual_value
if store_list is not None and len(store_list) > 0: for store in store_list: store_id = str(store['id']) meta_data_dict = energy_store_hourly[store_id].get(current_datetime_utc, None) if meta_data_dict is not None and len(meta_data_dict) > 0: for energy_category_id, actual_value in meta_data_dict.items(): aggregated_value['meta_data'][energy_category_id] = \ aggregated_value['meta_data'].get(energy_category_id, Decimal(0.0)) + actual_value
if tenant_list is not None and len(tenant_list) > 0: for tenant in tenant_list: tenant_id = str(tenant['id']) meta_data_dict = energy_tenant_hourly[tenant_id].get(current_datetime_utc, None) if meta_data_dict is not None and len(meta_data_dict) > 0: for energy_category_id, actual_value in meta_data_dict.items(): aggregated_value['meta_data'][energy_category_id] = \ aggregated_value['meta_data'].get(energy_category_id, Decimal(0.0)) + actual_value
if child_space_list is not None and len(child_space_list) > 0: for child_space in child_space_list: child_space_id = str(child_space['id']) meta_data_dict = energy_child_space_hourly[child_space_id].get(current_datetime_utc, None) if meta_data_dict is not None and len(meta_data_dict) > 0: for energy_category_id, actual_value in meta_data_dict.items(): aggregated_value['meta_data'][energy_category_id] = \ aggregated_value['meta_data'].get(energy_category_id, Decimal(0.0)) + actual_value
aggregated_values.append(aggregated_value)
current_datetime_utc += timedelta(minutes=config.minutes_to_count)
except Exception as e: error_string = "Error in step 21 of space_energy_input_category.worker " + str(e) if cursor_energy_db: cursor_energy_db.close() if cnx_energy_db: cnx_energy_db.close() print(error_string) return error_string
#################################################################################################################### # Step 22: save energy data to energy database #################################################################################################################### print("Step 22: save energy data to energy database")
while len(aggregated_values) > 0: insert_100 = aggregated_values[:100] aggregated_values = aggregated_values[100:] try: add_values = (" INSERT INTO tbl_space_input_category_hourly " " (space_id, " " energy_category_id, " " start_datetime_utc, " " actual_value) " " VALUES ")
for aggregated_value in insert_100: for energy_category_id, actual_value in aggregated_value['meta_data'].items(): add_values += " (" + str(space['id']) + "," add_values += " " + str(energy_category_id) + "," add_values += "'" + aggregated_value['start_datetime_utc'].isoformat()[0:19] + "'," add_values += str(actual_value) + "), " print("add_values:" + add_values) # trim ", " at the end of string and then execute cursor_energy_db.execute(add_values[:-2]) cnx_energy_db.commit()
except Exception as e: error_string = "Error in step 22 of space_energy_input_category.worker " + str(e) print(error_string) if cursor_energy_db: cursor_energy_db.close() if cnx_energy_db: cnx_energy_db.close() return error_string
if cursor_energy_db: cursor_energy_db.close() if cnx_energy_db: cnx_energy_db.close() return None
`
浙公网安备 33010602011771号