search

Sudwest-Fryslan / OpenZaakBrug

Een vertaler om de bestaande applicaties, die het oude zds-standaard praten, toch met een zgw-zaaksysteem te laten werken.
European Union Public License 1.2
5 stars 7 forks source link

Performance hack - Bypasses zgw/cmis/.. when geefLijstZaakdocumenten_Lv01 is used and directly use database #281

Closed EduardWitteveen closed 2 years ago

EduardWitteveen commented 3 years ago

The Open Zaakbrug could perform a query to the database, instead of using the correct and nice zgw communications. This means that the xml is created with the output of the sql below.

SELECT
  DOSSIER.DOSSIER_ID,
  DOSSIER.DOSSIER_OMS,
  DOSSIER.DAT_AAN,
  DOSSIER.GEBR_AAN,
  DOSSIER.GEBR_BEW,
  DOSSIER.U##SDOSS_ID,
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = DOSSIER.DOSSIER_ID AND U##OBJECT_TYPE = 'D' AND U##kenmerk_ID = 'CMISNAME') AS CMISNAME,
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = DOSSIER.DOSSIER_ID AND U##OBJECT_TYPE = 'D' AND U##kenmerk_ID = 'CMISTYPD') AS CMISTYPD,
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = DOSSIER.DOSSIER_ID AND U##OBJECT_TYPE = 'D' AND U##kenmerk_ID = 'PATHDOS') AS PATHDOS,
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = DOSSIER.DOSSIER_ID AND U##OBJECT_TYPE = 'D' AND U##kenmerk_ID = 'REPIDFOL') AS REPIDFOL,
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = DOSSIER.DOSSIER_ID AND U##OBJECT_TYPE = 'D' AND U##kenmerk_ID = 'ZKBASTYP') AS ZKBASTYP,
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = DOSSIER.DOSSIER_ID AND U##OBJECT_TYPE = 'D' AND U##kenmerk_ID = 'ZKNR') AS ZKNR,
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = DOSSIER.DOSSIER_ID AND U##OBJECT_TYPE = 'D' AND U##kenmerk_ID = 'ZKZAKBO') AS ZKZAKBO,
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = DOSSIER.DOSSIER_ID AND U##OBJECT_TYPE = 'D' AND U##kenmerk_ID = 'ZKZAKURL') AS ZKZAKURL,
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = DOSSIER.DOSSIER_ID AND U##OBJECT_TYPE = 'D' AND U##kenmerk_ID = 'ZKZKZTPU') AS ZKZKZTPU,
  poststuk.poststuk_id,
  poststuk.onderwerp,
  poststuk.DAT_AAN,
  poststuk.DAT_BEW,
  poststuk.GEBR_AAN,
  poststuk.GEBR_BEW,
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = poststuk.poststuk_id AND U##OBJECT_TYPE = 'S' AND U##kenmerk_ID = 'CMISTYPS') AS CMISTYPS,
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = poststuk.poststuk_id AND U##OBJECT_TYPE = 'S' AND U##kenmerk_ID = 'REPIDDOC') AS REPIDDOC, 
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = poststuk.poststuk_id AND U##OBJECT_TYPE = 'S' AND U##kenmerk_ID = 'ZKAUTEUR') AS ZKAUTEUR,  
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = poststuk.poststuk_id AND U##OBJECT_TYPE = 'S' AND U##kenmerk_ID = 'ZKBATYPE') AS ZKBATYPE, 
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = poststuk.poststuk_id AND U##OBJECT_TYPE = 'S' AND U##kenmerk_ID = 'ZKBESTNM') AS ZKBESTNM, 
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = poststuk.poststuk_id AND U##OBJECT_TYPE = 'S' AND U##kenmerk_ID = 'ZKBGREG') AS ZKBGREG,
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = poststuk.poststuk_id AND U##OBJECT_TYPE = 'S' AND U##kenmerk_ID = 'ZKBRNORG') AS ZKBRNORG,  
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = poststuk.poststuk_id AND U##OBJECT_TYPE = 'S' AND U##kenmerk_ID = 'ZKDOCCR') AS ZKDOCCR,
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = poststuk.poststuk_id AND U##OBJECT_TYPE = 'S' AND U##kenmerk_ID = 'ZKDOCFOR') AS ZKDOCFOR,  
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = poststuk.poststuk_id AND U##OBJECT_TYPE = 'S' AND U##kenmerk_ID = 'ZKDOCID') AS ZKDOCID,
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = poststuk.poststuk_id AND U##OBJECT_TYPE = 'S' AND U##kenmerk_ID = 'ZKDOCTIT') AS ZKDOCTIT,  
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = poststuk.poststuk_id AND U##OBJECT_TYPE = 'S' AND U##kenmerk_ID = 'ZKDOCUID') AS ZKDOCUID,  
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = poststuk.poststuk_id AND U##OBJECT_TYPE = 'S' AND U##kenmerk_ID = 'ZKDOCVER') AS ZKDOCVER,  
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = poststuk.poststuk_id AND U##OBJECT_TYPE = 'S' AND U##kenmerk_ID = 'ZKIGR') AS ZKIGR,  
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = poststuk.poststuk_id AND U##OBJECT_TYPE = 'S' AND U##kenmerk_ID = 'ZKIOBJTP') AS ZKIOBJTP,  
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = poststuk.poststuk_id AND U##OBJECT_TYPE = 'S' AND U##kenmerk_ID = 'ZKLATVER') AS ZKLATVER,  
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = poststuk.poststuk_id AND U##OBJECT_TYPE = 'S' AND U##kenmerk_ID = 'ZKLMVER') AS ZKLMVER,  
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = poststuk.poststuk_id AND U##OBJECT_TYPE = 'S' AND U##kenmerk_ID = 'ZKMAJVER') AS ZKMAJVER,  
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = poststuk.poststuk_id AND U##OBJECT_TYPE = 'S' AND U##kenmerk_ID = 'ZKTAAL') AS ZKTAAL,  
  (SELECT waarde FROM CORSA_PROD.OBJ_KENM WHERE OBJ_KENM.U##OBJECT_ID = poststuk.poststuk_id AND U##OBJECT_TYPE = 'S' AND U##kenmerk_ID = 'ZKVERT') AS ZKVERT
FROM CORSA_PROD.DOSSIER
JOIN CORSA_PROD.STUK_DOSS
       ON STUK_DOSS.U##DOSSIER_ID = DOSSIER.DOSSIER_ID
JOIN corsa_prod.poststuk poststuk
       ON poststuk.U##SOORT_ID = 'ZGWDOCS' 
       AND poststuk.U##POSTSTUK_ID = STUK_DOSS.U##POSTSTUK_ID
WHERE DOSSIER.U##SDOSS_ID = 'ZGWZKDOS' 
AND DOSSIER.U##DOSSIER_ID = (SELECT U##OBJECT_ID FROM CORSA_PROD.OBJ_KENM WHERE U##OBJECT_TYPE = 'D' AND U##kenmerk_ID = 'ZKZAKURL' AND U##WAARDE = UPPER('https://oz.prod/zaken/api/v1/zaken/4c822916-dacc-4158-ab47-a6a75f41810e'))
EduardWitteveen commented 2 years ago

can this one be closed?

alisihab commented 2 years ago

Implemented in OpenZaakBrugExtensions project