R/imports.R

In m070ch/ips.tools: Tools for IPS

#' @title Response Import
#'
#' @description Imports Responses from CRM.
#'
#' @details
#' Imports Test Responses Sheet from CRM based of Exam and Form Code.
#' Uses the ItemResponses SQL procedure in TestingDB.
#' Dependencies: RODBC
#'
#' @param exam The exam code
#' @param form The form code
#' @param crmUsername Your CRM Username credential
#' @param crmPassword Your CRM Password credential
#' @param source The source (default is "CRMDEV")
#' @return The test responses sheet from CRM
#' @examples import.responses("AB", "AB2016A_01", "me", "p@ssw0rd")
#' @family CRM Imports
import.responses <- function(exam, form, crmUsername, crmPassword, source){
  ### Get Data From CRM ###
  sqlparameterResponseImport<-paste(
    "EXEC TestingDB.dbo.ItemResponses
    @Forms = ","'",form,"'",",
    @Exam = ","'",exam,"'"
    , sep="") #Sets SQL for Test Responses query Parameters

  sqlparameterResponseImport<- gsub("[\n]","", sqlparameterResponseImport) #cleans up line breaks in original object

  if(missing(source)){
    dbhandle <- RODBC::odbcDriverConnect(paste("driver={SQL Server};server=CRMDEV;database=TestingDB;UID=", crmUsername,";PWD=", crmPassword,";", sep = ""))

  }
  else {
    dbhandle <- RODBC::odbcDriverConnect(paste("driver={SQL Server};server=", source, ";database=TestingDB;UID=", crmUsername,";PWD=", crmPassword,";", sep = ""))

  }

  ###Run Query###
  ResponseImport <- RODBC::sqlQuery(dbhandle, sqlparameterResponseImport) # get test responses
  RODBC::odbcCloseAll()

  return(ResponseImport)
}

#' @title Item Statistics Import
#'
#' @description Imports Statistics from CRM.
#'
#' @details
#' Imports Item Statistics Sheet from CRM based of Exam and Form Code.
#' Uses the ItemResponses SQL procedure in TestingDB.
#' Dependencies: RODBC
#'
#' @param exam The exam code
#' @param form The form code
#' @param itemtype Either Scored, Pretest, or both (default)
#' @param crmUsername Your CRM Username credential
#' @param crmPassword Your CRM Password credential
#' @param source The source (default is "CRMDEV")
#' @param clean clean the data? boolean
#' @return The item statistics sheet from CRM
#' @examples import.itemstats("AB", "AB2016A_01", itemtype = NA, "me", "p@ssw0rd")
#' @family CRM Imports
import.itemstats <- function(exam, form, itemtype, crmUsername, crmPassword, source, clean){
  ### Get Data From CRM ###
  if(is.na(itemtype)){
    sqlparameterCTT<-paste(
      "EXEC TestingDB.dbo.ItemStatisticsDetailWindow
      @Forms = ","'",form,"'",",
      @Exam = ","'",exam,"'"," ,
      @Scored = 'Both Scored and Pretest', @TestYear = 'All Years', @TestMonth = 'All Months', @TestQuarter = 'All Quarters'" #todo: make work
      , sep="") #Sets SQL for CTT query Parameters
  }
  else {
    sqlparameterCTT<-paste(
      "EXEC TestingDB.dbo.ItemStatisticsDetailWindow
      @Forms = ","'",form,"'",",
      @Exam = ","'",exam,"'"," ,
      @Scored = ","'",itemtype,"'",", @TestYear = 'All Years', @TestMonth = 'All Months', @TestQuarter = 'All Quarters'" #todo: make work
      , sep="") #Sets SQL for CTT query Parameters
  }

  sqlparameterCTT<- gsub("[\n]","", sqlparameterCTT) #cleans up line breaks in original object

  if(missing(source)){
    dbhandle <- RODBC::odbcDriverConnect(paste("driver={SQL Server};server=CRMDEV;database=TestingDB;UID=", crmUsername,";PWD=", crmPassword,";", sep = ""))

  }
  else {
    dbhandle <- RODBC::odbcDriverConnect(paste("driver={SQL Server};server=", source, ";database=TestingDB;UID=", crmUsername,";PWD=", crmPassword,";", sep = ""))

  }

  ###Run Query###
  CTT <- RODBC::sqlQuery(dbhandle, sqlparameterCTT) # get list of items for item analysis
  RODBC::odbcCloseAll()

  if (!missing(clean)){
    if(clean){
      CTT<- CTT[which(CTT$Exam==exam & CTT$Forms==form & CTT$TestYear=="All Years" & CTT$TestQuarter=="All Quarters" & CTT$TestMonth=="All Months"),]
    }
  }
  return(CTT)
}

#' @title Anchor Item Attributes Import
#'
#' @description Imports Item Attributes from CRM.
#'
#' @details
#' Imports Item Attributes Sheet from CRM based of ExamCode.
#' Uses a SQL statement.  Includes columns neccessary for anchoring in IRT calibration.
#' Dependencies: RODBC
#'
#' @param exam The exam code
#' @param crmUsername Your CRM Username credential
#' @param crmPassword Your CRM Password credential
#' @param source The source (default is "CRMDEV")
#' @return An item attributes and statistics table from CRM
#' @examples import.anchors("AB", "me", "p@ssw0rd")
#' @family CRM Imports
import.anchors <- function(exam, crmUsername, crmPassword, source){
  #get table of all exam items
  sqlparameterStatImport<-paste("SELECT DISTINCT new_itemstatistic.New_Exam AS Exam, new_itemstatistic.New_ItemId AS ItemId, new_itemstatistic.New_IRT_b AS IRT_b,
                                new_itemstatistic.New_Scored AS Scored, new_itemstatistic.New_Domain AS Domain, new_itemstatistic.New_OfficialStat AS Official_Stat,
                                new_itemStatistic.New_ItemType AS Item_Type, new_itemStatistic.New_FormBuild As FormBuild, new_itemstatistic.New_Forms AS Forms,
                                new_itemStatistic.New_IRT_d1 AS d1, new_itemStatistic.New_IRT_d2 AS d2, new_itemStatistic.New_IRT_d3 AS d3, new_itemStatistic.New_IRT_d4 AS d4
                                FROM ARDMS_MSCRM.dbo.new_itemstatistic WITH(NOLOCK)
                                WHERE StateCode=0 AND DeletionStateCode<>2 AND New_OfficialStat=1 AND New_IRT_b IS NOT NULL AND New_Exam='"
                                , exam, "' ORDER BY new_itemstatistic.New_ItemId ASC"
                                , sep="") #Sets SQL for Test Responses query Parameters

  sqlparameterStatImport<- gsub("[\n]","", sqlparameterStatImport) #cleans up line breaks in original object

  if(missing(source)){
    dbhandle <- RODBC::odbcDriverConnect(paste("driver={SQL Server};server=CRMDEV;database=TestingDB;UID=", crmUsername,";PWD=", crmPassword,";", sep = ""))

  }
  else {
    dbhandle <- RODBC::odbcDriverConnect(paste("driver={SQL Server};server=", source, ";database=TestingDB;UID=", crmUsername,";PWD=", crmPassword,";", sep = ""))

  }

  ###Run Query###
  AnchorImport <- RODBC::sqlQuery(dbhandle, sqlparameterStatImport) # get test responses
  RODBC::odbcCloseAll()
  return(AnchorImport)
}