API tools faq
paste
ganryu

Dump

ganryu
Mar 16th, 2016
125
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 35.57 KB | None | 0 0
raw download clone embed print report
  1. -- MySQL dump 10.13  Distrib 5.6.24, for Win64 (x86_64)
  2. --
  3. -- Host: 127.0.0.1    Database: desarrollo
  4. -- ------------------------------------------------------
  5. -- Server version   5.6.25
  6.  
  7. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  8. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  9. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  10. /*!40101 SET NAMES utf8 */;
  11. /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
  12. /*!40103 SET TIME_ZONE='+00:00' */;
  13. /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
  14. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
  15. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
  16. /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
  17.  
  18. --
  19. -- Table structure for table `arreglo`
  20. --
  21.  
  22. DROP TABLE IF EXISTS `arreglo`;
  23. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  24. /*!40101 SET character_set_client = utf8 */;
  25. CREATE TABLE `arreglo` (
  26.   `arreglo_id` INT(11) DEFAULT NULL,
  27.   `producto_id` INT(11) DEFAULT NULL
  28. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  29. /*!40101 SET character_set_client = @saved_cs_client */;
  30.  
  31. --
  32. -- Dumping data for table `arreglo`
  33. --
  34.  
  35. LOCK TABLES `arreglo` WRITE;
  36. /*!40000 ALTER TABLE `arreglo` DISABLE KEYS */;
  37. /*!40000 ALTER TABLE `arreglo` ENABLE KEYS */;
  38. UNLOCK TABLES;
  39.  
  40. --
  41. -- Table structure for table `carrito`
  42. --
  43.  
  44. DROP TABLE IF EXISTS `carrito`;
  45. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  46. /*!40101 SET character_set_client = utf8 */;
  47. CREATE TABLE `carrito` (
  48.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  49.   `cantidad` INT(11) DEFAULT NULL,
  50.   `cliente_id` INT(11) DEFAULT NULL,
  51.   `inventario_id` INT(11) DEFAULT NULL,
  52.   PRIMARY KEY (`id`)
  53. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  54. /*!40101 SET character_set_client = @saved_cs_client */;
  55.  
  56. --
  57. -- Dumping data for table `carrito`
  58. --
  59.  
  60. LOCK TABLES `carrito` WRITE;
  61. /*!40000 ALTER TABLE `carrito` DISABLE KEYS */;
  62. /*!40000 ALTER TABLE `carrito` ENABLE KEYS */;
  63. UNLOCK TABLES;
  64.  
  65. --
  66. -- Table structure for table `ci_sessions`
  67. --
  68.  
  69. DROP TABLE IF EXISTS `ci_sessions`;
  70. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  71. /*!40101 SET character_set_client = utf8 */;
  72. CREATE TABLE `ci_sessions` (
  73.   `session_id` VARCHAR(40) NOT NULL DEFAULT '0',
  74.   `ip_address` VARCHAR(45) NOT NULL DEFAULT '0',
  75.   `user_agent` VARCHAR(120) NOT NULL,
  76.   `last_activity` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  77.   `user_data` text NOT NULL,
  78.   PRIMARY KEY (`session_id`),
  79.   KEY `last_activity_idx` (`last_activity`)
  80. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  81. /*!40101 SET character_set_client = @saved_cs_client */;
  82.  
  83. --
  84. -- Dumping data for table `ci_sessions`
  85. --
  86.  
  87. LOCK TABLES `ci_sessions` WRITE;
  88. /*!40000 ALTER TABLE `ci_sessions` DISABLE KEYS */;
  89. INSERT INTO `ci_sessions` VALUES ('ff2c63677a464121900b03c8087df859','::1','Mozilla/5.0 (Windows NT 10.0; WOW64; rv:45.0) Gecko/20100101 Firefox/45.0',1458148656,'a:4:{s:9:\"user_data\";s:0:\"\";s:4:\"mail\";s:23:\"administrador@gmail.com\";s:4:\"pass\";s:4:\"1234\";s:3:\"rol\";s:13:\"administrador\";}');
  90. /*!40000 ALTER TABLE `ci_sessions` ENABLE KEYS */;
  91. UNLOCK TABLES;
  92.  
  93. --
  94. -- Table structure for table `cliente`
  95. --
  96.  
  97. DROP TABLE IF EXISTS `cliente`;
  98. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  99. /*!40101 SET character_set_client = utf8 */;
  100. CREATE TABLE `cliente` (
  101.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  102.   `dni` INT(11) DEFAULT NULL,
  103.   `nombre` VARCHAR(45) DEFAULT NULL,
  104.   `apellido` VARCHAR(45) DEFAULT NULL,
  105.   `direccion` VARCHAR(45) DEFAULT NULL,
  106.   `telefono` VARCHAR(45) DEFAULT NULL,
  107.   `usuario_id` INT(11) DEFAULT NULL,
  108.   `habilitado` tinyint(4) DEFAULT NULL,
  109.   PRIMARY KEY (`id`)
  110. ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
  111. /*!40101 SET character_set_client = @saved_cs_client */;
  112.  
  113. --
  114. -- Dumping data for table `cliente`
  115. --
  116.  
  117. LOCK TABLES `cliente` WRITE;
  118. /*!40000 ALTER TABLE `cliente` DISABLE KEYS */;
  119. INSERT INTO `cliente` VALUES (1,0,'Consumidor Final','','','',2,1),(2,123,'Alejandro','Solis','Mitre','(0297) 448-3596',1,0),(4,35659024,'Santiago','Santana','Houssay 2461','(0297) 446-3596',4,0),(5,36906528,'Martin','Gimenez','Codigo 2399','(0297) 472-2546',5,0);
  120. /*!40000 ALTER TABLE `cliente` ENABLE KEYS */;
  121. UNLOCK TABLES;
  122.  
  123. --
  124. -- Table structure for table `contrato`
  125. --
  126.  
  127. DROP TABLE IF EXISTS `contrato`;
  128. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  129. /*!40101 SET character_set_client = utf8 */;
  130. CREATE TABLE `contrato` (
  131.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  132.   `fecha_inicio` VARCHAR(45) DEFAULT NULL,
  133.   `fecha_vencimiento` datetime DEFAULT NULL,
  134.   `coste_mensual` DECIMAL(10,0) DEFAULT NULL,
  135.   `direccion` VARCHAR(45) DEFAULT NULL,
  136.   `cuenta_corriente_id` INT(11) DEFAULT NULL,
  137.   PRIMARY KEY (`id`)
  138. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  139. /*!40101 SET character_set_client = @saved_cs_client */;
  140.  
  141. --
  142. -- Dumping data for table `contrato`
  143. --
  144.  
  145. LOCK TABLES `contrato` WRITE;
  146. /*!40000 ALTER TABLE `contrato` DISABLE KEYS */;
  147. /*!40000 ALTER TABLE `contrato` ENABLE KEYS */;
  148. UNLOCK TABLES;
  149.  
  150. --
  151. -- Table structure for table `contrato_has_movimiento`
  152. --
  153.  
  154. DROP TABLE IF EXISTS `contrato_has_movimiento`;
  155. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  156. /*!40101 SET character_set_client = utf8 */;
  157. CREATE TABLE `contrato_has_movimiento` (
  158.   `contrato_id` INT(11) DEFAULT NULL,
  159.   `movimiento_id` INT(11) DEFAULT NULL
  160. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  161. /*!40101 SET character_set_client = @saved_cs_client */;
  162.  
  163. --
  164. -- Dumping data for table `contrato_has_movimiento`
  165. --
  166.  
  167. LOCK TABLES `contrato_has_movimiento` WRITE;
  168. /*!40000 ALTER TABLE `contrato_has_movimiento` DISABLE KEYS */;
  169. /*!40000 ALTER TABLE `contrato_has_movimiento` ENABLE KEYS */;
  170. UNLOCK TABLES;
  171.  
  172. --
  173. -- Table structure for table `cuenta_corriente`
  174. --
  175.  
  176. DROP TABLE IF EXISTS `cuenta_corriente`;
  177. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  178. /*!40101 SET character_set_client = utf8 */;
  179. CREATE TABLE `cuenta_corriente` (
  180.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  181.   `limite_credito` DECIMAL(10,0) DEFAULT NULL,
  182.   `saldo` DECIMAL(10,0) DEFAULT NULL,
  183.   `cliente_id` INT(11) DEFAULT NULL,
  184.   PRIMARY KEY (`id`)
  185. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  186. /*!40101 SET character_set_client = @saved_cs_client */;
  187.  
  188. --
  189. -- Dumping data for table `cuenta_corriente`
  190. --
  191.  
  192. LOCK TABLES `cuenta_corriente` WRITE;
  193. /*!40000 ALTER TABLE `cuenta_corriente` DISABLE KEYS */;
  194. /*!40000 ALTER TABLE `cuenta_corriente` ENABLE KEYS */;
  195. UNLOCK TABLES;
  196.  
  197. --
  198. -- Table structure for table `entrega`
  199. --
  200.  
  201. DROP TABLE IF EXISTS `entrega`;
  202. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  203. /*!40101 SET character_set_client = utf8 */;
  204. CREATE TABLE `entrega` (
  205.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  206.   `fecha_entrega` datetime DEFAULT NULL,
  207.   `contrato_id` INT(11) DEFAULT NULL,
  208.   `inventario_id` INT(11) DEFAULT NULL,
  209.   PRIMARY KEY (`id`)
  210. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  211. /*!40101 SET character_set_client = @saved_cs_client */;
  212.  
  213. --
  214. -- Dumping data for table `entrega`
  215. --
  216.  
  217. LOCK TABLES `entrega` WRITE;
  218. /*!40000 ALTER TABLE `entrega` DISABLE KEYS */;
  219. /*!40000 ALTER TABLE `entrega` ENABLE KEYS */;
  220. UNLOCK TABLES;
  221.  
  222. --
  223. -- Table structure for table `inventario`
  224. --
  225.  
  226. DROP TABLE IF EXISTS `inventario`;
  227. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  228. /*!40101 SET character_set_client = utf8 */;
  229. CREATE TABLE `inventario` (
  230.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  231.   `stock` INT(11) DEFAULT NULL,
  232.   `producto_id` INT(11) DEFAULT NULL,
  233.   `sucursal_id` INT(11) DEFAULT NULL,
  234.   `habilitado` tinyint(4) DEFAULT NULL,
  235.   PRIMARY KEY (`id`)
  236. ) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;
  237. /*!40101 SET character_set_client = @saved_cs_client */;
  238.  
  239. --
  240. -- Dumping data for table `inventario`
  241. --
  242.  
  243. LOCK TABLES `inventario` WRITE;
  244. /*!40000 ALTER TABLE `inventario` DISABLE KEYS */;
  245. INSERT INTO `inventario` VALUES (19,12,7,1,1),(20,23,7,2,1),(21,34,7,3,1),(25,44,9,1,1),(26,5,9,2,1),(27,0,9,3,1),(28,166,10,1,1),(29,6,10,2,1),(30,0,10,3,1);
  246. /*!40000 ALTER TABLE `inventario` ENABLE KEYS */;
  247. UNLOCK TABLES;
  248.  
  249. --
  250. -- Table structure for table `linea_pedido`
  251. --
  252.  
  253. DROP TABLE IF EXISTS `linea_pedido`;
  254. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  255. /*!40101 SET character_set_client = utf8 */;
  256. CREATE TABLE `linea_pedido` (
  257.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  258.   `precio_producto` DECIMAL(10,0) DEFAULT NULL,
  259.   `cantidad` INT(11) DEFAULT NULL,
  260.   `pedido_id` INT(11) DEFAULT NULL,
  261.   `producto_id` INT(11) DEFAULT NULL,
  262.   PRIMARY KEY (`id`)
  263. ) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8;
  264. /*!40101 SET character_set_client = @saved_cs_client */;
  265.  
  266. --
  267. -- Dumping data for table `linea_pedido`
  268. --
  269.  
  270. LOCK TABLES `linea_pedido` WRITE;
  271. /*!40000 ALTER TABLE `linea_pedido` DISABLE KEYS */;
  272. INSERT INTO `linea_pedido` VALUES (20,10,4,51,7),(21,10,5,52,7),(22,14,9,52,9),(23,10,6,53,7),(24,14,3,53,9),(26,10,33,54,7),(27,10,6,55,7),(28,20,1,56,10),(29,10,6,57,7),(30,14,1,58,9),(31,14,2,59,9),(32,20,1,59,10),(34,10,3,60,7),(35,14,4,60,9),(37,10,4,61,7),(38,14,1,61,9),(39,20,3,62,10),(40,20,3,63,10),(41,20,3,64,10),(42,10,1,65,7),(43,20,3,65,10),(45,20,6,66,10),(46,20,3,67,10),(47,20,4,68,10),(48,20,4,69,10),(49,20,4,70,10),(50,20,5,71,10),(51,10,5,72,7),(52,10,6,73,7),(53,10,3,74,7),(54,10,49,75,7),(55,10,9,76,7);
  273. /*!40000 ALTER TABLE `linea_pedido` ENABLE KEYS */;
  274. UNLOCK TABLES;
  275.  
  276. --
  277. -- Table structure for table `lote`
  278. --
  279.  
  280. DROP TABLE IF EXISTS `lote`;
  281. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  282. /*!40101 SET character_set_client = utf8 */;
  283. CREATE TABLE `lote` (
  284.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  285.   `stock_lote` INT(11) DEFAULT NULL,
  286.   `fecha_ingreso` datetime DEFAULT NULL,
  287.   `fecha_vencimiento` datetime DEFAULT NULL,
  288.   `oferta_id` INT(11) DEFAULT NULL,
  289.   `inventario_id` INT(11) DEFAULT NULL,
  290.   PRIMARY KEY (`id`)
  291. ) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8;
  292. /*!40101 SET character_set_client = @saved_cs_client */;
  293.  
  294. --
  295. -- Dumping data for table `lote`
  296. --
  297.  
  298. LOCK TABLES `lote` WRITE;
  299. /*!40000 ALTER TABLE `lote` DISABLE KEYS */;
  300. INSERT INTO `lote` VALUES (16,10,'2015-11-20 13:17:56','2015-08-14 00:00:00',1,20),(19,4,'2015-11-20 13:29:31','2015-12-10 00:00:00',1,25),(21,5,'2015-11-20 13:30:52','2015-12-19 00:00:00',1,26),(22,6,'2015-11-20 13:31:07','2015-11-20 00:00:00',1,29),(23,13,'2015-11-20 13:31:45','2016-03-11 00:00:00',1,20),(24,30,'2015-11-20 16:38:01','2015-11-30 00:00:00',1,21),(25,4,'2015-11-20 16:38:43','2015-12-09 00:00:00',1,21),(46,19,'2016-03-10 13:58:25','2016-03-10 00:00:00',1,28),(50,40,'2016-03-10 15:56:13','2016-03-27 00:00:00',1,25),(51,123,'2016-03-15 10:34:10','2016-03-15 00:00:00',1,28),(52,12,'2016-03-15 17:46:34','2016-03-16 00:00:00',1,19),(53,12,'2016-03-15 17:47:52','2016-03-15 00:00:00',1,28),(54,12,'2016-03-15 18:57:31','2016-03-15 00:00:00',1,28);
  301. /*!40000 ALTER TABLE `lote` ENABLE KEYS */;
  302. UNLOCK TABLES;
  303. /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  304. /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  305. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  306. /*!50003 SET character_set_client  = utf8 */ ;
  307. /*!50003 SET character_set_results = utf8 */ ;
  308. /*!50003 SET collation_connection  = utf8_general_ci */ ;
  309. /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  310. /*!50003 SET sql_mode              = 'NO_ENGINE_SUBSTITUTION' */ ;
  311. DELIMITER ;;
  312. /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `desarrollo`.`lote_BEFORE_INSERT` BEFORE INSERT ON `lote` FOR EACH ROW
  313. BEGIN
  314.     SET SQL_SAFE_UPDATES = 0;
  315.    
  316.     SET NEW.fecha_ingreso = NOW();
  317.        
  318.     SET SQL_SAFE_UPDATES = 1;
  319. END */;;
  320. DELIMITER ;
  321. /*!50003 SET sql_mode              = @saved_sql_mode */ ;
  322. /*!50003 SET character_set_client  = @saved_cs_client */ ;
  323. /*!50003 SET character_set_results = @saved_cs_results */ ;
  324. /*!50003 SET collation_connection  = @saved_col_connection */ ;
  325. /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  326. /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  327. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  328. /*!50003 SET character_set_client  = utf8 */ ;
  329. /*!50003 SET character_set_results = utf8 */ ;
  330. /*!50003 SET collation_connection  = utf8_general_ci */ ;
  331. /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  332. /*!50003 SET sql_mode              = 'NO_ENGINE_SUBSTITUTION' */ ;
  333. DELIMITER ;;
  334. /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `desarrollo`.`lote_AFTER_INSERT` AFTER INSERT ON `lote` FOR EACH ROW
  335. BEGIN
  336. SET SQL_SAFE_UPDATES = 0;
  337.     UPDATE desarrollo.inventario
  338.     SET inventario.stock = inventario.stock + NEW.stock_lote
  339.     WHERE inventario.id = NEW.inventario_id;
  340. SET SQL_SAFE_UPDATES = 1;
  341. END */;;
  342. DELIMITER ;
  343. /*!50003 SET sql_mode              = @saved_sql_mode */ ;
  344. /*!50003 SET character_set_client  = @saved_cs_client */ ;
  345. /*!50003 SET character_set_results = @saved_cs_results */ ;
  346. /*!50003 SET collation_connection  = @saved_col_connection */ ;
  347. /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  348. /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  349. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  350. /*!50003 SET character_set_client  = utf8 */ ;
  351. /*!50003 SET character_set_results = utf8 */ ;
  352. /*!50003 SET collation_connection  = utf8_general_ci */ ;
  353. /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  354. /*!50003 SET sql_mode              = 'NO_ENGINE_SUBSTITUTION' */ ;
  355. DELIMITER ;;
  356. /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `desarrollo`.`lote_BEFORE_DELETE` BEFORE DELETE ON `lote` FOR EACH ROW
  357. BEGIN
  358.  
  359. SET SQL_SAFE_UPDATES = 0;
  360.     UPDATE desarrollo.inventario
  361.     SET inventario.stock = inventario.stock - OLD.stock_lote
  362.     WHERE inventario.id = OLD.inventario_id;
  363. SET SQL_SAFE_UPDATES = 1;
  364.  
  365. END */;;
  366. DELIMITER ;
  367. /*!50003 SET sql_mode              = @saved_sql_mode */ ;
  368. /*!50003 SET character_set_client  = @saved_cs_client */ ;
  369. /*!50003 SET character_set_results = @saved_cs_results */ ;
  370. /*!50003 SET collation_connection  = @saved_col_connection */ ;
  371.  
  372. --
  373. -- Table structure for table `lotes`
  374. --
  375.  
  376. DROP TABLE IF EXISTS `lotes`;
  377. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  378. /*!40101 SET character_set_client = utf8 */;
  379. CREATE TABLE `lotes` (
  380.   `id` INT(11) NOT NULL,
  381.   `inventario_id` INT(11) DEFAULT NULL,
  382.   `fecha_vencimiento` datetime DEFAULT NULL,
  383.   PRIMARY KEY (`id`)
  384. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  385. /*!40101 SET character_set_client = @saved_cs_client */;
  386.  
  387. --
  388. -- Dumping data for table `lotes`
  389. --
  390.  
  391. LOCK TABLES `lotes` WRITE;
  392. /*!40000 ALTER TABLE `lotes` DISABLE KEYS */;
  393. INSERT INTO `lotes` VALUES (16,20,'2015-08-14 00:00:00'),(18,19,'2015-12-04 00:00:00'),(19,25,'2015-12-10 00:00:00'),(20,28,'2015-11-20 00:00:00'),(21,26,'2015-12-19 00:00:00'),(22,29,'2015-11-20 00:00:00'),(24,21,'2015-11-30 00:00:00');
  394. /*!40000 ALTER TABLE `lotes` ENABLE KEYS */;
  395. UNLOCK TABLES;
  396.  
  397. --
  398. -- Table structure for table `movimiento`
  399. --
  400.  
  401. DROP TABLE IF EXISTS `movimiento`;
  402. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  403. /*!40101 SET character_set_client = utf8 */;
  404. CREATE TABLE `movimiento` (
  405.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  406.   `fecha_hora` datetime DEFAULT NULL,
  407.   `importe` DECIMAL(10,0) DEFAULT NULL,
  408.   `tipo_movimiento` VARCHAR(12) DEFAULT NULL,
  409.   `cuenta_corriente_id` INT(11) DEFAULT NULL,
  410.   PRIMARY KEY (`id`)
  411. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  412. /*!40101 SET character_set_client = @saved_cs_client */;
  413.  
  414. --
  415. -- Dumping data for table `movimiento`
  416. --
  417.  
  418. LOCK TABLES `movimiento` WRITE;
  419. /*!40000 ALTER TABLE `movimiento` DISABLE KEYS */;
  420. /*!40000 ALTER TABLE `movimiento` ENABLE KEYS */;
  421. UNLOCK TABLES;
  422.  
  423. --
  424. -- Table structure for table `movimiento_has_venta`
  425. --
  426.  
  427. DROP TABLE IF EXISTS `movimiento_has_venta`;
  428. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  429. /*!40101 SET character_set_client = utf8 */;
  430. CREATE TABLE `movimiento_has_venta` (
  431.   `movimiento_id` INT(11) DEFAULT NULL,
  432.   `venta_id` INT(11) DEFAULT NULL
  433. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  434. /*!40101 SET character_set_client = @saved_cs_client */;
  435.  
  436. --
  437. -- Dumping data for table `movimiento_has_venta`
  438. --
  439.  
  440. LOCK TABLES `movimiento_has_venta` WRITE;
  441. /*!40000 ALTER TABLE `movimiento_has_venta` DISABLE KEYS */;
  442. /*!40000 ALTER TABLE `movimiento_has_venta` ENABLE KEYS */;
  443. UNLOCK TABLES;
  444.  
  445. --
  446. -- Table structure for table `oferta`
  447. --
  448.  
  449. DROP TABLE IF EXISTS `oferta`;
  450. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  451. /*!40101 SET character_set_client = utf8 */;
  452. CREATE TABLE `oferta` (
  453.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  454.   `fecha_inicio` datetime DEFAULT NULL,
  455.   `fecha_fin` datetime DEFAULT NULL,
  456.   `descuento` INT(11) DEFAULT NULL,
  457.   PRIMARY KEY (`id`)
  458. ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
  459. /*!40101 SET character_set_client = @saved_cs_client */;
  460.  
  461. --
  462. -- Dumping data for table `oferta`
  463. --
  464.  
  465. LOCK TABLES `oferta` WRITE;
  466. /*!40000 ALTER TABLE `oferta` DISABLE KEYS */;
  467. INSERT INTO `oferta` VALUES (1,'0000-00-00 00:00:00','0000-00-00 00:00:00',0);
  468. /*!40000 ALTER TABLE `oferta` ENABLE KEYS */;
  469. UNLOCK TABLES;
  470.  
  471. --
  472. -- Table structure for table `pedido`
  473. --
  474.  
  475. DROP TABLE IF EXISTS `pedido`;
  476. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  477. /*!40101 SET character_set_client = utf8 */;
  478. CREATE TABLE `pedido` (
  479.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  480.   `importe` DECIMAL(10,0) DEFAULT NULL,
  481.   `fecha_hora` datetime DEFAULT NULL,
  482.   `valor_iva` INT(11) DEFAULT NULL,
  483.   `cliente_id` INT(11) DEFAULT NULL,
  484.   `sucursal_id` INT(11) DEFAULT NULL,
  485.   PRIMARY KEY (`id`)
  486. ) ENGINE=InnoDB AUTO_INCREMENT=77 DEFAULT CHARSET=utf8;
  487. /*!40101 SET character_set_client = @saved_cs_client */;
  488.  
  489. --
  490. -- Dumping data for table `pedido`
  491. --
  492.  
  493. LOCK TABLES `pedido` WRITE;
  494. /*!40000 ALTER TABLE `pedido` DISABLE KEYS */;
  495. INSERT INTO `pedido` VALUES (51,48,'2015-11-20 13:25:03',21,1,2),(52,204,'2015-11-20 13:33:18',21,1,2),(53,120,'2015-11-20 14:40:40',21,1,1),(54,396,'2015-11-20 15:05:50',21,1,2),(55,72,'2015-11-20 16:25:52',21,2,1),(56,24,'2015-11-20 16:26:22',21,2,2),(57,72,'2015-11-20 16:39:36',21,1,3),(58,16,'2015-11-20 16:43:41',21,2,1),(59,56,'2015-11-20 22:30:53',21,2,1),(60,100,'2015-11-20 23:43:52',21,2,1),(61,64,'2015-11-20 23:51:27',21,2,2),(62,72,'2016-01-03 21:59:01',21,1,1),(63,72,'2016-01-03 21:59:58',21,1,1),(64,72,'2016-01-03 22:07:31',21,2,1),(65,84,'2016-01-24 16:58:28',21,1,2),(66,144,'2016-01-24 16:59:26',21,2,2),(67,72,'2016-03-10 01:22:10',21,1,1),(68,96,'2016-03-10 11:23:56',21,1,1),(69,96,'2016-03-10 11:32:31',21,1,1),(70,96,'2016-03-10 11:34:32',21,1,1),(71,120,'2016-03-10 14:00:23',21,1,1),(72,60,'2016-03-10 15:52:29',21,1,1),(73,72,'2016-03-10 15:53:31',21,1,1),(74,36,'2016-03-10 16:10:52',21,1,1),(75,588,'2016-03-10 16:12:08',21,1,1),(76,108,'2016-03-10 17:38:11',21,1,1);
  496. /*!40000 ALTER TABLE `pedido` ENABLE KEYS */;
  497. UNLOCK TABLES;
  498. /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  499. /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  500. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  501. /*!50003 SET character_set_client  = utf8 */ ;
  502. /*!50003 SET character_set_results = utf8 */ ;
  503. /*!50003 SET collation_connection  = utf8_general_ci */ ;
  504. /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  505. /*!50003 SET sql_mode              = 'NO_ENGINE_SUBSTITUTION' */ ;
  506. DELIMITER ;;
  507. /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `desarrollo`.`pedido_BEFORE_INSERT` BEFORE INSERT ON `pedido` FOR EACH ROW
  508. thisTrigger: BEGIN
  509.     IF (@disable_triggers) THEN
  510.         LEAVE thisTrigger;
  511.     END IF;
  512.        
  513.     SET SQL_SAFE_UPDATES = 0;
  514.    
  515.     SET NEW.fecha_hora = NOW();
  516.        
  517.     SET SQL_SAFE_UPDATES = 1;
  518. END */;;
  519. DELIMITER ;
  520. /*!50003 SET sql_mode              = @saved_sql_mode */ ;
  521. /*!50003 SET character_set_client  = @saved_cs_client */ ;
  522. /*!50003 SET character_set_results = @saved_cs_results */ ;
  523. /*!50003 SET collation_connection  = @saved_col_connection */ ;
  524. /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  525. /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  526. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  527. /*!50003 SET character_set_client  = utf8 */ ;
  528. /*!50003 SET character_set_results = utf8 */ ;
  529. /*!50003 SET collation_connection  = utf8_general_ci */ ;
  530. /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  531. /*!50003 SET sql_mode              = 'NO_ENGINE_SUBSTITUTION' */ ;
  532. DELIMITER ;;
  533. /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `desarrollo`.`pedido_AFTER_INSERT` AFTER INSERT ON `pedido` FOR EACH ROW
  534. BEGIN
  535.     SET SQL_SAFE_UPDATES = 0;
  536.    
  537.     INSERT INTO linea_pedido(precio_producto, cantidad, pedido_id, producto_id)
  538.     SELECT producto.precio, carrito.cantidad, NEW.id as pedido_id, inventario.producto_id
  539.     FROM carrito
  540.     INNER JOIN inventario
  541.         ON carrito.inventario_id = inventario.id
  542.     INNER JOIN producto
  543.         ON inventario.producto_id = producto.id
  544.     WHERE inventario.sucursal_id = NEW.sucursal_id
  545.         AND carrito.cliente_id = NEW.cliente_id;
  546.        
  547.     DELETE
  548.     FROM carrito
  549.     USING carrito INNER JOIN inventario
  550.     ON carrito.inventario_id = inventario.id
  551.     WHERE
  552.         carrito.cliente_id = NEW.cliente_id
  553.         AND inventario.sucursal_id = NEW.sucursal_id;
  554.     SET SQL_SAFE_UPDATES=1;
  555. END */;;
  556. DELIMITER ;
  557. /*!50003 SET sql_mode              = @saved_sql_mode */ ;
  558. /*!50003 SET character_set_client  = @saved_cs_client */ ;
  559. /*!50003 SET character_set_results = @saved_cs_results */ ;
  560. /*!50003 SET collation_connection  = @saved_col_connection */ ;
  561. /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  562. /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  563. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  564. /*!50003 SET character_set_client  = utf8 */ ;
  565. /*!50003 SET character_set_results = utf8 */ ;
  566. /*!50003 SET collation_connection  = utf8_general_ci */ ;
  567. /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  568. /*!50003 SET sql_mode              = 'NO_ENGINE_SUBSTITUTION' */ ;
  569. DELIMITER ;;
  570. /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `desarrollo`.`pedido_AFTER_DELETE` AFTER DELETE ON `pedido` FOR EACH ROW
  571. thisTrigger: BEGIN
  572.     IF (@disable_triggers) THEN
  573.         LEAVE thisTrigger;
  574.     END IF;
  575.  
  576. SET SQL_SAFE_UPDATES = 0;
  577.  
  578. UPDATE inventario
  579. INNER JOIN linea_pedido
  580.     ON inventario.producto_id = linea_pedido.producto_id
  581. SET inventario.stock = inventario.stock + linea_pedido.cantidad
  582. WHERE inventario.sucursal_id = OLD.sucursal_id
  583.     AND linea_pedido.pedido_id = OLD.id;
  584.    
  585. SET SQL_SAFE_UPDATES = 1;
  586. END */;;
  587. DELIMITER ;
  588. /*!50003 SET sql_mode              = @saved_sql_mode */ ;
  589. /*!50003 SET character_set_client  = @saved_cs_client */ ;
  590. /*!50003 SET character_set_results = @saved_cs_results */ ;
  591. /*!50003 SET collation_connection  = @saved_col_connection */ ;
  592.  
  593. --
  594. -- Table structure for table `producto`
  595. --
  596.  
  597. DROP TABLE IF EXISTS `producto`;
  598. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  599. /*!40101 SET character_set_client = utf8 */;
  600. CREATE TABLE `producto` (
  601.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  602.   `precio` DECIMAL(10,0) DEFAULT NULL,
  603.   `nombre_producto` VARCHAR(45) DEFAULT NULL,
  604.   `descripcion` VARCHAR(45) DEFAULT NULL,
  605.   `tipo_producto` VARCHAR(45) DEFAULT NULL,
  606.   `path_img` VARCHAR(99) DEFAULT NULL,
  607.   PRIMARY KEY (`id`)
  608. ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
  609. /*!40101 SET character_set_client = @saved_cs_client */;
  610.  
  611. --
  612. -- Dumping data for table `producto`
  613. --
  614.  
  615. LOCK TABLES `producto` WRITE;
  616. /*!40000 ALTER TABLE `producto` DISABLE KEYS */;
  617. INSERT INTO `producto` VALUES (7,10,'Rosa','Rosa','Flor Natural','Rosa.jpg'),(9,14,'Tulipan','Tulipan','Flor Natural','Tulipan.jpg'),(10,20,'Coronilla','coronilla','Flor Artificial','Coronilla.jpg');
  618. /*!40000 ALTER TABLE `producto` ENABLE KEYS */;
  619. UNLOCK TABLES;
  620. /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  621. /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  622. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  623. /*!50003 SET character_set_client  = utf8 */ ;
  624. /*!50003 SET character_set_results = utf8 */ ;
  625. /*!50003 SET collation_connection  = utf8_general_ci */ ;
  626. /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  627. /*!50003 SET sql_mode              = 'NO_ENGINE_SUBSTITUTION' */ ;
  628. DELIMITER ;;
  629. /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `desarrollo`.`producto_AFTER_INSERT` AFTER INSERT ON `producto` FOR EACH ROW
  630. BEGIN
  631.     SET SQL_SAFE_UPDATES = 0;
  632.        
  633.     INSERT INTO inventario(stock, producto_id, sucursal_id, habilitado)
  634.     SELECT 0, NEW.id, sucursal.id, 1
  635.     FROM sucursal;
  636.        
  637.     SET SQL_SAFE_UPDATES=1;
  638. END */;;
  639. DELIMITER ;
  640. /*!50003 SET sql_mode              = @saved_sql_mode */ ;
  641. /*!50003 SET character_set_client  = @saved_cs_client */ ;
  642. /*!50003 SET character_set_results = @saved_cs_results */ ;
  643. /*!50003 SET collation_connection  = @saved_col_connection */ ;
  644.  
  645. --
  646. -- Table structure for table `productos`
  647. --
  648.  
  649. DROP TABLE IF EXISTS `productos`;
  650. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  651. /*!40101 SET character_set_client = utf8 */;
  652. CREATE TABLE `productos` (
  653.   `id` INT(11) NOT NULL,
  654.   `inventario_id` INT(11) DEFAULT NULL,
  655.   `fecha_vencimiento` datetime DEFAULT NULL,
  656.   PRIMARY KEY (`id`)
  657. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  658. /*!40101 SET character_set_client = @saved_cs_client */;
  659.  
  660. --
  661. -- Dumping data for table `productos`
  662. --
  663.  
  664. LOCK TABLES `productos` WRITE;
  665. /*!40000 ALTER TABLE `productos` DISABLE KEYS */;
  666. /*!40000 ALTER TABLE `productos` ENABLE KEYS */;
  667. UNLOCK TABLES;
  668.  
  669. --
  670. -- Table structure for table `rol`
  671. --
  672.  
  673. DROP TABLE IF EXISTS `rol`;
  674. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  675. /*!40101 SET character_set_client = utf8 */;
  676. CREATE TABLE `rol` (
  677.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  678.   `nombre_rol` VARCHAR(45) DEFAULT NULL,
  679.   PRIMARY KEY (`id`)
  680. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
  681. /*!40101 SET character_set_client = @saved_cs_client */;
  682.  
  683. --
  684. -- Dumping data for table `rol`
  685. --
  686.  
  687. LOCK TABLES `rol` WRITE;
  688. /*!40000 ALTER TABLE `rol` DISABLE KEYS */;
  689. INSERT INTO `rol` VALUES (1,'cliente'),(2,'vendedor'),(3,'administrador');
  690. /*!40000 ALTER TABLE `rol` ENABLE KEYS */;
  691. UNLOCK TABLES;
  692.  
  693. --
  694. -- Table structure for table `sucursal`
  695. --
  696.  
  697. DROP TABLE IF EXISTS `sucursal`;
  698. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  699. /*!40101 SET character_set_client = utf8 */;
  700. CREATE TABLE `sucursal` (
  701.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  702.   `nombre_sucursal` VARCHAR(45) DEFAULT NULL,
  703.   `direccion` VARCHAR(45) DEFAULT NULL,
  704.   `telefono` INT(11) DEFAULT NULL,
  705.   `mail` VARCHAR(45) DEFAULT NULL,
  706.   PRIMARY KEY (`id`)
  707. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
  708. /*!40101 SET character_set_client = @saved_cs_client */;
  709.  
  710. --
  711. -- Dumping data for table `sucursal`
  712. --
  713.  
  714. LOCK TABLES `sucursal` WRITE;
  715. /*!40000 ALTER TABLE `sucursal` DISABLE KEYS */;
  716. INSERT INTO `sucursal` VALUES (1,'Sucursal1','lavalle123',0,'suc@gmail.com'),(2,'Sucursal2','bla',0,'bla@gmail.com'),(3,'Sucursal3','pepe',4324,'asd@gmail.com');
  717. /*!40000 ALTER TABLE `sucursal` ENABLE KEYS */;
  718. UNLOCK TABLES;
  719.  
  720. --
  721. -- Table structure for table `usuario`
  722. --
  723.  
  724. DROP TABLE IF EXISTS `usuario`;
  725. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  726. /*!40101 SET character_set_client = utf8 */;
  727. CREATE TABLE `usuario` (
  728.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  729.   `mail` VARCHAR(45) DEFAULT NULL,
  730.   `clave_de_acceso` VARCHAR(45) DEFAULT NULL,
  731.   `rol_id` INT(11) DEFAULT NULL,
  732.   PRIMARY KEY (`id`)
  733. ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
  734. /*!40101 SET character_set_client = @saved_cs_client */;
  735.  
  736. --
  737. -- Dumping data for table `usuario`
  738. --
  739.  
  740. LOCK TABLES `usuario` WRITE;
  741. /*!40000 ALTER TABLE `usuario` DISABLE KEYS */;
  742. INSERT INTO `usuario` VALUES (1,'alejandro@gmail.com','1234',1),(2,'vendedor@hotmail.com','1234',2),(3,'administrador@gmail.com','1234',3),(4,'santana.santiago@gmail.cm','1234',1),(5,'jm.gimenez@gmail.com','1234',1),(6,'f.alberto@yahoo.com','alabarda',1);
  743. /*!40000 ALTER TABLE `usuario` ENABLE KEYS */;
  744. UNLOCK TABLES;
  745.  
  746. --
  747. -- Table structure for table `venta`
  748. --
  749.  
  750. DROP TABLE IF EXISTS `venta`;
  751. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  752. /*!40101 SET character_set_client = utf8 */;
  753. CREATE TABLE `venta` (
  754.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  755.   `fecha_hora_venta` datetime DEFAULT NULL,
  756.   `pedido_id` INT(11) DEFAULT NULL,
  757.   PRIMARY KEY (`id`)
  758. ) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8;
  759. /*!40101 SET character_set_client = @saved_cs_client */;
  760.  
  761. --
  762. -- Dumping data for table `venta`
  763. --
  764.  
  765. LOCK TABLES `venta` WRITE;
  766. /*!40000 ALTER TABLE `venta` DISABLE KEYS */;
  767. INSERT INTO `venta` VALUES (26,'2015-11-20 13:25:06',51),(27,'2015-11-20 13:33:59',52),(32,'2015-11-20 16:29:14',55),(33,'2015-11-20 16:30:03',56),(34,'2015-11-20 16:39:42',57),(35,'2015-11-20 16:45:31',58),(36,'2015-11-20 22:31:19',59),(37,'2015-11-20 23:44:11',60),(38,'2015-11-20 23:51:47',61),(39,'2016-01-03 21:59:08',62),(40,'2016-01-03 22:00:04',63),(41,'2016-01-03 22:07:57',53),(42,'2016-01-24 16:57:46',64),(43,'2016-01-24 16:58:34',65),(44,'2016-02-29 14:41:39',54),(45,'2016-03-10 01:22:18',67),(46,'2016-03-10 11:24:18',68),(47,'2016-03-10 11:32:41',69),(48,'2016-03-10 11:36:06',70),(49,'2016-03-10 14:00:33',71),(50,'2016-03-10 15:52:32',72),(51,'2016-03-10 15:53:34',73),(52,'2016-03-10 16:10:56',74),(53,'2016-03-10 16:12:11',75),(54,'2016-03-10 17:38:17',76);
  768. /*!40000 ALTER TABLE `venta` ENABLE KEYS */;
  769. UNLOCK TABLES;
  770. /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  771. /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  772. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  773. /*!50003 SET character_set_client  = utf8 */ ;
  774. /*!50003 SET character_set_results = utf8 */ ;
  775. /*!50003 SET collation_connection  = utf8_general_ci */ ;
  776. /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  777. /*!50003 SET sql_mode              = 'NO_ENGINE_SUBSTITUTION' */ ;
  778. DELIMITER ;;
  779. /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `desarrollo`.`venta_BEFORE_INSERT` BEFORE INSERT ON `venta` FOR EACH ROW
  780. thisTrigger: BEGIN
  781.     IF (@disable_triggers) THEN
  782.         LEAVE thisTrigger;
  783.     END IF;
  784.    
  785.     SET SQL_SAFE_UPDATES = 0;
  786.    
  787.     SET NEW.fecha_hora_venta = NOW();
  788.        
  789.     SET SQL_SAFE_UPDATES = 1;
  790. END */;;
  791. DELIMITER ;
  792. /*!50003 SET sql_mode              = @saved_sql_mode */ ;
  793. /*!50003 SET character_set_client  = @saved_cs_client */ ;
  794. /*!50003 SET character_set_results = @saved_cs_results */ ;
  795. /*!50003 SET collation_connection  = @saved_col_connection */ ;
  796.  
  797. --
  798. -- Dumping events for database 'desarrollo'
  799. --
  800.  
  801. --
  802. -- Dumping routines for database 'desarrollo'
  803. --
  804. /*!50003 DROP PROCEDURE IF EXISTS `sp_actualizar_lotes_venta` */;
  805. /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  806. /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  807. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  808. /*!50003 SET character_set_client  = utf8 */ ;
  809. /*!50003 SET character_set_results = utf8 */ ;
  810. /*!50003 SET collation_connection  = utf8_general_ci */ ;
  811. /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  812. /*!50003 SET sql_mode              = 'NO_ENGINE_SUBSTITUTION' */ ;
  813. DELIMITER ;;
  814. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_actualizar_lotes_venta`(IN id_pedido INTEGER)
  815. BEGIN
  816.     DECLARE id_inventario INTEGER;
  817.     DECLARE cantidad INTEGER;
  818.     DECLARE done INTEGER DEFAULT FALSE;
  819.  
  820.     DECLARE cur1 CURSOR FOR
  821.     SELECT inventario.id, linea_pedido.cantidad
  822.     FROM desarrollo.pedido
  823.     INNER JOIN desarrollo.linea_pedido
  824.         ON pedido.id = linea_pedido.pedido_id
  825.     INNER JOIN desarrollo.inventario
  826.         ON (pedido.sucursal_id = inventario.sucursal_id
  827.         AND linea_pedido.producto_id = inventario.producto_id)
  828.     WHERE pedido.id = id_pedido;
  829.    
  830.     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
  831.    
  832.     OPEN cur1;
  833.     lineas: LOOP
  834.         FETCH cur1 INTO id_inventario, cantidad;
  835.         IF done = 1 THEN LEAVE lineas; END IF;
  836.         CALL sp_quitar_elementos_inventario(id_inventario, cantidad);
  837.     END LOOP;
  838.     CLOSE cur1;
  839. END ;;
  840. DELIMITER ;
  841. /*!50003 SET sql_mode              = @saved_sql_mode */ ;
  842. /*!50003 SET character_set_client  = @saved_cs_client */ ;
  843. /*!50003 SET character_set_results = @saved_cs_results */ ;
  844. /*!50003 SET collation_connection  = @saved_col_connection */ ;
  845. /*!50003 DROP PROCEDURE IF EXISTS `sp_quitar_elementos_inventario` */;
  846. /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  847. /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  848. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  849. /*!50003 SET character_set_client  = utf8 */ ;
  850. /*!50003 SET character_set_results = utf8 */ ;
  851. /*!50003 SET collation_connection  = utf8_general_ci */ ;
  852. /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  853. /*!50003 SET sql_mode              = 'NO_ENGINE_SUBSTITUTION' */ ;
  854. DELIMITER ;;
  855. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_quitar_elementos_inventario`(IN inv_id INTEGER, IN cantidad INTEGER)
  856. BEGIN
  857.     DECLARE lote_id INTEGER;
  858.     DECLARE resto INTEGER;
  859.     DECLARE cant INTEGER DEFAULT cantidad;
  860.    
  861.     SET SQL_SAFE_UPDATES = 0;
  862.    
  863.     UPDATE inventario
  864.     SET inventario.stock = inventario.stock - cantidad
  865.     WHERE inventario.id = inv_id;
  866.    
  867.     quitar_elementos: LOOP
  868.         SELECT lote.id
  869.         INTO lote_id
  870.         FROM desarrollo.lote
  871.         WHERE lote.inventario_id = inv_id
  872.         ORDER BY lote.fecha_vencimiento ASC
  873.         LIMIT 1;
  874.        
  875.         CALL sp_quitar_elementos_lote(lote_id, cant, resto);
  876.        
  877.         IF resto = 0 THEN
  878.             LEAVE quitar_elementos;
  879.         ELSE
  880.             SET cant = resto;
  881.             SET resto = NULL;
  882.         END IF;
  883.     END LOOP quitar_elementos;
  884.    
  885.     SET SQL_SAFE_UPDATES = 1;
  886. END ;;
  887. DELIMITER ;
  888. /*!50003 SET sql_mode              = @saved_sql_mode */ ;
  889. /*!50003 SET character_set_client  = @saved_cs_client */ ;
  890. /*!50003 SET character_set_results = @saved_cs_results */ ;
  891. /*!50003 SET collation_connection  = @saved_col_connection */ ;
  892. /*!50003 DROP PROCEDURE IF EXISTS `sp_quitar_elementos_lote` */;
  893. /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  894. /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  895. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  896. /*!50003 SET character_set_client  = utf8 */ ;
  897. /*!50003 SET character_set_results = utf8 */ ;
  898. /*!50003 SET collation_connection  = utf8_general_ci */ ;
  899. /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  900. /*!50003 SET sql_mode              = 'NO_ENGINE_SUBSTITUTION' */ ;
  901. DELIMITER ;;
  902. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_quitar_elementos_lote`(IN id_lote INTEGER, IN cant INTEGER, OUT resto INTEGER)
  903. BEGIN
  904.     DECLARE stock INTEGER;
  905.     DECLARE dif INTEGER;
  906.    
  907.     SELECT lote.stock_lote INTO stock FROM desarrollo.lote WHERE lote.id = id_lote;
  908.     SET dif = ABS(cant - stock);
  909.    
  910.     SET SQL_SAFE_UPDATES = 0;
  911.     UPDATE desarrollo.lote
  912.     SET stock_lote = IF(stock <= cant, 0, dif)
  913.     WHERE id = id_lote;
  914.    
  915.     DELETE FROM desarrollo.lote WHERE stock_lote = 0 LIMIT 1;
  916.    
  917.     SET resto = IF(stock < cant, dif, 0);
  918.    
  919.     SET SQL_SAFE_UPDATES = 1;
  920. END ;;
  921. DELIMITER ;
  922. /*!50003 SET sql_mode              = @saved_sql_mode */ ;
  923. /*!50003 SET character_set_client  = @saved_cs_client */ ;
  924. /*!50003 SET character_set_results = @saved_cs_results */ ;
  925. /*!50003 SET collation_connection  = @saved_col_connection */ ;
  926. /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
  927.  
  928. /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
  929. /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
  930. /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
  931. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  932. /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  933. /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  934. /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
  935.  
  936. -- Dump completed on 2016-03-16 15:44:04
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!