Python models.Session類代碼示例

def content(start_date, finish_date, site_id, typ, user):
    sess = f = writer = None
    try:
        sess = Session()
        running_name, finished_name = chellow.dloads.make_names(
            "site_hh_data_" + start_date.strftime("%Y%m%d%H%M") + ".csv", user)
        f = open(running_name, mode='w', newline='')
        writer = csv.writer(f, lineterminator='\n')
        writer.writerow(
            ('Site Code', 'Type', 'Date') + tuple(map(str, range(1, 49))))
        site = Site.get_by_id(sess, site_id)
        line = None
        for hh in site.hh_data(sess, start_date, finish_date):
            hh_start = hh['start_date']
            if (hh_start.hour, hh_start.minute) == (0, 0):
                if line is not None:
                    writer.writerow(line)
                line = [site.code, typ, hh_start.strftime("%Y-%m-%d")]
            line.append(str(hh[typ]))
        if line is not None:
            writer.writerow(line)
    except:
        msg = traceback.format_exc()
        sys.stderr.write(msg)
        writer.writerow([msg])
    finally:
        if sess is not None:
            sess.close()
        if f is not None:
            f.close()
            os.rename(running_name, finished_name)

def get_db_id():
    sess = None
    try:
        sess = Session()
        return Contract.get_non_core_by_name(sess, 'bank_holidays').id
    finally:
        if sess is not None:
            sess.close()

def long_process(start_date, finish_date, st_id, months, year, month, user):
    caches = {}
    tmp_file = sess = None
    try:
        sess = Session()
        if st_id is None:
            st = None
            base_name = "site_monthly_duration_for_all_site_for_" + \
                str(months) + "_to_" + str(year) + "_" + str(month) + ".csv"
        else:
            st = Site.get_by_id(sess, st_id)
            base_name = "site_monthly_duration_for_" + st.code + "_" + \
                str(months) + "_to_" + str(year) + "_" + str(month) + ".csv"
        running_name, finished_name = chellow.dloads.make_names(
            base_name, user)
        tmp_file = open(running_name, "w")

        forecast_date = chellow.computer.forecast_date()

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

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

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

    def run(self):
        while not self.stopped.isSet():
            if self.lock.acquire(False):
                sess = None
                try:
                    sess = Session()
                    self.run_inner(sess)
                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 GCv rates.")

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

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

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

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

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

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

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

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

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

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

