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)