app.cashflow.update_cash() - Code Metrics - Inspection of "added wrapper for admin routes to simplify code an..." - whahn1983/pycashflow - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( fe0a02...8aaba9 )

by William

created 2023-12-19 03:03 UTC

app.cashflow.update_cash() B

↳ Parent: app.cashflow

Complexity

Conditions

Size

Total Lines	46
Code Lines	37

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	5
eloc	37
nop	1
dl	0
loc	46
rs	8.5253
c	0
b	0
f	0

from app import db
from .models import Schedule, Total, Running, Transactions, Skip, Balance
from datetime import datetime
import pandas as pd
import json
import plotly
import plotly.express as px
import os
from dateutil.relativedelta import relativedelta
from natsort import index_natsorted
import numpy as np
import decimal
import plotly.graph_objs as go
from pathlib import Path


def update_cash(balance):
    # if the database has been modified, update the calculations
    try:
        modifiedtime = os.path.getmtime(os.environ.get('DATABASE_URL').replace('sqlite:///', ''))
        modifiedtime = datetime.fromtimestamp(modifiedtime)
        modpath = os.environ.get('DATABASE_URL').replace('sqlite:///', '')
        modpath = modpath.replace('db.sqlite', 'modified')
        os.close(os.open(modpath, os.O_CREAT))
        dbmodified = os.path.getmtime(modpath)
        dbmodified = datetime.fromtimestamp(dbmodified)
    except:
        basedir = os.path.abspath(os.path.dirname(__file__))
        datafile = os.path.join(basedir, "data/db.sqlite")
        modifiedtime = os.path.getmtime(datafile)
        modifiedtime = datetime.fromtimestamp(modifiedtime)
        modpath = os.path.join(basedir, "data/modified")
        os.close(os.open(modpath, os.O_CREAT))
        dbmodified = os.path.getmtime(modpath)
        dbmodified = datetime.fromtimestamp(dbmodified)

    if modifiedtime > dbmodified:
        try:
            if balance.amount:
                db.session.query(Balance).delete()
                balance = Balance(amount=balance.amount, date=datetime.today())
                db.session.add(balance)
                db.session.commit()
        except:
            balance = Balance(amount='0',
                              date=datetime.today())
            db.session.add(balance)
            db.session.commit()

        # empty the tables to create fresh data from the schedule
        db.session.query(Total).delete()
        db.session.query(Running).delete()
        db.session.query(Transactions).delete()
        db.session.commit()

        # calculate total events for the year amount
        calc_schedule()

        # calculate sum of running transactions
        calc_transactions(balance)

        Path(modpath).touch()


def calc_schedule():
    months = 13
    weeks = 53
    years = 1
    quarters = 4
    biweeks = 27

    try:
        engine = db.create_engine(os.environ.get('DATABASE_URL')).connect()
    except:
        engine = db.create_engine('sqlite:///db.sqlite').connect()

    # pull the schedule information
    df = pd.read_sql('SELECT * FROM schedule;', engine)

    # loop through the schedule and create transactions in a table out to the future number of years
    todaydate = datetime.today().date()
    for i in range(len(df.index)):
        format = '%Y-%m-%d'
        name = df['name'][i]
        startdate = df['startdate'][i]
        frequency = df['frequency'][i]
        amount = df['amount'][i]
        type = df['type'][i]
        existing = Schedule.query.filter_by(name=name).first()
        if frequency == 'Monthly':
            for k in range(months):
                futuredate = datetime.strptime(startdate, format).date() + relativedelta(months=k)
                if futuredate <= todaydate:
                    existing.startdate = futuredate + relativedelta(months=1)
                if type == 'Income':
                    rollbackdate = datetime.combine(futuredate, datetime.min.time())
                    total = Total(type=type, name=name, amount=amount,
                                  date=pd.tseries.offsets.BDay(1).rollback(rollbackdate).date())
                else:
                    total = Total(type=type, name=name, amount=amount, date=futuredate - pd.tseries.offsets.BDay(0))
                db.session.add(total)
        elif frequency == 'Weekly':
            for k in range(weeks):
                futuredate = datetime.strptime(startdate, format).date() + relativedelta(weeks=k)
                if futuredate <= todaydate:
                    existing.startdate = futuredate + relativedelta(weeks=1)
                total = Total(type=type, name=name, amount=amount, date=futuredate - pd.tseries.offsets.BDay(0))
                db.session.add(total)
        elif frequency == 'Yearly':
            for k in range(years):
                futuredate = datetime.strptime(startdate, format).date() + relativedelta(years=k)
                if futuredate <= todaydate:
                    existing.startdate = futuredate + relativedelta(years=1)
                total = Total(type=type, name=name, amount=amount, date=futuredate - pd.tseries.offsets.BDay(0))
                db.session.add(total)
        elif frequency == 'Quarterly':
            for k in range(quarters):
                futuredate = datetime.strptime(startdate, format).date() + relativedelta(months=3 * k)
                if futuredate <= todaydate:
                    existing.startdate = futuredate + relativedelta(months=3)
                total = Total(type=type, name=name, amount=amount, date=futuredate - pd.tseries.offsets.BDay(0))
                db.session.add(total)
        elif frequency == 'BiWeekly':
            for k in range(biweeks):
                futuredate = datetime.strptime(startdate, format).date() + relativedelta(weeks=2 * k)
                if futuredate <= todaydate:
                    existing.startdate = futuredate + relativedelta(weeks=2)
                total = Total(type=type, name=name, amount=amount, date=futuredate - pd.tseries.offsets.BDay(0))
                db.session.add(total)
        elif frequency == 'Onetime':
            futuredate = datetime.strptime(startdate, format).date()
            if futuredate < todaydate:
                db.session.delete(existing)
            else:
                total = Total(type=type, name=name, amount=amount, date=futuredate)
                db.session.add(total)
    db.session.commit()

    # add the hold items
    df = pd.read_sql('SELECT * FROM hold;', engine)
    for i in range(len(df.index)):
        name = df['name'][i]
        amount = df['amount'][i]
        type = df['type'][i]
        total = Total(type=type, name=name, amount=amount, date=todaydate + relativedelta(days=1))
        db.session.add(total)
    db.session.commit()

    # add the skip items
    df = pd.read_sql('SELECT * FROM skip;', engine)
    for i in range(len(df.index)):
        format = '%Y-%m-%d'
        name = df['name'][i]
        amount = df['amount'][i]
        type = df['type'][i]
        date = df['date'][i]
        if datetime.strptime(date, format).date() < todaydate:
            skip = Skip.query.filter_by(name=name).first()
            db.session.delete(skip)
        else:
            total = Total(type=type, name=name, amount=amount, date=datetime.strptime(date, format).date())
            db.session.add(total)
    db.session.commit()


