sql_queries.py

get_all_tickets = """SELECT tickets.id, tickets.status, tickets.category, tickets.summary, users.first_name, users.last_name, strftime('%d/%m/%Y', tickets.created_at) as date FROM ((devices INNER JOIN inventory_ticket_relations ON devices.id = inventory_ticket_relations.inventory_item_id) INNER JOIN tickets ON inventory_ticket_relations.ticket_id = tickets.id) INNER JOIN users ON tickets.created_by = users.id ORDER BY tickets.id DESC LIMIT {}, {};"""

get_repairs_tickets = """SELECT tickets.id, tickets.status, tickets.category, tickets.summary, users.first_name, users.last_name, strftime('%d/%m/%Y', tickets.created_at) as date FROM ((devices INNER JOIN inventory_ticket_relations ON devices.id = inventory_ticket_relations.inventory_item_id) INNER JOIN tickets ON inventory_ticket_relations.ticket_id = tickets.id) INNER JOIN users ON tickets.created_by = users.id WHERE (((tickets.category)="Student Repair")) ORDER BY tickets.id DESC LIMIT {}, {};"""

get_loans_tickets = """SELECT tickets.id, tickets.status, tickets.category, tickets.summary, users.first_name, users.last_name, strftime('%d/%m/%Y', tickets.created_at) as date FROM ((devices INNER JOIN inventory_ticket_relations ON devices.id = inventory_ticket_relations.inventory_item_id) INNER JOIN tickets ON inventory_ticket_relations.ticket_id = tickets.id) INNER JOIN users ON tickets.created_by = users.id WHERE (((tickets.category)="Loans")) ORDER BY tickets.id DESC LIMIT {}, {};"""

get_misc_tickets = """SELECT tickets.id, tickets.status, tickets.category, tickets.summary, users.first_name, users.last_name, strftime('%d/%m/%Y', tickets.created_at) as date FROM ((devices INNER JOIN inventory_ticket_relations ON devices.id = inventory_ticket_relations.inventory_item_id) INNER JOIN tickets ON inventory_ticket_relations.ticket_id = tickets.id) INNER JOIN users ON tickets.created_by = users.id WHERE (((tickets.category) NOT LIKE "Loans")) AND (((tickets.category) NOT LIKE "Student Repair")) ORDER BY tickets.id DESC LIMIT {}, {};"""

get_ticket = """SELECT tickets.id, tickets.summary, users.first_name, users.last_name, tickets.c_vendor_case_number, devices.manufacturer, devices.model, devices.name, devices.serial_number, tickets.c_replacement_device, tickets.description, strftime('%d/%m/%Y', tickets.created_at) as date FROM ((devices INNER JOIN inventory_ticket_relations ON devices.id = inventory_ticket_relations.inventory_item_id) INNER JOIN tickets ON inventory_ticket_relations.ticket_id = tickets.id) INNER JOIN users ON tickets.created_by = users.id WHERE (((tickets.id)={}));"""

get_ticket_comments = """SELECT comments.body, strftime('%d/%m/%Y', comments.created_at) as date FROM comments WHERE (((comments.comment_type) NOT LIKE "response") AND ((comments.ticket_id)={})) ORDER BY comments.id;"""

get_top10_repairs = """SELECT tickets.id, tickets.status, tickets.category, tickets.summary, users.first_name, users.last_name, strftime('%d/%m/%Y', tickets.created_at) as date FROM ((devices INNER JOIN inventory_ticket_relations ON devices.id = inventory_ticket_relations.inventory_item_id) INNER JOIN tickets ON inventory_ticket_relations.ticket_id = tickets.id) INNER JOIN users ON tickets.created_by = users.id WHERE (((tickets.category)="Student Repair")) ORDER BY tickets.id DESC LIMIT 10;"""

get_top10_loans = """SELECT tickets.id, tickets.status, tickets.category, tickets.summary, users.first_name, users.last_name, strftime('%d/%m/%Y', tickets.created_at) as date FROM ((devices INNER JOIN inventory_ticket_relations ON devices.id = inventory_ticket_relations.inventory_item_id) INNER JOIN tickets ON inventory_ticket_relations.ticket_id = tickets.id) INNER JOIN users ON tickets.created_by = users.id WHERE (((tickets.category)="Loans")) ORDER BY tickets.id DESC LIMIT 10;"""

get_top10_misc = """SELECT tickets.id, tickets.status, tickets.category, tickets.summary, users.first_name, users.last_name, strftime('%d/%m/%Y', tickets.created_at) as date FROM ((devices INNER JOIN inventory_ticket_relations ON devices.id = inventory_ticket_relations.inventory_item_id) INNER JOIN tickets ON inventory_ticket_relations.ticket_id = tickets.id) INNER JOIN users ON tickets.created_by = users.id WHERE (((tickets.category) NOT LIKE "Loans")) AND (((tickets.category) NOT LIKE "Student Repair")) ORDER BY tickets.id DESC LIMIT 10;"""

all_tickets_count = """SELECT count(*) FROM ((devices INNER JOIN inventory_ticket_relations ON devices.id = inventory_ticket_relations.inventory_item_id) INNER JOIN tickets ON inventory_ticket_relations.ticket_id = tickets.id) INNER JOIN users ON tickets.created_by = users.id;"""

all_repairs_count = """SELECT count(*) FROM ((devices INNER JOIN inventory_ticket_relations ON devices.id = inventory_ticket_relations.inventory_item_id) INNER JOIN tickets ON inventory_ticket_relations.ticket_id = tickets.id) INNER JOIN users ON tickets.created_by = users.id WHERE (((tickets.category)="Student Repair"));"""

all_loans_count = """SELECT count(*) FROM ((devices INNER JOIN inventory_ticket_relations ON devices.id = inventory_ticket_relations.inventory_item_id) INNER JOIN tickets ON inventory_ticket_relations.ticket_id = tickets.id) INNER JOIN users ON tickets.created_by = users.id WHERE (((tickets.category)="Loans"));"""

all_misc_count = """SELECT count(*) FROM ((devices INNER JOIN inventory_ticket_relations ON devices.id = inventory_ticket_relations.inventory_item_id) INNER JOIN tickets ON inventory_ticket_relations.ticket_id = tickets.id) INNER JOIN users ON tickets.created_by = users.id WHERE (((tickets.category) NOT LIKE "Loans")) AND (((tickets.category) NOT LIKE "Student Repair"));"""