dbo.LIST_CONTRAVENCIONES

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

CREATE OR ALTER PROCEDURE dbo.LIST_CONTRAVENCIONES 

AS


BEGIN
       SET NOCOUNT ON;

SELECT        contravenciones.id_contravencion, contravenciones.id_cliente, contravenciones.semana, contravenciones.anio, contravenciones.pedido, contravenciones.nombre, contravenciones.apellido, contravenciones.dni, 
                         contravenciones.cuit, contravenciones.dominio, contravenciones.fecha_alta, contravenciones.domicilio, contravenciones.numero, contravenciones.piso, contravenciones.dpto, contravenciones.localidad, 
                         contravenciones.cp, contravenciones.procesado, contravenciones.fecha_procesamiento, contravenciones.user_procesamiento, contravenciones.pesos_ba, contravenciones.pesos_caba, 
                         contravenciones.litros_ba, contravenciones.litros_caba, contravenciones_score2.SCORE, contravenciones_score2.MOTIVOS_SCORE, contravenciones_score2.motivo
FROM            contravenciones_score2 RIGHT OUTER JOIN
                         contravenciones ON contravenciones_score2.id_contravencion = contravenciones.id_contravencion
ORDER BY contravenciones.pedido


select * from contravenciones
select * from contravenciones_data
update contravenciones_data set litros = isnull(importe_acta,0) / (17.97/2)


DECLARE @ID INT = 0

WHILE (@ID < 1560)
BEGIN
UPDATE CONTRAVENCIONES 
SET litros_ba = P.LITROSba 
FROM (SELECT sum(contravenciones_data.litros) AS LITROSBA from contravenciones_data 
join (select max(id_data) as id_data,acta from contravenciones_data where id_contravencion = @ID and consulta_valida = 1 and jurisdiccion = 'BA'
group by acta) hh on contravenciones_data.id_data = hh.id_data
where id_contravencion = @ID and consulta_valida = 1 and jurisdiccion = 'BA') P
where CONTRAVENCIONES.id_contravencion = @ID


SELECT @ID

UPDATE CONTRAVENCIONES 
SET litros_caba = P.LITROSCAba 
FROM (SELECT sum(contravenciones_data.litros) AS LITROSCABA from contravenciones_data 
join (select max(id_data) as id_data,acta from contravenciones_data where id_contravencion = @ID and consulta_valida = 1 and jurisdiccion = 'CABA'
group by acta) hh on contravenciones_data.id_data = hh.id_data
where id_contravencion = @ID and consulta_valida = 1 and jurisdiccion = 'CABA') P
where CONTRAVENCIONES.id_contravencion = @ID



SET @ID = @ID + 1

END


truncate table contravenciones_litros_caba

insert into contravenciones_litros_caba
SELECT DISTINCT contravenciones.id_contravencion,sum(CONTRAVENCIONES_data.importe_acta) as pesos_ba,
SUM(LITROS) AS LITROSBA,contravenciones_data.acta
FROM 
contravenciones_data left JOIN CONTRAVENCIONES ON CONTRAVENCIONES.id_contravencion = 
contravenciones_data.id_contravencion
WHERE 
contravenciones_data.jurisdiccion = 'CABA' and 
contravenciones_data.acta is not null
GROUP BY contravenciones.id_contravencion,CONTRAVENCIONES_data.acta
order by contravenciones.id_contravencion


truncate table contravenciones_litros_ba

insert into contravenciones_litros_ba
SELECT DISTINCT contravenciones.id_contravencion,sum(CONTRAVENCIONES_data.importe_acta) as pesos_ba,
SUM(LITROS) AS LITROSBA,contravenciones_data.acta
FROM 
contravenciones_data left JOIN CONTRAVENCIONES ON CONTRAVENCIONES.id_contravencion = 
contravenciones_data.id_contravencion
WHERE 
contravenciones_data.jurisdiccion = 'BA' and 
contravenciones_data.acta is not null
GROUP BY contravenciones.id_contravencion,CONTRAVENCIONES_data.acta
order by contravenciones.id_contravencion




update contravenciones 
set 
contravenciones.pesos_ba  = p_ba.pesos_ba,
contravenciones.litros_ba  = p_ba.litros_ba
from contravenciones inner join (SELECT DISTINCT id_contravencion,sum(pesos_ba) as pesos_ba,
SUM(LITROS_ba) AS LITROS_ba FROM contravenciones_litros_ba  GROUP BY contravenciones_litros_ba.id_contravencion) p_ba
on contravenciones.id_contravencion = p_ba.id_contravencion

