Example #1
0
    def run(self):
        sess = None
        try:
            self._log(
                "Starting to parse the file with '" +
                self.contract_name + "'.")
            sess = db.session()
            batch = Batch.get_by_id(sess, self.batch_id)
            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:
                    db.set_read_write(sess)
                    bill_type = BillType.get_by_code(
                        sess, raw_bill['bill_type_code'])
                    bill = batch.insert_bill(
                        sess, raw_bill['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_type, raw_bill['breakdown'])
                    sess.flush()
                    for raw_read in raw_bill['reads']:
                        tpr_code = raw_read['tpr_code']
                        if tpr_code is None:
                            tpr = None
                        else:
                            tpr = Tpr.get_by_code(sess, tpr_code)

                        prev_type = ReadType.get_by_code(
                            sess, raw_read['prev_type_code'])
                        pres_type = ReadType.get_by_code(
                            sess, raw_read['pres_type_code'])
                        read = bill.insert_read(
                            sess, tpr, raw_read['coefficient'],
                            raw_read['units'], raw_read['msn'],
                            raw_read['mpan'], raw_read['prev_date'],
                            raw_read['prev_value'], prev_type,
                            raw_read['pres_date'], raw_read['pres_value'],
                            pres_type)
                        sess.expunge(read)
                    sess.commit()
                    self.successful_bills.append(raw_bill)
                    sess.expunge(bill)
                except UserException, e:
                    sess.rollback()
                    raw_bill['error'] = str(e)
                    self.failed_bills.append(raw_bill)

            if len(self.failed_bills) == 0:
                self._log(
                    "All the bills have been successfully loaded and attached "
                    "to the batch.")
            else:
                self._log(
                    "The import has finished, but " +
                    str(len(self.failed_bills)) + " bills failed to load.")
Example #2
0
    eras = sess.query(Era).filter(
        Era.supply == supply).order_by(Era.start_date.desc())
    return {
        'supply': supply, 'messages': messages, 'sources': sources,
        'generator_types': generator_types, 'gsp_groups': gsp_groups,
        'eras': eras}

sess = None
try:
    sess = db.session()
    if inv.getRequest().getMethod() == 'GET':
        supply_id = inv.getLong('supply_id')
        supply = Supply.get_by_id(sess, supply_id)
        render(inv, template, make_fields(sess, supply))
    else:
        db.set_read_write(sess)
        supply_id = inv.getLong('supply_id')
        supply = Supply.get_by_id(sess, supply_id)

        if inv.hasParameter("delete"):
            supply.delete(sess)
            sess.commit()
            inv.sendSeeOther("/reports/99/output/")
        elif inv.hasParameter("insert_era"):
            start_date = form_date(inv, 'start')
            supply.insert_era_at(sess, start_date)
            sess.commit()
            inv.sendSeeOther("/reports/7/output/?supply_id=" + str(supply.id))
        else:
            name = inv.getString("name")
            source_id = inv.getLong("source_id")
Example #3
0
    def run(self):
        while not self.stopped.isSet():
            if self.lock.acquire(False):
                sess = None
                try:
                    sess = db.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.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 UserException(
                                "The property " +
                                ELEXON_PORTAL_SCRIPTING_KEY_KEY +
                                " cannot be found in the configuration "
                                "properties.")

                        data = urllib2.urlopen(
                            'https://downloads.elexonportal.co.uk/file/'
                            'download/RCRC_FILE?key=' + scripting_key)
                        parser = csv.reader(data, delimiter=',', quotechar='"')
                        piterator = iter(parser)
                        values = piterator.next()
                        values = piterator.next()
                        month_rcrcs = {}
                        for values in piterator:
                            hh_date = datetime.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())) + "}"
                            db.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:
                            self.log(
                                "There isn't a whole month there yet. The "
                                "last date is " +
                                sorted(month_rcrcs.keys())[-1])
                except:
                    self.log("Outer problem " + traceback.format_exc())
                    if sess is not None:
                        sess.rollback()
                finally:
                    try:
                        if sess is not None:
                            sess.close()
                    finally:
                        self.lock.release()
                        self.log("Finished checking RCRC rates.")

            self.going.wait(30 * 60)
            self.going.clear()
