R/DataExtract.R

In UW-L-S-Academic-Information-Management/UWdataUTIL: UW R Admit Data Utils

#This file contains functions to run daily data extract of enrollment or
#admissions data, updating a data file containing this information. 
#


###This script queries current enrollment data and writes out the 
###unique date stamped enrollments for each date it is run.
enrollment.data.update <- function(TERM, INFOACCESScon, FILE = paste(getwd(), "/enrollment.csv", sep = "")){
require(dplyr)
require(dbplyr)
require(xlsxjars)
require(xlsx)
require(readr)
  
  
  
  ##Current student enrollment information.
  curr.course.enrollment.query <- "SELECT DISTINCT
       TRUNC(STDNT_CRSE_ROSTER_MULTITERM.EXTRACT_DATE_TIME) AS EXTRACT_DATE,
       STDNT_CRSE_ROSTER_MULTITERM.COURSE_ID,
       STDNT_CRSE_ROSTER_MULTITERM.ID,
       STDNT_CRSE_ROSTER_MULTITERM.CREDITS_TAKEN
FROM UW.STDNT_CRSE_ROSTER_MULTITERM STDNT_CRSE_ROSTER_MULTITERM
WHERE (STDNT_CRSE_ROSTER_MULTITERM.COURSE_SECTION_RANGE1 NOT IN ('000', '00'))"

  
  query.filter <- paste("\n AND (STDNT_CRSE_ROSTER_MULTITERM.TERM = '", TERM , sep = "")
  query.filter <- paste(query.filter, "')" , sep = "")
  curr.course.enrollment.query <- paste(curr.course.enrollment.query, query.filter, sep = "", collapse = " ")  
  

  
  curr.course.enrollment <- DBI::dbGetQuery(INFOACCESScon,curr.course.enrollment.query)
  
  #Read in historical data and write out unique new data.
  
    if(file.exists(FILE)){
    curr.course.enrollment.readin <- read_csv(FILE, 
                                              col_types = cols(EXTRACT_DATE = col_date(format = "%Y-%m-%d")))
    curr.course.enrollment <- unique(rbind(curr.course.enrollment.readin, curr.course.enrollment))
    }
    write.csv(curr.course.enrollment, FILE, row.names = FALSE)

  
return(curr.course.enrollment)
  
}


admissions.data.update <- function(TERMS, INFOACCESScon, FILE = paste(getwd(), "/admissions.csv", sep = "")){
  
require(dplyr)
require(dbplyr)
require(xlsxjars)
require(xlsx)  
require(readr)
  
  
admissions.query <- "SELECT DISTINCT 
TRUNC(STDNT_UGRD_APPLICANT.EXTRACT_DATE_TIME) AS EXTRACT_DATE,
STDNT_UGRD_APPLICANT.ID,
  STDNT_UGRD_APPLICANT.APPLICATION_NUMBER,
  STDNT_UGRD_APPLICANT.APPLIC_ADMIT_TERM,
  STDNT_UGRD_APPLICANT.APPLIC_ADMIT_TYPE,
  STDNT_UGRD_APPLICANT.PRIMARY_ACADEMIC_GROUP,
  STDNT_UGRD_APPLICANT.APPLIC_ACADEMIC_PROGRAM,
  STDNT_UGRD_APPLICANT.APPLIC_ACADEMIC_PLAN,
  STDNT_UGRD_APPLICANT.APPLIC_ACADEMIC_LEVEL,
  STDNT_UGRD_APPLICANT.APPLIC_PROGRAM_STATUS,
  STDNT_UGRD_APPLICANT.CAMPUS_ID,
  STDNT_UGRD_APPLICANT.RESIDENCY_CODE,
  STDNT_UGRD_APPLICANT.RESIDENCY_DATE,
  STDNT_UGRD_APPLICANT.TUITION_RESIDENCY_CODE,
  STDNT_UGRD_APPLICANT.PROGRAM_ACTION,
  STDNT_UGRD_APPLICANT.PROGRAM_ACTION_DATE,
  STDNT_UGRD_APPLICANT.PROGRAM_REASON
FROM UW.STDNT_UGRD_APPLICANT STDNT_UGRD_APPLICANT"  


query.filter <- paste(TERMS, sep = "", collapse = "', '")
#JobIDSTRING <- JobIDS
query.filter <- paste(c("WHERE (STDNT_UGRD_APPLICANT.APPLIC_ADMIT_TERM IN ('", query.filter), sep = "", collapse = "")
query.filter <- paste(c(query.filter, "'))" ), sep = "", collapse = "")
admissions.query <- paste(c(admissions.query, query.filter), sep = "", collapse = " ")





curr.admissions <- DBI::dbGetQuery(INFOACCESScon,admissions.query)

#Read in historical data and write out unique new data.

if(file.exists(FILE)){
  curr.admissions.readin <- read_csv(FILE, 
                                            col_types = cols(EXTRACT_DATE = col_date(format = "%Y-%m-%d")))
  curr.admissions <- unique(rbind(curr.admissions.readin, curr.admissions))
}
write.csv(curr.admissions, FILE, row.names = FALSE)


return(curr.admissions)


  
}