def calc_transactions(balance):
    try:
        engine = db.create_engine(os.environ.get('DATABASE_URL')).connect()
    except:
        engine = db.create_engine('sqlite:///db.sqlite').connect()

    # retrieve the total future transactions
    df = pd.read_sql('SELECT * FROM total;', engine)
    df = df.sort_values(by="date", key=lambda x: np.argsort(index_natsorted(df["date"])))

    # collect the next 60 days of transactions for the transactions table
    format = '%Y-%m-%d'
    todaydate = datetime.today().date()
    todaydateplus = todaydate + relativedelta(months=2)
    for i in df.iterrows():
        if todaydateplus > \
                datetime.strptime(i[1].date, format).date() > todaydate and "(SKIP)" not in i[1].iloc[3]:
            transactions = Transactions(name=i[1].iloc[3], type=i[1].type, amount=i[1].amount,
                                        date=datetime.strptime(i[1].date, format).date())
            db.session.add(transactions)
    db.session.commit()

    # for schedules marked as expenses, make the value negative for the sum
    for i in df.iterrows():
        id = i[1].id
        amount = i[1].amount
        type = i[1].type
        if type == 'Expense':
            amount = float(amount) * -1
            df.at[id - 1, 'amount'] = amount
        elif type == 'Income':
            pass

    # group total transactions by date and sum the amounts for each date
    df = df.groupby("date")['amount'].sum().reset_index()

    # loop through the total transactions by date and add the sums to the total balance amount
    runbalance = float(balance.amount)
    running = Running(amount=runbalance, date=datetime.today().date())
    db.session.add(running)
    for i in df.iterrows():
        format = '%Y-%m-%d'
        rundate = i[1].date
        amount = i[1].amount
        if datetime.strptime(rundate, format).date() > todaydate:
            runbalance += amount
            running = Running(amount=runbalance, date=datetime.strptime(rundate, format).date())
            db.session.add(running)
    db.session.commit()


def plot_cash():
    try:
        engine = db.create_engine(os.environ.get('DATABASE_URL')).connect()
    except:
        engine = db.create_engine('sqlite:///db.sqlite').connect()

    # plot the running balances by date on a line plot
    df = pd.read_sql('SELECT * FROM running;', engine)
    df = df.sort_values(by='date', ascending=False)
    format = '%Y-%m-%d'
    minbalance = df['amount'].min()
    minbalance = decimal.Decimal(str(minbalance)).quantize(decimal.Decimal('.01'))
    if float(minbalance) >= 0:
        minrange = 0
    else:
        minrange = float(minbalance) * 1.1
    maxbalance = 0
    todaydate = datetime.today().date()
    todaydateplus = todaydate + relativedelta(months=2)
    for i in df.iterrows():
        if todaydateplus > datetime.strptime(i[1].date, format).date() > todaydate:
            if i[1].amount > maxbalance:
                maxbalance = i[1].amount
    maxrange = maxbalance * 1.1
    start_date = str(datetime.today().date())
    end_date = str(datetime.today().date() + relativedelta(months=2))
    layout = go.Layout(yaxis=dict(range=[minrange, maxrange]), xaxis=dict(range=[start_date, end_date]),
                       margin=dict(l=5, r=20, t=35, b=5))
    fig = px.line(df, x="date", y="amount", template="plotly", title="Cash Flow", line_shape="spline")
    fig.update_layout(layout)
    fig.update_xaxes(title_text='Date')
    fig.update_yaxes(title_text='Amount')
    fig.update_layout(paper_bgcolor="PaleTurquoise")

    graphJSON = json.dumps(fig, cls=plotly.utils.PlotlyJSONEncoder)

    return minbalance, graphJSON

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

Push — master ( fe0a02...8aaba9 )

app.cashflow.update_cash() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like