Example #4
0
    def import_now(self):
        if self.lock.acquire(False):
            try:
                found_new = True
                while found_new:
                    found_new = False
                    sess = None
                    ftp = None

                    try:
                        sess = db.session()
                        contract = Contract.get_hhdc_by_id(
                            sess, self.contract_id)
                        properties = contract.make_properties()

                        host_name = properties["hostname"]
                        user_name = properties["username"]
                        password = properties["password"]
                        try:
                            port = properties["port"]
                        except KeyError:
                            port = 21
                        file_type = properties["file_type"]
                        directories = properties['directories']
                        state = contract.make_state()

                        try:
                            last_import_keys = state['last_import_keys']
                        except KeyError:
                            last_import_keys = {}
                            state['last_import_keys'] = last_import_keys

                        sess.rollback()
                        self.log(
                            "Connecting to ftp server at " + host_name +
                            ":" + str(port) + ".")
                        ftp = ftplib.FTP()
                        ftp.connect(host_name, port)
                        ftp.login(user_name, password)
                        home_path = ftp.pwd()

                        file = None

                        for directory in directories:
                            self.log(
                                "Checking the directory '" + directory + "'.")
                            try:
                                last_import_key = last_import_keys[directory]
                            except KeyError:
                                last_import_key = ''
                                last_import_keys[directory] = last_import_key

                            dir_path = home_path + "/" + directory
                            ftp.cwd(dir_path)
                            files = []
                            for fname in ftp.nlst():
                                fpath = dir_path + "/" + fname
                                try:
                                    ftp.cwd(fpath)
                                    continue  # directory
                                except ftplib.error_perm:
                                    pass

                                key = ftp.sendcmd(
                                    "MDTM " + fpath).split()[1] + '_' + fname
                                if key > last_import_key:
                                    files.append((key, fpath))

                            if len(files) > 0:
                                file = sorted(files)[0]
                                last_import_keys[directory] = file[0]
                                break

                        if file is None:
                            self.log("No new files found.")
                            ftp.quit()
                            self.log("Logged out.")
                        else:
                            key, fpath = file
                            self.log(
                                "Attempting to download " + fpath +
                                " with key " + key + ".")
                            f = tempfile.TemporaryFile()
                            ftp.retrbinary("RETR " + fpath, f.write)
                            self.log("File downloaded successfully.")
                            ftp.quit()
                            self.log("Logged out.")

                            self.log("Treating files as type " + file_type)
                            f.seek(0, os.SEEK_END)
                            fsize = f.tell()
                            f.seek(0)
                            self.importer = HhDataImportProcess(
                                self.contract_id, 0, f, fpath + file_type,
                                fsize)

                            self.importer.run()
                            messages = self.importer.messages
                            self.importer = None
                            for message in messages:
                                self.log(message)

                            if len(messages) > 0:
                                raise UserException("Problem loading file.")

                            db.set_read_write(sess)
                            contract = Contract.get_hhdc_by_id(
                                sess, self.contract_id)
                            contract.update_state(state)
                            sess.commit()
                            self.log("Finished loading '" + fpath)
                            found_new = True
                    except UserException, e:
                        self.log("Problem " + str(e))
                        sess.rollback()
                    except Exception:
                        self.log("Unknown Exception " + traceback.format_exc())
                        sess.rollback()
                    finally:
                        try:
                            if sess is not None:
                                sess.close()
                        except:
                            self.log(
                                "Unknown Exception II" +
                                traceback.format_exc())
