def main():
fee_logger = logbook.Logger('main')
const = constants.Constants()
database = 'well.sqlite'
fee_logger.notice('Connecting to the database')
db = sqlite3.connect(database)
db.row_factory = sqlite3.Row
cur = db.cursor()
fee_date: str = utils.prompt_for_current_date("Date paid")
fee_logger.trace(f'fee_date: {fee_date}')
fee_amount: int = utils.prompt_for_amount("Amount")
# and make this amount negative to show as paid
fee_amount = fee_amount * -1
fee_logger.trace(f'fee amount: {fee_amount}')
fee_note = utils.prompt_for_notes("Notes")
fee_logger.trace(f'fee_note: {fee_note}')
# enter this into the activity log prior to entering each
# 1/4 share individually
exec_str = f"""
INSERT INTO activity (date, type, amount, note)
VALUES (?, ?, ?, ?)
"""
params = (fee_date, const.administrative_fee_paid, fee_amount, fee_note)
cur.execute(exec_str, params)
# save, then close the cursor and db
db.commit()
cur.close()
db.close()
def main():
database = 'well.sqlite'
logger = logbook.Logger('pge_bill_paid')
utils.backup_file(database)
db = sqlite3.connect(database)
db.row_factory = sqlite3.Row
cur = db.cursor()
# get date_paid
payment_date = utils.prompt_for_current_date("Date paid")
# get amount
payment_amount = utils.prompt_for_amount("Amount paid")
const = constants.Constants()
# mark the bill paid in the activity account
payment_amount = payment_amount * -1
exec_str = f"""
INSERT INTO activity (date, type, amount, note)
VALUES (?, ?, ?, ?)
"""
params = (payment_date, const.pge_bill_paid, payment_amount, "PGE bill paid")
logger.trace(params)
cur.execute(exec_str, params)
# save, then close the cursor and db
db.commit()
cur.close()
db.close()
def main():
savings_logger = logbook.Logger("get_savings_bal.log")
database = "well.sqlite"
db = sqlite3.connect(database)
db.row_factory = sqlite3.Row
cur = db.cursor()
exec_str = f"""
SELECT SUM(amount)
FROM activity
WHERE type = (?)
OR type = (?)
OR type = (?)
"""
const = constants.Constants()
params = (
const.savings_deposit_made,
const.savings_disbursement,
const.savings_dividend,
)
cur.execute(exec_str, params)
current_savings_balance = cur.fetchone()[0]
savings_logger.trace(f"current_savings_balance: {current_savings_balance}")
print(f"===============================================")
print(f" current savings balance: $ {current_savings_balance/100:,.2f}")
print(f"===============================================")
# close the cursor and db
cur.close()
db.close()
def set_current_usage(acct_obj):
logger = logbook.Logger("set_current_usage")
const = constants.Constants()
usage = acct_obj.latest_reading - acct_obj.previous_reading
logger.trace(f"raw usage: {usage} -- {acct_obj.reads_in}")
if acct_obj.reads_in == "cubic feet":
usage = round((usage * const.gallons_per_cubic_foot), 2)
logger.trace(f"CONVERTED usage: {usage:.2f}")
acct_obj.current_usage = usage
def main():
payment_logger = logbook.Logger('payment_recd')
db = "well.sqlite"
database = sqlite3.connect(f"{db}")
cur = database.cursor()
acct = utils.prompt_for_account("Please choose an account", cur)
#
# show the account's current balance here
# fetch balance and display
cur_balance = utils.get_acct_balance(acct, cur)
if cur_balance == 0:
print(f"This account has a zero balance.")
exit(0)
date = utils.prompt_for_current_date("Payment date")
# fetch amount and flip the sign
print(f"Current balance: ${cur_balance:.2f}")
amt = utils.prompt_for_amount("Payment amount")
payment_logger.debug(f"get_amount just returned this amount: {amt}")
amt *= -1
# cobble together the account note
# changed this to be multi-line so the payment method can
# a little longer .... 2019.06.21
notes = "Payment on account|"
notes += utils.prompt_for_notes("Payment notes")
payment_logger.debug(date)
payment_logger.debug(amt)
payment_logger.debug(acct)
payment_logger.debug(notes)
# backup the file prior to adding any data
utils.backup_file(db)
const = constants.Constants()
# insert the payment into the DB
cur.execute(
"INSERT INTO activity (date, acct, type, amount, note) VALUES (?, ?, ?, ?, ?)",
(date, acct, const.payment_received, amt, notes),
)
# fetch updated balance and display
cur_balance = utils.get_acct_balance(acct, cur)
print(f"Updated balance: ${cur_balance:.2f}\n")
# save, then close the cursor and database
database.commit()
cur.close()
database.close()
def get_payments(cur, acct_id, date):
const = constants.Constants()
exec_str = f"""
SELECT SUM(amount)
FROM activity
WHERE acct = ?
AND type = ?
AND date > ?
"""
params = (acct_id, const.payment_received, date)
row = cur.execute(exec_str, params)
return row.fetchone()[0]
def get_last_pge_bill_recd_amount(cur):
const = constants.Constants()
exec_str: str = f"""
SELECT amount
FROM activity
where type = ?
ORDER BY date
DESC
LIMIT 1
"""
params = (const.pge_bill_received, )
row = cur.execute(exec_str, params)
amount = row.fetchone()[0]
return amount
def get_pge_share(cur, acct_id, date):
logger = logbook.Logger("get_pge_share")
const = constants.Constants()
logger.trace(f"{acct_id} -- {date}")
exec_str = f"""
SELECT amount
FROM activity
WHERE acct = ?
AND type = ?
AND date >= ?
"""
params = (acct_id, const.pge_bill_share, date)
row = cur.execute(exec_str, params)
return row.fetchone()[0]
def get_last_pge_bill_recd_date(cur):
logger = logbook.Logger("get_last_pge_bill_recd_date")
logger.trace(f"entering get_last_pge_bill_recd_date")
const = constants.Constants()
exec_str = f"""
SELECT date
FROM activity
WHERE type = (?)
ORDER BY date
DESC
"""
params = (const.pge_bill_received, )
cur.execute(exec_str, params)
row = cur.fetchone()
return row["date"]
def main():
fee_logger = logbook.Logger('main')
const = constants.Constants()
database = 'well.sqlite'
fee_logger.notice('Connecting to the database')
db = sqlite3.connect(database)
db.row_factory = sqlite3.Row
cur = db.cursor()
fee_date: str = utils.prompt_for_current_date("Fee date")
fee_logger.trace(f'fee_date: {fee_date}')
fee_amount: int = utils.prompt_for_amount("Fee amount")
fee_logger.trace(f'fee amount: {fee_amount}')
qtr_share: int = int(round(fee_amount / 4))
fee_logger.trace(f'qtr_share (rounded): {qtr_share}')
fee_note = utils.prompt_for_notes("Notes")
fee_logger.trace(f'fee_note: {fee_note}')
# enter this into the activity log prior to entering each
# 1/4 share individually
exec_str = f"""
INSERT INTO activity (date, type, amount, note)
VALUES (?, ?, ?, ?)
"""
params = (fee_date, const.administrative_fee_received, fee_amount,
fee_note)
cur.execute(exec_str, params)
exec_str = "SELECT * FROM account"
cur.execute(exec_str)
rows = cur.fetchall()
for r in rows:
if r["active"] == "no":
fee_logger.trace(f"Account {r['acct_id']} currently INACTIVE")
continue
exec_str = f"""
INSERT INTO activity (date, acct, type, amount, note)
VALUES (?,?,?,?,?)
"""
params = (fee_date, r["acct_id"], const.administrative_fee_share,
qtr_share, fee_note)
cur.execute(exec_str, params)
# save, then close the cursor and db
db.commit()
cur.close()
db.close()
def get_savings_balance(cur):
logger = logbook.Logger("get_savings_balance")
logger.trace("Entering get_savings_balance()")
exec_str = f"""
SELECT SUM(amount)
FROM activity
WHERE type = (?)
OR type = (?)
OR type = (?)
"""
const = constants.Constants()
params = (
const.savings_deposit_made,
const.savings_disbursement,
const.savings_dividend,
)
row = cur.execute(exec_str, params)
cur_balance = row.fetchone()[0]
return cur_balance
def print_savings_account_balance(cur):
logger = logbook.Logger("savings_balance")
logger.notice("entering print_savings_account_balance")
exec_str = f"""
SELECT SUM(amount)
FROM activity
WHERE type = (?)
OR type = (?)
OR type = (?)
"""
const = constants.Constants()
params = (
const.savings_deposit_made,
const.savings_disbursement,
const.savings_dividend,
)
row = cur.execute(exec_str, params)
value = row.fetchone()[0]
logger.trace(f"value: {value}")
print(f"savings account balance: {(value / 100):9.2f}")
def main():
database = "well.sqlite"
# set up for logging
LEVELS = {
"debug": logging.DEBUG,
"info": logging.INFO,
"warning": logging.WARNING,
"error": logging.ERROR,
"critical": logging.CRITICAL,
}
if len(sys.argv) > 1:
level_name = sys.argv[1]
level = LEVELS.get(level_name, logging.NOTSET)
logging.basicConfig(level=level)
logger = logging.getLogger()
logger.debug("Entering main")
db = sqlite3.connect(database)
db.row_factory = sqlite3.Row
cur = db.cursor()
rebate_date = utils.prompt_for_current_date("Rebate date")
rebate_amount = utils.prompt_for_amount("Rebate amount")
rebate_note = utils.prompt_for_notes("Notes")
const = constants.Constants()
exec_str = f"""
INSERT INTO activity (date, type, amount, note) VALUES (?, ?, ?, ?)
"""
params = (rebate_date, const._misc_rebate_received, rebate_amount,
rebate_note)
cur.execute(exec_str, params)
logger.debug("attempting to backup the database file now")
utils.backup_file(database)
# save, then close the cursor and db
db.commit()
cur.close()
db.close()
def get_new_charges(cur, acct_id, date):
logger = logbook.Logger("get_new_charges")
logger.notice("entering get_new_charges")
const = constants.Constants()
exec_str = f"""
SELECT SUM(amount)
FROM activity
WHERE acct = ?
AND type IN (?, ?, ?)
AND date > ?
"""
params = (
acct_id,
const.pge_bill_share,
const.savings_assessment,
const.administrative_fee_share,
date,
)
logger.trace(f"{exec_str}")
logger.trace(params)
row = cur.execute(exec_str, params)
return row.fetchone()[0]
def main():
database = "well.sqlite"
logger = logbook.Logger("reports")
# this dictionary will hold the variables that are needed
# to process each account's nothing activity
# this is outside any loop so that the info only
# has to be culled once ... rather than each time the
# loop is run for each account
monthly_global_variables = {}
db = sqlite3.connect(database)
db.row_factory = sqlite3.Row
cur = db.cursor()
last_pge_bill_recd_date = utils.get_last_pge_bill_recd_date(cur)
logger.trace(f"last_pge_bill_recd_date: {last_pge_bill_recd_date}")
monthly_global_variables[
"last_pge_bill_recd_date"] = last_pge_bill_recd_date
last_pge_bill_recd_amount = utils.get_last_pge_bill_recd_amount(cur)
logger.trace(f"last_pge_bill_recd_amount: {last_pge_bill_recd_amount}")
monthly_global_variables[
"last_pge_bill_recd_amount"] = last_pge_bill_recd_amount
monthly_global_variables["ttl_monthly_usage"] = 0.0
savings_balance = utils.get_savings_balance(cur)
# leave this in for testing:
# savings_balance = 1000000
if savings_balance < 1000000:
monthly_global_variables["assessment_needed"] = True
else:
monthly_global_variables["assessment_needed"] = False
# collect relevant dates
dates = utils.get_last_two_reading_dates(cur)
logger.trace(f"dates --> {dates}")
start_date = dates[1]
logger.trace(f"start_date --> {start_date}")
monthly_global_variables["start_date"] = start_date
end_date = dates[0]
logger.trace(f"end_date --> {end_date}")
monthly_global_variables["end_date"] = end_date
readable_start_date = utils.make_date_readable(start_date)
monthly_global_variables["readable_start_date"] = readable_start_date
readable_end_date = utils.make_date_readable(end_date)
monthly_global_variables["readable_end_date"] = readable_end_date
monthly_global_variables[
"current_savings_balance"] = utils.get_savings_balance(cur)
const = constants.Constants()
exec_str = f"""
SELECT *
FROM activity
WHERE (type = ? OR type = ? OR type = ?)
AND (date > ?)
"""
params = (
const.savings_deposit_made,
const.savings_dividend,
const.savings_disbursement,
monthly_global_variables["start_date"],
)
logger.trace(params)
rows = cur.execute(exec_str, params)
savings_data_list = []
for row in rows:
logger.trace(
f"{row['type']} - {row['date']} - {row['amount']} - {row['note']}")
data_list = []
if row["type"] == const.savings_deposit_made:
data_list.append("Deposit made")
elif row["type"] == const.savings_dividend:
data_list.append("Dividend to account")
data_list.append(row["date"])
data_list.append(row["amount"] / 100)
data_list.append(row["note"])
savings_data_list.append(data_list)
logger.trace(f"{savings_data_list}")
acct_list = []
cur.execute("SELECT * FROM account")
rows = cur.fetchall()
for r in rows:
logger.trace(
f"======================================================================="
)
if r["active"] == "no":
logger.trace(f"account {r['acct_id']} is INACTIVE")
continue
# init the accounts
logger.trace(
f"init an account ... add to acct list ... {r['last_name']} ... {r['acct_id']}"
)
acct_obj = account.Account(
r["acct_id"],
r["first_name"],
r["last_name"],
r["file_alias"],
r["address"],
r["reads_in"],
r["master"],
)
acct_list.append(acct_obj)
# set readings
utils.set_last_two_readings(cur, acct_obj)
# set current usage
utils.set_current_usage(acct_obj)
# calculate total usage for the community
# ttl_monthly_usage = ttl_monthly_usage + acct_obj.current_usage
monthly_global_variables["ttl_monthly_usage"] += acct_obj.current_usage
# get and set the previous balance
# TODO: i think this needs to use the latest reading date...
logger.trace(f" * * * Setting the prev_balance")
logger.trace(f"start_date: {start_date}")
logger.trace(f" * * * Setting the prev_balance")
logger.trace(
f"get_prev_balance is going to use {monthly_global_variables['end_date']}"
)
prev_balance = utils.get_prev_balance(
cur, acct_obj.acct_id, monthly_global_variables["start_date"])
if prev_balance is None:
prev_balance = 0
acct_obj.prev_balance = prev_balance
logger.trace(f"prev_balance: {prev_balance}")
# adjustments?
adjustments = utils.get_adjustments(cur, acct_obj.acct_id,
last_pge_bill_recd_date)
if adjustments is None:
adjustments = 0
acct_obj.adjustments = adjustments
logger.trace(f"adjustments: {adjustments}")
# fetch pge bill share
pge_bill_share = utils.get_pge_share(cur, acct_obj.acct_id,
last_pge_bill_recd_date)
if pge_bill_share is None:
pge_bill_share = 0
acct_obj.pge_bill_share = pge_bill_share
logger.trace(f"pge_bill_share: {pge_bill_share}")
# check for any payments made
payments = utils.get_payments(cur, acct_obj.acct_id,
monthly_global_variables["start_date"])
if payments is None:
payments = 0
acct_obj.payments = payments
logger.trace(f"==========================================")
logger.trace(f"payments: {payments}")
logger.trace(f"==========================================")
# this is just new pge shares and assessments and new fees...
# new charges
new_charges = utils.get_new_charges(
cur, acct_obj.acct_id, monthly_global_variables["start_date"])
if new_charges is None:
new_charges = 0
acct_obj.new_charges = new_charges
logger.trace(f"new charges: {acct_obj.new_charges}")
acct_obj.current_balance = (acct_obj.prev_balance +
acct_obj.adjustments + acct_obj.payments +
acct_obj.new_charges)
logger.trace(f"{monthly_global_variables['ttl_monthly_usage']}")
for acct in acct_list:
utils.generate_pdf(cur, acct, monthly_global_variables,
savings_data_list)
# save, then close the cursor and db
db.commit()
cur.close()
db.close()
def main():
logger = logbook.Logger('savings_dep')
database = 'well.sqlite'
utils.backup_file(database)
db = sqlite3.connect(database)
db.row_factory = sqlite3.Row
cur = db.cursor()
const = constants.Constants()
exec_str = f"""
SELECT SUM(amount)
FROM (SELECT amount
FROM activity
WHERE type = (?)
ORDER BY date
DESC
LIMIT 4)
"""
params = (const.savings_assessment, )
logger.trace(f"params: {params}")
cur.execute(exec_str, params)
last_assessment_ttl = cur.fetchone()[0]
last_assessment_ttl = last_assessment_ttl / 100
print(f"===============================================")
print(f" last assessments totaled: ${last_assessment_ttl:12.2f}")
exec_str = f"""
SELECT SUM(amount)
FROM activity
WHERE type = (?)
OR type = (?)
OR type = (?)
"""
params = (const.savings_deposit_made, const.savings_disbursement,
const.savings_dividend)
logger.trace(f"params: {params}")
cur.execute(exec_str, params)
current_savings_balance = cur.fetchone()[0]
if current_savings_balance is None:
current_savings_balance = 0
logger.trace(f"current_savings_balance: {current_savings_balance}")
print(f"===============================================")
print(f" current savings balance: ${current_savings_balance / 100:12,.2f}")
print(f"===============================================")
# get date_paid
deposit_date = utils.prompt_for_current_date("Date deposit made")
# get amount
deposit_amount = utils.prompt_for_amount("Amount of deposit")
# prompt for notes ... 'Dep for Jan 2019' ... or similar
note = utils.prompt_for_notes('Notes for this deposit')
exec_str = f"""
INSERT INTO activity (date, type, amount, note) VALUES (?, ?, ?, ?)
"""
params = (deposit_date, const.savings_deposit_made, deposit_amount, note)
cur.execute(exec_str, params)
exec_str = f"""
SELECT SUM(amount)
FROM activity
WHERE type = (?)
OR type = (?)
OR type = (?)
"""
params = (const.savings_deposit_made, const.savings_disbursement,
const.savings_dividend)
logger.trace(f"{params}")
cur.execute(exec_str, params)
current_savings_balance = cur.fetchone()[0]
logger.trace(f"current_savings_balance: {current_savings_balance}")
print(f"===============================================")
print(f" current savings balance: ${current_savings_balance / 100: ,.2f}")
print(f"===============================================")
# save, then close the cursor and db
db.commit()
cur.close()
db.close()
def main():
database = 'well.sqlite'
logger = logbook.Logger("savings_dividend")
logger.debug('Entering main')
utils.backup_file(database)
db = sqlite3.connect(database)
db.row_factory = sqlite3.Row
cur = db.cursor()
# check current balance here
const = constants.Constants()
exec_str = f"""
SELECT SUM(amount)
FROM activity
WHERE type = (?)
OR type = (?)
OR type = (?)
"""
params = (const.savings_deposit_made, const.savings_disbursement,
const.savings_dividend)
logger.debug(f"params: {params}")
cur.execute(exec_str, params)
current_savings_balance = cur.fetchone()[0]
logger.debug(f"current_savings_balance: {current_savings_balance}")
print(f"===============================================")
print(f" current savings balance: ${current_savings_balance / 100:,.2f}")
print(f"===============================================")
# get date_paid
dividend_date = utils.prompt_for_current_date("Date of dividend")
# get amount
dividend_amount = utils.prompt_for_amount("Amount of dividend")
# prompt for notes ... 'Dep for Jan 2019' ... or similar
note = f"Savings dividend"
# enter into the activity log
exec_str = f"""
INSERT INTO activity (date, type, amount, note)
VALUES (?, ?, ?, ?)
"""
params = (dividend_date, const.savings_dividend, dividend_amount, note)
cur.execute(exec_str, params)
# check the balance again
exec_str = f"""
SELECT SUM(amount)
FROM activity
WHERE type = (?)
OR type = (?)
OR type = (?)
"""
params = (const.savings_deposit_made, const.savings_disbursement,
const.savings_dividend)
cur.execute(exec_str, params)
current_savings_balance = cur.fetchone()[0]
logger.debug(f"current_savings_balance: {current_savings_balance}")
print(f"===============================================")
print(f" current savings balance: ${current_savings_balance / 100: ,.2f}")
print(f"===============================================")
# save, then close the cursor and db
db.commit()
cur.close()
db.close()
def generate_pdf(cur, acct_obj, monthly_global_variables, savings_data_list):
logger = logbook.Logger("generate_pdf")
logger.trace(f"entering generate_pdf")
# let's get a few things set up here ...
dt = datetime.now().strftime("%Y.%m.%d")
stub = f"{acct_obj.file_alias}--{dt}"
output_file = generate_unique_pdf_filename(stub)
pdf = fpdf.FPDF("P", "pt", "Letter")
pdf.add_page()
# #######################################################
# HEADERS Section
# #######################################################
pdf.set_font("Arial", "B", 16)
lh = pdf.font_size
pdf.cell(0, 0, f"{acct_obj.fn} {acct_obj.ln}")
pdf.ln(lh)
pdf.cell(0, 0, f"{acct_obj.addr}")
pdf.ln(lh)
pdf.cell(0, 0, "Aromas, CA 95004")
pdf.ln(lh)
pdf.ln(lh)
pdf.ln(lh)
# #######################################################
# ACCOUNT OVERVIEW Section
# #######################################################
epw = pdf.w - (2 * pdf.l_margin)
col_width = epw / 5
pdf.set_fill_color(220, 220, 220)
pdf.set_font("Courier", "", 10)
pdf.cell(col_width, lh, f"Previous Balance", border="LTR", align="C")
pdf.cell(col_width, lh, f"Payments", border="LTR", align="C")
pdf.cell(col_width, lh, f"Adjustments", border="LTR", align="C")
pdf.cell(col_width, lh, f"New Charges", border="LTR", align="C")
pdf.cell(col_width,
lh,
f"Account Balance",
border="LTR",
align="C",
fill=True)
pdf.ln(lh)
# data
pdf.set_font("Courier", "", 10)
value = acct_obj.prev_balance
if value < 0:
# turns this: $-6.95
# into this: -$6.95
value = value * -1
pdf.cell(col_width,
lh,
f"-${value / 100:.2f}",
border="LBR",
align="C")
else:
pdf.cell(col_width, lh, f"${value / 100:.2f}", border="LBR", align="C")
value = acct_obj.payments
if value < 0:
value = value * -1
pdf.cell(col_width,
lh,
f"-${value / 100:.2f}",
border="LBR",
align="C")
else:
pdf.cell(col_width, lh, f"${value / 100:.2f}", border="LBR", align="C")
value = acct_obj.adjustments
if value < 0:
value = value * -1
pdf.cell(col_width,
lh,
f"-${value / 100:.2f}",
border="LBR",
align="C")
else:
pdf.cell(col_width, lh, f"${value / 100:.2f}", border="LBR", align="C")
# this one should be okay as the new charges should never be negative
pdf.cell(col_width,
lh,
f"${acct_obj.new_charges / 100:.2f}",
border="LBR",
align="C")
pdf.set_font("Courier", "B", 12)
value = acct_obj.current_balance
if value < 0:
value = value * -1
pdf.cell(col_width,
lh,
f"-${value / 100:.2f}",
border="LBR",
align="C",
fill=True)
else:
pdf.cell(col_width,
lh,
f"${value / 100:.2f}",
border="LBR",
align="C",
fill=True)
pdf.ln(lh)
# #######################################################
# USAGE Section
# #######################################################
pdf.set_font("Arial", "B", 12)
pdf.ln(pdf.font_size)
lh = pdf.font_size
pdf.ln()
col_width = epw / 2
pdf.cell(col_width, lh, f"Your usage")
pdf.set_font("Arial", "", 10)
pdf.cell(
0,
lh,
f"{monthly_global_variables['readable_start_date']} to {monthly_global_variables['readable_end_date']}",
align="R",
)
pdf.ln(lh)
pdf.ln(2)
pdf.line(pdf.l_margin, pdf.y, pdf.w - pdf.r_margin, pdf.y)
pdf.ln(lh)
col_width = epw / 4
pdf.set_font("Arial", "", 12)
lh = lh + 2
pdf.cell(col_width, 0, f"Latest reading")
pdf.cell(col_width, 0, f"{acct_obj.latest_reading}", align="R")
pdf.ln(lh)
pdf.cell(col_width, 0, f"Previous reading")
pdf.cell(col_width, 0, f"{acct_obj.previous_reading}", align="R")
pdf.ln(lh)
# cubic feet
const = constants.Constants()
if acct_obj.reads_in == "cubic feet":
pdf.cell(col_width, 0, f"Difference (cubic feet)")
pdf.cell(
col_width,
0,
f"{acct_obj.latest_reading - acct_obj.previous_reading}",
align="R",
)
pdf.ln(lh)
pdf.cell(
col_width,
0,
f"Usage (gallons = {acct_obj.latest_reading - acct_obj.previous_reading} x "
+ f"{const.gallons_per_cubic_foot})",
)
pdf.cell(col_width, 0, f"{acct_obj.current_usage:.2f}", align="R")
pdf.ln(lh)
else:
pdf.cell(col_width, 0, f"Difference")
pdf.cell(
col_width,
0,
f"{acct_obj.latest_reading - acct_obj.previous_reading}",
align="R",
)
pdf.ln(lh)
pdf.cell(col_width, 0, f"Total well usage (gallons)")
pdf.cell(col_width,
0,
f"{monthly_global_variables['ttl_monthly_usage']:.2f}",
align="R")
pdf.ln(lh)
current_usage_percent = (round(
(acct_obj.current_usage /
monthly_global_variables["ttl_monthly_usage"]), 4) * 100)
pdf.cell(col_width, 0, f"Usage percent")
pdf.cell(col_width, 0, f"{current_usage_percent:.2f}", align="R")
pdf.ln(lh)
pdf.line(pdf.l_margin, pdf.y, pdf.w - pdf.r_margin, pdf.y)
pdf.ln(lh)
pdf.cell(col_width, 0, f"PGE bill")
pdf.cell(
col_width,
0,
f"${monthly_global_variables['last_pge_bill_recd_amount'] / 100:.2f}",
align="R",
)
pct = round(current_usage_percent, 2)
pdf.ln(lh)
share = (
(monthly_global_variables["last_pge_bill_recd_amount"]) * pct) / 10000
share = round(share, 2)
pdf.cell(
col_width,
0,
f"Your share of PGE bill (${monthly_global_variables['last_pge_bill_recd_amount'] / 100:.2f} x {pct / 100:0.4f})",
)
pdf.cell(col_width, 0, f" ${share:.2f}", align="R")
if monthly_global_variables["assessment_needed"]:
pdf.ln(lh)
pdf.line(pdf.l_margin, pdf.y, pdf.w - pdf.r_margin, pdf.y)
pdf.ln(lh)
pdf.cell(
col_width * 2,
0,
f"Savings assessment ({acct_obj.current_usage:.2f} gallons x ${const.assessment_per_gallon} per gallon)",
)
pdf.cell(
col_width,
0,
f" ${acct_obj.current_usage * const.assessment_per_gallon:.2f}",
align="R",
)
# #######################################################
# ACCOUNT ACTIVITY Section
# #######################################################
pdf.set_font("Arial", "B", 12)
lh = pdf.font_size
pdf.ln(lh)
pdf.ln(lh)
pdf.cell(0, lh, f"Account activity")
pdf.ln(lh)
pdf.ln(2)
pdf.line(pdf.l_margin, pdf.y, pdf.w - pdf.r_margin, pdf.y)
pdf.ln(lh)
# changed date from > to >=
exec_str: str = f"""
SELECT *
FROM activity
WHERE (acct = ?)
AND (date >= ?)
"""
params = (acct_obj.acct_id, monthly_global_variables["start_date"])
rows = cur.execute(exec_str, params)
pdf.set_font("Arial", "", 12)
col_width = epw / 6
for row in rows:
pdf.cell(col_width, 0, f"{make_date_readable(row['date'])}")
# note ... chk to see if it needs chopping up
# splits the string on the pipe (|) character
# then displays them on separate lines
notes = row["note"].split("|")
number_of_notes = len(notes)
# TODO: there is some repeated code here ... see if you can suss this out
# if number_of_notes == 1:
# # pdf.cell(col_width * 2, 0, f"{row['note']}")
# pdf.cell(col_width * 2, 0, f"{notes[0]}")
# pdf.cell(col_width, 0, f"${row['amount'] / 100:.2f}", align="R")
# pdf.ln(lh + 2)
# else:
# pdf.cell(col_width * 2, 0, f"{notes[0]}")
# pdf.cell(col_width, 0, f"${row['amount'] / 100:.2f}", align="R")
# pdf.ln(lh + 2)
#
# for i in range(1, number_of_notes):
# pdf.cell(col_width, 0, " ")
# pdf.cell(col_width * 2, 0, f"{notes[i]}")
# pdf.ln(lh + 2)
# TODO: this is to replace the above .... test on 6/20
pdf.cell(col_width * 2, 0, f"{notes[0]}")
value = row["amount"] / 100
# if line_item amount is negative .... put the minus sign outside the
# $ sign .... -$6.93
if value < 0:
value = value * -1
pdf.cell(col_width, 0, f"-${value:.2f}", align="R")
else:
pdf.cell(col_width, 0, f"${value:.2f}", align="R")
pdf.ln(lh + 2)
if number_of_notes > 1:
for i in range(1, number_of_notes):
pdf.cell(col_width, 0, " ")
pdf.cell(col_width * 2, 0, f"{notes[i]}")
pdf.ln(lh + 2)
# #######################################################
# SAVINGS Section
# #######################################################
pdf.set_font("Arial", "B", 12)
lh = pdf.font_size
pdf.ln(lh)
pdf.cell(0, lh, f"Savings activity")
pdf.ln(lh)
pdf.ln(2)
pdf.line(pdf.l_margin, pdf.y, pdf.w - pdf.r_margin, pdf.y)
pdf.ln(lh)
pdf.set_font("Arial", "", 12)
for item in savings_data_list:
col_width = epw / 6
pdf.cell(col_width, 0, f"{make_date_readable(item[1])}")
pdf.cell(col_width * 1.5, 0, f"{item[0]}")
pdf.cell(col_width * 1.5, 0, f"{item[3]}")
pdf.cell(col_width, 0, f"${item[2]:.2f}", align="R")
pdf.ln(lh + 2)
# bal = get_savings_balance(logger, cur)
# bal = bal / 100
pdf.ln(lh)
pdf.cell(
0,
0,
f"Current savings account balance: ${monthly_global_variables['current_savings_balance'] / 100:.2f}",
)
# #######################################################
# FOOTERS Section
# #######################################################
# 716 is as low as i can get this
# without pushing to the next page
pdf.set_y(716)
pdf.set_font("Arial", "i", 8)
col_width = epw / 8
pdf.ln()
pdf.cell(col_width, 0, f"Google Pay:")
pdf.cell(col_width, 0, f"*****@*****.**")
pdf.ln(lh - 3)
pdf.cell(col_width, 0, f"PayPal:")
pdf.cell(col_width, 0, f"www.paypal.me/Rmeineke")
pdf.cell(0, 0, "Questions or comments to: [email protected]", align="R")
pdf.ln(lh - 3)
pdf.cell(col_width, 0, f"Venmo:")
pdf.cell(col_width, 0, f"www.venmo.com/Rmeineke")
generated_str = f"File generated: {dt}"
pdf.cell(0, 0, generated_str, align="R")
# write the file
pdf.output(output_file, "F")
backup_filename = f"backups/{output_file}"
shutil.copyfile(output_file, backup_filename)
logger.trace(f"leaving generate_pdf")
def main():
logger = logbook.Logger("bill_recd")
database = "well.sqlite"
# make sure this gets backed up prior to any
# writing of the db
utils.backup_file(database)
db = sqlite3.connect(database)
db.row_factory = sqlite3.Row
cur = db.cursor()
# prompt for amount of the bill .. and date
bill_date = utils.prompt_for_current_date("Date of bill")
pge_bill = float(utils.prompt_for_amount("PGE bill amount"))
logger.trace(f"pge_bill: {int(pge_bill)}")
const = constants.Constants()
exec_str = f"""
INSERT INTO activity (date, type, amount, note)
VALUES (?, ?, ?, ?)
"""
params = (bill_date, const.pge_bill_received, pge_bill,
"PGE bill received")
cur.execute(exec_str, params)
# instantiate an obj for each of the accounts
cur.execute("SELECT * FROM account")
rows = cur.fetchall()
acct_list = []
total_usage = 0.0
# each row('r') ... should represent an individual account
for r in rows:
if r["active"] == "no":
logger.trace(f"Account {r['acct_id']} currently INACTIVE")
continue
acct_obj = account.Account(
r["acct_id"],
r["first_name"],
r["last_name"],
r["file_alias"],
r["address"],
r["reads_in"],
r["master"],
)
acct_list.append(acct_obj)
# fetch the last two reading rows from the db
query: str = f"""
SELECT reading
FROM reading
WHERE account_id = (?)
ORDER BY reading_id
DESC LIMIT 2
"""
params = (r["acct_id"], )
rows = cur.execute(query, params)
# near as I can tell this returns a row for each line of data found
# the row is a list of selected items .... so 'reading' is the
# zeroeth item ...
#
# need to collect them both in a list for further processing
readings_list = []
for row in rows:
readings_list.append(
row["reading"]) # this retrieval by name seems to be fine
logger.trace(f"readings_list: {readings_list}")
acct_obj.latest_reading = readings_list[0]
acct_obj.previous_reading = readings_list[1]
acct_obj.calculate_current_usage()
logger.trace(f"current usage: {acct_obj.current_usage}")
logger.trace(f"{acct_obj.reads_in} .. {acct_obj.previous_reading}")
total_usage += acct_obj.current_usage
total_usage = round(total_usage, 2)
logger.trace(f"total usage: {total_usage}")
# a balance less than $10k should trigger an assessment
# in the upcoming for loop
savings_balance = utils.get_savings_balance(cur)
logger.trace(f"savings_balance: {savings_balance}")
assessment_total = 0
for acct in acct_list:
logger.trace(f"\n\n{acct.addr}")
logger.trace(
f"current_usage_percent (b4 calculation): {acct.current_usage_percent}"
)
logger.trace(
f"current_usage_percent: {(acct.current_usage / total_usage) * 100}"
)
logger.trace(f"total_usage: {total_usage}")
acct.current_usage_percent = round(
(acct.current_usage / total_usage) * 100, 2)
logger.trace(
f"current_usage_percent (rounded): {acct.current_usage_percent:.2f}"
)
logger.trace(f"pge_bill: {int(pge_bill)}")
logger.trace(f"a.current_usage_percent: {acct.current_usage_percent}")
acct.pge_bill_share = round(
(pge_bill * acct.current_usage_percent / 100), 0)
logger.trace(f"pge_bill_share: {int(acct.pge_bill_share)}")
exec_str = f"""
INSERT INTO activity (date, acct, type, amount, note)
VALUES (?, ?, ?, ?, ?)
"""
params = (
bill_date,
acct.acct_id,
const.pge_bill_share,
acct.pge_bill_share,
"PGE bill share",
)
cur.execute(exec_str, params)
# this should be moved outside ... no sense going through all
# this if no assessment needed ...
# move it outside and process as separate
if savings_balance < 1000000:
logger.trace(f"Assessment is due.")
acct.savings_assessment = int(
round(acct.current_usage * const.assessment_per_gallon * 100,
0))
logger.trace(f"Assessed: {acct.savings_assessment}")
# write this to the db
exec_str = f"""
INSERT INTO activity (date, acct, type, amount, note)
VALUES (?, ?, ?, ?, ?)
"""
params = (
bill_date,
acct.acct_id,
const.savings_assessment,
acct.savings_assessment,
"Savings assessment",
)
cur.execute(exec_str, params)
assessment_total += acct.savings_assessment
logger.trace(
f"Bill total: {int(round(acct.savings_assessment + acct.pge_bill_share, 2))}"
)
else:
logger.trace(f"No assessment needed.")
# added this to make the savings deposit easier
# 2019.07.21
#
# 2019.08.24 ... this needed reformatting
print(f"==============================================")
print(f"assessment_total: ${assessment_total / 100:10.2f}")
print(f"==============================================")
# save, then close the cursor and db
db.commit()
cur.close()
db.close()
