def render_content(tab):
balanceData = db.runQueryFromFile(F_BALANCE, session.getUserIdParam())
savingsData = db.runQueryFromFile(F_SAVINGS, session.getUserIdParam())
if tab == 'tab1':
return html.Div([
html.H3('Balance over time'),
dcc.Graph(id='balance-graph',
figure=generateBarGraph(balanceData, "monthname",
["balance"], ["Balance"]))
])
elif tab == 'tab2':
return html.Div([
html.H3('Savings over time'),
dcc.Graph(id='savings-graph',
figure=generateBarGraph(savingsData, "monthname",
["savings"], ["Savings"]))
])
elif tab == 'tab3':
return html.Div([
html.H3('Savings over time'),
dcc.Graph(
id='income-graph',
figure=generateBarGraph(savingsData, "monthname",
["monthlycredit", "monthlydebit"],
["Income", "Expenses"]))
])
def generateMonthlyReport(month, categories):
params = [{
'name': 'month',
'value': [month]
}, {
'name': 'filter',
'value': categories
}, {
'name': 'userid',
'value': [session.getUserId()]
}]
return db.runQueryFromFile(F_MONTHLY, params)
def getSelectedCategories(filter):
if (filter != None and len(filter) > 0):
selectedCategories = filter
else:
categories_df = db.runQueryFromFile(F_GETCATEGORIES,
session.getUserIdParam())
selectedCategories = getAllCategories(categories_df)
#Empty list triggers SQL syntax error, append value ''
if len(selectedCategories) < 1:
selectedCategories.append('')
return selectedCategories
def generatePieChartData(month, categories):
params = [{
'name': 'month',
'value': [month]
}, {
'name': 'filter',
'value': categories
}, {
'name': 'userid',
'value': [session.getUserId()]
}]
return db.runQueryFromFile(F_MONTHLYBYCATEGORY, params)
def queryByAmount(self):
# params = [
# {'name': 'mincount', 'value': [self.minOccurrences]}
# ]
dataSet = db.runQueryFromFile(self.F_RECURRINGBYAMOUNT,
session.getUserIdParam())
for row in dataSet.values:
# debit, COUNT(*), min(date) as first, max(date) as last
amount = row[0] # CurrencyCol()
count = row[1] # IntCol()
startDate = row[2] # DateCol()
lastDate = row[3] # DateCol()
businessId = 0
name = 'Check'
type = RecurrentExpense.TYPE_FIXED # StringCol()
avgAmount = 0 # CurrencyCol()
maxAmount = 0 # CurrencyCol()
minAmount = 0 # CurrencyCol()
# If recurring expense is already tracked by business, skip it
# This is no longer relevant because we only track checks here
# tracker = self.getExpenseTrackerByBusiness(businessId)
# if (tracker != None):
# print('Recurring expense already tracked by business ' + str(businessId))
# continue
if math.isnan(amount):
continue
tracker = self.getExpenseTrackerByAmount(amount)
if (tracker == None):
tracker = RecurrentExpense(businessId=0,
name=name,
type=type,
amount=amount,
count=count,
startDate=startDate,
lastDate=lastDate,
avgAmount=avgAmount,
minAmount=minAmount,
maxAmount=maxAmount,
userId=session.getUserId())
print('New expense tracked by amount ' + str(amount))
else:
tracker.update(0, name, type, amount, count, startDate,
lastDate, avgAmount, minAmount, maxAmount)
print('Update expense tracked by amount ' + str(amount))
self.trackers.append(tracker)
print(tracker.id)
return
def generateTrackersReport(dateRange):
if dateRange is None:
return None
params = [{
'name': 'start',
'value': [dateRange[0]]
}, {
'name': 'stop',
'value': [dateRange[1]]
}, {
'name': 'userid',
'value': [session.getUserId()]
}]
return db.runQueryFromFile(F_TRACKERS, params)
def queryByBusiness(self):
# params = [
# {'name': 'mincount', 'value': [self.minOccurrences]}
# ]
dataSet = db.runQueryFromFile(self.F_RECURRINGBYBUSINESS,
session.getUserIdParam())
for row in dataSet.values:
name = row[2] #StringCol()
businessId = row[0] #IntCol()
amount = 0 #CurrencyCol()
type = RecurrentExpense.TYPE_VARIABLE #StringCol()
count = row[1] #IntCol()
startDate = row[6] #DateCol()
lastDate = row[7] #DateCol()
avgAmount = row[3] #CurrencyCol()
maxAmount = row[5] #CurrencyCol()
minAmount = row[4] #CurrencyCol()
tracker = self.getExpenseTrackerByBusiness(businessId)
if (tracker == None):
tracker = RecurrentExpense(businessId=businessId,
name=name,
type=type,
amount=amount,
count=count,
startDate=startDate,
lastDate=lastDate,
avgAmount=avgAmount,
minAmount=minAmount,
maxAmount=maxAmount,
userId=session.getUserId())
print('New expense tracked by business ' + str(businessId))
else:
tracker.update(businessId, name, type, amount, count,
startDate, lastDate, avgAmount, minAmount,
maxAmount)
print('Update expense tracked by business ' + str(businessId))
self.trackers.append(tracker)
print(tracker.id)
return
def addCategory(category):
categories_df = db.runQueryFromFile(F_GETCATEGORIES,
session.getUserIdParam())
categories_list = getAllCategories(categories_df)
if category not in categories_list:
categories_list.append(category)
def updateCategoryFilter(title, newCategory):
categories_df = db.runQueryFromFile(F_GETCATEGORIES,
session.getUserIdParam())
filter = getDropDownData(categories_df)
return filter
def updateMonthSelector(title):
selectableMonths = db.runQueryFromFile(F_GETMONTHS,
session.getUserIdParam())
months = getDropDownData(selectableMonths)
return months
def getDataPoints():
dataFrame = db.runQueryFromFile(F_GETRECURRINGDATA,
session.getUserIdParam())
dataPoints = TimeSeriesData(dataFrame)
return dataPoints
def getDataPoints():
dataFrame = db.runQueryFromFile(F_GETRECURRINGDATA)
dataPoints = TimeSeriesData(dataFrame)
return dataPoints
'type': 'bar',
'name': names[i]
} for i in range(0, (len(yNames)))]
}
F_BALANCE = 'src/queryBalanceReport.sql'
F_SAVINGS = 'src/querySavingsReport.sql'
F_MONTHLY = 'src/queryMonthlyReport.sql'
#Q_REPORT = 'SELECT * FROM credit_entry'
#Q_MONTHLY = "select report_date, purchase_date,business,card_number,credit,debit from credit_entry where to_char(report_date, 'YYYY-MM') = '2018-04' order by purchase_date asc"
#balanceData = db.runQueryFromFile(F_BALANCE)
#savingsData = db.runQueryFromFile(F_SAVINGS)
#reportData = db.runQuery(Q_MONTHLY)
reportData = db.runQueryFromFile(F_MONTHLY)
app = dash.Dash()
app.css.append_css(
{"external_url": "https://codepen.io/chriddyp/pen/bWLwgP.css"})
app.layout = html.Div(children=[
html.H4(children='Bank Report - Work In Pogress'),
# dcc.Graph(id='balance-graph',figure=generateBarGraph(balanceData,"monthname",["balance"],["Balance"])),
# dcc.Graph(id='savings-graph',figure=generateBarGraph(savingsData,"monthname",["savings"],["Savings"])),
# dcc.Graph(id='income-graph',figure=generateBarGraph(savingsData,"monthname",["monthlycredit","monthlydebit"],["Income","Expenses"])),
generateTable(reportData),
html.Div(id='output')
])
def updateCategoriesGraph(title):
categoriesData = db.runQueryFromFile(F_CATEGORIESOVERTIME,
session.getUserIdParam())
return generateTimeSeries(getCategories(), categoriesData)
def getCategories():
df = db.runQueryFromFile(F_GETCATEGORIES, session.getUserIdParam())
list = [v[0] for v in df.values]
return list
