def content(contract_id, end_year, end_month, months, user): caches = {} sess = f = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( "displaced.csv", user) f = open(running_name, mode="w", newline="") writer = csv.writer(f, lineterminator="\n") titles = [ "Site Code", "Site Name", "Associated Site Ids", "From", "To", "Gen Types", "CHP kWh", "LM kWh", "Turbine kWh", "PV kWh", ] month_list = list( c_months_u(finish_year=end_year, finish_month=end_month, months=months)) start_date, finish_date = month_list[0][0], month_list[-1][-1] forecast_date = chellow.computer.forecast_date() contract = Contract.get_supplier_by_id(sess, contract_id) sites = (sess.query(Site).join(SiteEra).join(Era).join(Supply).join( Source).filter( or_(Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date, or_(Source.code.in_(("gen", "gen-net")), Era.exp_mpan_core != null()), ).distinct()) bill_titles = chellow.computer.contract_func( caches, contract, "displaced_virtual_bill_titles")() for title in bill_titles: if title == "total-msp-kwh": title = "total-displaced-msp-kwh" titles.append(title) writer.writerow(titles) for site in sites: for month_start, month_finish in month_list: displaced_era = chellow.computer.displaced_era( sess, caches, site, month_start, month_finish, forecast_date) if displaced_era is None: continue supplier_contract = displaced_era.imp_supplier_contract if contract is not None and contract != supplier_contract: continue linked_sites = set() generator_types = set() for era in (sess.query(Era).join(SiteEra).filter( SiteEra.site == site, Era.start_date <= month_finish, or_(Era.finish_date == null(), Era.finish_date >= month_start), )): for site_era in era.site_eras: if site_era.site != site: linked_sites.add(site_era.site.code) supply = era.supply if supply.generator_type is not None: generator_types.add(supply.generator_type.code) supply_ids = set() for era in (sess.query(Era).join(SiteEra).filter( SiteEra.site == site, SiteEra.is_physical, Era.start_date <= month_finish, or_(Era.finish_date == null(), Era.finish_date >= month_start), )): supply_ids.add(era.supply.id) vals = [ site.code, site.name, ", ".join(list(linked_sites)), hh_format(month_start), hh_format(month_finish), ", ".join(list(generator_types)), ] total_gen_breakdown = {} results = iter( sess.execute( "select supply.id, hh_datum.value, " "hh_datum.start_date, channel.imp_related, " "source.code, generator_type.code as " "gen_type_code from hh_datum, channel, source, " "era, supply left outer join generator_type on " "supply.generator_type_id = generator_type.id " "where hh_datum.channel_id = channel.id and " "channel.era_id = era.id and era.supply_id = " "supply.id and supply.source_id = source.id and " "channel.channel_type = 'ACTIVE' and not " "(source.code = 'net' and channel.imp_related " "is true) and hh_datum.start_date >= " ":month_start and hh_datum.start_date " "<= :month_finish and " "supply.id = any(:supply_ids) order " "by hh_datum.start_date, supply.id", params={ "month_start": month_start, "month_finish": month_finish, "supply_ids": sorted(list(supply_ids)), }, )) ( sup_id, hh_val, hh_start, imp_related, source_code, gen_type_code, ) = next(results, (None, None, None, None, None, None)) for hh_date in hh_range(caches, month_start, month_finish): gen_breakdown = {} exported = 0 while hh_start == hh_date: if not imp_related and source_code in ("net", "gen-net"): exported += hh_val if (imp_related and source_code == "gen") or ( not imp_related and source_code == "gen-net"): gen_breakdown[gen_type_code] = ( gen_breakdown.setdefault(gen_type_code, 0) + hh_val) if (not imp_related and source_code == "gen") or ( imp_related and source_code == "gen-net"): gen_breakdown[gen_type_code] = ( gen_breakdown.setdefault(gen_type_code, 0) - hh_val) ( sup_id, hh_val, hh_start, imp_related, source_code, gen_type_code, ) = next(results, (None, None, None, None, None, None)) displaced = sum(gen_breakdown.values()) - exported added_so_far = 0 for key in sorted(gen_breakdown.keys()): kwh = gen_breakdown[key] if displaced < 0: total_gen_breakdown[key] = ( total_gen_breakdown.get(key, 0) + kwh) else: if kwh + added_so_far > displaced: total_gen_breakdown[key] = ( total_gen_breakdown.get(key, 0) + displaced - added_so_far) break else: total_gen_breakdown[key] = ( total_gen_breakdown.get(key, 0) + kwh) added_so_far += kwh for title in ["chp", "lm", "turb", "pv"]: vals.append(str(total_gen_breakdown.get(title, ""))) site_ds = chellow.computer.SiteSource( sess, site, month_start, month_finish, forecast_date, caches, displaced_era, ) disp_func = chellow.computer.contract_func( caches, supplier_contract, "displaced_virtual_bill") disp_func(site_ds) bill = site_ds.supplier_bill for title in bill_titles: if title in bill: vals.append(to_val(bill[title])) del bill[title] else: vals.append("") for k in sorted(bill.keys()): vals.append(k) vals.append(str(bill[k])) writer.writerow(vals) except BaseException: msg = traceback.format_exc() sys.stderr.write(msg) writer.writerow([msg]) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def content(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', None)() writer.writerow( ['MPAN Core', 'Site Code', 'Site Name', 'Account', 'From', 'To'] + bill_titles) while not month_start > finish_date: period_start = start_date \ if month_start < start_date else month_start if month_finish > finish_date: period_finish = finish_date else: period_finish = month_finish for era in sess.query(Era).distinct().filter( or_( Era.imp_supplier_contract_id == contract.id, Era.exp_supplier_contract_id == contract.id), Era.start_date <= period_finish, or_( Era.finish_date == null(), Era.finish_date >= period_start)): era_start = era.start_date if period_start < era_start: chunk_start = era_start else: chunk_start = period_start era_finish = era.finish_date if hh_after(period_finish, era_finish): chunk_finish = era_finish else: chunk_finish = period_finish 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, None, 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)] contract_func( caches, contract, 'virtual_bill', None)(data_source) bill = data_source.supplier_bill for title in bill_titles: if title in bill: val = str(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(contract_id, end_year, end_month, months, user): caches = {} sess = f = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'displaced.csv', user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') titles = [ 'Site Code', 'Site Name', 'Associated Site Ids', 'From', 'To', 'Gen Types', 'CHP kWh', 'LM kWh', 'Turbine kWh', 'PV kWh'] 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() contract = Contract.get_supplier_by_id(sess, contract_id) sites = sess.query(Site).join(SiteEra).join(Era).join(Supply). \ join(Source).filter( or_(Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date, or_( Source.code.in_(('gen', 'gen-net')), Era.exp_mpan_core != null())).distinct() bill_titles = chellow.computer.contract_func( caches, contract, 'displaced_virtual_bill_titles', None)() for title in bill_titles: if title == 'total-msp-kwh': title = 'total-displaced-msp-kwh' titles.append(title) writer.writerow(titles) for site in sites: month_start = start_date month_finish = month_start + relativedelta(months=1) - HH while not month_finish > finish_date: for site_group in site.groups( sess, month_start, month_finish, True): if site_group.start_date > month_start: chunk_start = site_group.start_date else: chunk_start = month_start if site_group.finish_date > month_finish: chunk_finish = month_finish else: chunk_finish = site_group.finish_date displaced_era = chellow.computer.displaced_era( sess, site_group, chunk_start, chunk_finish) if displaced_era is None: continue supplier_contract = displaced_era.imp_supplier_contract if contract is not None and contract != supplier_contract: continue linked_sites = ','.join( a_site.code for a_site in site_group.sites if not a_site == site) generator_types = ' '.join( sorted( [ supply.generator_type.code for supply in site_group.supplies if supply.generator_type is not None])) vals = [ site.code, site.name, linked_sites, hh_format(chunk_start), hh_format(chunk_finish), generator_types] total_gen_breakdown = {} results = iter( sess.execute( "select supply.id, hh_datum.value, " "hh_datum.start_date, channel.imp_related, " "source.code, generator_type.code as " "gen_type_code from hh_datum, channel, source, " "era, supply left outer join generator_type on " "supply.generator_type_id = generator_type.id " "where hh_datum.channel_id = channel.id and " "channel.era_id = era.id and era.supply_id = " "supply.id and supply.source_id = source.id and " "channel.channel_type = 'ACTIVE' and not " "(source.code = 'net' and channel.imp_related " "is true) and hh_datum.start_date >= " ":chunk_start and hh_datum.start_date " "<= :chunk_finish and " "supply.id = any(:supply_ids) order " "by hh_datum.start_date, supply.id", params={ 'chunk_start': chunk_start, 'chunk_finish': chunk_finish, 'supply_ids': [ s.id for s in site_group.supplies]})) ( sup_id, hh_val, hh_start, imp_related, source_code, gen_type_code) = next( results, (None, None, None, None, None, None)) hh_date = chunk_start while hh_date <= finish_date: gen_breakdown = {} exported = 0 while hh_start == hh_date: if not imp_related and source_code in ( 'net', 'gen-net'): exported += hh_val if (imp_related and source_code == 'gen') or \ (not imp_related and source_code == 'gen-net'): gen_breakdown[gen_type_code] = \ gen_breakdown.setdefault( gen_type_code, 0) + hh_val if ( not imp_related and source_code == 'gen') or ( imp_related and source_code == 'gen-net'): gen_breakdown[gen_type_code] = \ gen_breakdown.setdefault( gen_type_code, 0) - hh_val ( sup_id, hh_val, hh_start, imp_related, source_code, gen_type_code) = next( results, (None, None, None, None, None, None)) displaced = sum(gen_breakdown.values()) - exported added_so_far = 0 for key in sorted(gen_breakdown.keys()): kwh = gen_breakdown[key] if kwh + added_so_far > displaced: total_gen_breakdown[key] = \ total_gen_breakdown.get(key, 0) + \ displaced - added_so_far break else: total_gen_breakdown[key] = \ total_gen_breakdown.get(key, 0) + kwh added_so_far += kwh hh_date += HH for title in ['chp', 'lm', 'turb', 'pv']: vals.append(str(total_gen_breakdown.get(title, ''))) site_ds = chellow.computer.SiteSource( sess, site, chunk_start, chunk_finish, forecast_date, None, caches, displaced_era) disp_func = chellow.computer.contract_func( caches, supplier_contract, 'displaced_virtual_bill', None) disp_func(site_ds) bill = site_ds.supplier_bill for title in bill_titles: if title in bill: val = bill[title] if isinstance(val, Datetime): val = hh_format(val) else: val = str(val) vals.append(val) del bill[title] else: vals.append('') 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: 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): now = Datetime.now(pytz.utc) report_context = {} future_funcs = {} report_context['future_funcs'] = future_funcs 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) for contract in sess.query(Contract).join(MarketRole).filter( MarketRole.code == 'Z'): try: props = scenario_props[contract.name] except KeyError: continue try: rate_start = props['start_date'] except KeyError: raise BadRequest( "In " + scenario_contract.name + " for the rate " + contract.name + " the start_date is missing.") if rate_start is not None: rate_start = rate_start.replace(tzinfo=pytz.utc) lib = importlib.import_module('chellow.' + contract.name) if hasattr(lib, 'create_future_func'): future_funcs[contract.id] = { 'start_date': rate_start, 'func': lib.create_future_func( props['multiplier'], props['constant'])} start_date = scenario_props['scenario_start'] if start_date is None: start_date = Datetime( now.year, now.month, 1, tzinfo=pytz.utc) else: start_date = start_date.replace(tzinfo=pytz.utc) 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 'kwh_start' in scenario_props: kwh_start = scenario_props['kwh_start'] else: kwh_start = None if kwh_start is None: kwh_start = chellow.computer.forecast_date() else: kwh_start = kwh_start.replace(tzinfo=pytz.utc) sites = sess.query(Site).join(SiteEra).join(Era).filter( Era.start_date <= finish_date, or_( Era.finish_date == null(), Era.finish_date >= start_date)).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.filter(Era.supply == supply) running_name, finished_name = chellow.dloads.make_names( '_'.join(base_name) + '.ods', user) rf = open(running_name, "wb") f = odswriter.writer(rf, '1.1') group_tab = f.new_sheet("Site Level") sup_tab = f.new_sheet("Supply Level") changes = defaultdict(list, {}) try: kw_changes = scenario_props['kw_changes'] except KeyError: kw_changes = '' for row in csv.reader(io.StringIO(kw_changes)): if len(''.join(row).strip()) == 0: continue if len(row) != 4: raise BadRequest( "Can't interpret the row " + str(row) + " it should be of " "the form SITE_CODE, USED / GENERATED, DATE, MULTIPLIER") site_code, typ, date_str, kw_str = row date = Datetime.strptime(date_str.strip(), "%Y-%m-%d").replace( tzinfo=pytz.utc) changes[site_code.strip()].append( { 'type': typ.strip(), 'date': date, 'multiplier': float(kw_str)}) sup_header_titles = [ 'imp-mpan-core', 'exp-mpan-core', 'metering-type', 'source', 'generator-type', 'supply-name', 'msn', 'pc', 'site-id', 'site-name', 'associated-site-ids', 'month'] site_header_titles = [ '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.hhdc_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 <= start_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', None) 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) sup_tab.writerow( sup_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']]) group_tab.writerow(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_changes = changes[site.code] site_associates = set() site_category = None site_sources = set() site_gen_types = set() site_month_data = defaultdict(int) for group in site.groups( sess, month_start, month_finish, False): site_associates.update( set( s.code for s in group.sites if s.code != site.code)) for cand_supply in group.supplies: site_sources.add(cand_supply.source.code) if cand_supply.generator_type is not None: site_gen_types.add(cand_supply.generator_type.code) for cand_era in sess.query(Era).filter( Era.supply == cand_supply, Era.start_date <= group.finish_date, or_( Era.finish_date == null(), Era.finish_date >= group.start_date)). \ options( joinedload(Era.channels), joinedload(Era.pc), joinedload(Era.mtc).joinedload( Mtc.meter_type)): if site_category != 'hh': if cand_era.pc.code == '00': site_category = 'hh' elif site_category != 'amr': if len(cand_era.channels) > 0: site_category = 'amr' elif site_category != 'nhh': if cand_era.mtc.meter_type.code \ not in ['UM', 'PH']: site_category = 'nhh' else: site_category = 'unmetered' for group in site.groups( sess, month_start, month_finish, True): calcs = [] deltas = defaultdict(int) group_associates = set( s.code for s in group.sites if s.code != site.code) for supply in group.supplies: if supply_id is not None and supply.id != supply_id: continue for era in sess.query(Era).join(Supply) \ .join(Source).filter( Era.supply == supply, Era.start_date <= group.finish_date, or_( Era.finish_date == null(), Era.finish_date >= group.start_date)) \ .options( joinedload(Era.ssc), joinedload(Era.hhdc_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_contract), joinedload(Era.mtc).joinedload( Mtc.meter_type)): if era.start_date > group.start_date: ss_start = era.start_date else: ss_start = group.start_date if hh_before(era.finish_date, group.finish_date): ss_finish = era.finish_date else: ss_finish = group.finish_date if era.imp_mpan_core is None: imp_ss = None else: imp_ss = SupplySource( sess, ss_start, ss_finish, kwh_start, era, True, None, report_context) if era.exp_mpan_core is None: exp_ss = None measurement_type = imp_ss.measurement_type else: exp_ss = SupplySource( sess, ss_start, ss_finish, kwh_start, era, False, None, report_context) 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)) if imp_ss is not None and len(era.channels) == 0: for hh in imp_ss.hh_data: deltas[hh['start-date']] += hh['msp-kwh'] imp_net_delts = defaultdict(int) exp_net_delts = defaultdict(int) imp_gen_delts = defaultdict(int) displaced_era = chellow.computer.displaced_era( sess, group, group.start_date, group.finish_date) site_ds = chellow.computer.SiteSource( sess, site, group.start_date, group.finish_date, kwh_start, None, report_context, displaced_era) for hh in site_ds.hh_data: try: delta = deltas[hh['start-date']] hh['import-net-kwh'] += delta hh['used-kwh'] += delta except KeyError: pass for hh in site_ds.hh_data: for change in site_changes: if change['type'] == 'used' and \ change['date'] <= hh['start-date']: used = change['multiplier'] * hh['used-kwh'] exp_net = max( 0, hh['import-gen-kwh'] - hh['export-gen-kwh'] - used) exp_net_delt = exp_net - hh['export-net-kwh'] exp_net_delts[hh['start-date']] += exp_net_delt displaced = hh['import-gen-kwh'] - \ hh['export-gen-kwh'] - exp_net imp_net = used - displaced imp_delt = imp_net - hh['import-net-kwh'] imp_net_delts[hh['start-date']] += imp_delt hh['import-net-kwh'] = imp_net hh['used-kwh'] = used hh['export-net-kwh'] = exp_net hh['msp-kwh'] = displaced elif change['type'] == 'generated' and \ change['date'] <= hh['start-date']: imp_gen = change['multiplier'] * \ hh['import-gen-kwh'] imp_gen_delt = imp_gen - hh['import-gen-kwh'] exp_net = max( 0, imp_gen - hh['export-gen-kwh'] - hh['used-kwh']) exp_net_delt = exp_net - hh['export-net-kwh'] exp_net_delts[hh['start-date']] += exp_net_delt displaced = imp_gen - hh['export-gen-kwh'] - \ exp_net imp_net = hh['used-kwh'] - displaced imp_net_delt = imp_net - hh['import-net-kwh'] imp_net_delts[hh['start-date']] += imp_net_delt imp_gen_delts[hh['start-date']] += imp_gen_delt hh['import-net-kwh'] = imp_net hh['export-net-kwh'] = exp_net hh['import-gen-kwh'] = imp_gen hh['msp-kwh'] = displaced 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', None) 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 = [ None, None, displaced_era.make_meter_category(), 'displaced', None, None, None, None, site.code, site.name, ','.join(sorted(list(group_associates))), month_finish] + \ [month_data[t] for t in summary_titles] sup_tab.writerow(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: era = exp_ss.era else: era = imp_ss.era supply = era.supply source = supply.source source_code = source.code 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 source_code == 'net': delts = imp_net_delts elif source_code == 'gen': delts = imp_gen_delts else: delts = [] if len(delts) > 0 and imp_ss is not None: for hh in imp_ss.hh_data: diff = hh['msp-kwh'] + delts[hh['start-date']] if diff < 0: hh['msp-kwh'] = 0 hh['msp-kw'] = 0 delts[hh['start-date']] -= hh['msp-kwh'] else: hh['msp-kwh'] += delts[hh['start-date']] hh['msp-kw'] += hh['msp-kwh'] / 2 del delts[hh['start-date']] left_kwh = sum(delts.values()) if left_kwh > 0: first_hh = imp_ss.hh_data[0] first_hh['msp-kwh'] += left_kwh first_hh['msp-kw'] += left_kwh / 2 imp_supplier_contract = era.imp_supplier_contract if imp_supplier_contract is not None: import_vb_function = contract_func( report_context, imp_supplier_contract, 'virtual_bill', None) 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) imp_supplier_bill = imp_ss.supplier_bill try: gbp = imp_supplier_bill['net-gbp'] except KeyError: 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['used-gbp'] += gbp elif source_code == '3rd-party': month_data['import-3rd-party-gbp'] += gbp month_data['used-gbp'] += gbp elif source_code == '3rd-party-reverse': month_data['export-3rd-party-gbp'] += gbp month_data['used-gbp'] -= gbp kwh = sum( hh['msp-kwh'] for hh in imp_ss.hh_data) if source_code in ('net', 'gen-net'): month_data['import-net-kwh'] += kwh month_data['used-kwh'] += kwh elif source_code == '3rd-party': month_data['import-3rd-party-kwh'] += kwh month_data['used-kwh'] += kwh elif source_code == '3rd-party-reverse': month_data['export-3rd-party-kwh'] += kwh month_data['used-kwh'] -= kwh elif source_code in ('gen', 'gen-net'): month_data['import-gen-kwh'] += kwh exp_supplier_contract = era.exp_supplier_contract if exp_supplier_contract is None: kwh = sess.query( func.coalesce( func.sum( cast(HhDatum.value, Float)), 0)). \ join(Channel).filter( Channel.era == era, Channel.channel_type == 'ACTIVE', Channel.imp_related == false()).scalar() if source_code == 'gen': month_data['export-net-kwh'] += kwh else: export_vb_function = contract_func( report_context, exp_supplier_contract, 'virtual_bill', None) export_vb_function(exp_ss) 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.' kwh = sum(hh['msp-kwh'] for hh in exp_ss.hh_data) if source_code in ('net', 'gen-net'): month_data['export-net-kwh'] += kwh month_data['export-net-gbp'] += gbp elif source_code in \ ('3rd-party', '3rd-party-reverse'): month_data['export-3rd-party-kwh'] += kwh month_data['export-3rd-party-gbp'] += gbp month_data['used-kwh'] -= kwh month_data['used-gbp'] -= gbp elif source_code == 'gen': month_data['export-gen-kwh'] += kwh sss = exp_ss if imp_ss is None else imp_ss dc_contract = era.hhdc_contract sss.contract_func( dc_contract, 'virtual_bill')(sss) dc_bill = sss.dc_bill gbp = dc_bill['net-gbp'] mop_contract = era.mop_contract 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 else: month_data['import-net-gbp'] += gbp month_data['used-gbp'] += gbp if source_code in ('gen', 'gen-net'): generator_type = supply.generator_type.code site_gen_types.add(generator_type) else: generator_type = None sup_category = era.make_meter_category() if CATEGORY_ORDER[site_category] < \ CATEGORY_ORDER[sup_category]: site_category = sup_category 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 = \ float(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) out = [ era.imp_mpan_core, era.exp_mpan_core, sup_category, source_code, generator_type, supply.name, era.msn, era.pc.code, site.code, site.name, ','.join(sorted(list(site_associates))), month_finish] + [ month_data[t] for t in summary_titles] + [None] + [ (mop_bill[t] if t in mop_bill else None) for t in title_dict['mop']] + [None] + \ [(dc_bill[t] if t in dc_bill else None) for t in title_dict['dc']] if imp_supplier_contract is None: out += [None] * \ (len(title_dict['imp-supplier']) + 1) else: out += [None] + [ ( imp_supplier_bill[t] if t in imp_supplier_bill else None) for t in title_dict['imp-supplier']] if exp_supplier_contract is not None: out += [None] + [ ( exp_supplier_bill[t] if t in exp_supplier_bill else None) for t in title_dict['exp-supplier']] for k, v in month_data.items(): site_month_data[k] += v sup_tab.writerow(out) group_tab.writerow( [ site.code, site.name, ''.join(sorted(list(site_associates))), 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() month_start += relativedelta(months=1) except BadRequest as e: msg = e.description + traceback.format_exc() sys.stderr.write(msg + '\n') group_tab.writerow(["Problem " + msg]) except: msg = traceback.format_exc() sys.stderr.write(msg + '\n') group_tab.writerow(["Problem " + msg]) finally: if sess is not None: sess.close() try: f.close() rf.close() os.rename(running_name, finished_name) except: 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(contract_id, end_year, end_month, months, user): caches = {} sess = f = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'displaced.csv', user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') titles = [ 'Site Code', 'Site Name', 'Associated Site Ids', 'From', 'To', 'Gen Types', 'CHP kWh', 'LM kWh', 'Turbine kWh', 'PV kWh' ] 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() contract = Contract.get_supplier_by_id(sess, contract_id) sites = sess.query(Site).join(SiteEra).join(Era).join(Supply). \ join(Source).filter( or_(Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date, or_( Source.code.in_(('gen', 'gen-net')), Era.exp_mpan_core != null())).distinct() bill_titles = chellow.computer.contract_func( caches, contract, 'displaced_virtual_bill_titles')() for title in bill_titles: if title == 'total-msp-kwh': title = 'total-displaced-msp-kwh' titles.append(title) writer.writerow(titles) for site in sites: month_start = start_date month_finish = month_start + relativedelta(months=1) - HH for i in range(months): displaced_era = chellow.computer.displaced_era( sess, caches, site, month_start, month_finish, forecast_date) if displaced_era is None: continue supplier_contract = displaced_era.imp_supplier_contract if contract is not None and contract != supplier_contract: continue linked_sites = set() generator_types = set() for era in sess.query(Era).join(SiteEra).filter( SiteEra.site == site, Era.start_date <= month_finish, or_(Era.finish_date == null(), Era.finish_date >= month_start)): for site_era in era.site_eras: if site_era.site != site: linked_sites.add(site_era.site.code) supply = era.supply if supply.generator_type is not None: generator_types.add(supply.generator_type.code) supply_ids = set() for era in sess.query(Era).join(SiteEra).filter( SiteEra.site == site, SiteEra.is_physical, Era.start_date <= month_finish, or_(Era.finish_date == null(), Era.finish_date >= month_start)): supply_ids.add(era.supply.id) vals = [ site.code, site.name, ', '.join(list(linked_sites)), hh_format(month_start), hh_format(month_finish), ', '.join(list(generator_types)) ] total_gen_breakdown = {} results = iter( sess.execute( "select supply.id, hh_datum.value, " "hh_datum.start_date, channel.imp_related, " "source.code, generator_type.code as " "gen_type_code from hh_datum, channel, source, " "era, supply left outer join generator_type on " "supply.generator_type_id = generator_type.id " "where hh_datum.channel_id = channel.id and " "channel.era_id = era.id and era.supply_id = " "supply.id and supply.source_id = source.id and " "channel.channel_type = 'ACTIVE' and not " "(source.code = 'net' and channel.imp_related " "is true) and hh_datum.start_date >= " ":month_start and hh_datum.start_date " "<= :month_finish and " "supply.id = any(:supply_ids) order " "by hh_datum.start_date, supply.id", params={ 'month_start': month_start, 'month_finish': month_finish, 'supply_ids': sorted(list(supply_ids)) })) (sup_id, hh_val, hh_start, imp_related, source_code, gen_type_code) = next(results, (None, None, None, None, None, None)) for hh_date in hh_range(caches, month_start, month_finish): gen_breakdown = {} exported = 0 while hh_start == hh_date: if not imp_related and source_code in ('net', 'gen-net'): exported += hh_val if (imp_related and source_code == 'gen') or \ (not imp_related and source_code == 'gen-net'): gen_breakdown[gen_type_code] = \ gen_breakdown.setdefault( gen_type_code, 0) + hh_val if (not imp_related and source_code == 'gen') or ( imp_related and source_code == 'gen-net'): gen_breakdown[gen_type_code] = \ gen_breakdown.setdefault( gen_type_code, 0) - hh_val (sup_id, hh_val, hh_start, imp_related, source_code, gen_type_code) = next( results, (None, None, None, None, None, None)) displaced = sum(gen_breakdown.values()) - exported added_so_far = 0 for key in sorted(gen_breakdown.keys()): kwh = gen_breakdown[key] if kwh + added_so_far > displaced: total_gen_breakdown[key] = \ total_gen_breakdown.get(key, 0) + \ displaced - added_so_far break else: total_gen_breakdown[key] = \ total_gen_breakdown.get(key, 0) + kwh added_so_far += kwh for title in ['chp', 'lm', 'turb', 'pv']: vals.append(str(total_gen_breakdown.get(title, ''))) site_ds = chellow.computer.SiteSource(sess, site, month_start, month_finish, forecast_date, caches, displaced_era) disp_func = chellow.computer.contract_func( caches, supplier_contract, 'displaced_virtual_bill') disp_func(site_ds) bill = site_ds.supplier_bill for title in bill_titles: if title in bill: vals.append(to_val(bill[title])) del bill[title] else: vals.append('') for k in sorted(bill.keys()): vals.append(k) vals.append(str(bill[k])) writer.writerow(vals) month_start += relativedelta(months=1) month_finish = month_start + relativedelta(months=1) - HH 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 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}" )