· 7 years ago · Dec 10, 2018, 05:20 PM
1DELIMITER ;
2SET FOREIGN_KEY_CHECKS=0;
3
4DROP TABLE IF EXISTS `fornecedores` ;
5CREATE TABLE IF NOT EXISTS `fornecedores` (
6 `fid` INT( 11 ) NOT NULL PRIMARY KEY AUTO_INCREMENT ,
7 `nome` VARCHAR( 90 ) NOT NULL ,
8 `senha` BLOB NOT NULL ,
9 `cnpj` VARCHAR( 52 ) NOT NULL ,
10 `email` VARCHAR( 108 ) NOT NULL ,
11 `telefone` VARCHAR ( 32 ) NOT NULL ,
12 `imposto` DECIMAL( 10, 2 ) NOT NULL DEFAULT '0.00' ,
13 `status` ENUM( 'Travado', 'Liberado' ) NOT NULL DEFAULT 'Liberado' ,
14 `administrador` BOOLEAN NOT NULL DEFAULT 0,
15 UNIQUE KEY ( `cnpj` ) ,
16 UNIQUE KEY ( `email` ) ,
17 INDEX( `nome` )
18) Engine = InnoDB ;
19
20DROP TABLE IF EXISTS `categorias` ;
21CREATE TABLE IF NOT EXISTS `categorias` (
22 `cid` SMALLINT( 6 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
23 `nome` VARCHAR( 50 ) NOT NULL ,
24 `data` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
25 UNIQUE KEY ( `nome` )
26) Engine = InnoDB ;
27INSERT INTO `categorias` ( `nome`, `data` ) VALUES ( 'Sem Categoria' , NOW() ) ;
28
29DROP TABLE IF EXISTS `produtos` ;
30CREATE TABLE IF NOT EXISTS `produtos` (
31 `pid` INT( 11 ) NOT NULL PRIMARY KEY AUTO_INCREMENT ,
32 `nome` VARCHAR( 98 ) NOT NULL ,
33 `quantidade` SMALLINT( 6 ) NOT NULL DEFAULT 0,
34 `categoria` SMALLINT( 6 ) NOT NULL ,
35 `tipo` VARCHAR( 18 ) NOT NULL ,
36 `marca` VARCHAR( 56 ) NOT NULL ,
37 `cotando` BOOLEAN NOT NULL DEFAULT 0,
38 `isento` BOOLEAN NOT NULL DEFAULT 0,
39 INDEX( `categoria` ) ,
40 FOREIGN KEY( `categoria` ) REFERENCES `categorias`( `cid` ) ON DELETE CASCADE ON UPDATE RESTRICT
41) Engine = InnoDB ;
42
43DROP TABLE IF EXISTS `cotacao` ;
44CREATE TABLE IF NOT EXISTS `cotacao` (
45 `id` BIGINT( 22 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
46 `pid` INT( 11 ) NOT NULL ,
47 `fid` INT( 11 ) NOT NULL ,
48 `preco` DECIMAL( 10, 2 ) NOT NULL DEFAULT '0.00' ,
49 `precoComImposto` DECIMAL( 10, 2 ) NOT NULL DEFAULT '0.00' ,
50 INDEX ( `pid` ) ,
51 INDEX ( `fid` ) ,
52 FOREIGN KEY ( `pid` ) REFERENCES `produtos` ( `pid` ) ON DELETE CASCADE ON UPDATE RESTRICT ,
53 FOREIGN KEY ( `fid` ) REFERENCES `fornecedores` ( `fid` ) ON DELETE CASCADE ON UPDATE RESTRICT
54) Engine = InnoDB ;
55
56DROP TABLE IF EXISTS `impostocategoria` ;
57CREATE TABLE IF NOT EXISTS `impostocategoria` (
58 `icid` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
59 `cid` SMALLINT( 6 ) NOT NULL ,
60 `fid` INT( 11 ) NOT NULL ,
61 `imposto` DECIMAL( 10, 2 ) NOT NULL DEFAULT '0.00' ,
62 INDEX( `cid` ),
63 INDEX( `fid` ),
64 FOREIGN KEY ( `cid` ) REFERENCES `categorias`( `cid` ) ON DELETE CASCADE ON UPDATE RESTRICT ,
65 FOREIGN KEY ( `fid` ) REFERENCES `fornecedores`( `fid` ) ON DELETE CASCADE ON UPDATE RESTRICT
66) Engine = InnoDB ;
67
68DROP TABLE IF EXISTS `notificacoes` ;
69CREATE TABLE IF NOT EXISTS `notificacoes` (
70 `nid` BIGINT( 22 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
71 `fid` INT( 11 ) NOT NULL ,
72 `mensagem` TEXT NOT NULL ,
73 `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
74 FOREIGN KEY ( `fid` ) REFERENCES `fornecedores`( `fid` ) ON DELETE CASCADE ON UPDATE RESTRICT
75) Engine = InnoDB ;
76
77DROP TABLE IF EXISTS `faixas` ;
78CREATE TABLE IF NOT EXISTS `faixas` (
79 `tid` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
80 `pid` INT( 11) NOT NULL ,
81 `faixa` DECIMAL( 10, 2 ) NOT NULL ,
82 INDEX ( `pid` ) ,
83 FOREIGN KEY ( `pid` ) REFERENCES `produtos`( `pid` ) ON DELETE CASCADE ON UPDATE RESTRICT
84) Engine = InnoDB ;
85
86DROP TABLE IF EXISTS `historico` ;
87CREATE TABLE IF NOT EXISTS `historico` (
88 `hid` MEDIUMINT( 8 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
89 `conteudo` BLOB NOT NULL ,
90 `data` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
91 INDEX ( `data` )
92) Engine = InnoDB ;
93
94DROP TRIGGER IF EXISTS `onInsertProvider` ;
95DELIMITER //
96CREATE TRIGGER `onInsertProvider` AFTER INSERT ON `fornecedores`
97FOR EACH ROW BEGIN
98 INSERT INTO `cotacao` ( `pid`, `fid`, `preco` )
99 SELECT `pid`, NEW.`fid`, 0.00 FROM `produtos` WHERE `cotando` = 1 ;
100END//
101
102DROP TRIGGER IF EXISTS `onBeforeUpdatePrdt` ;
103DELIMITER //
104CREATE TRIGGER `onBeforeUpdatePrdt` BEFORE UPDATE ON `produtos`
105FOR EACH ROW BEGIN
106 IF NEW.`cotando` = 0 AND OLD.`cotando` = 1 THEN
107 SET NEW.`quantidade` = 0 ;
108 END IF ;
109END//
110
111DROP TRIGGER IF EXISTS `onUpdatePrdt` ;
112DELIMITER //
113CREATE TRIGGER `onUpdatePrdt` AFTER UPDATE ON `produtos`
114FOR EACH ROW BEGIN
115 DECLARE `localState` BOOLEAN ;
116 DECLARE `newState` BOOLEAN ;
117 DECLARE `localIsentoState` BOOLEAN ;
118 DECLARE `newIsentoState` BOOLEAN ;
119 DECLARE `hasProductsInQuotation` BOOLEAN ;
120 DECLARE `categoriaAtual` SMALLINT( 6 ) ;
121 DECLARE `novaCategoria` SMALLINT( 6 ) ;
122
123
124 SET `localState` = ( OLD.`cotando` ) ;
125 SET `newState` = ( NEW.`cotando` ) ;
126 SET `newIsentoState` = ( NEW.`isento` ) ;
127 SET `localIsentoState` = ( OLD.`isento` ) ;
128 SET `categoriaAtual` = ( OLD.`categoria` ) ;
129 SET `novaCategoria` = ( NEW.`categoria` ) ;
130
131 IF `novaCategoria` <> `categoriaAtual` THEN
132 IF NOT IsIsento( OLD.`pid` ) THEN
133 UPDATE `cotacao` SET `cotacao`.`preco` = `cotacao`.`preco`
134 WHERE `cotacao`.`pid` = OLD.`pid` ;
135 END IF ;
136 END IF ;
137
138 IF `localState` = 0 THEN
139 IF `newState` = 1 THEN
140 DELETE FROM `cotacao` WHERE `pid` = ( OLD.`pid` ) ;
141 INSERT INTO `cotacao` ( `pid`, `fid`, `preco` )
142 SELECT `pid`, `fornecedores`.`fid`, 0.00
143 FROM `fornecedores` JOIN `produtos`
144 WHERE `pid` = ( OLD.`pid` ) AND `fornecedores`.`administrador` = 0
145 ORDER BY `pid` ASC ;
146 END IF ;
147 END IF ;
148
149 IF `localState` = 1 THEN
150 IF `newState` = 0 THEN
151 DELETE FROM `cotacao` WHERE `pid` = ( OLD.`pid` ) ;
152 END IF ;
153 END IF ;
154
155 SELECT IF( COUNT(*) > 0, TRUE, FALSE ) INTO `hasProductsInQuotation` FROM `impostocategoria`
156 INNER JOIN `produtos` ON `impostocategoria`.`cid` = `produtos`.`categoria`
157 WHERE `produtos`.`pid` = OLD.`pid` AND `cotando` = 1 ;
158 IF `hasProductsInQuotation` = TRUE THEN
159 IF `localIsentoState` = 0 AND `newIsentoState` = 1 THEN
160 UPDATE `cotacao` INNER JOIN `fornecedores` ON `cotacao`.`fid` = `fornecedores`.`fid`
161 SET `cotacao`.`precoComImposto` = ( COALESCE( `cotacao`.`preco`, 0 ) + ( ( `fornecedores`.`imposto` / 100.0 ) ) * `cotacao`.`preco` ) ,
162 `cotacao`.`preco` = `cotacao`.`preco`
163 WHERE `cotacao`.`pid` = OLD.`pid` ;
164 ELSEIF `localIsentoState` = 1 AND `newIsentoState` = 0 THEN
165 UPDATE `cotacao` SET `cotacao`.`preco` = `cotacao`.`preco` WHERE `cotacao`.`pid` = OLD.`pid` ;
166 END IF ;
167 END IF ;
168END//
169
170DROP TRIGGER IF EXISTS `onUpdateProvider` ;
171DELIMITER //
172CREATE TRIGGER `onUpdateProvider` AFTER UPDATE ON `fornecedores`
173FOR EACH ROW BEGIN
174 DECLARE `countCotacao` INT( 11 ) ;
175 DECLARE `impostoCorrente` DECIMAL( 10, 2 ) ;
176 DECLARE `novoImposto` DECIMAL( 10, 2 ) ;
177 SET `novoImposto` = NEW.`imposto` ;
178 SELECT `imposto` INTO `impostoCorrente` FROM `fornecedores` WHERE `fid` = OLD.`fid` ;
179 SELECT COUNT(*) INTO `countCotacao` FROM `cotacao` WHERE `fid` = OLD.`fid` ;
180 IF `countCotacao` > 0 THEN
181 UPDATE `cotacao`
182 SET `precoComImposto` = ( `preco` + ( ( `novoImposto` / 100.0 ) * `preco` ) )
183 WHERE `fid` = OLD.`fid` ;
184 END IF ;
185
186END//
187
188DROP FUNCTION IF EXISTS `IsIsento` ;
189DELIMITER //
190CREATE FUNCTION `IsIsento`(`productID` INT) RETURNS BOOLEAN LANGUAGE SQL NOT DETERMINISTIC
191BEGIN
192 DECLARE `hasProduct` BOOLEAN ;
193 DECLARE `isIsento` BOOLEAN DEFAULT FALSE ;
194 SELECT IF( COUNT(*) > 0 , TRUE , FALSE ) INTO `hasProduct` FROM `produtos` WHERE `pid` = `productID` ;
195 IF `productID` <> 0 AND `hasProduct` THEN
196 SELECT IF( COUNT(*) > 0 , TRUE , FALSE ) INTO `isIsento`
197 FROM `produtos` WHERE `pid` = `productID` AND `isento` = 1 ;
198 END IF ;
199 RETURN isIsento ;
200END//
201
202DROP TRIGGER IF EXISTS `onUpdateCategoryTax` ;
203DELIMITER //
204CREATE TRIGGER `onUpdateCategoryTax` AFTER UPDATE ON `impostocategoria`
205FOR EACH ROW BEGIN
206 DECLARE `countCotacao` INT( 11 );
207 DECLARE `impostoAtual` DECIMAL( 10, 2 );
208 SELECT COUNT(*) INTO `countCotacao` FROM `cotacao` INNER JOIN `produtos` ON `produtos`.`pid` = `cotacao`.`pid`
209 WHERE `fid` = OLD.`fid` AND `produtos`.`categoria` = NEW.`cid` ;
210 SELECT `imposto` INTO `impostoAtual` FROM `impostocategoria` WHERE `cid` = NEW.`cid` AND `fid` = OLD.`fid` ;
211 IF `countCotacao` > 0 THEN
212 UPDATE `cotacao` AS `c` INNER JOIN `produtos` ON `produtos`.`pid` = `c`.`pid`
213 INNER JOIN `fornecedores` ON `c`.`fid` = `fornecedores`.`fid`
214 SET `c`.`precoComImposto` = ( ( NEW.`imposto` / 100.0 ) * `c`.`preco` )
215 + ( `c`.`preco` + ( ( `fornecedores`.`imposto` / 100.0 ) * `c`.`preco` ) )
216 WHERE `c`.`fid` = NEW.`fid` AND `produtos`.`categoria` = NEW.`cid`
217 AND NOT IsIsento( `produtos`.`pid` ) ;
218 END IF ;
219END//
220
221DROP TRIGGER IF EXISTS `onInsertCategoryTax` ;
222DELIMITER //
223CREATE TRIGGER `onInsertCategoryTax` AFTER INSERT ON `impostocategoria`
224FOR EACH ROW BEGIN
225 DECLARE `countCotacao` INT( 11 );
226 SELECT COUNT(*) INTO `countCotacao` FROM `cotacao` INNER JOIN `produtos` ON `produtos`.`pid` = `cotacao`.`pid`
227 WHERE `fid` = NEW.`fid` AND `produtos`.`categoria` = NEW.`cid` ;
228 IF `countCotacao` > 0 THEN
229 UPDATE `cotacao` AS `c` INNER JOIN `produtos` ON `produtos`.`pid` = `c`.`pid`
230 INNER JOIN `fornecedores` ON `c`.`fid` = `fornecedores`.`fid`
231 SET `c`.`precoComImposto` = ( ( NEW.`imposto` / 100.0 ) * `c`.`preco` )
232 + ( `c`.`preco` + ( ( `fornecedores`.`imposto` / 100.0 ) * `c`.`preco` ) )
233 WHERE `c`.`fid` = NEW.`fid` AND `produtos`.`categoria` = NEW.`cid` AND NOT IsIsento( `produtos`.`pid` ) ;
234 END IF ;
235END//
236
237DROP TRIGGER IF EXISTS `onDeleteCategoryTax` ;
238DELIMITER //
239CREATE TRIGGER `onDeleteCategoryTax` AFTER DELETE ON `impostocategoria`
240FOR EACH ROW BEGIN
241 DECLARE `countCotacao` INT( 11 );
242 SELECT COUNT(*) INTO `countCotacao` FROM `cotacao` INNER JOIN `produtos` ON `produtos`.`pid` = `cotacao`.`pid`
243 WHERE `fid` = OLD.`fid` AND `produtos`.`categoria` = OLD.`cid` ;
244 IF `countCotacao` > 0 THEN
245 UPDATE `cotacao` AS `c` INNER JOIN `produtos` ON `produtos`.`pid` = `c`.`pid`
246 INNER JOIN `fornecedores` ON `c`.`fid` = `fornecedores`.`fid`
247 SET `c`.`precoComImposto` = ( `c`.`preco` + ( ( `fornecedores`.`imposto` / 100.0 ) * `c`.`preco` ) ) , `c`.`preco` = `c`.`preco`
248 WHERE `c`.`fid` = OLD.`fid` AND `produtos`.`categoria` = OLD.`cid` AND NOT IsIsento( `produtos`.`pid` ) ;
249 END IF ;
250END//
251
252DROP TRIGGER IF EXISTS `onUpdateQuotationPrice` ;
253DELIMITER //
254CREATE TRIGGER `onUpdateQuotationPrice` BEFORE UPDATE ON `cotacao`
255FOR EACH ROW BEGIN
256 DECLARE `impostoFornecedor` DECIMAL( 10 , 2 ) ;
257 DECLARE `impostoCategoria` DECIMAL( 10 , 2 ) ;
258 DECLARE `precoOriginal` DECIMAL( 10 , 2 ) ;
259 DECLARE `impostoFinal` DECIMAL( 10 , 2 ) ;
260
261 IF NOT IsIsento( NEW.`pid` ) THEN
262 SELECT
263 COALESCE(((`ic`.`imposto` / 100.0) * NEW.`preco`), 0),
264 IF(
265 COALESCE( ( ( `ic`.`imposto` / 100.0 ) * NEW.`preco` ) + NEW.`preco` , 0 ) > 0,
266 ( `fornecedores`.`imposto` / 100.0) * COALESCE( ( (`ic`.`imposto` / 100.0) * NEW.`preco`) + NEW.`preco`, 0),
267 ( `fornecedores`.`imposto` / 100.0) * COALESCE( NEW.`preco` )
268 ), NEW.`preco`
269 INTO
270 `impostoCategoria`,
271 `impostoFornecedor`,
272 `precoOriginal`
273 FROM `cotacao` INNER JOIN `produtos` ON `produtos`.`pid` = `cotacao`.`pid`
274 INNER JOIN `fornecedores` ON `cotacao`.`fid` = `fornecedores`.`fid`
275 LEFT JOIN `impostocategoria` AS `ic` ON `produtos`.`categoria` = `ic`.`cid`
276 AND `ic`.`fid` = `cotacao`.`fid` WHERE `cotacao`.`pid` = NEW.`pid` AND `cotacao`.`fid` = NEW.`fid`;
277 SET NEW.`precoComImposto` = ( COALESCE( `precoOriginal`, 0 )
278 + COALESCE( `impostoCategoria`, 0 )
279 + COALESCE( `impostoFornecedor`, 0 )
280 ) ;
281 ELSE
282 SELECT ((`fornecedores`.`imposto` / 100.0) * NEW.`preco`),
283 NEW.`preco` INTO `impostoFornecedor`, `precoOriginal`
284 FROM `cotacao` INNER JOIN `produtos` ON `produtos`.`pid` = `cotacao`.`pid`
285 INNER JOIN `fornecedores` ON `cotacao`.`fid` = `fornecedores`.`fid`
286 WHERE `cotacao`.`pid` = NEW.`pid` AND `cotacao`.`fid` = NEW.`fid` ;
287 SET NEW.`precoComImposto` = ( COALESCE( `precoOriginal`, 0 )
288 + COALESCE( `impostoFornecedor`, 0 )
289 ) ;
290 END IF ;
291END//