def content(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
#.........這裏部分代碼省略.........

def content(year, supply_id, user):
    f = sess = None
    try:
        sess = Session()
        fname = ['crc', str(year), str(year + 1)]
        if supply_id is None:
            fname.append('all_supplies')
        else:
            fname.append('supply_' + str(supply_id))
        running_name, finished_name = chellow.dloads.make_names(
            '_'.join(fname) + '.csv', user)
        f = open(running_name, "w")

        ACTUAL_READ_TYPES = ['N', 'N3', 'C', 'X', 'CP']
        f.write(
            ','.join(
                (
                    'Chellow Supply Id', 'MPAN Core', 'Site Id', 'Site Name',
                    'From', 'To', 'NHH Breakdown', 'Actual HH Normal Days',
                    'Actual AMR Normal Days', 'Actual NHH Normal Days',
                    'Actual Unmetered Normal Days', 'Max HH Normal Days',
                    'Max AMR Normal Days', 'Max NHH Normal Days',
                    'Max Unmetered Normal Days', 'Total Actual Normal Days',
                    'Total Max Normal Days', 'Data Type', 'HH kWh', 'AMR kWh',
                    'NHH kWh', 'Unmetered kwh', 'HH Filled kWh',
                    'AMR Filled kWh', 'Total kWh', 'Note')) + '\n')

        year_start = Datetime(year, 4, 1, tzinfo=pytz.utc)
        year_finish = year_start + relativedelta(years=1) - HH

        supplies = sess.query(Supply).join(Era).join(Source).filter(
            Source.code.in_(('net', 'gen-net')), Era.imp_mpan_core != null(),
            Era.start_date <= year_finish, or_(
                Era.finish_date == null(),
                Era.finish_date >= year_start)).distinct().order_by(Supply.id)
        if supply_id is not None:
            supply = Supply.get_by_id(sess, supply_id)
            supplies = supplies.filter(Supply.id == supply.id)

        meter_types = ('hh', 'amr', 'nhh', 'unmetered')

        for supply in supplies:
            total_kwh = dict([(mtype, 0) for mtype in meter_types])
            filled_kwh = dict([(mtype, 0) for mtype in ('hh', 'amr')])
            normal_days = dict([(mtype, 0) for mtype in meter_types])
            max_normal_days = dict([(mtype, 0) for mtype in meter_types])

            breakdown = ''

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

                meter_type = era.make_meter_category()

                era_start = era.start_date
                period_start = era_start \
                    if era_start > year_start else year_start

                era_finish = era.finish_date
                if hh_after(era_finish, year_finish):
                    period_finish = year_finish
                else:
                    period_finish = era_finish

                max_normal_days[meter_type] += (
                    (period_finish - period_start).total_seconds() +
                    60 * 30) / (60 * 60 * 24)

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

                if meter_type == 'nhh':

                    read_list = []
                    read_keys = {}
                    pairs = []

                    prior_pres_reads = iter(
                        sess.query(RegisterRead).join(Bill).join(BillType)
                        .join(RegisterRead.present_type).filter(
                            RegisterRead.units == 0,
                            ReadType.code.in_(ACTUAL_READ_TYPES),
                            Bill.supply == supply,
                            RegisterRead.present_date < period_start,
                            BillType.code != 'W').order_by(
                            RegisterRead.present_date.desc()))
                    prior_prev_reads = iter(
                        sess.query(RegisterRead).join(Bill).join(BillType)
                        .join(RegisterRead.previous_type).filter(
                            RegisterRead.units == 0,
                            ReadType.code.in_(ACTUAL_READ_TYPES),
                            Bill.supply == supply,
                            RegisterRead.previous_date < period_start,
                            BillType.code != 'W').order_by(
                            RegisterRead.previous_date.desc()))
#.........這裏部分代碼省略.........

def content(start_date, finish_date, contract_id, user):
    caches = {}
    sess = None
    try:
        sess = Session()
        running_name, finished_name = chellow.dloads.make_names(
            'mop_virtual_bills.csv', user)
        f = open(running_name, mode='w', newline='')
        writer = csv.writer(f, lineterminator='\n')
        contract = Contract.get_mop_by_id(sess, contract_id)

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

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

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

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

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

def content(supply_id, start_date, finish_date, user):
    forecast_date = datetime.datetime.max.replace(tzinfo=pytz.utc)
    caches = {}
    f = sess = None
    try:
        sess = Session()
        running_name, finished_name = chellow.dloads.make_names(
            'supplies_duration.csv', user)
        f = open(running_name, "w")
        f.write(
            ','.join(
                (
                    "Supply Id", "Supply Name", "Source", "Generator Type",
                    "Site Ids", "Site Names", "From", "To", "PC", "MTC", "CoP",
                    "SSC", "Normal Reads", "Type", "Import LLFC",
                    "Import MPAN Core", "Import Supply Capacity",
                    "Import Supplier", "Import Total MSP kWh",
                    "Import Non-actual MSP kWh", "Import Total GSP kWh",
                    "Import MD / kW", "Import MD Date", "Import MD / kVA",
                    "Import Bad HHs", "Export LLFC", "Export MPAN Core",
                    "Export Supply Capacity", "Export Supplier",
                    "Export Total MSP kWh", "Export Non-actual MSP kWh",
                    "Export GSP kWh", "Export MD / kW", "Export MD Date",
                    "Export MD / kVA", "Export Bad HHs")))

        supplies = sess.query(Supply).join(Era).filter(
            or_(Era.finish_date == null(), Era.finish_date >= start_date),
            Era.start_date <= finish_date).order_by(Supply.id).distinct()

        if supply_id is not None:
            supplies = supplies.filter(
                Supply.id == Supply.get_by_id(sess, supply_id).id)

        for supply in supplies:
            site_codes = ''
            site_names = ''
            eras = supply.find_eras(sess, start_date, finish_date)

            era = eras[-1]
            for site_era in era.site_eras:
                site = site_era.site
                site_codes = site_codes + site.code + ', '
                site_names = site_names + site.name + ', '
            site_codes = site_codes[:-2]
            site_names = site_names[:-2]

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

            ssc = era.ssc
            ssc_code = '' if ssc is None else ssc.code

            prime_reads = set()
            for read, rdate in chain(
                    sess.query(
                        RegisterRead, RegisterRead.previous_date).join(
                        RegisterRead.previous_type).join(Bill).join(
                        BillType).filter(
                    Bill.supply == supply, BillType.code != 'W',
                    RegisterRead.previous_date >= start_date,
                    RegisterRead.previous_date <= finish_date,
                    ReadType.code.in_(NORMAL_READ_TYPES)),

                    sess.query(
                        RegisterRead, RegisterRead.present_date).join(
                        RegisterRead.present_type).join(Bill).join(
                        BillType).filter(
                    Bill.supply == supply, BillType.code != 'W',
                    RegisterRead.present_date >= start_date,
                    RegisterRead.present_date <= finish_date,
                    ReadType.code.in_(NORMAL_READ_TYPES))):
                prime_bill = sess.query(Bill).join(BillType).filter(
                    Bill.supply == supply,
                    Bill.start_date <= read.bill.finish_date,
                    Bill.finish_date >= read.bill.start_date,
                    Bill.reads.any()).order_by(
                    Bill.issue_date.desc(), BillType.code).first()
                if prime_bill.id == read.bill.id:
                    prime_reads.add(
                        str(rdate) + "_" + read.msn)

            supply_type = era.make_meter_category()

            if eras[0].start_date > start_date:
                chunk_start = eras[0].start_date
            else:
                chunk_start = start_date

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

            num_hh = int(
                (chunk_finish - (chunk_start - HH)).total_seconds() /
                (30 * 60))

            f.write(
#.........這裏部分代碼省略.........

    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]
#.........這裏部分代碼省略.........

def site_content(site_id, start_date, finish_date, user, file_name):
    sess = f = None
    try:
        sess = Session()
        running_name, finished_name = chellow.dloads.make_names(
            file_name, user)
        f = open(running_name, mode='w', newline='')
        writer = csv.writer(f, lineterminator='\n')
        site = Site.get_by_id(sess, site_id)
        sites = sess.query(Site).filter(Site.id == site_id)
        start_date_str = hh_format(start_date)
        finish_date_str = hh_format(finish_date)

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

def content(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)
#.........這裏部分代碼省略.........

    def run(self):
        while not self.stopped.isSet():
            if self.lock.acquire(False):
                sess = None
                try:
                    sess = Session()
                    self.log("Starting to check BSUoS rates.")
                    contract = Contract.get_non_core_by_name(sess, 'bsuos')
                    latest_rs = sess.query(RateScript).filter(
                        RateScript.contract == contract).order_by(
                        RateScript.start_date.desc()).first()
                    latest_rs_id = latest_rs.id
                    this_month_start = latest_rs.start_date + \
                        relativedelta(months=1)
                    next_month_start = this_month_start + \
                        relativedelta(months=1)
                    now = Datetime.now(pytz.utc)
                    props = contract.make_properties()
                    if props.get('enabled', False):

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

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

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

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

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

def content(batch_id, bill_id, user):
    caches = {}
    tmp_file = sess = None
    forecast_date = Datetime.max.replace(tzinfo=pytz.utc)
    try:
        sess = Session()
        running_name, finished_name = chellow.dloads.make_names(
            'bill_check.csv', user)
        tmp_file = open(running_name, mode='w', newline='')
        writer = csv.writer(tmp_file, lineterminator='\n')
        if batch_id is not None:
            batch = Batch.get_by_id(sess, batch_id)
            bills = sess.query(Bill).filter(
                Bill.batch_id == batch.id).order_by(Bill.reference)
        elif bill_id is not None:
            bill = Bill.get_by_id(sess, bill_id)
            bills = sess.query(Bill).filter(Bill.id == bill.id)
            batch = bill.batch

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

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

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

        titles = [
            'batch', 'bill-reference', 'bill-type', 'bill-kwh', 'bill-net-gbp',
            'bill-vat-gbp', 'bill-start-date', 'bill-finish-date',
            'bill-mpan-core', 'site-code', 'site-name', 'covered-from',
            'covered-to', 'covered-bills', 'metered-kwh']
        for t in virtual_bill_titles:
            titles.append('covered-' + t)
            titles.append('virtual-' + t)
            if t.endswith('-gbp'):
                titles.append('difference-' + t)

        writer.writerow(titles)

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

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

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

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

            bill_start = bill.start_date
            bill_finish = bill.finish_date

            era = supply.find_era_at(sess, bill.finish_date)
            if era is None:
                raise BadRequest(
                    "Extraordinary! There isn't an era for the bill " +
                    str(bill.id) + ".")

            values = [
                batch.reference, bill.reference, bill.bill_type.code,
                bill.kwh, bill.net, bill.vat, hh_format(bill_start),
                hh_format(bill_finish), era.imp_mpan_core]

            covered_start = bill_start
            covered_finish = bill_finish
            covered_bill_ids = []
            covered_bdown = {'sum-msp-kwh': 0, 'net-gbp': 0, 'vat-gbp': 0}
            covered_primary_bill = None
#.........這裏部分代碼省略.........

from datetime import datetime as Datetime
import pytz
from chellow.models import Session, Contract
import chellow.computer
import chellow.scenario
from chellow.utils import HH

sess = None
try:
    sess = Session()
    ccl_contract_id = Contract.get_non_core_by_name(sess, 'ccl').id
finally:
    if sess is not None:
        sess.close()


create_future_func = chellow.scenario.make_create_future_func_simple(
    'ccl', ['ccl_rate'])


def ccl(data_source):
    rate_set = data_source.supplier_rate_sets['ccl-rate']

    if data_source.supply.find_era_at(
            data_source.sess, data_source.finish_date + HH) is None:
        sup_end = data_source.finish_date
    else:
        sup_end = None

    try:
        cache = data_source.caches['ccl']