R/fetch_grades.R

In ekholme-ccps/ccpsr: Tools for Working with CCPS Data

#' Fetch Grade Data
#'
#' @param year year to get grades for
#' @param secondary_only logical. If TRUE, will return data for students in middle, high, alternative, or regional schools. If summarizing GPA, you probably want this to be TRUE.
#'
#' @return a tibble with all grade data for year
#' @export
#'
#' @examples \dontrun{
#' fetch_grades(2018)
#' }
fetch_grades <- function(year, secondary_only = TRUE) {

  #check that year is numeric
  if (!is.numeric(year)) {
    rlang::abort(paste0("`year` must be an integer, not ", typeof(year)))
  }

  #check school year value
  if (year < 2014) {
    rlang::abort("Grades are only available from 2014 onward")
  }

  con <- ccpsr::set_con()

  tmp <- odbc::dbGetQuery(con, paste0('
                  SELECT gr.*,
                  cr.Academic_Type
                  FROM [CCPS_Shared].[CCPS_Shared].[RE_Student_GP_Grades] gr
                  LEFT JOIN (
                    SELECT COURSE_ID,
                    Academic_Type
                    FROM [CCPS_Shared].[CCPS_Shared].[RE_Courses]
                  ) cr ON gr.Course_Id = cr.COURSE_ID
                  WHERE SchYr = ', year
  ))

  ret <- if (secondary_only == TRUE) {

      dplyr::filter(tmp, SchoolCode %in% ccpsr::secondary_school_codes)

  } else tmp

  ret <- janitor::clean_names(ret)

  return(ret)

}