684 lines
33 KiB
Python
684 lines
33 KiB
Python
from db import set_bill_type_growth, new_bill, deleteFutureEstimates, get_finance_data, get_bill_data, get_bill_types, get_bill_freqs
|
|
from calc import calc_key_dates
|
|
from defines import END_YEAR
|
|
import datetime
|
|
import re
|
|
from datetime import date, timedelta
|
|
|
|
|
|
################################################################################
|
|
# this finds start and end dates of a quarter for a given date
|
|
################################################################################
|
|
def quarter_bounds(d):
|
|
q = (d.month-1)//3
|
|
start = date(d.year, 3*q+1, 1)
|
|
# last day of quarter = first day of next quarter minus 1 day
|
|
if q == 3:
|
|
next_start = date(d.year+1, 1, 1)
|
|
else:
|
|
next_start = date(d.year, 3*q+4, 1)
|
|
end = next_start - timedelta(days=1)
|
|
return start, end
|
|
|
|
################################################################################
|
|
# takes a bill and its previous bill, works out days between and adds cost / day
|
|
# to each quarter the bill covers from prev. to now. Usually means it splits
|
|
# one bill in a previous and this qtr (or just puts it all into the current qtr)
|
|
################################################################################
|
|
def allocate_by_quarter( bill_info, bill_type, yr, prev_bill, bill):
|
|
start = date( int(prev_bill['bill_date'][:4]), int(prev_bill['bill_date'][5:7]), int(prev_bill['bill_date'][8:]))
|
|
end = date( int(bill['bill_date'][:4]), int(bill['bill_date'][5:7]), int(bill['bill_date'][8:]))
|
|
|
|
time_difference = end - start
|
|
days = time_difference.days
|
|
cost_per_day = bill['amount']/days
|
|
if end < start:
|
|
return {}
|
|
if not 'qtr' in bill_info[bill_type]:
|
|
bill_info[bill_type]['qtr'] = {}
|
|
|
|
q_start, q_end = quarter_bounds(start)
|
|
cur = q_start
|
|
# walk quarters that might overlap - start from the quarter of `start`, iterate until past `end`
|
|
while cur <= end:
|
|
q_start, q_end = quarter_bounds(cur)
|
|
overlap_start = max(start, q_start)
|
|
overlap_end = min(end, q_end)
|
|
# only add qtr total for yr being calc'd
|
|
if overlap_end >= overlap_start:
|
|
days = (overlap_end - overlap_start).days + 1
|
|
q = (q_start.month-1)//3 + 1
|
|
# initialise arrays if needed
|
|
if q_start.year not in bill_info[bill_type]['qtr']:
|
|
bill_info[bill_type]['qtr'][q_start.year] = {}
|
|
for i in range(1,5):
|
|
bill_info[bill_type]['qtr'][q_start.year][i]=0
|
|
if q not in bill_info[bill_type]['qtr'][q_start.year]:
|
|
bill_info[bill_type]['qtr'][q_start.year][q]=0
|
|
bill_info[bill_type]['qtr'][q_start.year][q] += days*cost_per_day
|
|
# next quarter
|
|
cur = q_end + timedelta(days=1)
|
|
return
|
|
|
|
################################################################################
|
|
# given a bill date in format YYYY-MM-DD, return quarter (1-4)
|
|
################################################################################
|
|
def qtr(d):
|
|
m = int(d[5:7])
|
|
return ( (m-1)//3 + 1 )
|
|
|
|
################################################################################
|
|
# find the bill just after the date given
|
|
################################################################################
|
|
def find_next_bill( bill_type, bill_info, bill_date ):
|
|
wanted_year = int(bill_date[:4])
|
|
wanted_mm = int(bill_date[5:7])
|
|
# if we want a bill after our last year, just return None
|
|
if int(wanted_year) > int(bill_info[bill_type]['last_bill_year']):
|
|
return None
|
|
|
|
for yr in range( wanted_year, bill_info[bill_type]['last_bill_year']+1 ):
|
|
# start with bills in the year wanted (if any)
|
|
if yr in bill_info[bill_type]['year']:
|
|
# reverse this list so we can q1 bills before q4
|
|
for bill in bill_info[bill_type]['year'][yr][::-1]:
|
|
bill_mm = int(bill['bill_date'][5:7])
|
|
# if bill is in this year but later OR its a later year, return this bill
|
|
if (wanted_year == yr and bill_mm > wanted_mm) or wanted_year < yr:
|
|
return bill
|
|
# failsafe
|
|
return None
|
|
|
|
|
|
# see if this bill exists (used to prevent adding more than once in future
|
|
# estimated bills)
|
|
def find_this_bill( bill_type, bill_info, bill_date ):
|
|
yr = int(bill_date[:4])
|
|
if not bill_type in bill_info or not 'year' in bill_info[bill_type] or not yr in bill_info[bill_type]['year']:
|
|
return None
|
|
for b in bill_info[bill_type]['year'][yr]:
|
|
if bill_type == b['bill_type'] and bill_date == b['bill_date']:
|
|
return b
|
|
return None
|
|
|
|
|
|
# find the bill just before the date given
|
|
def find_previous_bill( bill_type, bill_info, bill_date ):
|
|
wanted_year = int(bill_date[:4])
|
|
wanted_mm = int(bill_date[5:7])
|
|
# if we don't have a bill before this date, no way to set price
|
|
if int(wanted_year) < int(bill_info[bill_type]['first_bill_year']):
|
|
return None
|
|
|
|
# start loop from bill_date, go backwards and find which one it is (same year, should be month-based)
|
|
# earlier year, then just last one from the year.
|
|
yr_range=range( wanted_year, bill_info[bill_type]['first_bill_year']-1, -1 )
|
|
if wanted_year == int(bill_info[bill_type]['first_bill_year']):
|
|
# range of this year with -1, does not return anything, so force this year.
|
|
yr_range=[ wanted_year ]
|
|
|
|
for yr in yr_range:
|
|
# start with bills in the year wanted (if any)
|
|
# must include 'estimated' bills to deal with growth of future years
|
|
if yr in bill_info[bill_type]['year']:
|
|
# okay, we have the previous billing year, and we wanted one for a year in the future,
|
|
# just return the last one in this year as its the most recent
|
|
if wanted_year > yr:
|
|
# small chance of future bills having estimates and reals (kayo did this)
|
|
for tmp in bill_info[bill_type]['year'][yr]:
|
|
if tmp['estimated'] == 0:
|
|
return tmp
|
|
return bill_info[bill_type]['year'][yr][0]
|
|
else:
|
|
# lets go through the newest to oldest of these bills
|
|
for bill in bill_info[bill_type]['year'][yr]:
|
|
bill_mm = int(bill['bill_date'][5:7])
|
|
# reversing the bills, means we start with the 'most recent' in this year to the oldest
|
|
# if the month we want is after the bill, we are done
|
|
if wanted_mm > bill_mm:
|
|
return bill
|
|
return None
|
|
|
|
|
|
# quick wrapper to add a new estimated bill - new estimates have the flag in
|
|
# the DB set, but also we update bill_info to reflect the new bill so future
|
|
# growth can build of this esimate too - e.g 2030 can use 2029, etc
|
|
def new_estimated_bill( bill_info, yr, bill_type, amt, new_date ):
|
|
# add to DB
|
|
new_bill( bill_type, amt, new_date, 1 )
|
|
|
|
# patch this data back into bill_info so growth works in future
|
|
if not yr in bill_info[bill_type]['year']:
|
|
bill_info[bill_type]['year'][yr]=[]
|
|
bill={}
|
|
bill['bill_date']=new_date
|
|
bill['amount']=amt
|
|
bill['bill_type']=bill_type
|
|
bill['estimated']=1
|
|
# need to insert(0,) to add this "newest" bill to start of the data for {yr} so that find_previous_bill can work - only need the above 3 fields
|
|
bill_info[bill_type]['year'][yr].insert(0,bill)
|
|
|
|
if bill_info[bill_type]['num_ann_bills'] == 4:
|
|
q = qtr( new_date )
|
|
# new bill in this qtr of this year, so set arrays up
|
|
if yr not in bill_info[bill_type]['qtr']:
|
|
bill_info[bill_type]['qtr'][yr]={}
|
|
pb = find_previous_bill( bill_type, bill_info, new_date )
|
|
if pb['estimated'] == 0:
|
|
allocate_by_quarter( bill_info, bill_type, yr, pb, bill )
|
|
else:
|
|
if not q in bill_info[bill_type]['qtr'][yr]:
|
|
# first in this year, just init it...
|
|
bill_info[bill_type]['qtr'][yr][q]=0
|
|
bill_info[bill_type]['qtr'][yr][q]+=amt
|
|
return
|
|
|
|
|
|
# missing annual bill, find date based on MM-DD and add new year - given we start with first_bill anyway, will only be used for future bill predictions
|
|
# future only, so add ann_growth (based on drop-down) for each future year
|
|
# NOTE: only ever called when there is a need to add a new bill
|
|
def add_missing_annual_bill_in_yr( bill_type, bill_info, yr ):
|
|
mm_dd = bill_info[bill_type]['last_bill']['bill_date'][5:]
|
|
new_date= f'{yr}-{mm_dd}'
|
|
pb=find_previous_bill( bill_type, bill_info, new_date )
|
|
if pb:
|
|
amt = pb['amount']
|
|
else:
|
|
amt = bill_info[bill_type]['last_bill']['amount']
|
|
# okay the missing bill is before the first bill...
|
|
amt += amt * bill_info[bill_type]['growth']/100
|
|
|
|
new_estimated_bill( bill_info, yr, bill_type, amt, new_date )
|
|
return
|
|
|
|
# missing quarterly bill, find date based on MM-DD and ??? - can have missing bilsl in first year
|
|
# add growth (based on drop-down) for each future year
|
|
def add_missing_quarter_bills_in_yr( bill_type, bill_info, yr ):
|
|
# okay we have data for last year but some missing (in this year), lets fill in gaps
|
|
# could be called if only have data for q2 - q4 in first year and we dont have a previous years q1 data so don't try
|
|
if 'qtr' in bill_info[bill_type] and yr-1 in bill_info[bill_type]['qtr']:
|
|
# if we do have data in this year, we have q1-q3 only, and want missing qtrs set range appropriately...
|
|
if yr in bill_info[bill_type]['qtr']:
|
|
# per if above, ONLY get here if we have first few bills of {yr}, cannot be last few
|
|
have_q = qtr( bill_info[bill_type]['year'][yr][0]['bill_date'] )
|
|
r=range(have_q+1,5)
|
|
else:
|
|
r=range(1,5)
|
|
for q in r:
|
|
if 'forced' in bill_info[bill_type]['freq']:
|
|
actually_add_estimated_new_quarter_bill_forced(bill_type, bill_info, yr, q)
|
|
else:
|
|
actually_add_estimated_new_quarter_bill(bill_type, bill_info, yr, q)
|
|
return
|
|
|
|
################################################################################
|
|
# func take a qtr in a year, finds equiv from previous year, calcs new based on
|
|
# it (same 'day' with amt * growth)
|
|
################################################################################
|
|
def actually_add_estimated_new_quarter_bill( bill_type, bill_info, yr, q ):
|
|
|
|
# amt is total of last year's qtr bill (NOTE: use 4-q, bills are in desc order)
|
|
last_yrs_bill_in_this_q = bill_info[bill_type]['year'][yr-1][4-q]
|
|
amt = last_yrs_bill_in_this_q['amount']*(1+bill_info[bill_type]['growth']/100)
|
|
|
|
# make new qtr bill same 'day' (mm-dd) as last year, just chg (yr)
|
|
mmdd=last_yrs_bill_in_this_q['bill_date'][5:]
|
|
new_date = f'{yr}-{mmdd}'
|
|
new_estimated_bill( bill_info, yr, bill_type, amt, new_date )
|
|
return
|
|
|
|
def actually_add_estimated_new_quarter_bill_forced( bill_type, bill_info, yr, q ):
|
|
last_yrs_qtr_amount = bill_info[bill_type]['qtr'][yr-1][q]
|
|
amt=last_yrs_qtr_amount*(1+bill_info[bill_type]['growth']/100)
|
|
new_date = f'{yr}-{q*3:02d}-01'
|
|
new_estimated_bill( bill_info, yr, bill_type, amt, new_date )
|
|
return
|
|
|
|
# missing monthly bills, find date based on DD and put in each missing month
|
|
# add growth (based on drop-down) for each future year
|
|
# NOTE: ALWAYS called for first year - don't always add bills/see below
|
|
def add_missing_monthly_bills_in_yr( bill_type, bill_info, yr ):
|
|
|
|
print( f"add_missing_monthly_bills_in_yr for ( bt={bill_type} -- yr={yr} )" )
|
|
|
|
# start date arithmetic from first bill (this is possibly an issue if monthly is not
|
|
# really perfectly the same each month, but its only for an estimate so should be ok
|
|
dd = bill_info[bill_type]['first_bill']['bill_date'][8:]
|
|
mm = bill_info[bill_type]['first_bill']['bill_date'][5:7]
|
|
lb_mm = bill_info[bill_type]['last_bill']['bill_date'][5:7]
|
|
|
|
#okay add monthly bills for the rest of this year if its the first year
|
|
if bill_info[bill_type]['first_bill_year'] == yr:
|
|
start_m=int(mm)
|
|
else:
|
|
start_m=0
|
|
|
|
# fill in rest of this year
|
|
for i in range( start_m+1, 13 ):
|
|
bill_found=False
|
|
new_date = f'{yr}-{i:02d}-{dd}'
|
|
new_date_yymm=f'{yr}-{i:02d}'
|
|
if yr in bill_info[bill_type]['year']:
|
|
for b in bill_info[bill_type]['year'][yr]:
|
|
# this bill exists, skip adding it (this occurs when called to
|
|
# add bills as there are < 12 bills in first_year, BUT, we
|
|
# don't fill before first_bill so the < 12 ALWAYS triggers
|
|
if new_date_yymm in str(b['bill_date']):
|
|
bill_found=True
|
|
break
|
|
if not bill_found:
|
|
pb=find_previous_bill( bill_type, bill_info, new_date )
|
|
nb=find_next_bill( bill_type, bill_info, new_date )
|
|
if not pb:
|
|
print("Failed to find previous_bill, can't calculate missing bill - returning" )
|
|
return
|
|
|
|
amt = pb['amount']
|
|
# if there is no next bill then use growth, otherwise, I am only putting in real bills
|
|
# where changes occur, so keep the pb amount 'unchanged'
|
|
if not nb:
|
|
# if this month is the same as the last bill month and as per above
|
|
# we don't have a bill for this date, then add annual grotwh
|
|
if i == int(lb_mm):
|
|
amt += amt * bill_info[bill_type]['growth']/100
|
|
bill_info[bill_type]['last_bill_amount']=amt
|
|
new_estimated_bill( bill_info, yr, bill_type, amt, new_date )
|
|
return
|
|
|
|
# given the bill_type has a which_growth contain min/avg/max, return the corresponding growth number
|
|
def get_growth_value( bt, bill_type ):
|
|
for el in bt:
|
|
if el['id'] == bill_type:
|
|
which = el['which_growth']
|
|
break
|
|
|
|
if which == 'avg':
|
|
return el['ann_growth_avg']
|
|
elif which == 'min':
|
|
return el['ann_growth_min']
|
|
elif which == 'simple':
|
|
return el['ann_growth_simple']
|
|
elif which == 'max':
|
|
return el['ann_growth_max']
|
|
elif which == 'cpi':
|
|
finance_data = get_finance_data()
|
|
return finance_data['Inflation']
|
|
else:
|
|
match = re.match("flat-(\d+)", which )
|
|
if match:
|
|
return int(match.group(1))
|
|
else:
|
|
print( f"FAILED TO GET_GROWTH_VALUE --> which={which}" )
|
|
return 0
|
|
|
|
|
|
################################################################################
|
|
# go through the bill data from the DB, put it into more friendly formats, then
|
|
# work out and then add missing bill data (might be b/c we have monthly bills,
|
|
# and I didn't want to input 12 of them at the same price), and it always
|
|
# occurs for future bills
|
|
################################################################################
|
|
def process_bill_data(bd, bt, bf, key_dates):
|
|
# this maps a bill id to a freq id (e.g. bill #34 - has a frequency of #2 (which might be quarterly)
|
|
bt_id_freq = {row["id"]: row["freq"] for row in bt}
|
|
bt_id_ann_growth_avg = {row["id"]: row["ann_growth_avg"] for row in bt}
|
|
bt_id_name = {row["id"]: row["name"] for row in bt}
|
|
|
|
# this maps freq to bills per annum (e.g. id=2 to 4 bills per annum)
|
|
bf_id_num = {row["id"]: row["num_bills_per_annum"] for row in bf}
|
|
# and allows me a way to see if the bill is quarterly but also fixed or seasonal
|
|
bf_id_name = {row["id"]: row["name"] for row in bf}
|
|
|
|
# want to proces all bill data into easier to maniuplate structure, so make
|
|
# a bill_info[bill_id] with first_bill, last_bill, [yr] with matching bills to process
|
|
bill_info={}
|
|
future_car_bills=[]
|
|
future_D_quit_bills=[]
|
|
|
|
for bill in bd:
|
|
bill_type = bill['bill_type']
|
|
if bill['bill_date'] == 'future':
|
|
# Future bills, deal with them at the end - they have dynamic start dates
|
|
if 'Hyundai' in bt_id_name[bill_type]:
|
|
future_car_bills.insert( 0, bill )
|
|
else:
|
|
future_D_quit_bills.insert( 0, bill )
|
|
bill_info[bill_type]={}
|
|
bill_info[bill_type]['future'] = 1
|
|
bill_info[bill_type]['freq'] = bf_id_name[bt_id_freq[bill_type]]
|
|
bill_info[bill_type]['growth'] = get_growth_value( bt, bill_type )
|
|
bill_info[bill_type]['num_ann_bills'] = bf_id_num[bt_id_freq[bill_type]]
|
|
bill_info[bill_type]['year']={}
|
|
continue
|
|
|
|
yr= int(bill['bill_date'][:4])
|
|
# new bill type
|
|
if not bill_type in bill_info:
|
|
bill_info[bill_type]={}
|
|
bill_info[bill_type]['freq'] = bf_id_name[bt_id_freq[bill_type]]
|
|
bill_info[bill_type]['growth'] = get_growth_value( bt, bill_type )
|
|
bill_info[bill_type]['num_ann_bills'] = bf_id_num[bt_id_freq[bill_type]]
|
|
bill_info[bill_type]['first_bill']={}
|
|
bill_info[bill_type]['last_bill']={}
|
|
# due to sql sorting, this first instance is the last bill
|
|
bill_info[bill_type]['last_bill']=bill
|
|
bill_info[bill_type]['last_bill_year']=int(bill['bill_date'][:4])
|
|
if not bill['estimated']:
|
|
bill_info[bill_type]['last_real_bill_year']=int(bill['bill_date'][:4])
|
|
bill_info[bill_type]['year']={}
|
|
if not yr in bill_info[bill_type]['year']:
|
|
bill_info[bill_type]['year'][yr]=[]
|
|
|
|
# keep updating last to this matching bill
|
|
bill_info[bill_type]['first_bill']=bill
|
|
bill_info[bill_type]['first_bill_year']=int(bill['bill_date'][:4])
|
|
if not 'last_real_bill_year' in bill_info[bill_type] and not bill['estimated']:
|
|
bill_info[bill_type]['last_real_bill_year']=int(bill['bill_date'][:4])
|
|
# append this bill to list for this year
|
|
bill_info[bill_type]['year'][yr].append(bill)
|
|
|
|
# now process the bill_info from yr of first bill to yr of last bill
|
|
for bill_type in bill_info:
|
|
if 'future' in bill_info[bill_type]:
|
|
continue
|
|
# find freq id based on bill_type id, then use that to find num bills by freq id
|
|
num = bf_id_num[bt_id_freq[bill_type]]
|
|
|
|
if 'last_bill' not in bill_info[bill_type]:
|
|
print("Cannot process bill_type={bill_type} - no bill info for it at all" )
|
|
# range of years to process (yr_min to yr_max)
|
|
yr_min=int(bill_info[bill_type]['first_bill']['bill_date'][:4])
|
|
yr_max=int(bill_info[bill_type]['last_bill']['bill_date'][:4])
|
|
|
|
ProportionQtrlyData( bill_type, bill_info )
|
|
|
|
# go from first_bill year until reach end year
|
|
for yr in range( yr_min, END_YEAR+1 ):
|
|
# we have all the bills needed for yr - but dont be cute with qtrly, gas bills suck can have missing with 4 bills
|
|
# > can occur when we add a real bill "on top of" an estimate.
|
|
if yr in bill_info[bill_type]['year'] and len(bill_info[bill_type]['year'][yr]) >= bill_info[bill_type]['num_ann_bills'] and bill_info[bill_type]['num_ann_bills'] !=4:
|
|
continue
|
|
add_missing_bills_for_yr( bill_type, bill_info, yr )
|
|
derive_ann_growth( bill_type, bill_info, key_dates )
|
|
|
|
deal_with_future_car_bills( key_dates, future_car_bills, bill_info )
|
|
deal_with_future_D_quit_bills( key_dates, future_D_quit_bills, bill_info )
|
|
|
|
return bill_info
|
|
|
|
################################################################################
|
|
# deal_with_future_car_bills - just add these estimate bills based on when we
|
|
# own the car (data can change if I buy it out)
|
|
################################################################################
|
|
def deal_with_future_car_bills( key_dates, future_car_bills, bill_info ):
|
|
car_yr=key_dates['D_hyundai_owned'][0:4]
|
|
car_mmdd=key_dates['D_hyundai_owned'][5:]
|
|
for fb in future_car_bills:
|
|
# deal with future bills due to their starting dates being dynamic
|
|
amt=fb['amount']
|
|
bt=fb['bill_type']
|
|
# factor in growth for next bill
|
|
for yr in range( int(car_yr), END_YEAR+1 ):
|
|
new_date=f"{yr}-{car_mmdd}"
|
|
# if we dont already have an annual bill for this year (all car bills are annual)
|
|
if yr not in bill_info[bt]['year']:
|
|
new_estimated_bill( bill_info, yr, fb['bill_type'], amt, new_date )
|
|
amt += amt * bill_info[bt]['growth']/100
|
|
|
|
|
|
################################################################################
|
|
# deal_with_future_D_quit_bills - just add these estimate bills based on when I
|
|
# quit
|
|
################################################################################
|
|
def deal_with_future_D_quit_bills( key_dates, future_D_quit_bills, bill_info ):
|
|
D_quit_yr = key_dates['D_quit_date'][0:4]
|
|
dq_mm=key_dates['D_quit_date'][5:7]
|
|
dq_dd=key_dates['D_quit_date'][8:]
|
|
if int(dq_dd) > 28: dq_dd=28
|
|
for fb in future_D_quit_bills:
|
|
# deal with future bills due to their starting dates being dynamic
|
|
amt=fb['amount']
|
|
bt=fb['bill_type']
|
|
if bill_info[bt]['num_ann_bills'] == 1:
|
|
# factor in growth for next bill
|
|
for yr in range( int(D_quit_yr), END_YEAR+1 ):
|
|
new_date=f"{yr}-{dq_mm}-{dq_dd}"
|
|
# if we dont already have an annual bill for this year
|
|
if not find_this_bill( bt, bill_info, new_date ):
|
|
new_estimated_bill( bill_info, yr, bt, amt, new_date )
|
|
amt += amt * bill_info[bt]['growth']/100
|
|
elif bill_info[bt]['num_ann_bills'] == 12:
|
|
# do rest of this year, then next years
|
|
for m in range( int(dq_mm), 13):
|
|
new_date=f"{D_quit_yr}-{m:02d}-{dq_dd}"
|
|
if not find_this_bill( bt, bill_info, new_date ):
|
|
new_estimated_bill( bill_info, yr, bt, amt, new_date )
|
|
for yr in range( int(D_quit_yr)+1, END_YEAR+1 ):
|
|
amt += amt * bill_info[bt]['growth']/100
|
|
for m in range( 1, 13):
|
|
new_date=f"{yr}-{m:02d}-{dq_dd}"
|
|
if not find_this_bill( bt, bill_info, new_date ):
|
|
new_estimated_bill( bill_info, yr, bt, amt, new_date )
|
|
|
|
################################################################################
|
|
# add_missing_bills_for_yr -- wrapper to call right func based on bill freq
|
|
################################################################################
|
|
def add_missing_bills_for_yr( bill_type, bill_info, yr ):
|
|
num = bill_info[bill_type]['num_ann_bills']
|
|
if num == 1:
|
|
add_missing_annual_bill_in_yr( bill_type, bill_info, yr )
|
|
elif num == 4:
|
|
add_missing_quarter_bills_in_yr( bill_type, bill_info, yr )
|
|
elif num == 12:
|
|
add_missing_monthly_bills_in_yr( bill_type, bill_info, yr )
|
|
return
|
|
|
|
################################################################################
|
|
# Takes qtrly bills and start from 2nd year of bills (so we can estimate growth)
|
|
# and go through each bill allocating the proportion of each bill to each
|
|
# relevant quarter - to build more accurate totals. Would be mostly marginal
|
|
# accept when Gas qtrly bills have 6 per year, and we need to guess say qtr4 in
|
|
# the future, we can't easily find corresponding bill form previous year, so
|
|
# this allows us to aggregate per quarter and use matching quarter
|
|
################################################################################
|
|
def ProportionQtrlyData( bill_type, bill_info ):
|
|
# just do up to now for the moment so that add_missing_bills later will have qtr data to use
|
|
now_yr = datetime.date.today().year
|
|
# FIX UP CRAPPY QUARTERLY BILLING PROPORTIONS (only useful as some gas bills are 6 / year!)
|
|
if bill_info[bill_type]['num_ann_bills']==4:
|
|
for yr in range( bill_info[bill_type]['first_bill_year'], END_YEAR+1):
|
|
if yr in bill_info[bill_type]['year']:
|
|
for b in bill_info[bill_type]['year'][yr]:
|
|
pb = find_previous_bill( bill_type, bill_info, b['bill_date'] )
|
|
if not pb:
|
|
continue
|
|
allocate_by_quarter( bill_info, bill_type, yr, pb, b )
|
|
return
|
|
|
|
################################################################################
|
|
# function to work out totals per year, and then calcuates annual growth in
|
|
# terms of min/avg/max - uses qtr data for qtrly bills, or just normal totals
|
|
# for other bill types
|
|
################################################################################
|
|
def derive_ann_growth( bill_type, bill_info, key_dates ):
|
|
# just do up to now so we stop earlier than looking at other estimated (just an optimisation)
|
|
now_yr = datetime.date.today().year
|
|
|
|
total={}
|
|
for yr in range( bill_info[bill_type]['first_bill_year'], now_yr+1):
|
|
# if not enough bills in this year (or none), then try next year (first year might have not enough bills)
|
|
if yr not in bill_info[bill_type]['year'] or len(bill_info[bill_type]['year'][yr]) < bill_info[bill_type]['num_ann_bills']:
|
|
continue;
|
|
|
|
# just going to make sure we dont use estimated data in the last year of real data - can skew growths
|
|
if yr == bill_info[bill_type]['last_real_bill_year'] or bill_info[bill_type]['num_ann_bills'] ==1:
|
|
skip_yr=False
|
|
for b in bill_info[bill_type]['year'][yr]:
|
|
if b['estimated']:
|
|
skip_yr=True
|
|
if skip_yr:
|
|
continue
|
|
|
|
total[yr] = 0
|
|
for b in bill_info[bill_type]['year'][yr]:
|
|
total[yr] += b['amount']
|
|
|
|
# crazily we can have more bills in this year than expected, so work out qtrly costs, and patch that back into total array
|
|
for yr in range( bill_info[bill_type]['first_bill_year'], now_yr+1):
|
|
if 'qtr' in bill_info[bill_type] and yr in bill_info[bill_type]['qtr']:
|
|
tot=0
|
|
for q in range( 1,5 ):
|
|
tot += bill_info[bill_type]['qtr'][yr][q]
|
|
if yr in total:
|
|
# use new derived qtr, slightly more accurate
|
|
total[yr]=tot
|
|
|
|
# once we have all yr totals:
|
|
growth = {}
|
|
min_growth = 999
|
|
avg_growth = 0
|
|
max_growth = 0
|
|
count = 0
|
|
simple_first_yr=0
|
|
simple_last_yr=0
|
|
# start from year after first bill, so we can see annual growth from the following year onwards
|
|
for yr in range( bill_info[bill_type]['first_bill_year']+1, now_yr+1):
|
|
# if full data sets for consecutive years, work out annual growth stats
|
|
if yr-1 in total and yr in total:
|
|
if simple_first_yr==0:
|
|
simple_first_yr=yr
|
|
growth = (total[yr] - total[yr-1]) / total[yr-1] * 100
|
|
avg_growth += growth
|
|
count += 1
|
|
simple_last_yr=yr
|
|
if growth < min_growth:
|
|
min_growth = growth
|
|
if growth > max_growth:
|
|
max_growth = growth
|
|
# data to work with
|
|
if count:
|
|
# strt with 0, set it if we can below
|
|
simple_growth=0
|
|
if simple_first_yr != simple_last_yr:
|
|
# calculate a simple growth with full year consecutive totals -> last - first / years
|
|
simple_growth=( ((total[simple_last_yr]-total[simple_first_yr])/(simple_last_yr-simple_first_yr)) / total[simple_first_yr] )*100.0
|
|
else:
|
|
# calculate a simple growth based on last - first / years - only 1 consecutive year I guess, so can't use it, use real first/last
|
|
if bill_info[bill_type]['first_bill_year'] != bill_info[bill_type]['last_real_bill_year'] and bill_info[bill_type]['first_bill_year'] in total and bill_info[bill_type]['last_real_bill_year'] in total:
|
|
simple_growth=( ((total[bill_info[bill_type]['last_real_bill_year']]-total[bill_info[bill_type]['first_bill_year']])/(bill_info[bill_type]['last_real_bill_year']-bill_info[bill_type]['first_bill_year'])) / total[bill_info[bill_type]['first_bill_year']] )*100.0
|
|
set_bill_type_growth( bill_type, min_growth, avg_growth/count, max_growth, simple_growth )
|
|
else:
|
|
# okay use last - first / years to get a simple_growth, just need bills from different years
|
|
# if there are totals for them (may not be set with monthly and < 12 bills in 1st year)
|
|
if 'last_real_bill_year' in bill_info[bill_type] and bill_info[bill_type]['first_bill_year'] != bill_info[bill_type]['last_real_bill_year'] and bill_info[bill_type]['first_bill_year'] in total and bill_info[bill_type]['last_real_bill_year'] in total:
|
|
simple_growth=( ((total[bill_info[bill_type]['last_real_bill_year']]-total[bill_info[bill_type]['first_bill_year']])/(bill_info[bill_type]['last_real_bill_year']-bill_info[bill_type]['first_bill_year'])) / total[bill_info[bill_type]['first_bill_year']] )*100.0
|
|
set_bill_type_growth( bill_type, 0, 0, 0, simple_growth )
|
|
else:
|
|
# failsafe (just in case fill bills failed to add enough bills to average out)
|
|
print( f"{bill_type}: Unable to calculate growth!" )
|
|
|
|
################################################################################
|
|
# just go through this year to END_YEAR, total any bills for each year up
|
|
# so we can display the annual estimated bills onwards...
|
|
################################################################################
|
|
def calc_future_totals(bill_info, bill_types):
|
|
total={}
|
|
now_yr = datetime.date.today().year
|
|
for bt in bill_types:
|
|
total[bt['id']]={}
|
|
for yr in range( now_yr, END_YEAR+1):
|
|
total[bt['id']][yr]=0.0
|
|
if bt['id'] in bill_info and yr in bill_info[bt['id']]['year']:
|
|
for b in bill_info[bt['id']]['year'][yr]:
|
|
total[bt['id']][yr] += b['amount']
|
|
# had to round to 2 decimal here to get sensible totals
|
|
total[bt['id']][yr] = round( total[bt['id']][yr], 2 )
|
|
return total
|
|
|
|
|
|
################################################################################
|
|
# When we change the day D_quits, or we buyout the car, then future bills need
|
|
# to change/rebuild estimates, convenience routine used to find future bills -
|
|
# rather than go through them as we render /bills
|
|
################################################################################
|
|
def getFutureBills(bd,bt,future_car_bills, future_D_quit_bills):
|
|
# this maps a bill id to a name
|
|
bt_id_name = {row["id"]: row["name"] for row in bt}
|
|
|
|
for bill in bd:
|
|
bill_type = bill['bill_type']
|
|
if bill['bill_date'] == 'future':
|
|
# Future bills, deal with them at the end - they have dynamic start dates
|
|
if 'Hyundai' in bt_id_name[bill_type]:
|
|
future_car_bills.insert( 0, bill )
|
|
else:
|
|
future_D_quit_bills.insert( 0, bill )
|
|
return
|
|
|
|
|
|
################################################################################
|
|
# When we change the day D_quits, or we buyout the car, then future bills need
|
|
# to change/rebuild estimates, convenience routine used to handle this
|
|
################################################################################
|
|
def recalcFutureBills():
|
|
future_car_bills=[]
|
|
future_D_quit_bills=[]
|
|
|
|
print("Recalculating future bills as we changed a key date" )
|
|
finance_data = get_finance_data()
|
|
key_dates = calc_key_dates( finance_data )
|
|
bill_data = get_bill_data("order_by_date_only")
|
|
bill_types = get_bill_types()
|
|
bill_freqs = get_bill_freqs()
|
|
|
|
bt_id_freq = {row["id"]: row["freq"] for row in bill_types}
|
|
# this maps freq to bills per annum (e.g. id=2 to 4 bills per annum)
|
|
bf_id_num = {row["id"]: row["num_bills_per_annum"] for row in bill_freqs}
|
|
|
|
getFutureBills(bill_data, bill_types, future_car_bills, future_D_quit_bills)
|
|
deleteFutureEstimates()
|
|
# deal with future car bills
|
|
car_yr=key_dates['D_hyundai_owned'][0:4]
|
|
car_mmdd=key_dates['D_hyundai_owned'][5:]
|
|
for fb in future_car_bills:
|
|
amt=fb['amount']
|
|
bt=fb['bill_type']
|
|
# only can use simple growth as its a future bill
|
|
growth=bill_types[bt]['ann_growth_simple']
|
|
# factor in growth for next bills
|
|
for yr in range( int(car_yr), END_YEAR+1 ):
|
|
new_date=f"{yr}-{car_mmdd}"
|
|
new_bill( fb['bill_type'], amt, new_date, 1 )
|
|
amt += amt * growth/100
|
|
|
|
# deal with future D_Quit bills
|
|
D_quit_yr = key_dates['D_quit_date'][0:4]
|
|
dq_mm=key_dates['D_quit_date'][5:7]
|
|
dq_dd=key_dates['D_quit_date'][8:]
|
|
# avoid feb 29+ :)
|
|
if int(dq_dd) > 28: dq_dd=28
|
|
for fb in future_D_quit_bills:
|
|
# deal with future bills due to their starting dates being dynamic
|
|
amt=fb['amount']
|
|
bt=fb['bill_type']
|
|
growth=bill_types[bt]['ann_growth_simple']
|
|
num_ann_bills= bf_id_num[bt_id_freq[bt]]
|
|
if num_ann_bills == 1:
|
|
# factor in growth for next bill
|
|
for yr in range( int(D_quit_yr), END_YEAR+1 ):
|
|
new_date=f"{yr}-{dq_mm}-{dq_dd}"
|
|
# if we dont already have an annual bill for this year
|
|
new_bill( fb['bill_type'], amt, new_date, 1 )
|
|
amt += amt * growth/100
|
|
elif num_ann_bills == 12:
|
|
# do rest of this year, then next years
|
|
for m in range( int(dq_mm), 13):
|
|
new_date=f"{D_quit_yr}-{m:02d}-{dq_dd}"
|
|
new_bill( fb['bill_type'], amt, new_date, 1 )
|
|
for yr in range( int(D_quit_yr)+1, END_YEAR+1 ):
|
|
amt += amt * growth/100
|
|
for m in range( 1, 13):
|
|
new_date=f"{yr}-{m:02d}-{dq_dd}"
|
|
new_bill( fb['bill_type'], amt, new_date, 1 )
|
|
return
|