elec_statis_proxy.py 41.5 KB
Newer Older
lcn's avatar
lcn committed
1 2
import copy
import json
lcn's avatar
lcn committed
3 4 5
from itertools import groupby
from operator import itemgetter

lcn's avatar
lcn committed
6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
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 \
ZZH's avatar
ZZH committed
23
    proxy_today_yesterday_load, by_slots
lcn's avatar
lcn committed
24
from unify_api.modules.elec_charge.common.utils import aver_price, \
ZZH's avatar
ZZH committed
25
    load_power_charge
lcn's avatar
lcn committed
26 27 28 29 30 31 32 33 34
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('代理版电量电费统计曲线')
ZZH's avatar
ZZH committed
35
async def post_power_statis_proxy(req, body: StatisProxyReq) -> PcStatiResp:
lcn's avatar
lcn committed
36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54
    # 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":
        # 电量电费
ZZH's avatar
ZZH committed
55 56
        kwh_sv, charge_sv = await load_power_charge(cid_list, point_id, start,
                                                    end, date_type)
lcn's avatar
lcn committed
57 58
        # 需要增加15min电量电费, 渠道版首页不需要下载,暂时去掉
        # 今日/昨日负荷曲线
ZZH's avatar
ZZH committed
59
        today_p = await proxy_today_yesterday_load(cid_list, start, end)
lcn's avatar
lcn committed
60
        ysd_start, ysd_end = last_time_str(start, end, "day")
ZZH's avatar
ZZH committed
61
        yesterday_p = await proxy_today_yesterday_load(cid_list, ysd_start,
lcn's avatar
lcn committed
62
                                                          ysd_end)
lcn's avatar
lcn committed
63 64 65 66
        return PcStatiResp(kwh=kwh_sv, charge=charge_sv, today_p=today_p,
                           yesterday_p=yesterday_p)
    elif date_type == "month":
        # 本月电量电费, 平均电价
ZZH's avatar
ZZH committed
67 68
        kwh_sv, charge_sv = await load_power_charge(cid_list, point_id, start,
                                                    end, date_type)
lcn's avatar
lcn committed
69 70 71 72
        this_aver_price = aver_price(kwh_sv, charge_sv)
        # 上月电量电费, 平均电价
        last_start, last_end = last_time_str(start, end, "month")
        # 需要增加15min电量电费
ZZH's avatar
ZZH committed
73 74 75 76 77
        last_kwh_sv, last_charge_sv = await load_power_charge(cid_list,
                                                              point_id,
                                                              last_start,
                                                              last_end,
                                                              date_type)
lcn's avatar
lcn committed
78 79 80 81 82 83
        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":
        # 本月电量电费
ZZH's avatar
ZZH committed
84 85
        kwh_sv, charge_sv = await load_power_charge(cid_list, point_id, start,
                                                    end, date_type)
lcn's avatar
lcn committed
86 87 88 89 90 91 92 93 94 95 96
        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")
            # 电量电费
ZZH's avatar
ZZH committed
97 98
            kwh_sv, charge_sv = await load_power_charge(cid_list, point_id,
                                                        start, end, date_type)
lcn's avatar
lcn committed
99
            # 负荷曲线
ZZH's avatar
ZZH committed
100
            this_p = await proxy_today_yesterday_load(cid_list, start,
lcn's avatar
lcn committed
101
                                                         end)
lcn's avatar
lcn committed
102 103 104 105
            # 需要增加15min电量电费, 渠道版首页不需要下载,暂时去掉
            return PcStatiResp(kwh=kwh_sv, charge=charge_sv, today_p=this_p)
        else:
            # 电量电费
ZZH's avatar
ZZH committed
106 107
            kwh_sv, charge_sv = await load_power_charge(cid_list, point_id,
                                                        start, end, date_type)
lcn's avatar
lcn committed
108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363
            # 平均电价
            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":
        # 电量电费
ZZH's avatar
ZZH committed
364 365
        kwh_sv, charge_sv = await load_power_charge(cid_list, -1, start, end,
                                                    date_type)
lcn's avatar
lcn committed
366 367
        # 需要增加15min电量电费, 渠道版首页不需要下载,暂时去掉
        # 今日/昨日负荷曲线
ZZH's avatar
ZZH committed
368
        today_p = await proxy_today_yesterday_load(cid_list, start, end)
lcn's avatar
lcn committed
369
        ysd_start, ysd_end = last_time_str(start, end, "day")
ZZH's avatar
ZZH committed
370
        yesterday_p = await proxy_today_yesterday_load(cid_list,
lcn's avatar
lcn committed
371 372
                                                          ysd_start,
                                                          ysd_end)
lcn's avatar
lcn committed
373 374 375 376
        return PcStatiResp(kwh=kwh_sv, charge=charge_sv, today_p=today_p,
                           yesterday_p=yesterday_p)
    elif date_type == "month":
        # 本月电量电费, 平均电价
ZZH's avatar
ZZH committed
377 378
        kwh_sv, charge_sv = await load_power_charge(cid_list, -1, start, end,
                                                    date_type)
lcn's avatar
lcn committed
379 380 381 382
        this_aver_price = aver_price(kwh_sv, charge_sv)
        # 上月电量电费, 平均电价
        last_start, last_end = last_time_str(start, end, "month")
        # 需要增加15min电量电费
