Пример #1
0
def content():
    sess = None
    try:
        sess = db.session()

        contract = Contract.get_mop_by_id(sess, contract_id)

        forecast_date = computer.forecast_date()

        yield 'Import MPAN Core, Export MPAN Core, Start Date, Finish Date'
        bill_titles = computer.contract_func(
            caches, contract, 'virtual_bill_titles', None)()
        for title in bill_titles:
            yield ',' + title
        yield '\n'

        for era in sess.query(Era).filter(
                or_(Era.finish_date == null(), Era.finish_date >= start_date),
                Era.start_date <= finish_date,
                Era.mop_contract_id == contract.id).order_by(Era.supply_id):
            import_mpan_core = era.imp_mpan_core
            if import_mpan_core is None:
                import_mpan_core_str = ''
            else:
                mpan_core = import_mpan_core
                is_import = True
                import_mpan_core_str = mpan_core

            export_mpan_core = era.exp_mpan_core
            if export_mpan_core is None:
                export_mpan_core_str = ''
            else:
                is_import = False
                mpan_core = export_mpan_core
                export_mpan_core_str = mpan_core

            yield import_mpan_core_str + ',' + export_mpan_core_str + ',' + \
                hh_format(start_date) + ',' + hh_format(finish_date) + ','
            supply_source = computer.SupplySource(
                sess, start_date, finish_date, forecast_date, era, is_import,
                None, caches)
            computer.contract_func(
                caches, contract, 'virtual_bill', None)(supply_source)
            bill = supply_source.mop_bill
            for title in bill_titles:
                if title in bill:
                    yield '"' + str(bill[title]) + '",'
                    del bill[title]
                else:
                    yield ','
            for k in sorted(bill.keys()):
                yield ',"' + k + '","' + str(bill[k]) + '"'
            yield '\n'
    except:
        yield traceback.format_exc()
    finally:
        if sess is None:
            sess.close()
Пример #2
0
    def __init__(self, sess, batch_id, file_name, file_size, f):
        threading.Thread.__init__(self)
        global importer_id
        self.importer_id = importer_id
        importer_id += 1

        self.batch_id = batch_id
        if file_size == 0:
            raise UserException("File has zero length")

        contract = None
        parts = file_name.split('.')[::-1]
        for i in range(len(parts)):
            contr = db.Contract.find_by_role_code_name(
                sess, 'Z',
                'bill-parser-' + '.'.join(parts[:i+1][::-1]).lower())
            if contr is not None:
                contract = contr

        if contract is None:
            raise UserException(
                "Can't find a parser for the file '" + file_name +
                "'. The file name needs to have an extension that's one of " +
                "the following: " +
                ','.join(
                    name[0][12:] for name in sess.query(Contract.name).join(
                        MarketRole).filter(
                        MarketRole.code == 'Z',
                        Contract.name.like("bill-parser-%"))) + ".")

        self.contract_name = contract.name
        self.parser = computer.contract_func({}, contract, 'Parser', None)(f)
        self.successful_bills = []
        self.failed_bills = []
        self.log = collections.deque()
        self.bill_num = None
