estava pensando aqui… sera que na hora de fazer uma consulta do produto final
nao tem como ele fazer os calculos de tudo antes de exibir os dados?
uma duvida tambem… nao tem como passar parametros/variavel para a Trigger?
Porque pesar o banco com i/o em disco a cada consulta?
Não há como passar parâmetros para um trigger, a não ser o valor das colunas do registro da tabela a processar.
Fique tranquilo e não pense nisso agora, por volta das 17h30 com trabalhar nesse tarefa.
Mas é possivel fazer os calculos na consulta?
Eu pensei que o SC tinha alguma opção nativa pra facilitar esse tipo de operação
Meus sinceros agradecimentos.
Como prometido, segue exemplo completo e funcional explicando a praticidade e segurança das triggers.
PARTE 1 : MODELAGEM
CREATE DATABASE IF NOT EXISTS treino /*!40100 DEFAULT CHARACTER SET utf8 */;
USE treino;
– MySQL dump 10.13 Distrib 5.6.13, for Win32 (x86)
– Host: 192.168.25.7 Database: treino
– Server version 5.1.61
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8 /;
/!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /;
/!40103 SET TIME_ZONE=’+00:00’ /;
/!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO’ /;
/!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
–
– Table structure for table mat_prima
DROP TABLE IF EXISTS mat_prima;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 */;
CREATE TABLE mat_prima (
id_materia_prima int(11) NOT NULL AUTO_INCREMENT,
desc_materia_prima varchar(100) DEFAULT NULL,
genero_materia_prima varchar(45) DEFAULT NULL,
preco_compra_mp decimal(10,3) DEFAULT NULL,
icms_mp decimal(10,2) DEFAULT NULL,
pis_mp decimal(10,2) DEFAULT NULL,
cofins_mp decimal(10,2) DEFAULT NULL,
ipi_mp decimal(10,2) DEFAULT NULL,
ii_mp decimal(10,2) DEFAULT NULL,
frete_mp decimal(10,2) DEFAULT NULL,
comissao_mp decimal(10,2) DEFAULT NULL,
preco_final_mp decimal(10,3) DEFAULT NULL,
PRIMARY KEY (id_materia_prima)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
/!50003 SET sql_mode = @saved_sql_mode / ;
/!50003 SET character_set_client = @saved_cs_client / ;
/!50003 SET character_set_results = @saved_cs_results / ;
/!50003 SET collation_connection = @saved_col_connection / ;
/!50003 SET @saved_cs_client = @@character_set_client / ;
/!50003 SET @saved_cs_results = @@character_set_results / ;
/!50003 SET @saved_col_connection = @@collation_connection / ;
/!50003 SET character_set_client = utf8 / ;
/!50003 SET character_set_results = utf8 / ;
/!50003 SET collation_connection = utf8_general_ci / ;
/!50003 SET @saved_sql_mode = @@sql_mode / ;
/!50003 SET sql_mode = ‘’ / ;
DELIMITER ;;
/!50003 CREATE/ /!50017 DEFINER=root@%/ /!50003 TRIGGER mat_prima_AUPD AFTER UPDATE ON mat_prima FOR EACH ROW
begin
UPDATE prod_matprima SET total_pf=per_pfNEW.preco_final_mp WHERE id_mat_prima=NEW.id_materia_prima;
END /;;
DELIMITER ;
/!50003 SET sql_mode = @saved_sql_mode / ;
/!50003 SET character_set_client = @saved_cs_client / ;
/!50003 SET character_set_results = @saved_cs_results / ;
/!50003 SET collation_connection = @saved_col_connection */ ;
–
– Table structure for table prod_acabado
DROP TABLE IF EXISTS prod_acabado;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE prod_acabado (
id_produto_final int(11) NOT NULL AUTO_INCREMENT,
desc_pf varchar(60) DEFAULT NULL,
genero_pf varchar(45) DEFAULT NULL,
custo_total_pf decimal(10,2) DEFAULT NULL,
PRIMARY KEY (id_produto_final)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client */;
–
– Table structure for table prod_matprima
DROP TABLE IF EXISTS prod_matprima;
/!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE prod_matprima (
id_prod_acabado int(11) NOT NULL,
id_mat_prima varchar(45) NOT NULL,
per_pf decimal(10,4) DEFAULT NULL,
total_pf decimal(10,2) DEFAULT NULL,
PRIMARY KEY (id_prod_acabado,id_mat_prima)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client /;
/!50003 SET @saved_cs_client = @@character_set_client / ;
/!50003 SET @saved_cs_results = @@character_set_results / ;
/!50003 SET @saved_col_connection = @@collation_connection / ;
/!50003 SET character_set_client = utf8 / ;
/!50003 SET character_set_results = utf8 / ;
/!50003 SET collation_connection = utf8_general_ci / ;
/!50003 SET @saved_sql_mode = @@sql_mode / ;
/!50003 SET sql_mode = ‘’ / ;
DELIMITER ;;
/!50003 CREATE/ /!50017 DEFINER=root@%/ /!50003 TRIGGER prod_matprima_AINS AFTER INSERT ON prod_matprima FOR EACH ROW
begin
UPDATE prod_acabado
SET custo_total_pf=
(SELECT SUM(total_pf)
FROM prod_matprima
WHERE id_prod_acabado=NEW.id_prod_acabado)
WHERE id_produto_final=NEW.id_prod_acabado;
end /;;
DELIMITER ;
/!50003 SET sql_mode = @saved_sql_mode / ;
/!50003 SET character_set_client = @saved_cs_client / ;
/!50003 SET character_set_results = @saved_cs_results / ;
/!50003 SET collation_connection = @saved_col_connection / ;
/!50003 SET @saved_cs_client = @@character_set_client / ;
/!50003 SET @saved_cs_results = @@character_set_results / ;
/!50003 SET @saved_col_connection = @@collation_connection / ;
/!50003 SET character_set_client = utf8 / ;
/!50003 SET character_set_results = utf8 / ;
/!50003 SET collation_connection = utf8_general_ci / ;
/!50003 SET @saved_sql_mode = @@sql_mode / ;
/!50003 SET sql_mode = ‘’ / ;
DELIMITER ;;
/!50003 CREATE/ /!50017 DEFINER=root@%/ /!50003 TRIGGER prod_matprima_AUPD AFTER UPDATE ON prod_matprima FOR EACH ROW
begin
UPDATE prod_acabado
SET custo_total_pf=
(SELECT SUM(total_pf)
FROM prod_matprima
WHERE id_prod_acabado=NEW.id_prod_acabado)
WHERE id_produto_final=NEW.id_prod_acabado;
end /;;
DELIMITER ;
/!50003 SET sql_mode = @saved_sql_mode / ;
/!50003 SET character_set_client = @saved_cs_client / ;
/!50003 SET character_set_results = @saved_cs_results / ;
/!50003 SET collation_connection = @saved_col_connection / ;
/!50003 SET @saved_cs_client = @@character_set_client / ;
/!50003 SET @saved_cs_results = @@character_set_results / ;
/!50003 SET @saved_col_connection = @@collation_connection / ;
/!50003 SET character_set_client = utf8 / ;
/!50003 SET character_set_results = utf8 / ;
/!50003 SET collation_connection = utf8_general_ci / ;
/!50003 SET @saved_sql_mode = @@sql_mode / ;
/!50003 SET sql_mode = ‘’ / ;
DELIMITER ;;
/!50003 CREATE/ /!50017 DEFINER=root@%/ /*!50003 TRIGGER prod_matprima_ADEL AFTER DELETE ON prod_matprima FOR EACH ROW
begin
UPDATE prod_acabado
SET custo_total_pf=
(SELECT SUM(total_pf)
FROM prod_matprima
WHERE id_prod_acabado=OLD.id_prod_acabado)
WHERE id_produto_final=OLD.id_prod_acabado;
end /;;
DELIMITER ;
/!50003 SET sql_mode = @saved_sql_mode / ;
/!50003 SET character_set_client = @saved_cs_client / ;
/!50003 SET character_set_results = @saved_cs_results / ;
/!50003 SET collation_connection = @saved_col_connection */ ;
–
– Dumping events for database ‘treino’
–
– Dumping routines for database ‘treino’
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE /;
/!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /;
/!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /;
/!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /;
/!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
– Dump completed on 2014-01-26 0:37:51
Para Download das aplicações (Importe como aplicações em um projeto já existente):
http://iw.servehttp.com:86/ftp/sc_201401260043_bkp_Treino.zip
- Nota: O dwnload do exemplo não estará disponível por muito tempo.
Acessando o exemplo:
http://iw.servehttp.com:86/sc/7.1/app/Treino/form_prod_acabado
Tela do exemplo:
Explicação:
Triggers:
Tabela prod_matprima (tabela de ligação das matérias primas ao produto).
Gatilho 1:
Após Inserir, alterar ou excluir é executado um update em produtos baseado em um select sum em todas as matérias primas relacionadas a esse produto.
Tabela materia_prima
Gatilho 2:
Após alterar é executado um update em prod_matprima de todos registros relacionados a essa matéria prima recalculando o custo_total. O update em prod_matprima aciona o gatilho 1 que por sua vez atualiza o custo final do produto. (Encadeamento de gatilhos).
Aplicação form_mat_prima
Evento Onvalidate:
- Verifica se for deleção e se a matéria prima está em uso, não permite excluir.
Aplicação form_produto_acabado (Mestre/Detalhe):
Possui um detalhe apontando para form_prod_matprima utilizando o id do produto como referência de filtro de registros para o detalhe.
Aplicação form_prod_matprima (Detalhe do cadastro de produtos):
Evento Ajax no campo quantidade:
- Busca o custo da matéria prima para sua unidade padrão e calcula pela quantidade o custo de utilização dessa matéria no produto corrente.
Método PHP AtualizaProduto:
-Busca o custo final do produto e apresenta na janela relacionada do formulário mestre.
Evento OnLoadRecord:
-Executa AtualizaProduto quando da inserção ou alteração da linha detalhe.
Evento OnAfterDelete:
- Executa AtualizaProduto quando da deleção da linha detalhe
O exemplo levou 1,5 horas para ser confeccionado, e demonstra como eh simples e prático usar triggers num processo de atualizações que são disparados automaticamente, ganhando performance no processo, pois as atualizações são disparadas pelo próprio banco de dados, não trafegando dados entre o php e o banco, ganha-se segurança e integridade das informações, facilita manutenção e pode-se aplicar outras plataformas (tipo apps mobile) que os procedimentos serão os mesmos ganhando reprogramação.
O Exemplo é uma demonstração e não sei se vai ser utilizado exatamente como está para atender ao nosso colega criador desse tópico, pois houve dúvidas quanto a estrutura de dados, cálculos a executar e na imagem da tela postada aqui, mas acredito que estudando o exemplo facilmente chegará ao resultado esperado quando aplicar o conceito em seu projeto.
***Nota: O custo do produto que usei como exemplo, foi calculado em cima das matérias primas cujos os preços e unidades de medição foram pesquisados no site do Pão de Açúcar, e representa o custo real aproximado para confecção de um BOLO DE CENOURA.
Links de Ajuda
http://www.tudogostoso.com.br/receita/23-bolo-de-cenoura.html
Haroldo, primeiramente agradeço a sua ajuda! Ficou muito bom seu exemplo.
Realmente é isso que estou precisando… já estou modificando algumas coisas a partir do seu modelo para incluir em meu projeto.
Só peço por gentileza aos moderadores para não fechar o tópico, eu vou fazer os testes e retorno o mais breve possível.
Novamente agradeço.
Não se preocupe, um tópico nunca eh fechado aqui. O que pedimos e que retorne com sua experiência quanto a solução aplicada e se seu problema foi resolvido.
Haroldo.
Fiz as adaptações para o meu projeto… e esta funcionando parcialmente.
Estou com problema na Trigger da matéria prima, ao ser acionada ela altera o valor final dos produtos acabados, deixando todos com os mesmos valores…
Eu segui a risca suas dicas, ontem e hoje, já tentei de tudo antes de recorrer a ti… mas não estou conseguindo… fazer isso funcionar…
o restante esta tudo ok…
Por favor, me ajude
Trigger para atualizar materias primas
UPDATE prod_mat
SET total_pf=per_pf*NEW.preco_final_mp
WHERE id_mat_prima=NEW.id_materia_prima
Gratidão!
Qual o nome da coluna id da tabela materia_prima?
id_materia_prima
Se fez como demonstrei no exemplo tem que funcionar igual.
Você deve ter feito algo diferente.
Eu já revisei o projeto diversas vezes esta do mesmo modo que voce ensinou…
O problema esta mesmo na Trigger da Materia Prima ele atualiza todos os produtos mas com o mesmo preço.
As demais Triggers funcionam normalmente.
Bom vou da um tempo… e vou tentar refazer tudo pra ver se acerto.
Agradeço!
Al alterar um registro de matéria prima, esta aciona a trigger after update que por si recalcula (faz update) em todos os registos cujo oid seja dessa materia prima em prod_mat,
Alterar materia prima não executa update em produto.
Reveja a trigger after update em prod_mat eh essa que executa a alteração nos produtos.
Haroldo.
Meus sinceros agradecimentos.
Agora esta funcionando ok… realmente o problema era na Trigger After Update ( tabela: mat_prod )
Surgiu uma duvida em relação a tela de cadastro de produto acabado, por favor preciso de mais uma ajuda…
Ao cadastrar um produto acabado, esse produto vai em uma determinada embalagem. (1 para muitos)
Eu fiz o cadastro de embalagem do modo que você me ensinou aqui, pois cada embalagem esta vinculada a N matérias primas…
esta funcionando perfeitamente.
Minha duvida é a seguinte.
Na tela de cadastro de produto acabado, eu fiz um mestre / detalhe para as embalagens…
Pergunta: É possível retornar as embalagens cadastradas com checkbox?
Ao selecionar uma embalagem, o sistema deve fazer o seguinte calculo automatico, semelhante a Trigger materia prima…
custo_total_produto_acabado * volume_da_embalagem + preco_embalagem_selecionada
Caso ajude segue imagem.
Antes: http://lucheti.com.br/cadastro_pf_01.jpg
(feito através de iframe , porem era necessario clicar em calcular)
Depois: http://lucheti.com.br/cadastro_pf_02.jpg
Devo um favor a ti!
Gratidão.
AI tem que mexer novamente na modelagem.
em produtos acabados você deve ter:
custo_total_materias_primas (esse será o calculados pelas triggers)
Se for apenas uma embalagem por produto:
criar id_embalagem na tabela de produtos.
Na app form de produtos, criar um checkbox para o campo id_embalagem e em lookup automatico jogar o select.
criar campo custo_final_produto.
em onbeforeinsert e update, fazer sc_lookup e pegar o vaor da embalagem e somar calcular custo_final_produto = custo_total_materias_primas + custo_embalagem.
criar trigger para embalagens, quando alterar produto fazer update em produto baseado no calculo anterior.
nas triigger prod_mat prever o calculo anterior.
você aprendendo o conceito das triggers conseguirá ir em frente.
Haroldo.
Eu entendi parcialmente a sua solução… só não ficou claro como vou salvar o valor de cada embalagem selecionada?
Na imagem abaixo, eu fiz um select e a cada linha o código criava um campo de texto dinamicamente…
http://lucheti.com.br/cadastro_pf_01.jpg
São N embalagens para 1 produto.
No caso eu devo criar um id_embalagem na tabela PRODUTOS e também o campo custo_embalagem (para salvar o valor) ?
Fiz uma pergunta no meu post anterior.
Não esta claro como você aponta uma embalagem para o produto.
Cada produto tem apenas uma única embalagem?
Cada embalagem tem apenas um único produto?
O Custo do produto esta diretamente relacionado a embalagem? Acredito que sim, somente quando um produto tem embalagem única e própria, pois geralmente o custo da embalagem é apreciado no faturamento deste produto.
Na sua imagem, tem um campo input na linha de cada tipo de embalagem o que vai nele?
Cada produto tem apenas uma única embalagem?
R: Não. O produto pode ter inúmeras embalagens
Cada embalagem tem apenas um único produto?
R: Cada embalagem tem inúmeras matérias primas que a compõe. (por exemplo Frasco 1L, vai rotulo, etiqueta, caixa de papelão… o próprio frasco…) cada embalagem pode ir para N produtos.
O Custo do produto esta diretamente relacionado a embalagem?
R: O valor da embalagem é calculado atraves da quantidade de materias primas que a compõe. O mesmo ocorre com o produto.
O custo do produto, acredito, não esta relacionado a embalagem, porém ao selecionar uma embalagem, é formado outro custo mas não para o produto acabado e nem para a embalagem cadastrada, neste caso é um outro valor que deve ser armazenado.
Na sua imagem, tem um campo input na linha de cada tipo de embalagem o que vai nele?
R: O input recebe o valor de cada embalagem selecionada (a resposta acima)
Agradeço!
Eh acho que não estou conseguindo racionar bem hoje.