ZZH's avatar
ZZH committed
383 384 385 386
        last_kwh_sv, last_charge_sv = await load_power_charge(cid_list, -1,
                                                              last_start,
                                                              last_end,
                                                              date_type)
lcn's avatar
lcn committed
387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430
        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")
lcn's avatar
lcn committed
431 432
        es_re_96_dic = es_process(es_re_96, "HH:mm", time_key="create_time")
        # 为了es结果和slots对应
lcn's avatar
lcn committed
433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449
        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查询有数据的工厂
lcn's avatar
lcn committed
450 451
        for cid, cid_info in groupby(res_cid_96, key=itemgetter("cid")):
            cid_one = [one for one in cid_info]
lcn's avatar
lcn committed
452 453 454
            has_power_cids.append(cid)
            cid_name = com_dic[cid]["shortname"]
            # 把slots作为key提出来
lcn's avatar
lcn committed
455 456
            info_dic = es_process(cid_one, fmat="HH:mm",
                                  time_key="create_time")
lcn's avatar
lcn committed
457 458 459 460
            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)
ZZH's avatar
ZZH committed
461

lcn's avatar
lcn committed
462 463 464 465 466 467
        # 没有电量数据的工厂, 设为空字符串
        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)
ZZH's avatar
ZZH committed
468

lcn's avatar
lcn committed
469 470 471 472 473 474
        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")
lcn's avatar
lcn committed
475 476
        es_re_24_dic = es_process(es_re_24, fmat="HH:mm",
                                  time_key="create_time")  # 为了es结果和slots对应
lcn's avatar
lcn committed
477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496
        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查询有数据的工厂
lcn's avatar
lcn committed
497 498
        for cid, cid_info in groupby(res_cid_24, key=itemgetter("cid")):
            cid_one = [one for one in cid_info]
lcn's avatar
lcn committed
499 500 501
            has_power_cids_24.append(cid)
            cid_name = com_dic[cid]["shortname"]
            # 把slots作为key提出来
lcn's avatar
lcn committed
502 503
            info_dic_24 = es_process(cid_one, fmat="HH:mm",
                                     time_key="create_time")
lcn's avatar
lcn committed
504 505 506 507
            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)
ZZH's avatar
ZZH committed
508

lcn's avatar
lcn committed
509 510 511 512 513 514
        # 没有电量数据的工厂, 设为空字符串
        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)
ZZH's avatar
ZZH committed
515

lcn's avatar
lcn committed
516 517 518 519 520 521 522 523 524 525 526
        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")
lcn's avatar
lcn committed
527 528
        es_re_m_dic = es_process(es_re_m, fmat="MM-DD",
                                 time_key="create_time")  # 为了es结果和slots对应
lcn's avatar
lcn committed
529 530 531 532 533 534 535 536 537 538 539 540 541 542 543
        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查询有数据的工厂
lcn's avatar
lcn committed
544 545
        for cid, cid_info in groupby(res_cid_m, key=itemgetter("cid")):
            cid_one = [one for one in cid_info]
lcn's avatar
lcn committed
546 547 548
            has_power_cids.append(cid)
            cid_name = com_dic[cid]["shortname"]
            # 把slots作为key提出来
lcn's avatar
lcn committed
549 550
            info_dic = es_process(cid_one, fmat="MM-DD",
                                  time_key="create_time")
lcn's avatar
lcn committed
551 552 553
            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)
ZZH's avatar
ZZH committed
554

lcn's avatar
lcn committed
555 556 557 558 559 560
        # 没有电量数据的工厂, 设为空字符串
        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))
ZZH's avatar
ZZH committed
561

lcn's avatar
lcn committed
562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589
        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")
ZZH's avatar
ZZH committed
590

lcn's avatar
lcn committed
591 592 593
    # 参数为point_id, 转换为point_list
    if point_id == -1:  # 选的全部
        # 1.找出工厂所有pid,point表add_to_company字段为1
wang.wenrong's avatar
wang.wenrong committed
594
        sql = "SELECT pid from `point` WHERE cid = %s "
lcn's avatar
lcn committed
595 596 597 598 599
        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]
ZZH's avatar
ZZH committed
600

lcn's avatar
lcn committed
601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643
    # 查询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)
ZZH's avatar
ZZH committed
644

lcn's avatar
lcn committed
645 646 647 648 649 650
        # 没有电量数据的监测点, 设为空字符串
        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)
ZZH's avatar
ZZH committed
651

lcn's avatar
lcn committed
652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689
        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)
ZZH's avatar
ZZH committed
690

lcn's avatar
lcn committed
691 692 693 694 695 696
        # 没有电量数据的工厂, 设为空字符串
        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)
ZZH's avatar
ZZH committed
697

lcn's avatar
lcn committed
698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734
        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)
ZZH's avatar
ZZH committed
735

lcn's avatar
lcn committed
736 737 738 739 740 741
        # 没有电量数据的工厂, 设为空字符串
        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))
ZZH's avatar
ZZH committed
742

lcn's avatar
lcn committed
743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780
        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)
ZZH's avatar
ZZH committed
781

lcn's avatar
lcn committed
782 783 784 785 786 787
        # 没有电量数据的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))
ZZH's avatar
ZZH committed
788

lcn's avatar
lcn committed
789 790 791 792 793 794 795 796 797 798 799 800 801
        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