def get_all_data(): query = """select first_name "First Name", last_name "Last Name", fiscal_year_end_date "Fiscal Year End Date", base_annualized_salary "Base Annualized Salary", a.title "Authority Name", ds.title "Title", dp.title "Group" from Person p join Authority a on a.id = p.authority_id join Designation ds on ds.id = p.designation_id join Department dp on dp.id = p.department_id;""" db_conn = db_helper.create_db_connection() data = db_helper.fetch_data(db_conn, query) db_conn.close() return data
def get_foreign_id(db_conn, table_name, title): if not title: return None query = """SELECT id FROM {1} WHERE title = "{0}";""".format(title, table_name) data = db_helper.fetch_data(db_conn, query) if data: return data[0][0] else: insert_query = """INSERT INTO {1} (title) VALUES ("{0}");""".format(title, table_name) db_helper.execute_db_command(db_conn, insert_query) db_conn.commit() return get_foreign_id(db_conn, table_name, title)
def populate_incremental_data(offset=1000): query = """SELECT COUNT(id) FROM Person;""" db_conn = db_helper.create_db_connection() data = db_helper.fetch_data(db_conn, query) if data: limit = data[0][0] if not limit: limit = 1 # populate_data(endpoint=API_ENDPOINT, limit=limit, db_conn=db_conn) dir_files = len(os.listdir('data')) limit += dir_files * offset populate_data_to_file(endpoint=API_ENDPOINT, limit=limit, db_conn=db_conn, offset=offset) db_conn.close()
def department_num_employees(limit=10): query = """SELECT d.title, COUNT(p.id) as num_employees FROM Person p JOIN Department d ON d.id = p.department_id GROUP BY d.title ORDER BY num_employees DESC LIMIT {limit};""".format(limit=limit) db_conn = db_helper.create_db_connection() data = db_helper.fetch_data(db_conn, query) db_conn.close() return data
def department_avg_salary(limit=10): query = """SELECT d.title, (AVG(base_annualized_salary)) as avg_salary FROM Person p JOIN Department d ON d.id = p.department_id GROUP BY d.title ORDER BY d.title LIMIT {limit};""".format(limit=limit) db_conn = db_helper.create_db_connection() data = db_helper.fetch_data(db_conn, query) db_conn.close() return data
def authority_avg_salary(limit=10): query = """SELECT a.title, (AVG(base_annualized_salary)) as avg_salary FROM Person p JOIN Authority a ON a.id = p.authority_id GROUP BY p.authority_id ORDER BY avg_salary DESC LIMIT {limit};""".format(limit=limit) db_conn = db_helper.create_db_connection() data = db_helper.fetch_data(db_conn, query) db_conn.close() return data
def department_employees_full_part_time(limit=10): query = """SELECT d.title, SUM(case when pay_type='FT' then 1 else 0 END) as full_time, SUM(case when pay_type='PT' then 1 else 0 END) as part_time FROM Person p JOIN Department d ON d.id = p.department_id GROUP BY d.title ORDER BY d.title LIMIT {limit};""".format(limit=limit) db_conn = db_helper.create_db_connection() data = db_helper.fetch_data(db_conn, query) db_conn.close() return data
def department_pays_private_or_public(limit=10): query = """SELECT d.title AS max_department, CASE WHEN paid_by_another_entity=0 THEN COUNT(p.id) END AS private_pay, CASE WHEN paid_by_another_entity=1 THEN COUNT(p.id) END AS public_pay FROM Person p JOIN Department d ON d.id = p.department_id GROUP BY d.title ORDER BY d.title LIMIT {limit};""".format(limit=limit) db_conn = db_helper.create_db_connection() data = db_helper.fetch_data(db_conn, query) db_conn.close() return data
def authority_total_employees(limit=10, year=2108, sort_by='ASC'): query = """SELECT a.title, COUNT(p.id) AS num_employees FROM Person p JOIN Authority a ON a.id = p.authority_id WHERE strftime ('%Y', fiscal_year_end_date) = '2018' GROUP BY a.title ORDER BY num_employees {sort_by} LIMIT {limit};""".format(limit=limit, year=year, sort_by=sort_by) db_conn = db_helper.create_db_connection() data = db_helper.fetch_data(db_conn, query) db_conn.close() return data
def authority_total_compensation(limit=10, year=2108): query = """SELECT a.title, sum(p.total_compensation) AS total_compensation FROM Person p JOIN Authority a ON a.id = p.authority_id WHERE strftime ('%Y', fiscal_year_end_date) = '{year}' GROUP BY a.title ORDER BY total_compensation DESC LIMIT {limit};""".format(limit=limit, year=year) db_conn = db_helper.create_db_connection() data = db_helper.fetch_data(db_conn, query) db_conn.close() return data
def authority_employee_count_over_years(limit=10): query = """SELECT a.title, sum(case when strftime('%Y', fiscal_year_end_date) = '2016' then 1 else 0 end) as year_2016, sum(case when strftime('%Y', fiscal_year_end_date) = '2017' then 1 else 0 end) as year_2017, sum(case when strftime('%Y', fiscal_year_end_date) = '2018' then 1 else 0 end) as year_2018 FROM Person p JOIN Authority a ON a.id = p.authority_id GROUP BY p.authority_id ORDER BY year_2016 DESC, year_2017 DESC, year_2018 DESC LIMIT {limit};""".format(limit=limit) db_conn = db_helper.create_db_connection() data = db_helper.fetch_data(db_conn, query) db_conn.close() return data
def designation_pays_most_and_least(limit=10): query = """SELECT lower(d.title) AS designation, MAX(base_annualized_salary) AS max_pay, MIN(base_annualized_salary) AS min_pay FROM Person p JOIN Designation d ON d.id = p.designation_id WHERE base_annualized_salary >= 1 GROUP BY designation HAVING max_pay!=min_pay ORDER BY max_pay DESC LIMIT {limit};""".format(limit=limit) db_conn = db_helper.create_db_connection() data = db_helper.fetch_data(db_conn, query) db_conn.close() return data
def department_pays_most_and_least(limit=10): query = """SELECT d.title AS max_department, MAX(base_annualized_salary) AS max_pay, MIN(base_annualized_salary) AS min_pay FROM Person p JOIN Department d ON d.id = p.department_id WHERE base_annualized_salary >= 1 GROUP BY d.title ORDER BY max_pay DESC LIMIT {limit};""".format(limit=limit) db_conn = db_helper.create_db_connection() data = db_helper.fetch_data(db_conn, query) db_conn.close() return data
def authority_pays_most_and_least(limit=10): query = """SELECT lower(a.title) AS authority, MAX(base_annualized_salary) AS max_pay, MIN(base_annualized_salary) AS min_pay FROM Person p JOIN Authority a ON a.id = p.authority_id WHERE base_annualized_salary >= 1 GROUP BY a.id HAVING max_pay!=min_pay ORDER BY max_pay DESC LIMIT {limit};""".format(limit=limit) db_conn = db_helper.create_db_connection() data = db_helper.fetch_data(db_conn, query) db_conn.close() return data
def department_avg_salary_trend(limit=10): query = """SELECT d.title, AVG(case when strftime('%Y', fiscal_year_end_date) = '2011' then base_annualized_salary else NULL end) as year_2011, AVG(case when strftime('%Y', fiscal_year_end_date) = '2012' then base_annualized_salary else NULL end) as year_2012, AVG(case when strftime('%Y', fiscal_year_end_date) = '2013' then base_annualized_salary else NULL end) as year_2013, AVG(case when strftime('%Y', fiscal_year_end_date) = '2014' then base_annualized_salary else NULL end) as year_2014, AVG(case when strftime('%Y', fiscal_year_end_date) = '2015' then base_annualized_salary else NULL end) as year_2015, AVG(case when strftime('%Y', fiscal_year_end_date) = '2016' then base_annualized_salary else NULL end) as year_2016, AVG(case when strftime('%Y', fiscal_year_end_date) = '2017' then base_annualized_salary else NULL end) as year_2017, AVG(case when strftime('%Y', fiscal_year_end_date) = '2018' then base_annualized_salary else NULL end) as year_2018 FROM Person p JOIN Department d ON d.id = p.department_id GROUP BY d.title ORDER BY d.title LIMIT {limit};""".format(limit=limit) db_conn = db_helper.create_db_connection() data = db_helper.fetch_data(db_conn, query) db_conn.close() return data
def insert_person_record(db_conn, data, authority_id, department_id, designation_id): first_name = data['first_name'] if 'first_name' in data else None last_name = data['last_name'] if 'last_name' in data else None if not (first_name and last_name and authority_id and department_id and designation_id): return None query = """SELECT id FROM Person WHERE first_name="{first_name}" AND last_name="{last_name}" AND authority_id="{authority_id}" AND department_id="{department_id}" AND designation_id="{designation_id}";""".format(first_name=first_name, last_name=last_name, authority_id=authority_id, department_id=department_id, designation_id=designation_id) person_data = db_helper.fetch_data(db_conn, query) if not person_data: fiscal_year_end_date = None if data['fiscal_year_end_date']: try: fiscal_year_end_date = datetime.strptime(data['fiscal_year_end_date'], '%Y-%m-%dT%H:%M:%S.%f') except: fiscal_year_end_date = datetime.strptime(data['fiscal_year_end_date'], '%m/%d/%Y') fiscal_year_end_date = fiscal_year_end_date.strftime('%Y-%m-%d %H:%M:%S') total_compensation = data['total_compensation'] if 'total_compensation' in data else None other_compensation = data['other_compensation'] if 'other_compensation' in data else None extra_pay = data['extra_pay'] if 'extra_pay' in data else None performance_bonus = data['performance_bonus'] if 'performance_bonus' in data else None overtime_paid = data['overtime_paid'] if 'overtime_paid' in data else None actual_salary_paid = data['actual_salary_paid'] if 'actual_salary_paid' in data else None base_annualized_salary = data['base_annualized_salary'] if 'base_annualized_salary' in data else None pay_type = data['pay_type'] if 'pay_type' in data else None paid_by_another_entity = data['paid_by_another_entity'] if 'paid_by_another_entity' in data else None if paid_by_another_entity == "Y": paid_by_another_entity = 1 else: paid_by_another_entity = 0 exempt_indicator = data['exempt_indicator'] if 'exempt_indicator' in data else None if exempt_indicator == "Y": exempt_indicator = 1 else: exempt_indicator = 0 insert_query = """INSERT INTO Person (first_name, last_name, authority_id, department_id, designation_id, paid_by_another_entity, total_compensation, other_compensation, extra_pay, performance_bonus, overtime_paid, actual_salary_paid, base_annualized_salary, exempt_indicator, pay_type, fiscal_year_end_date) VALUES ("{first_name}", "{last_name}", {authority_id}, {department_id}, {designation_id}, "{paid_by_another_entity}", "{total_compensation}", "{other_compensation}", "{extra_pay}", "{performance_bonus}", "{overtime_paid}", "{actual_salary_paid}", "{base_annualized_salary}", "{exempt_indicator}", "{pay_type}", "{fiscal_year_end_date}");""".format(first_name=first_name, last_name=last_name, authority_id=authority_id, department_id=department_id, designation_id=designation_id, paid_by_another_entity=paid_by_another_entity, total_compensation=total_compensation, other_compensation=other_compensation, extra_pay=extra_pay, performance_bonus=performance_bonus, overtime_paid=overtime_paid, actual_salary_paid=actual_salary_paid, base_annualized_salary=base_annualized_salary, exempt_indicator=exempt_indicator, pay_type=pay_type, fiscal_year_end_date=fiscal_year_end_date) db_helper.execute_db_command(db_conn, insert_query) db_conn.commit()