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")
