class InterviewsHistoryRepository:
def __init__(self, db):
self.db = db
self.sql = SqlDatabase(db=db)
def CreateInterview(self, fields):
cursor = self.db.cursor()
command = """
INSERT INTO interviews
(application_id, user_id, entry_date, interview_date, interview_time, interview_type, interview_location, interview_medium, other_medium, contact_number, status, interviewer_names, video_link, extra_notes)
VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
"""
result = cursor.execute(command, tuple(fields.values()))
self.db.commit()
return result.lastrowid
def getInterviewByID(self, interview_ID):
result = self.sql.getByField('interviews', 'interview_id',
interview_ID)
interview_result = Interview(result)
return interview_result
def getInterviewsByUserID(self, user_id):
cursor = self.db.cursor()
command = "SELECT * FROM interviews WHERE user_id={} ORDER BY interview_date DESC, interview_time DESC".format(
user_id)
result = cursor.execute(command)
self.db.commit()
interviews_list = []
for interview in result:
interview_result = Interview(interview)
interviews_list.append(interview_result)
if interviews_list == []:
return None
return interviews_list
def getInterviewsByApplicationID(self, application_id):
cursor = self.db.cursor()
command = "SELECT * FROM interviews where application_id = {} ORDER BY interview_date DESC, interview_time DESC".format(
application_id)
result = cursor.execute(command)
self.db.commit()
interviews_list = []
for interview in result:
interview_result = Interview(interview)
interviews_list.append(interview_result)
if interviews_list == []:
return None
return interviews_list
def getTop6InterviewsByApplicationID(self, application_id):
cursor = self.db.cursor()
command = "SELECT * FROM interviews where application_id = {} ORDER BY interview_date, interview_time LIMIT 6".format(
application_id)
result = cursor.execute(command)
self.db.commit()
interviews_list = []
for interview in result:
interview_result = Interview(interview)
interviews_list.append(interview_result)
if interviews_list == []:
return None
return interviews_list
def updateInterviewByID(self, fields):
cursor = self.db.cursor()
command = """
UPDATE interviews
SET interview_date = ?,
interview_time = ?,
interviewer_names = ?,
interview_type = ?,
interview_location = ?,
interview_medium = ?,
other_medium = ?,
contact_number = ?,
status = ?,
video_link = ?,
extra_notes = ?
WHERE interview_id = ? AND application_id = ?"""
cursor.execute(command, tuple(fields.values()))
self.db.commit()
def updateInterviewStatusByID(self, fields):
cursor = self.db.cursor()
command = """
UPDATE interviews
SET status = ?
WHERE interview_id = ?"""
cursor.execute(command, tuple(fields.values()))
self.db.commit()
def deleteInterviewByID(self, interview_id):
message = ""
try:
cursor = self.db.cursor()
command = "DELETE FROM interviews WHERE interview_id = {}".format(
interview_id)
cursor.execute(command)
self.db.commit()
message = "Interview deleted successfully."
except sqlite3.Error as error:
message = "Interview failed to delete. " + error
finally:
return message
def deleteInterviewsByApplicationID(self, application_id):
message = ""
try:
cursor = self.db.cursor()
command = "DELETE FROM interviews WHERE application_id = {}".format(
application_id)
cursor.execute(command)
self.db.commit()
message = "Interviews deleted successfully."
except sqlite3.Error as error:
message = "Interviews failed to delete. " + error
finally:
return message
def deleteInterviewsByUserID(self, user_id):
message = ""
try:
cursor = self.db.cursor()
command = "DELETE FROM interviews WHERE user_id = {}".format(
user_id)
cursor.execute(command)
self.db.commit()
message = "Interviews deleted successfully."
except sqlite3.Error as error:
message = "Interviews failed to delete. " + error
finally:
return message
def __init__(self, db):
self.db = db
self.sql = SqlDatabase(db=db)
class CompanyRepository:
def __init__(self, db):
self.sql = SqlDatabase(db=db)
self.db = db
def createCompany(self, fields):
cursor = self.db.cursor()
command = """
INSERT INTO company
(user_id, name, description, location, industry, url, interviewers, contact_number)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
"""
result = cursor.execute(command, tuple(fields.values()))
self.db.commit()
return result.lastrowid
def getCompanyById(self, company_id):
result = self.sql.getByField('company', 'company_id', company_id)
company = Company(result)
return company
def getCompanyByName(self, company_name, user_id) -> Company:
result = self.sql.getByName('company', 'name', company_name, 'user_id',
user_id)
if not result:
return None
company = Company(result)
return company
def getCompanyEntriesByUserID(self, user_id):
cursor = self.db.cursor()
command = """
SELECT * FROM company
WHERE user_id = {}
ORDER BY name
""".format(user_id)
result = cursor.execute(command)
self.db.commit()
company_list = []
if not result:
return None
for company in result:
company_result = Company(company)
company_list.append(company_result)
return company_list
def getTop8CompaniesByUserID(self, user_id):
cursor = self.db.cursor()
command = """
SELECT * FROM company
WHERE user_id = {}
ORDER BY name
LIMIT 8
""".format(user_id)
result = cursor.execute(command)
self.db.commit()
company_list = []
if not result:
return None
for company in result:
company_result = Company(company)
company_list.append(company_result)
return company_list
def updateCompanyByID(self, fields):
cursor = self.db.cursor()
command = """
UPDATE company
SET name = ?,
description = ?,
industry = ?,
location = ?,
url = ?,
interviewers = ?,
contact_number = ?
WHERE user_id = ? and company_id = ?"""
cursor.execute(command, tuple(fields.values()))
self.db.commit()
# This method updates a company entry using details received from a job application.
def updateCompanyByApplication(self, fields):
cursor = self.db.cursor()
command = """
UPDATE company
SET name = ?,
description = ?,
industry = ?,
location = ?,
WHERE user_id = ? and company_id = ?"""
cursor.execute(command, tuple(fields.values()))
self.db.commit()
def deleteCompanyByID(self, company_id):
message = ""
try:
cursor = self.db.cursor()
command = "DELETE FROM company WHERE company_id = {}".format(
company_id)
cursor.execute(command)
self.db.commit()
message = "Company deleted successfully."
except sqlite3.Error as error:
message = "Company failed to delete. " + error
finally:
return message
def deleteAllCompaniesByUserID(self, user_id):
message = ""
try:
cursor = self.db.cursor()
command = "DELETE FROM company WHERE user_id = {}".format(user_id)
cursor.execute(command)
self.db.commit()
message = "Company deleted successfully."
except sqlite3.Error as error:
message = "Company failed to delete. " + error
finally:
return message