goosegle

cuadro de mando auditorias

Jan 14th, 2021
392
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT DISTINCT PC.Nombre AS PlanControl, PC.AnyoDelPlan, MT.Nombre AS Marco, C.ID, Lote.Nombre AS Lote, C.Nombre, W.StateKey AS Estado, I.Nombre AS Informe, Procedimientos, Realizados, Terminados
  2. FROM ControlAuditoria C
  3.     INNER JOIN LoteControl LC ON (C.ID = LC.ControlAuditoriaId)
  4.     INNER JOIN Lote ON (LC.LoteId = Lote.ID)
  5.     INNER JOIN PlanControl PC ON (PC.ID = Lote.PlanControlId)
  6.     INNER JOIN MarcosTrabajo MT ON (MT.ID = PC.MarcoTrabajoId)
  7.     INNER JOIN WorkflowPersistence W ON (W.EntityId = C.ID)
  8.     INNER JOIN (SELECT CA.ID AS ID, COUNT(P.ID) AS Procedimientos, COUNT(P.RealizadoPorId) AS Realizados, COUNT(P.RevisadoPorId) AS Terminados
  9.                 FROM ControlAuditoria CA
  10.                     INNER JOIN ProgramaTrabajo PT ON (CA.ProgramaTrabajo_ID = PT.ID)
  11.                     INNER JOIN Objetivos O ON (O.ProgramaTrabajoId = PT.ID)
  12.                     INNER JOIN Procedimientos P ON (P.ObjetivoId = O.ID)
  13.                 WHERE P.NoAplica = 0
  14.                 GROUP BY CA.ID, CA.Nombre) PR ON (PR.ID = C.ID)
  15.     LEFT JOIN (SELECT CV.Nombre, VA.ControlAuditoriaId
  16.                FROM DefinicionVersionInforme CV
  17.                    LEFT JOIN VersionInformeAuditoria VA ON (VA.DefinicionVersionInforme_ID = CV.ID)
  18.                    WHERE VA.ID = (SELECT MAX(ID) FROM VersionInformeAuditoria V2
  19.                                   WHERE V2.ControlAuditoriaId = VA.ControlAuditoriaId GROUP BY V2.ControlAuditoriaId)) I ON (I.ControlAuditoriaId = C.ID)
  20. WHERE MT.IsDeleted = 0
  21.     AND PC.IsDeleted = 0
  22.     AND C.IsDeleted = 0
  23.     AND W.WorkflowTypeIdentifier = 'controlauditoria'
  24. ORDER BY PC.Nombre, PC.AnyoDelPlan DESC, MT.Nombre
RAW Paste Data