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
