import copy import json from itertools import groupby from operator import itemgetter from pot_libs.logger import log from pot_libs.mysql_util.mysql_util import MysqlUtil from pot_libs.sanic_api import summary from pot_libs.utils.pendulum_wrapper import my_pendulum from unify_api.constants import SLOTS_15MIN, SLOTS from unify_api.modules.common.dao.common_dao import company_by_cids, \ point_by_points from unify_api.modules.common.procedures.cids import get_proxy_cids, get_cids from unify_api.modules.elec_charge.components.elec_charge_cps import PopReq from unify_api.modules.elec_charge.components.elec_statis_proxy_cps import \ StatisProxyReq from unify_api.modules.elec_charge.components.elec_statistics_cps import \ PcStatiResp, SlotValue from unify_api.modules.elec_charge.dao.elec_charge_dao import \ power_charge_p_aggs, power_charge_p_cid_aggs, histogram_aggs_points, \ power_charge_p_point_aggs from unify_api.modules.elec_charge.procedures.elec_statis_proxy_pds import \ proxy_today_yesterday_load, by_slots from unify_api.modules.elec_charge.common.utils import aver_price, \ load_power_charge from unify_api.utils.common_utils import process_es_data from unify_api.utils.es_query_body import es_process from unify_api.utils.time_format import last_time_str, proxy_power_slots, \ year_slots import io import pandas as pd @summary('代理版电量电费统计曲线') async def post_power_statis_proxy(req, body: StatisProxyReq) -> PcStatiResp: # 1.获取参数 product = req.json.get("product") proxy_id = body.proxy_id user_id = req.ctx.user_id cid = body.cid log.info(f"post_power_statis_proxy:{product}, type:{type(product)}") if product == 6 and cid: cid_list = await get_cids(user_id, product) else: # cid_list = await get_cids(user_id, product) cid_list = await get_proxy_cids(user_id, product, proxy_id) \ if proxy_id else () start = body.start end = body.end point_id = -1 date_type = body.date_type # 2. 如果是日统计,则需要增加今日/昨日负荷曲线, 15min一个点 if date_type == "day": # 电量电费 kwh_sv, charge_sv = await load_power_charge(cid_list, point_id, start, end, date_type) # 需要增加15min电量电费, 渠道版首页不需要下载,暂时去掉 # 今日/昨日负荷曲线 today_p = await proxy_today_yesterday_load(cid_list, start, end) ysd_start, ysd_end = last_time_str(start, end, "day") yesterday_p = await proxy_today_yesterday_load(cid_list, ysd_start, ysd_end) return PcStatiResp(kwh=kwh_sv, charge=charge_sv, today_p=today_p, yesterday_p=yesterday_p) elif date_type == "month": # 本月电量电费, 平均电价 kwh_sv, charge_sv = await load_power_charge(cid_list, point_id, start, end, date_type) this_aver_price = aver_price(kwh_sv, charge_sv) # 上月电量电费, 平均电价 last_start, last_end = last_time_str(start, end, "month") # 需要增加15min电量电费 last_kwh_sv, last_charge_sv = await load_power_charge(cid_list, point_id, last_start, last_end, date_type) last_aver_price = aver_price(last_kwh_sv, last_charge_sv) return PcStatiResp(kwh=kwh_sv, charge=charge_sv, this_aver_price=this_aver_price, last_aver_price=last_aver_price) elif date_type == "year": # 本月电量电费 kwh_sv, charge_sv = await load_power_charge(cid_list, point_id, start, end, date_type) this_aver_price = aver_price(kwh_sv, charge_sv) return PcStatiResp(kwh=kwh_sv, charge=charge_sv, this_aver_price=this_aver_price) else: start_f = my_pendulum.from_format(start, 'YYYY-MM-DD HH:mm:ss') end_f = my_pendulum.from_format(end, 'YYYY-MM-DD HH:mm:ss') diff_mm = (end_f - start_f).in_minutes() if diff_mm <= 48 * 60: # 自定义选时范围,不需要最后时间的数据,解决bug end = end_f.subtract(minutes=1).format("YYYY-MM-DD HH:mm:ss") # 电量电费 kwh_sv, charge_sv = await load_power_charge(cid_list, point_id, start, end, date_type) # 负荷曲线 this_p = await proxy_today_yesterday_load(cid_list, start, end) # 需要增加15min电量电费, 渠道版首页不需要下载,暂时去掉 return PcStatiResp(kwh=kwh_sv, charge=charge_sv, today_p=this_p) else: # 电量电费 kwh_sv, charge_sv = await load_power_charge(cid_list, point_id, start, end, date_type) # 平均电价 this_aver_price = aver_price(kwh_sv, charge_sv) return PcStatiResp(kwh=kwh_sv, charge=charge_sv, this_aver_price=this_aver_price) @summary('识电u首页电量电费统计曲线') async def post_power_statis_sdu(req, body: StatisProxyReq) -> PcStatiResp: date_type = body.date_type if date_type == "day": kwh_sv = SlotValue() kwh_sv.slots = ["00:00", "01:00", "02:00", "03:00", "04:00", "05:00", "06:00", "07:00", "08:00", "09:00", "10:00", "11:00", "12:00", "13:00", "14:00", "15:00", "16:00", "17:00", "18:00", "19:00", "20:00", "21:00", "22:00", "23:00"] kwh_sv.value = [6258.268905162811, 10016.823085308075, 8588.824381351471, 8935.39635181427, 8821.990401268005, 8544.7857503891, 7414.788442611694, 9187.431729316711, 7163.436096191406, 7256.216859817505, 2957.0944995880127, "", "", "", "", "", "", "", "", "", "", "", "", ""] charge_sv = SlotValue() charge_sv.slots = ["00:00", "01:00", "02:00", "03:00", "04:00", "05:00", "06:00", "07:00", "08:00", "09:00", "10:00", "11:00", "12:00", "13:00", "14:00", "15:00", "16:00", "17:00", "18:00", "19:00", "20:00", "21:00", "22:00", "23:00"] charge_sv.value = [1320.9250456094742, 2114.238419532776, 1812.8324270248413, 1885.9829701185226, 1862.0464475154877, 1803.5372290611267, 1565.0301276445389, 6018.399196624756, 4692.54298210144, 7311.137638092041, 2979.476005554199, "", "", "", "", "", "", "", "", "", "", "", "", ""] today_p = SlotValue() today_p.slots = ["00:00", "00:15", "00:30", "00:45", "01:00", "01:15", "01:30", "01:45", "02:00", "02:15", "02:30", "02:45", "03:00", "03:15", "03:30", "03:45", "04:00", "04:15", "04:30", "04:45", "05:00", "05:15", "05:30", "05:45", "06:00", "06:15", "06:30", "06:45", "07:00", "07:15", "07:30", "07:45", "08:00", "08:15", "08:30", "08:45", "09:00", "09:15", "09:30", "09:45", "10:00", "10:15", "10:30", "10:45", "11:00", "11:15", "11:30", "11:45", "12:00", "12:15", "12:30", "12:45", "13:00", "13:15", "13:30", "13:45", "14:00", "14:15", "14:30", "14:45", "15:00", "15:15", "15:30", "15:45", "16:00", "16:15", "16:30", "16:45", "17:00", "17:15", "17:30", "17:45", "18:00", "18:15", "18:30", "18:45", "19:00", "19:15", "19:30", "19:45", "20:00", "20:15", "20:30", "20:45", "21:00", "21:15", "21:30", "21:45", "22:00", "22:15", "22:30", "22:45", "23:00", "23:15", "23:30", "23:45"] today_p.value = [5088.19149017334, 5765.95064163208, 7076.445976257324, 7102.487512588501, 9091.029479980469, 6776.583387374878, 13139.973567962646, 11059.705905914307, 8995.41967010498, 5835.527791976929, 12547.395118713379, 6976.954944610596, 5414.047164916992, 9901.249280929565, 6858.300985336304, 13567.987976074219, 8942.03335762024, 11528.658069610596, 4859.732757568359, 9957.537420272827, 10950.249084472656, 5385.674430847168, 10921.455522537231, 6921.763963699341, 5087.754871368408, 7566.874572753906, 5653.077251434326, 11351.447074890137, 10268.422653198242, 8868.727451324463, 6196.819835662842, 11415.756977081299, 5602.684371948242, 7545.353427886963, 5787.765808105469, 9717.940776824951, 9814.94384765625, 7809.268592834473, 7226.157783508301, 4174.497215270996, 11828.37799835205, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""] yesterday_p = SlotValue() yesterday_p.slots = ["00:00", "00:15", "00:30", "00:45", "01:00", "01:15", "01:30", "01:45", "02:00", "02:15", "02:30", "02:45", "03:00", "03:15", "03:30", "03:45", "04:00", "04:15", "04:30", "04:45", "05:00", "05:15", "05:30", "05:45", "06:00", "06:15", "06:30", "06:45", "07:00", "07:15", "07:30", "07:45", "08:00", "08:15", "08:30", "08:45", "09:00", "09:15", "09:30", "09:45", "10:00", "10:15", "10:30", "10:45", "11:00", "11:15", "11:30", "11:45", "12:00", "12:15", "12:30", "12:45", "13:00", "13:15", "13:30", "13:45", "14:00", "14:15", "14:30", "14:45", "15:00", "15:15", "15:30", "15:45", "16:00", "16:15", "16:30", "16:45", "17:00", "17:15", "17:30", "17:45", "18:00", "18:15", "18:30", "18:45", "19:00", "19:15", "19:30", "19:45", "20:00", "20:15", "20:30", "20:45", "21:00", "21:15", "21:30", "21:45", "22:00", "22:15", "22:30", "22:45", "23:00", "23:15", "23:30", "23:45"] yesterday_p.value = [9293.347217559814, 11788.63765335083, 5568.857587814331, 9149.475133895874, 7084.443334579468, 5054.889009475708, 7166.356422424316, 5185.683692932129, 7122.029258728027, 8115.147048950195, 11925.44121170044, 3790.9156036376953, 11058.604145050049, 11141.001302719116, 2928.560749053955, 1773.5871753692627, 11096.078073501587, 2920.4704761505127, 11100.638122558594, 879.9124164581299, 8153.336156845093, 8124.552415847778, 9234.332719802856, 7351.442501068115, 7091.113838195801, 7231.719375610352, 7182.04577255249, 7173.044284820557, 9262.210247039795, 11445.085227966309, 8507.608936309814, 6652.65474319458, 5570.864601135254, 6590.559619903564, 13705.999382019043, 7595.505886077881, 9704.105606079102, 7647.639854431152, 3065.534523010254, 9745.466217041016, 7686.316566467285, 5660.9982986450195, 7867.017860412598, 5622.245002746582, 9666.338069915771, 5614.943454742432, 4070.527126312256, 7050.554489135742, 11608.867420196533, 9478.619812011719, 3480.9703407287598, 3621.1612091064453, 7584.301612854004, 3438.345561981201, 3593.394760131836, 5690.614585876465, 3703.5315322875977, 1515.8611526489258, 5678.209159851074, 4207.354759216309, 3701.366111755371, 7802.621482849121, 5768.858703613281, 5701.865165710449, 3807.8991165161133, 5849.516334533691, 3801.489601135254, 5827.148788452148, 3762.4312477111816, 1595.1804275512695, 3519.613571166992, 3334.448287963867, 4151.983123779297, 3422.100399017334, 5473.698417663574, 3458.858123779297, 1348.6159782409668, 1284.312427520752, 7421.998802185059, 3348.8148651123047, 3466.119976043701, 5405.394309997559, 1276.0592041015625, 1235.291217803955, 3228.3655281066895, 3253.4652252197266, 5292.516162872314, 3204.206081390381, 5223.387954711914, 1453.5232048034668, 1630.2007751464844, 4895.097999572754, 1085.7019882202148, 5134.565368652344, 3067.1756744384766, 11173.896724700928] return PcStatiResp(kwh=kwh_sv, charge=charge_sv, today_p=today_p, yesterday_p=yesterday_p) elif date_type == "month": kwh_sv = SlotValue() kwh_sv.slots = ["10-24", "10-25", "10-26", "10-27", "10-28", "10-29", "10-30", "10-31", "11-01", "11-02", "11-03", "11-04", "11-05", "11-06", "11-07", "11-08", "11-09", "11-10", "11-11", "11-12", "11-13", "11-14", "11-15", "11-16", "11-17", "11-18", "11-19", "11-20", "11-21", "11-22", "11-23"] kwh_sv.value = [191087.3690919876, 74046.85134029388, 169423.54148042202, 173451.91290092468, 183969.8257341385, 174893.8898410797, 34484.07443714142, 27011.772797584534, 58135.07944905758, 56016.78330171108, 140708.56240606308, 125845.94271659851, 111003.68944740295, 39563.79104876518, 29855.76039624214, 26631.301872253418, 37498.40665388107, 31237.861385822296, 39706.15871620178, 33636.695578575134, 48923.965045928955, 89037.77690839767, 100229.32058906555, 94250.28391981125, 71066.1781206131, 70595.5472164154, 52510.23557817936, 105119.9881310463, 179163.7517747879, 141606.7146730423, 87067.44291448593] charge_sv = SlotValue() charge_sv.slots = ["10-24", "10-25", "10-26", "10-27", "10-28", "10-29", "10-30", "10-31", "11-01", "11-02", "11-03", "11-04", "11-05", "11-06", "11-07", "11-08", "11-09", "11-10", "11-11", "11-12", "11-13", "11-14", "11-15", "11-16", "11-17", "11-18", "11-19", "11-20", "11-21", "11-22", "11-23"] charge_sv.value = [117636.47115492821, 36617.02539169788, 108928.21406883001, 106578.6646271944, 115377.79622995853, 109276.41187632084, 20784.91738796234, 17953.9272646904, 25422.20073568821, 38856.65817859769, 88081.10760295391, 78715.13790082932, 73832.59619486332, 28974.23614412546, 20237.774165689945, 17435.33741438389, 27406.703642070293, 20706.77432358265, 27695.862004876137, 22748.527092278004, 32926.029972970486, 58261.87518644333, 61275.00333726406, 57262.9490352273, 40151.92629635334, 40142.38252270222, 32061.665004611015, 74513.86127066612, 114936.01462650299, 80420.69597375393, 35303.08493697643] this_aver_price = SlotValue() this_aver_price.slots = ["10-24", "10-25", "10-26", "10-27", "10-28", "10-29", "10-30", "10-31", "11-01", "11-02", "11-03", "11-04", "11-05", "11-06", "11-07", "11-08", "11-09", "11-10", "11-11", "11-12", "11-13", "11-14", "11-15", "11-16", "11-17", "11-18", "11-19", "11-20", "11-21", "11-22", "11-23"] this_aver_price.value = [0.6156161535632382, 0.49451157920839356, 0.6429343473581998, 0.6144565536620624, 0.6271560880679161, 0.6248154922714378, 0.6027396045049636, 0.6646704531105744, 0.4372953641177199, 0.6936610045834384, 0.6259825706183075, 0.6254880864780327, 0.6651364162976541, 0.7323422598307796, 0.6778515736024333, 0.6546933941877393, 0.7308764848341551, 0.6628742623520535, 0.6975205585317691, 0.6763008880922197, 0.6730041185758372, 0.6543500658870146, 0.6113480863398052, 0.6075626157683195, 0.5649934660649364, 0.5686248510779729, 0.6105793404197609, 0.7088457922747718, 0.6415137743430359, 0.5679158376030289, 0.4054682640863781] last_aver_price = SlotValue() last_aver_price.slots = ["09-24", "09-25", "09-26", "09-27", "09-28", "09-29", "09-30", "10-01", "10-02", "10-03", "10-04", "10-05", "10-06", "10-07", "10-08", "10-09", "10-10", "10-11", "10-12", "10-13", "10-14", "10-15", "10-16", "10-17", "10-18", "10-19", "10-20", "10-21", "10-22", "10-23"] last_aver_price.value = [0.6173438977042207, 0.63415687958691, 0.6297031320656554, 0.48114453297921533, 0.6855699141280034, 0.6939461033783917, 0.6161468262616197, 0.6615765705227169, 0.6148073943145698, 0.6137319019686065, 0.6342796302756187, 0.6001306333621312, 0.6268474626435421, 0.6311068678126233, 0.7098522161881629, 0.6185188413887293, 0.6121327639069299, 0.6110496897945716, 0.5331591568551899, 0.6632352933101533, 0.644092567805303, 0.5683466114384315, 0.6318571635944108, 0.6206378367317664, 0.5886737264712703, 0.640022077944525, 0.6428458344730508, 0.6024729663254688, 0.6400440634276598, 0.6088876874990435] return PcStatiResp(kwh=kwh_sv, charge=charge_sv, this_aver_price=this_aver_price, last_aver_price=last_aver_price) @summary('电量电费统计曲线-优化版-管理版') async def post_power_statist_opt(req, body: PopReq) -> PcStatiResp: """目前知电U管理版使用""" # 1. 获取参数 cid_list = body.cid_list start = body.start end = body.end date_type = body.date_type if date_type == "range": date_type = "month" if date_type == "day": # 电量电费 kwh_sv, charge_sv = await load_power_charge(cid_list, -1, start, end, date_type) # 需要增加15min电量电费, 渠道版首页不需要下载,暂时去掉 # 今日/昨日负荷曲线 today_p = await proxy_today_yesterday_load(cid_list, start, end) ysd_start, ysd_end = last_time_str(start, end, "day") yesterday_p = await proxy_today_yesterday_load(cid_list, ysd_start, ysd_end) return PcStatiResp(kwh=kwh_sv, charge=charge_sv, today_p=today_p, yesterday_p=yesterday_p) elif date_type == "month": # 本月电量电费, 平均电价 kwh_sv, charge_sv = await load_power_charge(cid_list, -1, start, end, date_type) this_aver_price = aver_price(kwh_sv, charge_sv) # 上月电量电费, 平均电价 last_start, last_end = last_time_str(start, end, "month") # 需要增加15min电量电费 last_kwh_sv, last_charge_sv = await load_power_charge(cid_list, -1, last_start, last_end, date_type) last_aver_price = aver_price(last_kwh_sv, last_charge_sv) return PcStatiResp(kwh=kwh_sv, charge=charge_sv, this_aver_price=this_aver_price, last_aver_price=last_aver_price) else: # 不存在的date_type log.error(f"date_type:{date_type} not in (day, month)") return PcStatiResp() def division_down(list_p): """下载excel, 电量电费为万""" day_power = [ round(i / 10000, 2 if i / 10000 >= 1 else 4) if type(i) in [int, float] else i for i in list_p] day_power_tmp = [i for i in day_power if i] day_power.append(sum(day_power_tmp)) return day_power @summary('电量电费统计曲线-下载excel-管理版') async def get_power_statist_download(req): """目前知电U管理版使用""" output = io.BytesIO() writer = pd.ExcelWriter(output, engine='xlsxwriter') # 1. 获取参数 args = req.args cid_list = json.loads(args.get("cid_list")) start = args.get("start") end = args.get("end") date_type = args.get("date_type") # 查询出cid和工厂名对应关系 company_list = await company_by_cids(cid_list) # 把cid提出来 com_dic = process_es_data(company_list, key="cid") if date_type == "range": date_type = "month" if date_type == "day": slots_96 = SLOTS_15MIN slots_96_zj = copy.deepcopy(slots_96) slots_96_zj.append("总计") slots_24 = SLOTS[date_type] # 1. 96个点, 电量\电费\平均功率\电价 es_re_96 = await power_charge_p_aggs(start, end, cid_list, "15m") es_re_96_dic = es_process(es_re_96, "HH:mm", time_key="create_time") # 为了es结果和slots对应 kwh_96, charge_96, p_96, price_96 = by_slots(slots_96, es_re_96_dic) p_96.append("") price_96.append("") dict_96 = { "时间": slots_96_zj, "电量(万kWh)": division_down(kwh_96), "电费(万元)": division_down(charge_96), "平均功率(kW)": p_96, "电价(元/kWh)": price_96, } df = pd.DataFrame(dict_96) df.to_excel(writer, sheet_name="96个点", index=False) # 2. 根据工厂聚合, 96个点电量\96个点电费 res_cid_96 = await power_charge_p_cid_aggs(start, end, cid_list, "15m") power_96_dic = {"电量(万kWh)": slots_96_zj} charge_96_dic = {"电费(万元)": slots_96_zj} has_power_cids = [] # es查询有数据的工厂 for cid, cid_info in groupby(res_cid_96, key=itemgetter("cid")): cid_one = [one for one in cid_info] has_power_cids.append(cid) cid_name = com_dic[cid]["shortname"] # 把slots作为key提出来 info_dic = es_process(cid_one, fmat="HH:mm", time_key="create_time") kwh_96, charge_96, p_96, price_96 = by_slots(slots_96, info_dic) power_96_dic[cid_name] = division_down(kwh_96) charge_96_dic[cid_name] = division_down(charge_96) # 没有电量数据的工厂, 设为空字符串 for cid in cid_list: if cid not in has_power_cids: cid_name = com_dic[cid]["shortname"] power_96_dic[cid_name] = list(str(' ') * 97) charge_96_dic[cid_name] = list(str(' ') * 97) df1 = pd.DataFrame(power_96_dic) df1.to_excel(writer, sheet_name="96个点电量", index=False) df2 = pd.DataFrame(charge_96_dic) df2.to_excel(writer, sheet_name="96个点电费", index=False) # 3. 24个点, 电量\电费\平均功率\电价 es_re_24 = await power_charge_p_aggs(start, end, cid_list, "hour") es_re_24_dic = es_process(es_re_24, fmat="HH:mm", time_key="create_time") # 为了es结果和slots对应 kwh_24, charge_24, p_24, price_24 = by_slots(slots_24, es_re_24_dic) slots_24_zj = copy.deepcopy(slots_24) slots_24_zj.append("总计") p_24.append("") price_24.append("") dict_24 = { "时间": slots_24_zj, "电量(万kWh)": division_down(kwh_24), "电费(万元)": division_down(charge_24), "平均功率(kW)": p_24, "电价(元/kWh)": price_24, } df_24 = pd.DataFrame(dict_24) df_24.to_excel(writer, sheet_name="24个点", index=False) # 4. 根据工厂聚合, 24个点电量\24个点电费 res_cid_24 = await power_charge_p_cid_aggs(start, end, cid_list, "hour") power_24_dic = {"电量(万kWh)": slots_24_zj} charge_24_dic = {"电费(万元)": slots_24_zj} has_power_cids_24 = [] # es查询有数据的工厂 for cid, cid_info in groupby(res_cid_24, key=itemgetter("cid")): cid_one = [one for one in cid_info] has_power_cids_24.append(cid) cid_name = com_dic[cid]["shortname"] # 把slots作为key提出来 info_dic_24 = es_process(cid_one, fmat="HH:mm", time_key="create_time") kwh_24, charge_24, p_24, price_24 = by_slots(slots_24, info_dic_24) power_24_dic[cid_name] = division_down(kwh_24) charge_24_dic[cid_name] = division_down(charge_24) # 没有电量数据的工厂, 设为空字符串 for cid in cid_list: if cid not in has_power_cids_24: cid_name = com_dic[cid]["shortname"] power_24_dic[cid_name] = list(str(' ') * 25) charge_24_dic[cid_name] = list(str(' ') * 25) df3 = pd.DataFrame(power_24_dic) df3.to_excel(writer, sheet_name="24个点电量", index=False) df4 = pd.DataFrame(charge_24_dic) df4.to_excel(writer, sheet_name="24个点电费", index=False) # data_type = month else: # 1. 电量电费信息, 电量/电费/电价 slots_m = proxy_power_slots(start, end) slots_m_p = copy.deepcopy(slots_m) slots_m_p.append("总计") es_re_m = await power_charge_p_aggs(start, end, cid_list, "day") es_re_m_dic = es_process(es_re_m, fmat="MM-DD", time_key="create_time") # 为了es结果和slots对应 kwh_m, charge_m, p_m, price_m = by_slots(slots_m, es_re_m_dic) price_m.append("") dict_m = { "时间": slots_m_p, "电量(万kWh)": division_down(kwh_m), "电费(万元)": division_down(charge_m), "电价(元/kWh)": price_m, } df = pd.DataFrame(dict_m) df.to_excel(writer, sheet_name="电量电费信息", index=False) # 2. 按工厂统计, 电量/电费 res_cid_m = await power_charge_p_cid_aggs(start, end, cid_list, "day") power_m_dic = {"电量(万kWh)": slots_m_p} charge_m_dic = {"电费(万元)": slots_m_p} has_power_cids = [] # es查询有数据的工厂 for cid, cid_info in groupby(res_cid_m, key=itemgetter("cid")): cid_one = [one for one in cid_info] has_power_cids.append(cid) cid_name = com_dic[cid]["shortname"] # 把slots作为key提出来 info_dic = es_process(cid_one, fmat="MM-DD", time_key="create_time") kwh_m, charge_m, p_m, price_m = by_slots(slots_m, info_dic) power_m_dic[cid_name] = division_down(kwh_m) charge_m_dic[cid_name] = division_down(charge_m) # 没有电量数据的工厂, 设为空字符串 for cid in cid_list: if cid not in has_power_cids: cid_name = com_dic[cid]["shortname"] power_m_dic[cid_name] = list(str(' ') * (len(slots_m) + 1)) charge_m_dic[cid_name] = list(str(' ') * (len(slots_m) + 1)) df1 = pd.DataFrame(power_m_dic) df1.to_excel(writer, sheet_name="电量", index=False) df2 = pd.DataFrame(charge_m_dic) df2.to_excel(writer, sheet_name="电费", index=False) writer.save() output.seek(0) # django改为sanic返回 # response = HttpResponse(output, content_type='application/octet-stream') if date_type == "day": excel_name = f"{start[:10]}电量电费信息.xlsx" else: excel_name = f"{start[:10]}~{end[:10]}电量电费信息.xlsx" return output, excel_name @summary('电量电费-下载excel-工厂版') async def get_power_company_download(req): """目前智电U使用""" output = io.BytesIO() writer = pd.ExcelWriter(output, engine='xlsxwriter') # 1. 获取参数 args = req.args # point_list = json.loads(args.get("point_list")) point_id = int(args.get("point_id")) cid = int(args.get("cid")) start = args.get("start") end = args.get("end") date_type = args.get("date_type") # 参数为point_id, 转换为point_list if point_id == -1: # 选的全部 # 1.找出工厂所有pid,point表add_to_company字段为1 sql = "SELECT pid from `point` WHERE cid = %s " async with MysqlUtil() as conn: point_info = await conn.fetchall(sql=sql, args=(cid,)) point_list = [point.get("pid") for point in point_info] else: point_list = [point_id] # 查询point point_info_list = await point_by_points(point_list) pid_dic = {i["pid"]: i for i in point_info_list} # pid_dic = process_es_data(point_info_list, key="pid") if date_type == "range": date_type = "month" if date_type == "day": slots_96 = SLOTS_15MIN slots_96_zj = copy.deepcopy(slots_96) slots_96_zj.append("总计") slots_24 = SLOTS[date_type] # 1. 96个点, 电量\电费\平均功率\电价 es_re_96 = await histogram_aggs_points(start, end, point_list, "15m") es_re_96_dic = es_process(es_re_96, fmat="HH:mm") # 为了es结果和slots对应 kwh_96, charge_96, p_96, price_96 = by_slots(slots_96, es_re_96_dic) p_96.append("") price_96.append("") dict_96 = { "时间": slots_96_zj, "电量(万kWh)": division_down(kwh_96), "电费(万元)": division_down(charge_96), "平均功率(kW)": p_96, "电价(元/kWh)": price_96, } df = pd.DataFrame(dict_96) df.to_excel(writer, sheet_name="96个点", index=False) # 2. 根据监测点聚合, 96个点电量\96个点电费 res_pid_96 = await power_charge_p_point_aggs(start, end, point_list, "15m") power_96_dic = {"电量(万kWh)": slots_96_zj} charge_96_dic = {"电费(万元)": slots_96_zj} has_power_pids = [] # es查询有数据的工厂 for info in res_pid_96: pid = info.get("key") has_power_pids.append(pid) pid_name = pid_dic[pid]["name"] # 把slots作为key提出来 info_dic = es_process(info["quarter_time"]["buckets"], fmat="HH:mm") kwh_96, charge_96, p_96, price_96 = by_slots(slots_96, info_dic) power_96_dic[pid_name] = division_down(kwh_96) charge_96_dic[pid_name] = division_down(charge_96) # 没有电量数据的监测点, 设为空字符串 for pid in point_list: if pid not in has_power_pids: pid_name = pid_dic[pid]["name"] power_96_dic[pid_name] = list(str(' ') * 97) charge_96_dic[pid_name] = list(str(' ') * 97) df1 = pd.DataFrame(power_96_dic) df1.to_excel(writer, sheet_name="96个点电量", index=False) df2 = pd.DataFrame(charge_96_dic) df2.to_excel(writer, sheet_name="96个点电费", index=False) # 3. 24个点, 电量\电费\平均功率\电价 es_re_24 = await histogram_aggs_points(start, end, point_list, "hour") es_re_24_dic = es_process(es_re_24, fmat="HH:mm") # 为了es结果和slots对应 kwh_24, charge_24, p_24, price_24 = by_slots(slots_24, es_re_24_dic) slots_24_zj = copy.deepcopy(slots_24) slots_24_zj.append("总计") p_24.append("") price_24.append("") dict_24 = { "时间": slots_24_zj, "电量(万kWh)": division_down(kwh_24), "电费(万元)": division_down(charge_24), "平均功率(kW)": p_24, "电价(元/kWh)": price_24, } df_24 = pd.DataFrame(dict_24) df_24.to_excel(writer, sheet_name="24个点", index=False) # 4. 根据工厂聚合, 24个点电量\24个点电费 res_pid_24 = await power_charge_p_point_aggs(start, end, point_list, "hour") power_24_dic = {"电量(万kWh)": slots_24_zj} charge_24_dic = {"电费(万元)": slots_24_zj} has_power_pids_24 = [] # es查询有数据的工厂 for info in res_pid_24: pid = info.get("key") has_power_pids_24.append(pid) pid_name = pid_dic[pid]["name"] # 把slots作为key提出来 info_dic_24 = es_process(info["quarter_time"]["buckets"], fmat="HH:mm") kwh_24, charge_24, p_24, price_24 = by_slots(slots_24, info_dic_24) power_24_dic[pid_name] = division_down(kwh_24) charge_24_dic[pid_name] = division_down(charge_24) # 没有电量数据的工厂, 设为空字符串 for pid in point_list: if pid not in has_power_pids_24: pid_name = pid_dic[pid]["name"] power_24_dic[pid_name] = list(str(' ') * 25) charge_24_dic[pid_name] = list(str(' ') * 25) df3 = pd.DataFrame(power_24_dic) df3.to_excel(writer, sheet_name="24个点电量", index=False) df4 = pd.DataFrame(charge_24_dic) df4.to_excel(writer, sheet_name="24个点电费", index=False) elif date_type == "month": # 1. 电量电费信息, 电量/电费/电价 slots_m = proxy_power_slots(start, end) slots_m_p = copy.deepcopy(slots_m) slots_m_p.append("总计") es_re_m = await histogram_aggs_points(start, end, point_list, "day") es_re_m_dic = es_process(es_re_m, fmat="MM-DD") # 为了es结果和slots对应 kwh_m, charge_m, p_m, price_m = by_slots(slots_m, es_re_m_dic) price_m.append("") dict_m = { "时间": slots_m_p, "电量(万kWh)": division_down(kwh_m), "电费(万元)": division_down(charge_m), "电价(元/kWh)": price_m, } df = pd.DataFrame(dict_m) df.to_excel(writer, sheet_name="电量电费信息", index=False) # 2. 按工厂统计, 电量/电费 res_pid_m = await power_charge_p_point_aggs(start, end, point_list, "day") power_m_dic = {"电量(万kWh)": slots_m_p} charge_m_dic = {"电费(万元)": slots_m_p} has_power_pids = [] # es查询有数据的工厂 for info in res_pid_m: pid = info.get("key") has_power_pids.append(pid) pid_name = pid_dic[pid]["name"] # 把slots作为key提出来 info_dic = es_process(info["quarter_time"]["buckets"], fmat="MM-DD") kwh_m, charge_m, p_m, price_m = by_slots(slots_m, info_dic) power_m_dic[pid_name] = division_down(kwh_m) charge_m_dic[pid_name] = division_down(charge_m) # 没有电量数据的工厂, 设为空字符串 for pid in point_list: if pid not in has_power_pids: pid_name = pid_dic[pid]["name"] power_m_dic[pid_name] = list(str(' ') * (len(slots_m) + 1)) charge_m_dic[pid_name] = list(str(' ') * (len(slots_m) + 1)) df1 = pd.DataFrame(power_m_dic) df1.to_excel(writer, sheet_name="电量", index=False) df2 = pd.DataFrame(charge_m_dic) df2.to_excel(writer, sheet_name="电费", index=False) # data_type = year else: # 1. 电量电费信息, 电量/电费/电价 slots_m = year_slots(start, end) slots_m_p = copy.deepcopy(slots_m) slots_m_p.append("总计") es_re_m = await histogram_aggs_points(start, end, point_list, "month") es_re_m_dic = es_process(es_re_m, fmat="YYYY-MM") # 为了es结果和slots对应 kwh_m, charge_m, p_m, price_m = by_slots(slots_m, es_re_m_dic) price_m.append("") dict_m = { "时间": slots_m_p, "电量(万kWh)": division_down(kwh_m), "电费(万元)": division_down(charge_m), "电价(元/kWh)": price_m, } df = pd.DataFrame(dict_m) df.to_excel(writer, sheet_name="电量电费信息", index=False) # 2. 按工厂统计, 电量/电费 res_pid_m = await power_charge_p_point_aggs(start, end, point_list, "month") power_m_dic = {"电量(万kWh)": slots_m_p} charge_m_dic = {"电费(万元)": slots_m_p} has_power_pids = [] # es查询有数据的point for info in res_pid_m: pid = info.get("key") has_power_pids.append(pid) pid_name = pid_dic[pid]["name"] # 把slots作为key提出来 info_dic = es_process(info["quarter_time"]["buckets"], fmat="YYYY-MM") kwh_m, charge_m, p_m, price_m = by_slots(slots_m, info_dic) power_m_dic[pid_name] = division_down(kwh_m) charge_m_dic[pid_name] = division_down(charge_m) # 没有电量数据的point, 设为空字符串 for pid in point_list: if pid not in has_power_pids: pid_name = pid_dic[pid]["name"] power_m_dic[pid_name] = list(str(' ') * (len(slots_m) + 1)) charge_m_dic[pid_name] = list(str(' ') * (len(slots_m) + 1)) df1 = pd.DataFrame(power_m_dic) df1.to_excel(writer, sheet_name="电量", index=False) df2 = pd.DataFrame(charge_m_dic) df2.to_excel(writer, sheet_name="电费", index=False) writer.save() output.seek(0) # django改为sanic返回 # response = HttpResponse(output, content_type='application/octet-stream') if date_type == "day": excel_name = f"{start[:10]}电量电费信息.xlsx" else: excel_name = f"{start[:10]}~{end[:10]}电量电费信息.xlsx" return output, excel_name