def comment(environ):
"""
Comment page
"""
#get dictionary from request
d = request_dict(environ)
lname = d.get('lname', [''])[0]
fname = d.get('fname', [''])[0]
pname = d.get('pname', [''])[0]
area = d.get('area', [''])[0]
city = d.get('city', [''])[0]
phone = d.get('phone', [''])[0]
email = d.get('email', [''])[0]
comment = d.get('comment', [''])[0]
# Always escape user input to avoid script injection
lname = escape(lname)
fname = escape(fname)
pname = escape(pname)
area = escape(area)
city = escape(city)
phone = escape(phone)
email = escape(email)
comment = escape(comment)
#get area
sql_response_area = db.execute('SELECT rowid, * FROM area')
areas_dict = {}
for row in sql_response_area:
areas_dict[str(row[0])] = (row[1]).encode('utf-8')
#create options for areas
areas_option = '<option value="">--------</option>'
for k, v in areas_dict.items():
areas_option += '<option value="' + k + '">' + areas_dict[
k] + '</option>'
comment_added = ""
#insert data to table people_info
sql_data = '''
INSERT INTO people_info
VALUES (?,?,?,?,?,?,?,?);
'''
if d:
insert_value = (lname.decode('utf-8'), fname.decode('utf-8'),
pname.decode('utf-8') or None, area or None, city
or None, phone.decode('utf-8') or None,
email.decode('utf-8') or None, comment.decode('utf-8'))
db.execute(sql_data, insert_value)
comment_added = 'Your comment added to database'
html = read_tempate('templates/comment.html')
html_response = html % {
'areas_option': areas_option,
'comment_added': comment_added,
}
type_response = "response_ok"
response = (type_response, html_response)
return response
def area(environ):
"""
Area page
"""
sql_response = db.execute('SELECT rowid, * FROM area')
response_table = ''
for row in sql_response:
response_table += '<tr id=' + str(row[0]) + '>'
response_table += '<td>'
response_table += '<input type="checkbox" name="rm-areas" value='
response_table += str(row[0])
response_table += '></td>'
for item in row:
response_table += '<td>'
try:
response_table += item.encode('utf-8')
except AttributeError:
response_table += str(item)
response_table += '</td>'
response_table += '</tr>'
html = read_tempate('templates/area.html')
response_html = html % {
'response_table': response_table,
}
type_response = "response_ok"
response = (type_response, response_html)
return response
def refbook(environ):
"""
Reference book page
"""
response_html = read_tempate('templates/refbook.html')
type_response = "response_ok"
response = (type_response, response_html)
return response
def index(environ):
"""
Index page
"""
html = read_tempate('templates/index.html')
type_response = "response_ok"
response = (type_response, html)
return response
def city(environ):
"""
City page
"""
#get area
sql_response_area = db.execute('SELECT rowid, * FROM area')
areas_dict = {}
for row in sql_response_area:
areas_dict[str(row[0])] = (row[1]).encode('utf-8')
#create options for areas
areas_option = ''
for k, v in areas_dict.items():
areas_option += '<option value="' + k + '">' + areas_dict[
k] + '</option>'
#get city
sql_response_city = db.execute('SELECT rowid, * FROM city')
#create city table
response_table = ''
for row in sql_response_city:
response_table += '<tr id=' + str(row[0]) + '>'
response_table += '<td>'
response_table += '<input type="checkbox" name="rm-cities" value='
response_table += str(row[0])
response_table += '></td>'
for idx, item in enumerate(row):
response_table += '<td>'
if idx == 2 and item:
sql_get_area = '''
SELECT name FROM area
WHERE rowid = ({area_id});
'''.format(area_id=str(item))
area = db.execute(sql_get_area)
if area:
response_table += (area[0][0]).encode('utf-8')
else:
response_table += str(item)
else:
try:
response_table += item.encode('utf-8')
except AttributeError:
response_table += str(item)
response_table += '</td>'
response_table += '</tr>'
html = read_tempate('templates/city.html')
response_html = html % {
'response_table': response_table,
'areas_option': areas_option
}
type_response = "response_ok"
response = (type_response, response_html)
return response
def stat_area(environ):
"""
Stat area page
"""
#get area_id from environ['myapp.url_args']
area_id = environ['myapp.url_args']['area_id']
sql_select_areas = '''
SELECT city, COUNT(*)
FROM people_info
WHERE area = ?
GROUP BY city;
'''
sql_response = db.execute(sql_select_areas, area_id)
response_table = ''
for row in sql_response:
response_table += '<tr>'
if row[1]:
for idx, item in enumerate(row):
response_table += '<td>'
if idx == 0 and item:
#set area name to table
sql_get_city = '''
SELECT name FROM city
WHERE rowid = ({city_id});
'''.format(city_id=str(item))
city = db.execute(sql_get_city)
if city:
response_table += (city[0][0]).encode('utf-8')
else:
response_table += str(item)
else:
try:
response_table += item.encode('utf-8')
except AttributeError:
response_table += str(item)
response_table += '</td>'
response_table += '</tr>'
html = read_tempate('templates/stat-area.html')
response_html = html % {
'response_table': response_table,
}
type_response = "response_ok"
response = (type_response, response_html)
return response
def stat(environ):
"""
Stat page
"""
sql_select_areas = '''
SELECT area, COUNT(*) AS lenght
FROM people_info
GROUP BY area
HAVING COUNT(*) > 5;
'''
sql_response = db.execute(sql_select_areas)
response_table = ''
for row in sql_response:
response_table += '<tr>'
if row[1]:
for idx, item in enumerate(row):
response_table += '<td>'
if idx == 0 and item:
#set area name to table
sql_get_area = '''
SELECT name FROM area
WHERE rowid = ({area_id});
'''.format(area_id=str(item))
area = db.execute(sql_get_area)
response_table += '<a href="/stat/' + str(item) + '/">'
if area:
response_table += (area[0][0]).encode('utf-8')
else:
response_table += str(item)
response_table += '</a>'
else:
try:
response_table += item.encode('utf-8')
except AttributeError:
response_table += str(item)
response_table += '</td>'
response_table += '</tr>'
html = read_tempate('templates/stat.html')
response_html = html % {
'response_table': response_table,
}
type_response = "response_ok"
response = (type_response, response_html)
return response
def view(environ):
"""
View page
"""
#get dictionary from request
d = request_dict(environ)
comments = d.get('rm-comments', [])
comments = [escape(comment) for comment in comments]
if comments:
print(comments)
for comment_id in comments:
sql_delete_row = '''
DELETE FROM people_info
WHERE rowid = {comment_id};'''.format(comment_id=comment_id)
db.execute(sql_delete_row)
#get comments from db
sql_select_comments_innerjoin = '''
SELECT comment.rowid, last_name, first_name, patronymic_name, area.name,
city.name, phone, email, additional
FROM people_info comment
INNER JOIN area ON comment.area=area.rowid
INNER JOIN city ON comment.city=city.rowid
'''
sql_select_comments = '''
SELECT rowid, *
FROM people_info comment
'''
sql_response = db.execute(sql_select_comments)
response_table = ''
for row in sql_response:
response_table += '<tr id=' + str(row[0]) + '>'
response_table += '<td>'
response_table += '<input type="checkbox" name="rm-comments" value='
response_table += str(row[0])
response_table += '></td>'
for idx, item in enumerate(row):
response_table += '<td>'
if idx == 4 and item:
sql_get_area = '''
SELECT name FROM area
WHERE rowid = ({area_id});
'''.format(area_id=str(item))
area = db.execute(sql_get_area)
if area:
response_table += (area[0][0]).encode('utf-8')
else:
response_table += str(item)
elif idx == 5 and item:
sql_get_city = '''
SELECT name FROM city
WHERE rowid = ({city_id});
'''.format(city_id=str(item))
city = db.execute(sql_get_city)
if city:
response_table += (city[0][0]).encode('utf-8')
else:
response_table += str(item)
elif item is None:
response_table += ''
else:
try:
response_table += item.encode('utf-8')
except AttributeError:
response_table += str(item)
response_table += '</td>'
response_table += '</tr>'
html = read_tempate('templates/view.html')
if comments:
comments_rm = '<p>Comment(s) {comments} deleted</p>'.format(
comments=comments)
else:
comments_rm = ''
response_html = html % {
'response_table': response_table,
'comments': comments_rm
}
type_response = "response_ok"
response = (type_response, response_html)
return response