def content(user): sess = f = writer = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'llfcs.csv', user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') writer.writerow( ('Chellow Id', 'DNO Code', 'Code', 'Description', 'Voltage Level', 'Is Substation?', 'Is Import?', 'Valid From', 'Valid To')) for llfc in sess.query(Llfc).order_by(Llfc.id).options( joinedload(Llfc.dno), joinedload(Llfc.voltage_level)): writer.writerow( (str(llfc.id), llfc.dno.dno_code, llfc.code, llfc.description, llfc.voltage_level.code, llfc.is_substation, llfc.is_import, hh_format(llfc.valid_from), hh_format(llfc.valid_to))) except BaseException: writer.writerow([traceback.format_exc()]) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def long_process(start_date, finish_date, st_id, months, year, month, user): caches = {} tmp_file = sess = None try: sess = Session() if st_id is None: st = None base_name = "site_monthly_duration_for_all_site_for_" + \ str(months) + "_to_" + str(year) + "_" + str(month) + ".csv" else: st = Site.get_by_id(sess, st_id) base_name = "site_monthly_duration_for_" + st.code + "_" + \ str(months) + "_to_" + str(year) + "_" + str(month) + ".csv" running_name, finished_name = chellow.dloads.make_names( base_name, user) tmp_file = open(running_name, "w") forecast_date = chellow.computer.forecast_date() tmp_file.write( "Site Id,Site Name,Associated Site Ids,Sources," "Generator Types,Month,Metered Imported kWh," "Metered Displaced kWh,Metered Exported kWh,Metered Used kWh," "Metered Parasitic kWh,Metered Generated kWh," "Metered 3rd Party Import kWh,Metered 3rd Party Export kWh," "Metered Imported GBP,Metered Displaced GBP,Metered Exported GBP," "Metered Used GBP,Metered 3rd Party Import GBP," "Billed Imported kWh,Billed Imported GBP,Metering Type,Problem") for i in range(months): sites = sess.query(Site).order_by(Site.code) if st is not None: sites = sites.filter(Site.id == st.id) for site in sites: month_start = start_date + relativedelta(months=i) month_finish = month_start + relativedelta(months=1) - HH tmp_file.write( '\r\n' + ','.join( '"' + str(value) + '"' for value in process_site( sess, site, month_start, month_finish, forecast_date, tmp_file, start_date, finish_date, caches))) tmp_file.flush() except: msg = traceback.format_exc() sys.stderr.write(msg + '\n') tmp_file.write("Problem " + msg) finally: if sess is not None: sess.close() tmp_file.close() os.rename(running_name, finished_name)
def run(self): while not self.stopped.isSet(): if self.lock.acquire(False): sess = self.global_alert = None try: sess = Session() self.log("Starting to check bmarketidx.") contract = Contract.get_non_core_by_name(sess, "bmarketidx") latest_rs = ( sess.query(RateScript) .filter(RateScript.contract_id == contract.id) .order_by(RateScript.start_date.desc()) .first() ) start_ct = to_ct(latest_rs.start_date) months = list( c_months_u( start_year=start_ct.year, start_month=start_ct.month, months=2, ) ) month_start, month_finish = months[1] now = utc_datetime_now() if now > month_finish: _process_month( self.log, sess, contract, latest_rs, month_start, month_finish, ) except BaseException: self.log(f"Outer problem {traceback.format_exc()}") sess.rollback() self.global_alert = ( "There's a problem with the " "bmarketidx automatic importer." ) finally: self.lock.release() self.log("Finished checking bmarketidx rates.") if sess is not None: sess.close() self.going.wait(2 * 60 * 60) self.going.clear()
def content(user): sess = f = writer = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names("llfcs.csv", user) f = open(running_name, mode="w", newline="") writer = csv.writer(f, lineterminator="\n") writer.writerow( ( "Chellow Id", "DNO Code", "Code", "Description", "Voltage Level", "Is Substation?", "Is Import?", "Valid From", "Valid To", ) ) for llfc in ( sess.query(Llfc) .order_by(Llfc.id) .options(joinedload(Llfc.dno), joinedload(Llfc.voltage_level)) ): writer.writerow( ( str(llfc.id), llfc.dno.dno_code, llfc.code, llfc.description, llfc.voltage_level.code, llfc.is_substation, llfc.is_import, hh_format(llfc.valid_from), hh_format(llfc.valid_to), ) ) except BaseException: writer.writerow([traceback.format_exc()]) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def startup(): for procs in processes.values(): for proc in procs: if proc.isAlive(): raise BadRequest( "Can't start hh importer, there are still some " "hh imports running.") sess = None try: sess = Session() for contract in (sess.query(Contract).join(MarketRole).filter( MarketRole.code == "C").order_by(Contract.id)): startup_contract(contract.id) finally: if sess is not None: sess.close()
def content(user): sess = f = writer = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'site_snags.csv', user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') writer.writerow( ('Chellow Id', 'Site Code', 'Site Name', 'Snag Description', 'Start Date', 'Finish Date', 'Days Since Snag Finished', 'Duration Of Snag (Days)', 'Is Ignored?')) now = Datetime.now(pytz.utc) for snag in sess.query(Snag).join(Site).filter( Snag.site != null()).order_by(Site.code, Snag.description, Snag.start_date, Snag.id).options( joinedload(Snag.site)): snag_start = snag.start_date snag_finish = snag.finish_date if snag_finish is None: duration = now - snag_start age_of_snag = datetime.timedelta(0) else: duration = snag_finish - snag_start age_of_snag = now - snag_finish writer.writerow( (str(snag.id), snag.site.code, snag.site.name, snag.description, hh_format(snag_start), hh_format(snag_finish), str(age_of_snag.days + age_of_snag.seconds / (3600 * 24)), str(duration.days + duration.seconds / (3600 * 24)), str(snag.is_ignored))) 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 = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'mop_virtual_bills.csv', user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') contract = Contract.get_mop_by_id(sess, contract_id) forecast_date = chellow.computer.forecast_date() header_titles = [ 'Import MPAN Core', 'Export MPAN Core', 'Start Date', 'Finish Date'] bill_titles = chellow.computer.contract_func( caches, contract, 'virtual_bill_titles')() writer.writerow(header_titles + bill_titles) vb_func = chellow.computer.contract_func( caches, contract, 'virtual_bill') for era in sess.query(Era).filter( or_( Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date, Era.mop_contract == contract). \ order_by(Era.imp_mpan_core, Era.exp_mpan_core, Era.start_date): chunk_start = hh_max(era.start_date, start_date) chunk_finish = hh_min(era.finish_date, finish_date) import_mpan_core = era.imp_mpan_core if import_mpan_core is None: import_mpan_core_str = '' else: is_import = True import_mpan_core_str = import_mpan_core export_mpan_core = era.exp_mpan_core if export_mpan_core is None: export_mpan_core_str = '' else: is_import = False export_mpan_core_str = export_mpan_core out = [ import_mpan_core_str, export_mpan_core_str, hh_format(chunk_start), hh_format(chunk_finish)] supply_source = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, is_import, caches) vb_func(supply_source) bill = supply_source.mop_bill for title in bill_titles: if title in bill: out.append(make_val(bill[title])) del bill[title] else: out.append('') for k in sorted(bill.keys()): out.append(k) out.append(str(bill[k])) writer.writerow(out) except: msg = traceback.format_exc() sys.stderr.write(msg) writer.writerow([msg]) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def content(start_date, finish_date, imp_related, channel_type, is_zipped, supply_id, mpan_cores, user): zf = sess = tf = None base_name = ["supplies_hh_data", finish_date.strftime('%Y%m%d%H%M')] cache = {} try: sess = Session() supplies = sess.query(Supply).join(Era).filter( or_(Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date).order_by(Supply.id).distinct() if supply_id is not None: supply = Supply.get_by_id(sess, supply_id) supplies = supplies.filter(Supply.id == supply.id) first_era = sess.query(Era).filter( Era.supply == supply, or_(Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date).order_by( Era.start_date).first() if first_era.imp_mpan_core is None: name_core = first_era.exp_mpan_core else: name_core = first_era.imp_mpan_core base_name.append("supply_" + name_core.replace(' ', '_')) if mpan_cores is not None: supplies = supplies.filter( or_(Era.imp_mpan_core.in_(mpan_cores), Era.exp_mpan_core.in_(mpan_cores))) base_name.append('filter') outs = [] titles = ','.join([ 'Import MPAN Core', 'Export MPAN Core', 'Import Related?', 'Channel Type', 'Date' ] + list(map(str, range(48)))) running_name, finished_name = chellow.dloads.make_names( '_'.join(base_name) + ('.zip' if is_zipped else '.csv'), user) if is_zipped: zf = zipfile.ZipFile(running_name, "w", zipfile.ZIP_DEFLATED) else: tf = open(running_name, "w") outs.append(titles) for supply in supplies: era = supply.find_era_at(sess, finish_date) if era is None: imp_mpan_core_str = exp_mpan_core_str = 'NA' else: if era.imp_mpan_core is None: imp_mpan_core_str = "NA" else: imp_mpan_core_str = era.imp_mpan_core if era.exp_mpan_core is None: exp_mpan_core_str = "NA" else: exp_mpan_core_str = era.exp_mpan_core imp_related_str = "TRUE" if imp_related else "FALSE" hh_data = iter( sess.query(HhDatum).join(Channel).join(Era).filter( Era.supply == supply, HhDatum.start_date >= start_date, HhDatum.start_date <= finish_date, Channel.imp_related == imp_related, Channel.channel_type == channel_type).order_by( HhDatum.start_date)) datum = next(hh_data, None) for current_date in hh_range(cache, start_date, finish_date): if current_date.hour == 0 and current_date.minute == 0: outs.append("\n" + imp_mpan_core_str + "," + exp_mpan_core_str + "," + imp_related_str + "," + channel_type + "," + current_date.strftime('%Y-%m-%d')) outs.append(",") if datum is not None and datum.start_date == current_date: outs.append(str(datum.value)) datum = next(hh_data, None) if is_zipped: fname = '_'.join((imp_mpan_core_str, exp_mpan_core_str, str(supply.id) + '.csv')) zf.writestr(fname.encode('ascii'), titles + ''.join(outs)) else: tf.write(''.join(outs)) outs = [] # Avoid long-running transaction sess.rollback() if is_zipped: zf.close() else: tf.close() except BaseException: msg = traceback.format_exc() if is_zipped: zf.writestr('error.txt', msg) zf.close() else: tf.write(msg) finally: if sess is not None: sess.close() os.rename(running_name, finished_name)
def content(contract_id, days_hidden, user): sess = f = writer = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'channel_snags.csv', user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') writer.writerow( ( 'Hidden Days', 'Chellow Id', 'Imp MPAN Core', 'Exp MPAN Core', 'Site Code', 'Site Name', 'Snag Description', 'Import Related?', 'Channel Type', 'Start Date', 'Finish Date', 'Days Since Snag Finished', 'Duration Of Snag (Days)', 'Is Ignored?')) contract = Contract.get_hhdc_by_id(sess, contract_id) now = Datetime.now(pytz.utc) cutoff_date = now - relativedelta(days=days_hidden) for snag, channel, era, supply, site_era, site in sess.query( Snag, Channel, Era, Supply, SiteEra, Site).join( Channel, Era, Supply, SiteEra, Site).filter( SiteEra.is_physical == true(), Era.hhdc_contract == contract, Snag.start_date < cutoff_date).order_by( Site.code, Supply.id, Channel.imp_related, Channel.channel_type, Snag.description, Snag.start_date, Snag.id): snag_start = snag.start_date snag_finish = snag.finish_date if snag_finish is None: snag_finish_str = '' duration = now - snag_start age_of_snag = datetime.timedelta(0) else: snag_finish_str = snag_finish.strftime("%Y-%m-%d %H:%M") duration = snag_finish - snag_start age_of_snag = now - snag_finish writer.writerow( ( str(days_hidden), str(snag.id), '' if era.imp_mpan_core is None else era.imp_mpan_core, '' if era.exp_mpan_core is None else era.exp_mpan_core, site.code, site.name, snag.description, str(channel.imp_related), channel.channel_type, snag_start.strftime("%Y-%m-%d %H:%M"), snag_finish_str, str(age_of_snag.days + age_of_snag.seconds / (3600 * 24)), str(duration.days + duration.seconds / (3600 * 24)), str(snag.is_ignored))) 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(start_date, finish_date, site_id, user): sess = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'sites_duration.csv', user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') writer.writerow( ( "Site Id", "Site Name", "Associated Site Ids", "Sources", "Generator Types", "From", "To", "Imported kWh", "Displaced kWh", "Exported kWh", "Used kWh", "Parasitic kWh", "Generated kWh", "Meter Type")) streams = ( 'imp_net', 'displaced', 'exp_net', 'used', 'exp_gen', 'imp_gen') sites = sess.query(Site).order_by(Site.code) if site_id is not None: sites = sites.filter(Site.id == site_id) start_date_str = hh_format(start_date) finish_date_str = hh_format(finish_date) for site in sites: assoc = ' '.join( s.code for s in site.find_linked_sites( sess, start_date, finish_date)) totals = dict((stream, 0) for stream in streams) metering_type = '' source_codes = set() gen_types = set() for era in sess.query(Era).join(SiteEra).filter( SiteEra.is_physical == true(), SiteEra.site == site, Era.start_date <= finish_date, or_( Era.finish_date == null(), Era.finish_date >= start_date)).distinct().options( joinedload(Era.supply).joinedload(Supply.source), joinedload(Era.supply). joinedload(Supply.generator_type)): supply = era.supply source_codes.add(supply.source.code) gen_type = supply.generator_type if gen_type is not None: gen_types.add(gen_type.code) era_meter_type = era.make_meter_category() if METER_ORDER[era_meter_type] < METER_ORDER[metering_type]: metering_type = era_meter_type assoc_str = ','.join(sorted(assoc)) sources_str = ','.join(sorted(source_codes)) generators_str = ','.join(sorted(gen_types)) for hh in site.hh_data(sess, start_date, finish_date): for stream in streams: totals[stream] += hh[stream] writer.writerow( ( site.code, site.name, assoc_str, sources_str, generators_str, start_date_str, finish_date_str, totals['imp_net'], totals['displaced'], totals['exp_net'], totals['used'], totals['exp_gen'], totals['imp_gen'], metering_type)) 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 run(self): while not self.stopped.isSet(): if self.lock.acquire(False): sess = None try: sess = Session() self.log("Starting to check BSUoS rates.") contract = Contract.get_non_core_by_name(sess, 'bsuos') latest_rs = sess.query(RateScript).filter( RateScript.contract == contract).order_by( RateScript.start_date.desc()).first() latest_rs_id = latest_rs.id this_month_start = latest_rs.start_date + \ relativedelta(months=1) next_month_start = this_month_start + \ relativedelta(months=1) now = Datetime.now(pytz.utc) props = contract.make_properties() if props.get('enabled', False): if now > next_month_start: url = props['url'] self.log( "Checking to see if data is available from " + str(this_month_start) + " to " + str(next_month_start - HH) + " at " + url) res = requests.get(url) self.log( "Received " + str(res.status_code) + " " + res.reason) book = xlrd.open_workbook( file_contents=res.content) sheet = book.sheet_by_index(0) ct_tz = pytz.timezone('Europe/London') month_bsuos = {} for row_index in range(1, sheet.nrows): row = sheet.row(row_index) raw_date = Datetime( *xlrd.xldate_as_tuple( row[0].value, book.datemode)) hh_date_ct = ct_tz.localize(raw_date) hh_date = pytz.utc.normalize( hh_date_ct.astimezone(pytz.utc)) hh_date += relativedelta( minutes=30*int(row[1].value)) if not hh_date < this_month_start and \ hh_date < next_month_start: month_bsuos[key_format(hh_date)] = \ row[2].value if key_format(next_month_start - HH) in \ month_bsuos: self.log("The whole month's data is there.") script = "def rates_gbp_per_mwh():\n " \ "return {\n" + ',\n'.join( "'" + k + "': " + str(month_bsuos[k]) for k in sorted( month_bsuos.keys())) + "}" set_read_write(sess) contract = Contract.get_non_core_by_name( sess, 'bsuos') rs = RateScript.get_by_id(sess, latest_rs_id) contract.update_rate_script( sess, rs, rs.start_date, rs.start_date + relativedelta(months=2) - HH, rs.script) sess.flush() contract.insert_rate_script( sess, rs.start_date + relativedelta(months=1), script) sess.commit() self.log("Added new rate script.") else: self.log( "There isn't a whole month there yet. The " "last date is " + sorted(month_bsuos.keys())[-1]) else: self.log( "The automatic importer is disabled. To " "enable it, edit the contract properties to " "set 'enabled' to True.") except: self.log("Outer problem " + traceback.format_exc()) sess.rollback() finally: if sess is not None: sess.close() self.lock.release() self.log("Finished checking BSUoS rates.") self.going.wait(30 * 60) self.going.clear()
def site_content(site_id, start_date, finish_date, user, file_name): sess = f = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( file_name, user) f = open(running_name, mode="w", newline="") writer = csv.writer(f, lineterminator="\n") site = Site.get_by_id(sess, site_id) sites = sess.query(Site).filter(Site.id == site_id) start_date_str = hh_format(start_date) finish_date_str = hh_format(finish_date) for site in sites: writer.writerow([ "Site Code", "Site Name", "Associated Site Codes", "Sources", "Generator Types", "From", "To", "Type", "Date", ] + list(map(str, range(1, 51)))) associates = " ".join( s.code for s in site.find_linked_sites(sess, start_date, finish_date)) source_codes = set() gen_types = set() for supply in (sess.query(Supply).join(Era).join(SiteEra).filter( SiteEra.is_physical == true(), SiteEra.site == site, Era.start_date <= finish_date, or_(Era.finish_date == null(), Era.finish_date >= start_date), ).distinct().options(joinedload(Supply.source), joinedload(Supply.generator_type))): source_codes.add(supply.source.code) gen_type = supply.generator_type if gen_type is not None: gen_types.add(gen_type.code) source_codes_str = ", ".join(sorted(source_codes)) gen_types_str = ", ".join(sorted(gen_types)) vals = None for hh in site.hh_data(sess, start_date, finish_date): hh_start_ct = to_ct(hh["start_date"]) if hh_start_ct.hour == 0 and hh_start_ct.minute == 0: if vals is not None: writer.writerow(vals) vals = [ site.code, site.name, associates, source_codes_str, gen_types_str, start_date_str, finish_date_str, "used", hh_start_ct.strftime("%Y-%m-%d"), ] used_gen_kwh = hh["imp_gen"] - hh["exp_net"] - hh["exp_gen"] used_3p_kwh = hh["imp_3p"] - hh["exp_3p"] used_kwh = hh["imp_net"] + used_gen_kwh + used_3p_kwh vals.append(str(round(used_kwh, 2))) if vals is not None: writer.writerow(vals) except BaseException: msg = traceback.format_exc() sys.stderr.write(msg) f.write(msg) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def content(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(start_date, finish_date, g_contract_id, user): report_context = {} sess = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'gas_virtual_bills.csv', user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') g_contract = GContract.get_by_id(sess, g_contract_id) forecast_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 = chellow.computer.contract_func( report_context, g_contract, 'virtual_bill_titles', None)() writer.writerow( [ 'MPRN', '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 g_era in sess.query(GEra).distinct().filter( or_( GEra.imp_g_contract == g_contract, GEra.exp_g_contract == g_contract), GEra.start_date <= period_finish, or_( GEra.finish_date == null(), GEra.finish_date >= period_start)): g_era_start = g_era.start_date if period_start < g_era_start: chunk_start = g_era_start else: chunk_start = period_start g_era_finish = g_era.finish_date if hh_after(period_finish, g_era_finish): chunk_finish = g_era_finish else: chunk_finish = period_finish polarities = [] if g_era.imp_g_contract == g_contract: polarities.append(True) if g_era.exp_g_contract == g_contract: polarities.append(False) for polarity in polarities: data_source = chellow.g_engine.DataSource( sess, chunk_start, chunk_finish, forecast_date, g_era, polarity, None, report_context) site = sess.query(Site).join(SiteGEra).filter( SiteGEra.g_era == g_era, SiteGEra.is_physical == true()).one() vals = [ data_source.mprn, site.code, site.name, data_source.supplier_account, hh_format(data_source.start_date), hh_format(data_source.finish_date)] chellow.computer.contract_func( report_context, g_contract, 'virtual_bill', None)(data_source) bill = data_source.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: 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 run(self): sess = None try: sess = Session() self._log( "Starting to parse the file with '" + self.parser_name + "'.") bill_types = keydefaultdict( lambda k: BillType.get_by_code(sess, k)) tprs = keydefaultdict( lambda k: None if k is None else Tpr.get_by_code(sess, k)) read_types = keydefaultdict( lambda k: ReadType.get_by_code(sess, k)) batch = Batch.get_by_id(sess, self.batch_id) contract = batch.contract 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: account = raw_bill['account'] supply = sess.query(Supply).join(Era).filter( or_( and_( Era.imp_supplier_contract == contract, Era.imp_supplier_account == account), and_( Era.exp_supplier_contract == contract, Era.exp_supplier_account == account), and_( Era.mop_contract == contract, Era.mop_account == account), and_( Era.hhdc_contract == contract, Era.hhdc_account == account)) ).distinct().order_by(Supply.id).first() if supply is None: raise BadRequest( "Can't find an era with contract '" + contract.name + "' and account '" + account + "'.") with sess.begin_nested(): bill = batch.insert_bill( sess, 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_types[raw_bill['bill_type_code']], raw_bill['breakdown'], supply) for raw_read in raw_bill['reads']: bill.insert_read( sess, tprs[raw_read['tpr_code']], raw_read['coefficient'], raw_read['units'], raw_read['msn'], raw_read['mpan'], raw_read['prev_date'], raw_read['prev_value'], read_types[raw_read['prev_type_code']], raw_read['pres_date'], raw_read['pres_value'], read_types[raw_read['pres_type_code']]) 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( 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): while not self.stopped.isSet(): if self.lock.acquire(False): sess = book = sbp_sheet = ssp_sheet = None try: sess = Session() self.log("Starting to check System Prices.") # ct_tz = pytz.timezone('Europe/London') contract = Contract.get_non_core_by_name( sess, 'system_price') contract_props = contract.make_properties() if contract_props.get('enabled', False): for rscript in sess.query(RateScript).filter( RateScript.contract == contract).order_by( RateScript.start_date.desc()): ns = json.loads(rscript.script) rates = ns['gbp_per_nbp_mwh'] if len(rates) == 0: fill_start = rscript.start_date break elif rates[ key_format( rscript.finish_date)]['run'] == 'DF': fill_start = rscript.finish_date + HH break config = Contract.get_non_core_by_name( sess, 'configuration') config_props = config.make_properties() scripting_key = config_props.get( ELEXON_PORTAL_SCRIPTING_KEY_KEY) if scripting_key is None: raise BadRequest( "The property " + ELEXON_PORTAL_SCRIPTING_KEY_KEY + " cannot be found in the configuration " "properties.") url_str = contract_props['url'] + \ 'file/download/BESTVIEWPRICES_FILE?key=' + \ scripting_key self.log( "Downloading from " + url_str + " and extracting data from " + hh_format(fill_start)) url = urllib.parse.urlparse(url_str) if url.scheme == 'https': conn = http.client.HTTPSConnection( url.hostname, url.port) else: conn = http.client.HTTPConnection( url.hostname, url.port) conn.request("GET", url.path + '?' + url.query) res = conn.getresponse() self.log( "Received " + str(res.status) + " " + res.reason) data = res.read() book = xlrd.open_workbook(file_contents=data) sbp_sheet = book.sheet_by_index(1) ssp_sheet = book.sheet_by_index(2) sp_months = [] sp_month = None for row_index in range(1, sbp_sheet.nrows): sbp_row = sbp_sheet.row(row_index) ssp_row = ssp_sheet.row(row_index) raw_date = datetime.datetime( *xlrd.xldate_as_tuple( sbp_row[0].value, book.datemode)) hh_date_ct = to_ct(raw_date) hh_date = to_utc(hh_date_ct) run_code = sbp_row[1].value for col_idx in range(2, 52): if hh_date >= fill_start: sbp_val = sbp_row[col_idx].value if sbp_val != '': if hh_date.day == 1 and \ hh_date.hour == 0 and \ hh_date.minute == 0: sp_month = {} sp_months.append(sp_month) ssp_val = ssp_row[col_idx].value sp_month[hh_date] = { 'run': run_code, 'sbp': sbp_val, 'ssp': ssp_val} hh_date += HH self.log("Successfully extracted data.") last_date = sorted(sp_months[-1].keys())[-1] if last_date.month == (last_date + HH).month: del sp_months[-1] if 'limit' in contract_props: sp_months = sp_months[0:1] for sp_month in sp_months: sorted_keys = sorted(sp_month.keys()) month_start = sorted_keys[0] month_finish = sorted_keys[-1] rs = sess.query(RateScript).filter( RateScript.contract == contract, RateScript.start_date == month_start).first() if rs is None: self.log( "Adding a new rate script starting at " + hh_format(month_start) + ".") latest_rs = sess.query(RateScript).filter( RateScript.contract == contract).\ order_by(RateScript.start_date.desc()). \ first() contract.update_rate_script( sess, latest_rs, latest_rs.start_date, month_finish, latest_rs.script) rs = contract.insert_rate_script( sess, month_start, '') sess.flush() script = { 'gbp_per_nbp_mwh': dict( (key_format(k), v) for k, v in sp_month.items())} self.log( "Updating rate script starting at " + hh_format(month_start) + ".") contract.update_rate_script( sess, rs, rs.start_date, rs.finish_date, json.dumps( script, indent=' ', sort_keys=True)) sess.commit() else: self.log( "The automatic importer is disabled. To " "enable it, edit the contract properties to " "set 'enabled' to True.") except: self.log("Outer problem " + traceback.format_exc()) sess.rollback() finally: book = sbp_sheet = ssp_sheet = None self.lock.release() self.log("Finished checking System Price rates.") if sess is not None: sess.close() self.going.wait(24 * 60 * 60) self.going.clear()
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() prev_titles = None running_name, finished_name = chellow.dloads.make_names( 'supply_virtual_bills_hh_' + str(supply_id) + '.csv', user) f = open(running_name, mode='w', newline='') w = csv.writer(f, lineterminator='\n') for hh_start in hh_range(start_date, finish_date): era = sess.query(Era).filter( Era.supply == supply, Era.start_date <= hh_start, or_( Era.finish_date == null(), Era.finish_date >= hh_start)).one() site = sess.query(Site).join(SiteEra).filter( SiteEra.era == era, SiteEra.is_physical == true()).one() ds = chellow.computer.SupplySource( sess, hh_start, hh_start, forecast_date, era, True, caches) titles = [ 'MPAN Core', 'Site Code', 'Site Name', 'Account', 'HH Start', ''] output_line = [ ds.mpan_core, site.code, site.name, ds.supplier_account, hh_format(ds.start_date), ''] mop_titles = ds.contract_func( era.mop_contract, 'virtual_bill_titles')() titles.extend(['mop-' + t for t in mop_titles]) ds.contract_func(era.mop_contract, 'virtual_bill')(ds) bill = ds.mop_bill for title in mop_titles: output_line.append(bill.get(title, '')) if title in bill: del bill[title] for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) output_line.append('') dc_titles = ds.contract_func( era.hhdc_contract, 'virtual_bill_titles')() titles.append('') titles.extend(['dc-' + t for t in dc_titles]) ds.contract_func(era.hhdc_contract, 'virtual_bill')(ds) bill = ds.dc_bill for title in dc_titles: output_line.append(bill.get(title, '')) if title in bill: del bill[title] for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) if era.imp_supplier_contract is not None: contract = era.imp_supplier_contract output_line.append('') supplier_titles = ds.contract_func( contract, 'virtual_bill_titles')() titles.append('') titles.extend(['imp-supplier-' + t for t in supplier_titles]) ds.contract_func(contract, 'virtual_bill')(ds) bill = ds.supplier_bill for title in supplier_titles: output_line.append(bill.get(title, '')) if title in bill: del bill[title] for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) if era.exp_supplier_contract is not None: contract = era.exp_supplier_contract ds = chellow.computer.SupplySource( sess, hh_start, hh_start, forecast_date, era, False, caches) output_line.append('') supplier_titles = ds.contract_func( contract, 'virtual_bill_titles')() titles.append('') titles.extend(['exp-supplier-' + t for t in supplier_titles]) ds.contract_func(contract, 'virtual_bill')(ds) bill = ds.supplier_bill for title in supplier_titles: output_line.append(bill.get(title, '')) if title in bill: del bill[title] for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) if titles != prev_titles: prev_titles = titles w.writerow(titles) w.writerow(output_line) except: 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 run(self): while not self.stopped.isSet(): if self.lock.acquire(False): sess = None try: sess = Session() self.log("Starting to check RCRCs.") contract = Contract.get_non_core_by_name(sess, 'rcrc') latest_rs = sess.query(RateScript).filter( RateScript.contract_id == contract.id).order_by( RateScript.start_date.desc()).first() latest_rs_id = latest_rs.id latest_rs_start = latest_rs.start_date month_start = latest_rs_start + relativedelta(months=1) month_finish = month_start + relativedelta(months=1) - HH now = Datetime.now(pytz.utc) if now > month_finish: self.log( "Checking to see if data is available from " + str(month_start) + " to " + str(month_finish) + " on Elexon Portal.") config = Contract.get_non_core_by_name( sess, 'configuration') props = config.make_properties() scripting_key = props.get( ELEXON_PORTAL_SCRIPTING_KEY_KEY) if scripting_key is None: raise BadRequest( "The property " + ELEXON_PORTAL_SCRIPTING_KEY_KEY + " cannot be found in the configuration " "properties.") contract_props = contract.make_properties() url_str = ''.join( ( contract_props['url'], 'file/download/RCRC_FILE?key=', scripting_key)) r = requests.get(url_str) parser = csv.reader( (l.decode() for l in r.iter_lines()), delimiter=',', quotechar='"') piterator = iter(parser) values = next(piterator) values = next(piterator) month_rcrcs = {} for values in piterator: hh_date = Datetime.strptime( values[0], "%d/%m/%Y").replace(tzinfo=pytz.utc) hh_date += relativedelta(minutes=30*int(values[2])) if month_start <= hh_date <= month_finish: month_rcrcs[key_format(hh_date)] = values[3] if key_format(month_finish) in month_rcrcs: self.log("The whole month's data is there.") script = "def rates():\n return {\n" + \ ',\n'.join( "'" + k + "': " + month_rcrcs[k] for k in sorted(month_rcrcs.keys())) + "}" set_read_write(sess) contract = Contract.get_non_core_by_name( sess, 'rcrc') rs = RateScript.get_by_id(sess, latest_rs_id) contract.update_rate_script( sess, rs, rs.start_date, month_finish, rs.script) contract.insert_rate_script( sess, month_start, script) sess.commit() self.log("Added new rate script.") else: msg = "There isn't a whole month there yet." if len(month_rcrcs) > 0: msg += " The last date is " + \ sorted(month_rcrcs.keys())[-1] self.log(msg) except: self.log("Outer problem " + traceback.format_exc()) sess.rollback() finally: self.lock.release() self.log("Finished checking RCRC rates.") if sess is not None: sess.close() self.going.wait(30 * 60) self.going.clear()
def make_raw_bills(self): row_index = sess = None try: sess = Session() bills = [] title_row = self.sheet.row(10) issue_date = get_date(self.sheet.row(5), 2, self.book.datemode) if issue_date is None: raise BadRequest("Expected to find the issue date at cell C6.") for row_index in range(11, self.sheet.nrows): row = self.sheet.row(row_index) val = get_value(row, 1) if val is None or val == '': break self._set_last_line(row_index, val) mpan_core = parse_mpan_core(str(get_int(row, 1))) start_date = finish_date = get_date(row, 5, self.book.datemode) activity_name_raw = get_str(row, 6) activity_name = activity_name_raw.lower().replace(' ', '_') net_dec = get_dec(row, 8) if net_dec is None: raise BadRequest( "Can't find a decimal at column I, expecting the net " "GBP.") net = round(net_dec, 2) era = sess.query(Era).filter( or_(Era.imp_mpan_core == mpan_core, Era.exp_mpan_core == mpan_core), Era.start_date <= finish_date, or_(Era.finish_date == null(), Era.finish_date > start_date)).order_by( Era.start_date).first() if era is None: era = sess.query(Era).filter( or_(Era.imp_mpan_core == mpan_core, Era.exp_mpan_core == mpan_core)).order_by( Era.start_date.desc()).first() if era is None: account = mpan_core + '/MOP' else: account = era.mop_account breakdown = { 'raw-lines': [str(title_row)], 'activity-name': [activity_name], 'activity-gbp': net } bills.append({ 'bill_type_code': 'N', 'kwh': Decimal(0), 'vat': Decimal('0.00'), 'net': net, 'gross': net, 'reads': [], 'breakdown': breakdown, 'account': account, 'issue_date': issue_date, 'start_date': start_date, 'finish_date': finish_date, 'mpans': [mpan_core], 'reference': '_'.join((start_date.strftime('%Y%m%d'), finish_date.strftime('%Y%m%d'), issue_date.strftime('%Y%m%d'), mpan_core)) }) except BadRequest as e: raise BadRequest("Row number: " + str(row_index) + " " + e.description) finally: if sess is not None: sess.close() return bills
def content(g_batch_id, g_bill_id, user): forecast_date = to_utc(Datetime.max) report_context = {} sess = tmp_file = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( "g_bill_check.csv", user) tmp_file = open(running_name, "w") csv_writer = csv.writer(tmp_file) if g_batch_id is not None: g_batch = GBatch.get_by_id(sess, g_batch_id) g_bills = (sess.query(GBill).filter( GBill.g_batch == g_batch).order_by(GBill.reference)) elif g_bill_id is not None: g_bill = GBill.get_by_id(sess, g_bill_id) g_bills = sess.query(GBill).filter(GBill.id == g_bill.id) g_batch = g_bill.g_batch g_contract = g_batch.g_contract vbf = chellow.g_engine.g_contract_func(report_context, g_contract, "virtual_bill") if vbf is None: raise BadRequest( f"The contract {g_contract.name} doesn't have a function " f"virtual_bill.") header_titles = [ "batch", "bill_reference", "bill_type", "bill_start_date", "bill_finish_date", "mprn", "supply_name", "site_code", "site_name", "covered_start", "covered_finish", "covered_bill_ids", ] bill_titles = chellow.g_engine.g_contract_func(report_context, g_contract, "virtual_bill_titles")() titles = header_titles[:] for title in bill_titles: for prefix in ("covered_", "virtual_"): titles.append(prefix + title) if title.endswith("_gbp"): titles.append("difference_" + title) csv_writer.writerow(titles) g_bill_map = defaultdict(set, {}) for b in g_bills: g_bill_map[b.g_supply.id].add(b.id) for g_supply_id, g_bill_ids in g_bill_map.items(): while len(g_bill_ids) > 0: _process_g_bill_ids( sess, report_context, g_bill_ids, forecast_date, bill_titles, vbf, titles, csv_writer, ) except BadRequest as e: tmp_file.write("Problem: " + e.description) except BaseException: msg = traceback.format_exc() sys.stderr.write(msg + "\n") tmp_file.write("Problem " + msg) finally: try: if sess is not None: sess.close() except BaseException: tmp_file.write("\nProblem closing session.") finally: tmp_file.close() os.rename(running_name, finished_name)
def content(batch_id, bill_id, user): caches = {} tmp_file = sess = None forecast_date = Datetime.max.replace(tzinfo=pytz.utc) try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'bill_check.csv', user) tmp_file = open(running_name, mode='w', newline='') writer = csv.writer(tmp_file, lineterminator='\n') if batch_id is not None: batch = Batch.get_by_id(sess, batch_id) bills = sess.query(Bill).filter( Bill.batch_id == batch.id).order_by(Bill.reference) elif bill_id is not None: bill = Bill.get_by_id(sess, bill_id) bills = sess.query(Bill).filter(Bill.id == bill.id) batch = bill.batch contract = batch.contract market_role_code = contract.market_role.code vbf = chellow.computer.contract_func( caches, contract, 'virtual_bill', None) if vbf is None: raise BadRequest( 'The contract ' + contract.name + " doesn't have a function virtual_bill.") virtual_bill_titles_func = chellow.computer.contract_func( caches, contract, 'virtual_bill_titles', None) if virtual_bill_titles_func is None: raise BadRequest( 'The contract ' + contract.name + " doesn't have a function virtual_bill_titles.") virtual_bill_titles = virtual_bill_titles_func() titles = [ 'batch', 'bill-reference', 'bill-type', 'bill-kwh', 'bill-net-gbp', 'bill-vat-gbp', 'bill-start-date', 'bill-finish-date', 'bill-mpan-core', 'site-code', 'site-name', 'covered-from', 'covered-to', 'covered-bills', 'metered-kwh'] for t in virtual_bill_titles: titles.append('covered-' + t) titles.append('virtual-' + t) if t.endswith('-gbp'): titles.append('difference-' + t) writer.writerow(titles) for bill in bills: problem = '' supply = bill.supply read_dict = {} for read in bill.reads: gen_start = read.present_date.replace(hour=0).replace(minute=0) gen_finish = gen_start + relativedelta(days=1) - HH msn_match = False read_msn = read.msn for read_era in supply.find_eras(sess, gen_start, gen_finish): if read_msn == read_era.msn: msn_match = True break if not msn_match: problem += "The MSN " + read_msn + \ " of the register read " + str(read.id) + \ " doesn't match the MSN of the era." for dt, type in [ (read.present_date, read.present_type), (read.previous_date, read.previous_type)]: key = str(dt) + "-" + read.msn try: if type != read_dict[key]: problem += " Reads taken on " + str(dt) + \ " have differing read types." except KeyError: read_dict[key] = type bill_start = bill.start_date bill_finish = bill.finish_date era = supply.find_era_at(sess, bill.finish_date) if era is None: raise BadRequest( "Extraordinary! There isn't an era for the bill " + str(bill.id) + ".") values = [ batch.reference, bill.reference, bill.bill_type.code, bill.kwh, bill.net, bill.vat, hh_format(bill_start), hh_format(bill_finish), era.imp_mpan_core] covered_start = bill_start covered_finish = bill_finish covered_bill_ids = [] covered_bdown = {'sum-msp-kwh': 0, 'net-gbp': 0, 'vat-gbp': 0} covered_primary_bill = None enlarged = True while enlarged: enlarged = False covered_bills = [] cand_bills = dict( (b.id, b) for b in sess.query(Bill).join(Batch). join(Contract).join(MarketRole).filter( Bill.supply == supply, Bill.start_date <= covered_finish, Bill.finish_date >= covered_start, MarketRole.code == market_role_code).order_by( Bill.issue_date.desc(), Bill.start_date)) while True: to_del = None for a, b in combinations(cand_bills.values(), 2): if all( ( a.start_date == b.start_date, a.finish_date == b.finish_date, a.kwh == -1 * b.kwh, a.net == -1 * b.net, a.vat == -1 * b.vat, a.gross == -1 * b.gross)): to_del = (a.id, b.id) break if to_del is None: break else: for k in to_del: del cand_bills[k] for cand_bill_id in sorted(cand_bills.keys()): cand_bill = cand_bills[cand_bill_id] if covered_primary_bill is None and \ len(cand_bill.reads) > 0: covered_primary_bill = cand_bill if cand_bill.start_date < covered_start: covered_start = cand_bill.start_date enlarged = True break if cand_bill.finish_date > covered_finish: covered_finish = cand_bill.finish_date enlarged = True break covered_bills.append(cand_bill) for covered_bill in covered_bills: covered_bill_ids.append(covered_bill.id) covered_bdown['net-gbp'] += float(covered_bill.net) covered_bdown['vat-gbp'] += float(covered_bill.vat) covered_bdown['sum-msp-kwh'] += float(covered_bill.kwh) if len(covered_bill.breakdown) > 0: covered_rates = collections.defaultdict(set) for k, v in eval(covered_bill.breakdown, {}).items(): if k.endswith('rate'): covered_rates[k].add(v) elif k != 'raw-lines': try: covered_bdown[k] += v except KeyError: covered_bdown[k] = v except TypeError as detail: raise BadRequest( "For key " + str(k) + " the value " + str(v) + " can't be added to the existing value " + str(covered_bdown[k]) + ". " + str(detail)) for k, v in covered_rates.items(): covered_bdown[k] = v.pop() if len(v) == 1 else None virtual_bill = {} metered_kwh = 0 for era in sess.query(Era).filter( Era.supply_id == supply.id, Era.imp_mpan_core != null(), Era.start_date <= covered_finish, or_( Era.finish_date == null(), Era.finish_date >= covered_start)).distinct(): site = sess.query(Site).join(SiteEra).filter( SiteEra.is_physical == true(), SiteEra.era_id == era.id).one() if covered_start > era.start_date: chunk_start = covered_start else: chunk_start = era.start_date if hh_before(covered_finish, era.finish_date): chunk_finish = covered_finish else: chunk_finish = era.finish_date data_source = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, True, None, caches, covered_primary_bill) if data_source.measurement_type == 'hh': metered_kwh += sum( h['msp-kwh'] for h in data_source.hh_data) else: ds = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, True, None, caches) metered_kwh += sum(h['msp-kwh'] for h in ds.hh_data) vbf(data_source) if market_role_code == 'X': vb = data_source.supplier_bill elif market_role_code == 'C': vb = data_source.dc_bill elif market_role_code == 'M': vb = data_source.mop_bill else: raise BadRequest("Odd market role.") for k, v in vb.items(): if k.endswith('-rate'): if k not in virtual_bill: virtual_bill[k] = set() virtual_bill[k].add(v) else: try: virtual_bill[k] += v except KeyError: virtual_bill[k] = v except TypeError as detail: raise BadRequest( "For key " + str(k) + " and value " + str(v) + ". " + str(detail)) values += [ site.code, site.name, hh_format(covered_start), hh_format(covered_finish), ','.join(str(id).replace(',', '') for id in covered_bill_ids), metered_kwh] for title in virtual_bill_titles: try: cov_val = covered_bdown[title] values.append(cov_val) del covered_bdown[title] except KeyError: cov_val = None values.append('') try: virt_val = virtual_bill[title] if isinstance(virt_val, set): virt_val = ', '.join(str(v) for v in virt_val) elif isinstance(virt_val, Datetime): virt_val = hh_format(virt_val) values.append(virt_val) del virtual_bill[title] except KeyError: virt_val = None values.append('') if title.endswith('-gbp'): if all(isinstance(val, (int, float)) for val in [ cov_val, virt_val]): values.append(cov_val - virt_val) else: values.append('') for title in sorted(virtual_bill.keys()): virt_val = virtual_bill[title] if isinstance(virt_val, set): virt_val = ', '.join(str(v) for v in virt_val) elif isinstance(virt_val, Datetime): virt_val = hh_format(virt_val) values += ['virtual-' + title, virt_val] if title in covered_bdown: values += ['covered-' + title, covered_bdown[title]] else: values += ['', ''] writer.writerow(values) except BadRequest as e: tmp_file.write("Problem: " + e.description) except: msg = traceback.format_exc() sys.stderr.write(msg + '\n') tmp_file.write("Problem " + msg) finally: if sess is not None: sess.close() tmp_file.close() os.rename(running_name, finished_name)
def content(year, month, months, supply_id, user): tmp_file = sess = None try: sess = Session() supplies = sess.query(Supply).join(Era).distinct() if supply_id is None: base_name = "supplies_monthly_duration_for_all_supplies_for_" + \ str(months) + "_to_" + str(year) + "_" + str(month) + ".csv" else: supply = Supply.get_by_id(sess, supply_id) supplies = supplies.filter(Supply.id == supply.id) base_name = "supplies_monthly_duration_for_" + str(supply.id) + \ "_" + str(months) + "_to_" + str(year) + "_" + str(month) + \ ".csv" running_name, finished_name = chellow.dloads.make_names( base_name, user) tmp_file = open(running_name, "w") caches = {} start_date = Datetime(year, month, 1, tzinfo=pytz.utc) - \ relativedelta(months=months-1) field_names = ( 'supply-name', 'source-code', 'generator-type', 'month', 'pc-code', 'msn', 'site-code', 'site-name', 'metering-type', 'import-mpan-core', 'metered-import-kwh', 'metered-import-net-gbp', 'metered-import-estimated-kwh', 'billed-import-kwh', 'billed-import-net-gbp', 'export-mpan-core', 'metered-export-kwh', 'metered-export-estimated-kwh', 'billed-export-kwh', 'billed-export-net-gbp', 'problem', 'timestamp') tmp_file.write('supply-id,' + ','.join(field_names) + '\n') forecast_date = chellow.computer.forecast_date() for i in range(months): month_start = start_date + relativedelta(months=i) month_finish = month_start + relativedelta(months=1) - HH for supply in supplies.filter( Era.start_date <= month_finish, or_( Era.finish_date == null(), Era.finish_date >= month_start)): generator_type = supply.generator_type if generator_type is None: generator_type = '' else: generator_type = generator_type.code source_code = supply.source.code eras = supply.find_eras(sess, month_start, month_finish) era = eras[-1] metering_type = era.make_meter_category() site = sess.query(Site).join(SiteEra).filter( SiteEra.era == era, SiteEra.is_physical == true()).one() values = { 'supply-name': supply.name, 'source-code': source_code, 'generator-type': generator_type, 'month': hh_format(month_finish), 'pc-code': era.pc.code, 'msn': era.msn, 'site-code': site.code, 'site-name': site.name, 'metering-type': metering_type, 'problem': ''} tmp_file.write(str(supply.id) + ',') for is_import, pol_name in [ (True, 'import'), (False, 'export')]: if is_import: mpan_core = era.imp_mpan_core else: mpan_core = era.exp_mpan_core values[pol_name + '-mpan-core'] = mpan_core kwh = 0 est_kwh = 0 if metering_type in ['hh', 'amr']: est_kwh = sess.query(HhDatum.value).join(Channel) \ .join(Era).filter( HhDatum.status == 'E', Era.supply_id == supply.id, Channel.channel_type == 'ACTIVE', Channel.imp_related == is_import, HhDatum.start_date >= month_start, HhDatum.start_date <= month_finish).first() if est_kwh is None: est_kwh = 0 else: est_kwh = est_kwh[0] if not (is_import and source_code in ('net', 'gen-net')): kwh_sum = sess.query( cast(func.sum(HhDatum.value), Float) ).join(Channel).join(Era).filter( Era.supply_id == supply.id, Channel.channel_type == 'ACTIVE', Channel.imp_related == is_import, HhDatum.start_date >= month_start, HhDatum.start_date <= month_finish).one()[0] if kwh_sum is not None: kwh += kwh_sum values['metered-' + pol_name + '-estimated-kwh'] = est_kwh values['metered-' + pol_name + '-kwh'] = kwh values['metered-' + pol_name + '-net-gbp'] = 0 values['billed-' + pol_name + '-kwh'] = 0 values['billed-' + pol_name + '-net-gbp'] = 0 values['billed-' + pol_name + '-apportioned-kwh'] = 0 values['billed-' + pol_name + '-apportioned-net-gbp'] = 0 values['billed-' + pol_name + '-raw-kwh'] = 0 values['billed-' + pol_name + '-raw-net-gbp'] = 0 for bill in sess.query(Bill).filter( Bill.supply == supply, Bill.start_date <= month_finish, Bill.finish_date >= month_start): bill_start = bill.start_date bill_finish = bill.finish_date bill_duration = ( bill_finish - bill_start).total_seconds() + 30 * 60 overlap_duration = ( min(bill_finish, month_finish) - max(bill_start, month_start)).total_seconds() + 30 * 60 overlap_proportion = float( overlap_duration) / float(bill_duration) values['billed-import-net-gbp'] += \ overlap_proportion * float(bill.net) values['billed-import-kwh'] += \ overlap_proportion * float(bill.kwh) for era in eras: if era.start_date > month_start: chunk_start = era.start_date else: chunk_start = month_start if hh_after(era.finish_date, month_finish): chunk_finish = month_finish else: chunk_finish = era.finish_date import_mpan_core = era.imp_mpan_core if import_mpan_core is None: continue supplier_contract = era.imp_supplier_contract if source_code in ['net', 'gen-net', '3rd-party']: supply_source = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, True, None, caches) values['metered-import-kwh'] += sum( datum['msp-kwh'] for datum in supply_source.hh_data) import_vb_function = supply_source.contract_func( supplier_contract, 'virtual_bill') if import_vb_function is None: values['problem'] += "Can't find the " \ "virtual_bill function in the supplier " \ "contract. " else: import_vb_function(supply_source) values['metered-import-net-gbp'] += \ supply_source.supplier_bill['net-gbp'] supply_source.contract_func( era.hhdc_contract, 'virtual_bill')(supply_source) values['metered-import-net-gbp'] += \ supply_source.dc_bill['net-gbp'] mop_func = supply_source.contract_func( era.mop_contract, 'virtual_bill') if mop_func is None: values['problem'] += " MOP virtual_bill " \ "function can't be found." else: mop_func(supply_source) mop_bill = supply_source.mop_bill values['metered-import-net-gbp'] += \ mop_bill['net-gbp'] if len(mop_bill['problem']) > 0: values['problem'] += \ " MOP virtual bill problem: " + \ mop_bill['problem'] values['timestamp'] = int(time.time() * 1000) tmp_file.write( ','.join( '"' + str(values[name]) + '"' for name in field_names) + '\n') except: tmp_file.write(traceback.format_exc()) finally: if sess is not None: sess.close() tmp_file.close() os.rename(running_name, finished_name)
def make_raw_bills(self): raw_bills = [] breakdown = None for self.line_number, code in enumerate(self.parser): if code == "BCD": ivdt = self.parser.elements[0] issue_date = to_utc(to_ct_date(ivdt[0])) invn = self.parser.elements[2] reference = invn[0] account = "SA" + reference[:9] btcd = self.parser.elements[5] bill_type_code = btcd[0] sumo = self.parser.elements[7] start_date = to_start_date(sumo[0]) if to_ct_date(sumo[1]) in ( ct_datetime(2020, 4, 1), ct_datetime(2020, 3, 16), ): finish_date = to_start_date(sumo[1]) - HH else: finish_date = to_finish_date(sumo[1]) elif code == "MHD": type = self.parser.elements[1] message_type = type[0] if message_type == "UTLBIL": issue_date = None start_date = None finish_date = None account = None reference = None net = Decimal("0.00") vat = Decimal("0.00") gross = Decimal("0.00") kwh = Decimal(0) reads = [] bill_type_code = None mpan_core = None breakdown = defaultdict(int, {"raw-lines": []}) elif code == "CCD": ccde = self.parser.elements[1] consumption_charge_indicator = ccde[0] if consumption_charge_indicator == "1": prdt = self.parser.elements[6] pvdt = self.parser.elements[7] pres_read_date = to_finish_date(prdt[0]) prev_read_date = to_finish_date(pvdt[0]) tmod = self.parser.elements[3] mtnr = self.parser.elements[4] mloc = self.parser.elements[5] mpan = mloc[0] mpan_core = " ".join( [mpan[:2], mpan[2:6], mpan[6:10], mpan[10:13]]) mpan = (mpan[13:15] + " " + mpan[15:18] + " " + mpan[18:] + " " + mpan_core) prrd = self.parser.elements[9] pres_read_type = read_type_map[prrd[1]] prev_read_type = read_type_map[prrd[3]] adjf = self.parser.elements[12] cons = self.parser.elements[13] coefficient = Decimal(adjf[1]) / Decimal(100000) pres_reading_value = Decimal(prrd[0]) prev_reading_value = Decimal(prrd[2]) msn = mtnr[0] tpr_native = tmod[0] if tpr_native not in tmod_map: raise BadRequest( "The TPR code " + tpr_native + " can't be found in the TPR list for mpan " + mpan + ".") tpr_code = tmod_map[tpr_native] if tpr_code == "kW": units = "kW" tpr_code = None elif tpr_code == "kVA": units = "kVA" tpr_code = None else: units = "kWh" kwh += to_decimal(cons) / Decimal("1000") if mpan_core in WRONG_TPRS and pres_read_date == to_utc( ct_datetime(2020, 4, 1, 23, 30)): pres_read_date = to_utc(ct_datetime( 2020, 4, 1, 22, 30)) reads.append({ "msn": "Separator Read", "mpan": mpan, "coefficient": coefficient, "units": units, "tpr_code": tpr_code, "prev_date": to_utc(ct_datetime(2020, 4, 1, 23)), "prev_value": 0, "prev_type_code": "N", "pres_date": to_utc(ct_datetime(2020, 4, 1, 23)), "pres_value": 0, "pres_type_code": "N", }) reads.append({ "msn": msn, "mpan": mpan, "coefficient": coefficient, "units": units, "tpr_code": tpr_code, "prev_date": prev_read_date, "prev_value": prev_reading_value, "prev_type_code": prev_read_type, "pres_date": pres_read_date, "pres_value": pres_reading_value, "pres_type_code": pres_read_type, }) elif consumption_charge_indicator == "2": # tcod = self.parser.elements[2] tmod = self.parser.elements[3] mtnr = self.parser.elements[4] mloc = self.parser.elements[5] mpan = mloc[0] mpan_core = " ".join( [mpan[:2], mpan[2:6], mpan[6:10], mpan[10:13]]) mpan = (mpan[13:15] + " " + mpan[15:18] + " " + mpan[18:] + " " + mpan_core) prdt = self.parser.elements[6] pvdt = self.parser.elements[7] pres_read_date = to_finish_date(prdt[0]) prev_read_date = to_finish_date(pvdt[0]) ndrp = self.parser.elements[8] prrd = self.parser.elements[9] pres_read_type = read_type_map[prrd[1]] prev_read_type = read_type_map[prrd[3]] adjf = self.parser.elements[12] cona = self.parser.elements[13] coefficient = Decimal(adjf[1]) / Decimal(100000) pres_reading_value = Decimal(prrd[0]) prev_reading_value = Decimal(prrd[2]) msn = mtnr[0] tpr_code = tmod[0] if tpr_code not in tmod_map: raise BadRequest( "The TPR code " + tpr_code + " can't be found in the TPR list for mpan " + mpan + ".") tpr = tmod_map[tpr_code] if tpr == "kW": units = "kW" tpr = None prefix = "md-" elif tpr == "kVA": units = "kVA" tpr = None prefix = "md-" else: units = "kWh" kwh += to_decimal(cona) / Decimal("1000") prefix = tpr + "-" nuct = self.parser.elements[15] breakdown[prefix + "kwh"] += to_decimal(nuct) / Decimal("1000") cppu = self.parser.elements[18] rate_key = prefix + "rate" if rate_key not in breakdown: breakdown[rate_key] = set() breakdown[rate_key].add( to_decimal(cppu) / Decimal("100000")) ctot = self.parser.elements[19] breakdown[prefix + "gbp"] += to_decimal(ctot) / Decimal("100") if mpan_core in WRONG_TPRS and pres_read_date == to_utc( ct_datetime(2020, 4, 1, 23, 30)): pres_read_date = to_utc(ct_datetime( 2020, 4, 1, 22, 30)) reads.append({ "msn": "Separator Read", "mpan": mpan, "coefficient": coefficient, "units": units, "tpr_code": tpr, "prev_date": to_utc(ct_datetime(2020, 4, 1, 23)), "prev_value": 0, "prev_type_code": "N", "pres_date": to_utc(ct_datetime(2020, 4, 1, 23)), "pres_value": 0, "pres_type_code": "N", }) reads.append({ "msn": msn, "mpan": mpan, "coefficient": coefficient, "units": units, "tpr_code": tpr, "prev_date": prev_read_date, "prev_value": prev_reading_value, "prev_type_code": prev_read_type, "pres_date": pres_read_date, "pres_value": pres_reading_value, "pres_type_code": pres_read_type, }) elif consumption_charge_indicator == "3": # tcod = self.parser.elements[2] tmod = self.parser.elements[3] tmod0 = tmod[0] if tmod0 == "CCL": prefix = kwh_prefix = "ccl-" elif tmod0 in ["CQFITC", "CMFITC"]: prefix = "fit-" kwh_prefix = "fit-msp-" elif tmod0 == "FITARR": prefix = kwh_prefix = "fit-reconciliation-" else: tpr_code = tmod0 if tpr_code not in tmod_map: raise BadRequest( "The TPR code " + tpr_code + " can't be found in the TPR list for mpan " + mpan + ".") prefix = kwh_prefix = tmod_map[tpr_code] + "-" mtnr = self.parser.elements[4] ndrp = self.parser.elements[8] cona = self.parser.elements[13] nuct = self.parser.elements[15] breakdown[kwh_prefix + "kwh"] += to_decimal(nuct) / Decimal("1000") cppu = self.parser.elements[18] rate_key = prefix + "rate" if rate_key not in breakdown: breakdown[rate_key] = set() breakdown[rate_key].add( to_decimal(cppu) / Decimal("100000")) ctot = self.parser.elements[19] breakdown[prefix + "gbp"] += to_decimal(ctot) / Decimal("100") elif consumption_charge_indicator == "4": # tcod = self.parser.elements[2] tmod = self.parser.elements[3] tmod0 = tmod[0] mtnr = self.parser.elements[4] ndrp = self.parser.elements[8] if len(ndrp[0]) > 0: breakdown["standing-days"] += to_decimal(ndrp) cona = self.parser.elements[13] nuct = self.parser.elements[15] cppu = self.parser.elements[18] ctot = self.parser.elements[19] if len(ctot[0]) > 0: breakdown["standing-gbp"] += to_decimal( ctot) / Decimal("100") elif code == "MTR": if message_type == "UTLBIL": if mpan_core is None: sess = Session() era = (sess.query(Era).filter( Era.imp_supplier_account == account).first()) if era is not None: mpan_core = era.imp_mpan_core sess.close() raw_bill = { "bill_type_code": bill_type_code, "account": account, "mpan_core": mpan_core, "reference": reference, "issue_date": issue_date, "start_date": start_date, "finish_date": finish_date, "kwh": kwh, "net": net, "vat": vat, "gross": gross, "breakdown": breakdown, "reads": reads, } raw_bills.append(raw_bill) breakdown = None elif code == "MAN": madn = self.parser.elements[2] """ pc_code = madn[3] mtc_code = madn[4] llfc_code = madn[5] """ mpan_core = parse_mpan_core("".join( (madn[0], madn[1], madn[2]))) elif code == "VAT": uvla = self.parser.elements[5] net += to_decimal(uvla) / Decimal("100") uvtt = self.parser.elements[6] vat += to_decimal(uvtt) / Decimal("100") ucsi = self.parser.elements[7] gross += to_decimal(ucsi) / Decimal("100") if breakdown is not None: breakdown["raw-lines"].append(self.parser.line) return raw_bills
def content(year, supply_id, user): caches = {} sess = f = writer = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'supplies_triad.csv', user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') march_start = Datetime(year, 3, 1, tzinfo=pytz.utc) march_finish = Datetime(year, 4, 1, tzinfo=pytz.utc) - HH def triad_csv(supply_source): if supply_source is None or \ supply_source.mpan_core.startswith('99'): return [''] * 19 chellow.duos.duos_vb(supply_source) chellow.triad.hh(supply_source) chellow.triad.bill(supply_source) bill = supply_source.supplier_bill for rname, rset in supply_source.supplier_rate_sets.items(): if len(rset) == 1: bill[rname] = rset.pop() values = [supply_source.mpan_core] for i in range(1, 4): triad_prefix = 'triad-actual-' + str(i) for suffix in [ '-date', '-msp-kw', '-status', '-laf', '-gsp-kw']: values.append(bill[triad_prefix + suffix]) suffixes = ['gsp-kw', 'rate', 'gbp'] values += [bill['triad-actual-' + suf] for suf in suffixes] return values writer.writerow( ( "Site Code", "Site Name", "Supply Name", "Source", "Generator Type", "Import MPAN Core", "Import T1 Date", "Import T1 MSP kW", "Import T1 Status", "Import T1 LAF", "Import T1 GSP kW", "Import T2 Date", "Import T2 MSP kW", "Import T2 Status", "Import T2 LAF", "Import T2 GSP kW", "Import T3 Date", "Import T3 MSP kW", "Import T3 Status", "Import T3 LAF", "Import T3 GSP kW", "Import GSP kW", "Import Rate GBP / kW", "Import GBP", "Export MPAN Core", "Export T1 Date", "Export T1 MSP kW", "Export T1 Status", "Export T1 LAF", "Export T1 GSP kW", "Export T2 Date", "Export T2 MSP kW", "Export T2 Status", "Export T2 LAF", "Export T2 GSP kW", "Export T3 Date", "Export T3 MSP kW", "Export T3 Status", "Export T3 LAF", "Export T3 GSP kW", "Export GSP kW", "Export Rate GBP / kW", "Export GBP")) forecast_date = chellow.computer.forecast_date() eras = sess.query(Era).join(Supply).join(Source).join(Pc).filter( Era.start_date <= march_finish, or_(Era.finish_date == null(), Era.finish_date >= march_start), Source.code.in_(('net', 'gen-net')), Pc.code == '00').order_by(Supply.id) if supply_id is not None: eras = eras.filter(Supply.id == supply_id) for era in eras: site = sess.query(Site).join(SiteEra).filter( SiteEra.is_physical == true(), SiteEra.era == era).one() supply = era.supply imp_mpan_core = era.imp_mpan_core if imp_mpan_core is None: imp_supply_source = None else: imp_supply_source = chellow.computer.SupplySource( sess, march_start, march_finish, forecast_date, era, True, caches) exp_mpan_core = era.exp_mpan_core if exp_mpan_core is None: exp_supply_source = None else: exp_supply_source = chellow.computer.SupplySource( sess, march_start, march_finish, forecast_date, era, False, caches) gen_type = supply.generator_type gen_type = '' if gen_type is None else gen_type.code vals = [] for value in [ site.code, site.name, supply.name, supply.source.code, gen_type] + triad_csv(imp_supply_source) + \ triad_csv(exp_supply_source): if isinstance(value, Datetime): vals.append(hh_format(value)) else: vals.append(str(value)) writer.writerow(vals) 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(year, supply_id, user): caches = {} sess = f = writer = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'supplies_triad.csv', user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') march_start = Datetime(year, 3, 1, tzinfo=pytz.utc) march_finish = Datetime(year, 4, 1, tzinfo=pytz.utc) - HH def triad_csv(supply_source): if supply_source is None or \ supply_source.mpan_core.startswith('99'): return [''] * 19 chellow.duos.duos_vb(supply_source) chellow.triad.hh(supply_source) chellow.triad.bill(supply_source) bill = supply_source.supplier_bill for rname, rset in supply_source.supplier_rate_sets.items(): if len(rset) == 1: bill[rname] = rset.pop() values = [supply_source.mpan_core] for i in range(1, 4): triad_prefix = 'triad-actual-' + str(i) for suffix in [ '-date', '-msp-kw', '-status', '-laf', '-gsp-kw' ]: values.append(bill[triad_prefix + suffix]) suffixes = ['gsp-kw', 'rate', 'gbp'] values += [bill['triad-actual-' + suf] for suf in suffixes] return values writer.writerow( ("Site Code", "Site Name", "Supply Name", "Source", "Generator Type", "Import MPAN Core", "Import T1 Date", "Import T1 MSP kW", "Import T1 Status", "Import T1 LAF", "Import T1 GSP kW", "Import T2 Date", "Import T2 MSP kW", "Import T2 Status", "Import T2 LAF", "Import T2 GSP kW", "Import T3 Date", "Import T3 MSP kW", "Import T3 Status", "Import T3 LAF", "Import T3 GSP kW", "Import GSP kW", "Import Rate GBP / kW", "Import GBP", "Export MPAN Core", "Export T1 Date", "Export T1 MSP kW", "Export T1 Status", "Export T1 LAF", "Export T1 GSP kW", "Export T2 Date", "Export T2 MSP kW", "Export T2 Status", "Export T2 LAF", "Export T2 GSP kW", "Export T3 Date", "Export T3 MSP kW", "Export T3 Status", "Export T3 LAF", "Export T3 GSP kW", "Export GSP kW", "Export Rate GBP / kW", "Export GBP")) forecast_date = chellow.computer.forecast_date() eras = sess.query(Era).join(Supply).join(Source).join(Pc).filter( Era.start_date <= march_finish, or_(Era.finish_date == null(), Era.finish_date >= march_start), Source.code.in_(('net', 'gen-net')), Pc.code == '00').order_by(Supply.id) if supply_id is not None: eras = eras.filter(Supply.id == supply_id) for era in eras: site = sess.query(Site).join(SiteEra).filter( SiteEra.is_physical == true(), SiteEra.era == era).one() supply = era.supply imp_mpan_core = era.imp_mpan_core if imp_mpan_core is None: imp_supply_source = None else: imp_supply_source = chellow.computer.SupplySource( sess, march_start, march_finish, forecast_date, era, True, caches) exp_mpan_core = era.exp_mpan_core if exp_mpan_core is None: exp_supply_source = None else: exp_supply_source = chellow.computer.SupplySource( sess, march_start, march_finish, forecast_date, era, False, caches) gen_type = supply.generator_type gen_type = '' if gen_type is None else gen_type.code vals = [] for value in [ site.code, site.name, supply.name, supply.source.code, gen_type] + triad_csv(imp_supply_source) + \ triad_csv(exp_supply_source): if isinstance(value, Datetime): vals.append(hh_format(value)) else: vals.append(str(value)) writer.writerow(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(year, site_id, user): caches = {} sess = f = writer = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'output.csv', user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') writer.writerow( ( "Site Code", "Site Name", "Displaced TRIAD 1 Date", "Displaced TRIAD 1 MSP kW", "Displaced TRIAD LAF", "Displaced TRIAD 1 GSP kW", "Displaced TRIAD 2 Date", "Displaced TRIAD 2 MSP kW", "Displaced TRIAD 2 LAF", "Displaced TRIAD 2 GSP kW", "Displaced TRIAD 3 Date", "Displaced TRIAD 3 MSP kW", "Displaced TRIAD 3 LAF", "Displaced TRIAD 3 GSP kW", "Displaced GSP kW", "Displaced Rate GBP / kW", "GBP")) march_finish = datetime.datetime(year, 4, 1, tzinfo=pytz.utc) - HH march_start = datetime.datetime(year, 3, 1, tzinfo=pytz.utc) forecast_date = chellow.computer.forecast_date() if site_id is None: sites = sess.query(Site).join(SiteEra).join(Era).join(Supply).join( Source).filter( Source.code.in_(('gen', 'gen-net')), Era.start_date <= march_finish, or_( Era.finish_date == null(), Era.finish_date >= march_start)).distinct() else: site = Site.get_by_id(sess, site_id) sites = sess.query(Site).filter(Site.id == site.id) for site in sites.order_by(Site.code): displaced_era = chellow.computer.displaced_era( sess, caches, site, march_start, march_finish, forecast_date) if displaced_era is None: continue site_ds = chellow.computer.SiteSource( sess, site, march_start, march_finish, forecast_date, caches, displaced_era) chellow.duos.duos_vb(site_ds) chellow.triad.hh(site_ds) chellow.triad.bill(site_ds) bill = site_ds.supplier_bill for rname, rset in site_ds.supplier_rate_sets.items(): if len(rset) == 1: bill[rname] = rset.pop() values = [site.code, site.name] for i in range(1, 4): triad_prefix = 'triad-actual-' + str(i) values.append(hh_format(bill[triad_prefix + '-date'])) for suffix in ['-msp-kw', '-laf', '-gsp-kw']: values.append(bill[triad_prefix + suffix]) values += [ str(bill['triad-actual-' + suf]) for suf in [ 'gsp-kw', 'rate', 'gbp']] writer.writerow(values) except BadRequest as e: writer.writerow([e.description]) except: writer.writerow([traceback.format_exc()]) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def content( start_date_ct, finish_date_ct, imp_related, channel_type, is_zipped, supply_id, mpan_cores, user, ): start_date, finish_date = to_utc(start_date_ct), to_utc(finish_date_ct) zf = sess = tf = None base_name = ["supplies_hh_data", finish_date_ct.strftime("%Y%m%d%H%M")] cache = {} try: sess = Session() supplies = (sess.query(Supply).join(Era).filter( or_(Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date, ).order_by(Supply.id).distinct()) if supply_id is not None: supply = Supply.get_by_id(sess, supply_id) supplies = supplies.filter(Supply.id == supply.id) first_era = (sess.query(Era).filter( Era.supply == supply, or_(Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date, ).order_by(Era.start_date).first()) if first_era.imp_mpan_core is None: name_core = first_era.exp_mpan_core else: name_core = first_era.imp_mpan_core base_name.append("supply_" + name_core.replace(" ", "_")) if mpan_cores is not None: supplies = supplies.filter( or_(Era.imp_mpan_core.in_(mpan_cores), Era.exp_mpan_core.in_(mpan_cores))) base_name.append("filter") cf = StringIO() writer = csv.writer(cf, lineterminator="\n") titles = [ "Import MPAN Core", "Export MPAN Core", "Import Related?", "Channel Type", "HH Start Clock-Time", ] + list(range(1, 51)) writer.writerow(titles) titles_csv = cf.getvalue() cf.close() running_name, finished_name = chellow.dloads.make_names( "_".join(base_name) + (".zip" if is_zipped else ".csv"), user) if is_zipped: zf = zipfile.ZipFile(running_name, "w", zipfile.ZIP_DEFLATED) else: tf = open(running_name, mode="w", newline="") tf.write(titles_csv) for supply in supplies: cf = StringIO() writer = csv.writer(cf, lineterminator="\n") era = supply.find_era_at(sess, finish_date) if era is None: imp_mpan_core_str = exp_mpan_core_str = "NA" else: if era.imp_mpan_core is None: imp_mpan_core_str = "NA" else: imp_mpan_core_str = era.imp_mpan_core if era.exp_mpan_core is None: exp_mpan_core_str = "NA" else: exp_mpan_core_str = era.exp_mpan_core imp_related_str = "TRUE" if imp_related else "FALSE" hh_data = iter( sess.query(HhDatum).join(Channel).join(Era).filter( Era.supply == supply, HhDatum.start_date >= start_date, HhDatum.start_date <= finish_date, Channel.imp_related == imp_related, Channel.channel_type == channel_type, ).order_by(HhDatum.start_date)) datum = next(hh_data, None) row = [] for current_date in hh_range(cache, start_date, finish_date): dt_ct = to_ct(current_date) if dt_ct.hour == 0 and dt_ct.minute == 0: if len(row) > 0: writer.writerow(row) row = [ imp_mpan_core_str, exp_mpan_core_str, imp_related_str, channel_type, dt_ct.strftime("%Y-%m-%d"), ] if datum is not None and datum.start_date == current_date: row.append(datum.value) datum = next(hh_data, None) else: row.append(None) if len(row) > 0: writer.writerow(row) if is_zipped: fname = "_".join((imp_mpan_core_str, exp_mpan_core_str, str(supply.id) + ".csv")) zf.writestr(fname.encode("ascii"), titles_csv + cf.getvalue()) else: tf.write(cf.getvalue()) cf.close() # Avoid long-running transaction sess.rollback() if is_zipped: zf.close() else: tf.close() except BaseException: msg = traceback.format_exc() if is_zipped: zf.writestr("error.txt", msg) zf.close() else: tf.write(msg) finally: if sess is not None: sess.close() os.rename(running_name, finished_name)
def site_content(site_id, start_date, finish_date, user, file_name): sess = f = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( file_name, user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') site = Site.get_by_id(sess, site_id) sites = sess.query(Site).filter(Site.id == site_id) start_date_str = hh_format(start_date) finish_date_str = hh_format(finish_date) for site in sites: writer.writerow( [ "Site Code", "Site Name", "Associated Site Codes", "Sources", "Generator Types", "From", "To", "Type", "Date"] + list(map(str, range(1, 49)))) associates = ' '.join( s.code for s in site.find_linked_sites( sess, start_date, finish_date)) source_codes = set() gen_types = set() for supply in sess.query(Supply).join(Era).join(SiteEra).filter( SiteEra.is_physical == true(), SiteEra.site == site, Era.start_date <= finish_date, or_( Era.finish_date == null(), Era.finish_date >= start_date)).distinct().options( joinedload(Supply.source), joinedload(Supply.generator_type)): source_codes.add(supply.source.code) gen_type = supply.generator_type if gen_type is not None: gen_types.add(gen_type.code) source_codes_str = ', '.join(sorted(source_codes)) gen_types_str = ', '.join(sorted(gen_types)) vals = None for hh in site.hh_data(sess, start_date, finish_date): hh_start = hh['start_date'] if hh_start.hour == 0 and hh_start.minute == 0: if vals is not None: writer.writerow(vals) vals = [ site.code, site.name, associates, source_codes_str, gen_types_str, start_date_str, finish_date_str, 'used', hh_start.strftime('%Y-%m-%d')] used_gen_kwh = hh['imp_gen'] - hh['exp_net'] - hh['exp_gen'] used_3p_kwh = hh['imp_3p'] - hh['exp_3p'] used_kwh = hh['imp_net'] + used_gen_kwh + used_3p_kwh vals.append(str(round(used_kwh, 2))) if vals is not None: writer.writerow(vals) except: msg = traceback.format_exc() sys.stderr.write(msg) f.write(msg) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def content( start_date, finish_date, imp_related, channel_type, is_zipped, supply_id, mpan_cores, user): zf = sess = tf = None base_name = ["supplies_hh_data", finish_date.strftime('%Y%m%d%H%M')] try: sess = Session() supplies = sess.query(Supply).join(Era).filter( or_(Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date).order_by(Supply.id).distinct() if supply_id is not None: supply = Supply.get_by_id(sess, supply_id) supplies = supplies.filter(Supply.id == supply.id) first_era = sess.query(Era).filter( Era.supply == supply, or_( Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date).order_by(Era.start_date).first() if first_era.imp_mpan_core is None: name_core = first_era.exp_mpan_core else: name_core = first_era.imp_mpan_core base_name.append("supply_" + name_core.replace(' ', '_')) if mpan_cores is not None: supplies = supplies.filter( or_( Era.imp_mpan_core.in_(mpan_cores), Era.exp_mpan_core.in_(mpan_cores))) base_name.append('filter') outs = [] titles = "MPAN Core,Date," + ','.join(map(str, range(48))) running_name, finished_name = chellow.dloads.make_names( '_'.join(base_name) + ('.zip' if is_zipped else '.csv'), user) if is_zipped: zf = zipfile.ZipFile(running_name, "w", zipfile.ZIP_DEFLATED) else: tf = open(running_name, "w") outs.append(titles) for supply in supplies: era = supply.find_era_at(sess, finish_date) if era is None or era.imp_mpan_core is None: mpan_core_str = "NA" else: mpan_core_str = era.imp_mpan_core hh_data = iter( sess.query(HhDatum).join(Channel).join(Era).filter( Era.supply == supply, HhDatum.start_date >= start_date, HhDatum.start_date <= finish_date, Channel.imp_related == imp_related, Channel.channel_type == channel_type ).order_by(HhDatum.start_date)) datum = next(hh_data, None) for current_date in hh_range(start_date, finish_date): if current_date.hour == 0 and current_date.minute == 0: outs.append( "\n" + mpan_core_str + "," + current_date.strftime('%Y-%m-%d')) outs.append(",") if datum is not None and datum.start_date == current_date: outs.append(str(datum.value)) datum = next(hh_data, None) if is_zipped: fname = mpan_core_str + '_' + str(supply.id) + '.csv' zf.writestr(fname.encode('ascii'), titles + ''.join(outs)) else: tf.write(''.join(outs)) outs = [] if is_zipped: zf.close() else: tf.close() except: msg = traceback.format_exc() if is_zipped: zf.writestr('error.txt', msg) zf.close() else: tf.write(msg) finally: if sess is not None: sess.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(year, supply_id, user): f = sess = None try: sess = Session() fname = ['crc', str(year), str(year + 1)] if supply_id is None: fname.append('all_supplies') else: fname.append('supply_' + str(supply_id)) running_name, finished_name = chellow.dloads.make_names( '_'.join(fname) + '.csv', user) f = open(running_name, mode='w', newline='') w = csv.writer(f, lineterminator='\n') ACTUAL_READ_TYPES = ['N', 'N3', 'C', 'X', 'CP'] w.writerow( ( 'Chellow Supply Id', 'Report Start', 'Report Finish', 'MPAN Core', 'Site Id', 'Site Name', 'From', 'To', 'NHH Breakdown', 'Actual HH Normal Days', 'Actual AMR Normal Days', 'Actual NHH Normal Days', 'Actual Unmetered Normal Days', 'Max HH Normal Days', 'Max AMR Normal Days', 'Max NHH Normal Days', 'Max Unmetered Normal Days', 'Total Actual Normal Days', 'Total Max Normal Days', 'Data Type', 'HH kWh', 'AMR kWh', 'NHH kWh', 'Unmetered kwh', 'HH Filled kWh', 'AMR Filled kWh', 'Total kWh', 'Note')) year_start = Datetime(year, 4, 1, tzinfo=pytz.utc) year_finish = year_start + relativedelta(years=1) - HH supplies = sess.query(Supply).join(Era).join(Source).filter( Source.code.in_(('net', 'gen-net')), Era.imp_mpan_core != null(), Era.start_date <= year_finish, or_( Era.finish_date == null(), Era.finish_date >= year_start)).distinct().order_by(Supply.id) if supply_id is not None: supply = Supply.get_by_id(sess, supply_id) supplies = supplies.filter(Supply.id == supply.id) meter_types = ('hh', 'amr', 'nhh', 'unmetered') for supply in supplies: total_kwh = dict([(mtype, 0) for mtype in meter_types]) filled_kwh = dict([(mtype, 0) for mtype in ('hh', 'amr')]) normal_days = dict([(mtype, 0) for mtype in meter_types]) max_normal_days = dict([(mtype, 0) for mtype in meter_types]) breakdown = '' eras = sess.query(Era).filter( Era.supply == supply, Era.start_date <= year_finish, or_( Era.finish_date == null(), Era.finish_date >= year_start)).order_by( Era.start_date).all() supply_from = hh_max(eras[0].start_date, year_start) supply_to = hh_min(eras[-1].finish_date, year_finish) for era in eras: meter_type = era.meter_category period_start = hh_max(era.start_date, year_start) period_finish = hh_min(era.finish_date, year_finish) max_normal_days[meter_type] += ( (period_finish - period_start).total_seconds() + 60 * 30) / (60 * 60 * 24) mpan_core = era.imp_mpan_core site = sess.query(Site).join(SiteEra).filter( SiteEra.is_physical == true(), SiteEra.era_id == era.id).one() if meter_type == 'nhh': read_list = [] read_keys = {} pairs = [] prior_pres_reads = iter( sess.query(RegisterRead).join(Bill).join(BillType) .join(RegisterRead.present_type).filter( RegisterRead.units == 0, ReadType.code.in_(ACTUAL_READ_TYPES), Bill.supply == supply, RegisterRead.present_date < period_start, BillType.code != 'W').order_by( RegisterRead.present_date.desc())) prior_prev_reads = iter( sess.query(RegisterRead).join(Bill).join(BillType) .join(RegisterRead.previous_type).filter( RegisterRead.units == 0, ReadType.code.in_(ACTUAL_READ_TYPES), Bill.supply == supply, RegisterRead.previous_date < period_start, BillType.code != 'W').order_by( RegisterRead.previous_date.desc())) next_pres_reads = iter( sess.query(RegisterRead).join(Bill).join(BillType) .join(RegisterRead.present_type).filter( RegisterRead.units == 0, ReadType.code.in_(ACTUAL_READ_TYPES), Bill.supply == supply, RegisterRead.present_date >= period_start, BillType.code != 'W').order_by( RegisterRead.present_date)) next_prev_reads = iter( sess.query(RegisterRead).join(Bill).join(BillType). join(RegisterRead.previous_type).filter( RegisterRead.units == 0, ReadType.code.in_(ACTUAL_READ_TYPES), Bill.supply == supply, RegisterRead.previous_date >= period_start, BillType.code != 'W').order_by( RegisterRead.previous_date)) for is_forwards in [False, True]: if is_forwards: pres_reads = next_pres_reads prev_reads = next_prev_reads read_list.reverse() else: pres_reads = prior_pres_reads prev_reads = prior_prev_reads prime_pres_read = None prime_prev_read = None while True: while prime_pres_read is None: try: pres_read = next(pres_reads) except StopIteration: break pres_date = pres_read.present_date pres_msn = pres_read.msn read_key = '_'.join([str(pres_date), pres_msn]) if read_key in read_keys: continue pres_bill = sess.query(Bill).join(BillType). \ filter( Bill.reads.any(), Bill.supply == supply, Bill.finish_date >= pres_read.bill.start_date, Bill.start_date <= pres_read.bill.finish_date, BillType.code != 'W').order_by( Bill.issue_date.desc(), BillType.code).first() if pres_bill != pres_read.bill: continue reads = dict( ( read.tpr.code, float(read.present_value) * float(read.coefficient)) for read in sess.query(RegisterRead). filter( RegisterRead.units == 0, RegisterRead.bill == pres_bill, RegisterRead.present_date == pres_date, RegisterRead.msn == pres_msn)) prime_pres_read = { 'date': pres_date, 'reads': reads, 'msn': pres_msn} read_keys[read_key] = None while prime_prev_read is None: try: prev_read = next(prev_reads) except StopIteration: break prev_date = prev_read.previous_date prev_msn = prev_read.msn read_key = '_'.join([str(prev_date), prev_msn]) if read_key in read_keys: continue prev_bill = sess.query(Bill).join(BillType). \ filter( Bill.reads.any(), Bill.supply_id == supply.id, Bill.finish_date >= prev_read.bill.start_date, Bill.start_date <= prev_read.bill.finish_date, BillType.code != 'W').order_by( Bill.issue_date.desc(), BillType.code).first() if prev_bill != prev_read.bill: continue reads = dict( ( read.tpr.code, float(read.previous_value) * float(read.coefficient)) for read in sess.query(RegisterRead). filter( RegisterRead.units == 0, RegisterRead.bill_id == prev_bill.id, RegisterRead.previous_date == prev_date, RegisterRead.msn == prev_msn)) prime_prev_read = { 'date': prev_date, 'reads': reads, 'msn': prev_msn} read_keys[read_key] = None if prime_pres_read is None and \ prime_prev_read is None: break elif prime_pres_read is None: read_list.append(prime_prev_read) prime_prev_read = None elif prime_prev_read is None: read_list.append(prime_pres_read) prime_pres_read = None else: if is_forwards: if prime_pres_read['date'] <= \ prime_prev_read['date']: read_list.append(prime_pres_read) prime_pres_read = None else: read_list.append(prime_prev_read) prime_prev_read = None else: if prime_prev_read['date'] >= \ prime_pres_read['date']: read_list.append(prime_prev_read) prime_prev_read = None else: read_list.append(prime_pres_read) prime_pres_read = None if len(read_list) > 1: if is_forwards: aft_read = read_list[-2] fore_read = read_list[-1] else: aft_read = read_list[-1] fore_read = read_list[-2] if aft_read['msn'] == fore_read['msn'] and \ set(aft_read['reads'].keys()) == \ set(fore_read['reads'].keys()): pair_start_date = aft_read['date'] + HH pair_finish_date = fore_read['date'] num_hh = ( ( pair_finish_date + HH - pair_start_date).total_seconds() ) / (30 * 60) tprs = {} for tpr_code, initial_val in \ aft_read['reads'].items(): end_val = fore_read['reads'][tpr_code] kwh = end_val - initial_val if kwh < 0: digits = int( math.log10(initial_val)) + 1 kwh = 10 ** digits + kwh tprs[tpr_code] = kwh / num_hh pairs.append( { 'start-date': pair_start_date, 'finish-date': pair_finish_date, 'tprs': tprs}) if len(pairs) > 0 and ( not is_forwards or ( is_forwards and read_list[-1]['date'] > period_finish)): break breakdown += 'read list - \n' + str(read_list) + "\n" if len(pairs) == 0: pairs.append( { 'start-date': period_start, 'finish-date': period_finish, 'tprs': {'00001': 0}}) else: for pair in pairs: pair_start = pair['start-date'] pair_finish = pair['finish-date'] if pair_start >= year_start and \ pair_finish <= year_finish: block_start = hh_max(pair_start, period_start) block_finish = hh_min( pair_finish, period_finish) if block_start <= block_finish: normal_days[meter_type] += ( ( block_finish - block_start ).total_seconds() + 60 * 30) / (60 * 60 * 24) # smooth for i in range(1, len(pairs)): pairs[i - 1]['finish-date'] = pairs[i]['start-date'] \ - HH # stretch if pairs[0]['start-date'] > period_start: pairs[0]['start-date'] = period_start if pairs[-1]['finish-date'] < period_finish: pairs[-1]['finish-date'] = period_finish # chop pairs = [ pair for pair in pairs if not pair['start-date'] > period_finish and not pair['finish-date'] < period_start] # squash if pairs[0]['start-date'] < period_start: pairs[0]['start-date'] = period_start if pairs[-1]['finish-date'] > period_finish: pairs[-1]['finish-date'] = period_finish for pair in pairs: pair_hhs = ( ( pair['finish-date'] - pair['start-date'] ).total_seconds() + 30 * 60) / (60 * 30) pair['pair_hhs'] = pair_hhs for tpr_code, pair_kwh in pair['tprs'].items(): total_kwh[meter_type] += pair_kwh * pair_hhs breakdown += 'pairs - \n' + str(pairs) elif meter_type in ('hh', 'amr'): period_kwhs = list( float(v[0]) for v in sess.query(HhDatum.value). join(Channel).filter( Channel.imp_related == true(), Channel.channel_type == 'ACTIVE', Channel.era == era, HhDatum.start_date >= period_start, HhDatum.start_date <= period_finish).order_by( HhDatum.id)) year_kwhs = list( float(v[0]) for v in sess.query(HhDatum.value). join(Channel).join(Era).filter( Channel.imp_related == true(), Channel.channel_type == 'ACTIVE', Era.supply == supply, HhDatum.start_date >= year_start, HhDatum.start_date <= year_finish).order_by( HhDatum.id)) period_sum_kwhs = sum(period_kwhs) year_sum_kwhs = sum(year_kwhs) period_len_kwhs = len(period_kwhs) year_len_kwhs = len(year_kwhs) total_kwh[meter_type] += period_sum_kwhs period_hhs = ( period_finish + HH - period_start ).total_seconds() / (60 * 30) if year_len_kwhs > 0: filled_kwh[meter_type] += year_sum_kwhs / \ year_len_kwhs * (period_hhs - period_len_kwhs) normal_days[meter_type] += sess.query( func.count(HhDatum.value)).join(Channel). \ filter( Channel.imp_related == true(), Channel.channel_type == 'ACTIVE', Channel.era == era, HhDatum.start_date >= period_start, HhDatum.start_date <= period_finish, HhDatum.status == 'A').one()[0] / 48 elif meter_type == 'unmetered': year_seconds = ( year_finish - year_start).total_seconds() + 60 * 30 period_seconds = ( period_finish - period_start).total_seconds() + 60 * 30 total_kwh[meter_type] += era.imp_sc * period_seconds / \ year_seconds normal_days[meter_type] += period_seconds / (60 * 60 * 24) # for full year 183 total_normal_days = sum(normal_days.values()) total_max_normal_days = sum(max_normal_days.values()) is_normal = total_normal_days / total_max_normal_days >= 183 / 365 w.writerow( [ supply.id, hh_format(year_start), hh_format(year_finish), mpan_core, site.code, site.name, hh_format(supply_from), hh_format(supply_to), breakdown] + [ normal_days[t] for t in meter_types] + [ max_normal_days[t] for t in meter_types] + [ total_normal_days, total_max_normal_days, "Actual" if is_normal else "Estimated"] + [total_kwh[t] for t in meter_types] + [filled_kwh[t] for t in ('hh', 'amr')] + [sum(total_kwh.values()) + sum(filled_kwh.values()), '']) # avoid a long running transaction sess.rollback() except BaseException: msg = traceback.format_exc() sys.stderr.write(msg + '\n') f.write("Problem " + msg) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def run(self): while not self.stopped.isSet(): if self.lock.acquire(False): sess = None try: sess = Session() self.log("Starting to check RCRCs.") contract = Contract.get_non_core_by_name(sess, "rcrc") latest_rs = ( sess.query(RateScript) .filter(RateScript.contract_id == contract.id) .order_by(RateScript.start_date.desc()) .first() ) latest_rs_id = latest_rs.id latest_rs_start = latest_rs.start_date month_start = latest_rs_start + relativedelta(months=1) month_finish = month_start + relativedelta(months=1) - HH now = utc_datetime_now() if now > month_finish: self.log( "Checking to see if data is available from " + hh_format(month_start) + " to " + hh_format(month_finish) + " on Elexon Portal." ) config = Contract.get_non_core_by_name(sess, "configuration") props = config.make_properties() scripting_key = props.get(ELEXON_PORTAL_SCRIPTING_KEY_KEY) if scripting_key is None: raise BadRequest( "The property " + ELEXON_PORTAL_SCRIPTING_KEY_KEY + " cannot be found in the configuration " "properties." ) contract_props = contract.make_properties() url_str = "".join( ( contract_props["url"], "file/download/RCRC_FILE?key=", scripting_key, ) ) r = requests.get(url_str, timeout=60) parser = csv.reader( (x.decode() for x in r.iter_lines()), delimiter=",", quotechar='"', ) next(parser) next(parser) month_rcrcs = {} for values in parser: hh_date = utc_datetime_parse(values[0], "%d/%m/%Y") hh_date += relativedelta(minutes=30 * int(values[2])) if month_start <= hh_date <= month_finish: month_rcrcs[key_format(hh_date)] = Decimal(values[3]) if key_format(month_finish) in month_rcrcs: self.log("The whole month's data is there.") script = {"rates": month_rcrcs} contract = Contract.get_non_core_by_name(sess, "rcrc") rs = RateScript.get_by_id(sess, latest_rs_id) contract.update_rate_script( sess, rs, rs.start_date, month_finish, loads(rs.script) ) contract.insert_rate_script(sess, month_start, script) sess.commit() self.log( "Added a new rate script starting at " + hh_format(month_start) + "." ) else: msg = "There isn't a whole month there yet." if len(month_rcrcs) > 0: msg += ( " The last date is " + sorted(month_rcrcs.keys())[-1] ) self.log(msg) except BaseException: self.log("Outer problem " + traceback.format_exc()) sess.rollback() finally: self.lock.release() self.log("Finished checking RCRC rates.") if sess is not None: sess.close() self.going.wait(30 * 60) self.going.clear()
def content(supply_id, file_name, start_date, finish_date, user): caches = {} sess = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'supply_virtual_bills_' + str(supply_id) + '.csv', user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') supply = Supply.get_by_id(sess, supply_id) forecast_date = chellow.computer.forecast_date() prev_titles = None for era in sess.query(Era).filter( Era.supply == supply, Era.start_date < finish_date, or_( Era.finish_date == null(), Era.finish_date > start_date)).order_by(Era.start_date): chunk_start = hh_max(era.start_date, start_date) chunk_finish = hh_min(era.finish_date, finish_date) site = sess.query(Site).join(SiteEra).filter( SiteEra.era == era, SiteEra.is_physical == true()).one() ds = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, True, caches) titles = [ 'Imp MPAN Core', 'Exp MPAN Core', 'Site Code', 'Site Name', 'Account', 'From', 'To', ''] output_line = [ era.imp_mpan_core, era.exp_mpan_core, site.code, site.name, ds.supplier_account, hh_format(ds.start_date), hh_format(ds.finish_date), ''] mop_titles = ds.contract_func( era.mop_contract, 'virtual_bill_titles')() titles.extend(['mop-' + t for t in mop_titles]) ds.contract_func(era.mop_contract, 'virtual_bill')(ds) bill = ds.mop_bill for title in mop_titles: if title in bill: output_line.append(bill[title]) del bill[title] else: output_line.append('') for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) output_line.append('') dc_titles = ds.contract_func( era.hhdc_contract, 'virtual_bill_titles')() titles.append('') titles.extend(['dc-' + t for t in dc_titles]) ds.contract_func(era.hhdc_contract, 'virtual_bill')(ds) bill = ds.dc_bill for title in dc_titles: output_line.append(bill.get(title, '')) if title in bill: del bill[title] for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) tpr_query = sess.query(Tpr).join(MeasurementRequirement). \ join(Ssc).join(Era).filter( Era.start_date <= chunk_finish, or_( Era.finish_date == null(), Era.finish_date >= chunk_start) ).order_by(Tpr.code).distinct() if era.imp_supplier_contract is not None: output_line.append('') supplier_titles = ds.contract_func( era.imp_supplier_contract, 'virtual_bill_titles')() for tpr in tpr_query.filter( Era.imp_supplier_contract != null()): for suffix in ('-kwh', '-rate', '-gbp'): supplier_titles.append(tpr.code + suffix) titles.append('') titles.extend(['imp-supplier-' + t for t in supplier_titles]) ds.contract_func(era.imp_supplier_contract, 'virtual_bill')(ds) bill = ds.supplier_bill for title in supplier_titles: if title in bill: output_line.append(bill[title]) del bill[title] else: output_line.append('') for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) if era.exp_supplier_contract is not None: ds = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, False, caches) output_line.append('') supplier_titles = ds.contract_func( era.exp_supplier_contract, 'virtual_bill_titles')() for tpr in tpr_query.filter( Era.exp_supplier_contract != null()): for suffix in ('-kwh', '-rate', '-gbp'): supplier_titles.append(tpr.code + suffix) titles.append('') titles.extend(['exp-supplier-' + t for t in supplier_titles]) ds.contract_func( era.exp_supplier_contract, 'virtual_bill')(ds) bill = ds.supplier_bill for title in supplier_titles: output_line.append(bill.get(title, '')) if title in bill: del bill[title] for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) if titles != prev_titles: prev_titles = titles writer.writerow([str(v) for v in titles]) for i, val in enumerate(output_line): output_line[i] = csv_make_val(val) writer.writerow(output_line) except BadRequest as e: writer.writerow(["Problem: " + e.description]) except: writer.writerow([traceback.format_exc()]) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def none_content(site_id, start_date, finish_date, user, file_name): sess = zf = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( file_name, user) sites = sess.query(Site).join(SiteEra).join(Era).filter( SiteEra.is_physical == true(), or_(Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date) zf = zipfile.ZipFile(running_name, 'w') start_date_str = hh_format(start_date) finish_date_str = hh_format(finish_date) for site in sites: buf = StringIO() writer = csv.writer(buf, lineterminator='\n') writer.writerow([ "Site Code", "Site Name", "Associated Site Codes", "Sources", "Generator Types", "From", "To", "Type", "Date" ] + list(map(str, range(1, 49)))) associates = ' '.join( s.code for s in site.find_linked_sites(sess, start_date, finish_date)) source_codes = set() gen_types = set() for supply in sess.query(Supply).join(Era).join(SiteEra).filter( SiteEra.is_physical == true(), SiteEra.site == site, Era.start_date <= finish_date, or_(Era.finish_date == null(), Era.finish_date >= start_date)).distinct().options( joinedload(Supply.source), joinedload(Supply.generator_type)): source_codes.add(supply.source.code) gen_type = supply.generator_type if gen_type is not None: gen_types.add(gen_type.code) source_codes_str = ', '.join(sorted(source_codes)) gen_types_str = ', '.join(sorted(gen_types)) row = None for hh in site.hh_data(sess, start_date, finish_date): hh_start = hh['start_date'] if hh_start.hour == 0 and hh_start.minute == 0: if row is not None: writer.writerow(row) row = [ site.code, site.name, associates, source_codes_str, gen_types_str, start_date_str, finish_date_str, 'used', hh_start.strftime('%Y-%m-%d') ] used_gen_kwh = hh['imp_gen'] - hh['exp_net'] - hh['exp_gen'] used_3p_kwh = hh['imp_3p'] - hh['exp_3p'] used_kwh = hh['imp_net'] + used_gen_kwh + used_3p_kwh row.append(str(round(used_kwh, 2))) if row is not None: writer.writerow(row) zf.writestr( site.code + '_' + finish_date.strftime('%Y%m%d%M%H') + '.csv', buf.getvalue()) # Avoid long-running transaction sess.rollback() except BaseException: msg = traceback.format_exc() sys.stderr.write(msg) zf.write(msg) finally: if sess is not None: sess.close() if zf is not None: zf.close() os.rename(running_name, finished_name)
def run(self): while not self.stopped.isSet(): if self.lock.acquire(False): sess = None try: sess = Session() self.log("Starting to check bank holidays") contract = Contract.get_non_core_by_name(sess, "bank_holidays") contract_props = contract.make_properties() if contract_props.get("enabled", False): url_str = contract_props["url"] self.log("Downloading from " + url_str + ".") res = requests.get(url_str) self.log( " ".join(("Received", str(res.status_code), res.reason)) ) PREFIX = "DTSTART;VALUE=DATE:" hols = collections.defaultdict(list) for line in res.text.splitlines(): if line.startswith(PREFIX): dt = utc_datetime_parse(line[-8:], "%Y%m%d") hols[dt.year].append(dt) for year in sorted(hols.keys()): year_start = utc_datetime(year, 1, 1) year_finish = year_start + relativedelta(years=1) - HH rs = ( sess.query(RateScript) .filter( RateScript.contract == contract, RateScript.start_date == year_start, ) .first() ) if rs is None: self.log( "Adding a new rate script starting at " + hh_format(year_start) + "." ) latest_rs = ( sess.query(RateScript) .filter(RateScript.contract == contract) .order_by(RateScript.start_date.desc()) .first() ) contract.update_rate_script( sess, latest_rs, latest_rs.start_date, year_finish, loads(latest_rs.script), ) rs = contract.insert_rate_script(sess, year_start, {}) script = { "bank_holidays": [ v.strftime("%Y-%m-%d") for v in hols[year] ] } contract.update_rate_script( sess, rs, rs.start_date, rs.finish_date, script ) sess.commit() self.log( "Updated rate script starting at " + hh_format(year_start) + "." ) else: self.log( "The automatic importer is disabled. To " "enable it, edit the contract properties to " "set 'enabled' to True." ) except BaseException: self.log("Outer problem " + traceback.format_exc()) sess.rollback() finally: if sess is not None: sess.close() self.lock.release() self.log("Finished checking bank holidays.") self.going.wait(24 * 60 * 60) self.going.clear()
def content(start_date, finish_date, contract_id, user): caches = {} sess = supply_source = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( "mop_virtual_bills.csv", user) f = open(running_name, mode="w", newline="") writer = csv.writer(f, lineterminator="\n") contract = Contract.get_mop_by_id(sess, contract_id) forecast_date = chellow.computer.forecast_date() header_titles = [ "Import MPAN Core", "Export MPAN Core", "Start Date", "Finish Date", ] bill_titles = chellow.computer.contract_func(caches, contract, "virtual_bill_titles")() writer.writerow(header_titles + bill_titles) vb_func = chellow.computer.contract_func(caches, contract, "virtual_bill") for era in (sess.query(Era).filter( or_(Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date, Era.mop_contract == contract, ).order_by(Era.imp_mpan_core, Era.exp_mpan_core, Era.start_date)): chunk_start = hh_max(era.start_date, start_date) chunk_finish = hh_min(era.finish_date, finish_date) import_mpan_core = era.imp_mpan_core if import_mpan_core is None: import_mpan_core_str = "" else: is_import = True import_mpan_core_str = import_mpan_core export_mpan_core = era.exp_mpan_core if export_mpan_core is None: export_mpan_core_str = "" else: is_import = False export_mpan_core_str = export_mpan_core out = [ import_mpan_core_str, export_mpan_core_str, hh_format(chunk_start), hh_format(chunk_finish), ] supply_source = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, is_import, caches) vb_func(supply_source) bill = supply_source.mop_bill for title in bill_titles: if title in bill: out.append(make_val(bill[title])) del bill[title] else: out.append("") for k in sorted(bill.keys()): out.append(k) out.append(str(bill[k])) writer.writerow(out) except BadRequest as e: msg = "Problem " if supply_source is not None: msg += ("with supply " + supply_source.mpan_core + " starting at " + hh_format(supply_source.start_date) + " ") msg += str(e) sys.stderr.write(msg) writer.writerow([msg]) except BaseException: msg = traceback.format_exc() sys.stderr.write(msg) writer.writerow([msg]) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def content(contract_id, end_year, end_month, months, user): caches = {} sess = f = supply_source = None try: sess = Session() contract = Contract.get_dc_by_id(sess, contract_id) month_list = list( c_months_u(finish_year=end_year, finish_month=end_month, months=months)) start_date, finish_date = month_list[0][0], month_list[-1][-1] forecast_date = chellow.computer.forecast_date() running_name, finished_name = chellow.dloads.make_names( "dc_virtual_bills.csv", user) f = open(running_name, mode="w", newline="") writer = csv.writer(f, lineterminator="\n") bill_titles = chellow.computer.contract_func(caches, contract, "virtual_bill_titles")() header_titles = [ "Import MPAN Core", "Export MPAN Core", "Start Date", "Finish Date", ] vb_func = chellow.computer.contract_func(caches, contract, "virtual_bill") writer.writerow(header_titles + bill_titles) for era in (sess.query(Era).distinct().filter( or_(Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date, Era.dc_contract == contract, ).options(joinedload(Era.channels)).order_by(Era.supply_id)): imp_mpan_core = era.imp_mpan_core if imp_mpan_core is None: imp_mpan_core_str = "" is_import = False else: is_import = True imp_mpan_core_str = imp_mpan_core exp_mpan_core = era.exp_mpan_core exp_mpan_core_str = "" if exp_mpan_core is None else exp_mpan_core chunk_start = hh_max(era.start_date, start_date) chunk_finish = hh_min(era.finish_date, finish_date) vals = [ imp_mpan_core_str, exp_mpan_core_str, hh_format(chunk_start), hh_format(chunk_finish), ] supply_source = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, is_import, caches) vb_func(supply_source) bill = supply_source.dc_bill for title in bill_titles: vals.append(csv_make_val(bill.get(title))) if title in bill: del bill[title] for k in sorted(bill.keys()): vals.append(k) vals.append(csv_make_val(bill[k])) writer.writerow(vals) # Avoid long-running transactions sess.rollback() except BadRequest as e: msg = "Problem " if supply_source is not None: msg += ("with supply " + supply_source.mpan_core + " starting at " + hh_format(supply_source.start_date) + " ") msg += str(e) writer.writerow([msg]) except BaseException: msg = "Problem " + traceback.format_exc() + "\n" f.write(msg) finally: f.close() os.rename(running_name, finished_name) if sess is not None: sess.close()
def content(year, month, months, supply_id, user): sess = f = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( "register_reads.csv", user) f = open(running_name, mode="w", newline="") w = csv.writer(f, lineterminator="\n") titles = ( "Duration Start", "Duration Finish", "Supply Id", "Import MPAN Core", "Export MPAN Core", "Batch Reference", "Bill Id", "Bill Reference", "Bill Issue Date", "Bill Type", "Register Read Id", "TPR", "Coefficient", "Previous Read Date", "Previous Read Value", "Previous Read Type", "Present Read Date", "Present Read Value", "Present Read Type", ) w.writerow(titles) month_pairs = list( c_months_u(finish_year=year, finish_month=month, months=months)) start_date, finish_date = month_pairs[0][0], month_pairs[-1][-1] supplies = (sess.query(Supply).join(Bill).join(RegisterRead).filter( or_( and_( RegisterRead.present_date >= start_date, RegisterRead.present_date <= finish_date, ), and_( RegisterRead.previous_date >= start_date, RegisterRead.previous_date <= finish_date, ), )).order_by(Bill.supply_id).distinct()) if supply_id is not None: supply = Supply.get_by_id(sess, supply_id) supplies = supplies.filter(Bill.supply == supply) for supply in supplies: supply_id = supply.id for bill, batch, bill_type in (sess.query( Bill, Batch, BillType).join(Batch).join(BillType).join( RegisterRead).filter( Bill.supply == supply, or_( and_( RegisterRead.present_date >= start_date, RegisterRead.present_date <= finish_date, ), and_( RegisterRead.previous_date >= start_date, RegisterRead.previous_date <= finish_date, ), ), )): era = supply.find_era_at(sess, bill.start_date) if era is None: eras = (sess.query(Era).filter( Era.supply == supply).order_by(Era.start_date).all()) if bill.start_date < eras[0].start_date: era = eras[0] else: era = eras[-1] for read in (sess.query(RegisterRead).filter( RegisterRead.bill == bill, or_( and_( RegisterRead.present_date >= start_date, RegisterRead.present_date <= finish_date, ), and_( RegisterRead.previous_date >= start_date, RegisterRead.previous_date <= finish_date, ), ), ).options( joinedload(RegisterRead.tpr), joinedload(RegisterRead.previous_type), joinedload(RegisterRead.present_type), )): vals = [ start_date, finish_date, supply_id, era.imp_mpan_core, era.exp_mpan_core, batch.reference, bill.id, bill.reference, bill.issue_date, bill_type.code, read.id, "md" if read.tpr is None else read.tpr.code, read.coefficient, read.previous_date, read.previous_value, read.previous_type.code, read.present_date, read.present_value, read.present_type.code, ] w.writerow(csv_make_val(v) for v in vals) # Avoid a long-running transaction sess.rollback() except BadRequest as e: w.writerow([e.description]) except BaseException: msg = traceback.format_exc() f.write(msg) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def content(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(g_batch_id, g_bill_id, user): forecast_date = to_utc(Datetime.max) report_context = {} sess = tmp_file = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'g_bill_check.csv', user) tmp_file = open(running_name, "w") csv_writer = csv.writer(tmp_file) if g_batch_id is not None: g_batch = GBatch.get_by_id(sess, g_batch_id) g_bills = sess.query(GBill).filter( GBill.g_batch == g_batch).order_by(GBill.reference) elif g_bill_id is not None: g_bill = GBill.get_by_id(sess, g_bill_id) g_bills = sess.query(GBill).filter(GBill.id == g_bill.id) g_batch = g_bill.g_batch g_contract = g_batch.g_contract vbf = chellow.g_engine.g_contract_func( report_context, g_contract, 'virtual_bill') if vbf is None: raise BadRequest( 'The contract ' + g_contract.name + " doesn't have a function virtual_bill.") header_titles = [ 'batch', 'bill_reference', 'bill_type', 'bill_start_date', 'bill_finish_date', 'mprn', 'supply_name', 'site_code', 'site_name', 'covered_start', 'covered_finish', 'covered_bill_ids'] bill_titles = chellow.g_engine.g_contract_func( report_context, g_contract, 'virtual_bill_titles')() titles = header_titles[:] for title in bill_titles: for prefix in ('covered_', 'virtual_'): titles.append(prefix + title) if title.endswith('_gbp'): titles.append('difference_' + title) csv_writer.writerow(titles) for g_bill in g_bills: problem = '' g_supply = g_bill.g_supply read_dict = collections.defaultdict(set) for g_read in g_bill.g_reads: if not all( g_read.msn == era.msn for era in g_supply.find_g_eras( sess, g_read.prev_date, g_read.pres_date)): problem += "The MSN " + g_read.msn + \ " of the register read " + str(g_read.id) + \ " doesn't match the MSN of all the relevant eras." for dt, typ in [ (g_read.pres_date, g_read.pres_type), (g_read.prev_date, g_read.prev_type)]: typ_set = read_dict[str(dt) + "-" + g_read.msn] typ_set.add(typ) if len(typ_set) > 1: problem += " Reads taken on " + str(dt) + \ " have differing read types." g_era = g_supply.find_g_era_at(sess, g_bill.finish_date) if g_era is None: csv_writer.writerow( [ "Extraordinary! There isn't a era for this bill!"]) continue vals = { 'batch': g_batch.reference, 'bill_reference': g_bill.reference, 'bill_type': g_bill.bill_type.code, 'bill_start_date': g_bill.start_date, 'bill_finish_date': g_bill.finish_date, 'mprn': g_supply.mprn, 'covered_vat_gbp': Decimal('0.00'), 'covered_net_gbp': Decimal('0.00'), 'covered_gross_gbp': Decimal('0.00'), 'covered_kwh': Decimal(0), 'covered_start': g_bill.start_date, 'covered_finish': g_bill.finish_date, 'covered_bill_ids': []} covered_primary_bill = None enlarged = True while enlarged: enlarged = False for covered_bill in sess.query(GBill).filter( GBill.g_supply_id == g_supply.id, GBill.start_date <= vals['covered_finish'], GBill.finish_date >= vals['covered_start']).order_by( GBill.issue_date.desc(), GBill.start_date): if covered_primary_bill is None and \ len(covered_bill.g_reads) > 0: covered_primary_bill = covered_bill if covered_bill.start_date < vals['covered_start']: vals['covered_start'] = covered_bill.start_date enlarged = True break if covered_bill.finish_date > vals['covered_finish']: vals['covered_finish'] = covered_bill.finish_date enlarged = True break for covered_bill in sess.query(GBill).filter( GBill.g_supply == g_supply, GBill.start_date <= vals['covered_finish'], GBill.finish_date >= vals['covered_start']).order_by( GBill.issue_date.desc(), GBill.start_date): vals['covered_bill_ids'].append(covered_bill.id) bdown = covered_bill.make_breakdown() vals['covered_kwh'] += covered_bill.kwh vals['covered_net_gbp'] += covered_bill.net vals['covered_vat_gbp'] += covered_bill.vat vals['covered_gross_gbp'] += covered_bill.gross for title in bill_titles: k = 'covered_' + title v = bdown.get(title) if v is not None: if title.endswith('_rate') or title in ( 'correction_factor', 'calorific_value', 'units'): if k not in vals: vals[k] = set() vals[k].add(v) else: try: vals[k] += v except KeyError: vals[k] = v except TypeError: raise BadRequest( "Problem with key " + str(k) + " and value " + str(v) + " for existing " + str(vals[k])) if title in ( 'correction_factor', 'calorific_value', 'units_code', 'units_factor'): if k not in vals: vals[k] = set() for g_read in covered_bill.g_reads: if title in ('units_code', 'units_factor'): g_units = g_read.g_units if title == 'units_code': v = g_units.code else: v = g_units.factor else: v = getattr(g_read, title) vals[k].add(v) for g_era in sess.query(GEra).filter( GEra.g_supply == g_supply, GEra.start_date <= vals['covered_finish'], or_( GEra.finish_date == null(), GEra.finish_date >= vals['covered_start'])).distinct(): site = sess.query(Site).join(SiteGEra).filter( SiteGEra.is_physical == true(), SiteGEra.g_era == g_era).one() if vals['covered_start'] > g_era.start_date: chunk_start = vals['covered_start'] else: chunk_start = g_era.start_date if hh_before(vals['covered_finish'], g_era.finish_date): chunk_finish = vals['covered_finish'] else: chunk_finish = g_era.finish_date data_source = chellow.g_engine.GDataSource( sess, chunk_start, chunk_finish, forecast_date, g_era, report_context, covered_primary_bill) vbf(data_source) for k, v in data_source.bill.items(): vk = 'virtual_' + k try: if isinstance(v, set): vals[vk].update(v) else: vals[vk] += v except KeyError: vals[vk] = v except TypeError as detail: raise BadRequest( "For key " + str(vk) + " and value " + str(v) + ". " + str(detail)) vals['supply_name'] = g_supply.name vals['site_code'] = site.code vals['site_name'] = site.name for k, v in vals.items(): vals[k] = csv_make_val(v) for i, title in enumerate(titles): if title.startswith('difference_'): try: covered_val = float(vals[titles[i - 2]]) virtual_val = float(vals[titles[i - 1]]) vals[title] = covered_val - virtual_val except KeyError: vals[title] = None csv_writer.writerow( [ (vals.get(k) if vals.get(k) is not None else '') for k in titles]) except BadRequest as e: tmp_file.write("Problem: " + e.description) except: msg = traceback.format_exc() sys.stderr.write(msg + '\n') tmp_file.write("Problem " + msg) finally: try: if sess is not None: sess.close() except: tmp_file.write("\nProblem closing session.") finally: tmp_file.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(g_supply_id, file_name, start_date, finish_date, user): caches = {} sess = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'g_supply_virtual_bill_' + str(g_supply_id) + '.csv', user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') g_supply = GSupply.get_by_id(sess, g_supply_id) forecast_dt = forecast_date() prev_titles = None for g_era in sess.query(GEra).filter( GEra.g_supply == g_supply, GEra.start_date < finish_date, or_(GEra.finish_date == null(), GEra.finish_date > start_date)).order_by(GEra.start_date): chunk_start = hh_max(g_era.start_date, start_date) chunk_finish = hh_min(g_era.finish_date, finish_date) site = sess.query(Site).join(SiteGEra).filter( SiteGEra.g_era == g_era, SiteGEra.is_physical == true()).one() ds = GDataSource(sess, chunk_start, chunk_finish, forecast_dt, g_era, caches, None) titles = [ 'MPRN', 'Site Code', 'Site Name', 'Account', 'From', 'To', '' ] output_line = [ g_supply.mprn, site.code, site.name, ds.account, hh_format(ds.start_date), hh_format(ds.finish_date), '' ] contract_titles = g_contract_func(caches, g_era.g_contract, 'virtual_bill_titles')() titles.extend(contract_titles) g_contract_func(caches, g_era.g_contract, 'virtual_bill')(ds) bill = ds.bill for title in contract_titles: if title in bill: output_line.append(csv_make_val(bill[title])) del bill[title] else: output_line.append('') for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) if titles != prev_titles: prev_titles = titles writer.writerow([str(v) for v in titles]) writer.writerow(output_line) except BadRequest as e: writer.writerow(["Problem: " + e.description]) except BaseException: writer.writerow([traceback.format_exc()]) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def content(year, supply_id, user): sess = f = writer = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'crc_special_events.csv', user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') writer.writerow(("MPAN Core", "Site Id", "Site Name", "Date", "Event")) year_start = datetime(year, 4, 1, tzinfo=pytz.utc) year_finish = prev_hh(datetime(year + 1, 4, 1, tzinfo=pytz.utc)) def add_event(events, date, code, era=None, mpan_core=None): if era is None: mpan_cores = [mpan_core] else: mpan_cores = [] if era.imp_mpan_core is not None: mpan_cores.append(era.imp_mpan_core) if era.exp_mpan_core is not None: mpan_cores.append(era.exp_mpan_core) for mpan_core in mpan_cores: events.append( {'date': date, 'code': code, 'mpan-core': mpan_core}) if supply_id is None: supplies = sess.query(Supply).join(Source).join(Era).filter( Source.code.in_(('net', 'gen-net', 'gen')), Era.start_date <= year_finish, or_( Era.finish_date == null(), Era.finish_date >= year_start)).distinct() else: supply = Supply.get_by_id(supply_id) supplies = sess.query(Supply).filter(Supply.id == supply.id) for supply in supplies: eras = sess.query(Era).filter( Era.supply == supply, Era.start_date <= year_finish, or_(Era.finish_date == null(), Era.finish_date >= year_start) ).order_by(Era.start_date).all() events = [] first_era = eras[0] first_era_start = first_era.start_date if hh_after(first_era_start, year_start): add_event(events, first_era_start, "New Supply", first_era) last_era = eras[-1] last_era_finish = last_era.finish_date if hh_before(last_era_finish, year_finish): add_event(events, last_era_finish, "Disconnection", last_era) prev_era = first_era for era in eras[1:]: if era.msn != prev_era.msn: add_event(events, era.start_date, "Meter Change", era) if era.pc.code != prev_era.pc.code: add_event( events, era.start_date, "Change Of Profile Class", era) if era.mop_contract_id != prev_era.mop_contract_id: add_event(events, era.start_date, "Change Of MOP", era) if era.hhdc_contract_id != prev_era.hhdc_contract_id: add_event(events, era.start_date, "Change Of DC", era) for is_import in [True, False]: if era.imp_mpan_core is None: mpan_core = era.exp_mpan_core else: mpan_core = era.imp_mpan_core if is_import: cur_sup = era.imp_supplier_contract prev_sup = prev_era.imp_supplier_contract else: cur_sup = era.exp_supplier_contract prev_sup = prev_era.exp_supplier_contract if cur_sup is None and prev_sup is not None: add_event( events, era.start_date, "End of supply", mpan_core) elif cur_sup is not None and prev_sup is None: add_event( events, era.start_date, "Start of supply", None, mpan_core) elif cur_sup is not None and \ prev_sup is not None and cur_sup != prev_sup: add_event( events, era.start_date, "Change Of Supplier", None, mpan_core) prev_era = era if len(events) > 0: site = sess.query(Site).join(SiteEra).filter( SiteEra.is_physical == true(), SiteEra.era == last_era).one() for event in events: vals = [ event['mpan-core'], site.code, site.name, event['date'].strftime("%Y-%m-%d %H:%M"), event['code']] writer.writerow(vals) 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 run(self): while not self.stopped.isSet(): if self.lock.acquire(False): sess = None try: sess = Session() self.log("Starting to check bank holidays") contract = Contract.get_non_core_by_name( sess, 'bank_holidays') contract_props = contract.make_properties() if contract_props.get('enabled', False): url_str = contract_props['url'] self.log("Downloading from " + url_str + ".") res = requests.get(url_str) self.log( ' '.join( ( "Received", str(res.status_code), res.reason))) PREFIX = 'DTSTART;VALUE=DATE:' hols = collections.defaultdict(list) for line in res.text.splitlines(): if line.startswith(PREFIX): dt = utc_datetime_parse(line[-8:], "%Y%m%d") hols[dt.year].append(dt) for year in sorted(hols.keys()): year_start = utc_datetime(year, 1, 1) year_finish = year_start + \ relativedelta(years=1) - HH rs = sess.query(RateScript).filter( RateScript.contract == contract, RateScript.start_date == year_start).first() if rs is None: self.log( "Adding a new rate script starting at " + hh_format(year_start) + ".") latest_rs = sess.query(RateScript).filter( RateScript.contract == contract).\ order_by(RateScript.start_date.desc()). \ first() contract.update_rate_script( sess, latest_rs, latest_rs.start_date, year_finish, latest_rs.script) rs = contract.insert_rate_script( sess, year_start, '') script = { 'bank_holidays': [ v.strftime("%Y-%m-%d") for v in hols[year]]} self.log( "Updating rate script starting at " + hh_format(year_start) + ".") contract.update_rate_script( sess, rs, rs.start_date, rs.finish_date, json.dumps( script, indent=' ', sort_keys=True)) sess.commit() else: self.log( "The automatic importer is disabled. To " "enable it, edit the contract properties to " "set 'enabled' to True.") except: self.log("Outer problem " + traceback.format_exc()) sess.rollback() finally: if sess is not None: sess.close() self.lock.release() self.log("Finished checking bank holidays.") self.going.wait(24 * 60 * 60) self.going.clear()
def content(year, supply_id, user): caches = {} sess = f = writer = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( "supplies_triad.csv", user) f = open(running_name, mode="w", newline="") writer = csv.writer(f, lineterminator="\n") march_start = to_utc(ct_datetime(year, 3, 1)) march_finish = to_utc(ct_datetime(year, 4, 1)) - HH nov_start = to_utc(ct_datetime(year - 1, 11, 1)) scalar_names = { "triad-actual-gsp-kw", "triad-actual-gbp", "triad-estimate-gsp-kw", "triad-estimate-months", "triad-estimate-gbp", "triad-all-estimates-months", "triad-all-estimates-gbp", } rate_names = {"triad-actual-rate", "triad-estimate-rate"} for i in range(1, 4): for p in ("triad-actual-", "triad-estimate-"): act_pref = p + str(i) + "-" for suf in ("msp-kw", "gsp-kw"): scalar_names.add(act_pref + suf) for suf in ("date", "status", "laf"): rate_names.add(act_pref + suf) def triad_csv(supply_source): if supply_source is None or supply_source.mpan_core.startswith( "99"): return [""] * 19 chellow.duos.duos_vb(supply_source) chellow.triad.hh(supply_source) for hh in supply_source.hh_data: bill_hh = supply_source.supplier_bill_hhs[hh["start-date"]] for k in scalar_names & hh.keys(): bill_hh[k] = hh[k] for k in rate_names & hh.keys(): bill_hh[k] = {hh[k]} bill = reduce_bill_hhs(supply_source.supplier_bill_hhs) values = [supply_source.mpan_core] for i in range(1, 4): triad_prefix = "triad-actual-" + str(i) for suffix in [ "-date", "-msp-kw", "-status", "-laf", "-gsp-kw" ]: values.append(csv_make_val(bill[triad_prefix + suffix])) suffixes = ["gsp-kw", "rate", "gbp"] values += [ csv_make_val(bill["triad-actual-" + suf]) for suf in suffixes ] return values writer.writerow(( "Site Code", "Site Name", "Supply Name", "Source", "Generator Type", "Import MPAN Core", "Import T1 Date", "Import T1 MSP kW", "Import T1 Status", "Import T1 LAF", "Import T1 GSP kW", "Import T2 Date", "Import T2 MSP kW", "Import T2 Status", "Import T2 LAF", "Import T2 GSP kW", "Import T3 Date", "Import T3 MSP kW", "Import T3 Status", "Import T3 LAF", "Import T3 GSP kW", "Import GSP kW", "Import Rate GBP / kW", "Import GBP", "Export MPAN Core", "Export T1 Date", "Export T1 MSP kW", "Export T1 Status", "Export T1 LAF", "Export T1 GSP kW", "Export T2 Date", "Export T2 MSP kW", "Export T2 Status", "Export T2 LAF", "Export T2 GSP kW", "Export T3 Date", "Export T3 MSP kW", "Export T3 Status", "Export T3 LAF", "Export T3 GSP kW", "Export GSP kW", "Export Rate GBP / kW", "Export GBP", )) forecast_date = chellow.computer.forecast_date() eras = _make_eras(sess, nov_start, march_finish, supply_id) for era in eras: site = (sess.query(Site).join(SiteEra).filter( SiteEra.is_physical == true(), SiteEra.era == era).one()) supply = era.supply imp_mpan_core = era.imp_mpan_core if imp_mpan_core is None: imp_supply_source = None else: imp_supply_source = chellow.computer.SupplySource( sess, march_start, march_finish, forecast_date, era, True, caches) exp_mpan_core = era.exp_mpan_core if exp_mpan_core is None: exp_supply_source = None else: exp_supply_source = chellow.computer.SupplySource( sess, march_start, march_finish, forecast_date, era, False, caches) gen_type = supply.generator_type gen_type = "" if gen_type is None else gen_type.code vals = [] for value in ([ site.code, site.name, supply.name, supply.source.code, gen_type ] + triad_csv(imp_supply_source) + triad_csv(exp_supply_source)): if isinstance(value, Datetime): vals.append(hh_format(value)) else: vals.append(str(value)) writer.writerow(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 run(self): while not self.stopped.isSet(): if self.lock.acquire(False): sess = None try: sess = Session() self.log("Starting to check TLMs.") contract = Contract.get_non_core_by_name(sess, 'tlms') latest_rs = sess.query(RateScript).filter( RateScript.contract_id == contract.id).order_by( RateScript.start_date.desc()).first() latest_rs_id = latest_rs.id next_month_start = latest_rs.start_date + \ relativedelta(months=1) next_month_finish = latest_rs.start_date + \ relativedelta(months=2) - HH now = utc_datetime_now() if now > next_month_start: self.log( "Checking to see if data is available from " + str(next_month_start) + " to " + str(next_month_finish) + " on Elexon Portal.") config = Contract.get_non_core_by_name( sess, 'configuration') props = config.make_properties() scripting_key = props.get( ELEXON_PORTAL_SCRIPTING_KEY_KEY) if scripting_key is None: raise BadRequest( "The property " + ELEXON_PORTAL_SCRIPTING_KEY_KEY + " cannot be found in the configuration " + "properties.") contract_props = contract.make_properties() url_str = ''.join( ( contract_props['url'], 'file/download/TLM_FILE?key=', scripting_key)) r = requests.get(url_str) parser = csv.reader( (l.decode() for l in r.iter_lines()), delimiter=',', quotechar='"') self.log("Opened " + url_str + ".") next(parser, None) month_tlms = {} for values in parser: hh_date_ct = to_ct( Datetime.strptime(values[0], "%d/%m/%Y")) hh_date = to_utc(hh_date_ct) hh_date += relativedelta(minutes=30*int(values[2])) if next_month_start <= hh_date <= \ next_month_finish: month_tlms[key_format(hh_date)] = { 'off-taking': values[3], 'delivering': values[4]} if key_format(next_month_finish) in month_tlms: self.log("The whole month's data is there.") script = "def tlms():\n return {\n" + \ ',\n'.join( "'" + k + "': " + month_tlms[k]['off-taking'] for k in sorted(month_tlms.keys())) + "}" contract = Contract.get_non_core_by_name( sess, 'tlms') rs = RateScript.get_by_id(sess, latest_rs_id) contract.update_rate_script( sess, rs, rs.start_date, rs.start_date + relativedelta(months=2) - HH, rs.script) sess.flush() contract.insert_rate_script( sess, rs.start_date + relativedelta(months=1), script) sess.commit() self.log("Added new rate script.") else: msg = "There isn't a whole month there yet." if len(month_tlms) > 0: msg += "The last date is " + \ sorted(month_tlms.keys())[-1] self.log(msg) except: self.log("Outer problem " + traceback.format_exc()) sess.rollback() finally: if sess is not None: sess.close() self.lock.release() self.log("Finished checking TLM rates.") self.going.wait(30 * 60) self.going.clear()
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 none_content(site_codes, typ, start_date, finish_date, user, file_name): sess = zf = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( file_name, user) sites = (sess.query(Site).join(SiteEra).join(Era).filter( SiteEra.is_physical == true(), or_(Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date, )) if site_codes is not None: sites = sites.filter(Site.code.in_(site_codes)) zf = zipfile.ZipFile(running_name, "w") start_date_str = hh_format(start_date) finish_date_str = hh_format(finish_date) for site in sites: buf = StringIO() writer = csv.writer(buf, lineterminator="\n") writer.writerow([ "Site Code", "Site Name", "Associated Site Codes", "Sources", "Generator Types", "From", "To", "Type", "Date", ] + list(map(str, range(1, 51)))) associates = " ".join( s.code for s in site.find_linked_sites(sess, start_date, finish_date)) source_codes = set() gen_types = set() for supply in (sess.query(Supply).join(Era).join(SiteEra).filter( SiteEra.is_physical == true(), SiteEra.site == site, Era.start_date <= finish_date, or_(Era.finish_date == null(), Era.finish_date >= start_date), ).distinct().options(joinedload(Supply.source), joinedload(Supply.generator_type))): source_codes.add(supply.source.code) gen_type = supply.generator_type if gen_type is not None: gen_types.add(gen_type.code) source_codes_str = ", ".join(sorted(source_codes)) gen_types_str = ", ".join(sorted(gen_types)) row = None for hh in site.hh_data(sess, start_date, finish_date): ct_start_date = to_ct(hh["start_date"]) if ct_start_date.hour == 0 and ct_start_date.minute == 0: if row is not None: writer.writerow(row) row = [ site.code, site.name, associates, source_codes_str, gen_types_str, start_date_str, finish_date_str, typ, ct_start_date.strftime("%Y-%m-%d"), ] row.append(str(round(hh[typ], 2))) if row is not None: writer.writerow(row) zf.writestr( f"{site.code}_{finish_date.strftime('%Y%m%d%M%H')}.csv", buf.getvalue(), ) # Avoid long-running transaction sess.rollback() except BaseException: msg = traceback.format_exc() sys.stderr.write(msg) zf.write(msg) finally: if sess is not None: sess.close() if zf is not None: zf.close() os.rename(running_name, finished_name)
def content(supply_id, start_date, finish_date, user): forecast_date = datetime.datetime.max.replace(tzinfo=pytz.utc) caches = {} f = sess = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'supplies_duration.csv', user) f = open(running_name, "w") f.write( ','.join( ( "Supply Id", "Supply Name", "Source", "Generator Type", "Site Ids", "Site Names", "From", "To", "PC", "MTC", "CoP", "SSC", "Normal Reads", "Type", "Import LLFC", "Import MPAN Core", "Import Supply Capacity", "Import Supplier", "Import Total MSP kWh", "Import Non-actual MSP kWh", "Import Total GSP kWh", "Import MD / kW", "Import MD Date", "Import MD / kVA", "Import Bad HHs", "Export LLFC", "Export MPAN Core", "Export Supply Capacity", "Export Supplier", "Export Total MSP kWh", "Export Non-actual MSP kWh", "Export GSP kWh", "Export MD / kW", "Export MD Date", "Export MD / kVA", "Export Bad HHs"))) supplies = sess.query(Supply).join(Era).filter( or_(Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date).order_by(Supply.id).distinct() if supply_id is not None: supplies = supplies.filter( Supply.id == Supply.get_by_id(sess, supply_id).id) for supply in supplies: site_codes = '' site_names = '' eras = supply.find_eras(sess, start_date, finish_date) era = eras[-1] for site_era in era.site_eras: site = site_era.site site_codes = site_codes + site.code + ', ' site_names = site_names + site.name + ', ' site_codes = site_codes[:-2] site_names = site_names[:-2] if supply.generator_type is None: generator_type = '' else: generator_type = supply.generator_type.code ssc = era.ssc ssc_code = '' if ssc is None else ssc.code prime_reads = set() for read, rdate in chain( sess.query( RegisterRead, RegisterRead.previous_date).join( RegisterRead.previous_type).join(Bill).join( BillType).filter( Bill.supply == supply, BillType.code != 'W', RegisterRead.previous_date >= start_date, RegisterRead.previous_date <= finish_date, ReadType.code.in_(NORMAL_READ_TYPES)), sess.query( RegisterRead, RegisterRead.present_date).join( RegisterRead.present_type).join(Bill).join( BillType).filter( Bill.supply == supply, BillType.code != 'W', RegisterRead.present_date >= start_date, RegisterRead.present_date <= finish_date, ReadType.code.in_(NORMAL_READ_TYPES))): prime_bill = sess.query(Bill).join(BillType).filter( Bill.supply == supply, Bill.start_date <= read.bill.finish_date, Bill.finish_date >= read.bill.start_date, Bill.reads.any()).order_by( Bill.issue_date.desc(), BillType.code).first() if prime_bill.id == read.bill.id: prime_reads.add( str(rdate) + "_" + read.msn) supply_type = era.make_meter_category() if eras[0].start_date > start_date: chunk_start = eras[0].start_date else: chunk_start = start_date if hh_before(finish_date, era.finish_date): chunk_finish = finish_date else: chunk_finish = era.finish_date num_hh = int( (chunk_finish - (chunk_start - HH)).total_seconds() / (30 * 60)) f.write( '\n' + ','.join( ('"' + str(value) + '"') for value in [ supply.id, supply.name, supply.source.code, generator_type, site_codes, site_names, hh_format(start_date), hh_format(finish_date), era.pc.code, era.mtc.code, era.cop.code, ssc_code, len(prime_reads), supply_type]) + ',') f.write( mpan_bit( sess, supply, True, num_hh, eras, chunk_start, chunk_finish, forecast_date, caches) + "," + mpan_bit( sess, supply, False, num_hh, eras, chunk_start, chunk_finish, forecast_date, caches)) except: f.write(traceback.format_exc()) finally: sess.close() f.close() os.rename(running_name, finished_name)
def content(table, version, fin, user): sess = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( table + '_' + version + '_general_import.csv', user) f = open(running_name, mode='w', newline='') w = csv.writer(f, lineterminator='\n') reader = iter(csv.reader(fin)) next(reader) if table == 'Line_Loss_Factor_Class': VOLTAGE_LEVEL_CODES = set( [v.code for v in sess.query(VoltageLevel)]) DNO_MAP = dict( (dno.participant.code, dno) for dno in sess.query(Party). join(MarketRole).filter(MarketRole.code == 'R').options( joinedload(Party.participant))) for i, values in enumerate(reader): participant_code = values[0] # market_role_code = values[1] llfc_code = values[3].zfill(3) valid_from = parse_date(values[4]) description = values[5] is_import = values[6] in ('A', 'B') is_substation = any( p in description for p in ( '_SS', ' SS', ' S/S', '(S/S)', 'sub', 'Sub')) valid_to = parse_to_date(values[7]) voltage_level_code = 'LV' description_upper = description.upper() for vl_code in VOLTAGE_LEVEL_CODES: if vl_code in description_upper: voltage_level_code = vl_code break try: dno = DNO_MAP[participant_code] except KeyError: w.writerow( ( "# There is no DNO with participant code ", participant_code)) continue llfc = sess.query(Llfc).filter( Llfc.dno == dno, Llfc.code == llfc_code, Llfc.valid_from == valid_from).first() if llfc is None: w.writerow( ( 'insert', 'llfc', dno.dno_code, llfc_code, description, voltage_level_code, is_substation, is_import, hh_format(valid_from), hh_format(valid_to, ongoing_str=''))) elif any( ( description != llfc.description, voltage_level_code != llfc.voltage_level.code, is_substation != llfc.is_substation, is_import != llfc.is_import, valid_to != llfc.valid_to)): w.writerow( ( 'update', 'llfc', dno.dno_code, llfc.code, hh_format(llfc.valid_from), description, voltage_level_code, is_substation, is_import, hh_format(valid_to, ongoing_str=''))) elif table == 'Market_Participant': for i, values in enumerate(reader): participant_code = values[0] participant_name = values[1] participant = sess.query(Participant).filter( Participant.code == participant_code).first() if participant is None: w.writerow( ( 'insert', 'participant', participant_code, participant_name)) elif participant_name != participant.name: w.writerow( ( 'update', 'participant', participant_code, participant_name)) elif table == 'Market_Role': for i, values in enumerate(reader): role_code = values[0] role_description = values[1] role = sess.query(MarketRole).filter( MarketRole.code == role_code).first() if role is None: w.writerow( ( 'insert', 'market_role', role_code, role_description)) elif role_description != role.description: w.writerow( ( 'update', 'market_role', role_code, role_description)) elif table == 'Market_Participant_Role': for i, values in enumerate(reader): participant_code = values[0] market_role_code = values[1] valid_from = parse_date(values[2]) party = sess.query(Party).join(Participant). \ join(MarketRole).filter( Party.valid_from == valid_from, Participant.code == participant_code, MarketRole.code == market_role_code).first() valid_to = parse_to_date(values[3]) name = values[4] dno_code_str = values[14] dno_code = None if len(dno_code_str) == 0 else dno_code_str if dno_code == '99': continue if party is None: w.writerow( ( 'insert', 'party', market_role_code, participant_code, name, hh_format(valid_from), hh_format(valid_to, ongoing_str=''), dno_code_str)) elif any( ( name != party.name, dno_code != party.dno_code, valid_to != party.valid_to)): w.writerow( ( 'update', 'party', market_role_code, participant_code, name, hh_format(valid_from), hh_format(valid_to, ongoing_str=''), dno_code_str)) elif table == 'Meter_Timeswitch_Class': for i, values in enumerate(reader): code = values[0].zfill(3) valid_from = parse_date(values[1]) valid_to = parse_to_date(values[2]) description = values[3] is_common = values[4] == 'T' has_related_metering = values[5] == 'T' meter_type_code = values[6] meter_payment_type_code = values[7] has_comms = values[8] == 'T' is_hh = values[9] == 'H' tpr_count_str = values[10] tpr_count = 0 if tpr_count_str == '' else int(tpr_count_str) if is_common: mtc = sess.query(Mtc).filter( Mtc.dno == null(), Mtc.code == code, Mtc.valid_from == valid_from).first() if mtc is None: w.writerow( ( 'insert', 'mtc', '', code, description, has_related_metering, has_comms, is_hh, meter_type_code, meter_payment_type_code, tpr_count, hh_format(valid_from), hh_format(valid_to, ongoing_str=''))) elif any( ( description != mtc.description, has_related_metering != mtc.has_related_metering, has_comms != mtc.has_comms, is_hh != mtc.is_hh, meter_type_code != mtc.meter_type.code, meter_payment_type_code != mtc.meter_payment_type.code, tpr_count != mtc.tpr_count, valid_to != mtc.valid_to)): w.writerow( ( 'update', 'mtc', '', mtc.code, description, has_related_metering, has_comms, is_hh, meter_type_code, meter_payment_type_code, tpr_count, hh_format(mtc.valid_from), hh_format(valid_to, ongoing_str=''))) elif table == 'MTC_in_PES_Area': dnos = dict( (p.participant.code, (p.id, p.dno_code)) for p in sess.query( Party).join(Participant).join(MarketRole).filter( MarketRole.code == 'R').options( joinedload(Party.participant))) mtcs = dict( ((m.dno_id, m.code, m.valid_from), m) for m in sess.query(Mtc).options( joinedload(Mtc.meter_type), joinedload(Mtc.meter_payment_type)).all()) for i, values in enumerate(reader): code_str = values[0] if not Mtc.has_dno(code_str): continue code_int = int(code_str) code = code_str.zfill(3) participant_code = values[2] dno_id, dno_code = dnos[participant_code] valid_from = parse_date(values[3]) valid_to = parse_to_date(values[4]) description = values[5] meter_type_code = values[6] meter_payment_type_code = values[7] has_related_metering = code_int > 500 has_comms = values[8] == 'Y' is_hh = values[9] == 'H' tpr_count_str = values[10] tpr_count = 0 if tpr_count_str == '' else int(tpr_count_str) mtc = mtcs.get((dno_id, code, valid_from)) if mtc is None: w.writerow( ( 'insert', 'mtc', dno_code, code, description, has_related_metering, has_comms, is_hh, meter_type_code, meter_payment_type_code, tpr_count, hh_format(valid_from), hh_format(valid_to, ongoing_str=''))) elif any( ( description != mtc.description, has_related_metering != mtc.has_related_metering, has_comms != mtc.has_comms, is_hh != mtc.is_hh, meter_type_code != mtc.meter_type.code, meter_payment_type_code != mtc.meter_payment_type.code, tpr_count != mtc.tpr_count, valid_to != mtc.valid_to)): w.writerow( ( 'update', 'mtc', mtc.dno.dno_code, mtc.code, description, has_related_metering, has_comms, is_hh, meter_type_code, meter_payment_type_code, tpr_count, hh_format(mtc.valid_from), hh_format(valid_to, ongoing_str=''))) elif table == 'MTC_Meter_Type': for i, values in enumerate(reader): code = values[0] description = values[1] valid_from = parse_date(values[2]) valid_to = parse_to_date(values[3]) pt = sess.query(MeterType).filter( MeterType.code == code, MeterType.valid_from == valid_from).first() if pt is None: w.writerow( ( 'insert', 'meter_type', code, description, hh_format(valid_from), hh_format(valid_to, ongoing_str=''))) elif (description, valid_from, valid_to) != ( pt.description, pt.valid_from, pt.valid_to): w.writerow( ( 'update', 'meter_type', code, description, hh_format(valid_from), hh_format(valid_to))) else: raise Exception("The table " + table + " is not recognized.") except BaseException: w.writerow([traceback.format_exc()]) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def content(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() contract = Contract.get_hhdc_by_id(sess, contract_id) finish_date = Datetime(end_year, end_month, 1, tzinfo=pytz.utc) + \ relativedelta(months=1) - HH start_date = Datetime(end_year, end_month, 1, tzinfo=pytz.utc) - \ relativedelta(months=months - 1) forecast_date = chellow.computer.forecast_date() running_name, finished_name = chellow.dloads.make_names( 'hhdc_virtual_bills.csv', user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') bill_titles = chellow.computer.contract_func( caches, contract, 'virtual_bill_titles')() header_titles = [ 'Import MPAN Core', 'Export MPAN Core', 'Start Date', 'Finish Date'] vb_func = chellow.computer.contract_func( caches, contract, 'virtual_bill') writer.writerow(header_titles + bill_titles) for era in sess.query(Era).distinct().filter( or_(Era.finish_date == null(), Era.finish_date >= start_date), Era.start_date <= finish_date, Era.hhdc_contract == contract).order_by(Era.supply_id): imp_mpan_core = era.imp_mpan_core if imp_mpan_core is None: imp_mpan_core_str = '' is_import = False else: is_import = True imp_mpan_core_str = imp_mpan_core exp_mpan_core = era.exp_mpan_core exp_mpan_core_str = '' if exp_mpan_core is None else exp_mpan_core chunk_start = hh_max(era.start_date, start_date) chunk_finish = hh_min(era.finish_date, finish_date) vals = [ imp_mpan_core_str, exp_mpan_core_str, hh_format(chunk_start), hh_format(chunk_finish)] supply_source = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, is_import, caches) vb_func(supply_source) bill = supply_source.dc_bill for title in bill_titles: vals.append(str(bill.get(title, ''))) if title in bill: del bill[title] for k in sorted(bill.keys()): vals.append(k) vals.append(str(bill[k])) writer.writerow(vals) except BadRequest as e: f.write("Problem " + e.description + traceback.format_exc() + '\n') except: msg = "Problem " + traceback.format_exc() + '\n' f.write(msg) finally: f.close() os.rename(running_name, finished_name) if sess is not None: sess.close()
def content(year, supply_id, user): f = sess = None try: sess = Session() fname = ['crc', str(year), str(year + 1)] if supply_id is None: fname.append('all_supplies') else: fname.append('supply_' + str(supply_id)) running_name, finished_name = chellow.dloads.make_names( '_'.join(fname) + '.csv', user) f = open(running_name, "w") ACTUAL_READ_TYPES = ['N', 'N3', 'C', 'X', 'CP'] f.write( ','.join( ( 'Chellow Supply Id', 'MPAN Core', 'Site Id', 'Site Name', 'From', 'To', 'NHH Breakdown', 'Actual HH Normal Days', 'Actual AMR Normal Days', 'Actual NHH Normal Days', 'Actual Unmetered Normal Days', 'Max HH Normal Days', 'Max AMR Normal Days', 'Max NHH Normal Days', 'Max Unmetered Normal Days', 'Total Actual Normal Days', 'Total Max Normal Days', 'Data Type', 'HH kWh', 'AMR kWh', 'NHH kWh', 'Unmetered kwh', 'HH Filled kWh', 'AMR Filled kWh', 'Total kWh', 'Note')) + '\n') year_start = Datetime(year, 4, 1, tzinfo=pytz.utc) year_finish = year_start + relativedelta(years=1) - HH supplies = sess.query(Supply).join(Era).join(Source).filter( Source.code.in_(('net', 'gen-net')), Era.imp_mpan_core != null(), Era.start_date <= year_finish, or_( Era.finish_date == null(), Era.finish_date >= year_start)).distinct().order_by(Supply.id) if supply_id is not None: supply = Supply.get_by_id(sess, supply_id) supplies = supplies.filter(Supply.id == supply.id) meter_types = ('hh', 'amr', 'nhh', 'unmetered') for supply in supplies: total_kwh = dict([(mtype, 0) for mtype in meter_types]) filled_kwh = dict([(mtype, 0) for mtype in ('hh', 'amr')]) normal_days = dict([(mtype, 0) for mtype in meter_types]) max_normal_days = dict([(mtype, 0) for mtype in meter_types]) breakdown = '' for era in sess.query(Era).filter( Era.supply_id == supply.id, Era.start_date <= year_finish, or_( Era.finish_date == null(), Era.finish_date >= year_start)): meter_type = era.make_meter_category() era_start = era.start_date period_start = era_start \ if era_start > year_start else year_start era_finish = era.finish_date if hh_after(era_finish, year_finish): period_finish = year_finish else: period_finish = era_finish max_normal_days[meter_type] += ( (period_finish - period_start).total_seconds() + 60 * 30) / (60 * 60 * 24) mpan_core = era.imp_mpan_core site = sess.query(Site).join(SiteEra).filter( SiteEra.is_physical == true(), SiteEra.era_id == era.id).one() if meter_type == 'nhh': read_list = [] read_keys = {} pairs = [] prior_pres_reads = iter( sess.query(RegisterRead).join(Bill).join(BillType) .join(RegisterRead.present_type).filter( RegisterRead.units == 0, ReadType.code.in_(ACTUAL_READ_TYPES), Bill.supply == supply, RegisterRead.present_date < period_start, BillType.code != 'W').order_by( RegisterRead.present_date.desc())) prior_prev_reads = iter( sess.query(RegisterRead).join(Bill).join(BillType) .join(RegisterRead.previous_type).filter( RegisterRead.units == 0, ReadType.code.in_(ACTUAL_READ_TYPES), Bill.supply == supply, RegisterRead.previous_date < period_start, BillType.code != 'W').order_by( RegisterRead.previous_date.desc())) next_pres_reads = iter( sess.query(RegisterRead).join(Bill).join(BillType) .join(RegisterRead.present_type).filter( RegisterRead.units == 0, ReadType.code.in_(ACTUAL_READ_TYPES), Bill.supply == supply, RegisterRead.present_date >= period_start, BillType.code != 'W').order_by( RegisterRead.present_date)) next_prev_reads = iter( sess.query(RegisterRead).join(Bill).join(BillType). join(RegisterRead.previous_type).filter( RegisterRead.units == 0, ReadType.code.in_(ACTUAL_READ_TYPES), Bill.supply == supply, RegisterRead.previous_date >= period_start, BillType.code != 'W').order_by( RegisterRead.previous_date)) for is_forwards in [False, True]: if is_forwards: pres_reads = next_pres_reads prev_reads = next_prev_reads read_list.reverse() else: pres_reads = prior_pres_reads prev_reads = prior_prev_reads prime_pres_read = None prime_prev_read = None while True: while prime_pres_read is None: try: pres_read = next(pres_reads) except StopIteration: break pres_date = pres_read.present_date pres_msn = pres_read.msn read_key = '_'.join([str(pres_date), pres_msn]) if read_key in read_keys: continue pres_bill = sess.query(Bill).join(BillType). \ filter( Bill.reads.any(), Bill.supply == supply, Bill.finish_date >= pres_read.bill.start_date, Bill.start_date <= pres_read.bill.finish_date, BillType.code != 'W').order_by( Bill.issue_date.desc(), BillType.code).first() if pres_bill != pres_read.bill: continue reads = dict( ( read.tpr.code, float(read.present_value) * float(read.coefficient)) for read in sess.query(RegisterRead). filter( RegisterRead.units == 0, RegisterRead.bill == pres_bill, RegisterRead.present_date == pres_date, RegisterRead.msn == pres_msn)) prime_pres_read = { 'date': pres_date, 'reads': reads, 'msn': pres_msn} read_keys[read_key] = None while prime_prev_read is None: try: prev_read = next(prev_reads) except StopIteration: break prev_date = prev_read.previous_date prev_msn = prev_read.msn read_key = '_'.join([str(prev_date), prev_msn]) if read_key in read_keys: continue prev_bill = sess.query(Bill).join(BillType). \ filter( Bill.reads.any(), Bill.supply_id == supply.id, Bill.finish_date >= prev_read.bill.start_date, Bill.start_date <= prev_read.bill.finish_date, BillType.code != 'W').order_by( Bill.issue_date.desc(), BillType.code).first() if prev_bill != prev_read.bill: continue reads = dict( ( read.tpr.code, float(read.previous_value) * float(read.coefficient)) for read in sess.query(RegisterRead). filter( RegisterRead.units == 0, RegisterRead.bill_id == prev_bill.id, RegisterRead.previous_date == prev_date, RegisterRead.msn == prev_msn)) prime_prev_read = { 'date': prev_date, 'reads': reads, 'msn': prev_msn} read_keys[read_key] = None if prime_pres_read is None and \ prime_prev_read is None: break elif prime_pres_read is None: read_list.append(prime_prev_read) prime_prev_read = None elif prime_prev_read is None: read_list.append(prime_pres_read) prime_pres_read = None else: if is_forwards: if prime_pres_read['date'] <= \ prime_prev_read['date']: read_list.append(prime_pres_read) prime_pres_read = None else: read_list.append(prime_prev_read) prime_prev_read = None else: if prime_prev_read['date'] >= \ prime_pres_read['date']: read_list.append(prime_prev_read) prime_prev_read = None else: read_list.append(prime_pres_read) prime_pres_read = None if len(read_list) > 1: if is_forwards: aft_read = read_list[-2] fore_read = read_list[-1] else: aft_read = read_list[-1] fore_read = read_list[-2] if aft_read['msn'] == fore_read['msn'] and \ set(aft_read['reads'].keys()) == \ set(fore_read['reads'].keys()): pair_start_date = aft_read['date'] + HH pair_finish_date = fore_read['date'] num_hh = ( ( pair_finish_date + HH - pair_start_date).total_seconds() ) / (30 * 60) tprs = {} for tpr_code, initial_val in \ aft_read['reads'].items(): end_val = fore_read['reads'][tpr_code] kwh = end_val - initial_val if kwh < 0: digits = int( math.log10(initial_val)) + 1 kwh = 10 ** digits + kwh tprs[tpr_code] = kwh / num_hh pairs.append( { 'start-date': pair_start_date, 'finish-date': pair_finish_date, 'tprs': tprs}) if len(pairs) > 0 and \ (not is_forwards or ( is_forwards and read_list[-1]['date'] > period_finish)): break breakdown += 'read list - \n' + str(read_list) + "\n" if len(pairs) == 0: pairs.append( { 'start-date': period_start, 'finish-date': period_finish, 'tprs': {'00001': 0}}) else: for pair in pairs: pair_start = pair['start-date'] pair_finish = pair['finish-date'] if pair_start >= year_start and \ pair_finish <= year_finish: if pair_start > period_start: block_start = pair_start else: block_start = period_start if pair_finish < period_finish: block_finish = pair_finish else: block_finish = period_finish if block_start <= block_finish: normal_days[meter_type] += ( ( block_finish - block_start ).total_seconds() + 60 * 30) / (60 * 60 * 24) # smooth for i in range(1, len(pairs)): pairs[i - 1]['finish-date'] = pairs[i]['start-date'] \ - HH # stretch if pairs[0]['start-date'] > period_start: pairs[0]['start-date'] = period_start if pairs[-1]['finish-date'] < period_finish: pairs[-1]['finish-date'] = period_finish # chop pairs = [ pair for pair in pairs if not pair['start-date'] > period_finish and not pair['finish-date'] < period_start] # squash if pairs[0]['start-date'] < period_start: pairs[0]['start-date'] = period_start if pairs[-1]['finish-date'] > period_finish: pairs[-1]['finish-date'] = period_finish for pair in pairs: pair_hhs = ( ( pair['finish-date'] - pair['start-date'] ).total_seconds() + 30 * 60) / (60 * 30) pair['pair_hhs'] = pair_hhs for tpr_code, pair_kwh in pair['tprs'].items(): total_kwh[meter_type] += pair_kwh * pair_hhs breakdown += 'pairs - \n' + str(pairs) elif meter_type in ('hh', 'amr'): period_kwhs = list( float(v[0]) for v in sess.query(HhDatum.value). join(Channel).filter( Channel.imp_related == true(), Channel.channel_type == 'ACTIVE', Channel.era == era, HhDatum.start_date >= period_start, HhDatum.start_date <= period_finish).order_by( HhDatum.id)) year_kwhs = list( float(v[0]) for v in sess.query(HhDatum.value). join(Channel).join(Era).filter( Channel.imp_related == true(), Channel.channel_type == 'ACTIVE', Era.supply == supply, HhDatum.start_date >= year_start, HhDatum.start_date <= year_finish).order_by( HhDatum.id)) period_sum_kwhs = sum(period_kwhs) year_sum_kwhs = sum(year_kwhs) period_len_kwhs = len(period_kwhs) year_len_kwhs = len(year_kwhs) total_kwh[meter_type] += period_sum_kwhs period_hhs = ( period_finish + HH - period_start ).total_seconds() / (60 * 30) if year_len_kwhs > 0: filled_kwh[meter_type] += year_sum_kwhs / \ year_len_kwhs * (period_hhs - period_len_kwhs) normal_days[meter_type] += sess.query( func.count(HhDatum.value)).join(Channel). \ filter( Channel.imp_related == true(), Channel.channel_type == 'ACTIVE', Channel.era == era, HhDatum.start_date >= period_start, HhDatum.start_date <= period_finish, HhDatum.status == 'A').one()[0] / 48 elif meter_type == 'unmetered': bills = sess.query(Bill).filter( Bill.supply == supply, Bill.finish_date >= period_start, Bill.start_date <= period_finish) for bill in bills: total_kwh[meter_type] += kwh normal_days[meter_type] += ( ( period_finish - period_start).total_seconds() + 60 * 30) / (60 * 60 * 24) # for full year 183 total_normal_days = sum(normal_days.values()) total_max_normal_days = sum(max_normal_days.values()) is_normal = total_normal_days / total_max_normal_days >= 183 / 365 f.write( ','.join( '"' + str(val) + '"' for val in [ supply.id, mpan_core, site.code, site.name, hh_format(year_start), hh_format(year_finish), breakdown] + [ normal_days[type] for type in meter_types] + [ max_normal_days[type] for type in meter_types] + [ total_normal_days, total_max_normal_days, "Actual" if is_normal else "Estimated"] + [total_kwh[type] for type in meter_types] + [filled_kwh[type] for type in ('hh', 'amr')] + [sum(total_kwh.values()) + sum(filled_kwh.values()), '']) + '\n') # avoid a long running transaction sess.rollback() except: msg = traceback.format_exc() sys.stderr.write(msg + '\n') f.write("Problem " + msg) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def content(start_date, finish_date, g_contract_id, user): report_context = {} sess = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( "gas_virtual_bills.csv", user ) f = open(running_name, mode="w", newline="") writer = csv.writer(f, lineterminator="\n") g_contract = GContract.get_by_id(sess, g_contract_id) forecast_dt = forecast_date() month_start = utc_datetime(start_date.year, start_date.month, 1) month_finish = month_start + relativedelta(months=1) - HH bill_titles = contract_func(report_context, g_contract, "virtual_bill_titles")() writer.writerow( ["MPRN", "Site Code", "Site Name", "Account", "From", "To"] + bill_titles ) while not month_start > finish_date: period_start = hh_max(start_date, month_start) period_finish = hh_min(finish_date, month_finish) for g_era in ( sess.query(GEra) .distinct() .filter( GEra.g_contract == g_contract, GEra.start_date <= period_finish, or_(GEra.finish_date == null(), GEra.finish_date >= period_start), ) ): chunk_start = hh_max(g_era.start_date, period_start) chunk_finish = hh_min(g_era.finish_date, period_finish) data_source = GDataSource( sess, chunk_start, chunk_finish, forecast_dt, g_era, report_context, None, ) site = ( sess.query(Site) .join(SiteGEra) .filter(SiteGEra.g_era == g_era, SiteGEra.is_physical == true()) .one() ) vals = [ data_source.mprn, site.code, site.name, data_source.account, hh_format(data_source.start_date), hh_format(data_source.finish_date), ] contract_func(report_context, g_contract, "virtual_bill")(data_source) bill = data_source.bill for title in bill_titles: if title in bill: val = make_val(bill[title]) del bill[title] else: val = "" vals.append(val) for k in sorted(bill.keys()): vals.append(k) vals.append(str(bill[k])) writer.writerow(vals) month_start += relativedelta(months=1) month_finish = month_start + relativedelta(months=1) - HH except BadRequest as e: writer.writerow(["Problem: " + e.description]) except BaseException: msg = traceback.format_exc() sys.stderr.write(msg) writer.writerow([msg]) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)
def content(scenario_props, base_name, site_id, supply_id, user, compression, site_codes, now): report_context = {} try: comp = report_context["computer"] except KeyError: comp = report_context["computer"] = {} try: rate_cache = comp["rates"] except KeyError: rate_cache = comp["rates"] = {} try: ind_cont = report_context["contract_names"] except KeyError: ind_cont = report_context["contract_names"] = {} sess = None try: sess = Session() start_year = scenario_props["scenario_start_year"] start_month = scenario_props["scenario_start_month"] months = scenario_props["scenario_duration"] month_pairs = list( c_months_u(start_year=start_year, start_month=start_month, months=months)) start_date_utc = month_pairs[0][0] finish_date_utc = month_pairs[-1][-1] base_name.append( hh_format(start_date_utc).replace(" ", "_").replace(":", "").replace( "-", "")) base_name.append("for") base_name.append(str(months)) base_name.append("months") if "forecast_from" in scenario_props: forecast_from = scenario_props["forecast_from"] else: forecast_from = None if forecast_from is None: forecast_from = chellow.computer.forecast_date() else: forecast_from = to_utc(forecast_from) sites = sess.query(Site).distinct().order_by(Site.code) if site_id is not None: site = Site.get_by_id(sess, site_id) sites = sites.filter(Site.id == site.id) base_name.append("site") base_name.append(site.code) if supply_id is not None: supply = Supply.get_by_id(sess, supply_id) base_name.append("supply") base_name.append(str(supply.id)) sites = sites.join(SiteEra).join(Era).filter(Era.supply == supply) if len(site_codes) > 0: base_name.append("sitecodes") sites = sites.filter(Site.code.in_(site_codes)) running_name, finished_name = chellow.dloads.make_names( "_".join(base_name) + ".ods", user) rf = open(running_name, "wb") site_rows = [] era_rows = [] for rate_script in scenario_props.get("local_rates", []): contract_id = rate_script["contract_id"] try: cont_cache = rate_cache[contract_id] except KeyError: cont_cache = rate_cache[contract_id] = {} try: rate_script_start = rate_script["start_date"] except KeyError: raise BadRequest( f"Problem in the scenario properties. Can't find the " f"'start_date' key of the contract {contract_id} in " f"the 'local_rates' map.") try: rate_script_start = rate_script["start_date"] except KeyError: raise BadRequest( f"Problem in the scenario properties. Can't find the " f"'start_date' key of the contract {contract_id} in " f"the 'local_rates' map.") props = PropDict("scenario properties", rate_script["script"]) for dt in hh_range(report_context, rate_script_start, rate_script["finish_date"]): cont_cache[dt] = props for rate_script in scenario_props.get("industry_rates", []): contract_name = rate_script["contract_name"] try: cont_cache = ind_cont[contract_name] except KeyError: cont_cache = ind_cont[contract_name] = {} rfinish = rate_script["finish_date"] if rfinish is None: raise BadRequest( f"For the industry rate {contract_name} the finish_date " f"can't be null.") for dt in hh_range(report_context, rate_script["start_date"], rfinish): cont_cache[dt] = PropDict("scenario properties", rate_script["script"]) era_maps = scenario_props.get("era_maps", {}) by_hh = scenario_props.get("by_hh", False) scenario_hh = scenario_props.get("hh_data", {}) era_header_titles = [ "creation-date", "imp-mpan-core", "imp-supplier-contract", "exp-mpan-core", "exp-supplier-contract", "metering-type", "source", "generator-type", "supply-name", "msn", "pc", "site-id", "site-name", "associated-site-ids", "month", ] site_header_titles = [ "creation-date", "site-id", "site-name", "associated-site-ids", "month", "metering-type", "sources", "generator-types", ] summary_titles = [ "import-net-kwh", "export-net-kwh", "import-gen-kwh", "export-gen-kwh", "import-3rd-party-kwh", "export-3rd-party-kwh", "displaced-kwh", "used-kwh", "used-3rd-party-kwh", "import-net-gbp", "export-net-gbp", "import-gen-gbp", "export-gen-gbp", "import-3rd-party-gbp", "export-3rd-party-gbp", "displaced-gbp", "used-gbp", "used-3rd-party-gbp", "billed-import-net-kwh", "billed-import-net-gbp", "billed-supplier-import-net-gbp", "billed-dc-import-net-gbp", "billed-mop-import-net-gbp", ] title_dict = {} for cont_type, con_attr in ( ("mop", Era.mop_contract), ("dc", Era.dc_contract), ("imp-supplier", Era.imp_supplier_contract), ("exp-supplier", Era.exp_supplier_contract), ): titles = [] title_dict[cont_type] = titles conts = (sess.query(Contract).join(con_attr).join( Era.supply).join(Source).filter( Era.start_date <= finish_date_utc, or_(Era.finish_date == null(), Era.finish_date >= start_date_utc), ).distinct().order_by(Contract.id)) if supply_id is not None: conts = conts.filter(Era.supply_id == supply_id) for cont in conts: title_func = chellow.computer.contract_func( report_context, cont, "virtual_bill_titles") if title_func is None: raise Exception( f"For the contract {cont.name} there doesn't seem to " f"be a 'virtual_bill_titles' function.") for title in title_func(): if title not in titles: titles.append(title) tpr_query = (sess.query(Tpr).join(MeasurementRequirement).join( Ssc).join(Era).filter( Era.start_date <= finish_date_utc, or_(Era.finish_date == null(), Era.finish_date >= start_date_utc), ).order_by(Tpr.code).distinct()) for tpr in tpr_query.filter(Era.imp_supplier_contract != null()): for suffix in ("-kwh", "-rate", "-gbp"): title_dict["imp-supplier"].append(tpr.code + suffix) for tpr in tpr_query.filter(Era.exp_supplier_contract != null()): for suffix in ("-kwh", "-rate", "-gbp"): title_dict["exp-supplier"].append(tpr.code + suffix) era_rows.append( era_header_titles + summary_titles + [None] + ["mop-" + t for t in title_dict["mop"]] + [None] + ["dc-" + t for t in title_dict["dc"]] + [None] + ["imp-supplier-" + t for t in title_dict["imp-supplier"]] + [None] + ["exp-supplier-" + t for t in title_dict["exp-supplier"]]) site_rows.append(site_header_titles + summary_titles) sites = sites.all() deltas = {} normal_reads = set() normal_read_rows = [] for site in sites: deltas[site.id] = _make_site_deltas(sess, report_context, site, scenario_hh, forecast_from, supply_id) for month_start, month_finish in month_pairs: for site in sites: if by_hh: sf = [(d, d) for d in hh_range(report_context, month_start, month_finish)] else: sf = [(month_start, month_finish)] for start, finish in sf: normal_reads = normal_reads | _process_site( sess, report_context, forecast_from, start, finish, site, deltas[site.id], supply_id, era_maps, now, summary_titles, title_dict, era_rows, site_rows, ) normal_read_rows = [[ "mpan_core", "date", "msn", "type", "registers" ]] for mpan_core, r in sorted(list(normal_reads)): row = [mpan_core, r.date, r.msn, r.type] + list(r.reads) normal_read_rows.append(row) write_spreadsheet(rf, compression, site_rows, era_rows, normal_read_rows) except BadRequest as e: msg = e.description + traceback.format_exc() sys.stderr.write(msg + "\n") site_rows.append(["Problem " + msg]) write_spreadsheet(rf, compression, site_rows, era_rows, normal_read_rows) except BaseException: msg = traceback.format_exc() sys.stderr.write(msg + "\n") site_rows.append(["Problem " + msg]) write_spreadsheet(rf, compression, site_rows, era_rows, normal_read_rows) finally: if sess is not None: sess.close() try: rf.close() os.rename(running_name, finished_name) except BaseException: msg = traceback.format_exc() r_name, f_name = chellow.dloads.make_names("error.txt", user) ef = open(r_name, "w") ef.write(msg + "\n") ef.close()
def content( batch_id, bill_id, contract_id, start_date, finish_date, user, mpan_cores, fname_additional, ): caches = {} tmp_file = sess = supply_id = None forecast_date = to_utc(Datetime.max) try: running_name, finished_name = chellow.dloads.make_names( "bill_check_" + fname_additional + ".csv", user) tmp_file = open(running_name, mode="w", newline="") writer = csv.writer(tmp_file, lineterminator="\n") sess = Session() report_run = ReportRun("bill_check", user, fname_additional) sess.add(report_run) bills = (sess.query(Bill).order_by( Bill.supply_id, Bill.reference).options( joinedload(Bill.supply), subqueryload(Bill.reads).joinedload(RegisterRead.present_type), subqueryload(Bill.reads).joinedload( RegisterRead.previous_type), joinedload(Bill.batch), )) if len(mpan_cores) > 0: mpan_cores = list(map(parse_mpan_core, mpan_cores)) supply_ids = [ i[0] for i in sess.query(Era.supply_id).filter( or_( Era.imp_mpan_core.in_(mpan_cores), Era.exp_mpan_core.in_(mpan_cores), )).distinct() ] bills = bills.join(Supply).filter(Supply.id.in_(supply_ids)) if batch_id is not None: batch = Batch.get_by_id(sess, batch_id) bills = bills.filter(Bill.batch == batch) contract = batch.contract elif bill_id is not None: bill = Bill.get_by_id(sess, bill_id) bills = bills.filter(Bill.id == bill.id) contract = bill.batch.contract elif contract_id is not None: contract = Contract.get_by_id(sess, contract_id) bills = bills.join(Batch).filter( Batch.contract == contract, Bill.start_date <= finish_date, Bill.finish_date >= start_date, ) vbf = chellow.computer.contract_func(caches, contract, "virtual_bill") if vbf is None: raise BadRequest("The contract " + contract.name + " doesn't have a function virtual_bill.") virtual_bill_titles_func = chellow.computer.contract_func( caches, contract, "virtual_bill_titles") if virtual_bill_titles_func is None: raise BadRequest("The contract " + contract.name + " doesn't have a function virtual_bill_titles.") virtual_bill_titles = virtual_bill_titles_func() titles = [ "batch", "bill-reference", "bill-type", "bill-kwh", "bill-net-gbp", "bill-vat-gbp", "bill-start-date", "bill-finish-date", "imp-mpan-core", "exp-mpan-core", "site-code", "site-name", "covered-from", "covered-to", "covered-bills", "metered-kwh", ] for t in virtual_bill_titles: titles.append("covered-" + t) titles.append("virtual-" + t) if t.endswith("-gbp"): titles.append("difference-" + t) writer.writerow(titles) bill_map = defaultdict(set, {}) for bill in bills: bill_map[bill.supply.id].add(bill.id) for supply_id in bill_map.keys(): _process_supply( sess, caches, supply_id, bill_map, forecast_date, contract, vbf, virtual_bill_titles, writer, titles, report_run, ) report_run.update("finished") sess.commit() except BadRequest as e: if report_run is not None: report_run.update("problem") if supply_id is None: prefix = "Problem: " else: prefix = "Problem with supply " + str(supply_id) + ":" tmp_file.write(prefix + e.description) except BaseException: if report_run is not None: report_run.update("interrupted") if supply_id is None: prefix = "Problem: " else: prefix = "Problem with supply " + str(supply_id) + ":" msg = traceback.format_exc() sys.stderr.write(msg + "\n") tmp_file.write(prefix + msg) finally: if sess is not None: sess.close() tmp_file.close() os.rename(running_name, finished_name)
def make_raw_bills(self): row_index = None sess = None try: sess = Session() bills = [] title_row = self.sheet.row(10) issue_date_str = get_str(self.sheet.row(6), 0) issue_date = Datetime.strptime(issue_date_str[6:], "%d/%m/%Y %H:%M:%S") for row_index in range(11, self.sheet.nrows): row = self.sheet.row(row_index) val = get_value(row, 1) if val is None or val == '': break self._set_last_line(row_index, val) mpan_core = parse_mpan_core(str(get_int(row, 1))) start_date = get_date(row, 3, self.book.datemode) finish_date = get_date(row, 4, self.book.datemode) + \ Timedelta(days=1) - HH era = sess.query(Era).filter( or_(Era.imp_mpan_core == mpan_core, Era.exp_mpan_core == mpan_core), Era.start_date <= finish_date, or_(Era.finish_date == null(), Era.finish_date > start_date)).order_by( Era.start_date).first() if era is None: era = sess.query(Era).filter( or_(Era.imp_mpan_core == mpan_core, Era.exp_mpan_core == mpan_core)).order_by( Era.start_date.desc()).first() if era is None: account = mpan_core + '/DC' else: account = era.dc_account net = round(get_dec(row, 31), 2) cop_3_meters = get_int(row, 6) cop_3_rate = get_dec(row, 7) cop_3_gbp = get_dec(row, 8) # Cop 5 meters get_int(row, 9) cop_5_rate = get_dec(row, 10) cop_5_gbp = get_dec(row, 11) ad_hoc_visits = get_dec(row, 21) ad_hoc_rate = get_dec(row, 22) ad_hoc_gbp = get_dec(row, 23) annual_visits = get_int(row, 27) annual_rate = get_dec(row, 28) annual_gbp = get_dec(row, 29) annual_date = hh_format(get_date(row, 30, self.book.datemode)) if cop_3_meters > 0: cop = '3' mpan_rate = cop_3_rate mpan_gbp = cop_3_gbp else: cop = '5' mpan_rate = cop_5_rate mpan_gbp = cop_5_gbp breakdown = { 'raw_lines': [str(title_row)], 'cop': [cop], 'settlement-status': ['settlement'], 'mpan-rate': [mpan_rate], 'mpan-gbp': mpan_gbp, 'ad-hoc-visits': ad_hoc_visits, 'ad-hoc-rate': [ad_hoc_rate], 'ad-hoc-gbp': ad_hoc_gbp, 'annual-visits-count': annual_visits, 'annual-visits-rate': [annual_rate], 'annual-visits-gbp': annual_gbp, 'annual-visits-date': [annual_date] } bills.append({ 'bill_type_code': 'N', 'kwh': Decimal(0), 'vat': Decimal('0.00'), 'net': net, 'gross': net, 'reads': [], 'breakdown': breakdown, 'account': account, 'issue_date': issue_date, 'start_date': start_date, 'finish_date': finish_date, 'mpans': [mpan_core], 'reference': '_'.join((start_date.strftime('%Y%m%d'), finish_date.strftime('%Y%m%d'), issue_date.strftime('%Y%m%d'), mpan_core)) }) sess.rollback() except BadRequest as e: raise BadRequest("Row number: " + str(row_index) + " " + e.description) finally: if sess is not None: sess.close() return bills
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(supply_id, file_name, start_date, finish_date, user): caches = {} sess = None try: sess = Session() running_name, finished_name = chellow.dloads.make_names( 'supply_virtual_bills_' + str(supply_id) + '.csv', user) f = open(running_name, mode='w', newline='') writer = csv.writer(f, lineterminator='\n') supply = Supply.get_by_id(sess, supply_id) forecast_date = chellow.computer.forecast_date() prev_titles = None month_start = datetime.datetime( start_date.year, start_date.month, 1, tzinfo=pytz.utc) while not month_start > finish_date: month_finish = month_start + relativedelta(months=1) - HH if month_start > start_date: period_start = month_start else: period_start = start_date if month_finish > finish_date: period_finish = finish_date else: period_finish = month_finish for era in sess.query(Era).filter( Era.supply == supply, Era.start_date < period_finish, or_( Era.finish_date == null(), Era.finish_date > period_start )).order_by(Era.start_date): chunk_start = era.start_date \ if era.start_date > period_start else period_start chunk_finish = period_finish \ if hh_before(period_finish, era.finish_date) \ else era.finish_date site = sess.query(Site).join(SiteEra).filter( SiteEra.era == era, SiteEra.is_physical == true()).one() ds = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, True, None, caches) titles = [ 'Imp MPAN Core', 'Exp MPAN Core', 'Site Code', 'Site Name', 'Account', 'From', 'To', ''] output_line = [ era.imp_mpan_core, era.exp_mpan_core, site.code, site.name, ds.supplier_account, hh_format(ds.start_date), hh_format(ds.finish_date), ''] mop_titles = ds.contract_func( era.mop_contract, 'virtual_bill_titles')() titles.extend(['mop-' + t for t in mop_titles]) ds.contract_func(era.mop_contract, 'virtual_bill')(ds) bill = ds.mop_bill for title in mop_titles: if title in bill: output_line.append(bill[title]) del bill[title] else: output_line.append('') for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) output_line.append('') dc_titles = ds.contract_func( era.hhdc_contract, 'virtual_bill_titles')() titles.append('') titles.extend(['dc-' + t for t in dc_titles]) ds.contract_func(era.hhdc_contract, 'virtual_bill')(ds) bill = ds.dc_bill for title in dc_titles: output_line.append(bill.get(title, '')) if title in bill: del bill[title] for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) if era.imp_supplier_contract is not None: output_line.append('') imp_supplier_titles = ds.contract_func( era.imp_supplier_contract, 'virtual_bill_titles')() titles.append('') titles.extend( ['imp-supplier-' + t for t in imp_supplier_titles]) ds.contract_func( era.imp_supplier_contract, 'virtual_bill')(ds) bill = ds.supplier_bill for title in imp_supplier_titles: if title in bill: output_line.append(bill[title]) del bill[title] else: output_line.append('') for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) if era.exp_supplier_contract is not None: ds = chellow.computer.SupplySource( sess, chunk_start, chunk_finish, forecast_date, era, False, None, caches) output_line.append('') exp_supplier_titles = ds.contract_func( era.exp_supplier_contract, 'virtual_bill_titles')() titles.append('') titles.extend( ['exp-supplier-' + t for t in exp_supplier_titles]) ds.contract_func( era.exp_supplier_contract, 'virtual_bill')(ds) bill = ds.supplier_bill for title in exp_supplier_titles: output_line.append(bill.get(title, '')) if title in bill: del bill[title] for k in sorted(bill.keys()): output_line.extend([k, bill[k]]) if titles != prev_titles: prev_titles != titles writer.writerow([str(v) for v in titles]) for i, val in enumerate(output_line): if isinstance(val, datetime.datetime): output_line[i] = hh_format(val) elif val is None: output_line[i] = '' else: output_line[i] = str(val) writer.writerow(output_line) month_start += relativedelta(months=1) except BadRequest as e: writer.writerow(["Problem: " + e.description]) except: writer.writerow([traceback.format_exc()]) finally: if sess is not None: sess.close() if f is not None: f.close() os.rename(running_name, finished_name)