Zn0vd5ND

1CREATE DATABASE  IF NOT EXISTS `db_ecommerce` /*!40100 DEFAULT CHARACTER SET utf8 */;
2USE `db_ecommerce`;
3
4SET FOREIGN_KEY_CHECKS = 0;
5
6
7DROP TABLE IF EXISTS `tb_categories`;
8
9CREATE TABLE `tb_categories` (
10  `idcategory` int(11) NOT NULL AUTO_INCREMENT,
11  `descategory` varchar(32) NOT NULL,
12  `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
13  PRIMARY KEY (`idcategory`)
14) ENGINE=InnoDB DEFAULT CHARSET=utf8;
15
16DROP TABLE IF EXISTS `tb_ordersstatus`;
17
18CREATE TABLE `tb_ordersstatus` (
19  `idstatus` int(11) NOT NULL AUTO_INCREMENT,
20  `desstatus` varchar(32) NOT NULL,
21  `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
22  PRIMARY KEY (`idstatus`)
23) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
24
25INSERT INTO `tb_ordersstatus` VALUES (1,'Em Aberto','2017-03-13 03:00:00'),(2,'Aguardando Pagamento','2017-03-13 03:00:00'),(3,'Pago','2017-03-13 03:00:00'),(4,'Entregue','2017-03-13 03:00:00');
26
27DROP TABLE IF EXISTS `tb_persons`;
28
29CREATE TABLE `tb_persons` (
30  `idperson` int(11) NOT NULL AUTO_INCREMENT,
31  `desperson` varchar(64) NOT NULL,
32  `desemail` varchar(128) DEFAULT NULL,
33  `nrphone` bigint(20) DEFAULT NULL,
34  `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
35  PRIMARY KEY (`idperson`)
36) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
37
38INSERT INTO `tb_persons` VALUES (1,'João Rangel','admin@hcode.com.br',2147483647,'2017-03-01 03:00:00'),(7,'Suporte','suporte@hcode.com.br',1112345678,'2017-03-15 16:10:27');
39
40DROP TABLE IF EXISTS `tb_products`;
41
42CREATE TABLE `tb_products` (
43  `idproduct` int(11) NOT NULL AUTO_INCREMENT,
44  `desproduct` varchar(64) NOT NULL,
45  `vlprice` decimal(10,2) NOT NULL,
46  `vlwidth` decimal(10,2) NOT NULL,
47  `vlheight` decimal(10,2) NOT NULL,
48  `vllength` decimal(10,2) NOT NULL,
49  `vlweight` decimal(10,2) NOT NULL,
50  `desurl` varchar(128) NOT NULL,
51  `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
52  PRIMARY KEY (`idproduct`)
53) ENGINE=InnoDB DEFAULT CHARSET=utf8;
54
55INSERT INTO `tb_products` VALUES (1,'Smartphone Android 7.0',999.95,75.00,151.00,80.00,167.00,'smartphone-android-7.0','2017-03-13 03:00:00'),(2,'SmartTV LED 4K',3925.99,917.00,596.00,288.00,8600.00,'smarttv-led-4k','2017-03-13 03:00:00'),(3,'Notebook 14\" 4GB 1TB',1949.99,345.00,23.00,30.00,2000.00,'notebook-14-4gb-1tb','2017-03-13 03:00:00');
56
57
58DROP TABLE IF EXISTS `tb_addresses`;
59
60CREATE TABLE `tb_addresses` (
61  `idaddress` int(11) NOT NULL AUTO_INCREMENT,
62  `idperson` int(11) NOT NULL,
63  `desaddress` varchar(128) NOT NULL,
64  `descomplement` varchar(32) DEFAULT NULL,
65  `descity` varchar(32) NOT NULL,
66  `desstate` varchar(32) NOT NULL,
67  `descountry` varchar(32) NOT NULL,
68  `nrzipcode` int(11) NOT NULL,
69  `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
70  PRIMARY KEY (`idaddress`),
71  KEY `fk_addresses_persons_idx` (`idperson`),
72  CONSTRAINT `fk_addresses_persons` FOREIGN KEY (`idperson`) REFERENCES `tb_persons` (`idperson`) ON DELETE NO ACTION ON UPDATE NO ACTION
73) ENGINE=InnoDB DEFAULT CHARSET=utf8;
74
75DROP TABLE IF EXISTS `tb_carts`;
76
77CREATE TABLE `tb_carts` (
78  `idcart` int(11) NOT NULL,
79  `dessessionid` varchar(64) NOT NULL,
80  `iduser` int(11) DEFAULT NULL,
81  `idaddress` int(11) DEFAULT NULL,
82  `vlfreight` decimal(10,2) DEFAULT NULL,
83  `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
84  PRIMARY KEY (`idcart`),
85  KEY `FK_carts_users_idx` (`iduser`),
86  KEY `fk_carts_addresses_idx` (`idaddress`),
87  CONSTRAINT `fk_carts_addresses` FOREIGN KEY (`idaddress`) REFERENCES `tb_addresses` (`idaddress`) ON DELETE NO ACTION ON UPDATE NO ACTION,
88  CONSTRAINT `fk_carts_users` FOREIGN KEY (`iduser`) REFERENCES `tb_users` (`iduser`) ON DELETE NO ACTION ON UPDATE NO ACTION
89) ENGINE=InnoDB DEFAULT CHARSET=utf8;
90
91DROP TABLE IF EXISTS `tb_cartsproducts`;
92/*!40101 SET @saved_cs_client     = @@character_set_client */;
93/*!40101 SET character_set_client = utf8 */;
94CREATE TABLE `tb_cartsproducts` (
95  `idcartproduct` int(11) NOT NULL AUTO_INCREMENT,
96  `idcart` int(11) NOT NULL,
97  `idproduct` int(11) NOT NULL,
98  `dtremoved` datetime NOT NULL,
99  `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
100  PRIMARY KEY (`idcartproduct`),
101  KEY `FK_cartsproducts_carts_idx` (`idcart`),
102  KEY `FK_cartsproducts_products_idx` (`idproduct`),
103  CONSTRAINT `fk_cartsproducts_carts` FOREIGN KEY (`idcart`) REFERENCES `tb_carts` (`idcart`) ON DELETE NO ACTION ON UPDATE NO ACTION,
104  CONSTRAINT `fk_cartsproducts_products` FOREIGN KEY (`idproduct`) REFERENCES `tb_products` (`idproduct`) ON DELETE NO ACTION ON UPDATE NO ACTION
105) ENGINE=InnoDB DEFAULT CHARSET=utf8;
106
107DROP TABLE IF EXISTS `tb_orders`;
108
109CREATE TABLE `tb_orders` (
110  `idorder` int(11) NOT NULL AUTO_INCREMENT,
111  `idcart` int(11) NOT NULL,
112  `iduser` int(11) NOT NULL,
113  `idstatus` int(11) NOT NULL,
114  `vltotal` decimal(10,2) NOT NULL,
115  `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
116  PRIMARY KEY (`idorder`),
117  KEY `FK_orders_carts_idx` (`idcart`),
118  KEY `FK_orders_users_idx` (`iduser`),
119  KEY `fk_orders_ordersstatus_idx` (`idstatus`),
120  CONSTRAINT `fk_orders_carts` FOREIGN KEY (`idcart`) REFERENCES `tb_carts` (`idcart`) ON DELETE NO ACTION ON UPDATE NO ACTION,
121  CONSTRAINT `fk_orders_ordersstatus` FOREIGN KEY (`idstatus`) REFERENCES `tb_ordersstatus` (`idstatus`) ON DELETE NO ACTION ON UPDATE NO ACTION,
122  CONSTRAINT `fk_orders_users` FOREIGN KEY (`iduser`) REFERENCES `tb_users` (`iduser`) ON DELETE NO ACTION ON UPDATE NO ACTION
123) ENGINE=InnoDB DEFAULT CHARSET=utf8;
124
125
126DROP TABLE IF EXISTS `tb_productscategories`;
127
128CREATE TABLE `tb_productscategories` (
129  `idcategory` int(11) NOT NULL,
130  `idproduct` int(11) NOT NULL,
131  PRIMARY KEY (`idcategory`,`idproduct`),
132  KEY `fk_productscategories_products_idx` (`idproduct`),
133  CONSTRAINT `fk_productscategories_categories` FOREIGN KEY (`idcategory`) REFERENCES `tb_categories` (`idcategory`) ON DELETE NO ACTION ON UPDATE NO ACTION,
134  CONSTRAINT `fk_productscategories_products` FOREIGN KEY (`idproduct`) REFERENCES `tb_products` (`idproduct`) ON DELETE NO ACTION ON UPDATE NO ACTION
135) ENGINE=InnoDB DEFAULT CHARSET=utf8;
136
137DROP TABLE IF EXISTS `tb_users`;
138
139CREATE TABLE `tb_users` (
140  `iduser` int(11) NOT NULL AUTO_INCREMENT,
141  `idperson` int(11) NOT NULL,
142  `deslogin` varchar(64) NOT NULL,
143  `despassword` varchar(256) NOT NULL,
144  `inadmin` tinyint(4) NOT NULL DEFAULT '0',
145  `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
146  PRIMARY KEY (`iduser`),
147  KEY `FK_users_persons_idx` (`idperson`),
148  CONSTRAINT `fk_users_persons` FOREIGN KEY (`idperson`) REFERENCES `tb_persons` (`idperson`) ON DELETE NO ACTION ON UPDATE NO ACTION
149) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
150
151INSERT INTO `tb_users` VALUES (1,1,'admin','$2y$12$YlooCyNvyTji8bPRcrfNfOKnVMmZA9ViM2A3IpFjmrpIbp5ovNmga',1,'2017-03-13 03:00:00'),(7,7,'suporte','$2y$12$HFjgUm/mk1RzTy4ZkJaZBe0Mc/BA2hQyoUckvm.lFa6TesjtNpiMe',1,'2017-03-15 16:10:27');
152
153DROP TABLE IF EXISTS `tb_userslogs`;
154
155CREATE TABLE `tb_userslogs` (
156  `idlog` int(11) NOT NULL AUTO_INCREMENT,
157  `iduser` int(11) NOT NULL,
158  `deslog` varchar(128) NOT NULL,
159  `desip` varchar(45) NOT NULL,
160  `desuseragent` varchar(128) NOT NULL,
161  `dessessionid` varchar(64) NOT NULL,
162  `desurl` varchar(128) NOT NULL,
163  `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
164  PRIMARY KEY (`idlog`),
165  KEY `fk_userslogs_users_idx` (`iduser`),
166  CONSTRAINT `fk_userslogs_users` FOREIGN KEY (`iduser`) REFERENCES `tb_users` (`iduser`) ON DELETE NO ACTION ON UPDATE NO ACTION
167) ENGINE=InnoDB DEFAULT CHARSET=utf8;
168
169DROP TABLE IF EXISTS `tb_userspasswordsrecoveries`;
170
171CREATE TABLE `tb_userspasswordsrecoveries` (
172  `idrecovery` int(11) NOT NULL AUTO_INCREMENT,
173  `iduser` int(11) NOT NULL,
174  `desip` varchar(45) NOT NULL,
175  `dtrecovery` datetime DEFAULT NULL,
176  `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
177  PRIMARY KEY (`idrecovery`),
178  KEY `fk_userspasswordsrecoveries_users_idx` (`iduser`),
179  CONSTRAINT `fk_userspasswordsrecoveries_users` FOREIGN KEY (`iduser`) REFERENCES `tb_users` (`iduser`) ON DELETE NO ACTION ON UPDATE NO ACTION
180) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
181
182INSERT INTO `tb_userspasswordsrecoveries` VALUES (1,7,'127.0.0.1',NULL,'2017-03-15 16:10:59'),(2,7,'127.0.0.1','2017-03-15 13:33:45','2017-03-15 16:11:18'),(3,7,'127.0.0.1','2017-03-15 13:37:35','2017-03-15 16:37:12');
183
184SET FOREIGN_KEY_CHECKS = 1;
185
186DELIMITER ;;
187CREATE PROCEDURE `sp_userspasswordsrecoveries_create`(
188piduser INT,
189pdesip VARCHAR(45)
190)
191BEGIN
192  
193  INSERT INTO tb_userspasswordsrecoveries (iduser, desip)
194    VALUES(piduser, pdesip);
195    
196    SELECT * FROM tb_userspasswordsrecoveries
197    WHERE idrecovery = LAST_INSERT_ID();
198    
199END ;;
200DELIMITER ;
201
202DELIMITER ;;
203CREATE PROCEDURE `sp_usersupdate_save`(
204piduser INT,
205pdesperson VARCHAR(64), 
206pdeslogin VARCHAR(64), 
207pdespassword VARCHAR(256), 
208pdesemail VARCHAR(128), 
209pnrphone BIGINT, 
210pinadmin TINYINT
211)
212BEGIN
213  
214    DECLARE vidperson INT;
215    
216  SELECT idperson INTO vidperson
217    FROM tb_users
218    WHERE iduser = piduser;
219    
220    UPDATE tb_persons
221    SET 
222    desperson = pdesperson,
223        desemail = pdesemail,
224        nrphone = pnrphone
225  WHERE idperson = vidperson;
226    
227    UPDATE tb_users
228    SET
229    deslogin = pdeslogin,
230        despassword = pdespassword,
231        inadmin = pinadmin
232  WHERE iduser = piduser;
233    
234    SELECT * FROM tb_users a INNER JOIN tb_persons b USING(idperson) WHERE a.iduser = piduser;
235    
236END ;;
237DELIMITER ;
238
239DELIMITER ;;
240CREATE PROCEDURE `sp_users_delete`(
241piduser INT
242)
243BEGIN
244  
245    DECLARE vidperson INT;
246    
247  SELECT idperson INTO vidperson
248    FROM tb_users
249    WHERE iduser = piduser;
250    
251    DELETE FROM tb_users WHERE iduser = piduser;
252    DELETE FROM tb_persons WHERE idperson = vidperson;
253    
254END ;;
255DELIMITER ;
256
257DELIMITER ;;
258CREATE PROCEDURE `sp_users_save`(
259pdesperson VARCHAR(64), 
260pdeslogin VARCHAR(64), 
261pdespassword VARCHAR(256), 
262pdesemail VARCHAR(128), 
263pnrphone BIGINT, 
264pinadmin TINYINT
265)
266BEGIN
267  
268    DECLARE vidperson INT;
269    
270  INSERT INTO tb_persons (desperson, desemail, nrphone)
271    VALUES(pdesperson, pdesemail, pnrphone);
272    
273    SET vidperson = LAST_INSERT_ID();
274    
275    INSERT INTO tb_users (idperson, deslogin, despassword, inadmin)
276    VALUES(vidperson, pdeslogin, pdespassword, pinadmin);
277    
278    SELECT * FROM tb_users a INNER JOIN tb_persons b USING(idperson) WHERE a.iduser = LAST_INSERT_ID();
279    
280END ;;
281DELIMITER ;