Exemple #1
0
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)
Exemple #2
0
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)
Exemple #3
0
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)
Exemple #4
0
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()
Exemple #5
0
def content(contract_id, end_year, end_month, months, user):
    caches = {}
    sess = f = None
    try:
        sess = Session()
        running_name, finished_name = chellow.dloads.make_names(
            'displaced.csv', user)
        f = open(running_name, mode='w', newline='')
        writer = csv.writer(f, lineterminator='\n')
        titles = [
            'Site Code', 'Site Name', 'Associated Site Ids', 'From', 'To',
            'Gen Types', 'CHP kWh', 'LM kWh', 'Turbine kWh', 'PV kWh'
        ]

        finish_date = Datetime(end_year, end_month, 1, tzinfo=pytz.utc) + \
            relativedelta(months=1) - HH

        start_date = Datetime(end_year, end_month, 1, tzinfo=pytz.utc) - \
            relativedelta(months=months-1)

        forecast_date = chellow.computer.forecast_date()

        contract = Contract.get_supplier_by_id(sess, contract_id)
        sites = sess.query(Site).join(SiteEra).join(Era).join(Supply). \
            join(Source).filter(
                or_(Era.finish_date == null(), Era.finish_date >= start_date),
                Era.start_date <= finish_date,
                or_(
                    Source.code.in_(('gen', 'gen-net')),
                    Era.exp_mpan_core != null())).distinct()
        bill_titles = chellow.computer.contract_func(
            caches, contract, 'displaced_virtual_bill_titles')()

        for title in bill_titles:
            if title == 'total-msp-kwh':
                title = 'total-displaced-msp-kwh'
            titles.append(title)
        writer.writerow(titles)

        for site in sites:
            month_start = start_date
            month_finish = month_start + relativedelta(months=1) - HH
            for i in range(months):
                displaced_era = chellow.computer.displaced_era(
                    sess, caches, site, month_start, month_finish,
                    forecast_date)
                if displaced_era is None:
                    continue
                supplier_contract = displaced_era.imp_supplier_contract
                if contract is not None and contract != supplier_contract:
                    continue

                linked_sites = set()
                generator_types = set()
                for era in sess.query(Era).join(SiteEra).filter(
                        SiteEra.site == site, Era.start_date <= month_finish,
                        or_(Era.finish_date == null(),
                            Era.finish_date >= month_start)):
                    for site_era in era.site_eras:
                        if site_era.site != site:
                            linked_sites.add(site_era.site.code)
                    supply = era.supply
                    if supply.generator_type is not None:
                        generator_types.add(supply.generator_type.code)

                supply_ids = set()
                for era in sess.query(Era).join(SiteEra).filter(
                        SiteEra.site == site, SiteEra.is_physical,
                        Era.start_date <= month_finish,
                        or_(Era.finish_date == null(),
                            Era.finish_date >= month_start)):
                    supply_ids.add(era.supply.id)

                vals = [
                    site.code, site.name, ', '.join(list(linked_sites)),
                    hh_format(month_start),
                    hh_format(month_finish), ', '.join(list(generator_types))
                ]

                total_gen_breakdown = {}

                results = iter(
                    sess.execute(
                        "select supply.id, hh_datum.value, "
                        "hh_datum.start_date, channel.imp_related, "
                        "source.code, generator_type.code as "
                        "gen_type_code from hh_datum, channel, source, "
                        "era, supply left outer join generator_type on "
                        "supply.generator_type_id = generator_type.id "
                        "where hh_datum.channel_id = channel.id and "
                        "channel.era_id = era.id and era.supply_id = "
                        "supply.id and supply.source_id = source.id and "
                        "channel.channel_type = 'ACTIVE' and not "
                        "(source.code = 'net' and channel.imp_related "
                        "is true) and hh_datum.start_date >= "
                        ":month_start and hh_datum.start_date "
                        "<= :month_finish and "
                        "supply.id = any(:supply_ids) order "
                        "by hh_datum.start_date, supply.id",
                        params={
                            'month_start': month_start,
                            'month_finish': month_finish,
                            'supply_ids': sorted(list(supply_ids))
                        }))

                (sup_id, hh_val, hh_start, imp_related, source_code,
                 gen_type_code) = next(results,
                                       (None, None, None, None, None, None))

                for hh_date in hh_range(caches, month_start, month_finish):
                    gen_breakdown = {}
                    exported = 0
                    while hh_start == hh_date:
                        if not imp_related and source_code in ('net',
                                                               'gen-net'):
                            exported += hh_val
                        if (imp_related and source_code == 'gen') or \
                                (not imp_related and
                                    source_code == 'gen-net'):
                            gen_breakdown[gen_type_code] = \
                                gen_breakdown.setdefault(
                                    gen_type_code, 0) + hh_val

                        if (not imp_related and source_code == 'gen') or (
                                imp_related and source_code == 'gen-net'):
                            gen_breakdown[gen_type_code] = \
                                gen_breakdown.setdefault(
                                    gen_type_code, 0) - hh_val

                        (sup_id, hh_val, hh_start, imp_related,
                         source_code, gen_type_code) = next(
                             results, (None, None, None, None, None, None))

                    displaced = sum(gen_breakdown.values()) - exported
                    added_so_far = 0
                    for key in sorted(gen_breakdown.keys()):
                        kwh = gen_breakdown[key]
                        if kwh + added_so_far > displaced:
                            total_gen_breakdown[key] = \
                                total_gen_breakdown.get(key, 0) + \
                                displaced - added_so_far
                            break
                        else:
                            total_gen_breakdown[key] = \
                                total_gen_breakdown.get(key, 0) + kwh
                            added_so_far += kwh

                for title in ['chp', 'lm', 'turb', 'pv']:
                    vals.append(str(total_gen_breakdown.get(title, '')))

                site_ds = chellow.computer.SiteSource(sess, site, month_start,
                                                      month_finish,
                                                      forecast_date, caches,
                                                      displaced_era)
                disp_func = chellow.computer.contract_func(
                    caches, supplier_contract, 'displaced_virtual_bill')
                disp_func(site_ds)
                bill = site_ds.supplier_bill
                for title in bill_titles:
                    if title in bill:
                        vals.append(to_val(bill[title]))
                        del bill[title]
                    else:
                        vals.append('')

                for k in sorted(bill.keys()):
                    vals.append(k)
                    vals.append(str(bill[k]))
                writer.writerow(vals)

                month_start += relativedelta(months=1)
                month_finish = month_start + relativedelta(months=1) - HH
    except BaseException:
        msg = traceback.format_exc()
        sys.stderr.write(msg)
        writer.writerow([msg])
    finally:
        if sess is not None:
            sess.close()
        if f is not None:
            f.close()
            os.rename(running_name, finished_name)
