def print_wireless_monthly_summary(month, year=None):
"""Get wireless monthly summary for all lines. Results will be printed
to console.
:param month: month (1 - 12) of the end date of billing cycle
:type month: int
:param year: year of the end of of billing cycle. Default to current year
:type year: int
:returns: None
"""
# year value default to current year
year = year or dt.date.today().year
if not BillingCycle.select().where(
db.extract_date('month', BillingCycle.end_date) == month,
db.extract_date('year', BillingCycle.end_date) == year).exists():
print('No charge summary found for {}/{}. Please split the '
'bill first'.format(year, month))
return
bc = BillingCycle.select().where(
db.extract_date('month', BillingCycle.end_date) == month,
db.extract_date('year', BillingCycle.end_date) == year).get()
print('\n--------------------------------------------------------------')
print(' Charge Summary for Billing Cycle {}'.format(bc.name))
print('--------------------------------------------------------------')
query = (User.select(User.name, User.number,
MonthlyBill.total).join(MonthlyBill).where(
MonthlyBill.billing_cycle_id == bc.id).naive())
wireless_total = 0
for user in query.execute():
print(' {:^18s} ({}) Total: {:.2f}'.format(
user.name, user.number, user.total))
wireless_total += user.total
print('--------------------------------------------------------------')
print('{:>47}: {:.2f}\n'.format('Wireless Total', wireless_total))
def print_wireless_monthly_details(month, year=None):
"""Get wireless monthly details for all lines. Results will be printed
to console.
:param month: month (1 - 12) of the end date of billing cycle
:type month: int
:param year: year of the end of of billing cycle. Default to current year
:type year: int
:returns: None
"""
# year value default to current year
year = year or dt.date.today().year
if not BillingCycle.select().where(
db.extract_date('month', BillingCycle.end_date) == month,
db.extract_date('year', BillingCycle.end_date) == year).exists():
print('No charge summary found for {}/{}. Please split the '
'bill first'.format(year, month))
return
bc = BillingCycle.select().where(
db.extract_date('month', BillingCycle.end_date) == month,
db.extract_date('year', BillingCycle.end_date) == year).get()
query = (User.select(
User.id, User.name, User.number, ChargeType.text.alias('charge_type'),
pw.fn.SUM(
Charge.amount).alias('total')).join(Charge).join(BillingCycle).
switch(Charge).join(ChargeType).join(ChargeCategory).where(
BillingCycle.id == bc.id,
ChargeCategory.category == 'wireless').group_by(
User, BillingCycle, ChargeType).order_by(User.id).naive())
current_user_num = ''
current_user_total = 0
wireless_total = 0
print('')
for user in query.execute():
if user.number != current_user_num:
if current_user_total:
print(' - {:40} {:.2f}\n'.format(
'Total', current_user_total))
wireless_total += current_user_total
current_user_num = user.number
current_user_total = 0
print(' {} ({})'.format(user.name, user.number))
print(' - {:40} {:.2f}'.format(user.charge_type, user.total))
current_user_total += user.total
if current_user_total:
print(' - {:40} {:.2f}\n'.format('Total', current_user_total))
wireless_total += current_user_total
print('{:>48}: {:.2f}\n'.format('Wireless Total', wireless_total))
def run(self, lag, force):
"""
:param lag: a list of lags indicating which bills to split
:type lag: list
:param force: a flag to force splitting the bill
:type force: bool
:returns: None
"""
if not self.login():
return
for i, (bc_name,
bill_statement_id) in enumerate(self.get_history_bills()):
# if lag is not empty, only split bills specified
if lag and (i not in lag) and not force:
continue
# check if billing cycle already exist
if BillingCycle.select().where(BillingCycle.name == bc_name):
print('\U000026A0 Billing Cycle {} already '
'processed.'.format(bc_name).encode("utf-8"))
continue
print('\U0001F3C3 Start splitting bill {}...'.format(
bc_name).encode("utf-8"))
self.split_bill(bc_name, bill_statement_id)
print('\U0001F3C1 Finished splitting bill {}.'.format(
bc_name).encode("utf-8"))
def add_onetime_fee(amount, charge_name):
"""Add one time charge to all users. For example, we can use this to add
a $2 annual Twilio fee"""
charge_type = slugify(charge_name)
ct = ChargeType.create(type=charge_type,
text=charge_name,
charge_category=1)
ct.save()
bc = BillingCycle.select().order_by(BillingCycle.id.desc()).get()
with db.atomic():
for user in User.select():
Charge.create(user=user, charge_type=ct, billing_cycle=bc,
amount=amount).save()
print('{} {} added for user {}'.format(amount, charge_name, user.name))
def notify_users_monthly_details(message_client,
payment_msg,
month,
year=None):
"""Calculate monthly charge details for users and notify them.
:param message_client: a message client to send text message
:type message_client: MessageClient
:param payment_message: text appended to charge details so that your
users know how to pay you.
:param type: str
:param month: month (1 - 12) of the end date of billing cycle
:type month: int
:param year: year of the end of of billing cycle. Default to current year
:type year: int
:returns: None
"""
# year value default to current year
year = year or dt.date.today().year
if not BillingCycle.select().where(
db.extract_date('month', BillingCycle.end_date) == month,
db.extract_date('year', BillingCycle.end_date) == year).exists():
print('No charge summary found for {}/{}. Please split the '
'bill first'.format(year, month))
return
bc = BillingCycle.select().where(
db.extract_date('month', BillingCycle.end_date) == month,
db.extract_date('year', BillingCycle.end_date) == year).get()
query = (User.select(
User.id, User.name, User.number, ChargeType.text.alias('charge_type'),
pw.fn.SUM(
Charge.amount).alias('total')).join(Charge).join(BillingCycle).
switch(Charge).join(ChargeType).join(ChargeCategory).where(
BillingCycle.id == bc.id,
ChargeCategory.category == 'wireless').group_by(
User, BillingCycle, ChargeType).order_by(User.id).naive())
current_user_num = -1
current_user_total = 0
messages = {}
message = ''
print('')
for user in query.execute():
if user.number != current_user_num:
if current_user_total:
message += ' - {:30} {:.2f} \U0001F911\n'.format(
'Total', current_user_total)
messages[current_user_num] = message
current_user_num = user.number
current_user_total = 0
message = ('Hi {} ({}),\nYour AT&T Wireless Charges '
'for {}:\n'.format(user.name, user.number, bc.name))
message += ' - {:30} {:.2f}\n'.format(user.charge_type, user.total)
current_user_total += user.total
if current_user_total:
message += ' - {:30} {:.2f} \U0001F911\n'.format(
'Total', current_user_total)
messages[current_user_num] = message
# print message for user to confirm
for num, msg in messages.items():
print(num)
print(msg)
notify = input('Notify (y/n)? ')
if notify in ('y', 'Y', 'yes', 'Yes', 'YES'):
body = '{}\n{}'.format(msg, payment_msg)
message_client.send_message(body=body, to=num)
logger.info('%s charge details sent to %s, body:\n%s', bc.name,
num, msg)
print('\U00002705 Message sent to {}\n'.format(num))
def split_previous_bill(self):
"""All parsing and wireless bill splitting happen here.
First Parse for U-verse TV, then U-verse Internet, and finally
Wireless. Wireless account monthly charges (after discount if there
is any) are split among all users.
"""
logger.info('Start processing new bill...')
# billing cycle
new_charge_title = self.browser.find_element_by_xpath(
"//h3[contains(text(), 'New charges for')]").text
billing_cycle_name = new_charge_title[16:]
if BillingCycle.select().where(
BillingCycle.name == billing_cycle_name):
logger.warning('Billing Cycle %s already processed.',
billing_cycle_name)
return
billing_cycle = BillingCycle.create(name=billing_cycle_name)
# parse user name and number
users = self.parse_user_info()
# set account holder
account_holder = users[0]
# ---------------------------------------------------------------------
# U-verse tv
# ---------------------------------------------------------------------
# beginning div
beginning_div_xpath = (
"div[starts-with(@class, 'MarLeft12 MarRight90') and "
"descendant::div[contains(text(), 'U-verse TV')]]")
# # end div
end_div_xpath = (
"div[@class='Padbot5 topDotBorder MarLeft12 MarRight90' and "
"descendant::div[contains(text(), 'Total U-verse TV Charges')]]")
charge_elems = self.browser.find_elements_by_xpath(
"//div[(starts-with(@class, 'accSummary') or "
"@id='UTV-monthly') and preceding-sibling::{} and "
"following-sibling::{}]".format(beginning_div_xpath,
end_div_xpath))
if charge_elems:
utv_charge_category, _ = ChargeCategory.get_or_create(
category='utv', text='U-verse TV')
for elem in charge_elems:
charge_type_text = elem.find_element_by_xpath("div[1]").text
if charge_type_text.startswith('Monthly Charges'):
charge_type_text = 'Monthly Charges'
m = re.search(
'Total {}.*?\$([0-9.]+)'.format(charge_type_text),
elem.text,
flags=re.DOTALL)
charge_total = float(m.group(1))
# save data to db
charge_type_name = slugify(charge_type_text)
# ChargeType
charge_type, _ = ChargeType.get_or_create(
type=charge_type_name,
text=charge_type_text,
charge_category=utv_charge_category)
# Charge
try:
new_charge = Charge(user=account_holder,
charge_type=charge_type,
billing_cycle=billing_cycle,
amount=charge_total)
new_charge.save()
except IntegrityError:
logger.warning(
'Trying to write duplicate charge record!\n%s',
new_charge.__dict__)
else:
logger.info('No U-verse TV Charge Elements Found, skipped.')
# ---------------------------------------------------------------------
# U-verse Internet
# ---------------------------------------------------------------------
# beginning div
beginning_div_xpath = (
"div[starts-with(@class, 'MarLeft12 MarRight90') and "
"descendant::div[contains(text(), 'U-verse Internet')]]")
# end div
end_div_xpath = (
"div[@class='Padbot5 topDotBorder MarLeft12 MarRight90' and "
"descendant::div[contains(text(), "
"'Total U-verse Internet Charges')]]")
charge_elems = self.browser.find_elements_by_xpath(
"//div[(starts-with(@class, 'accSummary') or "
"@id='UVI-monthly') and preceding-sibling::{} and "
"following-sibling::{}]".format(beginning_div_xpath,
end_div_xpath))
if charge_elems:
uvi_charge_category, _ = ChargeCategory.get_or_create(
category='uvi', text='U-verse Internet')
for elem in charge_elems:
charge_type_text = elem.find_element_by_xpath("div[1]").text
if charge_type_text.startswith('Monthly Charges'):
charge_type_text = 'Monthly Charges'
m = re.search(
'Total {}.*?\$([0-9.]+)'.format(charge_type_text),
elem.text,
flags=re.DOTALL)
charge_total = float(m.group(1))
# save data to db
charge_type_name = slugify(charge_type_text)
# ChargeType
charge_type, _ = ChargeType.get_or_create(
type=charge_type_name,
text=charge_type_text,
charge_category=uvi_charge_category)
# Charge
try:
new_charge = Charge(user=account_holder,
charge_type=charge_type,
billing_cycle=billing_cycle,
amount=charge_total)
new_charge.save()
except IntegrityError:
logger.warning(
'Trying to write duplicate charge record!\n%s',
new_charge.__dict__)
else:
logger.info('No U-verse Internet Charge Elements Found, skipped.')
# ---------------------------------------------------------------------
# Wireless
# ---------------------------------------------------------------------
# beginning div
charged_users = users[:1] # users who have a positive balance
beginning_div_xpath = (
"div[@class='MarLeft12 MarRight90 ' and "
"descendant::div[contains(text(), '{name} {num}')]]")
# end div
end_div_xpath = (
"div[@class='topDotBorder accSummary MarLeft12 "
"Padbot5 botMar10 botMar23ie' and "
"descendant::div[contains(text(), 'Total for {num}')]]")
# first parse charges under account holder
charge_elems = self.browser.find_elements_by_xpath(
"//div[starts-with(@class, 'accSummary') and "
"preceding-sibling::{} and following-sibling::{}]".format(
beginning_div_xpath.format(name=account_holder.name,
num=account_holder.number),
end_div_xpath.format(num=account_holder.number)))
if charge_elems:
wireless_charge_category, _ = ChargeCategory.get_or_create(
category='wireless', text='Wireless')
for elem in charge_elems:
charge_type_text = elem.find_element_by_xpath("div[1]").text
offset = 0
if charge_type_text.startswith('Monthly Charges'):
charge_type_text = 'Monthly Charges'
# get account monthly charge and discount
act_m_elem = elem.find_element_by_xpath("div[4]/div[1]")
m = re.search('.*?\$([0-9.]+)', act_m_elem.text, re.DOTALL)
w_act_m = float(m.group(1))
m = re.search('National Account Discount.*?\$([0-9.]+)',
elem.text,
flags=re.DOTALL)
w_act_m_disc = float(m.group(1)) if m else 0
offset = w_act_m - w_act_m_disc
m = re.search(
'Total {}.*?\$([0-9.]+)'.format(charge_type_text),
elem.text,
flags=re.DOTALL)
charge_total = float(m.group(1)) - offset
# save data to db
charge_type_name = slugify(charge_type_text)
# ChargeType
charge_type, _ = ChargeType.get_or_create(
type=charge_type_name,
text=charge_type_text,
charge_category=wireless_charge_category)
# Charge
try:
new_charge = Charge(user=account_holder,
charge_type=charge_type,
billing_cycle=billing_cycle,
amount=charge_total)
new_charge.save()
except IntegrityError:
logger.warning(
'Trying to write duplicate charge record!\n%s',
new_charge.__dict__)
else:
raise ParsingError('No charges found for account holder.')
# iterate regular users
for user in users[1:]:
charge_total = 0
charge_elems = self.browser.find_elements_by_xpath(
"//div[starts-with(@class, 'accSummary') and "
"preceding-sibling::{} and following-sibling::{}]".format(
beginning_div_xpath.format(name=user.name,
num=user.number),
end_div_xpath.format(num=user.number)))
for elem in charge_elems:
charge_type_text = elem.find_element_by_xpath("div[1]").text
if charge_type_text.startswith('Monthly Charges'):
charge_type_text = 'Monthly Charges'
m = re.search(
'Total {}.*?\$([0-9.]+)'.format(charge_type_text),
elem.text,
flags=re.DOTALL)
charge_total = float(m.group(1)) - offset
# save data to db
charge_type_name = slugify(charge_type_text)
# ChargeType
charge_type, _ = ChargeType.get_or_create(
type=charge_type_name,
text=charge_type_text,
charge_category=wireless_charge_category)
# Charge
try:
new_charge = Charge(user=user,
charge_type=charge_type,
billing_cycle=billing_cycle,
amount=charge_total)
new_charge.save()
except IntegrityError:
logger.warning(
'Trying to write duplicate charge record!\n%s',
new_charge.__dict__)
if charge_total > 0:
charged_users.append(user)
# update share of account monthly charges for each line
# also calculate total wireless charges (for verification later)
act_m_share = (w_act_m - w_act_m_disc) / len(charged_users)
wireless_total = 0
for user in charged_users:
# ChargeType
charge_type, _ = ChargeType.get_or_create(
type='wireless-acount-monthly-charges-share',
text='Account Monthly Charges Share',
charge_category=wireless_charge_category)
# Charge
try:
new_charge = Charge(user=user,
charge_type=charge_type,
billing_cycle=billing_cycle,
amount=act_m_share)
new_charge.save()
except IntegrityError:
logger.warning('Trying to write duplicate charge record!\n%s',
new_charge.__dict__)
else:
user_total = Charge.select(
pw.fn.Sum(Charge.amount).alias('total')).join(
ChargeType,
on=Charge.charge_type_id == ChargeType.id).where(
(Charge.user == user),
Charge.billing_cycle == billing_cycle,
ChargeType.charge_category ==
wireless_charge_category)
wireless_total += user_total[0].total
# now that we have wireless_total calculated from user charges
# let's compare if it matches with what the bill says
wireless_total_elem = self.browser.find_element_by_xpath(
"//div[preceding-sibling::"
"div[contains(text(), 'Total Wireless Charges')]]")
bill_wireless_total = float(wireless_total_elem.text.strip('$'))
if abs(bill_wireless_total - wireless_total) > 0.01:
raise CalculationError(
'Wireless total calculated does not match with the bill')
logger.info('Wireless charges calculation results verified.')
logger.info('Finished procesessing bill %s.', billing_cycle_name)
