def run(self):
sess = None
try:
self._log(
"Starting to parse the file with '" + self.parser_name + "'.")
sess = Session()
g_batch = GBatch.get_by_id(sess, self.g_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:
bill_type = BillType.get_by_code(
sess, raw_bill['bill_type_code'])
g_bill = g_batch.insert_g_bill(
sess, bill_type, raw_bill['mprn'],
raw_bill['reference'], raw_bill['account'],
raw_bill['issue_date'], raw_bill['start_date'],
raw_bill['finish_date'], raw_bill['kwh'],
raw_bill['net_gbp'], raw_bill['vat_gbp'],
raw_bill['gross_gbp'], raw_bill['raw_lines'],
raw_bill['breakdown'])
sess.flush()
for raw_read in raw_bill['reads']:
prev_type = GReadType.get_by_code(
sess, raw_read['prev_type_code'])
pres_type = GReadType.get_by_code(
sess, raw_read['pres_type_code'])
g_units = GUnits.get_by_code(sess, raw_read['units'])
g_read = g_bill.insert_g_read(
sess, raw_read['msn'], raw_read['prev_value'],
raw_read['prev_date'], prev_type,
raw_read['pres_value'], raw_read['pres_date'],
pres_type, g_units, raw_read['correction_factor'],
raw_read['calorific_value'])
sess.expunge(g_read)
sess.commit()
self.successful_bills.append(raw_bill)
sess.expunge(g_bill)
except BadRequest as e:
sess.rollback()
raw_bill['error'] = e.description
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.")
except:
self._log("I've encountered a problem: " + traceback.format_exc())
finally:
if sess is not None:
sess.close()
def test_get_era_bundles_bill_after_supply_end(sess, client):
"""Check that a bill starting after the end of a supply still gets
shown.
"""
site = Site.insert(sess, "22488", "Water Works")
insert_sources(sess)
source = Source.get_by_code(sess, "net")
gsp_group = GspGroup.insert(sess, "_L", "South Western")
participant = Participant.insert(sess, "hhak", "AK Industries")
market_role_X = MarketRole.insert(sess, "X", "Supplier")
market_role_M = MarketRole.insert(sess, "M", "Mop")
market_role_C = MarketRole.insert(sess, "C", "HH Dc")
market_role_R = MarketRole.insert(sess, "R", "Distributor")
participant.insert_party(sess, market_role_M, "Fusion Mop Ltd",
utc_datetime(2000, 1, 1), None, None)
participant.insert_party(sess, market_role_X, "Fusion Ltc",
utc_datetime(2000, 1, 1), None, None)
participant.insert_party(sess, market_role_C, "Fusion DC",
utc_datetime(2000, 1, 1), None, None)
mop_contract = Contract.insert_mop(sess, "Fusion", participant, "", {},
utc_datetime(2000, 1, 1), None, {})
dc_contract = Contract.insert_hhdc(sess, "Fusion DC 2000", participant, "",
{}, utc_datetime(2000, 1, 1), None, {})
pc = Pc.insert(sess, "00", "hh", utc_datetime(2000, 1, 1), None)
insert_cops(sess)
cop = Cop.get_by_code(sess, "5")
imp_supplier_contract = Contract.insert_supplier(
sess,
"Fusion Supplier 2000",
participant,
"",
{},
utc_datetime(2000, 1, 1),
None,
{},
)
dno = participant.insert_party(sess, market_role_R, "WPD",
utc_datetime(2000, 1, 1), None, "22")
meter_type = MeterType.insert(sess, "C5", "COP 1-5",
utc_datetime(2000, 1, 1), None)
meter_payment_type = MeterPaymentType.insert(sess, "CR", "Credit",
utc_datetime(1996, 1, 1),
None)
Mtc.insert(
sess,
None,
"845",
"HH COP5 And Above With Comms",
False,
False,
True,
meter_type,
meter_payment_type,
0,
utc_datetime(1996, 1, 1),
None,
)
insert_voltage_levels(sess)
voltage_level = VoltageLevel.get_by_code(sess, "HV")
dno.insert_llfc(
sess,
"510",
"PC 5-8 & HH HV",
voltage_level,
False,
True,
utc_datetime(1996, 1, 1),
None,
)
insert_energisation_statuses(sess)
energisation_status = EnergisationStatus.get_by_code(sess, "E")
supply = site.insert_e_supply(
sess,
source,
None,
"Bob",
utc_datetime(2020, 1, 1),
utc_datetime(2020, 1, 31),
gsp_group,
mop_contract,
"773",
dc_contract,
"ghyy3",
"hgjeyhuw",
pc,
"845",
cop,
None,
energisation_status,
{},
"22 7867 6232 781",
"510",
imp_supplier_contract,
"7748",
361,
None,
None,
None,
None,
None,
)
batch = imp_supplier_contract.insert_batch(sess, "b1", "batch 1")
insert_bill_types(sess)
bill_type_N = BillType.get_by_code(sess, "N")
batch.insert_bill(
sess,
"ytgeklf",
"s77349",
utc_datetime(2020, 2, 10),
utc_datetime(2020, 2, 2),
utc_datetime(2020, 3, 1),
Decimal(0),
Decimal("0.00"),
Decimal("0.00"),
Decimal("0.00"),
bill_type_N,
{},
supply,
)
sess.commit()
bundles = get_era_bundles(sess, supply)
assert len(bundles[0]["imp_bills"]["bill_dicts"]) == 1
def test_supply(mocker, sess, client):
site = Site.insert(sess, "22488", "Water Works")
g_dn = GDn.insert(sess, "EE", "East of England")
g_ldz = g_dn.insert_g_ldz(sess, "EA")
g_exit_zone = g_ldz.insert_g_exit_zone(sess, "EA1")
insert_g_units(sess)
g_unit_M3 = GUnit.get_by_code(sess, "M3")
participant = Participant.insert(sess, "CALB", "AK Industries")
market_role_Z = MarketRole.get_by_code(sess, "Z")
participant.insert_party(sess, market_role_Z, "None core",
utc_datetime(2000, 1, 1), None, None)
g_cv_rate_script = {
"cvs": {
"EA": {
1: {
"applicable_at": utc_datetime(2020, 10, 3),
"cv": 39.2000
},
}
}
}
Contract.insert_non_core(sess, "g_cv", "", {}, utc_datetime(2000, 1, 1),
None, g_cv_rate_script)
bank_holiday_rate_script = {"bank_holidays": []}
Contract.insert_non_core(
sess,
"bank_holidays",
"",
{},
utc_datetime(2000, 1, 1),
None,
bank_holiday_rate_script,
)
charge_script = """
import chellow.g_ccl
from chellow.g_engine import g_rates
from chellow.utils import reduce_bill_hhs
def virtual_bill_titles():
return [
'units_consumed', 'correction_factor', 'unit_code', 'unit_factor',
'calorific_value', 'kwh', 'gas_rate', 'gas_gbp', 'ccl_rate',
'standing_rate', 'standing_gbp', 'net_gbp', 'vat_gbp', 'gross_gbp',
'problem']
def virtual_bill(ds):
chellow.g_ccl.vb(ds)
for hh in ds.hh_data:
start_date = hh['start_date']
bill_hh = ds.bill_hhs[start_date]
bill_hh['units_consumed'] = hh['units_consumed']
bill_hh['correction_factor'] = {hh['correction_factor']}
bill_hh['unit_code'] = {hh['unit_code']}
bill_hh['unit_factor'] = {hh['unit_factor']}
bill_hh['calorific_value'] = {hh['calorific_value']}
kwh = hh['kwh']
bill_hh['kwh'] = kwh
gas_rate = float(
g_rates(ds.sess, ds.caches, db_id, start_date)['gas_rate'])
bill_hh['gas_rate'] = {gas_rate}
bill_hh['gas_gbp'] = gas_rate * kwh
bill_hh['ccl_kwh'] = kwh
ccl_rate = hh['ccl']
bill_hh['ccl_rate'] = {ccl_rate}
bill_hh['ccl_kwh'] = kwh
bill_hh['ccl_gbp'] = kwh * ccl_rate
if hh['utc_is_month_end']:
standing_rate = float(
g_rates(
ds.sess, ds.caches, db_id, start_date)['standing_rate'])
bill_hh['standing_rate'] = {standing_rate}
bill_hh['standing_gbp'] = standing_rate
if hh['utc_decimal_hour'] == 0:
pass
bill_hh['net_gbp'] = sum(
v for k, v in bill_hh.items() if k.endswith('gbp'))
bill_hh['vat_gbp'] = 0
bill_hh['gross_gbp'] = bill_hh['net_gbp'] + bill_hh['vat_gbp']
ds.bill = reduce_bill_hhs(ds.bill_hhs)
"""
g_contract_rate_script = {
"gas_rate": 0.1,
"standing_rate": 0.1,
}
g_contract = GContract.insert(
sess,
"Fusion 2020",
charge_script,
{},
utc_datetime(2000, 1, 1),
None,
g_contract_rate_script,
)
insert_g_reading_frequencies(sess)
g_reading_frequency_M = GReadingFrequency.get_by_code(sess, "M")
msn = "hgeu8rhg"
g_supply = site.insert_g_supply(
sess,
"87614362",
"main",
g_exit_zone,
utc_datetime(2010, 1, 1),
None,
msn,
1,
g_unit_M3,
g_contract,
"d7gthekrg",
g_reading_frequency_M,
)
g_batch = g_contract.insert_g_batch(sess, "b1", "Jan batch")
breakdown = {"units_consumed": 771}
insert_bill_types(sess)
bill_type_N = BillType.get_by_code(sess, "N")
insert_g_read_types(sess)
g_read_type_A = GReadType.get_by_code(sess, "A")
g_bill = g_batch.insert_g_bill(
sess,
g_supply,
bill_type_N,
"55h883",
"dhgh883",
utc_datetime(2019, 4, 3),
utc_datetime(2015, 9, 1),
utc_datetime(2015, 9, 30, 22, 30),
Decimal("45"),
Decimal("12.40"),
Decimal("1.20"),
Decimal("14.52"),
"",
breakdown,
)
g_bill.insert_g_read(
sess,
msn,
g_unit_M3,
Decimal("1"),
Decimal("37"),
Decimal("90"),
utc_datetime(2015, 9, 1),
g_read_type_A,
Decimal("890"),
utc_datetime(2015, 9, 25),
g_read_type_A,
)
sess.commit()
mock_file = BytesIO()
mock_file.close = mocker.Mock()
mocker.patch("chellow.reports.report_g_monthly_duration.open",
return_value=mock_file)
mocker.patch(
"chellow.reports.report_g_monthly_duration.chellow.dloads.make_names",
return_value=("a", "b"),
)
mocker.patch("chellow.reports.report_g_monthly_duration.os.rename")
user = mocker.Mock()
site_id = site.id
g_supply_id = g_supply.id
compression = False
finish_year = 2015
finish_month = 9
months = 1
now = utc_datetime(2020, 9, 1)
chellow.reports.report_g_monthly_duration.content(
site_id,
g_supply_id,
user,
compression,
finish_year,
finish_month,
months,
now=now,
)
sheet = odio.parse_spreadsheet(mock_file)
table = list(sheet.tables[1].rows)
expected = [
[
"creation_date",
"mprn",
"supply_name",
"exit_zone",
"msn",
"unit",
"contract",
"site_id",
"site_name",
"associated_site_ids",
"month",
"kwh",
"gbp",
"billed_kwh",
"billed_gbp",
"units_consumed",
"correction_factor",
"unit_code",
"unit_factor",
"calorific_value",
"kwh",
"gas_rate",
"gas_gbp",
"ccl_rate",
"standing_rate",
"standing_gbp",
"net_gbp",
"vat_gbp",
"gross_gbp",
"problem",
],
[
Datetime(2020, 9, 1, 1),
"87614362",
"main",
"EA1",
"hgeu8rhg",
"M3",
"Fusion 2020",
"22488",
"Water Works",
"",
Datetime(2015, 9, 30, 23, 30),
10888.888888888665,
1146.1869155555785,
45.0,
12.4,
1000.0000000000156,
1.0,
"M3",
1.0,
39.2,
10888.888888888665,
0.1,
1088.8888888888619,
0.00525288,
0.1,
0.1,
1146.1869155555785,
0.0,
1146.1869155555785,
None,
],
]
assert expected == table
def test_batch(mocker, sess, client):
site = Site.insert(sess, "22488", "Water Works")
g_dn = GDn.insert(sess, "EE", "East of England")
g_ldz = g_dn.insert_g_ldz(sess, "EA")
g_exit_zone = g_ldz.insert_g_exit_zone(sess, "EA1")
insert_g_units(sess)
g_unit_M3 = GUnit.get_by_code(sess, "M3")
participant = Participant.insert(sess, "CALB", "AK Industries")
market_role_Z = MarketRole.get_by_code(sess, "Z")
participant.insert_party(sess, market_role_Z, "None core",
utc_datetime(2000, 1, 1), None, None)
g_cv_rate_script = {
"cvs": {
"EA": {
1: {
"applicable_at": utc_datetime(2020, 10, 3),
"cv": 39.2000
},
}
}
}
Contract.insert_non_core(sess, "g_cv", "", {}, utc_datetime(2000, 1, 1),
None, g_cv_rate_script)
bank_holiday_rate_script = {"bank_holidays": []}
Contract.insert_non_core(
sess,
"bank_holidays",
"",
{},
utc_datetime(2000, 1, 1),
None,
bank_holiday_rate_script,
)
charge_script = """
import chellow.g_ccl
from chellow.g_engine import g_rates
from chellow.utils import reduce_bill_hhs
def virtual_bill_titles():
return [
'units_consumed', 'correction_factor', 'unit_code', 'unit_factor',
'calorific_value', 'kwh', 'gas_rate', 'gas_gbp', 'ccl_rate',
'standing_rate', 'standing_gbp', 'net_gbp', 'vat_gbp', 'gross_gbp',
'problem']
def virtual_bill(ds):
chellow.g_ccl.vb(ds)
for hh in ds.hh_data:
start_date = hh['start_date']
bill_hh = ds.bill_hhs[start_date]
bill_hh['units_consumed'] = hh['units_consumed']
bill_hh['correction_factor'] = {hh['correction_factor']}
bill_hh['unit_code'] = {hh['unit_code']}
bill_hh['unit_factor'] = {hh['unit_factor']}
bill_hh['calorific_value'] = {hh['calorific_value']}
kwh = hh['kwh']
bill_hh['kwh'] = kwh
gas_rate = float(
g_rates(ds.sess, ds.caches, db_id, start_date)['gas_rate'])
bill_hh['gas_rate'] = {gas_rate}
bill_hh['gas_gbp'] = gas_rate * kwh
bill_hh['ccl_kwh'] = kwh
ccl_rate = hh['ccl']
bill_hh['ccl_rate'] = {ccl_rate}
bill_hh['ccl_kwh'] = kwh
bill_hh['ccl_gbp'] = kwh * ccl_rate
if hh['utc_is_month_end']:
standing_rate = float(
g_rates(
ds.sess, ds.caches, db_id, start_date)['standing_rate'])
bill_hh['standing_rate'] = {standing_rate}
bill_hh['standing_gbp'] = standing_rate
if hh['utc_decimal_hour'] == 0:
pass
bill_hh['net_gbp'] = sum(
v for k, v in bill_hh.items() if k.endswith('gbp'))
bill_hh['vat_gbp'] = 0
bill_hh['gross_gbp'] = bill_hh['net_gbp'] + bill_hh['vat_gbp']
ds.bill = reduce_bill_hhs(ds.bill_hhs)
"""
g_contract_rate_script = {
"gas_rate": 0.1,
"standing_rate": 0.1,
}
g_contract = GContract.insert(
sess,
"Fusion 2020",
charge_script,
{},
utc_datetime(2000, 1, 1),
None,
g_contract_rate_script,
)
insert_g_reading_frequencies(sess)
g_reading_frequency_M = GReadingFrequency.get_by_code(sess, "M")
g_supply = site.insert_g_supply(
sess,
"87614362",
"main",
g_exit_zone,
utc_datetime(2018, 1, 1),
None,
"hgeu8rhg",
1,
g_unit_M3,
g_contract,
"d7gthekrg",
g_reading_frequency_M,
)
g_batch = g_contract.insert_g_batch(sess, "b1", "Jan batch")
breakdown = {"units_consumed": 771}
insert_bill_types(sess)
bill_type_n = BillType.get_by_code(sess, "N")
g_bill = g_batch.insert_g_bill(
sess,
g_supply,
bill_type_n,
"55h883",
"dhgh883",
utc_datetime(2019, 4, 3),
utc_datetime(2020, 1, 1),
utc_datetime(2020, 1, 31, 23, 30),
Decimal("45"),
Decimal("12.40"),
Decimal("1.20"),
Decimal("14.52"),
"",
breakdown,
)
sess.commit()
mock_file = StringIO()
mock_file.close = mocker.Mock()
mocker.patch("chellow.reports.report_429.open", return_value=mock_file)
mocker.patch("chellow.reports.report_429.chellow.dloads.make_names",
return_value=("a", "b"))
mocker.patch("chellow.reports.report_429.os.rename")
user = mocker.Mock()
chellow.reports.report_429.content(g_batch.id, g_bill.id, user)
actual = mock_file.getvalue()
expected = [
"batch,bill_reference,bill_type,bill_start_date,bill_finish_date,"
"mprn,supply_name,site_code,site_name,covered_start,covered_finish,"
"covered_bill_ids,covered_units_consumed,virtual_units_consumed,"
"covered_correction_factor,virtual_correction_factor,"
"covered_unit_code,virtual_unit_code,covered_unit_factor,"
"virtual_unit_factor,covered_calorific_value,virtual_calorific_value,"
"covered_kwh,virtual_kwh,covered_gas_rate,virtual_gas_rate,"
"covered_gas_gbp,virtual_gas_gbp,difference_gas_gbp,covered_ccl_rate,"
"virtual_ccl_rate,covered_standing_rate,virtual_standing_rate,"
"covered_standing_gbp,virtual_standing_gbp,difference_standing_gbp,"
"covered_net_gbp,virtual_net_gbp,difference_net_gbp,covered_vat_gbp,"
"virtual_vat_gbp,difference_vat_gbp,covered_gross_gbp,"
"virtual_gross_gbp,difference_gross_gbp,covered_problem,"
"virtual_problem",
"b1,55h883,N,2020-01-01 00:00,2020-01-31 23:30,87614362,main,22488,"
"Water Works,2020-01-01 00:00,2020-01-31 23:30,1,771,0,,1.0,,M3,,1.0"
",,39.2,45,0.0,,0.1,,0.0,,,0.00339,,0.1,,0.1,,12.40,0.1,12.3,1.20,0,"
"1.2,14.52,0.1,14.42,,",
]
expected_str = "\r\n".join(expected) + "\r\n"
assert actual == expected_str
def test_bill_http(mocker, sess, client):
site = Site.insert(sess, "22488", "Water Works")
g_dn = GDn.insert(sess, "EE", "East of England")
g_ldz = g_dn.insert_g_ldz(sess, "EA")
g_exit_zone = g_ldz.insert_g_exit_zone(sess, "EA1")
insert_g_units(sess)
g_unit_M3 = GUnit.get_by_code(sess, "M3")
participant = Participant.insert(sess, "CALB", "AK Industries")
market_role_Z = MarketRole.get_by_code(sess, "Z")
participant.insert_party(sess, market_role_Z, "None core",
utc_datetime(2000, 1, 1), None, None)
g_contract = GContract.insert(sess, "Fusion 2020", "", {},
utc_datetime(2000, 1, 1), None, {})
insert_g_reading_frequencies(sess)
g_reading_frequency_M = GReadingFrequency.get_by_code(sess, "M")
g_supply = site.insert_g_supply(
sess,
"87614362",
"main",
g_exit_zone,
utc_datetime(2018, 1, 1),
None,
"hgeu8rhg",
1,
g_unit_M3,
g_contract,
"d7gthekrg",
g_reading_frequency_M,
)
g_batch = g_contract.insert_g_batch(sess, "b1", "Jan batch")
breakdown = {"units_consumed": 771}
insert_bill_types(sess)
bill_type_n = BillType.get_by_code(sess, "N")
g_bill = g_batch.insert_g_bill(
sess,
g_supply,
bill_type_n,
"55h883",
"dhgh883",
utc_datetime(2019, 4, 3),
utc_datetime(2020, 1, 1),
utc_datetime(2020, 1, 31, 23, 30),
Decimal("45"),
Decimal("12.40"),
Decimal("1.20"),
Decimal("14.52"),
"",
breakdown,
)
sess.commit()
data = {"g_bill_id": g_bill.id}
mock_Thread = mocker.patch("chellow.reports.report_429.threading.Thread",
autospec=True)
response = client.get("/reports/429", data=data)
match(response, 303)
user = None
args = (None, g_bill.id, user)
mock_Thread.assert_called_with(target=chellow.reports.report_429.content,
args=args)
def run(self):
sess = None
try:
self._log("Starting to parse the file with '" + self.parser_name +
"'.")
sess = Session()
g_batch = GBatch.get_by_id(sess, self.g_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:
bill_type = BillType.get_by_code(
sess, raw_bill['bill_type_code'])
g_supply = GSupply.get_by_mprn(sess, raw_bill['mprn'])
g_bill = g_batch.insert_g_bill(
sess, g_supply, bill_type, raw_bill['reference'],
raw_bill['account'], raw_bill['issue_date'],
raw_bill['start_date'], raw_bill['finish_date'],
raw_bill['kwh'], raw_bill['net_gbp'],
raw_bill['vat_gbp'], raw_bill['gross_gbp'],
raw_bill['raw_lines'], raw_bill['breakdown'])
sess.flush()
for raw_read in raw_bill['reads']:
prev_type = GReadType.get_by_code(
sess, raw_read['prev_type_code'])
pres_type = GReadType.get_by_code(
sess, raw_read['pres_type_code'])
g_unit = GUnit.get_by_code(sess, raw_read['unit'])
g_read = g_bill.insert_g_read(
sess, raw_read['msn'], g_unit,
raw_read['correction_factor'],
raw_read['calorific_value'],
raw_read['prev_value'], raw_read['prev_date'],
prev_type, raw_read['pres_value'],
raw_read['pres_date'], pres_type)
sess.expunge(g_read)
self.successful_bills.append(raw_bill)
sess.expunge(g_bill)
except BadRequest as e:
sess.rollback()
raw_bill['error'] = 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 " +
str(len(self.failed_bills)) +
" bills failed to load, " +
"and so the whole import has been rolled back.")
except BadRequest as e:
self._log(e.description)
except BaseException:
self._log("I've encountered a problem: " + traceback.format_exc())
finally:
if sess is not None:
sess.rollback()
sess.close()
def test_supply(mocker, sess, client):
site = Site.insert(sess, "22488", "Water Works")
g_dn = GDn.insert(sess, "EE", "East of England")
g_ldz = g_dn.insert_g_ldz(sess, "EA")
g_exit_zone = g_ldz.insert_g_exit_zone(sess, "EA1")
insert_g_units(sess)
g_unit_M3 = GUnit.get_by_code(sess, "M3")
participant = Participant.insert(sess, "CALB", "AK Industries")
market_role_Z = MarketRole.get_by_code(sess, "Z")
participant.insert_party(
sess, market_role_Z, "None core", utc_datetime(2000, 1, 1), None, None
)
g_cv_rate_script = {
"cvs": {
"EA": {
1: {"applicable_at": utc_datetime(2020, 10, 3), "cv": 39.2000},
}
}
}
Contract.insert_non_core(
sess, "g_cv", "", {}, utc_datetime(2000, 1, 1), None, g_cv_rate_script
)
bank_holiday_rate_script = {"bank_holidays": []}
Contract.insert_non_core(
sess,
"bank_holidays",
"",
{},
utc_datetime(2000, 1, 1),
None,
bank_holiday_rate_script,
)
charge_script = """
import chellow.g_ccl
from chellow.g_engine import g_rates
from chellow.utils import reduce_bill_hhs
def virtual_bill_titles():
return [
'units_consumed', 'correction_factor', 'unit_code', 'unit_factor',
'calorific_value', 'kwh', 'gas_rate', 'gas_gbp', 'ccl_rate',
'standing_rate', 'standing_gbp', 'net_gbp', 'vat_gbp', 'gross_gbp',
'problem']
def virtual_bill(ds):
chellow.g_ccl.vb(ds)
for hh in ds.hh_data:
start_date = hh['start_date']
bill_hh = ds.bill_hhs[start_date]
bill_hh['units_consumed'] = hh['units_consumed']
bill_hh['correction_factor'] = {hh['correction_factor']}
bill_hh['unit_code'] = {hh['unit_code']}
bill_hh['unit_factor'] = {hh['unit_factor']}
bill_hh['calorific_value'] = {hh['calorific_value']}
kwh = hh['kwh']
bill_hh['kwh'] = kwh
gas_rate = float(
g_rates(ds.sess, ds.caches, db_id, start_date)['gas_rate'])
bill_hh['gas_rate'] = {gas_rate}
bill_hh['gas_gbp'] = gas_rate * kwh
bill_hh['ccl_kwh'] = kwh
ccl_rate = hh['ccl']
bill_hh['ccl_rate'] = {ccl_rate}
bill_hh['ccl_kwh'] = kwh
bill_hh['ccl_gbp'] = kwh * ccl_rate
if hh['utc_is_month_end']:
standing_rate = float(
g_rates(
ds.sess, ds.caches, db_id, start_date)['standing_rate'])
bill_hh['standing_rate'] = {standing_rate}
bill_hh['standing_gbp'] = standing_rate
if hh['utc_decimal_hour'] == 0:
pass
bill_hh['net_gbp'] = sum(
v for k, v in bill_hh.items() if k.endswith('gbp'))
bill_hh['vat_gbp'] = 0
bill_hh['gross_gbp'] = bill_hh['net_gbp'] + bill_hh['vat_gbp']
ds.bill = reduce_bill_hhs(ds.bill_hhs)
"""
g_contract_rate_script = {
"gas_rate": 0.1,
"standing_rate": 0.1,
}
g_contract = GContract.insert(
sess,
"Fusion 2020",
charge_script,
{},
utc_datetime(2000, 1, 1),
None,
g_contract_rate_script,
)
insert_g_reading_frequencies(sess)
g_reading_frequency_M = GReadingFrequency.get_by_code(sess, "M")
msn = "hgeu8rhg"
g_supply = site.insert_g_supply(
sess,
"87614362",
"main",
g_exit_zone,
utc_datetime(2010, 1, 1),
None,
msn,
1,
g_unit_M3,
g_contract,
"d7gthekrg",
g_reading_frequency_M,
)
g_batch = g_contract.insert_g_batch(sess, "b1", "Jan batch")
breakdown = {"units_consumed": 771}
insert_bill_types(sess)
bill_type_N = BillType.get_by_code(sess, "N")
insert_g_read_types(sess)
g_read_type_A = GReadType.get_by_code(sess, "A")
g_bill = g_batch.insert_g_bill(
sess,
g_supply,
bill_type_N,
"55h883",
"dhgh883",
utc_datetime(2019, 4, 3),
utc_datetime(2015, 9, 1),
utc_datetime(2015, 9, 30, 22, 30),
Decimal("45"),
Decimal("12.40"),
Decimal("1.20"),
Decimal("14.52"),
"",
breakdown,
)
g_bill.insert_g_read(
sess,
msn,
g_unit_M3,
Decimal("1"),
Decimal("37"),
Decimal("90"),
utc_datetime(2015, 9, 1),
g_read_type_A,
Decimal("890"),
utc_datetime(2015, 9, 25),
g_read_type_A,
)
sess.commit()
mock_file = StringIO()
mock_file.close = mocker.Mock()
mocker.patch(
"chellow.reports.report_g_virtual_bills_hh.open", return_value=mock_file
)
mocker.patch(
"chellow.reports.report_g_virtual_bills_hh.chellow.dloads.make_names",
return_value=("a", "b"),
)
mocker.patch("chellow.reports.report_g_virtual_bills.os.rename")
user = mocker.Mock()
g_supply_id = g_supply.id
start_date = to_utc(ct_datetime(2018, 2, 1))
finish_date = to_utc(ct_datetime(2018, 2, 1, 0, 30))
chellow.reports.report_g_virtual_bills_hh.content(
g_supply_id, start_date, finish_date, user
)
mock_file.seek(0)
table = list(csv.reader(mock_file))
expected = [
[
"MPRN",
"Site Code",
"Site Name",
"Account",
"HH Start",
"",
"",
"units_consumed",
"correction_factor",
"unit_code",
"unit_factor",
"calorific_value",
"kwh",
"gas_rate",
"gas_gbp",
"ccl_rate",
"standing_rate",
"standing_gbp",
"net_gbp",
"vat_gbp",
"gross_gbp",
"problem",
],
[
"87614362",
"22488",
"Water Works",
"d7gthekrg",
"2018-02-01 00:00",
"",
"",
"0.6944444444444444",
"1.0",
"M3",
"1.0",
"39.2",
"7.561728395061729",
"0.1",
"0.7561728395061729",
"0.00198",
"",
"",
"0.7711450617283951",
"0",
"0.7711450617283951",
"",
"ccl_gbp",
"0.014972222222222222",
"ccl_kwh",
"7.561728395061729",
],
[
"87614362",
"22488",
"Water Works",
"d7gthekrg",
"2018-02-01 00:30",
"",
"",
"0.6944444444444444",
"1.0",
"M3",
"1.0",
"39.2",
"7.561728395061729",
"0.1",
"0.7561728395061729",
"0.00198",
"",
"",
"0.7711450617283951",
"0",
"0.7711450617283951",
"",
"ccl_gbp",
"0.014972222222222222",
"ccl_kwh",
"7.561728395061729",
],
]
match_tables(table, expected)
def run(self):
sess = None
try:
sess = Session()
batch = Batch.get_by_id(sess, self.batch_id)
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))
for bf in (sess.query(BatchFile).filter(
BatchFile.batch == batch).order_by(
BatchFile.upload_timestamp)):
self.parser = _process_batch_file(sess, bf, self._log)
for self.bill_num, raw_bill in enumerate(
self.parser.make_raw_bills()):
if "error" in raw_bill:
self.failed_bills.append(raw_bill)
else:
try:
mpan_core = raw_bill["mpan_core"]
supply = Supply.get_by_mpan_core(sess, mpan_core)
with sess.begin_nested():
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_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 KeyError as e:
err = raw_bill.get("error", "")
raw_bill["error"] = err + " " + str(e)
self.failed_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(f"The import has finished, but there were "
f"{len(self.failed_bills)} failures, and so the "
f"whole import has been rolled back.")
except BadRequest as e:
sess.rollback()
self._log(f"Problem: {e.description}")
except BaseException:
sess.rollback()
self._log(f"I've encountered a problem: {traceback.format_exc()}")
finally:
if sess is not None:
sess.close()
def run(self):
sess = None
try:
sess = Session()
self._log(
"Starting to parse the file with '" + self.parser_name + "'.")
set_read_write(sess)
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:
with sess.begin_nested():
sess.execute(
"set transaction isolation level serializable "
"read write")
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'])
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)
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()
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.dc_contract == contract, Era.dc_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 BadRequest as e:
sess.rollback()
self._log("Problem: " + e.description)
except BaseException:
sess.rollback()
self._log("I've encountered a problem: " + traceback.format_exc())
finally:
if sess is not None:
sess.close()
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()