Trabalho final PBD

consultas.sql

# 1 - quantidade de funcionario por loja com salario acima de 4000 reais
select l.nome, count(*) 'nº func. salario maior que 4000' 
from funcionario f, loja l 
where (f.loja_id = l.loja_id ) and (f.salario > 4000 ) 
group by l.loja_id;


# 2 - Quantidade de pedidos para cada forma de pagamento

SELECT fp.tipo as 'forma de pagamento', count(*) as 'quantidade de pedidos' FROM funcionario f, pedido p, pagamento pa, forma_pagamento fp 
WHERE (f.funcionario_id = p.funcionario_id) and 
(p.pagamento_id = pa.pagamento_id) and
(pa.forma_pagamento_id = fp.forma_pagamento_id)
group by fp.forma_pagamento_id ;


# 3 - Contato de todos os clientes (Pode ser view)
select p.nome, ct.email, ct.ddd, ct.telefone from pessoa p, cliente c, contato ct where p.pessoa_id = c.cliente_id
and (ct.pessoa_id = p.pessoa_id);

#4 Clientes cadastrados que não fizeram pedido
SELECT p.nome, p.pessoa_id
FROM cliente c, pessoa p
where (c.cliente_id = p.pessoa_id)
and c.cliente_id not in(
	SELECT c.cliente_id
	FROM cliente c, pedido p
    WHERE (c.cliente_id = p.cliente_id) 
    GROUP BY cliente_id
);

# 5 - Pedidos por clientes

SELECT pe.nome, count(*) as 'pedidos realizados'
FROM cliente c, pedido p, pessoa pe
    WHERE (c.cliente_id = p.cliente_id) and
    (pe.pessoa_id = c.cliente_id)
    GROUP BY c.cliente_id;
    

#6 - Ver lista de itens para um pedido, dado um id de pedido
set @id := 1;
select pr.nome, pr.preco_venda, ip.quantidade, (pr.preco_venda * ip.quantidade) as subtotal
from item_pedido ip, pedido p, produto pr
where (p.pedido_id = ip.pedido_id) and
(pr.produto_id = ip.produto_id) and
(p.pedido_id = @id);


#7- valor total por pedido e respectiva quantidade de itens e itens distintos
select temp.pedido_id, sum(temp.subtotal) as total, sum(temp.qtd) as 'quantidade de itens', count(distinct temp.id_item) as 'itens distintos'
from (
	select ip.item_pedido_id as 'id_item', p.pedido_id, ip.quantidade as 'qtd', (pr.preco_venda * ip.quantidade) as 'subtotal' 
    from item_pedido ip, pedido p, produto pr
	where (p.pedido_id = ip.pedido_id) and
		(pr.produto_id = ip.produto_id)
	
) temp
group by temp.pedido_id;

#8 - Funcionario que não fizeram nenhuma venda(pedido)

SELECT p.nome, p.pessoa_id
FROM funcionario c, pessoa p
where (c.funcionario_id = p.pessoa_id)
and c.funcionario_id not in(
	SELECT c.funcionario_id
	FROM funcionario c, pedido p
    WHERE (c.funcionario_id = p.funcionario_id) 
    GROUP BY funcionario_id
);

#9 - Numero de pedidos nos ultimos X meses por loja
set @meses := 5;
select l.nome as 'loja', count(*) as 'nº de pedidos' from pedido p, funcionario f, loja l
where (p.funcionario_id = f.funcionario_id) and
(l.loja_id = f.loja_id) and
data_pedido > NOW() - INTERVAL @meses MONTH 
group by l.loja_id;


# 10 - Clientes que possuem endereço na mesma cidade que funcionarios da loja
select p.nome as 'cliente', temp.nome as 'funcionario', ci.nome as 'cidade'  
from cliente c, pessoa p, endereco e, cidade ci,
(	select p.nome, f.funcionario_id, e.cidade_id 
	from funcionario f, pessoa p, endereco e
	where (f.funcionario_id = p.pessoa_id) and
	(p.pessoa_id = e.pessoa_id)
) temp
where (c.cliente_id = p.pessoa_id) and
(e.pessoa_id = p.pessoa_id) and 
(e.cidade_id = ci.cidade_id) and
(e.cidade_id = temp.cidade_id)
;


# 11 Nome dos fornecedores que não possuem contato
select nome, cnpj 
from fornecedor, pessoa p 
where fornecedor_id not in (
	select f.fornecedor_id 
    from pessoa p, fornecedor f, contato ct 
    where p.pessoa_id = f.fornecedor_id
	and (ct.pessoa_id = p.pessoa_id)
) and (p.pessoa_id = fornecedor_id);

procedures.sql

# Stored Procedures


# 1- insere_pessoa é utilizada por outras procedures para 
#inserir clientes, funcionarios ou fornecedores

DELIMITER $$

create procedure insere_pessoa(
	IN nome varchar(80),
    OUT id int
)

