def get_jobs_with_companies(self, applicant_email_id):
jobs = list()
self.cursor.execute(
'''
SELECT * FROM company INNER JOIN job WHERE company.email_id = job.company_email_id
AND job.job_id NOT IN (
SELECT application.job_id FROM application WHERE applicant_email_id = ?
)
''', (applicant_email_id, ))
result = self.cursor.fetchall()
if result is None:
return None
else:
for row in result:
company = Company()
job = Job()
company.email_id = row[0]
company.name = row[1]
company.location = row[2]
company.website = row[3]
company.description = row[4]
job.job_id = row[6]
job.location = row[7]
job.requirements = row[8]
job.company_email_id = row[9]
jobs.append({'company': company, 'job': job})
return jobs
def get_by_email_id(self, email_id):
company = Company()
self.cursor.execute('''SELECT * FROM company WHERE email_id = ?''',
(email_id, ))
result = self.cursor.fetchone()
if result is None:
return None
else:
company.email_id = result[0]
company.name = result[1]
company.location = result[2]
company.website = result[3]
company.description = result[4]
company.password = result[5]
return company
def update(self, company_details):
company = Company()
company_details['where_email_id'] = company_details['email_id']
company_tuple = tuple(values
for keys, values in company_details.items())
self.connection.execute(
'''UPDATE company SET email_id = ?, password = ?, name = ?, location = ?, website = ?, description = ? WHERE email_id = ?''',
company_tuple)
company.email_id = company_details['email_id']
company.password = company_details['password']
company.name = company_details['name']
company.location = company_details['location']
company.website = company_details['website']
company.description = company_details['description']
self.connection.commit()
return company
def create(self, company_details):
company = Company()
company.email_id = company_details['email_id']
company.password = company_details['password']
company.name = company_details['name']
company.location = company_details['location']
company.website = company_details['website']
company.description = company_details['description']
company_tuple = tuple(values
for keys, values in company_details.items())
self.connection.execute(
'''INSERT INTO company( email_id, password, name, location, website, description )VALUES (?, ?, ?, ?, ?, ?);''',
company_tuple)
self.connection.commit()
return company
