· 8 years ago · Apr 09, 2018, 06:46 PM
1CREATE DATABASE IF NOT EXISTS `db_ecommerce` /*!40100 DEFAULT CHARACTER SET utf8 */;
2USE `db_ecommerce`;
3-- MySQL dump 10.13 Distrib 5.7.17, for Win64 (x86_64)
4--
5-- Host: 127.0.0.1 Database: db_ecommerce
6-- ------------------------------------------------------
7-- Server version 5.5.5-10.1.19-MariaDB
8
9/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
10/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
11/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
12/*!40101 SET NAMES utf8 */;
13/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
14/*!40103 SET TIME_ZONE='+00:00' */;
15/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
16/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
17/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
18/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
19
20--
21-- Table structure for table `tb_addresses`
22--
23
24DROP TABLE IF EXISTS `tb_addresses`;
25/*!40101 SET @saved_cs_client = @@character_set_client */;
26/*!40101 SET character_set_client = utf8 */;
27CREATE TABLE `tb_addresses` (
28 `idaddress` int(11) NOT NULL AUTO_INCREMENT,
29 `idperson` int(11) NOT NULL,
30 `desaddress` varchar(128) NOT NULL,
31 `descomplement` varchar(32) DEFAULT NULL,
32 `descity` varchar(32) NOT NULL,
33 `desstate` varchar(32) NOT NULL,
34 `descountry` varchar(32) NOT NULL,
35 `nrzipcode` int(11) NOT NULL,
36 `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
37 PRIMARY KEY (`idaddress`),
38 KEY `fk_addresses_persons_idx` (`idperson`),
39 CONSTRAINT `fk_addresses_persons` FOREIGN KEY (`idperson`) REFERENCES `tb_persons` (`idperson`) ON DELETE NO ACTION ON UPDATE NO ACTION
40) ENGINE=InnoDB DEFAULT CHARSET=utf8;
41/*!40101 SET character_set_client = @saved_cs_client */;
42
43--
44-- Dumping data for table `tb_addresses`
45--
46
47LOCK TABLES `tb_addresses` WRITE;
48/*!40000 ALTER TABLE `tb_addresses` DISABLE KEYS */;
49/*!40000 ALTER TABLE `tb_addresses` ENABLE KEYS */;
50UNLOCK TABLES;
51
52--
53-- Table structure for table `tb_carts`
54--
55
56DROP TABLE IF EXISTS `tb_carts`;
57/*!40101 SET @saved_cs_client = @@character_set_client */;
58/*!40101 SET character_set_client = utf8 */;
59CREATE TABLE `tb_carts` (
60 `idcart` int(11) NOT NULL,
61 `dessessionid` varchar(64) NOT NULL,
62 `iduser` int(11) DEFAULT NULL,
63 `idaddress` int(11) DEFAULT NULL,
64 `vlfreight` decimal(10,2) DEFAULT NULL,
65 `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
66 PRIMARY KEY (`idcart`),
67 KEY `FK_carts_users_idx` (`iduser`),
68 KEY `fk_carts_addresses_idx` (`idaddress`),
69 CONSTRAINT `fk_carts_addresses` FOREIGN KEY (`idaddress`) REFERENCES `tb_addresses` (`idaddress`) ON DELETE NO ACTION ON UPDATE NO ACTION,
70 CONSTRAINT `fk_carts_users` FOREIGN KEY (`iduser`) REFERENCES `tb_users` (`iduser`) ON DELETE NO ACTION ON UPDATE NO ACTION
71) ENGINE=InnoDB DEFAULT CHARSET=utf8;
72/*!40101 SET character_set_client = @saved_cs_client */;
73
74--
75-- Dumping data for table `tb_carts`
76--
77
78LOCK TABLES `tb_carts` WRITE;
79/*!40000 ALTER TABLE `tb_carts` DISABLE KEYS */;
80/*!40000 ALTER TABLE `tb_carts` ENABLE KEYS */;
81UNLOCK TABLES;
82
83--
84-- Table structure for table `tb_cartsproducts`
85--
86
87DROP TABLE IF EXISTS `tb_cartsproducts`;
88/*!40101 SET @saved_cs_client = @@character_set_client */;
89/*!40101 SET character_set_client = utf8 */;
90CREATE TABLE `tb_cartsproducts` (
91 `idcartproduct` int(11) NOT NULL AUTO_INCREMENT,
92 `idcart` int(11) NOT NULL,
93 `idproduct` int(11) NOT NULL,
94 `dtremoved` datetime NOT NULL,
95 `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
96 PRIMARY KEY (`idcartproduct`),
97 KEY `FK_cartsproducts_carts_idx` (`idcart`),
98 KEY `FK_cartsproducts_products_idx` (`idproduct`),
99 CONSTRAINT `fk_cartsproducts_carts` FOREIGN KEY (`idcart`) REFERENCES `tb_carts` (`idcart`) ON DELETE NO ACTION ON UPDATE NO ACTION,
100 CONSTRAINT `fk_cartsproducts_products` FOREIGN KEY (`idproduct`) REFERENCES `tb_products` (`idproduct`) ON DELETE NO ACTION ON UPDATE NO ACTION
101) ENGINE=InnoDB DEFAULT CHARSET=utf8;
102/*!40101 SET character_set_client = @saved_cs_client */;
103
104--
105-- Dumping data for table `tb_cartsproducts`
106--
107
108LOCK TABLES `tb_cartsproducts` WRITE;
109/*!40000 ALTER TABLE `tb_cartsproducts` DISABLE KEYS */;
110/*!40000 ALTER TABLE `tb_cartsproducts` ENABLE KEYS */;
111UNLOCK TABLES;
112
113--
114-- Table structure for table `tb_categories`
115--
116
117DROP TABLE IF EXISTS `tb_categories`;
118/*!40101 SET @saved_cs_client = @@character_set_client */;
119/*!40101 SET character_set_client = utf8 */;
120CREATE TABLE `tb_categories` (
121 `idcategory` int(11) NOT NULL AUTO_INCREMENT,
122 `descategory` varchar(32) NOT NULL,
123 `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
124 PRIMARY KEY (`idcategory`)
125) ENGINE=InnoDB DEFAULT CHARSET=utf8;
126/*!40101 SET character_set_client = @saved_cs_client */;
127
128--
129-- Dumping data for table `tb_categories`
130--
131
132LOCK TABLES `tb_categories` WRITE;
133/*!40000 ALTER TABLE `tb_categories` DISABLE KEYS */;
134/*!40000 ALTER TABLE `tb_categories` ENABLE KEYS */;
135UNLOCK TABLES;
136
137--
138-- Table structure for table `tb_orders`
139--
140
141DROP TABLE IF EXISTS `tb_orders`;
142/*!40101 SET @saved_cs_client = @@character_set_client */;
143/*!40101 SET character_set_client = utf8 */;
144CREATE TABLE `tb_orders` (
145 `idorder` int(11) NOT NULL AUTO_INCREMENT,
146 `idcart` int(11) NOT NULL,
147 `iduser` int(11) NOT NULL,
148 `idstatus` int(11) NOT NULL,
149 `vltotal` decimal(10,2) NOT NULL,
150 `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
151 PRIMARY KEY (`idorder`),
152 KEY `FK_orders_carts_idx` (`idcart`),
153 KEY `FK_orders_users_idx` (`iduser`),
154 KEY `fk_orders_ordersstatus_idx` (`idstatus`),
155 CONSTRAINT `fk_orders_carts` FOREIGN KEY (`idcart`) REFERENCES `tb_carts` (`idcart`) ON DELETE NO ACTION ON UPDATE NO ACTION,
156 CONSTRAINT `fk_orders_ordersstatus` FOREIGN KEY (`idstatus`) REFERENCES `tb_ordersstatus` (`idstatus`) ON DELETE NO ACTION ON UPDATE NO ACTION,
157 CONSTRAINT `fk_orders_users` FOREIGN KEY (`iduser`) REFERENCES `tb_users` (`iduser`) ON DELETE NO ACTION ON UPDATE NO ACTION
158) ENGINE=InnoDB DEFAULT CHARSET=utf8;
159/*!40101 SET character_set_client = @saved_cs_client */;
160
161--
162-- Dumping data for table `tb_orders`
163--
164
165LOCK TABLES `tb_orders` WRITE;
166/*!40000 ALTER TABLE `tb_orders` DISABLE KEYS */;
167/*!40000 ALTER TABLE `tb_orders` ENABLE KEYS */;
168UNLOCK TABLES;
169
170--
171-- Table structure for table `tb_ordersstatus`
172--
173
174DROP TABLE IF EXISTS `tb_ordersstatus`;
175/*!40101 SET @saved_cs_client = @@character_set_client */;
176/*!40101 SET character_set_client = utf8 */;
177CREATE TABLE `tb_ordersstatus` (
178 `idstatus` int(11) NOT NULL AUTO_INCREMENT,
179 `desstatus` varchar(32) NOT NULL,
180 `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
181 PRIMARY KEY (`idstatus`)
182) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
183/*!40101 SET character_set_client = @saved_cs_client */;
184
185--
186-- Dumping data for table `tb_ordersstatus`
187--
188
189LOCK TABLES `tb_ordersstatus` WRITE;
190/*!40000 ALTER TABLE `tb_ordersstatus` DISABLE KEYS */;
191INSERT 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');
192/*!40000 ALTER TABLE `tb_ordersstatus` ENABLE KEYS */;
193UNLOCK TABLES;
194
195--
196-- Table structure for table `tb_persons`
197--
198
199DROP TABLE IF EXISTS `tb_persons`;
200/*!40101 SET @saved_cs_client = @@character_set_client */;
201/*!40101 SET character_set_client = utf8 */;
202CREATE TABLE `tb_persons` (
203 `idperson` int(11) NOT NULL AUTO_INCREMENT,
204 `desperson` varchar(64) NOT NULL,
205 `desemail` varchar(128) DEFAULT NULL,
206 `nrphone` bigint(20) DEFAULT NULL,
207 `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
208 PRIMARY KEY (`idperson`)
209) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
210/*!40101 SET character_set_client = @saved_cs_client */;
211
212--
213-- Dumping data for table `tb_persons`
214--
215
216LOCK TABLES `tb_persons` WRITE;
217/*!40000 ALTER TABLE `tb_persons` DISABLE KEYS */;
218INSERT 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');
219/*!40000 ALTER TABLE `tb_persons` ENABLE KEYS */;
220UNLOCK TABLES;
221
222--
223-- Table structure for table `tb_products`
224--
225
226DROP TABLE IF EXISTS `tb_products`;
227/*!40101 SET @saved_cs_client = @@character_set_client */;
228/*!40101 SET character_set_client = utf8 */;
229CREATE TABLE `tb_products` (
230 `idproduct` int(11) NOT NULL,
231 `desproduct` varchar(64) NOT NULL,
232 `vlprice` decimal(10,2) NOT NULL,
233 `vlwidth` decimal(10,2) NOT NULL,
234 `vlheight` decimal(10,2) NOT NULL,
235 `vllength` decimal(10,2) NOT NULL,
236 `vlweight` decimal(10,2) NOT NULL,
237 `desurl` varchar(128) NOT NULL,
238 `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
239 PRIMARY KEY (`idproduct`)
240) ENGINE=InnoDB DEFAULT CHARSET=utf8;
241/*!40101 SET character_set_client = @saved_cs_client */;
242
243--
244-- Dumping data for table `tb_products`
245--
246
247LOCK TABLES `tb_products` WRITE;
248/*!40000 ALTER TABLE `tb_products` DISABLE KEYS */;
249INSERT 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');
250/*!40000 ALTER TABLE `tb_products` ENABLE KEYS */;
251UNLOCK TABLES;
252
253--
254-- Table structure for table `tb_productscategories`
255--
256
257DROP TABLE IF EXISTS `tb_productscategories`;
258/*!40101 SET @saved_cs_client = @@character_set_client */;
259/*!40101 SET character_set_client = utf8 */;
260CREATE TABLE `tb_productscategories` (
261 `idcategory` int(11) NOT NULL,
262 `idproduct` int(11) NOT NULL,
263 PRIMARY KEY (`idcategory`,`idproduct`),
264 KEY `fk_productscategories_products_idx` (`idproduct`),
265 CONSTRAINT `fk_productscategories_categories` FOREIGN KEY (`idcategory`) REFERENCES `tb_categories` (`idcategory`) ON DELETE NO ACTION ON UPDATE NO ACTION,
266 CONSTRAINT `fk_productscategories_products` FOREIGN KEY (`idproduct`) REFERENCES `tb_products` (`idproduct`) ON DELETE NO ACTION ON UPDATE NO ACTION
267) ENGINE=InnoDB DEFAULT CHARSET=utf8;
268/*!40101 SET character_set_client = @saved_cs_client */;
269
270--
271-- Dumping data for table `tb_productscategories`
272--
273
274LOCK TABLES `tb_productscategories` WRITE;
275/*!40000 ALTER TABLE `tb_productscategories` DISABLE KEYS */;
276/*!40000 ALTER TABLE `tb_productscategories` ENABLE KEYS */;
277UNLOCK TABLES;
278
279--
280-- Table structure for table `tb_users`
281--
282
283DROP TABLE IF EXISTS `tb_users`;
284/*!40101 SET @saved_cs_client = @@character_set_client */;
285/*!40101 SET character_set_client = utf8 */;
286CREATE TABLE `tb_users` (
287 `iduser` int(11) NOT NULL AUTO_INCREMENT,
288 `idperson` int(11) NOT NULL,
289 `deslogin` varchar(64) NOT NULL,
290 `despassword` varchar(256) NOT NULL,
291 `inadmin` tinyint(4) NOT NULL DEFAULT '0',
292 `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
293 PRIMARY KEY (`iduser`),
294 KEY `FK_users_persons_idx` (`idperson`),
295 CONSTRAINT `fk_users_persons` FOREIGN KEY (`idperson`) REFERENCES `tb_persons` (`idperson`) ON DELETE NO ACTION ON UPDATE NO ACTION
296) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
297/*!40101 SET character_set_client = @saved_cs_client */;
298
299--
300-- Dumping data for table `tb_users`
301--
302
303LOCK TABLES `tb_users` WRITE;
304/*!40000 ALTER TABLE `tb_users` DISABLE KEYS */;
305INSERT 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');
306/*!40000 ALTER TABLE `tb_users` ENABLE KEYS */;
307UNLOCK TABLES;
308
309--
310-- Table structure for table `tb_userslogs`
311--
312
313DROP TABLE IF EXISTS `tb_userslogs`;
314/*!40101 SET @saved_cs_client = @@character_set_client */;
315/*!40101 SET character_set_client = utf8 */;
316CREATE TABLE `tb_userslogs` (
317 `idlog` int(11) NOT NULL AUTO_INCREMENT,
318 `iduser` int(11) NOT NULL,
319 `deslog` varchar(128) NOT NULL,
320 `desip` varchar(45) NOT NULL,
321 `desuseragent` varchar(128) NOT NULL,
322 `dessessionid` varchar(64) NOT NULL,
323 `desurl` varchar(128) NOT NULL,
324 `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
325 PRIMARY KEY (`idlog`),
326 KEY `fk_userslogs_users_idx` (`iduser`),
327 CONSTRAINT `fk_userslogs_users` FOREIGN KEY (`iduser`) REFERENCES `tb_users` (`iduser`) ON DELETE NO ACTION ON UPDATE NO ACTION
328) ENGINE=InnoDB DEFAULT CHARSET=utf8;
329/*!40101 SET character_set_client = @saved_cs_client */;
330
331--
332-- Dumping data for table `tb_userslogs`
333--
334
335LOCK TABLES `tb_userslogs` WRITE;
336/*!40000 ALTER TABLE `tb_userslogs` DISABLE KEYS */;
337/*!40000 ALTER TABLE `tb_userslogs` ENABLE KEYS */;
338UNLOCK TABLES;
339
340--
341-- Table structure for table `tb_userspasswordsrecoveries`
342--
343
344DROP TABLE IF EXISTS `tb_userspasswordsrecoveries`;
345/*!40101 SET @saved_cs_client = @@character_set_client */;
346/*!40101 SET character_set_client = utf8 */;
347CREATE TABLE `tb_userspasswordsrecoveries` (
348 `idrecovery` int(11) NOT NULL AUTO_INCREMENT,
349 `iduser` int(11) NOT NULL,
350 `desip` varchar(45) NOT NULL,
351 `dtrecovery` datetime DEFAULT NULL,
352 `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
353 PRIMARY KEY (`idrecovery`),
354 KEY `fk_userspasswordsrecoveries_users_idx` (`iduser`),
355 CONSTRAINT `fk_userspasswordsrecoveries_users` FOREIGN KEY (`iduser`) REFERENCES `tb_users` (`iduser`) ON DELETE NO ACTION ON UPDATE NO ACTION
356) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
357/*!40101 SET character_set_client = @saved_cs_client */;
358
359--
360-- Dumping data for table `tb_userspasswordsrecoveries`
361--
362
363LOCK TABLES `tb_userspasswordsrecoveries` WRITE;
364/*!40000 ALTER TABLE `tb_userspasswordsrecoveries` DISABLE KEYS */;
365INSERT 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');
366/*!40000 ALTER TABLE `tb_userspasswordsrecoveries` ENABLE KEYS */;
367UNLOCK TABLES;
368
369--
370-- Dumping routines for database 'db_ecommerce'
371--
372/*!50003 DROP PROCEDURE IF EXISTS `sp_userspasswordsrecoveries_create` */;
373/*!50003 SET @saved_cs_client = @@character_set_client */ ;
374/*!50003 SET @saved_cs_results = @@character_set_results */ ;
375/*!50003 SET @saved_col_connection = @@collation_connection */ ;
376/*!50003 SET character_set_client = utf8 */ ;
377/*!50003 SET character_set_results = utf8 */ ;
378/*!50003 SET collation_connection = utf8_general_ci */ ;
379/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
380/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
381DELIMITER ;;
382CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_userspasswordsrecoveries_create`(
383piduser INT,
384pdesip VARCHAR(45)
385)
386BEGIN
387
388 INSERT INTO tb_userspasswordsrecoveries (iduser, desip)
389 VALUES(piduser, pdesip);
390
391 SELECT * FROM tb_userspasswordsrecoveries
392 WHERE idrecovery = LAST_INSERT_ID();
393
394END ;;
395DELIMITER ;
396/*!50003 SET sql_mode = @saved_sql_mode */ ;
397/*!50003 SET character_set_client = @saved_cs_client */ ;
398/*!50003 SET character_set_results = @saved_cs_results */ ;
399/*!50003 SET collation_connection = @saved_col_connection */ ;
400/*!50003 DROP PROCEDURE IF EXISTS `sp_usersupdate_save` */;
401/*!50003 SET @saved_cs_client = @@character_set_client */ ;
402/*!50003 SET @saved_cs_results = @@character_set_results */ ;
403/*!50003 SET @saved_col_connection = @@collation_connection */ ;
404/*!50003 SET character_set_client = utf8 */ ;
405/*!50003 SET character_set_results = utf8 */ ;
406/*!50003 SET collation_connection = utf8_general_ci */ ;
407/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
408/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
409DELIMITER ;;
410CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_usersupdate_save`(
411piduser INT,
412pdesperson VARCHAR(64),
413pdeslogin VARCHAR(64),
414pdespassword VARCHAR(256),
415pdesemail VARCHAR(128),
416pnrphone BIGINT,
417pinadmin TINYINT
418)
419BEGIN
420
421 DECLARE vidperson INT;
422
423 SELECT idperson INTO vidperson
424 FROM tb_users
425 WHERE iduser = piduser;
426
427 UPDATE tb_persons
428 SET
429 desperson = pdesperson,
430 desemail = pdesemail,
431 nrphone = pnrphone
432 WHERE idperson = vidperson;
433
434 UPDATE tb_users
435 SET
436 deslogin = pdeslogin,
437 despassword = pdespassword,
438 inadmin = pinadmin
439 WHERE iduser = piduser;
440
441 SELECT * FROM tb_users a INNER JOIN tb_persons b USING(idperson) WHERE a.iduser = piduser;
442
443END ;;
444DELIMITER ;
445/*!50003 SET sql_mode = @saved_sql_mode */ ;
446/*!50003 SET character_set_client = @saved_cs_client */ ;
447/*!50003 SET character_set_results = @saved_cs_results */ ;
448/*!50003 SET collation_connection = @saved_col_connection */ ;
449/*!50003 DROP PROCEDURE IF EXISTS `sp_users_delete` */;
450/*!50003 SET @saved_cs_client = @@character_set_client */ ;
451/*!50003 SET @saved_cs_results = @@character_set_results */ ;
452/*!50003 SET @saved_col_connection = @@collation_connection */ ;
453/*!50003 SET character_set_client = utf8 */ ;
454/*!50003 SET character_set_results = utf8 */ ;
455/*!50003 SET collation_connection = utf8_general_ci */ ;
456/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
457/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
458DELIMITER ;;
459CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_users_delete`(
460piduser INT
461)
462BEGIN
463
464 DECLARE vidperson INT;
465
466 SELECT idperson INTO vidperson
467 FROM tb_users
468 WHERE iduser = piduser;
469
470 DELETE FROM tb_users WHERE iduser = piduser;
471 DELETE FROM tb_persons WHERE idperson = vidperson;
472
473END ;;
474DELIMITER ;
475/*!50003 SET sql_mode = @saved_sql_mode */ ;
476/*!50003 SET character_set_client = @saved_cs_client */ ;
477/*!50003 SET character_set_results = @saved_cs_results */ ;
478/*!50003 SET collation_connection = @saved_col_connection */ ;
479/*!50003 DROP PROCEDURE IF EXISTS `sp_users_save` */;
480/*!50003 SET @saved_cs_client = @@character_set_client */ ;
481/*!50003 SET @saved_cs_results = @@character_set_results */ ;
482/*!50003 SET @saved_col_connection = @@collation_connection */ ;
483/*!50003 SET character_set_client = utf8 */ ;
484/*!50003 SET character_set_results = utf8 */ ;
485/*!50003 SET collation_connection = utf8_general_ci */ ;
486/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
487/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
488DELIMITER ;;
489CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_users_save`(
490pdesperson VARCHAR(64),
491pdeslogin VARCHAR(64),
492pdespassword VARCHAR(256),
493pdesemail VARCHAR(128),
494pnrphone BIGINT,
495pinadmin TINYINT
496)
497BEGIN
498
499 DECLARE vidperson INT;
500
501 INSERT INTO tb_persons (desperson, desemail, nrphone)
502 VALUES(pdesperson, pdesemail, pnrphone);
503
504 SET vidperson = LAST_INSERT_ID();
505
506 INSERT INTO tb_users (idperson, deslogin, despassword, inadmin)
507 VALUES(vidperson, pdeslogin, pdespassword, pinadmin);
508
509 SELECT * FROM tb_users a INNER JOIN tb_persons b USING(idperson) WHERE a.iduser = LAST_INSERT_ID();
510
511END ;;
512DELIMITER ;
513/*!50003 SET sql_mode = @saved_sql_mode */ ;
514/*!50003 SET character_set_client = @saved_cs_client */ ;
515/*!50003 SET character_set_results = @saved_cs_results */ ;
516/*!50003 SET collation_connection = @saved_col_connection */ ;
517/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
518
519/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
520/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
521/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
522/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
523/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
524/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
525/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
526
527-- Dump completed on 2017-04-24 11:50:48
528
529index.php
530<?php
531
532require_once("vendor/autoload.php");
533
534$app = new \Slim\Slim();
535
536$app->config('debug', true);
537
538$app->get('/', function() {
539
540 $sql = new gustavo\DB\Sql();
541
542 $results = $sql->select("SELECT * FROM tb_users");
543
544 echo json_encode($results);
545
546});
547
548$app->run();
549
550 ?>
551sql.php
552
553<?php
554
555namespace DB;
556
557class Sql {
558
559 const HOSTNAME = "127.0.0.1:3306";
560 const USERNAME = "root";
561 const PASSWORD = "123456";
562 const DBNAME = "db_ecommerce";
563
564 private $conn;
565
566 public function __construct()
567 {
568
569 $this->conn = new \PDO(
570 "mysql:dbname=".Sql::DBNAME.";host=".Sql::HOSTNAME,
571 Sql::USERNAME,
572 Sql::PASSWORD
573 );
574
575 }
576
577 private function setParams($statement, $parameters = array())
578 {
579
580 foreach ($parameters as $key => $value) {
581
582 $this->bindParam($statement, $key, $value);
583
584 }
585
586 }
587
588 private function bindParam($statement, $key, $value)
589 {
590
591 $statement->bindParam($key, $value);
592
593 }
594
595 public function query($rawQuery, $params = array())
596 {
597
598 $stmt = $this->conn->prepare($rawQuery);
599
600 $this->setParams($stmt, $params);
601
602 $stmt->execute();
603
604 }
605
606 public function select($rawQuery, $params = array()):array
607 {
608
609 $stmt = $this->conn->prepare($rawQuery);
610
611 $this->setParams($stmt, $params);
612
613 $stmt->execute();
614
615 return $stmt->fetchAll(\PDO::FETCH_ASSOC);
616
617 }
618
619}
620
621 ?>
622
623composer.json
624{
625 "name": "gustavo/ecommerce",
626 "authors": [
627 {
628 "name": "gustavo solution",
629 "email": "gustavonarede1@gmail.com"
630 }
631 ],
632 "require": {
633 "phpmailer/phpmailer":"5.2.22",
634 "slim/slim":"2.0",
635 "rain/raintpl":"3.0.0"
636 },
637 "autoload": {
638 "psr-4": {
639 "gustavo\\": "vendor\\gustavo\\php-classes\\src"
640 }
641 }
642}