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 ]