def addFoodlockDetails(self):
foodid = int(request.form['foodid'])
quant = int(request.form['quant'])
cur_quant = int(request.form['cquant'])
timelimit = int(request.form['ngodur'])
ngo_id = session['userid']
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
query = "insert into food_lock(fid, food_quantity, timelimit, ngo_id) values(%s, %s, %s, %s)"
cur.execute(query, (foodid, quant, timelimit, ngo_id))
cur.execute('commit')
dif = cur_quant - quant
q = "update food_unlock set quantity = %s where fid = %s"
cur.execute(q, (dif, foodid))
rows = cur.rowcount
cur.execute('commit')
cur.close()
conn.close()
if rows:
return redirect('/lock')
else:
return redirect('/ngo_timeline')
def getques(cls, subid):
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
# aptitudeqns=[]
query = "select * from question where subid=%s order by RANDOM() LIMIT 10"
cur.execute(query, (subid, ))
return cur.fetchall()
def addFoodDetails(self):
des = request.form['des']
quant = request.form['quant']
duration = request.form['duration']
i = session['userid']
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
query = "insert into foodinfo(id, description, foodquantity, duration) values(%s, %s, %s, %s) RETURNING foodid"
cur.execute(query, (i, des, quant, duration))
inline_return = cur.fetchone()
cur.execute('commit')
q = "insert into food_unlock(fid,quantity) values(%s, %s)"
cur.execute(q, (inline_return[0], quant))
cur.execute('commit')
q = "select phone,userid from userinfo where type = 'ngo'"
cur.execute(q)
phonelist = cur.fetchall()
for num in phonelist:
message = str(num['userid']) + str(inline_return[0])
SendSMS.send(str(num['phone']), message)
cur.close()
conn.close()
return render_template('donate.html')
def getProfileadmin(cls, admin_id):
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
query = "select * from admin where admin_id = %s"
cur.execute(query, (admin_id, ))
inline_return = cur.fetchall()
cur.execute('commit')
return inline_return
def getProfile(cls, sid):
print(sid[0])
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
query = "select * from student where sid = %s"
cur.execute(query, (sid[0], ))
inline_return = cur.fetchall()
cur.execute('commit')
return inline_return
def getlog(cls, subid, sid):
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
query = "select score from tests where subid=%s and sid=%s"
cur.execute(query, (
subid,
sid,
))
return cur.fetchall()
def getrank(cls, subid, sid):
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
query = "select a.average, rank() over(order by a.average desc) from (select t.sid,s.sname,avg(t.score) as average from student s join tests t on s.sid=t.sid where t.subid=%s group by t.sid,t.subid,s.sname order by average desc) as a where a.sid=%s"
cur.execute(query, (
subid,
sid,
))
return cur.fetchone()
def changepasswordadmin(cls, admin_id, passw):
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
query = "UPDATE admin SET pswd=%s WHERE admin_id=%s"
cur.execute(query, (passw, admin_id))
cur.execute('commit')
cur.close()
conn.close()
return cur.rowcount
def checkAdminDetails(cls, mail_id, passw):
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
query = "select admin_id from admin where mail_id=%s and pswd=%s"
cur.execute(query, (mail_id, passw))
if cur.rowcount:
inline_return = cur.fetchone()
return inline_return[0]
else:
return False
def addquestions(cls, question, op1, op2, op3, op4, answer, subid):
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
# aptitudeqns=[]
query = "insert into question(qname, op1, op2, op3 , op4, ans, subid) \
values(%s,%s,%s,%s,%s,%s,%s)"
cur.execute(query, (question, op1, op2, op3, op4, answer, subid))
cur.execute("Commit")
return True
def addDetails(cls, sid, sname, passw, mailid, mobile):
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
query = "insert into student (sid,sname,pass,mailid,mobile) values(%s,%s,%s,%s,%s) RETURNING SID"
cur.execute(query, (sid, sname, passw, mailid, mobile))
inline_return = cur.fetchone()
cur.execute('commit')
print(inline_return)
a = inline_return[0]
cur.close()
conn.close()
return a
def changepassword(cls, sid, passw):
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
query = "UPDATE student SET pass=%s WHERE sid=%s"
cur.execute(query, (passw, sid[0]))
query = "select * from student where sid=%s"
cur.execute(query, (sid[0], ))
res = cur.fetchone()
cur.execute('commit')
cur.close()
conn.close()
return res
def update_Status(self):
lock_id = int(request.form['lock_id'])
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
query = "update food_lock set status = 'picked' where lock_id = %s"
cur.execute(query, (lock_id, ))
cur.execute('commit')
cur.close()
conn.close()
if session['usertype'] == "ngo":
return redirect('/lock')
else:
return redirect('/notification')
def checkUserDetails(cls, mailid, passw):
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
query = "select pass from student where mailid=%s"
cur.execute(query, (mailid, ))
res = cur.fetchone()
if res:
if (sha256_crypt.verify(passw, res[0])):
query = "select * from student where mailid=%s"
cur.execute(query, (mailid, ))
ree = cur.fetchone()
return ree
else:
return False
def getFoodUnlockDetails(self):
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
query = "select fi.*,fu.quantity,ui.location,ui.name from foodinfo fi, food_unlock fu,\
userinfo ui where fu.quantity > 0 and fu.fid = fi.foodid and fi.id = ui.userid \
and now() < (fi.created_timestamp + ( duration * interval '1 hour'))"
cur.execute(query)
res = cur.fetchall()
for i in res:
i['created_timestamp'] = i['created_timestamp'].astimezone(tz)
i['created_timestamp'] = i['created_timestamp'].strftime(
"%A, %d. %B %Y %I:%M%p")
i['foodid'] = str(i['foodid'])
return render_template('ngo_timeline.html', food=res)
def getHistory(self):
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
uid = session['userid']
query = "select fi.*, ui.name from foodinfo fi , userinfo ui\
where fi.id = ui.userid and id= %s order by foodid desc"
cur.execute(query, (uid, ))
res = cur.fetchall()
for i in res:
i['created_timestamp'] = i['created_timestamp'].astimezone(tz)
i['created_timestamp'] = i['created_timestamp'].strftime(
"%A, %d. %B %Y %I:%M%p")
cur.close()
conn.close()
return render_template('history.html', food=res)
def getNotificationDetails(self):
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
uid = session['userid']
query = "select result.*, uii.name from (select fi.*, fl.* from foodinfo fi, food_lock fl, userinfo ui \
where ui.userid = %s and ui.userid = fi.id and fi.foodid = fl.fid and fl.status = 'not picked') as result, userinfo uii\
where uii.userid = result.ngo_id ORDER BY result.created_timestamp DESC"
cur.execute(query, (uid, ))
res = cur.fetchall()
for i in res:
i['created_timestamp'] = i['created_timestamp'].astimezone(tz)
i['created_timestamp'] = i['created_timestamp'].strftime(
"%A, %d. %B %Y %I:%M%p")
cur.close()
conn.close()
return render_template('hotelnotification.html', food=res)
def getFoodDetails(self):
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
uid = session['userid']
query = "select fi.*, ui.name from foodinfo fi, userinfo ui\
where fi.id = ui.userid and id = %s order by foodid desc"
cur.execute(query, (uid, ))
res = cur.fetchall()
query = "select result.*, uii.name from (select fi.foodid, fl.* from foodinfo \
fi, food_lock fl, userinfo ui where ui.userid = %s and ui.userid = fi.id \
and fi.foodid = fl.fid) as result, userinfo uii where uii.userid = result.ngo_id \
order by result.foodid DESC"
cur.execute(query, (uid, ))
lock = cur.fetchall()
f = []
j = 0
x = 1
count = cur.rowcount
for i in res:
i['created_timestamp'] = i['created_timestamp'].astimezone(tz)
i['created_timestamp'] = i['created_timestamp'].strftime(
"%A, %d. %B %Y %I:%M%p")
d = []
d.append(i['created_timestamp'])
d.append(i['description'])
d.append(i['foodquantity'])
d.append(i['duration'])
c = 0
while j < count and i['foodid'] == lock[j]['fid']:
d.append(lock[j])
j = j + 1
c = c + 1
d.append(c)
f.append(d)
cur.close()
conn.close()
return render_template('timeline.html', lock=f)
def getFoodlockDetails(self):
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
ngo_id = session['userid']
print(ngo_id)
query = "select fu.lock_id, fu.timelimit, ui.name, fi.*, fu.food_quantity, \
ful.quantity, ui.location \
from foodinfo fi,food_unlock ful, food_lock fu, userinfo ui \
where fu.fid = fi.foodid and ful.fid = fi.foodid and fi.id = ui.userid and \
fu.ngo_id = %s \
and fu.status = 'not picked'"
cur.execute(query, (ngo_id, ))
res = cur.fetchall()
for i in res:
i['created_timestamp'] = i['created_timestamp'].astimezone(tz)
i['created_timestamp'] = i['created_timestamp'].strftime(
"%A, %d. %B %Y %I:%M%p")
return render_template('handout.html', food=res)
def checkUserDetails(self):
phone = request.form['phone']
password = request.form['password']
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory = psycopg2.extras.DictCursor)
query="select userid, name, type, password from userinfo where phone = %s"
cur.execute(query, (phone ,))
res = cur.fetchone()
if res and (password == res['password']):
session['userid'] = res[0]
session['username'] = res[1]
session['usertype'] = res[2]
query = "select result.fid, result.foodquantity, result.created_timestamp,\
result.food_quantity, result.foodquantity, uii.name from \
(select fi.*, fl.* as hname from foodinfo fi, food_lock fl, userinfo ui \
where ui.userid = %s and ui.userid = fi.id and fi.foodid = fl.fid) as \
result, userinfo uii where uii.userid = result.ngo_id ORDER BY \
result.fid DESC"
uid = int(session['userid'])
print(uid)
cur.execute(query, (uid, ))
r = cur.fetchall()
for i in r:
i['created_timestamp'] = i['created_timestamp'].astimezone(tz)
i['created_timestamp'] = i['created_timestamp'].strftime("%Y-%d-%m")
print(i['created_timestamp'])
session['list'] = r
cur.close()
conn.close()
if res and res['type'] == "hotel":
return redirect('/donate')
elif res and res['type'] == "ngo":
return redirect('/ngo_timeline')
else:
return render_template('login.html', message = "Not successfull")
else:
cur.close()
conn.close()
return render_template('login.html', message = "Not successfull")
def addDetails(self):
try:
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory = psycopg2.extras.DictCursor)
name = request.form['username']
password = request.form['password']
phone = request.form['phone']
location = request.form['location']
user_type = request.form['type']
query = "insert into userinfo (name, password, phone, location, type)\
values(%s, %s, %s, %s, %s) RETURNING userid"
cur.execute(query, (name, password, phone, location, user_type))
inline_return = cur.fetchone()
cur.execute('commit')
a = inline_return[0]
cur.close()
conn.close()
return render_template('login.html', message = "successfull")
except Exception :
return render_template('signup.html', message = "User already Exists")
finally:
cur.close()
conn.close()
def addFoodunlockDetails(self):
foodid = int(request.form['foodid'])
quant = int(request.form['quant'])
cur_quant = int(request.form['cquant'])
lock_id = int(request.form['lock_id'])
conn = DatabaseConnection.databaseConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
query = "update food_unlock set quantity = %s where fid = %s"
dif = cur_quant + quant
cur.execute(query, (dif, foodid))
rows = cur.rowcount
cur.execute('commit')
query = "update food_lock set status='obsolete' where lock_id = %s"
cur.execute(query, (lock_id, ))
res = cur.rowcount
cur.execute('commit')
cur.close()
conn.close()
if res:
return redirect('/lock')
else:
return redirect('/ngo_timeline')