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