Пример #3
0
def content():
    sess = None
    try:
        sess = db.session()

        contract = Contract.get_hhdc_by_id(sess, contract_id)

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

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

        forecast_date = computer.forecast_date()

        yield 'Import MPAN Core, Export MPAN Core, Start Date, Finish Date'
        bill_titles = computer.contract_func(
            caches, contract, 'virtual_bill_titles', None)()
        for title in bill_titles:
            yield ',' + title
        yield '\n'

        for era in sess.query(Era).distinct().join(Era.hhdc_contract).filter(
                or_(Era.finish_date == null(), Era.finish_date >= start_date),
                Era.start_date <= finish_date,
                Contract.id == contract.id).order_by(Era.supply_id):
            imp_mpan_core = era.imp_mpan_core
            if imp_mpan_core is None:
                imp_mpan_core_str = ''
                is_import = False
            else:
                is_import = True
                imp_mpan_core_str = imp_mpan_core

            exp_mpan_core = era.exp_mpan_core
            exp_mpan_core_str = '' if exp_mpan_core is None else exp_mpan_core

            if era.start_date > start_date:
                chunk_start = era.start_date
            else:
                chunk_start = start_date

            if hh_before(era.finish_date, finish_date):
                chunk_finish = era.finish_date
            else:
                chunk_finish = finish_date

            yield imp_mpan_core_str + ',' + exp_mpan_core_str + ',' + \
                hh_format(chunk_start) + ',' + hh_format(chunk_finish) + ','
            supply_source = computer.SupplySource(
                sess, chunk_start, chunk_finish, forecast_date, era, is_import,
                None, caches)
            supply_source.contract_func(contract, 'virtual_bill')(
                supply_source)
            bill = supply_source.dc_bill
            for title in bill_titles:
                yield '"' + str(bill.get(title, '')) + '",'
                if title in bill:
                    del bill[title]
            keys = bill.keys()
            keys.sort()
            for k in keys:
                yield ',"' + k + '","' + str(bill[k]) + '"'
            yield '\n'

    except:
        yield traceback.format_exc()
    finally:
        if sess is not None:
            sess.close()
