-- obtenerCargosGenerales
SELECT
    CONCAT('$', FORMAT(COALESCE(SUM(CASE WHEN C.CANCELADO IS NULL OR C.CANCELADO = '' THEN C.IMPORTE ELSE 0 END), 0), 2)) AS 'Total Cargos',
    CONCAT('$', FORMAT(COALESCE(SUM(CASE WHEN C.CANCELADO IS NULL OR C.CANCELADO = '' THEN C.ABONOS ELSE 0 END), 0), 2))  AS 'Total Abonos',
    CONCAT('$', FORMAT(COALESCE(SUM(CASE WHEN C.CANCELADO IS NULL OR C.CANCELADO = '' THEN C.SALDO ELSE 0 END), 0), 2))   AS 'Saldo',
    CONCAT('$', FORMAT(COALESCE(MAX(SF.SALDO), 0), 2))                                                                    AS 'Saldo a Favor'
FROM PACIENTE P
LEFT JOIN CARGOS C
    ON P.CODIGOPACIENTE = C.CODIGOPACIENTE
LEFT JOIN SALDOAFAVOR SF
    ON P.CODIGOPACIENTE = SF.CODIGOPACIENTE
WHERE P.CODIGOPACIENTE = ?
GROUP BY P.CODIGOPACIENTE;

-- obtenerCargos
SELECT
    C.FOLIOCARGO AS 'Folio cargo',
    C.FECHA AS 'Fecha',
    I.DESCRIPCION AS 'Concepto',
    C.CANTIDAD AS 'Cantidad',
    CONCAT('$', FORMAT(CAST(C.IMPORTEUNITARIO AS DOUBLE), 2)) AS 'P. unitario',
    CONCAT('$', FORMAT(CAST(C.DESCUENTO AS DOUBLE), 2))      AS 'Descuento',
    CONCAT('$', FORMAT(CAST(C.IMPORTE AS DOUBLE), 2))        AS 'Importe',
    CONCAT('$', FORMAT(CAST(C.ABONOS AS DOUBLE), 2))         AS 'Abono',
    CONCAT('$', FORMAT(CAST(C.SALDO AS DOUBLE), 2))          AS 'Saldo',
    C.REFERENCIA AS 'Referencia'
FROM CARGOS C
JOIN C_CONCEPTOS_INGRESOS I
    ON C.CODIGOTRABAJO = I.CODIGO
WHERE C.CODIGOPACIENTE = ?
ORDER BY C.FECHA DESC, C.FOLIOCARGO DESC;

-- obtenerAbonos
SELECT
    E.FECHA AS 'Fecha',
    E.FOLIORECIBO AS 'Folio',
    CASE E.TIPO_ABONO
        WHEN 'R' THEN 'Recibo'
        WHEN 'F' THEN 'Factura'
        ELSE E.TIPO_ABONO
    END AS 'Tipo',
    CONCAT('$', FORMAT(CAST(E.TOTAL AS DOUBLE), 2)) AS 'Total',
    E.REFERENCIA AS 'Referencia',
    FP.METODOS_PAGO AS 'Formas de pago'
FROM ABONOSENC E
LEFT JOIN (
    SELECT
        AF.FOLIORECIBO,
        GROUP_CONCAT(
            CASE AF.FORMAPAGO
                WHEN 0 THEN 'SALDO A FAVOR USADO'
                ELSE F.DESCRIPCION
            END
            SEPARATOR ', '
        ) AS METODOS_PAGO
    FROM ABONOFORMAPAGO AF
    LEFT JOIN CENTRAL.C_FORMASDEPAGO F
        ON F.IDFORMAPAGO = AF.FORMAPAGO
    GROUP BY AF.FOLIORECIBO
) FP
    ON FP.FOLIORECIBO = E.FOLIORECIBO
WHERE E.CODIGOPACIENTE = ?
ORDER BY E.FECHA DESC, E.FOLIORECIBO DESC;

-- obtenerCotizaciones
SELECT
    C.FOLIOCOTIZACION AS 'Folio cotización',
    C.FECHA AS 'Fecha',
    I.DESCRIPCION AS 'Concepto',
    C.CANTIDAD AS 'Cantidad',
    CONCAT('$', FORMAT(CAST(C.IMPORTEUNITARIO AS DOUBLE), 2)) AS 'P. unitario',
    CONCAT('$', FORMAT(CAST(C.DESCUENTO AS DOUBLE), 2))       AS 'Descuento',
    CONCAT('$', FORMAT(CAST(C.IMPORTE AS DOUBLE), 2))         AS 'Importe',
    C.NOTA AS 'Nota'
FROM COTIZACIONES C
JOIN C_CONCEPTOS_INGRESOS I
    ON C.CODIGOTRABAJO = I.CODIGO
WHERE C.CODIGOPACIENTE = ?
ORDER BY C.FECHA DESC, C.FOLIOCOTIZACION DESC;