update contravenciones 
set 
contravenciones.pesos_caba  = p_caba.pesos_caba,
contravenciones.litros_caba  = p_caba.litros_caba
from contravenciones inner join (SELECT DISTINCT id_contravencion,sum(pesos_caba) as pesos_caba,
SUM(LITROS_caba) AS LITROS_caba FROM contravenciones_litros_caba  GROUP BY contravenciones_litros_caba.id_contravencion) p_caba
on contravenciones.id_contravencion = p_caba.id_contravencion


update contravenciones set pesos_caba = 0 where pesos_caba is null
update contravenciones set pesos_ba = 0 where pesos_ba is null
update contravenciones set litros_caba = 0 where litros_caba is null
update contravenciones set litros_ba = 0 where litros_ba is null



SELECT        id_contravencion as id_fidelitas,
 pedido, contravenciones.nombre, contravenciones.apellido, dni, 
 dominio, 
 litros_ba AS BA, litros_caba as CABA,
  litros_ba + litros_caba AS total,
 0 as score1,
 '' as Judiciales,
 '' as Bancarios,
 contravenciones_score.SCORE as score2,
 '' as Reincidencia,
 0 as score3,
 0 as score_final,
 contravenciones.domicilio, numero, piso, dpto, contravenciones.localidad, contravenciones.cp, 
 pesos_ba, pesos_caba,  pesos_ba + pesos_caba AS pesos_total, 
 litros_ba + litros_caba AS litros_total, 8.99 AS valor_uf

FROM            contravenciones inner join contravenciones_score on contravenciones.dni = contravenciones_score.DOCUMENTO
WHERE        (fecha_procesamiento IS NOT NULL)
ORDER BY id_contravencion




/*

   SELECT        CC.id_contravencion, CC.id_cliente, CC.semana, CC.anio, CC.pedido, CC.nombre, CC.apellido, CC.dni, CC.dominio, CC.domicilio, CC.numero, CC.piso, CC.dpto, CC.localidad, CC.cp, CG.litrosCABA, 
                         CG.litrosBA, (CG.litrosBA+cg.litrosCABA) AS LITROS_TOTAL
FROM            contravenciones AS CC INNER JOIN
                             (SELECT        CD.id_contravencion, SUM(BA.litros) AS litrosBA, SUM(CABA.litros) AS litrosCABA
                               FROM            contravenciones_data AS CD INNER JOIN
                                                         contravenciones_data AS BA ON CD.id_data = BA.id_data AND BA.jurisdiccion = 'BA' INNER JOIN
                                                         contravenciones_data AS CABA ON CD.id_data = CABA.id_data AND CABA.jurisdiccion = 'CABA'
                               GROUP BY CD.id_contravencion) AS CG ON CC.id_contravencion = CG.id_contravencion
ORDER BY CC.id_contravencion
*/



/*

SELECT        contravenciones.id_contravencion, contravenciones.semana, contravenciones.anio, contravenciones.pedido, contravenciones.nombre, contravenciones.apellido, contravenciones.dni, contravenciones.dominio, 
contravenciones.domicilio, contravenciones.numero, contravenciones.piso, contravenciones.dpto, contravenciones.localidad, contravenciones.cp, contravenciones_score.SCORE, 
ppBA.litros_ba,
ppCABA.litros_caba, 
ppBA.litros_ba+ppCABA.litros_caba AS litros, 
ppCABA.pesos_caba, 
ppBA.pesos_ba

FROM contravenciones_score INNER JOIN
contravenciones ON contravenciones_score.DOCUMENTO = contravenciones.dni 
LEFT OUTER JOIN (SELECT SUM(litros_ba) AS litros_ba, SUM(pesos_ba) AS pesos_ba, id_contravencion
FROM            contravenciones_litros_ba
WHERE        (id_contravencion = contravenciones_litros_ba.id_contravencion)
GROUP BY id_contravencion) ppBA ON contravenciones.id_contravencion = ppBA.id_contravencion

LEFT OUTER JOIN
(SELECT SUM(litros_caba) AS litros_caba, SUM(pesos_caba) AS pesos_caba, id_contravencion
FROM            contravenciones_litros_caba
WHERE        (id_contravencion = contravenciones_litros_caba.id_contravencion)
GROUP BY id_contravencion) ppCABA ON contravenciones.id_contravencion = ppCABA.id_contravencion

WHERE        (contravenciones.procesado = 1)

/*
group by 
contravenciones.id_contravencion, contravenciones.semana, contravenciones.anio, contravenciones.pedido, contravenciones.nombre, contravenciones.apellido, contravenciones.dni, contravenciones.dominio, 
contravenciones.domicilio, contravenciones.numero, contravenciones.piso, contravenciones.dpto, contravenciones.localidad, contravenciones.cp, contravenciones_score.SCORE 
*/
/*
,contravenciones_litros_caba.acta, 
contravenciones_litros_ba.acta
*/
ORDER BY contravenciones.id_contravencion



