R/AlleVarNum.R

In Rapporteket/nger: Resultatrapporter for NGER

#' Lager AlleVarNum
#'
#' @return Returnerer AlleVarNum
#' @export
#'

AlleVarNum <- function(datoFra = '2013-01-01' , datoTil = Sys.Date()) {

 query <- paste0('SELECT
  patient.ID as PasientID,
  patient.REGISTERED_DATE as PasRegDato,
  patient.SSN as PersonNr,
  patient.SSN_TYPE as PersonNrType,
  patient.BIRTH_DATE as FodselsDato,
  patient.NATIVE_LANGUAGE as Morsmaal,
  patient.NATIVE_LANGUAGE_OTHER as MorsmaalAnnet,
  patient.NORWEGIAN as Norsktalende,
  patient.EDUCATION as Utdanning,
  patient.DECEASED as Avdod,
  patient.DECEASED_DATE as AvdodDato,
  patient.MARITAL_STATUS as SivilStatus,
  mce.MCEID as ForlopsID,
  mce.CENTREID AS AvdRESH,
  mce.CALLS_MADE as RingtGanger,
  mce.DELIVERY_DATE as Leveringsdato,
  mce.REVISION as RegisterRevisjon,
  operation.HEIGHT AS OpHoyde,
  operation.WEIGHT AS OpVekt,
  operation.BMI AS OpBMI,
  operation.PARITIES AS OpPariteter,
  operation.ADDR_TYPE AS OpAdrType,
  operation.ADDRESS AS OpAdr,
  operation.ZIPCODE AS OpPostnr,
  operation.TOWN AS OpPoststed,
  operation.MUNICIPALITY_NUMBER AS OpKomNr,
  operation.MUNICIPALITY_NAME AS OpKomNavn,
  operation.COUNTY AS OpFylke,
  operation.FREEFORM_ADDR AS OpAdresse,
  operation.EARLIER_VAGINAL AS OpTidlVagInngrep,
  operation.EARLIER_LAPAROSCOPY AS OpTidlLapsko,
  operation.LAPARASCOPY_COUNT AS OpTidlLapskoNum,
  operation.EARLIER_LAPAROTOMY AS OpTidlLaparotomi,
  operation.LAPAROTOMI_COUNT AS OpTidlLaparotomiNum,
  operation.SECTIO_COUNT AS OpSectioNum,
  operation.BLOOD_THINNERS AS OpBlodfortynnende,
  operation.OP_DATE AS OpDato,
  operation.OPTYPE AS OpType,
  operation.COMPLICATION AS OpArsakReopKomp,
  operation.MAIN_OPERATION AS OpHovedOperasjon,
  operation.COMPLICATION_TYPE AS OpKomplType,
  operation.OPCAT AS OpKategori,
  operation.OPCAT_OUTSIDE_DAYTIME AS OpIVaktTid,
  operation.MCETYPE AS OpMetode,
  operation.CARE_LEVEL AS OpBehNivaa,
  operation.OP_INDICATION1 AS OpIndikasjon1,
  operation.OP_INDICATION1_VERSION AS OpIndikasjon1Ver,
  operation.OP_INDICATION2 AS OpIndikasjon2,
  operation.OP_INDICATION2_VERSION AS OpIndikasjon2Ver,
  operation.OP_INDICATION3 AS OpIndikasjon3,
  operation.OP_INDICATION3_VERSION AS OpIndikasjon3Ver,
  operation.ANESTHESIA_NONE AS OpAnestesiIngen,
  operation.ANESTHESIA_LOCAL AS OpAnestesiLok,
  operation.ANESTHESIA_GENERAL AS OpAnestesiGen,
  operation.ANESTHESIA_SPINAL_EDA AS OpAnestesiSpinEDA,
  operation.ANESTHESIA_SEDATION AS OpAnestesiSed,
  operation.ASA AS OpASA,
  operation.OPTIME_COUNT AS OpTid,
  operation.ANTIBIOTIC_PROPHYLAXIS AS OpAntibProfylakse,
  operation.SURVIVED AS OpOverlevd,
  operation.FIRST_TIME_CLOSED as OpForstLukket,
  operation.STATUS as OpStatus,
  laparoscopy.DIAGNOSIS1 AS LapDiagnose1,
  laparoscopy.DIAGNOSIS1_VERSION AS LapDiagnose1Ver,
  laparoscopy.DIAGNOSIS2 AS LapDiagnose2,
  laparoscopy.DIAGNOSIS2_VERSION AS LapDiagnose2Ver,
  laparoscopy.DIAGNOSIS3 AS LapDiagnose3,
  laparoscopy.DIAGNOSIS3_VERSION AS LapDiagnose3Ver,
  laparoscopy.DIAGNOSIS4 AS LapDiagnose4,
  laparoscopy.DIAGNOSIS4_VERSION AS LapDiagnose4Ver,
  laparoscopy.PROCEDURE1 AS LapProsedyre1,
  laparoscopy.PROCEDURE1_VERSION AS LapProsedyre1Ver,
  laparoscopy.PROCEDURE2 AS LapProsedyre2,
  laparoscopy.PROCEDURE2_VERSION AS LapProsedyre2Ver,
  laparoscopy.PROCEDURE3 AS LapProsedyre3,
  laparoscopy.PROCEDURE3_VERSION AS LapProsedyre3Ver,
  laparoscopy.PROCEDURE4 AS LapProsedyre4,
  laparoscopy.PROCEDURE4_VERSION AS LapProsedyre4Ver,
  laparoscopy.ACCESS_METHOD AS LapTilgangsMetode,
  laparoscopy.OPTICALTROCAR AS LapOptTro,
  laparoscopy.ACCESS AS LapTilgang,
  laparoscopy.NUM_HJELPEINNSTIKK AS LapNumHjelpeinnstikk,
  laparoscopy.SINGELPORT AS LapSingelPort,
  laparoscopy.BIPOLAR_DIATERMI AS LapBipolarDiatermi,
  laparoscopy.UNIPOLAR_DIATERMI AS LapUnipolarDiatermi,
  laparoscopy.ADVANCED_TISSUE_SEALER AS LapVevforsegl,
  laparoscopy.MORCELLATOR AS LapMorcellatorUtenPose,
  laparoscopy.SUTUR AS LapSutur,
  laparoscopy.CLIPS AS LapClips,
  laparoscopy.NETT AS LapNett,
  laparoscopy.ADHERANSEPROFYLAXE AS LapAdherProfylakse,
  laparoscopy.HAEMOSTASIS_AGENT AS LapHemastase,
  laparoscopy.STAPLER_ENDOGIA AS LapStaplerEndogia,
  laparoscopy.PREPARATOPOSE AS LapPreparatopose,
  laparoscopy.UTERUSMANIPULATOR AS LapUterusman,
  laparoscopy.ROBOT_KIRURGI AS LapRobotKirurgi,
  laparoscopy.OTHER AS LapAndre,
  laparoscopy.MEDICATIONWITHDRAWAL AS LapPrepUttak,
  laparoscopy.PREPARATION_SPLIT_AT_WITHDRAWAL AS LapPrepOppdel,
  laparoscopy.COMPLICATIONS AS LapKomplikasjoner,
  laparoscopy.COMPLICATIONS_VASCULAR AS LapKomplKar,
  laparoscopy.COMPLICATIONS_INTESTINE AS LapKomplTarm,
  laparoscopy.COMPLICATIONS_BLADDER AS LapKomplBlaere,
  laparoscopy.COMPLICATIONS_URETHRA AS LapKomplUreter,
  laparoscopy.COMPLICATIONS_OTHER AS LapKomplAnnet,
  laparoscopy.COMPLICATIONS_AROSE_ACCESS AS LapSkadeTilgang,
  laparoscopy.COMPLICATIONS_AROSE_EXTRACTION AS LapSkadeUthent,
  laparoscopy.COMPLICATIONS_AROSE_DISSECTION AS LapSkadeDissek,
  laparoscopy.COMPLICATIONS_AROSE_VESSEL_SEALING AS LapSkadeForsegl,
  laparoscopy.COMPLICATIONS_AROSE_OTHER AS LapSkadeAnnet,
  laparoscopy.COMPLICATIONS_CONTRIBUTING_TECHNICAL AS LapSkadeaarsakTeknUtst,
  laparoscopy.COMPLICATIONS_CONTRIBUTING_ADHESIONS AS LapSkadeaarsakAdher,
  laparoscopy.COMPLICATIONS_CONTRIBUTING_PREVIOUS_SURGERY AS LapSkadeaarsakTidlKir,
  laparoscopy.COMPLICATIONS_CONTRIBUTING_OTHER AS LapSkadeaarsakAnnet,
  laparoscopy.PORTIOADAPTER AS LapUterusmanipulatorFJERNET,
  laparoscopy.TILGANG AS LapKompTilgang,
  laparoscopy.HJELPEINSTIKK AS LapHjelpeinnstikk,
  laparoscopy.INTRAABDOMINAL AS LapIntraabdominell,
  laparoscopy.TEKNISK_UTSTYR AS LapTekniskUtstyr,
  laparoscopy.POSTOPERATIV AS LapPostoperativ,
  laparoscopy.KAR_BLEED AS LapKarBlodning,
  laparoscopy.TARM AS LapTarm,
  laparoscopy.BLAERE AS LapBlare,
  laparoscopy.URETER AS LapUreter,
  laparoscopy.NERV AS LapNerv,
  laparoscopy.CONVERTED AS LapKonvertert,
  laparoscopy.CONVERTED_STATUS AS Konverteringsstatus,
  laparoscopy.STATUS AS LapStatus,
  hysteroscopy.DIAGNOSIS1 AS HysDiagnose1,
  hysteroscopy.DIAGNOSIS1_VERSION AS HysDiagnose1Ver,
  hysteroscopy.DIAGNOSIS2 AS HysDiagnose2,
  hysteroscopy.DIAGNOSIS2_VERSION AS HysDiagnose2Ver,
  hysteroscopy.DIAGNOSIS3 AS HysDiagnose3,
  hysteroscopy.DIAGNOSIS3_VERSION AS HysDiagnose3Ver,
  hysteroscopy.PROCEDURE1 AS HysProsedyre1,
  hysteroscopy.PROCEDURE1_VERSION AS HysProsedyre1Ver,
  hysteroscopy.PROCEDURE2 AS HysProsedyre2,
  hysteroscopy.PROCEDURE2_VERSION AS HysProsedyre2Ver,
  hysteroscopy.PROCEDURE3 AS HysProsedyre3,
  hysteroscopy.PROCEDURE3_VERSION AS HysProsedyre3Ver,
  hysteroscopy.BIPOLAR_DIATHERMY AS HysBipolarDiatermi,
  hysteroscopy.UNIPLOAR_DIATHERMY AS HysUnipolarDiatermi,
  hysteroscopy.MORCELLATOR AS HysMorcellator,
  hysteroscopy.ADHERENCE_PROPHYLAXIS AS HysAdProf,
  hysteroscopy.COPPER_SPIRAL AS HysKobberspir,
  hysteroscopy.HORMONAL_IUD AS HysHormonspir,
  hysteroscopy.PRETREATMENT_COMBO AS HysMedForbehCervix,
  hysteroscopy.COMPLETENESS AS HysGjforingsGrad,
  hysteroscopy.PAINS AS HysUfullSmerte,
  hysteroscopy.GASEMBOLI AS HysUfullMisGass,
  hysteroscopy.CMPLTCOMPLICATIONS AS HysUfullKompl,
  hysteroscopy.HIGH_FLUID_DEFICIT AS HysUfullHoyVaeske,
  hysteroscopy.ANNET_ALTERNATIV AS HysUfullAnnet,
  hysteroscopy.ANNEN_ARSAK AS HysUfullAnnetArsak,
  hysteroscopy.COMPLICATIONS AS HysKomplikasjoner,
  hysteroscopy.ACCESS AS HysTilgang,
  hysteroscopy.TECHNICAL AS HysTeknisk,
  hysteroscopy.PERFORATION AS HysPerforasjon,
  hysteroscopy.BLEEDING AS HysBlodning,
  hysteroscopy.FLUID_OVERLOAD AS HysFluidOverload,
  hysteroscopy.COMPLICATIONS_CERVIX_STENOSIS AS HysSkadeaarsakStenose,
  hysteroscopy.COMPLICATIONS_INTRAUTERINE_ADHESIONS AS HysSkadeaarsakAd,
  hysteroscopy.COMPLICATIONS_TECHNICAL AS HysSkadeaarsakTeknUtst,
  hysteroscopy.COMPLICATIONS_ANATOMICAL_CONDITIONS AS HysSkadeaarsakAnatomi,
  hysteroscopy.COMPLICATIONS_OTHER AS HysSkadeaarsakAnnet,
  hysteroscopy.COMPLICATIONS_TYPE_VIAFALSA AS HysKomplViaFalsa,
  hysteroscopy.COMPLICATIONS_TYPE_FLUID_DEFICIT AS HysKomplVaeske,
  hysteroscopy.COMPLICATIONS_TYPE_PERFORATION AS HysKomplPerf,
  hysteroscopy.COMPLICATIONS_TYPE_GASEMBOLI AS HysKomplGass,
  hysteroscopy.COMPLICATIONS_TYPE_BLEEDING AS HysKomplBlodn,
  hysteroscopy.COMPLICATIONS_TYPE_OTHER AS HysKomplAnnet,
  hysteroscopy.COMPLICATIONS_MEASURES_TAMPONADE AS HysKomplTiltakTamp,
  hysteroscopy.COMPLICATIONS_MEASURES_ABORTED AS HysKomplTiltakAvbr,
  hysteroscopy.COMPLICATIONS_MEASURES_OTHER AS HysKomplTiltakAnnet,
  hysteroscopy.COMPLICATIONS_MEASURES_NONE AS HysKomplTiltakIngen,
  hysteroscopy.CONVERTED AS HysKonvertert,
  hysteroscopy.CONVERTED_LAPAROSCOPY AS HysKonvLaparoskopi,
  hysteroscopy.CONVERTED_LAPROTOMY AS HysKonvLaparotomi,
  hysteroscopy.FIRST_TIME_CLOSED as HysForstLukket,
  hysteroscopy.STATUS as HysStatus,
  centreattribute.ATTRIBUTEVALUE as SykehusNavn
  FROM mce
  INNER JOIN patient ON mce.PATIENT_ID = patient.ID
  INNER JOIN operation ON mce.MCEID = operation.MCEID
  LEFT JOIN laparoscopy ON mce.MCEID = laparoscopy.MCEID
  LEFT JOIN hysteroscopy ON mce.MCEID = hysteroscopy.MCEID
  LEFT JOIN user u_op ON operation.FIRST_TIME_CLOSED_BY = u_op.ID -- ADDED
  LEFT JOIN user u_hys ON hysteroscopy.FIRST_TIME_CLOSED_BY = u_hys.ID -- ADDED
  LEFT JOIN centreattribute on mce.CENTREID = centreattribute.ID
  WHERE
  operation.STATUS = 1
  AND (laparoscopy.STATUS = 1 OR hysteroscopy.STATUS = 1)
  AND operation.OP_DATE >= \'', datoFra, '\' AND operation.OP_DATE <= \'', datoTil, '\'')

  AlleVarNum <- rapbase::loadRegData(registryName = 'data', query=query, dbType = "mysql")

  #Tatt ut:
  # CONCAT(u_op.FIRSTNAME, ' ', u_op.LASTNAME) as OpForstLukketAv, -- MODIFIED
  # CONCAT(u_hys.FIRSTNAME, ' ', u_hys.LASTNAME) as HysForstLukketAv, -- MODIFIED


  #Må legges til:
  # CASE operation.BMI_CATEGORY
  # WHEN 'Alvorlig undervekt' THEN 1
  # WHEN 'Moderat undervekt' THEN 2
  # WHEN 'Mild undervekt' THEN 3
  # WHEN 'Normal' THEN 4
  # WHEN 'Overvekt' THEN 5
  # WHEN 'Moderat fedme, klasse I' THEN 6
  # WHEN 'Fedme, klasse II' THEN 7
  # WHEN 'Fedme, klasse III' THEN 8
  # ELSE 9
  # END AS OpBMIKategori,

   }