Example #5
0
    def run(self):
        while not self.stopped.isSet():
            if self.lock.acquire(False):
                sess = None
                try:
                    sess = db.session()
                    self.log("Starting to check bank holidays")
                    contract = Contract.get_non_core_by_id(sess, db_id)
                    contract_props = contract.make_properties()

                    if contract_props.get('enabled', False):
                        url_str = contract_props['url']

                        self.log("Downloading from " + url_str + ".")

                        url = urlparse.urlparse(url_str)
                        if url.scheme == 'https':
                            conn = httplib.HTTPSConnection(
                                url.hostname, url.port)
                        else:
                            conn = httplib.HTTPConnection(
                                url.hostname, url.port)
                        conn.request("GET", url.path)

                        res = conn.getresponse()
                        self.log(
                            "Received " + str(res.status) + " " + res.reason)
                        PREFIX = 'DTSTART;VALUE=DATE:'
                        hols = collections.defaultdict(list)
                        for line in res.read().splitlines():
                            if line.startswith(PREFIX):
                                dt = datetime.datetime.strptime(
                                    line[-8:], "%Y%m%d"). \
                                    replace(tzinfo=pytz.utc)
                                hols[dt.year].append(dt)

                        for year in sorted(hols.keys()):
                            db.set_read_write(sess)
                            year_start = datetime.datetime(
                                year, 1, 1, tzinfo=pytz.utc)
                            year_finish = year_start + \
                                relativedelta(years=1) - HH
                            rs = sess.query(RateScript).filter(
                                RateScript.contract == contract,
                                RateScript.start_date == year_start).first()
                            if rs is None:
                                self.log(
                                    "Adding a new rate script starting at " +
                                    hh_format(year_start) + ".")

                                latest_rs = sess.query(RateScript).filter(
                                    RateScript.contract == contract).\
                                    order_by(RateScript.start_date.desc()). \
                                    first()

                                contract.update_rate_script(
                                    sess, latest_rs, latest_rs.start_date,
                                    year_finish, latest_rs.script)
                                rs = contract.insert_rate_script(
                                    sess, year_start, '')

                            script = {
                                'bank_holidays': [
                                    v.strftime("%Y-%m-%d")
                                    for v in hols[year]]}

                            self.log(
                                "Updating rate script starting at " +
                                hh_format(year_start) + ".")
                            contract.update_rate_script(
                                sess, rs, rs.start_date, rs.finish_date,
                                json.dumps(
                                    script, indent='    ', sort_keys=True))
                            sess.commit()
                    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())
                    if sess is not None:
                        sess.rollback()
                finally:
                    try:
                        if sess is not None:
                            sess.close()
                    finally:
                        self.lock.release()
                        self.log("Finished checking bank holidays.")

            self.going.wait(24 * 60 * 60)
            self.going.clear()
Example #6
0
    def run(self):
        while not self.stopped.isSet():
            if self.lock.acquire(False):
                sess = None
                try:
                    sess = db.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.datetime.now(pytz.utc)
                    if contract.make_properties().get('enabled', False):

                        if now > next_month_start:
                            self.log(
                                "Checking to see if data is available from " +
                                str(this_month_start) + " to " +
                                str(next_month_start - HH) +
                                " on Elexon Portal.")
                            conn = httplib.HTTPConnection(
                                "www2.nationalgrid.com")
                            conn.request(
                                "GET", "/WorkArea/DownloadAsset.aspx?id=32719")

                            res = conn.getresponse()
                            self.log(
                                "Received " + str(res.status) + " " +
                                res.reason)
                            data = res.read()
                            book = xlrd.open_workbook(file_contents=data)
                            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.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())) + "}"
                                db.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())
                    if sess is not None:
                        sess.rollback()
                finally:
                    try:
                        if sess is not None:
                            sess.close()
                    finally:
                        self.lock.release()
                        self.log("Finished checking BSUoS rates.")

            self.going.wait(30 * 60)
            self.going.clear()
Example #7
0
    def run(self):
        while not self.stopped.isSet():
            if self.lock.acquire(False):
                sess = None
                try:
                    sess = db.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 UserException(
                                "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 = urlparse.urlparse(url_str)
                        if url.scheme == 'https':
                            conn = httplib.HTTPSConnection(
                                url.hostname, url.port)
                        else:
                            conn = httplib.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)

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

                        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 = ct_tz.localize(raw_date)
                            hh_date = pytz.utc.normalize(
                                hh_date_ct.astimezone(pytz.utc))
                            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]
                            db.set_read_write(sess)
                            rs = sess.query(RateScript).filter(
                                RateScript.contract == contract,
                                RateScript.start_date == month_start).first()
                            if rs is None:
                                self.log(
                                    "Adding a new rate script starting at " +
                                    hh_format(month_start) + ".")

                                latest_rs = sess.query(RateScript).filter(
                                    RateScript.contract == contract).\
                                    order_by(RateScript.start_date.desc()). \
                                    first()

                                contract.update_rate_script(
                                    sess, latest_rs, latest_rs.start_date,
                                    month_finish, latest_rs.script)
                                rs = contract.insert_rate_script(
                                    sess, month_start, '')
                                sess.flush()
                            script = {
                                'gbp_per_nbp_mwh': dict(
                                    (key_format(k), v)
                                    for k, v in sp_month.iteritems())}
                            self.log(
                                "Updating rate script starting at " +
                                hh_format(month_start) + ".")
                            contract.update_rate_script(
                                sess, rs, rs.start_date, rs.finish_date,
                                json.dumps(
                                    script, indent='    ', sort_keys=True))
                            sess.commit()
                    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())
                    if sess is not None:
                        sess.rollback()
                finally:
                    try:
                        if sess is not None:
                            sess.close()
                    finally:
                        self.lock.release()
                        self.log("Finished checking System Price rates.")

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