BEGIN
	
	insert into pessoa(nome, data_de_cadastro) values (nome, curdate());
	set id = last_insert_id();

END $$

DELIMITER ;

DELIMITER $$


# 2- insere_fucionario insere um novo funcionario a partir de um registro de pessoa
#tratando possíveis problemas ao inserir
create procedure insere_funcionario(
	IN p_nome varchar(80),
    IN f_login varchar(20),
    IN f_senha varchar(20),
    IN f_loja varchar(80),
    IN f_salario numeric(12,1)
)

BEGIN
	DECLARE id_loja int;
    
    IF EXISTS (SELECT 1 FROM funcionario WHERE login like f_login) THEN
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = "Este login já existe. Tente outro";
	ELSEIF NOT EXISTS (SELECT 1 FROM loja WHERE nome like f_loja) THEN
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = "Esta loja não existe.";
	ELSE
		select loja_id into id_loja from loja where nome like f_loja;
		call insere_pessoa(p_nome , @id);
		insert into funcionario(funcionario_id, login, senha, loja_id, salario) values (@id, f_login, f_senha, id_loja, f_salario);
    END IF;
    
END $$

DELIMITER ;

#3 -  insere_fornecedor insere um novo fornecedor a partir de um registro de pessoa
# tratando o caso do cnpj já existir

DELIMITER $$

create procedure insere_fornecedor(
	IN p_nome varchar(80),
    IN f_cnpj varchar(20)
)

BEGIN
    
    IF EXISTS (SELECT 1 FROM fornecedor WHERE cnpj like f_cnpj) THEN
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = "Este CNPJ já está cadastrado";
	ELSE
		call insere_pessoa(p_nome , @id);
		insert into fornecedor(fornecedor_id, cnpj) values (@id, f_cnpj);
    END IF;
    
END $$

DELIMITER ;



#4 -  insere_cliente insere um novo cliente a partir de um registro de pessoa
# tratando o caso do cpf já existir


DELIMITER $$

create procedure insere_cliente(
	IN p_nome varchar(80),
    IN f_cpf varchar(20)
)

BEGIN
    
    IF EXISTS (SELECT 1 FROM cliente WHERE cpf like f_cpf) THEN
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = "Este CPF já está cadastrado";
	ELSE
		call insere_pessoa(p_nome , @id);
		insert into cliente(cliente_id, cpf) values (@id, f_cpf);
    END IF;
    
END $$

DELIMITER ;

schema.sql

create table compras (
	compra_id int not null auto_increment,
	loja_id int not null, 
	produto_id int not null, 
	fornecedor_id int not null, 
	quantidade int,
	data_da_compra date,
	constraint pk_compras primary key (compra_id),
	constraint fk_compras_produto_fornecedor foreign key (produto_id, fornecedor_id) 
	references produto_fornecedor (produto_id, fornecedor_id) on delete restrict on update cascade,
	constraint fk_compras_loja foreign key (loja_id)  
	references loja (loja_id) on delete restrict on update cascade
);

stored-incompletas.sql

	create temporary table tmp_itens (
			prod_id int,
			preco numeric (12,2),
			qtd int, 
			loja_id int
		);	

DELIMITER $$


create procedure insere_itens(
	IN item_nome varchar(80),
    IN item_qtd int,
    IN loja_nome varchar(50)
)

BEGIN

	declare id, em_estoque, lj_id int;
    declare valor numeric (12,1);
    
    select loja_id 
    into lj_id
    from loja l
    where l.nome like loja_nome;
    
    if lj_id is null then
		SIGNAL SQLSTATE '45000'
		SET MESSAGE_TEXT = "Esta loja não existe";
    else
    
    
		select p.produto_id, lp.quantidade, p.preco_venda
		into id, em_estoque, valor
		from produto p, loja_produto lp 
		where (p.produto_id = lp.produto_id)
		and (p.nome like item_nome)
		and (lp.loja_id = lj_id);
		
		if id is not null then
			if em_estoque >= item_qtd then
				insert into tmp_itens(prod_id, preco, qtd, loja_id) values (id, valor, item_qtd, lj_id);
			else
				SIGNAL SQLSTATE '45000'
				SET MESSAGE_TEXT = "Essa quantidade não está disponível";
			end if;
		else
			SIGNAL SQLSTATE '45000'
			SET MESSAGE_TEXT = "produto não existe, tente em outra loja";
		end if;
	end if;

END $$

DELIMITER ;

DELIMITER $$

create procedure cria_pedido(
	IN c_cpf varchar(11),
    IN fp_id int,
	IN f_id int
)

