def test_perl_hours():
statement = (
"SELECT TO_NUMERIC(SUM(billable_hours)) as perl_total_hours from "\
"RESOURCE_CAPACITY.PNMAC.MVW_FORECASTANDACTUALCOMBINEDINFO "\
"WHERE redline_project = 'Privacy Escalation & Response Log (PERL)';")
perl_hours_logged = snowflake_utility.SnowflakeConnection().fetch(
statement)[0][0]
assert perl_hours_logged == 5, "PERL hours should match 5"
def test_no_duplicate_employees_in_table():
statement = (
"SELECT TO_NUMERIC(count(distinct employee_id)) from " \
" RESOURCE_CAPACITY.PNMAC.MVW_FORECASTANDACTUALCOMBINEDINFO " \
"WHERE week = 40 and year = 2018 and employee_network_login = '******';"
)
count_of_alice_hong = snowflake_utility.SnowflakeConnection().fetch(
statement)[0][0]
assert count_of_alice_hong == 1, "Alice Hong count should match 1"
def test_ooo_value_for_2017_meets_expectations():
statement = (
"SELECT YEAR, TO_NUMERIC(SUM(BILLABLE_HOURS)) as yearly_hours " \
"FROM RESOURCE_CAPACITY.PNMAC.MVW_FORECASTANDACTUALCOMBINEDINFO " \
"WHERE YEAR = 2017 AND EFFORT_ID = 'OOO' GROUP BY YEAR;" )
ooo_value_for_2017 = snowflake_utility.SnowflakeConnection().fetch(
statement)[0]
assert ooo_value_for_2017 == (
2017, 75396), "Response for 2017 OOO did not match the expected 75,396"
def test_no_duplicate_employee_ids():
statement = (
"select count(*) from (select "\
" employeeid, count(*) "\
" from DW_ORG.PNMAC.VW_ONE_ROW_PER_EMPLOYEE "\
" group by employeeid "\
" having count(*) > 1) ;" \
)
duplicates = snowflake_utility.SnowflakeConnection().fetch(statement)[0][0]
assert duplicates == 0, 'There should be no employees with duplicate ID values'
def test_no_duplicate_network_logins():
statement = (
" select count(*) from (select "\
" networklogin, count(*) "\
" from DW_ORG.PNMAC.VW_ONE_ROW_PER_EMPLOYEE "\
" group by networklogin "\
" having count(*) > 1); "\
)
duplicates = snowflake_utility.SnowflakeConnection().fetch(statement)[0][0]
assert duplicates == 0, 'There should be no employees with networkLoginId values'
def test_work_study_hours_match_timesheet_entries():
statement = (
"select count(*) FROM " \
" (select id, sum(hours) as hours from REDLINE.PNMAC.MVW_REDLINEWORKSTUDYWITHHOURS group by id) mvw " \
" FULL OUTER JOIN " \
" (select issue_id, sum(entry_hours) as hours from REDLINE.PNMAC.VW_TIMESHEETENTRIES group by issue_id) vw " \
" ON mvw.id = vw.issue_id " \
" WHERE round(vw.hours) <> round(mvw.hours); "\
)
mismatches = snowflake_utility.SnowflakeConnection().fetch(statement)[0][0]
assert mismatches == 0, "Should be no hourly mismatch between MVW_REDLINEWORKSTUDYWITHHOURS and VW_TIMESHEETENTRIES"
def test_to_make_sure_last_month_has_hours():
year_month = (datetime.datetime.now() +
datetime.timedelta(-30)).strftime('%Y-%m')
last_month = year_month + '-01'
statement = (
"select count(billable_hours) from RESOURCE_CAPACITY.PNMAC.MVW_FORECASTANDACTUALCOMBINEDINFO "\
" where month_of_week_start_date = '" + last_month +"'; "
)
billable_hours_last_month = snowflake_utility.SnowflakeConnection().fetch(
statement)[0][0]
assert billable_hours_last_month > 0, "Last month must have had >0 hours!"
def test_timesheet_entries_table_matches_view():
statement = (
" select count(*) " \
" FROM (select issue_id, sum(hours) as hours from REDLINE.PNMAC.TIME_ENTRIES " \
" where year(created_on) >= '2017' group by issue_id) tbl " \
" FULL OUTER JOIN " \
" (select issue_id, sum(entry_hours) as hours from REDLINE.PNMAC.VW_TIMESHEETENTRIES group by issue_id) vw " \
" ON tbl.issue_id = vw.issue_id " \
" WHERE round(tbl.hours) <> round(vw.hours); " \
)
mismatches = snowflake_utility.SnowflakeConnection().fetch(statement)[0][0]
assert mismatches == 0, "Should be no hourly mismatch between TIME_ENTRIES and VW_TIMESHEETENTRIES"
def test_old_billable_hours_flowing_through_correctly():
statement = (
" select abs(tbl.hours - vw.hours) from " \
" (select sum(billable_hours) as hours from " \
" RESOURCE_CAPACITY.PNMAC.BILLABLE_HOURS where year >= 2017) tbl" \
" CROSS JOIN" \
" (select sum(BILLABLE_HOURS) as hours from " \
" RESOURCE_CAPACITY.PNMAC.MVW_FORECASTANDACTUALCOMBINEDINFO " \
" where REDLINE_ISSUE = 0 and year >= 2017) vw; " \
)
difference = snowflake_utility.SnowflakeConnection().fetch(statement)[0][0]
assert difference <= 10, "Should be <=10 mismatch between RESOURCE_CAPACITY.PNMAC.BILLABLE_HOURS and RESOURCE_CAPACITY.PNMAC.MVW_FORECASTANDACTUALCOMBINEDINFO"
def get_view_ddl(_database, _schema, _view):
fully_prefixed_name = dot_combine(_database, _schema, _view)
statement = "SELECT get_ddl('view','" + fully_prefixed_name + "');"
return snowflake_utility.SnowflakeConnection().fetch(
statement)[0][0].encode('utf-8')
def get_objects_for_schema(_object, _database, _schema):
statement = 'SHOW TERSE ' + _object + ' IN ' + _database + '.' + _schema + ';'
object_data = snowflake_utility.SnowflakeConnection().fetch(statement)
return get_names_from_list(object_data, 1)
