ALTER PROCEDURE [dbo].[sp_remResumenImposiciones] 
(
@PERIODO INT =0,
@MES INT=0 ,
@ficini int=0,
@ficfin int=0,
@unineg INT=0,
@DEP INT=0,
@SEC INT=0
)
AS
----{periodo},{mes},{fichaini},{fichafin},{unidadNegocios},{departamento},{seccion}

BEGIN
	SET NOCOUNT ON;


DECLARE @nomCaja VARCHAR(20)
IF CONVERT(DATETIME,(SELECT '01-' + CONVERT(VARCHAR(2),mes) + '-' + CONVERT(VARCHAR(4),periodo) FROM remParametros)) >= CONVERT(DATETIME,'01-04-2023')
BEGIN
	SET @nomCaja = (SELECT CASE caja2
							WHEN 0 THEN 'NO DEFINIDA'
							WHEN 1 THEN 'LOS ANDES'
							WHEN 2 THEN 'LA ARAUCANA'
							WHEN 3 THEN 'LOS HÉROES'
							WHEN 4 THEN '18 DE SEPTIEMBRE'
							END AS nombreCaja
					FROM remParametros)
END
ELSE
BEGIN
	SET @nomCaja = (SELECT CASE caja2
							WHEN 0 THEN 'NO DEFINIDA'
							WHEN 1 THEN 'LOS ANDES'
							WHEN 2 THEN 'LA ARAUCANA'
							WHEN 3 THEN 'LOS HÉROES'
							WHEN 5 THEN 'GABRIELA MISTRAL'
							WHEN 6 THEN '18 DE SEPTIEMBRE'
							END AS nombreCaja
					FROM remParametros)
END

SELECT    1 as tipo,'AFP' AS desctipo, T.ficha,T.idprevision,t.instSalud,
remIsapre.descripcion as isapre, afp.descripcion as afp,
    dbo.remValorResultado(T.periodo,T.mes,T.ficha, 4) AS  '4' ,
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 22) AS  '22',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 23) AS  '23',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 38) AS '38',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 6) AS '6',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 7) AS '7',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 8) AS '8',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 35) AS '35',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 45) AS '45',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 10) AS '10',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 11) AS '11',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 31) AS '31',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 28) AS '28',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 29) AS '29',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 30) AS '30',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 32) AS '32',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 33) AS '33',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 34) AS '34',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 46) AS '46',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 39) AS '39',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 40) AS '40',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 41) AS '41',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 42) AS '42',
	dbo.remValorResultado(T.periodo, T.mes, ficha, 47) AS 'RIMA',
	dbo.remValorResultado(T.periodo, T.mes, ficha, 48) AS 'CCI',
	dbo.remValorResultado(T.periodo, T.mes, ficha, 49) AS '49',
	dbo.remValorResultado(T.periodo, T.mes, ficha, 95) AS '95',
	dbo.remValorResultado(T.periodo, T.mes, ficha, 96) AS '96',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 39) +dbo.remValorResultado(T.periodo,T.mes,T.ficha, 40) +dbo.remValorResultado(T.periodo,T.mes,T.ficha, 41)+dbo.remValorResultado(T.periodo,T.mes,T.ficha, 42)+dbo.remValorResultado(T.periodo,T.mes,T.ficha, 43) AS  apv,
   0 AS montosalud,
	0 AS adicionalsalud,
	0 AS montomutual,
	0 AS MONTOCAJA,
	0 AS MONTOASIG,
	0 AS MONTOCAJARETRO,regimenPrevisional as rp ,regimenSalud as rs,PrevisionCuenta2,idApvi1,idApvi2,idApvi3,idApvc1	
INTO #AFP
FROM            remTrabajadores AS T LEFT OUTER JOIN
                         remPrevisiones AS AFP ON T.periodo = AFP.periodo AND T.mes = AFP.mes AND T.idPrevision = AFP.idPrevision AND 
                         T.regimenPrevisional = AFP.idRegimen LEFT OUTER JOIN
                         remIsapre ON T.periodo = remIsapre.periodo AND T.mes = remIsapre.mes AND T.instSalud = remIsapre.idIsapre
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 * 
INTO #previsiones FROM remPrevisiones WHERE periodo=@PERIODO AND mes=@MES


