def before_first_request(): chellow.utils.url_root = request.url_root sess = None try: sess = Session() configuration = sess.execute( select(Contract).where( Contract.name == "configuration")).scalar_one() props = configuration.make_properties() api_props = props.get("api", {}) api.description = api_props.get("description", "Access Chellow data") finally: if sess is not None: sess.close()
def content(start_date, finish_date, supply_id, mpan_cores, is_zipped, user): if is_zipped: file_extension = ".zip" else: file_extension = ".csv" base_name = ( "hh_data_row_" + to_ct(start_date).strftime("%Y%m%d%H%M") + file_extension ) tls = ["Site Code", "Imp MPAN Core", "Exp Mpan Core", "HH Start Clock-Time"] for polarity in ("Import", "Export"): for suffix in ( "ACTIVE kWh", "ACTIVE Status", "ACTIVE Modified", "REACTIVE_IMP kVArh", "REACTIVE_IMP Status", "REACTIVE_IMP Modified", "REACTIVE_EXP kVArh", "REACTIVE_EXP Status", "REACTIVE_EXP Modified", ): tls.append(polarity + " " + suffix) titles = csv_str(tls) running_name, finished_name = chellow.dloads.make_names(base_name, user) if is_zipped: zf = zipfile.ZipFile(running_name, "w") else: tmp_file = open(running_name, "w") sess = None try: sess = Session() caches = {} supplies = ( sess.query(Supply) .join(Era) .filter( Era.start_date <= finish_date, or_(Era.finish_date == null(), Era.finish_date >= start_date), ) .order_by(Era.supply_id, Era.start_date) .distinct() ) if supply_id is not None: sup = Supply.get_by_id(sess, supply_id) supplies = supplies.filter(Era.supply == sup) if mpan_cores is not None: supplies = supplies.filter( or_( Era.imp_mpan_core.in_(mpan_cores), Era.exp_mpan_core.in_(mpan_cores) ) ) if not is_zipped: tmp_file.write(titles) for supply in supplies: site, era = ( sess.query(Site, Era) .join(Era.site_eras) .filter( Era.supply == supply, Era.start_date <= finish_date, SiteEra.site_id == Site.id, or_(Era.finish_date == null(), Era.finish_date >= start_date), SiteEra.is_physical == true(), ) .order_by(Era.id) .first() ) outs = [] data = iter( sess.execute( """ select hh_base.start_date, max(imp_active.value), max(imp_active.status), max(imp_active.last_modified), max(imp_reactive_imp.value), max(imp_reactive_imp.status), max(imp_reactive_imp.last_modified), max(imp_reactive_exp.value), max(imp_reactive_exp.status), max(imp_reactive_exp.last_modified), max(exp_active.value), max(exp_active.status), max(exp_active.last_modified), max(exp_reactive_imp.value), max(imp_reactive_imp.status), max(exp_reactive_imp.last_modified), max(exp_reactive_exp.value), max(exp_reactive_exp.status), max(exp_reactive_exp.last_modified) from hh_datum hh_base join channel on hh_base.channel_id = channel.id join era on channel.era_id = era.id left join hh_datum imp_active on (imp_active.id = hh_base.id and channel.imp_related is true and channel.channel_type = 'ACTIVE') left join hh_datum imp_reactive_imp on (imp_reactive_imp.id = hh_base.id and channel.imp_related is true and channel.channel_type = 'REACTIVE_IMP') left join hh_datum imp_reactive_exp on (imp_reactive_exp.id = hh_base.id and channel.imp_related is true and channel.channel_type = 'REACTIVE_EXP') left join hh_datum exp_active on (exp_active.id = hh_base.id and channel.imp_related is false and channel.channel_type = 'ACTIVE') left join hh_datum exp_reactive_imp on (exp_reactive_imp.id = hh_base.id and channel.imp_related is false and channel.channel_type = 'REACTIVE_IMP') left join hh_datum exp_reactive_exp on (exp_reactive_exp.id = hh_base.id and channel.imp_related is false and channel.channel_type = 'REACTIVE_EXP') where supply_id = :supply_id and hh_base.start_date between :start_date and :finish_date group by hh_base.start_date order by hh_base.start_date """, params={ "supply_id": supply.id, "start_date": start_date, "finish_date": finish_date, }, ) ) datum = next(data, None) for dt in hh_range(caches, start_date, finish_date): row = [site.code, era.imp_mpan_core, era.exp_mpan_core, dt] if datum is not None: ( hh_start_date, imp_active, imp_active_status, imp_active_modified, imp_reactive_imp, imp_reactive_imp_status, imp_reactive_imp_modified, imp_reactive_exp, imp_reactive_exp_status, imp_reactive_exp_modified, exp_active, exp_active_status, exp_active_modified, exp_reactive_imp, exp_reactive_imp_status, exp_reactive_imp_modified, exp_reactive_exp, exp_reactive_exp_status, exp_reactive_exp_modified, ) = datum if hh_start_date == dt: datum = next(data, None) row += [ imp_active, imp_active_status, imp_active_modified, imp_reactive_imp, imp_reactive_imp_status, imp_reactive_imp_modified, imp_reactive_exp, imp_reactive_exp_status, imp_reactive_exp_modified, exp_active, exp_active_status, exp_active_modified, exp_reactive_imp, exp_reactive_imp_status, exp_reactive_imp_modified, exp_reactive_exp, exp_reactive_exp_status, exp_reactive_exp_modified, ] outs.append(csv_str(row)) if is_zipped: zf.writestr( ( "hh_data_row_" + str(era.id) + "_" + str(era.imp_mpan_core) + "_" + str(era.exp_mpan_core) ).replace(" ", "") + ".csv", titles + "".join(outs), ) else: tmp_file.write("".join(outs)) # Avoid a long-running transaction sess.rollback() except BaseException: msg = "Problem " + traceback.format_exc() if is_zipped: zf.writestr("error.txt", msg) else: tmp_file.write(msg) finally: if sess is not None: sess.close() if is_zipped: zf.close() else: tmp_file.close() os.rename(running_name, finished_name)
def content(contract_id, end_year, end_month, months, user): caches = {} sess = f = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( "displaced.csv", user) f = open(running_name, mode="w", newline="") writer = csv.writer(f, lineterminator="\n") titles = [ "Site Code", "Site Name", "Associated Site Ids", "From", "To", "Gen Types", "CHP kWh", "LM kWh", "Turbine kWh", "PV kWh", ] month_list = list( c_months_u(finish_year=end_year, finish_month=end_month, months=months)) start_date, finish_date = month_list[0][0], month_list[-1][-1] forecast_date = chellow.computer.forecast_date() contract = Contract.get_supplier_by_id(sess, contract_id) sites = (sess.query(Site).join(SiteEra).join(Era).join(Supply).join( Source).filter( or_(Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date, or_(Source.code.in_(("gen", "gen-net")), Era.exp_mpan_core != null()), ).distinct()) bill_titles = chellow.computer.contract_func( caches, contract, "displaced_virtual_bill_titles")() for title in bill_titles: if title == "total-msp-kwh": title = "total-displaced-msp-kwh" titles.append(title) writer.writerow(titles) for site in sites: for month_start, month_finish in month_list: displaced_era = chellow.computer.displaced_era( sess, caches, site, month_start, month_finish, forecast_date) if displaced_era is None: continue supplier_contract = displaced_era.imp_supplier_contract if contract is not None and contract != supplier_contract: continue linked_sites = set() generator_types = set() for era in (sess.query(Era).join(SiteEra).filter( SiteEra.site == site, Era.start_date <= month_finish, or_(Era.finish_date == null(), Era.finish_date >= month_start), )): for site_era in era.site_eras: if site_era.site != site: linked_sites.add(site_era.site.code) supply = era.supply if supply.generator_type is not None: generator_types.add(supply.generator_type.code) supply_ids = set() for era in (sess.query(Era).join(SiteEra).filter( SiteEra.site == site, SiteEra.is_physical, Era.start_date <= month_finish, or_(Era.finish_date == null(), Era.finish_date >= month_start), )): supply_ids.add(era.supply.id) vals = [ site.code, site.name, ", ".join(list(linked_sites)), hh_format(month_start), hh_format(month_finish), ", ".join(list(generator_types)), ] total_gen_breakdown = {} results = iter( sess.execute( "select supply.id, hh_datum.value, " "hh_datum.start_date, channel.imp_related, " "source.code, generator_type.code as " "gen_type_code from hh_datum, channel, source, " "era, supply left outer join generator_type on " "supply.generator_type_id = generator_type.id " "where hh_datum.channel_id = channel.id and " "channel.era_id = era.id and era.supply_id = " "supply.id and supply.source_id = source.id and " "channel.channel_type = 'ACTIVE' and not " "(source.code = 'net' and channel.imp_related " "is true) and hh_datum.start_date >= " ":month_start and hh_datum.start_date " "<= :month_finish and " "supply.id = any(:supply_ids) order " "by hh_datum.start_date, supply.id", params={ "month_start": month_start, "month_finish": month_finish, "supply_ids": sorted(list(supply_ids)), }, )) ( sup_id, hh_val, hh_start, imp_related, source_code, gen_type_code, ) = next(results, (None, None, None, None, None, None)) for hh_date in hh_range(caches, month_start, month_finish): gen_breakdown = {} exported = 0 while hh_start == hh_date: if not imp_related and source_code in ("net", "gen-net"): exported += hh_val if (imp_related and source_code == "gen") or ( not imp_related and source_code == "gen-net"): gen_breakdown[gen_type_code] = ( gen_breakdown.setdefault(gen_type_code, 0) + hh_val) if (not imp_related and source_code == "gen") or ( imp_related and source_code == "gen-net"): gen_breakdown[gen_type_code] = ( gen_breakdown.setdefault(gen_type_code, 0) - hh_val) ( sup_id, hh_val, hh_start, imp_related, source_code, gen_type_code, ) = next(results, (None, None, None, None, None, None)) displaced = sum(gen_breakdown.values()) - exported added_so_far = 0 for key in sorted(gen_breakdown.keys()): kwh = gen_breakdown[key] if displaced < 0: total_gen_breakdown[key] = ( total_gen_breakdown.get(key, 0) + kwh) else: if kwh + added_so_far > displaced: total_gen_breakdown[key] = ( total_gen_breakdown.get(key, 0) + displaced - added_so_far) break else: total_gen_breakdown[key] = ( total_gen_breakdown.get(key, 0) + kwh) added_so_far += kwh for title in ["chp", "lm", "turb", "pv"]: vals.append(str(total_gen_breakdown.get(title, ""))) site_ds = chellow.computer.SiteSource( sess, site, month_start, month_finish, forecast_date, caches, displaced_era, ) disp_func = chellow.computer.contract_func( caches, supplier_contract, "displaced_virtual_bill") disp_func(site_ds) bill = site_ds.supplier_bill for title in bill_titles: if title in bill: vals.append(to_val(bill[title])) del bill[title] else: vals.append("") for k in sorted(bill.keys()): vals.append(k) vals.append(str(bill[k])) writer.writerow(vals) except BaseException: msg = traceback.format_exc() sys.stderr.write(msg) writer.writerow([msg]) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def content(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).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) 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 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) supply = era.supply 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 supply.generator_type is None: generator_type = '' else: generator_type = supply.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]: if is_import: if era.imp_mpan_core is None: continue else: supplier_contract = era.imp_supplier_contract else: if era.exp_mpan_core is None: continue else: supplier_contract = era.exp_supplier_contract latest_supplier_bill_date = sess.query(Bill.finish_date) \ .join(Batch).filter( Bill.start_date <= date, Bill.supply == supply, Batch.contract == supplier_contract).order_by( Bill.finish_date.desc()).first() if latest_supplier_bill_date is not None: latest_supplier_bill_date = \ latest_supplier_bill_date[0] latest_supplier_bill_date = hh_format( latest_supplier_bill_date) if is_import: imp_latest_supplier_bill_date = \ latest_supplier_bill_date else: exp_latest_supplier_bill_date = \ latest_supplier_bill_date 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, 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() 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(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(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(user): sess = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( "ecoes_comparison.csv", user) f = open(running_name, mode="w", newline="") writer = csv.writer(f, lineterminator="\n") props = Contract.get_non_core_by_name( sess, "configuration").make_properties() ECOES_KEY = "ecoes" try: ecoes_props = props[ECOES_KEY] except KeyError: raise BadRequest( f"The property {ECOES_KEY} cannot be found in the configuration " f"properties.") for key in ("user_name", "password", "prefix", "ignore_mpan_cores"): try: ecoes_props[key] except KeyError: raise BadRequest( f"The property {key} cannot be found in the 'ecoes' section of " f"the configuration properties.") ignore_mpan_cores = ecoes_props["ignore_mpan_cores"] proxies = props.get("proxies", {}) s = requests.Session() s.verify = False r = s.get(ecoes_props["prefix"], proxies=proxies) r = s.post( ecoes_props["prefix"], data={ "Username": ecoes_props["user_name"], "Password": ecoes_props["password"], }, allow_redirects=False, ) imp_mpans = [ v.imp_mpan_core for (v, ) in sess.execute( select(Era).join(Supply).join(Source).join(Supply.dno).filter( Party.dno_code.notin_(("88", "99")), Era.finish_date == null(), Source.code != "3rd-party", Era.imp_mpan_core.notin_(ignore_mpan_cores), Era.imp_mpan_core != null(), ).distinct().order_by(Era.imp_mpan_core)) ] exp_mpans = [ v.exp_mpan_core for (v, ) in sess.execute( select(Era).join(Supply).join(Source).join(Supply.dno).filter( Party.dno_code.notin_(("88", "99")), Era.finish_date == null(), Source.code != "3rd-party", Era.exp_mpan_core != null(), Era.exp_mpan_core.notin_(ignore_mpan_cores), ).distinct().order_by(Era.exp_mpan_core)) ] mpans = imp_mpans + exp_mpans r = s.get( ecoes_props["prefix"] + "NonDomesticCustomer/ExportPortfolioMPANs?fileType=csv", proxies=proxies, ) titles = ( "MPAN Core", "MPAN Core No Spaces", "ECOES PC", "Chellow PC", "ECOES MTC", "Chellow MTC", "ECOES LLFC", "Chellow LLFC", "ECOES SSC", "Chellow SSC", "ECOES Energisation Status", "Chellow Energisation Status", "ECOES Supplier", "Chellow Supplier", "ECOES DC", "Chellow DC", "ECOES MOP", "Chellow MOP", "ECOES GSP Group", "Chellow GSP Group", "ECOES MSN", "Chellow MSN", "ECOES Meter Type", "Chellow Meter Type", "Problem", ) writer.writerow(titles) parser = iter(csv.reader(r.text.splitlines(True))) next(parser) # Skip titles for values in parser: problem = "" ecoes_titles = [ "mpan-core", "address-line-1", "address-line-2", "address-line-3", "address-line-4", "address-line-5", "address-line-6", "address-line-7", "address-line-8", "address-line-9", "post-code", "supplier", "registration-from", "mtc", "mtc-date", "llfc", "llfc-from", "pc", "ssc", "measurement-class", "energisation-status", "da", "dc", "mop", "mop-appoint-date", "gsp-group", "gsp-effective-from", "dno", "msn", "meter-install-date", "meter-type", "map-id", ] ecoes = dict(zip(ecoes_titles, map(str.strip, values))) mpan_spaces = " ".join(( ecoes["mpan-core"][:2], ecoes["mpan-core"][2:6], ecoes["mpan-core"][6:10], ecoes["mpan-core"][-3:], )) if mpan_spaces in ignore_mpan_cores: continue try: ecoes_es = ecoes["energisation-status"] except KeyError as e: print(r.text) raise e ecoes_disconnected = ecoes_es == "" current_chell = mpan_spaces in mpans if ecoes_disconnected and current_chell: problem += "Disconnected in ECOES, but current in Chellow. " elif not ecoes_disconnected and not current_chell: problem += f"In ECOES (as {ecoes_es}) but disconnected in Chellow. " if current_chell: mpans.remove(mpan_spaces) era = sess.execute( select(Era).filter( Era.finish_date == null(), or_( Era.imp_mpan_core == mpan_spaces, Era.exp_mpan_core == mpan_spaces, ), ).options( joinedload(Era.supply).joinedload(Supply.gsp_group), joinedload(Era.mop_contract).joinedload( Contract.party).joinedload(Party.participant), joinedload(Era.dc_contract).joinedload( Contract.party).joinedload(Party.participant), joinedload(Era.imp_supplier_contract).joinedload( Contract.party).joinedload(Party.participant), joinedload(Era.exp_supplier_contract).joinedload( Contract.party).joinedload(Party.participant), joinedload(Era.pc), joinedload(Era.imp_llfc), joinedload(Era.exp_llfc), joinedload(Era.mtc).joinedload(Mtc.meter_type), joinedload(Era.ssc), joinedload(Era.energisation_status), joinedload(Era.channels), )).scalar() if era.imp_mpan_core == mpan_spaces: supplier_contract = era.imp_supplier_contract llfc = era.imp_llfc else: supplier_contract = era.exp_supplier_contract llfc = era.exp_llfc chellow_pc = era.pc.code try: if int(ecoes["pc"]) != int(chellow_pc): problem += "The PCs don't match. " except ValueError: problem += "Can't parse the PC. " chellow_mtc = era.mtc.code try: if int(ecoes["mtc"]) != int(chellow_mtc): problem += "The MTCs don't match. " except ValueError: problem += "Can't parse the MTC. " chellow_llfc = llfc.code if ecoes["llfc"].zfill(3) != chellow_llfc: problem += "The LLFCs don't match. " chellow_ssc = era.ssc if chellow_ssc is None: chellow_ssc = "" chellow_ssc_int = None else: chellow_ssc = chellow_ssc.code chellow_ssc_int = int(chellow_ssc) if len(ecoes["ssc"]) > 0: ecoes_ssc_int = int(ecoes["ssc"]) else: ecoes_ssc_int = None if ecoes_ssc_int != chellow_ssc_int and not ( ecoes_ssc_int is None and chellow_ssc_int is None): problem += "The SSCs don't match. " chellow_es = era.energisation_status.code if ecoes_es != chellow_es: problem += "The energisation statuses don't match. " chellow_supplier = supplier_contract.party.participant.code if chellow_supplier != ecoes["supplier"]: problem += "The supplier codes don't match. " dc_contract = era.dc_contract if dc_contract is None: chellow_dc = "" else: chellow_dc = dc_contract.party.participant.code if chellow_dc != ecoes["dc"]: problem += "The DC codes don't match. " mop_contract = era.mop_contract if mop_contract is None: chellow_mop = "" else: chellow_mop = mop_contract.party.participant.code if chellow_mop != ecoes["mop"]: problem += "The MOP codes don't match. " chellow_gsp_group = era.supply.gsp_group.code if chellow_gsp_group != ecoes["gsp-group"]: problem += "The GSP group codes don't match. " chellow_msn = era.msn if chellow_msn is None: chellow_msn = "" if chellow_msn != ecoes["msn"]: problem += "The meter serial numbers don't match. " chellow_meter_type = _meter_type(era) if chellow_meter_type != ecoes["meter-type"]: problem += ( "The meter types don't match. See " "https://dtc.mrasco.com/DataItem.aspx?ItemCounter=0483 " ) else: chellow_pc = "" chellow_mtc = "" chellow_llfc = "" chellow_ssc = "" chellow_es = "" chellow_supplier = "" chellow_dc = "" chellow_mop = "" chellow_gsp_group = "" chellow_msn = "" chellow_meter_type = "" if len(problem) > 0: writer.writerow([ mpan_spaces, ecoes["mpan-core"], ecoes["pc"], chellow_pc, ecoes["mtc"], chellow_mtc, ecoes["llfc"], chellow_llfc, ecoes["ssc"], chellow_ssc, ecoes["energisation-status"], chellow_es, ecoes["supplier"], chellow_supplier, ecoes["dc"], chellow_dc, ecoes["mop"], chellow_mop, ecoes["gsp-group"], chellow_gsp_group, ecoes["msn"], chellow_msn, ecoes["meter-type"], chellow_meter_type, problem, ]) sess.expunge_all() for mpan_core in mpans: supply = Supply.get_by_mpan_core(sess, mpan_core) era = supply.find_era_at(sess, None) if era.imp_mpan_core == mpan_core: supplier_contract = era.imp_supplier_contract llfc = era.imp_llfc else: supplier_contract = era.exp_supplier_contract llfc = era.exp_llfc ssc = "" if era.ssc is None else era.ssc.code es = era.energisation_status.code dc_contract = era.dc_contract if dc_contract is None: dc = "" else: dc = dc_contract.party.participant.code mop_contract = era.mop_contract if mop_contract is None: mop = "" else: mop = mop_contract.party.participant.code msn = "" if era.msn is None else era.msn meter_type = _meter_type(era) writer.writerow([ mpan_core, mpan_core.replace(" ", ""), "", era.pc.code, "", era.mtc.code, "", llfc.code, "", ssc, "", es, "", supplier_contract.party.participant.code, "", dc, "", mop, "", supply.gsp_group.code, "", msn, "", meter_type, "In Chellow, but not in ECOES.", ]) 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(supply_id, start_date, finish_date, user): caches = {} try: sess = Session() supply = Supply.get_by_id(sess, supply_id) forecast_date = chellow.computer.forecast_date() running_name, finished_name = chellow.dloads.make_names( f"supply_virtual_bills_hh_{supply_id}.csv", user ) f = open(running_name, mode="w", newline="") w = csv.writer(f, lineterminator="\n") mop_titles = [] dc_titles = [] imp_supplier_titles = [] exp_supplier_titles = [] for era in sess.execute( select(Era).where( Era.supply == supply, Era.start_date <= finish_date, or_(Era.finish_date == null(), Era.finish_date >= start_date), ) ).scalars(): ds = chellow.computer.SupplySource( sess, era.start_date, era.start_date, forecast_date, era, True, caches ) for t in ds.contract_func(era.mop_contract, "virtual_bill_titles")(): if t not in mop_titles: mop_titles.append(t) for t in ds.contract_func(era.dc_contract, "virtual_bill_titles")(): if t not in dc_titles: dc_titles.append(t) if era.imp_supplier_contract is not None: for t in ds.contract_func( era.imp_supplier_contract, "virtual_bill_titles" )(): if t not in imp_supplier_titles: imp_supplier_titles.append(t) if era.exp_supplier_contract is not None: ds = chellow.computer.SupplySource( sess, era.start_date, era.start_date, forecast_date, era, False, caches, ) for t in ds.contract_func( era.exp_supplier_contract, "virtual_bill_titles" )(): if t not in exp_supplier_titles: exp_supplier_titles.append(t) titles = [ "mpan_core", "site_code", "site_name", "hh_start", ] for pref, t in ( ("mop", mop_titles), ("dc", dc_titles), ("imp_supplier", imp_supplier_titles), ("exp_supplier", exp_supplier_titles), ): titles.append("") titles.extend([f"{pref}_{n}" for n in t]) w.writerow(titles) for hh_start in hh_range(caches, start_date, finish_date): era = sess.execute( select(Era).where( Era.supply == supply, Era.start_date <= hh_start, or_(Era.finish_date == null(), Era.finish_date >= hh_start), ) ).scalar_one() site = sess.execute( select(Site) .join(SiteEra) .where(SiteEra.era == era, SiteEra.is_physical == true()) ).scalar_one() ds = chellow.computer.SupplySource( sess, hh_start, hh_start, forecast_date, era, True, caches ) vals = { "mpan_core": ds.mpan_core, "site_code": site.code, "site_name": site.name, "hh_start": hh_format(ds.start_date), } ds.contract_func(era.mop_contract, "virtual_bill")(ds) for k, v in ds.mop_bill.items(): vals[f"mop_{k}"] = v ds.contract_func(era.dc_contract, "virtual_bill")(ds) for k, v in ds.dc_bill.items(): vals[f"dc_{k}"] = v if era.imp_supplier_contract is not None: ds.contract_func(era.imp_supplier_contract, "virtual_bill")(ds) for k, v in ds.supplier_bill.items(): vals[f"imp_supplier_{k}"] = v if era.exp_supplier_contract is not None: ds = chellow.computer.SupplySource( sess, hh_start, hh_start, forecast_date, era, False, caches ) ds.contract_func(era.exp_supplier_contract, "virtual_bill")(ds) for k, v in ds.supplier_bill.items(): vals[f"exp_supplier_{k}"] = v w.writerow([csv_make_val(vals.get(t)) for t in titles]) except BaseException: msg = traceback.format_exc() sys.stderr.write(msg) w.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, supply_id, mpan_cores, is_zipped, user): if is_zipped: file_extension = ".zip" else: file_extension = ".csv" base_name = "hh_data_row_" + start_date.strftime("%Y%m%d%H%M") + \ file_extension titles = ','.join('"' + v + '"' for v in ( "Site Code", "Imp MPAN Core", "Exp Mpan Core", "Start Date", "Import ACTIVE", "Import ACTIVE Status", "Import REACTIVE_IMP", "Import REACTIVE_IMP Status", "Import REACTIVE_EXP", "Import REACTIVE_EXP Status", "Export ACTIVE", "Export ACTIVE Status", "Export REACTIVE_IMP", "Export REACTIVE_IMP Status", "Export REACTIVE_EXP", "Export REACTIVE_EXP Status")) + "\n" running_name, finished_name = chellow.dloads.make_names(base_name, user) if is_zipped: zf = zipfile.ZipFile(running_name, 'w') else: tmp_file = open(running_name, "w") sess = None try: sess = Session() supplies = sess.query(Supply).join(Era).filter( Era.start_date <= finish_date, or_( Era.finish_date == null(), Era.finish_date >= start_date), ).order_by(Era.supply_id, Era.start_date).distinct() if supply_id is not None: sup = Supply.get_by_id(sess, supply_id) supplies = supplies.filter(Era.supply == sup) if mpan_cores is not None: supplies = supplies.filter( or_( Era.imp_mpan_core.in_(mpan_cores), Era.exp_mpan_core.in_(mpan_cores))) if not is_zipped: tmp_file.write(titles) for supply in supplies: site, era = sess.query( Site, Era).join(Era.site_eras).filter( Era.supply == supply, Era.start_date <= finish_date, SiteEra.site_id == Site.id, or_( Era.finish_date == null(), Era.finish_date >= start_date), SiteEra.is_physical == true()).order_by(Era.id).first() outs = [] for hh_start_date, imp_active, imp_active_status, \ imp_reactive_imp, imp_reactive_imp_status, \ imp_reactive_exp, imp_reactive_exp_status, \ exp_active, exp_active_status, exp_reactive_imp, \ exp_reactive_imp_status, exp_reactive_exp, \ exp_reactive_exp_status in sess.execute(""" select hh_base.start_date, max(imp_active.value), max(imp_active.status), max(imp_reactive_imp.value), max(imp_reactive_imp.status), max(imp_reactive_exp.value), max(imp_reactive_exp.status), max(exp_active.value), max(exp_active.status), max(exp_reactive_imp.value), max(imp_reactive_imp.status), max(exp_reactive_imp.value), max(imp_reactive_exp.status) from hh_datum hh_base join channel on hh_base.channel_id = channel.id join era on channel.era_id = era.id left join hh_datum imp_active on (imp_active.id = hh_base.id and channel.imp_related is true and channel.channel_type = 'ACTIVE') left join hh_datum imp_reactive_imp on (imp_reactive_imp.id = hh_base.id and channel.imp_related is true and channel.channel_type = 'REACTIVE_IMP') left join hh_datum imp_reactive_exp on (imp_reactive_exp.id = hh_base.id and channel.imp_related is true and channel.channel_type = 'REACTIVE_EXP') left join hh_datum exp_active on (exp_active.id = hh_base.id and channel.imp_related is false and channel.channel_type = 'ACTIVE') left join hh_datum exp_reactive_imp on (exp_reactive_imp.id = hh_base.id and channel.imp_related is false and channel.channel_type = 'REACTIVE_IMP') left join hh_datum exp_reactive_exp on (exp_reactive_exp.id = hh_base.id and channel.imp_related is false and channel.channel_type = 'REACTIVE_EXP') where supply_id = :supply_id and hh_base.start_date between :start_date and :finish_date group by hh_base.start_date order by hh_base.start_date """, params={ 'supply_id': supply.id, 'start_date': start_date, 'finish_date': finish_date}): outs.append(','.join( '"' + ('' if v is None else str(v)) + '"' for v in ( site.code, era.imp_mpan_core, era.exp_mpan_core, hh_format(hh_start_date), imp_active, imp_active_status, imp_reactive_imp, imp_reactive_imp_status, imp_reactive_exp, imp_reactive_exp_status, exp_active, exp_active_status, exp_reactive_imp, exp_reactive_imp_status, exp_reactive_exp, exp_reactive_exp_status)) + '\n') if is_zipped: zf.writestr( ( "hh_data_row_" + str(era.id) + "_" + str(era.imp_mpan_core) + "_" + str(era.exp_mpan_core)).replace(' ', '') + '.csv', titles + ''.join(outs)) else: tmp_file.write(''.join(outs)) except: msg = "Problem " + traceback.format_exc() if is_zipped: zf.writestr('error.txt', msg) else: tmp_file.write(msg) finally: if sess is not None: sess.close() if is_zipped: zf.close() else: tmp_file.close() os.rename(running_name, finished_name)
def run(self): sess = None try: sess = Session() self._log( "Starting to parse the file with '" + self.parser_name + "'.") set_read_write(sess) batch = Batch.get_by_id(sess, self.batch_id) raw_bills = self.parser.make_raw_bills() self._log( "Successfully parsed the file, and now I'm starting to " "insert the raw bills.") for self.bill_num, raw_bill in enumerate(raw_bills): try: with sess.begin_nested(): sess.execute( "set transaction isolation level serializable " "read write") bill_type = BillType.get_by_code( sess, raw_bill['bill_type_code']) bill = batch.insert_bill( sess, raw_bill['account'], raw_bill['reference'], raw_bill['issue_date'], raw_bill['start_date'], raw_bill['finish_date'], raw_bill['kwh'], raw_bill['net'], raw_bill['vat'], raw_bill['gross'], bill_type, raw_bill['breakdown']) sess.flush() for raw_read in raw_bill['reads']: tpr_code = raw_read['tpr_code'] if tpr_code is None: tpr = None else: tpr = Tpr.get_by_code(sess, tpr_code) prev_type = ReadType.get_by_code( sess, raw_read['prev_type_code']) pres_type = ReadType.get_by_code( sess, raw_read['pres_type_code']) bill.insert_read( sess, tpr, raw_read['coefficient'], raw_read['units'], raw_read['msn'], raw_read['mpan'], raw_read['prev_date'], raw_read['prev_value'], prev_type, raw_read['pres_date'], raw_read['pres_value'], pres_type) self.successful_bills.append(raw_bill) except BadRequest as e: raw_bill['error'] = str(e.description) self.failed_bills.append(raw_bill) if len(self.failed_bills) == 0: sess.commit() self._log( "All the bills have been successfully loaded and attached " "to the batch.") else: sess.rollback() self._log( "The import has finished, but there were " + str(len(self.failed_bills)) + " failures, and so the " "whole import has been rolled back.") except: sess.rollback() self._log("I've encountered a problem: " + traceback.format_exc()) finally: 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') 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', '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 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) 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.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 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.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()).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 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]) dc_contract = era.dc_contract if dc_contract is None: dc_contract_name = '' dc_account = '' latest_dc_bill_date = 'No DC' else: 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] 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.dno_code, 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, dc_contract_name, dc_account, era.msn, hh_format(meter_installation_date), latest_normal_read_date, latest_normal_read_type, latest_dc_bill_date, latest_mop_bill_date, hh_format(supply_start_date), hh_format(supply_finish_date, ongoing_str=''), era.properties ] + 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 ]) # 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)