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()