Exemplo n.º 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()
Exemplo n.º 2
0
def long_process():
    sess = None
    tmp_file = None
    try:
        sess = db.session()
        if st_id is None:
            st = None
            base_name = "site_monthly_duration_for_all_site_for_" + \
                str(months) + "_to_" + str(year) + "_" + str(month) + ".csv"
        else:
            st = Site.get_by_id(sess, st_id)
            base_name = "site_monthly_duration_for_" + st.code + "_" + \
                str(months) + "_to_" + str(year) + "_" + str(month) + ".csv"
        running_name, finished_name = dloads.make_names(base_name, user)
        tmp_file = open(running_name, "w")

        forecast_date = computer.forecast_date()

        tmp_file.write(
            "Site Id,Site Name,Associated Site Ids,Sources,"
            "Generator Types,Month,Metered Imported kWh,"
            "Metered Displaced kWh,Metered Exported kWh,Metered Used kWh,"
            "Metered Parasitic kWh,Metered Generated kWh,"
            "Metered 3rd Party Import kWh,Metered 3rd Party Export kWh,"
            "Metered Imported GBP,Metered Displaced GBP,Metered Exported GBP,"
            "Metered Used GBP,Metered 3rd Party Import GBP,"
            "Billed Imported kWh,Billed Imported GBP,Metering Type,Problem")

        for i in range(months):
            sites = sess.query(Site).order_by(Site.code)
            if st is not None:
                sites = sites.filter(Site.id == st.id)
            for site in sites:
                month_start = start_date + relativedelta(months=i)
                month_finish = month_start + relativedelta(months=1) - HH
                tmp_file.write(
                    '\r\n' + ','.join(
                        '"' + str(value) + '"' for value in process_site(
                            sess, site, month_start, month_finish,
                            forecast_date, tmp_file)))
                tmp_file.flush()

    except:
        msg = traceback.format_exc()
        sys.stderr.write(msg + '\n')
        tmp_file.write("Problem " + msg)
    finally:
        try:
            if sess is not None:
                sess.close()
        except:
            tmp_file.write("\nProblem closing session.")
        finally:
            tmp_file.close()
            os.rename(running_name, finished_name)
