def apiYear(self):
dbConn = myDB()
modelObj = model()
SQL = 'SELECT Year, Quarter, SUM(Recipients) as Recipients, SUM(No_Originated) as No_Originated, SUM(Value_Originated) as Value_Originated, SUM(No_Disbursements) as No_Disbursements, SUM(Value_Disbursements) as Value_Disbursements from aid_tbl a WHERE Loan_Type NOT IN ("DL UNSUBSIDIZED") AND Code IN (SELECT DISTINCT(Code) FROM aid_tbl WHERE YEAR > (SELECT MAX(YEAR) - 1 FROM aid_tbl)) GROUP BY Year, Quarter'
data, forecast, accy, accy1, point = modelObj.model_date(
pd.DataFrame(dbConn.exec(SQL)))
return data, forecast, accy, accy1, point
def apistate(self, args):
dbConn = myDB()
print(args)
Where = "SELECT distinct(state) as state FROM school_tbl s WHERE s.Code IN (SELECT DISTINCT(Code) FROM aid_tbl WHERE YEAR > (SELECT MAX(YEAR) - 1 FROM aid_tbl)) "
if (len(args['IS_US']) != 0):
Where += " AND s.IS_US = " + args['IS_US']
Where += " ORDER BY IS_US"
df_school = pd.DataFrame(dbConn.exec(Where))
df_school.sort_values(by=['state'], inplace=True)
return df_school['state']
def apiSchool(self):
dbConn = myDB()
df_school = pd.DataFrame(dbConn.exec("SELECT * FROM school_tbl"))
df_school['IS_US'] = df_school['IS_US'].astype(int)
df_school['Code'] = df_school['Code'].astype(int)
df_school['Zip'] = df_school['Zip'].fillna(-1)
df_school['Zip'] = df_school['Zip'].astype(int)
df_school['Name'] = df_school["School"] + " [ " + df_school[
"Code"].map(str) + " ]"
df_school.drop(['Code', 'School', 'Zip_Code'], axis=1, inplace=True)
return df_school
def apizip(self, args):
dbConn = myDB()
Where = "SELECT distinct(zip) as Zip FROM school_tbl s WHERE s.Code IN (SELECT DISTINCT(Code) FROM aid_tbl WHERE YEAR > (SELECT MAX(YEAR) - 1 FROM aid_tbl)) AND zip != '-1' "
if (len(args['IS_US']) != 0):
Where += " AND s.IS_US = " + args['IS_US']
if (len(args['State']) != 0):
Where += " AND s.State = '" + args['State'] + "'"
Where += " ORDER BY zip"
df_school = pd.DataFrame(dbConn.exec(Where))
df_school['Zip'] = df_school['Zip'].fillna(-1)
return df_school['Zip'].astype(str)
def apiSchool_Type(self, args):
dbConn = myDB()
Where = ""
if (len(args['IS_US']) != 0):
Where += " AND s.IS_US = " + args['IS_US']
if (len(args['State']) != 0):
Where += " AND s.State = '" + args['State'] + "'"
if (len(args['Zip']) != 0):
Where += " AND s.Zip = '" + args['Zip'] + "'"
Where += " ORDER BY School_Type"
df_school = pd.DataFrame(
dbConn.exec(
"SELECT distinct(School_Type) as School_Type FROM school_tbl s WHERE s.Code IN (SELECT DISTINCT(Code) FROM aid_tbl WHERE YEAR > (SELECT MAX(YEAR) - 1 FROM aid_tbl)) "
+ Where))
df_school.sort_values(by=['School_Type'], inplace=True)
return df_school['School_Type']
def apiname(self, args):
dbConn = myDB()
Where = ""
if (len(args['IS_US']) != 0):
Where += " AND s.IS_US = " + args['IS_US']
if (len(args['State']) != 0):
Where += " AND s.State = '" + args['State'] + "'"
if (len(args['Zip']) != 0):
Where += " AND s.Zip = '" + args['Zip'] + "'"
if (len(args['School_Type']) != 0):
Where += " AND s.School_Type = '" + args['School_Type'] + "'"
df_school = pd.DataFrame(
dbConn.exec(
"SELECT * FROM school_tbl s WHERE s.Code IN (SELECT DISTINCT(Code) FROM aid_tbl WHERE YEAR > (SELECT MAX(YEAR) - 1 FROM aid_tbl)) "
+ Where))
df_school['Name'] = df_school["School"] + " [ " + df_school[
"Code"].map(str) + " ]"
df_school.sort_values(by=['Name'], inplace=True)
return df_school['Name']
def aidtype(self, args):
Where = ""
if (len(args['IS_US']) != 0):
Where += " AND s.IS_US = " + args['IS_US']
if (len(args['State']) != 0):
Where += " AND s.State = '" + args['State'] + "'"
if (len(args['Zip']) != 0):
Where += " AND s.Zip = '" + args['Zip'] + "'"
if (len(args['School_Type']) != 0):
Where += " AND s.School_Type = '" + args['School_Type'] + "'"
if (len(args['Name']) != 0):
Where += " AND s.Code = " + args['Name'].split('[ ')[1].split(
' ]')[0]
dbConn = myDB()
SQL = 'SELECT a.Loan_Type from aid_tbl a, school_tbl s WHERE a.Loan_Type NOT IN ("DL UNSUBSIDIZED") AND a.Code IN (SELECT DISTINCT(Code) FROM aid_tbl WHERE YEAR > (SELECT MAX(YEAR) - 1 FROM aid_tbl)) AND s.Code = a.Code ' + Where + ' GROUP BY a.Loan_Type ORDER BY a.Loan_Type'
data = pd.DataFrame(dbConn.exec(SQL))
data['aidtype'] = data['Loan_Type'].replace(
['DL SUBSIDIZED- UNDERGRADUATE', 'DL SUBSIDIZED- GRADUATE'],
'DL SUBSIDIZED')
data.drop(['Loan_Type'], axis=1, inplace=True)
return data
def forcast(self, args):
Where = ""
if (len(args['IS_US']) != 0):
Where += " AND s.IS_US = " + args['IS_US']
if (len(args['State']) != 0):
Where += " AND s.State = '" + args['State'] + "'"
if (len(args['Zip']) != 0):
Where += " AND s.Zip = '" + args['Zip'] + "'"
if (len(args['School_Type']) != 0):
Where += " AND s.School_Type = '" + args['School_Type'] + "'"
if (len(args['Name']) != 0):
Where += " AND s.Code = " + args['Name'].split('[ ')[1].split(
' ]')[0]
if (len(args['Aid_Type']) != 0):
Where += " AND a.Loan_Type = '" + args['Aid_Type'] + "'"
dbConn = myDB()
modelObj = model()
SQL = 'SELECT a.Year, a.Quarter, SUM(a.Recipients) as Recipients, SUM(a.No_Originated) as No_Originated, SUM(a.Value_Originated) as Value_Originated, SUM(a.No_Disbursements) as No_Disbursements, SUM(a.Value_Disbursements) as Value_Disbursements from aid_tbl a, school_tbl s WHERE a.Loan_Type NOT IN ("DL UNSUBSIDIZED") AND a.Code IN (SELECT DISTINCT(Code) FROM aid_tbl WHERE YEAR > (SELECT MAX(YEAR) - 1 FROM aid_tbl)) AND s.Code = a.Code ' + Where + ' GROUP BY a.Year, a.Quarter'
data, forecast, accy, accy1, point = modelObj.model_date(
pd.DataFrame(dbConn.exec(SQL)))
return data, forecast, accy, accy1, point
def apiisUs(self):
dbConn = myDB()
df_school = pd.DataFrame(
dbConn.exec("SELECT distinct(IS_US) as IS_US FROM school_tbl"))
return df_school['IS_US'].astype(int)
def model1(self):
dbConn = myDB()
return 'df_year'