app.cashflow.update_cash() - Code Metrics - Inspection of "Fix for end of the month dates for quarterly trans..." - whahn1983/pycashflow - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( f30af1...63b945 )

by William

created 2024-02-09 16:09 UTC

app.cashflow.update_cash() B

↳ Parent: app.cashflow

Complexity

Conditions

Size

Total Lines	49
Code Lines	39

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	7
eloc	39
nop	2
dl	0
loc	49
rs	7.544
c	0
b	0
f	0

from app import db
from .models import Schedule, Total, Running, Transactions, Skip, Balance
from datetime import datetime, date
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, refresh):
    # 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)

    dt = date.today()
    today = datetime.combine(dt, datetime.min.time())

    if modifiedtime > dbmodified or dbmodified < today or refresh == 1:
        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]
        firstdate = df['firstdate'][i]
        frequency = df['frequency'][i]
        amount = df['amount'][i]
        type = df['type'][i]
        existing = Schedule.query.filter_by(name=name).first()
        if not firstdate:
            existing.firstdate = datetime.strptime(startdate, format).date()
            firstdate = existing.firstdate.strftime(format)
            db.session.commit()
        if frequency == 'Monthly':
            for k in range(months):
                futuredate = datetime.strptime(startdate, format).date() + relativedelta(months=k)
                futuredateday = futuredate.day
                firstdateday = datetime.strptime(firstdate, format).date().day
                if firstdateday > futuredateday:
                    try:
                        for m in range(3):
                            futuredateday += 1
                            if firstdateday >= futuredateday:
                                futuredate = futuredate.replace(day=futuredateday)
                    except ValueError:
                        pass
                if futuredate <= todaydate:

                    existing.startdate = futuredate + relativedelta(months=1)
                    daycheckdate = futuredate + relativedelta(months=1)
                    daycheck = daycheckdate.day
                    if firstdateday > daycheck:
                        try:
                            for m in range(3):
                                daycheck += 1
                                if firstdateday >= daycheck:
                                    existing.startdate = daycheckdate.replace(day=daycheck)
                        except ValueError:
                            pass
                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)
                futuredateday = futuredate.day
                firstdateday = datetime.strptime(firstdate, format).date().day
                if firstdateday > futuredateday:
                    try:
                        for m in range(3):
                            futuredateday += 1
                            if firstdateday >= futuredateday:
                                futuredate = futuredate.replace(day=futuredateday)
                    except ValueError:
                        pass
                if futuredate <= todaydate:

                    existing.startdate = futuredate + relativedelta(months=3)
                    daycheckdate = futuredate + relativedelta(months=3)
                    daycheck = daycheckdate.day
                    if firstdateday > daycheck:
                        try:
                            for m in range(3):
                                daycheck += 1
                                if firstdateday >= daycheck:
                                    existing.startdate = daycheckdate.replace(day=daycheck)
                        except ValueError:
                            pass
                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), dragmode='pan')
    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, date
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, refresh):
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		dt = date.today()
38		today = datetime.combine(dt, datetime.min.time())
39
40		if modifiedtime > dbmodified or dbmodified < today or refresh == 1:
41		try:
42		if balance.amount:
43		db.session.query(Balance).delete()
44		balance = Balance(amount=balance.amount, date=datetime.today())
45		db.session.add(balance)
46		db.session.commit()
47		except:
48		balance = Balance(amount='0',
49		date=datetime.today())
50		db.session.add(balance)
51		db.session.commit()
52
53		# empty the tables to create fresh data from the schedule
54		db.session.query(Total).delete()
55		db.session.query(Running).delete()
56		db.session.query(Transactions).delete()
57		db.session.commit()
58
59		# calculate total events for the year amount
60		calc_schedule()
61
62		# calculate sum of running transactions
63		calc_transactions(balance)
64
65		Path(modpath).touch()
66
67
68		def calc_schedule():
69		months = 13
70		weeks = 53
71		years = 1
72		quarters = 4
73		biweeks = 27
74
75		try:
76		engine = db.create_engine(os.environ.get('DATABASE_URL')).connect()
77		except:
78		engine = db.create_engine('sqlite:///db.sqlite').connect()
79
80		# pull the schedule information
81		df = pd.read_sql('SELECT * FROM schedule;', engine)
82
83		# loop through the schedule and create transactions in a table out to the future number of years
84		todaydate = datetime.today().date()
85		for i in range(len(df.index)):
86		format = '%Y-%m-%d'
87		name = df['name'][i]
88		startdate = df['startdate'][i]
89		firstdate = df['firstdate'][i]
90		frequency = df['frequency'][i]
91		amount = df['amount'][i]
92		type = df['type'][i]
93		existing = Schedule.query.filter_by(name=name).first()
94		if not firstdate:
95		existing.firstdate = datetime.strptime(startdate, format).date()
96		firstdate = existing.firstdate.strftime(format)
97		db.session.commit()
98		if frequency == 'Monthly':
99		for k in range(months):
100		futuredate = datetime.strptime(startdate, format).date() + relativedelta(months=k)
101		futuredateday = futuredate.day
102		firstdateday = datetime.strptime(firstdate, format).date().day
103		if firstdateday > futuredateday:
104		try:
105		for m in range(3):
106		futuredateday += 1
107		if firstdateday >= futuredateday:
108		futuredate = futuredate.replace(day=futuredateday)
109		except ValueError:
110		pass
111	View Code Duplication	if futuredate <= todaydate:
		0 ignored issues – show Duplication introduced 2024-02-09 16:12 UTC by Report Bug Copy Issue Report This code seems to be duplicated in your project. Loading history...
