-- =============================================
-- Author:		<Yonatan Gaete>
-- Create date: <17/10/2024>
-- Description:	<Data para Informe Ventas por Forma de Pago>
-- Last update: <Cristobal Lepe> <Se separan resultados por pago (cobranza)>
-- Update Date: <07-03-2025>
-- Description: <Error al tener documentos con mas de 1 pago>
-- =============================================
ALTER PROCEDURE [dbo].[sp_venInformeVentasFPago]
(
	@rut INT = 0,
	@tipodoc INT = 0,
	@formaPagDoc INT = 0,
	@condPag INT = 0,
	@vendedor INT = 0,
	@fecini	DATE,
    @fecfin	DATE,
	@condicion INT = 0
)

AS

--TODOS LOS DOCUMENTOS (PAGADOS, NO PAGADOS)
IF @condicion = 0
	BEGIN
		SELECT E.rutcte,
			   CONVERT(VARCHAR, E.rutcte) + '-' + CONVERT(VARCHAR, conCtacte.digcte) AS RutCliente,
			   conctacte.nomcte,
			   E.coddoc,
			   E.codsii,
			   venDocumentos.alias_doc,
			   E.numinv,
			   E.fecdoc,
			   E.fecven,
			   CASE (ROW_NUMBER() OVER (PARTITION BY E.codsii, E.numinv ORDER BY E.codsii, E.numinv))
			   WHEN 1 THEN (E.total - ISNULL((SELECT total FROM venEncabezado WHERE codsii = 61 AND motivo IN(1,3,4) AND tipref1 = E.codsii AND docref1 = E.numinv), 0))
			   ELSE 0 END AS total,
			   ISNULL((SELECT nompag FROM venCondicionPago WHERE venCondicionPago.codpag = E.codpag), '') AS codpagFact,
			   CASE ISNULL((SELECT id FROM cobPagoDocumentos WHERE tipo = P.tipo AND ndocumento = P.ndocumento AND fpago = P.fpago AND id = P.id), 0)
			   WHEN 0 THEN (E.total - venSaldoDocumentos.saldo - ISNULL((SELECT total FROM venEncabezado WHERE codsii = 61 AND motivo IN(1,3,4) AND tipref1 = E.codsii AND docref1 = E.numinv), 0))
			   ELSE P.monto
			   END AS montoPagado,
			   CASE (ROW_NUMBER() OVER (PARTITION BY E.codsii, E.numinv ORDER BY E.codsii, E.numinv))
			   WHEN 1 THEN venSaldoDocumentos.saldo
			   ELSE 0 END AS saldo,
			   ISNULL(CP.codpag,
			   (CASE
				WHEN (SELECT numinv FROM venReferencias R WHERE R.codsii = 61 AND (R.codsiiref = E.codsii AND R.numinvref = E.numinv)) IS NOT NULL THEN 0
				ELSE -1
				END)) AS codpag,
			   ISNULL(CP.nompag, 
			   (CASE
				WHEN (SELECT numinv FROM venReferencias R WHERE R.codsii = 61 AND (R.codsiiref = E.codsii AND R.numinvref = E.numinv)) IS NOT NULL 
				THEN 'NOTA DE CREDITO ' + CONVERT(VARCHAR(20), (SELECT numinv FROM venReferencias R WHERE R.codsii = 61 AND (R.codsiiref = E.codsii AND R.numinvref = E.numinv)))
				ELSE ''
				END)) AS nompag,
			   CASE 
					WHEN venSaldoDocumentos.saldo = 0 THEN 'PAGADO'
					WHEN venSaldoDocumentos.saldo < 0 THEN 'PAGADO'
					ELSE 'NO PAGADO'
			   END as estado,
			   ISNULL(C.fecha, P.fechamov) as fechapago,
			   ISNULL(B.id_misbancos, 0) as idbanco,
			   ISNULL(B.nomban, '') as banco,
			   CASE P.tipocontable
			   WHEN 1 THEN 'INGRESO/' + CONVERT(varchar(20), p.comprocontable)
			   WHEN 2 THEN 'EGRESO/' + CONVERT(varchar(20), p.comprocontable)
			   WHEN 3 THEN 'TRASPASO/' + CONVERT(varchar(20), p.comprocontable)
			   ELSE ''
			   END AS comprobante, origen
		INTO #A 
		FROM venEncabezado E
			INNER JOIN conctacte ON E.rutcte = conctacte.rutcte
			INNER JOIN VenDocumentos ON E.codsii = venDocumentos.codsii
			INNER JOIN venSaldoDocumentos ON E.numinv = venSaldoDocumentos.numinv
										  AND  E.codsii = venSaldoDocumentos.codsii
			LEFT OUTER JOIN cobpagodocumentos P on E.numinv = P.ndocumento AND E.codsii = P.tipo
			LEFT JOIN VenCondicionPago CP ON P.fpago = CP.codpag
			LEFT JOIN finCartola C ON P.idcartola = C.id
			LEFT OUTER JOIN cobMisBancos B on B.id_MisBancos = C.id_misbancos
		WHERE (@rut = 0 OR E.rutcte = @rut)
			AND  (@tipoDoc = 0 OR E.codsii = @tipoDoc)
			AND ( @formaPagDoc = 0 OR E.codpag = @formaPagDoc)
			AND ( @condPag = 0 OR CP.codpag = @condPag )
			AND  E.fecdoc BETWEEN @fecini and @fecfin
			AND (E.codven = @vendedor OR @vendedor = 0) 

		SELECT 
			   0 AS rutcte,
			   '' AS RutCliente,
			   '' AS nomcte,
			   0 AS coddoc,
			   0 AS codsii,
			   '' AS alias_doc,
			   0 AS numinv,
			   Getdate() AS fecdoc,
			   Getdate() AS fecven,
			   0 AS total,
			   '' AS codpagFact,
			   SUM(#A.montoPagado) AS montoPagado,
			   0 AS saldo,
			   -2 As codpag,
			   #A.nompag, 			   
			   '' AS estado,
			   Getdate() AS fechapago,
			   0 AS idbanco,
			   '' AS banco,
			   '' AS comprobante, 
			   0 AS origen
		INTO #Resultado
		FROM #A
		WHERE montoPagado > 0
		GROUP BY nompag

		SELECT * FROM #A

		UNION ALL

		SELECT * FROM #Resultado

		UNION ALL

		SELECT 
			   0 AS rutcte,
			   '' AS RutCliente,
			   '' AS nomcte,
			   0 AS coddoc,
			   0 AS codsii,
			   '' AS alias_doc,
			   0 AS numinv,
			   Getdate() AS fecdoc,
			   Getdate() AS fecven,
			   0 AS total,
			   '' AS codpagFact,
			   SUM(#A.montoPagado) AS montoPagado,
			   0 AS saldo,
			   -2 As codpag,
			   'TOTAL PAGOS' AS nompag, 			   
			   '' AS estado,
			   Getdate() AS fechapago,
			   0 AS idbanco,
			   '' AS banco,
			   '' AS comprobante, 
			   0 AS origen
		FROM #A
		WHERE montoPagado > 0
	END		

--DOCUMENTOS PAGADOS
IF @condicion = 1
	BEGIN
		SELECT E.rutcte,
			   CONVERT(VARCHAR, E.rutcte) + '-' + CONVERT(VARCHAR, conCtacte.digcte) AS RutCliente,
			   conctacte.nomcte,
			   E.coddoc,
			   E.codsii,
			   venDocumentos.alias_doc,
			   E.numinv,
			   E.fecdoc,
			   E.fecven,
			   CASE (ROW_NUMBER() OVER (PARTITION BY E.codsii, E.numinv ORDER BY E.codsii, E.numinv))
			   WHEN 1 THEN (E.total - ISNULL((SELECT total FROM venEncabezado WHERE codsii = 61 AND motivo IN(1,3,4) AND tipref1 = E.codsii AND docref1 = E.numinv), 0))
			   ELSE 0 END AS total,
			   ISNULL((SELECT nompag FROM venCondicionPago WHERE venCondicionPago.codpag = E.codpag), '') AS codpagFact,
			   CASE ISNULL((SELECT id FROM cobPagoDocumentos WHERE tipo = P.tipo AND ndocumento = P.ndocumento AND fpago = P.fpago AND id = P.id), 0)
			   WHEN 0 THEN (E.total - venSaldoDocumentos.saldo - ISNULL((SELECT total FROM venEncabezado WHERE codsii = 61 AND motivo IN(1,3,4) AND tipref1 = E.codsii AND docref1 = E.numinv), 0))
			   ELSE P.monto
			   END AS montoPagado,
			   CASE (ROW_NUMBER() OVER (PARTITION BY E.codsii, E.numinv ORDER BY E.codsii, E.numinv))
			   WHEN 1 THEN venSaldoDocumentos.saldo
			   ELSE 0 END AS saldo,
			   ISNULL(CP.codpag,
			   (CASE
				WHEN (SELECT numinv FROM venReferencias R WHERE R.codsii = 61 AND (R.codsiiref = E.codsii AND R.numinvref = E.numinv)) IS NOT NULL THEN 0
				ELSE -1
				END)) AS codpag,
			   ISNULL(CP.nompag, 
			   (CASE
				WHEN (SELECT numinv FROM venReferencias R WHERE R.codsii = 61 AND (R.codsiiref = E.codsii AND R.numinvref = E.numinv)) IS NOT NULL 
				THEN 'NOTA DE CREDITO ' + CONVERT(VARCHAR(20), (SELECT numinv FROM venReferencias R WHERE R.codsii = 61 AND (R.codsiiref = E.codsii AND R.numinvref = E.numinv)))
				ELSE ''
				END)) AS nompag,
			   'PAGADO' as estado,
			   ISNULL(C.fecha, P.fechamov) as fechapago,
			   ISNULL(B.id_misbancos, 0) as idbanco,
			   ISNULL(B.nomban, '') as banco,
			   CASE P.tipocontable
			   WHEN 1 THEN 'INGRESO/' + CONVERT(varchar(20), p.comprocontable)
			   WHEN 2 THEN 'EGRESO/' + CONVERT(varchar(20), p.comprocontable)
			   WHEN 3 THEN 'TRASPASO/' + CONVERT(varchar(20), p.comprocontable)
			   ELSE ''
			   END AS comprobante, origen
	    INTO #B
		FROM venEncabezado E
			INNER JOIN conctacte ON E.rutcte = conctacte.rutcte
			INNER JOIN VenDocumentos ON E.codsii = venDocumentos.codsii
			INNER JOIN venSaldoDocumentos ON E.numinv = venSaldoDocumentos.numinv
										  AND  E.codsii = venSaldoDocumentos.codsii
			LEFT OUTER JOIN cobpagodocumentos P on E.numinv = P.ndocumento AND E.codsii = P.tipo
			LEFT JOIN VenCondicionPago CP ON P.fpago = CP.codpag
			LEFT JOIN finCartola C ON P.idcartola = C.id
			LEFT OUTER JOIN cobMisBancos B on B.id_MisBancos = C.id_misbancos
		WHERE (@rut = 0 OR E.rutcte = @rut)
			AND ( @tipoDoc = 0 OR E.codsii = @tipoDoc)
			AND  (@formaPagDoc = 0 OR E.codpag = @formaPagDoc)
			AND ( @condPag = 0 OR CP.codpag = @condPag )
			AND  E.fecdoc BETWEEN @fecini and @fecfin
			AND (E.codven = @vendedor OR @vendedor = 0) 
			AND  (venSaldoDocumentos.saldo = 0 OR venSaldoDocumentos.saldo < 0)

		SELECT 
			   0 AS rutcte,
			   '' AS RutCliente,
			   '' AS nomcte,
			   0 AS coddoc,
			   0 AS codsii,
			   '' AS alias_doc,
			   0 AS numinv,
			   Getdate() AS fecdoc,
			   Getdate() AS fecven,
			   0 AS total,
			   '' AS codpagFact,
			   SUM(#B.montoPagado) AS montoPagado,
			   0 AS saldo,
			   -2 As codpag,
			   #B.nompag, 			   
			   '' AS estado,
			   Getdate() AS fechapago,
			   0 AS idbanco,
			   '' AS banco,
			   '' AS comprobante, 
			   0 AS origen
		INTO #Resultado2
		FROM #B
		WHERE montoPagado > 0
		GROUP BY nompag

		SELECT * FROM #B

		UNION ALL

		SELECT * FROM #Resultado2

		UNION ALL

		SELECT 
			   0 AS rutcte,
			   '' AS RutCliente,
			   '' AS nomcte,
			   0 AS coddoc,
			   0 AS codsii,
			   '' AS alias_doc,
			   0 AS numinv,
			   Getdate() AS fecdoc,
			   Getdate() AS fecven,
			   0 AS total,
			   '' AS codpagFact,
			   SUM(#B.montoPagado) AS montoPagado,
			   0 AS saldo,
			   -2 As codpag,
			   'TOTAL PAGOS' AS nompag, 			   
			   '' AS estado,
			   Getdate() AS fechapago,
			   0 AS idbanco,
			   '' AS banco,
			   '' AS comprobante, 
			   0 AS origen
		FROM #B
		WHERE montoPagado > 0
END

--DOCUMENTOS NO PAGADOS
IF @condicion = 2
	BEGIN
		SELECT E.rutcte,
			   CONVERT(VARCHAR, E.rutcte) + '-' + CONVERT(VARCHAR, conCtacte.digcte) AS RutCliente,
			   conctacte.nomcte,
			   E.coddoc,
			   E.codsii,
			   venDocumentos.alias_doc,
			   E.numinv,
			   E.fecdoc,
			   E.fecven,
			   CASE (ROW_NUMBER() OVER (PARTITION BY E.codsii, E.numinv ORDER BY E.codsii, E.numinv))
			   WHEN 1 THEN (E.total - ISNULL((SELECT total FROM venEncabezado WHERE codsii = 61 AND motivo IN(1,3,4) AND tipref1 = E.codsii AND docref1 = E.numinv), 0))
			   ELSE 0 END AS total,
			   ISNULL((SELECT nompag FROM venCondicionPago WHERE venCondicionPago.codpag = E.codpag), '') AS codpagFact,
			   CASE ISNULL((SELECT id FROM cobPagoDocumentos WHERE tipo = P.tipo AND ndocumento = P.ndocumento AND fpago = P.fpago AND id = P.id), 0)
			   WHEN 0 THEN (E.total - venSaldoDocumentos.saldo - ISNULL((SELECT total FROM venEncabezado WHERE codsii = 61 AND motivo IN(1,3,4) AND tipref1 = E.codsii AND docref1 = E.numinv), 0))
			   ELSE P.monto
			   END AS montoPagado,
			   CASE (ROW_NUMBER() OVER (PARTITION BY E.codsii, E.numinv ORDER BY E.codsii, E.numinv))
			   WHEN 1 THEN venSaldoDocumentos.saldo
			   ELSE 0 END AS saldo,
			   ISNULL(CP.codpag,
			   (CASE
				WHEN (SELECT numinv FROM venReferencias R WHERE R.codsii = 61 AND (R.codsiiref = E.codsii AND R.numinvref = E.numinv)) IS NOT NULL THEN 0
				ELSE -1
				END)) AS codpag,
			   ISNULL(CP.nompag, 
			   (CASE
				WHEN (SELECT numinv FROM venReferencias R WHERE R.codsii = 61 AND (R.codsiiref = E.codsii AND R.numinvref = E.numinv)) IS NOT NULL 
				THEN 'NOTA DE CREDITO ' + CONVERT(VARCHAR(20), (SELECT numinv FROM venReferencias R WHERE R.codsii = 61 AND (R.codsiiref = E.codsii AND R.numinvref = E.numinv)))
				ELSE ''
				END)) AS nompag,
				'NO PAGADO' as estado,
			   ISNULL(C.fecha, '') as fechapago,
			   ISNULL(B.id_misbancos, 0) as idbanco,
			   ISNULL(B.nomban, '') as banco,
			   CASE P.tipocontable
			   WHEN 1 THEN 'INGRESO/' + CONVERT(varchar(20), p.comprocontable)
			   WHEN 2 THEN 'EGRESO/' + CONVERT(varchar(20), p.comprocontable)
			   WHEN 3 THEN 'TRASPASO/' + CONVERT(varchar(20), p.comprocontable)
			   ELSE ''
			   END AS comprobante, origen
	    INTO #C
		FROM venEncabezado E
			INNER JOIN conctacte ON E.rutcte = conctacte.rutcte
			INNER JOIN VenDocumentos ON E.codsii = venDocumentos.codsii
			INNER JOIN venSaldoDocumentos ON E.numinv = venSaldoDocumentos.numinv
										  AND  E.codsii = venSaldoDocumentos.codsii
			LEFT OUTER JOIN cobpagodocumentos P on E.numinv = P.ndocumento AND E.codsii = P.tipo
			LEFT JOIN VenCondicionPago CP ON P.fpago = CP.codpag
			LEFT JOIN finCartola C ON P.idcartola = C.id
			LEFT OUTER JOIN cobMisBancos B on B.id_MisBancos = C.id_misbancos
		WHERE (@rut = 0 OR E.rutcte = @rut)
			AND  (@tipoDoc = 0 OR E.codsii = @tipoDoc)
			AND  (@formaPagDoc = 0 OR E.codpag = @formaPagDoc)
			AND ( @condPag = 0 OR CP.codpag = @condPag )
			AND  E.fecdoc BETWEEN @fecini and @fecfin
			AND (E.codven = @vendedor OR @vendedor = 0) 
			AND  venSaldoDocumentos.saldo > 0

		SELECT 
			   0 AS rutcte,
			   '' AS RutCliente,
			   '' AS nomcte,
			   0 AS coddoc,
			   0 AS codsii,
			   '' AS alias_doc,
			   0 AS numinv,
			   Getdate() AS fecdoc,
			   Getdate() AS fecven,
			   0 AS total,
			   '' AS codpagFact,
			   SUM(#C.montoPagado) AS montoPagado,
			   0 AS saldo,
			   -2 As codpag,
			   #C.nompag, 			   
			   '' AS estado,
			   Getdate() AS fechapago,
			   0 AS idbanco,
			   '' AS banco,
			   '' AS comprobante, 
			   0 AS origen
		INTO #Resultado3
		FROM #C
		WHERE montoPagado > 0
		GROUP BY nompag

		SELECT * FROM #C

		UNION ALL

		SELECT * FROM #Resultado3

		UNION ALL

		SELECT 
			   0 AS rutcte,
			   '' AS RutCliente,
			   '' AS nomcte,
			   0 AS coddoc,
			   0 AS codsii,
			   '' AS alias_doc,
			   0 AS numinv,
			   Getdate() AS fecdoc,
			   Getdate() AS fecven,
			   0 AS total,
			   '' AS codpagFact,
			   SUM(#C.montoPagado) AS montoPagado,
			   0 AS saldo,
			   -2 As codpag,
			   'TOTAL PAGOS' AS nompag, 			   
			   '' AS estado,
			   Getdate() AS fechapago,
			   0 AS idbanco,
			   '' AS banco,
			   '' AS comprobante, 
			   0 AS origen
		FROM #C
		WHERE montoPagado > 0
END