def _make_calcs( sess, site, start_date, finish_date, supply_id, site_deltas, forecast_from, report_context, era_maps, ): site_gen_types = set() calcs = [] for era in (sess.query(Era).join(SiteEra).join(Pc).filter( SiteEra.site == site, SiteEra.is_physical == true(), Era.start_date <= finish_date, or_(Era.finish_date == null(), Era.finish_date >= start_date), ).options( joinedload(Era.ssc), joinedload(Era.dc_contract), joinedload(Era.mop_contract), joinedload(Era.imp_supplier_contract), joinedload(Era.exp_supplier_contract), joinedload(Era.channels), joinedload(Era.imp_llfc).joinedload(Llfc.voltage_level), joinedload(Era.exp_llfc).joinedload(Llfc.voltage_level), joinedload(Era.cop), joinedload(Era.supply).joinedload(Supply.dno), joinedload(Era.supply).joinedload(Supply.gsp_group), joinedload(Era.supply).joinedload(Supply.source), joinedload(Era.mtc).joinedload(Mtc.meter_type), joinedload(Era.pc), joinedload(Era.site_eras), ).order_by(Pc.code)): supply = era.supply if supply.generator_type is not None: site_gen_types.add(supply.generator_type.code) if supply_id is not None and supply.id != supply_id: continue ss_start = hh_max(era.start_date, start_date) ss_finish = hh_min(era.finish_date, finish_date) if era.imp_mpan_core is None: imp_ss = None else: sup_deltas = site_deltas["supply_deltas"][True][supply.source.code] imp_ss = SupplySource( sess, ss_start, ss_finish, forecast_from, era, True, report_context, era_maps=era_maps, deltas=sup_deltas, ) if era.exp_mpan_core is None: exp_ss = None measurement_type = imp_ss.measurement_type else: sup_deltas = site_deltas["supply_deltas"][False][ supply.source.code] exp_ss = SupplySource( sess, ss_start, ss_finish, forecast_from, era, False, report_context, era_maps=era_maps, deltas=sup_deltas, ) measurement_type = exp_ss.measurement_type order = meter_order[measurement_type] calcs.append( (order, era.imp_mpan_core, era.exp_mpan_core, imp_ss, exp_ss)) # Check if gen deltas haven't been consumed extra_sss = set() for is_imp in (True, False): sup_deltas = site_deltas["supply_deltas"][is_imp]["gen"] if (len( list(t for t in sup_deltas["site"] if start_date <= t <= finish_date)) > 0): extra_sss.add(is_imp) displaced_era = chellow.computer.displaced_era( sess, report_context, site, start_date, finish_date, forecast_from, has_scenario_generation=len(extra_sss) > 0, ) if len(extra_sss) > 0: if True in extra_sss: sup_deltas = site_deltas["supply_deltas"][True]["gen"] imp_ss_name = site.code + "_extra_gen_TRUE" imp_ss = ScenarioSource( sess, start_date, finish_date, True, report_context, sup_deltas, displaced_era.imp_supplier_contract, imp_ss_name, ) else: imp_ss_name = imp_ss = None if False in extra_sss: exp_ss_name = site.code + "_extra_gen_FALSE" sup_deltas = site_deltas["supply_deltas"][False]["gen"] exp_ss = ScenarioSource( sess, start_date, finish_date, False, report_context, sup_deltas, displaced_era.imp_supplier_contract, imp_ss_name, ) else: exp_ss_name = exp_ss = None calcs.append((0, imp_ss_name, exp_ss_name, imp_ss, exp_ss)) # Check if exp net deltas haven't been consumed sup_deltas = site_deltas["supply_deltas"][False]["net"] if len( list(t for t in sup_deltas["site"] if start_date <= t <= finish_date)) > 0: ss_name = site.code + "_extra_net_export" ss = SupplySource( sess, start_date, finish_date, forecast_from, displaced_era, False, report_context, era_maps=era_maps, deltas=sup_deltas, ) calcs.append((0, None, ss_name, None, ss)) return calcs, displaced_era, site_gen_types
def _process_supply( sess, caches, supply_id, bill_map, forecast_date, contract, vbf, virtual_bill_titles, writer, titles, report_run, ): gaps = {} data_sources = {} market_role_code = contract.market_role.code bill_ids = bill_map[supply_id] while len(bill_ids) > 0: bill_id = list(sorted(bill_ids))[0] bill_ids.remove(bill_id) bill = (sess.query(Bill).filter(Bill.id == bill_id).options( joinedload(Bill.batch), joinedload(Bill.bill_type), joinedload(Bill.reads), joinedload(Bill.supply), joinedload(Bill.reads).joinedload(RegisterRead.present_type), joinedload(Bill.reads).joinedload(RegisterRead.previous_type), ).one()) virtual_bill = {"problem": ""} supply = bill.supply read_dict = {} for read in bill.reads: gen_start = read.present_date.replace(hour=0).replace(minute=0) gen_finish = gen_start + relativedelta(days=1) - HH msn_match = False read_msn = read.msn for read_era in supply.find_eras(sess, gen_start, gen_finish): if read_msn == read_era.msn: msn_match = True break if not msn_match: virtual_bill["problem"] += ( f"The MSN {read_msn} of the register read {read.id} doesn't match " f"the MSN of the era.") for dt, typ in [ (read.present_date, read.present_type), (read.previous_date, read.previous_type), ]: key = str(dt) + "-" + read.msn try: if typ != read_dict[key]: virtual_bill[ "problem"] += f" Reads taken on {dt} have differing read types." except KeyError: read_dict[key] = typ bill_start = bill.start_date bill_finish = bill.finish_date covered_start = bill_start covered_finish = bill_start covered_bdown = {"sum-msp-kwh": 0, "net-gbp": 0, "vat-gbp": 0} vb_elems = set() enlarged = True while enlarged: enlarged = False covered_elems = find_elements(bill) covered_bills = OrderedDict((b.id, b) for b in sess.query( Bill).join(Batch).join(Contract).join(MarketRole).filter( Bill.supply == supply, Bill.start_date <= covered_finish, Bill.finish_date >= covered_start, MarketRole.code == market_role_code, ).order_by(Bill.start_date, Bill.issue_date)) while True: to_del = None for a, b in combinations(covered_bills.values(), 2): if all(( a.start_date == b.start_date, a.finish_date == b.finish_date, a.kwh == -1 * b.kwh, a.net == -1 * b.net, a.vat == -1 * b.vat, a.gross == -1 * b.gross, )): to_del = (a.id, b.id) break if to_del is None: break else: for k in to_del: del covered_bills[k] bill_ids.discard(k) for k, covered_bill in tuple(covered_bills.items()): elems = find_elements(covered_bill) if elems.isdisjoint(covered_elems): if k != bill.id: del covered_bills[k] continue else: covered_elems.update(elems) if covered_bill.start_date < covered_start: covered_start = covered_bill.start_date enlarged = True break if covered_bill.finish_date > covered_finish: covered_finish = covered_bill.finish_date enlarged = True break if len(covered_bills) == 0: continue primary_covered_bill = None for covered_bill in covered_bills.values(): bill_ids.discard(covered_bill.id) covered_bdown["net-gbp"] += float(covered_bill.net) covered_bdown["vat-gbp"] += float(covered_bill.vat) covered_bdown["sum-msp-kwh"] += float(covered_bill.kwh) covered_rates = defaultdict(set) for k, v in loads(covered_bill.breakdown).items(): if k in ("raw_lines", "raw-lines"): continue if isinstance(v, list): covered_rates[k].update(set(v)) else: if isinstance(v, Decimal): v = float(v) try: covered_bdown[k] += v except KeyError: covered_bdown[k] = v except TypeError as detail: raise BadRequest( f"For key {k} in {[b.id for b in covered_bills.values()]} " f"the value {v} can't be added to the existing value " f"{covered_bdown[k]}. {detail}") if k.endswith("-gbp"): elem = k[:-4] covered_elems.add(elem) add_gap( caches, gaps, elem, covered_bill.start_date, covered_bill.finish_date, False, v, ) for k, v in covered_rates.items(): covered_bdown[k] = v.pop() if len(v) == 1 else None if primary_covered_bill is None or ( (covered_bill.finish_date - covered_bill.start_date) > (primary_covered_bill.finish_date - primary_covered_bill.start_date)): primary_covered_bill = covered_bill metered_kwh = 0 for era in (sess.query(Era).filter( Era.supply == supply, Era.start_date <= covered_finish, or_(Era.finish_date == null(), Era.finish_date >= covered_start), ).distinct().options( joinedload(Era.channels), joinedload(Era.cop), joinedload(Era.dc_contract), joinedload(Era.exp_llfc), joinedload(Era.exp_llfc).joinedload(Llfc.voltage_level), joinedload(Era.exp_supplier_contract), joinedload(Era.imp_llfc), joinedload(Era.imp_llfc).joinedload(Llfc.voltage_level), joinedload(Era.imp_supplier_contract), joinedload(Era.mop_contract), joinedload(Era.mtc).joinedload(Mtc.meter_type), joinedload(Era.pc), joinedload(Era.supply).joinedload(Supply.dno), joinedload(Era.supply).joinedload(Supply.gsp_group), joinedload(Era.supply).joinedload(Supply.source), )): chunk_start = hh_max(covered_start, era.start_date) chunk_finish = hh_min(covered_finish, era.finish_date) if contract not in ( era.mop_contract, era.dc_contract, era.imp_supplier_contract, era.exp_supplier_contract, ): virtual_bill["problem"] += ( f"From {hh_format(chunk_start)} to {hh_format(chunk_finish)} " f"the contract of the era doesn't match the contract of the bill." ) continue if contract.market_role.code == "X": polarity = contract != era.exp_supplier_contract else: polarity = era.imp_supplier_contract is not None try: ds_key = ( chunk_start, chunk_finish, forecast_date, era.id, polarity, primary_covered_bill.id, ) data_source = data_sources[ds_key] except KeyError: data_source = data_sources[ ds_key] = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, polarity, caches, primary_covered_bill, ) vbf(data_source) if data_source.measurement_type == "hh": metered_kwh += sum(h["msp-kwh"] for h in data_source.hh_data) else: ds = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, polarity, caches, ) metered_kwh += sum(h["msp-kwh"] for h in ds.hh_data) if market_role_code == "X": vb = data_source.supplier_bill vb_hhs = data_source.supplier_bill_hhs elif market_role_code == "C": vb = data_source.dc_bill vb_hhs = data_source.dc_bill_hhs elif market_role_code == "M": vb = data_source.mop_bill vb_hhs = data_source.mop_bill_hhs else: raise BadRequest("Odd market role.") for k, v in vb.items(): try: if isinstance(v, set): virtual_bill[k].update(v) else: virtual_bill[k] += v except KeyError: virtual_bill[k] = v except TypeError as detail: raise BadRequest(f"For key {k} and value {v}. {detail}") for dt, bl in vb_hhs.items(): for k, v in bl.items(): if all((k.endswith("-gbp"), k != "net-gbp", v != 0)): add_gap(caches, gaps, k[:-4], dt, dt, True, v) for k in virtual_bill.keys(): if k.endswith("-gbp"): vb_elems.add(k[:-4]) long_map = {} vb_keys = set(virtual_bill.keys()) for elem in sorted(vb_elems, key=len, reverse=True): els = long_map[elem] = set() for k in tuple(vb_keys): if k.startswith(elem + "-"): els.add(k) vb_keys.remove(k) for elem in vb_elems.difference(covered_elems): for k in long_map[elem]: del virtual_bill[k] try: del virtual_bill["net-gbp"] except KeyError: pass virtual_bill["net-gbp"] = sum(v for k, v in virtual_bill.items() if k.endswith("-gbp")) era = supply.find_era_at(sess, bill_finish) if era is None: imp_mpan_core = exp_mpan_core = None site_code = site_name = None virtual_bill[ "problem"] += "This bill finishes before or after the supply. " else: imp_mpan_core = era.imp_mpan_core exp_mpan_core = era.exp_mpan_core site = (sess.query(Site).join(SiteEra).filter( SiteEra.is_physical == true(), SiteEra.era == era).one()) site_code = site.code site_name = site.name # Find bill to use for header data if bill.id not in covered_bills: for cbill in covered_bills.values(): if bill.batch == cbill.batch: bill = cbill values = { "batch": bill.batch.reference, "bill-reference": bill.reference, "bill-type": bill.bill_type.code, "bill-kwh": bill.kwh, "bill-net-gbp": bill.net, "bill-vat-gbp": bill.vat, "bill-start-date": bill_start, "bill-finish-date": bill_finish, "imp-mpan-core": imp_mpan_core, "exp-mpan-core": exp_mpan_core, "site-code": site_code, "site-name": site_name, "covered-from": covered_start, "covered-to": covered_finish, "covered-bills": sorted(covered_bills.keys()), "metered-kwh": metered_kwh, } for title in virtual_bill_titles: try: cov_val = covered_bdown[title] del covered_bdown[title] except KeyError: cov_val = None values[f"covered-{title}"] = cov_val try: virt_val = virtual_bill[title] del virtual_bill[title] except KeyError: virt_val = None values[f"virtual-{title}"] = virt_val if title.endswith("-gbp"): if isinstance(virt_val, (int, float, Decimal)): if isinstance(cov_val, (int, float, Decimal)): diff_val = float(cov_val) - float(virt_val) else: diff_val = 0 - float(virt_val) else: diff_val = 0 values[f"difference-{title}"] = diff_val report_run_titles = list(titles) for title in sorted(virtual_bill.keys()): virt_val = virtual_bill[title] virt_title = f"virtual-{title}" values[virt_title] = virt_val report_run_titles.append(virt_title) if title in covered_bdown: cov_title = f"covered-{title}" cov_val = covered_bdown[title] values[cov_title] = cov_val report_run_titles.append(cov_title) if title.endswith("-gbp"): if isinstance(virt_val, (int, float, Decimal)): if isinstance(cov_val, (int, float, Decimal)): diff_val = float(cov_val) - float(virt_val) else: diff_val = 0 - float(virt_val) else: diff_val = 0 values[f"difference-{title}"] = diff_val t = "difference-tpr-gbp" try: values[t] += diff_val except KeyError: values[t] = diff_val report_run_titles.append(t) csv_row = [] for t in titles: v = values[t] if t == "covered-bills": val = " | ".join(str(b) for b in v) else: val = csv_make_val(v) csv_row.append(val) for t in report_run_titles: if t not in titles: csv_row.append(t) csv_row.append(values[t]) writer.writerow(csv_row) values["bill_id"] = bill.id values["batch_id"] = bill.batch.id values["supply_id"] = supply.id values["site_id"] = None if site_code is None else site.id report_run.insert_row(sess, "", report_run_titles, values, {"is_checked": False}) for bill in sess.query(Bill).filter( Bill.supply == supply, Bill.start_date <= covered_finish, Bill.finish_date >= covered_start, ): for k, v in loads(bill.breakdown).items(): if k.endswith("-gbp"): add_gap( caches, gaps, k[:-4], bill.start_date, bill.finish_date, False, v, ) # Avoid long-running transactions sess.commit() clumps = [] for element, elgap in sorted(gaps.items()): for start_date, hhgap in sorted(elgap.items()): if hhgap["has_virtual"] and not hhgap["has_covered"]: if len(clumps) == 0 or not all(( clumps[-1]["element"] == element, clumps[-1]["finish_date"] + HH == start_date, )): clumps.append({ "element": element, "start_date": start_date, "finish_date": start_date, "gbp": hhgap["gbp"], }) else: clumps[-1]["finish_date"] = start_date for i, clump in enumerate(clumps): vals = {} for title in titles: if title.startswith("difference-") and title.endswith("-gbp"): vals[title] = 0 else: vals[title] = None vals["covered-problem"] = "_".join(( "missing", clump["element"], "supplyid", str(supply.id), "from", hh_format(clump["start_date"]), )) vals["imp-mpan-core"] = imp_mpan_core vals["exp-mpan-core"] = exp_mpan_core vals["batch"] = "missing_bill" vals["bill-start-date"] = hh_format(clump["start_date"]) vals["bill-finish-date"] = hh_format(clump["finish_date"]) vals["difference-net-gbp"] = clump["gbp"] writer.writerow(csv_make_val(vals[title]) for title in titles) vals["bill_id"] = None vals["batch_id"] = None vals["supply_id"] = supply.id vals["site_id"] = None if site_code is None else site.id report_run.insert_row(sess, "", titles, vals, {"is_checked": False}) # Avoid long-running transactions sess.commit()
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 content(supply_id, start_date, finish_date, user): forecast_date = to_utc(Datetime.max) caches = {} f = sess = era = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'supplies_duration.csv', user) f = open(running_name, mode='w', newline='') w = csv.writer(f, lineterminator='\n') w.writerow( ( "Era Start", "Era Finish", "Supply Id", "Supply Name", "Source", "Generator Type", "Site Code", "Site Name", "Associated Site Codes", "From", "To", "PC", "MTC", "CoP", "SSC", "Properties", "MOP Contract", "MOP Account", "DC Contract", "DC Account", "Normal Reads", "Type", "Supply Start", "Supply Finish", "Import LLFC", "Import MPAN Core", "Import Supply Capacity", "Import Supplier", "Import Total MSP kWh", "Import Non-actual MSP kWh", "Import Total GSP kWh", "Import MD / kW", "Import MD Date", "Import MD / kVA", "Import Bad HHs", "Export LLFC", "Export MPAN Core", "Export Supply Capacity", "Export Supplier", "Export Total MSP kWh", "Export Non-actual MSP kWh", "Export GSP kWh", "Export MD / kW", "Export MD Date", "Export MD / kVA", "Export Bad HHs")) eras = sess.query(Era).filter( or_(Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date).order_by( Era.supply_id, Era.start_date).options( joinedload(Era.supply), joinedload(Era.supply).joinedload(Supply.source), joinedload(Era.supply).joinedload(Supply.generator_type), joinedload(Era.imp_llfc).joinedload(Llfc.voltage_level), joinedload(Era.exp_llfc).joinedload(Llfc.voltage_level), joinedload(Era.imp_llfc), joinedload(Era.exp_llfc), joinedload(Era.mop_contract), joinedload(Era.dc_contract), joinedload(Era.channels), joinedload(Era.site_eras).joinedload(SiteEra.site), joinedload(Era.pc), joinedload(Era.cop), joinedload(Era.mtc).joinedload(Mtc.meter_type), joinedload(Era.imp_supplier_contract), joinedload(Era.exp_supplier_contract), joinedload(Era.ssc), joinedload(Era.site_eras)) if supply_id is not None: eras = eras.filter(Era.supply == Supply.get_by_id(sess, supply_id)) for era in eras: supply = era.supply site_codes = set() site = None for site_era in era.site_eras: if site_era.is_physical: site = site_era.site else: site_codes.add(site_era.site.code) sup_eras = sess.query(Era).filter( Era.supply == supply).order_by(Era.start_date).all() supply_start = sup_eras[0].start_date supply_finish = sup_eras[-1].finish_date if supply.generator_type is None: generator_type = '' else: generator_type = supply.generator_type.code ssc = era.ssc ssc_code = '' if ssc is None else ssc.code prime_reads = set() for read, rdate in chain( sess.query( RegisterRead, RegisterRead.previous_date).join( RegisterRead.previous_type).join(Bill).join( BillType).filter( Bill.supply == supply, BillType.code != 'W', RegisterRead.previous_date >= start_date, RegisterRead.previous_date <= finish_date, ReadType.code.in_(NORMAL_READ_TYPES)).options( joinedload(RegisterRead.bill)), sess.query( RegisterRead, RegisterRead.present_date).join( RegisterRead.present_type).join(Bill).join( BillType).filter( Bill.supply == supply, BillType.code != 'W', RegisterRead.present_date >= start_date, RegisterRead.present_date <= finish_date, ReadType.code.in_(NORMAL_READ_TYPES)).options( joinedload(RegisterRead.bill))): prime_bill = sess.query(Bill).join(BillType).filter( Bill.supply == supply, Bill.start_date <= read.bill.finish_date, Bill.finish_date >= read.bill.start_date, Bill.reads.any()).order_by( Bill.issue_date.desc(), BillType.code).first() if prime_bill.id == read.bill.id: prime_reads.add(str(rdate) + "_" + read.msn) supply_type = era.meter_category chunk_start = hh_max(era.start_date, start_date) chunk_finish = hh_min(era.finish_date, finish_date) num_hh = int( (chunk_finish + HH - chunk_start).total_seconds() / (30 * 60)) w.writerow( [ hh_format(era.start_date), hh_format(era.finish_date, ongoing_str=''), supply.id, supply.name, supply.source.code, generator_type, site.code, site.name, '| '.join(sorted(site_codes)), hh_format(start_date), hh_format(finish_date), era.pc.code, era.mtc.code, era.cop.code, ssc_code, era.properties, era.mop_contract.name, era.mop_account, era.dc_contract.name, era.dc_account, len(prime_reads), supply_type, hh_format(supply_start), hh_format(supply_finish, ongoing_str='')] + mpan_bit( sess, supply, True, num_hh, era, chunk_start, chunk_finish, forecast_date, caches) + mpan_bit( sess, supply, False, num_hh, era, chunk_start, chunk_finish, forecast_date, caches)) # Avoid a long-running transaction sess.rollback() except BadRequest as e: if era is None: pref = "Problem: " else: pref = "Problem with era " + chellow.utils.url_root + "eras/" + \ str(era.id) + "/edit : " f.write(pref + e.description) except BaseException as e: if era is None: pref = "Problem: " else: pref = "Problem with era " + str(era.id) + ": " f.write(pref + str(e)) f.write(traceback.format_exc()) finally: sess.close() f.close() os.rename(running_name, finished_name)
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 content(start_date, finish_date, contract_id, user): caches = {} sess = f = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'virtual_bills.csv', user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') contract = Contract.get_supplier_by_id(sess, contract_id) forecast_date = chellow.computer.forecast_date() month_start = Datetime( start_date.year, start_date.month, 1, tzinfo=pytz.utc) month_finish = month_start + relativedelta(months=1) - HH 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') while not month_start > finish_date: period_start = hh_max(start_date, month_start) period_finish = hh_min(finish_date, month_finish) for era in sess.query(Era).distinct().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)): chunk_start = hh_max(period_start, era.start_date) chunk_finish = hh_min(period_finish, era.finish_date) polarities = [] if era.imp_supplier_contract == contract: polarities.append(True) if era.exp_supplier_contract == contract: polarities.append(False) for polarity in polarities: vals = [] data_source = SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, polarity, caches) site = sess.query(Site).join(SiteEra).filter( SiteEra.era == era, SiteEra.is_physical == true()).one() vals = [ data_source.mpan_core, site.code, site.name, data_source.supplier_account, hh_format(data_source.start_date), hh_format(data_source.finish_date)] vb_func(data_source) bill = data_source.supplier_bill for title in bill_titles: if title in bill: val = make_val(bill[title]) del bill[title] else: val = '' vals.append(val) for k in sorted(bill.keys()): vals.append(k) vals.append(str(bill[k])) writer.writerow(vals) month_start += relativedelta(months=1) month_finish = month_start + relativedelta(months=1) - HH except BadRequest as e: writer.writerow([e.description]) except: writer.writerow([traceback.format_exc()]) finally: if sess is not None: sess.close() f.close() os.rename(running_name, finished_name)
def content(year, supply_id, user): f = sess = None try: sess = Session() fname = ["crc", str(year), str(year + 1)] if supply_id is None: fname.append("all_supplies") else: fname.append("supply_" + str(supply_id)) running_name, finished_name = chellow.dloads.make_names( "_".join(fname) + ".csv", user) f = open(running_name, mode="w", newline="") w = csv.writer(f, lineterminator="\n") ACTUAL_READ_TYPES = ["N", "N3", "C", "X", "CP"] w.writerow(( "Chellow Supply Id", "Report Start", "Report Finish", "MPAN Core", "Site Id", "Site Name", "From", "To", "NHH Breakdown", "Actual HH Normal Days", "Actual AMR Normal Days", "Actual NHH Normal Days", "Actual Unmetered Normal Days", "Max HH Normal Days", "Max AMR Normal Days", "Max NHH Normal Days", "Max Unmetered Normal Days", "Total Actual Normal Days", "Total Max Normal Days", "Data Type", "HH kWh", "AMR kWh", "NHH kWh", "Unmetered kwh", "HH Filled kWh", "AMR Filled kWh", "Total kWh", "Note", )) year_start = Datetime(year, 4, 1, tzinfo=pytz.utc) year_finish = year_start + relativedelta(years=1) - HH supplies = (sess.query(Supply).join(Era).join(Source).filter( Source.code.in_(("net", "gen-net")), Era.imp_mpan_core != null(), Era.start_date <= year_finish, or_(Era.finish_date == null(), Era.finish_date >= year_start), ).distinct().order_by(Supply.id)) if supply_id is not None: supply = Supply.get_by_id(sess, supply_id) supplies = supplies.filter(Supply.id == supply.id) meter_types = ("hh", "amr", "nhh", "unmetered") for supply in supplies: total_kwh = dict([(mtype, 0) for mtype in meter_types]) filled_kwh = dict([(mtype, 0) for mtype in ("hh", "amr")]) normal_days = dict([(mtype, 0) for mtype in meter_types]) max_normal_days = dict([(mtype, 0) for mtype in meter_types]) breakdown = "" eras = (sess.query(Era).filter( Era.supply == supply, Era.start_date <= year_finish, or_(Era.finish_date == null(), Era.finish_date >= year_start), ).order_by(Era.start_date).all()) supply_from = hh_max(eras[0].start_date, year_start) supply_to = hh_min(eras[-1].finish_date, year_finish) for era in eras: meter_type = era.meter_category period_start = hh_max(era.start_date, year_start) period_finish = hh_min(era.finish_date, year_finish) max_normal_days[meter_type] += ( (period_finish - period_start).total_seconds() + 60 * 30) / (60 * 60 * 24) mpan_core = era.imp_mpan_core site = (sess.query(Site).join(SiteEra).filter( SiteEra.is_physical == true(), SiteEra.era_id == era.id).one()) if meter_type in ("nhh", "amr"): read_list = [] read_keys = {} pairs = [] prior_pres_reads = iter( sess.query(RegisterRead).join(Bill).join( BillType).join(RegisterRead.present_type).filter( RegisterRead.units == 0, ReadType.code.in_(ACTUAL_READ_TYPES), Bill.supply == supply, RegisterRead.present_date < period_start, BillType.code != "W", ).order_by(RegisterRead.present_date.desc())) prior_prev_reads = iter( sess.query(RegisterRead).join(Bill).join( BillType).join(RegisterRead.previous_type).filter( RegisterRead.units == 0, ReadType.code.in_(ACTUAL_READ_TYPES), Bill.supply == supply, RegisterRead.previous_date < period_start, BillType.code != "W", ).order_by(RegisterRead.previous_date.desc())) next_pres_reads = iter( sess.query(RegisterRead).join(Bill).join( BillType).join(RegisterRead.present_type).filter( RegisterRead.units == 0, ReadType.code.in_(ACTUAL_READ_TYPES), Bill.supply == supply, RegisterRead.present_date >= period_start, BillType.code != "W", ).order_by(RegisterRead.present_date)) next_prev_reads = iter( sess.query(RegisterRead).join(Bill).join( BillType).join(RegisterRead.previous_type).filter( RegisterRead.units == 0, ReadType.code.in_(ACTUAL_READ_TYPES), Bill.supply == supply, RegisterRead.previous_date >= period_start, BillType.code != "W", ).order_by(RegisterRead.previous_date)) for is_forwards in [False, True]: if is_forwards: pres_reads = next_pres_reads prev_reads = next_prev_reads read_list.reverse() else: pres_reads = prior_pres_reads prev_reads = prior_prev_reads prime_pres_read = None prime_prev_read = None while True: while prime_pres_read is None: try: pres_read = next(pres_reads) except StopIteration: break pres_date = pres_read.present_date pres_msn = pres_read.msn read_key = "_".join([str(pres_date), pres_msn]) if read_key in read_keys: continue pres_bill = ( sess.query(Bill).join(BillType).filter( Bill.reads.any(), Bill.supply == supply, Bill.finish_date >= pres_read.bill.start_date, Bill.start_date <= pres_read.bill.finish_date, BillType.code != "W", ).order_by(Bill.issue_date.desc(), BillType.code).first()) if pres_bill != pres_read.bill: continue reads = dict(( read.tpr.code, float(read.present_value) * float(read.coefficient), ) for read in sess.query(RegisterRead).filter( RegisterRead.units == 0, RegisterRead.bill == pres_bill, RegisterRead.present_date == pres_date, RegisterRead.msn == pres_msn, )) prime_pres_read = { "date": pres_date, "reads": reads, "msn": pres_msn, } read_keys[read_key] = None while prime_prev_read is None: try: prev_read = next(prev_reads) except StopIteration: break prev_date = prev_read.previous_date prev_msn = prev_read.msn read_key = "_".join([str(prev_date), prev_msn]) if read_key in read_keys: continue prev_bill = ( sess.query(Bill).join(BillType).filter( Bill.reads.any(), Bill.supply_id == supply.id, Bill.finish_date >= prev_read.bill.start_date, Bill.start_date <= prev_read.bill.finish_date, BillType.code != "W", ).order_by(Bill.issue_date.desc(), BillType.code).first()) if prev_bill != prev_read.bill: continue reads = dict(( read.tpr.code, float(read.previous_value) * float(read.coefficient), ) for read in sess.query(RegisterRead).filter( RegisterRead.units == 0, RegisterRead.bill_id == prev_bill.id, RegisterRead.previous_date == prev_date, RegisterRead.msn == prev_msn, )) prime_prev_read = { "date": prev_date, "reads": reads, "msn": prev_msn, } read_keys[read_key] = None if prime_pres_read is None and prime_prev_read is None: break elif prime_pres_read is None: read_list.append(prime_prev_read) prime_prev_read = None elif prime_prev_read is None: read_list.append(prime_pres_read) prime_pres_read = None else: if is_forwards: if (prime_pres_read["date"] <= prime_prev_read["date"]): read_list.append(prime_pres_read) prime_pres_read = None else: read_list.append(prime_prev_read) prime_prev_read = None else: if (prime_prev_read["date"] >= prime_pres_read["date"]): read_list.append(prime_prev_read) prime_prev_read = None else: read_list.append(prime_pres_read) prime_pres_read = None if len(read_list) > 1: if is_forwards: aft_read = read_list[-2] fore_read = read_list[-1] else: aft_read = read_list[-1] fore_read = read_list[-2] if aft_read["msn"] == fore_read["msn"] and set( aft_read["reads"].keys()) == set( fore_read["reads"].keys()): pair_start_date = aft_read["date"] + HH pair_finish_date = fore_read["date"] num_hh = ((pair_finish_date + HH - pair_start_date ).total_seconds()) / (30 * 60) tprs = {} for tpr_code, initial_val in aft_read[ "reads"].items(): end_val = fore_read["reads"][tpr_code] kwh = end_val - initial_val if kwh < 0: digits = int( math.log10(initial_val)) + 1 kwh = 10**digits + kwh tprs[tpr_code] = kwh / num_hh pairs.append({ "start-date": pair_start_date, "finish-date": pair_finish_date, "tprs": tprs, }) if len(pairs) > 0 and ( not is_forwards or (is_forwards and read_list[-1]["date"] > period_finish)): break breakdown += "read list - \n" + dumps(read_list) + "\n" if len(pairs) == 0: pairs.append({ "start-date": period_start, "finish-date": period_finish, "tprs": { "00001": 0 }, }) else: for pair in pairs: pair_start = pair["start-date"] pair_finish = pair["finish-date"] if pair_start >= year_start and pair_finish <= year_finish: block_start = hh_max(pair_start, period_start) block_finish = hh_min(pair_finish, period_finish) if block_start <= block_finish: normal_days[meter_type] += ( (block_finish - block_start).total_seconds() + 60 * 30) / (60 * 60 * 24) # smooth for i in range(1, len(pairs)): pairs[i - 1]["finish-date"] = pairs[i]["start-date"] - HH # stretch if pairs[0]["start-date"] > period_start: pairs[0]["start-date"] = period_start if pairs[-1]["finish-date"] < period_finish: pairs[-1]["finish-date"] = period_finish # chop pairs = [ pair for pair in pairs if not pair["start-date"] > period_finish and not pair["finish-date"] < period_start ] # squash if pairs[0]["start-date"] < period_start: pairs[0]["start-date"] = period_start if pairs[-1]["finish-date"] > period_finish: pairs[-1]["finish-date"] = period_finish for pair in pairs: pair_hhs = ((pair["finish-date"] - pair["start-date"] ).total_seconds() + 30 * 60) / (60 * 30) pair["pair_hhs"] = pair_hhs for tpr_code, pair_kwh in pair["tprs"].items(): total_kwh[meter_type] += pair_kwh * pair_hhs breakdown += "pairs - \n" + dumps(pairs) elif meter_type == "hh": period_kwhs = list( float(v[0]) for v in sess.query(HhDatum.value).join( Channel).filter( Channel.imp_related == true(), Channel.channel_type == "ACTIVE", Channel.era == era, HhDatum.start_date >= period_start, HhDatum.start_date <= period_finish, ).order_by(HhDatum.id)) year_kwhs = list( float(v[0]) for v in sess.query(HhDatum.value).join( Channel).join(Era).filter( Channel.imp_related == true(), Channel.channel_type == "ACTIVE", Era.supply == supply, HhDatum.start_date >= year_start, HhDatum.start_date <= year_finish, ).order_by(HhDatum.id)) period_sum_kwhs = sum(period_kwhs) year_sum_kwhs = sum(year_kwhs) period_len_kwhs = len(period_kwhs) year_len_kwhs = len(year_kwhs) total_kwh[meter_type] += period_sum_kwhs period_hhs = (period_finish + HH - period_start).total_seconds() / (60 * 30) if year_len_kwhs > 0: filled_kwh[meter_type] += ( year_sum_kwhs / year_len_kwhs * (period_hhs - period_len_kwhs)) normal_days[meter_type] += (sess.query( func.count(HhDatum.value)).join(Channel).filter( Channel.imp_related == true(), Channel.channel_type == "ACTIVE", Channel.era == era, HhDatum.start_date >= period_start, HhDatum.start_date <= period_finish, HhDatum.status == "A", ).one()[0] / 48) elif meter_type == "unmetered": year_seconds = (year_finish - year_start).total_seconds() + 60 * 30 period_seconds = (period_finish - period_start).total_seconds() + 60 * 30 total_kwh[ meter_type] += era.imp_sc * period_seconds / year_seconds normal_days[meter_type] += period_seconds / (60 * 60 * 24) # for full year 183 total_normal_days = sum(normal_days.values()) total_max_normal_days = sum(max_normal_days.values()) is_normal = total_normal_days / total_max_normal_days >= 183 / 365 w.writerow( [ supply.id, hh_format(year_start), hh_format(year_finish), mpan_core, site.code, site.name, hh_format(supply_from), hh_format(supply_to), breakdown, ] + [normal_days[t] for t in meter_types] + [max_normal_days[t] for t in meter_types] + [ total_normal_days, total_max_normal_days, "Actual" if is_normal else "Estimated", ] + [total_kwh[t] for t in meter_types] + [filled_kwh[t] for t in ("hh", "amr")] + [sum(total_kwh.values()) + sum(filled_kwh.values()), ""]) # avoid a long running transaction sess.rollback() except BaseException: msg = traceback.format_exc() sys.stderr.write(msg + "\n") f.write("Problem " + msg) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def _process_era( sess, caches, vb_func, forecast_date, bill_titles, contract, period_start, period_finish, era, ): chunk_start = hh_max(period_start, era.start_date) chunk_finish = hh_min(period_finish, era.finish_date) polarities = [] if era.imp_supplier_contract == contract: polarities.append(True) if era.exp_supplier_contract == contract: polarities.append(False) for polarity in polarities: vals = [] data_source = SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, polarity, caches, ) site = ( sess.query(Site) .join(SiteEra) .filter(SiteEra.era == era, SiteEra.is_physical == true()) .one() ) vals = [ data_source.mpan_core, site.code, site.name, data_source.supplier_account, data_source.start_date, data_source.finish_date, ] vb_func(data_source) bill = data_source.supplier_bill for title in bill_titles: if title in bill: val = bill[title] del bill[title] else: val = "" vals.append(val) for k in sorted(bill.keys()): vals.append(k) vals.append(str(bill[k])) return vals
def __init__( self, sess, start_date, finish_date, forecast_date, g_era, caches, g_bill): self.sess = sess self.caches = caches self.forecast_date = forecast_date self.start_date = start_date self.finish_date = finish_date times = get_times( sess, caches, start_date, finish_date, forecast_date) self.years_back = times['years-back'] self.history_start = times['history-start'] self.history_finish = times['history-finish'] self.problem = '' self.bill = defaultdict(int, {'problem': ''}) self.hh_data = [] self.rate_sets = defaultdict(set) self.g_bill = g_bill if self.g_bill is not None: self.g_bill_start = g_bill.start_date self.g_bill_finish = g_bill.finish_date self.is_last_g_bill_gen = \ not self.g_bill_finish < self.start_date and not \ self.g_bill_finish > self.finish_date self.g_era = g_era self.g_supply = g_era.g_supply self.mprn = self.g_supply.mprn self.account = g_era.account self.g_contract = g_era.g_contract self.consumption_info = '' if self.years_back == 0: hist_g_eras = [self.g_era] else: hist_g_eras = sess.query(GEra).filter( GEra.g_supply == self.g_supply, GEra.start_date <= self.history_finish, or_( GEra.finish_date == null(), GEra.finish_date >= self.history_start)).order_by( GEra.start_date).all() if len(hist_g_eras) == 0: hist_g_eras = sess.query(GEra).filter( GEra.g_supply == self.g_supply).order_by( GEra.start_date).limit(1).all() dte = start_date hist_map = {} for i, hist_g_era in enumerate(hist_g_eras): if self.history_start > hist_g_era.start_date: chunk_start = self.history_start else: if i == 0: chunk_start = self.history_start else: chunk_start = hist_g_era.start_date chunk_finish = hh_min(hist_g_era.finish_date, self.history_finish) if self.g_bill is None: read_list = [] read_keys = {} pairs = [] prior_pres_g_reads = iter( sess.query(GRegisterRead).join(GBill).join(BillType) .join(GRegisterRead.pres_type).filter( GReadType.code.in_(ACTUAL_READ_TYPES), GBill.g_supply == self.g_supply, GRegisterRead.present_date < chunk_start, BillType.code != 'W').order_by( GRegisterRead.pres_date.desc())) prior_prev_g_reads = iter( sess.query(GRegisterRead).join(GBill).join(BillType) .join(GRegisterRead.prev_type).filter( GReadType.code.in_(ACTUAL_READ_TYPES), GBill.g_supply == self.g_supply, GRegisterRead.prev_date < chunk_start, BillType.code != 'W').order_by( GRegisterRead.prev_date.desc())) next_pres_g_reads = iter( sess.query(GRegisterRead).join(GBill).join(BillType) .join(GRegisterRead.pres_type).filter( GReadType.code.in_(ACTUAL_READ_TYPES), GBill.g_supply == self.g_supply, GRegisterRead.pres_date >= chunk_start, BillType.code != 'W').order_by( GRegisterRead.pres_date)) next_prev_g_reads = iter( sess.query(GRegisterRead).join(GBill).join(BillType) .join(GRegisterRead.prev_type).filter( GReadType.code.in_(ACTUAL_READ_TYPES), GBill.g_supply == self.g_supply, GRegisterRead.prev_date >= chunk_start, BillType.code != 'W').order_by( GRegisterRead.prev_date)) for is_forwards in [False, True]: if is_forwards: pres_g_reads = next_pres_g_reads prev_g_reads = next_prev_g_reads read_list.reverse() else: pres_g_reads = prior_pres_g_reads prev_g_reads = prior_prev_g_reads prime_pres_g_read = None prime_prev_g_read = None while True: while prime_pres_g_read is None: try: pres_g_read = pres_g_reads.next() except StopIteration: break pres_date = pres_g_read.present_date pres_msn = pres_g_read.msn read_key = '_'.join([str(pres_date), pres_msn]) if read_key in read_keys: continue pres_g_bill = sess.query(GBill) \ .join(GRegisterRead) \ .join(BillType).filter( GBill.g_supply == self.g_supply, GBill.finish_date >= pres_g_read.pres_date, GBill.start_date <= pres_g_read.pres_date, BillType.code != 'W').order_by( GBill.issue_date.desc(), BillType.code).first() if pres_g_bill != pres_g_read.g_bill: continue reads = sess.query(GRegisterRead.pres_value). \ filter( GRegisterRead.g_bill == pres_g_bill, GRegisterRead.pres_date == pres_date, GRegisterRead.msn == pres_msn).all() prime_pres_g_read = { 'date': pres_date, 'reads': reads, 'msn': pres_msn} read_keys[read_key] = None while prime_prev_g_read is None: try: prev_g_read = prev_g_reads.next() except StopIteration: break prev_date = prev_g_read.prev_date prev_msn = prev_g_read.msn read_key = '_'.join([str(prev_date), prev_msn]) if read_key in read_keys: continue prev_g_bill = sess.query(GBill).join(BillType). \ filter( GBill.g_supply == self.g_supply, GBill.finish_date >= prev_g_read.g_bill.start_date, GBill.start_date <= prev_g_read.g_bill.start_date, BillType.code != 'W').order_by( GBill.issue_date.desc(), BillType.code).first() if prev_g_bill != prev_g_read.g_bill: continue reads = sess.query(GRegisterRead.prev_val).filter( GRegisterRead.g_bill == prev_g_bill, GRegisterRead.prev_date == prev_date, GRegisterRead.msn == prev_msn).all() prime_prev_read = { 'date': prev_date, 'reads': reads, 'msn': prev_msn} read_keys[read_key] = None if prime_pres_g_read is None and \ prime_prev_g_read is None: break elif prime_pres_g_read is None: read_list.append(prime_prev_g_read) prime_prev_g_read = None elif prime_prev_g_read is None: read_list.append(prime_pres_g_read) prime_pres_g_read = None else: if is_forwards: if prime_prev_g_read['date'] == \ prime_pres_g_read['date'] or \ prime_pres_g_read['date'] < \ prime_prev_g_read['date']: read_list.append(prime_pres_g_read) prime_pres_g_read = None else: read_list.append(prime_prev_read) prime_prev_read = None else: if prime_prev_read['date'] == \ prime_pres_g_read['date'] or \ prime_prev_g_read['date'] > \ prime_pres_g_read['date']: read_list.append(prime_prev_read) prime_prev_read = None else: read_list.append(prime_pres_g_read) prime_pres_g_read = None if len(read_list) > 1: if is_forwards: aft_read = read_list[-2] fore_read = read_list[-1] else: aft_read = read_list[-1] fore_read = read_list[-2] if aft_read['msn'] == fore_read['msn']: num_hh = ( fore_read['date'] - aft_read['date'] ).total_seconds() / (30 * 60) tprs = {} for tpr_code, initial_val in \ aft_read['reads'].items(): if tpr_code in fore_read['reads']: end_val = fore_read['reads'][tpr_code] else: continue kwh = end_val - initial_val if kwh < 0: digits = int( math.log10(initial_val)) + 1 kwh = 10 ** digits + kwh tprs[tpr_code] = kwh / num_hh pairs.append( { 'start-date': aft_read['date'], 'finish-date': fore_read['date'] + HH, 'tprs': tprs}) if len(pairs) > 0 and ( not is_forwards or ( is_forwards and read_list[-1]['date'] > chunk_finish)): break self.consumption_info += 'read list - \n' + str(read_list) \ + "\n" if len(pairs) == 0: pairs.append( { 'start-date': chunk_start, 'finish-date': chunk_finish, 'tprs': {'00001': 0}}) # smooth for i in range(1, len(pairs)): pairs[i - 1]['finish-date'] = pairs[i]['start-date'] - HH # stretch if pairs[0]['start-date'] > chunk_start: pairs[0]['start-date'] = chunk_start if pairs[-1]['finish-date'] < chunk_finish: pairs[-1]['finish-date'] = chunk_finish # chop pairs = [ pair for pair in pairs if not pair['start-date'] > chunk_finish and not pair['finish-date'] < chunk_start] # squash if pairs[0]['start-date'] < chunk_start: pairs[0]['start-date'] = chunk_start if pairs[-1]['finish-date'] > chunk_finish: pairs[-1]['finish-date'] = chunk_finish self.consumption_info += 'pairs - \n' + str(pairs) for pair in pairs: pair_hhs = ( pair['finish-date'] + HH - pair['start-date'] ).total_seconds() / (60 * 30) orig_dte = dte for tpr_code, pair_kwh in pair['tprs'].items(): hh_date = pair['start-date'] dte = orig_dte datum_generator = _datum_generator( sess, self.caches, self.years_back) hh_part = [] while not hh_date > pair['finish-date']: datum = datum_generator(sess, dte) if datum is not None: hh_part.append(datum.copy()) hh_date += HH dte += HH kwh = pair_kwh * pair_hhs / len(hh_part) \ if len(hh_part) > 0 else 0 for datum in hh_part: datum.update( { 'msp-kw': kwh * 2, 'msp-kwh': kwh, 'hist-kwh': kwh, 'imp-msp-kvar': 0, 'imp-msp-kvarh': 0, 'exp-msp-kvar': 0, 'exp-msp-kvarh': 0}) self.hh_data += hh_part else: g_bills = [] for cand_bill in sess.query(GBill).join(GBatch) \ .join(BillType).filter( GBill.g_supply == self.g_supply, GBill.g_reads.any(), GBatch.g_contract == self.g_contract, GBill.start_date <= chunk_finish, GBill.finish_date >= chunk_start, BillType.code != 'W').order_by( GBill.issue_date.desc(), GBill.start_date): can_insert = True for g_bill in g_bills: if not cand_bill.start_date > g_bill.finish_date \ and not cand_bill.finish_date < \ g_bill.start_date: can_insert = False break if can_insert: g_bills.append(cand_bill) prev_type_alias = aliased(GReadType) pres_type_alias = aliased(GReadType) for g_bill in g_bills: units_consumed = 0 for prev_date, prev_value, prev_type, pres_date, \ pres_value, pres_type, g_units_code, \ g_units_factor in sess.query( GRegisterRead.prev_date, cast(GRegisterRead.prev_value, Float), prev_type_alias.code, GRegisterRead.pres_date, cast(GRegisterRead.pres_value, Float), pres_type_alias.code, GUnits.code, cast(GUnits.factor, Float)).join( GUnits).join( prev_type_alias, GRegisterRead.prev_type_id == prev_type_alias.id).join( pres_type_alias, GRegisterRead.pres_type_id == pres_type_alias.id).filter( GRegisterRead.g_bill == g_bill) \ .order_by(GRegisterRead.pres_date): if prev_date < g_bill.start_date: self.problem += "There's a read before the " \ "start of the bill!" if pres_date > g_bill.finish_date: self.problem += "There's a read after the end " \ "of the bill!" advance = pres_value - prev_value if advance < 0: self.problem += "Clocked? " digits = int(math.log10(prev_value)) + 1 advance = 10 ** digits - prev_value + pres_value units_consumed += advance bill_s = ( g_bill.finish_date - g_bill.start_date + timedelta(minutes=30)).total_seconds() hh_units_consumed = units_consumed / (bill_s / (60 * 30)) for hh_date in hh_range( g_bill.start_date, g_bill.finish_date): hist_map[hh_date] = { 'units_code': g_units_code, 'units_factor': g_units_factor, 'units_consumed': hh_units_consumed} for d in datum_range( sess, self.caches, self.years_back, start_date, finish_date): h = d.copy() hist_start = h['hist_start'] h.update(hist_map.get(hist_start, {})) h['kwh'] = h['units_consumed'] * h['units_factor'] * \ h['correction_factor'] * h['cv'] self.hh_data.append(h)
def content(supply_id, file_name, start_date, finish_date, user): caches = {} sess = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'supply_virtual_bills_' + str(supply_id) + '.csv', user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') supply = Supply.get_by_id(sess, supply_id) forecast_date = chellow.computer.forecast_date() prev_titles = None for era in sess.query(Era).filter( Era.supply == supply, Era.start_date < finish_date, or_( Era.finish_date == null(), Era.finish_date > start_date)).order_by(Era.start_date): chunk_start = hh_max(era.start_date, start_date) chunk_finish = hh_min(era.finish_date, finish_date) site = sess.query(Site).join(SiteEra).filter( SiteEra.era == era, SiteEra.is_physical == true()).one() ds = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, True, caches) titles = [ 'Imp MPAN Core', 'Exp MPAN Core', 'Site Code', 'Site Name', 'Account', 'From', 'To', ''] output_line = [ era.imp_mpan_core, era.exp_mpan_core, site.code, site.name, ds.supplier_account, hh_format(ds.start_date), hh_format(ds.finish_date), ''] mop_titles = ds.contract_func( era.mop_contract, 'virtual_bill_titles')() titles.extend(['mop-' + t for t in mop_titles]) ds.contract_func(era.mop_contract, 'virtual_bill')(ds) bill = ds.mop_bill for title in mop_titles: if title in bill: output_line.append(bill[title]) del bill[title] else: output_line.append('') for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) output_line.append('') dc_titles = ds.contract_func( era.hhdc_contract, 'virtual_bill_titles')() titles.append('') titles.extend(['dc-' + t for t in dc_titles]) ds.contract_func(era.hhdc_contract, 'virtual_bill')(ds) bill = ds.dc_bill for title in dc_titles: output_line.append(bill.get(title, '')) if title in bill: del bill[title] for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) tpr_query = sess.query(Tpr).join(MeasurementRequirement). \ join(Ssc).join(Era).filter( Era.start_date <= chunk_finish, or_( Era.finish_date == null(), Era.finish_date >= chunk_start) ).order_by(Tpr.code).distinct() if era.imp_supplier_contract is not None: output_line.append('') supplier_titles = ds.contract_func( era.imp_supplier_contract, 'virtual_bill_titles')() for tpr in tpr_query.filter( Era.imp_supplier_contract != null()): for suffix in ('-kwh', '-rate', '-gbp'): supplier_titles.append(tpr.code + suffix) titles.append('') titles.extend(['imp-supplier-' + t for t in supplier_titles]) ds.contract_func(era.imp_supplier_contract, 'virtual_bill')(ds) bill = ds.supplier_bill for title in supplier_titles: if title in bill: output_line.append(bill[title]) del bill[title] else: output_line.append('') for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) if era.exp_supplier_contract is not None: ds = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, False, caches) output_line.append('') supplier_titles = ds.contract_func( era.exp_supplier_contract, 'virtual_bill_titles')() for tpr in tpr_query.filter( Era.exp_supplier_contract != null()): for suffix in ('-kwh', '-rate', '-gbp'): supplier_titles.append(tpr.code + suffix) titles.append('') titles.extend(['exp-supplier-' + t for t in supplier_titles]) ds.contract_func( era.exp_supplier_contract, 'virtual_bill')(ds) bill = ds.supplier_bill for title in supplier_titles: output_line.append(bill.get(title, '')) if title in bill: del bill[title] for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) if titles != prev_titles: prev_titles = titles writer.writerow([str(v) for v in titles]) for i, val in enumerate(output_line): output_line[i] = csv_make_val(val) writer.writerow(output_line) except BadRequest as e: writer.writerow(["Problem: " + e.description]) except: writer.writerow([traceback.format_exc()]) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
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 _bill_kwh( sess, caches, g_supply, hist_g_era, chunk_start, chunk_finish, g_cv_id, hist_map, g_ldz_code, ): cf = float(hist_g_era.correction_factor) g_unit = hist_g_era.g_unit unit_code, unit_factor = g_unit.code, float(g_unit.factor) for hh_date in hh_range(caches, chunk_start, chunk_finish): cv, avg_cv = find_cv(sess, caches, g_cv_id, hh_date, g_ldz_code) hist_map[hh_date] = { "unit_code": unit_code, "unit_factor": unit_factor, "correction_factor": cf, "calorific_value": cv, "avg_cv": avg_cv, } g_bills = dict( (b.id, b) for b in sess.query(GBill) .filter( GBill.g_supply == g_supply, GBill.start_date <= chunk_finish, GBill.finish_date >= chunk_start, ) .order_by(GBill.issue_date.desc(), GBill.start_date) ) while True: to_del = None for a, b in combinations(g_bills.values(), 2): if all( ( a.start_date == b.start_date, a.finish_date == b.finish_date, a.kwh == -1 * b.kwh, a.net == -1 * b.net, a.vat == -1 * b.vat, a.gross == -1 * b.gross, ) ): to_del = (a.id, b.id) break if to_del is None: break else: for k in to_del: del g_bills[k] for _, g_bill in sorted(g_bills.items()): units_consumed = 0 for prev_value, pres_value in sess.query( cast(GRegisterRead.prev_value, Float), cast(GRegisterRead.pres_value, Float) ).filter(GRegisterRead.g_bill == g_bill): units_diff = pres_value - prev_value if units_diff < 0: total_units = 10 ** len(str(int(prev_value))) c_units = total_units - prev_value + pres_value if c_units < abs(units_diff): units_diff = c_units units_consumed += units_diff bill_s = ( g_bill.finish_date - g_bill.start_date + timedelta(minutes=30) ).total_seconds() hh_units_consumed = units_consumed / (bill_s / (60 * 30)) block_start = hh_max(g_bill.start_date, chunk_start) block_finish = hh_min(g_bill.finish_date, chunk_finish) for hh_date in hh_range(caches, block_start, block_finish): hist_map[hh_date]["units_consumed"] = hh_units_consumed
def __init__( self, sess, start_date, finish_date, forecast_date, g_era, caches, g_bill ): self.sess = sess self.caches = caches self.forecast_date = forecast_date self.start_date = start_date self.finish_date = finish_date self.bill_hhs = {} times = get_times(sess, caches, start_date, finish_date, forecast_date) self.years_back = times["years-back"] self.history_start = times["history-start"] self.history_finish = times["history-finish"] self.problem = "" self.bill = defaultdict(int, {"problem": ""}) self.hh_data = [] self.rate_sets = defaultdict(set) self.g_bill = g_bill if self.g_bill is not None: self.g_bill_start = g_bill.start_date self.g_bill_finish = g_bill.finish_date self.is_last_g_bill_gen = ( not self.g_bill_finish < self.start_date and not self.g_bill_finish > self.finish_date ) self.g_era = g_era self.g_supply = g_era.g_supply self.mprn = self.g_supply.mprn self.g_exit_zone_code = self.g_supply.g_exit_zone.code self.g_ldz_code = self.g_supply.g_exit_zone.g_ldz.code self.g_dn_code = self.g_supply.g_exit_zone.g_ldz.g_dn.code self.account = g_era.account self.g_reading_frequency = g_era.g_reading_frequency self.g_reading_frequency_code = self.g_reading_frequency.code self.g_contract = g_era.g_contract self.consumption_info = "" if self.years_back == 0: hist_g_eras = [self.g_era] else: hist_g_eras = ( sess.query(GEra) .filter( GEra.g_supply == self.g_supply, GEra.start_date <= self.history_finish, or_( GEra.finish_date == null(), GEra.finish_date >= self.history_start, ), ) .order_by(GEra.start_date) .all() ) if len(hist_g_eras) == 0: hist_g_eras = ( sess.query(GEra) .filter(GEra.g_supply == self.g_supply) .order_by(GEra.start_date) .limit(1) .all() ) g_cv_id = get_non_core_contract_id("g_cv") hist_map = {} for i, hist_g_era in enumerate(hist_g_eras): if self.history_start > hist_g_era.start_date: chunk_start = self.history_start else: if i == 0: chunk_start = self.history_start else: chunk_start = hist_g_era.start_date chunk_finish = hh_min(hist_g_era.finish_date, self.history_finish) if self.g_bill is None: self.consumption_info += _no_bill_kwh( sess, caches, self.g_supply, chunk_start, chunk_finish, hist_g_era, g_cv_id, self.g_ldz_code, hist_map, forecast_date, ) else: _bill_kwh( sess, self.caches, self.g_supply, hist_g_era, chunk_start, chunk_finish, g_cv_id, hist_map, self.g_ldz_code, ) for d in datum_range( sess, self.caches, self.years_back, start_date, finish_date ): h = d.copy() hist_start = h["hist_start"] h.update(hist_map.get(hist_start, {})) h["kwh"] = ( h["units_consumed"] * h["unit_factor"] * h["correction_factor"] * h["calorific_value"] / 3.6 ) h["kwh_avg"] = ( h["units_consumed"] * h["unit_factor"] * h["correction_factor"] * h["avg_cv"] / 3.6 ) h["ug_rate"] = float( get_file_rates(self.caches, "g_ug", h["start_date"])["ug_gbp_per_kwh"][ self.g_exit_zone_code ] ) self.hh_data.append(h) self.bill_hhs[d["start_date"]] = {}
def content(year, supply_id, user): f = sess = None try: sess = Session() fname = ['crc', str(year), str(year + 1)] if supply_id is None: fname.append('all_supplies') else: fname.append('supply_' + str(supply_id)) running_name, finished_name = chellow.dloads.make_names( '_'.join(fname) + '.csv', user) f = open(running_name, "w") ACTUAL_READ_TYPES = ['N', 'N3', 'C', 'X', 'CP'] f.write( ','.join( ( 'Chellow Supply Id', 'MPAN Core', 'Site Id', 'Site Name', 'From', 'To', 'NHH Breakdown', 'Actual HH Normal Days', 'Actual AMR Normal Days', 'Actual NHH Normal Days', 'Actual Unmetered Normal Days', 'Max HH Normal Days', 'Max AMR Normal Days', 'Max NHH Normal Days', 'Max Unmetered Normal Days', 'Total Actual Normal Days', 'Total Max Normal Days', 'Data Type', 'HH kWh', 'AMR kWh', 'NHH kWh', 'Unmetered kwh', 'HH Filled kWh', 'AMR Filled kWh', 'Total kWh', 'Note')) + '\n') year_start = Datetime(year, 4, 1, tzinfo=pytz.utc) year_finish = year_start + relativedelta(years=1) - HH supplies = sess.query(Supply).join(Era).join(Source).filter( Source.code.in_(('net', 'gen-net')), Era.imp_mpan_core != null(), Era.start_date <= year_finish, or_( Era.finish_date == null(), Era.finish_date >= year_start)).distinct().order_by(Supply.id) if supply_id is not None: supply = Supply.get_by_id(sess, supply_id) supplies = supplies.filter(Supply.id == supply.id) meter_types = ('hh', 'amr', 'nhh', 'unmetered') for supply in supplies: total_kwh = dict([(mtype, 0) for mtype in meter_types]) filled_kwh = dict([(mtype, 0) for mtype in ('hh', 'amr')]) normal_days = dict([(mtype, 0) for mtype in meter_types]) max_normal_days = dict([(mtype, 0) for mtype in meter_types]) breakdown = '' for era in sess.query(Era).filter( Era.supply_id == supply.id, Era.start_date <= year_finish, or_( Era.finish_date == null(), Era.finish_date >= year_start)): meter_type = era.make_meter_category() era_start = era.start_date period_start = era_start \ if era_start > year_start else year_start era_finish = era.finish_date if hh_after(era_finish, year_finish): period_finish = year_finish else: period_finish = era_finish max_normal_days[meter_type] += ( (period_finish - period_start).total_seconds() + 60 * 30) / (60 * 60 * 24) mpan_core = era.imp_mpan_core site = sess.query(Site).join(SiteEra).filter( SiteEra.is_physical == true(), SiteEra.era_id == era.id).one() if meter_type == 'nhh': read_list = [] read_keys = {} pairs = [] prior_pres_reads = iter( sess.query(RegisterRead).join(Bill).join(BillType) .join(RegisterRead.present_type).filter( RegisterRead.units == 0, ReadType.code.in_(ACTUAL_READ_TYPES), Bill.supply == supply, RegisterRead.present_date < period_start, BillType.code != 'W').order_by( RegisterRead.present_date.desc())) prior_prev_reads = iter( sess.query(RegisterRead).join(Bill).join(BillType) .join(RegisterRead.previous_type).filter( RegisterRead.units == 0, ReadType.code.in_(ACTUAL_READ_TYPES), Bill.supply == supply, RegisterRead.previous_date < period_start, BillType.code != 'W').order_by( RegisterRead.previous_date.desc())) next_pres_reads = iter( sess.query(RegisterRead).join(Bill).join(BillType) .join(RegisterRead.present_type).filter( RegisterRead.units == 0, ReadType.code.in_(ACTUAL_READ_TYPES), Bill.supply == supply, RegisterRead.present_date >= period_start, BillType.code != 'W').order_by( RegisterRead.present_date)) next_prev_reads = iter( sess.query(RegisterRead).join(Bill).join(BillType). join(RegisterRead.previous_type).filter( RegisterRead.units == 0, ReadType.code.in_(ACTUAL_READ_TYPES), Bill.supply == supply, RegisterRead.previous_date >= period_start, BillType.code != 'W').order_by( RegisterRead.previous_date)) for is_forwards in [False, True]: if is_forwards: pres_reads = next_pres_reads prev_reads = next_prev_reads read_list.reverse() else: pres_reads = prior_pres_reads prev_reads = prior_prev_reads prime_pres_read = None prime_prev_read = None while True: while prime_pres_read is None: try: pres_read = next(pres_reads) except StopIteration: break pres_date = pres_read.present_date pres_msn = pres_read.msn read_key = '_'.join([str(pres_date), pres_msn]) if read_key in read_keys: continue pres_bill = sess.query(Bill).join(BillType). \ filter( Bill.reads.any(), Bill.supply == supply, Bill.finish_date >= pres_read.bill.start_date, Bill.start_date <= pres_read.bill.finish_date, BillType.code != 'W').order_by( Bill.issue_date.desc(), BillType.code).first() if pres_bill != pres_read.bill: continue reads = dict( ( read.tpr.code, float(read.present_value) * float(read.coefficient)) for read in sess.query(RegisterRead). filter( RegisterRead.units == 0, RegisterRead.bill == pres_bill, RegisterRead.present_date == pres_date, RegisterRead.msn == pres_msn)) prime_pres_read = { 'date': pres_date, 'reads': reads, 'msn': pres_msn} read_keys[read_key] = None while prime_prev_read is None: try: prev_read = next(prev_reads) except StopIteration: break prev_date = prev_read.previous_date prev_msn = prev_read.msn read_key = '_'.join([str(prev_date), prev_msn]) if read_key in read_keys: continue prev_bill = sess.query(Bill).join(BillType). \ filter( Bill.reads.any(), Bill.supply_id == supply.id, Bill.finish_date >= prev_read.bill.start_date, Bill.start_date <= prev_read.bill.finish_date, BillType.code != 'W').order_by( Bill.issue_date.desc(), BillType.code).first() if prev_bill != prev_read.bill: continue reads = dict( ( read.tpr.code, float(read.previous_value) * float(read.coefficient)) for read in sess.query(RegisterRead). filter( RegisterRead.units == 0, RegisterRead.bill_id == prev_bill.id, RegisterRead.previous_date == prev_date, RegisterRead.msn == prev_msn)) prime_prev_read = { 'date': prev_date, 'reads': reads, 'msn': prev_msn} read_keys[read_key] = None if prime_pres_read is None and \ prime_prev_read is None: break elif prime_pres_read is None: read_list.append(prime_prev_read) prime_prev_read = None elif prime_prev_read is None: read_list.append(prime_pres_read) prime_pres_read = None else: if is_forwards: if prime_pres_read['date'] <= \ prime_prev_read['date']: read_list.append(prime_pres_read) prime_pres_read = None else: read_list.append(prime_prev_read) prime_prev_read = None else: if prime_prev_read['date'] >= \ prime_pres_read['date']: read_list.append(prime_prev_read) prime_prev_read = None else: read_list.append(prime_pres_read) prime_pres_read = None if len(read_list) > 1: if is_forwards: aft_read = read_list[-2] fore_read = read_list[-1] else: aft_read = read_list[-1] fore_read = read_list[-2] if aft_read['msn'] == fore_read['msn'] and \ set(aft_read['reads'].keys()) == \ set(fore_read['reads'].keys()): pair_start_date = aft_read['date'] + HH pair_finish_date = fore_read['date'] num_hh = ( ( pair_finish_date + HH - pair_start_date).total_seconds() ) / (30 * 60) tprs = {} for tpr_code, initial_val in \ aft_read['reads'].items(): end_val = fore_read['reads'][tpr_code] kwh = end_val - initial_val if kwh < 0: digits = int( math.log10(initial_val)) + 1 kwh = 10 ** digits + kwh tprs[tpr_code] = kwh / num_hh pairs.append( { 'start-date': pair_start_date, 'finish-date': pair_finish_date, 'tprs': tprs}) if len(pairs) > 0 and \ (not is_forwards or ( is_forwards and read_list[-1]['date'] > period_finish)): break breakdown += 'read list - \n' + str(read_list) + "\n" if len(pairs) == 0: pairs.append( { 'start-date': period_start, 'finish-date': period_finish, 'tprs': {'00001': 0}}) else: for pair in pairs: pair_start = pair['start-date'] pair_finish = pair['finish-date'] if pair_start >= year_start and \ pair_finish <= year_finish: block_start = hh_max(pair_start, period_start) block_finish = hh_min( pair_finish, period_finish) if block_start <= block_finish: normal_days[meter_type] += ( ( block_finish - block_start ).total_seconds() + 60 * 30) / (60 * 60 * 24) # smooth for i in range(1, len(pairs)): pairs[i - 1]['finish-date'] = pairs[i]['start-date'] \ - HH # stretch if pairs[0]['start-date'] > period_start: pairs[0]['start-date'] = period_start if pairs[-1]['finish-date'] < period_finish: pairs[-1]['finish-date'] = period_finish # chop pairs = [ pair for pair in pairs if not pair['start-date'] > period_finish and not pair['finish-date'] < period_start] # squash if pairs[0]['start-date'] < period_start: pairs[0]['start-date'] = period_start if pairs[-1]['finish-date'] > period_finish: pairs[-1]['finish-date'] = period_finish for pair in pairs: pair_hhs = ( ( pair['finish-date'] - pair['start-date'] ).total_seconds() + 30 * 60) / (60 * 30) pair['pair_hhs'] = pair_hhs for tpr_code, pair_kwh in pair['tprs'].items(): total_kwh[meter_type] += pair_kwh * pair_hhs breakdown += 'pairs - \n' + str(pairs) elif meter_type in ('hh', 'amr'): period_kwhs = list( float(v[0]) for v in sess.query(HhDatum.value). join(Channel).filter( Channel.imp_related == true(), Channel.channel_type == 'ACTIVE', Channel.era == era, HhDatum.start_date >= period_start, HhDatum.start_date <= period_finish).order_by( HhDatum.id)) year_kwhs = list( float(v[0]) for v in sess.query(HhDatum.value). join(Channel).join(Era).filter( Channel.imp_related == true(), Channel.channel_type == 'ACTIVE', Era.supply == supply, HhDatum.start_date >= year_start, HhDatum.start_date <= year_finish).order_by( HhDatum.id)) period_sum_kwhs = sum(period_kwhs) year_sum_kwhs = sum(year_kwhs) period_len_kwhs = len(period_kwhs) year_len_kwhs = len(year_kwhs) total_kwh[meter_type] += period_sum_kwhs period_hhs = ( period_finish + HH - period_start ).total_seconds() / (60 * 30) if year_len_kwhs > 0: filled_kwh[meter_type] += year_sum_kwhs / \ year_len_kwhs * (period_hhs - period_len_kwhs) normal_days[meter_type] += sess.query( func.count(HhDatum.value)).join(Channel). \ filter( Channel.imp_related == true(), Channel.channel_type == 'ACTIVE', Channel.era == era, HhDatum.start_date >= period_start, HhDatum.start_date <= period_finish, HhDatum.status == 'A').one()[0] / 48 elif meter_type == 'unmetered': bills = sess.query(Bill).filter( Bill.supply == supply, Bill.finish_date >= period_start, Bill.start_date <= period_finish) for bill in bills: total_kwh[meter_type] += kwh normal_days[meter_type] += ( ( period_finish - period_start).total_seconds() + 60 * 30) / (60 * 60 * 24) # for full year 183 total_normal_days = sum(normal_days.values()) total_max_normal_days = sum(max_normal_days.values()) is_normal = total_normal_days / total_max_normal_days >= 183 / 365 f.write( ','.join( '"' + str(val) + '"' for val in [ supply.id, mpan_core, site.code, site.name, hh_format(year_start), hh_format(year_finish), breakdown] + [ normal_days[type] for type in meter_types] + [ max_normal_days[type] for type in meter_types] + [ total_normal_days, total_max_normal_days, "Actual" if is_normal else "Estimated"] + [total_kwh[type] for type in meter_types] + [filled_kwh[type] for type in ('hh', 'amr')] + [sum(total_kwh.values()) + sum(filled_kwh.values()), '']) + '\n') # avoid a long running transaction sess.rollback() except: msg = traceback.format_exc() sys.stderr.write(msg + '\n') f.write("Problem " + msg) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def content(start_date, finish_date, contract_id, user): caches = {} sess = supply_source = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( "mop_virtual_bills.csv", user) f = open(running_name, mode="w", newline="") writer = csv.writer(f, lineterminator="\n") contract = Contract.get_mop_by_id(sess, contract_id) forecast_date = chellow.computer.forecast_date() header_titles = [ "Import MPAN Core", "Export MPAN Core", "Start Date", "Finish Date", ] bill_titles = chellow.computer.contract_func(caches, contract, "virtual_bill_titles")() writer.writerow(header_titles + bill_titles) vb_func = chellow.computer.contract_func(caches, contract, "virtual_bill") for era in (sess.query(Era).filter( or_(Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date, Era.mop_contract == contract, ).order_by(Era.imp_mpan_core, Era.exp_mpan_core, Era.start_date)): chunk_start = hh_max(era.start_date, start_date) chunk_finish = hh_min(era.finish_date, finish_date) import_mpan_core = era.imp_mpan_core if import_mpan_core is None: import_mpan_core_str = "" else: is_import = True import_mpan_core_str = import_mpan_core export_mpan_core = era.exp_mpan_core if export_mpan_core is None: export_mpan_core_str = "" else: is_import = False export_mpan_core_str = export_mpan_core out = [ import_mpan_core_str, export_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.mop_bill for title in bill_titles: if title in bill: out.append(make_val(bill[title])) del bill[title] else: out.append("") for k in sorted(bill.keys()): out.append(k) out.append(str(bill[k])) writer.writerow(out) 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) sys.stderr.write(msg) writer.writerow([msg]) 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(year, month, months, supply_id, user): tmp_file = sess = None try: sess = Session() supplies = (sess.query(Supply).join(Era).distinct().options( joinedload(Supply.generator_type))) if supply_id is None: base_name = ("supplies_monthly_duration_for_all_supplies_for_" + str(months) + "_to_" + str(year) + "_" + str(month) + ".csv") else: supply = Supply.get_by_id(sess, supply_id) supplies = supplies.filter(Supply.id == supply.id) base_name = ("supplies_monthly_duration_for_" + str(supply.id) + "_" + str(months) + "_to_" + str(year) + "_" + str(month) + ".csv") running_name, finished_name = chellow.dloads.make_names( base_name, user) tmp_file = open(running_name, "w") caches = {} start_date = Datetime( year, month, 1, tzinfo=pytz.utc) - relativedelta(months=months - 1) field_names = ( "supply-name", "source-code", "generator-type", "month", "pc-code", "msn", "site-code", "site-name", "metering-type", "import-mpan-core", "metered-import-kwh", "metered-import-net-gbp", "metered-import-estimated-kwh", "billed-import-kwh", "billed-import-net-gbp", "export-mpan-core", "metered-export-kwh", "metered-export-estimated-kwh", "billed-export-kwh", "billed-export-net-gbp", "problem", "timestamp", ) tmp_file.write("supply-id," + ",".join(field_names) + "\n") forecast_date = chellow.computer.forecast_date() for i in range(months): month_start = start_date + relativedelta(months=i) month_finish = month_start + relativedelta(months=1) - HH for supply in supplies.filter( Era.start_date <= month_finish, or_(Era.finish_date == null(), Era.finish_date >= month_start), ): generator_type = supply.generator_type if generator_type is None: generator_type = "" else: generator_type = generator_type.code source_code = supply.source.code eras = supply.find_eras(sess, month_start, month_finish) era = eras[-1] metering_type = era.meter_category site = (sess.query(Site).join(SiteEra).filter( SiteEra.era == era, SiteEra.is_physical == true()).one()) values = { "supply-name": supply.name, "source-code": source_code, "generator-type": generator_type, "month": hh_format(month_finish), "pc-code": era.pc.code, "msn": era.msn, "site-code": site.code, "site-name": site.name, "metering-type": metering_type, "problem": "", } tmp_file.write(str(supply.id) + ",") for is_import, pol_name in [(True, "import"), (False, "export")]: if is_import: mpan_core = era.imp_mpan_core else: mpan_core = era.exp_mpan_core values[pol_name + "-mpan-core"] = mpan_core kwh = 0 est_kwh = 0 if metering_type in ["hh", "amr"]: est_kwh = (sess.query( HhDatum.value).join(Channel).join(Era).filter( HhDatum.status == "E", Era.supply_id == supply.id, Channel.channel_type == "ACTIVE", Channel.imp_related == is_import, HhDatum.start_date >= month_start, HhDatum.start_date <= month_finish, ).first()) if est_kwh is None: est_kwh = 0 else: est_kwh = est_kwh[0] if not (is_import and source_code in ("net", "gen-net")): kwh_sum = (sess.query( cast(func.sum(HhDatum.value), Float)).join(Channel).join(Era).filter( Era.supply_id == supply.id, Channel.channel_type == "ACTIVE", Channel.imp_related == is_import, HhDatum.start_date >= month_start, HhDatum.start_date <= month_finish, ).one()[0]) if kwh_sum is not None: kwh += kwh_sum values["metered-" + pol_name + "-estimated-kwh"] = est_kwh values["metered-" + pol_name + "-kwh"] = kwh values["metered-" + pol_name + "-net-gbp"] = 0 values["billed-" + pol_name + "-kwh"] = 0 values["billed-" + pol_name + "-net-gbp"] = 0 values["billed-" + pol_name + "-apportioned-kwh"] = 0 values["billed-" + pol_name + "-apportioned-net-gbp"] = 0 values["billed-" + pol_name + "-raw-kwh"] = 0 values["billed-" + pol_name + "-raw-net-gbp"] = 0 for bill in sess.query(Bill).filter( Bill.supply == supply, Bill.start_date <= month_finish, Bill.finish_date >= month_start, ): bill_start = bill.start_date bill_finish = bill.finish_date bill_duration = (bill_finish - bill_start).total_seconds() + 30 * 60 overlap_duration = (min(bill_finish, month_finish) - max( bill_start, month_start)).total_seconds() + 30 * 60 overlap_proportion = float(overlap_duration) / float( bill_duration) values[ "billed-import-net-gbp"] += overlap_proportion * float( bill.net) values["billed-import-kwh"] += overlap_proportion * float( bill.kwh) for era in eras: chunk_start = hh_max(era.start_date, month_start) chunk_finish = hh_min(era.finish_date, month_finish) import_mpan_core = era.imp_mpan_core if import_mpan_core is None: continue supplier_contract = era.imp_supplier_contract if source_code in ["net", "gen-net", "3rd-party"]: supply_source = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, True, caches, ) values["metered-import-kwh"] += sum( datum["msp-kwh"] for datum in supply_source.hh_data) import_vb_function = supply_source.contract_func( supplier_contract, "virtual_bill") if import_vb_function is None: values["problem"] += ( "Can't find the " "virtual_bill function in the supplier " "contract. ") else: import_vb_function(supply_source) values[ "metered-import-net-gbp"] += supply_source.supplier_bill[ "net-gbp"] supply_source.contract_func( era.dc_contract, "virtual_bill")(supply_source) values[ "metered-import-net-gbp"] += supply_source.dc_bill[ "net-gbp"] mop_func = supply_source.contract_func( era.mop_contract, "virtual_bill") if mop_func is None: values["problem"] += (" MOP virtual_bill " "function can't be found.") else: mop_func(supply_source) mop_bill = supply_source.mop_bill values["metered-import-net-gbp"] += mop_bill[ "net-gbp"] if len(mop_bill["problem"]) > 0: values["problem"] += ( " MOP virtual bill problem: " + mop_bill["problem"]) values["timestamp"] = int(time.time() * 1000) tmp_file.write(",".join('"' + str(values[name]) + '"' for name in field_names) + "\n") except BaseException: tmp_file.write(traceback.format_exc()) finally: if sess is not None: sess.close() tmp_file.close() os.rename(running_name, finished_name)
def https_handler(sess, log_f, properties, contract, now=None): url_template_str = properties["url_template"] url_values = properties.get("url_values", {}) download_days = properties["download_days"] if now is None: now = utc_datetime_now() window_finish = utc_datetime(now.year, now.month, now.day) - HH window_start = utc_datetime(now.year, now.month, now.day) - Timedelta(days=download_days) log_f(f"Window start: {hh_format(window_start)}") log_f(f"Window finish: {hh_format(window_finish)}") env = jinja2.Environment(autoescape=True, undefined=jinja2.StrictUndefined) url_template = env.from_string(url_template_str) for era in (sess.query(Era).filter( Era.dc_contract == contract, Era.start_date <= window_finish, or_(Era.finish_date == null(), Era.finish_date >= window_start), ).distinct()): chunk_start = hh_max(era.start_date, window_start) chunk_finish = hh_min(era.finish_date, window_finish) for mpan_core in (era.imp_mpan_core, era.exp_mpan_core): if mpan_core is None: continue log_f(f"Looking at MPAN core {mpan_core}.") vals = {"chunk_start": chunk_start, "chunk_finish": chunk_finish} vals.update(url_values.get(mpan_core, {})) try: url = url_template.render(vals) except jinja2.exceptions.UndefinedError as e: raise BadRequest( f"Problem rendering the URL template: {url_template_str}. " f"The problem is: {e}. This can be fixed by editing the " f"properties of this contract.") log_f(f"Retrieving data from {url}.") sess.rollback() # Avoid long transactions res = requests.get(url, timeout=120) res.raise_for_status() result = requests.get(url, timeout=120).json() if isinstance(result, dict): result_data = result["DataPoints"] elif isinstance(result, list): result_data = result else: raise BadRequest( f"Expecting a JSON object at the top level, but instead got " f"{result}") raw_data = [] for jdatum in result_data: raw_data.append( dict( mpan_core=mpan_core, start_date=utc_datetime(1, 1, 1) + Timedelta(seconds=jdatum["Time"] / 10000000), channel_type="ACTIVE", value=jdatum["Value"], status="A", )) HhDatum.insert(sess, raw_data, contract) sess.commit() log_f("Finished loading.") return False
def content(running_name, finished_name, date, supply_id, mpan_cores): sess = None try: sess = Session() f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') writer.writerow( ( 'Date', 'Physical Site Id', 'Physical Site Name', 'Other Site Ids', 'Other Site Names', 'Supply Id', 'Source', 'Generator Type', 'GSP Group', 'DNO Name', 'Voltage Level', 'Metering Type', 'Mandatory HH', 'PC', 'MTC', 'CoP', 'SSC', 'Number Of Registers', 'MOP Contract', 'Mop Account', 'HHDC Contract', 'HHDC Account', 'Meter Serial Number', 'Meter Installation Date', 'Latest Normal Meter Read Date', 'Latest Normal Meter Read Type', 'Latest DC Bill Date', 'Latest MOP Bill Date', 'Import ACTIVE?', 'Import REACTIVE_IMPORT?', 'Import REACTIVE_EXPORT?', 'Export ACTIVE?', 'Export REACTIVE_IMPORT?', 'Export REACTIVE_EXPORT?', 'Import MPAN core', 'Import Agreed Supply Capacity (kVA)', 'Import LLFC Code', 'Import LLFC Description', 'Import Supplier Contract', 'Import Supplier Account', 'Import Mandatory kW', 'Latest Import Supplier Bill Date', 'Export MPAN core', 'Export Agreed Supply Capacity (kVA)', 'Export LLFC Code', 'Export LLFC Description', 'Export Supplier Contract', 'Export Supplier Account', 'Export Mandatory kW', 'Latest Export Supplier Bill Date')) NORMAL_READ_TYPES = ('N', 'C', 'N3') year_start = date + HH - relativedelta(years=1) eras = sess.query(Era, Supply, GeneratorType).join(Supply).outerjoin( GeneratorType).filter( Era.start_date <= date, or_(Era.finish_date == null(), Era.finish_date >= date)).order_by( Era.supply_id).options(joinedload(Era.site_eras)) if supply_id is not None: supply = Supply.get_by_id(sess, supply_id) eras = eras.filter(Era.supply == supply) if mpan_cores is not None: eras = eras.filter( or_( Era.imp_mpan_core.in_(mpan_cores), Era.exp_mpan_core.in_(mpan_cores))) for era, supply, generator_type in eras: site_codes = [] site_names = [] for site_era in era.site_eras: if site_era.is_physical: physical_site = site_era.site else: site = site_era.site site_codes.append(site.code) site_names.append(site.name) if era.imp_mpan_core is None: voltage_level_code = era.exp_llfc.voltage_level.code else: voltage_level_code = era.imp_llfc.voltage_level.code if generator_type is None: generator_type_str = '' else: generator_type_str = generator_type.code metering_type = era.make_meter_category() if metering_type == 'nhh': latest_prev_normal_read = sess.query(RegisterRead). \ join(Bill).join(RegisterRead.previous_type).filter( ReadType.code.in_(NORMAL_READ_TYPES), RegisterRead.previous_date <= date, Bill.supply_id == supply.id).order_by( RegisterRead.previous_date.desc()).first() latest_pres_normal_read = sess.query(RegisterRead) \ .join(Bill).join(RegisterRead.present_type).filter( ReadType.code.in_(NORMAL_READ_TYPES), RegisterRead.present_date <= date, Bill.supply == supply).order_by( RegisterRead.present_date.desc()).first() if latest_prev_normal_read is None and \ latest_pres_normal_read is None: latest_normal_read_date = None latest_normal_read_type = None elif latest_pres_normal_read is not None and \ latest_prev_normal_read is None: latest_normal_read_date = \ latest_pres_normal_read.present_date latest_normal_read_type = \ latest_pres_normal_read.present_type.code elif latest_pres_normal_read is None and \ latest_prev_normal_read is not None: latest_normal_read_date = \ latest_prev_normal_read.previous_date latest_normal_read_type = \ latest_prev_normal_read.previous_type.code elif latest_pres_normal_read.present_date > \ latest_prev_normal_read.previous_date: latest_normal_read_date = \ latest_pres_normal_read.present_date latest_normal_read_type = \ latest_pres_normal_read.present_type.code else: latest_normal_read_date = \ latest_prev_normal_read.previous_date latest_normal_read_type = \ latest_prev_normal_read.previous_type.code if latest_normal_read_date is not None: latest_normal_read_date = \ hh_format(latest_normal_read_date) else: latest_normal_read_date = metering_type latest_normal_read_type = None mop_contract = era.mop_contract if mop_contract is None: mop_contract_name = '' mop_account = '' latest_mop_bill_date = 'No MOP' else: mop_contract_name = mop_contract.name mop_account = era.mop_account latest_mop_bill_date = sess.query(Bill.finish_date) \ .join(Batch).filter( Bill.start_date <= date, Bill.supply == supply, Batch.contract == mop_contract).order_by( Bill.finish_date.desc()).first() if latest_mop_bill_date is not None: latest_mop_bill_date = hh_format(latest_mop_bill_date[0]) hhdc_contract = era.hhdc_contract if hhdc_contract is None: hhdc_contract_name = '' hhdc_account = '' latest_hhdc_bill_date = 'No HHDC' else: hhdc_contract_name = hhdc_contract.name hhdc_account = era.hhdc_account latest_hhdc_bill_date = sess.query(Bill.finish_date) \ .join(Batch).filter( Bill.start_date <= date, Bill.supply == supply, Batch.contract == hhdc_contract).order_by( Bill.finish_date.desc()).first() if latest_hhdc_bill_date is not None: latest_hhdc_bill_date = hh_format(latest_hhdc_bill_date[0]) channel_values = [] for imp_related in [True, False]: for channel_type in CHANNEL_TYPES: if era.find_channel( sess, imp_related, channel_type) is None: channel_values.append('false') else: channel_values.append('true') imp_avg_months = None exp_avg_months = None for is_import in [True, False]: if metering_type == 'nhh': continue params = { 'supply_id': supply.id, 'year_start': year_start, 'year_finish': date, 'is_import': is_import} month_mds = tuple( md[0] * 2 for md in sess.execute(""" select max(hh_datum.value) as md from hh_datum join channel on (hh_datum.channel_id = channel.id) join era on (channel.era_id = era.id) where era.supply_id = :supply_id and hh_datum.start_date >= :year_start and hh_datum.start_date <= :year_finish and channel.channel_type = 'ACTIVE' and channel.imp_related = :is_import group by extract(month from (hh_datum.start_date at time zone 'utc')) order by md desc limit 3 """, params=params)) avg_months = sum(month_mds) if len(month_mds) > 0: avg_months /= len(month_mds) if is_import: imp_avg_months = avg_months else: exp_avg_months = avg_months if (imp_avg_months is not None and imp_avg_months > 100) or \ (exp_avg_months is not None and exp_avg_months > 100): mandatory_hh = 'yes' else: mandatory_hh = 'no' imp_latest_supplier_bill_date = None exp_latest_supplier_bill_date = None for is_import in [True, False]: for er in sess.query(Era).filter( Era.supply == era.supply, Era.start_date <= date).order_by( Era.start_date.desc()): if is_import: if er.imp_mpan_core is None: break else: supplier_contract = er.imp_supplier_contract else: if er.exp_mpan_core is None: break else: supplier_contract = er.exp_supplier_contract latest_bill_date = sess.query(Bill.finish_date) \ .join(Batch).filter( Bill.finish_date >= er.start_date, Bill.finish_date <= hh_min(er.finish_date, date), Bill.supply == supply, Batch.contract == supplier_contract).order_by( Bill.finish_date.desc()).first() if latest_bill_date is not None: latest_bill_date = hh_format(latest_bill_date[0]) if is_import: imp_latest_supplier_bill_date = latest_bill_date else: exp_latest_supplier_bill_date = latest_bill_date break meter_installation_date = sess.query(func.min(Era.start_date)) \ .filter(Era.supply == era.supply, Era.msn == era.msn).one()[0] if era.ssc is None: ssc_code = num_registers = None else: ssc_code = era.ssc.code num_registers = sess.query(MeasurementRequirement).filter( MeasurementRequirement.ssc == era.ssc).count() writer.writerow( ( ('' if value is None else str(value))) for value in [ hh_format(date), physical_site.code, physical_site.name, ', '.join(site_codes), ', '.join(site_names), supply.id, supply.source.code, generator_type_str, supply.gsp_group.code, supply.dno_contract.name, voltage_level_code, metering_type, mandatory_hh, era.pc.code, era.mtc.code, era.cop.code, ssc_code, num_registers, mop_contract_name, mop_account, hhdc_contract_name, hhdc_account, era.msn, hh_format(meter_installation_date), latest_normal_read_date, latest_normal_read_type, latest_hhdc_bill_date, latest_mop_bill_date] + channel_values + [ era.imp_mpan_core, era.imp_sc, None if era.imp_llfc is None else era.imp_llfc.code, None if era.imp_llfc is None else era.imp_llfc.description, None if era.imp_supplier_contract is None else era.imp_supplier_contract.name, era.imp_supplier_account, imp_avg_months, imp_latest_supplier_bill_date] + [ era.exp_mpan_core, era.exp_sc, None if era.exp_llfc is None else era.exp_llfc.code, None if era.exp_llfc is None else era.exp_llfc.description, None if era.exp_supplier_contract is None else era.exp_supplier_contract.name, era.exp_supplier_account, exp_avg_months, exp_latest_supplier_bill_date]) except: 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(contract_id, end_year, end_month, months, user): caches = {} sess = f = None try: sess = Session() contract = Contract.get_hhdc_by_id(sess, contract_id) finish_date = Datetime(end_year, end_month, 1, tzinfo=pytz.utc) + \ relativedelta(months=1) - HH start_date = Datetime(end_year, end_month, 1, tzinfo=pytz.utc) - \ relativedelta(months=months - 1) forecast_date = chellow.computer.forecast_date() running_name, finished_name = chellow.dloads.make_names( 'hhdc_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.hhdc_contract == contract).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(str(bill.get(title, ''))) if title in bill: del bill[title] for k in sorted(bill.keys()): vals.append(k) vals.append(str(bill[k])) writer.writerow(vals) except BadRequest as e: f.write("Problem " + e.description + traceback.format_exc() + '\n') except: 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 content(batch_id, bill_id, contract_id, start_date, finish_date, user): caches = {} tmp_file = sess = bill = None forecast_date = to_utc(Datetime.max) sess = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'bill_check.csv', user) tmp_file = open(running_name, mode='w', newline='') writer = csv.writer(tmp_file, lineterminator='\n') bills = sess.query(Bill).order_by( Bill.supply_id, Bill.reference).options( joinedload(Bill.supply), subqueryload(Bill.reads).joinedload(RegisterRead.present_type), subqueryload(Bill.reads).joinedload(RegisterRead.previous_type), joinedload(Bill.batch)) if batch_id is not None: batch = Batch.get_by_id(sess, batch_id) bills = bills.filter(Bill.batch == batch) contract = batch.contract elif bill_id is not None: bill = Bill.get_by_id(sess, bill_id) bills = bills.filter(Bill.id == bill.id) contract = bill.batch.contract elif contract_id is not None: contract = Contract.get_by_id(sess, contract_id) bills = bills.join(Batch).filter( Batch.contract == contract, Bill.start_date <= finish_date, Bill.finish_date >= start_date) market_role_code = contract.market_role.code vbf = chellow.computer.contract_func(caches, contract, 'virtual_bill') if vbf is None: raise BadRequest( 'The contract ' + contract.name + " doesn't have a function virtual_bill.") virtual_bill_titles_func = chellow.computer.contract_func( caches, contract, 'virtual_bill_titles') if virtual_bill_titles_func is None: raise BadRequest( 'The contract ' + contract.name + " doesn't have a function virtual_bill_titles.") virtual_bill_titles = virtual_bill_titles_func() titles = [ 'batch', 'bill-reference', 'bill-type', 'bill-kwh', 'bill-net-gbp', 'bill-vat-gbp', 'bill-start-date', 'bill-finish-date', 'imp-mpan-core', 'exp-mpan-core', 'site-code', 'site-name', 'covered-from', 'covered-to', 'covered-bills', 'metered-kwh'] for t in virtual_bill_titles: titles.append('covered-' + t) titles.append('virtual-' + t) if t.endswith('-gbp'): titles.append('difference-' + t) writer.writerow(titles) bill_map = defaultdict(set, {}) for bill in bills: bill_map[bill.supply.id].add(bill.id) for supply_id, bill_ids in bill_map.items(): gaps = {} data_sources = {} while len(bill_ids) > 0: bill_id = list(sorted(bill_ids))[0] bill_ids.remove(bill_id) bill = sess.query(Bill).filter(Bill.id == bill_id).options( joinedload(Bill.batch), joinedload(Bill.bill_type), joinedload(Bill.reads), joinedload(Bill.supply), joinedload(Bill.reads).joinedload( RegisterRead.present_type), joinedload(Bill.reads).joinedload( RegisterRead.previous_type)).one() virtual_bill = {'problem': ''} supply = bill.supply read_dict = {} for read in bill.reads: gen_start = read.present_date.replace(hour=0).replace( minute=0) gen_finish = gen_start + relativedelta(days=1) - HH msn_match = False read_msn = read.msn for read_era in supply.find_eras( sess, gen_start, gen_finish): if read_msn == read_era.msn: msn_match = True break if not msn_match: virtual_bill['problem'] += "The MSN " + read_msn + \ " of the register read " + str(read.id) + \ " doesn't match the MSN of the era." for dt, typ in [ (read.present_date, read.present_type), (read.previous_date, read.previous_type)]: key = str(dt) + "-" + read.msn try: if typ != read_dict[key]: virtual_bill['problem'] += " Reads taken " + \ "on " + str(dt) + \ " have differing read types." except KeyError: read_dict[key] = typ bill_start = bill.start_date bill_finish = bill.finish_date covered_start = bill_start covered_finish = bill_finish covered_bdown = {'sum-msp-kwh': 0, 'net-gbp': 0, 'vat-gbp': 0} vb_elems = set() enlarged = True while enlarged: enlarged = False covered_elems = find_elements(bill) covered_bills = OrderedDict( (b.id, b) for b in sess.query(Bill).join(Batch). join(Contract).join(MarketRole).filter( Bill.supply == supply, Bill.start_date <= covered_finish, Bill.finish_date >= covered_start, MarketRole.code == market_role_code).order_by( Bill.start_date, Bill.issue_date)) while True: to_del = None for a, b in combinations(covered_bills.values(), 2): if all( ( a.start_date == b.start_date, a.finish_date == b.finish_date, a.kwh == -1 * b.kwh, a.net == -1 * b.net, a.vat == -1 * b.vat, a.gross == -1 * b.gross)): to_del = (a.id, b.id) break if to_del is None: break else: for k in to_del: del covered_bills[k] for k, covered_bill in tuple(covered_bills.items()): elems = find_elements(covered_bill) if elems.isdisjoint(covered_elems): if k != bill.id: del covered_bills[k] continue else: covered_elems.update(elems) if covered_bill.start_date < covered_start: covered_start = covered_bill.start_date enlarged = True break if covered_bill.finish_date > covered_finish: covered_finish = covered_bill.finish_date enlarged = True break if len(covered_bills) == 0: continue primary_covered_bill = None for covered_bill in covered_bills.values(): if covered_bill.id in bill_ids: bill_ids.remove(covered_bill.id) covered_bdown['net-gbp'] += float(covered_bill.net) covered_bdown['vat-gbp'] += float(covered_bill.vat) covered_bdown['sum-msp-kwh'] += float(covered_bill.kwh) covered_rates = defaultdict(set) for k, v in loads(covered_bill.breakdown).items(): if k in ('raw_lines', 'raw-lines'): continue if isinstance(v, list): covered_rates[k].update(set(v)) else: if isinstance(v, Decimal): v = float(v) try: covered_bdown[k] += v except KeyError: covered_bdown[k] = v except TypeError as detail: raise BadRequest( "For key " + str(k) + " in " + str( [ b.id for b in covered_bills.values() ]) + " the value " + str(v) + " can't be added to the existing value " + str(covered_bdown[k]) + ". " + str(detail)) if k.endswith('-gbp'): elem = k[:-4] covered_elems.add(elem) add_gap( caches, gaps, elem, covered_bill.start_date, covered_bill.finish_date, False, v) for k, v in covered_rates.items(): covered_bdown[k] = v.pop() if len(v) == 1 else None if primary_covered_bill is None or ( ( covered_bill.finish_date - covered_bill.start_date) > ( primary_covered_bill.finish_date - primary_covered_bill.start_date)): primary_covered_bill = covered_bill metered_kwh = 0 for era in sess.query(Era).filter( Era.supply == supply, Era.start_date <= covered_finish, or_( Era.finish_date == null(), Era.finish_date >= covered_start) ).distinct().options( joinedload(Era.channels), joinedload(Era.cop), joinedload(Era.dc_contract), joinedload(Era.exp_llfc), joinedload(Era.exp_llfc).joinedload( Llfc.voltage_level), joinedload(Era.exp_supplier_contract), joinedload(Era.imp_llfc), joinedload(Era.imp_llfc).joinedload( Llfc.voltage_level), joinedload(Era.imp_supplier_contract), joinedload(Era.mop_contract), joinedload(Era.mtc).joinedload(Mtc.meter_type), joinedload(Era.pc), joinedload(Era.supply).joinedload(Supply.dno), joinedload(Era.supply).joinedload(Supply.gsp_group), joinedload(Era.supply).joinedload(Supply.source)): chunk_start = hh_max(covered_start, era.start_date) chunk_finish = hh_min(covered_finish, era.finish_date) if contract not in ( era.mop_contract, era.dc_contract, era.imp_supplier_contract, era.exp_supplier_contract): virtual_bill['problem'] += ''.join( ( "From ", hh_format(chunk_start), " to ", hh_format(chunk_finish), " the contract of ", "the era doesn't match the contract of the ", "bill.")) continue if contract.market_role.code == 'X': polarity = contract != era.exp_supplier_contract else: polarity = era.imp_supplier_contract is not None ''' pairs = [] last_finish = chunk_start - HH for hd in chellow.computer.datum_range( sess, caches, 0, chunk_start, chunk_finish): if hd['utc-is-month-end'] or hd['ct-is-month-end']: end_date = hd['start-date'] pairs.append((last_finish + HH, end_date)) last_finish = end_date if hd['start-date'] > last_finish: pairs.append((last_finish + HH, hd['start-date'])) for ss_start, ss_finish in pairs: ''' try: ds_key = ( chunk_start, chunk_finish, forecast_date, era.id, polarity, primary_covered_bill.id) data_source = data_sources[ds_key] except KeyError: data_source = data_sources[ds_key] = \ chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, polarity, caches, primary_covered_bill) vbf(data_source) if data_source.measurement_type == 'hh': metered_kwh += sum( h['msp-kwh'] for h in data_source.hh_data) else: ds = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, polarity, caches) metered_kwh += sum( h['msp-kwh'] for h in ds.hh_data) if market_role_code == 'X': vb = data_source.supplier_bill elif market_role_code == 'C': vb = data_source.dc_bill elif market_role_code == 'M': vb = data_source.mop_bill else: raise BadRequest("Odd market role.") for k, v in vb.items(): try: if isinstance(v, set): virtual_bill[k].update(v) else: virtual_bill[k] += v except KeyError: virtual_bill[k] = v except TypeError as detail: raise BadRequest( "For key " + str(k) + " and value " + str(v) + ". " + str(detail)) if all((k.endswith('-gbp'), k != 'net-gbp', v != 0)): add_gap( caches, gaps, k[:-4], chunk_start, chunk_finish, True, v) for k in virtual_bill.keys(): if k.endswith('-gbp'): vb_elems.add(k[:-4]) long_map = {} vb_keys = set(virtual_bill.keys()) for elem in sorted(vb_elems, key=len, reverse=True): els = long_map[elem] = set() for k in tuple(vb_keys): if k.startswith(elem + '-'): els.add(k) vb_keys.remove(k) for elem in vb_elems.difference(covered_elems): for k in long_map[elem]: del virtual_bill[k] try: del virtual_bill['net-gbp'] except KeyError: pass virtual_bill['net-gbp'] = sum( v for k, v in virtual_bill.items() if k.endswith('-gbp')) era = supply.find_era_at(sess, bill_finish) if era is None: imp_mpan_core = exp_mpan_core = None site_code = site_name = None virtual_bill['problem'] += \ "This bill finishes before or after the supply. " else: imp_mpan_core = era.imp_mpan_core exp_mpan_core = era.exp_mpan_core site = sess.query(Site).join(SiteEra).filter( SiteEra.is_physical == true(), SiteEra.era == era).one() site_code = site.code site_name = site.name # Find bill to use for header data if bill.id not in covered_bills: for cbill in covered_bills.values(): if bill.batch == cbill.batch: bill = cbill values = [ bill.batch.reference, bill.reference, bill.bill_type.code, bill.kwh, bill.net, bill.vat, hh_format(bill_start), hh_format(bill_finish), imp_mpan_core, exp_mpan_core, site_code, site_name, hh_format(covered_start), hh_format(covered_finish), ':'.join( str(i).replace(',', '') for i in covered_bills.keys()), metered_kwh] for title in virtual_bill_titles: try: cov_val = covered_bdown[title] values.append(cov_val) del covered_bdown[title] except KeyError: cov_val = None values.append('') try: virt_val = csv_make_val(virtual_bill[title]) values.append(virt_val) del virtual_bill[title] except KeyError: virt_val = 0 values.append('') if title.endswith('-gbp'): if isinstance(virt_val, (int, float, Decimal)): if isinstance(cov_val, (int, float, Decimal)): values.append(float(cov_val) - float(virt_val)) else: values.append(0 - float(virt_val)) else: values.append('') for title in sorted(virtual_bill.keys()): virt_val = csv_make_val(virtual_bill[title]) values += ['virtual-' + title, virt_val] if title in covered_bdown: values += ['covered-' + title, covered_bdown[title]] else: values += ['', ''] writer.writerow(values) for bill in sess.query(Bill).filter( Bill.supply == supply, Bill.start_date <= covered_finish, Bill.finish_date >= covered_start): for k, v in loads(bill.breakdown).items(): if k.endswith('-gbp'): add_gap( caches, gaps, k[:-4], bill.start_date, bill.finish_date, False, v) # Avoid long-running transactions sess.rollback() clumps = [] for element, elgap in sorted(gaps.items()): for start_date, hhgap in sorted(elgap.items()): if hhgap['has_virtual'] and not hhgap['has_covered']: if len(clumps) == 0 or not all( ( clumps[-1]['element'] == element, clumps[-1]['finish_date'] + HH == start_date)): clumps.append( { 'element': element, 'start_date': start_date, 'finish_date': start_date, 'gbp': hhgap['gbp']}) else: clumps[-1]['finish_date'] = start_date for i, clump in enumerate(clumps): vals = dict((title, '') for title in titles) vals['covered-problem'] = '_'.join( ( 'missing', clump['element'], 'supplyid', str(supply.id), 'from', hh_format(clump['start_date']))) vals['imp-mpan-core'] = imp_mpan_core vals['exp-mpan-core'] = exp_mpan_core vals['batch'] = 'missing_bill' vals['bill-start-date'] = hh_format(clump['start_date']) vals['bill-finish-date'] = hh_format(clump['finish_date']) vals['difference-net-gbp'] = clump['gbp'] writer.writerow(vals[title] for title in titles) # Avoid long-running transactions sess.rollback() except BadRequest as e: if bill is None: prefix = "Problem: " else: prefix = "Problem with bill " + str(bill.id) + ':' tmp_file.write(prefix + e.description) except BaseException: msg = traceback.format_exc() sys.stderr.write(msg + '\n') tmp_file.write("Problem " + msg) finally: if sess is not None: sess.close() tmp_file.close() os.rename(running_name, finished_name)
def https_handler(self, sess, properties, contract): url_template_str = properties['url_template'] url_values = properties.get('url_values', {}) download_days = properties['download_days'] now = utc_datetime_now() window_finish = utc_datetime(now.year, now.month, now.day) - HH window_start = utc_datetime(now.year, now.month, now.day) - Timedelta(days=download_days) self.log("Window start: " + hh_format(window_start)) self.log("Window finish: " + hh_format(window_finish)) env = jinja2.Environment(autoescape=True, undefined=jinja2.StrictUndefined) url_template = env.from_string(url_template_str) for era in sess.query(Era).filter( Era.dc_contract == contract, Era.start_date <= window_finish, or_(Era.finish_date == null(), Era.finish_date >= window_start)).distinct(): chunk_start = hh_max(era.start_date, window_start) chunk_finish = hh_min(era.finish_date, window_finish) for mpan_core in (era.imp_mpan_core, era.exp_mpan_core): if mpan_core is None: continue self.log("Looking at MPAN core {mpan_core}.".format( mpan_core=mpan_core)) vals = { 'chunk_start': chunk_start, 'chunk_finish': chunk_finish } vals.update(url_values.get(mpan_core, {})) try: url = url_template.render(vals) except jinja2.exceptions.UndefinedError as e: raise BadRequest( "Problem rendering the URL template: " + url_template_str + ". The problem is: " + str(e) + ". This can be fixed by " + "editing the properties of this contract.") self.log("Retrieving data from {url}.".format(url=url)) res = requests.get(url) res.raise_for_status() result = requests.get(url).json() if isinstance(result, dict): result_data = result['DataPoints'] elif isinstance(result, list): result_data = result else: raise BadRequest( "Expecting a JSON object at the top level, but " "instead got " + str(result)) raw_data = [] for jdatum in result_data: raw_data.append( dict(mpan_core=mpan_core, start_date=utc_datetime(1, 1, 1) + Timedelta(seconds=jdatum['Time'] / 10000000), channel_type='ACTIVE', value=jdatum['Value'], status='A')) HhDatum.insert(sess, raw_data, contract) sess.commit() self.log("Finished loading.") return False
def content(start_date, finish_date, contract_id, user): caches = {} sess = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'mop_virtual_bills.csv', user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') contract = Contract.get_mop_by_id(sess, contract_id) forecast_date = chellow.computer.forecast_date() header_titles = [ 'Import MPAN Core', 'Export MPAN Core', 'Start Date', 'Finish Date'] bill_titles = chellow.computer.contract_func( caches, contract, 'virtual_bill_titles')() writer.writerow(header_titles + bill_titles) vb_func = chellow.computer.contract_func( caches, contract, 'virtual_bill') for era in sess.query(Era).filter( or_( Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date, Era.mop_contract == contract). \ order_by(Era.imp_mpan_core, Era.exp_mpan_core, Era.start_date): chunk_start = hh_max(era.start_date, start_date) chunk_finish = hh_min(era.finish_date, finish_date) import_mpan_core = era.imp_mpan_core if import_mpan_core is None: import_mpan_core_str = '' else: is_import = True import_mpan_core_str = import_mpan_core export_mpan_core = era.exp_mpan_core if export_mpan_core is None: export_mpan_core_str = '' else: is_import = False export_mpan_core_str = export_mpan_core out = [ import_mpan_core_str, export_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.mop_bill for title in bill_titles: if title in bill: out.append(make_val(bill[title])) del bill[title] else: out.append('') for k in sorted(bill.keys()): out.append(k) out.append(str(bill[k])) writer.writerow(out) except: 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 __init__(self, sess, start_date, finish_date, forecast_date, g_era, caches, g_bill): self.sess = sess self.caches = caches self.forecast_date = forecast_date self.start_date = start_date self.finish_date = finish_date times = get_times(sess, caches, start_date, finish_date, forecast_date) self.years_back = times['years-back'] self.history_start = times['history-start'] self.history_finish = times['history-finish'] self.problem = '' self.bill = defaultdict(int, {'problem': ''}) self.hh_data = [] self.rate_sets = defaultdict(set) self.g_bill = g_bill if self.g_bill is not None: self.g_bill_start = g_bill.start_date self.g_bill_finish = g_bill.finish_date self.is_last_g_bill_gen = \ not self.g_bill_finish < self.start_date and not \ self.g_bill_finish > self.finish_date self.g_era = g_era self.g_supply = g_era.g_supply self.mprn = self.g_supply.mprn self.g_exit_zone_code = self.g_supply.g_exit_zone.code self.g_ldz_code = self.g_supply.g_exit_zone.g_ldz.code self.g_dn_code = self.g_supply.g_exit_zone.g_ldz.g_dn.code self.account = g_era.account self.g_contract = g_era.g_contract self.consumption_info = '' if self.years_back == 0: hist_g_eras = [self.g_era] else: hist_g_eras = sess.query(GEra).filter( GEra.g_supply == self.g_supply, GEra.start_date <= self.history_finish, or_(GEra.finish_date == null(), GEra.finish_date >= self.history_start)).order_by( GEra.start_date).all() if len(hist_g_eras) == 0: hist_g_eras = sess.query(GEra).filter( GEra.g_supply == self.g_supply).order_by( GEra.start_date).limit(1).all() g_cv_id = get_non_core_contract_id('g_cv') hist_map = {} for i, hist_g_era in enumerate(hist_g_eras): if self.history_start > hist_g_era.start_date: chunk_start = self.history_start else: if i == 0: chunk_start = self.history_start else: chunk_start = hist_g_era.start_date chunk_finish = hh_min(hist_g_era.finish_date, self.history_finish) if self.g_bill is None: read_list = [] read_keys = set() pairs = [] prior_pres_g_reads = iter( sess.query(GRegisterRead).join(GBill).join(BillType).join( GRegisterRead.pres_type).filter( GReadType.code.in_(ACTUAL_READ_TYPES), GBill.g_supply == self.g_supply, GRegisterRead.pres_date < chunk_start, BillType.code != 'W').order_by( GRegisterRead.pres_date.desc())) prior_prev_g_reads = iter( sess.query(GRegisterRead).join(GBill).join(BillType).join( GRegisterRead.prev_type).filter( GReadType.code.in_(ACTUAL_READ_TYPES), GBill.g_supply == self.g_supply, GRegisterRead.prev_date < chunk_start, BillType.code != 'W').order_by( GRegisterRead.prev_date.desc())) next_pres_g_reads = iter( sess.query(GRegisterRead).join(GBill).join(BillType).join( GRegisterRead.pres_type).filter( GReadType.code.in_(ACTUAL_READ_TYPES), GBill.g_supply == self.g_supply, GRegisterRead.pres_date >= chunk_start, BillType.code != 'W').order_by( GRegisterRead.pres_date)) next_prev_g_reads = iter( sess.query(GRegisterRead).join(GBill).join(BillType).join( GRegisterRead.prev_type).filter( GReadType.code.in_(ACTUAL_READ_TYPES), GBill.g_supply == self.g_supply, GRegisterRead.prev_date >= chunk_start, BillType.code != 'W').order_by( GRegisterRead.prev_date)) for is_forwards in (False, True): if is_forwards: pres_g_reads = next_pres_g_reads prev_g_reads = next_prev_g_reads read_list.reverse() else: pres_g_reads = prior_pres_g_reads prev_g_reads = prior_prev_g_reads prime_pres_g_read = None prime_prev_g_read = None while True: while prime_pres_g_read is None: try: pres_g_read = next(pres_g_reads) except StopIteration: break pres_date = pres_g_read.pres_date pres_msn = pres_g_read.msn read_key = '_'.join([str(pres_date), pres_msn]) if read_key in read_keys: continue pres_g_bill = sess.query(GBill).join( BillType).filter( GBill.g_supply == self.g_supply, GBill.finish_date >= pres_g_read.g_bill.start_date, GBill.start_date <= pres_g_read.g_bill.finish_date, BillType.code != 'W').order_by( GBill.issue_date.desc(), BillType.code).first() if pres_g_bill != pres_g_read.g_bill: continue value = sess.query( cast(GRegisterRead.pres_value, Float)).filter( GRegisterRead.g_bill == pres_g_bill, GRegisterRead.pres_date == pres_date, GRegisterRead.msn == pres_msn).scalar() prime_pres_g_read = { 'date': pres_date, 'value': value, 'msn': pres_msn } read_keys.add(read_key) while prime_prev_g_read is None: try: prev_g_read = next(prev_g_reads) except StopIteration: break prev_date = prev_g_read.prev_date prev_msn = prev_g_read.msn read_key = '_'.join([str(prev_date), prev_msn]) if read_key in read_keys: continue prev_g_bill = sess.query(GBill).join( BillType).filter( GBill.g_supply == self.g_supply, GBill.finish_date >= prev_g_read.g_bill.start_date, GBill.start_date <= prev_g_read.g_bill.finish_date, BillType.code != 'W').order_by( GBill.issue_date.desc(), BillType.code).first() if prev_g_bill != prev_g_read.g_bill: continue value = sess.query( cast(GRegisterRead.prev_value, Float)).filter( GRegisterRead.g_bill == prev_g_bill, GRegisterRead.prev_date == prev_date, GRegisterRead.msn == prev_msn).scalar() prime_prev_g_read = { 'date': prev_date, 'value': value, 'msn': prev_msn } read_keys.add(read_key) if prime_pres_g_read is None and \ prime_prev_g_read is None: break elif prime_pres_g_read is None: read_list.append(prime_prev_g_read) prime_prev_g_read = None elif prime_prev_g_read is None: read_list.append(prime_pres_g_read) prime_pres_g_read = None else: if is_forwards: if prime_prev_g_read['date'] == \ prime_pres_g_read['date'] or \ prime_pres_g_read['date'] < \ prime_prev_g_read['date']: read_list.append(prime_pres_g_read) prime_pres_g_read = None else: read_list.append(prime_prev_g_read) prime_prev_g_read = None else: if prime_prev_g_read['date'] == \ prime_pres_g_read['date'] or \ prime_prev_g_read['date'] > \ prime_pres_g_read['date']: read_list.append(prime_prev_g_read) prime_prev_g_read = None else: read_list.append(prime_pres_g_read) prime_pres_g_read = None if len(read_list) > 1: if is_forwards: aft_read = read_list[-2] fore_read = read_list[-1] else: aft_read = read_list[-1] fore_read = read_list[-2] if aft_read['msn'] == fore_read['msn']: num_hh = (fore_read['date'] - aft_read['date'] ).total_seconds() / (30 * 60) units = fore_read['value'] - aft_read['value'] if units < 0: digits = int(math.log10( aft_read['value'])) + 1 units = 10**digits + units pairs.append({ 'start-date': aft_read['date'], 'units': units / num_hh }) if not is_forwards or ( is_forwards and read_list[-1]['date'] > chunk_finish): break self.consumption_info += 'read list - \n' + dumps(read_list) \ + "\n" hhs = _find_hhs(sess, caches, hist_g_era, pairs, chunk_start, chunk_finish, g_cv_id, self.g_ldz_code) hist_map.update(hhs) self.consumption_info += 'pairs - \n' + dumps(pairs) else: g_bills = [] for cand_bill in sess.query(GBill).join(GBatch) \ .join(BillType).filter( GBill.g_supply == self.g_supply, GBill.g_reads.any(), GBatch.g_contract == self.g_contract, GBill.start_date <= chunk_finish, GBill.finish_date >= chunk_start, BillType.code != 'W').order_by( GBill.issue_date.desc(), GBill.start_date): can_insert = True for g_bill in g_bills: if not cand_bill.start_date > g_bill.finish_date \ and not cand_bill.finish_date < \ g_bill.start_date: can_insert = False break if can_insert: g_bills.append(cand_bill) for g_bill in g_bills: units_consumed = 0 for prev_value, pres_value in sess.query( cast(GRegisterRead.prev_value, Float), cast( GRegisterRead.pres_value, Float)).filter(GRegisterRead.g_bill == g_bill): units_diff = pres_value - prev_value if units_diff < 0: total_units = 10**len(str(int(prev_value))) c_units = total_units - prev_value + pres_value if c_units < abs(units_diff): units_diff = c_units units_consumed += units_diff bill_s = (g_bill.finish_date - g_bill.start_date + timedelta(minutes=30)).total_seconds() hh_units_consumed = units_consumed / (bill_s / (60 * 30)) cf = float(hist_g_era.correction_factor) g_unit = hist_g_era.g_unit unit_code, unit_factor = g_unit.code, float(g_unit.factor) for hh_date in hh_range(caches, g_bill.start_date, g_bill.finish_date): cv, avg_cv = find_cv(sess, caches, g_cv_id, hh_date, self.g_ldz_code) hist_map[hh_date] = { 'unit_code': unit_code, 'unit_factor': unit_factor, 'units_consumed': hh_units_consumed, 'correction_factor': cf, 'calorific_value': cv, 'avg_cv': avg_cv } for d in datum_range(sess, self.caches, self.years_back, start_date, finish_date): h = d.copy() hist_start = h['hist_start'] h.update(hist_map.get(hist_start, {})) h['kwh'] = h['units_consumed'] * h['unit_factor'] * \ h['correction_factor'] * h['calorific_value'] / 3.6 h['kwh_avg'] = h['units_consumed'] * h['unit_factor'] * \ h['correction_factor'] * h['avg_cv'] / 3.6 h['ug_rate'] = float( get_file_rates( self.caches, 'g_ug', h['start_date'])['ug_gbp_per_kwh'][self.g_exit_zone_code]) self.hh_data.append(h)
def content(supply_id, file_name, start_date, finish_date, user): caches = {} sess = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( "supply_virtual_bills_" + str(supply_id) + ".csv", user) f = open(running_name, mode="w", newline="") writer = csv.writer(f, lineterminator="\n") supply = Supply.get_by_id(sess, supply_id) forecast_date = chellow.computer.forecast_date() prev_titles = None for era in (sess.query(Era).filter( Era.supply == supply, Era.start_date < finish_date, or_(Era.finish_date == null(), Era.finish_date > start_date), ).order_by(Era.start_date)): chunk_start = hh_max(era.start_date, start_date) chunk_finish = hh_min(era.finish_date, finish_date) site = (sess.query(Site).join(SiteEra).filter( SiteEra.era == era, SiteEra.is_physical == true()).one()) ds = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, era.imp_supplier_contract is not None, caches, ) titles = [ "Imp MPAN Core", "Exp MPAN Core", "Site Code", "Site Name", "Account", "From", "To", "", ] output_line = [ era.imp_mpan_core, era.exp_mpan_core, site.code, site.name, ds.supplier_account, hh_format(ds.start_date), hh_format(ds.finish_date), "", ] mop_titles = ds.contract_func(era.mop_contract, "virtual_bill_titles")() titles.extend(["mop-" + t for t in mop_titles]) ds.contract_func(era.mop_contract, "virtual_bill")(ds) bill = ds.mop_bill for title in mop_titles: if title in bill: output_line.append(bill[title]) del bill[title] else: output_line.append("") for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) output_line.append("") dc_titles = ds.contract_func(era.dc_contract, "virtual_bill_titles")() titles.append("") titles.extend(["dc-" + t for t in dc_titles]) ds.contract_func(era.dc_contract, "virtual_bill")(ds) bill = ds.dc_bill for title in dc_titles: output_line.append(bill.get(title, "")) if title in bill: del bill[title] for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) tpr_query = (sess.query(Tpr).join(MeasurementRequirement).join( Ssc).join(Era).filter( Era.start_date <= chunk_finish, or_(Era.finish_date == null(), Era.finish_date >= chunk_start), ).order_by(Tpr.code).distinct()) if era.imp_supplier_contract is not None: output_line.append("") supplier_titles = ds.contract_func(era.imp_supplier_contract, "virtual_bill_titles")() for tpr in tpr_query.filter( Era.imp_supplier_contract != null()): for suffix in ("-kwh", "-rate", "-gbp"): supplier_titles.append(tpr.code + suffix) titles.append("") titles.extend(["imp-supplier-" + t for t in supplier_titles]) ds.contract_func(era.imp_supplier_contract, "virtual_bill")(ds) bill = ds.supplier_bill for title in supplier_titles: if title in bill: output_line.append(bill[title]) del bill[title] else: output_line.append("") for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) if era.exp_supplier_contract is not None: ds = chellow.computer.SupplySource(sess, chunk_start, chunk_finish, forecast_date, era, False, caches) output_line.append("") supplier_titles = ds.contract_func(era.exp_supplier_contract, "virtual_bill_titles")() for tpr in tpr_query.filter( Era.exp_supplier_contract != null()): for suffix in ("-kwh", "-rate", "-gbp"): supplier_titles.append(tpr.code + suffix) titles.append("") titles.extend(["exp-supplier-" + t for t in supplier_titles]) ds.contract_func(era.exp_supplier_contract, "virtual_bill")(ds) bill = ds.supplier_bill for title in supplier_titles: output_line.append(bill.get(title, "")) if title in bill: del bill[title] for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) if titles != prev_titles: prev_titles = titles writer.writerow([str(v) for v in titles]) for i, val in enumerate(output_line): output_line[i] = csv_make_val(val) writer.writerow(output_line) except BadRequest as e: writer.writerow(["Problem: " + e.description]) except BaseException: writer.writerow([traceback.format_exc()]) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def content(base_name, site_id, g_supply_id, user, compression, start_date, months): now = utc_datetime_now() report_context = {} sess = None try: sess = Session() base_name.append( hh_format(start_date).replace(' ', '_').replace(':', '').replace('-', '')) base_name.append('for') base_name.append(str(months)) base_name.append('months') finish_date = start_date + relativedelta(months=months) forecast_from = chellow.computer.forecast_date() 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 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.join(SiteGEra).join(GEra).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) sites = sites.all() month_start = start_date while month_start < finish_date: month_finish = month_start + relativedelta(months=1) - HH for site in sites: 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([ 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([ 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) month_start += relativedelta(months=1) 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, 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 content( start_year, start_month, start_day, finish_year, finish_month, finish_day, is_import, supply_id, user): caches = {} sess = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'daily_supplier_virtual_bill.csv', user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') start_date = Datetime( start_year, start_month, start_day, tzinfo=pytz.utc) finish_date = Datetime( finish_year, finish_month, finish_day, tzinfo=pytz.utc) + \ relativedelta(days=1) - HH supply = Supply.get_by_id(sess, supply_id) forecast_date = chellow.computer.forecast_date() day_start = start_date header_titles = [ 'MPAN Core', 'Site Code', 'Site Name', 'Account', 'From', 'To', 'Is Forecast?' ] bill_titles = [] # Find titles for era in sess.query(Era).filter( Era.supply == supply, Era.start_date <= finish_date, or_( Era.finish_date == null(), Era.finish_date >= start_date)): if is_import: cont = era.imp_supplier_contract else: cont = era.exp_supplier_contract for title in chellow.computer.contract_func( caches, cont, 'virtual_bill_titles')(): if title not in bill_titles: bill_titles.append(title) ssc = era.ssc if ssc is not None: for mr in ssc.measurement_requirements: for suffix in ('-kwh', '-rate', '-gbp'): title = mr.tpr.code + suffix if title not in bill_titles: bill_titles.append(title) writer.writerow(header_titles + bill_titles) while not day_start > finish_date: day_finish = day_start + relativedelta(days=1) - HH for era in supply.find_eras(sess, day_start, day_finish): chunk_start = hh_max(era.start_date, day_start) chunk_finish = hh_min(era.finish_date, day_finish) ss = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, is_import, caches) site = sess.query(Site).join(SiteEra).filter( SiteEra.era == era, SiteEra.is_physical == true()).one() row = [ ss.mpan_core, site.code, site.name, ss.supplier_account, hh_format(ss.start_date), hh_format(ss.finish_date), ss.years_back > 0] chellow.computer.contract_func( caches, ss.supplier_contract, 'virtual_bill')(ss) bill = ss.supplier_bill for title in bill_titles: if title in bill: row.append(csv_make_val(bill[title])) del bill[title] else: row.append('') for k in sorted(bill.keys()): row.append(k) row.append(csv_make_val(bill[k])) writer.writerow(row) day_start += relativedelta(days=1) except BadRequest as e: writer.writerow(["Problem: " + e.description]) except BaseException: writer.writerow([traceback.format_exc()]) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
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(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) finish_date = utc_datetime(end_year, end_month, 1) + MONTH - HH start_date = utc_datetime(end_year, end_month, 1) - relativedelta(months=months - 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 content(running_name, finished_name, date, supply_id, mpan_cores): sess = None try: sess = Session() f = open(running_name, mode="w", newline="") writer = csv.writer(f, lineterminator="\n") titles = ( "Date", "Import MPAN Core", "Export MPAN Core", "Physical Site Id", "Physical Site Name", "Other Site Ids", "Other Site Names", "Supply Id", "Source", "Generator Type", "GSP Group", "DNO Name", "Voltage Level", "Is Substations", "Metering Type", "Mandatory HH", "PC", "MTC", "CoP", "SSC Code", "SSC Description", "Energisation Status", "Number Of Registers", "MOP Contract", "Mop Account", "DC Contract", "DC Account", "Meter Serial Number", "Meter Installation Date", "Latest Normal Meter Read Date", "Latest Normal Meter Read Type", "Latest DC Bill Date", "Latest MOP Bill Date", "Supply Start Date", "Supply Finish Date", "Properties", "Import ACTIVE?", "Import REACTIVE_IMPORT?", "Import REACTIVE_EXPORT?", "Export ACTIVE?", "Export REACTIVE_IMPORT?", "Export REACTIVE_EXPORT?", "Import Agreed Supply Capacity (kVA)", "Import LLFC Code", "Import LLFC Description", "Import Supplier Contract", "Import Supplier Account", "Import Mandatory kW", "Latest Import Supplier Bill Date", "Export Agreed Supply Capacity (kVA)", "Export LLFC Code", "Export LLFC Description", "Export Supplier Contract", "Export Supplier Account", "Export Mandatory kW", "Latest Export Supplier Bill Date", ) writer.writerow(titles) NORMAL_READ_TYPES = ("N", "C", "N3") year_start = date + HH - relativedelta(years=1) era_ids = (sess.query(Era.id).filter( Era.start_date <= date, or_(Era.finish_date == null(), Era.finish_date >= date), ).order_by(Era.supply_id)) if supply_id is not None: supply = Supply.get_by_id(sess, supply_id) era_ids = era_ids.filter(Era.supply == supply) if mpan_cores is not None: era_ids = era_ids.filter( or_(Era.imp_mpan_core.in_(mpan_cores), Era.exp_mpan_core.in_(mpan_cores))) for (era_id, ) in era_ids: era, supply, generator_type = (sess.query( Era, Supply, GeneratorType).join( Supply, Era.supply_id == Supply.id).outerjoin( GeneratorType, Supply.generator_type_id == GeneratorType.id).filter( Era.id == era_id).options( joinedload(Era.channels), joinedload(Era.cop), joinedload(Era.dc_contract), joinedload(Era.exp_llfc), joinedload(Era.exp_supplier_contract), joinedload(Era.imp_llfc), joinedload(Era.imp_supplier_contract), joinedload(Era.mop_contract), joinedload(Era.mtc), joinedload(Era.mtc).joinedload(Mtc.meter_type), joinedload(Era.pc), joinedload(Era.site_eras).joinedload( SiteEra.site), joinedload(Era.ssc), joinedload(Era.energisation_status), joinedload(Era.supply).joinedload( Supply.source), joinedload(Era.supply).joinedload( Supply.gsp_group), joinedload(Era.supply).joinedload(Supply.dno), ).one()) site_codes = [] site_names = [] for site_era in era.site_eras: if site_era.is_physical: physical_site = site_era.site else: site = site_era.site site_codes.append(site.code) site_names.append(site.name) sup_eras = (sess.query(Era).filter(Era.supply == supply).order_by( Era.start_date).all()) supply_start_date = sup_eras[0].start_date supply_finish_date = sup_eras[-1].finish_date if era.imp_mpan_core is None: voltage_level_code = era.exp_llfc.voltage_level.code is_substation = era.exp_llfc.is_substation else: voltage_level_code = era.imp_llfc.voltage_level.code is_substation = era.imp_llfc.is_substation if generator_type is None: generator_type_str = "" else: generator_type_str = generator_type.code metering_type = era.meter_category if metering_type in ("nhh", "amr"): latest_prev_normal_read = ( sess.query(RegisterRead).join(Bill).join( RegisterRead.previous_type).filter( ReadType.code.in_(NORMAL_READ_TYPES), RegisterRead.previous_date <= date, Bill.supply_id == supply.id, ).order_by(RegisterRead.previous_date.desc()).options( joinedload(RegisterRead.previous_type)).first()) latest_pres_normal_read = ( sess.query(RegisterRead).join(Bill).join( RegisterRead.present_type).filter( ReadType.code.in_(NORMAL_READ_TYPES), RegisterRead.present_date <= date, Bill.supply == supply, ).order_by(RegisterRead.present_date.desc()).options( joinedload(RegisterRead.present_type)).first()) if latest_prev_normal_read is None and latest_pres_normal_read is None: latest_normal_read_date = None latest_normal_read_type = None elif (latest_pres_normal_read is not None and latest_prev_normal_read is None): latest_normal_read_date = latest_pres_normal_read.present_date latest_normal_read_type = latest_pres_normal_read.present_type.code elif (latest_pres_normal_read is None and latest_prev_normal_read is not None): latest_normal_read_date = latest_prev_normal_read.previous_date latest_normal_read_type = latest_prev_normal_read.previous_type.code elif (latest_pres_normal_read.present_date > latest_prev_normal_read.previous_date): latest_normal_read_date = latest_pres_normal_read.present_date latest_normal_read_type = latest_pres_normal_read.present_type.code else: latest_normal_read_date = latest_prev_normal_read.previous_date latest_normal_read_type = latest_prev_normal_read.previous_type.code if latest_normal_read_date is not None: latest_normal_read_date = hh_format( latest_normal_read_date) else: latest_normal_read_date = metering_type latest_normal_read_type = None mop_contract = era.mop_contract mop_contract_name = mop_contract.name mop_account = era.mop_account latest_mop_bill_date = (sess.query( Bill.finish_date).join(Batch).filter( Bill.start_date <= date, Bill.supply == supply, Batch.contract == mop_contract, ).order_by(Bill.finish_date.desc()).first()) if latest_mop_bill_date is not None: latest_mop_bill_date = hh_format(latest_mop_bill_date[0]) dc_contract = era.dc_contract dc_contract_name = dc_contract.name dc_account = era.dc_account latest_dc_bill_date = (sess.query( Bill.finish_date).join(Batch).filter( Bill.start_date <= date, Bill.supply == supply, Batch.contract == dc_contract, ).order_by(Bill.finish_date.desc()).first()) if latest_dc_bill_date is not None: latest_dc_bill_date = hh_format(latest_dc_bill_date[0]) channel_values = [] for imp_related in [True, False]: for channel_type in CHANNEL_TYPES: if era.find_channel(sess, imp_related, channel_type) is None: channel_values.append("false") else: channel_values.append("true") imp_avg_months = None exp_avg_months = None for is_import in [True, False]: if metering_type == "nhh": continue params = { "supply_id": supply.id, "year_start": year_start, "year_finish": date, "is_import": is_import, } month_mds = tuple(md[0] * 2 for md in sess.execute( """ select max(hh_datum.value) as md from hh_datum join channel on (hh_datum.channel_id = channel.id) join era on (channel.era_id = era.id) where era.supply_id = :supply_id and hh_datum.start_date >= :year_start and hh_datum.start_date <= :year_finish and channel.channel_type = 'ACTIVE' and channel.imp_related = :is_import group by extract(month from (hh_datum.start_date at time zone 'utc')) order by md desc limit 3 """, params=params, )) avg_months = sum(month_mds) if len(month_mds) > 0: avg_months /= len(month_mds) if is_import: imp_avg_months = avg_months else: exp_avg_months = avg_months if (imp_avg_months is not None and imp_avg_months > 100) or (exp_avg_months is not None and exp_avg_months > 100): mandatory_hh = "yes" else: mandatory_hh = "no" imp_latest_supplier_bill_date = None exp_latest_supplier_bill_date = None for is_import in (True, False): for er in (sess.query(Era).filter( Era.supply == era.supply, Era.start_date <= date).order_by( Era.start_date.desc())): if is_import: if er.imp_mpan_core is None: break else: supplier_contract = er.imp_supplier_contract else: if er.exp_mpan_core is None: break else: supplier_contract = er.exp_supplier_contract latest_bill_date = (sess.query( Bill.finish_date).join(Batch).filter( Bill.finish_date >= er.start_date, Bill.finish_date <= hh_min(er.finish_date, date), Bill.supply == supply, Batch.contract == supplier_contract, ).order_by(Bill.finish_date.desc()).first()) if latest_bill_date is not None: latest_bill_date = hh_format(latest_bill_date[0]) if is_import: imp_latest_supplier_bill_date = latest_bill_date else: exp_latest_supplier_bill_date = latest_bill_date break meter_installation_date = (sess.query(func.min( Era.start_date)).filter(Era.supply == era.supply, Era.msn == era.msn).one()[0]) ssc = era.ssc if ssc is None: ssc_code = ssc_description = num_registers = None else: ssc_code, ssc_description = ssc.code, ssc.description num_registers = (sess.query(MeasurementRequirement).filter( MeasurementRequirement.ssc == ssc).count()) vals = ([ date, era.imp_mpan_core, era.exp_mpan_core, physical_site.code, physical_site.name, ", ".join(site_codes), ", ".join(site_names), supply.id, supply.source.code, generator_type_str, supply.gsp_group.code, supply.dno.dno_code, voltage_level_code, is_substation, metering_type, mandatory_hh, era.pc.code, era.mtc.code, era.cop.code, ssc_code, ssc_description, era.energisation_status.code, num_registers, mop_contract_name, mop_account, dc_contract_name, dc_account, era.msn, meter_installation_date, latest_normal_read_date, latest_normal_read_type, latest_dc_bill_date, latest_mop_bill_date, supply_start_date, supply_finish_date, era.properties, ] + channel_values + [ era.imp_sc, None if era.imp_llfc is None else era.imp_llfc.code, None if era.imp_llfc is None else era.imp_llfc.description, None if era.imp_supplier_contract is None else era.imp_supplier_contract.name, era.imp_supplier_account, imp_avg_months, imp_latest_supplier_bill_date, ] + [ era.exp_sc, None if era.exp_llfc is None else era.exp_llfc.code, None if era.exp_llfc is None else era.exp_llfc.description, None if era.exp_supplier_contract is None else era.exp_supplier_contract.name, era.exp_supplier_account, exp_avg_months, exp_latest_supplier_bill_date, ]) writer.writerow([csv_make_val(v) for v in vals]) # Avoid a long-running transaction sess.rollback() 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, scenario_id, base_name, site_id, supply_id, user, compression): now = utc_datetime_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() if scenario_props is None: scenario_contract = Contract.get_supplier_by_id(sess, scenario_id) scenario_props = scenario_contract.make_properties() base_name.append(scenario_contract.name) start_date = scenario_props['scenario_start'] if start_date is None: start_date = utc_datetime(now.year, now.month, 1) else: start_date = to_utc(start_date) base_name.append( hh_format(start_date).replace(' ', '_').replace(':', '').replace('-', '')) months = scenario_props['scenario_duration'] base_name.append('for') base_name.append(str(months)) base_name.append('months') finish_date = start_date + relativedelta(months=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) 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 get_map_list(scenario_props, '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( "Problem in the scenario properties. Can't find the " + "'start_date' key of the contract " + str(contract_id) + " in the 'local_rates' map.") try: rate_script_start = rate_script['start_date'] except KeyError: raise BadRequest( "Problem in the scenario properties. Can't find the " + "'start_date' key of the contract " + str(contract_id) + " in the 'local_rates' map.") for dt in hh_range(report_context, rate_script_start, rate_script['finish_date']): cont_cache[dt] = PropDict('scenario properties', rate_script['script']) for rate_script in get_map_list(scenario_props, '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("For the industry rate " + contract_name + " the " "finish_date 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', {}) 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' ] 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, or_( Era.finish_date == null(), Era.finish_date >= start_date), Source.code.in_(('net', '3rd-party')) ).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("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 titles: titles.append(title) tpr_query = 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) ).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 = {} for site in sites: try: site_scenario_hh = scenario_hh[site.code] except KeyError: site_scenario_hh = scenario_hh[site.code] = {} site_deltas = deltas[site.code] = {'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, kw_str = cells ts = parse_hh_start(date_str) earliest_delta = min(ts, earliest_delta) latest_delta = max(ts, latest_delta) hh_data[ts] = float(kw_str) found_hh = True if not found_hh: continue 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'] month_start = utc_datetime(earliest_delta.year, earliest_delta.month) while month_start <= latest_delta: month_finish = month_start + relativedelta(months=1) - HH 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), ~Era.channels.any()): 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 in site_ds.hh_data: hh_start = hh['start-date'] 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 month_start += relativedelta(months=1) month_start = start_date while month_start < finish_date: month_finish = month_start + relativedelta(months=1) - HH for site in sites: site_category = None site_sources = set() site_gen_types = set() site_month_data = defaultdict(int) calcs = [] for era in sess.query(Era).join(SiteEra).join(Pc).filter( SiteEra.site == site, SiteEra.is_physical == true(), Era.start_date <= month_finish, or_(Era.finish_date == null(), Era.finish_date >= month_start)).options( joinedload(Era.ssc), joinedload(Era.dc_contract), joinedload(Era.mop_contract), joinedload(Era.imp_supplier_contract), joinedload(Era.exp_supplier_contract), joinedload(Era.channels), joinedload(Era.imp_llfc).joinedload( Llfc.voltage_level), joinedload(Era.exp_llfc).joinedload( Llfc.voltage_level), joinedload(Era.cop), joinedload(Era.supply).joinedload(Supply.dno), joinedload(Era.supply).joinedload( Supply.gsp_group), joinedload(Era.supply).joinedload( Supply.source), joinedload(Era.mtc).joinedload(Mtc.meter_type), joinedload(Era.pc), joinedload(Era.site_eras)).order_by(Pc.code): supply = era.supply if supply.generator_type is not None: site_gen_types.add(supply.generator_type.code) if supply_id is not None and supply.id != supply_id: continue ss_start = hh_max(era.start_date, month_start) ss_finish = hh_min(era.finish_date, month_finish) if era.imp_mpan_core is None: imp_ss = None else: sup_deltas = site_deltas['supply_deltas'][True][ supply.source.code] imp_ss = SupplySource(sess, ss_start, ss_finish, forecast_from, era, True, report_context, era_maps=era_maps, deltas=sup_deltas) if era.exp_mpan_core is None: exp_ss = None measurement_type = imp_ss.measurement_type else: sup_deltas = site_deltas['supply_deltas'][False][ supply.source.code] exp_ss = SupplySource(sess, ss_start, ss_finish, forecast_from, era, False, report_context, era_maps=era_maps, deltas=sup_deltas) measurement_type = exp_ss.measurement_type order = meter_order[measurement_type] calcs.append((order, era.imp_mpan_core, era.exp_mpan_core, imp_ss, exp_ss)) # Check if gen deltas haven't been consumed extra_sss = set() for is_imp in (True, False): sup_deltas = site_deltas['supply_deltas'][is_imp]['gen'] if len( list(t for t in sup_deltas['site'] if month_start <= t <= month_finish)) > 0: extra_sss.add(is_imp) displaced_era = chellow.computer.displaced_era( sess, report_context, site, month_start, month_finish, forecast_from, has_scenario_generation=len(extra_sss) > 0) if len(extra_sss) > 0: if True in extra_sss: sup_deltas = site_deltas['supply_deltas'][True]['gen'] imp_ss_name = site.code + "_extra_gen_TRUE" imp_ss = ScenarioSource( sess, month_start, month_finish, True, report_context, sup_deltas, displaced_era.imp_supplier_contract, imp_ss_name) else: imp_ss_name = imp_ss = None if False in extra_sss: exp_ss_name = site.code + "_extra_gen_FALSE" sup_deltas = site_deltas['supply_deltas'][False]['gen'] exp_ss = ScenarioSource( sess, month_start, month_finish, False, report_context, sup_deltas, displaced_era.imp_supplier_contract, imp_ss_name) else: exp_ss_name = exp_ss = None calcs.append((0, imp_ss_name, exp_ss_name, imp_ss, exp_ss)) # Check if exp net deltas haven't been consumed sup_deltas = site_deltas['supply_deltas'][False]['net'] if len( list(t for t in sup_deltas['site'] if month_start <= t <= month_finish)) > 0: ss_name = site.code + "_extra_net_export" ss = SupplySource(sess, month_start, month_finish, forecast_from, displaced_era, False, report_context, era_maps=era_maps, deltas=sup_deltas) calcs.append((0, None, ss_name, None, ss)) site_ds = chellow.computer.SiteSource(sess, site, month_start, month_finish, forecast_from, report_context, displaced_era, deltas=site_deltas) if displaced_era is not None and supply_id is None: month_data = {} for sname in ('import-net', 'export-net', 'import-gen', 'export-gen', 'import-3rd-party', 'export-3rd-party', 'msp', 'used', 'used-3rd-party', 'billed-import-net'): for xname in ('kwh', 'gbp'): month_data[sname + '-' + xname] = 0 month_data['used-kwh'] = month_data['displaced-kwh'] = sum( hh['msp-kwh'] for hh in site_ds.hh_data) disp_supplier_contract = \ displaced_era.imp_supplier_contract disp_vb_function = chellow.computer.contract_func( report_context, disp_supplier_contract, 'displaced_virtual_bill') if disp_vb_function is None: raise BadRequest( "The supplier contract " + disp_supplier_contract.name + " doesn't have the displaced_virtual_bill() " "function.") disp_vb_function(site_ds) disp_supplier_bill = site_ds.supplier_bill try: gbp = disp_supplier_bill['net-gbp'] except KeyError: disp_supplier_bill['problem'] += 'For the supply ' + \ site_ds.mpan_core + ' the virtual bill ' + \ str(disp_supplier_bill) + ' from the contract ' + \ disp_supplier_contract.name + \ ' does not contain the net-gbp key.' month_data['used-gbp'] = month_data['displaced-gbp'] = \ site_ds.supplier_bill['net-gbp'] out = [ now, None, disp_supplier_contract.name, None, None, displaced_era.meter_category, 'displaced', None, None, None, None, site.code, site.name, '', month_finish ] + [month_data[t] for t in summary_titles ] + [None] + [None] * len(title_dict['mop']) + [ None ] + [None] * len(title_dict['dc']) + [ None ] + make_bill_row(title_dict['imp-supplier'], disp_supplier_bill) era_rows.append(out) for k, v in month_data.items(): site_month_data[k] += v for i, (order, imp_mpan_core, exp_mpan_core, imp_ss, exp_ss) in enumerate(sorted(calcs, key=str)): if imp_ss is None: source_code = exp_ss.source_code supply = exp_ss.supply else: source_code = imp_ss.source_code supply = imp_ss.supply site_sources.add(source_code) month_data = {} for name in ('import-net', 'export-net', 'import-gen', 'export-gen', 'import-3rd-party', 'export-3rd-party', 'displaced', 'used', 'used-3rd-party', 'billed-import-net'): for sname in ('kwh', 'gbp'): month_data[name + '-' + sname] = 0 if imp_ss is not None: imp_supplier_contract = imp_ss.supplier_contract if imp_supplier_contract is not None: import_vb_function = contract_func( report_context, imp_supplier_contract, 'virtual_bill') if import_vb_function is None: raise BadRequest( "The supplier contract " + imp_supplier_contract.name + " doesn't have the virtual_bill() " "function.") import_vb_function(imp_ss) kwh = sum(hh['msp-kwh'] for hh in imp_ss.hh_data) imp_supplier_bill = imp_ss.supplier_bill try: gbp = imp_supplier_bill['net-gbp'] except KeyError: gbp = 0 imp_supplier_bill['problem'] += \ 'For the supply ' + \ imp_ss.mpan_core + \ ' the virtual bill ' + \ str(imp_supplier_bill) + \ ' from the contract ' + \ imp_supplier_contract.name + \ ' does not contain the net-gbp key.' if source_code in ('net', 'gen-net'): month_data['import-net-gbp'] += gbp month_data['import-net-kwh'] += kwh month_data['used-gbp'] += gbp month_data['used-kwh'] += kwh if source_code == 'gen-net': month_data['export-gen-kwh'] += kwh elif source_code == '3rd-party': month_data['import-3rd-party-gbp'] += gbp month_data['import-3rd-party-kwh'] += kwh month_data['used-3rd-party-gbp'] += gbp month_data['used-3rd-party-kwh'] += kwh month_data['used-gbp'] += gbp month_data['used-kwh'] += kwh elif source_code == '3rd-party-reverse': month_data['export-3rd-party-gbp'] += gbp month_data['export-3rd-party-kwh'] += kwh month_data['used-3rd-party-gbp'] -= gbp month_data['used-3rd-party-kwh'] -= kwh month_data['used-gbp'] -= gbp month_data['used-kwh'] -= kwh elif source_code == 'gen': month_data['import-gen-kwh'] += kwh if exp_ss is not None: exp_supplier_contract = exp_ss.supplier_contract if exp_supplier_contract is not None: export_vb_function = contract_func( report_context, exp_supplier_contract, 'virtual_bill') export_vb_function(exp_ss) kwh = sum(hh['msp-kwh'] for hh in exp_ss.hh_data) exp_supplier_bill = exp_ss.supplier_bill try: gbp = exp_supplier_bill['net-gbp'] except KeyError: exp_supplier_bill['problem'] += \ 'For the supply ' + imp_ss.mpan_core + \ ' the virtual bill ' + \ str(imp_supplier_bill) + \ ' from the contract ' + \ imp_supplier_contract.name + \ ' does not contain the net-gbp key.' if source_code in ('net', 'gen-net'): month_data['export-net-gbp'] += gbp month_data['export-net-kwh'] += kwh if source_code == 'gen-net': month_data['import-gen-kwh'] += kwh elif source_code == '3rd-party': month_data['export-3rd-party-gbp'] += gbp month_data['export-3rd-party-kwh'] += kwh month_data['used-3rd-party-gbp'] -= gbp month_data['used-3rd-party-kwh'] -= kwh month_data['used-gbp'] -= gbp month_data['used-kwh'] -= kwh elif source_code == '3rd-party-reverse': month_data['import-3rd-party-gbp'] += gbp month_data['import-3rd-party-kwh'] += kwh month_data['used-3rd-party-gbp'] += gbp month_data['used-3rd-party-kwh'] += kwh month_data['used-gbp'] += gbp month_data['used-kwh'] += kwh elif source_code == 'gen': month_data['export-gen-kwh'] += kwh sss = exp_ss if imp_ss is None else imp_ss dc_contract = sss.dc_contract if dc_contract is not None: sss.contract_func(dc_contract, 'virtual_bill')(sss) dc_bill = sss.dc_bill gbp = dc_bill['net-gbp'] mop_contract = sss.mop_contract if mop_contract is not None: mop_bill_function = sss.contract_func( mop_contract, 'virtual_bill') mop_bill_function(sss) mop_bill = sss.mop_bill gbp += mop_bill['net-gbp'] if source_code in ('3rd-party', '3rd-party-reverse'): month_data['import-3rd-party-gbp'] += gbp month_data['used-3rd-party-gbp'] += gbp else: month_data['import-net-gbp'] += gbp month_data['used-gbp'] += gbp generator_type = sss.generator_type_code if source_code in ('gen', 'gen-net'): site_gen_types.add(generator_type) era_category = sss.measurement_type if CATEGORY_ORDER[site_category] < \ CATEGORY_ORDER[era_category]: site_category = era_category era_associates = set() if mop_contract is not None: era_associates.update({ s.site.code for s in era.site_eras if not s.is_physical }) for bill in sess.query(Bill).filter( Bill.supply == supply, Bill.start_date <= sss.finish_date, Bill.finish_date >= sss.start_date): bill_start = bill.start_date bill_finish = bill.finish_date bill_duration = ( bill_finish - bill_start).total_seconds() + \ (30 * 60) overlap_duration = ( min(bill_finish, sss.finish_date) - max(bill_start, sss.start_date) ).total_seconds() + (30 * 60) overlap_proportion = overlap_duration / \ bill_duration month_data['billed-import-net-kwh'] += \ overlap_proportion * float(bill.kwh) month_data['billed-import-net-gbp'] += \ overlap_proportion * float(bill.net) if imp_ss is None: imp_supplier_contract_name = None pc_code = exp_ss.pc_code else: if imp_supplier_contract is None: imp_supplier_contract_name = '' else: imp_supplier_contract_name = \ imp_supplier_contract.name pc_code = imp_ss.pc_code if exp_ss is None: exp_supplier_contract_name = None else: if exp_supplier_contract is None: exp_supplier_contract_name = '' else: exp_supplier_contract_name = \ exp_supplier_contract.name out = [ now, imp_mpan_core, imp_supplier_contract_name, exp_mpan_core, exp_supplier_contract_name, era_category, source_code, generator_type, sss.supply_name, sss.msn, pc_code, site.code, site.name, ','.join(sorted(list(era_associates))), month_finish] + [ month_data[t] for t in summary_titles] + [None] + \ make_bill_row(title_dict['mop'], mop_bill) + [None] + \ make_bill_row(title_dict['dc'], dc_bill) if imp_ss is None: out += [None] * (len(title_dict['imp-supplier']) + 1) else: out += [None] + make_bill_row( title_dict['imp-supplier'], imp_supplier_bill) if exp_ss is not None: out += [None] + make_bill_row( title_dict['exp-supplier'], exp_supplier_bill) for k, v in month_data.items(): site_month_data[k] += v era_rows.append(out) site_rows.append([ now, site.code, site.name, ', '.join( s.code for s in site.find_linked_sites( sess, month_start, month_finish)), month_finish, site_category, ', '.join(sorted(list(site_sources))), ', '.join(sorted(list(site_gen_types))) ] + [site_month_data[k] for k in summary_titles]) sess.rollback() write_spreadsheet(rf, compression, site_rows, era_rows) month_start += relativedelta(months=1) 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) except BaseException: msg = traceback.format_exc() sys.stderr.write(msg + '\n') site_rows.append(["Problem " + msg]) write_spreadsheet(rf, compression, site_rows, 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 _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(year, month, months, supply_id, user): tmp_file = sess = None try: sess = Session() supplies = sess.query(Supply).join(Era).distinct().options( joinedload(Supply.generator_type)) if supply_id is None: base_name = "supplies_monthly_duration_for_all_supplies_for_" + \ str(months) + "_to_" + str(year) + "_" + str(month) + ".csv" else: supply = Supply.get_by_id(sess, supply_id) supplies = supplies.filter(Supply.id == supply.id) base_name = "supplies_monthly_duration_for_" + str(supply.id) + \ "_" + str(months) + "_to_" + str(year) + "_" + str(month) + \ ".csv" running_name, finished_name = chellow.dloads.make_names( base_name, user) tmp_file = open(running_name, "w") caches = {} start_date = Datetime(year, month, 1, tzinfo=pytz.utc) - \ relativedelta(months=months-1) field_names = ('supply-name', 'source-code', 'generator-type', 'month', 'pc-code', 'msn', 'site-code', 'site-name', 'metering-type', 'import-mpan-core', 'metered-import-kwh', 'metered-import-net-gbp', 'metered-import-estimated-kwh', 'billed-import-kwh', 'billed-import-net-gbp', 'export-mpan-core', 'metered-export-kwh', 'metered-export-estimated-kwh', 'billed-export-kwh', 'billed-export-net-gbp', 'problem', 'timestamp') tmp_file.write('supply-id,' + ','.join(field_names) + '\n') forecast_date = chellow.computer.forecast_date() for i in range(months): month_start = start_date + relativedelta(months=i) month_finish = month_start + relativedelta(months=1) - HH for supply in supplies.filter( Era.start_date <= month_finish, or_(Era.finish_date == null(), Era.finish_date >= month_start)): generator_type = supply.generator_type if generator_type is None: generator_type = '' else: generator_type = generator_type.code source_code = supply.source.code eras = supply.find_eras(sess, month_start, month_finish) era = eras[-1] metering_type = era.meter_category site = sess.query(Site).join(SiteEra).filter( SiteEra.era == era, SiteEra.is_physical == true()).one() values = { 'supply-name': supply.name, 'source-code': source_code, 'generator-type': generator_type, 'month': hh_format(month_finish), 'pc-code': era.pc.code, 'msn': era.msn, 'site-code': site.code, 'site-name': site.name, 'metering-type': metering_type, 'problem': '' } tmp_file.write(str(supply.id) + ',') for is_import, pol_name in [(True, 'import'), (False, 'export')]: if is_import: mpan_core = era.imp_mpan_core else: mpan_core = era.exp_mpan_core values[pol_name + '-mpan-core'] = mpan_core kwh = 0 est_kwh = 0 if metering_type in ['hh', 'amr']: est_kwh = sess.query(HhDatum.value).join(Channel) \ .join(Era).filter( HhDatum.status == 'E', Era.supply_id == supply.id, Channel.channel_type == 'ACTIVE', Channel.imp_related == is_import, HhDatum.start_date >= month_start, HhDatum.start_date <= month_finish).first() if est_kwh is None: est_kwh = 0 else: est_kwh = est_kwh[0] if not (is_import and source_code in ('net', 'gen-net')): kwh_sum = sess.query( cast(func.sum(HhDatum.value), Float)).join(Channel).join(Era).filter( Era.supply_id == supply.id, Channel.channel_type == 'ACTIVE', Channel.imp_related == is_import, HhDatum.start_date >= month_start, HhDatum.start_date <= month_finish).one()[0] if kwh_sum is not None: kwh += kwh_sum values['metered-' + pol_name + '-estimated-kwh'] = est_kwh values['metered-' + pol_name + '-kwh'] = kwh values['metered-' + pol_name + '-net-gbp'] = 0 values['billed-' + pol_name + '-kwh'] = 0 values['billed-' + pol_name + '-net-gbp'] = 0 values['billed-' + pol_name + '-apportioned-kwh'] = 0 values['billed-' + pol_name + '-apportioned-net-gbp'] = 0 values['billed-' + pol_name + '-raw-kwh'] = 0 values['billed-' + pol_name + '-raw-net-gbp'] = 0 for bill in sess.query(Bill).filter( Bill.supply == supply, Bill.start_date <= month_finish, Bill.finish_date >= month_start): bill_start = bill.start_date bill_finish = bill.finish_date bill_duration = (bill_finish - bill_start).total_seconds() + 30 * 60 overlap_duration = (min(bill_finish, month_finish) - max( bill_start, month_start)).total_seconds() + 30 * 60 overlap_proportion = float(overlap_duration) / float( bill_duration) values['billed-import-net-gbp'] += \ overlap_proportion * float(bill.net) values['billed-import-kwh'] += \ overlap_proportion * float(bill.kwh) for era in eras: chunk_start = hh_max(era.start_date, month_start) chunk_finish = hh_min(era.finish_date, month_finish) import_mpan_core = era.imp_mpan_core if import_mpan_core is None: continue supplier_contract = era.imp_supplier_contract if source_code in ['net', 'gen-net', '3rd-party']: supply_source = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, True, caches) values['metered-import-kwh'] += sum( datum['msp-kwh'] for datum in supply_source.hh_data) import_vb_function = supply_source.contract_func( supplier_contract, 'virtual_bill') if import_vb_function is None: values['problem'] += "Can't find the " \ "virtual_bill function in the supplier " \ "contract. " else: import_vb_function(supply_source) values['metered-import-net-gbp'] += \ supply_source.supplier_bill['net-gbp'] supply_source.contract_func( era.dc_contract, 'virtual_bill')(supply_source) values['metered-import-net-gbp'] += \ supply_source.dc_bill['net-gbp'] mop_func = supply_source.contract_func( era.mop_contract, 'virtual_bill') if mop_func is None: values['problem'] += " MOP virtual_bill " \ "function can't be found." else: mop_func(supply_source) mop_bill = supply_source.mop_bill values['metered-import-net-gbp'] += \ mop_bill['net-gbp'] if len(mop_bill['problem']) > 0: values['problem'] += \ " MOP virtual bill problem: " + \ mop_bill['problem'] values['timestamp'] = int(time.time() * 1000) tmp_file.write(','.join('"' + str(values[name]) + '"' for name in field_names) + '\n') except BaseException: tmp_file.write(traceback.format_exc()) finally: if sess is not None: sess.close() tmp_file.close() os.rename(running_name, finished_name)
def content(year, supply_id, user): f = sess = None try: sess = Session() fname = ['crc', str(year), str(year + 1)] if supply_id is None: fname.append('all_supplies') else: fname.append('supply_' + str(supply_id)) running_name, finished_name = chellow.dloads.make_names( '_'.join(fname) + '.csv', user) f = open(running_name, mode='w', newline='') w = csv.writer(f, lineterminator='\n') ACTUAL_READ_TYPES = ['N', 'N3', 'C', 'X', 'CP'] w.writerow( ( 'Chellow Supply Id', 'Report Start', 'Report Finish', 'MPAN Core', 'Site Id', 'Site Name', 'From', 'To', 'NHH Breakdown', 'Actual HH Normal Days', 'Actual AMR Normal Days', 'Actual NHH Normal Days', 'Actual Unmetered Normal Days', 'Max HH Normal Days', 'Max AMR Normal Days', 'Max NHH Normal Days', 'Max Unmetered Normal Days', 'Total Actual Normal Days', 'Total Max Normal Days', 'Data Type', 'HH kWh', 'AMR kWh', 'NHH kWh', 'Unmetered kwh', 'HH Filled kWh', 'AMR Filled kWh', 'Total kWh', 'Note')) year_start = Datetime(year, 4, 1, tzinfo=pytz.utc) year_finish = year_start + relativedelta(years=1) - HH supplies = sess.query(Supply).join(Era).join(Source).filter( Source.code.in_(('net', 'gen-net')), Era.imp_mpan_core != null(), Era.start_date <= year_finish, or_( Era.finish_date == null(), Era.finish_date >= year_start)).distinct().order_by(Supply.id) if supply_id is not None: supply = Supply.get_by_id(sess, supply_id) supplies = supplies.filter(Supply.id == supply.id) meter_types = ('hh', 'amr', 'nhh', 'unmetered') for supply in supplies: total_kwh = dict([(mtype, 0) for mtype in meter_types]) filled_kwh = dict([(mtype, 0) for mtype in ('hh', 'amr')]) normal_days = dict([(mtype, 0) for mtype in meter_types]) max_normal_days = dict([(mtype, 0) for mtype in meter_types]) breakdown = '' eras = sess.query(Era).filter( Era.supply == supply, Era.start_date <= year_finish, or_( Era.finish_date == null(), Era.finish_date >= year_start)).order_by( Era.start_date).all() supply_from = hh_max(eras[0].start_date, year_start) supply_to = hh_min(eras[-1].finish_date, year_finish) for era in eras: meter_type = era.meter_category period_start = hh_max(era.start_date, year_start) period_finish = hh_min(era.finish_date, year_finish) max_normal_days[meter_type] += ( (period_finish - period_start).total_seconds() + 60 * 30) / (60 * 60 * 24) mpan_core = era.imp_mpan_core site = sess.query(Site).join(SiteEra).filter( SiteEra.is_physical == true(), SiteEra.era_id == era.id).one() if meter_type == 'nhh': read_list = [] read_keys = {} pairs = [] prior_pres_reads = iter( sess.query(RegisterRead).join(Bill).join(BillType) .join(RegisterRead.present_type).filter( RegisterRead.units == 0, ReadType.code.in_(ACTUAL_READ_TYPES), Bill.supply == supply, RegisterRead.present_date < period_start, BillType.code != 'W').order_by( RegisterRead.present_date.desc())) prior_prev_reads = iter( sess.query(RegisterRead).join(Bill).join(BillType) .join(RegisterRead.previous_type).filter( RegisterRead.units == 0, ReadType.code.in_(ACTUAL_READ_TYPES), Bill.supply == supply, RegisterRead.previous_date < period_start, BillType.code != 'W').order_by( RegisterRead.previous_date.desc())) next_pres_reads = iter( sess.query(RegisterRead).join(Bill).join(BillType) .join(RegisterRead.present_type).filter( RegisterRead.units == 0, ReadType.code.in_(ACTUAL_READ_TYPES), Bill.supply == supply, RegisterRead.present_date >= period_start, BillType.code != 'W').order_by( RegisterRead.present_date)) next_prev_reads = iter( sess.query(RegisterRead).join(Bill).join(BillType). join(RegisterRead.previous_type).filter( RegisterRead.units == 0, ReadType.code.in_(ACTUAL_READ_TYPES), Bill.supply == supply, RegisterRead.previous_date >= period_start, BillType.code != 'W').order_by( RegisterRead.previous_date)) for is_forwards in [False, True]: if is_forwards: pres_reads = next_pres_reads prev_reads = next_prev_reads read_list.reverse() else: pres_reads = prior_pres_reads prev_reads = prior_prev_reads prime_pres_read = None prime_prev_read = None while True: while prime_pres_read is None: try: pres_read = next(pres_reads) except StopIteration: break pres_date = pres_read.present_date pres_msn = pres_read.msn read_key = '_'.join([str(pres_date), pres_msn]) if read_key in read_keys: continue pres_bill = sess.query(Bill).join(BillType). \ filter( Bill.reads.any(), Bill.supply == supply, Bill.finish_date >= pres_read.bill.start_date, Bill.start_date <= pres_read.bill.finish_date, BillType.code != 'W').order_by( Bill.issue_date.desc(), BillType.code).first() if pres_bill != pres_read.bill: continue reads = dict( ( read.tpr.code, float(read.present_value) * float(read.coefficient)) for read in sess.query(RegisterRead). filter( RegisterRead.units == 0, RegisterRead.bill == pres_bill, RegisterRead.present_date == pres_date, RegisterRead.msn == pres_msn)) prime_pres_read = { 'date': pres_date, 'reads': reads, 'msn': pres_msn} read_keys[read_key] = None while prime_prev_read is None: try: prev_read = next(prev_reads) except StopIteration: break prev_date = prev_read.previous_date prev_msn = prev_read.msn read_key = '_'.join([str(prev_date), prev_msn]) if read_key in read_keys: continue prev_bill = sess.query(Bill).join(BillType). \ filter( Bill.reads.any(), Bill.supply_id == supply.id, Bill.finish_date >= prev_read.bill.start_date, Bill.start_date <= prev_read.bill.finish_date, BillType.code != 'W').order_by( Bill.issue_date.desc(), BillType.code).first() if prev_bill != prev_read.bill: continue reads = dict( ( read.tpr.code, float(read.previous_value) * float(read.coefficient)) for read in sess.query(RegisterRead). filter( RegisterRead.units == 0, RegisterRead.bill_id == prev_bill.id, RegisterRead.previous_date == prev_date, RegisterRead.msn == prev_msn)) prime_prev_read = { 'date': prev_date, 'reads': reads, 'msn': prev_msn} read_keys[read_key] = None if prime_pres_read is None and \ prime_prev_read is None: break elif prime_pres_read is None: read_list.append(prime_prev_read) prime_prev_read = None elif prime_prev_read is None: read_list.append(prime_pres_read) prime_pres_read = None else: if is_forwards: if prime_pres_read['date'] <= \ prime_prev_read['date']: read_list.append(prime_pres_read) prime_pres_read = None else: read_list.append(prime_prev_read) prime_prev_read = None else: if prime_prev_read['date'] >= \ prime_pres_read['date']: read_list.append(prime_prev_read) prime_prev_read = None else: read_list.append(prime_pres_read) prime_pres_read = None if len(read_list) > 1: if is_forwards: aft_read = read_list[-2] fore_read = read_list[-1] else: aft_read = read_list[-1] fore_read = read_list[-2] if aft_read['msn'] == fore_read['msn'] and \ set(aft_read['reads'].keys()) == \ set(fore_read['reads'].keys()): pair_start_date = aft_read['date'] + HH pair_finish_date = fore_read['date'] num_hh = ( ( pair_finish_date + HH - pair_start_date).total_seconds() ) / (30 * 60) tprs = {} for tpr_code, initial_val in \ aft_read['reads'].items(): end_val = fore_read['reads'][tpr_code] kwh = end_val - initial_val if kwh < 0: digits = int( math.log10(initial_val)) + 1 kwh = 10 ** digits + kwh tprs[tpr_code] = kwh / num_hh pairs.append( { 'start-date': pair_start_date, 'finish-date': pair_finish_date, 'tprs': tprs}) if len(pairs) > 0 and ( not is_forwards or ( is_forwards and read_list[-1]['date'] > period_finish)): break breakdown += 'read list - \n' + str(read_list) + "\n" if len(pairs) == 0: pairs.append( { 'start-date': period_start, 'finish-date': period_finish, 'tprs': {'00001': 0}}) else: for pair in pairs: pair_start = pair['start-date'] pair_finish = pair['finish-date'] if pair_start >= year_start and \ pair_finish <= year_finish: block_start = hh_max(pair_start, period_start) block_finish = hh_min( pair_finish, period_finish) if block_start <= block_finish: normal_days[meter_type] += ( ( block_finish - block_start ).total_seconds() + 60 * 30) / (60 * 60 * 24) # smooth for i in range(1, len(pairs)): pairs[i - 1]['finish-date'] = pairs[i]['start-date'] \ - HH # stretch if pairs[0]['start-date'] > period_start: pairs[0]['start-date'] = period_start if pairs[-1]['finish-date'] < period_finish: pairs[-1]['finish-date'] = period_finish # chop pairs = [ pair for pair in pairs if not pair['start-date'] > period_finish and not pair['finish-date'] < period_start] # squash if pairs[0]['start-date'] < period_start: pairs[0]['start-date'] = period_start if pairs[-1]['finish-date'] > period_finish: pairs[-1]['finish-date'] = period_finish for pair in pairs: pair_hhs = ( ( pair['finish-date'] - pair['start-date'] ).total_seconds() + 30 * 60) / (60 * 30) pair['pair_hhs'] = pair_hhs for tpr_code, pair_kwh in pair['tprs'].items(): total_kwh[meter_type] += pair_kwh * pair_hhs breakdown += 'pairs - \n' + str(pairs) elif meter_type in ('hh', 'amr'): period_kwhs = list( float(v[0]) for v in sess.query(HhDatum.value). join(Channel).filter( Channel.imp_related == true(), Channel.channel_type == 'ACTIVE', Channel.era == era, HhDatum.start_date >= period_start, HhDatum.start_date <= period_finish).order_by( HhDatum.id)) year_kwhs = list( float(v[0]) for v in sess.query(HhDatum.value). join(Channel).join(Era).filter( Channel.imp_related == true(), Channel.channel_type == 'ACTIVE', Era.supply == supply, HhDatum.start_date >= year_start, HhDatum.start_date <= year_finish).order_by( HhDatum.id)) period_sum_kwhs = sum(period_kwhs) year_sum_kwhs = sum(year_kwhs) period_len_kwhs = len(period_kwhs) year_len_kwhs = len(year_kwhs) total_kwh[meter_type] += period_sum_kwhs period_hhs = ( period_finish + HH - period_start ).total_seconds() / (60 * 30) if year_len_kwhs > 0: filled_kwh[meter_type] += year_sum_kwhs / \ year_len_kwhs * (period_hhs - period_len_kwhs) normal_days[meter_type] += sess.query( func.count(HhDatum.value)).join(Channel). \ filter( Channel.imp_related == true(), Channel.channel_type == 'ACTIVE', Channel.era == era, HhDatum.start_date >= period_start, HhDatum.start_date <= period_finish, HhDatum.status == 'A').one()[0] / 48 elif meter_type == 'unmetered': year_seconds = ( year_finish - year_start).total_seconds() + 60 * 30 period_seconds = ( period_finish - period_start).total_seconds() + 60 * 30 total_kwh[meter_type] += era.imp_sc * period_seconds / \ year_seconds normal_days[meter_type] += period_seconds / (60 * 60 * 24) # for full year 183 total_normal_days = sum(normal_days.values()) total_max_normal_days = sum(max_normal_days.values()) is_normal = total_normal_days / total_max_normal_days >= 183 / 365 w.writerow( [ supply.id, hh_format(year_start), hh_format(year_finish), mpan_core, site.code, site.name, hh_format(supply_from), hh_format(supply_to), breakdown] + [ normal_days[t] for t in meter_types] + [ max_normal_days[t] for t in meter_types] + [ total_normal_days, total_max_normal_days, "Actual" if is_normal else "Estimated"] + [total_kwh[t] for t in meter_types] + [filled_kwh[t] for t in ('hh', 'amr')] + [sum(total_kwh.values()) + sum(filled_kwh.values()), '']) # avoid a long running transaction sess.rollback() except BaseException: msg = traceback.format_exc() sys.stderr.write(msg + '\n') f.write("Problem " + msg) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def content(supply_id, start_date, finish_date, user): forecast_date = datetime.datetime.max.replace(tzinfo=pytz.utc) caches = {} f = sess = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'supplies_duration.csv', user) f = open(running_name, "w") f.write( ','.join( ( "Supply Id", "Supply Name", "Source", "Generator Type", "Site Ids", "Site Names", "From", "To", "PC", "MTC", "CoP", "SSC", "Normal Reads", "Type", "Import LLFC", "Import MPAN Core", "Import Supply Capacity", "Import Supplier", "Import Total MSP kWh", "Import Non-actual MSP kWh", "Import Total GSP kWh", "Import MD / kW", "Import MD Date", "Import MD / kVA", "Import Bad HHs", "Export LLFC", "Export MPAN Core", "Export Supply Capacity", "Export Supplier", "Export Total MSP kWh", "Export Non-actual MSP kWh", "Export GSP kWh", "Export MD / kW", "Export MD Date", "Export MD / kVA", "Export Bad HHs"))) supplies = sess.query(Supply).join(Era).filter( or_(Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date).order_by(Supply.id).distinct() if supply_id is not None: supplies = supplies.filter( Supply.id == Supply.get_by_id(sess, supply_id).id) for supply in supplies: site_codes = '' site_names = '' eras = supply.find_eras(sess, start_date, finish_date) era = eras[-1] for site_era in era.site_eras: site = site_era.site site_codes = site_codes + site.code + ', ' site_names = site_names + site.name + ', ' site_codes = site_codes[:-2] site_names = site_names[:-2] if supply.generator_type is None: generator_type = '' else: generator_type = supply.generator_type.code ssc = era.ssc ssc_code = '' if ssc is None else ssc.code prime_reads = set() for read, rdate in chain( sess.query( RegisterRead, RegisterRead.previous_date).join( RegisterRead.previous_type).join(Bill).join( BillType).filter( Bill.supply == supply, BillType.code != 'W', RegisterRead.previous_date >= start_date, RegisterRead.previous_date <= finish_date, ReadType.code.in_(NORMAL_READ_TYPES)), sess.query( RegisterRead, RegisterRead.present_date).join( RegisterRead.present_type).join(Bill).join( BillType).filter( Bill.supply == supply, BillType.code != 'W', RegisterRead.present_date >= start_date, RegisterRead.present_date <= finish_date, ReadType.code.in_(NORMAL_READ_TYPES))): prime_bill = sess.query(Bill).join(BillType).filter( Bill.supply == supply, Bill.start_date <= read.bill.finish_date, Bill.finish_date >= read.bill.start_date, Bill.reads.any()).order_by( Bill.issue_date.desc(), BillType.code).first() if prime_bill.id == read.bill.id: prime_reads.add( str(rdate) + "_" + read.msn) supply_type = era.make_meter_category() chunk_start = hh_max(eras[0].start_date, start_date) chunk_finish = hh_min(era.finish_date, finish_date) num_hh = int( (chunk_finish - (chunk_start - HH)).total_seconds() / (30 * 60)) f.write( '\n' + ','.join( ('"' + str(value) + '"') for value in [ supply.id, supply.name, supply.source.code, generator_type, site_codes, site_names, hh_format(start_date), hh_format(finish_date), era.pc.code, era.mtc.code, era.cop.code, ssc_code, len(prime_reads), supply_type]) + ',') f.write( mpan_bit( sess, supply, True, num_hh, eras, chunk_start, chunk_finish, forecast_date, caches) + "," + mpan_bit( sess, supply, False, num_hh, eras, chunk_start, chunk_finish, forecast_date, caches)) except: f.write(traceback.format_exc()) finally: sess.close() f.close() os.rename(running_name, finished_name)
def _process_g_bill_ids( sess, report_context, g_bill_ids, forecast_date, bill_titles, vbf, titles, csv_writer, ): g_bill_id = list(sorted(g_bill_ids))[0] g_bill_ids.remove(g_bill_id) g_bill = sess.query(GBill).filter(GBill.id == g_bill_id).one() problem = "" g_supply = g_bill.g_supply read_dict = defaultdict(set) for g_read in g_bill.g_reads: if not all(g_read.msn == era.msn for era in g_supply.find_g_eras( sess, g_read.prev_date, g_read.pres_date)): problem += ( f"The MSN {g_read.msn} of the register read {g_read.id} doesn't match " f"the MSN of all the relevant eras.") for dt, typ in [ (g_read.pres_date, g_read.pres_type), (g_read.prev_date, g_read.prev_type), ]: typ_set = read_dict[str(dt) + "-" + g_read.msn] typ_set.add(typ) if len(typ_set) > 1: problem += f" Reads taken on {dt} have differing read types." vals = { "covered_vat_gbp": Decimal("0.00"), "covered_net_gbp": Decimal("0.00"), "covered_gross_gbp": Decimal("0.00"), "covered_kwh": Decimal(0), "covered_start": g_bill.start_date, "covered_finish": g_bill.finish_date, "covered_bill_ids": [], } covered_primary_bill = None enlarged = True while enlarged: enlarged = False covered_bills = OrderedDict( (b.id, b) for b in sess.query(GBill).filter( GBill.g_supply == g_supply, GBill.start_date <= vals["covered_finish"], GBill.finish_date >= vals["covered_start"], ).order_by(GBill.issue_date.desc(), GBill.start_date)) num_covered = None while num_covered != len(covered_bills): num_covered = len(covered_bills) for a, b in combinations(tuple(covered_bills.values()), 2): if all(( a.start_date == b.start_date, a.finish_date == b.finish_date, a.kwh == -1 * b.kwh, a.net == -1 * b.net, a.vat == -1 * b.vat, a.gross == -1 * b.gross, )): for gb_id in a.id, b.id: del covered_bills[gb_id] if gb_id in g_bill_ids: g_bill_ids.remove(gb_id) break for covered_bill in covered_bills.values(): if covered_primary_bill is None and len(covered_bill.g_reads) > 0: covered_primary_bill = covered_bill if covered_bill.start_date < vals["covered_start"]: vals["covered_start"] = covered_bill.start_date enlarged = True break if covered_bill.finish_date > vals["covered_finish"]: vals["covered_finish"] = covered_bill.finish_date enlarged = True break if len(covered_bills) == 0: return for covered_bill in covered_bills.values(): if covered_bill.id in g_bill_ids: g_bill_ids.remove(covered_bill.id) vals["covered_bill_ids"].append(covered_bill.id) bdown = covered_bill.make_breakdown() vals["covered_kwh"] += covered_bill.kwh vals["covered_net_gbp"] += covered_bill.net vals["covered_vat_gbp"] += covered_bill.vat vals["covered_gross_gbp"] += covered_bill.gross for title in bill_titles: k = "covered_" + title v = bdown.get(title) if v is not None: if isinstance(v, list): if k not in vals: vals[k] = set() vals[k].update(set(v)) else: try: vals[k] += v except KeyError: vals[k] = v except TypeError: raise BadRequest( f"Problem with bill {g_bill.id} and key {k} and value {v} " f"for existing {vals[k]}") if title in ( "correction_factor", "calorific_value", "unit_code", "unit_factor", ): if k not in vals: vals[k] = set() for g_read in covered_bill.g_reads: if title in ("unit_code", "unit_factor"): g_unit = g_read.g_unit if title == "unit_code": v = g_unit.code else: v = g_unit.factor else: v = getattr(g_read, title) vals[k].add(v) for g_era in (sess.query(GEra).filter( GEra.g_supply == g_supply, GEra.start_date <= vals["covered_finish"], or_(GEra.finish_date == null(), GEra.finish_date >= vals["covered_start"]), ).distinct()): site = (sess.query(Site).join(SiteGEra).filter( SiteGEra.is_physical == true(), SiteGEra.g_era == g_era).one()) chunk_start = hh_max(vals["covered_start"], g_era.start_date) chunk_finish = hh_min(vals["covered_finish"], g_era.finish_date) data_source = chellow.g_engine.GDataSource( sess, chunk_start, chunk_finish, forecast_date, g_era, report_context, covered_primary_bill, ) vbf(data_source) for k, v in data_source.bill.items(): vk = "virtual_" + k try: if isinstance(v, set): vals[vk].update(v) else: vals[vk] += v except KeyError: vals[vk] = v except TypeError as detail: raise BadRequest(f"For key {vk} and value {v}. {detail}") if g_bill.id not in covered_bills.keys(): g_bill = covered_bills[sorted(covered_bills.keys())[0]] vals["batch"] = g_bill.g_batch.reference vals["bill_reference"] = g_bill.reference vals["bill_type"] = g_bill.bill_type.code vals["bill_start_date"] = g_bill.start_date vals["bill_finish_date"] = g_bill.finish_date vals["mprn"] = g_supply.mprn vals["supply_name"] = g_supply.name vals["site_code"] = site.code vals["site_name"] = site.name for k, v in vals.items(): if k == "covered_bill_ids": vals[k] = " | ".join(str(b) for b in v) else: vals[k] = csv_make_val(v) for i, title in enumerate(titles): if title.startswith("difference_"): try: covered_val = float(vals[titles[i - 2]]) virtual_val = float(vals[titles[i - 1]]) vals[title] = covered_val - virtual_val except KeyError: vals[title] = None csv_writer.writerow([(vals.get(k) if vals.get(k) is not None else "") for k in titles])
def mpan_bit( sess, supply, is_import, num_hh, eras, chunk_start, chunk_finish, forecast_date, caches): mpan_core_str = '' llfc_code = '' sc_str = '' supplier_contract_name = '' gsp_kwh = '' for era in eras: mpan_core = era.imp_mpan_core if is_import else era.exp_mpan_core if mpan_core is None: continue mpan_core_str = mpan_core if is_import: supplier_contract_name = era.imp_supplier_contract.name llfc = era.imp_llfc sc = era.imp_sc else: supplier_contract_name = era.exp_supplier_contract.name llfc = era.exp_llfc sc = era.exp_sc llfc_code = llfc.code sc_str = str(sc) if llfc.is_import and era.pc.code == '00' and \ supply.source.code not in ('gen') and \ supply.dno_contract.name != '99': if gsp_kwh == '': gsp_kwh = 0 block_start = hh_max(era.start_date, chunk_start) block_finish = hh_min(era.finish_date, chunk_finish) supply_source = chellow.computer.SupplySource( sess, block_start, block_finish, forecast_date, era, is_import, caches) chellow.duos.duos_vb(supply_source) gsp_kwh += sum(datum['gsp-kwh'] for datum in supply_source.hh_data) md = 0 sum_kwh = 0 non_actual = 0 date_at_md = None kvarh_at_md = None num_na = 0 for datum in sess.query(HhDatum).join(Channel).join(Era).filter( Era.supply == supply, Channel.imp_related == is_import, Channel.channel_type == 'ACTIVE', HhDatum.start_date >= chunk_start, HhDatum.start_date <= chunk_finish).order_by(HhDatum.id): hh_value = float(datum.value) hh_status = datum.status if hh_value > md: md = hh_value date_at_md = datum.start_date kvarh_at_md = sess.query( cast(func.max(HhDatum.value), Float)).join( Channel).join(Era).filter( Era.supply == supply, Channel.imp_related == is_import, Channel.channel_type != 'ACTIVE', HhDatum.start_date == date_at_md).one()[0] sum_kwh += hh_value if hh_status != 'A': non_actual += hh_value num_na += 1 kw_at_md = md * 2 if kvarh_at_md is None: kva_at_md = 'None' else: kva_at_md = (kw_at_md ** 2 + (kvarh_at_md * 2) ** 2) ** 0.5 num_bad = num_hh - sess.query(HhDatum).join(Channel).join(Era).filter( Era.supply == supply, Channel.imp_related == is_import, Channel.channel_type == 'ACTIVE', HhDatum.start_date >= chunk_start, HhDatum.start_date <= chunk_finish).count() + num_na date_at_md_str = '' if date_at_md is None else hh_format(date_at_md) return ','.join(str(val) for val in [ llfc_code, mpan_core_str, sc_str, supplier_contract_name, sum_kwh, non_actual, gsp_kwh, kw_at_md, date_at_md_str, kva_at_md, num_bad])
def content(g_supply_id, file_name, start_date, finish_date, user): caches = {} sess = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'g_supply_virtual_bill_' + str(g_supply_id) + '.csv', user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') g_supply = GSupply.get_by_id(sess, g_supply_id) forecast_dt = forecast_date() prev_titles = None for g_era in sess.query(GEra).filter( GEra.g_supply == g_supply, GEra.start_date < finish_date, or_(GEra.finish_date == null(), GEra.finish_date > start_date)).order_by(GEra.start_date): chunk_start = hh_max(g_era.start_date, start_date) chunk_finish = hh_min(g_era.finish_date, finish_date) site = sess.query(Site).join(SiteGEra).filter( SiteGEra.g_era == g_era, SiteGEra.is_physical == true()).one() ds = GDataSource(sess, chunk_start, chunk_finish, forecast_dt, g_era, caches, None) titles = [ 'MPRN', 'Site Code', 'Site Name', 'Account', 'From', 'To', '' ] output_line = [ g_supply.mprn, site.code, site.name, ds.account, hh_format(ds.start_date), hh_format(ds.finish_date), '' ] contract_titles = g_contract_func(caches, g_era.g_contract, 'virtual_bill_titles')() titles.extend(contract_titles) g_contract_func(caches, g_era.g_contract, 'virtual_bill')(ds) bill = ds.bill for title in contract_titles: if title in bill: output_line.append(csv_make_val(bill[title])) del bill[title] else: output_line.append('') for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) if titles != prev_titles: prev_titles = titles writer.writerow([str(v) for v in titles]) writer.writerow(output_line) except BadRequest as e: writer.writerow(["Problem: " + e.description]) except BaseException: writer.writerow([traceback.format_exc()]) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def hh(data_source, rate_period='monthly', est_kw=None): for hh in (h for h in data_source.hh_data if h['ct-is-month-end']): hh_start = hh['start-date'] month_start = utc_datetime(hh_start.year, hh_start.month) month_finish = month_start + relativedelta(months=1) - HH financial_year_start = month_start while financial_year_start.month != 4: financial_year_start -= relativedelta(months=1) last_financial_year_start = financial_year_start - relativedelta( years=1) financial_year_finish = financial_year_start + relativedelta( years=1) - HH est_triad_kws = [] earliest_triad = None for dt in get_file_rates( data_source.caches, 'triad_dates', last_financial_year_start)['triad_dates']: triad_hh = None earliest_triad = hh_min(earliest_triad, dt) try: ds = next( data_source.get_data_sources(dt, dt, financial_year_start)) chellow.duos.duos_vb(ds) triad_hh = ds.hh_data[0] while dt < financial_year_start: dt += relativedelta(years=1) for ds in data_source.get_data_sources( dt, dt, financial_year_start): chellow.duos.duos_vb(ds) datum = ds.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 data_source.site is None: era = data_source.supply.find_era_at( data_source.sess, earliest_triad) if era is None or era.get_channel( data_source.sess, data_source.is_import, 'ACTIVE') is None: if est_kw is not None: est_triad_kw = est_kw else: est_triad_kw = 0.85 * max( datum['msp-kwh'] for datum in data_source.hh_data) * 2 for est_datum in est_triad_kws: est_datum['msp-kw'] = est_triad_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 data_source.llfc.is_import else 'export' gsp_group_code = data_source.gsp_group_code rate = float( get_file_rates( data_source.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 ds in data_source.get_data_sources(month_start, month_finish): for h in ds.hh_data: if h['utc-decimal-hour'] == 0: est_intervals += 1 hh['triad-estimate-days'] = est_intervals hh['triad-estimate-gbp'] = est_triad_gbp / total_intervals * \ est_intervals if month_start.month == 3: triad_kws = [] for t_date in get_file_rates( data_source.caches, 'triad_dates', month_start)['triad_dates']: try: ds = next(data_source.get_data_sources(t_date, t_date)) if data_source.supplier_contract is None or \ ds.supplier_contract == \ data_source.supplier_contract: chellow.duos.duos_vb(ds) thh = ds.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: ds = next(data_source.get_data_sources(t_date, t_date)) if data_source.supplier_contract is None or \ ds.supplier_contract == \ data_source.supplier_contract: chellow.duos.duos_vb(ds) thh['laf'] = ds.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 data_source.llfc.is_import else 'export' gsp_group_code = data_source.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 = start_date.month if start_month < 4: start_month += 12 if finish_date is None: finish_month = financial_year_finish.month else: finish_month = finish_date.month if finish_month < 4: finish_month += 12 rt = get_file_rates( data_source.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 = data_source.supply.find_era_at( data_source.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 = data_source.supply.find_era_at(data_source.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(start_date, finish_date, g_contract_id, user): report_context = {} sess = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( "gas_virtual_bills.csv", user ) f = open(running_name, mode="w", newline="") writer = csv.writer(f, lineterminator="\n") g_contract = GContract.get_by_id(sess, g_contract_id) forecast_dt = forecast_date() month_start = utc_datetime(start_date.year, start_date.month, 1) month_finish = month_start + relativedelta(months=1) - HH bill_titles = contract_func(report_context, g_contract, "virtual_bill_titles")() writer.writerow( ["MPRN", "Site Code", "Site Name", "Account", "From", "To"] + bill_titles ) while not month_start > finish_date: period_start = hh_max(start_date, month_start) period_finish = hh_min(finish_date, month_finish) for g_era in ( sess.query(GEra) .distinct() .filter( GEra.g_contract == g_contract, GEra.start_date <= period_finish, or_(GEra.finish_date == null(), GEra.finish_date >= period_start), ) ): chunk_start = hh_max(g_era.start_date, period_start) chunk_finish = hh_min(g_era.finish_date, period_finish) data_source = GDataSource( sess, chunk_start, chunk_finish, forecast_dt, g_era, report_context, None, ) site = ( sess.query(Site) .join(SiteGEra) .filter(SiteGEra.g_era == g_era, SiteGEra.is_physical == true()) .one() ) vals = [ data_source.mprn, site.code, site.name, data_source.account, hh_format(data_source.start_date), hh_format(data_source.finish_date), ] contract_func(report_context, g_contract, "virtual_bill")(data_source) bill = data_source.bill for title in bill_titles: if title in bill: val = make_val(bill[title]) del bill[title] else: val = "" vals.append(val) for k in sorted(bill.keys()): vals.append(k) vals.append(str(bill[k])) writer.writerow(vals) month_start += relativedelta(months=1) month_finish = month_start + relativedelta(months=1) - HH except BadRequest as e: writer.writerow(["Problem: " + e.description]) 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(batch_id, bill_id, user): caches = {} tmp_file = sess = bill = None forecast_date = Datetime.max.replace(tzinfo=pytz.utc) try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'bill_check.csv', user) tmp_file = open(running_name, mode='w', newline='') writer = csv.writer(tmp_file, lineterminator='\n') bills = sess.query(Bill).options( joinedload(Bill.supply), subqueryload(Bill.reads).joinedload(RegisterRead.present_type), subqueryload(Bill.reads).joinedload(RegisterRead.previous_type)) if batch_id is not None: batch = Batch.get_by_id(sess, batch_id) bills = bills.filter(Bill.batch == batch).order_by(Bill.reference) elif bill_id is not None: bill = Bill.get_by_id(sess, bill_id) bills = bills.filter(Bill.id == bill.id) batch = bill.batch contract = batch.contract market_role_code = contract.market_role.code vbf = chellow.computer.contract_func(caches, contract, 'virtual_bill') if vbf is None: raise BadRequest( 'The contract ' + contract.name + " doesn't have a function virtual_bill.") virtual_bill_titles_func = chellow.computer.contract_func( caches, contract, 'virtual_bill_titles') if virtual_bill_titles_func is None: raise BadRequest( 'The contract ' + contract.name + " doesn't have a function virtual_bill_titles.") virtual_bill_titles = virtual_bill_titles_func() titles = [ 'batch', 'bill-reference', 'bill-type', 'bill-kwh', 'bill-net-gbp', 'bill-vat-gbp', 'bill-start-date', 'bill-finish-date', 'bill-mpan-core', 'site-code', 'site-name', 'covered-from', 'covered-to', 'covered-bills', 'metered-kwh'] for t in virtual_bill_titles: titles.append('covered-' + t) titles.append('virtual-' + t) if t.endswith('-gbp'): titles.append('difference-' + t) writer.writerow(titles) for bill in bills: problem = '' supply = bill.supply read_dict = {} for read in bill.reads: gen_start = read.present_date.replace(hour=0).replace(minute=0) gen_finish = gen_start + relativedelta(days=1) - HH msn_match = False read_msn = read.msn for read_era in supply.find_eras(sess, gen_start, gen_finish): if read_msn == read_era.msn: msn_match = True break if not msn_match: problem += "The MSN " + read_msn + \ " of the register read " + str(read.id) + \ " doesn't match the MSN of the era." for dt, type in [ (read.present_date, read.present_type), (read.previous_date, read.previous_type)]: key = str(dt) + "-" + read.msn try: if type != read_dict[key]: problem += " Reads taken on " + str(dt) + \ " have differing read types." except KeyError: read_dict[key] = type bill_start = bill.start_date bill_finish = bill.finish_date era = supply.find_era_at(sess, bill_finish) if era is None: raise BadRequest( "Extraordinary! There isn't an era for the bill " + str(bill.id) + ".") values = [ batch.reference, bill.reference, bill.bill_type.code, bill.kwh, bill.net, bill.vat, hh_format(bill_start), hh_format(bill_finish), era.imp_mpan_core] covered_start = bill_start covered_finish = bill_finish covered_bdown = {'sum-msp-kwh': 0, 'net-gbp': 0, 'vat-gbp': 0} enlarged = True while enlarged: enlarged = False covered_bills = OrderedDict( (b.id, b) for b in sess.query(Bill).join(Batch). join(Contract).join(MarketRole). filter( Bill.supply == supply, Bill.start_date <= covered_finish, Bill.finish_date >= covered_start, MarketRole.code == market_role_code).order_by( Bill.start_date, Bill.issue_date)) while True: to_del = None for a, b in combinations(covered_bills.values(), 2): if all( ( a.start_date == b.start_date, a.finish_date == b.finish_date, a.kwh == -1 * b.kwh, a.net == -1 * b.net, a.vat == -1 * b.vat, a.gross == -1 * b.gross)): to_del = (a.id, b.id) break if to_del is None: break else: for k in to_del: del covered_bills[k] for covered_bill in covered_bills.values(): if covered_bill.start_date < covered_start: covered_start = covered_bill.start_date enlarged = True break if covered_bill.finish_date > covered_finish: covered_finish = covered_bill.finish_date enlarged = True break if bill.id not in covered_bills: continue primary_covered_bill = None for covered_bill in covered_bills.values(): covered_bdown['net-gbp'] += float(covered_bill.net) covered_bdown['vat-gbp'] += float(covered_bill.vat) covered_bdown['sum-msp-kwh'] += float(covered_bill.kwh) if len(covered_bill.breakdown) > 0: covered_rates = defaultdict(set) for k, v in eval(covered_bill.breakdown, {}).items(): if k.endswith('rate'): covered_rates[k].add(v) elif k != 'raw-lines': try: covered_bdown[k] += v except KeyError: covered_bdown[k] = v except TypeError as detail: raise BadRequest( "For key " + str(k) + " the value " + str(v) + " can't be added to the existing value " + str(covered_bdown[k]) + ". " + str(detail)) for k, v in covered_rates.items(): covered_bdown[k] = v.pop() if len(v) == 1 else None if primary_covered_bill is None or ( ( covered_bill.finish_date - covered_bill.start_date) > ( primary_covered_bill.finish_date - primary_covered_bill.start_date)): primary_covered_bill = covered_bill virtual_bill = {} metered_kwh = 0 for era in sess.query(Era).filter( Era.supply == supply, Era.imp_mpan_core != null(), Era.start_date <= covered_finish, or_( Era.finish_date == null(), Era.finish_date >= covered_start), or_( Era.mop_contract == contract, Era.hhdc_contract == contract, Era.imp_supplier_contract == contract, Era.exp_supplier_contract == contract)).distinct(): site = sess.query(Site).join(SiteEra).filter( SiteEra.is_physical == true(), SiteEra.era == era).one() chunk_start = hh_max(covered_start, era.start_date) chunk_finish = hh_min(covered_finish, era.finish_date) data_source = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, True, caches, primary_covered_bill) if data_source.measurement_type == 'hh': metered_kwh += sum( h['msp-kwh'] for h in data_source.hh_data) else: ds = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, True, caches) metered_kwh += sum(h['msp-kwh'] for h in ds.hh_data) vbf(data_source) if market_role_code == 'X': vb = data_source.supplier_bill elif market_role_code == 'C': vb = data_source.dc_bill elif market_role_code == 'M': vb = data_source.mop_bill else: raise BadRequest("Odd market role.") for k, v in vb.items(): try: if isinstance(v, set): virtual_bill[k].update(v) else: virtual_bill[k] += v except KeyError: virtual_bill[k] = v except TypeError as detail: raise BadRequest( "For key " + str(k) + " and value " + str(v) + ". " + str(detail)) values += [ site.code, site.name, hh_format(covered_start), hh_format(covered_finish), ':'.join( str(i).replace(',', '') for i in covered_bills.keys()), metered_kwh] for title in virtual_bill_titles: try: cov_val = covered_bdown[title] values.append(cov_val) del covered_bdown[title] except KeyError: cov_val = None values.append('') try: virt_val = csv_make_val(virtual_bill[title]) values.append(virt_val) del virtual_bill[title] except KeyError: virt_val = None values.append('') if title.endswith('-gbp'): if isinstance(virt_val, (int, float)): if isinstance(cov_val, (int, float)): values.append(cov_val - virt_val) else: values.append(0 - virt_val) else: values.append('') for title in sorted(virtual_bill.keys()): virt_val = csv_make_val(virtual_bill[title]) values += ['virtual-' + title, virt_val] if title in covered_bdown: values += ['covered-' + title, covered_bdown[title]] else: values += ['', ''] writer.writerow(values) except BadRequest as e: if bill is None: prefix = "Problem: " else: prefix = "Problem with bill " + str(bill.id) + ':' tmp_file.write(prefix + e.description) except: msg = traceback.format_exc() sys.stderr.write(msg + '\n') tmp_file.write("Problem " + msg) finally: if sess is not None: sess.close() tmp_file.close() os.rename(running_name, finished_name)