ALTER PROCEDURE [dbo].[sp_cobDocumentosGestiones]
(
    @ruti int=0,
    @rutf int=99999999,
    @cobrador int=0,
    @tipofiltro int=1, --1=TODOS 2-PORVENCER  3-VENCIDOS
    @fechacontrol date,
    @grupo1 int=0,
    @grupo2 int=0,
    @grupo3 int=0,
    @cancelados INT= 0,
    @tipocliente INT= 0,
    @checkconceptos as int = 0,
    @estadopago int = 0,  --0 = TODOS, 1 = PAGADOS, 2 = NO PAGADOS,
    @ndocumento int = 0,
    @rango bit = 0,
    @fecini date = '01/01/1900',
    @fecfin date = '01/01/2050',
    @origen int = 0
)
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @cuenta INT;
    DECLARE @conventa INT;
    
    SELECT @cuenta = ctacliente, @conventa = ventasnocon, @checkconceptos = checkconceptos 
    FROM cobParametros;
    
    -- Tabla de clientes válidos
    SELECT rutcte AS RUT 
    INTO #CTACTE
    FROM conCtacte WITH (NOLOCK)
    WHERE rutcte > 0 
        AND (tipocliente = @tipocliente OR @tipocliente = 0);
    
    CREATE CLUSTERED INDEX IX_CTACTE ON #CTACTE(RUT);
    
    -- COMPROBANTES (SIN filtro de finConceptos)
    SELECT 
        0 AS seleccion,
        conComprobantes.ctacte as rut,
        conComprobantes.tipo_docto as tipo,
        conComprobantes.num_docto as numero,
        conComprobantes.fecha_emision as fechaemi,
        conComprobantes.vencimiento as fechaven,
        conComprobantes.debe,
        conComprobantes.haber,
        '' as url
    INTO #COMPROBANTES
    FROM conComprobantes WITH (NOLOCK)
    INNER JOIN #CTACTE ON conComprobantes.ctacte = #CTACTE.RUT
    WHERE conComprobantes.ctacte BETWEEN @ruti AND @rutf
        AND (conComprobantes.cuenta = @cuenta OR @cuenta = 0)
        AND conComprobantes.tipo_docto NOT IN (60,61) 
        AND RTRIM(conComprobantes.sistema) <> '';
    
    CREATE CLUSTERED INDEX IX_COMP ON #COMPROBANTES(rut, tipo, numero);
    
    -- PAGOS
    SELECT 
        0 AS seleccion,
        P.rut,
        P.tipo,
        P.ndocumento AS numero,
        CAST('2078-12-31' AS DATE) AS fechaemi,
        CAST('2078-12-31' AS DATE) AS fechaven,
        0 as debe,
        P.monto AS haber,
        '' as url
    INTO #PAGOS
    FROM cobPagoDocumentos P WITH (NOLOCK)
    INNER JOIN #CTACTE C ON P.rut = C.RUT
    WHERE P.rut BETWEEN @ruti AND @rutf
        AND P.comprocontable = 0;
    
    -- VENTAS
    SELECT  
        0 AS seleccion,
        venEncabezado.rutcte AS rut,
        venEncabezado.codsii AS tipo,
        venEncabezado.numinv AS numero,
        venEncabezado.fecdoc as fechaemi,
        venEncabezado.fecven AS fechaven,
        venEncabezado.total AS debe,
        0 AS haber,
        venEncabezado.url
    INTO #VENTAS
    FROM venEncabezado WITH (NOLOCK)
    INNER JOIN #CTACTE ON venEncabezado.rutcte = #CTACTE.RUT
    WHERE venEncabezado.rutcte BETWEEN @ruti AND @rutf
        AND venEncabezado.numcom = 0
        AND venEncabezado.grudoc = 10
        AND venEncabezado.codsii NOT IN (60,61)
        AND venEncabezado.estado <> 9
        AND @conventa = 1
        AND ((ISNULL(venEncabezado.origen,0) <> 2 OR @origen = 2 OR @origen = 0) 
            AND (venEncabezado.origen = 2 OR @origen = 1 OR @origen = 0));
    
    CREATE CLUSTERED INDEX IX_VENTAS ON #VENTAS(rut, tipo, numero);
    
    -- NOTAS DE CREDITO DE VENTAS (con CONVERT como en original)
    SELECT  
        0 AS seleccion,
        venEncabezado.rutcte as rut,
        venEncabezado.tipref1 as tipo,
        venEncabezado.docref1 as numero,
        venEncabezado.fecdoc AS fechaemi,
        VE.fechaven,
        0 AS debe,
        venEncabezado.total AS haber,
        venEncabezado.url
    INTO #NCVENTAS
    FROM venEncabezado WITH (NOLOCK)
    INNER JOIN #VENTAS AS VE ON venEncabezado.tipref1 = CONVERT(INT, VE.tipo) 
        AND venEncabezado.docref1 = CONVERT(VARCHAR(50), VE.numero)
        AND venEncabezado.rutcte = CONVERT(INT, VE.rut)
    WHERE venEncabezado.rutcte BETWEEN @ruti AND @rutf
        AND venEncabezado.numcom = 0
        AND venEncabezado.grudoc = 10
        AND venEncabezado.codsii IN (60,61)
        AND venEncabezado.estado <> 9
        AND @conventa = 1
        AND ((ISNULL(venEncabezado.origen,0) <> 2 OR @origen = 2 OR @origen = 0) 
            AND (venEncabezado.origen = 2 OR @origen = 1 OR @origen = 0));
    
    -- NOTAS DE CREDITO DE COMPROBANTES (con CONVERT como en original)
    SELECT 
        0 AS seleccion,
        venEncabezado.rutcte as rut, 
        venEncabezado.tipref1 as tipo, 
        venEncabezado.docref1 as numero,
        venEncabezado.fecdoc AS fechaemi, 
        C.fechaven,
        0 AS debe,
        venEncabezado.total AS haber,
        venEncabezado.url
    INTO #NOTACREDITO
    FROM venEncabezado WITH (NOLOCK)
    INNER JOIN #COMPROBANTES AS C ON venEncabezado.rutcte = C.rut
        AND venEncabezado.tipref1 = C.tipo 
        AND venEncabezado.docref1 = CONVERT(VARCHAR(50), C.numero)
    WHERE venEncabezado.rutcte BETWEEN @ruti AND @rutf
        AND venEncabezado.grudoc = 10
        AND venEncabezado.codsii IN (60,61)
        AND venEncabezado.estado <> 9
        AND venEncabezado.numcom = 0 
        AND venencabezado.tipcom = 0
        AND (C.DEBE - C.HABER) > 0
        AND ((ISNULL(venEncabezado.origen,0) <> 2 OR @origen = 2 OR @origen = 0) 
            AND (venEncabezado.origen = 2 OR @origen = 1 OR @origen = 0));
    
    -- Consolidar todas las tablas
    SELECT * 
    INTO #A
    FROM #COMPROBANTES
    UNION ALL
    SELECT * FROM #PAGOS
    UNION ALL
    SELECT * FROM #VENTAS
    UNION ALL 
    SELECT * FROM #NCVENTAS
    UNION ALL
    SELECT * FROM #NOTACREDITO;
    
    -- Agrupar con HAVING para filtrar saldos cero
    SELECT 
        0 AS seleccion,
        rut,
        tipo,
        numero,
        MIN(fechaemi) as fechaemi,
        MIN(fechaven) as fechaven,
        SUM(debe) as debe,
        SUM(haber) as haber,
        SUM(debe) - SUM(haber) AS saldo,
        MAX(url) as url
    INTO #B
    FROM #A 
    GROUP BY rut, tipo, numero
    HAVING SUM(debe) - SUM(haber) <> 0;
    
    CREATE CLUSTERED INDEX IX_B ON #B(rut, tipo, numero);
    
    -- Query final unificada
    SELECT 
        0 AS seleccion,
        B.rut,
        CC.digcte AS dv,
        RTRIM(B.rut) + '-' + RTRIM(CC.digcte) AS rut_dv,
        CC.nomcte,
        B.tipo,
        B.numero,
        ISNULL(VE.fecdoc, CAST('1900-01-01' AS DATE)) as fechaemi,
        B.fechaven,
        B.debe,
        B.haber,
        COALESCE(VD.alias_doc, 'NO DEFINIDO' + ' - ' + CONVERT(VARCHAR, B.tipo)) AS alias_doc,
        VD.nomdoc,
        VE.url,
        B.saldo,
        0 as excedente,
        CC.cobrador,
        DATEDIFF(day, B.fechaven, GETDATE()) as dias_mora,
        CC.pagocliente,
        CC.grupo1,
        CC.grupo2,
        CC.grupo3,
        COB.nomcob AS nom_cobrador,
        ISNULL(VE.origen, 0) as origen,
        VE.codven,
        VV.nomven,
        0 AS seleccion2,
        G1.nomgru1,
        G2.nomgru2,
        G3.nomgru3,
        0 AS numero2
    FROM #B B
    INNER JOIN venDocumentosSii VDS WITH (NOLOCK) ON B.tipo = VDS.codsii
    LEFT OUTER JOIN conCtacte CC WITH (NOLOCK) ON B.rut = CC.rutcte
    LEFT OUTER JOIN venDocumentos VD WITH (NOLOCK) ON VDS.codsii = VD.codsii
    LEFT OUTER JOIN venEncabezado VE WITH (NOLOCK) ON B.tipo = VE.codsii AND B.numero = VE.numinv
    LEFT OUTER JOIN cobCobradores COB WITH (NOLOCK) ON COB.codcob = CC.cobrador
    LEFT OUTER JOIN venVendedores VV WITH (NOLOCK) ON VV.codven = VE.codven
    LEFT OUTER JOIN cobGrupo1 G1 WITH (NOLOCK) ON CC.grupo1 = G1.grupo1
    LEFT OUTER JOIN cobGrupo2 G2 WITH (NOLOCK) ON CC.grupo2 = G2.grupo2 AND CC.grupo1 = G2.grupo1
    LEFT OUTER JOIN cobGrupo3 G3 WITH (NOLOCK) ON CC.grupo3 = G3.grupo3 AND CC.grupo2 = G3.grupo2 AND CC.grupo1 = G3.grupo1
    WHERE (B.saldo > 0 OR @cancelados = 1)
        AND (CC.cobrador = @cobrador OR @cobrador = 0)
        AND (CC.grupo1 = @grupo1 OR @grupo1 = 0 OR B.rut = 0)
        AND (CC.grupo2 = @grupo2 OR @grupo2 = 0 OR B.rut = 0)
        AND (CC.grupo3 = @grupo3 OR @grupo3 = 0 OR B.rut = 0)
        AND ((ISNULL(VE.origen,0) <> 2 OR @origen = 2 OR @origen = 0) 
            AND (VE.origen = 2 OR @origen = 1 OR @origen = 0))
        -- Filtro por tipo
        AND (
            (@tipofiltro = 1) OR
            (@tipofiltro = 2 AND CONVERT(DATE, B.fechaven) > @fechacontrol) OR
            (@tipofiltro = 3 AND CONVERT(DATE, B.fechaven) < @fechacontrol)
        )
        -- Filtros de estado pago
        AND ((B.haber > 0 OR @estadopago = 2 OR @estadopago = 0) 
            AND (B.numero = @ndocumento OR @ndocumento = 0) 
            AND ((fechaemi >= @fecini AND fechaemi <= @fecfin) OR @rango = 0))
        AND ((B.haber = 0 OR @estadopago = 1 OR @estadopago = 0) 
            AND (B.numero = @ndocumento OR @ndocumento = 0) 
            AND ((fechaemi >= @fecini AND fechaemi <= @fecfin) OR @rango = 0))
    ORDER BY CC.nomcte, dias_mora DESC;
    
    -- Limpiar
    DROP TABLE #CTACTE;
    DROP TABLE #COMPROBANTES;
    DROP TABLE #PAGOS;
    DROP TABLE #VENTAS;
    DROP TABLE #NCVENTAS;
    DROP TABLE #NOTACREDITO;
    DROP TABLE #A;
    DROP TABLE #B;
END