def insert_with_filter(self, employees_df, conn, exist_ids, client):
try:
# first - insert new employees
is_new_employee = ~employees_df['employee_id'].isin(exist_ids)
new_employees_df = employees_df[is_new_employee]
if not new_employees_df.empty:
print("new employees found")
result = self.to_sql_with_pd(new_employees_df, conn)
if not result.success: # insertion failed
conn.close()
return result
# second - delete past employees
past_employee_ids = [
emp_id for emp_id in exist_ids
if emp_id not in set(employees_df['employee_id'])
]
if past_employee_ids:
print("past employees found")
return self.delete_by_ids(conn, past_employee_ids, client)
else:
conn.close()
return Result(True, "")
except Error as e:
conn.close()
return Result(False, str(e))
def to_sql_with_pd(employees_df, conn):
try:
employees_df.to_sql('employee',
conn,
if_exists='append',
index=False)
return Result(True, "")
except Error as e:
return Result(False, str(e))
def delete_by_ids(conn, list_ids, client):
try:
c = conn.cursor()
query = '''DELETE FROM employee WHERE client=? AND employee_id IN ({})'''.format(
", ".join("?" * len(list_ids)))
c.execute(query, [client] + list_ids)
conn.commit()
conn.close()
return Result(True, "")
except Error as e:
return Result(False, str(e))
def from_csv_to_df(self, field_names):
if not self.file_path.endswith(".csv"):
return Result(False, "CSV files only."), None
try:
employees_df = pd.read_csv(self.file_path,
usecols=field_names,
parse_dates=[field_names.date_of_birth
])[list(field_names)]
self.change_columns_names(employees_df)
# Using DataFrame.insert() to add a column for client name
employees_df.insert(
1, "client",
[self.client_name for _ in range(employees_df.shape[0])], True)
return Result(True, ""), employees_df
except Exception as e:
return Result(False, str(e)), None
def insert_employees(self, client_name, f_path, first_name_field, last_name_field, employee_id_field,
date_of_birth_field):
field_names = FieldName(employee_id=employee_id_field,
first_name=first_name_field,
last_name=last_name_field,
date_of_birth=date_of_birth_field)
if not client_name:
return Result(False, "Client name must be filled.")
if None in field_names:
return Result(False,
"File should contain 4 explicit columns: first name, last name, date of birth, employee id "
"and start with a header.")
file_handler = FileHandler(f_path, client_name)
result, employees_df = file_handler.from_csv_to_df(field_names)
if not result.success:
return result
return self.db_handler.insert_employees(employees_df, client_name)
def check_employee_eligible(self, employee_data):
conn = self.create_connection()
if conn:
try:
c = conn.cursor()
c.execute(
'''SELECT * FROM employee WHERE employee_id=? AND client=? AND first_name=? AND last_name=?''',
(
employee_data['employee_id'],
employee_data['client'],
employee_data['first_name'],
employee_data['last_name'],
))
exists = c.fetchall()
print(exists)
if not exists:
return Result(
False,
"This employee does not exist_ids in the system.")
return Result(True, "")
except Error as e:
return Result(False, str(e))
return Result(False, "Connection Failed.")
def insert_employees(self, employees_df, client):
conn = self.create_connection()
if conn:
# first - get all ids for this client
c = conn.cursor()
c.execute('''SELECT employee_id FROM employee WHERE client=?''',
(client, ))
exist_ids = [item[0] for item in c.fetchall()]
c.close()
if not exist_ids:
print("new client")
return self.to_sql_with_pd(employees_df, conn)
else:
print("existing client")
return self.insert_with_filter(employees_df, conn, exist_ids,
client)
return Result(False, "Connection Failed.")
def check_employee_eligible(self, employee_data):
if self.__is_data_valid(employee_data):
return self.db_handler.check_employee_eligible(employee_data)
error = "Employee data should contain these five fields: first_name, last_name, date_of_birth, " \
"employee_id and client."
return Result(False, error)