SET SERVEROUTPUT ON
--------------------------------------------------------------------------------

--EJERCICIO A:

DECLARE
    V_RUT CLIENTE.RUTCLIENTE%TYPE;
    V_NOMBRE CLIENTE.NOMBRE%TYPE;
    V_CANT_FACTURAS NUMBER := 0;
    V_SUMA_FACTURAS NUMBER := 0;
    V_PROMEDIO_FACTURAS NUMBER := 0;
    V_MAX_FACTURAS FACTURA.TOTAL%TYPE := 0;
    V_MIN_FACTURAS FACTURA.TOTAL%TYPE := 0;

    ERR_CODIGO NUMBER;
    ERR_MENSAJE VARCHAR(255);

BEGIN

    SELECT
        C.RUTCLIENTE,
        C.NOMBRE,
        COUNT(F.RUTCLIENTE),
        SUM(F.TOTAL),
        AVG(F.TOTAL),
        MAX(F.TOTAL),
        MIN(F.TOTAL)
    INTO
        V_RUT,
        V_NOMBRE,
        V_CANT_FACTURAS,
        V_SUMA_FACTURAS,
        V_PROMEDIO_FACTURAS,
        V_MAX_FACTURAS,
        V_MIN_FACTURAS
    FROM CLIENTE C
    JOIN FACTURA F
    ON C.RUTCLIENTE = F.RUTCLIENTE
    GROUP BY C.RUTCLIENTE, C.NOMBRE
    HAVING COUNT(F.RUTCLIENTE) = (SELECT MAX(COUNT(F.RUTCLIENTE))
                                    FROM CLIENTE C
                                    JOIN FACTURA F
                                    ON C.RUTCLIENTE = F.RUTCLIENTE
                                    GROUP BY C.RUTCLIENTE, C.NOMBRE);

    DBMS_OUTPUT.PUT_LINE('Rut de Cliente: ' || V_RUT);
    DBMS_OUTPUT.PUT_LINE('Nombre de Cliente: ' || V_NOMBRE);
    DBMS_OUTPUT.PUT_LINE('Cantidad Facturas: ' || V_CANT_FACTURAS);
    DBMS_OUTPUT.PUT_LINE('Monto Total Facturas: ' || V_SUMA_FACTURAS);
    DBMS_OUTPUT.PUT_LINE('Monto Promedio Facturas: ' || V_PROMEDIO_FACTURAS);
    DBMS_OUTPUT.PUT_LINE('Monto Maximo Facturas: '|| V_MAX_FACTURAS);
    DBMS_OUTPUT.PUT_LINE('Monto Minimo Facturas: '|| V_MIN_FACTURAS);

    EXCEPTION
    WHEN OTHERS THEN
        ERR_CODIGO := SQLCODE;
        ERR_MENSAJE:= SQLERRM;

        DBMS_OUTPUT.PUT_LINE('CODIGO DEL ERROR :' || ERR_CODIGO);
        DBMS_OUTPUT.PUT_LINE('MENSAJE ERROR :'    || ERR_MENSAJE);
END;

--EJERCICIO B:

DECLARE

    V_RUT1 CLIENTE.RUTCLIENTE%TYPE;
    V_NOMBRE1 CLIENTE.NOMBRE%TYPE;
    V_TOTAL_FACTURA1 NUMBER := 0;
    V_RUT2 CLIENTE.RUTCLIENTE%TYPE;
    V_NOMBRE2 CLIENTE.NOMBRE%TYPE;
    V_TOTAL_FACTURA2 NUMBER := 0;

    ERR_CODIGO NUMBER;
    ERR_MENSAJE VARCHAR(255);

