repasoEjerciciosDriveTG

-- Crear tablas
create table cargo (
    id_cargo varchar(3) primary key,
    nombre_cargo varchar(50)
);

create table tipo (
    id_tipo varchar(3) primary key,
    nombre_tipo varchar(50)
);

create table proveedor (
    cuit_proveedor varchar(20) primary key,
    nombre_proveedor varchar(50)
);

create table empleado (
    codigo_empleado int primary key,
    nombre_empleado varchar(50),
    dni_empleado varchar(10),
    id_cargo varchar(3),
    foreign key (id_cargo) references cargo(id_cargo)
);

create table cliente (
    codigo_cliente int primary key,
    cuit_cliente varchar(20),
    nombre_cliente varchar(50),
    telefono_cliente varchar(20),
    id_tipo varchar(3),
    foreign key (id_tipo) references tipo(id_tipo)
);

create table producto (
    codigo_producto int primary key,
    nombre_producto varchar(50),
    precio_producto numeric(10, 2),
    cuit_proveedor varchar(20),
    foreign key (cuit_proveedor) references proveedor(cuit_proveedor)
);

create table pedido (
    codigo_pedido int primary key,
    codigo_cliente int,
    codigo_empleado int,
    codigo_compania int,
    fecha_pedido date,
    fecha_envio date,
    direccion_destinatario varchar(100),
    foreign key (codigo_cliente) references cliente(codigo_cliente),
    foreign key (codigo_empleado) references empleado(codigo_empleado)
);

create table detalle_pedido (
    codigo_pedido int,
    codigo_producto int,
    cantidad int,
    porcentaje_descuento numeric(5, 2),
    primary key (codigo_pedido, codigo_producto),
    foreign key (codigo_pedido) references pedido(codigo_pedido),
    foreign key (codigo_producto) references producto(codigo_producto)
);

create table compania (
    codigo_compania int primary key,
    nombre_compania varchar(50),
    telefono_compania varchar(20),
    codigo_provincia int
);

create table provincia (
    codigo_provincia int primary key,
    nombre_provincia varchar(50)
);

-- Cargar datos en las tablas
insert into cargo (id_cargo, nombre_cargo) values
('ger', 'gerente'),
('enc', 'encargado'),
('ven', 'vendedor');

insert into tipo (id_tipo, nombre_tipo) values
('cf', 'consumidor final'),
('ri', 'responsable inscripto'),
('ex', 'exento');

insert into proveedor (cuit_proveedor, nombre_proveedor) values
('11-11111111-1', 'proveedor1'),
('22-22222222-2', 'proveedor2'),
('33-33333333-3', 'proveedor3');

insert into empleado (codigo_empleado, nombre_empleado, dni_empleado, id_cargo) values
(2810, 'empleado1', '22222222', 'ven'),
(2811, 'empleado2', '33333333', 'ven'),
(2812, 'empleado3', '44444444', 'enc'),
(2813, 'empleado4', '55555555', 'ger'),
(2814, 'empleado5', '11111111', 'ven');

insert into cliente (codigo_cliente, cuit_cliente, nombre_cliente, telefono_cliente, id_tipo) values
(1001, '20-2110010-1', 'cliente1', '111', 'cf'),
(2002, '20-2120020-2', 'cliente2', '222', 'ex'),
(3003, '20-2130030-3', 'cliente3', '333', 'cf'),
(4004, '20-2140040-4', 'cliente4', '444', 'ri'),
(5005, '20-2150050-5', 'cliente5', '555', 'ri');

insert into producto (codigo_producto, nombre_producto, precio_producto, cuit_proveedor) values
(111, 'pendrive 32gb', 3550.00, '11-11111111-1'),
(222, 'cámara web', 16000.00, '11-11111111-1'),
(333, 'resma a4', 3000.00, '22-22222222-2'),
(444, 'caja 100dvd', 12000.00, '33-33333333-3'),
(555, 'pendrive 64gb', 5000.00, '22-22222222-2');

insert into pedido (codigo_pedido, codigo_cliente, codigo_empleado, codigo_compania, fecha_pedido, fecha_envio, direccion_destinatario) values
(1, 1001, 2814, 10, '2023-09-29', '2021-10-02', 'direccion1'),
(2, 4004, 2814, 20, '2023-09-29', '2021-10-03', 'direccion2'),
(3, 1001, 2810, 40, '2023-09-29', '2021-10-10', 'direccion3'),
(4, 2002, 2814, 30, '2023-10-02', '2021-10-02', 'direccion4'),
(5, 3003, 2811, 30, '2023-10-02', '2021-10-03', 'direccion5'),
(6, 5005, 2810, 30, '2023-10-03', '2021-10-05', 'direccion6');

insert into detalle_pedido (codigo_pedido, codigo_producto, cantidad, porcentaje_descuento) values
(1, 111, 2, 0),
(1, 222, 5, 0),
(3, 444, 20, 10.00),
(4, 333, 10, 5.00),
(5, 555, 1, 0),
(5, 111, 1, 0),
(5, 222, 1, 15.00),
(5, 333, 1, 0),
(6, 555, 5, 10.00);

insert into compania (codigo_compania, nombre_compania, telefono_compania, codigo_provincia) values
(10, 'compañía1', '155155155', 381),
(20, 'compañía2', '152152152', 388),
(30, 'compañía3', '153153153', 388),
(40, 'compañía4', '154154154', 387);