Exemplo n.º 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()
Exemplo n.º 4
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()
Exemplo n.º 5
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()
Exemplo n.º 6
0
def content():
    sess = None
    tmp_file = None
    try:
        sess = db.session()
        supplies = sess.query(Supply).join(Era).distinct()

        if supply_id is None:
            base_name = "supplies_monthly_duration_for_all_supplies_for_" + \
                str(months) + "_to_" + str(year) + "_" + str(month) + ".csv"
        else:
            supply = Supply.get_by_id(sess, supply_id)
            supplies = supplies.filter(Supply.id == supply.id)
            base_name = "supplies_monthly_duration_for_" + str(supply.id) + \
                "_" + str(months) + "_to_" + str(year) + "_" + str(month) + \
                ".csv"
        running_name, finished_name = dloads.make_names(base_name, user)

        tmp_file = open(running_name, "w")

        caches = {}

        start_date = datetime.datetime(year, month, 1, tzinfo=pytz.utc) - \
            relativedelta(months=months-1)

        field_names = (
            'supply-name', 'source-code', 'generator-type', 'month', 'pc-code',
            'msn', 'site-code', 'site-name', 'metering-type',
            'import-mpan-core', 'metered-import-kwh', 'metered-import-net-gbp',
            'metered-import-estimated-kwh', 'billed-import-kwh',
            'billed-import-net-gbp', 'export-mpan-core', 'metered-export-kwh',
            'metered-export-estimated-kwh', 'billed-export-kwh',
            'billed-export-net-gbp', 'problem', 'timestamp')

        tmp_file.write('supply-id,' + ','.join(field_names) + '\n')

        forecast_date = computer.forecast_date()

        for i in range(months):
            month_start = start_date + relativedelta(months=i)
            month_finish = month_start + relativedelta(months=1) - HH

            for supply in supplies.filter(
                    Era.start_date <= month_finish,
                    or_(
                        Era.finish_date == null(),
                        Era.finish_date >= month_start)):

                generator_type = supply.generator_type
                if generator_type is None:
                    generator_type = ''
                else:
                    generator_type = generator_type.code

                source_code = supply.source.code
                eras = supply.find_eras(sess, month_start, month_finish)
                era = eras[-1]
                metering_type = era.make_meter_category()

                site = sess.query(Site).join(SiteEra).filter(
                    SiteEra.era == era, SiteEra.is_physical == true()).one()

                values = {
                    'supply-name': supply.name, 'source-code': source_code,
                    'generator-type': generator_type,
                    'month': hh_format(month_finish), 'pc-code': era.pc.code,
                    'msn': era.msn, 'site-code': site.code,
                    'site-name': site.name, 'metering-type': metering_type,
                    'problem': ''}

                tmp_file.write(str(supply.id) + ',')

                for is_import, pol_name in [
                        (True, 'import'), (False, 'export')]:
                    if is_import:
                        mpan_core = era.imp_mpan_core
                    else:
                        mpan_core = era.exp_mpan_core

                    values[pol_name + '-mpan-core'] = mpan_core
                    kwh = 0
                    est_kwh = 0

                    if metering_type in ['hh', 'amr']:
                        est_kwh = sess.query(HhDatum.value).join(Channel) \
                            .join(Era).filter(
                                HhDatum.status == 'E',
                                Era.supply_id == supply.id,
                                Channel.channel_type == 'ACTIVE',
                                Channel.imp_related == is_import,
                                HhDatum.start_date >= month_start,
                                HhDatum.start_date <= month_finish).first()
                        if est_kwh is None:
                            est_kwh = 0
                        else:
                            est_kwh = est_kwh[0]

                    if not (is_import and source_code in ('net', 'gen-net')):
                        kwh_sum = sess.query(
                            cast(func.sum(HhDatum.value), Float)
                            ).join(Channel).join(Era).filter(
                            Era.supply_id == supply.id,
                            Channel.channel_type == 'ACTIVE',
                            Channel.imp_related == is_import,
                            HhDatum.start_date >= month_start,
                            HhDatum.start_date <= month_finish).one()[0]
                        if kwh_sum is not None:
                            kwh += kwh_sum

                    values['metered-' + pol_name + '-estimated-kwh'] = est_kwh
                    values['metered-' + pol_name + '-kwh'] = kwh
                    values['metered-' + pol_name + '-net-gbp'] = 0
                    values['billed-' + pol_name + '-kwh'] = 0
                    values['billed-' + pol_name + '-net-gbp'] = 0
                    values['billed-' + pol_name + '-apportioned-kwh'] = 0
                    values['billed-' + pol_name + '-apportioned-net-gbp'] = 0
                    values['billed-' + pol_name + '-raw-kwh'] = 0
                    values['billed-' + pol_name + '-raw-net-gbp'] = 0

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

                for era in eras:
                    if era.start_date > month_start:
                        chunk_start = era.start_date
                    else:
                        chunk_start = month_start
                    if hh_after(era.finish_date, month_finish):
                        chunk_finish = month_finish
                    else:
                        chunk_finish = era.finish_date

                    import_mpan_core = era.imp_mpan_core
                    if import_mpan_core is None:
                        continue

                    supplier_contract = era.imp_supplier_contract

                    if source_code in ['net', 'gen-net', '3rd-party']:
                        supply_source = computer.SupplySource(
                            sess, chunk_start, chunk_finish, forecast_date,
                            era, True, None, caches)

                        values['metered-import-kwh'] += sum(
                            datum['msp-kwh']
                            for datum in supply_source.hh_data)

                        import_vb_function = supply_source.contract_func(
                            supplier_contract, 'virtual_bill')
                        if import_vb_function is None:
                            values['problem'] += "Can't find the " \
                                "virtual_bill  function in the supplier " \
                                "contract. "
                        else:
                            import_vb_function(supply_source)
                            values['metered-import-net-gbp'] += \
                                supply_source.supplier_bill['net-gbp']

                        supply_source.contract_func(
                            era.hhdc_contract, 'virtual_bill')(supply_source)
                        values['metered-import-net-gbp'] += \
                            supply_source.dc_bill['net-gbp']

                        mop_func = supply_source.contract_func(
                            era.mop_contract, 'virtual_bill')
                        if mop_func is None:
                            values['problem'] += " MOP virtual_bill " \
                                "function can't be found."
                        else:
                            mop_func(supply_source)
                            mop_bill = supply_source.mop_bill
                            values['metered-import-net-gbp'] += \
                                mop_bill['net-gbp']
                            if len(mop_bill['problem']) > 0:
                                values['problem'] += \
                                    " MOP virtual bill problem: " + \
                                    mop_bill['problem']

                values['timestamp'] = int(time.time() * 1000)
                tmp_file.write(
                    ','.join(
                        '"' + str(values[name]) +
                        '"' for name in field_names) + '\n')
    except:
        tmp_file.write(traceback.format_exc())
    finally:
        if sess is not None:
            sess.close()
        tmp_file.close()
        os.rename(running_name, finished_name)