BEGIN

    SELECT
        C.RUTCLIENTE,
        C.NOMBRE,
        SUM(F.TOTAL)
    INTO
        V_RUT1,
        V_NOMBRE1,
        V_TOTAL_FACTURA1
    FROM CLIENTE C
    JOIN FACTURA F
    ON C.RUTCLIENTE = F.RUTCLIENTE
    GROUP BY C.RUTCLIENTE, C.NOMBRE
    HAVING SUM(F.TOTAL) = (SELECT MAX(SUM(F.TOTAL))
                            FROM CLIENTE C
                            JOIN FACTURA F
                            ON C.RUTCLIENTE = F.RUTCLIENTE
                            GROUP BY C.RUTCLIENTE, C.NOMBRE);

    SELECT
        C.RUTCLIENTE,
        C.NOMBRE,
        SUM(F.TOTAL)
    INTO
        V_RUT2,
        V_NOMBRE2,
        V_TOTAL_FACTURA2
    FROM CLIENTE C
    JOIN FACTURA F
    ON C.RUTCLIENTE = F.RUTCLIENTE
    GROUP BY C.RUTCLIENTE, C.NOMBRE
    HAVING SUM(F.TOTAL) = (SELECT MIN(SUM(F.TOTAL))
                            FROM CLIENTE C
                            JOIN FACTURA F
                            ON C.RUTCLIENTE = F.RUTCLIENTE
                            GROUP BY C.RUTCLIENTE, C.NOMBRE);


    DBMS_OUTPUT.PUT_LINE('Cliente con mayor facturacion:');
    DBMS_OUTPUT.PUT_LINE('------------------------------');
    DBMS_OUTPUT.PUT_LINE('Rut: ' || V_RUT1 || ' Nombre: ' || V_NOMBRE1 || ' Monto Facturado: ' || V_TOTAL_FACTURA1);

    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('');

    DBMS_OUTPUT.PUT_LINE('Cliente con menor facturacion:');
    DBMS_OUTPUT.PUT_LINE('------------------------------');
    DBMS_OUTPUT.PUT_LINE('Rut: ' || V_RUT2 || ' Nombre: ' || V_NOMBRE2 || ' Monto Facturado: ' || V_TOTAL_FACTURA2);

    EXCEPTION
    WHEN OTHERS THEN
        ERR_CODIGO := SQLCODE;
        ERR_MENSAJE:= SQLERRM;

        DBMS_OUTPUT.PUT_LINE('CODIGO DEL ERROR :' || ERR_CODIGO);
        DBMS_OUTPUT.PUT_LINE('MENSAJE ERROR :'    || ERR_MENSAJE);

END;

--EJERCICIO C:

DECLARE

    V_RUT CLIENTE.RUTCLIENTE%TYPE;
    V_NOMBRE CLIENTE.NOMBRE%TYPE;
    V_DIRECCION CLIENTE.DIRECCION%TYPE;
    V_COMUNA CLIENTE.CODCOMUNA%TYPE;
    V_CREDITO CLIENTE.CREDITO%TYPE;
    V_SALDO CLIENTE.SALDO%TYPE;

    ERR_CODIGO NUMBER;
    ERR_MENSAJE VARCHAR(255);