Пример #4
0
def process_site(
        sess, site, month_start, month_finish, forecast_date, tmp_file):
    site_code = site.code
    associates = []
    sources = set()
    generator_types = set()
    metering_type = 'no-supply'
    problem = ''
    month_data = {}

    for stream_name in [
            'import-net', 'export-net', 'import-gen', 'export-gen',
            'import-3rd-party', 'export-3rd-party', 'msp', 'used',
            'used-3rd-party']:
        month_data[stream_name + '-kwh'] = 0
        month_data[stream_name + '-gbp'] = 0

    billed_gbp = 0
    billed_kwh = 0

    for group in site.groups(sess, month_start, month_finish, False):
        for cand_site in group.sites:
            cand_site_code = cand_site.code
            if cand_site_code != site_code and \
                    cand_site_code not in associates:
                associates.append(cand_site_code)
        for cand_supply in group.supplies:
            sources.add(cand_supply.source.code)
            if cand_supply.generator_type is not None:
                generator_types.add(cand_supply.generator_type.code)
            for cand_era in cand_supply.find_eras(
                    sess, group.start_date, group.finish_date):
                if metering_type != 'hh':
                    if cand_era.pc.code == '00':
                        metering_type = 'hh'
                    elif metering_type != 'amr':
                        if len(cand_era.channels) > 0:
                            metering_type = 'amr'
                        elif metering_type != 'nhh':
                            if cand_era.mtc.meter_type.code not in [
                                    'UM', 'PH']:
                                metering_type = 'nhh'
                            else:
                                metering_type = 'unmetered'

    for group in site.groups(sess, month_start, month_finish, True):
        if group.start_date > start_date:
            chunk_start = group.start_date
        else:
            chunk_start = start_date

        if group.finish_date > finish_date:
            chunk_finish = finish_date
        else:
            chunk_finish = group.finish_date

        for supply in group.supplies:
            source_code = supply.source.code

            for era in sess.query(Era).filter(
                    Era.supply == supply, Era.start_date <= chunk_finish,
                    or_(
                        Era.finish_date == null(),
                        Era.finish_date >= chunk_start)):
                tmp_file.write(' ')

                # GBP

                if era.start_date > chunk_start:
                    bill_start = era.start_date
                else:
                    bill_start = chunk_start
                if hh_after(era.finish_date, chunk_finish):
                    bill_finish = chunk_finish
                else:
                    bill_finish = era.finish_date

                supply_source = None
                supplier_contract = era.imp_supplier_contract
                if supplier_contract is not None:
                    supply_source = computer.SupplySource(
                        sess, bill_start, bill_finish, forecast_date, era,
                        True, tmp_file, caches)
                    if supply_source.measurement_type not in ['hh', 'amr']:
                        kwh = sum(
                            hh['msp-kwh'] for hh in supply_source.hh_data)
                        if source_code in ('net', 'gen-net'):
                            month_data['import-net-kwh'] += kwh
                        elif source_code in ('3rd-party', '3rd-party-reverse'):
                            month_data['import-3rd-party-kwh'] += kwh

                    import_vb_function = computer.contract_func(
                        caches, supplier_contract, 'virtual_bill', tmp_file)
                    if import_vb_function is None:
                        problem += "Can't find the virtual_bill function in " \
                            "the supplier contract. "
                    else:
                        import_vb_function(supply_source)
                        v_bill = supply_source.supplier_bill

                        if 'problem' in v_bill and len(v_bill['problem']) > 0:
                            problem += 'Supplier Problem: ' + v_bill['problem']

                        try:
                            gbp = v_bill['net-gbp']
                        except KeyError:
                            problem += 'For the supply ' + \
                                supply_source.mpan_core + \
                                ' the virtual bill ' + str(v_bill) + \
                                ' from the contract ' + \
                                supplier_contract.getName() + \
                                ' does not contain the net-gbp key.'
                        if source_code in ('net', 'gen-net'):
                            month_data['import-net-gbp'] += gbp
                        elif source_code in ('3rd-party', '3rd-party-reverse'):
                            month_data['import-3rd-party-gbp'] += gbp

                if supply_source is None:
                    supply_source = computer.SupplySource(
                        sess, bill_start, bill_finish, forecast_date, era,
                        False, tmp_file, caches)
                dc_contract = era.hhdc_contract
                supply_source.contract_func(
                    dc_contract, 'virtual_bill')(supply_source)
                dc_bill = supply_source.dc_bill
                dc_gbp = dc_bill['net-gbp']
                if 'problem' in dc_bill and len(dc_bill['problem']) > 0:
                    problem += 'DC Problem: ' + dc_bill['problem']

                mop_contract = era.mop_contract
                mop_bill_function = supply_source.contract_func(
                    mop_contract, 'virtual_bill')
                mop_bill_function(supply_source)
                mop_bill = supply_source.mop_bill
                mop_gbp = mop_bill['net-gbp']
                if 'problem' in mop_bill and len(mop_bill['problem']) > 0:
                    problem += 'MOP Problem: ' + mop_bill['problem']

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

            for bill in sess.query(Bill).filter(
                    Bill.supply == supply, Bill.start_date <= chunk_finish,
                    Bill.finish_date >= chunk_start):
                bill_start = bill.start_date
                bill_finish = bill.finish_date
                bill_duration = totalseconds(bill_finish - bill_start) + \
                    (30 * 60)
                overlap_duration = totalseconds(
                    min(bill_finish, chunk_finish) -
                    max(bill_start, chunk_start)) + (30 * 60)
                overlap_proportion = float(overlap_duration) / bill_duration
                billed_gbp += overlap_proportion * float(bill.net)
                billed_kwh += overlap_proportion * float(bill.kwh)

        displaced_era = computer.displaced_era(
            sess, group, chunk_start, chunk_finish)
        site_ds = computer.SiteSource(
            sess, site, chunk_start, chunk_finish, forecast_date, tmp_file,
            caches, displaced_era)
        if displaced_era is not None:
            computer.contract_func(
                caches, displaced_era.imp_supplier_contract,
                'displaced_virtual_bill', tmp_file)(site_ds)
            month_data['msp-gbp'] += site_ds.supplier_bill['net-gbp']

        for stream_name in (
                'import-3rd-party', 'export-3rd-party', 'import-net',
                'export-net', 'import-gen', 'export-gen', 'msp'):
            name = stream_name + '-kwh'
            month_data[name] += sum(hh[name] for hh in site_ds.hh_data)

    month_data['used-3rd-party-kwh'] = \
        month_data['import-3rd-party-kwh'] - \
        month_data['export-3rd-party-kwh']
    month_data['used-3rd-party-gbp'] = month_data['import-3rd-party-gbp']
    month_data['used-gbp'] += \
        month_data['import-net-gbp'] + month_data['msp-gbp'] + \
        month_data['used-3rd-party-gbp']

    month_data['used-kwh'] += month_data['msp-kwh'] + \
        month_data['used-3rd-party-kwh'] + month_data['import-net-kwh']

    result = [
        site.code, site.name, ','.join(associates),
        ','.join(sorted(list(sources))),
        '.'.join(sorted(list(generator_types))),
        hh_format(month_finish), month_data['import-net-kwh'],
        month_data['msp-kwh'], month_data['export-net-kwh'],
        month_data['used-kwh'], month_data['export-gen-kwh'],
        month_data['import-gen-kwh'], month_data['import-3rd-party-kwh'],
        month_data['export-3rd-party-kwh'], month_data['import-net-gbp'],
        month_data['msp-gbp'], 0, month_data['used-gbp'],
        month_data['used-3rd-party-gbp'], billed_kwh, billed_gbp,
        metering_type, problem]
    return result