Exemplo n.º 7
0
def content():
    sess = None
    try:
        sess = db.session()
        supply = Supply.get_by_id(sess, supply_id)

        forecast_date = computer.forecast_date()

        prev_titles = None

        hh_start = start_date

        while not hh_start > finish_date:
            era = (
                sess.query(Era)
                .filter(
                    Era.supply == supply,
                    Era.start_date <= hh_start,
                    or_(Era.finish_date == null(), Era.finish_date >= hh_start),
                )
                .one()
            )

            site = sess.query(Site).join(SiteEra).filter(SiteEra.era == era, SiteEra.is_physical == true()).one()

            ds = computer.SupplySource(sess, hh_start, hh_start, forecast_date, era, True, None, caches)

            titles = ["MPAN Core", "Site Code", "Site Name", "Account", "HH Start", ""]

            output_line = [ds.mpan_core, site.code, site.name, ds.supplier_account, hh_format(ds.start_date), ""]

            mop_titles = ds.contract_func(era.mop_contract, "virtual_bill_titles")()
            titles.extend(["mop-" + t for t in mop_titles])

            ds.contract_func(era.mop_contract, "virtual_bill")(ds)
            bill = ds.mop_bill
            for title in mop_titles:
                output_line.append(bill.get(title, ""))
                if title in bill:
                    del bill[title]
            for k in sorted(bill.keys()):
                output_line.extend([k, bill[k]])

            output_line.append("")
            dc_titles = ds.contract_func(era.hhdc_contract, "virtual_bill_titles")()
            titles.append("")
            titles.extend(["dc-" + t for t in dc_titles])

            ds.contract_func(era.hhdc_contract, "virtual_bill")(ds)
            bill = ds.dc_bill
            for title in dc_titles:
                output_line.append(bill.get(title, ""))
                if title in bill:
                    del bill[title]
            for k in sorted(bill.keys()):
                output_line.extend([k, bill[k]])

            if era.imp_supplier_contract is not None:
                contract = era.imp_supplier_contract
                output_line.append("")
                supplier_titles = ds.contract_func(contract, "virtual_bill_titles")()
                titles.append("")
                titles.extend(["imp-supplier-" + t for t in supplier_titles])

                ds.contract_func(contract, "virtual_bill")(ds)
                bill = ds.supplier_bill
                for title in supplier_titles:
                    output_line.append(bill.get(title, ""))
                    if title in bill:
                        del bill[title]

                for k in sorted(bill.keys()):
                    output_line.extend([k, bill[k]])

            if era.exp_supplier_contract is not None:
                contract = era.exp_supplier_contract
                ds = computer.SupplySource(sess, hh_start, hh_start, forecast_date, era, False, None, caches)
                output_line.append("")
                supplier_titles = ds.contract_func(contract, "virtual_bill_titles")()
                titles.append("")
                titles.extend(["exp-supplier-" + t for t in supplier_titles])

                ds.contract_func(contract, "virtual_bill")(ds)
                bill = ds.supplier_bill
                for title in supplier_titles:
                    output_line.append(bill.get(title, ""))
                    if title in bill:
                        del bill[title]

                for k in sorted(bill.keys()):
                    output_line.extend([k, bill[k]])

            if titles != prev_titles:
                prev_titles = titles
                yield ",".join('"' + str(v) + '"' for v in titles) + "\n"
            yield ",".join('"' + str(v) + '"' for v in output_line) + "\n"

            hh_start += HH
    except:
        yield traceback.format_exc()
    finally:
        if sess is not None:
            sess.close()
