397 lines
13 KiB
Python
397 lines
13 KiB
Python
import mysql.connector
|
|
import config
|
|
|
|
|
|
def getMySqlConnection():
|
|
connection = mysql.connector.connect(**config.MYSQL_CONFIG)
|
|
return connection
|
|
|
|
|
|
def closeConnection(connection,cursor):
|
|
if connection:
|
|
cursor.close()
|
|
connection.close()
|
|
|
|
|
|
|
|
def get_areas():
|
|
connection = getMySqlConnection()
|
|
cursor = connection.cursor()
|
|
|
|
postgreSQL_select_Query = "select * from aree"
|
|
|
|
cursor.execute(postgreSQL_select_Query)
|
|
publisher_records = cursor.fetchall()
|
|
|
|
closeConnection(connection,cursor)
|
|
return publisher_records
|
|
|
|
def get_sub_areas(area):
|
|
|
|
connection = getMySqlConnection()
|
|
cursor = connection.cursor()
|
|
|
|
postgreSQL_select_Query = "select ID,SOTTOAREA,AREA from sottoaree where area = %s"
|
|
|
|
cursor.execute(postgreSQL_select_Query, (area,))
|
|
publisher_records = cursor.fetchall()
|
|
|
|
closeConnection(connection,cursor)
|
|
return publisher_records
|
|
|
|
|
|
|
|
|
|
|
|
# print(get_sub_areas(2))
|
|
|
|
|
|
|
|
def getCompetences(competence_id=999999):
|
|
connection = getMySqlConnection()
|
|
cursor = connection.cursor()
|
|
|
|
postgreSQL_select_Query = "select * from competenze order by ID"
|
|
if competence_id != 999999:
|
|
postgreSQL_select_Query += " WHERE ID =" + str(competence_id)
|
|
|
|
cursor.execute(postgreSQL_select_Query)
|
|
publisher_records = cursor.fetchall()
|
|
|
|
closeConnection(connection,cursor)
|
|
return publisher_records
|
|
|
|
def getProducts(product_id=999999):
|
|
connection = getMySqlConnection()
|
|
cursor = connection.cursor()
|
|
|
|
postgreSQL_select_Query = "select * from prodotti order by ID"
|
|
if product_id != 999999:
|
|
postgreSQL_select_Query += " WHERE ID =" + str(product_id)
|
|
|
|
cursor.execute(postgreSQL_select_Query)
|
|
publisher_records = cursor.fetchall()
|
|
|
|
closeConnection(connection,cursor)
|
|
return publisher_records
|
|
|
|
|
|
|
|
|
|
def getTicket(filter_type="", filter_value=""):
|
|
connection = getMySqlConnection()
|
|
cursor = connection.cursor()
|
|
postgreSQL_select_Query = ""
|
|
params = ()
|
|
if filter_type == "":
|
|
postgreSQL_select_Query = "select * from segnalazioni limit 2"
|
|
elif filter_type == "C":
|
|
postgreSQL_select_Query = "select s.TICKET,cl.INTESTATARIO,s.OGGETTO,g.GRAVITA,c.COMPETENZA,p.PRODOTTO,s.APERTURA from segnalazioni as s join prodotti as p on s.PRODOTTO=p.ID join competenze as c on s.COMPETENZA=c.ID join gravita as g on s.GRAVITA=g.ID join clienti as cl on cl.ID=s.CLIENTE where s.COMPETENZA=%s AND DATE(s.APERTURA)=CURDATE()"
|
|
params = (filter_value,)
|
|
elif filter_type == "P":
|
|
postgreSQL_select_Query = "select s.TICKET,cl.INTESTATARIO,s.OGGETTO,g.GRAVITA,c.COMPETENZA,p.PRODOTTO,s.APERTURA from segnalazioni as s join prodotti as p on s.PRODOTTO=p.ID join gravita as g on s.GRAVITA=g.ID join competenze as c on s.COMPETENZA=c.ID join clienti as cl on cl.ID=s.CLIENTE where s.PRODOTTO=%s AND DATE(s.APERTURA)=CURDATE()"
|
|
params = (filter_value,)
|
|
elif filter_type == "A":
|
|
postgreSQL_select_Query = "select s.TICKET,cl.INTESTATARIO,s.OGGETTO,g.GRAVITA,c.COMPETENZA,p.PRODOTTO,s.APERTURA from segnalazioni as s join prodotti as p on s.PRODOTTO=p.ID join gravita as g on s.GRAVITA=g.ID join competenze as c on s.COMPETENZA=c.ID join clienti as cl on cl.ID=s.CLIENTE where s.AREA=%s AND DATE(s.APERTURA)=CURDATE()"
|
|
params = (filter_value,)
|
|
elif filter_type == "S":
|
|
postgreSQL_select_Query = "select s.TICKET,cl.INTESTATARIO,s.OGGETTO,g.GRAVITA,c.COMPETENZA,p.PRODOTTO,s.APERTURA from segnalazioni as s join prodotti as p on s.PRODOTTO=p.ID join gravita as g on s.GRAVITA=g.ID join competenze as c on s.COMPETENZA=c.ID join clienti as cl on cl.ID=s.CLIENTE where s.SOTTOAREA=%s AND DATE(s.APERTURA)=CURDATE()"
|
|
params = (filter_value,)
|
|
else:
|
|
closeConnection(connection, cursor)
|
|
return []
|
|
|
|
cursor.execute(postgreSQL_select_Query, params)
|
|
publisher_records = cursor.fetchall()
|
|
|
|
closeConnection(connection,cursor)
|
|
return publisher_records
|
|
|
|
|
|
def getTicketsByFilterSet(filter_rows):
|
|
filter_values = {
|
|
"C": [],
|
|
"P": [],
|
|
"A": [],
|
|
"S": [],
|
|
}
|
|
for row in filter_rows:
|
|
filter_type = str(row[2])
|
|
if filter_type not in filter_values:
|
|
continue
|
|
try:
|
|
filter_value = int(row[3])
|
|
except (TypeError, ValueError):
|
|
continue
|
|
if filter_value not in filter_values[filter_type]:
|
|
filter_values[filter_type].append(filter_value)
|
|
|
|
if not any(filter_values.values()):
|
|
return []
|
|
|
|
connection = getMySqlConnection()
|
|
cursor = connection.cursor()
|
|
select_query = """
|
|
select
|
|
s.TICKET,
|
|
cl.INTESTATARIO,
|
|
s.OGGETTO,
|
|
g.GRAVITA,
|
|
c.COMPETENZA,
|
|
p.PRODOTTO,
|
|
s.APERTURA
|
|
from segnalazioni as s
|
|
join prodotti as p on s.PRODOTTO=p.ID
|
|
join gravita as g on s.GRAVITA=g.ID
|
|
join competenze as c on s.COMPETENZA=c.ID
|
|
join clienti as cl on cl.ID=s.CLIENTE
|
|
"""
|
|
column_map = {
|
|
"C": "s.COMPETENZA",
|
|
"P": "s.PRODOTTO",
|
|
"A": "s.AREA",
|
|
"S": "s.SOTTOAREA",
|
|
}
|
|
where_parts = ["DATE(s.APERTURA)=CURDATE()"]
|
|
params = []
|
|
for filter_type, values in filter_values.items():
|
|
if not values:
|
|
continue
|
|
placeholders = ",".join(["%s"] * len(values))
|
|
where_parts.append(f"{column_map[filter_type]} IN ({placeholders})")
|
|
params.extend(values)
|
|
|
|
select_query += " where " + " AND ".join(where_parts)
|
|
cursor.execute(select_query, tuple(params))
|
|
publisher_records = cursor.fetchall()
|
|
|
|
closeConnection(connection,cursor)
|
|
return publisher_records
|
|
|
|
|
|
|
|
def getProductOrCompetence(type_,value_):
|
|
connection = getMySqlConnection()
|
|
cursor = connection.cursor()
|
|
|
|
postgreSQL_select_product = "select prodotto from prodotti WHERE ID="+value_
|
|
postgreSQL_select_competence = "select COMPETENZA from competenze WHERE ID="+value_
|
|
postgreSQL_select_area = "select AREA from aree a where ID="+value_
|
|
postgreSQL_select_sub_area = "select SOTTOAREA from sottoaree where ID="+value_
|
|
|
|
if type_ == "P":
|
|
cursor.execute(postgreSQL_select_product)
|
|
elif type_ == 'C':
|
|
cursor.execute(postgreSQL_select_competence)
|
|
elif type_ == 'A':
|
|
cursor.execute(postgreSQL_select_area)
|
|
elif type_ == 'S':
|
|
cursor.execute(postgreSQL_select_sub_area)
|
|
|
|
publisher_records = cursor.fetchall()
|
|
|
|
closeConnection(connection,cursor)
|
|
# print(publisher_records)
|
|
return publisher_records
|
|
|
|
|
|
def getCompetenceOrProdotto(ticket):
|
|
connection = getMySqlConnection()
|
|
cursor = connection.cursor()
|
|
|
|
select_problem = "SELECT COMPETENZA, PRODOTTO, PROBLEMA, AREA, SOTTOAREA FROM segnalazioni where TICKET = %s"
|
|
cursor.execute(select_problem, (ticket,))
|
|
|
|
publisher_records = cursor.fetchall()
|
|
|
|
closeConnection(connection,cursor)
|
|
|
|
if not publisher_records:
|
|
return "", "", "", "", ""
|
|
|
|
return str(publisher_records[0][0]) ,str(publisher_records[0][1]) ,str(publisher_records[0][2]) , str(publisher_records[0][3]) ,str(publisher_records[0][4])
|
|
|
|
# def getProblemaBYCompetenzaOrProgramma(competence, prodotto):
|
|
# connection = getMySqlConnection()
|
|
# cursor = connection.cursor()
|
|
# # From DB the competence is not null
|
|
# if prodotto != None and competence != None:
|
|
# select_problem = "SELECT PROBLEMA FROM segnalazioni where COMPETENZA = " + str(competence) + " AND PRODOTTO = " + str(prodotto)+ ""
|
|
# elif prodotto == None:
|
|
# select_problem = "SELECT PROBLEMA FROM segnalazioni where COMPETENZA = " + str(competence) + ""
|
|
|
|
# cursor.execute(select_problem)
|
|
|
|
# publisher_records = cursor.fetchall()
|
|
|
|
# closeConnection(connection,cursor)
|
|
# return publisher_records
|
|
|
|
def getProblemaBYCompetenzaOrProgramma_manul(competence, prodotto, area, sottoarea):
|
|
connection = getMySqlConnection()
|
|
cursor = connection.cursor()
|
|
|
|
|
|
where_parts = []
|
|
params = []
|
|
|
|
if prodotto is not None and prodotto.isdigit():
|
|
print("Trovato prodotto: ", prodotto)
|
|
where_parts.append("PRODOTTO = %s")
|
|
params.append(int(prodotto))
|
|
|
|
if area is not None and area.isdigit():
|
|
print("Trovato area: ", area)
|
|
where_parts.append("AREA = %s")
|
|
params.append(int(area))
|
|
|
|
|
|
if sottoarea is not None and sottoarea.isdigit():
|
|
print("Trovato sottoarea: ", sottoarea)
|
|
where_parts.append("SOTTOAREA = %s")
|
|
params.append(int(sottoarea))
|
|
|
|
# If none of the where condition is satisfied
|
|
if not where_parts:
|
|
if competence is not None and str(competence).isdigit():
|
|
print("Trovato competence: ", competence)
|
|
where_parts.append("COMPETENZA = %s")
|
|
params.append(int(competence))
|
|
else:
|
|
print("Trovato : ", "Nulla")
|
|
where_parts.append("FALSE")
|
|
|
|
|
|
main_sql = "SELECT PROBLEMA FROM segnalazioni WHERE " + " AND ".join(where_parts)
|
|
|
|
print(main_sql)
|
|
cursor.execute(main_sql, tuple(params))
|
|
|
|
publisher_records = cursor.fetchall()
|
|
count = cursor.rowcount
|
|
|
|
closeConnection(connection,cursor)
|
|
# if count <1:
|
|
# print("FFFFFF")
|
|
# return "Non ho trovato ticket con i filtri del ticket principale."
|
|
return publisher_records
|
|
|
|
|
|
def getTicketByProblema(problem):
|
|
connection = getMySqlConnection()
|
|
cursor = connection.cursor()
|
|
|
|
# Use placeholders for the query and pass the value as a parameter
|
|
select_problem = "SELECT TICKET, SOLUZIONE, PROBLEMA FROM segnalazioni WHERE PROBLEMA = %s"
|
|
cursor.execute(select_problem, (problem,))
|
|
|
|
publisher_records = cursor.fetchall()
|
|
|
|
closeConnection(connection,cursor)
|
|
return publisher_records[0]
|
|
|
|
|
|
def get_ticket_context(ticket):
|
|
connection = getMySqlConnection()
|
|
cursor = connection.cursor(dictionary=True)
|
|
|
|
query = """
|
|
SELECT
|
|
s.TICKET AS ticket_id,
|
|
s.OGGETTO AS subject,
|
|
s.PROBLEMA AS problem,
|
|
s.SOLUZIONE AS solution,
|
|
s.COMPETENZA AS competence_id,
|
|
c.COMPETENZA AS competence,
|
|
s.PRODOTTO AS product_id,
|
|
p.prodotto AS product,
|
|
s.AREA AS area_id,
|
|
a.AREA AS area,
|
|
s.SOTTOAREA AS subarea_id,
|
|
sa.SOTTOAREA AS subarea,
|
|
s.CLIENTE AS client_id,
|
|
cl.INTESTATARIO AS client,
|
|
s.GRAVITA AS gravity_id,
|
|
g.GRAVITA AS gravity,
|
|
s.STATO AS status_id,
|
|
st.STATO AS status,
|
|
s.APERTURA AS opened_at,
|
|
s.CHIUSURA AS closed_at,
|
|
s.PROGRAMMA AS program,
|
|
s.TIPO_PROGRAMMA AS program_type,
|
|
s.VERSIONE AS version_id,
|
|
s.PATCH AS patch_id,
|
|
s.CLASSIFICAZIONE AS classification_id,
|
|
s.SISTEMA_OPERATIVO AS os_id
|
|
FROM segnalazioni s
|
|
LEFT JOIN competenze c ON c.ID = s.COMPETENZA
|
|
LEFT JOIN prodotti p ON p.ID = s.PRODOTTO
|
|
LEFT JOIN aree a ON a.ID = s.AREA
|
|
LEFT JOIN sottoaree sa ON sa.ID = s.SOTTOAREA
|
|
LEFT JOIN clienti cl ON cl.ID = s.CLIENTE
|
|
LEFT JOIN gravita g ON g.ID = s.GRAVITA
|
|
LEFT JOIN stati st ON st.ID = s.STATO
|
|
WHERE s.TICKET = %s
|
|
"""
|
|
cursor.execute(query, (ticket,))
|
|
records = cursor.fetchall()
|
|
closeConnection(connection, cursor)
|
|
return records[0] if records else None
|
|
|
|
|
|
def fetch_solved_ticket_batch(last_ticket=0, limit=1000):
|
|
connection = getMySqlConnection()
|
|
cursor = connection.cursor(dictionary=True)
|
|
|
|
query = """
|
|
SELECT
|
|
s.TICKET AS ticket_id,
|
|
s.OGGETTO AS subject,
|
|
s.PROBLEMA AS problem,
|
|
s.SOLUZIONE AS solution,
|
|
s.COMPETENZA AS competence_id,
|
|
c.COMPETENZA AS competence,
|
|
s.PRODOTTO AS product_id,
|
|
p.prodotto AS product,
|
|
s.AREA AS area_id,
|
|
a.AREA AS area,
|
|
s.SOTTOAREA AS subarea_id,
|
|
sa.SOTTOAREA AS subarea,
|
|
s.CLIENTE AS client_id,
|
|
cl.INTESTATARIO AS client,
|
|
s.GRAVITA AS gravity_id,
|
|
g.GRAVITA AS gravity,
|
|
s.STATO AS status_id,
|
|
st.STATO AS status,
|
|
s.APERTURA AS opened_at,
|
|
s.CHIUSURA AS closed_at,
|
|
s.PROGRAMMA AS program,
|
|
s.TIPO_PROGRAMMA AS program_type,
|
|
s.VERSIONE AS version_id,
|
|
s.PATCH AS patch_id,
|
|
s.CLASSIFICAZIONE AS classification_id,
|
|
s.SISTEMA_OPERATIVO AS os_id
|
|
FROM segnalazioni s
|
|
LEFT JOIN competenze c ON c.ID = s.COMPETENZA
|
|
LEFT JOIN prodotti p ON p.ID = s.PRODOTTO
|
|
LEFT JOIN aree a ON a.ID = s.AREA
|
|
LEFT JOIN sottoaree sa ON sa.ID = s.SOTTOAREA
|
|
LEFT JOIN clienti cl ON cl.ID = s.CLIENTE
|
|
LEFT JOIN gravita g ON g.ID = s.GRAVITA
|
|
LEFT JOIN stati st ON st.ID = s.STATO
|
|
WHERE s.TICKET > %s
|
|
AND s.SOLUZIONE IS NOT NULL
|
|
AND TRIM(s.SOLUZIONE) <> ''
|
|
ORDER BY s.TICKET
|
|
LIMIT %s
|
|
"""
|
|
cursor.execute(query, (last_ticket, limit))
|
|
records = cursor.fetchall()
|
|
closeConnection(connection, cursor)
|
|
return records
|
|
|
|
|
|
|
|
# print(getTicketByProblema("CERCA SABRINA"))
|
|
# print(getTicketTest("P", "24"))
|