Python Session.query方法代碼示例

# 需要導入模塊: from chellow.models import Session [as 別名]
# 或者: from chellow.models.Session import query [as 別名]
def long_process(start_date, finish_date, st_id, months, year, month, user):
    caches = {}
    tmp_file = sess = None
    try:
        sess = Session()
        if st_id is None:
            st = None
            base_name = "site_monthly_duration_for_all_site_for_" + \
                str(months) + "_to_" + str(year) + "_" + str(month) + ".csv"
        else:
            st = Site.get_by_id(sess, st_id)
            base_name = "site_monthly_duration_for_" + st.code + "_" + \
                str(months) + "_to_" + str(year) + "_" + str(month) + ".csv"
        running_name, finished_name = chellow.dloads.make_names(
            base_name, user)
        tmp_file = open(running_name, "w")

        forecast_date = chellow.computer.forecast_date()

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

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

    except:
        msg = traceback.format_exc()
        sys.stderr.write(msg + '\n')
        tmp_file.write("Problem " + msg)
    finally:
        if sess is not None:
            sess.close()
        tmp_file.close()
        os.rename(running_name, finished_name)

# 需要導入模塊: from chellow.models import Session [as 別名]
# 或者: from chellow.models.Session import query [as 別名]
def content(contract_id, days_hidden, user):
    sess = f = writer = None
    try:
        sess = Session()
        running_name, finished_name = chellow.dloads.make_names(
            'channel_snags.csv', user)
        f = open(running_name, mode='w', newline='')
        writer = csv.writer(f, lineterminator='\n')
        writer.writerow(
            (
                'Hidden Days', 'Chellow Id', 'Imp MPAN Core', 'Exp MPAN Core',
                'Site Code', 'Site Name', 'Snag Description',
                'Import Related?', 'Channel Type', 'Start Date', 'Finish Date',
                'Days Since Snag Finished', 'Duration Of Snag (Days)',
                'Is Ignored?'))

        contract = Contract.get_hhdc_by_id(sess, contract_id)

        now = Datetime.now(pytz.utc)
        cutoff_date = now - relativedelta(days=days_hidden)

        for snag, channel, era, supply, site_era, site in sess.query(
                Snag, Channel, Era, Supply, SiteEra, Site).join(
                Channel, Era, Supply, SiteEra, Site).filter(
                SiteEra.is_physical == true(), Era.hhdc_contract == contract,
                Snag.start_date < cutoff_date).order_by(
                Site.code, Supply.id, Channel.imp_related,
                Channel.channel_type, Snag.description,
                Snag.start_date, Snag.id):
            snag_start = snag.start_date
            snag_finish = snag.finish_date
            if snag_finish is None:
                snag_finish_str = ''
                duration = now - snag_start
                age_of_snag = datetime.timedelta(0)
            else:
                snag_finish_str = snag_finish.strftime("%Y-%m-%d %H:%M")
                duration = snag_finish - snag_start
                age_of_snag = now - snag_finish

            writer.writerow(
                (
                    str(days_hidden), str(snag.id),
                    '' if era.imp_mpan_core is None else era.imp_mpan_core,
                    '' if era.exp_mpan_core is None else era.exp_mpan_core,
                    site.code, site.name, snag.description,
                    str(channel.imp_related), channel.channel_type,
                    snag_start.strftime("%Y-%m-%d %H:%M"), snag_finish_str,
                    str(age_of_snag.days + age_of_snag.seconds / (3600 * 24)),
                    str(duration.days + duration.seconds / (3600 * 24)),
                    str(snag.is_ignored)))
    except:
        msg = traceback.format_exc()
        sys.stderr.write(msg)
        writer.writerow([msg])
    finally:
        if sess is not None:
            sess.close()
        if f is not None:
            f.close()
            os.rename(running_name, finished_name)

