def update_scheme(name, description, eligibility, category):
#return "Update Function"
c,conn = connection()
ct = c.execute("select * from SCHEMES where name = (%s)",(esc(name),))
results = c.fetchall()
for row in results:
#n = row[1]
d = row[2]
e = row[3]
ca = row[4]
if description != "" and description is not None:
d = description
if eligibility != "" and eligibility is not None:
e = eligibility
if category != "" and category is not None:
ca = category
rt = c.execute ("""
UPDATE SCHEMES
SET description=%s, eligibility=%s, category=%s
WHERE name=%s
""", (d,e,ca,name))
conn.commit()
c.close()
def get_my_complaints():
c,conn = connection()
uid = session['user'];
rows = c.execute("select c.c_id,c.c_dept,c.c_subject,c.c_text,pc.time_of_lodging,c.c_status, (select count(*) from PUBLIC_COMPLAINTS pc2 where pc2.c_id_uf = pc.c_id_uf) as reasks from COMPLAINTS c, PUBLIC_COMPLAINTS pc where c.c_id=pc.c_id_uf and pc.p_uid_f = '" + uid + "';")
res=[]
if rows > 0:
res = c.fetchall()
return jsonify(res)
def validation():
if session.get('validator'):
c, conn = connection()
ct = c.execute("select * from appn")
results = c.fetchall()
return render_template('adminApproval.html', results=results)
else:
return render_template('secondHome.html')
def get_scheme():
c, conn = connection()
c.execute("select name from SCHEMES")
results = c.fetchall()
arr = []
for rows in results:
arr.append(rows[0])
return jsonify(data=arr)
def checkValid():
if request.method == 'POST':
c, conn = connection()
c.execute("select * from appn")
results = c.fetchall()
for rows in results:
valid = request.form[str(rows[0])]
print(valid)
if valid == "Valid":
print("True")
c2, conn2 = connection()
ct = c2.execute("select * from USER where id=" + str(rows[1]))
userResults = c2.fetchall()
for row in userResults:
pres_scheme = row[10]
pres_scheme = pres_scheme + "," + rows[11]
if (ct > 0):
c2.execute(
"update user set schemes_applied=%s WHERE id=%s",
(pres_scheme, rows[1]))
conn2.commit()
c2.close()
else:
c3, conn3 = connection()
c3.execute(
"insert into USER (id,id_type,name,gender,year_of_birth,father_or_spouse_name,address,pincode,income,schemes_applied) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);",
(esc(rows[1]), esc(str(rows[2])), esc(rows[3]),
esc(str(rows[4])), esc(str(rows[5])), esc(
rows[6]), esc(rows[7]), esc(str(
rows[8])), esc(str(rows[9])), esc(rows[11])))
conn3.commit()
c3.close()
c1, conn1 = connection()
c1.execute("DELETE FROM APPN WHERE appn_id=" + str(rows[0]))
conn1.commit()
c1.close()
else:
print("False")
c1, conn1 = connection()
c1.execute("DELETE FROM APPN WHERE appn_id=" + str(rows[0]))
conn1.commit()
c1.close()
return redirect(url_for('validation'))
def get_desc():
c, conn = connection()
c.execute("select desc from schemes where name='" +
request.args.get('name') + "'")
results = c.fetchall()
x = None
for rows in results:
x = rows[0]
return jsonify(data=x)
def insert_appn(uid,id_type,name,gender,yob,care_of,address,pincode,income,phone,schemes_applied):
c,conn=connection()
scheme_id=""
c.execute("select id from schemes where name='"+schemes_applied+"'")
results=c.fetchall()
for rows in results:
scheme_id=rows[0]
ins = c.execute("insert into APPN (id,id_type,name,gender,year_of_birth,father_or_spouse_name,address,pincode,income,phone,schemes_applied) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);",(esc(uid),esc(str(id_type)),esc(name),esc(str(gender)),esc(str(yob)),esc(care_of),esc(address),esc(pincode),esc(income),esc(phone),esc(str(scheme_id))))
conn.commit()
c.close()
def get_complaints():
c,conn=connection()
rows = c.execute("select * from COMPLAINTS ORDER BY c_time_of_lodging DESC;");
if rows > 0:
results=c.fetchall()
arr=[]
for i in range(len(results)):
arr.append(results[i])
print("Hello")
print("Out of loop")
return jsonify(arr)
def auth_user(username, password):
c, conn = connection()
ct = c.execute("select p_pwd from USER where p_uid='" + username + "'")
if ct > 0:
res = c.fetchall()
for row in res:
pwd = row[0]
if (pwd == password):
return True
else:
return False
return False
def login():
name = request.form['uname']
pssword = request.form['pword']
con, cur = connection()
squery = "select username,password from registration where username='******' and password='******';"
cur.execute(squery)
unamedata = cur.fetchone()
if unamedata is None:
return '''<script>alert("invalid user");window.location="/"</script>'''
else:
x = [i for i in unamedata]
session['sname'] = x[0]
return redirect(url_for('loginhome'))
def get_desc():
c, conn = connection()
c.execute("select description,eligibility from SCHEMES where name='" +
request.args.get('name') + "'")
results = c.fetchall()
x = None
y = None
for rows in results:
x = rows[0]
y = rows[1]
global schemeName
schemeName = request.args.get('name')
return jsonify(desc=x, elig=y)
def search_db(keyword):
c, conn = connection()
rows = c.execute("select * from COMPLAINTS")
lis = []
if rows > 0:
res = c.fetchall()
for i in range(len(res)):
text = res[i][3]
words = text.split(" ")
if keyword in words:
lis.append(res[i][0])
return lis
def authuser(username, password):
c,conn = connection()
ct=c.execute("select password from EMPS where username = '******'")
if ct>0:
results = c.fetchall()
for row in results:
z = row[0]
'''if password == passwd:
return True
else:
return False'''
if(z == password):
return True
else:
return False
return False
def reask():
c,conn=connection()
uid= session['user']
cid = request.args['c']
print("PID = ")
print(uid)
print("CID =")
print(cid)
rowcount1 = c.execute("select * from PUBLIC_COMPLAINTS where p_uid_f='" + uid + "' and c_id_uf='"+cid+"';");
if rowcount1>0:
return redirect(url_for('hello_world'))
rows = c.execute("insert into PUBLIC_COMPLAINTS values('"+str(uid)+"','"+str(cid)+"','"+datetime.now().strftime('%Y-%m-%d %H:%M:%S')+"');");
row1 = c.execute("update COMPLAINTS set c_time_of_lodging = '"+datetime.now().strftime('%Y-%m-%d %H:%M:%S')+"' where c_id = "+cid+";")
conn.commit()
c.close()
return redirect(url_for('hello_world'))
def register_user(username, first_name, last_name, dob, phone, email, addr1,
addr2, pincode, city, state, password):
#auth_user('abc','def')
c, conn = connection()
#ins = c.execute("insert into SCHEMES values (%s,%s,%s,%s)",(esc(name),esc(description),esc(eligibility),esc(category)))
rowcount1 = c.execute("select * from USER where p_uid='" + username + "'")
if rowcount1 > 0:
return False
rowcount2 = c.execute("select * from USER where p_ph='" + phone + "'")
if rowcount2 > 0:
return False
ins = c.execute("insert into USER values (?,?,?,?,?,?,?,?,?,?,?,?)",
(username, first_name, last_name, dob, phone, email, addr1,
addr2, pincode, city, state, password))
conn.commit()
c.close()
def registor():
fname = request.form['fname']
email = request.form['email']
runame = request.form['runame']
rpword = request.form['rpword']
image = request.files['filename']
image.save("C:/Users/febin/Documents/GitHub/SystemUser/static/pics/" +
image.filename)
path = "/static/pics/" + image.filename
con, cur = connection()
inquery = "insert into registration (fullname,email,username,password,imgpath) values ('" + fname + "','" + email + "','" + runame + "','" + rpword + "','" + path + "');"
myquery = cur.execute(inquery)
con.commit()
return redirect(url_for('loginhome'))
def search_db(keyword):
c, conn = connection()
rows = c.execute("select * from COMPLAINTS")
if rows > 0:
lis = []
res = c.fetchall()
for i in range(len(res)):
#text = res[i][3]
words = res[i][3].split(" ")
kw = keyword.split(" ")
for k in kw:
if k in words:
lis.append([
res[i][0], res[i][1], res[i][2], res[i][3], res[i][4],
res[i][5], res[i][6]
])
return lis
def check(uid,name):
z=None
c,conn=connection()
row_count = c.execute("select schemes_applied from USER where id='"+str(uid)+"'")
if row_count>0:
results=c.fetchall()
arr=[]
temp={}
for row in results:
#print(row[0].split(','))
schemes=row[0].split(',')
for s in schemes:
print(s)
c.execute("select * from SCHEMES where id="+s)
results=c.fetchall()
for row in results:
temp[row[1]]=row[4]
print(temp)
c.execute("select category from SCHEMES where name='"+name+"'")
results=c.fetchall()
for row in results:
z=row[0]
'''
for key_a,vals_a in temp.items():
for key_b,vals_b in temp.items():
if temp[key_a]==temp[key_b]:
print("conflict")
return True
'''
for key,value in temp.items():
print(key,temp[key])
if temp[key]==z:
print("hello")
return False,key
else:
print("no")
return True,''
else:
return True,''
def lodge_complaint():
c,conn=connection()
uid= session['user']
cid = ''.join(random.choice(string.ascii_uppercase + string.digits) for _ in range(10))
cdept = request.args['c_dept']
'''csubject = request.args['c_subject']
cbody = request.args['c_body']
print("PID = ")
print(uid)
print("CiD = ")
print(cid)
print("c_dept = ")
print(cdept)
print("Csubject = ")
print(csubject)
print("Cbody = ")
print(cbody)
rowcount1 = c.execute("insert into COMPLAINTS values('"+cid+"','"+str(cdept)+"', '"+str(csubject)+"', '"+str(cbody)+"', '"+datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+"', FALSE);");
rows = c.execute("insert into PUBLIC_COMPLAINTS values('"+str(uid)+"','"+str(cid)+"','"+datetime.now().strftime('%Y-%m-%d %H:%M:%S')+"');");
conn.commit()
c.close()'''
return redirect(url_for('hello_world'))
def add_scheme(name, description, eligibility, category):
c,conn = connection()
#ins = c.execute("insert into SCHEMES values (%s,%s,%s,%s)",(esc(name),esc(description),esc(eligibility),esc(category)))
ins = c.execute("insert into SCHEMES (name, description, eligibility, category) values (%s,%s,%s,%s);",(esc(name),esc(description),esc(eligibility),esc(category)))
conn.commit()
c.close()
def delete_scheme(id1):
c,conn = connection()
ct = c.execute("DELETE FROM SCHEMES WHERE id="+id1)
conn.commit()
c.close()
