def test_c_months_u(): finish_year, finish_month = 2009, 3 start, _ = next( c_months_u(finish_year=finish_year, finish_month=finish_month, months=1)) assert start == to_utc(ct_datetime(finish_year, finish_month))
def run(self): while not self.stopped.isSet(): if self.lock.acquire(False): sess = self.global_alert = None try: sess = Session() self.log("Starting to check bmarketidx.") contract = Contract.get_non_core_by_name(sess, "bmarketidx") latest_rs = ( sess.query(RateScript) .filter(RateScript.contract_id == contract.id) .order_by(RateScript.start_date.desc()) .first() ) start_ct = to_ct(latest_rs.start_date) months = list( c_months_u( start_year=start_ct.year, start_month=start_ct.month, months=2, ) ) month_start, month_finish = months[1] now = utc_datetime_now() if now > month_finish: _process_month( self.log, sess, contract, latest_rs, month_start, month_finish, ) except BaseException: self.log(f"Outer problem {traceback.format_exc()}") sess.rollback() self.global_alert = ( "There's a problem with the " "bmarketidx automatic importer." ) finally: self.lock.release() self.log("Finished checking bmarketidx rates.") if sess is not None: sess.close() self.going.wait(2 * 60 * 60) self.going.clear()
def test_c_months_u_start_finish(): start_year, start_month, finish_year, finish_month = 2009, 3, 2009, 4 month_list = list( c_months_u( start_year=start_year, start_month=start_month, finish_year=finish_year, finish_month=finish_month, )) print(month_list) assert month_list == [ (to_utc(ct_datetime(2009, 3)), to_utc(ct_datetime(2009, 3, 31, 23, 30))), (to_utc(ct_datetime(2009, 4)), to_utc(ct_datetime(2009, 4, 30, 23, 30))), ]
def do_get(sess): months = req_int("months") finish_year = req_int("finish_year") finish_month = req_int("finish_month") month_list = list( c_months_u(finish_year=finish_year, finish_month=finish_month, months=months)) start_date, finish_date = month_list[0][0], month_list[-1][-1] typ = req_str("type") site_id = req_int("site_id") args = (start_date, finish_date, site_id, typ, g.user) threading.Thread(target=content, args=args).start() return chellow_redirect("/downloads", 303)
def content(year, month, months, supply_id, user): sess = f = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( "register_reads.csv", user) f = open(running_name, mode="w", newline="") w = csv.writer(f, lineterminator="\n") titles = ( "Duration Start", "Duration Finish", "Supply Id", "Import MPAN Core", "Export MPAN Core", "Batch Reference", "Bill Id", "Bill Reference", "Bill Issue Date", "Bill Type", "Register Read Id", "TPR", "Coefficient", "Previous Read Date", "Previous Read Value", "Previous Read Type", "Present Read Date", "Present Read Value", "Present Read Type", ) w.writerow(titles) month_pairs = list( c_months_u(finish_year=year, finish_month=month, months=months)) start_date, finish_date = month_pairs[0][0], month_pairs[-1][-1] supplies = (sess.query(Supply).join(Bill).join(RegisterRead).filter( or_( and_( RegisterRead.present_date >= start_date, RegisterRead.present_date <= finish_date, ), and_( RegisterRead.previous_date >= start_date, RegisterRead.previous_date <= finish_date, ), )).order_by(Bill.supply_id).distinct()) if supply_id is not None: supply = Supply.get_by_id(sess, supply_id) supplies = supplies.filter(Bill.supply == supply) for supply in supplies: supply_id = supply.id for bill, batch, bill_type in (sess.query( Bill, Batch, BillType).join(Batch).join(BillType).join( RegisterRead).filter( Bill.supply == supply, or_( and_( RegisterRead.present_date >= start_date, RegisterRead.present_date <= finish_date, ), and_( RegisterRead.previous_date >= start_date, RegisterRead.previous_date <= finish_date, ), ), )): era = supply.find_era_at(sess, bill.start_date) if era is None: eras = (sess.query(Era).filter( Era.supply == supply).order_by(Era.start_date).all()) if bill.start_date < eras[0].start_date: era = eras[0] else: era = eras[-1] for read in (sess.query(RegisterRead).filter( RegisterRead.bill == bill, or_( and_( RegisterRead.present_date >= start_date, RegisterRead.present_date <= finish_date, ), and_( RegisterRead.previous_date >= start_date, RegisterRead.previous_date <= finish_date, ), ), ).options( joinedload(RegisterRead.tpr), joinedload(RegisterRead.previous_type), joinedload(RegisterRead.present_type), )): vals = [ start_date, finish_date, supply_id, era.imp_mpan_core, era.exp_mpan_core, batch.reference, bill.id, bill.reference, bill.issue_date, bill_type.code, read.id, "md" if read.tpr is None else read.tpr.code, read.coefficient, read.previous_date, read.previous_value, read.previous_type.code, read.present_date, read.present_value, read.present_type.code, ] w.writerow(csv_make_val(v) for v in vals) # Avoid a long-running transaction sess.rollback() except BadRequest as e: w.writerow([e.description]) except BaseException: msg = traceback.format_exc() f.write(msg) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def content(contract_id, end_year, end_month, months, user): caches = {} sess = f = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( "displaced.csv", user) f = open(running_name, mode="w", newline="") writer = csv.writer(f, lineterminator="\n") titles = [ "Site Code", "Site Name", "Associated Site Ids", "From", "To", "Gen Types", "CHP kWh", "LM kWh", "Turbine kWh", "PV kWh", ] month_list = list( c_months_u(finish_year=end_year, finish_month=end_month, months=months)) start_date, finish_date = month_list[0][0], month_list[-1][-1] forecast_date = chellow.computer.forecast_date() contract = Contract.get_supplier_by_id(sess, contract_id) sites = (sess.query(Site).join(SiteEra).join(Era).join(Supply).join( Source).filter( or_(Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date, or_(Source.code.in_(("gen", "gen-net")), Era.exp_mpan_core != null()), ).distinct()) bill_titles = chellow.computer.contract_func( caches, contract, "displaced_virtual_bill_titles")() for title in bill_titles: if title == "total-msp-kwh": title = "total-displaced-msp-kwh" titles.append(title) writer.writerow(titles) for site in sites: for month_start, month_finish in month_list: displaced_era = chellow.computer.displaced_era( sess, caches, site, month_start, month_finish, forecast_date) if displaced_era is None: continue supplier_contract = displaced_era.imp_supplier_contract if contract is not None and contract != supplier_contract: continue linked_sites = set() generator_types = set() for era in (sess.query(Era).join(SiteEra).filter( SiteEra.site == site, Era.start_date <= month_finish, or_(Era.finish_date == null(), Era.finish_date >= month_start), )): for site_era in era.site_eras: if site_era.site != site: linked_sites.add(site_era.site.code) supply = era.supply if supply.generator_type is not None: generator_types.add(supply.generator_type.code) supply_ids = set() for era in (sess.query(Era).join(SiteEra).filter( SiteEra.site == site, SiteEra.is_physical, Era.start_date <= month_finish, or_(Era.finish_date == null(), Era.finish_date >= month_start), )): supply_ids.add(era.supply.id) vals = [ site.code, site.name, ", ".join(list(linked_sites)), hh_format(month_start), hh_format(month_finish), ", ".join(list(generator_types)), ] total_gen_breakdown = {} results = iter( sess.execute( "select supply.id, hh_datum.value, " "hh_datum.start_date, channel.imp_related, " "source.code, generator_type.code as " "gen_type_code from hh_datum, channel, source, " "era, supply left outer join generator_type on " "supply.generator_type_id = generator_type.id " "where hh_datum.channel_id = channel.id and " "channel.era_id = era.id and era.supply_id = " "supply.id and supply.source_id = source.id and " "channel.channel_type = 'ACTIVE' and not " "(source.code = 'net' and channel.imp_related " "is true) and hh_datum.start_date >= " ":month_start and hh_datum.start_date " "<= :month_finish and " "supply.id = any(:supply_ids) order " "by hh_datum.start_date, supply.id", params={ "month_start": month_start, "month_finish": month_finish, "supply_ids": sorted(list(supply_ids)), }, )) ( sup_id, hh_val, hh_start, imp_related, source_code, gen_type_code, ) = next(results, (None, None, None, None, None, None)) for hh_date in hh_range(caches, month_start, month_finish): gen_breakdown = {} exported = 0 while hh_start == hh_date: if not imp_related and source_code in ("net", "gen-net"): exported += hh_val if (imp_related and source_code == "gen") or ( not imp_related and source_code == "gen-net"): gen_breakdown[gen_type_code] = ( gen_breakdown.setdefault(gen_type_code, 0) + hh_val) if (not imp_related and source_code == "gen") or ( imp_related and source_code == "gen-net"): gen_breakdown[gen_type_code] = ( gen_breakdown.setdefault(gen_type_code, 0) - hh_val) ( sup_id, hh_val, hh_start, imp_related, source_code, gen_type_code, ) = next(results, (None, None, None, None, None, None)) displaced = sum(gen_breakdown.values()) - exported added_so_far = 0 for key in sorted(gen_breakdown.keys()): kwh = gen_breakdown[key] if displaced < 0: total_gen_breakdown[key] = ( total_gen_breakdown.get(key, 0) + kwh) else: if kwh + added_so_far > displaced: total_gen_breakdown[key] = ( total_gen_breakdown.get(key, 0) + displaced - added_so_far) break else: total_gen_breakdown[key] = ( total_gen_breakdown.get(key, 0) + kwh) added_so_far += kwh for title in ["chp", "lm", "turb", "pv"]: vals.append(str(total_gen_breakdown.get(title, ""))) site_ds = chellow.computer.SiteSource( sess, site, month_start, month_finish, forecast_date, caches, displaced_era, ) disp_func = chellow.computer.contract_func( caches, supplier_contract, "displaced_virtual_bill") disp_func(site_ds) bill = site_ds.supplier_bill for title in bill_titles: if title in bill: vals.append(to_val(bill[title])) del bill[title] else: vals.append("") for k in sorted(bill.keys()): vals.append(k) vals.append(str(bill[k])) writer.writerow(vals) except BaseException: msg = traceback.format_exc() sys.stderr.write(msg) writer.writerow([msg]) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def content(scenario_props, base_name, site_id, supply_id, user, compression, site_codes, now): report_context = {} try: comp = report_context["computer"] except KeyError: comp = report_context["computer"] = {} try: rate_cache = comp["rates"] except KeyError: rate_cache = comp["rates"] = {} try: ind_cont = report_context["contract_names"] except KeyError: ind_cont = report_context["contract_names"] = {} sess = None try: sess = Session() start_year = scenario_props["scenario_start_year"] start_month = scenario_props["scenario_start_month"] months = scenario_props["scenario_duration"] month_pairs = list( c_months_u(start_year=start_year, start_month=start_month, months=months)) start_date_utc = month_pairs[0][0] finish_date_utc = month_pairs[-1][-1] base_name.append( hh_format(start_date_utc).replace(" ", "_").replace(":", "").replace( "-", "")) base_name.append("for") base_name.append(str(months)) base_name.append("months") if "forecast_from" in scenario_props: forecast_from = scenario_props["forecast_from"] else: forecast_from = None if forecast_from is None: forecast_from = chellow.computer.forecast_date() else: forecast_from = to_utc(forecast_from) sites = sess.query(Site).distinct().order_by(Site.code) if site_id is not None: site = Site.get_by_id(sess, site_id) sites = sites.filter(Site.id == site.id) base_name.append("site") base_name.append(site.code) if supply_id is not None: supply = Supply.get_by_id(sess, supply_id) base_name.append("supply") base_name.append(str(supply.id)) sites = sites.join(SiteEra).join(Era).filter(Era.supply == supply) if len(site_codes) > 0: base_name.append("sitecodes") sites = sites.filter(Site.code.in_(site_codes)) running_name, finished_name = chellow.dloads.make_names( "_".join(base_name) + ".ods", user) rf = open(running_name, "wb") site_rows = [] era_rows = [] for rate_script in scenario_props.get("local_rates", []): contract_id = rate_script["contract_id"] try: cont_cache = rate_cache[contract_id] except KeyError: cont_cache = rate_cache[contract_id] = {} try: rate_script_start = rate_script["start_date"] except KeyError: raise BadRequest( f"Problem in the scenario properties. Can't find the " f"'start_date' key of the contract {contract_id} in " f"the 'local_rates' map.") try: rate_script_start = rate_script["start_date"] except KeyError: raise BadRequest( f"Problem in the scenario properties. Can't find the " f"'start_date' key of the contract {contract_id} in " f"the 'local_rates' map.") props = PropDict("scenario properties", rate_script["script"]) for dt in hh_range(report_context, rate_script_start, rate_script["finish_date"]): cont_cache[dt] = props for rate_script in scenario_props.get("industry_rates", []): contract_name = rate_script["contract_name"] try: cont_cache = ind_cont[contract_name] except KeyError: cont_cache = ind_cont[contract_name] = {} rfinish = rate_script["finish_date"] if rfinish is None: raise BadRequest( f"For the industry rate {contract_name} the finish_date " f"can't be null.") for dt in hh_range(report_context, rate_script["start_date"], rfinish): cont_cache[dt] = PropDict("scenario properties", rate_script["script"]) era_maps = scenario_props.get("era_maps", {}) by_hh = scenario_props.get("by_hh", False) scenario_hh = scenario_props.get("hh_data", {}) era_header_titles = [ "creation-date", "imp-mpan-core", "imp-supplier-contract", "exp-mpan-core", "exp-supplier-contract", "metering-type", "source", "generator-type", "supply-name", "msn", "pc", "site-id", "site-name", "associated-site-ids", "month", ] site_header_titles = [ "creation-date", "site-id", "site-name", "associated-site-ids", "month", "metering-type", "sources", "generator-types", ] summary_titles = [ "import-net-kwh", "export-net-kwh", "import-gen-kwh", "export-gen-kwh", "import-3rd-party-kwh", "export-3rd-party-kwh", "displaced-kwh", "used-kwh", "used-3rd-party-kwh", "import-net-gbp", "export-net-gbp", "import-gen-gbp", "export-gen-gbp", "import-3rd-party-gbp", "export-3rd-party-gbp", "displaced-gbp", "used-gbp", "used-3rd-party-gbp", "billed-import-net-kwh", "billed-import-net-gbp", "billed-supplier-import-net-gbp", "billed-dc-import-net-gbp", "billed-mop-import-net-gbp", ] title_dict = {} for cont_type, con_attr in ( ("mop", Era.mop_contract), ("dc", Era.dc_contract), ("imp-supplier", Era.imp_supplier_contract), ("exp-supplier", Era.exp_supplier_contract), ): titles = [] title_dict[cont_type] = titles conts = (sess.query(Contract).join(con_attr).join( Era.supply).join(Source).filter( Era.start_date <= finish_date_utc, or_(Era.finish_date == null(), Era.finish_date >= start_date_utc), ).distinct().order_by(Contract.id)) if supply_id is not None: conts = conts.filter(Era.supply_id == supply_id) for cont in conts: title_func = chellow.computer.contract_func( report_context, cont, "virtual_bill_titles") if title_func is None: raise Exception( f"For the contract {cont.name} there doesn't seem to " f"be a 'virtual_bill_titles' function.") for title in title_func(): if title not in titles: titles.append(title) tpr_query = (sess.query(Tpr).join(MeasurementRequirement).join( Ssc).join(Era).filter( Era.start_date <= finish_date_utc, or_(Era.finish_date == null(), Era.finish_date >= start_date_utc), ).order_by(Tpr.code).distinct()) for tpr in tpr_query.filter(Era.imp_supplier_contract != null()): for suffix in ("-kwh", "-rate", "-gbp"): title_dict["imp-supplier"].append(tpr.code + suffix) for tpr in tpr_query.filter(Era.exp_supplier_contract != null()): for suffix in ("-kwh", "-rate", "-gbp"): title_dict["exp-supplier"].append(tpr.code + suffix) era_rows.append( era_header_titles + summary_titles + [None] + ["mop-" + t for t in title_dict["mop"]] + [None] + ["dc-" + t for t in title_dict["dc"]] + [None] + ["imp-supplier-" + t for t in title_dict["imp-supplier"]] + [None] + ["exp-supplier-" + t for t in title_dict["exp-supplier"]]) site_rows.append(site_header_titles + summary_titles) sites = sites.all() deltas = {} normal_reads = set() normal_read_rows = [] for site in sites: deltas[site.id] = _make_site_deltas(sess, report_context, site, scenario_hh, forecast_from, supply_id) for month_start, month_finish in month_pairs: for site in sites: if by_hh: sf = [(d, d) for d in hh_range(report_context, month_start, month_finish)] else: sf = [(month_start, month_finish)] for start, finish in sf: normal_reads = normal_reads | _process_site( sess, report_context, forecast_from, start, finish, site, deltas[site.id], supply_id, era_maps, now, summary_titles, title_dict, era_rows, site_rows, ) normal_read_rows = [[ "mpan_core", "date", "msn", "type", "registers" ]] for mpan_core, r in sorted(list(normal_reads)): row = [mpan_core, r.date, r.msn, r.type] + list(r.reads) normal_read_rows.append(row) write_spreadsheet(rf, compression, site_rows, era_rows, normal_read_rows) except BadRequest as e: msg = e.description + traceback.format_exc() sys.stderr.write(msg + "\n") site_rows.append(["Problem " + msg]) write_spreadsheet(rf, compression, site_rows, era_rows, normal_read_rows) except BaseException: msg = traceback.format_exc() sys.stderr.write(msg + "\n") site_rows.append(["Problem " + msg]) write_spreadsheet(rf, compression, site_rows, era_rows, normal_read_rows) finally: if sess is not None: sess.close() try: rf.close() os.rename(running_name, finished_name) except BaseException: msg = traceback.format_exc() r_name, f_name = chellow.dloads.make_names("error.txt", user) ef = open(r_name, "w") ef.write(msg + "\n") ef.close()
def _make_site_deltas(sess, report_context, site, scenario_hh, forecast_from, supply_id): site_scenario_hh = scenario_hh.get(site.code, {}) site_deltas = {"hhs": {}} delts = site_deltas["supply_deltas"] = {} for is_import in (True, False): delts[is_import] = {} for src in ("gen", "net", "gen-net", "3rd-party", "3rd-party-reverse", "sub"): delts[is_import][src] = {"site": {}} earliest_delta = to_utc(Datetime.max) latest_delta = to_utc(Datetime.min) found_hh = False for typ in ("used", "generated", "parasitic", "gen_net"): hh_str = site_scenario_hh.get(typ, "") hh_data = site_scenario_hh[typ] = {} for row in csv.reader(StringIO(hh_str)): cells = [cell.strip() for cell in row] if len("".join(cells)) == 0: continue if len(cells) != 2: raise BadRequest("Can't interpret the row " + str(cells) + " it should be of the form 'timestamp, kWh'") date_str, kwh_str = cells ts = parse_hh_start(date_str) earliest_delta = min(ts, earliest_delta) latest_delta = max(ts, latest_delta) try: hh_data[ts] = float(kwh_str) except ValueError as e: raise BadRequest("When looking at " + typ + " hh data, can't parse the " "kWh at " + date_str + ": " + str(e)) found_hh = True if not found_hh: return site_deltas scenario_used = site_scenario_hh["used"] scenario_generated = site_scenario_hh["generated"] scenario_parasitic = site_scenario_hh["parasitic"] scenario_gen_net = site_scenario_hh["gen_net"] earliest_delta_ct = to_ct(earliest_delta) for month_start, month_finish in c_months_u(earliest_delta_ct.year, earliest_delta_ct.month, months=None): if month_start > latest_delta: break chunk_start = hh_max(month_start, earliest_delta) chunk_finish = hh_min(month_finish, latest_delta) site_ds = chellow.computer.SiteSource(sess, site, chunk_start, chunk_finish, forecast_from, report_context) hh_map = dict((h["start-date"], h) for h in site_ds.hh_data) for era in (sess.query(Era).join(SiteEra).join(Pc).filter( SiteEra.site == site, SiteEra.is_physical == true(), Era.imp_mpan_core != null(), Pc.code != "00", Era.start_date <= chunk_finish, or_(Era.finish_date == null(), Era.finish_date >= chunk_start), )): if supply_id is not None and era.supply_id != supply_id: continue ss_start = hh_max(era.start_date, chunk_start) ss_finish = hh_min(era.finish_date, chunk_finish) ss = SupplySource(sess, ss_start, ss_finish, forecast_from, era, True, report_context) for hh in ss.hh_data: sdatum = hh_map[hh["start-date"]] sdatum["import-net-kwh"] += hh["msp-kwh"] sdatum["used-kwh"] += hh["msp-kwh"] for era in (sess.query(Era).join(SiteEra).join(Pc).join(Supply).join( Source).filter( SiteEra.site == site, SiteEra.is_physical == true(), Era.imp_mpan_core != null(), Era.start_date <= chunk_finish, or_(Era.finish_date == null(), Era.finish_date >= chunk_start), Source.code == "gen-net", )): if supply_id is not None and era.supply_id != supply_id: continue ss_start = hh_max(era.start_date, chunk_start) ss_finish = hh_min(era.finish_date, chunk_finish) ss = SupplySource(sess, ss_start, ss_finish, forecast_from, era, False, report_context) for hh in ss.hh_data: sdatum = hh_map[hh["start-date"]] try: sdatum["gen-net-kwh"] += hh["msp-kwh"] except KeyError: sdatum["gen-net-kwh"] = hh["msp-kwh"] for hh_start, hh in hh_map.items(): if hh_start in scenario_used: used_delt = scenario_used[hh_start] - hh["used-kwh"] imp_net_delt = 0 exp_net_delt = 0 if used_delt < 0: diff = hh["import-net-kwh"] + used_delt if diff < 0: imp_net_delt -= hh["import-net-kwh"] exp_net_delt -= diff else: imp_net_delt += used_delt else: diff = hh["export-net-kwh"] - used_delt if diff < 0: exp_net_delt -= hh["export-net-kwh"] imp_net_delt -= diff else: exp_net_delt -= used_delt try: delts[False]["net"]["site"][hh_start] += exp_net_delt except KeyError: delts[False]["net"]["site"][hh_start] = exp_net_delt try: delts[True]["net"]["site"][hh_start] += imp_net_delt except KeyError: delts[True]["net"]["site"][hh_start] = imp_net_delt hh["import-net-kwh"] += imp_net_delt hh["export-net-kwh"] += exp_net_delt hh["used-kwh"] += used_delt hh["msp-kwh"] -= exp_net_delt if hh_start in scenario_generated: imp_gen_delt = scenario_generated[hh_start] - hh[ "import-gen-kwh"] imp_net_delt = 0 exp_net_delt = 0 if imp_gen_delt < 0: diff = hh["export-net-kwh"] + imp_gen_delt if diff < 0: exp_net_delt -= hh["export-net-kwh"] imp_net_delt -= diff else: exp_net_delt += imp_gen_delt else: diff = hh["import-net-kwh"] - imp_gen_delt if diff < 0: imp_net_delt -= hh["import-net-kwh"] exp_net_delt -= diff else: imp_net_delt -= imp_gen_delt try: delts[True]["gen"]["site"][hh_start] += imp_gen_delt except KeyError: delts[True]["gen"]["site"][hh_start] = imp_gen_delt try: delts[False]["net"]["site"][hh_start] += exp_net_delt except KeyError: delts[False]["net"]["site"][hh_start] = exp_net_delt try: delts[True]["net"]["site"][hh_start] += imp_net_delt except KeyError: delts[True]["net"]["site"][hh_start] = imp_net_delt hh["import-net-kwh"] += imp_net_delt hh["export-net-kwh"] += exp_net_delt hh["import-gen-kwh"] += imp_gen_delt hh["msp-kwh"] -= imp_net_delt if hh_start in scenario_parasitic: exp_gen_delt = scenario_parasitic[hh_start] - hh[ "export-gen-kwh"] imp_net_delt = 0 exp_net_delt = 0 if exp_gen_delt < 0: diff = hh["import-net-kwh"] + exp_gen_delt if diff < 0: imp_net_delt -= hh["import-net-kwh"] exp_net_delt -= diff else: imp_net_delt += exp_gen_delt else: diff = hh["export-net-kwh"] - exp_gen_delt if diff < 0: exp_net_delt -= hh["export-net-kwh"] imp_net_delt -= diff else: exp_net_delt -= exp_gen_delt try: delts[False]["gen"]["site"][hh_start] += imp_gen_delt except KeyError: delts[False]["gen"]["site"][hh_start] = exp_gen_delt try: delts[False]["net"]["site"][hh_start] += exp_net_delt except KeyError: delts[False]["net"]["site"][hh_start] = exp_net_delt try: delts[True]["net"]["site"][hh_start] += imp_net_delt except KeyError: delts[True]["net"]["site"][hh_start] = imp_net_delt hh["import-net-kwh"] += imp_net_delt hh["export-net-kwh"] += exp_net_delt hh["export-gen-kwh"] += exp_gen_delt hh["msp-kwh"] -= imp_net_delt if hh_start in scenario_gen_net: gen_net_delt = scenario_gen_net[hh_start] - hh["gen-net-kwh"] try: delts[False]["gen-net"]["site"][hh_start] += gen_net_delt except KeyError: delts[False]["gen-net"]["site"][hh_start] = gen_net_delt hh["import-gen-kwh"] += gen_net_delt hh["export-net-kwh"] += gen_net_delt site_deltas["hhs"][hh_start] = hh sup_deltas = site_deltas["supply_deltas"][False]["net"]["site"] if all(v == 0 for v in sup_deltas.values()): sup_deltas.clear() return site_deltas
def content(contract_id, end_year, end_month, months, user): caches = {} sess = f = supply_source = None try: sess = Session() contract = Contract.get_dc_by_id(sess, contract_id) month_list = list( c_months_u(finish_year=end_year, finish_month=end_month, months=months)) start_date, finish_date = month_list[0][0], month_list[-1][-1] forecast_date = chellow.computer.forecast_date() running_name, finished_name = chellow.dloads.make_names( "dc_virtual_bills.csv", user) f = open(running_name, mode="w", newline="") writer = csv.writer(f, lineterminator="\n") bill_titles = chellow.computer.contract_func(caches, contract, "virtual_bill_titles")() header_titles = [ "Import MPAN Core", "Export MPAN Core", "Start Date", "Finish Date", ] vb_func = chellow.computer.contract_func(caches, contract, "virtual_bill") writer.writerow(header_titles + bill_titles) for era in (sess.query(Era).distinct().filter( or_(Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date, Era.dc_contract == contract, ).options(joinedload(Era.channels)).order_by(Era.supply_id)): imp_mpan_core = era.imp_mpan_core if imp_mpan_core is None: imp_mpan_core_str = "" is_import = False else: is_import = True imp_mpan_core_str = imp_mpan_core exp_mpan_core = era.exp_mpan_core exp_mpan_core_str = "" if exp_mpan_core is None else exp_mpan_core chunk_start = hh_max(era.start_date, start_date) chunk_finish = hh_min(era.finish_date, finish_date) vals = [ imp_mpan_core_str, exp_mpan_core_str, hh_format(chunk_start), hh_format(chunk_finish), ] supply_source = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, is_import, caches) vb_func(supply_source) bill = supply_source.dc_bill for title in bill_titles: vals.append(csv_make_val(bill.get(title))) if title in bill: del bill[title] for k in sorted(bill.keys()): vals.append(k) vals.append(csv_make_val(bill[k])) writer.writerow(vals) # Avoid long-running transactions sess.rollback() except BadRequest as e: msg = "Problem " if supply_source is not None: msg += ("with supply " + supply_source.mpan_core + " starting at " + hh_format(supply_source.start_date) + " ") msg += str(e) writer.writerow([msg]) except BaseException: msg = "Problem " + traceback.format_exc() + "\n" f.write(msg) finally: f.close() os.rename(running_name, finished_name) if sess is not None: sess.close()
def _process_hh(ds, rate_period, est_kw, hh): month_start, month_finish = next( c_months_u(start_year=hh["ct-year"], start_month=hh["ct-month"])) month_start_ct = to_ct(month_start) if month_start_ct.month > 3: year = month_start_ct.year else: year = month_start_ct.year - 1 financial_year_start = to_utc(ct_datetime(year, 4, 1)) last_financial_year_start = to_utc(ct_datetime(year - 1, 4, 1)) financial_year_finish = to_utc(ct_datetime(year + 1, 3, 31, 23, 30)) est_triad_kws = [] earliest_triad = None for dt in get_file_rates(ds.caches, "triad_dates", last_financial_year_start)["triad_dates"]: triad_hh = None earliest_triad = hh_min(earliest_triad, dt) try: d = next(ds.get_data_sources(dt, dt, financial_year_start)) chellow.duos.duos_vb(d) triad_hh = d.hh_data[0] while dt < financial_year_start: dt += relativedelta(years=1) for d in ds.get_data_sources(dt, dt, financial_year_start): chellow.duos.duos_vb(d) datum = d.hh_data[0] triad_hh["laf"] = datum["laf"] triad_hh["gsp-kw"] = datum["laf"] * triad_hh["msp-kw"] except StopIteration: triad_hh = { "hist-start": dt, "msp-kw": 0, "start-date": dt, "status": "before start of MPAN", "laf": 1, "gsp-kw": 0, } est_triad_kws.append(triad_hh) if ds.site is None: era = ds.supply.find_era_at(ds.sess, earliest_triad) if (era is None or era.get_channel(ds.sess, ds.is_import, "ACTIVE") is None and est_kw is None): est_kw = 0.85 * max(datum["msp-kwh"] for datum in ds.hh_data) * 2 if est_kw is not None: for est_datum in est_triad_kws: est_datum["msp-kw"] = est_kw est_datum["gsp-kw"] = est_datum["msp-kw"] * est_datum["laf"] gsp_kw = 0 for i, triad_hh in enumerate(est_triad_kws): triad_prefix = "triad-estimate-" + str(i + 1) hh[triad_prefix + "-date"] = triad_hh["hist-start"] hh[triad_prefix + "-msp-kw"] = triad_hh["msp-kw"] hh[triad_prefix + "-status"] = triad_hh["status"] hh[triad_prefix + "-laf"] = triad_hh["laf"] hh[triad_prefix + "-gsp-kw"] = triad_hh["gsp-kw"] gsp_kw += triad_hh["gsp-kw"] hh["triad-estimate-gsp-kw"] = gsp_kw / 3 polarity = "import" if ds.llfc.is_import else "export" gsp_group_code = ds.gsp_group_code rate = float( get_file_rates( ds.caches, "triad_rates", month_start)["triad_gbp_per_gsp_kw"][polarity][gsp_group_code]) hh["triad-estimate-rate"] = rate est_triad_gbp = hh["triad-estimate-rate"] * hh["triad-estimate-gsp-kw"] if rate_period == "monthly": total_intervals = 12 est_intervals = 1 hh["triad-estimate-months"] = est_intervals else: dt = financial_year_start total_intervals = 0 while dt <= financial_year_finish: total_intervals += 1 dt += relativedelta(days=1) est_intervals = 0 for d in ds.get_data_sources(month_start, month_finish): for h in d.hh_data: if h["ct-decimal-hour"] == 0: est_intervals += 1 hh["triad-estimate-days"] = est_intervals hh["triad-estimate-gbp"] = est_triad_gbp / total_intervals * est_intervals if hh["ct-month"] == 3: triad_kws = [] for t_date in get_file_rates(ds.caches, "triad_dates", month_start)["triad_dates"]: try: d = next(ds.get_data_sources(t_date, t_date)) if (ds.supplier_contract is None or d.supplier_contract == ds.supplier_contract): chellow.duos.duos_vb(d) thh = d.hh_data[0] else: thh = { "hist-start": t_date, "msp-kw": 0, "start-date": t_date, "status": "before contract", "laf": "before contract", "gsp-kw": 0, } except StopIteration: thh = { "hist-start": t_date, "msp-kw": 0, "start-date": t_date, "status": "before start of supply", "laf": "before start of supply", "gsp-kw": 0, } while t_date < financial_year_start: t_date += relativedelta(years=1) try: d = next(ds.get_data_sources(t_date, t_date)) if (ds.supplier_contract is None or d.supplier_contract == ds.supplier_contract): chellow.duos.duos_vb(d) thh["laf"] = d.hh_data[0]["laf"] thh["gsp-kw"] = thh["laf"] * thh["msp-kw"] except StopIteration: pass triad_kws.append(thh) gsp_kw = 0 for i, triad_hh in enumerate(triad_kws): pref = "triad-actual-" + str(i + 1) hh[pref + "-date"] = triad_hh["start-date"] hh[pref + "-msp-kw"] = triad_hh["msp-kw"] hh[pref + "-status"] = triad_hh["status"] hh[pref + "-laf"] = triad_hh["laf"] hh[pref + "-gsp-kw"] = triad_hh["gsp-kw"] gsp_kw += triad_hh["gsp-kw"] hh["triad-actual-gsp-kw"] = gsp_kw / 3 polarity = "import" if ds.llfc.is_import else "export" gsp_group_code = ds.gsp_group_code tot_rate = 0 for start_date, finish_date, script in get_file_scripts("triad_rates"): if start_date <= financial_year_finish and not hh_before( finish_date, financial_year_start): start_month = to_ct(start_date).month if start_month < 4: start_month += 12 if finish_date is None: finish_month = 3 else: finish_month = to_ct(finish_date).month if finish_month < 4: finish_month += 12 rt = get_file_rates( ds.caches, "triad_rates", start_date )["triad_gbp_per_gsp_kw"][polarity][gsp_group_code] tot_rate += (finish_month - start_month + 1) * float(rt) rate = tot_rate / 12 hh["triad-actual-rate"] = rate hh["triad-actual-gbp"] = hh["triad-actual-rate"] * hh[ "triad-actual-gsp-kw"] era = ds.supply.find_era_at(ds.sess, month_finish) est_intervals = 0 interval = (relativedelta( months=1) if rate_period == "monthly" else relativedelta(days=1)) dt = month_finish while era is not None and dt > financial_year_start: est_intervals += 1 dt -= interval if hh_after(dt, era.finish_date): era = ds.supply.find_era_at(ds.sess, dt) if rate_period == "monthly": hh["triad-all-estimates-months"] = est_intervals else: hh["triad-all-estimates-days"] = est_intervals hh["triad-all-estimates-gbp"] = (est_triad_gbp / total_intervals * est_intervals * -1)
def content(site_id, g_supply_id, user, compression, finish_year, finish_month, months, now=None): if now is None: now = ct_datetime_now() report_context = {} sess = None month_list = list( c_months_u(finish_year=finish_year, finish_month=finish_month, months=months)) start_date, finish_date = month_list[0][0], month_list[-1][-1] try: sess = Session() base_name = [ "g_monthly_duration", hh_format(start_date).replace(" ", "_").replace(":", "").replace("-", ""), "for", str(months), "months", ] forecast_from = chellow.computer.forecast_date() sites = (sess.query(Site).join(SiteGEra).join(GEra).filter( SiteGEra.is_physical == true()).distinct().order_by(Site.code)) if site_id is not None: site = Site.get_by_id(sess, site_id) sites = sites.filter(Site.id == site.id) base_name.append("site") base_name.append(site.code) if g_supply_id is not None: g_supply = GSupply.get_by_id(sess, g_supply_id) base_name.append("g_supply") base_name.append(str(g_supply.id)) sites = sites.filter(GEra.g_supply == g_supply) running_name, finished_name = chellow.dloads.make_names( "_".join(base_name) + ".ods", user) rf = open(running_name, "wb") site_rows = [] g_era_rows = [] era_header_titles = [ "creation_date", "mprn", "supply_name", "exit_zone", "msn", "unit", "contract", "site_id", "site_name", "associated_site_ids", "month", ] site_header_titles = [ "creation_date", "site_id", "site_name", "associated_site_ids", "month", ] summary_titles = ["kwh", "gbp", "billed_kwh", "billed_gbp"] vb_titles = [] conts = (sess.query(GContract).join(GEra).join(GSupply).filter( GEra.start_date <= finish_date, or_(GEra.finish_date == null(), GEra.finish_date >= start_date), ).distinct().order_by(GContract.id)) if g_supply_id is not None: conts = conts.filter(GEra.g_supply_id == g_supply_id) for cont in conts: title_func = chellow.computer.contract_func( report_context, cont, "virtual_bill_titles") if title_func is None: raise Exception("For the contract " + cont.name + " there doesn't seem " + "to be a 'virtual_bill_titles' function.") for title in title_func(): if title not in vb_titles: vb_titles.append(title) g_era_rows.append(era_header_titles + summary_titles + vb_titles) site_rows.append(site_header_titles + summary_titles) for month_start, month_finish in month_list: for site in sites.filter( GEra.start_date <= month_finish, or_(GEra.finish_date == null(), GEra.finish_date >= month_start), ): site_kwh = site_gbp = site_billed_kwh = site_billed_gbp = 0 for g_era in (sess.query(GEra).join(SiteGEra).filter( SiteGEra.site == site, SiteGEra.is_physical == true(), GEra.start_date <= month_finish, or_(GEra.finish_date == null(), GEra.finish_date >= month_start), ).options( joinedload(GEra.g_contract), joinedload(GEra.g_supply), joinedload(GEra.g_supply).joinedload( GSupply.g_exit_zone), ).order_by(GEra.id)): g_supply = g_era.g_supply if g_supply_id is not None and g_supply.id != g_supply_id: continue ss_start = hh_max(g_era.start_date, month_start) ss_finish = hh_min(g_era.finish_date, month_finish) ss = GDataSource( sess, ss_start, ss_finish, forecast_from, g_era, report_context, None, ) contract = g_era.g_contract vb_function = contract_func(report_context, contract, "virtual_bill") if vb_function is None: raise BadRequest( "The contract " + contract.name + " doesn't have the virtual_bill() function.") vb_function(ss) bill = ss.bill try: gbp = bill["net_gbp"] except KeyError: gbp = 0 bill["problem"] += ( "For the supply " + ss.mprn + " the virtual bill " + str(bill) + " from the contract " + contract.name + " does not contain the net_gbp key.") try: kwh = bill["kwh"] except KeyError: kwh = 0 bill["problem"] += ("For the supply " + ss.mprn + " the virtual bill " + str(bill) + " from the contract " + contract.name + " does not contain the 'kwh' key.") billed_kwh = billed_gbp = 0 g_era_associates = { s.site.code for s in g_era.site_g_eras if not s.is_physical } for g_bill in sess.query(GBill).filter( GBill.g_supply == g_supply, GBill.start_date <= ss_finish, GBill.finish_date >= ss_start, ): bill_start = g_bill.start_date bill_finish = g_bill.finish_date bill_duration = (bill_finish - bill_start ).total_seconds() + (30 * 60) overlap_duration = (min(bill_finish, ss_finish) - max( bill_start, ss_start)).total_seconds() + (30 * 60) overlap_proportion = overlap_duration / bill_duration billed_kwh += overlap_proportion * float(g_bill.kwh) billed_gbp += overlap_proportion * float(g_bill.net) associated_site_ids = ",".join(sorted(g_era_associates)) g_era_rows.append([ make_val(v) for v in [ now, g_supply.mprn, g_supply.name, g_supply.g_exit_zone.code, g_era.msn, g_era.g_unit.code, contract.name, site.code, site.name, associated_site_ids, month_finish, kwh, gbp, billed_kwh, billed_gbp, ] ] + [make_val(bill.get(t)) for t in vb_titles]) site_kwh += kwh site_gbp += gbp site_billed_kwh += billed_kwh site_billed_gbp += billed_gbp linked_sites = ", ".join(s.code for s in site.find_linked_sites( sess, month_start, month_finish)) site_rows.append([ make_val(v) for v in [ now, site.code, site.name, linked_sites, month_finish, site_kwh, site_gbp, site_billed_kwh, site_billed_gbp, ] ]) sess.rollback() write_spreadsheet(rf, compression, site_rows, g_era_rows) except BadRequest as e: site_rows.append(["Problem " + e.description]) write_spreadsheet(rf, compression, site_rows, g_era_rows) except BaseException: msg = traceback.format_exc() sys.stderr.write(msg + "\n") site_rows.append(["Problem " + msg]) write_spreadsheet(rf, compression, site_rows, g_era_rows) finally: if sess is not None: sess.close() try: rf.close() os.rename(running_name, finished_name) except BaseException: msg = traceback.format_exc() r_name, f_name = chellow.dloads.make_names("error.txt", user) ef = open(r_name, "w") ef.write(msg + "\n") ef.close()
def datum_2010_04_01(ds, hh): start_date = hh["start-date"] dno_cache = ds.caches["dno"][ds.dno_code] if not ds.full_channels and not (hh["msp-kwh"] > 0 and hh["anti-msp-kwh"] == 0): imp_msp_kvarh, exp_msp_kvarh = 0, 0 else: imp_msp_kvarh, exp_msp_kvarh = hh["imp-msp-kvarh"], hh["exp-msp-kvarh"] try: gsp_group_cache = dno_cache[ds.gsp_group_code] except KeyError: gsp_group_cache = dno_cache[ds.gsp_group_code] = {} try: tariff = gsp_group_cache["tariffs"][ds.llfc_code][start_date] except KeyError: try: tariff_cache = gsp_group_cache["tariffs"] except KeyError: tariff_cache = gsp_group_cache["tariffs"] = {} try: tariffs = tariff_cache[ds.llfc_code] except KeyError: tariffs = tariff_cache[ds.llfc_code] = {} try: tariff = tariffs[start_date] except KeyError: tariff = None try: tariff_list = get_file_rates(ds.caches, ds.dno_code, start_date)[ ds.gsp_group_code ]["tariffs"] except KeyError as e: raise BadRequest(str(e)) for llfcs_pcs, tf in tariff_list.items(): key = llfcs_pcs.split("_") llfcs = [v.strip() for v in key[0].split(",")] if len(key) == 2: pcs = [v.strip() for v in key[1].split(",")] else: pcs = None if ds.llfc_code in llfcs and (pcs is None or ds.pc_code in pcs): tariff = tf break if tariff is None: raise BadRequest( f"For the DNO {ds.dno_code} and timestamp {hh_format(start_date)} " f"and GSP group {ds.gsp_group_code}, the LLFC '{ds.llfc_code}' " f"can't be found in the 'tariffs' section." ) tariffs[start_date] = tariff try: band = gsp_group_cache["bands"][start_date] except KeyError: try: bands_cache = gsp_group_cache["bands"] except KeyError: bands_cache = gsp_group_cache["bands"] = {} try: band = bands_cache[start_date] except KeyError: band = "green" ct_hr = hh["ct-decimal-hour"] weekend = hh["ct-day-of-week"] > 4 try: slots = get_file_rates(ds.caches, ds.dno_code, start_date)[ ds.gsp_group_code ]["bands"] except KeyError as e: raise BadRequest(str(e)) for slot in slots: slot_weekend = slot["weekend"] == 1 if slot_weekend == weekend and slot["start"] <= ct_hr < slot["finish"]: band = slot["band"] break bands_cache[start_date] = band try: laf = dno_cache["lafs"][ds.llfc_code][start_date] except KeyError: try: laf_cache = dno_cache["lafs"] except KeyError: laf_cache = dno_cache["lafs"] = {} try: laf_cache_llfc = laf_cache[ds.llfc_code] except KeyError: laf_cache_llfc = laf_cache[ds.llfc_code] = {} try: laf = laf_cache_llfc[start_date] except KeyError: dno_code = ds.dno_code if dno_code == "99": laf_cache_llfc[start_date] = 1 else: m_start, m_finish = next( c_months_u( start_year=hh["ct-year"], start_month=hh["ct-month"], months=1 ) ) for (laf,) in ds.sess.execute( select(Laf) .join(Llfc) .join(Party) .where( Party.dno_code == ds.dno_code, Llfc.code == ds.llfc_code, Laf.timestamp >= m_start, Laf.timestamp <= m_finish, ) ): laf_cache_llfc[laf.timestamp] = float(laf.value) try: laf = laf_cache_llfc[start_date] except KeyError: raise BadRequest( f"Missing LAF for DNO {ds.dno_code}, LLFC {ds.llfc_code} and " f"timestamp {hh_format(start_date)}" ) hh["laf"] = laf hh["gsp-kwh"] = laf * hh["msp-kwh"] hh["gsp-kw"] = hh["gsp-kwh"] * 2 kvarh = max( max(imp_msp_kvarh, exp_msp_kvarh) - (0.95 ** -2 - 1) ** 0.5 * hh["msp-kwh"], 0 ) hh["duos-reactive-kvarh"] = kvarh duos_reactive_rate = tariff["gbp-per-kvarh"] if duos_reactive_rate is not None: duos_reactive_rate = float(duos_reactive_rate) if duos_reactive_rate != 0: hh["duos-reactive-rate"] = duos_reactive_rate hh["duos-reactive-gbp"] = kvarh * duos_reactive_rate rate = float(tariff[KEYS[band]["tariff-rate"]]) hh[KEYS[band]["bill-rate"]] = rate hh[KEYS[band]["kwh"]] = hh["msp-kwh"] hh[KEYS[band]["gbp"]] = rate * hh["msp-kwh"] if hh["ct-decimal-hour"] == 23.5 and not ds.is_displaced: hh["duos-fixed-days"] = 1 rate = float(tariff["gbp-per-mpan-per-day"]) hh["duos-fixed-rate"] = rate hh["duos-fixed-gbp"] = rate hh["duos-availability-days"] = 1 kva = ds.sc hh["duos-availability-kva"] = kva rate = float(tariff["gbp-per-kva-per-day"]) hh["duos-availability-rate"] = rate hh["duos-availability-gbp"] = rate * kva if hh["ct-is-month-end"] and not ds.is_displaced: month_to = start_date month_from = to_utc(ct_datetime(hh["ct-year"], hh["ct-month"], 1)) md_kva = 0 days_in_month = 0 for dsc in ds.get_data_sources(month_from, month_to): for datum in dsc.hh_data: md_kva = max( md_kva, ( datum["msp-kw"] ** 2 + max(datum["imp-msp-kvar"], datum["exp-msp-kvar"]) ** 2 ) ** 0.5, ) if datum["ct-decimal-hour"] == 0: days_in_month += 1 excess_kva = max(md_kva - ds.sc, 0) if "excess-gbp-per-kva-per-day" in tariff and excess_kva != 0: rate = float(tariff["excess-gbp-per-kva-per-day"]) hh["duos-excess-availability-kva"] = excess_kva rate = float(tariff["excess-gbp-per-kva-per-day"]) hh["duos-excess-availability-rate"] = rate hh["duos-excess-availability-days"] = days_in_month hh["duos-excess-availability-gbp"] = rate * excess_kva * days_in_month
def create_csv(f, sess, start_date, finish_date, contract_id): caches = {} writer = csv.writer(f, lineterminator="\n") contract = Contract.get_supplier_by_id(sess, contract_id) forecast_date = chellow.computer.forecast_date() start_date_ct, finish_date_ct = to_ct(start_date), to_ct(finish_date) month_pairs = c_months_u( start_year=start_date_ct.year, start_month=start_date_ct.month, finish_year=finish_date_ct.year, finish_month=finish_date_ct.month, ) bill_titles = contract_func(caches, contract, "virtual_bill_titles")() for tpr in ( sess.query(Tpr) .join(MeasurementRequirement) .join(Ssc) .join(Era) .filter( Era.start_date <= finish_date, or_(Era.finish_date == null(), Era.finish_date >= start_date), or_( Era.imp_supplier_contract == contract, Era.exp_supplier_contract == contract, ), ) .order_by(Tpr.code) .distinct() ): for suffix in ("-kwh", "-rate", "-gbp"): bill_titles.append(tpr.code + suffix) writer.writerow( ["MPAN Core", "Site Code", "Site Name", "Account", "From", "To"] + bill_titles ) vb_func = contract_func(caches, contract, "virtual_bill") for month_start, month_finish in month_pairs: period_start = hh_max(start_date, month_start) period_finish = hh_min(finish_date, month_finish) for era in ( sess.query(Era) .filter( or_( Era.imp_supplier_contract == contract, Era.exp_supplier_contract == contract, ), Era.start_date <= period_finish, or_(Era.finish_date == null(), Era.finish_date >= period_start), ) .order_by(Era.imp_mpan_core) ): try: vals = _process_era( sess, caches, vb_func, forecast_date, bill_titles, contract, period_start, period_finish, era, ) writer.writerow(csv_make_val(v) for v in vals) except BadRequest as e: raise BadRequest( f"Problem with {chellow.utils.url_root}eras/{era.id}/edit " f"{e.description}" )
def _write_sites(sess, caches, writer, year, site_id): titles = ( "Site Code", "Site Name", "Displaced TRIAD 1 Date", "Displaced TRIAD 1 MSP kW", "Displaced TRIAD LAF", "Displaced TRIAD 1 GSP kW", "Displaced TRIAD 2 Date", "Displaced TRIAD 2 MSP kW", "Displaced TRIAD 2 LAF", "Displaced TRIAD 2 GSP kW", "Displaced TRIAD 3 Date", "Displaced TRIAD 3 MSP kW", "Displaced TRIAD 3 LAF", "Displaced TRIAD 3 GSP kW", "Displaced GSP kW", "Displaced Rate GBP / kW", "GBP", ) writer.writerow(titles) march_finish_ct = ct_datetime(year, 4, 1) - HH march_finish_utc = to_utc(march_finish_ct) march_start_ct = ct_datetime(year, 3, 1) march_start_utc = to_utc(march_start_ct) year_start = to_utc(ct_datetime(year - 1, 4, 1)) forecast_date = chellow.computer.forecast_date() sites = _make_sites(sess, year_start, march_finish_utc, site_id, ("gen", "gen-net")) scalar_names = {"triad-actual-gsp-kw", "triad-actual-gbp"} rate_names = {"triad-actual-rate", "triad-estimate-rate"} for i in range(1, 4): pref = "triad-actual-" + str(i) + "-" for suf in ("msp-kw", "gsp-kw"): scalar_names.add(pref + suf) for suf in ("date", "status", "laf"): rate_names.add(pref + suf) for site in sites: displaced_era = None for month_start, month_finish in sorted(c_months_u(start_year=year - 1, start_month=4, months=12), reverse=True): displaced_era = chellow.computer.displaced_era( sess, caches, site, month_start, month_finish, forecast_date) if displaced_era is not None: break if displaced_era is None: break site_ds = chellow.computer.SiteSource( sess, site, march_start_utc, march_finish_utc, forecast_date, caches, displaced_era, ) chellow.duos.duos_vb(site_ds) chellow.triad.hh(site_ds) for hh in site_ds.hh_data: bill_hh = site_ds.supplier_bill_hhs[hh["start-date"]] for k in scalar_names & hh.keys(): bill_hh[k] = hh[k] for k in rate_names & hh.keys(): bill_hh[k] = {hh[k]} bill = reduce_bill_hhs(site_ds.supplier_bill_hhs) values = [site.code, site.name] for i in range(1, 4): triad_prefix = "triad-actual-" + str(i) + "-" for suffix in ("date", "msp-kw", "laf", "gsp-kw"): values.append(csv_make_val(bill[triad_prefix + suffix])) for suffix in ("gsp-kw", "rate", "gbp"): values.append(csv_make_val(bill["triad-actual-" + suffix])) writer.writerow(values) # Avoid long-running transaction sess.rollback()
def fetch_cvs(sess, log_f): log_f("Starting to check GCv rates.") contract = Contract.get_non_core_by_name(sess, "g_cv") latest_rs = (sess.query(RateScript).filter( RateScript.contract == contract).order_by( RateScript.start_date.desc()).first()) latest_rs_id = latest_rs.id latest_rs_start_date_ct = to_ct(latest_rs.start_date) month_pairs = list( c_months_u( start_year=latest_rs_start_date_ct.year, start_month=latest_rs_start_date_ct.month, months=2, )) month_start, month_finish = month_pairs[1] now = utc_datetime_now() props = contract.make_properties() if not props.get("enabled", False): log_f("The automatic importer is disabled. To enable it, edit the " "contract properties to set 'enabled' to true.") return search_start = month_start - relativedelta(days=1) search_finish = month_finish + relativedelta(days=1) if now <= search_finish: return url = props["url"] log_f(f"Checking to see if data is available " f"from {hh_format(search_start)} to " f"{hh_format(search_finish)} at {url}") res = requests.post( url, data={ "LatestValue": "true", "PublicationObjectIds": "408:28,+408:5328,+408:5320,+408:5291," "+408:5366,+408:5312,+408:5346,+408:5324,+408:5316,+408:5308," "+408:5336,+408:5333,+408:5342,+408:5354,+408:82,+408:70," "+408:59,+408:38,+408:49", "PublicationObjectStagingIds": "PUBOBJ1660,PUBOB4507,PUBOB4508," "PUBOB4510,PUBOB4509,PUBOB4511,PUBOB4512,PUBOB4513,PUBOB4514," "PUBOB4515,PUBOB4516,PUBOB4517,PUBOB4518,PUBOB4519,PUBOB4521," "PUBOB4520,PUBOB4522,PUBOBJ1661,PUBOBJ1662", "Applicable": "applicableFor", "PublicationObjectCount": "19", "FromUtcDatetime": param_format(search_start), "ToUtcDateTime": param_format(search_finish), "FileType": "Csv", }, ) log_f(f"Received {res.status_code} {res.reason}") month_cv = defaultdict(dict) cf = csv.reader(res.text.splitlines()) row = next(cf) # Skip title row last_date = utc_datetime(1900, 1, 1) for row in cf: applicable_at_str = row[0] applicable_for_str = row[1] applicable_for = to_utc( to_ct(Datetime.strptime(applicable_for_str, "%d/%m/%Y"))) data_item = row[2] value_str = row[3] if "LDZ" in data_item and month_start <= applicable_for < month_finish: ldz = data_item[-3:-1] cvs = month_cv[ldz] applicable_at = to_utc( to_ct(Datetime.strptime(applicable_at_str, "%d/%m/%Y %H:%M:%S"))) last_date = max(last_date, applicable_at) cv = Decimal(value_str) try: existing = cvs[applicable_for.day] if applicable_at > existing["applicable_at"]: existing["cv"] = cv existing["applicable_at"] = applicable_at except KeyError: cvs[applicable_for.day] = { "cv": cv, "applicable_at": applicable_at } all_equal = len(set(map(len, month_cv.values()))) <= 1 if last_date + Timedelta(days=1) > month_finish and all_equal: log_f("The whole month's data is there.") script = {"cvs": month_cv} contract = Contract.get_non_core_by_name(sess, "g_cv") rs = RateScript.get_by_id(sess, latest_rs_id) contract.update_rate_script(sess, rs, rs.start_date, month_finish, loads(rs.script)) sess.flush() contract.insert_rate_script(sess, month_start, script) sess.commit() log_f("Added new rate script.") else: log_f(f"There isn't a whole month there yet. The " f"last date is {hh_format(last_date)}.")