Пример #5
0
def content():
    sess = None
    try:
        sess = db.session()

        start_date = datetime.datetime(
            start_year, start_month, start_day, tzinfo=pytz.utc)
        finish_date = datetime.datetime(
            finish_year, finish_month, finish_day, tzinfo=pytz.utc) + \
            relativedelta(days=1) - HH

        caches = {}
        supply = Supply.get_by_id(sess, supply_id)
        forecast_date = computer.forecast_date()
        day_start = start_date
        prev_bill_titles = []

        while not day_start > finish_date:
            day_finish = day_start + relativedelta(days=1) - HH

            for era in supply.find_eras(sess, day_start, day_finish):
                if era.start_date > day_start:
                    chunk_start = era.start_date
                else:
                    chunk_start = day_start

                if hh_after(era.finish_date, day_finish):
                    chunk_finish = day_finish
                else:
                    chunk_finish = era.finish_date

                ss = computer.SupplySource(
                    sess, chunk_start, chunk_finish, forecast_date, era,
                    is_import, None, caches)

                sup_con = ss.supplier_contract
                bill_titles = computer.contract_func(
                    caches, sup_con, 'virtual_bill_titles', None)()
                if bill_titles != prev_bill_titles:
                    yield ','.join(
                        [
                            'MPAN Core', 'Site Code', 'Site Name', 'Account',
                            'From', 'To'] + bill_titles) + '\n'
                    prev_bill_titles = bill_titles

                site = sess.query(Site).join(SiteEra).filter(
                    SiteEra.era == era, SiteEra.is_physical == true()).one()
                yield ','.join('"' + str(value) + '"' for value in [
                    ss.mpan_core, site.code, site.name, ss.supplier_account,
                    hh_format(ss.start_date), hh_format(ss.finish_date)])

                computer.contract_func(
                    caches, sup_con, 'virtual_bill', None)(ss)
                bill = ss.supplier_bill
                for title in bill_titles:
                    if title in bill:
                        val_raw = bill[title]
                        if isinstance(val_raw, datetime.datetime):
                            val = hh_format(val_raw)
                        else:
                            val = str(val_raw)

                        yield ',"' + val + '"'
                        del bill[title]
                    else:
                        yield ',""'

                for k in sorted(bill.keys()):
                    yield ',"' + k + '","' + str(bill[k]) + '"'
                yield '\n'
            day_start += relativedelta(days=1)
    except:
        yield traceback.format_exc()
    finally:
        if sess is not None:
            sess.close()
