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():
logger = logbook.Logger("readings")
database = "well.sqlite"
db = sqlite3.connect(database)
db.row_factory = sqlite3.Row
cur = db.cursor()
reading_date: str = utils.prompt_for_current_date("Reading date")
exec_str = "INSERT INTO reading_date (date) VALUES (?)"
params = (reading_date, )
logger.trace(f"{exec_str}{params}")
cur.execute(exec_str, params)
last_inserted_row_id = cur.lastrowid
logger.trace(f"attempting to backup the database file now")
backup_file_name = utils.backup_file(database)
logger.trace(f"database backed up to: {backup_file_name}")
exec_str = "SELECT * FROM account"
cur.execute(exec_str)
rows = cur.fetchall()
for r in rows:
if r["active"] == "no":
logger.trace(f"Account {r['acct_id']} currently INACTIVE")
continue
# fetch last month's reading as a sanity check
exec_str = f"""
SELECT reading
FROM reading
WHERE account_id = (?)
ORDER BY reading_id
DESC
"""
params = (r["acct_id"], )
cur.execute(exec_str, params)
last_reading = cur.fetchone()
print(f"Last month's reading: {last_reading['reading']}")
# grab current reading and then insert it into the DB
reading = input(f"{r['acct_id']} - {r['address']}: ")
# this should allow empty input .... in case of inactive account
if not reading:
continue
exec_str = f"""
INSERT INTO reading (reading_id, account_id, reading)
VALUES (?, ?, ?)
"""
params = (last_inserted_row_id, r["acct_id"], reading)
logger.trace(params)
cur.execute(exec_str, params)
# save, then close the cursor and db
db.commit()
cur.close()
db.close()
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 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 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: Logger = logging.getLogger()
logger.debug("Entering main")
db = sqlite3.connect(database)
db.row_factory = sqlite3.Row
cur = db.cursor()
logger.debug("attempting to backup the database file now")
backup_file_name: str = utils.backup_file(logger, database)
logger.debug(f'database backed up to: {backup_file_name}')
reading_date = utils.prompt_for_current_date(logger, "Reading date")
exec_str: str = "INSERT INTO reading_date (date) VALUES (?)"
params = (reading_date, )
logger.debug(f"{exec_str}{params}")
cur.execute(exec_str, params)
last_inserted_row_id = cur.lastrowid
logger.debug(f"last_inserted_row_id: {last_inserted_row_id}")
acct: str = utils.prompt_for_account(logger, "Please choose an account",
cur)
reading: str = input(f"Reading: ")
exec_str = "INSERT INTO reading (reading_id, account_id, reading) VALUES (?, ?, ?)"
params = (last_inserted_row_id, int(acct), int(reading))
cur.execute(exec_str, params)
# save, then close the cursor and db
db.commit()
cur.close()
db.close()
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 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 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()