def settings():
if 'username' in session:
if request.method == "GET":
conn, curs = login.connector()
quer = "select preferCurrency, spendingLimit, emailAddress, annualSalary, gender from userSettings where username=\""+str(session['uname'])+"\""
curs.execute(quer)
dat = curs.fetchall()
if len(dat) > 0:
return render_template('settings.html', CoName=CoName, name=session['username'], data=dat)
else:
return render_template('settings.html', CoName=CoName, name=session['username'], data=None)
conn.close()
collect()
elif request.method == "POST":
key = []
val = []
for k, v in request.form.iteritems():
key.append(k.encode('ascii', 'ignore')), val.append(v.encode('ascii', 'ignore'))
key.append('username')
val.append(session['uname'].encode('ascii', 'ignore'))
modify(zip(key, val))
conn, curs = login.connector()
quer = "select preferCurrency, spendingLimit, emailAddress, annualSalary, gender from userSettings where username=\""+str(session['uname'])+"\""
curs.execute(quer)
dat = curs.fetchall()
flash("Your settings has been updated.")
return render_template('settings.html', CoName=CoName, name=session['username'], data=dat)
conn.close()
collect()
else:
flash("Please login to continue")
return redirect(url_for("main"))
def updateShareSettings(uname, shareTag, shareWith):
conn, curs = connector()
quer = "select ShareTagID from shares where shareTag='" + str(
shareTag) + "'"
curs.execute(quer)
shareTagID = curs.fetchall()[0][0]
curShares = []
quer = "select username from sharesWith where shareTagID='" + str(
shareTagID) + "'"
curs.execute(quer)
if curs.rowcount > 0:
for i in curs.fetchall():
curShares.append(i[0])
exshares = checkuser(shareWith)
new = []
for i in exshares:
if i in curShares:
curShares.remove(i)
elif i not in curShares:
new.append(i)
if len(curShares) == 0 and len(new) == 0:
pass
elif len(curShares) > 0 and len(new) > 0:
for j in curShares:
deleteWith(uname, shareTag, j)
for k in new:
sharingWith(uname, shareTag, k)
elif len(curShares) == 0 and len(new) > 0:
for k in new:
sharingWith(uname, shareTag, k)
elif len(curShares) > 0 and len(new) == 0:
for j in curShares:
deleteWith(uname, shareTag, j)
def insert(nwdat):
quer = "insert into userSettings"+str(tuple(nwdat.keys())).replace("'", "")+" values"+str(tuple(nwdat.values()))
conn, curs = connector()
curs.execute(quer)
conn.commit()
conn.close()
collect()
def updateShareSettings(uname, shareTag, shareWith): conn, curs = connector() quer = "select ShareTagID from shares where shareTag='"+str(shareTag)+"'" curs.execute(quer) shareTagID=curs.fetchall()[0][0] curShares = [] quer = "select username from sharesWith where shareTagID='"+str(shareTagID)+"'" curs.execute(quer) if curs.rowcount > 0: for i in curs.fetchall(): curShares.append(i[0]) exshares = checkuser(shareWith) new = [] for i in exshares: if i in curShares: curShares.remove(i) elif i not in curShares: new.append(i) if len(curShares) == 0 and len(new) == 0: pass elif len(curShares) > 0 and len(new) > 0: for j in curShares: deleteWith(uname, shareTag, j) for k in new: sharingWith(uname, shareTag, k) elif len(curShares) == 0 and len(new) > 0: for k in new: sharingWith(uname, shareTag, k) elif len(curShares) > 0 and len(new) == 0: for j in curShares: deleteWith(uname, shareTag, j)
def spendingTrend():
conn, curs = login.connector()
if 'username' in session:
if request.method == "GET":
quer = "select spentOn, spentAt, amount, share, sharetag, created, username from expense where created > (created between DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW()) and username='******'uname'])+"'"
curs.execute(quer)
dat = curs.fetchall()
if len(dat) > 0:
quer = "select sum(amount) from expense where created > (created between DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW()) and username='******'uname'])+"'"
curs.execute(quer)
total = curs.fetchall()[0][0]
dat = dat
else:
dat = None
shares = getGroup(session['uname'])
return render_template('spendingTrend.html', CoName=CoName, name=session['username'], data=dat, method=request.method, shares=shares, total=total)
elif request.method == "POST":
shares = getGroup(session['uname'])
dat = spendingTrends(session['uname'], request.form)
collect()
return render_template('spendingTrend.html', CoName=CoName, name=session['username'], data=dat[0], total=dat[1], method=request.method, fromDate=request.form['fromDate'], toDate=request.form['toDate'], shares=shares)
conn.close()
else:
flash("Please login to browse")
return redirect(url_for('main'))
def spendingTrends(username, form): conn, curs = connector() if form['shareTag'] == "None": quer = "SELECT spentOn, spentAt, amount, share, sharetag, created, username FROM expense WHERE DATE_FORMAT(created, '%Y-%m-%d') BETWEEN '"+str(form['fromDate'])+"' AND '"+str(form['toDate'])+"' and username='******'" curs.execute(quer) dat = curs.fetchall() if len(dat) > 0: quer = "SELECT sum(amount) FROM expense WHERE DATE_FORMAT(created, '%Y-%m-%d') BETWEEN '"+str(form['fromDate'])+"' AND '"+str(form['toDate'])+"' and username='******'" curs.execute(quer) total = curs.fetchall()[0][0] return dat, total else: return None, None else: quer = "SELECT spentOn, spentAt, amount, share, sharetag, created, username FROM expense WHERE DATE_FORMAT(created, '%Y-%m-%d') BETWEEN '"+str(form['fromDate'])+"' AND '"+str(form['toDate'])+"' and shareTag='"+str(form['shareTag'])+"'" curs.execute(quer) dat = curs.fetchall() if len(dat) > 0: quer = "SELECT sum(amount) FROM expense WHERE DATE_FORMAT(created, '%Y-%m-%d') BETWEEN '"+str(form['fromDate'])+"' AND '"+str(form['toDate'])+"' and shareTag='"+str(form['shareTag'])+"'" curs.execute(quer) total = curs.fetchall()[0][0] return dat, total else: return None, None conn.close()
def spendingTrends(username, form):
conn, curs = connector()
if form['shareTag'] == "None":
quer = "SELECT spentOn, spentAt, amount, share, sharetag, created, username FROM expense WHERE DATE_FORMAT(created, '%Y-%m-%d') BETWEEN '" + str(
form['fromDate']) + "' AND '" + str(
form['toDate']) + "' and username='******'"
curs.execute(quer)
dat = curs.fetchall()
if len(dat) > 0:
quer = "SELECT sum(amount) FROM expense WHERE DATE_FORMAT(created, '%Y-%m-%d') BETWEEN '" + str(
form['fromDate']) + "' AND '" + str(
form['toDate']) + "' and username='******'"
curs.execute(quer)
total = curs.fetchall()[0][0]
return dat, total
else:
return None, None
else:
quer = "SELECT spentOn, spentAt, amount, share, sharetag, created, username FROM expense WHERE DATE_FORMAT(created, '%Y-%m-%d') BETWEEN '" + str(
form['fromDate']) + "' AND '" + str(
form['toDate']) + "' and shareTag='" + str(
form['shareTag']) + "'"
curs.execute(quer)
dat = curs.fetchall()
if len(dat) > 0:
quer = "SELECT sum(amount) FROM expense WHERE DATE_FORMAT(created, '%Y-%m-%d') BETWEEN '" + str(
form['fromDate']) + "' AND '" + str(
form['toDate']) + "' and shareTag='" + str(
form['shareTag']) + "'"
curs.execute(quer)
total = curs.fetchall()[0][0]
return dat, total
else:
return None, None
conn.close()
def insert(nwdat):
quer = "insert into userSettings" + str(tuple(nwdat.keys())).replace(
"'", "") + " values" + str(tuple(nwdat.values()))
conn, curs = connector()
curs.execute(quer)
conn.commit()
conn.close()
collect()
def delete():
conn, curs = connector()
quer = "show tables"
curs.execute(quer)
tables = curs.fetchall()
table = []
for i in tables:
if i[0] != 'users':
quer = "delete from " + str(i[0]) + " where username='" + str(user)
def update(nwdat):
conn, curs = connector()
username = nwdat['username']
nwdat.pop('username')
for i in nwdat:
quer = "update userSettings set "+str(i)+"=\""+str(nwdat[i])+"\" where username=\""+username+"\""
curs.execute(quer)
conn.commit()
conn.close()
collect()
def checkuser(usernames):
confuser = []
conn, curs = connector()
for i in usernames.split(","):
user = i.encode('ascii', 'ignore').rstrip().lstrip()
quer = "select username from users where username='******'"
curs.execute(quer)
if curs.rowcount > 0:
confuser.append(user)
return confuser
def checkuser(usernames):
confuser = []
conn, curs = connector()
for i in usernames.split(","):
user = i.encode('ascii', 'ignore').rstrip().lstrip()
quer = "select username from users where username='******'"
curs.execute(quer)
if curs.rowcount > 0:
confuser.append(user)
return confuser
def checker(username): conn, curs = connector() quer = "select username, preferCurrency, spendingLimit, emailAddress, annualSalary, gender from userSettings where username='******'" curs.execute(quer) dat = curs.fetchall() conn.close() collect() if len(dat) > 0: return True, dat[0][0], dat[0][1], dat[0][2], dat[0][3], dat[0][4], dat[0][5] else: return False, None, None, None, None, None, None
def update(nwdat):
conn, curs = connector()
username = nwdat['username']
nwdat.pop('username')
for i in nwdat:
quer = "update userSettings set " + str(i) + "=\"" + str(
nwdat[i]) + "\" where username=\"" + username + "\""
curs.execute(quer)
conn.commit()
conn.close()
collect()
def deleteWith(uname, shareTag, usernames): conn, curs = connector() quer = "select shareTagID from shares where username='******' and shareTag='"+shareTag+"'" curs.execute(quer) shareId = curs.fetchall()[0][0] username = checkuser(usernames) for i in username: if i != str(uname): quer = "delete from sharesWith where username='******' and shareTagID='"+str(shareId)+"'" curs.execute(quer) conn.commit()
def sharingWith(uname, shareTag, usernames):
conn, curs = connector()
quer = "select shareTagID from shares where username='******' and shareTag='"+shareTag+"'"
curs.execute(quer)
shareId = curs.fetchall()[0][0]
username = checkuser(usernames)
for i in username:
if i != str(uname):
quer = "insert into sharesWith(username, shareTagID) values('"+i.encode('ascii', 'ignore').rstrip().lstrip()+"', '"+str(shareId)+"')"
curs.execute(quer)
conn.commit()
def deleteWith(uname, shareTag, usernames):
conn, curs = connector()
quer = "select shareTagID from shares where username='******' and shareTag='" + shareTag + "'"
curs.execute(quer)
shareId = curs.fetchall()[0][0]
username = checkuser(usernames)
for i in username:
if i != str(uname):
quer = "delete from sharesWith where username='******' and shareTagID='" + str(shareId) + "'"
curs.execute(quer)
conn.commit()
def checker(username):
conn, curs = connector()
quer = "select username, preferCurrency, spendingLimit, emailAddress, annualSalary, gender from userSettings where username='******'"
curs.execute(quer)
dat = curs.fetchall()
conn.close()
collect()
if len(dat) > 0:
return True, dat[0][0], dat[0][1], dat[0][2], dat[0][3], dat[0][
4], dat[0][5]
else:
return False, None, None, None, None, None, None
def sharingWith(uname, shareTag, usernames):
conn, curs = connector()
quer = "select shareTagID from shares where username='******' and shareTag='" + shareTag + "'"
curs.execute(quer)
shareId = curs.fetchall()[0][0]
username = checkuser(usernames)
for i in username:
if i != str(uname):
quer = "insert into sharesWith(username, shareTagID) values('" + i.encode(
'ascii',
'ignore').rstrip().lstrip() + "', '" + str(shareId) + "')"
curs.execute(quer)
conn.commit()
def trackExpense():
if 'username' in session:
conn, curs = login.connector()
quer = "select distinct(spentAt) from expense where username='******'uname'])+"'"
curs.execute(quer)
places = curs.fetchall()
place = []
if len(places) > 0:
for i in places:
place.append(i[0].rstrip())
if request.method == 'GET':
shares = getGroup(session['uname'])
collect()
return render_template('trackExpense.html', CoName=CoName, name=session['username'], place=place, shares=shares)
elif request.method == "POST":
col = []
val = []
mutDict = {}
for i in request.form:
mutDict[i] = request.form[i]
if mutDict['share'] == 'personal':
mutDict['shareTag']
if mutDict['share'] == 'personal':
mutDict.pop('shareTag')
for j in mutDict:
col.append(j), val.append(mutDict[j].encode('ascii', 'ignore'))
col.append("username")
val.append(session['uname'].encode('ascii', 'ignore'))
quer = "insert into expense"+re.sub('\'', '', str(tuple(col))) + " values"+str(tuple(val))
conn, curs = login.connector()
curs.execute(quer)
conn.commit()
shares = getGroup(session['uname'])
collect()
flash("Data has been saved for further tracking.")
return render_template('trackExpense.html', CoName=CoName, name=session['username'], shares=shares, place=place)
else:
flash("login to continue")
return redirect(url_for("main"))
def createTag(username, shareTag, meshare):
conn, curs = connector()
quer = "select shareTag from shares where shareTag='" + str(shareTag) + "'"
curs.execute(quer)
ifexit = curs.fetchall()
if len(ifexit) == 0:
quer = "insert into shares(username, shareTag) values('" + username + "','" + shareTag + "')"
curs.execute(quer)
conn.commit()
sharingWith(username, shareTag, meshare)
return True
else:
return False
conn.close()
def createTag(username, shareTag, meshare):
conn, curs = connector()
quer = "select shareTag from shares where shareTag='"+str(shareTag)+"'"
curs.execute(quer)
ifexit = curs.fetchall()
if len(ifexit) == 0:
quer = "insert into shares(username, shareTag) values('"+username+"','"+shareTag+"')"
curs.execute(quer)
conn.commit()
sharingWith(username, shareTag, meshare)
return True
else:
return False
conn.close()
def deleteTag(username, shareTag): conn, curs = connector() quer = "select shareTagID from shares where username='******' and shareTag='"+shareTag+"'" curs.execute(quer) dat = curs.fetchall() if len(dat) > 0: quer = "delete from sharesWith where shareTagID='"+str(dat[0][0])+"'" curs.execute(quer) conn.commit() quer = "delete from shares where username='******' and shareTag='"+shareTag+"'" curs.execute(quer) conn.commit() return True else: return False conn.close()
def deleteTag(username, shareTag):
conn, curs = connector()
quer = "select shareTagID from shares where username='******' and shareTag='" + shareTag + "'"
curs.execute(quer)
dat = curs.fetchall()
if len(dat) > 0:
quer = "delete from sharesWith where shareTagID='" + str(
dat[0][0]) + "'"
curs.execute(quer)
conn.commit()
quer = "delete from shares where username='******' and shareTag='" + shareTag + "'"
curs.execute(quer)
conn.commit()
return True
else:
return False
conn.close()
def sharesForSettings(username):
myshares = {}
conn, curs = connector()
quer = "select shareTag, shareTagID from shares where username='******'"
curs.execute(quer)
if curs.rowcount > 0:
dat = curs.fetchall()
for i in dat:
myshares[i[0]] = {}
quer = "select username from sharesWith where shareTagID='"+str(i[1])+"'"
curs.execute(quer)
if curs.rowcount > 0:
sharings = []
for j in curs.fetchall():
sharings.append(j[0])
myshares[i[0]]['sharingWith'] = sharings
conn.close()
return myshares
def getGroup(username): shares = [] conn, curs = connector() quer = "select shareTag from shares where username='******'" curs.execute(quer) if curs.rowcount > 0: for i in curs.fetchall(): shares.append(i[0]) quer = "select shares.shareTag from shares join sharesWith on shares.shareTagID=sharesWith.shareTagID where sharesWith.username='******'" curs.execute(quer) if curs.rowcount > 0: for i in curs.fetchall(): shares.append(i[0]) conn.close() if len(shares) > 0: return shares else: return None
def getGroup(username):
shares = []
conn, curs = connector()
quer = "select shareTag from shares where username='******'"
curs.execute(quer)
if curs.rowcount > 0:
for i in curs.fetchall():
shares.append(i[0])
quer = "select shares.shareTag from shares join sharesWith on shares.shareTagID=sharesWith.shareTagID where sharesWith.username='******'"
curs.execute(quer)
if curs.rowcount > 0:
for i in curs.fetchall():
shares.append(i[0])
conn.close()
if len(shares) > 0:
return shares
else:
return None
def sharesForSettings(username):
myshares = {}
conn, curs = connector()
quer = "select shareTag, shareTagID from shares where username='******'"
curs.execute(quer)
if curs.rowcount > 0:
dat = curs.fetchall()
for i in dat:
myshares[i[0]] = {}
quer = "select username from sharesWith where shareTagID='" + str(
i[1]) + "'"
curs.execute(quer)
if curs.rowcount > 0:
sharings = []
for j in curs.fetchall():
sharings.append(j[0])
myshares[i[0]]['sharingWith'] = sharings
conn.close()
return myshares
def register():
conn, curs = login.connector()
if request.method == "POST":
col = []
val = []
for i in request.form:
col.append(i), val.append(request.form[i].encode('ascii', 'ignore'))
quer = "insert into users"+re.sub('\'', '', str(tuple(col))) + " values"+str(tuple(val))
try:
curs.execute(quer)
conn.commit()
conn.close()
collect()
session['username'] = request.form['fname']
session['uname'] = request.form['username']
return render_template('register.html', CoName=CoName, name=session['username'])
except:
flash("This username is not available.")
return render_template('main.html', CoName=CoName)
else:
flash("You cannot access this page")
return redirect(url_for('main'))
def main():
if request.method == "GET":
if 'username' in session:
conn, curs = login.connector()
#total amount
quer = "select sum(amount) from expense where created > (created between DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW()) and username=\""+str(session['uname'])+"\""
curs.execute(quer)
total = curs.fetchall()[0][0]
if total != None:
total = total
else:
total = 0
#Personal amount
pquer = "select sum(amount) from expense where (share='personal' and created > (created between DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW())) and username='******'uname'])+"'"
curs.execute(pquer)
ptotal = curs.fetchall()[0][0]
#shared amount
squer = "select sum(amount) from expense where (share='shared' and created > (created between DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW())) and username='******'uname'])+"'"
curs.execute(squer)
stotal = curs.fetchall()[0][0]
#shared amount
ttquer = "select spendingLimit from userSettings where username='******'uname'])+"'"
curs.execute(ttquer)
tttotal = curs.fetchall()
if len(tttotal) > 0:
if tttotal[0][0] != None:
tttotal = tttotal[0][0] - total
else:
tttotal = None
conn.close()
collect()
return render_template('main.html', CoName=CoName, name=session['username'], total=total, ptotal=ptotal, stotal=stotal, tttotal=tttotal)
else:
return render_template('main.html', CoName=CoName)
elif request.method == "POST":
amI = login.login(request.form['username'], request.form['password'])
if amI[0] == True:
session['username'] = amI[1]
session['uname'] = amI[2]
conn, curs = login.connector()
quer = "select sum(amount) from expense where created > (created between DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW()) and username=\""+str(session['uname'])+"\""
curs.execute(quer)
total = curs.fetchall()[0][0]
if total != None:
total = total
else:
total = 0
pquer = "select sum(amount) from expense where (share='personal' and created > (created between DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW())) and username='******'uname'])+"'"
curs.execute(pquer)
ptotal = curs.fetchall()[0][0]
squer = "select sum(amount) from expense where (share='shared' and created > (created between DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW())) and username='******'uname'])+"'"
curs.execute(squer)
stotal = curs.fetchall()[0][0]
ttquer = "select spendingLimit from userSettings where username='******'uname'])+"'"
curs.execute(ttquer)
tttotal = curs.fetchall()
if len(tttotal) > 0:
if tttotal[0][0] != None:
tttotal = tttotal[0][0] - total
else:
tttotal = None
conn.close()
collect()
return render_template('main.html', CoName=CoName, name=session['username'], total=total, ptotal=ptotal, stotal=stotal, tttotal=tttotal)
else:
flash("incorrect username or password.")
return render_template('main.html', CoName=CoName)
