Detectar y eliminar pagos duplicados, recalcular cta. cte. y reprocesar planes de pagos

DetectorDuplicadosCSSPK.sql

*/","Kine",
"/* Buscar duplicados */
/* que no tienen bill (POR LINK)*/ 
SELECT

CSSPK_BILL_AFFILIATE_CORE.matricula,
CSSPK_BILL_PAY.methodOfPayment,
bill , imputationDate as FechaImputacion,

CAST(GROUP_CONCAT(DISTINCT CSSPK_BILL_PAY.id) AS CHAR) AS PagosID,
CASE CSSPK_BILL_PAY.payType
	WHEN 1 THEN "APORTES"
	WHEN 2 THEN "CONVENIO PAG"
	WHEN 3 THEN "CONVENIO EXTRAJUD"
	WHEN 4 THEN "PRESTAMOS"
END as tipo,
#Esquivar dejar basura y hacer quilombo con la integridad referencial,  y Borrar Pago:

CAST(
CASE CSSPK_BILL_PAY.payType

WHEN 2 THEN 
	concat("SET @id_temp = NULL;
	SELECT 
	CSSPK_BILL_PAY.id INTO @id_temp  FROM CSSPK_BILL_PAY
	WHERE (CSSPK_BILL_PAY.id=SUBSTRING_INDEX('",CAST(GROUP_CONCAT(DISTINCT CSSPK_BILL_PAY.id)AS CHAR),"',',',1) OR CSSPK_BILL_PAY.id=SUBSTRING_INDEX('",CAST(GROUP_CONCAT(DISTINCT CSSPK_BILL_PAY.id)AS CHAR),"',',',-1)) AND CSSPK_BILL_PAY.id NOT IN  (
	SELECT  CSSPK_BILL_INSTALLMENT_WRAPP.pay FROM CSSPK_BILL_INSTALLMENT_WRAPP
	WHERE CSSPK_BILL_INSTALLMENT_WRAPP.pay=SUBSTRING_INDEX('",CAST(GROUP_CONCAT(DISTINCT CSSPK_BILL_PAY.id)AS CHAR),"',',',1) OR CSSPK_BILL_INSTALLMENT_WRAPP.pay=SUBSTRING_INDEX('",CAST(GROUP_CONCAT(DISTINCT CSSPK_BILL_PAY.id)AS CHAR),"',',',-1) 
	);DELETE FROM `CSSPK_BILL_PAY` where id=@id_temp;") 

WHEN 3 THEN 
	concat("SET @id_temp = NULL;
	SELECT 
	CSSPK_BILL_PAY.id INTO @id_temp  FROM CSSPK_BILL_PAY
	WHERE (CSSPK_BILL_PAY.id=SUBSTRING_INDEX('",CAST(GROUP_CONCAT(DISTINCT CSSPK_BILL_PAY.id)AS CHAR),"',',',1) OR CSSPK_BILL_PAY.id=SUBSTRING_INDEX('",CAST(GROUP_CONCAT(DISTINCT CSSPK_BILL_PAY.id)AS CHAR),"',',',-1)) AND CSSPK_BILL_PAY.id NOT IN  (
	SELECT  CSSPK_BILL_INSTALLMENT_WRAPP.pay FROM CSSPK_BILL_INSTALLMENT_WRAPP
	WHERE CSSPK_BILL_INSTALLMENT_WRAPP.pay=SUBSTRING_INDEX('",CAST(GROUP_CONCAT(DISTINCT CSSPK_BILL_PAY.id)AS CHAR),"',',',1) OR CSSPK_BILL_INSTALLMENT_WRAPP.pay=SUBSTRING_INDEX('",CAST(GROUP_CONCAT(DISTINCT CSSPK_BILL_PAY.id)AS CHAR),"',',',-1) 
	);DELETE FROM `CSSPK_BILL_PAY` where id=@id_temp;") 

WHEN 4 THEN 
	concat("SET @id_temp = NULL;
	SELECT 
	CSSPK_BILL_PAY.id INTO @id_temp  FROM CSSPK_BILL_PAY
	WHERE (CSSPK_BILL_PAY.id=SUBSTRING_INDEX('",CAST(GROUP_CONCAT(DISTINCT CSSPK_BILL_PAY.id)AS CHAR),"',',',1) OR CSSPK_BILL_PAY.id=SUBSTRING_INDEX('",CAST(GROUP_CONCAT(DISTINCT CSSPK_BILL_PAY.id)AS CHAR),"',',',-1)) AND CSSPK_BILL_PAY.id NOT IN  (
	SELECT  CSSPK_BILL_LOAN_INSTALLMENT_WRAPP.pay FROM CSSPK_BILL_LOAN_INSTALLMENT_WRAPP
	WHERE CSSPK_BILL_LOAN_INSTALLMENT_WRAPP.pay=SUBSTRING_INDEX('",CAST(GROUP_CONCAT(DISTINCT CSSPK_BILL_PAY.id)AS CHAR),"',',',1) OR CSSPK_BILL_LOAN_INSTALLMENT_WRAPP.pay=SUBSTRING_INDEX('",CAST(GROUP_CONCAT(DISTINCT CSSPK_BILL_PAY.id)AS CHAR),"',',',-1) 
	);DELETE FROM `CSSPK_BILL_PAY` where id=@id_temp;") 
ELSE  
   concat("DELETE FROM `CSSPK_BILL_PAY` where id='",CSSPK_BILL_PAY.id,"';")
END 
AS CHAR)

AS DELETE_BILL_SQL,


-- USAR ESTO PARA NO USAR APAGAR FORANEAS!!
 -- IF(CSSPK_BILL_PAY.payType = '2',
--     concat("DELETE FROM `CSSPK_BILL_PAY` where id='",CSSPK_BILL_PAY.id,"'; "),
-- 		concat("DELETE FROM `CSSPK_BILL_PAY` where id='",CSSPK_BILL_PAY.id,"'; ")
-- ) AS DELETE_BILL_SQL,


#APORTES: Si CSSPK_BILL_PAY.payType=1
concat("http://app.cajakinesiologospba.org.ar:8081/billing/admin/currentAccountView_recalculate.do?matricula=",CSSPK_BILL_AFFILIATE_CORE.matricula) as RECALCULATE_CTACTE_HTML,

#CONVENIOS DE PAGO: Si CSSPK_BILL_PAY.payType=2
IF( CSSPK_BILL_PAY.payType=2, 
			(
				select concat("http://app.cajakinesiologospba.org.ar:8081/billing/admin/paymentPlan_reacomodarPagosDePlan.do?id=",pro_csspk_pdp.YI_PAYMENT_PLAN.ID) 
				FROM pro_csspk_pdp.YI_PAYMENT_PLAN  WHERE pro_csspk_pdp.YI_PAYMENT_PLAN.customerId = matricula and course=2 limit 1
			) , 
			"" ) as  RECALCULATE_CONVENIOS_HTML,

#CONVENIO EXTRA-JUDICIAL. Si CSSPK_BILL_PAY.payType=3
IF( CSSPK_BILL_PAY.payType=3, 
			(
				select concat("http://app.cajakinesiologospba.org.ar:8081/billing/admin/paymentPlan_reacomodarPagosDePlan.do?id=",pro_csspk_pdp.YI_PAYMENT_PLAN.ID) 
				FROM pro_csspk_pdp.YI_PAYMENT_PLAN  WHERE pro_csspk_pdp.YI_PAYMENT_PLAN.customerId = matricula and course=2 limit 1
			) , 
			"" ) as  RECALCULATE_CONV_EXTRAJUDICIAL_HTML,

#CONVENIO PRESTAMO. Si CSSPK_BILL_PAY.payType=4
IF( CSSPK_BILL_PAY.payType=4, 
			(
				select concat("http://app.cajakinesiologospba.org.ar:8081/billing/admin/loanPlanReview_reacomodarPagosDePrestamo.do?id=",pro_csspk_pdp.YI_PAYMENT_PLAN.ID) 
				FROM pro_csspk_pdp.YI_PAYMENT_PLAN  WHERE pro_csspk_pdp.YI_PAYMENT_PLAN.customerId = matricula and course=2 limit 1
			) , 
			"" ) as  RECALCULATE_PRESTAMOS_HTML





FROM
CSSPK_BILL_PAY
INNER JOIN CSSPK_BILL_AFFILIATE_CORE ON CSSPK_BILL_AFFILIATE_CORE.id = CSSPK_BILL_PAY.affiliate

where acreditationDate>'2018-01-01'  AND CSSPK_BILL_PAY.bill IS NULL 
			#AND payType=4  #tipo
group by 
CSSPK_BILL_PAY.affiliate,
CSSPK_BILL_PAY.period,
CSSPK_BILL_PAY.methodOfPayment,
CSSPK_BILL_PAY.acreditationDate,
CSSPK_BILL_PAY.date,
CSSPK_BILL_PAY.imputationDate,
CSSPK_BILL_PAY.payType,
CSSPK_BILL_PAY.amount
having Count(*) >1


UNION 
/* que tienen bill (no link)*/ 
SELECT

CSSPK_BILL_AFFILIATE_CORE.matricula,
CSSPK_BILL_PAY.methodOfPayment,
bill , imputationDate as FechaImputacion,
CAST(GROUP_CONCAT(CSSPK_BILL_PAY.id)AS CHAR)  as PagosID,
CASE CSSPK_BILL_PAY.payType
	WHEN 1 THEN "APORTES"
	WHEN 2 THEN "CONVENIO PAG"
	WHEN 3 THEN "CONVENIO EXTRAJUD"
	WHEN 4 THEN "PRESTAMOS"
END as tipo,


#Borrar Pago:
CAST(concat("SET  FOREIGN_KEY_CHECKS=0; delete FROM `CSSPK_BILL_PAY` where id='",CSSPK_BILL_PAY.id,"' ;SET  FOREIGN_KEY_CHECKS=1; ") AS CHAR) AS DELETE_BILL_SQL,


 

#APORTES: Si CSSPK_BILL_PAY.payType=1
concat("http://app.cajakinesiologospba.org.ar:8081/billing/admin/currentAccountView_recalculate.do?matricula=",CSSPK_BILL_AFFILIATE_CORE.matricula) as RECALCULATE_CTACTE_HTML,

#CONVENIOS DE PAGO: Si CSSPK_BILL_PAY.payType=2
IF( CSSPK_BILL_PAY.payType=2, 
			(
				select concat("http://app.cajakinesiologospba.org.ar:8081/billing/admin/paymentPlan_reacomodarPagosDePlan.do?id=",pro_csspk_pdp.YI_PAYMENT_PLAN.ID) 
				FROM pro_csspk_pdp.YI_PAYMENT_PLAN  WHERE pro_csspk_pdp.YI_PAYMENT_PLAN.customerId = matricula and course=2 limit 1
			) , 
			"" ) as  RECALCULATE_CONVENIOS_HTML,

#CONVENIO EXTRA-JUDICIAL. Si CSSPK_BILL_PAY.payType=3
IF( CSSPK_BILL_PAY.payType=3, 
			(
				select concat("http://app.cajakinesiologospba.org.ar:8081/billing/admin/paymentPlan_reacomodarPagosDePlan.do?id=",pro_csspk_pdp.YI_PAYMENT_PLAN.ID) 
				FROM pro_csspk_pdp.YI_PAYMENT_PLAN  WHERE pro_csspk_pdp.YI_PAYMENT_PLAN.customerId = matricula and course=2 limit 1
			) , 
			"" ) as  RECALCULATE_CONV_EXTRAJUDICIAL_HTML,

#CONVENIO PRESTAMO. Si CSSPK_BILL_PAY.payType=4
IF( CSSPK_BILL_PAY.payType=4, 
			(
				select concat("http://app.cajakinesiologospba.org.ar:8081/billing/admin/loanPlanReview_reacomodarPagosDePrestamo.do?id=",pro_csspk_pdp.YI_PAYMENT_PLAN.ID) 
				FROM pro_csspk_pdp.YI_PAYMENT_PLAN  WHERE pro_csspk_pdp.YI_PAYMENT_PLAN.customerId = matricula and course=2 limit 1
			) , 
			"" ) as  RECALCULATE_PRESTAMOS_HTML





FROM
CSSPK_BILL_PAY
INNER JOIN CSSPK_BILL_AFFILIATE_CORE ON CSSPK_BILL_AFFILIATE_CORE.id = CSSPK_BILL_PAY.affiliate
INNER JOIN CSSPK_BILL_BILL ON CSSPK_BILL_PAY.bill = CSSPK_BILL_BILL.id

where acreditationDate>'2018-01-01'  AND CSSPK_BILL_BILL.id IS NOT NULL 
			#AND payType=4  #tipo
group by 
CSSPK_BILL_PAY.bill,
CSSPK_BILL_PAY.affiliate,
CSSPK_BILL_PAY.period,
CSSPK_BILL_PAY.methodOfPayment,
CSSPK_BILL_PAY.acreditationDate,
CSSPK_BILL_PAY.date,
CSSPK_BILL_PAY.imputationDate,
CSSPK_BILL_PAY.payType,
CSSPK_BILL_PAY.amount
having Count(*) >1