· 7 years ago · Nov 17, 2018, 04:14 PM
1SET @@FOREIGN_KEY_CHECKS = 0 ;
2DROP TABLE IF EXISTS `fornecedores` ;
3CREATE TABLE IF NOT EXISTS `fornecedores` (
4 `fid` INT( 11 ) NOT NULL PRIMARY KEY AUTO_INCREMENT ,
5 `nome` VARCHAR( 90 ) NOT NULL ,
6 `senha` BLOB NOT NULL ,
7 `cnpj` VARCHAR( 52 ) NOT NULL ,
8 `email` VARCHAR( 108 ) NOT NULL ,
9 `telefone` CHAR ( 11 ) NOT NULL ,
10 `imposto` DECIMAL( 10, 2 ) NOT NULL DEFAULT '0.00' ,
11 `status` ENUM( 'Travado', 'Liberado' ) NOT NULL DEFAULT 'Liberado' ,
12 `administrador` BOOLEAN NOT NULL DEFAULT 0,
13 UNIQUE KEY ( `cnpj` ) ,
14 UNIQUE KEY ( `email` ) ,
15 INDEX( `nome` )
16) Engine = InnoDB ;
17
18DROP TABLE IF EXISTS `categorias` ;
19CREATE TABLE IF NOT EXISTS `categorias` (
20 `cid` SMALLINT( 6 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
21 `nome` VARCHAR( 50 ) NOT NULL ,
22 `data` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
23 UNIQUE KEY ( `nome` )
24) Engine = InnoDB ;
25INSERT INTO `categorias` ( `nome`, `data` ) VALUES ( 'Sem Categoria' , NOW() ) ;
26
27DROP TABLE IF EXISTS `produtos` ;
28CREATE TABLE IF NOT EXISTS `produtos` (
29 `pid` INT( 11 ) NOT NULL PRIMARY KEY AUTO_INCREMENT ,
30 `nome` VARCHAR( 98 ) NOT NULL ,
31 `quantidade` SMALLINT( 6 ) NOT NULL DEFAULT 0,
32 `categoria` SMALLINT( 6 ) NOT NULL ,
33 `tipo` VARCHAR( 18 ) NOT NULL ,
34 `marca` VARCHAR( 56 ) NOT NULL ,
35 `cotando` BOOLEAN NOT NULL DEFAULT 0,
36 `isento` BOOLEAN NOT NULL DEFAULT 0,
37 UNIQUE KEY( `nome` ) ,
38 INDEX( `categoria` ) ,
39 FOREIGN KEY( `categoria` ) REFERENCES `categorias`( `cid` ) ON DELETE CASCADE ON UPDATE RESTRICT
40) Engine = InnoDB ;
41
42DROP TRIGGER IF EXISTS `onInsertProvider` ;
43DELIMITER //
44CREATE TRIGGER `onInsertProvider` AFTER INSERT ON `fornecedores`
45FOR EACH ROW BEGIN
46 INSERT INTO `cotacao` ( `pid`, `fid`, `preco` )
47 SELECT `pid`, NEW.`fid`, 0.00 FROM `produtos` WHERE `cotando` = 1 ;
48END//
49
50
51DROP TRIGGER IF EXISTS `onUpdatePrdt` ;
52DELIMITER //
53CREATE TRIGGER `onUpdatePrdt` AFTER UPDATE ON `produtos`
54FOR EACH ROW BEGIN
55 DECLARE `localState` BOOLEAN ;
56 DECLARE `newState` BOOLEAN ;
57 DECLARE `localIsentoState` BOOLEAN ;
58 DECLARE `newIsentoState` BOOLEAN ;
59 DECLARE `hasProductsInQuotation` BOOLEAN ;
60
61 SET `localState` = ( OLD.`cotando` ) ;
62 SET `newState` = ( NEW.`cotando` ) ;
63
64 SET `newIsentoState` = ( NEW.`isento` ) ;
65 SET `localIsentoState` = ( OLD.`isento` ) ;
66 -- SE e SOMENTE SE o produto que está sendo atualizado NÃO estiver cotando
67 -- E o seu estado de 'cotação' estiver sendo alterado para TRUE (1)
68 -- Irá selecionar todos os fornecedores colocando esse produto na cotação do fornecedor.
69
70 IF `localState` = 0 THEN
71 IF `newState` = 1 THEN
72 DELETE FROM `cotacao` WHERE `pid` = ( OLD.`pid` ) ;
73 INSERT INTO `cotacao` ( `pid`, `fid`, `preco` )
74 SELECT `pid`, `fornecedores`.`fid`, 0.00
75 FROM `fornecedores` JOIN `produtos`
76 WHERE `pid` = ( OLD.`pid` ) AND `fornecedores`.`administrador` = 0
77 ORDER BY `pid` ASC ;
78 END IF ;
79 END IF ;
80
81 -- SE o status local do produto for TRUE (1) [Estiver Cotando ...]
82 -- E o novo status for mudado para FALSE (0) [Zero], irá deletar
83 -- Todos os registros da tabela 'cotacao' em que o ID do produto
84 -- é igual ao produto que está sendo modificado
85 IF `localState` = 1 THEN
86 IF `newState` = 0 THEN
87 DELETE FROM `cotacao` WHERE `pid` = ( OLD.`pid` ) ;
88 END IF ;
89 END IF ;
90
91 SELECT IF( COUNT(*) > 0, TRUE, FALSE ) INTO `hasProductsInQuotation` FROM `impostocategoria`
92 INNER JOIN `produtos` ON `impostocategoria`.`cid` = `produtos`.`categoria`
93 WHERE `produtos`.`pid` = OLD.`pid` AND `cotando` = 1 ;
94 IF `hasProductsInQuotation` = TRUE THEN
95 IF `localIsentoState` = 0 AND `newIsentoState` = 1 THEN
96 -- Caso estivermos alterando para um estado "isento"(1), a taixa cobrada
97 -- pela categoria deve ser removida, sem necessidade de alteração manual
98 -- portanto, achamos o produto e recalculamos o imposto apenas com base na taixa do fornecedor
99 UPDATE `cotacao` INNER JOIN `fornecedores` ON `cotacao`.`fid` = `fornecedores`.`fid`
100 SET `cotacao`.`precoComImposto` = ( `cotacao`.`preco` + ( ( `fornecedores`.`imposto` / 100.0 ) ) * `cotacao`.`preco` ) ,
101 `cotacao`.`preco` = `cotacao`.`preco`
102 WHERE `cotacao`.`pid` = OLD.`pid` ;
103 ELSEIF `localIsentoState` = 1 AND `newIsentoState` = 0 THEN
104 -- Trigger cuida do resto, apenas redefina o preço para o corrente, para que uma atualização
105 -- seja comitada, logo então o trigger irá achar este produto como NÃO isento, e então
106 -- irá recalcular o preço devidamente correto para este produto.
107 UPDATE `cotacao` SET `cotacao`.`preco` = `cotacao`.`preco` WHERE `cotacao`.`pid` = OLD.`pid` ;
108 END IF ;
109 END IF ;
110END//
111
112DROP TABLE IF EXISTS `cotacao` ;
113CREATE TABLE IF NOT EXISTS `cotacao` (
114 `id` BIGINT( 22 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
115 `pid` INT( 11 ) NOT NULL ,
116 `fid` INT( 11 ) NOT NULL ,
117 `preco` DECIMAL( 10, 2 ) NOT NULL DEFAULT '0.00' ,
118 `precoComImposto` DECIMAL( 10, 2 ) NOT NULL DEFAULT '0.00' ,
119 INDEX ( `pid` ) ,
120 INDEX ( `fid` ) ,
121 FOREIGN KEY ( `pid` ) REFERENCES `produtos` ( `pid` ) ON DELETE CASCADE ON UPDATE RESTRICT ,
122 FOREIGN KEY ( `fid` ) REFERENCES `fornecedores` ( `fid` ) ON DELETE CASCADE ON UPDATE RESTRICT
123) Engine = InnoDB ;
124
125DELIMITER //
126CREATE TRIGGER `onUpdateProvider` BEFORE UPDATE ON `fornecedores`
127FOR EACH ROW BEGIN
128 DECLARE `countCotacao` INT( 11 ) ;
129 DECLARE `impostoCorrente` DECIMAL( 10, 2 ) ;
130 DECLARE `novoImposto` DECIMAL( 10, 2 ) ;
131 SET `novoImposto` = NEW.`imposto` ;
132 SELECT `imposto` INTO `impostoCorrente` FROM `fornecedores` WHERE `fid` = OLD.`fid` ;
133 IF `impostoCorrente` <> `novoImposto` THEN
134 SELECT COUNT(*) INTO `countCotacao` FROM `cotacao` WHERE `fid` = OLD.`fid` ;
135 IF `countCotacao` > 0 THEN
136 UPDATE `cotacao`
137 SET `precoComImposto` = ( `preco` + ( ( `novoImposto` / 100.0 ) * `preco` ) )
138 WHERE `fid` = OLD.`fid` ;
139 END IF ;
140 END IF ;
141END//
142DELIMITER //
143DELIMITER ;
144
145DROP TABLE IF EXISTS `impostoCategoria` ;
146CREATE TABLE IF NOT EXISTS `impostoCategoria` (
147 `icid` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
148 `cid` SMALLINT( 6 ) NOT NULL ,
149 `fid` INT( 11 ) NOT NULL ,
150 `imposto` DECIMAL( 10, 2 ) NOT NULL DEFAULT '0.00' ,
151 INDEX( `cid` ),
152 INDEX( `fid` ),
153 FOREIGN KEY ( `cid` ) REFERENCES `categorias`( `cid` ) ON DELETE CASCADE ON UPDATE RESTRICT ,
154 FOREIGN KEY ( `fid` ) REFERENCES `fornecedores`( `fid` ) ON DELETE CASCADE ON UPDATE RESTRICT
155) Engine = InnoDB ;
156
157DROP FUNCTION IF EXISTS `IsIseno` ;
158DELIMITER //
159-- Checa se um produto é isento (imposto).
160-- caso não for, retorna FALSE, caso for retorna TRUE
161CREATE FUNCTION `IsIsento`(`productID` INT) RETURNS BOOLEAN LANGUAGE SQL NOT DETERMINISTIC
162BEGIN
163 DECLARE `hasProduct` BOOLEAN ;
164 DECLARE `isIsento` BOOLEAN DEFAULT FALSE ;
165 SELECT IF( COUNT(*) > 0 , TRUE , FALSE ) INTO `hasProduct` FROM `produtos` WHERE `pid` = `productID` ;
166 IF `productID` <> 0 AND `hasProduct` THEN
167 SELECT IF( COUNT(*) > 0 , TRUE , FALSE ) INTO `isIsento`
168 FROM `produtos` WHERE `pid` = `productID` AND `isento` = 1 ;
169 END IF ;
170 RETURN isIsento ;
171END //
172DELIMITER ;
173
174DROP TRIGGER `onUpdateCategoryTax` ;
175DELIMITER //
176CREATE TRIGGER `onUpdateCategoryTax` AFTER UPDATE ON `impostoCategoria`
177FOR EACH ROW BEGIN
178 -- Se alterar um imposto de uma categoria, e esse fornecedor
179 -- tiver produtos em cotação, recalcula os preços com o novo imposto
180 DECLARE `countCotacao` INT( 11 );
181 DECLARE `impostoAtual` DECIMAL( 10, 2 );
182 SELECT COUNT(*) INTO `countCotacao` FROM `cotacao` INNER JOIN `produtos` ON `produtos`.`pid` = `cotacao`.`pid`
183 WHERE `fid` = OLD.`fid` AND `produtos`.`categoria` = NEW.`cid` ;
184 SELECT `imposto` INTO `impostoAtual` FROM `impostoCategoria` WHERE `cid` = NEW.`cid` AND `fid` = OLD.`fid` ;
185 IF `impostoAtual` <> NEW.`imposto` THEN
186 IF `countCotacao` > 0 THEN
187 UPDATE `cotacao` AS `c` INNER JOIN `produtos` ON `produtos`.`pid` = `c`.`pid`
188 INNER JOIN `fornecedores` ON `c`.`fid` = `fornecedores`.`fid`
189 SET `c`.`precoComImposto` = ( ( NEW.`imposto` / 100.0 ) * `c`.`preco` )
190 + ( `c`.`preco` + ( ( `fornecedores`.`imposto` / 100.0 ) * `c`.`preco` ) )
191 WHERE `c`.`fid` = NEW.`fid` AND `produtos`.`categoria` = NEW.`cid`
192 AND NOT IsIsento( `produtos`.`pid` ) ;
193 END IF ;
194 END IF ;
195END//
196
197DELIMITER ;
198
199DROP TRIGGER `onInsertCategoryTax` ;
200
201DELIMITER //
202CREATE TRIGGER `onInsertCategoryTax` AFTER INSERT ON `impostoCategoria`
203FOR EACH ROW BEGIN
204 -- Ao inserir um imposto pra uma categoria, e este fornecedor
205 -- tiver produtos desta categoria em cotação, recalcula os preços
206 -- que foram gravados na tabela aplicando o novo imposto da categoria
207 DECLARE `countCotacao` INT( 11 );
208 SELECT COUNT(*) INTO `countCotacao` FROM `cotacao` INNER JOIN `produtos` ON `produtos`.`pid` = `cotacao`.`pid`
209 WHERE `fid` = NEW.`fid` AND `produtos`.`categoria` = NEW.`cid` ;
210 IF `countCotacao` > 0 THEN
211 UPDATE `cotacao` AS `c` INNER JOIN `produtos` ON `produtos`.`pid` = `c`.`pid`
212 INNER JOIN `fornecedores` ON `c`.`fid` = `fornecedores`.`fid`
213 SET `c`.`precoComImposto` = ( ( NEW.`imposto` / 100.0 ) * `c`.`preco` )
214 + ( `c`.`preco` + ( ( `fornecedores`.`imposto` / 100.0 ) * `c`.`preco` ) )
215 WHERE `c`.`fid` = NEW.`fid` AND `produtos`.`categoria` = NEW.`cid` AND NOT IsIsento( `produtos`.`pid` ) ;
216 END IF ;
217END//
218DELIMITER ;
219
220DROP TRIGGER `onDeleteCategoryTax` ;
221
222DELIMITER //
223CREATE TRIGGER `onDeleteCategoryTax` AFTER DELETE ON `impostoCategoria`
224FOR EACH ROW BEGIN
225 -- Se deletar um imposto de uma categoria, e tiver
226 -- produtos deste fornecedor em cotação, os preços são recalculados
227 -- removendo o imposto que foi aplicado pra esta categoria
228 DECLARE `countCotacao` INT( 11 );
229 SELECT COUNT(*) INTO `countCotacao` FROM `cotacao` INNER JOIN `produtos` ON `produtos`.`pid` = `cotacao`.`pid`
230 WHERE `fid` = OLD.`fid` AND `produtos`.`categoria` = OLD.`cid` ;
231 IF `countCotacao` > 0 THEN
232 UPDATE `cotacao` AS `c` INNER JOIN `produtos` ON `produtos`.`pid` = `c`.`pid`
233 INNER JOIN `fornecedores` ON `c`.`fid` = `fornecedores`.`fid`
234 SET `c`.`precoComImposto` = ( `c`.`preco` + ( ( `fornecedores`.`imposto` / 100.0 ) * `c`.`preco` ) )
235 WHERE `c`.`fid` = OLD.`fid` AND `produtos`.`categoria` = OLD.`cid` AND NOT IsIsento( `produtos`.`pid` ) ;
236 END IF ;
237END//
238
239DELIMITER ;
240
241DELIMITER //
242CREATE TRIGGER `onUpdateQuotationPrice` BEFORE UPDATE ON `cotacao`
243FOR EACH ROW BEGIN
244 -- Quando fazer alteração no preço, faz novamente às contas
245 -- de impostos sob esse novo preço
246 DECLARE `impostoFornecedor` DECIMAL( 10 , 2 ) ;
247 DECLARE `impostoCategoria` DECIMAL( 10 , 2 ) ;
248 DECLARE `precoOriginal` DECIMAL( 10 , 2 ) ;
249
250 -- Se o produto não for "isento", adiciona o imposto calculado
251 -- para a categoria que ele está (se tiver)
252 IF NOT IsIsento( NEW.`pid` ) THEN
253 SELECT ((`fornecedores`.`imposto` / 100.0) * NEW.`preco`),
254 ((`ic`.`imposto` / 100.0) * NEW.`preco`), NEW.`preco`
255 INTO `impostoFornecedor`, `impostoCategoria`, `precoOriginal`
256 FROM `cotacao` INNER JOIN `produtos` ON `produtos`.`pid` = `cotacao`.`pid`
257 INNER JOIN `fornecedores` ON `cotacao`.`fid` = `fornecedores`.`fid`
258 INNER JOIN `impostocategoria` AS `ic` ON `produtos`.`categoria` = `ic`.`cid`
259 AND `ic`.`fid` = `cotacao`.`fid` WHERE `cotacao`.`pid` = NEW.`pid` AND `cotacao`.`fid` = NEW.`fid`
260 AND NOT IsIsento( NEW.`pid` ) ;
261 SET NEW.`precoComImposto` = ( `precoOriginal` + `impostoFornecedor` + `impostoCategoria` ) ;
262 ELSE
263 -- se o produto for "isento"
264 -- não adiciona o imposto da categoria
265 IF NEW.`preco` <> NULL THEN
266 SELECT ((`fornecedores`.`imposto` / 100.0) * NEW.`preco`),
267 NEW.`preco` INTO `impostoCategoria`, `precoOriginal`
268 FROM `cotacao` INNER JOIN `produtos` ON `produtos`.`pid` = `cotacao`.`pid`
269 INNER JOIN `fornecedores` ON `cotacao`.`fid` = `fornecedores`.`fid`
270 WHERE `cotacao`.`pid` = NEW.`pid` AND `cotacao`.`fid` = NEW.`fid` ;
271 SET NEW.`precoComImposto` = ( `precoOriginal` + `impostoFornecedor` ) ;
272 END IF ;
273 END IF ;
274END//
275DELIMITER ;