|
1
|
|
|
from app import db |
|
2
|
|
|
from .models import Schedule, Total, Running, Transactions, Skip, Balance |
|
3
|
|
|
from datetime import datetime |
|
4
|
|
|
import pandas as pd |
|
5
|
|
|
import json |
|
6
|
|
|
import plotly |
|
7
|
|
|
import plotly.express as px |
|
8
|
|
|
import os |
|
9
|
|
|
from dateutil.relativedelta import relativedelta |
|
10
|
|
|
from natsort import index_natsorted |
|
11
|
|
|
import numpy as np |
|
12
|
|
|
import decimal |
|
13
|
|
|
import plotly.graph_objs as go |
|
14
|
|
|
from pathlib import Path |
|
15
|
|
|
|
|
16
|
|
|
|
|
17
|
|
|
def update_cash(balance): |
|
18
|
|
|
# if the database has been modified, update the calculations |
|
19
|
|
|
try: |
|
20
|
|
|
modifiedtime = os.path.getmtime(os.environ.get('DATABASE_URL').replace('sqlite:///', '')) |
|
21
|
|
|
modifiedtime = datetime.fromtimestamp(modifiedtime) |
|
22
|
|
|
modpath = os.environ.get('DATABASE_URL').replace('sqlite:///', '') |
|
23
|
|
|
modpath = modpath.replace('db.sqlite', 'modified') |
|
24
|
|
|
os.close(os.open(modpath, os.O_CREAT)) |
|
25
|
|
|
dbmodified = os.path.getmtime(modpath) |
|
26
|
|
|
dbmodified = datetime.fromtimestamp(dbmodified) |
|
27
|
|
|
except: |
|
28
|
|
|
basedir = os.path.abspath(os.path.dirname(__file__)) |
|
29
|
|
|
datafile = os.path.join(basedir, "data/db.sqlite") |
|
30
|
|
|
modifiedtime = os.path.getmtime(datafile) |
|
31
|
|
|
modifiedtime = datetime.fromtimestamp(modifiedtime) |
|
32
|
|
|
modpath = os.path.join(basedir, "data/modified") |
|
33
|
|
|
os.close(os.open(modpath, os.O_CREAT)) |
|
34
|
|
|
dbmodified = os.path.getmtime(modpath) |
|
35
|
|
|
dbmodified = datetime.fromtimestamp(dbmodified) |
|
36
|
|
|
|
|
37
|
|
|
if modifiedtime > dbmodified: |
|
38
|
|
|
try: |
|
39
|
|
|
if balance.amount: |
|
40
|
|
|
db.session.query(Balance).delete() |
|
41
|
|
|
balance = Balance(amount=balance.amount, date=datetime.today()) |
|
42
|
|
|
db.session.add(balance) |
|
43
|
|
|
db.session.commit() |
|
44
|
|
|
except: |
|
45
|
|
|
balance = Balance(amount='0', |
|
46
|
|
|
date=datetime.today()) |
|
47
|
|
|
db.session.add(balance) |
|
48
|
|
|
db.session.commit() |
|
49
|
|
|
|
|
50
|
|
|
# empty the tables to create fresh data from the schedule |
|
51
|
|
|
db.session.query(Total).delete() |
|
52
|
|
|
db.session.query(Running).delete() |
|
53
|
|
|
db.session.query(Transactions).delete() |
|
54
|
|
|
db.session.commit() |
|
55
|
|
|
|
|
56
|
|
|
# calculate total events for the year amount |
|
57
|
|
|
calc_schedule() |
|
58
|
|
|
|
|
59
|
|
|
# calculate sum of running transactions |
|
60
|
|
|
calc_transactions(balance) |
|
61
|
|
|
|
|
62
|
|
|
Path(modpath).touch() |
|
63
|
|
|
|
|
64
|
|
|
|
|
65
|
|
|
def calc_schedule(): |
|
66
|
|
|
months = 13 |
|
67
|
|
|
weeks = 53 |
|
68
|
|
|
years = 1 |
|
69
|
|
|
quarters = 4 |
|
70
|
|
|
biweeks = 27 |
|
71
|
|
|
|
|
72
|
|
|
try: |
|
73
|
|
|
engine = db.create_engine(os.environ.get('DATABASE_URL')).connect() |
|
74
|
|
|
except: |
|
75
|
|
|
engine = db.create_engine('sqlite:///db.sqlite').connect() |
|
76
|
|
|
|
|
77
|
|
|
# pull the schedule information |
|
78
|
|
|
df = pd.read_sql('SELECT * FROM schedule;', engine) |
|
79
|
|
|
|
|
80
|
|
|
# loop through the schedule and create transactions in a table out to the future number of years |
|
81
|
|
|
todaydate = datetime.today().date() |
|
82
|
|
|
for i in range(len(df.index)): |
|
83
|
|
|
format = '%Y-%m-%d' |
|
84
|
|
|
name = df['name'][i] |
|
85
|
|
|
startdate = df['startdate'][i] |
|
86
|
|
|
frequency = df['frequency'][i] |
|
87
|
|
|
amount = df['amount'][i] |
|
88
|
|
|
type = df['type'][i] |
|
89
|
|
|
existing = Schedule.query.filter_by(name=name).first() |
|
90
|
|
|
if frequency == 'Monthly': |
|
91
|
|
|
for k in range(months): |
|
92
|
|
|
futuredate = datetime.strptime(startdate, format).date() + relativedelta(months=k) |
|
93
|
|
|
if futuredate <= todaydate: |
|
94
|
|
|
existing.startdate = futuredate + relativedelta(months=1) |
|
95
|
|
|
if type == 'Income': |
|
96
|
|
|
rollbackdate = datetime.combine(futuredate, datetime.min.time()) |
|
97
|
|
|
total = Total(type=type, name=name, amount=amount, |
|
98
|
|
|
date=pd.tseries.offsets.BDay(1).rollback(rollbackdate).date()) |
|
99
|
|
|
else: |
|
100
|
|
|
total = Total(type=type, name=name, amount=amount, date=futuredate - pd.tseries.offsets.BDay(0)) |
|
101
|
|
|
db.session.add(total) |
|
102
|
|
|
elif frequency == 'Weekly': |
|
103
|
|
|
for k in range(weeks): |
|
104
|
|
|
futuredate = datetime.strptime(startdate, format).date() + relativedelta(weeks=k) |
|
105
|
|
|
if futuredate <= todaydate: |
|
106
|
|
|
existing.startdate = futuredate + relativedelta(weeks=1) |
|
107
|
|
|
total = Total(type=type, name=name, amount=amount, date=futuredate - pd.tseries.offsets.BDay(0)) |
|
108
|
|
|
db.session.add(total) |
|
109
|
|
|
elif frequency == 'Yearly': |
|
110
|
|
|
for k in range(years): |
|
111
|
|
|
futuredate = datetime.strptime(startdate, format).date() + relativedelta(years=k) |
|
112
|
|
|
if futuredate <= todaydate: |
|
113
|
|
|
existing.startdate = futuredate + relativedelta(years=1) |
|
114
|
|
|
total = Total(type=type, name=name, amount=amount, date=futuredate - pd.tseries.offsets.BDay(0)) |
|
115
|
|
|
db.session.add(total) |
|
116
|
|
|
elif frequency == 'Quarterly': |
|
117
|
|
|
for k in range(quarters): |
|
118
|
|
|
futuredate = datetime.strptime(startdate, format).date() + relativedelta(months=3 * k) |
|
119
|
|
|
if futuredate <= todaydate: |
|
120
|
|
|
existing.startdate = futuredate + relativedelta(months=3) |
|
121
|
|
|
total = Total(type=type, name=name, amount=amount, date=futuredate - pd.tseries.offsets.BDay(0)) |
|
122
|
|
|
db.session.add(total) |
|
123
|
|
|
elif frequency == 'BiWeekly': |
|
124
|
|
|
for k in range(biweeks): |
|
125
|
|
|
futuredate = datetime.strptime(startdate, format).date() + relativedelta(weeks=2 * k) |
|
126
|
|
|
if futuredate <= todaydate: |
|
127
|
|
|
existing.startdate = futuredate + relativedelta(weeks=2) |
|
128
|
|
|
total = Total(type=type, name=name, amount=amount, date=futuredate - pd.tseries.offsets.BDay(0)) |
|
129
|
|
|
db.session.add(total) |
|
130
|
|
|
elif frequency == 'Onetime': |
|
131
|
|
|
futuredate = datetime.strptime(startdate, format).date() |
|
132
|
|
|
if futuredate < todaydate: |
|
133
|
|
|
db.session.delete(existing) |
|
134
|
|
|
else: |
|
135
|
|
|
total = Total(type=type, name=name, amount=amount, date=futuredate) |
|
136
|
|
|
db.session.add(total) |
|
137
|
|
|
db.session.commit() |
|
138
|
|
|
|
|
139
|
|
|
# add the hold items |
|
140
|
|
|
df = pd.read_sql('SELECT * FROM hold;', engine) |
|
141
|
|
|
for i in range(len(df.index)): |
|
142
|
|
|
name = df['name'][i] |
|
143
|
|
|
amount = df['amount'][i] |
|
144
|
|
|
type = df['type'][i] |
|
145
|
|
|
total = Total(type=type, name=name, amount=amount, date=todaydate + relativedelta(days=1)) |
|
146
|
|
|
db.session.add(total) |
|
147
|
|
|
db.session.commit() |
|
148
|
|
|
|
|
149
|
|
|
# add the skip items |
|
150
|
|
|
df = pd.read_sql('SELECT * FROM skip;', engine) |
|
151
|
|
|
for i in range(len(df.index)): |
|
152
|
|
|
format = '%Y-%m-%d' |
|
153
|
|
|
name = df['name'][i] |
|
154
|
|
|
amount = df['amount'][i] |
|
155
|
|
|
type = df['type'][i] |
|
156
|
|
|
date = df['date'][i] |
|
157
|
|
|
if datetime.strptime(date, format).date() < todaydate: |
|
158
|
|
|
skip = Skip.query.filter_by(name=name).first() |
|
159
|
|
|
db.session.delete(skip) |
|
160
|
|
|
else: |
|
161
|
|
|
total = Total(type=type, name=name, amount=amount, date=datetime.strptime(date, format).date()) |
|
162
|
|
|
db.session.add(total) |
|
163
|
|
|
db.session.commit() |
|
164
|
|
|
|
|
165
|
|
|
|
|
166
|
|
|
def calc_transactions(balance): |
|
167
|
|
|
try: |
|
168
|
|
|
engine = db.create_engine(os.environ.get('DATABASE_URL')).connect() |
|
169
|
|
|
except: |
|
170
|
|
|
engine = db.create_engine('sqlite:///db.sqlite').connect() |
|
171
|
|
|
|
|
172
|
|
|
# retrieve the total future transactions |
|
173
|
|
|
df = pd.read_sql('SELECT * FROM total;', engine) |
|
174
|
|
|
df = df.sort_values(by="date", key=lambda x: np.argsort(index_natsorted(df["date"]))) |
|
175
|
|
|
|
|
176
|
|
|
# collect the next 60 days of transactions for the transactions table |
|
177
|
|
|
format = '%Y-%m-%d' |
|
178
|
|
|
todaydate = datetime.today().date() |
|
179
|
|
|
todaydateplus = todaydate + relativedelta(months=2) |
|
180
|
|
|
for i in df.iterrows(): |
|
181
|
|
|
if todaydateplus > \ |
|
182
|
|
|
datetime.strptime(i[1].date, format).date() > todaydate and "(SKIP)" not in i[1].iloc[3]: |
|
183
|
|
|
transactions = Transactions(name=i[1].iloc[3], type=i[1].type, amount=i[1].amount, |
|
184
|
|
|
date=datetime.strptime(i[1].date, format).date()) |
|
185
|
|
|
db.session.add(transactions) |
|
186
|
|
|
db.session.commit() |
|
187
|
|
|
|
|
188
|
|
|
# for schedules marked as expenses, make the value negative for the sum |
|
189
|
|
|
for i in df.iterrows(): |
|
190
|
|
|
id = i[1].id |
|
191
|
|
|
amount = i[1].amount |
|
192
|
|
|
type = i[1].type |
|
193
|
|
|
if type == 'Expense': |
|
194
|
|
|
amount = float(amount) * -1 |
|
195
|
|
|
df.at[id - 1, 'amount'] = amount |
|
196
|
|
|
elif type == 'Income': |
|
197
|
|
|
pass |
|
198
|
|
|
|
|
199
|
|
|
# group total transactions by date and sum the amounts for each date |
|
200
|
|
|
df = df.groupby("date")['amount'].sum().reset_index() |
|
201
|
|
|
|
|
202
|
|
|
# loop through the total transactions by date and add the sums to the total balance amount |
|
203
|
|
|
runbalance = float(balance.amount) |
|
204
|
|
|
running = Running(amount=runbalance, date=datetime.today().date()) |
|
205
|
|
|
db.session.add(running) |
|
206
|
|
|
for i in df.iterrows(): |
|
207
|
|
|
format = '%Y-%m-%d' |
|
208
|
|
|
rundate = i[1].date |
|
209
|
|
|
amount = i[1].amount |
|
210
|
|
|
if datetime.strptime(rundate, format).date() > todaydate: |
|
211
|
|
|
runbalance += amount |
|
212
|
|
|
running = Running(amount=runbalance, date=datetime.strptime(rundate, format).date()) |
|
213
|
|
|
db.session.add(running) |
|
214
|
|
|
db.session.commit() |
|
215
|
|
|
|
|
216
|
|
|
|
|
217
|
|
|
def plot_cash(): |
|
218
|
|
|
try: |
|
219
|
|
|
engine = db.create_engine(os.environ.get('DATABASE_URL')).connect() |
|
220
|
|
|
except: |
|
221
|
|
|
engine = db.create_engine('sqlite:///db.sqlite').connect() |
|
222
|
|
|
|
|
223
|
|
|
# plot the running balances by date on a line plot |
|
224
|
|
|
df = pd.read_sql('SELECT * FROM running;', engine) |
|
225
|
|
|
df = df.sort_values(by='date', ascending=False) |
|
226
|
|
|
format = '%Y-%m-%d' |
|
227
|
|
|
minbalance = df['amount'].min() |
|
228
|
|
|
minbalance = decimal.Decimal(str(minbalance)).quantize(decimal.Decimal('.01')) |
|
229
|
|
|
if float(minbalance) >= 0: |
|
230
|
|
|
minrange = 0 |
|
231
|
|
|
else: |
|
232
|
|
|
minrange = float(minbalance) * 1.1 |
|
233
|
|
|
maxbalance = 0 |
|
234
|
|
|
todaydate = datetime.today().date() |
|
235
|
|
|
todaydateplus = todaydate + relativedelta(months=2) |
|
236
|
|
|
for i in df.iterrows(): |
|
237
|
|
|
if todaydateplus > datetime.strptime(i[1].date, format).date() > todaydate: |
|
238
|
|
|
if i[1].amount > maxbalance: |
|
239
|
|
|
maxbalance = i[1].amount |
|
240
|
|
|
maxrange = maxbalance * 1.1 |
|
241
|
|
|
start_date = str(datetime.today().date()) |
|
242
|
|
|
end_date = str(datetime.today().date() + relativedelta(months=2)) |
|
243
|
|
|
layout = go.Layout(yaxis=dict(range=[minrange, maxrange]), xaxis=dict(range=[start_date, end_date]), |
|
244
|
|
|
margin=dict(l=5, r=20, t=35, b=5)) |
|
245
|
|
|
fig = px.line(df, x="date", y="amount", template="plotly", title="Cash Flow", line_shape="spline") |
|
246
|
|
|
fig.update_layout(layout) |
|
247
|
|
|
fig.update_xaxes(title_text='Date') |
|
248
|
|
|
fig.update_yaxes(title_text='Amount') |
|
249
|
|
|
fig.update_layout(paper_bgcolor="PaleTurquoise") |
|
250
|
|
|
|
|
251
|
|
|
graphJSON = json.dumps(fig, cls=plotly.utils.PlotlyJSONEncoder) |
|
252
|
|
|
|
|
253
|
|
|
return minbalance, graphJSON |
whahn1983 /
pycashflow