Пример #6
0
def content():
    sess = None
    try:
        sess = db.session()

        yield ','.join(
            (
                'Site Code', 'Site Name', 'Associated Site Ids', 'From', 'To',
                'Gen Types', 'CHP kWh', 'LM kWh', 'Turbine kWh', 'PV kWh'))

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

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

        forecast_date = 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 = 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'
            yield ',' + title
        yield '\n'

        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 = 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]))

                    yield ','.join(
                        '"' + value + '"' for value in [
                            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]}))
                    try:
                        res = results.next()
                        hhChannelValue = res.value
                        hhChannelStartDate = res.start_date
                        imp_related = res.imp_related
                        source_code = res.code
                        gen_type = res.gen_type_code
                        hh_date = chunk_start

                        while hh_date <= finish_date:
                            gen_breakdown = {}
                            exported = 0
                            while hhChannelStartDate == hh_date:
                                if not imp_related and source_code in (
                                        'net', 'gen-net'):
                                    exported += hhChannelValue
                                if (imp_related and source_code == 'gen') or \
                                        (not imp_related and
                                            source_code == 'gen-net'):
                                    gen_breakdown[gen_type] = \
                                        gen_breakdown.setdefault(
                                            gen_type, 0) + hhChannelValue

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

                                try:
                                    res = results.next()
                                    source_code = res.code
                                    hhChannelValue = res.value
                                    hhChannelStartDate = res.start_date
                                    imp_related = res.imp_related
                                    gen_type = res.gen_type_code
                                except StopIteration:
                                    hhChannelStartDate = None

                            displaced = sum(gen_breakdown.itervalues()) - \
                                exported
                            added_so_far = 0
                            for key in sorted(gen_breakdown.iterkeys()):
                                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
                    except StopIteration:
                        pass

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

                    site_ds = computer.SiteSource(
                        sess, site, chunk_start, chunk_finish, forecast_date,
                        None, caches, displaced_era)
                    disp_func = 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.datetime):
                                val = hh_format(val)
                            else:
                                val = str(val)
                            yield ',"' + val + '"'
                            del bill[title]
                        else:
                            yield ',""'

                    keys = bill.keys()
                    keys.sort()
                    for k in keys:
                        yield ',"' + k + '","' + str(bill[k]) + '"'
                    yield '\n'

                month_start += relativedelta(months=1)
                month_finish = month_start + relativedelta(months=1) - HH
    except:
        yield traceback.format_exc()
    finally:
        if sess is not None:
            sess.close()
Пример #7
0
            "from pg_class t, pg_class i, pg_index ix, pg_attribute a, "
            "pg_namespace where t.oid = ix.indrelid "
            "and i.oid = ix.indexrelid and a.attrelid = t.oid "
            "and a.attnum = ANY(ix.indkey) and t.relkind = 'r' "
            "and t.relnamespace = pg_namespace.oid "
            "and pg_namespace.nspname = 'public' "
            "group by t.relname, i.relname, pg_namespace.nspname "
            "order by t.relname, i.relname"
        )
        return vals

    sess = None
    try:
        sess = db.session()
        caches = {}

        if inv.getRequest().getMethod() == "POST":
            if inv.hasParameter("run_shutdown"):
                shutdown_contract = Contract.get_non_core_by_name(sess, "shutdown")
                computer.contract_func(caches, shutdown_contract, "on_shut_down", None)(None)
                render(inv, template, make_vals(sess, "Shut down successfully."))
            elif inv.hasParameter("run_startup"):
                shutdown_contract = Contract.get_non_core_by_name(sess, "startup")
                computer.contract_func(caches, shutdown_contract, "on_start_up", None)(None)
                render(inv, template, make_vals(sess, "Started up successfully."))
        else:
            render(inv, template, make_vals(sess))
    finally:
        if sess is not None:
            sess.close()
Пример #8
0
def content():
    try:
        sess = db.session()

        contract = Contract.get_supplier_by_id(sess, contract_id)
        forecast_date = computer.forecast_date()

        month_start = datetime.datetime(
            start_date.year, start_date.month, 1, tzinfo=pytz.utc)

        month_finish = month_start + relativedelta(months=1) - HH

        bill_titles = computer.contract_func(
            caches, contract, 'virtual_bill_titles', None)()
        yield 'MPAN Core,Site Code,Site Name,Account,From,To,' + \
            ','.join(bill_titles) + '\n'

        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:
                    data_source = computer.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()

                    yield ','.join('"' + str(value) + '"' for value in [
                        data_source.mpan_core, site.code, site.name,
                        data_source.supplier_account,
                        hh_format(data_source.start_date),
                        hh_format(data_source.finish_date)])

                    computer.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 = ''
                        yield ',"' + val + '"'

                    for k in sorted(bill.keys()):
                        yield ',"' + k + '","' + str(bill[k]) + '"'
                    yield '\n'

            month_start += relativedelta(months=1)
            month_finish = month_start + relativedelta(months=1) - HH
    except:
        yield traceback.format_exc()
    finally:
        if sess is not None:
            sess.close()