insert into provincia (codigo_provincia, nombre_provincia) values
(381, 'jujuy'),
(388, 'tucumán'),
(387, 'salta');

--1. Realice una función que muestre por pantalla la cantidad total de registros de todas las tablas.

create or replace function total_registros()
returns void as $$
declare
    total integer;
begin
    select ((select count(*) cargo)+
    (select count(*) tipo_cliente)+
    (select count(*) proveedor)+
    (select count(*)empleado)+
    (select count (*)cliente)+
    (select count(*) producto)+
    (select count(*) pedido)+
    (select count(*)detalle_pedido)+
    (select count(*) compania)+
    (select count(*) provincia))
    into total;

    raise notice 'Total de registros: %', total;
end;
$$language plpgsql;

select total_registros();

create or replace function total()
returns int as $$
declare
    total integer;
begin
    select ((select count(*) cargo)+
    (select count(*) tipo_cliente)+
    (select count(*) proveedor)+
    (select count(*)empleado)+
    (select count (*)cliente)+
    (select count(*) producto)+
    (select count(*) pedido)+
    (select count(*)detalle_pedido)+
    (select count(*) compania)+
    (select count(*) provincia))
    into total;

    return total;
end;
$$language plpgsql;

select total();

--2. Realice una función que reciba como parámetro el código de provincia y devuelva el nombre de las compañías asociadas con el mismo.

create or replace function asociadas(codigoprov integer)
returns table(nombre_comp varchar, codigo_prov integer) as $$
begin
    return query
    select c.nombre_compania, p.codigo_provincia from compania c
    inner join provincia p on c.codigo_provincia = p.codigo_provincia
    where p.codigo_provincia = codigoprov;
end;
$$language plpgsql;

drop function asociadas;
select * from asociadas (388);

--3. Realice una función que reciba como parámetro una fecha de pedido y devuelva el monto total de pedidos para esa fecha.

create or replace function monto_total(fechap date)
returns numeric as $$
declare
    total numeric;
begin
    select sum(dp.cantidad * p.precio_producto * (1- dp.porcentaje_descuento / 100))
    into total
    from detalle_pedido dp
    inner join pedido pe on dp.codigo_pedido = pe.codigo_pedido
    inner join producto p on dp.codigo_producto = dp.codigo_producto
    where pe.fecha_pedido = fechap;

    if total is null then
        total := 0;
    end if;

    return total;
end;
$$language plpgsql;

select * from monto_total ('03-10-2023');

--4. Realice una función que reciba como parámetros una “Fecha Pedido desde” y una “Fecha
--Pedido hasta”, cargue el cursor con los datos de pedidos, lea el cursor y grabe dichos
--datos en la tabla “pedido_especial”

create table pedido_especial(fecha_pedido date, fecha_envio date, direccion_destinatario varchar (20));

create or replace function grabar(desde date,hasta date)
returns void as $$
declare
    datos_pedidos cursor for
        select pe.fecha_pedido, pe.fecha_envio, pe.direccion_destinatario
        from pedido pe
        where pe.fecha_pedido between desde and hasta;

    v_fecha_pedido date;
    v_fecha_envio date;
    v_direccion varchar(20);
begin

    open datos_pedidos;

    loop
        fetch  datos_pedidos into v_fecha_pedido,v_fecha_envio,v_direccion;
        exit when not found;

        insert into pedido_especial(fecha_pedido,fecha_envio,direccion_destinatario)
        values(v_fecha_pedido,v_fecha_envio,v_direccion);

    end loop;

    close datos_pedidos;

end;
$$language plpgsql;

drop function grabar();
select grabar('02-10-2023','03-10-2023');
select * from pedido_especial;
select grabar('29-09-2023','03-10-2023');

--Realice un trigger que antes de guardar un nuevo detalle de pedido con un producto cuyo
--precio sea superior a $10.000,00; le aplique un porcentaje descuento a 10% (valor a
--guardar en la columna respectiva).


create or replace function aplicarpor()
returns trigger as $$
begin
    if(select precio_producto from producto  where codigo_producto = new.codigo_producto)>10000 then
        new.porcentaje_descuento :=10;
    end if;

    return new;
end;
$$language plpgsql;

create trigger verificaraplicacion
before insert on detalle_pedido
for each row
execute function aplicarpor();

--Realice la creación de la tabla “Pedidos Pendientes” con los siguientes campos: código
--pedido, nombre cliente, nombre empleado, nombre compania, fecha pedido. Crear un
--trigger que luego de insertar un pedido sin fecha de envío, le asigne la fecha actual,
--guarde sus datos en la tabla “Pedidos Pendientes”.

create table pedidos_pendientes(
    codigo_pedido integer,
    nombre_cliente varchar(20),
    nombre_empleado varchar(20),
    nombre_compania varchar(20),
    fecha_pedido date
);



create or replace function asignar()
returns trigger as $$
begin
    if(select fecha_envio from pedido where codigo_pedido = new.codigo_pedido) = '' then

    insert into pedidos pendientes(
        codigo_pedido,
        nombre_cliente,
        nombre_empleado,
        nombre_compania,
        fecha_pedido
        );
    values(new.codigo_pedido,
    new.nombre_cliente,
    new.nombre_empeado,
    new.nombre_compania,
    new.fecha_pedido);