timetable.data.update <- function(TERM, INFOACCESScon, FILE = paste(getwd(), "/timetable.csv", sep = "")){
  
  require(dplyr)
  require(dbplyr)
  require(xlsxjars)
  require(xlsx)  

  
  timetable.query <- "SELECT DISTINCT *
FROM UW.STDNT_TIMETABLE_MULTITERM STDNT_TIMETABLE_MULTITERM"  
  
  
  query.filter <- paste("\n WHERE (STDNT_TIMETABLE_MULTITERM.TERM = '", TERM , sep = "")
  query.filter <- paste(query.filter, "')" , sep = "")
  timetable.query <- paste(timetable.query, query.filter, sep = "", collapse = " ")  
  
  
  timetable <- DBI::dbGetQuery(INFOACCESScon,timetable.query)
  
  timetable$EXTRACT_DATE <- trunc.Date(timetable$EXTRACT_DATE_TIME)
  
  
  #Read in historical data and write out unique new data.
  
  if(file.exists(FILE)){
    timetable.readin <- read.csv(FILE)
    timetable.readin$TERM <- as.character(timetable.readin$TERM)
    timetable.readin$START_DATE <- as.Date(timetable.readin$START_DATE)
    timetable.readin$END_DATE <- as.Date(timetable.readin$END_DATE)
    timetable.readin$WEEKS_OF_INSTRUCTION <- as.character(timetable.readin$WEEKS_OF_INSTRUCTION)
    timetable.readin$CATALOG_NUMBER <- as.character(timetable.readin$CATALOG_NUMBER)
    timetable.readin$COURSE_ID <- as.character(timetable.readin$COURSE_ID)
    timetable.readin$SECTION_NUMBER <- as.character(timetable.readin$SECTION_NUMBER)
    timetable.readin$COMBINED_SECTIONS_ID <- as.character(timetable.readin$COMBINED_SECTIONS_ID)
    timetable.readin$CONTACT_PERIODS <- as.character(timetable.readin$CONTACT_PERIODS)
    timetable.readin$INSTRUCTOR_ID <- as.character(timetable.readin$INSTRUCTOR_ID)
    timetable.readin$COURSE_ADMINISTRATOR_ID <- as.character(timetable.readin$COURSE_ADMINISTRATOR_ID)
    timetable.readin$PRIMARY_SUBJECT_CODE <- as.character(timetable.readin$PRIMARY_SUBJECT_CODE)
    timetable.readin$PRIMARY_CATALOG_NUMBER <- as.character(timetable.readin$PRIMARY_CATALOG_NUMBER)
    timetable.readin$PRIMARY_SECTION_NUMBER <- as.character(timetable.readin$PRIMARY_SECTION_NUMBER)
    timetable.readin$ASSOC_LVL_REQUISITE_GRP_NBR <- as.character(timetable.readin$ASSOC_LVL_REQUISITE_GRP_NBR)
    timetable.readin$CATALOG_LVL_REQUISITE_GRP_NBR <- as.character(timetable.readin$CATALOG_LVL_REQUISITE_GRP_NBR)
    timetable.readin$AUTO_ENROLLED_SECTION1 <- as.character(timetable.readin$AUTO_ENROLLED_SECTION1)
    timetable.readin$AUTO_ENROLLED_SECTION2 <- as.character(timetable.readin$AUTO_ENROLLED_SECTION2)
    timetable.readin$EXTRACT_DATE_TIME <- as.Date(timetable.readin$EXTRACT_DATE_TIME)
    timetable.readin$EXTRACT_DATE <- as.Date(timetable.readin$EXTRACT_DATE, format = "%Y-%m-%d")
    timetable <- unique(bind_rows(timetable.readin, timetable))
  }
  write.csv(timetable, FILE, row.names = FALSE)
  
return(timetable)
}