Пример #9
0
def content():
    global scenario_props
    sess = None
    try:
        sess = db.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 UserException(
                    "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 = globals()[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.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 = 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()
        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 = 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(StringIO.StringIO(kw_changes)):
            if len(''.join(row).strip()) == 0:
                continue
            if len(row) != 4:
                raise UserException(
                    "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.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 = 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 cand_supply.find_eras(
                                sess, group.start_date, group.finish_date):
                            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)):

                            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 = computer.displaced_era(
                        sess, group, group.start_date, group.finish_date)
                    site_ds = 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 = computer.contract_func(
                            report_context, disp_supplier_contract,
                            'displaced_virtual_bill', None)
                        if disp_vb_function is None:
                            raise UserException(
                                "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.iteritems():
                            site_month_data[k] += v
                    for i, (
                            order, imp_mpan_core, exp_mpan_core, imp_ss,
                            exp_ss) in enumerate(sorted(calcs)):
                        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 = computer.contract_func(
                                report_context, imp_supplier_contract,
                                'virtual_bill', None)
                            if import_vb_function is None:
                                raise UserException(
                                    "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 = computer.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 = totalseconds(
                                bill_finish - bill_start) + (30 * 60)
                            overlap_duration = totalseconds(
                                min(bill_finish, sss.finish_date) -
                                max(bill_start, sss.start_date)) + (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.iteritems():
                            site_month_data[k] += v
                        sup_tab.writerow(out)

                    sess.rollback()

                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])

            month_start += relativedelta(months=1)
    except:
        msg = traceback.format_exc()
        sys.stderr.write(msg + '\n')
        group_tab.writerow(["Problem " + msg])
    finally:
        try:
            f.close()
            rf.close()
            os.rename(running_name, finished_name)
            if sess is not None:
                sess.close()
        except:
            msg = traceback.format_exc()
            r_name, f_name = dloads.make_names('error.txt', user)
            ef = open(r_name, "wb")
            ef.write(msg + '\n')
            ef.close()
Пример #10
0
            mpan_core = era.imp_mpan_core
            contract = era.imp_supplier_contract
            data_source = computer.SupplySource(
                sess, block_start, block_finish, forecast_date, era, True,
                None, caches)
            headings = [
                'id', 'supplier_contract', 'account', 'start date',
                'finish date']
            data = [
                data_source.id, contract.name, data_source.supplier_account,
                data_source.start_date, data_source.finish_date]
            mera = {'headings': headings, 'data': data, 'skip': False}

            meras.append(mera)
            computer.contract_func(
                caches, contract, 'virtual_bill', None)(data_source)
            bill = data_source.supplier_bill
            net_gbp += bill['net-gbp']

            for title in computer.contract_func(
                    caches, contract, 'virtual_bill_titles', None)():
                if title == 'consumption-info':
                    del bill[title]
                    continue
                headings.append(title)
                data.append(bill[title])
                if title in bill:
                    del bill[title]

            for k in sorted(bill.keys()):
                headings.append(k)
