def pending(line_manager_id): query = "select * from leave_history lh where line_manager = '%s' and status='pending'" % line_manager_id return db.get_many(connection, query)
def leave_history(username): query = "select * from leave_history where staff_id='" + str( username) + "'" return db.get_many(connection, query)
def dr_names(user_id): query = """select concat(first_name, " " ,middle_name, " " ,last_name) name ,staff_number id from personal_data pd where line_manager ='""" + str( user_id) + "'" return db.get_many(connection, query)
def all_leave_types(): query = "select * from leave_types" return db.get_many(connection, query)
def get_drs(lm): query = " select * from personal_data where line_manager = %s" % lm return db.get_many(connection, query)
def line_manager_approved(line_manager_id): query = "select * from leave_history lh where line_manager = '%s' and status='approved' and year(start_date) = year(now())" % line_manager_id return db.get_many(connection, query)
def all_staff(): query = "select p1.*,concat(p2.first_name ,' ',p2.middle_name , ' ', p2.last_name ) line_manager_name,p2.email line_manager_email from personal_data p1 left join personal_data p2 on p1.line_manager = p2.id " return db.get_many(connection, query)
def get_office(): query = "select * from station" return db.get_many(connection, query)
def get_departments(): query = "select * from departments" return db.get_many(connection, query)
def all_data(username, level, gender): query = "with X as (select leave_type_id , count(status) total, start_date from history where status='approved' and Staff_id = '" + str( username ) + "' and year(start_date) = year(now()) group by leave_type_id ) select coalesce(x.total,0) total, a.*, b.* FROM role_leave_days a,leave_types b left join x on b.id=x.leave_type_id where a.leave_id=b.id and a.level='" + str( level) + "' and b.gender like '%" + str(gender) + "%'" return db.get_many(connection, query)
def role_leave(): query = "select lt.name, lt.gender, r.role_name, rld.duration, rld.carry_forward, rld.days_carry_forward, rld.id, rld.role_id from role_leave_days rld left join leave_types lt on rld.leave_id = lt.id left join `role` r on r.id = rld.role_id " return db.get_many(connection, query)
def role(): query = "select * from role" return db.get_many(connection, query)
def lmid(): query = "select * from personal_data where role in (2,3)" return db.get_many(connection, query)
def holiday(): query = "select * from holidays" return db.get_many(connection, query)
def approved_pending_info(staff_id, id): query = "select * from approved_pending_leave_view where Staff_id = '%s' and level ='%s'" % ( staff_id, id) print('my approved_pending_info is', query) return db.get_many(connection, query)
def staff_all(): query = "select * from personal_data" return db.get_many(connection, query)
def get_leave_types(gender, level): query = "select * from leave_days_types where gender like '%" + str( gender) + "%' and level = '" + str(level) + "'" print('my leave type days is', query) return db.get_many(connection, query)
def remaining(lm_id): query = "select * from approved_pending_leave_lm_view where line_manager =%s" % lm_id return db.get_many(connection, query)
def all_approved(): query = "select * from leave_history lh where status='approved' and year(start_date) = year(now())" return db.get_many(connection, query)
def get_leave_holidays(start_date, end_date): query = "select * from holidays where date between '%s' and '%s'" % ( start_date, end_date) return db.get_many(connection, query)