def add_comment():
card_id = request.args.get('card_id')
user_id = request.args.get('user_id')
comment = request.args.get('text')
add_comment_query = "INSERT INTO comment (card_id, user_id, description, timestamp) VALUES (%s, '%s', '%s', CURRENT_TIMESTAMP)" % (
card_id, user_id, comment)
CURSOR.execute(add_comment_query)
DB.commit()
return json.dumps({'success': True}), 200, {
'ContentType': 'application/json'
}
def user_login():
user_id = request.args.get('user_id')
if user_id is None:
return json.dumps({'success': False}), 401, {
'ContentType': 'application/json'
}
add_user_query = "INSERT IGNORE INTO user (user_id) VALUES ('%s')" % (
user_id)
CURSOR.execute(add_user_query)
DB.commit()
return json.dumps({'success': True}), 200, {
'ContentType': 'application/json'
}
def login():
admin_id = request.form['admin_id']
password = request.form['password']
check_admin_validity = "SELECT admin_id, ward FROM admin WHERE admin_id = '%s' AND password = SHA('%s')" % (
admin_id, password)
CURSOR.execute(check_admin_validity)
if CURSOR.rowcount == 0:
flash('Invalid Login')
DB.commit()
return redirect(url_for('login_page'))
else:
session['admin_id'] = admin_id
session['ward'] = CURSOR.fetchone()['ward']
DB.commit()
return redirect(url_for('dashboard'))
def get_card_details(card_id):
get_comments_query = "SELECT description AS text, timestamp FROM comment WHERE card_id=%d" % (
card_id)
CURSOR.execute(get_comments_query)
comments = CURSOR.fetchall()
DB.commit()
if comments is None:
comments = []
get_additional_details_query = "SELECT description, category, status FROM card WHERE card_id=%s" % (
card_id)
CURSOR.execute(get_additional_details_query)
additional_details = CURSOR.fetchone()
DB.commit()
if additional_details is None:
return json.dumps([])
additional_details['comments'] = comments
return json.dumps(additional_details,
indent=4,
sort_keys=True,
default=str)
def send_ward_name():
ward_name = ''
filter = request.args.get('filter')
if filter == 'name':
ward_name = request.args.get('val')
elif filter == 'loc':
lat = float(request.args.get('lat'))
lng = float(request.args.get('lng'))
ward_name = get_location_ward((lat, lng))
ward_name = reformat_ward_name(ward)
ward_query = "SELECT ward_name AS w_name, ward_region AS w_regn FROM ward WHERE ward_name = '%s'" % (
ward_name)
CURSOR.execute(ward_query)
DB.commit()
ward_details = CURSOR.fetchone()
return json.dumps(ward_details)
def upload_card():
pass
# up_file = request.form['file']
title = request.form['title']
lat = request.form['lat']
lng = request.form['lng']
description = request.form['description']
# image =
user_id = request.form['user_id']
category = request.form['category']
ward = reformat_ward_name(get_location_ward(lat, lng))
add_card_query = "INSERT INTO card (title, lat, lng, description,category,ward, user_id)\
VALUES ('%s', %s, %s, '%s', '%s', '%s')" % (
title, lat, lng, description, category, ward, user_id)
CURSOR.execute(add_card_query)
DB.commit()
return json.dumps({'success': True}), 200, {
'ContentType': 'application/json'
}
def dashboard():
data = {}
total_issues_query = "SELECT COUNT(card_id) AS card_id_c FROM card"
CURSOR.execute(total_issues_query)
data['total_issues'] = CURSOR.fetchone()['card_id_c']
if data['total_issues'] is None:
data['total_issues'] = 0
DB.commit()
urgent_issues_query = "SELECT COUNT(card_id) AS card_id_c FROM card"
CURSOR.execute(urgent_issues_query)
data['urgent_issues'] = CURSOR.fetchone()['card_id_c']
if data['urgent_issues'] is None:
data['urgent_issues'] = 0
DB.commit()
total_comment_query = "SELECT COUNT(comment_id) AS comm_id_c FROM comment"
CURSOR.execute(total_comment_query)
data['activity'] = CURSOR.fetchone()['comm_id_c']
if data['activity'] is None:
data['activity'] = 0
data['activity'] = data['total_issues'] + data['activity']
DB.commit()
recent_issues_query = "SELECT card_id, category, timestamp, status FROM card ORDER BY timestamp DESC"
CURSOR.execute(recent_issues_query)
data['recent'] = CURSOR.fetchall()
DB.commit()
if data['recent'] is None:
data['recent'] = []
markers_query = "SELECT lat,lng FROM card"
CURSOR.execute(markers_query)
data['markers'] = CURSOR.fetchall()
if data['markers'] is None:
data['markers'] = []
DB.commit()
return render_template("dashboard.html", title='Dashboard', data=data)
def get_cards():
filter = request.args.get('filter')
get_cards_query = ''
result = ''
if filter is None:
get_cards_query = "SELECT card.card_id AS card_id, card.timestamp AS timestamp, title, lat, lng, image, category, ward.ward_region AS ward, \
COUNT(upvote.card_id) AS upvotes, COUNT(comment.comment_id) AS comment_count FROM card \
LEFT JOIN upvote ON card.card_id = upvote.card_id \
LEFT JOIN comment ON card.card_id = comment.card_id \
INNER JOIN ward ON card.ward = ward.ward_name\
GROUP BY card.card_id"
CURSOR.execute(get_cards_query)
result = CURSOR.fetchall()
DB.commit()
elif filter == 'nearby':
lat = request.args.get('lat')
lng = request.args.get('lng')
ward = get_location_ward(lat, lng)
ward = reformat_ward_name(ward)
get_cards_query = "SELECT card.card_id AS card_id, card.timestamp AS timestamp, title, lat, lng, image, category, ward.ward_region AS ward, \
COUNT(upvote.card_id) AS upvotes, COUNT(comment.comment_id) AS comment_count FROM card \
LEFT JOIN upvote ON card.card_id = upvote.card_id \
LEFT JOIN comment ON card.card_id = comment.card_id \
INNER JOIN ward ON card.ward = ward.ward_name\
GROUP BY card.card_id \
WHERE card.ward = '%s'" % (ward)
CURSOR.execute(get_cards_query)
result = CURSOR.fetchall()
DB.commit()
result.sort(
key=lambda x: sqrt((lat - x['lat'])**2 + (lng - x['lng'])**2))
if len(result) > 25:
result = result[:25]
result.sort(key=lambda x: x['upvotes'], reverse=True)
elif filter == 'upvotes':
get_cards_query = "SELECT card.card_id AS card_id, card.timestamp AS timestamp, title, lat, lng, image, category, ward.ward_region AS ward, \
COUNT(upvote.card_id) AS upvotes, COUNT(comment.comment_id) AS comment_count FROM card \
LEFT JOIN upvote ON card.card_id = upvote.card_id \
LEFT JOIN comment ON card.card_id = comment.card_id \
INNER JOIN ward ON card.ward = ward.ward_name\
WHERE card.ward='%s'\
GROUP BY card.card_id\
ORDER BY upvotes DESC " % (ward)
CURSOR.execute(get_cards_query)
result = CURSOR.fetchall()
DB.commit()
elif filter == 'time':
get_cards_query = "SELECT card.card_id AS card_id, card.timestamp AS timestamp, title, lat, lng, image, category, ward.ward_region AS ward, \
COUNT(upvote.card_id) AS upvotes, COUNT(comment.comment_id) AS comment_count FROM card \
LEFT JOIN upvote ON card.card_id = upvote.card_id \
LEFT JOIN comment ON card.card_id = comment.card_id \
INNER JOIN ward ON card.ward = ward.ward_name\
WHERE card.ward='%s'\
GROUP BY card.card_id\
ORDER BY timestamp DESC " % (ward)
CURSOR.execute(get_cards_query)
result = CURSOR.fetchall()
DB.commit()
if result is None:
result = []
return json.dumps(result, indent=4, sort_keys=True, default=str)
def get_ward_suggestions():
wards_query = 'SELECT ward_name AS w_name, ward_region AS w_regn FROM ward'
CURSOR.execute(wards_query)
wards = CURSOR.fetchall()
return json.dumps(wards, indent=4, sort_keys=True, default=str)