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
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