BEGIN

    SELECT
        C.RUTCLIENTE,
        C.NOMBRE,
        C.DIRECCION,
        C.CODCOMUNA,
        C.CREDITO,
        C.SALDO
    INTO
        V_RUT,
        V_NOMBRE,
        V_DIRECCION,
        V_COMUNA,
        V_CREDITO,
        V_SALDO
    FROM CLIENTE C
    FULL JOIN FACTURA F
    ON C.RUTCLIENTE = F.RUTCLIENTE
    GROUP BY C.RUTCLIENTE, C.NOMBRE, C.DIRECCION, C.CODCOMUNA, C.CREDITO, C.SALDO
    HAVING SUM(F.TOTAL) = (SELECT MAX(SUM(F.TOTAL))
                            FROM CLIENTE C
                            JOIN FACTURA F
                            ON C.RUTCLIENTE = F.RUTCLIENTE
                            GROUP BY C.RUTCLIENTE, C.NOMBRE);

    DBMS_OUTPUT.PUT_LINE('ANTECEDENTES DE CLIENTE CON MAYOR FACTURACION');
    DBMS_OUTPUT.PUT_LINE('---------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('Rut Cliente: ' || TRIM(V_RUT) || '      Nombre Cliente: ' || TRIM(V_NOMBRE));
    DBMS_OUTPUT.PUT_LINE('Direccion: ' || TRIM(V_DIRECCION) || '        Comuna: ' || TRIM(V_COMUNA));
    DBMS_OUTPUT.PUT_LINE('Credito Aprovado: ' || TRIM(V_CREDITO) || '      Saldo Credito: ' || TRIM(V_SALDO));

    EXCEPTION
    WHEN OTHERS THEN
        ERR_CODIGO := SQLCODE;
        ERR_MENSAJE:= SQLERRM;

        DBMS_OUTPUT.PUT_LINE('CODIGO DEL ERROR :' || ERR_CODIGO);
        DBMS_OUTPUT.PUT_LINE('MENSAJE ERROR :'    || ERR_MENSAJE);

END;

--EJERCICIO D:

DECLARE

    V_CODPRODUCTO PRODUCTO.CODPRODUCTO%TYPE;
    V_DESCRIPCION PRODUCTO.DESCRIPCION%TYPE;
    V_CODUNIDAD PRODUCTO.CODUNIDAD%TYPE;
    V_PROCEDENCIA PRODUCTO.PROCEDENCIA%TYPE;
    V_TOTALSTOCK PRODUCTO.TOTALSTOCK%TYPE;
    V_STKSEGURIDAD PRODUCTO.STKSEGURIDAD%TYPE;
    V_CANTIDAD DETALLE_FACTURA.CANTIDAD%TYPE;
    V_AUX1 VARCHAR2(15);
    V_AUX2 VARCHAR2(15);

    ERR_CODIGO NUMBER;
    ERR_MENSAJE VARCHAR(255);

BEGIN

    SELECT
        PR.CODPRODUCTO,
        PR.DESCRIPCION,
        PR.CODUNIDAD,
        PR.PROCEDENCIA,
        PR.TOTALSTOCK,
        PR.STKSEGURIDAD,
        DF.CANTIDAD
    INTO
        V_CODPRODUCTO,
        V_DESCRIPCION,
        V_CODUNIDAD,
        V_PROCEDENCIA,
        V_TOTALSTOCK,
        V_STKSEGURIDAD,
        V_CANTIDAD

    FROM PRODUCTO PR
    JOIN DETALLE_FACTURA DF
    ON PR.CODPRODUCTO = DF.CODPRODUCTO
    GROUP BY PR.CODPRODUCTO, PR.DESCRIPCION, PR.CODUNIDAD, PR.PROCEDENCIA, PR.TOTALSTOCK, PR.STKSEGURIDAD, DF.CANTIDAD
    HAVING MAX(CANTIDAD) = (SELECT MAX(CANTIDAD) FROM DETALLE_FACTURA);

    IF V_CODUNIDAD = 'UN' THEN
      V_AUX1 := 'UNITARIO';
    END IF;

    IF V_PROCEDENCIA = 'N' THEN
      V_AUX2 := 'Nacional';
    ELSE
      V_AUX2 := 'Internacional';
    END IF;


    DBMS_OUTPUT.PUT_LINE('INFORME DE RANKING DE VENTAS DE PRODUCTOS');
    DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------------------------------------------------------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('CODPRODUCTO:' || V_CODPRODUCTO || ' - DESCRIPCION:' || TRIM(V_DESCRIPCION) || ' - UNIDAD MEDIDA:' || V_AUX1 || ' - PROCEDENCIA:' || V_AUX2 ||
                         ' - TOTAL STOCK:' || V_TOTALSTOCK || ' - STOCK SEGURIDAD:' || V_STKSEGURIDAD || ' - TOTAL FACTURADO:' || V_CANTIDAD);

    EXCEPTION
    WHEN OTHERS THEN
        ERR_CODIGO := SQLCODE;
        ERR_MENSAJE:= SQLERRM;

        DBMS_OUTPUT.PUT_LINE('CODIGO DEL ERROR :' || ERR_CODIGO);
        DBMS_OUTPUT.PUT_LINE('MENSAJE ERROR :'    || ERR_MENSAJE);

END;

--EJERCICIO E:

DECLARE

    V_TOTAL_VALORPESO NUMBER := 0;
    V_TOTAL_VALORDOLAR NUMBER := 0;
    V_TOTAL NUMBER := 0;

    ERR_CODIGO NUMBER;
    ERR_MENSAJE VARCHAR(255);

BEGIN
    SELECT
      SUM(VALORPESO * TOTALSTOCK)
    INTO
      V_TOTAL_VALORPESO
    FROM PRODUCTO
    WHERE PROCEDENCIA = 'N';

    SELECT
      SUM(VALORDOLAR * TOTALSTOCK * 628.65)
    INTO
      V_TOTAL_VALORDOLAR
    FROM PRODUCTO
    WHERE PROCEDENCIA = 'I';

    DBMS_OUTPUT.PUT_LINE('INFORME DE VALORIZACION DE BODEGA');
    DBMS_OUTPUT.PUT_LINE('--------------------------------------');
    DBMS_OUTPUT.PUT_LINE('SUMA PRODUCTO NACIONAL: ' || V_TOTAL_VALORPESO);
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('SUMA PRODUCTO IMPORTADO: ' || V_TOTAL_VALORDOLAR);
    DBMS_OUTPUT.PUT_LINE(' ');
    V_TOTAL := V_TOTAL_VALORDOLAR + V_TOTAL_VALORPESO;
    DBMS_OUTPUT.PUT_LINE('SUMA TOTAL (NACIONAL + IMPORTADO): ' || V_TOTAL);

    EXCEPTION
    WHEN OTHERS THEN
        ERR_CODIGO := SQLCODE;
        ERR_MENSAJE:= SQLERRM;

        DBMS_OUTPUT.PUT_LINE('CODIGO DEL ERROR :' || ERR_CODIGO);
        DBMS_OUTPUT.PUT_LINE('MENSAJE ERROR :'    || ERR_MENSAJE);
END;