Exemplo n.º 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()
Exemplo n.º 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()
Exemplo n.º 10
0
def content():
    sess = None
    try:
        sess = db.session()
        supply = Supply.get_by_id(sess, supply_id)

        forecast_date = computer.forecast_date()

        prev_titles = None

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

        while not month_start > finish_date:
            month_finish = month_start + relativedelta(months=1) - HH
            if month_start > start_date:
                period_start = month_start
            else:
                period_start = start_date

            if month_finish > finish_date:
                period_finish = finish_date
            else:
                period_finish = month_finish

            for era in sess.query(Era).filter(
                    Era.supply_id == supply.id, Era.start_date < period_finish,
                    or_(
                        Era.finish_date == null(),
                        Era.finish_date > period_start
                    )).order_by(Era.start_date):

                chunk_start = era.start_date \
                    if era.start_date > period_start else period_start

                chunk_finish = period_finish \
                    if utils.hh_before(period_finish, era.finish_date) \
                    else era.finish_date

                site = sess.query(Site).join(SiteEra).filter(
                    SiteEra.era == era, SiteEra.is_physical == true()).one()

                ds = computer.SupplySource(
                    sess, chunk_start, chunk_finish, forecast_date, era, True,
                    None, caches)

                titles = [
                    'Imp MPAN Core', 'Exp MPAN Core', 'Site Code', 'Site Name',
                    'Account', 'From', 'To', '']

                output_line = [
                    era.imp_mpan_core, era.exp_mpan_core, site.code,
                    site.name, ds.supplier_account, hh_format(ds.start_date),
                    hh_format(ds.finish_date), '']

                mop_titles = ds.contract_func(
                    era.mop_contract, 'virtual_bill_titles')()
                titles.extend(['mop-' + t for t in mop_titles])

                ds.contract_func(era.mop_contract, 'virtual_bill')(ds)
                bill = ds.mop_bill
                for title in mop_titles:
                    if title in bill:
                        output_line.append(bill[title])
                        del bill[title]
                    else:
                        output_line.append('')

                for k in sorted(bill.keys()):
                    output_line.extend([k, bill[k]])

                output_line.append('')
                dc_titles = ds.contract_func(
                    era.hhdc_contract, 'virtual_bill_titles')()
                titles.append('')
                titles.extend(['dc-' + t for t in dc_titles])

                ds.contract_func(era.hhdc_contract, 'virtual_bill')(ds)
                bill = ds.dc_bill
                for title in dc_titles:
                    output_line.append(bill.get(title, ''))
                    if title in bill:
                        del bill[title]
                for k in sorted(bill.keys()):
                    output_line.extend([k, bill[k]])

                if era.imp_supplier_contract is not None:
                    output_line.append('')
                    imp_supplier_titles = ds.contract_func(
                        era.imp_supplier_contract, 'virtual_bill_titles')()
                    titles.append('')
                    titles.extend(
                        ['imp-supplier-' + t for t in imp_supplier_titles])

                    ds.contract_func(
                        era.imp_supplier_contract, 'virtual_bill')(ds)
                    bill = ds.supplier_bill

                    for title in imp_supplier_titles:
                        if title in bill:
                            output_line.append(bill[title])
                            del bill[title]
                        else:
                            output_line.append('')

                    for k in sorted(bill.keys()):
                        output_line.extend([k, bill[k]])

                if era.exp_supplier_contract is not None:
                    ds = computer.SupplySource(
                        sess, chunk_start, chunk_finish, forecast_date, era,
                        False, None, caches)

                    output_line.append('')
                    exp_supplier_titles = ds.contract_func(
                        era.exp_supplier_contract, 'virtual_bill_titles')()
                    titles.append('')
                    titles.extend(
                        ['exp-supplier-' + t for t in exp_supplier_titles])

                    ds.contract_func(
                        era.exp_supplier_contract, 'virtual_bill')(ds)
                    bill = ds.supplier_bill
                    for title in exp_supplier_titles:
                        output_line.append(bill.get(title, ''))
                        if title in bill:
                            del bill[title]

                    for k in sorted(bill.keys()):
                        output_line.extend([k, bill[k]])

                if titles != prev_titles:
                    prev_titles != titles
                    yield ','.join('"' + str(v) + '"' for v in titles) + '\n'
                for i, val in enumerate(output_line):
                    if isinstance(val, datetime.datetime):
                        output_line[i] = hh_format(val)
                    elif val is None:
                        output_line[i] = ''
                    else:
                        output_line[i] = str(val)
                yield ','.join(
                    '"' + str('' if v is None else v) +
                    '"' for v in output_line) + '\n'

            month_start += relativedelta(months=1)
    except:
        yield traceback.format_exc()
    finally:
        if sess is not None:
            sess.close()