# 需要導入模塊: from chellow.models import Session [as 別名]
# 或者: from chellow.models.Session import query [as 別名]
def site_content(site_id, start_date, finish_date, user, file_name):
    sess = f = None
    try:
        sess = Session()
        running_name, finished_name = chellow.dloads.make_names(
            file_name, user)
        f = open(running_name, mode='w', newline='')
        writer = csv.writer(f, lineterminator='\n')
        site = Site.get_by_id(sess, site_id)
        sites = sess.query(Site).filter(Site.id == site_id)
        start_date_str = hh_format(start_date)
        finish_date_str = hh_format(finish_date)

        for site in sites:
            writer.writerow(
                [
                    "Site Code", "Site Name", "Associated Site Codes",
                    "Sources", "Generator Types", "From", "To", "Type",
                    "Date"] + list(map(str, range(1, 49))))
            associates = ' '.join(
                s.code for s in site.find_linked_sites(
                    sess, start_date, finish_date))
            source_codes = set()
            gen_types = set()
            for supply in sess.query(Supply).join(Era).join(SiteEra).filter(
                    SiteEra.is_physical == true(), SiteEra.site == site,
                    Era.start_date <= finish_date, or_(
                        Era.finish_date == null(),
                        Era.finish_date >= start_date)).distinct().options(
                            joinedload(Supply.source),
                            joinedload(Supply.generator_type)):
                source_codes.add(supply.source.code)
                gen_type = supply.generator_type
                if gen_type is not None:
                    gen_types.add(gen_type.code)
            source_codes_str = ', '.join(sorted(source_codes))
            gen_types_str = ', '.join(sorted(gen_types))
            vals = None
            for hh in site.hh_data(sess, start_date, finish_date):
                hh_start = hh['start_date']
                if hh_start.hour == 0 and hh_start.minute == 0:
                    if vals is not None:
                        writer.writerow(vals)
                    vals = [
                        site.code, site.name, associates, source_codes_str,
                        gen_types_str, start_date_str, finish_date_str, 'used',
                        hh_start.strftime('%Y-%m-%d')]
                used_gen_kwh = hh['imp_gen'] - hh['exp_net'] - hh['exp_gen']
                used_3p_kwh = hh['imp_3p'] - hh['exp_3p']
                used_kwh = hh['imp_net'] + used_gen_kwh + used_3p_kwh
                vals.append(str(round(used_kwh, 2)))
            if vals is not None:
                writer.writerow(vals)
    except:
        msg = traceback.format_exc()
        sys.stderr.write(msg)
        f.write(msg)
    finally:
        if sess is not None:
            sess.close()
        if f is not None:
            f.close()
            os.rename(running_name, finished_name)

# 需要導入模塊: from chellow.models import Session [as 別名]
# 或者: from chellow.models.Session import query [as 別名]
def content(year, site_id, user):
    caches = {}
    sess = f = writer = None
    try:
        sess = Session()
        running_name, finished_name = chellow.dloads.make_names(
            'output.csv', user)
        f = open(running_name, mode='w', newline='')
        writer = csv.writer(f, lineterminator='\n')
        writer.writerow(
            (
                "Site Code", "Site Name", "Displaced TRIAD 1 Date",
                "Displaced TRIAD 1 MSP kW", "Displaced TRIAD LAF",
                "Displaced TRIAD 1 GSP kW", "Displaced TRIAD 2 Date",
                "Displaced TRIAD 2 MSP kW", "Displaced TRIAD 2 LAF",
                "Displaced TRIAD 2 GSP kW", "Displaced TRIAD 3 Date",
                "Displaced TRIAD 3 MSP kW", "Displaced TRIAD 3 LAF",
                "Displaced TRIAD 3 GSP kW", "Displaced GSP kW",
                "Displaced Rate GBP / kW", "GBP"))

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

        forecast_date = chellow.computer.forecast_date()

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

        for site in sites.order_by(Site.code):
            displaced_era = chellow.computer.displaced_era(
                sess, caches, site, march_start, march_finish, forecast_date)
            if displaced_era is None:
                continue

            site_ds = chellow.computer.SiteSource(
                sess, site, march_start, march_finish, forecast_date, caches,
                displaced_era)
            chellow.duos.duos_vb(site_ds)
            chellow.triad.hh(site_ds)
            chellow.triad.bill(site_ds)

            bill = site_ds.supplier_bill
            for rname, rset in site_ds.supplier_rate_sets.items():
                if len(rset) == 1:
                    bill[rname] = rset.pop()
            values = [site.code, site.name]
            for i in range(1, 4):
                triad_prefix = 'triad-actual-' + str(i)
                values.append(hh_format(bill[triad_prefix + '-date']))
                for suffix in ['-msp-kw', '-laf', '-gsp-kw']:
                    values.append(bill[triad_prefix + suffix])

            values += [
                str(bill['triad-actual-' + suf]) for suf in [
                    'gsp-kw', 'rate', 'gbp']]

            writer.writerow(values)
    except BadRequest as e:
        writer.writerow([e.description])
    except:
        writer.writerow([traceback.format_exc()])
    finally:
        if sess is not None:
            sess.close()
        if f is not None:
            f.close()
            os.rename(running_name, finished_name)