112		existing.startdate = futuredate + relativedelta(months=1)
113		daycheckdate = futuredate + relativedelta(months=1)
114		daycheck = daycheckdate.day
115		if firstdateday > daycheck:
116		try:
117		for m in range(3):
118		daycheck += 1
119		if firstdateday >= daycheck:
120		existing.startdate = daycheckdate.replace(day=daycheck)
121		except ValueError:
122		pass
123		if type == 'Income':
124		rollbackdate = datetime.combine(futuredate, datetime.min.time())
125		total = Total(type=type, name=name, amount=amount,
126		date=pd.tseries.offsets.BDay(1).rollback(rollbackdate).date())
127		else:
128		total = Total(type=type, name=name, amount=amount, date=futuredate - pd.tseries.offsets.BDay(0))
129		db.session.add(total)
130		elif frequency == 'Weekly':
131		for k in range(weeks):
132		futuredate = datetime.strptime(startdate, format).date() + relativedelta(weeks=k)
133		if futuredate <= todaydate:
134		existing.startdate = futuredate + relativedelta(weeks=1)
135		total = Total(type=type, name=name, amount=amount, date=futuredate - pd.tseries.offsets.BDay(0))
136		db.session.add(total)
137		elif frequency == 'Yearly':
138		for k in range(years):
139		futuredate = datetime.strptime(startdate, format).date() + relativedelta(years=k)
140		if futuredate <= todaydate:
141		existing.startdate = futuredate + relativedelta(years=1)
142		total = Total(type=type, name=name, amount=amount, date=futuredate - pd.tseries.offsets.BDay(0))
143		db.session.add(total)
144		elif frequency == 'Quarterly':
145		for k in range(quarters):
146		futuredate = datetime.strptime(startdate, format).date() + relativedelta(months=3 * k)
147		futuredateday = futuredate.day
148		firstdateday = datetime.strptime(firstdate, format).date().day
149		if firstdateday > futuredateday:
150		try:
151		for m in range(3):
152		futuredateday += 1
153		if firstdateday >= futuredateday:
154		futuredate = futuredate.replace(day=futuredateday)
155		except ValueError:
156		pass
157	View Code Duplication	if futuredate <= todaydate:
		0 ignored issues – show Duplication introduced 2024-02-09 16:12 UTC by Report Bug Copy Issue Report This code seems to be duplicated in your project. Loading history...
