ALTER PROCEDURE [dbo].[sp_remPagoImposicionesFonasa]
(
	@PERIODO INT = 0,
	@MES INT = 0,
	@ficini INT = 0,
	@ficfin INT = 0,
	@unineg INT = 0,
	@DEP INT = 0,
	@SEC INT = 0
)
AS
BEGIN

	SELECT 
	T.ficha,
	T.rut, T.dv, T.apePaterno, T.apeMaterno, T.nombres, 
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 12) AS  'Dias Trabajados',
	'AFP' AS 'Entidad Previsional',	
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 36) AS  'Remuneracion Imponible',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 7) AS  'Cotizacion 7%',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 36) * (CONVERT(DECIMAL(18, 4), (SELECT REPLACE(valor, ',', '.') FROM remFactores WHERE periodo = @PERIODO AND mes = @MES AND codigo = 100 ))/100) AS 'Cotizacion 3.9%',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 36) * 0.034 AS 'Cotizacion 3.4%',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 36) * 0.0055 AS 'Cotizacion 0.55%'
	INTO #FON
	FROM
	remTrabajadores AS T
	WHERE T.periodo = @PERIODO 
	AND	T.mes = @MES
	AND	(T.ficha BETWEEN @ficini AND @ficfin)
	AND (@unineg = 0 or T.unidadNegocios = @unineg) 
	AND	(@DEP = 0 or T.departamento = @DEP)
	AND (@SEC = 0 or T.seccion = @SEC)
	AND	T.activo = 1 
	AND	T.instSalud = 7
	ORDER BY T.apePaterno,
		T.apeMaterno

	SELECT 
		T.ficha,
		ISNULL (MP.codMovimiento, 0) AS Codigo,
		ISNULL (MP.fechaInicio, '') AS FechaIni,
		ISNULL (MP.fechaTermino, '') AS FechaFin
	INTO #MOV
	FROM
		remMovPersonal AS MP JOIN remTrabajadores AS T
		ON MP.periodo = T.periodo AND MP.mes = T.mes AND MP.ficha = T.ficha
	WHERE T.periodo = @PERIODO
		AND T.mes = @MES
		AND (T.ficha BETWEEN @ficini AND @ficfin)
		AND (@unineg = 0 or T.unidadNegocios = @unineg) 
		AND	(@DEP = 0 or T.departamento = @DEP)
		AND (@SEC = 0 or T.seccion = @SEC)
		AND T.activo = 1

	--SELECT * FROM #FON AS F INNER JOIN #MOV AS M ON F.ficha = M.ficha
	IF (@PERIODO = 2020 AND @MES < 12) OR @PERIODO < 2020
		BEGIN
			SELECT
				ROW_NUMBER() OVER(PARTITION BY F.ficha ORDER BY F.ficha)as numlin,
				1 AS validador,
				F.ficha,
				F.rut,
				F.dv,
				F.apePaterno,
				F.apeMaterno,
				F.nombres,
				F.[Dias Trabajados],
				F.[Entidad Previsional],			
				F.[Remuneracion Imponible],
				CASE  
				WHEN (SELECT caja FROM remParametros) = 0 THEN F.[Cotizacion 7%]
				WHEN (SELECT caja FROM remParametros) > 0 THEN F.[Cotizacion 3.4%]
				END AS 'Cotizacion 7%',
				ISNULL (M.codigo, 0) AS Codigo,
				ISNULL (M.FechaIni, '') AS FechaIni,
				ISNULL (M.FechaFin, '') AS FechaFin
			INTO #TOP
			FROM #FON AS F LEFT OUTER JOIN #MOV AS M ON F.ficha = M.ficha

			UNION ALL

			SELECT
				ROW_NUMBER() OVER(PARTITION BY F.ficha ORDER BY F.ficha)as numlin,
				0 AS validador,
				F.ficha,
				F.rut,
				F.dv,
				F.apePaterno,
				F.apeMaterno,
				F.nombres,
				0 AS [Dias Trabajados],
				F.[Entidad Previsional],
				0 AS [Remuneracion Imponible],
				0 AS [Cotizacion 7%],
				ISNULL (M.codigo, 0) AS Codigo,
				ISNULL (M.FechaIni, '') AS FechaIni,
				ISNULL (M.FechaFin, '') AS FechaFin
			FROM #FON AS F LEFT OUTER JOIN #MOV AS M ON F.ficha = M.ficha

			SELECT *  FROM #TOP 
			WHERE (validador= 0 AND numlin > 1) OR (validador = 1 AND numlin = 1)
			ORDER BY #TOP.apePaterno, #TOP.apeMaterno, #TOP.[Remuneracion Imponible] DESC
		END;

	ELSE IF ((@PERIODO = 2020 AND @MES = 12) OR (@MES = 1 AND @PERIODO = 2022)) OR @PERIODO = 2021
		BEGIN
			SELECT
				ROW_NUMBER() OVER(PARTITION BY F.ficha ORDER BY F.ficha)as numlin,
				1 AS validador,
				F.ficha,
				F.rut,
				F.dv,
				F.apePaterno,
				F.apeMaterno,
				F.nombres,
				F.[Dias Trabajados],
				F.[Entidad Previsional],			
				F.[Remuneracion Imponible],
				CASE  
				WHEN (SELECT caja FROM remParametros) = 0 THEN F.[Cotizacion 7%]
				WHEN (SELECT caja FROM remParametros) > 0 THEN F.[Cotizacion 3.9%]
				END AS 'Cotizacion 7%',
				ISNULL (M.codigo, 0) AS Codigo,
				ISNULL (M.FechaIni, '') AS FechaIni,
				ISNULL (M.FechaFin, '') AS FechaFin
			INTO #TOP2
			FROM #FON AS F LEFT OUTER JOIN #MOV AS M ON F.ficha = M.ficha

			UNION ALL

			SELECT
				ROW_NUMBER() OVER(PARTITION BY F.ficha ORDER BY F.ficha)as numlin,
				0 AS validador,
				F.ficha,
				F.rut,
				F.dv,
				F.apePaterno,
				F.apeMaterno,
				F.nombres,
				0 AS [Dias Trabajados],
				F.[Entidad Previsional],
				0 AS [Remuneracion Imponible],
				0 AS [Cotizacion 7%],
				ISNULL (M.codigo, 0) AS Codigo,
				ISNULL (M.FechaIni, '') AS FechaIni,
				ISNULL (M.FechaFin, '') AS FechaFin
			FROM #FON AS F LEFT OUTER JOIN #MOV AS M ON F.ficha = M.ficha

			SELECT *  FROM #TOP2 
			WHERE (validador= 0 AND numlin > 1) OR (validador = 1 AND numlin = 1)
			ORDER BY #TOP2.apePaterno, #TOP2.apeMaterno, #TOP2.[Remuneracion Imponible] DESC
		END;

	ELSE IF (@PERIODO = 2022 AND @MES > 1) OR (@PERIODO = 2023 AND @MES = 1)
		BEGIN
			SELECT
				ROW_NUMBER() OVER(PARTITION BY F.ficha ORDER BY F.ficha)as numlin,
				1 AS validador,
				F.ficha,
				F.rut,
				F.dv,
				F.apePaterno,
				F.apeMaterno,
				F.nombres,
				F.[Dias Trabajados],
				F.[Entidad Previsional],			
				F.[Remuneracion Imponible],
				CASE  
				WHEN (SELECT caja FROM remParametros) = 0 THEN F.[Cotizacion 7%]
				WHEN (SELECT caja FROM remParametros) > 0 THEN F.[Cotizacion 0.55%]
				END AS 'Cotizacion 7%',
				ISNULL (M.codigo, 0) AS Codigo,
				ISNULL (M.FechaIni, '') AS FechaIni,
				ISNULL (M.FechaFin, '') AS FechaFin
			INTO #TOP3
			FROM #FON AS F LEFT OUTER JOIN #MOV AS M ON F.ficha = M.ficha

			UNION ALL

			SELECT
				ROW_NUMBER() OVER(PARTITION BY F.ficha ORDER BY F.ficha)as numlin,
				0 AS validador,
				F.ficha,
				F.rut,
				F.dv,
				F.apePaterno,
				F.apeMaterno,
				F.nombres,
				0 AS [Dias Trabajados],
				F.[Entidad Previsional],
				0 AS [Remuneracion Imponible],
				0 AS [Cotizacion 7%],
				ISNULL (M.codigo, 0) AS Codigo,
				ISNULL (M.FechaIni, '') AS FechaIni,
				ISNULL (M.FechaFin, '') AS FechaFin
			FROM #FON AS F LEFT OUTER JOIN #MOV AS M ON F.ficha = M.ficha

			SELECT *  FROM #TOP3 
			WHERE (validador= 0 AND numlin > 1) OR (validador = 1 AND numlin = 1)
			ORDER BY #TOP3.apePaterno, #TOP3.apeMaterno, #TOP3.[Remuneracion Imponible] DESC
		END;

	ELSE IF DATEFROMPARTS(@PERIODO, @MES, 1) > '2023-01-01'
		BEGIN
			SELECT
				ROW_NUMBER() OVER(PARTITION BY F.ficha ORDER BY F.ficha)as numlin,
				1 AS validador,
				F.ficha,
				F.rut,
				F.dv,
				F.apePaterno,
				F.apeMaterno,
				F.nombres,
				F.[Dias Trabajados],
				F.[Entidad Previsional],			
				F.[Remuneracion Imponible],
				CASE  
				WHEN (SELECT caja FROM remParametros) = 0 THEN F.[Cotizacion 7%]
				WHEN (SELECT caja FROM remParametros) > 0 THEN F.[Cotizacion 3.9%]
				END AS 'Cotizacion 7%',
				ISNULL (M.codigo, 0) AS Codigo,
				ISNULL (M.FechaIni, '') AS FechaIni,
				ISNULL (M.FechaFin, '') AS FechaFin
			INTO #TOP4
			FROM #FON AS F LEFT OUTER JOIN #MOV AS M ON F.ficha = M.ficha

			UNION ALL

			SELECT
				ROW_NUMBER() OVER(PARTITION BY F.ficha ORDER BY F.ficha)as numlin,
				0 AS validador,
				F.ficha,
				F.rut,
				F.dv,
				F.apePaterno,
				F.apeMaterno,
				F.nombres,
				0 AS [Dias Trabajados],
				F.[Entidad Previsional],
				0 AS [Remuneracion Imponible],
				0 AS [Cotizacion 7%],
				ISNULL (M.codigo, 0) AS Codigo,
				ISNULL (M.FechaIni, '') AS FechaIni,
				ISNULL (M.FechaFin, '') AS FechaFin
			FROM #FON AS F LEFT OUTER JOIN #MOV AS M ON F.ficha = M.ficha

			SELECT *  FROM #TOP4 
			WHERE (validador= 0 AND numlin > 1) OR (validador = 1 AND numlin = 1)
			ORDER BY #TOP4.apePaterno, #TOP4.apeMaterno, #TOP4.[Remuneracion Imponible] DESC
		END;
END