USE [vigia_produccion]
GO
/****** Object:  StoredProcedure [app].[SPIVCVisitasTerminados_Marzo2026_PorPeriodo_OPT]    Script Date: 24/03/2026 9:00:05 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==============================================================================================
-- Author:		<jjrodriguez>
-- Create date: <24-03-2026>
-- Description:	Extrae las visita incluyendo los terminados por periodo.
-- Modification history:
-- ==============================================================================================
ALTER PROCEDURE [app].[SPIVCVisitasTerminados_Marzo2026_PorPeriodo_OPT]
(
    @Entidad INT = 0,		 
    @Linea INT = 0,
    @Localidad INT = 0,	
    @FechaD DATETIME, 
    @FechaH DATETIME
)
AS
BEGIN
    SET NOCOUNT ON;

    -----------------------------------------
    -- FECHAS
    -----------------------------------------
    DECLARE @FDesde DATE = CAST(@FechaD AS DATE);
    DECLARE @FHasta DATE = DATEADD(DAY, 1, CAST(@FechaH AS DATE));

    -----------------------------------------
    -- TABLAS TEMPORALES
    -----------------------------------------
    CREATE TABLE #Entidades(idEntidad INT PRIMARY KEY);
    CREATE TABLE #Localidades(idLocalidad INT PRIMARY KEY);
    CREATE TABLE #Linea(IdLinea INT PRIMARY KEY);

    IF @Entidad = 0
    BEGIN
        INSERT INTO #Entidades
        SELECT idTblEntidades FROM tblEntidad WHERE Id_TipoEntidad = 13  OR idTblEntidades = 1;

        INSERT INTO #Localidades
        SELECT IdTblLocalidades FROM tblLocalidades;
    END
    ELSE
    BEGIN
        INSERT INTO #Entidades VALUES (@Entidad);

        IF @Localidad = 0
            INSERT INTO #Localidades SELECT IdTblLocalidades FROM tblLocalidades;
        ELSE
            INSERT INTO #Localidades VALUES (@Localidad);
    END

    IF @Linea = 0
        INSERT INTO #Linea
        SELECT idTblOpciones FROM TblOpciones WHERE idTblListas = 1000001;
    ELSE
        INSERT INTO #Linea VALUES (@Linea);

    -----------------------------------------
    -- BASES
    -----------------------------------------
    SELECT * INTO #VIS
    FROM tblVisitasIVC
    WHERE FechaVisita >= @FDesde
      AND FechaVisita < @FHasta;

    SELECT * INTO #TER
    FROM app.tblActasMS
    WHERE FechaHoraActa >= @FDesde
      AND FechaHoraActa < @FHasta
      AND Id_TipoActa = 1001159;

    -----------------------------------------
    -- RESULTADO FINAL
    -----------------------------------------
    SELECT 
        ROW_NUMBER() OVER(
            PARTITION BY A.IdTBLEstablecimientos, A.Mes_Reporte 
            ORDER BY A.FechaVisita DESC
        ) AS NumVisitasRepetidas,
        A.*
    FROM
    (
        -----------------------------------------
        -- VISITAS
        -----------------------------------------
        SELECT  
            VIS.IdTBLEstablecimientos AS IdTBLEstablecimientos,
            VIS.txtNumeroInscripcion AS NumeroInscripcion,
            CASE VIS.ServicioPrincipal WHEN '1' THEN 'Si' ELSE 'No' END AS ServicioPrincipal,
            CASE VIS.ServicioPrestadoPorTercero WHEN '1' THEN 'Si' ELSE 'No' END AS ServicioPrestadoPorTercero,
            RTRIM(UBI.NombreUbicacionServicio) AS UbicacionA,
            VIS.IdTblVisitasIVC AS IdVisitasIVC_IdTerminados,
            VIS.EsProyectoVI AS EsProyectoVI,
            PRO.NombreProyecto AS Proyecto,
            Opc2.NombreOpcion AS TerminalPortuario,
            CASE WHEN VIS.EstablecimientoFormal = 1 THEN 'Si' ELSE 'No' END AS EstablecimientoFormal,
            CASE WHEN VIS.PresentaMatriculaEstablecimiento = 1 THEN 'Si' ELSE 'No' END AS Presenta_Matricula,
            VIS.NumeroMatriculaEstablecimiento AS MatriculaEstrablecimiento,
            VIS.NumeroTrabajadores AS NumeroTrabajadores,
            CASE WHEN VIS.HorarioDiurno= 1 THEN 'Si' ELSE 'No' END AS HorarioDiurno,
            CASE WHEN VIS.HorarioNocturno= 1 THEN 'Si' ELSE 'No' END AS HorarioNocturno,
            CASE WHEN VIS.Horario24horas= 1 THEN 'Si' ELSE 'No' END AS Horario24horas,
            VIS.HorarioOtro AS HorarioOtro,
            CASE WHEN VIS.DiasFuncionamiento_LunAVier= 1 THEN 'Si' ELSE 'No' END AS DiasFuncionamiento_LunAVier,
            CASE WHEN VIS.DiasFuncionamiento_DomADom= 1 THEN 'Si' ELSE 'No' END AS DiasFuncionamiento_DomADom,
            CASE WHEN VIS.DiasFuncionamiento_FinSem= 1 THEN 'Si' ELSE 'No' END AS DiasFuncionamiento_FinSem,
            VIS.DiasFuncionamiento_Otro AS DiasFuncionamiento_Otro,
            VIS.PuntajeTotalINVIMA AS PuntajeINVIMA,
            UPPER(Ent2.NombreEntidad) AS Entidad_Reporta,
            VIS.RazonSocial AS RazonSocial,
            VIS.NombreComercial AS NombreComercial,
            VIS.NITEstablecimiento AS NITEstablecimiento,
            CASE WHEN VIS.AreaRural = 1 THEN 'Si' ELSE 'No' END AS AreaRural,
            VIS.DireccionComercial AS DireccionComercial,
            UPPER(Loc.NombreLocalidad) AS Localidad,
            Upz.NombreUPZ AS UPZ,
            Bar.NombreBarrio AS Barrio,
            VIS.BarrioDigitado AS BarrioDigitado,
            CASE WHEN VIS.txtSede2 = 1 THEN 'Unica' ELSE 'Otro' END AS Sede,
            VIS.txtCualSede2 AS CualSede,
            Opc3.NombreOpcion AS TipoUbicacion,
            ISNULL(Opc4.NombreOpcion, Opc3.NombreOpcion) AS Ubicacion,
            VIS.Sede AS Cual_Ubicacion,
            VIS.Telefonos1 AS Telefonos,
            VIS.eMail AS Email,
            VIS.NombresPropietario AS Propietario,
            VIS.id_TipoIdentificacionPropietario AS TipoDocProp,
            VIS.NumeroIdentificacionPropietario AS DocProp,
            VIS.NombreRepLegal AS RepLegal,
            VIS.id_TipoIdentificacionRepLegal AS TipoDocRepLegal,
            VIS.NumeroIdentificacionRepLegal AS DocRepLegal,
            CASE TipEST.Riesgo WHEN '1' THEN 'Riesgo Bajo' WHEN '2' THEN 'Riesgo Alto' ELSE 'No Aplica' END AS Riesgo,
            UPPER(Opc.NombreOpcion) AS Linea,
            UPPER(Inte.Nombre) AS Intervencion,
            UPPER(TipEst.NombreTipoEstablecimiento) AS TipoEstablecimiento,
            UPPER(Ciiu.Descripcion) AS ActividadEconomica,
            Ciiu.CodCIIU AS CIIU,
            VIS.NumeroActa AS NumeroActa,
            VIS.NumeroCarpeta AS NumeroCarpeta,
            Opc5.NombreOpcion AS MotivoVisita,
            VIS.Radicado AS RadicadoSolicitud,
            VIS.fechaRadicado AS FechaRadicadoSolicitud,
            VIS.FechaVisita AS FechaVisita,
            FORMAT(VIS.FechaVisita,'MM/yyyy') AS Mes_Reporte,
            YEAR(VIS.FechaVisita) AS Año_Reporte,
            VIS.id_Concepto AS Concepto,
            VIS.Exigencias AS Exigencias,
            VIS.RadicadoSDS AS Radicado,
            VIS.FechaRadicadoSDS AS FechaRadicado,
            CASE WHEN CAST(VIS.MS_Establecimiento AS INT) + CAST(VIS.MS_Producto AS INT) > 0 THEN 'Si' ELSE 'No' END AS Aplico_Medida,
            VIS.NombreFuncionarioSalud1 AS Funcionario1,
            VIS.Id_TipoDocFuncionarioSalud1 AS TipoDocF1,
            VIS.DocNumFuncionarioSalud1 AS DocF1,
            VIS.CargoFuncionarioSalud1 AS CargoF1,
            VIS.NombreFuncionarioSalud2 AS Funcionario2,
            VIS.Id_TipoDocFuncionarioSalud2 AS TipoDocF2,
            VIS.DocNumFuncionarioSalud2 AS DocF2,
            VIS.CargoFuncionarioSalud2 AS CargoF2,
            RTRIM(Usu.Nombre1)+' '+RTRIM(Usu.Apellido1) AS FuncionarioCreo,
            VIS.FechaCreacionRegistro AS FechaCreacion,
            CASE VIS.SPPFacturarVisita WHEN '1' THEN 'Si' ELSE 'No' END AS SPPFacturarVisita,
            VigSPP.CodVigencia AS CodVigencia,
            LocSPP.NombreLocalidad AS LocalidadFactura,
            INTSPP.Nombre AS SPPIntervencionFactura

        FROM #VIS VIS
        INNER JOIN tblUsuarios Usu ON VIS.idTblUsuarioCreoRegistro = Usu.idTblUsuarios
        INNER JOIN tblEntidad Ent2 ON Usu.IdTblEntidades = Ent2.idTblEntidades
        INNER JOIN #Entidades E ON Ent2.idTblEntidades = E.idEntidad
        LEFT JOIN tblLocalidades Loc ON VIS.IdTblLocalidades = Loc.IdTblLocalidades
        INNER JOIN #Localidades L ON Loc.IdTblLocalidades = L.idLocalidad
        LEFT JOIN tblUpz Upz ON VIS.idTblUpz = Upz.IdTblUpz
        LEFT JOIN tblBarrios Bar ON VIS.idTblBarrios = Bar.IdTblBarrios
        LEFT JOIN tblIntervencionPAB Inte ON VIS.idTblIntervencionPAB = Inte.IdTblIntervencionPAB
        LEFT JOIN TblOpciones Opc ON Inte.id_LineaAmbiente = Opc.idTblOpciones
        INNER JOIN #Linea LN ON Opc.idTblOpciones = LN.IdLinea
        LEFT JOIN tblTipoEstablecimiento TipEST ON VIS.idtblTipoEstablecimiento = TipEST.IdTipoEstablecimiento
        LEFT JOIN tblCIIU Ciiu ON VIS.idTblCIIU = Ciiu.IdTblCIIU
        LEFT JOIN TblOpciones Opc2 ON VIS.Id_TerminalPortuaria = Opc2.idTblOpciones
        LEFT JOIN TblOpciones Opc3 ON VIS.Id_TipoSede = Opc3.idTblOpciones
        LEFT JOIN TblOpciones Opc4 ON VIS.Id_Sede = Opc4.idTblOpciones
        INNER JOIN TblOpciones Opc5 ON VIS.Id_MotivoVisita = Opc5.idTextoOpcion AND Opc5.idTblListas = 2000025
        LEFT JOIN tblUbicacionesServiciosIVC UBI ON UBI.IdTblUbicacionServicio = VIS.IdTblUbicacionServicio
        LEFT JOIN tblProyectosVI PRO ON PRO.idTblProyectoVI = VIS.IdTblProyectoVI
        LEFT JOIN tblIntervencionPAB INTSPP ON VIS.SPPidIntervencionFactura = INTSPP.IdTblIntervencionPAB
        LEFT JOIN tblSPPVigencias VigSPP ON VIS.IdTblSPPVigencias = VigSPP.IdTblSPPVigencias
        LEFT JOIN tblLocalidades LocSPP ON VIS.SPPidlocalidadFactura = LocSPP.IdTblLocalidades

        UNION ALL

        -----------------------------------------
        -- ACTAS (ALINEADO)
        -----------------------------------------
        SELECT  
            TER.IdEstablecimientos AS IdTBLEstablecimientos,
            TER.txtNumeroInscripcion AS NumeroInscripcion,
            CASE TER.ServicioPrincipal WHEN '1' THEN 'Si' ELSE 'No' END AS ServicioPrincipal,
            CASE TER.ServicioPrestadoPorTercero WHEN '1' THEN 'Si' ELSE 'No' END AS ServicioPrestadoPorTercero,
            RTRIM(UBI.NombreUbicacionServicio) AS UbicacionA,
            TER.IdTblActasMS AS IdVisitasIVC_IdTerminados,
            0 AS EsProyectoVI,
            '' AS Proyecto,
            Opc2.NombreOpcion AS TerminalPortuario,
            CASE WHEN EST.EstablecimientoFormal=1 THEN 'Si' ELSE 'No' END AS EstablecimientoFormal,
            CASE WHEN EST.PresentaMatriculaEstablecimiento=1 THEN 'Si' ELSE 'No' END AS Presenta_Matricula,
            EST.NumeroMatriculaEstablecimiento AS MatriculaEstrablecimiento,
            EST.NumeroTrabajadores AS NumeroTrabajadores,
            CASE WHEN EST.HorarioDiurno=1 THEN 'Si' ELSE 'No' END AS HorarioDiurno,
            CASE WHEN EST.HorarioNocturno=1 THEN 'Si' ELSE 'No' END AS HorarioNocturno,
            CASE WHEN EST.Horario24horas=1 THEN 'Si' ELSE 'No' END AS Horario24horas,
            EST.HorarioOtro AS HorarioOtro,
            CASE WHEN EST.DiasFuncionamiento_LunAVier=1 THEN 'Si' ELSE 'No' END AS DiasFuncionamiento_LunAVier,
            CASE WHEN EST.DiasFuncionamiento_DomADom=1 THEN 'Si' ELSE 'No' END AS DiasFuncionamiento_DomADom,
            CASE WHEN EST.DiasFuncionamiento_FinSem=1 THEN 'Si' ELSE 'No' END AS DiasFuncionamiento_FinSem,
            EST.DiasFuncionamiento_Otro AS DiasFuncionamiento_Otro,
            0 AS PuntajeINVIMA,
            UPPER(Ent2.NombreEntidad) AS Entidad_Reporta,
            EST.RazonSocial AS RazonSocial,
            EST.NombreComercial AS NombreComercial,
            EST.NITEstablecimiento AS NITEstablecimiento,
            CASE WHEN EST.AreaRural=1 THEN 'Si' ELSE 'No' END AS AreaRural,
            EST.DireccionComercial AS DireccionComercial,
            UPPER(Loc.NombreLocalidad) AS Localidad,
            Upz.NombreUPZ AS UPZ,
            Bar.NombreBarrio AS Barrio,
            EST.BarrioDigitado AS BarrioDigitado,
            CASE WHEN TER.txtSede2=1 THEN 'Unica' ELSE 'Otro' END AS Sede,
            TER.txtCualSede2 AS CualSede,
            Opc3.NombreOpcion AS TipoUbicacion,
            ISNULL(Opc4.NombreOpcion,Opc3.NombreOpcion) AS Ubicacion,
            EST.Sede AS Cual_Ubicacion,
            EST.Telefonos1 AS Telefonos,
            EST.eMail AS Email,
            EST.NombresPropietario AS Propietario,
            EST.id_TipoIdentificacionPropietario AS TipoDocProp,
            EST.NumeroIdentificacionPropietario AS DocProp,
            EST.NombreRepLegal AS RepLegal,
            EST.id_TipoIdentificacionRepLegal AS TipoDocRepLegal,
            EST.NumeroIdentificacionRepLegal AS DocRepLegal,
            CASE TipEST.Riesgo WHEN '1' THEN 'Riesgo Bajo' WHEN '2' THEN 'Riesgo Alto' ELSE 'No Aplica' END AS Riesgo,
            UPPER(Opc.NombreOpcion) AS Linea,
            UPPER(Inte.Nombre) AS Intervencion,
            UPPER(TipEst.NombreTipoEstablecimiento) AS TipoEstablecimiento,
            UPPER(Ciiu.Descripcion) AS ActividadEconomica,
            Ciiu.CodCIIU AS CIIU,
            TER.NroActa AS NumeroActa,
            '' AS NumeroCarpeta,
            Opc5.NombreOpcion AS MotivoVisita,
            '' AS RadicadoSolicitud,
            '' AS FechaRadicadoSolicitud,
            TER.FechaHoraActa AS FechaVisita,
            FORMAT(TER.FechaHoraActa,'MM/yyyy') AS Mes_Reporte,
            YEAR(TER.FechaHoraActa) AS Año_Reporte,
            'Ter' AS Concepto,
            '' AS Exigencias,
            '' AS Radicado,
            '' AS FechaRadicado,
            'NO' AS Aplico_Medida,
            TER.NombreUsuarioSalud AS Funcionario1,
            TER.Id_TipoIdUsuarioSalud AS TipoDocF1,
            TER.NroIdUsuarioSalud AS DocF1,
            TER.CargoUsuarioSalud AS CargoF1,
            TER.NombreUsuarioSalud2 AS Funcionario2,
            TER.Id_TipoIdUsuarioSalud2 AS TipoDocF2,
            TER.NroIdUsuarioSalud2 AS DocF2,
            TER.CargoUsuarioSalud2 AS CargoF2,
            RTRIM(Usu.Nombre1)+' '+RTRIM(Usu.Apellido1) AS FuncionarioCreo,
            TER.FechaCreacion AS FechaCreacion,
            CASE TER.SPPFacturarVisita WHEN '1' THEN 'Si' ELSE 'No' END AS SPPFacturarVisita,
            VigSPP.CodVigencia AS CodVigencia,
            LocSPP.NombreLocalidad AS LocalidadFactura,
            INTSPP.Nombre AS SPPIntervencionFactura

        FROM #TER TER
        INNER JOIN tblEstablecimientos EST ON TER.IdEstablecimientos=EST.IdEstablecimientos
        INNER JOIN tblUsuarios Usu ON TER.IdUsuarioCreaRegistro=Usu.idTblUsuarios
        INNER JOIN tblEntidad Ent2 ON Usu.IdTblEntidades=Ent2.idTblEntidades
        INNER JOIN #Entidades E ON Ent2.idTblEntidades=E.idEntidad
        LEFT JOIN tblLocalidades Loc ON EST.IdTblLocalidades=Loc.IdTblLocalidades
        INNER JOIN #Localidades L ON Loc.IdTblLocalidades=L.idLocalidad
        LEFT JOIN tblUpz Upz ON EST.idTblUpz=Upz.IdTblUpz
        LEFT JOIN tblBarrios Bar ON EST.idTblBarrios=Bar.IdTblBarrios
        LEFT JOIN tblIntervencionPAB Inte ON EST.idTblIntervencionPAB=Inte.IdTblIntervencionPAB
        LEFT JOIN TblOpciones Opc ON Inte.id_LineaAmbiente=Opc.idTblOpciones
        INNER JOIN #Linea LN ON Opc.idTblOpciones=LN.IdLinea
        LEFT JOIN tblTipoEstablecimiento TipEST ON EST.idTblTipoEstablecimiento=TipEST.IdTipoEstablecimiento
        LEFT JOIN tblCIIU Ciiu ON EST.idTblCIIU=Ciiu.IdTblCIIU
        LEFT JOIN TblOpciones Opc2 ON EST.Id_TerminalPortuaria=Opc2.idTblOpciones
        LEFT JOIN TblOpciones Opc3 ON EST.Id_TipoSede=Opc3.idTblOpciones
        LEFT JOIN TblOpciones Opc4 ON EST.Id_Sede=Opc4.idTblOpciones
        LEFT JOIN tblUbicacionesServiciosIVC UBI ON UBI.IdTblUbicacionServicio=TER.IdUbicacionServicio
        LEFT JOIN tblIntervencionPAB INTSPP ON TER.SPPidIntervencionFactura=INTSPP.IdTblIntervencionPAB
        LEFT JOIN tblSPPVigencias VigSPP ON TER.IdTblSPPVigencias=VigSPP.IdTblSPPVigencias
        LEFT JOIN tblLocalidades LocSPP ON TER.SPPidlocalidadFactura=LocSPP.IdTblLocalidades
        INNER JOIN TblOpciones Opc5 ON TER.DatosActa.value(N'(/NewDataSet/DatosActa/Id_MotivoVisita)[1]','varchar(20)')=Opc5.idTextoOpcion AND Opc5.idTblListas=2000025

    ) A
    ORDER BY A.Entidad_Reporta, A.IdTBLEstablecimientos;

END