Пример #11
0
def content():
    sess = tmp_file = None
    try:
        sess = db.session()

        running_name, finished_name = dloads.make_names('bill_check.csv', user)
        tmp_file = open(running_name, "w")
        if batch_id is not None:
            batch = Batch.get_by_id(sess, batch_id)
            bills = sess.query(Bill).filter(
                Bill.batch_id == batch.id).order_by(Bill.reference)
        elif bill_id is not None:
            bill = Bill.get_by_id(sess, bill_id)
            bills = sess.query(Bill).filter(Bill.id == bill.id)
            batch = bill.batch

        contract = batch.contract
        market_role_code = contract.market_role.code

        vbf = computer.contract_func(caches, contract, 'virtual_bill', None)
        if vbf is None:
            raise UserException(
                'The contract ' + contract.name +
                " doesn't have a function virtual_bill.")

        virtual_bill_titles_func = computer.contract_func(
            caches, contract, 'virtual_bill_titles', None)
        if virtual_bill_titles_func is None:
            raise UserException(
                'The contract ' + contract.name +
                " doesn't have a function virtual_bill_titles.")
        virtual_bill_titles = virtual_bill_titles_func()

        tmp_file.write(
            ','.join(
                [
                    'batch', 'bill-reference', 'bill-type', 'bill-kwh',
                    'bill-net-gbp', 'bill-vat-gbp', 'bill-start-date',
                    'bill-finish-date', 'bill-mpan-core', 'site-code',
                    'site-name', 'covered-from', 'covered-to',
                    'covered-bills'] +
                [
                    'covered-' + val + ',virtual-' + val + (
                        ',difference-' + val if val.endswith('-gbp') else '')
                    for val in virtual_bill_titles]) + '\n')

        for bill in bills:
            problem = ''
            supply = bill.supply

            read_dict = {}
            for read in bill.reads:
                gen_start = read.present_date.replace(hour=0).replace(minute=0)
                gen_finish = gen_start + relativedelta(days=1) - HH
                msn_match = False
                read_msn = read.msn
                for read_era in supply.find_eras(sess, gen_start, gen_finish):
                    if read_msn == read_era.msn:
                        msn_match = True
                        break

                if not msn_match:
                    problem += "The MSN " + read_msn + \
                        " of the register read " + str(read.id) + \
                        " doesn't match the MSN of the era."

                for dt, type in [
                        (read.present_date, read.present_type),
                        (read.previous_date, read.previous_type)]:
                    key = str(dt) + "-" + read.msn
                    try:
                        if type != read_dict[key]:
                            problem += " Reads taken on " + str(dt) + \
                                " have differing read types."
                    except KeyError:
                        read_dict[key] = type

            bill_start = bill.start_date
            bill_finish = bill.finish_date

            era = supply.find_era_at(sess, bill.finish_date)
            if era is None:
                tmp_file.write(
                    "\n,,,,,,,,,,Extraordinary! There isn't a era for "
                    "this bill!")
                continue

            tmp_file.write(
                ','.join(
                    '"' + str(val) + '"' for val in [
                        batch.reference, bill.reference, bill.bill_type.code,
                        bill.kwh,
                        bill.net, bill.vat, hh_format(bill_start),
                        hh_format(bill_finish), era.imp_mpan_core]) + ",")

            covered_start = bill_start
            covered_finish = bill_finish
            covered_bill_ids = []
            covered_bdown = {'sum-msp-kwh': 0, 'net-gbp': 0, 'vat-gbp': 0}
            covered_primary_bill = None
            enlarged = True

            while enlarged:
                enlarged = False
                for covered_bill in sess.query(Bill).filter(
                        Bill.supply_id == supply.id,
                        Bill.start_date <= covered_finish,
                        Bill.finish_date >= covered_start).order_by(
                        Bill.issue_date.desc(), Bill.start_date):
                    if market_role_code != \
                            covered_bill.batch.contract.market_role.code:
                        continue

                    if covered_primary_bill is None and \
                            len(covered_bill.reads) > 0:
                        covered_primary_bill = covered_bill
                    if covered_bill.start_date < covered_start:
                        covered_start = covered_bill.start_date
                        enlarged = True
                        break
                    if covered_bill.finish_date > covered_finish:
                        covered_finish = covered_bill.finish_date
                        enlarged = True
                        break

            for covered_bill in sess.query(Bill).filter(
                    Bill.supply_id == supply.id,
                    Bill.start_date <= covered_finish,
                    Bill.finish_date >= covered_start).order_by(
                    Bill.issue_date.desc(), Bill.start_date):
                if market_role_code != \
                        covered_bill.batch.contract.market_role.code:
                    continue
                covered_bill_ids.append(covered_bill.id)
                covered_bdown['net-gbp'] += float(covered_bill.net)
                covered_bdown['vat-gbp'] += float(covered_bill.vat)
                covered_bdown['sum-msp-kwh'] += float(covered_bill.kwh)
                if len(covered_bill.breakdown) > 0:
                    covered_rates = collections.defaultdict(set)
                    for k, v in eval(covered_bill.breakdown, {}).iteritems():

                        if k.endswith('rate'):
                            covered_rates[k].add(v)
                        elif k != 'raw-lines':
                            try:
                                covered_bdown[k] += v
                            except KeyError:
                                covered_bdown[k] = v
                            except TypeError, detail:
                                raise UserException(
                                    "For key " + str(k) + " the value " +
                                    str(v) +
                                    " can't be added to the existing value " +
                                    str(covered_bdown[k]) + ". " + str(detail))
                    for k, v in covered_rates.iteritems():
                        covered_bdown[k] = v.pop() if len(v) == 1 else None

            virtual_bill = {}

            for era in sess.query(Era).filter(
                    Era.supply_id == supply.id, Era.imp_mpan_core != null(),
                    Era.start_date <= covered_finish,
                    or_(
                        Era.finish_date == null(),
                        Era.finish_date >= covered_start)).distinct():
                site = sess.query(Site).join(SiteEra).filter(
                    SiteEra.is_physical == true(),
                    SiteEra.era_id == era.id).one()

                if covered_start > era.start_date:
                    chunk_start = covered_start
                else:
                    chunk_start = era.start_date

                if hh_before(covered_finish, era.finish_date):
                    chunk_finish = covered_finish
                else:
                    chunk_finish = era.finish_date

                data_source = computer.SupplySource(
                    sess, chunk_start, chunk_finish, forecast_date, era, True,
                    None, caches, covered_primary_bill)
                vbf(data_source)

                if market_role_code == 'X':
                    vb = data_source.supplier_bill
                elif market_role_code == 'C':
                    vb = data_source.dc_bill
                elif market_role_code == 'M':
                    vb = data_source.mop_bill
                else:
                    raise UserException("Odd market role.")

                for k, v in vb.iteritems():
                    try:
                        virtual_bill[k] += v
                    except KeyError:
                        virtual_bill[k] = v
                    except TypeError, detail:
                        raise UserException(
                            "For key " + str(k) + " and value " + str(v) +
                            ". " + str(detail))

            values = [
                site.code, site.name, hh_format(covered_start),
                hh_format(covered_finish),
                ';'.join(str(id).replace(',', '') for id in covered_bill_ids)]
            for title in virtual_bill_titles:
                try:
                    cov_val = covered_bdown[title]
                    values.append(cov_val)
                    del covered_bdown[title]
                except KeyError:
                    cov_val = None
                    values.append('')

                try:
                    virt_val = virtual_bill[title]
                    if isinstance(virt_val, datetime.datetime):
                        virt_val = hh_format(virt_val)
                    values.append(virt_val)
                    del virtual_bill[title]
                except KeyError:
                    virt_val = None
                    values.append('')

                if title.endswith('-gbp'):
                    if all(isinstance(val, (int, float)) for val in [
                            cov_val, virt_val]):
                        values.append(cov_val - virt_val)
                    else:
                        values.append('')

            for title in sorted(virtual_bill.keys()):
                val = virtual_bill[title]
                if isinstance(val, datetime.datetime):
                    val = hh_format(val)
                values += ['virtual-' + title, val]
                if title in covered_bdown:
                    values += ['covered-' + title, covered_bdown[title]]
                else:
                    values += ['', '']

            tmp_file.write(
                ','.join('"' + str(value) + '"' for value in values) + '\n')