# 需要導入模塊: from chellow.models import Session [as 別名]
# 或者: from chellow.models.Session import query [as 別名]
    def run(self):
        while not self.stopped.isSet():
            if self.lock.acquire(False):
                sess = None
                try:
                    sess = Session()
                    self.log("Starting to check BSUoS rates.")
                    contract = Contract.get_non_core_by_name(sess, 'bsuos')
                    latest_rs = sess.query(RateScript).filter(
                        RateScript.contract == contract).order_by(
                        RateScript.start_date.desc()).first()
                    latest_rs_id = latest_rs.id
                    this_month_start = latest_rs.start_date + \
                        relativedelta(months=1)
                    next_month_start = this_month_start + \
                        relativedelta(months=1)
                    now = Datetime.now(pytz.utc)
                    props = contract.make_properties()
                    if props.get('enabled', False):

                        if now > next_month_start:
                            url = props['url']
                            self.log(
                                "Checking to see if data is available from " +
                                str(this_month_start) + " to " +
                                str(next_month_start - HH) +
                                " at " + url)
                            res = requests.get(url)
                            self.log(
                                "Received " + str(res.status_code) + " " +
                                res.reason)
                            book = xlrd.open_workbook(
                                file_contents=res.content)
                            sheet = book.sheet_by_index(0)

                            ct_tz = pytz.timezone('Europe/London')

                            month_bsuos = {}
                            for row_index in range(1, sheet.nrows):
                                row = sheet.row(row_index)
                                raw_date = Datetime(
                                    *xlrd.xldate_as_tuple(
                                        row[0].value, book.datemode))
                                hh_date_ct = ct_tz.localize(raw_date)
                                hh_date = pytz.utc.normalize(
                                    hh_date_ct.astimezone(pytz.utc))
                                hh_date += relativedelta(
                                    minutes=30*int(row[1].value))
                                if not hh_date < this_month_start and \
                                        hh_date < next_month_start:
                                    month_bsuos[key_format(hh_date)] = \
                                        row[2].value

                            if key_format(next_month_start - HH) in \
                                    month_bsuos:
                                self.log("The whole month's data is there.")
                                script = "def rates_gbp_per_mwh():\n    " \
                                    "return {\n" + ',\n'.join(
                                        "'" + k + "': " + str(month_bsuos[k])
                                        for k in sorted(
                                            month_bsuos.keys())) + "}"
                                set_read_write(sess)
                                contract = Contract.get_non_core_by_name(
                                    sess, 'bsuos')
                                rs = RateScript.get_by_id(sess, latest_rs_id)
                                contract.update_rate_script(
                                    sess, rs, rs.start_date,
                                    rs.start_date + relativedelta(months=2) -
                                    HH, rs.script)
                                sess.flush()
                                contract.insert_rate_script(
                                    sess,
                                    rs.start_date + relativedelta(months=1),
                                    script)
                                sess.commit()
                                self.log("Added new rate script.")
                            else:
                                self.log(
                                    "There isn't a whole month there yet. The "
                                    "last date is " +
                                    sorted(month_bsuos.keys())[-1])
                    else:
                        self.log(
                            "The automatic importer is disabled. To "
                            "enable it, edit the contract properties to "
                            "set 'enabled' to True.")
                except:
                    self.log("Outer problem " + traceback.format_exc())
                    sess.rollback()
                finally:
                    if sess is not None:
                        sess.close()
                    self.lock.release()
                    self.log("Finished checking BSUoS rates.")

            self.going.wait(30 * 60)
            self.going.clear()