Exemple #6
0
def content(scenario_props, scenario_id, base_name, site_id, supply_id, user,
            compression):
    now = utc_datetime_now()
    report_context = {}

    try:
        comp = report_context['computer']
    except KeyError:
        comp = report_context['computer'] = {}

    try:
        rate_cache = comp['rates']
    except KeyError:
        rate_cache = comp['rates'] = {}

    try:
        ind_cont = report_context['contract_names']
    except KeyError:
        ind_cont = report_context['contract_names'] = {}

    sess = None
    try:
        sess = Session()
        if scenario_props is None:
            scenario_contract = Contract.get_supplier_by_id(sess, scenario_id)
            scenario_props = scenario_contract.make_properties()
            base_name.append(scenario_contract.name)

        start_date = scenario_props['scenario_start']
        if start_date is None:
            start_date = utc_datetime(now.year, now.month, 1)
        else:
            start_date = to_utc(start_date)

        base_name.append(
            hh_format(start_date).replace(' ',
                                          '_').replace(':',
                                                       '').replace('-', ''))

        months = scenario_props['scenario_duration']
        base_name.append('for')
        base_name.append(str(months))
        base_name.append('months')
        finish_date = start_date + relativedelta(months=months)

        if 'forecast_from' in scenario_props:
            forecast_from = scenario_props['forecast_from']
        else:
            forecast_from = None

        if forecast_from is None:
            forecast_from = chellow.computer.forecast_date()
        else:
            forecast_from = to_utc(forecast_from)

        sites = sess.query(Site).distinct().order_by(Site.code)
        if site_id is not None:
            site = Site.get_by_id(sess, site_id)
            sites = sites.filter(Site.id == site.id)
            base_name.append('site')
            base_name.append(site.code)
        if supply_id is not None:
            supply = Supply.get_by_id(sess, supply_id)
            base_name.append('supply')
            base_name.append(str(supply.id))
            sites = sites.join(SiteEra).join(Era).filter(Era.supply == supply)

        running_name, finished_name = chellow.dloads.make_names(
            '_'.join(base_name) + '.ods', user)

        rf = open(running_name, "wb")
        site_rows = []
        era_rows = []

        for rate_script in get_map_list(scenario_props, 'local_rates'):
            contract_id = rate_script['contract_id']
            try:
                cont_cache = rate_cache[contract_id]
            except KeyError:
                cont_cache = rate_cache[contract_id] = {}

            try:
                rate_script_start = rate_script['start_date']
            except KeyError:
                raise BadRequest(
                    "Problem in the scenario properties. Can't find the " +
                    "'start_date' key of the contract " + str(contract_id) +
                    " in the 'local_rates' map.")

            try:
                rate_script_start = rate_script['start_date']
            except KeyError:
                raise BadRequest(
                    "Problem in the scenario properties. Can't find the " +
                    "'start_date' key of the contract " + str(contract_id) +
                    " in the 'local_rates' map.")

            for dt in hh_range(report_context, rate_script_start,
                               rate_script['finish_date']):
                cont_cache[dt] = PropDict('scenario properties',
                                          rate_script['script'])

        for rate_script in get_map_list(scenario_props, 'industry_rates'):
            contract_name = rate_script['contract_name']
            try:
                cont_cache = ind_cont[contract_name]
            except KeyError:
                cont_cache = ind_cont[contract_name] = {}

            rfinish = rate_script['finish_date']
            if rfinish is None:
                raise BadRequest("For the industry rate " + contract_name +
                                 " the "
                                 "finish_date can't be null.")
            for dt in hh_range(report_context, rate_script['start_date'],
                               rfinish):
                cont_cache[dt] = PropDict('scenario properties',
                                          rate_script['script'])

        era_maps = scenario_props.get('era_maps', {})

        scenario_hh = scenario_props.get('hh_data', {})

        era_header_titles = [
            'creation-date', 'imp-mpan-core', 'imp-supplier-contract',
            'exp-mpan-core', 'exp-supplier-contract', 'metering-type',
            'source', 'generator-type', 'supply-name', 'msn', 'pc', 'site-id',
            'site-name', 'associated-site-ids', 'month'
        ]
        site_header_titles = [
            'creation-date', 'site-id', 'site-name', 'associated-site-ids',
            'month', 'metering-type', 'sources', 'generator-types'
        ]
        summary_titles = [
            'import-net-kwh', 'export-net-kwh', 'import-gen-kwh',
            'export-gen-kwh', 'import-3rd-party-kwh', 'export-3rd-party-kwh',
            'displaced-kwh', 'used-kwh', 'used-3rd-party-kwh',
            'import-net-gbp', 'export-net-gbp', 'import-gen-gbp',
            'export-gen-gbp', 'import-3rd-party-gbp', 'export-3rd-party-gbp',
            'displaced-gbp', 'used-gbp', 'used-3rd-party-gbp',
            'billed-import-net-kwh', 'billed-import-net-gbp'
        ]

        title_dict = {}
        for cont_type, con_attr in (('mop', Era.mop_contract),
                                    ('dc', Era.dc_contract),
                                    ('imp-supplier',
                                     Era.imp_supplier_contract),
                                    ('exp-supplier',
                                     Era.exp_supplier_contract)):
            titles = []
            title_dict[cont_type] = titles
            conts = sess.query(Contract).join(con_attr).join(Era.supply). \
                join(Source).filter(
                    Era.start_date <= finish_date, or_(
                        Era.finish_date == null(),
                        Era.finish_date >= start_date),
                    Source.code.in_(('net', '3rd-party'))
                ).distinct().order_by(Contract.id)
            if supply_id is not None:
                conts = conts.filter(Era.supply_id == supply_id)
            for cont in conts:
                title_func = chellow.computer.contract_func(
                    report_context, cont, 'virtual_bill_titles')
                if title_func is None:
                    raise Exception("For the contract " + cont.name +
                                    " there doesn't seem to be a "
                                    "'virtual_bill_titles' function.")
                for title in title_func():
                    if title not in titles:
                        titles.append(title)

        tpr_query = sess.query(Tpr).join(MeasurementRequirement).join(Ssc). \
            join(Era).filter(
                Era.start_date <= finish_date, or_(
                    Era.finish_date == null(),
                    Era.finish_date >= start_date)
            ).order_by(Tpr.code).distinct()
        for tpr in tpr_query.filter(Era.imp_supplier_contract != null()):
            for suffix in ('-kwh', '-rate', '-gbp'):
                title_dict['imp-supplier'].append(tpr.code + suffix)
        for tpr in tpr_query.filter(Era.exp_supplier_contract != null()):
            for suffix in ('-kwh', '-rate', '-gbp'):
                title_dict['exp-supplier'].append(tpr.code + suffix)

        era_rows.append(
            era_header_titles + summary_titles + [None] +
            ['mop-' + t for t in title_dict['mop']] + [None] +
            ['dc-' + t for t in title_dict['dc']] + [None] +
            ['imp-supplier-' + t for t in title_dict['imp-supplier']] +
            [None] + ['exp-supplier-' + t for t in title_dict['exp-supplier']])
        site_rows.append(site_header_titles + summary_titles)

        sites = sites.all()
        deltas = {}
        for site in sites:
            try:
                site_scenario_hh = scenario_hh[site.code]
            except KeyError:
                site_scenario_hh = scenario_hh[site.code] = {}

            site_deltas = deltas[site.code] = {'hhs': {}}
            delts = site_deltas['supply_deltas'] = {}
            for is_import in (True, False):
                delts[is_import] = {}
                for src in ('gen', 'net', 'gen-net', '3rd-party',
                            '3rd-party-reverse', 'sub'):
                    delts[is_import][src] = {'site': {}}

            earliest_delta = to_utc(Datetime.max)
            latest_delta = to_utc(Datetime.min)

            found_hh = False
            for typ in ('used', 'generated', 'parasitic', 'gen_net'):
                hh_str = site_scenario_hh.get(typ, '')
                hh_data = site_scenario_hh[typ] = {}
                for row in csv.reader(StringIO(hh_str)):
                    cells = [cell.strip() for cell in row]
                    if len(''.join(cells)) == 0:
                        continue

                    if len(cells) != 2:
                        raise BadRequest(
                            "Can't interpret the row " + str(cells) +
                            " it should be of the form 'timestamp, kWh'")

                    date_str, kw_str = cells
                    ts = parse_hh_start(date_str)
                    earliest_delta = min(ts, earliest_delta)
                    latest_delta = max(ts, latest_delta)
                    hh_data[ts] = float(kw_str)
                    found_hh = True

            if not found_hh:
                continue

            scenario_used = site_scenario_hh['used']
            scenario_generated = site_scenario_hh['generated']
            scenario_parasitic = site_scenario_hh['parasitic']
            scenario_gen_net = site_scenario_hh['gen_net']

            month_start = utc_datetime(earliest_delta.year,
                                       earliest_delta.month)
            while month_start <= latest_delta:
                month_finish = month_start + relativedelta(months=1) - HH
                chunk_start = hh_max(month_start, earliest_delta)
                chunk_finish = hh_min(month_finish, latest_delta)
                site_ds = chellow.computer.SiteSource(sess, site, chunk_start,
                                                      chunk_finish,
                                                      forecast_from,
                                                      report_context)
                hh_map = dict((h['start-date'], h) for h in site_ds.hh_data)

                for era in sess.query(Era).join(SiteEra).join(Pc).filter(
                        SiteEra.site == site, SiteEra.is_physical == true(),
                        Era.imp_mpan_core != null(), Pc.code != '00',
                        Era.start_date <= chunk_finish,
                        or_(Era.finish_date == null(),
                            Era.finish_date >= chunk_start),
                        ~Era.channels.any()):

                    if supply_id is not None and era.supply_id != supply_id:
                        continue

                    ss_start = hh_max(era.start_date, chunk_start)
                    ss_finish = hh_min(era.finish_date, chunk_finish)

                    ss = SupplySource(sess, ss_start, ss_finish, forecast_from,
                                      era, True, report_context)

                    for hh in ss.hh_data:
                        sdatum = hh_map[hh['start-date']]
                        sdatum['import-net-kwh'] += hh['msp-kwh']
                        sdatum['used-kwh'] += hh['msp-kwh']

                for era in sess.query(Era).join(SiteEra).join(Pc).join(
                        Supply).join(Source).filter(
                            SiteEra.site == site,
                            SiteEra.is_physical == true(),
                            Era.imp_mpan_core != null(),
                            Era.start_date <= chunk_finish,
                            or_(Era.finish_date == null(),
                                Era.finish_date >= chunk_start),
                            Source.code == 'gen-net'):

                    if supply_id is not None and era.supply_id != supply_id:
                        continue

                    ss_start = hh_max(era.start_date, chunk_start)
                    ss_finish = hh_min(era.finish_date, chunk_finish)

                    ss = SupplySource(sess, ss_start, ss_finish, forecast_from,
                                      era, False, report_context)

                    for hh in ss.hh_data:
                        sdatum = hh_map[hh['start-date']]
                        try:
                            sdatum['gen-net-kwh'] += hh['msp-kwh']
                        except KeyError:
                            sdatum['gen-net-kwh'] = hh['msp-kwh']

                for hh in site_ds.hh_data:
                    hh_start = hh['start-date']
                    if hh_start in scenario_used:
                        used_delt = scenario_used[hh_start] - hh['used-kwh']
                        imp_net_delt = 0
                        exp_net_delt = 0

                        if used_delt < 0:
                            diff = hh['import-net-kwh'] + used_delt
                            if diff < 0:
                                imp_net_delt -= hh['import-net-kwh']
                                exp_net_delt -= diff
                            else:
                                imp_net_delt += used_delt
                        else:
                            diff = hh['export-net-kwh'] - used_delt
                            if diff < 0:
                                exp_net_delt -= hh['export-net-kwh']
                                imp_net_delt -= diff
                            else:
                                exp_net_delt -= used_delt

                        try:
                            delts[False]['net']['site'][hh_start] += \
                                exp_net_delt
                        except KeyError:
                            delts[False]['net']['site'][hh_start] = \
                                exp_net_delt

                        try:
                            delts[True]['net']['site'][hh_start] += \
                                imp_net_delt
                        except KeyError:
                            delts[True]['net']['site'][hh_start] = imp_net_delt

                        hh['import-net-kwh'] += imp_net_delt
                        hh['export-net-kwh'] += exp_net_delt
                        hh['used-kwh'] += used_delt
                        hh['msp-kwh'] -= exp_net_delt

                    if hh_start in scenario_generated:
                        imp_gen_delt = scenario_generated[hh_start] - \
                            hh['import-gen-kwh']
                        imp_net_delt = 0
                        exp_net_delt = 0

                        if imp_gen_delt < 0:
                            diff = hh['export-net-kwh'] + imp_gen_delt
                            if diff < 0:
                                exp_net_delt -= hh['export-net-kwh']
                                imp_net_delt -= diff
                            else:
                                exp_net_delt += imp_gen_delt
                        else:
                            diff = hh['import-net-kwh'] - imp_gen_delt
                            if diff < 0:
                                imp_net_delt -= hh['import-net-kwh']
                                exp_net_delt -= diff
                            else:
                                imp_net_delt -= imp_gen_delt

                        try:
                            delts[True]['gen']['site'][hh_start] += \
                                imp_gen_delt
                        except KeyError:
                            delts[True]['gen']['site'][hh_start] = imp_gen_delt

                        try:
                            delts[False]['net']['site'][hh_start] += \
                                exp_net_delt
                        except KeyError:
                            delts[False]['net']['site'][hh_start] = \
                                exp_net_delt

                        try:
                            delts[True]['net']['site'][hh_start] += \
                                imp_net_delt
                        except KeyError:
                            delts[True]['net']['site'][hh_start] = imp_net_delt

                        hh['import-net-kwh'] += imp_net_delt
                        hh['export-net-kwh'] += exp_net_delt
                        hh['import-gen-kwh'] += imp_gen_delt
                        hh['msp-kwh'] -= imp_net_delt

                    if hh_start in scenario_parasitic:
                        exp_gen_delt = scenario_parasitic[hh_start] - \
                            hh['export-gen-kwh']
                        imp_net_delt = 0
                        exp_net_delt = 0

                        if exp_gen_delt < 0:
                            diff = hh['import-net-kwh'] + exp_gen_delt
                            if diff < 0:
                                imp_net_delt -= hh['import-net-kwh']
                                exp_net_delt -= diff
                            else:
                                imp_net_delt += exp_gen_delt
                        else:
                            diff = hh['export-net-kwh'] - exp_gen_delt
                            if diff < 0:
                                exp_net_delt -= hh['export-net-kwh']
                                imp_net_delt -= diff
                            else:
                                exp_net_delt -= exp_gen_delt

                        try:
                            delts[False]['gen']['site'][hh_start] += \
                                imp_gen_delt
                        except KeyError:
                            delts[False]['gen']['site'][hh_start] = \
                                exp_gen_delt

                        try:
                            delts[False]['net']['site'][hh_start] += \
                                exp_net_delt
                        except KeyError:
                            delts[False]['net']['site'][hh_start] = \
                                exp_net_delt

                        try:
                            delts[True]['net']['site'][hh_start] += \
                                imp_net_delt
                        except KeyError:
                            delts[True]['net']['site'][hh_start] = imp_net_delt

                        hh['import-net-kwh'] += imp_net_delt
                        hh['export-net-kwh'] += exp_net_delt
                        hh['export-gen-kwh'] += exp_gen_delt
                        hh['msp-kwh'] -= imp_net_delt

                    if hh_start in scenario_gen_net:
                        gen_net_delt = scenario_gen_net[hh_start] - \
                            hh['gen-net-kwh']

                        try:
                            delts[False]['gen-net']['site'][hh_start] += \
                                gen_net_delt
                        except KeyError:
                            delts[False]['gen-net']['site'][hh_start] = \
                                gen_net_delt

                        hh['import-gen-kwh'] += gen_net_delt
                        hh['export-net-kwh'] += gen_net_delt

                    site_deltas['hhs'][hh_start] = hh
                month_start += relativedelta(months=1)

        month_start = start_date
        while month_start < finish_date:
            month_finish = month_start + relativedelta(months=1) - HH
            for site in sites:
                site_category = None
                site_sources = set()
                site_gen_types = set()
                site_month_data = defaultdict(int)
                calcs = []
                for era in sess.query(Era).join(SiteEra).join(Pc).filter(
                        SiteEra.site == site, SiteEra.is_physical == true(),
                        Era.start_date <= month_finish,
                        or_(Era.finish_date == null(),
                            Era.finish_date >= month_start)).options(
                                joinedload(Era.ssc),
                                joinedload(Era.dc_contract),
                                joinedload(Era.mop_contract),
                                joinedload(Era.imp_supplier_contract),
                                joinedload(Era.exp_supplier_contract),
                                joinedload(Era.channels),
                                joinedload(Era.imp_llfc).joinedload(
                                    Llfc.voltage_level),
                                joinedload(Era.exp_llfc).joinedload(
                                    Llfc.voltage_level), joinedload(Era.cop),
                                joinedload(Era.supply).joinedload(Supply.dno),
                                joinedload(Era.supply).joinedload(
                                    Supply.gsp_group),
                                joinedload(Era.supply).joinedload(
                                    Supply.source),
                                joinedload(Era.mtc).joinedload(Mtc.meter_type),
                                joinedload(Era.pc),
                                joinedload(Era.site_eras)).order_by(Pc.code):

                    supply = era.supply
                    if supply.generator_type is not None:
                        site_gen_types.add(supply.generator_type.code)

                    if supply_id is not None and supply.id != supply_id:
                        continue

                    ss_start = hh_max(era.start_date, month_start)
                    ss_finish = hh_min(era.finish_date, month_finish)

                    if era.imp_mpan_core is None:
                        imp_ss = None
                    else:
                        sup_deltas = site_deltas['supply_deltas'][True][
                            supply.source.code]

                        imp_ss = SupplySource(sess,
                                              ss_start,
                                              ss_finish,
                                              forecast_from,
                                              era,
                                              True,
                                              report_context,
                                              era_maps=era_maps,
                                              deltas=sup_deltas)

                    if era.exp_mpan_core is None:
                        exp_ss = None
                        measurement_type = imp_ss.measurement_type
                    else:
                        sup_deltas = site_deltas['supply_deltas'][False][
                            supply.source.code]

                        exp_ss = SupplySource(sess,
                                              ss_start,
                                              ss_finish,
                                              forecast_from,
                                              era,
                                              False,
                                              report_context,
                                              era_maps=era_maps,
                                              deltas=sup_deltas)
                        measurement_type = exp_ss.measurement_type

                    order = meter_order[measurement_type]
                    calcs.append((order, era.imp_mpan_core, era.exp_mpan_core,
                                  imp_ss, exp_ss))

                # Check if gen deltas haven't been consumed
                extra_sss = set()
                for is_imp in (True, False):
                    sup_deltas = site_deltas['supply_deltas'][is_imp]['gen']
                    if len(
                            list(t for t in sup_deltas['site']
                                 if month_start <= t <= month_finish)) > 0:
                        extra_sss.add(is_imp)

                displaced_era = chellow.computer.displaced_era(
                    sess,
                    report_context,
                    site,
                    month_start,
                    month_finish,
                    forecast_from,
                    has_scenario_generation=len(extra_sss) > 0)

                if len(extra_sss) > 0:
                    if True in extra_sss:
                        sup_deltas = site_deltas['supply_deltas'][True]['gen']
                        imp_ss_name = site.code + "_extra_gen_TRUE"
                        imp_ss = ScenarioSource(
                            sess, month_start, month_finish, True,
                            report_context, sup_deltas,
                            displaced_era.imp_supplier_contract, imp_ss_name)
                    else:
                        imp_ss_name = imp_ss = None
                    if False in extra_sss:
                        exp_ss_name = site.code + "_extra_gen_FALSE"
                        sup_deltas = site_deltas['supply_deltas'][False]['gen']
                        exp_ss = ScenarioSource(
                            sess, month_start, month_finish, False,
                            report_context, sup_deltas,
                            displaced_era.imp_supplier_contract, imp_ss_name)
                    else:
                        exp_ss_name = exp_ss = None

                    calcs.append((0, imp_ss_name, exp_ss_name, imp_ss, exp_ss))

                # Check if exp net deltas haven't been consumed
                sup_deltas = site_deltas['supply_deltas'][False]['net']
                if len(
                        list(t for t in sup_deltas['site']
                             if month_start <= t <= month_finish)) > 0:
                    ss_name = site.code + "_extra_net_export"
                    ss = SupplySource(sess,
                                      month_start,
                                      month_finish,
                                      forecast_from,
                                      displaced_era,
                                      False,
                                      report_context,
                                      era_maps=era_maps,
                                      deltas=sup_deltas)

                    calcs.append((0, None, ss_name, None, ss))

                site_ds = chellow.computer.SiteSource(sess,
                                                      site,
                                                      month_start,
                                                      month_finish,
                                                      forecast_from,
                                                      report_context,
                                                      displaced_era,
                                                      deltas=site_deltas)

                if displaced_era is not None and supply_id is None:
                    month_data = {}
                    for sname in ('import-net', 'export-net', 'import-gen',
                                  'export-gen', 'import-3rd-party',
                                  'export-3rd-party', 'msp', 'used',
                                  'used-3rd-party', 'billed-import-net'):
                        for xname in ('kwh', 'gbp'):
                            month_data[sname + '-' + xname] = 0

                    month_data['used-kwh'] = month_data['displaced-kwh'] = sum(
                        hh['msp-kwh'] for hh in site_ds.hh_data)

                    disp_supplier_contract = \
                        displaced_era.imp_supplier_contract
                    disp_vb_function = chellow.computer.contract_func(
                        report_context, disp_supplier_contract,
                        'displaced_virtual_bill')
                    if disp_vb_function is None:
                        raise BadRequest(
                            "The supplier contract " +
                            disp_supplier_contract.name +
                            " doesn't have the displaced_virtual_bill() "
                            "function.")
                    disp_vb_function(site_ds)
                    disp_supplier_bill = site_ds.supplier_bill

                    try:
                        gbp = disp_supplier_bill['net-gbp']
                    except KeyError:
                        disp_supplier_bill['problem'] += 'For the supply ' + \
                            site_ds.mpan_core + ' the virtual bill ' + \
                            str(disp_supplier_bill) + ' from the contract ' + \
                            disp_supplier_contract.name + \
                            ' does not contain the net-gbp key.'

                    month_data['used-gbp'] = month_data['displaced-gbp'] = \
                        site_ds.supplier_bill['net-gbp']

                    out = [
                        now, None, disp_supplier_contract.name, None, None,
                        displaced_era.meter_category, 'displaced', None, None,
                        None, None, site.code, site.name, '', month_finish
                    ] + [month_data[t] for t in summary_titles
                         ] + [None] + [None] * len(title_dict['mop']) + [
                             None
                         ] + [None] * len(title_dict['dc']) + [
                             None
                         ] + make_bill_row(title_dict['imp-supplier'],
                                           disp_supplier_bill)

                    era_rows.append(out)
                    for k, v in month_data.items():
                        site_month_data[k] += v

                for i, (order, imp_mpan_core, exp_mpan_core, imp_ss,
                        exp_ss) in enumerate(sorted(calcs, key=str)):
                    if imp_ss is None:
                        source_code = exp_ss.source_code
                        supply = exp_ss.supply
                    else:
                        source_code = imp_ss.source_code
                        supply = imp_ss.supply

                    site_sources.add(source_code)
                    month_data = {}
                    for name in ('import-net', 'export-net', 'import-gen',
                                 'export-gen', 'import-3rd-party',
                                 'export-3rd-party', 'displaced', 'used',
                                 'used-3rd-party', 'billed-import-net'):
                        for sname in ('kwh', 'gbp'):
                            month_data[name + '-' + sname] = 0

                    if imp_ss is not None:
                        imp_supplier_contract = imp_ss.supplier_contract
                        if imp_supplier_contract is not None:
                            import_vb_function = contract_func(
                                report_context, imp_supplier_contract,
                                'virtual_bill')
                            if import_vb_function is None:
                                raise BadRequest(
                                    "The supplier contract " +
                                    imp_supplier_contract.name +
                                    " doesn't have the virtual_bill() "
                                    "function.")
                            import_vb_function(imp_ss)

                        kwh = sum(hh['msp-kwh'] for hh in imp_ss.hh_data)
                        imp_supplier_bill = imp_ss.supplier_bill

                        try:
                            gbp = imp_supplier_bill['net-gbp']
                        except KeyError:
                            gbp = 0
                            imp_supplier_bill['problem'] += \
                                'For the supply ' + \
                                imp_ss.mpan_core + \
                                ' the virtual bill ' + \
                                str(imp_supplier_bill) + \
                                ' from the contract ' + \
                                imp_supplier_contract.name + \
                                ' does not contain the net-gbp key.'

                        if source_code in ('net', 'gen-net'):
                            month_data['import-net-gbp'] += gbp
                            month_data['import-net-kwh'] += kwh
                            month_data['used-gbp'] += gbp
                            month_data['used-kwh'] += kwh
                            if source_code == 'gen-net':
                                month_data['export-gen-kwh'] += kwh
                        elif source_code == '3rd-party':
                            month_data['import-3rd-party-gbp'] += gbp
                            month_data['import-3rd-party-kwh'] += kwh
                            month_data['used-3rd-party-gbp'] += gbp
                            month_data['used-3rd-party-kwh'] += kwh
                            month_data['used-gbp'] += gbp
                            month_data['used-kwh'] += kwh
                        elif source_code == '3rd-party-reverse':
                            month_data['export-3rd-party-gbp'] += gbp
                            month_data['export-3rd-party-kwh'] += kwh
                            month_data['used-3rd-party-gbp'] -= gbp
                            month_data['used-3rd-party-kwh'] -= kwh
                            month_data['used-gbp'] -= gbp
                            month_data['used-kwh'] -= kwh
                        elif source_code == 'gen':
                            month_data['import-gen-kwh'] += kwh

                    if exp_ss is not None:
                        exp_supplier_contract = exp_ss.supplier_contract
                        if exp_supplier_contract is not None:
                            export_vb_function = contract_func(
                                report_context, exp_supplier_contract,
                                'virtual_bill')
                            export_vb_function(exp_ss)

                        kwh = sum(hh['msp-kwh'] for hh in exp_ss.hh_data)
                        exp_supplier_bill = exp_ss.supplier_bill
                        try:
                            gbp = exp_supplier_bill['net-gbp']
                        except KeyError:
                            exp_supplier_bill['problem'] += \
                                'For the supply ' + imp_ss.mpan_core + \
                                ' the virtual bill ' + \
                                str(imp_supplier_bill) + \
                                ' from the contract ' + \
                                imp_supplier_contract.name + \
                                ' does not contain the net-gbp key.'

                        if source_code in ('net', 'gen-net'):
                            month_data['export-net-gbp'] += gbp
                            month_data['export-net-kwh'] += kwh
                            if source_code == 'gen-net':
                                month_data['import-gen-kwh'] += kwh

                        elif source_code == '3rd-party':
                            month_data['export-3rd-party-gbp'] += gbp
                            month_data['export-3rd-party-kwh'] += kwh
                            month_data['used-3rd-party-gbp'] -= gbp
                            month_data['used-3rd-party-kwh'] -= kwh
                            month_data['used-gbp'] -= gbp
                            month_data['used-kwh'] -= kwh
                        elif source_code == '3rd-party-reverse':
                            month_data['import-3rd-party-gbp'] += gbp
                            month_data['import-3rd-party-kwh'] += kwh
                            month_data['used-3rd-party-gbp'] += gbp
                            month_data['used-3rd-party-kwh'] += kwh
                            month_data['used-gbp'] += gbp
                            month_data['used-kwh'] += kwh
                        elif source_code == 'gen':
                            month_data['export-gen-kwh'] += kwh

                    sss = exp_ss if imp_ss is None else imp_ss
                    dc_contract = sss.dc_contract
                    if dc_contract is not None:
                        sss.contract_func(dc_contract, 'virtual_bill')(sss)
                    dc_bill = sss.dc_bill
                    gbp = dc_bill['net-gbp']

                    mop_contract = sss.mop_contract
                    if mop_contract is not None:
                        mop_bill_function = sss.contract_func(
                            mop_contract, 'virtual_bill')
                        mop_bill_function(sss)
                    mop_bill = sss.mop_bill
                    gbp += mop_bill['net-gbp']

                    if source_code in ('3rd-party', '3rd-party-reverse'):
                        month_data['import-3rd-party-gbp'] += gbp
                        month_data['used-3rd-party-gbp'] += gbp
                    else:
                        month_data['import-net-gbp'] += gbp
                    month_data['used-gbp'] += gbp

                    generator_type = sss.generator_type_code
                    if source_code in ('gen', 'gen-net'):
                        site_gen_types.add(generator_type)

                    era_category = sss.measurement_type
                    if CATEGORY_ORDER[site_category] < \
                            CATEGORY_ORDER[era_category]:
                        site_category = era_category

                    era_associates = set()
                    if mop_contract is not None:
                        era_associates.update({
                            s.site.code
                            for s in era.site_eras if not s.is_physical
                        })

                        for bill in sess.query(Bill).filter(
                                Bill.supply == supply,
                                Bill.start_date <= sss.finish_date,
                                Bill.finish_date >= sss.start_date):
                            bill_start = bill.start_date
                            bill_finish = bill.finish_date
                            bill_duration = (
                                bill_finish - bill_start).total_seconds() + \
                                (30 * 60)
                            overlap_duration = (
                                min(bill_finish, sss.finish_date) -
                                max(bill_start, sss.start_date)
                            ).total_seconds() + (30 * 60)
                            overlap_proportion = overlap_duration / \
                                bill_duration
                            month_data['billed-import-net-kwh'] += \
                                overlap_proportion * float(bill.kwh)
                            month_data['billed-import-net-gbp'] += \
                                overlap_proportion * float(bill.net)

                    if imp_ss is None:
                        imp_supplier_contract_name = None
                        pc_code = exp_ss.pc_code
                    else:
                        if imp_supplier_contract is None:
                            imp_supplier_contract_name = ''
                        else:
                            imp_supplier_contract_name = \
                                imp_supplier_contract.name
                        pc_code = imp_ss.pc_code

                    if exp_ss is None:
                        exp_supplier_contract_name = None
                    else:
                        if exp_supplier_contract is None:
                            exp_supplier_contract_name = ''
                        else:
                            exp_supplier_contract_name = \
                                exp_supplier_contract.name

                    out = [
                        now, imp_mpan_core, imp_supplier_contract_name,
                        exp_mpan_core, exp_supplier_contract_name,
                        era_category, source_code, generator_type,
                        sss.supply_name, sss.msn, pc_code, site.code,
                        site.name, ','.join(sorted(list(era_associates))),
                        month_finish] + [
                        month_data[t] for t in summary_titles] + [None] + \
                        make_bill_row(title_dict['mop'], mop_bill) + [None] + \
                        make_bill_row(title_dict['dc'], dc_bill)
                    if imp_ss is None:
                        out += [None] * (len(title_dict['imp-supplier']) + 1)
                    else:
                        out += [None] + make_bill_row(
                            title_dict['imp-supplier'], imp_supplier_bill)
                    if exp_ss is not None:
                        out += [None] + make_bill_row(
                            title_dict['exp-supplier'], exp_supplier_bill)

                    for k, v in month_data.items():
                        site_month_data[k] += v
                    era_rows.append(out)

                site_rows.append([
                    now, site.code, site.name, ', '.join(
                        s.code for s in site.find_linked_sites(
                            sess, month_start, month_finish)), month_finish,
                    site_category, ', '.join(sorted(list(site_sources))),
                    ', '.join(sorted(list(site_gen_types)))
                ] + [site_month_data[k] for k in summary_titles])
                sess.rollback()
            write_spreadsheet(rf, compression, site_rows, era_rows)
            month_start += relativedelta(months=1)
    except BadRequest as e:
        msg = e.description + traceback.format_exc()
        sys.stderr.write(msg + '\n')
        site_rows.append(["Problem " + msg])
        write_spreadsheet(rf, compression, site_rows, era_rows)
    except BaseException:
        msg = traceback.format_exc()
        sys.stderr.write(msg + '\n')
        site_rows.append(["Problem " + msg])
        write_spreadsheet(rf, compression, site_rows, era_rows)
    finally:
        if sess is not None:
            sess.close()
        try:
            rf.close()
            os.rename(running_name, finished_name)
        except BaseException:
            msg = traceback.format_exc()
            r_name, f_name = chellow.dloads.make_names('error.txt', user)
            ef = open(r_name, "w")
            ef.write(msg + '\n')
            ef.close()