SELECT 1 as tipoa,PrevisionCuenta2 as id,sum([6]) as  valor , AFP.descripcion as nombreinst INTO #AHORRO from #aFP INNER JOIN #previsiones AS AFP ON #aFP.PrevisionCuenta2 = AFP.idPrevision group by PrevisionCuenta2,descripcion
UNION ALL
SELECT 2 as tipoa,idApvi1 as id,sum([39]) as  valor , AFP.descripcion as nombreinst from #aFP INNER JOIN #previsiones AS AFP ON #aFP.idApvi1 = AFP.idPrevision group by idApvi1,descripcion
UNION ALL
SELECT 2 as tipoa,idApvi2 as id,sum([40]) as  valor , AFP.descripcion as nombreinst from #aFP INNER JOIN #previsiones AS AFP ON #aFP.idApvi2 = AFP.idPrevision group by idApvi2,descripcion
UNION ALL
SELECT 2 as tipoa,idApvi3 as id,sum([41]) as  valor , AFP.descripcion as nombreinst from #aFP INNER JOIN #previsiones AS AFP ON #aFP.idApvi3 = AFP.idPrevision group by idApvi3,descripcion
UNION ALL
SELECT 3 as tipoa,idApvC1 as id,sum([42]) as  valor , AFP.descripcion as nombreinst from #aFP INNER JOIN #previsiones AS AFP ON #aFP.idApvC1 = AFP.idPrevision group by idApvc1,descripcion



--SELECT * FROM #AHORRO


--======= AFP ===========

SELECT 1 as orden,1 as tipo,'AFP' AS desctipo,idprevision as id,afp AS descripcion,sum([4]) as valor, afp as nombreinst 
INTO #todo
from #aFP WHERE rp=1  group by idPrevision,afp
UNION ALL
SELECT 2 as orden,1 as tipo,'AFP' AS desctipo,idprevision as id,'SEG.CES.TRABJ.' AS descripcion,sum([22]) as valor , afp as nombreinst from #aFP group by idPrevision,afp
UNION ALL
SELECT 3 as orden,1 as tipo,'AFP' AS desctipo,idprevision as id,'SEG.CES.EMP.' AS descripcion,sum([23]) as valor , afp as nombreinst from #aFP  group by idPrevision,afp
UNION ALL
SELECT 4 as orden,1 as tipo,'AFP' AS desctipo,idprevision as id,'SIS' AS descripcion,sum([38]) as valor , afp as nombreinst from #aFP WHERE rp=1 group by idPrevision,afp
UNION ALL

SELECT 5 as orden,1 as tipo,'AFP' AS desctipo,id,'AHORRO AFP ' + nombreinst AS descripcion,sum(valor) as  valor , nombreinst from #AHORRO WHERE tipoa= 1 group by id,nombreinst

UNION ALL

SELECT 6 as orden,1 as tipo,'AFP' AS desctipo,id,'APV ' + nombreinst  AS descripcion,sum(valor) as  valor , nombreinst from #AHORRO  WHERE tipoa= 2 group by id,nombreinst

UNION ALL

SELECT 7 as orden,1 as tipo,'AFP' AS desctipo,id,'APC ' + nombreinst  AS descripcion,sum(valor) as  valor , nombreinst from #AHORRO WHERE tipoa=3 group by id,nombreinst

UNION ALL
SELECT 8 as orden,1 as tipo,'AFP' AS desctipo,idprevision as id,'CCI' AS descripcion,sum([CCI]) as valor , afp as nombreinst from #aFP WHERE rp=1 group by idPrevision,afp

--UNION ALL
--SELECT 8 as orden,1 as tipo,'AFP' AS desctipo,PrevisionCuenta2 as id,'AHORRO AFP' AS descripcion,sum([6]) as  valor , afp as nombreinst from #aFP WHERE rp=1 group by PrevisionCuenta2,afp

--UNION ALL
--SELECT 9 as orden,1 as tipo,'AFP' AS desctipo,idprevision as id,'APV' AS descripcion,sum(apv) as valor, afp as nombreinst from #aFP WHERE rp=1 group by idPrevision,afp

--======= ISAPRE ===========
UNION ALL
SELECT 1 as orden,2 as tipo,'ISAPRE' AS desctipo,instSalud as id,isapre AS descripcion,sum([7]) as valor ,isapre as nombreinst from #aFP WHERE rs=2 group by instSalud,isapre

UNION ALL
SELECT 2 as orden,2 as tipo,'ISAPRE' AS desctipo,instSalud as id,'ADICIONAL' AS descripcion,sum([8]) as valor,isapre as nombreinst from #aFP WHERE rs=2 group by instSalud,isapre

UNION ALL


--======= FONASA ===========
SELECT 1 as orden,3 as tipo,'FONASA' AS desctipo,0 as id,'INP' AS descripcion,sum([7]) as valor,'FONASA' as nombreinst from #aFP WHERE rs=1 group by instSalud,isapre
UNION ALL
SELECT 2 as orden,3 as tipo,'FONASA' AS desctipo,0 as id,'INP REGIMEN ANTIGUO' AS descripcion,sum([4]) as valor,'FONASA' as nombreinst from #aFP WHERE rp=2 group by instSalud,isapre

