text: -- MySQL dump 10.13  Distrib 5.7.12, for Win64 (x86_64)
  --
  -- Host: localhost    Database: guardartododb
  -- ------------------------------------------------------
  -- Server version	5.7.17-log
  
  /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  /*!40101 SET NAMES utf8 */;
  /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
  /*!40103 SET TIME_ZONE='+00:00' */;
  /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
  /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
  /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
  /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
  
  --
  -- Table structure for table `client`
  --
  
  DROP TABLE IF EXISTS `client`;
  /*!40101 SET @saved_cs_client     = @@character_set_client */;
  /*!40101 SET character_set_client = utf8 */;
  CREATE TABLE `client` (
    `clientId` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(300) NOT NULL,
    `street` varchar(200) DEFAULT NULL,
    `town` varchar(200) DEFAULT NULL,
    `country` varchar(200) DEFAULT NULL,
    `state` varchar(200) DEFAULT NULL,
    `zipcode` varchar(5) DEFAULT NULL,
    `rfc` varchar(45) DEFAULT NULL,
    `phone` varchar(45) DEFAULT NULL,
    `cellPhone` varchar(45) DEFAULT NULL,
    `email` varchar(250) DEFAULT NULL,
    `legalRepresentative` varchar(400) DEFAULT NULL,
    `lineOfBusiness` varchar(200) DEFAULT NULL,
    `kindOfBusiness` varchar(200) DEFAULT NULL,
    `enable` tinyint(1) DEFAULT '1',
    `createByUserId` int(11) DEFAULT NULL,
    `createDatetime` datetime DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`clientId`)
  ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
  /*!40101 SET character_set_client = @saved_cs_client */;
  
  --
  -- Dumping data for table `client`
  --
  
  LOCK TABLES `client` WRITE;
  /*!40000 ALTER TABLE `client` DISABLE KEYS */;
  INSERT INTO `client` VALUES (12,'Dan Stevens','Downton Abbey','Matthew Crawley','Croydon','Reino Unido','23234','RFDE34534234FGD','234234325234','245234234','dan@sdgdfdfgf.com','KING','Persona Fisica','HOGAR',1,NULL,'2017-01-26 21:27:45'),(13,'Aubrey Plaza','April Ludgate','Parks and Recreation','Wilmington','Delaware, Estados Unidos','','AUPL930239403SFD','765453453','32534634','aubrey@sdffds.com','KING','PERSONA FISICA','HOGAR',1,NULL,'2017-03-22 19:50:36');
  /*!40000 ALTER TABLE `client` ENABLE KEYS */;
  UNLOCK TABLES;
  
  --
  -- Table structure for table `payment`
  --
  
  DROP TABLE IF EXISTS `payment`;
  /*!40101 SET @saved_cs_client     = @@character_set_client */;
  /*!40101 SET character_set_client = utf8 */;
  CREATE TABLE `payment` (
    `paymentId` int(11) NOT NULL,
    `rentId` int(11) NOT NULL,
    `date` datetime NOT NULL,
    `transaction` datetime DEFAULT NULL,
    `promotionId` int(11) DEFAULT NULL,
    `amount` decimal(10,2) NOT NULL DEFAULT '0.00',
    `comment` varchar(200) DEFAULT NULL,
    `methodOfPayment` varchar(45) DEFAULT NULL,
    `enable` tinyint(1) DEFAULT '1',
    `createByUserId` varchar(45) DEFAULT NULL,
    `createDatetime` datetime DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`paymentId`,`rentId`,`date`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  /*!40101 SET character_set_client = @saved_cs_client */;
  
  --
  -- Dumping data for table `payment`
  --
  
  LOCK TABLES `payment` WRITE;
  /*!40000 ALTER TABLE `payment` DISABLE KEYS */;
  /*!40000 ALTER TABLE `payment` ENABLE KEYS */;
  UNLOCK TABLES;
  
  --
  -- Table structure for table `promotion`
  --
  
  DROP TABLE IF EXISTS `promotion`;
  /*!40101 SET @saved_cs_client     = @@character_set_client */;
  /*!40101 SET character_set_client = utf8 */;
  CREATE TABLE `promotion` (
    `promotionId` int(11) NOT NULL AUTO_INCREMENT,
    `promotiontypeId` int(11) NOT NULL,
    `name` varchar(200) NOT NULL,
    `description` varchar(400) DEFAULT NULL,
    `amount` decimal(10,0) DEFAULT NULL,
    `percentage` int(11) DEFAULT NULL,
    `enable` tinyint(1) NOT NULL DEFAULT '1',
    `createByUserId` int(11) DEFAULT NULL,
    `createDatetime` datetime DEFAULT NULL,
    PRIMARY KEY (`promotionId`)
  ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
  /*!40101 SET character_set_client = @saved_cs_client */;
  
  --
  -- Dumping data for table `promotion`
  --
  
  LOCK TABLES `promotion` WRITE;
  /*!40000 ALTER TABLE `promotion` DISABLE KEYS */;
  INSERT INTO `promotion` VALUES (1,1,'3 X 2','Renta por dos meses y llevate el tercero gratis',NULL,100,1,NULL,NULL),(2,1,'Mes gratis al año de renta','Renta por un año y llevate el siguiente mes gratis',NULL,100,1,NULL,NULL);
  /*!40000 ALTER TABLE `promotion` ENABLE KEYS */;
  UNLOCK TABLES;
  
  --
  -- Table structure for table `promotiontype`
  --
  
  DROP TABLE IF EXISTS `promotiontype`;
  /*!40101 SET @saved_cs_client     = @@character_set_client */;
  /*!40101 SET character_set_client = utf8 */;
  CREATE TABLE `promotiontype` (
    `promotiontypeId` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(45) NOT NULL,
    `enable` tinyint(1) DEFAULT '1',
    `createByUserId` varchar(45) DEFAULT NULL,
    `createDatetime` datetime DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`promotiontypeId`)
  ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
  /*!40101 SET character_set_client = @saved_cs_client */;
  
  --
  -- Dumping data for table `promotiontype`
  --
  
  LOCK TABLES `promotiontype` WRITE;
  /*!40000 ALTER TABLE `promotiontype` DISABLE KEYS */;
  INSERT INTO `promotiontype` VALUES (1,'Mes gratis',1,NULL,NULL),(2,'Descuento',1,NULL,NULL);
  /*!40000 ALTER TABLE `promotiontype` ENABLE KEYS */;
  UNLOCK TABLES;
  
  --
  -- Table structure for table `rent`
  --
  
  DROP TABLE IF EXISTS `rent`;
  /*!40101 SET @saved_cs_client     = @@character_set_client */;
  /*!40101 SET character_set_client = utf8 */;
  CREATE TABLE `rent` (
    `rentId` int(11) NOT NULL AUTO_INCREMENT,
    `clientId` int(11) NOT NULL,
    `storagelokerId` int(11) NOT NULL,
    `startDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `endDate` datetime DEFAULT NULL,
    `cost` decimal(10,0) DEFAULT '0',
    `iva` decimal(10,0) DEFAULT '0',
    `extra` decimal(10,0) DEFAULT '0',
    `total` decimal(10,0) DEFAULT '0',
    `comment` varchar(500) NOT NULL DEFAULT '',
    `folio` varchar(20) DEFAULT NULL,
    `active` tinyint(1) NOT NULL DEFAULT '1',
    `enable` tinyint(1) NOT NULL DEFAULT '1',
    `createByUserId` int(11) DEFAULT NULL,
    `createDatetime` datetime DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`rentId`)
  ) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8;
  /*!40101 SET character_set_client = @saved_cs_client */;
  
  --
  -- Dumping data for table `rent`
  --
  
  LOCK TABLES `rent` WRITE;
  /*!40000 ALTER TABLE `rent` DISABLE KEYS */;
  INSERT INTO `rent` VALUES (41,12,1,'2017-04-07 20:39:53',NULL,2500,2,50,2552,'','B1704A01041',1,1,NULL,'2017-04-07 20:40:29'),(42,13,7,'2017-04-08 16:08:53',NULL,2000,52,500,2552,'','B1704A07042',1,1,NULL,'2017-04-08 16:09:16');
  /*!40000 ALTER TABLE `rent` ENABLE KEYS */;
  UNLOCK TABLES;
  
  --
  -- Table structure for table `rentauthorization`
  --
  
  DROP TABLE IF EXISTS `rentauthorization`;
  /*!40101 SET @saved_cs_client     = @@character_set_client */;
  /*!40101 SET character_set_client = utf8 */;
  CREATE TABLE `rentauthorization` (
    `rentAuthorizationId` int(11) NOT NULL AUTO_INCREMENT,
    `rentId` int(11) NOT NULL,
    `userId` int(11) NOT NULL,
    `enable` tinyint(1) DEFAULT '1',
    `createByUserId` varchar(45) DEFAULT NULL,
    `createDatetime` datetime DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`rentAuthorizationId`)
  ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
  /*!40101 SET character_set_client = @saved_cs_client */;
  
  --
  -- Dumping data for table `rentauthorization`
  --
  
  LOCK TABLES `rentauthorization` WRITE;
  /*!40000 ALTER TABLE `rentauthorization` DISABLE KEYS */;
  INSERT INTO `rentauthorization` VALUES (3,41,11,1,NULL,'2017-04-07 20:40:29'),(4,42,11,1,NULL,'2017-04-08 16:09:16');
  /*!40000 ALTER TABLE `rentauthorization` ENABLE KEYS */;
  UNLOCK TABLES;
  
  --
  -- Table structure for table `rentfile`
  --
  
  DROP TABLE IF EXISTS `rentfile`;
  /*!40101 SET @saved_cs_client     = @@character_set_client */;
  /*!40101 SET character_set_client = utf8 */;
  CREATE TABLE `rentfile` (
    `rentfileId` int(11) NOT NULL AUTO_INCREMENT,
    `rentId` int(11) NOT NULL,
    `name` varchar(200) NOT NULL,
    `originalname` varchar(200) DEFAULT NULL,
    `enable` tinyint(1) DEFAULT '1',
    PRIMARY KEY (`rentfileId`)
  ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
  /*!40101 SET character_set_client = @saved_cs_client */;
  
  --
  -- Dumping data for table `rentfile`
  --
  
  LOCK TABLES `rentfile` WRITE;
  /*!40000 ALTER TABLE `rentfile` DISABLE KEYS */;
  INSERT INTO `rentfile` VALUES (6,41,'GTflyer.jpg','GTflyer.jpg',1);
  /*!40000 ALTER TABLE `rentfile` ENABLE KEYS */;
  UNLOCK TABLES;
  
  --
  -- Table structure for table `rentpromotion`
  --
  
  DROP TABLE IF EXISTS `rentpromotion`;
  /*!40101 SET @saved_cs_client     = @@character_set_client */;
  /*!40101 SET character_set_client = utf8 */;
  CREATE TABLE `rentpromotion` (
    `rentpromotionId` int(11) NOT NULL AUTO_INCREMENT,
    `rentId` int(11) NOT NULL,
    `promotionId` int(11) NOT NULL,
    `applied` tinyint(1) DEFAULT '0',
    `enable` tinyint(1) DEFAULT '1',
    `createByUserId` varchar(45) DEFAULT NULL,
    `createDatetime` datetime DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`rentpromotionId`)
  ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
  /*!40101 SET character_set_client = @saved_cs_client */;
  
  --
  -- Dumping data for table `rentpromotion`
  --
  
  LOCK TABLES `rentpromotion` WRITE;
  /*!40000 ALTER TABLE `rentpromotion` DISABLE KEYS */;
  INSERT INTO `rentpromotion` VALUES (7,41,1,0,1,NULL,'2017-04-07 20:40:29'),(8,42,1,0,1,NULL,'2017-04-08 16:09:16'),(9,42,2,0,1,NULL,'2017-04-08 16:09:16');
  /*!40000 ALTER TABLE `rentpromotion` ENABLE KEYS */;
  UNLOCK TABLES;
  
  --
  -- Table structure for table `storageloker`
  --
  
  DROP TABLE IF EXISTS `storageloker`;
  /*!40101 SET @saved_cs_client     = @@character_set_client */;
  /*!40101 SET character_set_client = utf8 */;
  CREATE TABLE `storageloker` (
    `storagelokerId` int(11) NOT NULL AUTO_INCREMENT,
    `storagelokertypeId` int(11) NOT NULL,
    `number` varchar(45) NOT NULL,
    `enable` tinyint(1) DEFAULT '1',
    `createByUserId` int(11) DEFAULT NULL,
    `createDatetime` datetime DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`storagelokerId`)
  ) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8;
  /*!40101 SET character_set_client = @saved_cs_client */;
  
  --
  -- Dumping data for table `storageloker`
  --
  
  LOCK TABLES `storageloker` WRITE;
  /*!40000 ALTER TABLE `storageloker` DISABLE KEYS */;
  INSERT INTO `storageloker` VALUES (1,3,'A01',1,NULL,'2016-12-19 21:01:47'),(2,3,'A02',1,NULL,'2016-12-19 21:01:47'),(3,1,'A03',1,NULL,'2016-12-19 21:01:47'),(4,1,'A04',1,NULL,'2016-12-19 21:01:47'),(5,4,'A05',1,NULL,'2016-12-19 21:01:47'),(6,3,'A06',1,NULL,'2016-12-19 21:01:47'),(7,3,'A07',1,NULL,'2016-12-19 21:01:47'),(8,4,'A08',1,NULL,'2016-12-19 21:01:47'),(9,4,'A09',1,NULL,'2016-12-19 21:01:47'),(10,4,'A10',1,NULL,'2016-12-19 21:01:47'),(11,4,'A11',1,NULL,'2016-12-19 21:01:47'),(12,4,'B01',1,NULL,'2016-12-19 21:01:47'),(13,4,'B02',1,NULL,'2016-12-19 21:01:47'),(14,1,'B03',1,NULL,'2016-12-19 21:01:47'),(15,1,'B04',1,NULL,'2016-12-19 21:01:47'),(16,1,'B05',1,NULL,'2016-12-19 21:01:47'),(17,1,'B06',1,NULL,'2016-12-19 21:01:47'),(18,1,'B07',1,NULL,'2016-12-19 21:01:47'),(19,1,'B08',1,NULL,'2016-12-19 21:01:47'),(20,1,'B09',1,NULL,'2016-12-19 21:01:47'),(21,1,'B10',1,NULL,'2016-12-19 21:01:47'),(22,2,'B11',1,NULL,'2016-12-19 21:01:47'),(23,2,'B12',1,NULL,'2016-12-19 21:01:47'),(24,2,'B13',1,NULL,'2016-12-19 21:01:47'),(25,2,'B14',1,NULL,'2016-12-19 21:01:47'),(26,2,'C01',1,NULL,'2016-12-19 21:01:47'),(27,2,'C02',1,NULL,'2016-12-19 21:01:47'),(28,2,'C03',1,NULL,'2016-12-19 21:01:47'),(29,3,'C04',1,NULL,'2016-12-19 21:01:47'),(30,3,'C05',1,NULL,'2016-12-19 21:01:47'),(31,3,'C06',1,NULL,'2016-12-19 21:01:47'),(32,2,'C07',1,NULL,'2016-12-19 21:01:47'),(33,3,'C08',1,NULL,'2016-12-19 21:01:47'),(34,1,'C09',1,NULL,'2016-12-19 21:01:47'),(35,2,'C10',1,NULL,'2016-12-19 21:01:47'),(36,4,'C11',1,NULL,'2016-12-19 21:01:47'),(37,2,'C12',1,NULL,'2016-12-19 21:01:47'),(38,2,'C13',1,NULL,'2016-12-19 21:01:47'),(39,1,'C14',1,NULL,'2016-12-19 21:01:47'),(40,4,'D01',1,NULL,'2016-12-19 21:01:47'),(41,4,'D02',1,NULL,'2016-12-19 21:01:47'),(42,3,'D03',1,NULL,'2016-12-19 21:01:47'),(43,3,'D04',1,NULL,'2016-12-19 21:01:47'),(44,3,'D05',1,NULL,'2016-12-19 21:01:47');
  /*!40000 ALTER TABLE `storageloker` ENABLE KEYS */;
  UNLOCK TABLES;
  
  --
  -- Table structure for table `storagelokertype`
  --
  
  DROP TABLE IF EXISTS `storagelokertype`;
  /*!40101 SET @saved_cs_client     = @@character_set_client */;
  /*!40101 SET character_set_client = utf8 */;
  CREATE TABLE `storagelokertype` (
    `storagelokertypeId` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(45) NOT NULL,
    `description` varchar(45) DEFAULT NULL,
    `price` decimal(8,2) NOT NULL,
    `size` varchar(45) DEFAULT NULL,
    `enable` tinyint(1) DEFAULT '1',
    `createByUserId` int(11) DEFAULT NULL,
    `createDatetime` datetime DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`storagelokertypeId`)
  ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
  /*!40101 SET character_set_client = @saved_cs_client */;
  
  --
  -- Dumping data for table `storagelokertype`
  --
  
  LOCK TABLES `storagelokertype` WRITE;
  /*!40000 ALTER TABLE `storagelokertype` DISABLE KEYS */;
  INSERT INTO `storagelokertype` VALUES (1,'MiniBodega',NULL,638.00,'3mts',1,NULL,'2016-12-19 20:26:29'),(2,'Chica',NULL,1624.00,'7mts',1,NULL,'2016-12-19 20:26:29'),(3,'Mediana',NULL,2552.00,'11mts',1,NULL,'2016-12-19 20:26:29'),(4,'Grande',NULL,3712.00,'16mts',1,NULL,'2016-12-19 20:26:29');
  /*!40000 ALTER TABLE `storagelokertype` ENABLE KEYS */;
  UNLOCK TABLES;
  
  --
  -- Table structure for table `user`
  --
  
  DROP TABLE IF EXISTS `user`;
  /*!40101 SET @saved_cs_client     = @@character_set_client */;
  /*!40101 SET character_set_client = utf8 */;
  CREATE TABLE `user` (
    `userId` int(11) NOT NULL AUTO_INCREMENT,
    `firstName` varchar(200) NOT NULL,
    `lastName` varchar(200) NOT NULL,
    `username` varchar(20) NOT NULL,
    `password` varchar(20) NOT NULL,
    `reset` tinyint(1) DEFAULT '1',
    `imgUrl` varchar(200) DEFAULT NULL,
    `enable` tinyint(1) DEFAULT '1',
    PRIMARY KEY (`userId`,`username`)
  ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
  /*!40101 SET character_set_client = @saved_cs_client */;
  
  --
  -- Dumping data for table `user`
  --
  
  LOCK TABLES `user` WRITE;
  /*!40000 ALTER TABLE `user` DISABLE KEYS */;
  INSERT INTO `user` VALUES (1,'Administrador','','admin','Gu4rdar@Todo',0,'users-1.svg',1),(11,'Edilberto','Salazar','mosh','tool4',0,'users-10.svg',1);
  /*!40000 ALTER TABLE `user` ENABLE KEYS */;
  UNLOCK TABLES;
  
  --
  -- Dumping routines for database 'guardartododb'
  --
  /*!50003 DROP FUNCTION IF EXISTS `f_get_payment_status` */;
  /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  /*!50003 SET character_set_client  = utf8 */ ;
  /*!50003 SET character_set_results = utf8 */ ;
  /*!50003 SET collation_connection  = utf8_general_ci */ ;
  /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  /*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
  DELIMITER ;;
  CREATE DEFINER=`root`@`localhost` FUNCTION `f_get_payment_status`(rentId int) RETURNS int(11)
  BEGIN
  
  RETURN 1;
  END ;;
  DELIMITER ;
  /*!50003 SET sql_mode              = @saved_sql_mode */ ;
  /*!50003 SET character_set_client  = @saved_cs_client */ ;
  /*!50003 SET character_set_results = @saved_cs_results */ ;
  /*!50003 SET collation_connection  = @saved_col_connection */ ;
  /*!50003 DROP FUNCTION IF EXISTS `f_pending_payments` */;
  /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  /*!50003 SET character_set_client  = utf8 */ ;
  /*!50003 SET character_set_results = utf8 */ ;
  /*!50003 SET collation_connection  = utf8_general_ci */ ;
  /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  /*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
  DELIMITER ;;
  CREATE DEFINER=`root`@`localhost` FUNCTION `f_pending_payments`(rentId int) RETURNS int(11)
  BEGIN
  	DECLARE pending INT;
      DECLARE i INT;
      DECLARE counter INT;
      DECLARE valida BIT;
  
  	SELECT timestampdiff(month, r.startDate, DATE_ADD(NOW(), INTERVAL 1 MONTH)) FROM rent r WHERE r.rentId = rentId INTO pending;
  	SET i = 0;
      SET counter = 0;
  
      WHILE(i < pending) DO
  		SET i = i + 1;
  
  		SELECT COALESCE(sum(p.amount),0) < r.total
  			from payment p
  			INNER JOIN rent r ON p.rentId = r.rentId
  			WHERE p.rentId = rentId AND p.date BETWEEN DATE_ADD(DATE(r.startDate), INTERVAL i-1 MONTH) AND DATE_ADD(DATE(r.startDate), INTERVAL i MONTH)
  			INTO valida;
  
          set counter = counter + IF(valida = 1, 1, 0);
  
      END WHILE;
  
      RETURN counter;
  END ;;
  DELIMITER ;
  /*!50003 SET sql_mode              = @saved_sql_mode */ ;
  /*!50003 SET character_set_client  = @saved_cs_client */ ;
  /*!50003 SET character_set_results = @saved_cs_results */ ;
  /*!50003 SET collation_connection  = @saved_col_connection */ ;
  /*!50003 DROP PROCEDURE IF EXISTS `sp_get_pendingpayment_rent` */;
  /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  /*!50003 SET character_set_client  = utf8 */ ;
  /*!50003 SET character_set_results = utf8 */ ;
  /*!50003 SET collation_connection  = utf8_general_ci */ ;
  /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  /*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
  DELIMITER ;;
  CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_get_pendingpayment_rent`(p_rentId int)
  BEGIN
  	DECLARE pending INT;
      DECLARE i INT;
      DECLARE counter INT;
      drop temporary table if exists valida;
      create temporary table valida (
  		id int unsigned not null,
          valid int null,
  		date date not null
  	);
  
  	SELECT timestampdiff(month, r.startDate,  DATE_ADD(NOW(), INTERVAL 12 MONTH)) FROM rent r WHERE r.rentId = p_rentId INTO pending;
  	SET i = 0;
      SET counter = 0;
  
      WHILE(i < pending) DO
  		SET i = i + 1;
  
  		INSERT INTO valida
          SELECT i, COALESCE(sum(p.amount),0) < r.total, DATE_ADD(DATE(r.startDate), INTERVAL i-1 MONTH)
  			from payment p
  			INNER JOIN rent r ON p.rentId = r.rentId
  			WHERE p.rentId = p_rentId AND p.date BETWEEN DATE_ADD(DATE(r.startDate), INTERVAL i-1 MONTH) AND DATE_ADD(DATE(r.startDate), INTERVAL i MONTH);
  
  
      END WHILE;
  
  	select * FROM valida;
      drop temporary table if exists valida;
  END ;;
  DELIMITER ;
  /*!50003 SET sql_mode              = @saved_sql_mode */ ;
  /*!50003 SET character_set_client  = @saved_cs_client */ ;
  /*!50003 SET character_set_results = @saved_cs_results */ ;
  /*!50003 SET collation_connection  = @saved_col_connection */ ;
  /*!50003 DROP PROCEDURE IF EXISTS `sp_new_clients` */;
  /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  /*!50003 SET character_set_client  = utf8 */ ;
  /*!50003 SET character_set_results = utf8 */ ;
  /*!50003 SET collation_connection  = utf8_general_ci */ ;
  /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  /*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
  DELIMITER ;;
  CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_new_clients`()
  BEGIN
  DECLARE i INT;
    DECLARE counter INT;
    SET i = 0;
    SET counter = 5;
    
    drop temporary table if exists tabletemp;
      create temporary table tabletemp (
          count int null,
  		date date not null
  	);
    
    WHILE(i < counter) DO
  		
  
  		INSERT INTO tabletemp
          SELECT count(c.clientId),  DATE_ADD(date_format(now(), '%y-%m-01' ), INTERVAL i - counter MONTH)
  		FROM client c 
  		WHERE  c.enable = 1 
  
  			AND 
  
  			date(c.createDatetime) BETWEEN 
  
  			DATE_ADD(date_format(now(), '%y-%m-01' ), INTERVAL i - counter MONTH)
  
  			AND 
  
  			last_day(DATE_ADD(date_format(now(), '%y-%m-01' ), INTERVAL i - counter MONTH));
          
          SET i = i + 1;
          
    END WHILE;
    
    select * FROM tabletemp;
    drop temporary table if exists tabletemp;
  END ;;
  DELIMITER ;
  /*!50003 SET sql_mode              = @saved_sql_mode */ ;
  /*!50003 SET character_set_client  = @saved_cs_client */ ;
  /*!50003 SET character_set_results = @saved_cs_results */ ;
  /*!50003 SET collation_connection  = @saved_col_connection */ ;
  /*!50003 DROP PROCEDURE IF EXISTS `sp_storagelokers_rented` */;
  /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  /*!50003 SET character_set_client  = utf8 */ ;
  /*!50003 SET character_set_results = utf8 */ ;
  /*!50003 SET collation_connection  = utf8_general_ci */ ;
  /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  /*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
  DELIMITER ;;
  CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_storagelokers_rented`()
  BEGIN
    DECLARE i INT;
    DECLARE counter INT;
    SET i = 0;
    SET counter = 5;
    
    drop temporary table if exists tabletemp;
      create temporary table tabletemp (
          count int null,
  		date date not null
  	);
    
    WHILE(i < counter) DO
  		
  
  		INSERT INTO tabletemp
          SELECT count(r.rentId),  DATE_ADD(date_format(now(), '%y-%m-01' ), INTERVAL i - counter MONTH)
  		FROM rent r 
  		WHERE  r.enable = 1 
  
  			AND 
  
  			date(r.startDate) BETWEEN 
  
  			DATE_ADD(date_format(now(), '%y-%m-01' ), INTERVAL i - counter MONTH)
  
  			AND 
  
  			last_day(DATE_ADD(date_format(now(), '%y-%m-01' ), INTERVAL i - counter MONTH));
          
          SET i = i + 1;
          
    END WHILE;
    
    select * FROM tabletemp;
    drop temporary table if exists tabletemp;
    
  END ;;
  DELIMITER ;
  /*!50003 SET sql_mode              = @saved_sql_mode */ ;
  /*!50003 SET character_set_client  = @saved_cs_client */ ;
  /*!50003 SET character_set_results = @saved_cs_results */ ;
  /*!50003 SET collation_connection  = @saved_col_connection */ ;
  /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
  
  /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
  /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
  /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
  /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
  
  -- Dump completed on 2017-04-20 22:45:21
  