def update_ticket(ticket_number):
""" Update a ticket """
if not is_logged_in():
return redirect(url_for('login'))
ticket_id = int(ticket_number)
tickets = get_tickets(where_clause="a.id = %s", params=(ticket_id, ))
if tickets:
ticket = tickets[0]
else:
message = "Failed to retreive ticket. Please contact IT."
flash(message)
return redirect(url_for('index'))
ticket_updates = get_ticket_updates(where_clause="a.ticket_id = %s",
params=(ticket_id, ))
userid = session["id"]
user_details = \
fetch_from_table(where_clause="id = %s", params=(userid, ))[0]
if request.method == "POST":
form, reassign_set = update_reassign_selector()
else:
form = UpdateTicketForm()
reassign_set = False
session["ticket_id"] = ticket_id
return render_template('update_ticket.html',
title='Update Ticket',
user_details=user_details,
ticket=ticket,
form=form,
reassign_set=reassign_set,
ticket_updates=ticket_updates)
def get_ticket_updates(where_clause="%s",
params=(1, ),
order="a.dt_updated DESC",
log_query=False):
"""
Get details of ticket updates from the db.
Output:
A list of dictionaries if any ticket updates are in the db.
Each dictionary contains details of a particular ticket.
"""
required_columns = """
a.id,
b.description as 'type',
LPAD(a.ticket_id, 7, '0') as 'ticket_number',
a.post,
CONCAT(c.firstname, " ", c.lastname) as 'rep',
a.dt_updated
"""
table = """
ticket_update a
INNER JOIN `update_type` b ON b.id = a.type_id
INNER JOIN `agent` c ON c.id = a.posted_by
"""
updates = fetch_from_table(required_columns=required_columns,
where_clause=where_clause,
params=params,
table=table,
log_query=log_query,
order=order)
return updates
def get_tickets(
where_clause="status_id = %s",
params=(1, ),
order="a.dt_created",
log_query=False):
"""
Get ticket details from the db.
Output:
A list of dictionaries if any tickets are in the db.
Each dictionary contains details of a particular ticket.
"""
required_columns = """
LPAD(a.id, 7, '0') as 'ticket_number',
CONCAT(a.firstname, " ", a.lastname) as 'name',
a.phone_number,
b.`description` as 'gender',
c.ward_number,
a.location,
d.description as 'topic',
e.description as 'priority',
f.name as 'partner',
g.name as 'programme',
a.details,
CONCAT(h.firstname, " ", h.lastname) as 'rep',
CONCAT(i.firstname, " ", i.lastname) as 'agent',
a.assigned_to,
j.description as 'status',
a.dt_created,
k.name as 'district',
l.name as 'province',
a.location
"""
table = """
ticket a
INNER JOIN `gender` b ON b.id = a.gender_id
INNER JOIN `ward` c ON c.id = a.ward_id
INNER JOIN `topic` d ON d.id = a.topic_id
INNER JOIN `priority` e ON e.id = a.priority_id
INNER JOIN `partner` f ON f.id = a.partner_id
INNER JOIN `programme` g ON g.id = a.programme_id
INNER JOIN `agent` h ON h.id = a.created_by
INNER JOIN `agent` i ON i.id = a.assigned_to
INNER JOIN `ticket_status` j ON j.id = a.status_id
INNER JOIN `district` k ON k.id = c.district_id
INNER JOIN `province` l ON l.id = k.province_id
"""
tickets = fetch_from_table(
required_columns=required_columns,
where_clause=where_clause,
params=params,
table=table,
order=order,
log_query=log_query)
return tickets
def get_tickets(where_clause="status_id = %s",
params=(1, ),
order="a.dt_created",
log_query=False):
"""
Get ticket details from the db.
Output:
A list of dictionaries if any tickets are in the db.
Each dictionary contains details of a particular ticket.
"""
required_columns = """
LPAD(a.id, 7, '0') as 'ticket_number',
CONCAT(a.firstname, " ", a.lastname) as 'name',
a.phone_number,
b.`description` as 'gender',
c.ward_number,
a.location,
d.description as 'topic',
e.description as 'priority',
f.name as 'partner',
g.name as 'programme',
a.details,
CONCAT(h.firstname, " ", h.lastname) as 'rep',
CONCAT(i.firstname, " ", i.lastname) as 'agent',
a.assigned_to,
j.description as 'status',
a.dt_created,
k.name as 'district',
l.name as 'province',
a.location
"""
table = """
ticket a
INNER JOIN `gender` b ON b.id = a.gender_id
INNER JOIN `ward` c ON c.id = a.ward_id
INNER JOIN `topic` d ON d.id = a.topic_id
INNER JOIN `priority` e ON e.id = a.priority_id
INNER JOIN `partner` f ON f.id = a.partner_id
INNER JOIN `programme` g ON g.id = a.programme_id
INNER JOIN `agent` h ON h.id = a.created_by
INNER JOIN `agent` i ON i.id = a.assigned_to
INNER JOIN `ticket_status` j ON j.id = a.status_id
INNER JOIN `district` k ON k.id = c.district_id
INNER JOIN `province` l ON l.id = k.province_id
"""
tickets = fetch_from_table(required_columns=required_columns,
where_clause=where_clause,
params=params,
table=table,
order=order,
log_query=log_query)
return tickets
def get_villages(required_columns="*", where_clause="%s", params=(1, )):
"""
Get details about villages from database
required_columns: A string containing the columns required from the
query or * for all
where_clause: A string containg the statements after the where clause
params: A tuple containing all the required parameters
"""
villages = fetch_from_table(required_columns=required_columns,
where_clause=where_clause,
params=params,
table="village")
return villages
def get_villages(required_columns="*", where_clause="%s", params=(1, )):
"""
Get details about villages from database
required_columns: A string containing the columns required from the
query or * for all
where_clause: A string containg the statements after the where clause
params: A tuple containing all the required parameters
"""
villages = fetch_from_table(required_columns=required_columns,
where_clause=where_clause,
params=params,
table="village")
return villages
def get_user_details(required_columns="*",
where_clause="%s",
params=(1, ),
log_query=False):
"""
Get details about users from database
required_columns: A string containing the columns required from the
query or * for all
where_clause: A string containg the statements after the where clause
params: A tuple containing all the required parameters
"""
user_details = fetch_from_table(required_columns=required_columns,
where_clause=where_clause,
params=params,
table="agent",
log_query=log_query)
return user_details
def get_user_details(
required_columns="*",
where_clause="%s",
params=(1, ),
log_query=False):
"""
Get details about users from database
required_columns: A string containing the columns required from the
query or * for all
where_clause: A string containg the statements after the where clause
params: A tuple containing all the required parameters
"""
user_details = fetch_from_table(required_columns=required_columns,
where_clause=where_clause,
params=params,
table="agent",
log_query=log_query)
return user_details
def validate_login():
""" Check if agent provided correct password. """
try:
username = request.form['username']
password = request.form['password']
except NameError as error:
message = str(error)
else:
if check_pw(username, password):
user_data = fetch_from_table(where_clause="username = %s",
params=(username, ),
table='agent')
session["id"] = user_data[0]["id"]
return redirect(url_for('index'))
else:
message = "Access Denied!"
flash(message, "error")
return redirect(url_for('login'))
def check_pw(username, password):
"""
Validate supplied username & password with the one in the database
Output: True if successful
"""
required_columns = "passwd"
where_clause = "username = %s"
params = (username, )
user_details = fetch_from_table(required_columns=required_columns,
where_clause=where_clause,
params=params,
table="agent")
if user_details:
valid_pw = bytes(user_details[0]["passwd"], 'utf8')
return valid_pw == hashpw(password.encode("utf-8"), valid_pw)
else:
return False
def check_pw(username, password):
"""
Validate supplied username & password with the one in the database
Output: True if successful
"""
required_columns = "passwd"
where_clause = "username = %s"
params = (username, )
user_details = fetch_from_table(
required_columns=required_columns,
where_clause=where_clause,
params=params,
table="agent")
if user_details:
valid_pw = bytes(user_details[0]["passwd"], 'utf8')
return valid_pw == hashpw(password.encode("utf-8"), valid_pw)
else:
return False
def validate_login():
""" Check if agent provided correct password. """
try:
username = request.form['username']
password = request.form['password']
except NameError as error:
message = str(error)
else:
if check_pw(username, password):
user_data = fetch_from_table(
where_clause="username = %s",
params=(username, ),
table='agent')
session["id"] = user_data[0]["id"]
return redirect(url_for('index'))
else:
message = "Access Denied!"
flash(message, "error")
return redirect(url_for('login'))
def get_ticket_updates(
where_clause="%s",
params=(1, ),
order="a.dt_updated DESC",
log_query=False):
"""
Get details of ticket updates from the db.
Output:
A list of dictionaries if any ticket updates are in the db.
Each dictionary contains details of a particular ticket.
"""
required_columns = """
a.id,
b.description as 'type',
LPAD(a.ticket_id, 7, '0') as 'ticket_number',
a.post,
CONCAT(c.firstname, " ", c.lastname) as 'rep',
a.dt_updated
"""
table = """
ticket_update a
INNER JOIN `update_type` b ON b.id = a.type_id
INNER JOIN `agent` c ON c.id = a.posted_by
"""
updates = fetch_from_table(
required_columns=required_columns,
where_clause=where_clause,
params=params,
table=table,
log_query=log_query,
order=order)
return updates
def update_ticket(ticket_number):
""" Update a ticket """
if not is_logged_in():
return redirect(url_for('login'))
ticket_id = int(ticket_number)
tickets = get_tickets(where_clause="a.id = %s", params=(ticket_id, ))
if tickets:
ticket = tickets[0]
else:
message = "Failed to retreive ticket. Please contact IT."
flash(message)
return redirect(url_for('index'))
ticket_updates = get_ticket_updates(
where_clause="a.ticket_id = %s",
params=(ticket_id, ))
userid = session["id"]
user_details = \
fetch_from_table(where_clause="id = %s", params=(userid, ))[0]
if request.method == "POST":
form, reassign_set = update_reassign_selector()
else:
form = UpdateTicketForm()
reassign_set = False
session["ticket_id"] = ticket_id
return render_template('update_ticket.html',
title='Update Ticket',
user_details=user_details,
ticket=ticket,
form=form,
reassign_set=reassign_set,
ticket_updates=ticket_updates)
def save_ticket_update():
"""Save update to a ticket.
Will need to refactor this function. It's too big
There are 3 possible update types.
1. Normal update to a ticket.
2. Close ticket.
3. Reassign ticket to a different agent.
"""
if not is_logged_in():
return redirect(url_for('login'))
try:
update_type = request.form["update_type"]
if update_type == "3":
#Re-assign Ticket
new_agent_id = int(request.form["reassign_ticket"])
else:
details = request.form["update_details"]
except (AttributeError, NameError, HTTPException, KeyError) as error:
message = str(error)
#message = "Error. Please contact IT."
flash(message)
return redirect(url_for('index'))
userid = session["id"]
message = "Update successfully posted."
data = get_user_details(where_clause="id = %s", params=(userid, ))
user_details = data[0]
if update_type == "1":
pass #Nothig to do here yet!
elif update_type == "2":
#Close the ticket
#update_table(table="", set_string="", data=(), where_string="")
set_string = "status_id = %s" #Corresponds to ticket closed
data = (2, session["ticket_id"])
where_string = "id = %s"
update_table(table="ticket",
set_string=set_string,
where_string=where_string,
data=data)
message = "Ticket successfully closed."
elif update_type == "3":
#Re-assign the ticket
#get_tickets(where_clause="status_id = %s", params=(1, ))
where_clause = "a.id = %s"
params = (session["ticket_id"], )
ticket = tickets = get_tickets(where_clause=where_clause,
params=params)[0]
agents = fetch_from_table(required_columns="firstname, lastname",
where_clause="id = %s",
params=(new_agent_id, ),
table="agent")
if agents:
new_agent = agents[0]
else:
message = "Failed to re-assign ticket. Please contact IT."
flash(message)
return redirect(url_for('index'))
details = "Ticket reassigned from {} to {} {}".format(
ticket["rep"], new_agent["firstname"], new_agent["lastname"])
#Now changed the agent in the ticket table
set_string = "assigned_to = %s"
data = (new_agent_id, session["ticket_id"])
where_string = "id = %s"
update_table(table="ticket",
set_string=set_string,
where_string=where_string,
data=data)
else:
#Invalid option chosen. Somehow?
message = "Invalid option chosen. Somehow?"
flash(message)
return redirect(url_for('index'))
add_update(update_type, session["ticket_id"], details, session["id"])
flash(message)
if update_type == "2":
return redirect(url_for('index'))
else:
url = 'update_ticket/{}'.format(session["ticket_id"])
return redirect(url)
def save_ticket_update():
"""Save update to a ticket.
Will need to refactor this function. It's too big
There are 3 possible update types.
1. Normal update to a ticket.
2. Close ticket.
3. Reassign ticket to a different agent.
"""
if not is_logged_in():
return redirect(url_for('login'))
try:
update_type = request.form["update_type"]
if update_type == "3" :
#Re-assign Ticket
new_agent_id = int(request.form["reassign_ticket"])
else:
details = request.form["update_details"]
except (AttributeError, NameError, HTTPException, KeyError) as error:
message = str(error)
#message = "Error. Please contact IT."
flash(message)
return redirect(url_for('index'))
userid = session["id"]
message = "Update successfully posted."
data = get_user_details(where_clause="id = %s", params=(userid, ))
user_details = data[0]
if update_type == "1":
pass #Nothig to do here yet!
elif update_type == "2":
#Close the ticket
#update_table(table="", set_string="", data=(), where_string="")
set_string = "status_id = %s" #Corresponds to ticket closed
data = (2, session["ticket_id"])
where_string = "id = %s"
update_table(
table="ticket",
set_string=set_string,
where_string=where_string,
data=data
)
message = "Ticket successfully closed."
elif update_type == "3":
#Re-assign the ticket
#get_tickets(where_clause="status_id = %s", params=(1, ))
where_clause="a.id = %s"
params = (session["ticket_id"], )
ticket = tickets = get_tickets(
where_clause=where_clause,
params=params)[0]
agents = fetch_from_table(
required_columns="firstname, lastname",
where_clause="id = %s",
params=(new_agent_id, ),
table="agent")
if agents:
new_agent = agents[0]
else:
message = "Failed to re-assign ticket. Please contact IT."
flash(message)
return redirect(url_for('index'))
details = "Ticket reassigned from {} to {} {}".format(
ticket["rep"],
new_agent["firstname"],
new_agent["lastname"])
#Now changed the agent in the ticket table
set_string = "assigned_to = %s"
data = (new_agent_id, session["ticket_id"])
where_string = "id = %s"
update_table(
table="ticket",
set_string=set_string,
where_string=where_string,
data=data
)
else:
#Invalid option chosen. Somehow?
message = "Invalid option chosen. Somehow?"
flash(message)
return redirect(url_for('index'))
add_update(
update_type,
session["ticket_id"],
details,
session["id"])
flash(message)
if update_type == "2":
return redirect(url_for('index'))
else:
url = 'update_ticket/{}'.format(session["ticket_id"])
return redirect(url)
def prep_select(
table="",
constraint="",
required_columns="*",
where_clause="",
params=(1, )):
"""
Prepare a list of id, agent tuples for use in creating selectfields
in forms.
input
=====
table: A string with the table(s) required.
constraint: (to be removed. Superceded by where_clause) A string containing
the value used in where statment.
where_clause: A string containg the statements after the where clause
eg " id = %s or staffid != %s"
params: Atuple containing the data refered to in the query
output: a list of tuples in as below:
======
(id, "firstname lastname") for the agent table.
"""
if table == "agent":
required_columns = "id, firstname, lastname"
user_details = fetch_from_table(
table=table,
required_columns=required_columns,
where_clause=where_clause,
params=params)
data = [(0, "---Please Select Agent---")]
for agent in user_details:
fullname = "{0} {1}".format(agent["firstname"], agent["lastname"])
data.append((agent["id"], fullname))
elif table == "gender":
required_columns = "id, description"
genders = get_gender(required_columns=required_columns)
data = [(0, "---Please Select Gender---")]
for gender in genders:
data.append((gender["id"], gender["description"]))
elif table == "province":
required_columns = "id, name"
provinces = get_provinces(required_columns=required_columns)
data = [(0, "---Please Select Province---")]
for province in provinces:
data.append((province["id"], province["name"]))
elif table == "district":
required_columns = "id, name"
where_clause = "province_id = %s"
params = (constraint,)
districts = get_districts(required_columns=required_columns,
where_clause=where_clause,
params=params)
data = [(0, "---Please Select District---")]
for district in districts:
data.append((district["id"], district["name"]))
elif table == "ward":
required_columns = "id, ward_number"
where_clause = "district_id = %s"
params = (constraint,)
wards = get_wards(required_columns=required_columns,
where_clause=where_clause,
params=params)
data = [(0, "---Please Select Ward---")]
for ward in wards:
data.append((ward["id"], ward["ward_number"]))
elif table == "village":
required_columns = "id, name"
where_clause = "ward_id = %s"
params = (constraint,)
villages = get_villages(required_columns=required_columns,
where_clause=where_clause,
params=params)
data = [(0, "---Please Select village---")]
for village in villages:
data.append((village["id"], village["name"]))
elif table == "partner":
required_columns = "id, name"
partners = get_partners(required_columns=required_columns)
data = [(0, "---Please Select Partner---")]
for partner in partners:
data.append((partner["id"], partner["name"]))
elif table == "programme":
required_columns = "id, name"
programmes = get_programmes(required_columns=required_columns)
data = [(0, "---Please Select Programme---")]
for programme in programmes:
data.append((programme["id"], programme["name"]))
elif table == "topic":
required_columns = "id, description"
topics = get_topics(required_columns=required_columns)
data = list()
data = [(0, "---Please Select topic---")]
for topic in topics:
data.append((topic["id"], topic["description"]))
elif table == "priority":
required_columns = "id, description"
prioritys = get_prioritys(required_columns=required_columns)
data = [(0, "---Please Select Priority---")]
for priority in prioritys:
data.append((priority["id"], priority["description"]))
elif table == "update_type":
update_types = fetch_from_table(
required_columns="id, description",
table=table)
data = [(0, "---Please Select Option---")]
for update_type in update_types:
data.append((update_type["id"], update_type["description"]))
return data
def prep_select(table="",
constraint="",
required_columns="*",
where_clause="",
params=(1, )):
"""
Prepare a list of id, agent tuples for use in creating selectfields
in forms.
input
=====
table: A string with the table(s) required.
constraint: (to be removed. Superceded by where_clause) A string containing
the value used in where statment.
where_clause: A string containg the statements after the where clause
eg " id = %s or staffid != %s"
params: Atuple containing the data refered to in the query
output: a list of tuples in as below:
======
(id, "firstname lastname") for the agent table.
"""
if table == "agent":
required_columns = "id, firstname, lastname"
user_details = fetch_from_table(table=table,
required_columns=required_columns,
where_clause=where_clause,
params=params)
data = [(0, "---Please Select Agent---")]
for agent in user_details:
fullname = "{0} {1}".format(agent["firstname"], agent["lastname"])
data.append((agent["id"], fullname))
elif table == "gender":
required_columns = "id, description"
genders = get_gender(required_columns=required_columns)
data = [(0, "---Please Select Gender---")]
for gender in genders:
data.append((gender["id"], gender["description"]))
elif table == "province":
required_columns = "id, name"
provinces = get_provinces(required_columns=required_columns)
data = [(0, "---Please Select Province---")]
for province in provinces:
data.append((province["id"], province["name"]))
elif table == "district":
required_columns = "id, name"
where_clause = "province_id = %s"
params = (constraint, )
districts = get_districts(required_columns=required_columns,
where_clause=where_clause,
params=params)
data = [(0, "---Please Select District---")]
for district in districts:
data.append((district["id"], district["name"]))
elif table == "ward":
required_columns = "id, ward_number"
where_clause = "district_id = %s"
params = (constraint, )
wards = get_wards(required_columns=required_columns,
where_clause=where_clause,
params=params)
data = [(0, "---Please Select Ward---")]
for ward in wards:
data.append((ward["id"], ward["ward_number"]))
elif table == "village":
required_columns = "id, name"
where_clause = "ward_id = %s"
params = (constraint, )
villages = get_villages(required_columns=required_columns,
where_clause=where_clause,
params=params)
data = [(0, "---Please Select village---")]
for village in villages:
data.append((village["id"], village["name"]))
elif table == "partner":
required_columns = "id, name"
partners = get_partners(required_columns=required_columns)
data = [(0, "---Please Select Partner---")]
for partner in partners:
data.append((partner["id"], partner["name"]))
elif table == "programme":
required_columns = "id, name"
programmes = get_programmes(required_columns=required_columns)
data = [(0, "---Please Select Programme---")]
for programme in programmes:
data.append((programme["id"], programme["name"]))
elif table == "topic":
required_columns = "id, description"
topics = get_topics(required_columns=required_columns)
data = list()
data = [(0, "---Please Select topic---")]
for topic in topics:
data.append((topic["id"], topic["description"]))
elif table == "priority":
required_columns = "id, description"
prioritys = get_prioritys(required_columns=required_columns)
data = [(0, "---Please Select Priority---")]
for priority in prioritys:
data.append((priority["id"], priority["description"]))
elif table == "update_type":
update_types = fetch_from_table(required_columns="id, description",
table=table)
data = [(0, "---Please Select Option---")]
for update_type in update_types:
data.append((update_type["id"], update_type["description"]))
return data