UNION ALL

--======= MUTUAL ===========
SELECT 1 as orden,4 as tipo,'MUTUAL' AS desctipo,0 as id,'MUTUAL' AS descripcion,sum([35]) as valor ,'MUTUAL' as nombreinst from #aFP

UNION ALL

--======= CAJA ===========

SELECT 1 as orden,5 as tipo,'CAJA' AS desctipo,0 as id,UPPER('Full Ahorro' ) AS descripcion,sum([31]) as valor ,'CAJA' as nombreinst 
from #aFP

UNION ALL

SELECT 2 as orden,5 as tipo,'CAJA' AS desctipo,0 as id,UPPER('Credito Social' ) AS descripcion,sum([28]) as valor ,'CAJA' as nombreinst 
from #aFP

UNION ALL

SELECT 3 as orden,5 as tipo,'CAJA' AS desctipo,0 as id,UPPER('Leasing' ) AS descripcion,sum([30]) as valor ,'CAJA' as nombreinst 
from #aFP

UNION ALL

--SELECT 7 as orden,5 as tipo,'CAJA' AS desctipo,0 as id,UPPER('F-Ahorro' ) AS descripcion,sum([31]) as valor ,'CAJA' as nombreinst from #aFP
--UNION ALL
SELECT 4 as orden,5 as tipo,'CAJA' AS desctipo,0 as id,UPPER('Seguro Vida') AS descripcion,sum([32]) as valor ,'CAJA' as nombreinst 
from #aFP

UNION ALL

SELECT 5 as orden,5 as tipo,'CAJA' AS desctipo,0 as id,'OTROS' AS descripcion,sum([34]) as valor ,'CAJA' as nombreinst 
from #aFP

UNION ALL

SELECT 1 as orden,5 as tipo,'CAJA' AS desctipo,0 as id,'CAJA' AS descripcion,sum([45]) as valor ,'CAJA' as nombreinst 
from #aFP

UNION ALL

SELECT 2 as orden,5 as tipo,'CAJA' AS desctipo,0 as id,UPPER('Asignacion Familiar') AS descripcion,sum([10]) * -1 as valor,'CAJA' as nombreinst 
from #aFP

UNION ALL

SELECT 3 as orden,5 as tipo,'CAJA' AS desctipo,0 as id,UPPER('Asignacion Familiar Retro') AS descripcion,sum([11]) * -1 as valor ,'CAJA' as nombreinst 
from #aFP

UNION ALL

SELECT 1 as orden,6 as tipo,'CAJA2' AS desctipo,0 as id,'PRESTAMO CAJA ' + @nomCaja AS descripcion,sum([96]) as valor ,'CAJA2' as nombreinst 
FROM #aFP

--======= Ley Pensiones ===========
--UNION ALL
--SELECT 1 as orden,7 as tipo,'SEGURO SOCIAL' AS desctipo,0 as id,'RIMA' AS descripcion,sum(RIMA) as valor ,'SEGURO SOCIAL' as nombreinst FROM #aFP
--UNION ALL
--SELECT 1 as orden,7 as tipo,'SEGURO SOCIAL' AS desctipo,0 as id,'CCI' AS descripcion,sum(CCI) as valor ,'SEGURO SOCIAL' as nombreinst FROM #aFP
UNION ALL

SELECT 1 as orden,7 as tipo,'SEGURO SOCIAL' AS desctipo,0 as id,'EXPECTATIVA VIDA' AS descripcion,sum([49]) as valor ,'SEGURO SOCIAL' as nombreinst 
FROM #aFP

UNION ALL

SELECT 2 as orden,7 as tipo,'SEGURO SOCIAL' AS desctipo,0 as id,'RENTABILIDAD PROTEGIDA' AS descripcion,sum([95]) as valor ,'SEGURO SOCIAL' as nombreinst 
FROM #aFP


order by tipo,id,orden


select * FROM #todo where valor >0 or valor <0


end

--select 1 as tipo,desctipo,idprevision as  id,afp as descripcion,
--SUM([4]) AS MONTO1,
--SUM([22]) AS MONTO2,
--SUM([23]) AS MONTO3,
--SUM([38]) AS MONTO4,
--SUM([6]) AS MONTO5,
--SUM([apv]) AS MONTO6
--from #AFP
--GROUP BY TIPO,desctipo,afp,idprevision

--union all 

--select 2 as tipo,desctipo,instsalud as  id,isapre as descripcion,
--SUM([7])  AS MONTO1,
--SUM([8])  AS MONTO2,
--0 AS MONTO3,
--0 AS MONTO4,
--0 AS MONTO5,
--0 AS MONTO6
--from #AFP
--GROUP BY TIPO,desctipo,instsalud,isapre