SALIDA XLS

SELECT        contravenciones.id_contravencion, contravenciones.id_cliente, contravenciones.semana, contravenciones.anio, contravenciones.pedido, contravenciones.nombre, contravenciones.apellido, contravenciones.dni, 
                         contravenciones.cuit, contravenciones.dominio, contravenciones.fecha_alta, contravenciones.domicilio, contravenciones.numero, contravenciones.piso, contravenciones.dpto, contravenciones.localidad, 
                         contravenciones.cp, contravenciones.procesado, contravenciones.fecha_procesamiento, contravenciones.user_procesamiento, contravenciones_score.SCORE, contravenciones_score.DOCUMENTO, 
                         contravenciones_litros_ba.LITROSBA, contravenciones_litros_caba.LITROSCABA, contravenciones_litros_ba.LITROSBA + contravenciones_litros_caba.LITROSCABA AS litros
FROM            contravenciones_litros_ba INNER JOIN
                         contravenciones ON contravenciones_litros_ba.id_contravencion = contravenciones.id_contravencion INNER JOIN
                         contravenciones_litros_caba ON contravenciones.id_contravencion = contravenciones_litros_caba.id_contravencion INNER JOIN
                         contravenciones_score ON contravenciones.dni = contravenciones_score.DOCUMENTO
ORDER BY contravenciones.id_contravencion


insert into contravenciones_litros_ba

SELECT DISTINCT contravenciones.id_contravencion,sum(CONTRAVENCIONES_data.importe_acta) as pesos_ba,
SUM(LITROS) AS LITROSBA,CONTRAVENCIONES_data.acta
FROM 
contravenciones_data INNER JOIN CONTRAVENCIONES ON CONTRAVENCIONES.id_contravencion = 
contravenciones_data.id_contravencion
WHERE 
/*
contravenciones_data.jurisdiccion = 'BA' and 
*/
contravenciones_data.acta is not null
GROUP BY contravenciones.id_contravencion,CONTRAVENCIONES_data.acta
order by contravenciones.id_contravencion



in--sert into contravenciones_litros_ba
SELECT DISTINCT contravenciones.id_contravencion,
SUM(LITROS) AS LITROSBA,CONTRAVENCIONES.DNI,CONTRAVENCIONES.dominio
 FROM 
contravenciones_data INNER JOIN CONTRAVENCIONES ON CONTRAVENCIONES.id_contravencion = 
contravenciones_data.id_contravencion

WHERE contravenciones_data.jurisdiccion = 'BA'
GROUP BY contravenciones.id_contravencion,CONTRAVENCIONES.DNI,CONTRAVENCIONES.dominio


select * from contravenciones_litros_ba

in--sert into contravenciones_litros_caba
SELECT DISTINCT contravenciones.id_contravencion,
SUM(LITROS) AS LITROSCABA,CONTRAVENCIONES.DNI,CONTRAVENCIONES.dominio
 FROM 
contravenciones_data INNER JOIN CONTRAVENCIONES ON CONTRAVENCIONES.id_contravencion = 
contravenciones_data.id_contravencion

WHERE contravenciones_data.jurisdiccion = 'CABA'
GROUP BY contravenciones.id_contravencion,CONTRAVENCIONES.DNI,CONTRAVENCIONES.dominio


select * from  contravenciones_litros_caba






SELECT DISTINCT contravenciones.id_contravencion,
SUM(LITROS) AS LITROSBA,CONTRAVENCIONES.DNI,CONTRAVENCIONES.dominio
 FROM 
contravenciones_data INNER JOIN CONTRAVENCIONES ON CONTRAVENCIONES.id_contravencion = 
contravenciones_data.id_contravencion

WHERE contravenciones_data.jurisdiccion = 'BA'
GROUP BY contravenciones.id_contravencion,CONTRAVENCIONES.DNI,CONTRAVENCIONES.dominio

*/





END

GO