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_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 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_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_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_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_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_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_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[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_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_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
]
def load_meters(self, account_pk: int) -> List[Meter]:
"""Load all meters for an account.
Note: This is just for computing line items.
For all the accounts we've provisioned thus far, statements
and physical meters are in one to one correspondence (there
are no submeters).
"""
query = """
select *
from Meter
where Meter.AccountFK=%s and Meter.ServiceType in ('electric', 'natural_gas')
"""
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 all SAID's we have on record, and any ESP Customer Numbers found."""
query = """
SELECT *
FROM xmlmeter
WHERE
AccountFK=%s
AND ServiceType in ('electric', 'natural_gas', 'lighting')
AND PODid in ({})
""".format(create_placeholders(self.service_ids))
result_set = self.fetch_all(query, account_pk, *self.service_ids)
results = [
UrjanetPyMySqlDataSource.parse_meter_row(row) for row in result_set
]
return results
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.
saids = []
# SAIDs may have a trailing letter that's not in the Urjanet version; try both versions
for said in self.said.split(","):
saids.append(said)
saids.append(re.sub(r"[A-Z]$", "", said))
query = "SELECT * FROM Meter WHERE ServiceType in %s AND AccountFK=%s AND MeterNumber in %s"
result_set = self.fetch_all(query, self.commodity_type.value,
account_pk, saids)
return [
UrjanetPyMySqlDataSource.parse_meter_row(row) for row in result_set
]
def load_meters(self, account_pk: int) -> List[Meter]:
"""Load all meters for an account.
Note: This is just for computing line items.
Urjanet appears to use the last 6 of our service ID as the
meter number in their records.
"""
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[-6:]))
return [
UrjanetPyMySqlDataSource.parse_meter_row(row) for row in result_set
]
def load_meters(self, account_pk: int) -> List[Meter]:
"""Load all meters for an account.
PSE meter numbers tend to have a single-letter prefix, e.g. "Z".
Urjanet sometimes stores the meter number without this prefix.
Therefore, we look for bills with meter numbers that match either
the prefix or non-prefixed meter number.
"""
said_no_prefix = re.sub("^[A-Z]", "", self.said)
query = """
select *
from Meter
where AccountFK=%s and Meter.ServiceType in ('electric', 'natural_gas')
and (Meter.MeterNumber like %s or Meter.MeterNumber like %s)
"""
result_set = self.fetch_all(
query, account_pk, "%{}%".format(self.said), "%{}%".format(said_no_prefix)
)
return [UrjanetPyMySqlDataSource.parse_meter_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 AccountFK=%s and ServiceType in ('electric', 'gas')
"""
result_set = self.fetch_all(query, account_pk)
# Check our requirement that each statement has data about a single POD id
pod_ids = [mtr["PODid"] for mtr in result_set]
if len(set(pod_ids)) > 1:
msg = (
"This scraper requires each statement to "
"contain information for a single meter/PODid. "
"Violated by: Statement PK=={1}"
)
msg = msg.format(account_pk)
raise ScraperPreconditionError(msg)
return [UrjanetPyMySqlDataSource.parse_meter_row(row) for row in result_set]
def load_meters(self, account_pk: int) -> List[Meter]:
"""LADWP formats these bills so that the 'meter number' is often on two lines.
Urjanet pulls in that second line with a space. Gridium often munges the whole
thing into a single number. It's unclear who is right. So we use a bit of replace()
magic to just flatten the whole thing into a single number to normalize this.
"""
query = """
SELECT *
FROM Meter
WHERE
AccountFK=%s
AND replace(Meter.MeterNumber, ' ', '') LIKE %s
AND ServiceType in ('electric', 'natural_gas')
"""
result_set = self.fetch_all(query, account_pk,
"%%%s%%" % self.service_id)
return [
UrjanetPyMySqlDataSource.parse_meter_row(row) for row in result_set
]
def test_parse_meter_row(self):
"""Test the basic functionality of the parse_meter_row function."""
meter_row = {
"PK": 1,
"Tariff": "test_tariff",
"ServiceType": "test_service",
"PODid": "12345",
"MeterNumber": "67890",
"IntervalStart": date(2000, 2, 1),
"IntervalEnd": date(2000, 3, 1),
"__EXTRA1": "EXTRA1", # It's okay to have extra fields
"__EXTRA2": "EXTRA2",
}
result = UrjanetPyMySqlDataSource.parse_meter_row(meter_row)
for field in meter_row:
if field.startswith("__EXTRA"):
with self.assertRaises(AttributeError):
getattr(result, field)
else:
self.assertEqual(getattr(result, field), meter_row[field])
def load_meters(self, account_pk: int) -> List[Meter]:
"""Load all meters for an account.
Normally we'd pull only bill data that matches our service ID. However, FPL has a habit of
reassigning new service IDs to the same meter. To handle that we're just always going
to assume that meters in the same account are the same meter, regardless of the ID. This
assumption depends on the arrangement in which only a single meter is ever attached
to one statement. If that isn't true, bail out and fix this some other way.
"""
query = """
select *
from Meter
where Meter.AccountFK=%s
"""
result_set = self.fetch_all(query, account_pk)
assert len(result_set) <= 1
return [
UrjanetPyMySqlDataSource.parse_meter_row(row) for row in result_set
]