R/SQL_DDsld.R

In INESSS-QC/admissibilite1: Admissibilité des individus

#' Extraction
#'
#' Permet d'extraire la première date de soins de longue durée de tous les bénéficiaires avant la date de fin d'extraction.
#'
#' @param user Identification de l'usager.
#' @param FinExtraction "AAAA-MM-JJ". Date de fin de l'étude.
#' @param savefile Répertoire où enregistrer le résultat se terminant par */nom_du_fichier.rds*. Inscrire `FALSE` si la sauvegarde n'est pas nécessaire.
#' @param statement Source de données machine.
#' @param database Database sur le serveur. Peut être `NULL`.
#'
#' @import data.table
#' @importFrom DBI dbGetQuery
#' @importFrom lubridate as_date
#' @importFrom askpass askpass
#' @export
SQL_DDsld<- function(
  user,
  FinExtraction,
  savefile = FALSE,
  statement = "PEI_PRD",
  database = "ESPA_TRAV_16"
){

  # Fonctions --------------------------------------------------------------------------------------

  .verif_args <- function(user, pwd,FinExtraction,statement,database){
    ### Vérification des arguments s'ils ont le type de variable requis pour qu'il n'y ait pas
    ### d'erreur dans la fonction. Si le type de variable n'est pas bon, renvoie une erreur.
    if(!is.character(user))
      stop("'user' n'est pas de type CHARACTER.")
    if(!is.character(pwd))
      stop("'pwd' n'est pas de type CHARACTER.")
    if(is.na(as_date(FinExtraction)))
      stop("'FinExtraction' n'est pas sous la forme 'AAAA-MM-JJ'.")
    if(!is.character(statement))
      stop("'statement' n'est pas de type CHARACTER.")
    if(!is.null(database))
      if(!is.character(database))
        stop("'database' n'est pas de type CHARACTER.")
  }

  # Code -------------------------------------------------------------------------------------------

  pwd <- askpass("Entrer votre mot de passe :")
  .verif_args(user, pwd,FinExtraction,statement,database)  # vérification des arguments

  DSLD<- dbGetQuery(  # extraction - requête SQL
    .connexion(statement, user, pwd, database),
    paste0( "select FIPA.BENF_NO_INDIV_BEN_BANLS as ID,
            DATE_C1_1,
            DATE_C1_2,
            DATE_C2_1,
            DATE_C2_2,
            DATE_C3,
            DATE_C4,
            DATE_C5_1,
            DATE_C5_2,
            DATE_C6
            FROM PROD.V_FICH_ID_BEN_CM as FIPA

            LEFT JOIN ( SELECT BENF_NO_INDIV_BEN_BANLS as ID,
            min(BENF_DD_PLAN_MED_BEN ) as DATE_C1_1
            FROM PROD.V_PLAN_MED_BEN_CM
            WHERE BENF_COD_PLAN= 97 AND
            BENF_DD_PLAN_MED_BEN <= '",FinExtraction,"'
            GROUP BY ID ) AS C1_1
            ON C1_1.ID=FIPA.BENF_NO_INDIV_BEN_BANLS

            LEFT JOIN (SELECT BENF_NO_INDIV_BEN_BANLS AS ID,
            MIN(BENF_DD_ADMIS_BEN) AS DATE_C1_2
            FROM PROD.V_ADMIS_BEN_CM
            WHERE BENF_COD_CLA_SITU_ADMIS = 19 AND
            BENF_DD_ADMIS_BEN <= '",FinExtraction,"'
            GROUP BY ID) AS C1_2
            ON C1_2.ID = FIPA.BENF_NO_INDIV_BEN_BANLS

            LEFT JOIN (SELECT SMOD_NO_INDIV_BEN_BANLS as ID,
            MIN ( SMOD_DAT_SERV) as DATE_C2_1
            FROM Prod.I_SMOD_SERV_MD_CM
            WHERE( (SMOD_NO_ETAB_USUEL<10000 AND SMOD_NO_ETAB_USUEL mod 10 is in (4,5)) OR
            (SMOD_NO_ETAB_USUEL>9999 AND SMOD_NO_ETAB_USUEL<30000 AND SMOD_NO_ETAB_USUEL mod 10 = 5 ))
            AND SMOD_DAT_SERV <= '",FinExtraction,"'
            GROUP BY ID) AS C2_1
            ON C2_1.ID= FIPA.BENF_NO_INDIV_BEN_BANLS

            LEFT JOIN (SELECT SMOD_NO_INDIV_BEN_BANLS  as ID,
            MIN (SMOD_DAT_SERV) AS DATE_C2_2
            FROM Prod.I_SMOD_SERV_MD_CM
            WHERE ETAB_COD_SECT_ACTIV_ETAB=5 AND
            SMOD_DAT_SERV <=  '",FinExtraction,"'
            GROUP BY ID) as C2_2
            ON C2_2.ID=FIPA.BENF_NO_INDIV_BEN_BANLS


            LEFT JOIN ( select ID,
            min(DATE_C3) as DATE_C3
            FROM (
            SELECT SHOP_NO_INDIV_BEN_BANLS AS ID,
            CASE WHEN TYP_ETAB='SLD' OR TYP_ETAB_PROV='SLD' THEN SHOP_DAT_ADMIS_SEJ_HOSP
            WHEN TYP_ETAB_DESTI='SLD' THEN SHOP_DAT_DEPAR_SEJ_HOSP
            END AS DATE_C3
            FROM RES_SSS.V_SEJ_HOSP_CM

            LEFT JOIN (SELECT  no_etab,
            an,
            typ as TYP_ETAB
            FROM ESPA_TRAV_16.vocation) AS ETAB1
            ON ETAB1.no_etab=SHOP_NO_ETAB_MSSS AND
            EXTRACT(YEAR FROM SHOP_DAT_ADMIS_SEJ_HOSP)=ETAB1.an

            LEFT JOIN (SELECT no_etab,
            an,
            typ as TYP_ETAB_PROV
            FROM ESPA_TRAV_16.vocation) AS ETAB2
            ON ETAB2.no_etab=SHOP_NO_ETAB_MSSS_DESTI AND
            EXTRACT(YEAR FROM SHOP_DAT_ADMIS_SEJ_HOSP) = ETAB2.an

            LEFT JOIN (SELECT no_etab,
            an,
            typ as TYP_ETAB_DESTI
            FROM ESPA_TRAV_16.vocation) AS ETAB3
            ON ETAB3.no_etab=SHOP_NO_ETAB_MSSS_PROVE
            AND EXTRACT(YEAR FROM SHOP_DAT_DEPAR_SEJ_HOSP) = ETAB3.an
            WHERE SHOP_DAT_ADMIS_SEJ_HOSP<='",FinExtraction,"' ) AS TAB2

            WHERE DATE_C3	<='",FinExtraction,"'
            GROUP BY ID) as C3
            ON FIPA.BENF_NO_INDIV_BEN_BANLS =C3.ID

            LEFT JOIN (SELECT SHOP_NO_INDIV_BEN_BANLS AS ID,
            MIN(SHOP_DAT_ADMIS_SEJ_HOSP) AS DATE_C4
            FROM RES_SSS.V_SEJ_HOSP_CM
            WHERE SHOP_TYP_SOIN_SEJ_HOSP in (3,6)
            AND SHOP_DAT_ADMIS_SEJ_HOSP<='",FinExtraction,"'
            GROUP BY ID) AS C4
            ON FIPA.BENF_NO_INDIV_BEN_BANLS =C4.ID

            LEFT JOIN (SELECT SHOP_NO_INDIV_BEN_BANLS AS ID,
            MIN ( SHOP_DAT_ADMIS_SEJ_HOSP) as  DATE_C5_1
            FROM  RES_SSS.V_SEJ_HOSP_CM AS VSER
            WHERE  SHOP_TYP_LIEU_SEJ_HOSP_PROVE in (3,6,8,10,14,15,20,40)
            AND SHOP_DAT_ADMIS_SEJ_HOSP<='",FinExtraction,"'
            GROUP BY ID) AS C5_1
            ON  C5_1.ID=FIPA.BENF_NO_INDIV_BEN_BANLS

            LEFT JOIN (SELECT SHOP_NO_INDIV_BEN_BANLS as ID,
            MIN ( SHOP_DAT_DEPAR_SEJ_HOSP) as  DATE_C5_2
            FROM  RES_SSS.V_SEJ_HOSP_CM AS VSER
            WHERE  SHOP_TYP_LIEU_SEJ_HOSP_DESTI in (3,6,8,10,14,15,20,40) AND
            SHOP_DAT_DEPAR_SEJ_HOSP <='",FinExtraction,"'
            GROUP BY ID) AS C5_2
            ON C5_2.ID=FIPA.BENF_NO_INDIV_BEN_BANLS

            LEFT JOIN ( SELECT SHOP_NO_INDIV_BEN_BANLS as  ID,
            min( SHOP_DAT_ADMIS_SEJ_HOSP ) AS DATE_C6
            FROM  RES_SSS.V_SEJ_SERV_HOSP_CM AS VSER
            WHERE  SHOP_TYP_SEJ_SERV_HOSP =3
            AND SHOP_DAT_ADMIS_SEJ_HOSP<='",FinExtraction,"'
            GROUP BY 1)	AS C6
            ON C6.ID=FIPA.BENF_NO_INDIV_BEN_BANLS

             "))

  DSLD<-as.data.table(DSLD)
  DSLD<-DSLD[,DDsld:=pmin(DATE_C1_1,
                          DATE_C1_2,
                          DATE_C2_1,
                          DATE_C2_2,
                          DATE_C3,
                          DATE_C4,
                          DATE_C5_1,
                          DATE_C5_2,
                          DATE_C6,na.rm =T)]
  #column<-c("ID","DDsld")
  #DSLD<-DSLD[,column,with=FALSE]
  attr(DSLD, "date_extraction")<-Sys.time()

  #### mettre `DDextract` et `DFextract` en attribut au lieu de variable.
  attr(DSLD, "dates_extract") <- list(
    fin = FinExtraction
  )
  ####

  DSLD <- DSLD[!is.na(DSLD$DDsld), ] # droper les rows ou il y a des NAs dans la colonne DDsld

  if(is.character(savefile))  # sauvegarder DT sur le disque dur
    saveRDS(DSLD, savefile)
  return( DSLD)

}