Exemplo n.º 11
0
HH, hh_before = utils.HH, utils.hh_before
Supply, Era = db.Supply, db.Era
render = templater.render
inv, template = globals()['inv'], globals()['template']


sess = None
try:
    sess = db.session()

    supply_id = inv.getLong("supply_id")
    supply = Supply.get_by_id(sess, supply_id)

    start_date = utils.form_date(inv, 'start')
    finish_date = utils.form_date(inv, 'finish')
    forecast_date = computer.forecast_date()

    net_gbp = 0
    caches = {}
    meras = []
    debug = ''

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

    while not month_start > finish_date:
        month_finish = month_start + relativedelta(months=1) - HH

        chunk_start = start_date if start_date > month_start else month_start

        if finish_date < month_finish:
Exemplo n.º 12
0
def content():
    sess = None
    try:
        sess = db.session()

        year_finish = datetime.datetime(year, 4, 1, tzinfo=pytz.utc) - HH

        def triad_csv(supply_source):
            if supply_source is None or \
                    supply_source.mpan_core.startswith('99'):
                return [''] * 19

            duos.duos_vb(supply_source)
            triad.triad_bill(supply_source)

            bill = supply_source.supplier_bill
            values = [supply_source.mpan_core]
            for i in range(1, 4):
                triad_prefix = 'triad-actual-' + str(i)
                for suffix in [
                        '-date', '-msp-kw', '-status', '-laf', '-gsp-kw']:
                    values.append(bill[triad_prefix + suffix])

            suffixes = ['gsp-kw', 'rate', 'gbp']
            values += [bill['triad-actual-' + suf] for suf in suffixes]
            return values

        yield ', '.join(
            (
                "Site Code", "Site Name", "Supply Name", "Source",
                "Generator Type", "Import MPAN Core", "Import T1 Date",
                "Import T1 MSP kW", "Import T1 Status", "Import T1 LAF",
                "Import T1 GSP kW", "Import T2 Date", "Import T2 MSP kW",
                "Import T2 Status", "Import T2 LAF", "Import T2 GSP kW",
                "Import T3 Date", "Import T3 MSP kW", "Import T3 Status",
                "Import T3 LAF", "Import T3 GSP kW", "Import GSP kW",
                "Import Rate GBP / kW", "Import GBP", "Export MPAN Core",
                "Export T1 Date", "Export T1 MSP kW", "Export T1 Status",
                "Export T1 LAF", "Export T1 GSP kW", "Export T2 Date",
                "Export T2 MSP kW", "Export T2 Status", "Export T2 LAF",
                "Export T2 GSP kW", "Export T3 Date", "Export T3 MSP kW",
                "Export T3 Status", "Export T3 LAF", "Export T3 GSP kW",
                "Export GSP kW", "Export Rate GBP / kW", "Export GBP")) + '\n'

        forecast_date = computer.forecast_date()
        eras = sess.query(Era).join(Supply).join(Source).join(Pc).filter(
            Era.start_date <= year_finish,
            or_(Era.finish_date == null(), Era.finish_date >= year_finish),
            Source.code.in_(('net', 'gen-net')),
            Pc.code == '00').order_by(Supply.id)

        if supply_id is not None:
            eras = eras.filter(Supply.id == supply_id)

        for era in eras:
            site = sess.query(Site).join(SiteEra).filter(
                SiteEra.is_physical == true(), SiteEra.era == era).one()
            supply = era.supply
            yield site.code + ',"' + site.name + '","' + supply.name + '",' + \
                supply.source.code

            imp_mpan_core = era.imp_mpan_core
            if imp_mpan_core is None:
                imp_supply_source = None
            else:
                imp_supply_source = computer.SupplySource(
                    sess, year_finish, year_finish, forecast_date, era, True,
                    None, caches)

            exp_mpan_core = era.exp_mpan_core
            if exp_mpan_core is None:
                exp_supply_source = None
            else:
                exp_supply_source = computer.SupplySource(
                    sess, year_finish, year_finish, forecast_date, era, False,
                    None, caches)

            gen_type = supply.generator_type
            gen_type = '' if gen_type is None else gen_type.code
            for value in [gen_type] + triad_csv(imp_supply_source) + \
                    triad_csv(exp_supply_source):
                if isinstance(value, datetime.datetime):
                    yield ',"' + hh_format(value) + '"'
                else:
                    yield ',"' + str(value) + '"'
            yield '\n'
    except:
        yield traceback.format_exc()
    finally:
        if sess is not None:
            sess.close()