Exemple #7
0
def create_csv(f, sess, start_date, finish_date, contract_id):
    caches = {}
    writer = csv.writer(f, lineterminator="\n")
    contract = Contract.get_supplier_by_id(sess, contract_id)
    forecast_date = chellow.computer.forecast_date()

    start_date_ct, finish_date_ct = to_ct(start_date), to_ct(finish_date)

    month_pairs = c_months_u(
        start_year=start_date_ct.year,
        start_month=start_date_ct.month,
        finish_year=finish_date_ct.year,
        finish_month=finish_date_ct.month,
    )

    bill_titles = contract_func(caches, contract, "virtual_bill_titles")()

    for tpr in (
        sess.query(Tpr)
        .join(MeasurementRequirement)
        .join(Ssc)
        .join(Era)
        .filter(
            Era.start_date <= finish_date,
            or_(Era.finish_date == null(), Era.finish_date >= start_date),
            or_(
                Era.imp_supplier_contract == contract,
                Era.exp_supplier_contract == contract,
            ),
        )
        .order_by(Tpr.code)
        .distinct()
    ):
        for suffix in ("-kwh", "-rate", "-gbp"):
            bill_titles.append(tpr.code + suffix)
    writer.writerow(
        ["MPAN Core", "Site Code", "Site Name", "Account", "From", "To"] + bill_titles
    )
    vb_func = contract_func(caches, contract, "virtual_bill")

    for month_start, month_finish in month_pairs:
        period_start = hh_max(start_date, month_start)
        period_finish = hh_min(finish_date, month_finish)

        for era in (
            sess.query(Era)
            .filter(
                or_(
                    Era.imp_supplier_contract == contract,
                    Era.exp_supplier_contract == contract,
                ),
                Era.start_date <= period_finish,
                or_(Era.finish_date == null(), Era.finish_date >= period_start),
            )
            .order_by(Era.imp_mpan_core)
        ):
            try:
                vals = _process_era(
                    sess,
                    caches,
                    vb_func,
                    forecast_date,
                    bill_titles,
                    contract,
                    period_start,
                    period_finish,
                    era,
                )
                writer.writerow(csv_make_val(v) for v in vals)
            except BadRequest as e:
                raise BadRequest(
                    f"Problem with {chellow.utils.url_root}eras/{era.id}/edit "
                    f"{e.description}"
                )