def query_data(dataset): from app import con if dataset == 'df_ind': with con() as con: sql = 'SELECT * FROM li_dash_sla_bl' df_ind = pd.read_sql_query(sql=sql, con=con, parse_dates=['JOBCREATEDDATEFIELD', 'PROCESSDATECOMPLETEDFIELD']) sql_bd17 = 'SELECT * FROM business_days_since_2017' df_bd17 = pd.read_sql_query(sql=sql_bd17, con=con, parse_dates=['DATEOFYEAR']) # Rename columns to be more readable df_ind = (df_ind.rename(columns={'JOBID': 'Job ID', 'PROCESSID': 'Process ID', 'JOBTYPE': 'Job Type'}) .assign(MonthDateText=lambda x: x['JOBCREATEDDATEFIELD'].dt.strftime('%b %Y')) .assign(DayDateText=lambda x: x['JOBCREATEDDATEFIELD'].dt.strftime('%b %d %Y'))) df_ind['Month Year'] = df_ind['JOBCREATEDDATEFIELD'].map(lambda dt: dt.date().replace(day=1)) df_ind['Job Created Day'] = df_ind['JOBCREATEDDATEFIELD'].dt.date df_ind['Process Completed Day'] = df_ind['PROCESSDATECOMPLETEDFIELD'].dt.date df_bd17['DATEOFYEARDATEONLY'] = df_bd17['DATEOFYEAR'].dt.date # Get the number of business days since 1/1/2017 for all the Job Created Dates and Process Completed Dates df_merged = df_ind.merge(df_bd17, left_on='Job Created Day', right_on='DATEOFYEARDATEONLY', how='left') df = df_merged.merge(df_bd17, left_on='Process Completed Day', right_on='DATEOFYEARDATEONLY', how='left') # Subtract the number of business days between 1/1/2017 and the Job Created Date from the number of business days between # 1/1/2017 and the Process Completed Date to get the number of business days that the job was open/in progress. df['Bus. Days Open'] = df['BUSINESSDAYSSINCE_y'] - df['BUSINESSDAYSSINCE_x'] # Flag each job as either being within SLA or not based on whether the job was open for 2 days or fewer df['W/in SLA'] = np.where(df['Bus. Days Open'] <= 2, 1, 0) elif dataset == 'last_ddl_time': with con() as con: sql = 'SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) last_ddl_time FROM LI_DASH_SLA_BL' df = pd.read_sql_query(sql=sql, con=con) return df.to_json(date_format='iso', orient='split')
def query_data(dataset): from app import con if dataset == 'df_ind': with con() as con: sql = 'SELECT * FROM li_dash_expirationdates_tl' df = pd.read_sql_query(sql=sql, con=con, parse_dates=['EXPIRATIONDATE']) df = (df.assign( YearText=lambda x: x['EXPIRATIONDATE'].dt.strftime('%Y')).assign( MonthDateText=lambda x: x['EXPIRATIONDATE'].dt. strftime('%b %Y')).assign( WeekText=lambda x: x['EXPIRATIONDATE'].dt.strftime('%W'))) df['Year'] = df['EXPIRATIONDATE'].dt.year df['Month Year'] = df['EXPIRATIONDATE'].map( lambda dt: dt.date().replace(day=1)) df['Week'] = df['EXPIRATIONDATE'].map(lambda dt: dt.week) df['YearWeekText'] = df['YearText'] + '-' + df['WeekText'] + '-0' df['Year Week'] = pd.to_datetime(df['YearWeekText'], format='%Y-%W-%w') df['Year Week'] = df['Year Week'].map(lambda t: t.date()) elif dataset == 'last_ddl_time': with con() as con: sql = 'SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) last_ddl_time FROM LI_DASH_EXPIRATIONDATES_TL' df = pd.read_sql_query(sql=sql, con=con) return df.to_json(date_format='iso', orient='split')
def query_data(dataset): from app import con with con() as con: if dataset == 'df_ind': sql = 'SELECT * FROM li_stat_permits_accelreview' df = pd.read_sql_query(sql=sql, con=con, parse_dates=[ 'PERMITAPPLICATIONDATE', 'PERMITISSUEDATE', 'REVIEWISSUEDATE', 'PAIDDTTM' ]) df = (df.rename( columns={ 'APNO': 'Permit Number', 'PERMITAPPLICATIONDATE': 'Permit Application Date', 'PERMITISSUEDATE': 'Permit Issue Date', 'SLACOMPLIANCE': 'SLA Compliance', 'PERMITDESCRIPTION': 'Permit Type', 'WORKTYPE': 'Work Type' })) df['Permit Type'] = df['Permit Type'].astype(str) df['Permit Type'] = df['Permit Type'].map( lambda x: x.replace(" PERMIT", "")) df['Permit Type'] = df['Permit Type'].str.lower() df['Permit Type'] = df['Permit Type'].str.title() df['Work Type'] = df['Work Type'].fillna('None').astype(str) elif dataset == 'last_ddl_time': sql = 'SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) last_ddl_time FROM LI_STAT_PERMITS_ACCELREVIEW' df = pd.read_sql_query(sql=sql, con=con) return df.to_json(date_format='iso', orient='split')
def test_db(self): result = app.con() result = str(result) self.assertEqual( result, "Database(MongoClient(host=['192.168.99.100:32339'], document_class=dict, tz_aware=False, connect=True), 'jsondb')" )
def query_data(dataset): from app import con with con() as con: if dataset == 'df_ind': sql = 'SELECT * FROM li_dash_indworkloads' df = pd.read_sql_query(sql=sql, con=con, parse_dates=['DATECOMPLETEDFIELD']) # Rename the columns to be more readable df = (df.rename( columns={ 'PROCESSID': 'Process ID', 'PROCESSTYPE': 'Process Type', 'JOBNUMBER': 'Job Number', 'JOBTYPE': 'Job Type', 'LICENSEKIND': 'Kind of License', 'LICENSETYPE': 'License Type', 'PERSON': 'Person', 'SCHEDULEDSTARTDATE': 'Scheduled Start Date', 'DATECOMPLETED': 'Date Completed', 'DURATION': 'Duration (days)', 'JOBLINK': 'Job Link' }).assign(DateText=lambda x: x['DATECOMPLETEDFIELD'].dt. strftime('%b %Y'))) df['Month Year'] = df['DATECOMPLETEDFIELD'].map( lambda dt: dt.date().replace(day=1)) elif dataset == 'last_ddl_time': sql = 'SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) last_ddl_time FROM LI_DASH_INDWORKLOADS' df = pd.read_sql_query(sql=sql, con=con) return df.to_json(date_format='iso', orient='split')
def query_data(dataset): from app import con with con() as con: if dataset == 'df_ind': sql = 'SELECT * FROM li_dash_incompleteprocesses_bl' df = pd.read_sql_query(sql=sql, con=con, parse_dates=['SCHEDULEDSTARTDATEFIELD']) # Rename the columns to be more readable df = (df.rename( columns={ 'PROCESSID': 'Process ID', 'PROCESSTYPE': 'Process Type', 'JOBNUMBER': 'Job Number', 'JOBTYPE': 'Job Type', 'LICENSETYPE': 'License Type', 'ASSIGNEDSTAFF': 'Assigned Staff', 'NUMASSIGNEDSTAFF': 'Num of Assigned Staff', 'SCHEDULEDSTARTDATE': 'Scheduled Start Date', 'TIMESINCESCHEDULEDSTARTDATE': 'Days Open', 'JOBLINK': 'Job Link' }).assign(DateText=lambda x: x['SCHEDULEDSTARTDATEFIELD'].dt. strftime('%b %Y'))) df['Month Year'] = df['SCHEDULEDSTARTDATEFIELD'].map( lambda dt: dt.date().replace(day=1)) elif dataset == 'last_ddl_time': sql = 'SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) last_ddl_time FROM LI_DASH_INCOMPLETEPROCESSES_BL' df = pd.read_sql_query(sql=sql, con=con) return df.to_json(date_format='iso', orient='split')
def query_data(dataset): from app import con with con() as con: if dataset == 'df_ind': sql = 'SELECT * FROM li_dash_uninsp_bl_comp_check' df = pd.read_sql_query(sql=sql, con=con, parse_dates=['MOSTRECENTCCFIELD']) elif dataset == 'last_ddl_time': sql = 'SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) last_ddl_time FROM LI_DASH_UNINSP_BL_COMP_CHECK' df = pd.read_sql_query(sql=sql, con=con) return df.to_json(date_format='iso', orient='split')
def query_data(dataset): from app import con with con() as con: if dataset == 'df_ind': sql = 'SELECT * FROM li_dash_overdueinsp_bl' df = pd.read_sql_query(sql=sql, con=con) elif dataset == 'last_ddl_time': sql = 'SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) last_ddl_time FROM LI_DASH_OVERDUEINSP_BL' df = pd.read_sql_query(sql=sql, con=con) return df.to_json(date_format='iso', orient='split')
def query_data(dataset): from app import con with con() as con: if dataset == 'df_ind': sql = 'SELECT * FROM li_dash_jobvolsbysubtype_bl' df = pd.read_sql_query(sql=sql, con=con, parse_dates=['JOBCREATEDDATEFIELD']) elif dataset == 'last_ddl_time': sql = 'SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) last_ddl_time FROM LI_DASH_JOBVOLSBYSUBTYPE_BL' df = pd.read_sql_query(sql=sql, con=con) return df.to_json(date_format='iso', orient='split')
def query_data(dataset): from app import con with con() as con: if dataset == 'df_ind': sql = 'SELECT * FROM li_stat_permits_otcvsreview' df = pd.read_sql_query(sql=sql, con=con, parse_dates=['ISSUEDATE']) elif dataset == 'last_ddl_time': sql = 'SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) last_ddl_time FROM LI_STAT_PERMITS_OTCVSREVIEW' df = pd.read_sql_query(sql=sql, con=con) return df.to_json(date_format='iso', orient='split')
def query_data(dataset): from app import con with con() as con: if dataset == 'df_ind': sql = 'SELECT * FROM li_stat_licenserevenue_tl' df = pd.read_sql_query(sql=sql, con=con, parse_dates=['PAYMENTDATE']) elif dataset == 'last_ddl_time': sql = 'SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) last_ddl_time FROM LI_STAT_LICENSEREVENUE_TL' df = pd.read_sql_query(sql=sql, con=con) return df.to_json(date_format='iso', orient='split')
def query_data(dataset): from app import con with con() as con: if dataset == 'df_ind': sql = 'SELECT * FROM li_stat_publicdemos' df = pd.read_sql_query(sql=sql, con=con, parse_dates=['DEMODATE']) elif dataset == 'last_ddl_time': sql = 'SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) last_ddl_time FROM LI_STAT_PUBLICDEMOS' df = pd.read_sql_query(sql=sql, con=con) return df.to_json(date_format='iso', orient='split')
def query_data(dataset): from app import con with con() as con: if dataset == 'df_ind': sql = 'SELECT * FROM li_stat_licenserevenue_bl' df = pd.read_sql_query(sql=sql, con=con, parse_dates=['PAYMENTDATE']) df.rename(columns={'JOBTYPE': 'Job Type', 'PAYMENTDATE': 'Date', 'TOTALAMOUNT': 'Revenue Collected'}, inplace=True) elif dataset == 'last_ddl_time': sql = 'SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) last_ddl_time FROM LI_STAT_LICENSEREVENUE_BL' df = pd.read_sql_query(sql=sql, con=con) return df.to_json(date_format='iso', orient='split')
def query_data(dataset): from app import con with con() as con: if dataset == 'df_ind': sql = 'SELECT * FROM li_stat_submittalvolumes_tl' df = (pd.read_sql_query(sql=sql, con=con, parse_dates=['ISSUEDATE']) .sort_values(by='ISSUEDATE')) elif dataset == 'last_ddl_time': sql = 'SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) last_ddl_time FROM LI_STAT_SUBMITTALVOLUMES_TL' df = pd.read_sql_query(sql=sql, con=con) return df.to_json(date_format='iso', orient='split')
def query_data(dataset): from app import con with con() as con: if dataset == 'df_ind': sql = 'SELECT * FROM li_stat_permitsfees' df = pd.read_sql_query(sql=sql, con=con, parse_dates=['ISSUEDATE']) df['PERMITDESCRIPTION'] = df['PERMITDESCRIPTION'].map( lambda x: x.replace(" PERMIT", "")) df['PERMITDESCRIPTION'] = df['PERMITDESCRIPTION'].str.lower() df['PERMITDESCRIPTION'] = df['PERMITDESCRIPTION'].str.title() elif dataset == 'last_ddl_time': sql = 'SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) last_ddl_time FROM LI_STAT_PERMITSFEES' df = pd.read_sql_query(sql=sql, con=con) return df.to_json(date_format='iso', orient='split')
def query_data(dataset): from app import con with con() as con: if dataset == 'df_ind': sql = 'SELECT * FROM li_stat_licensevolumes_tl' df = pd.read_sql_query(sql=sql, con=con, parse_dates=['ISSUEDATE']) df = (df.rename( columns={ 'ISSUEDATE': 'Issue Date', 'LICENSETYPE': 'License Type', 'COUNTJOBS': 'Number of Licenses Issued' }).assign( DateText=lambda x: x['Issue Date'].dt.strftime('%b %Y'))) elif dataset == 'last_ddl_time': sql = 'SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) last_ddl_time FROM LI_STAT_LICENSEVOLUMES_TL' df = pd.read_sql_query(sql=sql, con=con) return df.to_json(date_format='iso', orient='split')
def query_data(dataset): from app import con with con() as con: if dataset == 'df_ind': sql = 'SELECT * FROM li_dash_activeproc_tl_ind' elif dataset == 'df_counts': sql = 'SELECT * FROM li_dash_activeproc_tl_counts' elif dataset == 'ind_last_ddl_time': sql = 'SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) last_ddl_time FROM LI_DASH_ACTIVEPROC_TL_IND' elif dataset == 'counts_last_ddl_time': sql = 'SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) last_ddl_time FROM LI_DASH_ACTIVEPROC_TL_COUNTS' df = pd.read_sql_query(sql=sql, con=con) if dataset == 'df_counts': # Make TIMESINCESCHEDULEDSTARTDATE a Categorical Series and give it a sort order df['TIMESINCESCHEDULEDSTARTDATE'] = pd.Categorical(df['TIMESINCESCHEDULEDSTARTDATE'], time_categories) df.sort_values(by='TIMESINCESCHEDULEDSTARTDATE', inplace=True) return df.to_json(date_format='iso', orient='split')
def query_data(dataset): from app import con with con() as con: if dataset == 'df_ind': sql = 'SELECT * FROM li_stat_immdang_ind' df = pd.read_sql_query(sql=sql, con=con, parse_dates=['VIOLATIONDATE']) df.sort_values(by='VIOLATIONDATE', inplace=True) elif dataset == 'df_counts': sql = 'SELECT * FROM li_stat_immdang_counts' df = pd.read_sql_query(sql=sql, con=con, parse_dates=['VIOLATIONDATE']) df = df.assign(DateText=lambda x: x['VIOLATIONDATE'].dt.strftime('%b %Y')) df.sort_values(by='VIOLATIONDATE', inplace=True) elif dataset == 'ind_last_ddl_time': sql = 'SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) last_ddl_time FROM LI_STAT_IMMDANG_IND' df = pd.read_sql_query(sql=sql, con=con) elif dataset == 'counts_last_ddl_time': sql = 'SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) last_ddl_time FROM LI_STAT_IMMDANG_COUNTS' df = pd.read_sql_query(sql=sql, con=con) return df.to_json(date_format='iso', orient='split')
def query_data(): from app import con with con() as con: sql = 'SELECT DISTINCT * FROM expiring_licenses' df = pd.read_sql_query(sql=sql, con=con, parse_dates=['EXPIRATIONDATE']) # Rename the columns to be more readable df = (df.rename( columns={ 'LEGALNAME': 'Legal Name', 'BUSINESS_NAME': 'Business Name', 'LICENSETYPE': 'License Type', 'EXPIRATIONDATE': 'Expiration Date', 'OWNEROCCUPIED': 'Owner Occupied', 'MESSAGE': 'Message', 'BUSINESSID': 'Business ID', 'LICENSENUMBER': 'License Number', 'LINK': 'Link' })) return df.to_json(date_format='iso', orient='split')
def query_data(dataset): from app import con with con() as con: if dataset == 'df_ind': sql = 'SELECT * FROM li_stat_uninspectedservreq' df = pd.read_sql_query(sql=sql, con=con, parse_dates=['CALLDATE']) df.rename(columns= {'SERVREQNO': 'Service Request Num', 'CALLDATE': 'Call Date', 'PROBLEMDESCRIPTION': 'Problem Description', 'UNIT': 'Unit', 'DISTRICT': 'District'}, inplace=True) df['Call Date'] = pd.to_datetime(df['Call Date']) df['Call Date (no time)'] = df['Call Date'].dt.date df['Bus. Days Outstanding'] = df[df['Call Date (no time)'].notnull()].apply(calc_bus_days, axis=1) df['Within SLA'] = np.where(df['Bus. Days Outstanding'] > df['SLA'], 'No', 'Yes') df['Bus. Days Overdue'] = np.where(df['Within SLA'] == 'No', df['Bus. Days Outstanding'] - df['SLA'], 'N/A') elif dataset == 'last_ddl_time': sql = 'SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) last_ddl_time FROM LI_STAT_UNINSPECTEDSERVREQ' df = pd.read_sql_query(sql=sql, con=con) return df.to_json(date_format='iso', orient='split')
def test_db_name(self): result = app.con().name self.assertEqual(result, "jsondb")