def update(self, id, request_update_dict):
connection = MsqlConnection()
sentence_searh = "SELECT * FROM request_update WHERE ID = " + str(id)
row = connection.get_one(sentence_searh)
if row:
sentence_search2 = "SELECT * FROM support_department WHERE id='" + request_update_dict[
"id_department_support"] + "'"
row2 = connection.get_all(sentence_search2)
if row2:
sentence_search3 = "SELECT * FROM request WHERE id='" + request_update_dict[
"id_request"] + "'"
row3 = connection.get_all(sentence_search3)
if row3:
x = datetime.datetime.now()
current_date = str(x.year) + "-" + str(
x.month) + "-" + str(x.day)
sentence = "UPDATE request_update set id_department_support = '" + request_update_dict[
"id_department_support"] + "', description = '" + request_update_dict[
"description"] + "', status = '" + request_update_dict[
"status"] + "', date = '" + str(
current_date
) + "', id_request = '" + request_update_dict[
"id_request"] + "' WHERE ID = '" + str(
id) + "'"
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "request_update was updated"
else:
return "id request does not exist"
else:
return "id support deparment does not exist"
return "This request_update does not exist"
def new(self, request_update_dict):
connection = MsqlConnection()
sentence_search = "SELECT * FROM support_department WHERE id='" + request_update_dict[
"id_department_support"] + "'"
row = connection.get_all(sentence_search)
if row:
sentence_search2 = "SELECT * FROM request WHERE id='" + request_update_dict[
"id_request"] + "'"
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_update(id_department_support, description, " \
"status, date, id_request) VALUES('" + request_update_dict["id_department_support"] + "','" + \
request_update_dict["description"] + "','" + request_update_dict["status"] + "','" \
+ str(current_date) + "','" + request_update_dict["id_request"] + "')"
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "request_update was created correctly"
else:
connection.close_connection()
return "This id request does not exist"
else:
connection.close_connection()
return "This id support department does not exist"
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"
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 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_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 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 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 new(self, department_involved_dict):
connection = MsqlConnection()
sentence_search_department_support = "SELECT * FROM support_department WHERE id ='" \
+ str(department_involved_dict["id_department_support"]) + "'"
row = connection.get_all(sentence_search_department_support)
if row:
sentence_search_request = "SELECT * FROM problem_types WHERE id='" \
+ str(department_involved_dict["id_problem_type"]) + "'"
row_request = connection.get_all(sentence_search_request)
if row_request:
sentence = "INSERT support_department_problem_type(id_problem_type, id_support_department) VALUES('" + \
str(department_involved_dict[
"id_problem_type"]) + "','" + str(department_involved_dict["id_department_support"]) + "') "
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "This request was created correctly"
else:
connection.close_connection()
return "This problem type does not exist"
else:
connection.close_connection()
return "This department support does not exist" ""
def update(self, id, support_department_dict):
connection = MsqlConnection()
sentence_search = "SELECT * FROM support_department WHERE id='" + str(
id) + "'"
row = connection.get_all(sentence_search)
if row:
sentence_search_department = "SELECT * FROM department WHERE id='" \
+ support_department_dict["id_department"] + "'"
row_search = connection.get_all(sentence_search_department)
if row_search:
sentence = "UPDATE support_department set id_department = '" + support_department_dict[
"id_department"] + "', name = '" + support_department_dict["name"] + "', email = '"\
+ support_department_dict["email"] + "' WHERE ID = '" + str(id) + "'"
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "departments_involved was updated"
else:
connection.close_connection()
return "This id department does not exist"
else:
connection.close_connection()
return "This request does not exist"
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 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 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 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 update(self, id, admin_dict):
connection = MsqlConnection()
sentence_search = "SELECT * FROM support_department WHERE id='" + admin_dict["id_support_department"] + "'"
row = connection.get_all(sentence_search)
if row:
sentence = "UPDATE admin_support set first_name = '" + admin_dict["first_name"] + "', last_name = '" + \
admin_dict["last_name"] + "', phone = '" + admin_dict["phone"] + "', email = '" + admin_dict[
"email"] + "', employee_serial = '" + admin_dict["employee_serial"] + "', employee_serial = '" + \
admin_dict["id_support_department"] + "' WHERE ID = '" + str(id) + "'"
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "admin_support was updated correctly"
else:
connection.close_connection()
return "This support department does not exist"
def new(self, support_department_dict):
connection = MsqlConnection()
sentence_search = "SELECT * FROM department WHERE id='" \
+ support_department_dict["id_department"] + "'"
row = connection.get_all(sentence_search)
if row:
sentence = "INSERT support_department(id_department, name, " \
"email) VALUES('" + support_department_dict["id_department"] + "','" + \
support_department_dict["name"] + "','" + support_department_dict["email"] + "') "
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "This support department was created succesfully"
else:
connection.close_connection()
return "This support department does not exist"
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 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 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, 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 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 new(self, employee_in_charge_dict):
connection = MsqlConnection()
# sentence_search no jala
sentence_search = "SELECT * FROM employee_in_charge WHERE id ='" + employee_in_charge_dict[
"id_department"] + "'"
row = connection.get_all(sentence_search)
if row:
connection.close_connection()
return "This employee_in_charge is repeated"
else:
print("hola2")
sentence = "INSERT employee_in_charge(id_department, first_name, last_name, phone, email, employee_serial) " \
"VALUES('" + employee_in_charge_dict["id_department"] + "','" + employee_in_charge_dict[
"first_name"] + "','" + employee_in_charge_dict["last_name"] + "','" + \
employee_in_charge_dict[
"phone"] + \
"','" + employee_in_charge_dict["email"] + "','" + employee_in_charge_dict[
"employee_serial"] + "')"
connection.execute(sentence)
connection.commit()
connection.close_connection()
return "employee_in_charge was created correctly" ""
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 getAllNumberRequest(self):
connection = MsqlConnection()
sentence = "SELECT * FROM request"
records = connection.get_all(sentence)
return len(records)