def invalidate_cache(flash_message=True):
logging.info('Invalidating cache.')
if request.method == 'POST':
Data.invalidate_cache()
if flash_message:
flash('Cache was successfully invalidated.', 'info')
return _standard_render()
def data_upload():
if request.method == 'POST':
if 'file' not in request.files:
flash(f'No file provided.', 'warning')
else:
f = request.files['file']
fn = secure_filename(f.filename)
if not f or not fn.endswith('.csv'):
flash('Not an identified CSV file.', 'warning')
else:
try:
stream = io.StringIO(f.stream.read().decode("UTF8"),
newline=None)
stream.seek(0)
input_df = pd.read_csv(stream, index_col=False)
# TODO - necessary?
# input_df = Data.cast_df_to_schema(input_df, Schema.BANK_TB_NAME)
Data.merge(input_df)
update_html_df()
# to ensure visuals and insights take in the most up to date information
invalidate_cache(flash=False)
refresh_dynamic_insights()
refresh_static_insights(ignore_clock=True)
except IOError as e:
flash(f'Unable to parse file. {e}', 'warning')
flash('Awesome! File processed successfully.', 'success')
return _standard_render()
def update_transaction_history():
mrtd_org = Data.get_most_recent_transaction_date(BankSchema.BANK_TB_NAME, files['base_db'])
mrtd = BankSchema.DATE_FORMAT.format(dateparser.parse(mrtd_org).date()) if mrtd_org else BankSchema.DATE_FORMAT.format(
datetime.date.min)
# update on transactions only following the mrtd
start_date, end_date = mrtd, BankSchema.DATE_FORMAT.format(datetime.datetime.now())
full_df = get_transactions(start_date, end_date)
# TODO - this stuff isn't getting flashed
p1 = 'Successfully updated your bank to the latest transaction history.'
p2 = '\nNo previous data was found in your database so your entire transactions history was applied.'
p3 = f'\nFrom date {str(start_date)} to {str(end_date)}.'
p4 = f'No new transactions have been found.'
try:
if full_df is None or full_df.shape[0] == 0:
flash(p4, 'info')
return _standard_render()
logger.info(f'Merging transactions into {BankSchema.BANK_TB_NAME} database.')
Data.merge(full_df, files['base_db'])
if not mrtd_org:
flash(p1 + p2, 'info')
else:
flash(p1 + p3, 'info')
except Exception as e:
flash(e, 'warning')
# update data-state on /date end
if full_df is not None and full_df.shape[0] > 0:
# note: need to run from `as_html_form_from_sql` because of the
# additional preprocessing that is handled in merge operation
update_html_df()
refresh_static_insights()
refresh_dynamic_insights()
return _standard_render()
def cli_user_populate_missing_categories(
self, auto_fill_with_model_suggestion=False):
"""
obj: have the user manually update missing columns, and update the db accordingly.
this is not used by the flask application itself. it is a command line utility
"""
missing_cat_df = Data.get_missing_categories()
if len(missing_cat_df) == 0:
logger.info('No rows identified to categorize.')
return
model_c1 = self.classifier.get_model(BankSchema.BASE_MODEL_C1_NAME)
model_c2 = self.classifier.get_model(BankSchema.BASE_MODEL_C2_NAME)
user_c1_cats, user_c2_cats = [], []
if not auto_fill_with_model_suggestion:
print(">> Current Categories::")
print(
">> " +
pprint.pformat(self.get_current_unique_categories(), indent=4))
print(
">> Note: press enter to take suggestion, or 'none' for no category",
end='\n\n')
bar = ProgressBar()
for index in bar(range(missing_cat_df.shape[0])):
row = missing_cat_df.iloc[index]
print(">>\n" +
tabulate(row.to_frame().T, headers='keys', tablefmt='psql'))
suggestion_c1 = model_c1.predict(row[
Data.get_missing_categories()].values) if model_c1 else None
suggestion_c2 = model_c2.predict(row[
Data.get_missing_categories()].values) if model_c2 else None
category_c1, category_c2 = None, None
if not auto_fill_with_model_suggestion:
if suggestion_c1:
print(f">> Suggestion c1: {suggestion_c1}")
category_c1 = input(
'>> Enter Expense super category: ').lower().strip()
if suggestion_c2:
print(f">> Suggestion c2: {suggestion_c2}")
category_c2 = input(
'>> Enter Expense secondary category: ').lower().strip()
category_c1 = None if category_c1 == 'none' else category_c1
category_c2 = None if category_c2 == 'none' else category_c2
print('\n\n')
user_c1_cats.append(category_c1 if bool(category_c1)
or not suggestion_c1 else suggestion_c1)
user_c2_cats.append(category_c2 if bool(category_c2)
or not suggestion_c2 else suggestion_c2)
pk_fields, cat_fields = BankSchema.get_pk_fields(
), BankSchema.get_table_chase_y_fields()
filled_cat_df = missing_cat_df[pk_fields]
pd.options.mode.chained_assignment = None
filled_cat_df[cat_fields[0]] = user_c1_cats
filled_cat_df[cat_fields[1]] = user_c2_cats
Data.update_missing_categories(filled_cat_df.to_dict('records'),
pk_fields, cat_fields)
def data_truncate():
if request.method == 'POST':
Data.truncate_all_tables()
DataState.html_df = None
invalidate_cache(flash=False)
refresh_dynamic_insights()
refresh_static_insights(ignore_clock=True)
flash(
f"{BankSchema.BANK_TB_NAME} table has been fully truncated from the base database.",
'info')
return _standard_render()
def data_update():
if request.method == 'POST':
form = request.form
update_df = pd.read_html(form['hidden_post_name'],
header=0,
index_col=0,
parse_dates=True)[0]
update_df = Data.cast_df_to_schema(update_df, BankSchema.BANK_TB_NAME)
Data.merge(update_df)
update_html_df()
invalidate_cache(flash=False)
refresh_dynamic_insights()
refresh_static_insights(ignore_clock=True)
flash('Data updated with new csv.', 'info')
return _standard_render()
def get_money_gain_and_spent_this_month_vs_last_month(
date_range,
table_name,
_overide_month=None,
as_dataframe=False,
db_path=files['base_db']):
df = Data.get_table_as_df(date_range, table_name, db_path=db_path)
if df is None or df.shape[0] == 0:
return None
df[BankSchema.SCHEMA_BANK_DATE.name] = pd.to_datetime(
df[BankSchema.SCHEMA_BANK_DATE.name])
this_month = _overide_month or datetime.date.today().month
last_month = this_month - 1 or 12
inc_this_month, exp_this_month = RawInsights.Static._agg_income_and_expenses_by_month(
df, this_month)
sum_income_last_month, exp_last_month = RawInsights.Static._agg_income_and_expenses_by_month(
df, last_month)
as_dict = {
'inc_this_month': "{0:.2f}".format(inc_this_month),
'exp_this_month': "{0:.2f}".format(exp_this_month),
'inc_last_month': "{0:.2f}".format(sum_income_last_month),
'exp_last_month': "{0:.2f}".format(exp_last_month)
}
if not as_dataframe:
return as_dict
else:
# convert to a single row dataframe
_temp_df = pd.DataFrame(list(as_dict.values())).T
_temp_df.columns = list(as_dict.keys())
return _temp_df
def as_html_form_from_sql(table_name, date_range, order_by_col_name, order, table_id=None,
replace_default_data_frame_class_with=None):
rddfcw = replace_default_data_frame_class_with
df = Data.get_table_as_df(date_range, table_name, order_by_col_name, order)
if df is None or df.shape[0] == 0:
return None
return HTMLHelper.as_html_form_from_df(df, table_id, make_editable=True,
replace_default_data_frame_class_with=rddfcw)
def _top_n_categories(inc_or_exp, date_range, table_name, n, db_path):
df = Data.get_table_as_df(date_range, table_name, db_path=db_path)
if df is None or df.shape[0] == 0:
return None
df_filter = df[df[BankSchema.SCHEMA_BANK_INC_OR_EXP.name] ==
inc_or_exp]
most_common_filter = RawInsights.Dynammic._agg_top_n_income_and_expense_categories(
df_filter, n)
return pd.DataFrame(most_common_filter,
columns=['category', 'frequency'])
def table_summary_statistics(table_name,
date_range,
db_path=files['base_db']):
pd.options.html.border = 0
summary_df = Data.get_table_as_df(date_range,
table_name,
db_path=db_path)
if summary_df is None or summary_df.shape[0] == 0:
logger.debug(
'Unable to instantiate summary statistics table. The database is probably empty.'
)
return None
else:
return summary_df.describe()
def visuals_redraw():
if request.method == 'POST':
form = request.form
drs, dre = form['start_query_name'], form['end_query_name']
if not valid_dr(drs, dre):
return _standard_render()
State.date_range_start, State.date_range_end = drs, dre
# update which visuals to draw and redraw them
requested_plots_to_draw = set(ImageRegistry.get_all_plot_ids()) & set(form.keys())
_update_requested_plots_to_draw(set(requested_plots_to_draw))
df = Data.get_table_as_df(DateRange(State.date_range_start, State.date_range_end),
table_name=BankSchema.BANK_TB_NAME)
if df is None or df.shape[0] == 0:
flash(f'No data int table {BankSchema.BANK_TB_NAME} produce diagrams.', 'warning')
else:
ImageRegistry.plot_all(list(requested_plots_to_draw), df, drs, dre)
return _standard_render()
def data_download():
if request.method == 'POST':
logger.info('Downloading bank data as csv file.')
with tempfile.TemporaryDirectory() as tmpdir:
dr = DateRange(State.date_range_start, State.date_range_end)
bank_df = Data.get_table_as_df(dr, BankSchema.BANK_TB_NAME,
DataState.order_by_column_name,
DataState.order_by)
if bank_df is None or bank_df.shape[0] == 0:
flash(
f'{BankSchema.BANK_TB_NAME} table is empty. Nothing to download.',
'warning')
return _standard_render()
bank_df.to_csv(os.path.join(tmpdir, ConstData.FILE_NAME_DOWNLOAD),
index=False)
return send_from_directory(directory=tmpdir,
filename=ConstData.FILE_NAME_DOWNLOAD,
as_attachment=True)
return _standard_render()
def spending_heatmap(date_range, db_path=files['base_db'], **kwargs):
logging.info('Plotting spending_heatmap plot')
hm_df = Data.get_heatmap_df(date_range, db_path)
if hm_df is None:
logging.warning(
'Query for heatmap dataframe returned as empty, ignoring plot.'
)
return None
hm_df = hm_df.dropna()
hmap = folium.Map(zoom_start=6, location=Visuals.HM_START_LAT_LON)
hm_wide = HeatMap(list(
zip(hm_df[BankSchema.SCHEMA_LOCATION_LAT.name].values,
hm_df[BankSchema.SCHEMA_LOCATION_LON.name].values,
hm_df[BankSchema.SCHEMA_BANK_AMOUNT.name].values)),
min_opacity=0.2,
radius=17,
blur=15,
max_zoom=1)
hmap.add_child(hm_wide)
return hmap.get_root().render()
def __init__(self):
self.classifier = BankClassifier()
self.model_df = Data.get_table_as_df(date_range=None)
self.current_categories_dict = None
assert self.model_df.shape[0] > 0, "No bank static was found."
def _get_html_from_ids():
return {image_id: Data.get_most_recent_html_from_id(image_id) for image_id in VisualState.plot_selections.keys()
if VisualState.plot_selections[image_id]}
def __init__(self):
self.model_df = Data.get_table_as_df(date_range=None)
assert self.model_df.shape[0] > 0, "No bank static was found."
def db():
Data.create_base_db()