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()