def get_service_type(self, service_ids: List[str]) -> Optional[str]:
"""Return the first service type associated with these PODids"""
query = """
SELECT ServiceType
FROM xmlmeter
WHERE PODid IN ({})
""".format(create_placeholders(service_ids))
result = self.fetch_one(query, *service_ids)
return result.get("ServiceType") if result else None
def get_urja_pdfs_for_service(self) -> List[BillPdf]:
service_ids = get_service_ids(
self.urja_datasource.utility_service) # type: ignore
log.info(
"Looking for Urjanet PDFs to attach to existing SMD Bills on service ids: %s",
service_ids,
)
utility_account_id = self.urja_datasource.utility_service.utility_account_id # type: ignore
# Order by StatementDate ASC so most recent statement will be added as the first attachment on a bill.
query = """
SELECT xmlaccount.SourceLink, xmlaccount.StatementDate, xmlmeter.IntervalStart, xmlmeter.IntervalEnd
FROM xmlaccount, xmlmeter
WHERE xmlaccount.PK = xmlmeter.AccountFK
AND xmlaccount.UtilityProvider = 'PacGAndE'
AND (RawAccountNumber LIKE %s OR REPLACE(RawAccountNumber, '-', '')=%s)
AND PODid in ({})
ORDER BY xmlmeter.IntervalStart DESC, xmlaccount.StatementDate ASC
""".format(create_placeholders(service_ids))
pge_pdfs: List[BillPdf] = []
account_number_prefix_regex = "{}%".format(utility_account_id)
result_set = self.urja_datasource.fetch_all( # type: ignore
query, account_number_prefix_regex, utility_account_id,
*service_ids)
for row in result_set:
source_url = row.get("SourceLink")
statement = row.get("StatementDate") or row.get("IntervalEnd")
start = row.get("IntervalStart")
end = row.get("IntervalEnd")
attachments = make_attachments(
source_urls=[source_url],
statement=statement,
utility="utility:pge",
account_id=utility_account_id,
gen_utility=None,
gen_utility_account_id=None,
)
if attachments:
att = attachments[0]
pge_pdfs.append(
BillPdf(
utility_account_id=utility_account_id,
gen_utility_account_id="",
start=start,
end=end,
statement=statement,
s3_key=att.key,
))
return pge_pdfs
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 attach_corresponding_urja_pdfs(
self, partial_bills: BillingData) -> BillingData:
"""Attempt to update each SMD Partial Bill with the latest statement from Urjanet."""
self.conn = db.urjanet_connection()
utility_account_id = self.service.utility_account_id
service_ids = get_service_ids(self.service)
query = """
SELECT xmlaccount.SourceLink, xmlaccount.StatementDate
FROM xmlaccount, xmlmeter
WHERE xmlaccount.PK = xmlmeter.AccountFK
AND xmlaccount.UtilityProvider = 'PacGAndE'
AND RawAccountNumber = %s
AND PODid in ({})
AND xmlmeter.IntervalStart > %s
AND xmlmeter.IntervalStart < %s
ORDER BY xmlaccount.StatementDate DESC
LIMIT 1
""".format(create_placeholders(service_ids))
updated_partials = []
for pb in partial_bills:
attachments = None
pdf = self.fetch_one(query, utility_account_id, *service_ids,
pb.start - timedelta(days=1),
pb.start + timedelta(days=1))
if pdf:
source_url = pdf.get("SourceLink")
statement = pdf.get("StatementDate", pb.statement or pb.end)
attachments = make_attachments(
source_urls=[source_url],
statement=statement,
utility=self.service.utility,
account_id=utility_account_id,
gen_utility=self.service.gen_utility,
gen_utility_account_id=self.service.gen_utility_account_id,
)
if attachments:
updated_partials.append(pb._replace(attachments=attachments))
else:
updated_partials.append(pb)
self.conn.close()
return updated_partials
def get_all_service_ids(self, account_pks: List[int]) -> List[str]:
"""
Supplements known SAIDs with Third Party ESP Customer Numbers.
Typically, Third Party Urjanet "Meters" will have the same "ESP Customer Number" as the PG&E SAID, so it is easy
to locate related charges. However, sometimes PG&E assigns a different ESP Customer Number. To locate
additional ESP Customer Numbers that are related to PG&E SAID's, look for third party meters with charges
whose ChargeUnitsUsed match PG&E charges in the same billing period at the same address.
Address alone is not enough to narrow down, as service addresses are not always unique.
"""
service_ids = self.get_historical_service_ids()
service_address = self.get_service_address(service_ids)
service_type = self.get_service_type(service_ids)
if account_pks and service_address and service_type:
# Creates a temporary table of T&D charges at the
# same address, on the same type of meter, associated with PODids we have on record.
query = """
CREATE TEMPORARY TABLE tnd_charges AS
SELECT distinct(Charge.ChargeUnitsUsed), Charge.IntervalStart
FROM xmlaccount Account, xmlmeter Meter, xmlcharge Charge
WHERE Charge.MeterFK = Meter.PK
AND Account.PK = Meter.AccountFK
AND Meter.PODid in ({})
AND Meter.ServiceAddress = %s
AND Meter.ServiceType = %s
AND Account.UtilityProvider = 'PacGAndE'
AND Charge.ChargeUnitsUsed is not null;
""".format(create_placeholders(service_ids))
self.execute(query, *service_ids, service_address.upper(),
service_type)
# Look for missing Third Party PODids at the same address,
# same service type, and for the same month, that have multiple "ChargeUnitsUsed" that correspond
# to the T&D charges. For example, a T&D bill may have 80.000000 kWh charged at some rate,
# and the corresponding third party bill will have 80.000000 kWh charged at a different rate.
query = """
SELECT distinct(Meter.PODid)
FROM xmlmeter Meter, xmlcharge Charge, tnd_charges, xmlaccount Account
WHERE Meter.PK = Charge.MeterFK
AND Meter.AccountFK in ({})
AND Meter.ServiceType = %s
AND Account.UtilityProvider != 'PacGAndE'
AND Account.PK = Meter.AccountFK
AND Meter.IntervalStart = tnd_charges.IntervalStart
AND Charge.ChargeUnitsUsed = tnd_charges.ChargeUnitsUsed
AND UPPER(Meter.ServiceAddress) = %s
GROUP BY Meter.PODid, Meter.IntervalStart having count(Charge.ChargeUnitsUsed) > 1;
""".format(create_placeholders(account_pks))
meter_pod_id_results = self.fetch_all(query, *account_pks,
service_type,
service_address)
esp_customer_numbers = [
result.get("PODid") for result in meter_pod_id_results
]
log.info("Additional ESP Customer Numbers located: {}".format([
num for num in esp_customer_numbers if num not in service_ids
]))
# Temp table cleanup
query = """
DROP TABLE tnd_charges;
"""
self.execute(query)
return list(set(service_ids + esp_customer_numbers))
return service_ids