Exemplo n.º 13
0
def content():
    sess = None
    try:
        sess = db.session()

        march_finish = datetime.datetime(year, 4, 1, tzinfo=pytz.utc) - HH
        march_start = datetime.datetime(year, 3, 1, tzinfo=pytz.utc)

        yield ", ".join(
            (
                "Site Code",
                "Site Name",
                "Displaced TRIAD 1 Date",
                "Displaced TRIAD 1 MSP kW",
                "Displaced TRIAD LAF",
                "Displaced TRIAD 1 GSP kW",
                "Displaced TRIAD 2 Date",
                "Displaced TRIAD 2 MSP kW",
                "Displaced TRIAD 2 LAF",
                "Displaced TRIAD 2 GSP kW",
                "Displaced TRIAD 3 Date",
                "Displaced TRIAD 3 MSP kW",
                "Displaced TRIAD 3 LAF",
                "Displaced TRIAD 3 GSP kW",
                "Displaced GSP kW",
                "Displaced Rate GBP / kW",
                "GBP",
            )
        ) + "\n"

        forecast_date = computer.forecast_date()

        if site_id is None:
            sites = (
                sess.query(Site)
                .join(SiteEra)
                .join(Era)
                .join(Supply)
                .join(Source)
                .filter(
                    Source.code.in_(("gen", "gen-net")),
                    Era.start_date <= march_finish,
                    or_(Era.finish_date == null(), Era.finish_date >= march_start),
                )
                .distinct()
            )
        else:
            site = Site.get_by_id(sess, site_id)
            sites = sess.query(Site).filter(Site.id == site.id)

        for site in sites:
            for site_group in site.groups(sess, march_start, march_finish, True):
                if site_group.start_date > march_start:
                    chunk_start = site_group.start_date
                else:
                    chunk_start = march_start

                if not site_group.finish_date < march_finish:
                    chunk_finish = march_finish
                else:
                    continue

                yield '"' + site.code + '","' + site.name + '"'

                displaced_era = computer.displaced_era(sess, site_group, chunk_start, chunk_finish)
                if displaced_era is None:
                    continue

                site_ds = computer.SiteSource(
                    sess, site, chunk_start, chunk_finish, forecast_date, None, caches, displaced_era
                )
                duos.duos_vb(site_ds)
                triad.triad_bill(site_ds)

                bill = site_ds.supplier_bill
                values = []
                for i in range(1, 4):
                    triad_prefix = "triad-actual-" + str(i)
                    for suffix in ["-date", "-msp-kw", "-laf", "-gsp-kw"]:
                        values.append(bill[triad_prefix + suffix])

                values += [bill["triad-actual-" + suf] for suf in ["gsp-kw", "rate", "gbp"]]

                for value in values:
                    if isinstance(value, datetime.datetime):
                        yield "," + hh_format(value)
                    else:
                        yield "," + str(value)
                yield "\n"
    except:
        yield traceback.format_exc()
    finally:
        if sess is not None:
            sess.close()