# 需要導入模塊: from chellow.models import Session [as 別名]
# 或者: from chellow.models.Session import query [as 別名]
def content(year, month, months, supply_id, user):
    tmp_file = sess = None
    try:
        sess = Session()
        supplies = sess.query(Supply).join(Era).distinct()

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

        tmp_file = open(running_name, "w")

        caches = {}

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

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

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

        forecast_date = chellow.computer.forecast_date()

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

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

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

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

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

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

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

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

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

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

                    if not (is_import and source_code in ('net', 'gen-net')):
                        kwh_sum = sess.query(
                            cast(func.sum(HhDatum.value), Float)
#.........這裏部分代碼省略.........

# 需要導入模塊: from chellow.models import Session [as 別名]
# 或者: from chellow.models.Session import query [as 別名]
def content(
        start_date, finish_date, imp_related, channel_type, is_zipped,
        supply_id, mpan_cores, user):
    zf = sess = tf = None
    base_name = ["supplies_hh_data", finish_date.strftime('%Y%m%d%H%M')]
    try:
        sess = Session()
        supplies = sess.query(Supply).join(Era).filter(
            or_(Era.finish_date == null(), Era.finish_date >= start_date),
            Era.start_date <= finish_date).order_by(Supply.id).distinct()
        if supply_id is not None:
            supply = Supply.get_by_id(sess, supply_id)
            supplies = supplies.filter(Supply.id == supply.id)
            first_era = sess.query(Era).filter(
                Era.supply == supply,
                or_(
                    Era.finish_date == null(), Era.finish_date >= start_date),
                Era.start_date <= finish_date).order_by(Era.start_date).first()
            if first_era.imp_mpan_core is None:
                name_core = first_era.exp_mpan_core
            else:
                name_core = first_era.imp_mpan_core
            base_name.append("supply_" + name_core.replace(' ', '_'))

        if mpan_cores is not None:
            supplies = supplies.filter(
                or_(
                    Era.imp_mpan_core.in_(mpan_cores),
                    Era.exp_mpan_core.in_(mpan_cores)))
            base_name.append('filter')

        outs = []
        titles = "MPAN Core,Date," + ','.join(map(str, range(48)))

        running_name, finished_name = chellow.dloads.make_names(
            '_'.join(base_name) + ('.zip' if is_zipped else '.csv'), user)
        if is_zipped:
            zf = zipfile.ZipFile(running_name, "w", zipfile.ZIP_DEFLATED)
        else:
            tf = open(running_name, "w")
            outs.append(titles)

        for supply in supplies:
            era = supply.find_era_at(sess, finish_date)
            if era is None or era.imp_mpan_core is None:
                mpan_core_str = "NA"
            else:
                mpan_core_str = era.imp_mpan_core

            hh_data = iter(
                sess.query(HhDatum).join(Channel).join(Era).filter(
                    Era.supply == supply, HhDatum.start_date >= start_date,
                    HhDatum.start_date <= finish_date,
                    Channel.imp_related == imp_related,
                    Channel.channel_type == channel_type
                ).order_by(HhDatum.start_date))
            datum = next(hh_data, None)

            for current_date in hh_range(start_date, finish_date):
                if current_date.hour == 0 and current_date.minute == 0:
                    outs.append(
                        "\n" + mpan_core_str + "," +
                        current_date.strftime('%Y-%m-%d'))
                outs.append(",")

                if datum is not None and datum.start_date == current_date:
                    outs.append(str(datum.value))
                    datum = next(hh_data, None)
            if is_zipped:
                fname = mpan_core_str + '_' + str(supply.id) + '.csv'
                zf.writestr(fname.encode('ascii'), titles + ''.join(outs))
            else:
                tf.write(''.join(outs))
            outs = []
        if is_zipped:
            zf.close()
        else:
            tf.close()

    except:
        msg = traceback.format_exc()
        if is_zipped:
            zf.writestr('error.txt', msg)
            zf.close()
        else:
            tf.write(msg)
    finally:
        if sess is not None:
            sess.close()
        os.rename(running_name, finished_name)

# 需要導入模塊: from chellow.models import Session [as 別名]
# 或者: from chellow.models.Session import query [as 別名]
def content(running_name, finished_name, date, supply_id, mpan_cores):
    sess = None
    try:
        sess = Session()
        f = open(running_name, mode='w', newline='')
        writer = csv.writer(f, lineterminator='\n')
        writer.writerow(
            (
                'Date', 'Physical Site Id', 'Physical Site Name',
                'Other Site Ids', 'Other Site Names', 'Supply Id', 'Source',
                'Generator Type', 'GSP Group', 'DNO Name', 'Voltage Level',
                'Metering Type', 'Mandatory HH', 'PC', 'MTC', 'CoP', 'SSC',
                'Number Of Registers', 'MOP Contract', 'Mop Account',
                'HHDC Contract', 'HHDC Account', 'Meter Serial Number',
                'Meter Installation Date', 'Latest Normal Meter Read Date',
                'Latest Normal Meter Read Type', 'Latest DC Bill Date',
                'Latest MOP Bill Date', 'Import ACTIVE?',
                'Import REACTIVE_IMPORT?', 'Import REACTIVE_EXPORT?',
                'Export ACTIVE?', 'Export REACTIVE_IMPORT?',
                'Export REACTIVE_EXPORT?', 'Import MPAN core',
                'Import Agreed Supply Capacity (kVA)', 'Import LLFC Code',
                'Import LLFC Description', 'Import Supplier Contract',
                'Import Supplier Account', 'Import Mandatory kW',
                'Latest Import Supplier Bill Date', 'Export MPAN core',
                'Export Agreed Supply Capacity (kVA)', 'Export LLFC Code',
                'Export LLFC Description', 'Export Supplier Contract',
                'Export Supplier Account', 'Export Mandatory kW',
                'Latest Export Supplier Bill Date'))

        NORMAL_READ_TYPES = ('N', 'C', 'N3')
        year_start = date + HH - relativedelta(years=1)

        eras = sess.query(Era).filter(
            Era.start_date <= date,
            or_(Era.finish_date == null(), Era.finish_date >= date)).order_by(
            Era.supply_id)

        if supply_id is not None:
            supply = Supply.get_by_id(sess, supply_id)

            eras = eras.filter(Era.supply == supply)

        if mpan_cores is not None:
            eras = eras.filter(
                or_(
                    Era.imp_mpan_core.in_(mpan_cores),
                    Era.exp_mpan_core.in_(mpan_cores)))

        for era in eras:
            site_codes = []
            site_names = []
            for site_era in era.site_eras:
                if site_era.is_physical:
                    physical_site = site_era.site
                else:
                    site = site_era.site
                    site_codes.append(site.code)
                    site_names.append(site.name)
            supply = era.supply
            if era.imp_mpan_core is None:
                voltage_level_code = era.exp_llfc.voltage_level.code
            else:
                voltage_level_code = era.imp_llfc.voltage_level.code

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

            metering_type = era.make_meter_category()

            if metering_type == 'nhh':
                latest_prev_normal_read = sess.query(RegisterRead). \
                    join(Bill).join(RegisterRead.previous_type).filter(
                        ReadType.code.in_(NORMAL_READ_TYPES),
                        RegisterRead.previous_date <= date,
                        Bill.supply_id == supply.id).order_by(
                        RegisterRead.previous_date.desc()).first()

                latest_pres_normal_read = sess.query(RegisterRead) \
                    .join(Bill).join(RegisterRead.present_type).filter(
                        ReadType.code.in_(NORMAL_READ_TYPES),
                        RegisterRead.present_date <= date,
                        Bill.supply == supply).order_by(
                        RegisterRead.present_date.desc()).first()

                if latest_prev_normal_read is None and \
                        latest_pres_normal_read is None:
                    latest_normal_read_date = None
                    latest_normal_read_type = None
                elif latest_pres_normal_read is not None and \
                        latest_prev_normal_read is None:
                    latest_normal_read_date = \
                        latest_pres_normal_read.present_date
                    latest_normal_read_type = \
                        latest_pres_normal_read.present_type.code
                elif latest_pres_normal_read is None and \
                        latest_prev_normal_read is not None:
                    latest_normal_read_date = \
                        latest_prev_normal_read.previous_date
#.........這裏部分代碼省略.........

# 需要導入模塊: from chellow.models import Session [as 別名]
# 或者: from chellow.models.Session import query [as 別名]
    def run(self):
        while not self.stopped.isSet():
            if self.lock.acquire(False):
                sess = book = sbp_sheet = ssp_sheet = None
                try:
                    sess = Session()
                    self.log("Starting to check System Prices.")
                    # ct_tz = pytz.timezone('Europe/London')
                    contract = Contract.get_non_core_by_name(
                        sess, 'system_price')
                    contract_props = contract.make_properties()

                    if contract_props.get('enabled', False):
                        for rscript in sess.query(RateScript).filter(
                                RateScript.contract == contract).order_by(
                                RateScript.start_date.desc()):
                            ns = json.loads(rscript.script)
                            rates = ns['gbp_per_nbp_mwh']
                            if len(rates) == 0:
                                fill_start = rscript.start_date
                                break
                            elif rates[
                                    key_format(
                                        rscript.finish_date)]['run'] == 'DF':
                                fill_start = rscript.finish_date + HH
                                break

                        config = Contract.get_non_core_by_name(
                            sess, 'configuration')
                        config_props = config.make_properties()

                        scripting_key = config_props.get(
                            ELEXON_PORTAL_SCRIPTING_KEY_KEY)
                        if scripting_key is None:
                            raise BadRequest(
                                "The property " +
                                ELEXON_PORTAL_SCRIPTING_KEY_KEY +
                                " cannot be found in the configuration "
                                "properties.")
                        url_str = contract_props['url'] + \
                            'file/download/BESTVIEWPRICES_FILE?key=' + \
                            scripting_key

                        self.log(
                            "Downloading from " + url_str +
                            " and extracting data from " +
                            hh_format(fill_start))

                        url = urllib.parse.urlparse(url_str)
                        if url.scheme == 'https':
                            conn = http.client.HTTPSConnection(
                                url.hostname, url.port)
                        else:
                            conn = http.client.HTTPConnection(
                                url.hostname, url.port)
                        conn.request("GET", url.path + '?' + url.query)

                        res = conn.getresponse()
                        self.log(
                            "Received " + str(res.status) + " " + res.reason)
                        data = res.read()
                        book = xlrd.open_workbook(file_contents=data)
                        sbp_sheet = book.sheet_by_index(1)
                        ssp_sheet = book.sheet_by_index(2)

                        sp_months = []
                        sp_month = None
                        for row_index in range(1, sbp_sheet.nrows):
                            sbp_row = sbp_sheet.row(row_index)
                            ssp_row = ssp_sheet.row(row_index)
                            raw_date = datetime.datetime(
                                *xlrd.xldate_as_tuple(
                                    sbp_row[0].value, book.datemode))
                            hh_date_ct = to_ct(raw_date)
                            hh_date = to_utc(hh_date_ct)
                            run_code = sbp_row[1].value
                            for col_idx in range(2, 52):
                                if hh_date >= fill_start:
                                    sbp_val = sbp_row[col_idx].value
                                    if sbp_val != '':
                                        if hh_date.day == 1 and \
                                                hh_date.hour == 0 and \
                                                hh_date.minute == 0:
                                            sp_month = {}
                                            sp_months.append(sp_month)
                                        ssp_val = ssp_row[col_idx].value
                                        sp_month[hh_date] = {
                                            'run': run_code,
                                            'sbp': sbp_val, 'ssp': ssp_val}
                                hh_date += HH
                        self.log("Successfully extracted data.")
                        last_date = sorted(sp_months[-1].keys())[-1]
                        if last_date.month == (last_date + HH).month:
                            del sp_months[-1]
                        if 'limit' in contract_props:
                            sp_months = sp_months[0:1]
                        for sp_month in sp_months:
                            sorted_keys = sorted(sp_month.keys())
                            month_start = sorted_keys[0]
                            month_finish = sorted_keys[-1]
#.........這裏部分代碼省略.........

# 需要導入模塊: from chellow.models import Session [as 別名]
# 或者: from chellow.models.Session import query [as 別名]
def content(supply_id, start_date, finish_date, user):
    caches = {}
    try:
        sess = Session()
        supply = Supply.get_by_id(sess, supply_id)

        forecast_date = chellow.computer.forecast_date()

        prev_titles = None
        running_name, finished_name = chellow.dloads.make_names(
            'supply_virtual_bills_hh_' + str(supply_id) + '.csv', user)
        f = open(running_name, mode='w', newline='')
        w = csv.writer(f, lineterminator='\n')

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                for k in sorted(bill.keys()):
#.........這裏部分代碼省略.........

# 需要導入模塊: from chellow.models import Session [as 別名]
# 或者: from chellow.models.Session import query [as 別名]
    def run(self):
        while not self.stopped.isSet():
            if self.lock.acquire(False):
                sess = None
                try:
                    sess = Session()
                    self.log("Starting to check RCRCs.")
                    contract = Contract.get_non_core_by_name(sess, 'rcrc')
                    latest_rs = sess.query(RateScript).filter(
                        RateScript.contract_id == contract.id).order_by(
                        RateScript.start_date.desc()).first()
                    latest_rs_id = latest_rs.id
                    latest_rs_start = latest_rs.start_date

                    month_start = latest_rs_start + relativedelta(months=1)
                    month_finish = month_start + relativedelta(months=1) - HH
                    now = Datetime.now(pytz.utc)
                    if now > month_finish:
                        self.log(
                            "Checking to see if data is available from " +
                            str(month_start) + " to " + str(month_finish) +
                            " on Elexon Portal.")
                        config = Contract.get_non_core_by_name(
                            sess, 'configuration')
                        props = config.make_properties()

                        scripting_key = props.get(
                            ELEXON_PORTAL_SCRIPTING_KEY_KEY)
                        if scripting_key is None:
                            raise BadRequest(
                                "The property " +
                                ELEXON_PORTAL_SCRIPTING_KEY_KEY +
                                " cannot be found in the configuration "
                                "properties.")

                        contract_props = contract.make_properties()
                        url_str = ''.join(
                            (
                                contract_props['url'],
                                'file/download/RCRC_FILE?key=',
                                scripting_key))

                        r = requests.get(url_str)
                        parser = csv.reader(
                            (l.decode() for l in r.iter_lines()),
                            delimiter=',', quotechar='"')
                        piterator = iter(parser)
                        values = next(piterator)
                        values = next(piterator)
                        month_rcrcs = {}
                        for values in piterator:
                            hh_date = Datetime.strptime(
                                values[0], "%d/%m/%Y").replace(tzinfo=pytz.utc)
                            hh_date += relativedelta(minutes=30*int(values[2]))
                            if month_start <= hh_date <= month_finish:
                                month_rcrcs[key_format(hh_date)] = values[3]

                        if key_format(month_finish) in month_rcrcs:
                            self.log("The whole month's data is there.")
                            script = "def rates():\n    return {\n" + \
                                ',\n'.join(
                                    "'" + k + "': " + month_rcrcs[k] for k in
                                    sorted(month_rcrcs.keys())) + "}"
                            set_read_write(sess)
                            contract = Contract.get_non_core_by_name(
                                sess, 'rcrc')
                            rs = RateScript.get_by_id(sess, latest_rs_id)
                            contract.update_rate_script(
                                sess, rs, rs.start_date, month_finish,
                                rs.script)
                            contract.insert_rate_script(
                                sess, month_start, script)
                            sess.commit()
                            self.log("Added new rate script.")
                        else:
                            msg = "There isn't a whole month there yet."
                            if len(month_rcrcs) > 0:
                                msg += " The last date is " + \
                                    sorted(month_rcrcs.keys())[-1]
                            self.log(msg)
                except:
                    self.log("Outer problem " + traceback.format_exc())
                    sess.rollback()
                finally:
                    self.lock.release()
                    self.log("Finished checking RCRC rates.")
                    if sess is not None:
                        sess.close()

            self.going.wait(30 * 60)
            self.going.clear()

# 需要導入模塊: from chellow.models import Session [as 別名]
# 或者: from chellow.models.Session import query [as 別名]
def content(start_date, finish_date, contract_id, user):
    caches = {}
    sess = None
    try:
        sess = Session()
        running_name, finished_name = chellow.dloads.make_names(
            'mop_virtual_bills.csv', user)
        f = open(running_name, mode='w', newline='')
        writer = csv.writer(f, lineterminator='\n')
        contract = Contract.get_mop_by_id(sess, contract_id)

        forecast_date = chellow.computer.forecast_date()
        header_titles = [
            'Import MPAN Core', 'Export MPAN Core', 'Start Date',
            'Finish Date']

        bill_titles = chellow.computer.contract_func(
            caches, contract, 'virtual_bill_titles')()
        writer.writerow(header_titles + bill_titles)
        vb_func = chellow.computer.contract_func(
            caches, contract, 'virtual_bill')

        for era in sess.query(Era).filter(
                or_(
                    Era.finish_date == null(), Era.finish_date >= start_date),
                Era.start_date <= finish_date, Era.mop_contract == contract). \
                order_by(Era.imp_mpan_core, Era.exp_mpan_core, Era.start_date):
            chunk_start = hh_max(era.start_date, start_date)
            chunk_finish = hh_min(era.finish_date, finish_date)
            import_mpan_core = era.imp_mpan_core
            if import_mpan_core is None:
                import_mpan_core_str = ''
            else:
                is_import = True
                import_mpan_core_str = import_mpan_core

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

            out = [
                import_mpan_core_str, export_mpan_core_str,
                hh_format(chunk_start), hh_format(chunk_finish)]
            supply_source = chellow.computer.SupplySource(
                sess, chunk_start, chunk_finish, forecast_date, era, is_import,
                caches)
            vb_func(supply_source)
            bill = supply_source.mop_bill
            for title in bill_titles:
                if title in bill:
                    out.append(make_val(bill[title]))
                    del bill[title]
                else:
                    out.append('')
            for k in sorted(bill.keys()):
                out.append(k)
                out.append(str(bill[k]))
            writer.writerow(out)
    except:
        msg = traceback.format_exc()
        sys.stderr.write(msg)
        writer.writerow([msg])
    finally:
        if sess is not None:
            sess.close()
        if f is not None:
            f.close()
            os.rename(running_name, finished_name)

# 需要導入模塊: from chellow.models import Session [as 別名]
# 或者: from chellow.models.Session import query [as 別名]
def content(supply_id, file_name, start_date, finish_date, user):
    caches = {}
    sess = None
    try:
        sess = Session()
        running_name, finished_name = chellow.dloads.make_names(
            'supply_virtual_bills_' + str(supply_id) + '.csv', user)
        f = open(running_name, mode='w', newline='')
        writer = csv.writer(f, lineterminator='\n')

        supply = Supply.get_by_id(sess, supply_id)

        forecast_date = chellow.computer.forecast_date()

        prev_titles = None

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

            chunk_start = hh_max(era.start_date, start_date)
            chunk_finish = hh_min(era.finish_date, finish_date)
            site = sess.query(Site).join(SiteEra).filter(
                SiteEra.era == era, SiteEra.is_physical == true()).one()

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

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

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

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

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

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

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

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

            tpr_query = sess.query(Tpr).join(MeasurementRequirement). \
                join(Ssc).join(Era).filter(
                    Era.start_date <= chunk_finish, or_(
                        Era.finish_date == null(),
                        Era.finish_date >= chunk_start)
                ).order_by(Tpr.code).distinct()

            if era.imp_supplier_contract is not None:
                output_line.append('')
                supplier_titles = ds.contract_func(
                    era.imp_supplier_contract, 'virtual_bill_titles')()
                for tpr in tpr_query.filter(
                        Era.imp_supplier_contract != null()):
                    for suffix in ('-kwh', '-rate', '-gbp'):
                        supplier_titles.append(tpr.code + suffix)
                titles.append('')
                titles.extend(['imp-supplier-' + t for t in supplier_titles])

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

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

                for k in sorted(bill.keys()):
                    output_line.extend([k, bill[k]])
#.........這裏部分代碼省略.........

# 需要導入模塊: from chellow.models import Session [as 別名]
# 或者: from chellow.models.Session import query [as 別名]
    def run(self):
        sess = None
        try:
            sess = Session()
            self._log(
                "Starting to parse the file with '" + self.parser_name + "'.")

            bill_types = keydefaultdict(
                lambda k: BillType.get_by_code(sess, k))

            tprs = keydefaultdict(
                lambda k: None if k is None else Tpr.get_by_code(sess, k))

            read_types = keydefaultdict(
                lambda k: ReadType.get_by_code(sess, k))

            batch = Batch.get_by_id(sess, self.batch_id)
            contract = batch.contract
            raw_bills = self.parser.make_raw_bills()
            self._log(
                "Successfully parsed the file, and now I'm starting to "
                "insert the raw bills.")
            for self.bill_num, raw_bill in enumerate(raw_bills):
                try:
                    account = raw_bill['account']
                    supply = sess.query(Supply).join(Era).filter(
                        or_(
                            and_(
                                Era.imp_supplier_contract == contract,
                                Era.imp_supplier_account == account),
                            and_(
                                Era.exp_supplier_contract == contract,
                                Era.exp_supplier_account == account),
                            and_(
                                Era.mop_contract == contract,
                                Era.mop_account == account),
                            and_(
                                Era.hhdc_contract == contract,
                                Era.hhdc_account == account))
                        ).distinct().order_by(Supply.id).first()

                    if supply is None:
                        raise BadRequest(
                            "Can't find an era with contract '" +
                            contract.name + "' and account '" + account + "'.")
                    with sess.begin_nested():
                        bill = batch.insert_bill(
                            sess, account, raw_bill['reference'],
                            raw_bill['issue_date'], raw_bill['start_date'],
                            raw_bill['finish_date'], raw_bill['kwh'],
                            raw_bill['net'], raw_bill['vat'],
                            raw_bill['gross'],
                            bill_types[raw_bill['bill_type_code']],
                            raw_bill['breakdown'], supply)
                        for raw_read in raw_bill['reads']:
                            bill.insert_read(
                                sess, tprs[raw_read['tpr_code']],
                                raw_read['coefficient'], raw_read['units'],
                                raw_read['msn'], raw_read['mpan'],
                                raw_read['prev_date'], raw_read['prev_value'],
                                read_types[raw_read['prev_type_code']],
                                raw_read['pres_date'], raw_read['pres_value'],
                                read_types[raw_read['pres_type_code']])
                        self.successful_bills.append(raw_bill)
                except BadRequest as e:
                    raw_bill['error'] = str(e.description)
                    self.failed_bills.append(raw_bill)

            if len(self.failed_bills) == 0:
                sess.commit()
                self._log(
                    "All the bills have been successfully loaded and attached "
                    "to the batch.")
            else:
                sess.rollback()
                self._log(
                    "The import has finished, but there were " +
                    str(len(self.failed_bills)) + " failures, and so the "
                    "whole import has been rolled back.")

        except:
            sess.rollback()
            self._log("I've encountered a problem: " + traceback.format_exc())
        finally:
            if sess is not None:
                sess.close()

# 需要導入模塊: from chellow.models import Session [as 別名]
# 或者: from chellow.models.Session import query [as 別名]
def content(year, supply_id, user):
    caches = {}
    sess = f = writer = None
    try:
        sess = Session()
        running_name, finished_name = chellow.dloads.make_names(
            'supplies_triad.csv', user)
        f = open(running_name, mode='w', newline='')
        writer = csv.writer(f, lineterminator='\n')

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

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

            chellow.duos.duos_vb(supply_source)
            chellow.triad.hh(supply_source)
            chellow.triad.bill(supply_source)
            bill = supply_source.supplier_bill
            for rname, rset in supply_source.supplier_rate_sets.items():
                if len(rset) == 1:
                    bill[rname] = rset.pop()
            values = [supply_source.mpan_core]
            for i in range(1, 4):
                triad_prefix = 'triad-actual-' + str(i)
                for suffix in [
                        '-date', '-msp-kw', '-status', '-laf', '-gsp-kw']:
                    values.append(bill[triad_prefix + suffix])

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

        writer.writerow(
            (
                "Site Code", "Site Name", "Supply Name", "Source",
                "Generator Type", "Import MPAN Core", "Import T1 Date",
                "Import T1 MSP kW", "Import T1 Status", "Import T1 LAF",
                "Import T1 GSP kW", "Import T2 Date", "Import T2 MSP kW",
                "Import T2 Status", "Import T2 LAF", "Import T2 GSP kW",
                "Import T3 Date", "Import T3 MSP kW", "Import T3 Status",
                "Import T3 LAF", "Import T3 GSP kW", "Import GSP kW",
                "Import Rate GBP / kW", "Import GBP", "Export MPAN Core",
                "Export T1 Date", "Export T1 MSP kW", "Export T1 Status",
                "Export T1 LAF", "Export T1 GSP kW", "Export T2 Date",
                "Export T2 MSP kW", "Export T2 Status", "Export T2 LAF",
                "Export T2 GSP kW", "Export T3 Date", "Export T3 MSP kW",
                "Export T3 Status", "Export T3 LAF", "Export T3 GSP kW",
                "Export GSP kW", "Export Rate GBP / kW", "Export GBP"))

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

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

        for era in eras:
            site = sess.query(Site).join(SiteEra).filter(
                SiteEra.is_physical == true(), SiteEra.era == era).one()
            supply = era.supply

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

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

            gen_type = supply.generator_type
            gen_type = '' if gen_type is None else gen_type.code
            vals = []
            for value in [
                    site.code, site.name, supply.name, supply.source.code,
                    gen_type] + triad_csv(imp_supply_source) + \
                    triad_csv(exp_supply_source):
                if isinstance(value, Datetime):
                    vals.append(hh_format(value))
                else:
                    vals.append(str(value))
            writer.writerow(vals)
    except:
        msg = traceback.format_exc()
        sys.stderr.write(msg)
        writer.writerow([msg])
#.........這裏部分代碼省略.........