def getNumberRequestUser(self, mail):
connection = MsqlConnection()
adapter = UserAdapter()
user = adapter.get(mail)
sentence = "SELECT * FROM request WHERE user_id_creation = '" + str(user['id']) + "'"
records = connection.get_all(sentence)
return len(records)
def getNumberRequestInvolved(self, mail):
connection = MsqlConnection()
adapter = AdminSupportAdapter()
admin = adapter.get(mail)
print(admin)
sentence = "SELECT * FROM departments_involved WHERE id_department_support = '" + str(admin['id_support_department']) + "'"
records = connection.get_all(sentence)
return len(records)
def getRequestInvolved(self, mail):
connection = MsqlConnection()
adapter = AdminSupportAdapter()
admin = adapter.get(mail)
sentence = "SELECT id_request FROM departments_involved WHERE id_department_support = '" + str(admin['id_support_department']) + "'"
records = connection.get_all(sentence)
record_to_return = []
for i in records:
record_to_return.append(self.get(i[0]))
return record_to_return
def list(self):
connection = MsqlConnection()
sentence = "SELECT * FROM department"
records = connection.get_all(sentence)
connection.close_connection()
records_to_return = []
for record in records:
each_department = {
"id": record[0],
"department": record[1]
}
records_to_return.append(each_department)
return records_to_return
def new(self, department_dict):
connection = MsqlConnection()
sentence = "INSERT department(department) VALUES('" + department_dict["department"] + "')"
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "Department was created correctly"""
def new(self, user_dict):
connection = MsqlConnection()
sentence = "INSERT users(first_name, last_name, phone, email, employee_serial) VALUES('" + user_dict["first_name"] + "','" + user_dict["last_name"] + "','" + user_dict["phone"] + "','" + user_dict["email"] + "','" + user_dict["employee_serial"] + "') "
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "User was created correctly"""
def list(self):
connection = MsqlConnection()
sentence = "SELECT * FROM departments_involved"
records = connection.get_all(sentence)
connection.close_connection()
records_to_return = []
for record in records:
each_department_involved = {
"id": record[0],
"id_department_support": record[1],
"id_request": record[2],
}
records_to_return.append(each_department_involved)
return records_to_return
def list(self):
connection = MsqlConnection()
sentence = "SELECT * FROM problem_types"
records = connection.get_all(sentence)
connection.close_connection()
records_to_return = []
for record in records:
each_problem_type = {
"id": record[0],
"name": record[1],
"description": record[2],
}
records_to_return.append(each_problem_type)
return records_to_return
def delete(self, id):
connection = MsqlConnection()
sentenceSearh = "SELECT * FROM request WHERE ID = " + str(id)
row = connection.get_one(sentenceSearh)
if row:
sentence = "DELETE FROM request WHERE ID = '" + str(id) + "'"
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "request was deleted"
return "This request does not exist"
def delete(self, id):
connection = MsqlConnection()
sentence_search = "SELECT * FROM departments_involved WHERE ID = " + str(id)
row = connection.get_one(sentence_search)
if row:
sentence = "DELETE FROM departments_involved WHERE ID = '" + str(id) + "'"
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "departments_involved was deleted"
return "This departments_involved does not exist"
def get(self, mail):
connection = MsqlConnection()
sentence = "SELECT * FROM users WHERE email = '" + mail + "'"
records = connection.get_all(sentence)
connection.close_connection()
for record in records:
each_user = {
"id": record[0],
"first_name": record[1],
"last_name": record[2],
"phone": record[3],
"email": record[4],
"employee_serial": record[5]
}
return each_user
def list(self):
connection = MsqlConnection()
sentence = "SELECT * FROM support_department"
records = connection.get_all(sentence)
connection.close_connection()
records_to_return = []
for record in records:
each_support_department = {
"id": record[0],
"id_department": record[1],
"name": record[2],
"email": record[3]
}
records_to_return.append(each_support_department)
return records_to_return
def update(self, id, user_dict):
connection = MsqlConnection()
sentenceSearh = "SELECT * FROM users WHERE ID = " + str(id)
row = connection.get_one(sentenceSearh)
if row:
sentence = "UPDATE users set first_name = '" + user_dict["first_name"] + "', last_name = '" + user_dict["last_name"] + "', phone = '" + user_dict["phone"] + "', email = '" + user_dict["email"] + "', employee_serial = '" + user_dict["employee_serial"] + "' WHERE ID = '" + str(id) + "'"
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "User was updated"
return "This user does not exist"
def update(self, id, department_dict):
connection = MsqlConnection()
sentenceSearh = "SELECT * FROM department WHERE ID = " + str(id)
row = connection.get_one(sentenceSearh)
if row:
sentence = "UPDATE department set department = '" + department_dict["department"] + "' WHERE ID = '" + str(id) + "'"
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "Department was updated"
return "This department does not exist"
def new(self, problem_type_dict):
connection = MsqlConnection()
sentence = "INSERT problem_types(name, description) " \
"VALUES('" + problem_type_dict["name"] + "','" + problem_type_dict[
"description"] + "')"
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "problem_types was created correctly" ""
def delete(self, id):
connection = MsqlConnection()
sentence_search = "SELECT * FROM employee_in_charge WHERE ID = " + str(
id)
row = connection.get_one(sentence_search)
if row:
sentence = "DELETE FROM employee_in_charge WHERE ID = '" + str(
id) + "'"
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "employee_in_charge was deleted"
return "This employee_in_charge does not exist"
def list(self):
connection = MsqlConnection()
sentence = "SELECT * FROM request_update"
records = connection.get_all(sentence)
connection.close_connection()
records_to_return = []
for record in records:
each_request = {
"id": record[0],
"id_department_support": record[1],
"description": record[2],
"status": record[3],
"date": record[4],
"id_request": record[5]
}
records_to_return.append(each_request)
return records_to_return
def list(self):
connection = MsqlConnection()
sentence = "SELECT * FROM users"
records = connection.get_all(sentence)
connection.close_connection()
records_to_return = []
for record in records:
each_user = {
"id": record[0],
"first_name": record[1],
"last_name": record[2],
"phone": record[3],
"email": record[4],
"employee_serial": record[5]
}
records_to_return.append(each_user)
return records_to_return
def update(self, id, problem_types_dict):
connection = MsqlConnection()
sentenceSearh = "SELECT * FROM problem_types WHERE ID = " + str(id)
row = connection.get_one(sentenceSearh)
if row:
sentence = "UPDATE problem_types set name = '" + problem_types_dict[
"name"] + "', description = '" + problem_types_dict[
"description"] + "' WHERE ID = '" + str(id) + "'"
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "problem_types was updated"
return "This problem_types does not exist"
def list(self):
connection = MsqlConnection()
sentence = "SELECT * FROM employee_in_charge"
records = connection.get_all(sentence)
connection.close_connection()
records_to_return = []
for record in records:
each_employee_in_charge = {
"id": record[0],
"id_department": record[1],
"first_name": record[2],
"last_name": record[3],
"phone": record[4],
"email": record[5],
"employee_serial": record[6],
}
records_to_return.append(each_employee_in_charge)
return records_to_return
def get(self, id):
connection = MsqlConnection()
sentence = "SELECT * FROM request WHERE id = '" + str(id) + "'"
records = connection.get_all(sentence)
connection.close_connection()
for record in records:
each_request = {
"id": record[0],
"id_problem_type": record[1],
"description": record[2],
"creation_date": record[3],
"update_date": record[4],
"date_closed_ticket": record[5],
"user_id_creation": record[6],
"status": record[7],
"solution": record[8],
"priority": record[9],
"country": record[10],
}
return each_request
def update(self, id, employee_in_charge_dict):
connection = MsqlConnection()
sentence_search = "SELECT * FROM employee_in_charge WHERE ID = " + str(
id)
row = connection.get_one(sentence_search)
if row:
sentence = "UPDATE employee_in_charge set id_department = '" + employee_in_charge_dict[
"id_department"] + "', first_name = '" + employee_in_charge_dict["last_name"] + "', phone = '" + \
employee_in_charge_dict["phone"] + "', email = '" + employee_in_charge_dict[
"email"] + "', employee_serial = '" + employee_in_charge_dict[
"employee_serial"] + "' WHERE ID = '" + str(id) + "'"
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "employee_in_charge was updated"
return "This employee_in_charge does not exist"
def getAllRequest(self):
connection = MsqlConnection()
sentence = "SELECT * FROM request"
records = connection.get_all(sentence)
connection.close_connection()
records_to_return = []
for record in records:
each_request = {
"id": record[0],
"id_problem_type": record[1],
"description": record[2],
"creation_date": record[3],
"update_date": record[4],
"date_closed_ticket": record[5],
"user_id_creation": record[6],
"status": record[7],
"solution": record[8],
"priority": record[9],
"country": record[10],
}
records_to_return.append(each_request)
return records_to_return
def update(self, id, department_country_support_dict):
connection = MsqlConnection()
sentenceSearh = "SELECT * FROM department_country_support WHERE ID = " + str(
id)
row = connection.get_one(sentenceSearh)
if row:
sentence_valid_department = "SELECT * FROM department WHERE id ='" + department_country_support_dict[
"id_department"] + "'"
row_valid = connection.get_all(sentence_valid_department)
if row_valid:
sentence = "UPDATE department_country_support set id_department = '" + department_country_support_dict[
"id_department"] + "', country = '" + department_country_support_dict[
"country"] + "' WHERE ID = '" + str(id) + "'"
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "department_country_support was updated"
else:
return "This department id does not exist"
else:
return "This department_country_support does not exist"
def getRequestUser(self, mail):
connection = MsqlConnection()
adapter = UserAdapter()
user = adapter.get(mail)
sentence = "SELECT * FROM request WHERE user_id_creation = '" + str(user['id']) + "'"
records = connection.get_all(sentence)
records_to_return = []
for record in records:
each_request = {
"id": record[0],
"id_problem_type": record[1],
"description": record[2],
"creation_date": record[3],
"update_date": record[4],
"date_closed_ticket": record[5],
"user_id_creation": record[6],
"status": record[7],
"solution": record[8],
"priority": record[9],
"country": record[10],
}
records_to_return.append(each_request)
return records_to_return
def update(self, id, department_involved_dict):
connection = MsqlConnection()
sentence_search_department_support = "SELECT * FROM support_department WHERE id ='" \
+ department_involved_dict["id_department_support"] + "'"
row = connection.get_all(sentence_search_department_support)
if row:
sentence_search_request = "SELECT * FROM request WHERE id='" \
+ department_involved_dict["id_request"] + "'"
row_request = connection.get_all(sentence_search_request)
if row_request:
sentence = "UPDATE departments_involved set id_department_support = '" + department_involved_dict[
"id_department_support"] + "', id_request = '" + department_involved_dict[
"id_request"] + "' WHERE ID = '" + str(id) + "'"
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "departments_involved was updated"
else:
connection.close_connection()
return "This request does not exist"
else:
connection.close_connection()
return "This department support does not exist"
def update(self, id, request_dict):
connection = MsqlConnection()
sentenceSearh = "SELECT * FROM request WHERE ID = " + str(id)
row = connection.get_one(sentenceSearh)
if row:
sentence_search = "SELECT * FROM problem_types WHERE id='" + request_dict["id_problem_type"] + "'"
row_2 = connection.get_all(sentence_search)
if row_2:
x = datetime.datetime.now()
current_date = str(x.year) + "-" + str(x.month) + "-" + str(x.day)
sentence = "UPDATE request set id_problemType = '" + request_dict[
"id_problem_type"] + "', description = '" + request_dict["description"] + "', creation_date = '" + \
request_dict["creation_date"] + "', update_date = '" + request_dict[
"update_date"] + "', date_closed_ticket = '" + request_dict[
"date_closed_ticket"] + "', status = '" + request_dict[
"status"] + "', solution = '" + request_dict[
"solution"] + "', priority = '" + request_dict[
"priority"] + "', country = '" + request_dict[
"country"] + "' WHERE ID = '" + str(id) + "'"
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "request was updated"
else:
connection.close_connection()
return "This problem type does not exist"
else:
connection.close_connection()
return "This request does not exist"
def new(self, department_involved_dict):
connection = MsqlConnection()
sentence_search_department_support = "SELECT * FROM support_department WHERE id ='" \
+ department_involved_dict["id_department_support"] + "'"
row = connection.get_all(sentence_search_department_support)
if row:
sentence_search_request = "SELECT * FROM request WHERE id='" \
+ department_involved_dict["id_request"] + "'"
row_request = connection.get_all(sentence_search_request)
if row_request:
sentence = "INSERT departments_involved(id_department_support, id_request) VALUES('" + \
department_involved_dict[
"id_department_support"] + "','" + department_involved_dict["id_request"] + "') "
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "This department involved was created correctly"
else:
connection.close_connection()
return "This request does not exist"
else:
connection.close_connection()
return "This department support does not exist"
def new(self, department_country_support_dict):
connection = MsqlConnection()
sentence_search = "SELECT * FROM department WHERE id ='" + department_country_support_dict[
"id_department"] + "'"
row = connection.get_all(sentence_search)
if row:
sentence = "INSERT department_country_support(id_department, country) VALUES('" + department_country_support_dict[
"id_department"] + "','" + department_country_support_dict[
"country"] + "') "
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "departments_involved was created correctly"
else:
connection.close_connection()
return "This department was incorrectly"
def new(self, request_dict):
connection = MsqlConnection()
sentence_search = "SELECT * FROM problem_types WHERE id='" + request_dict["id_problem_type"] + "'"
row = connection.get_all(sentence_search)
if row:
sentence_search2 = "SELECT * FROM users WHERE id='" + request_dict["user_id_creation"] + "'"
row2 = connection.get_all(sentence_search2)
if row2:
x = datetime.datetime.now()
current_date = str(x.year) + "-" + str(x.month) + "-" + str(x.day)
sentence = "INSERT request(id_problemType, description, creation_date, user_id_creation, status, priority, country) VALUES('" + request_dict["id_problem_type"] + "','" \
+ request_dict["description"] + "','" + str(current_date) + "','" \
+ request_dict["user_id_creation"] + "','IN PROCESS','" + request_dict["priority"] \
+ "','" + request_dict["country"] +"')"
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "This request was created succesfully"
else:
connection.close_connection()
return "This user does not exist"
else:
connection.close_connection()
return "This problem type does not exist"