def test_parse_meter_row_keyerror(self):
"""Test that parse_meter_row raises a KeyError when a field is missing"""
meter_row = {
"PK": 1,
"Tariff": "test_tariff",
# Exclude this field"ServiceType": "test_service",
"PODid": "12345",
"MeterNumber": "67890",
"IntervalStart": date(2000, 2, 1),
"IntervalEnd": date(2000, 3, 1),
}
with self.assertRaises(KeyError):
UrjanetPyMySqlDataSource.parse_meter_row(meter_row)
def test_parse_meter_row_valueerror(self):
"""Test that parse_meter_row raises a ValueError when a field has an invalid value"""
meter_row = {
"PK": "not_an_int", # ValueError occurs here
"Tariff": "test_tariff",
"ServiceType": "test_service",
"PODid": "12345",
"MeterNumber": "67890",
"IntervalStart": date(2000, 2, 1),
"IntervalEnd": date(2000, 3, 1),
}
with self.assertRaises(ValueError):
UrjanetPyMySqlDataSource.parse_meter_row(meter_row)
def test_parse_charge_row_keyerror(self):
"""Test that parse_charge_row raises a KeyError when a field is missing"""
charge_row = {
"PK": 1,
"ChargeActualName": "test_charge_name",
"UsageUnit": "kW",
"ChargeUnitsUsed": Decimal(200),
"ChargeRatePerUnit": Decimal(10),
# Exclude this field: "ChargeAmount": Decimal(100.00)
"ThirdPartyProvider": "test_provider",
"IntervalStart": date(2000, 2, 1),
"IntervalEnd": date(2000, 3, 1),
}
with self.assertRaises(KeyError):
UrjanetPyMySqlDataSource.parse_charge_row(charge_row)
def run_urjanet_datafeed(
account: SnapmeterAccount,
meter: Meter,
datasource: MeterDataSource,
params: dict,
urja_datasource: UrjanetPyMySqlDataSource,
transformer: UrjanetGridiumTransformer,
task_id: Optional[str] = None,
partial_type: Optional[PartialBillProviderType] = None,
) -> Status:
conn = db.urjanet_connection()
try:
urja_datasource.conn = conn
scraper_config = BaseUrjanetConfiguration(
urja_datasource=urja_datasource,
urja_transformer=transformer,
utility_name=meter.utility_service.utility,
fetch_attachments=True,
partial_type=partial_type,
)
return run_datafeed(
BaseUrjanetScraper,
account,
meter,
datasource,
params,
configuration=scraper_config,
task_id=task_id,
)
finally:
conn.close()
def test_parse_charge_row_nil_pk(self):
"""Test that parse_charge_row raises a ValueError when the primary key field is missing"""
charge_row = {
"PK": None,
"ChargeActualName": "test_charge_name",
"ChargeAmount": Decimal(100.00),
"UsageUnit": "kW",
"ChargeUnitsUsed": Decimal(200),
"ChargeRatePerUnit": Decimal(10),
"ThirdPartyProvider": "test_provider",
"IsAdjustmentCharge": 0,
"IntervalStart": date(2000, 2, 1),
"IntervalEnd": date(2000, 3, 1),
}
with self.assertRaises(ValueError):
UrjanetPyMySqlDataSource.parse_charge_row(charge_row)
def load_meters(self, account_pk: int) -> List[Meter]:
"""Load all meters for an account."""
query = """
select *
from Meter
where Meter.AccountFK=%s and Meter.ServiceType in ('electric', 'natural_gas', 'lighting')
"""
if self.meter_number:
query += " AND (Meter.MeterNumber LIKE %s OR Meter.MeterNumber LIKE %s)"
result_set = self.fetch_all(
query,
account_pk,
"%{}%".format(self.said),
"%{}".format(self.meter_number),
)
else:
query += " AND Meter.MeterNumber LIKE %s"
result_set = self.fetch_all(query, account_pk,
"%{}%".format(self.said))
if not result_set and self.service_type:
# If no meters have been found for the account
query = """
select *
from Meter
where Meter.AccountFK=%s and Meter.ServiceType in (%s)
"""
result_set = self.fetch_all(query, account_pk, self.service_type)
return [
UrjanetPyMySqlDataSource.parse_meter_row(row) for row in result_set
]
def load_accounts(self) -> List[Account]:
"""Load accounts based on the account id.
For this utility, a statement is only admissible to our
system if there is a meter associated with it. (There's at
most one meter per account.)
"""
query = """
select *
from Account
where AccountNumber=%s and Account.UtilityProvider='NVEnergy'
"""
result_set = self.fetch_all(query, self.account_number)
# Group the statements by interval, and take the latest statement for a given interval
result_set = [
max(group, key=lambda stmt: stmt["StatementDate"])
for _, group in itertools.groupby(
result_set, lambda stmt:
(stmt["IntervalStart"], stmt["IntervalEnd"]))
]
return [
UrjanetPyMySqlDataSource.parse_account_row(row)
for row in result_set
]
def load_accounts(self) -> List[Account]:
"""Load accounts based on the account id.
Note: Currently this scraper does NOT filter by utility name.
The reason for this is that some of the older datapoints for
these meters have a different utility name, but the Urjanet
data adheres to the same constraints. This may need to be
revisited in the future.
"""
query = """
select *
from Account, Meter
where Account.PK=Meter.AccountFK and Meter.PODid=%s
"""
result_set = self.fetch_all(query, self.account_number)
# For each billing period, we extract the most recent statement,
# to account for billing corrections.
groups: DefaultDict[Tuple, SqlQueryResult] = defaultdict(list)
for stmt in result_set:
date_range = (stmt["IntervalStart"], stmt["IntervalEnd"])
groups[date_range].append(stmt)
get_date = itemgetter("StatementDate")
return [
UrjanetPyMySqlDataSource.parse_account_row(row)
for row in [max(group, key=get_date) for group in groups.values()]
]
def test_parse_account_row(self):
"""Test the basic functionality of the parse_account_row function."""
account_row = {
"PK": 1,
"UtilityProvider": "test_provider",
"AccountNumber": "12345",
"RawAccountNumber": "1234-5",
"SourceLink": "test_link",
"StatementType": "test_statement_type",
"StatementDate": date(2000, 1, 1),
"IntervalStart": date(2000, 2, 1),
"IntervalEnd": date(2000, 3, 1),
"TotalBillAmount": Decimal("100.00"),
"AmountDue": Decimal("200.00"),
"NewCharges": Decimal("80.00"),
"OutstandingBalance": Decimal("90.00"),
"PreviousBalance": Decimal("120.00"),
"__EXTRA1": "EXTRA1", # It's okay to have extra fields
"__EXTRA2": "EXTRA2",
}
result = UrjanetPyMySqlDataSource.parse_account_row(account_row)
for field in account_row:
if field.startswith("__EXTRA"):
with self.assertRaises(AttributeError):
getattr(result, field)
else:
self.assertEqual(getattr(result, field), account_row[field])
def test_parse_charge_row_valueerror(self):
"""Test that parse_charge_row raises a ValueError when a field has an invalid value"""
charge_row = {
"PK": 1,
"ChargeActualName": "test_charge_name",
"ChargeAmount": "not_a_decimal", # ValueError here
"UsageUnit": "kW",
"ChargeUnitsUsed": Decimal(200),
"ChargeRatePerUnit": Decimal(10),
"ThirdPartyProvider": "test_provider",
"IsAdjustmentCharge": 0,
"IntervalStart": date(2000, 2, 1),
"IntervalEnd": date(2000, 3, 1),
"ChargeId": None,
}
with self.assertRaises(ValueError):
UrjanetPyMySqlDataSource.parse_charge_row(charge_row)
def load_accounts(self) -> List[Account]:
query = """
select *
from Account
where RawAccountNumber=%s and UtilityProvider = 'PugetSoundEnergy'
"""
result_set = self.fetch_all(query, self.account_number)
return [UrjanetPyMySqlDataSource.parse_account_row(row) for row in result_set]
def load_meters(self, account_pk: int) -> List[Meter]:
"""Load meters based on the service id"""
query = """SELECT * FROM Meter WHERE ServiceType = 'electric' AND AccountFK=%s
and MeterNumber LIKE %s
"""
result_set = self.fetch_all(query, account_pk, self.said)
return [UrjanetPyMySqlDataSource.parse_meter_row(row) for row in result_set]
def load_meters(self, account_pk: int) -> List[UrjaMeter]:
"""Load all electric meters for an account"""
query = "SELECT * FROM Meter WHERE ServiceType in ('electric') AND AccountFK=%s"
result_set = self.fetch_all(query, account_pk)
return [
UrjanetPyMySqlDataSource.parse_meter_row(row) for row in result_set
]
def load_accounts(self) -> List[Account]:
query = """
SELECT *
FROM Account
WHERE AccountNumber=%s AND UtilityProvider = 'AmericanWater'
"""
result_set = self.fetch_all(query, self.account_number)
return [UrjanetPyMySqlDataSource.parse_account_row(row) for row in result_set]
def load_meters(self, account_pk: int) -> List[Meter]:
"""Load meters based on the service id"""
query = """
select *
from Meter
where AccountFK=%s and ServiceType in ('water', 'sewer', 'irrigation', 'sanitation')
"""
result_set = self.fetch_all(query, account_pk)
return [UrjanetPyMySqlDataSource.parse_meter_row(row) for row in result_set]
def load_accounts(self) -> List[Account]:
query = """
select *
from Account
where AccountNumber=%s and UtilityProvider = 'ContraCostaWaterDistrictCA'
"""
acct_no = self.account_number
result_set = self.fetch_all(query, acct_no)
return [UrjanetPyMySqlDataSource.parse_account_row(row) for row in result_set]
def load_meters(self, account_pk: int) -> List[UrjaMeter]:
"""Load all meters for an account
Currently only has water meters.
"""
query = "SELECT * FROM Meter WHERE ServiceType='water' AND AccountFK=%s"
result_set = self.fetch_all(query, account_pk)
return [UrjanetPyMySqlDataSource.parse_meter_row(row) for row in result_set]
def load_accounts(self) -> List[Account]:
"""Load accounts based on the account id"""
query = """
SELECT *
FROM Account
WHERE AccountNumber=%s AND UtilityProvider = 'CityOfSouthlakeTX'
"""
result_set = self.fetch_all(query, self.account_number)
return [UrjanetPyMySqlDataSource.parse_account_row(row) for row in result_set]
def load_accounts(self) -> List[Account]:
"""Load accounts based on the account id"""
query = """
SELECT *
FROM Account
WHERE AccountNumber=%s AND UtilityProvider = 'LADeptOfWAndP'
"""
acct_no = self.account_number
result_set = self.fetch_all(query, acct_no)
return [UrjanetPyMySqlDataSource.parse_account_row(row) for row in result_set]
def load_meters(self, account_pk: int) -> List[UrjaMeter]:
"""Load all meters for an account
Currently, water, sewer meters are loaded.
"""
query = "SELECT * FROM Meter WHERE ServiceType in ('water', 'sewer', 'irrigation') AND AccountFK=%s"
result_set = self.fetch_all(query, account_pk)
return [
UrjanetPyMySqlDataSource.parse_meter_row(row) for row in result_set
]
def load_meter_usages(self, account_pk: int, meter_pk: int) -> List[Usage]:
"""Fetch all usages for a given meter"""
query = """
SELECT *
FROM xmlusage
WHERE AccountFK=%s AND MeterFK=%s
"""
result_set = self.fetch_all(query, account_pk, meter_pk)
return [
UrjanetPyMySqlDataSource.parse_usage_row(row) for row in result_set
]
def test_parse_account_row_valueerror(self):
"""Test that parse_account_row raises a ValueError when a field has an invalid value"""
account_row = {
"PK": 1,
"UtilityProvider": "test_provider",
"AccountNumber": "12345",
"RawAccountNumber": "1234-5",
"SourceLink": "test_link",
"StatementType": "test_statement_type",
"StatementDate": "not_a_date", # ValueError should occur here
"IntervalStart": date(2000, 2, 1),
"IntervalEnd": date(2000, 3, 1),
"TotalBillAmount": Decimal("100.00"),
"AmountDue": Decimal("200.00"),
"NewCharges": Decimal("80.00"),
"OutstandingBalance": Decimal("90.00"),
"PreviousBalance": Decimal("120.00"),
}
with self.assertRaises(ValueError):
UrjanetPyMySqlDataSource.parse_account_row(account_row)
def load_meters(self, account_pk: int) -> List[Meter]:
"""Load meters for an account, optionally filtering by meter ID
Currently, both water and sewer meters are loaded.
"""
query = "SELECT * FROM Meter WHERE ServiceType in ('water', 'sewer') AND AccountFK=%s"
result_set = self.fetch_all(query, account_pk)
return [
UrjanetPyMySqlDataSource.parse_meter_row(row) for row in result_set
]
def load_meters(self, account_pk: int) -> List[Meter]:
"""Load meters based on the service id"""
query = """
SELECT *
FROM Meter
WHERE
AccountFK=%s
AND ServiceType='water'
AND PODid=%s
"""
result_set = self.fetch_all(query, account_pk, self.service_id)
return [UrjanetPyMySqlDataSource.parse_meter_row(row) for row in result_set]
def load_accounts(self) -> List[Account]:
"""Get by account number."""
query = """
SELECT *
FROM Account
WHERE AccountNumber=%s AND UtilityProvider = 'LADeptOfWAndP'
"""
result_set = self.fetch_all(query, self.account_number)
return [
UrjanetPyMySqlDataSource.parse_account_row(row)
for row in result_set
]
def load_meters(self, account_pk: int) -> List[UrjaMeter]:
"""Load meters matching a Gridium meter SAID.
A bill can contain usage and charges for multiple meters. Select meters where the
Urjanet Meter.MeterNumber matches a Gridium utility_service.service_id
"""
# The utility may totalize submeters, and have two meter numbers for one set of charges.
# In this case, the SAID should contain both meter ids, separated by commas.
query = "SELECT * FROM Meter WHERE ServiceType='electric' AND AccountFK=%s AND MeterNumber in %s"
result_set = self.fetch_all(query, account_pk, self.said.split(","))
return [
UrjanetPyMySqlDataSource.parse_meter_row(row) for row in result_set
]
def load_accounts(self) -> List[Account]:
"""Load accounts based on the account id."""
query = """
select *
from Account
where AccountNumber like %s and UtilityProvider = 'SDGAndE'
"""
result_set = self.fetch_all(
query, "%{}%".format(self.account_number.replace(" ", "")))
return [
UrjanetPyMySqlDataSource.parse_account_row(row)
for row in result_set
]
def load_meters(self, account_pk: int) -> List[Meter]:
"""Load all meters for an account."""
query = """
select *
from Meter
where Meter.AccountFK=%s and Meter.ServiceType in ('electric', 'natural_gas')
and Meter.MeterNumber LIKE %s
"""
result_set = self.fetch_all(query, account_pk,
"%{}%".format(self.said.replace(" ", "")))
return [
UrjanetPyMySqlDataSource.parse_meter_row(row) for row in result_set
]
def load_accounts(self) -> List[Account]:
"""Load accounts based on the account id."""
query = """
select *
from Account
where AccountNumber=%s and Account.UtilityProvider='FPL'
"""
result_set = self.fetch_all(query, self.account_number)
return [
UrjanetPyMySqlDataSource.parse_account_row(row)
for row in result_set
]
def load_meters(self, account_pk: int) -> List[Meter]:
"""Load meters based on the service id"""
query = """
SELECT *
FROM Meter
WHERE
AccountFK=%s
AND ServiceType in ('electric', 'natural_gas', 'lighting')
AND PODid LIKE %s
"""
result_set = self.fetch_all(query, account_pk, self.meter_id)
return [
UrjanetPyMySqlDataSource.parse_meter_row(row) for row in result_set
]