158		existing.startdate = futuredate + relativedelta(months=3)
159		daycheckdate = futuredate + relativedelta(months=3)
160		daycheck = daycheckdate.day
161		if firstdateday > daycheck:
162		try:
163		for m in range(3):
164		daycheck += 1
165		if firstdateday >= daycheck:
166		existing.startdate = daycheckdate.replace(day=daycheck)
167		except ValueError:
168		pass
169		total = Total(type=type, name=name, amount=amount, date=futuredate - pd.tseries.offsets.BDay(0))
170		db.session.add(total)
171		elif frequency == 'BiWeekly':
172		for k in range(biweeks):
173		futuredate = datetime.strptime(startdate, format).date() + relativedelta(weeks=2 * k)
174		if futuredate <= todaydate:
175		existing.startdate = futuredate + relativedelta(weeks=2)
176		total = Total(type=type, name=name, amount=amount, date=futuredate - pd.tseries.offsets.BDay(0))
177		db.session.add(total)
178		elif frequency == 'Onetime':
179		futuredate = datetime.strptime(startdate, format).date()
180		if futuredate < todaydate:
181		db.session.delete(existing)
182		else:
183		total = Total(type=type, name=name, amount=amount, date=futuredate)
184		db.session.add(total)
185		db.session.commit()
186
187		# add the hold items
188		df = pd.read_sql('SELECT * FROM hold;', engine)
189		for i in range(len(df.index)):
190		name = df['name'][i]
191		amount = df['amount'][i]
192		type = df['type'][i]
193		total = Total(type=type, name=name, amount=amount, date=todaydate + relativedelta(days=1))
194		db.session.add(total)
195		db.session.commit()
196
197		# add the skip items
198		df = pd.read_sql('SELECT * FROM skip;', engine)
199		for i in range(len(df.index)):
200		format = '%Y-%m-%d'
201		name = df['name'][i]
202		amount = df['amount'][i]
203		type = df['type'][i]
204		date = df['date'][i]
205		if datetime.strptime(date, format).date() < todaydate:
206		skip = Skip.query.filter_by(name=name).first()
207		db.session.delete(skip)
208		else:
209		total = Total(type=type, name=name, amount=amount, date=datetime.strptime(date, format).date())
210		db.session.add(total)
211		db.session.commit()
212
213
214		def calc_transactions(balance):
215		try:
216		engine = db.create_engine(os.environ.get('DATABASE_URL')).connect()
217		except:
218		engine = db.create_engine('sqlite:///db.sqlite').connect()
219
220		# retrieve the total future transactions
221		df = pd.read_sql('SELECT * FROM total;', engine)
222		df = df.sort_values(by="date", key=lambda x: np.argsort(index_natsorted(df["date"])))
223
224		# collect the next 60 days of transactions for the transactions table
225		format = '%Y-%m-%d'
226		todaydate = datetime.today().date()
227		todaydateplus = todaydate + relativedelta(months=2)
228		for i in df.iterrows():
229		if todaydateplus > \
230		datetime.strptime(i[1].date, format).date() > todaydate and "(SKIP)" not in i[1].iloc[3]:
231		transactions = Transactions(name=i[1].iloc[3], type=i[1].type, amount=i[1].amount,
232		date=datetime.strptime(i[1].date, format).date())
233		db.session.add(transactions)
234		db.session.commit()
235
236		# for schedules marked as expenses, make the value negative for the sum
237		for i in df.iterrows():
238		id = i[1].id
239		amount = i[1].amount
240		type = i[1].type
241		if type == 'Expense':
242		amount = float(amount) * -1
243		df.at[id - 1, 'amount'] = amount
244		elif type == 'Income':
245		pass
246
247		# group total transactions by date and sum the amounts for each date
248		df = df.groupby("date")['amount'].sum().reset_index()
249
250		# loop through the total transactions by date and add the sums to the total balance amount
251		runbalance = float(balance.amount)
252		running = Running(amount=runbalance, date=datetime.today().date())
253		db.session.add(running)
254		for i in df.iterrows():
255		format = '%Y-%m-%d'
256		rundate = i[1].date
257		amount = i[1].amount
258		if datetime.strptime(rundate, format).date() > todaydate:
259		runbalance += amount
260		running = Running(amount=runbalance, date=datetime.strptime(rundate, format).date())
261		db.session.add(running)
262		db.session.commit()
263
264
265		def plot_cash():
266		try:
267		engine = db.create_engine(os.environ.get('DATABASE_URL')).connect()
268		except:
269		engine = db.create_engine('sqlite:///db.sqlite').connect()
270
271		# plot the running balances by date on a line plot
272		df = pd.read_sql('SELECT * FROM running;', engine)
273		df = df.sort_values(by='date', ascending=False)
274		format = '%Y-%m-%d'
275		minbalance = df['amount'].min()
276		minbalance = decimal.Decimal(str(minbalance)).quantize(decimal.Decimal('.01'))
277		if float(minbalance) >= 0:
278		minrange = 0
279		else:
280		minrange = float(minbalance) * 1.1
281		maxbalance = 0
282		todaydate = datetime.today().date()
283		todaydateplus = todaydate + relativedelta(months=2)
284		for i in df.iterrows():
285		if todaydateplus > datetime.strptime(i[1].date, format).date() > todaydate:
286		if i[1].amount > maxbalance:
287		maxbalance = i[1].amount
288		maxrange = maxbalance * 1.1
289		start_date = str(datetime.today().date())
290		end_date = str(datetime.today().date() + relativedelta(months=2))
291		layout = go.Layout(yaxis=dict(range=[minrange, maxrange]), xaxis=dict(range=[start_date, end_date]),
292		margin=dict(l=5, r=20, t=35, b=5), dragmode='pan')
293		fig = px.line(df, x="date", y="amount", template="plotly", title="Cash Flow", line_shape="spline")
294		fig.update_layout(layout)
295		fig.update_xaxes(title_text='Date')
296		fig.update_yaxes(title_text='Amount')
297		fig.update_layout(paper_bgcolor="PaleTurquoise")
298
299		graphJSON = json.dumps(fig, cls=plotly.utils.PlotlyJSONEncoder)
300
301		return minbalance, graphJSON

whahn1983 / pycashflow

Push — master ( f30af1...63b945 )

app.cashflow.update_cash() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like