def view_specific_user_projects(conn, **kwargs):
"""
a function to fetch the records from the user_project_wage table in the database based on the role of the
logged in user.
:param conn: a sqlite db connection object
:param kwargs: a keyword argument variable in case of update, assignment_approval, wage_approval or pending_requests
:return: a list of user project associations.
"""
action = kwargs.get('action', None)
if not action == 'assignment_approval' and not action == 'wage_approval' and not action == 'pending_requests':
sql_query_user = '''SELECT * FROM gpm_mem_rel WHERE gpm_user_id=?'''
sql_params_user = (Login.logged_in_user['user_id'],)
cursor_obj_user = Core.query_runner(conn, sql_query_user, sql_params_user)
user_ids = [user['mem_user_id'] for user in cursor_obj_user.fetchall()]
cursor_obj_user.close()
else:
user_ids = kwargs.get('user_ids', [])
if user_ids:
if len(user_ids) > 1:
sql_query = f'''SELECT * FROM user_project_wage WHERE user_id IN {str(tuple(user_ids))}'''
else:
sql_query = f'''SELECT * FROM user_project_wage WHERE user_id={user_ids[0]}'''
if not action == 'update':
sql_query = sql_query + ''' AND is_deleted=False'''
if action == 'pending_requests':
sql_query = sql_query + ''' AND is_wage_approved=False OR is_bdo_approved=False'''
elif action == 'wage_approval':
sql_query = sql_query + ''' AND is_wage_approved=False'''
elif action == 'assignment_approval':
sql_query = sql_query + ''' AND is_bdo_approved=False'''
cursor_obj = Core.query_runner(conn, sql_query, sql_params=None)
user_projects = cursor_obj.fetchall()
cursor_obj.close()
return user_projects
def view_specific_projects(conn, **kwargs):
"""
a function to fetch the records from the project table in the database based on the role of the
logged in user.
:param conn: a sqlite db connection object
:param kwargs: a keyword argument variable in case of update
:return: a list of projects.
"""
action = kwargs.get('action', None)
sql_params = None
if Login.logged_in_user['role'] == 'bdo':
sql_params = (Login.logged_in_user['user_id'],)
elif Login.logged_in_user['role'] == 'gpm':
sql_query_user = '''SELECT * FROM bdo_gpm_rel WHERE gpm_user_id=?'''
sql_params_user = (Login.logged_in_user['user_id'],)
cursor_obj_user = Core.query_runner(conn, sql_query_user, sql_params_user)
user = cursor_obj_user.fetchone()
cursor_obj_user.close()
sql_params = (user['bdo_user_id'], )
if sql_params is not None:
if action == 'update':
sql_query = '''SELECT * FROM project WHERE created_by=?'''
else:
sql_query = '''SELECT * FROM project WHERE created_by=? AND is_deleted=False'''
cursor_obj = Core.query_runner(conn, sql_query, sql_params)
projects = cursor_obj.fetchall()
cursor_obj.close()
return projects
def add_user(self, conn, gpm_user_id=None):
"""
function to insert a record in the user table, thereby adding a new user
:param self: reference to the current object reference
:param conn: a sqlite db connection object
:param gpm_user_id: gpm_user_id in case the new user is member
:return: new user_id of the record created
"""
try:
sql_query = '''INSERT INTO user(username, password, email, first_name, last_name, age, gender, contact, area,
pin_code, role, created_at, updated_at, is_deleted) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)'''
sql_params = (
self.username,
self.password,
self.email,
self.first_name,
self.last_name,
self.age,
self.gender,
self.contact,
self.area,
self.pin_code,
self.role,
self.created_at,
self.updated_at,
self.is_deleted,
)
cursor_obj = Core.query_runner(conn, sql_query, sql_params)
user_id = cursor_obj.lastrowid
cursor_obj.close()
sql_query_rel = str()
sql_params_rel = tuple()
if Login.logged_in_user['role'] == 'bdo' and self.role == 'gpm':
sql_query_rel = '''INSERT INTO bdo_gpm_rel(bdo_user_id, gpm_user_id) VALUES(?,?)'''
sql_params_rel = (
Login.logged_in_user['user_id'],
user_id,
)
elif Login.logged_in_user[
'role'] == 'bdo' and self.role == 'member':
sql_query_rel = '''INSERT INTO gpm_mem_rel(gpm_user_id, mem_user_id) VALUES(?,?)'''
sql_params_rel = (
gpm_user_id,
user_id,
)
cursor_obj_rel = Core.query_runner(conn, sql_query_rel,
sql_params_rel)
cursor_obj_rel.close()
return user_id
except IntegrityError:
print(IntegrityError)
def view_specific_users(conn, **kwargs):
"""
a function to fetch the records from the user table in the database based on the role of the
logged in user. user are the reporting user/heads of the logged in user
:param conn: a sqlite db connection object
:param kwargs: a keyword argument variable in case of update
:return: a list of users.
"""
action = kwargs.get('action', None)
sql_query = str()
if Login.logged_in_user['role'] == 'bdo':
sql_query = '''SELECT * FROM bdo_gpm_rel WHERE bdo_user_id=?'''
elif Login.logged_in_user['role'] == 'gpm':
sql_query = '''SELECT * FROM gpm_mem_rel WHERE gpm_user_id=?'''
sql_params = (Login.logged_in_user['user_id'], )
cursor_obj = Core.query_runner(conn, sql_query, sql_params)
user_ids = list()
if Login.logged_in_user['role'] == 'bdo':
gpm_user_ids = [
user['gpm_user_id'] for user in cursor_obj.fetchall()
]
sql_query_mem = f'''SELECT * FROM gpm_mem_rel WHERE gpm_user_id IN {str(tuple(gpm_user_ids))}'''
cursor_obj_mem = Core.query_runner(conn,
sql_query_mem,
sql_params=None)
mem_user_ids = [
user['mem_user_id'] for user in cursor_obj_mem.fetchall()
]
cursor_obj_mem.close()
user_ids = list(set().union(gpm_user_ids, mem_user_ids))
elif Login.logged_in_user['role'] == 'gpm':
user_ids = [user['mem_user_id'] for user in cursor_obj.fetchall()]
cursor_obj.close()
if user_ids:
if len(user_ids) > 1:
sql_query_user = f'''SELECT user_id, username, role FROM user WHERE user_id IN {str(tuple(user_ids))}'''
else:
sql_query_user = f'''SELECT user_id, username, role FROM user WHERE user_id={user_ids[0]}'''
if not action == 'update':
sql_query_user = sql_query_user + ''' AND is_deleted=False'''
cursor_obj_user = Core.query_runner(conn,
sql_query_user,
sql_params=None)
users = cursor_obj_user.fetchall()
cursor_obj_user.close()
return users
else:
return None
def update_user(conn, username, **kwargs):
"""
a function to update the details of the user
:param conn: a sqlite db connection object
:param username: username of the user to be updated
:param kwargs: a list of keyword arguments with field name and field values to be updated
:return: number of row updated
"""
if username is not None:
updated_at = datetime.now()
update_param = str()
new_values = list()
for key, value in kwargs.items():
update_param = update_param + key + "=?,"
new_values.append(value)
update_param = update_param + "updated_at=?"
sql_query = f'''UPDATE user SET ''' + update_param + ''' WHERE username=?'''
sql_params = tuple(new_values) + (
str(updated_at),
str(username),
)
cursor_obj = Core.query_runner(conn, sql_query, sql_params)
update_row_count = cursor_obj.rowcount
cursor_obj.close()
return update_row_count
def issue_complaints(self, conn):
"""
function to insert a record in the member_complaints table, thereby filing a new complaint
:param self: reference to the current object reference
:param conn: a sqlite db connection object
:return: new complaint_id of the record created
"""
try:
sql_query = '''INSERT INTO member_complaints(user_id, bdo_user_id, gpm_user_id, complain_subject, complain_description, bdo_remarks, gpm_remarks, created_at, updated_at, is_resolved) VALUES(?,?,?,?,?,?,?,?,?,?)'''
sql_params = (
self.user_id,
self.bdo_user_id,
self.gpm_user_id,
self.complain_subject,
self.complain_description,
self.bdo_remarks,
self.gpm_remarks,
self.created_at,
self.updated_at,
self.is_resolved,
)
cursor_obj = Core.query_runner(conn, sql_query, sql_params)
member_complaints_id = cursor_obj.lastrowid
cursor_obj.close()
return member_complaints_id
except IntegrityError:
print(IntegrityError)
def get_members_bdo_gpm(conn):
"""
a function to fetch the user reporting heads of the logged in member
:param conn: a sqlite db connection object
:return: a dictionary of reporting heads of logged in member
"""
sql_query_gpm = '''SELECT * FROM gpm_mem_rel WHERE mem_user_id=?'''
sql_params_gpm = (Login.logged_in_user['user_id'], )
cursor_obj_gpm = Core.query_runner(conn, sql_query_gpm, sql_params_gpm)
mem_gpm_rel = cursor_obj_gpm.fetchone()
cursor_obj_gpm.close()
sql_query_bdo = '''SELECT * FROM bdo_gpm_rel WHERE gpm_user_id=?'''
sql_params_bdo = (mem_gpm_rel['gpm_user_id'], )
cursor_obj_bdo = Core.query_runner(conn, sql_query_bdo, sql_params_bdo)
mem_bdo_rel = cursor_obj_bdo.fetchone()
cursor_obj_bdo.close()
mem_reporting_heads = dict()
mem_reporting_heads['user_id'] = Login.logged_in_user['user_id']
mem_reporting_heads['bdo_user_id'] = mem_bdo_rel['bdo_user_id']
mem_reporting_heads['gpm_user_id'] = mem_gpm_rel['gpm_user_id']
return mem_reporting_heads
def view_user_details(conn, user_id):
"""
a function to fetch the details of the user from the user table in the database based on the user_id.
:param conn: a sqlite db connection object
:param user_id: user_id of the specific user to fetch from db
:return: details of a user record of specific user_id
"""
if user_id is not None:
sql_query = '''SELECT * FROM user WHERE user_id=?'''
sql_params = (user_id, )
cursor_obj = Core.query_runner(conn, sql_query, sql_params)
user = cursor_obj.fetchone()
cursor_obj.close()
return user
def view_project_details(conn, project_id):
"""
a function to fetch the details of the project from the project table in the database based on the project_id.
:param conn: a sqlite db connection object
:param project_id: project_id of the specific project to fetch from db
:return: details of a project record of specific project_id
"""
if project_id is not None:
sql_query = '''SELECT * FROM project WHERE project_id=?'''
sql_params = (project_id, )
cursor_obj = Core.query_runner(conn, sql_query, sql_params)
project = cursor_obj.fetchone()
cursor_obj.close()
return project
def delete_project(conn, project_id):
"""
a function to set the is_deleted flag to True, thereby soft deleting the project from db
:param conn: a sqlite db connection object
:param project_id: project_id to be soft deleted
:return: number of soft deleted row
"""
if project_id:
updated_at = datetime.now()
sql_query = '''UPDATE project SET is_deleted=True,updated_at=? WHERE project_id=?'''
sql_params = (updated_at, project_id, )
cursor_obj = Core.query_runner(conn, sql_query, sql_params)
update_row_count = cursor_obj.rowcount
cursor_obj.close()
return update_row_count
def view_user_projects(conn, user_id):
"""
a function to fetch the list of the projects associated with the user from the user_project_wage table in the
database based on the user_id.
:param conn: a sqlite db connection object
:param user_id: user_id of the specific user to fetch from db
:return: list of the details of projects associated with user with specific user_id
"""
if user_id is not None:
sql_query = '''SELECT * FROM user_project_wage WHERE user_id=? AND is_deleted=False'''
sql_params = (user_id, )
cursor_obj = Core.query_runner(conn, sql_query, sql_params)
user_projects = cursor_obj.fetchall()
cursor_obj.close()
return user_projects
def delete_user_project(conn, user_id, project_id):
"""
a function to set the is_deleted flag to True, thereby soft deleting the user project association from db
:param conn: a sqlite db connection object
:param user_id: user_id of the specific user to fetch from db
:param project_id: project_id of project associated with user_id
:return: number of soft deleted row
"""
if project_id is not None and user_id is not None:
sql_query = '''UPDATE user_project_wage SET is_deleted=True WHERE user_id=? AND project_id=?'''
sql_params = (user_id, project_id, )
cursor_obj = Core.query_runner(conn, sql_query, sql_params)
update_row_count = cursor_obj.rowcount
cursor_obj.close()
return update_row_count
def view_user_project_details(conn, user_id, project_id):
"""
a function to fetch the details of the projects associated with the user from the user_project_wage table in the
database based on the user_id and project_id.
:param conn: a sqlite db connection object
:param user_id: user_id of the specific user to fetch from db
:param project_id: project_id of project associated with user_id
:return: details of a projects associated with user
"""
if project_id is not None and user_id is not None:
sql_query = '''SELECT * FROM user_project_wage WHERE user_id=? AND project_id=?'''
sql_params = (user_id, project_id,)
cursor_obj = Core.query_runner(conn, sql_query, sql_params)
user_project = cursor_obj.fetchone()
cursor_obj.close()
return user_project
def assign_project(self, conn):
"""
function to insert a record in the user_project_wage table, thereby adding a new assignment of user to a project
:param self: reference to the current object reference
:param conn: a sqlite db connection object
:return: new user_project_wage_id of the record created
"""
try:
sql_query = '''INSERT INTO user_project_wage(user_id, project_id, no_of_days_worked, wage, attendance, is_bdo_approved, is_wage_approved, is_job_card_issued, is_deleted) VALUES(?,?,?,?,?,?,?,?,?)'''
sql_params = (self.user_id, self.project_id, self.no_of_days_worked, self.wage, self.attendance, self.is_bdo_approved, self.is_wage_approved, self.is_job_card_issued, self.is_deleted, )
cursor_obj = Core.query_runner(conn, sql_query, sql_params)
user_project_wage_id = cursor_obj.lastrowid
cursor_obj.close()
return user_project_wage_id
except IntegrityError:
print(IntegrityError)
def delete_user(conn, username):
"""
a function to set the is_deleted flag to True, thereby soft deleting the user from db
:param conn: a sqlite db connection object
:param username: username of the user to be soft deleted
:return: number of soft deleted row
"""
if username:
updated_at = datetime.now()
sql_query = '''UPDATE user SET is_deleted=True,updated_at=? WHERE username=?'''
sql_params = (
updated_at,
username,
)
cursor_obj = Core.query_runner(conn, sql_query, sql_params)
update_row_count = cursor_obj.rowcount
cursor_obj.close()
return update_row_count
def change_field_status(conn, user_id, project_id, **kwargs):
"""
a function to set the alternate boolean flag field to True/False, thereby changing approval status.
wage approval etc.
:param conn: a sqlite db connection object
:param user_id: user id of the user
:param project_id: project id of the project user is associated with
:param kwargs: a list of keyword arguments with field name and field values to be updated
:return: number of updated row
"""
field_name = kwargs.get('field_name', None)
if project_id is not None and user_id is not None:
sql_query = '''UPDATE user_project_wage SET ''' + field_name + '''=True WHERE user_id=? AND project_id=?'''
sql_params = (user_id, project_id,)
cursor_obj = Core.query_runner(conn, sql_query, sql_params)
update_row_count = cursor_obj.rowcount
cursor_obj.close()
return update_row_count
def add_project(self, conn):
"""
function to insert a record in the project table, thereby adding a new project
:param self: reference to the current object reference
:param conn: a sqlite db connection object
:return: new project_id of the record created
"""
try:
sql_query = '''INSERT INTO project(project_name, project_type, area, total_required_member, cost_estimate,
start_date, end_date_estimate, created_by, created_at, updated_at, is_deleted)
VALUES(?,?,?,?,?,?,?,?,?,?,?)'''
sql_params = (self.project_name, self.project_type, self.area, self.total_required_member,
self.cost_estimate, self.start_date, self.end_date_estimate, self.created_by, self.created_at, self.updated_at,
self.is_deleted, )
cursor_obj = Core.query_runner(conn, sql_query, sql_params)
project_id = cursor_obj.lastrowid
cursor_obj.close()
return project_id
except IntegrityError:
print(IntegrityError)
def login(conn, username, password):
"""
a function that validates the login credentials i.e. username and password from the user table in the database
:param conn: a sqlite db connection object
:param username: username of the login user
:param password: password to validate the user authenticity
:return: a dictionary that contains logged in user details with **role** in the application
"""
sql_query = '''SELECT * FROM user WHERE username=? AND password=?'''
sql_params = (username, password, )
cursor_obj = Core.query_runner(conn, sql_query, sql_params)
user = cursor_obj.fetchone()
cursor_obj.close()
if user is not None:
if user['is_deleted'] == 0:
Login.logged_in_user = dict(user)
return dict(user)
else:
user = '******'
return user
else:
return user
def update_user_project(conn, user_id, project_id, **kwargs):
"""
a function to update the details of the user
:param conn: a sqlite db connection object
:param user_id: user_id of the specific user to fetch from db
:param project_id: project_id of project associated with user_id
:param kwargs: a list of keyword arguments with field name and field values to be updated
:return: number of row updated
"""
if project_id is not None and user_id is not None:
update_param = str()
new_values = list()
for key, value in kwargs.items():
update_param = update_param + key + "=?,"
new_values.append(value)
update_param = update_param[:-1]
sql_query = f'''UPDATE user_project_wage SET ''' + update_param + ''' WHERE user_id=? AND project_id=?'''
sql_params = tuple(new_values) + (user_id, project_id,)
print(sql_query)
print(sql_params)
cursor_obj = Core.query_runner(conn, sql_query, sql_params)
update_row_count = cursor_obj.rowcount
cursor_obj.close()
return update_row_count
def view_specific_complaints(conn, **kwargs):
"""
a function to fetch the records from the member_complaints table in the database based on the role of the
logged in user.
:param conn: a sqlite db connection object
:param kwargs: a keyword argument variable in case of update
:return: a list of complaints.
"""
action = kwargs.get('action', None)
sql_query_complaints = str()
if Login.logged_in_user['role'] == 'bdo':
sql_query_complaints = '''SELECT * FROM member_complaints WHERE bdo_user_id=?'''
elif Login.logged_in_user['role'] == 'gpm':
sql_query_complaints = '''SELECT * FROM member_complaints WHERE gpm_user_id=?'''
elif Login.logged_in_user['role'] == 'member':
sql_query_complaints = f'''SELECT * FROM member_complaints WHERE user_id=?'''
sql_params_complaints = (Login.logged_in_user['user_id'], )
if not action == 'update':
sql_query_complaints = sql_query_complaints + ''' AND is_resolved=False'''
cursor_obj_complaints = Core.query_runner(conn, sql_query_complaints,
sql_params_complaints)
complaints = cursor_obj_complaints.fetchall()
cursor_obj_complaints.close()
return complaints
def test_query_runner(self, mock_cursor):
mock_cursor.cursor().execute.return_value = sqlite3.Cursor
cursor_obj = Core.query_runner(MockData.get_db_connection(),
MockData.get_sql_query(),
MockData.get_sql_params())
assert isinstance(cursor_obj, (sqlite3.Cursor, ))