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 submit_link():
if request.method == 'POST':
# database stuff
c, conn = connection()
title = request.form['title']
newlink = request.form['newlink']
c.execute("INSERT INTO links (title,link) VALUES (%s,%s)",
(esc(title),esc(newlink)) )
conn.commit()
flash("Link Submitted!")
c.close()
conn.close()
gc.collect()
return redirect(url_for("main_page"))
else:
return render_template("submit.html")
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 login():
error = ''
try:
cur, conn = connection()
if request.method == 'POST':
if request.form['submit'] == 'login':
attempted_username = str(request.form['eid'])
attempted_password = str(request.form['pass'])
query = "SELECT * FROM users WHERE email = '" + attempted_username + "';"
cur.execute(query)
password = cur.fetchone()[4]
if sha256_crypt.verify(attempted_password, password):
session['logged_in'] = True
session['username'] = attempted_username
return redirect(url_for('profile'))
else:
error = 'Invalid credentials.'
elif request.form['submit'] == 'signup':
fname = str(request.form['fname'])
sname = str(request.form['sname'])
email = str(request.form['email'])
phone = str(request.form['phone'])
password = str(request.form['pass'])
repass = str(request.form['repass'])
if fname and sname and email and phone and password and repass and len(phone) == 10 and password == repass:
password = sha256_crypt.encrypt(password)
cur.execute("INSERT INTO users (fname, surname, email, phone, password) VALUES (%s, %s, %s, %s, %s)", (esc(fname), esc(sname), esc(email), esc(phone), esc(password)))
conn.commit()
session['logged_in'] = True
session['username'] = attempted_username
return redirect(url_for('profile'))
else:
error = 'Invalid.'
conn.close()
cur.close()
gc.collect()
return render_template("login.html", error=error)
except Exception as e:
return str(e)
def simple_where(data, field):
if data:
where.append(field + " like '%%" + esc(data) + "%%'")
def build_query(get):
where = [] # holds where clauses, will be joined by and
orderby = [] # holds order by clauses needed for hit relevance only.
placeholders = {} # sql interpolated values
def simple_where(data, field):
if data:
where.append(field + " like '%%" + esc(data) + "%%'")
# title search
if get.get('title'):
title = esc(get['title'])
orderby.append("title='%s' DESC" % title)
orderby.append("title like ' %%{}%% ' DESC".format(title))
orderby.append("title like '%%{}%%' DESC".format(title))
# now search by word in title, alt, and cast
for i, word in enumerate(title.split()):
where.append("""(title like '%%{0}%%'
or alttitle like '%%{0}%%'
or cast like '%%{0}%%')""".format(esc(word)))
# cast search
simple_where(get.get('cast'), 'cast')
# keywords in entry
for word in get.get('keywords', '').split():
simple_where(word, 'entry')
# genre query. some manual queries "?genre=action&genre2=adventure" to account for
# i'm fetching all article ids and issuing an id in [...] query cause that's how php did it.
genres = get.getlist('genre') + [get.get('genre2')]
genres = filter(None, [Genre.name_to_id(genre) for genre in genres])
if genres:
article_ids = [
str(row[0]) for row in Genre.objects.filter(
id__in=genres).values_list('articles__id')
]
where.append('id in (' + ', '.join(article_ids) + ')')
# labels
labels = filter(None, get.getlist('label'))
if labels:
article_ids = [
str(row[0]) for row in Tags.objects.filter(
id__in=labels).values_list('articles__id')
]
where.append('id in (' + ','.join(article_ids) + ')')
# year tags
year = get.get('year_from', '')
if year and year.isdigit():
where.append('year >=%(year_from)s')
placeholders['year_from'] = year
year = get.get('year_to', '')
if year and year.isdigit():
where.append('year <=%(year_to)s')
placeholders['year_to'] = year
ratings = dict(overall=['overall'],
artistic=['stars'],
moral=['moral', 'spiritual'],
age=['age'],
mpaa=['mpaa'],
usccb=['usccb'])
for field, tagnames in ratings.items():
if get.get(field):
where.append(
Ratings.sql(tagnames, get.get(field),
get.get('%s_modifier' % field, '=')))
if get.get('fletter'):
letter = get['fletter']
where.append("""(%(fletter)s= LEFT(TRIM(leading "The " from
TRIM(leading "A " from
TRIM(leading "An " from
TRIM(leading '"' from title)
)
)
),1))""")
placeholders['fletter'] = letter
# Manually selected ordering
orderings = {
'title':
"""TRIM(leading "The " from TRIM(leading "A " from TRIM(leading "An " from TRIM(leading '"' from title)))) ASC""",
'date': ' dt_modified DESC',
'overall': Ratings.locate(['overall'], Ratings.overall, join=","),
'artistic': Ratings.locate(['artistic'], Ratings.artistic, join=","),
'year': "year DESC",
}
if get.get('order', '') in orderings:
orderby = [orderings[get.get('order')]]
if orderby:
orderby = ' ORDER BY ' + ', '.join(orderby)
else:
orderby = ''
where.append('exclude_from_search=0')
where = ' AND '.join(filter(None, where))
sql = 'select * from blog where ' + where + orderby
return sql, placeholders
def CreateLinks (self, cursor, fromObj, toObjs):
for (objname, objid) in toObjs:
query = "INSERT INTO Attributes (Name, ParentId, ChildId) VALUES ('" + esc(str(objname)) + "', " + str(fromObj) + ", " + str(objid) + ")"
cursor.execute (query)
def simple_where(data, field):
if data:
where.append(field + " like '%%" + esc(data) + "%%'")
def build_query(get):
where = [] # holds where clauses, will be joined by and
orderby = [] # holds order by clauses needed for hit relevance only.
placeholders = {} # sql interpolated values
def simple_where(data, field):
if data:
where.append(field + " like '%%" + esc(data) + "%%'")
# title search
if get.get('title'):
title = esc(get['title'])
orderby.append("title='%s' DESC" % title)
orderby.append("title like ' %%{}%% ' DESC".format(title))
orderby.append("title like '%%{}%%' DESC".format(title))
# now search by word in title, alt, and cast
for i, word in enumerate(title.split()):
where.append("""(title like '%%{0}%%'
or alttitle like '%%{0}%%'
or cast like '%%{0}%%')""".format(esc(word)))
# cast search
simple_where(get.get('cast'), 'cast')
# keywords in entry
for word in get.get('keywords', '').split():
simple_where(word, 'entry')
# genre query. some manual queries "?genre=action&genre2=adventure" to account for
# i'm fetching all article ids and issuing an id in [...] query cause that's how php did it.
genres = get.getlist('genre') + [get.get('genre2')]
genres = filter(None, [Genre.name_to_id(genre) for genre in genres])
if genres:
article_ids = [str(row[0]) for row in Genre.objects.filter(id__in=genres).values_list('articles__id')]
where.append('id in (' + ', '.join(article_ids) + ')')
# labels
labels = filter(None, get.getlist('label'))
if labels:
article_ids = [str(row[0]) for row in Tags.objects.filter(id__in=labels).values_list('articles__id')]
where.append('id in (' + ','.join(article_ids) + ')')
# year tags
year = get.get('year_from', '')
if year and year.isdigit():
where.append('year >=%(year_from)s')
placeholders['year_from'] = year
year = get.get('year_to', '')
if year and year.isdigit():
where.append('year <=%(year_to)s')
placeholders['year_to'] = year
ratings = dict(overall=['overall'],
artistic=['stars'],
moral=['moral', 'spiritual'],
age=['age'],
mpaa=['mpaa'],
usccb=['usccb']
)
for field, tagnames in ratings.items():
if get.get(field):
where.append(Ratings.sql(tagnames, get.get(field), get.get('%s_modifier' % field, '=')))
if get.get('fletter'):
letter = get['fletter']
where.append("""(%(fletter)s= LEFT(TRIM(leading "The " from
TRIM(leading "A " from
TRIM(leading "An " from
TRIM(leading '"' from title)
)
)
),1))""")
placeholders['fletter'] = letter
# Manually selected ordering
orderings = {
'title': """TRIM(leading "The " from TRIM(leading "A " from TRIM(leading "An " from TRIM(leading '"' from title)))) ASC""",
'date': ' dt_modified DESC',
'overall': Ratings.locate(['overall'], Ratings.overall, join=","),
'artistic': Ratings.locate(['artistic'], Ratings.artistic, join=","),
'year': "year DESC",
}
if get.get('order','') in orderings:
orderby = [orderings[get.get('order')]]
if orderby:
orderby = ' ORDER BY ' + ', '.join(orderby)
else:
orderby = ''
where.append('exclude_from_search=0')
where = ' AND '.join(filter(None, where))
sql = 'select * from blog where ' + where + orderby
return sql, placeholders
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 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()