BEGIN
	DECLARE done int;
	DECLARE id_pedido int;
    DECLARE id_pag int;
    DECLARE id_cliente int;
    DECLARE tmp_prod_id int;
    DECLARE tmp_preco numeric(12,2);
    DECLARE tmp_qtd int;

	DECLARE cur_ingrediente CURSOR FOR
		SELECT prod_id, preco, qtd FROM tmp_itens;
	DECLARE CONTINUE handler for not found set done=1;
    
    select cliente_id 
    into id_cliente
    from cliente c
    where c.cpf = c_cpf;
    
    if id_cliente is not null then
		insert into pagamento(status, forma_pagamento_id)
        values(0, fp_id);
        set id_pag = last_insert_id();
		insert into pedido(cliente_id, funcionario_id, data_pedido, pagamento_id) 
        values (id_cliente, f_id, curdate(), id_pag);
        set id_pedido = last_insert_id();
	else
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = "CPF do cliente ou login do funcionario invalidos";
    end if;
    
    set done = 0;
	/* Inicia o cursor*/
    open cur_itens;
    get_item: LOOP
		fetch cur_itens into tmp_prod_id, tmp_preco, tmp_qtd;
        if done = 1 then
			leave get_item;
		end if;
        insert into item_pedido(produto_id, pedido_id, quantidade, preco_unitario)
        values (tmp_prod_id, id_pedido, tmp_qtd, tmp_preco);

	END LOOP get_item;
    close cur_itens;

    drop table tmp_itens;

    
END $$

DELIMITER ;




DELIMITER $$
CREATE TRIGGER tgr_controle_estoque AFTER INSERT ON item_pedido FOR EACH ROW
BEGIN
	DECLARE qtd_estoque, id_prod, id_loja INT;
    
		select loja_id into id_loja from tmp_itens 
        where prod_id = NEW.produto_id;

		SELECT produto_id, quantidade
        INTO id_prod, qtd_estoque
		FROM loja_produto lp
		WHERE (lp.produto_id = NEW.produto_id) and
        (lp.loja_id = id_loja);
		
		update loja_produto lp
        set lp.quantidade = qtd_estoque - NEW.quantidade
        where (lp.produto_id = NEW.produto_id) and
        (lp.loja_id = id_loja);


END $$
DELIMITER ;

alter table pagamento add column total numeric(12,2) default 0;

DELIMITER $$
CREATE TRIGGER tgr_total_pagamento AFTER INSERT ON item_pedido FOR EACH ROW
BEGIN
	DECLARE total_atual numeric(12,2);
    DECLARE id_pag int;
    
		# pega o total do pedido atual
		select pa.total, pa.pagamento_id into total_atual, id_pag 
        from pagamento pa, pedido pe 
        where (pe.pagamento_id = pa.pagamento_id) and
		(pe.pedido_id = NEW.pedido_id);
		
        update pagamento
        set total = total + (NEW.quantidade * NEW.preco_unitario)
        where pagamento_id = id_pag;

END $$
DELIMITER ;

##################

call nova_compra("Gooseberry",20,"09774562000100", "loja 1");
select * from produto;
select * from fornecedor;
drop procedure nova_compra;

DELIMITER $$

create procedure nova_compra(
	IN produto_nome varchar(80),
    IN produto_qtd int,
    IN fornecedor_cnpj varchar(20),
    IN loja_nome varchar(20)
)

BEGIN

	declare f_id, p_id, l_id int;
    
    if exists (select 1 from produto where nome like produto_nome) then
		select produto_id 
        into p_id from produto  
        where nome like produto_nome;
	else
		SIGNAL SQLSTATE '45000'
		SET MESSAGE_TEXT = "Não existe produto com este nome";
    end if;
    
    if exists (select 1 from fornecedor where cnpj like fornecedor_cnpj) then
		select fornecedor_id 
        into f_id from fornecedor  
        where cnpj like fornecedor_cnpj;
	else
		SIGNAL SQLSTATE '45000'
		SET MESSAGE_TEXT = "Não existe fornecedor com este CNPJ";
    end if;
    
    if exists (select 1 from loja where nome like loja_nome) then
		select loja_id
        into l_id from loja  
        where nome like loja_nome;
	else
		SIGNAL SQLSTATE '45000'
		SET MESSAGE_TEXT = "Não existe loja com este nome";
    end if;
    /* verifica se todos os ID's existem 
    depois verifica se o fornecedor fornece o produto escolhido
    caso exista, insere um novo registro na tabela 'compras'
    */
    if p_id is not null and (f_id is not null) and (l_id is not null) then
		if exists (select 1 from produto_fornecedor pf where pf.produto_id = p_id and (pf.fornecedor_id = f_id)) then
			insert into compras(loja_id, produto_id, fornecedor_id, quantidade, data_da_compra) 
            		values (l_id, p_id, f_id, produto_qtd, curdate());
		else
			SIGNAL SQLSTATE '45000'
			SET MESSAGE_TEXT = "Este fornecedor não fornece o produto escolhido";
		end if;
    end if;

END $$

DELIMITER ;

DELIMITER $$
CREATE TRIGGER tgr_entrada_estoque AFTER INSERT ON compras FOR EACH ROW
BEGIN
    
		update loja_produto
		set quantidade = quantidade + NEW.quantidade
		where (loja_id = NEW.loja_id) and (produto_id = NEW.produto_id);


END $$
DELIMITER ;