-- Table structure for table `tipo_liquidaciones`
--

CREATE TABLE `tipo_liquidaciones` (
  `id` int NOT NULL AUTO_INCREMENT,
  `concepto` varchar(40) NOT NULL,
  `valor` decimal(14,2) DEFAULT '0.00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tipo_liquidaciones`
--

INSERT INTO `tipo_liquidaciones` VALUES (1,'Honorarios',100.00),(2,'Liq. de Impuestos',50.00),(3,'FONDO DE TERCEROS',0.00);

--
-- Table structure for table `tipo_socios`
--

CREATE TABLE `tipo_socios` (
  `id` int NOT NULL AUTO_INCREMENT,
  `descripcion` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `tipo_socios`
--

INSERT INTO `tipo_socios` VALUES (1,'ACTIVO'),(2,'BAJA'),(3,'VITALICIO'),(4,'FALLECIDO'),(5,'BECADO');

--
-- Table structure for table `tipos_doc`
--

CREATE TABLE `tipos_doc` (
  `id` int NOT NULL AUTO_INCREMENT,
  `siglas` varchar(10) NOT NULL,
  `descripcion` varchar(60) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tipos_doc`
--

INSERT INTO `tipos_doc` VALUES (1,'AD','A DESIGNAR'),(2,'C.I.','CEDULA DE IDENTIDAD'),(3,'D.N.I.','DOC. UNICO'),(4,'L.E.','LIBRETA DE ENROLAMIENTO'),(5,'CUIL','CUIL');

--
-- Table structure for table `tipos_mov`
--

CREATE TABLE `tipos_mov` (
  `id` int NOT NULL AUTO_INCREMENT,
  `descripcion` varchar(60) NOT NULL,
  `desc_abre` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tipos_mov`
--

INSERT INTO `tipos_mov` VALUES (1,'LIQUIDACIONES DE HONORARIOS','LIQ'),(2,'PAGOS DE HONORARIOS','PGOS');

--
-- Table structure for table `tipos_socios`
--

CREATE TABLE `tipos_socios` (
  `id` int NOT NULL AUTO_INCREMENT,
  `siglas` varchar(10) NOT NULL,
  `descripcion` varchar(60) NOT NULL,
  `importe` decimal(14,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tipos_socios`
--

INSERT INTO `tipos_socios` VALUES (1,'GRUPO FAMI','GRUPO FAMILIAR',2000.00),(2,'PLE MAYOR','PLENO MAYOR',1200.00),(3,'VTCIO','VITALICIO',0.00),(4,'FCDO','FACELLIDO',0.00),(5,'BCDO','BECADO',0.00),(6,'SOCIO DEPO','SOCIO DEPORTIVO',1200.00),(7,'BAJA','BAJA',0.00),(8,'BAJATEMPO','BAJAS TEMPORALES',0.00),(9,'CD','COMISION DIRECTIVA',0.00);

--
-- Temporary view structure for view `v_act_socios`
--

/*!50001 DROP VIEW IF EXISTS `v_act_socios`*/;
/*!50001 CREATE VIEW `v_act_socios` AS SELECT 
 1 AS `TITULARES`,
 1 AS `NULL`,
 1 AS `soc_id`,
 1 AS `apeynom`,
 1 AS `id`,
 1 AS `actividad`,
 1 AS `importe`*/;

--
-- Temporary view structure for view `v_cab_cta_cte`
--

/*!50001 DROP VIEW IF EXISTS `v_cab_cta_cte`*/;
/*!50001 CREATE VIEW `v_cab_cta_cte` AS SELECT 
 1 AS `nro_socio`,
 1 AS `nombre_apellido`,
 1 AS `calle`,
 1 AS `altura`,
 1 AS `departamento`,
 1 AS `piso`,
 1 AS `id_loca`,
 1 AS `id_tpsoc`,
 1 AS `id_cob`,
 1 AS `importe`,
 1 AS `total_deuda`,
 1 AS `fecha_baja`*/;

--
-- Temporary view structure for view `v_estado_ctacte`
--

/*!50001 DROP VIEW IF EXISTS `v_estado_ctacte`*/;
/*!50001 CREATE VIEW `v_estado_ctacte` AS SELECT 
 1 AS `id_cob`,
 1 AS `soc_id`,
 1 AS `haber`,
 1 AS `saldo`,
 1 AS `debe`*/;

--
-- Temporary view structure for view `v_logs_procesos`
--

/*!50001 DROP VIEW IF EXISTS `v_logs_procesos`*/;
/*!50001 CREATE VIEW `v_logs_procesos` AS SELECT 
 1 AS `id`,
 1 AS `tp_err`,
 1 AS `texto_error`,
 1 AS `nomproceso`,
 1 AS `fecha_proceso`,
 1 AS `id_estado`,
 1 AS `descripcion`*/;

--
-- Temporary view structure for view `v_padron`
--

/*!50001 DROP VIEW IF EXISTS `v_padron`*/;
/*!50001 CREATE VIEW `v_padron` AS SELECT 
 1 AS `id`,
 1 AS `apeynom`,
 1 AS `calle`,
 1 AS `altura`,
 1 AS `id_tpsoc`,
 1 AS `soc_fliar`,
 1 AS `apeynom_fliar`,
 1 AS `id_act`,
 1 AS `actividad`,
 1 AS `importe`*/;

--
-- Temporary view structure for view `v_socios`
--

/*!50001 DROP VIEW IF EXISTS `v_socios`*/;
/*!50001 CREATE VIEW `v_socios` AS SELECT 
 1 AS `id`,
 1 AS `apeynom`,
 1 AS `razon_social`,
 1 AS `calle`,
 1 AS `domicilio`,
 1 AS `altura`,
 1 AS `departamento`,
 1 AS `piso`,
 1 AS `id_tpdoc`,
 1 AS `nro_doc`,
 1 AS `fecha_nac`,
 1 AS `e_mail`,
 1 AS `tel`,
 1 AS `tel_fax`,
 1 AS `observaciones`,
 1 AS `id_loca`,
 1 AS `ent_id`,
 1 AS `fecha_inicio`,
 1 AS `fecha_baja`,
 1 AS `id_tpsoc`,
 1 AS `edad`*/;

--
-- Dumping routines for database 'lypcom_sportivo'
--
/*!50003 DROP FUNCTION IF EXISTS `obtener_actividades` */;
DELIMITER ;;
CREATE DEFINER=`dba_user`@`%` FUNCTION `obtener_actividades`(pn_soc int(10)) RETURNS varchar(100) CHARSET utf8mb3
    DETERMINISTIC
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE a CHAR(16);
DECLARE z VARCHAR(100);
DECLARE cur1 CURSOR FOR SELECT actividad FROM act_valorizadas,actividades
where id = id_act and id_act not in (7,8) and id_soc = pn_soc
group by actividad,id_soc
order by id_soc;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

	
	set z = " ";

OPEN cur1;
read_loop : LOOP
FETCH cur1 INTO a;
	
	IF done THEN
      LEAVE read_loop;
    END IF;
	
	set z = CONCAT(CONCAT(z,","), SUBSTRING(a,1,3));
	
 END LOOP;
CLOSE cur1;	
	
	
  return z;

END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `obtener_total_act` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` FUNCTION `obtener_total_act`(pn_soc int(10)) RETURNS decimal(14,2)
    DETERMINISTIC
BEGIN
set @total = 0;
	SELECT  sum(importe)  FROM act_valorizadas,actividades
where id = id_act and id_act not in (7,8)
and id_soc = pn_soc
group by id_soc into @total;
  return @total;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `obt_act_valor` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` FUNCTION `obt_act_valor`(pn_socpad int(10)) RETURNS int
    DETERMINISTIC
BEGIN
set @cantidad= 0;
select count(id_act)
        into @cantidad
        from act_valorizadas
        where id_socpad = pn_socpad
        AND id_act = 1
        group by id_act;
  if @cantidad = '' then
     set @cantidad= 0;
  end if;
  return @cantidad;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `obt_act_valorizada` */;
DELIMITER ;;
CREATE DEFINER=`dba_user`@`%` FUNCTION `obt_act_valorizada`(pn_socpad int(10)) RETURNS int
    DETERMINISTIC
BEGIN
     select  valor.id_act
     into @act
     from act_valorizadas valor
     where valor.id_socpad = pn_socpad;
     RETURN  @act;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `obt_act_valorizada_fliar` */;
DELIMITER ;;
CREATE DEFINER=`dba_user`@`%` FUNCTION `obt_act_valorizada_fliar`(pn_socpad int(10)) RETURNS int
    DETERMINISTIC
BEGIN
     select  valor.id_act
     into @act
     from act_valorizadas valor
     where valor.id_socpad = pn_socpad;
     RETURN  @act;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `obt_leyenda` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` FUNCTION `obt_leyenda`() RETURNS varchar(200) CHARSET utf8mb3
    DETERMINISTIC
BEGIN
      select max(leyenda)
      into @leyenda
      from leyendas;
  
  return @leyenda;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `ob_act_socios` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` FUNCTION `ob_act_socios`(pn_socpad int(10)) RETURNS int
    DETERMINISTIC
BEGIN
	select count(*)   
  into @act
  FROM act_valorizadas
  where id_socpad    =  pn_socpad;
  if @act = '' then
     set @act= 0;
  end if;
  return @act;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `ob_cuota` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` FUNCTION `ob_cuota`( pd_periodo DATE) RETURNS varchar(10) CHARSET utf8mb3
    DETERMINISTIC
BEGIN
select date_format(pd_periodo , '%m') into @n_mes;
  return @n_mes;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `ob_grpo_fliar` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` FUNCTION `ob_grpo_fliar`(pn_socpad int(10)) RETURNS int
    DETERMINISTIC
BEGIN
select count(*)   
  into @grpo_fliar
  FROM soc_fliares
  where id_socpad    =  pn_socpad;
  if @grpo_fliar = '' then
     set @grpo_fliar= 0;
  end if;
  return @grpo_fliar;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `actualizar_edad` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` PROCEDURE `actualizar_edad`()
BEGIN
     update  socios
     set edad = edad + 1
	 where ((DATEDIFF(CURRENT_DATE(), fecha_nac))/365) > edad
     and edad != 0;
     update  socios
     set edad = edad + 1
     where ((DATEDIFF(CURRENT_DATE(), fecha_nac))/365) >= 1
     and edad = 0;

     update  soc_fliares
     set edad = edad + 1
     where ((DATEDIFF(CURRENT_DATE(), fecha_nac))/365) > edad
     and edad != 0;
     update  soc_fliares
     set edad = edad + 1
     where ((DATEDIFF(CURRENT_DATE(), fecha_nac))/365) >= 1
     and edad = 0;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `act_val_titular` */;
DELIMITER ;;
CREATE DEFINER=`dba_user`@`%` PROCEDURE `act_val_titular`(IN `pn_socio` INT(10),
                                                   OUT `pn_act_fut` INT(10), OUT `pn_can_fut` INT(10), OUT `pn_imp_fut` DECIMAL(14,2), 
												   OUT `pn_act_finf` INT(10), OUT `pn_can_finf` INT(10), OUT `pn_imp_finf` DECIMAL(14,2),
												   OUT `pn_act_bas` INT(10), OUT `pn_can_bas` INT(10), OUT `pn_imp_bas` DECIMAL(14,2), 
												   OUT `pn_act_gim` INT(10), OUT `pn_can_gim` INT(10), OUT `pn_imp_gim` DECIMAL(14,2), 
												   OUT `pn_act_fun` INT(10), OUT `pn_can_fun` INT(10), OUT `pn_imp_fun` DECIMAL(14,2), 
												   OUT `pn_act_bap` INT(10), OUT `pn_can_bap` INT(10), OUT `pn_imp_bap` DECIMAL(14,2), 
												   OUT `pn_act_bec` INT(10), OUT `pn_can_bec` INT(10), OUT `pn_imp_bec` DECIMAL(14,2), 
												   OUT `pn_act_feme` INT(10), OUT `pn_can_feme` INT(10), OUT `pn_imp_feme` DECIMAL(14,2)
												  )
BEGIN
     DECLARE done INT DEFAULT FALSE             ;   
     DECLARE n_socgim    INT(10)                ;
     DECLARE n_can_act   INT(10)       DEFAULT 0;
     DECLARE n_act       INT(5)                 ;
     DECLARE n_imp_act   DECIMAL(14,4) DEFAULT 0;
	 DECLARE v_sigla     VARCHAR(3)             ;
     DECLARE cur1 CURSOR FOR
                     select id_socgim, id_act,importe,sigla, count(id_act)
                     from act_valorizadas,
                          actividades
                     where id_socgim = pn_socio
                     and   id_act    = id
                     and   importe > 0
                     group by id_socgim,id_act,importe,sigla;
         
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
     OPEN cur1;
          ciclo1 : LOOP 
            
              FETCH cur1 INTO n_socgim,n_act,n_imp_act,v_sigla, n_can_act;
            IF done THEN
               CLOSE cur1;
               LEAVE ciclo1;
            END IF;
            
            IF n_act = 1 THEN
                SET pn_act_fut = n_act;
             	SET pn_can_fut = n_can_act;
             	SET pn_imp_fut = n_imp_act;
			ELSEIF n_act = 2 THEN
                SET pn_act_finf  = n_act;
             	SET pn_can_finf  = n_can_act;
             	SET pn_imp_finf  = n_imp_act; 	
            ELSEIF n_act = 3 THEN
                SET pn_act_bas = n_act;
            	SET pn_can_bas = n_can_act;	
             	SET pn_imp_bas = n_imp_act;              	 		   
            ELSEIF n_act = 4 THEN
                SET pn_act_gim = n_act;
             	SET pn_can_gim = n_can_act;
             	SET pn_imp_gim = n_imp_act;             	
            ELSEIF n_act = 5 THEN
                SET pn_act_fun = n_act;
             	SET pn_can_fun = n_can_act;
             	SET pn_imp_fun = n_imp_act;             	            	
            ELSEIF n_act = 7 THEN
                SET pn_act_bap = n_act;
             	SET pn_can_bap = n_can_act;
             	SET pn_imp_bap = n_imp_act;  
			ELSEIF n_act = 8 THEN
                SET pn_act_bec  = n_act;
             	SET pn_can_bec = n_can_act;
             	SET pn_imp_bec = n_imp_act; 
            ELSEIF n_act = 9 THEN
                SET pn_act_feme  = n_act;
             	SET pn_can_feme = n_can_act;
             	SET pn_imp_feme = n_imp_act;            
			END IF;
            
        END LOOP ciclo1;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ajustecabctacte` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` PROCEDURE `ajustecabctacte`()
BEGIN
DECLARE done INT DEFAULT FALSE             ;   
DECLARE n_socio     INT(10)                ;
DECLARE n_cob       INT(10)                ;
DECLARE n_deuda     DECIMAL(14,2)          ;
DECLARE n_haber     DECIMAL(14,2)          ;
DECLARE n_debe      DECIMAL(14,2)          ;
DECLARE n_saldo     DECIMAL(14,2)          ;
DECLARE cur1 CURSOR FOR
                     select cab.soc_id,total_deuda, debe, haber, saldo
                     from cab_cta_cte cab,
                          det_cta_cte det
                          where det.soc_id = cab.soc_id
                     AND   cab.total_deuda != det.saldo;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;



OPEN cur1;
     ciclo1 : LOOP 
     SET n_socio = 0;
     FETCH cur1 INTO n_socio,n_deuda,n_debe,n_haber,n_saldo;
     IF done THEN
        CLOSE cur1;
        LEAVE ciclo1;
     END IF;
      
    
    
    UPDATE cab_cta_cte
    SET    total_deuda = n_saldo
    WHERE  id = n_socio
    AND    soc_id = n_socio;
    
    
END LOOP ciclo1;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ajustectacte` */;
DELIMITER ;;
CREATE DEFINER=`dba_user`@`%` PROCEDURE `ajustectacte`()
BEGIN
DECLARE done INT DEFAULT FALSE             ;   
DECLARE n_socio     INT(10)                ;
DECLARE n_cob       INT(10)                ;
DECLARE n_deuda     DECIMAL(14,2)          ;
DECLARE n_haber     DECIMAL(14,2)          ;
DECLARE n_debe      DECIMAL(14,2)          ;
DECLARE n_saldo     DECIMAL(14,2)          ;
DECLARE cur1 CURSOR FOR
                     select soc.id_cob,cab.soc_id,total_deuda, debe, haber, saldo
                     from cab_cta_cte cab,
	                        det_cta_cte det,
                          socios soc
                     where cab.soc_id = det.soc_id
                     AND   cab.soc_id = soc.id
                     AND   det.saldo > 0
                     AND   cab.total_deuda <> det.saldo
                     AND   det.soc_id in(103,686,740,818,1565,1894,2006,2011)
AND det.periodo = 2022
AND det.n_item = 4;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;



OPEN cur1;
     ciclo1 : LOOP 
     SET n_socio = 0;
     FETCH cur1 INTO n_cob,n_socio,n_deuda,n_debe,n_haber,n_saldo;
     IF done THEN
        CLOSE cur1;
        LEAVE ciclo1;
     END IF;
      
    
    
    UPDATE cab_cta_cte
    SET    total_deuda = n_saldo
    WHERE  id = n_socio
    AND    soc_id = n_socio;
    
    
END LOOP ciclo1;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `cobranza` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` PROCEDURE `cobranza`(IN pn_idcob INT, IN pd_periodo  DATE, IN pn_desde INT, IN pn_hasta INT, IN pn_importe decimal(14,2),IN pn_parcial varchar(2))
BEGIN

DECLARE done INT DEFAULT FALSE             ;   
DECLARE n_socio     INT(10)                ;
DECLARE nro_recibo  INT(10)                ;
DECLARE n_importe   DECIMAL(14,4) DEFAULT 0;
DECLARE n_deuda     DECIMAL(14,4) DEFAULT 0;
DECLARE n_total_liq DECIMAL(14,4) DEFAULT 0;
DECLARE v_cuota     VARCHAR(10)            ;

DECLARE cur1 CURSOR FOR
                     SELECT  id, soc_id, importe, deuda_acumulada, total_liq,cuota_nro
                     FROM    recibos
                     WHERE   pago =  'N' 
                     AND     id_cob = pn_idcob  
                     AND     date_format(periodo , '%Y-%m') =  date_format(pd_periodo,'%Y-%m')
                     AND     id between pn_desde AND pn_hasta;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;



OPEN cur1;
     ciclo1 : LOOP 
     SET n_socio = 0;
     FETCH cur1 INTO nro_recibo,n_socio, n_importe,n_deuda,n_total_liq,v_cuota;
     IF done THEN
        CLOSE cur1;
        LEAVE ciclo1;
     END IF;
     
    IF pn_parcial != 'T' THEN
       SET n_total_liq = pn_importe;
    END IF;
           
    
    UPDATE cab_cta_cte
    SET    total_deuda = total_deuda - n_total_liq
    WHERE  id = n_socio
    AND    soc_id = n_socio;
    
    UPDATE det_cta_cte
    SET    haber =  n_total_liq,
           saldo =  debe - n_total_liq
    WHERE  ccte_id = n_socio
    AND    soc_id = n_socio
    AND    id_rcbo = nro_recibo;
    
    UPDATE recibos
    SET pago = 'S'
    WHERE id = nro_recibo
    AND   soc_id = n_socio
    AND   cuota_nro = v_cuota;
    
    
END LOOP ciclo1;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `cob_ajuste` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` PROCEDURE `cob_ajuste`(IN pn_ctacte INT,IN pn_socio INT,  IN pn_recibo INT, IN pn_importe decimal(14,2),IN pn_debe decimal(14,2),IN pn_haber decimal(14,2),IN pn_cuota INT)
BEGIN
     UPDATE cab_cta_cte
    SET    total_deuda =  total_deuda - pn_haber
    WHERE  id = pn_ctacte
    AND    soc_id = pn_socio;
    
    UPDATE det_cta_cte
    SET    debe  = pn_debe,
           haber =  pn_haber,
           saldo =  pn_debe- pn_haber
    WHERE  ccte_id = pn_ctacte
    AND    soc_id  = pn_socio
    AND    id_rcbo = pn_recibo
    AND    n_item  = pn_cuota;
    
    UPDATE recibos
    SET pago = 'S'
    WHERE id = pn_recibo
    AND   soc_id = pn_socio
    AND   cuota_nro = pn_cuota;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `edad` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` PROCEDURE `edad`()
BEGIN
	DECLARE done INT DEFAULT FALSE;                                                                                                                                                                           
    DECLARE pn_edad INT; 
    DECLARE pn_socio INT;  
    DECLARE socios CURSOR FOR  SELECT   id,FLOOR( (curdate() - fecha_nac ) / 10000 ) from socios
                               where fecha_nac is not null;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;                                                                                                                                                   
    OPEN socios;                                                                                                                                                                                           
    read_loop_socios :                                                                                                                                                                                      
    LOOP                                                                                                                                                                                                      
       FETCH socios INTO pn_socio,pn_edad;
       IF done THEN                                                                                                                                                                                    
           LEAVE read_loop_socios;                                                                                                                                                                      
       END IF;                        
                                                                                                                                                                        
                                                                                                                                                                                                          
	   update socios 
       set edad = pn_edad
       where id = pn_socio;
                                                                                                                                                                                                          
	END LOOP;                                                                                                                                                                                                 
    CLOSE socios;                                                                                                                                                                                           
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `edad_fliar` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` PROCEDURE `edad_fliar`()
BEGIN
     DECLARE done INT DEFAULT FALSE;                                                                                                                                                                           
	 DECLARE pn_edad INT; 
     DECLARE pn_socio INT;
     DECLARE soc_fliar CURSOR FOR  SELECT   id,FLOOR( (curdate() - fecha_nac ) / 10000 ) from soc_fliares
                             where fecha_nac is not null;
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;                                                                                                                                                   
     OPEN soc_fliar;                                                                                                                                                                                           
     read_loop_soc_fliar :                                                                                                                                                                                      
     LOOP                                                                                                                                                                                                      
         FETCH soc_fliar INTO pn_socio,pn_edad;
         IF done THEN                                                                                                                                                                                    
           LEAVE read_loop_soc_fliar;                                                                                                                                                                      
         END IF;                        
                                                                                                                                                                        
                                                                                                                                                                                                          
		 update soc_fliares 
         set edad = pn_edad
         where id = pn_socio;
                                                                                                                                                                                                          
     END LOOP;                                                                                                                                                                                                 
     CLOSE soc_fliar;   
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `insertar_socios` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` PROCEDURE `insertar_socios`()
BEGIN                                                                                                                                                                                                     
                                                                                                                                                                                                          
DECLARE done INT DEFAULT FALSE;                                                                                                                                                                           
DECLARE pn_id INT;                                                                                                                                                                                        
DECLARE pv_nombre VARCHAR(60);                                                                                                                                                                            
DECLARE pv_dire   VARCHAR(60);                                                                                                                                                                            
DECLARE pv_numero int(11);                                                                                                                                                                                
DECLARE pn_loca int(10);                                                                                                                                                                                  
DECLARE pv_tel VARCHAR(50);                                                                                                                                                                               
DECLARE pv_fec_alta VARCHAR(50);                                                                                                                                                                          
DECLARE pv_fec_baja VARCHAR(50);                                                                                                                                                                          
DECLARE pn_cobra INT(10);                                                                                                                                                                                 
DECLARE pn_tpsocio  INT(10);                                                                                                                                                                              
DECLARE pn_actividad INT(10);                                                                                                                                                                             
DECLARE pn_profes  INT(10);                                                                                                                                                                               
DECLARE pv_obsoc  varchar(50);                                                                                                                                                                            
DECLARE pv_fecha_nac  VARCHAR(50);                                                                                                                                                                        
DECLARE pn_concepto VARCHAR(50);                                                                                                                                                                          
DECLARE pd_fecha_aper DATE;                                                                                                                                                                               
DECLARE pd_fec_vto DATE;                                                                                                                                                                                  
DECLARE pn_saldo DECIMAL(14,2);                                                                                                                                                                           
DECLARE clientes CURSOR FOR  SELECT clientes.CL_CODIGO,                                                                                                                                                   
								   clientes.CL_NOM_RAS,                                                                                                                   
								   CONCAT(SUBSTRING(clientes.CL_CLAVE,4,2),' ',substring(clientes.CL_DIREC,length(SUBSTRING_INDEX(clientes.CL_DIREC,' ',4)),30)) CL_DIREC,
								   clientes.CL_NUMERO,                                                                                                                    
								   16,                                                                                                                                    
								   clientes.CL_TELEFON,                                                                                                                   
								   clientes.CL_FE_ALTA,                                                                                                                   
								   clientes.CL_FE_BAJA,                                                                                                                   
								   clientes.CL_COBRA,                                                                                                                     
								   clientes.CL_SECCION,                                                                                                                   
								   clientes.CL_DEPORTE,                                                                                                                   
								   clientes.CL_PROFESA,                                                                                                                   
								   clientes.CL_OSOCIAL,                                                                                                                   
								   clientes.CL_FEC_NAC,                                                                                                                   
								   clientes.CL_CON1,                                                                                                                      
								   clientes.CL_SALDO                                                                                                                      
							 FROM sportivo.clientes clientes                                                                                                                  
							 WHERE   TITULAR = 'x'
							 AND     clientes.CL_DIREC IS NOT NULL
							 AND     clientes.CL_COBRA IS NOT NULL  
               AND     clientes.CL_CODIGO IS NOT NULL
               and     NOT EXISTS (SELECT *
                                   FROM socios
                                   WHERE id  = CL_CODIGO);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;                                                                                                                                                   
                                                                                                                                                                                                          
                                                                                                                                                                                                         
 OPEN clientes;                                                                                                                                                                                           
read_loop_clientes :                                                                                                                                                                                      
LOOP                                                                                                                                                                                                      
 FETCH clientes INTO pn_id, pv_nombre, pv_dire, pv_numero, pn_loca, pv_tel, pv_fec_alta, pv_fec_baja,                                                                                                     
                     pn_cobra, pn_tpsocio, pn_actividad, pn_profes, pv_obsoc, pv_fecha_nac, pn_concepto,pn_saldo ;                                                                                        
          IF done THEN                                                                                                                                                                                    
           LEAVE read_loop_clientes;                                                                                                                                                                      
          END IF;                        
                                                                                                                                                                        
                                                                                                                                                                                                          
		 insert into socios (                                                                                                                                                                     
										  id,                                                                                                                     
										  apeynom,                                                                                                                
                                          calle,                                                                                                                                                          
                                          altura,                                                                                                                                                         
										  id_loca,                                                                                                                
										  ent_id,                                                                                                                 
                                          tel,                                                                                                                                                            
                                          fecha_inicio,                                                                                                                                                   
										  fecha_baja,                                                                                                             
										  id_cob,                                                                                                                 
										  id_tpsoc,                                                                                                               
										  id_prof,                                                                                                                
										  ob_social,                                                                                                              
										  fecha_nac )                                                                                                             
							values (                                                                                                                                          
										pn_id,                                                                                                                    
										pv_nombre,                                                                                                                
										pv_dire,                                                                                                                  
										pv_numero,                                                                                                                
										pn_loca,                                                                                                                  
										1,                                                                                                                        
										pv_tel,                                                                                                                   
										pv_fec_alta,                                                                                                              
										pv_fec_baja,                                                                                                              
                                        pn_cobra,                                                                                                                                                         
										pn_tpsocio,                                                                                                               
										pn_profes,                                                                                                                
										pv_obsoc,                                                                                                                 
										pv_fecha_nac                                                                                                              
										) ; 	                                                                                                                  
                                                                                                                                                                                                          
         insert into act_valorizadas (                                                                                                                                                                    
                                       id_act,                                                                                                                                                            
                                       id_tpsoc,                                                                                                                                                          
                                       id_soc,                                                                                                                                                            
                                       id_grpo,                                                                                                                                                           
                                       id_socpad)                                                                                                                                                         
		  	                values (                                                                                                                                                          
							           pn_actividad,                                                                                                                          
									   pn_tpsocio,                                                                                                                    
									   pn_id,                                                                                                                         
									   pn_id,                                                                                                                         
									   pn_id);                                                                                                                        
		                                                                                                                                                                                          
		select CURRENT_DATE() into pd_fecha_aper;                                                                                                                                                 
                                                                                                                                                                                                          
	  	insert into cab_cta_cte(                                                                                                                                                                  
			                    ent_id,                                                                                                                                                       
                          id,                                                                                                                                                                             
			                    soc_id,                                                                                                                                                       
			                    fecha_apertura,                                                                                                                                               
			                    total_deuda ,                                                                                                                                                 
                          observacion,                                                                                                                                                                    
                          cuota_nro)                                                                                                                                                                      
						 values (                                                                                                                                                 
                 1,                                                                                                                                                                                       
						        pn_id,                                                                                                                                            
								pn_id,                                                                                                                                    
								pd_fecha_aper,                                                                                                                            
								pn_saldo,                                                                                                                                 
                null,                                                                                                                                                                                     
                0);                                                                                                                                                                                       
	    select DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH) into pd_fec_vto;                                                                                                                            
        insert into det_cta_cte(                                                                                                                                                                          
                                ent_id,                                                                                                                                                                   
                                ccte_id,                                                                                                                                                                  
             	                soc_id,                                                                                                                                                                   
	                            n_item,                                                                                                                                                               
	                            fecha_vto,                                                                                                                                                            
	                            debe,                                                                                                                                                                 
	                            haber,                                                                                                                                                                
	                            saldo                                                                                                                                                                 
                               )                                                                                                                                                                          
						values (                                                                                                                                                  
						        1,                                                                                                                                                
								pn_id,                                                                                                                                    
								pn_id,                                                                                                                                    
								1,                                                                                                                                        
								pd_fec_vto,                                                                                                                               
								pn_saldo,                                                                                                                                 
								0,                                                                                                                                        
								pn_saldo);							                                                                          
                                                                                                                                                                                                          
END LOOP;                                                                                                                                                                                                 
CLOSE clientes;                                                                                                                                                                                           
                                                                                                                                                                                                          
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `insertar_sociosfliares` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` PROCEDURE `insertar_sociosfliares`()
BEGIN                                                                                                                                                                                                     
                                                                                                                                                                                                          
DECLARE done INT DEFAULT FALSE;                                                                                                                                                                           
DECLARE pn_id INT;
DECLARE pn_idpadre INT;                                                                                                                                                                                        
DECLARE pv_nombre VARCHAR(60);                                                                                                                                                                            
DECLARE pv_dire   VARCHAR(60);                                                                                                                                                                            
DECLARE pv_numero int(11);                                                                                                                                                                                
DECLARE pn_loca int(10);                                                                                                                                                                                  
DECLARE pv_tel VARCHAR(50);                                                                                                                                                                               
DECLARE pv_fec_alta VARCHAR(50);                                                                                                                                                                          
DECLARE pv_fec_baja VARCHAR(50);                                                                                                                                                                          
DECLARE pn_cobra INT(10);                                                                                                                                                                                 
DECLARE pn_tpsocio  INT(10);                                                                                                                                                                              
DECLARE pn_actividad INT(10);                                                                                                                                                                             
DECLARE pn_profes  INT(10);                                                                                                                                                                               
DECLARE pv_obsoc  varchar(50);                                                                                                                                                                            
DECLARE pv_fecha_nac  VARCHAR(50);                                                                                                                                                                        
DECLARE pn_concepto VARCHAR(50);                                                                                                                                                                          
DECLARE pd_fecha_aper DATE;                                                                                                                                                                               
DECLARE pd_fec_vto DATE;                                                                                                                                                                                  
DECLARE pn_saldo DECIMAL(14,2);                                                                                                                                                                           
DECLARE clientes CURSOR FOR  SELECT clientes.codigo_padre,
                                    clientes.CL_CODIGO,                                                                                                                                                   
				    clientes.CL_NOM_RAS,                                                                                                                   
				    CONCAT(SUBSTRING(clientes.CL_CLAVE,4,2),' ',substring(clientes.CL_DIREC,length(SUBSTRING_INDEX(clientes.CL_DIREC,' ',4)),30)) CL_DIREC,
				    clientes.CL_NUMERO,                                                                                                                    
				    16,                                                                                                                                    
				    clientes.CL_TELEFON,                                                                                                                   
				    clientes.CL_FE_ALTA,                                                                                                                   
				    clientes.CL_FE_BAJA,                                                                                                                   
				    clientes.CL_SECCION,                                                                                                                   
				    clientes.CL_DEPORTE,                                                                                                                   
			  	    clientes.CL_PROFESA,                                                                                                                   
				    clientes.CL_OSOCIAL,                                                                                                                   
				    clientes.CL_FEC_NAC,                                                                                                                   
				    clientes.CL_CON1,                                                                                                                      
				    clientes.CL_SALDO                                                                                                                      
			     FROM sportivo.clientes clientes                     
			     WHERE   clientes.CL_DIREC IS NOT NULL
			     AND     clientes.CL_COBRA IS NOT NULL 
           AND     clientes.CL_CODIGO IS NOT NULL 
           AND      clientes.codigo_padre IS NOT NULL
           and     NOT EXISTS (SELECT *
                                   FROM act_valorizadas
                                   WHERE id_socpad  = CL_CODIGO);                                                                                                            
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;                                                                                                                                                                                     
 OPEN clientes;                                                                                                                                                                                           
read_loop_clientes :                                                                                                                                                                                      
LOOP                                                                                                                                                                                                      
 FETCH clientes INTO pn_idpadre,pn_id, pv_nombre, pv_dire, pv_numero, pn_loca, pv_tel, pv_fec_alta, pv_fec_baja,                                                                                                     
                      pn_tpsocio, pn_actividad, pn_profes, pv_obsoc, pv_fecha_nac, pn_concepto,pn_saldo ;                                                                                        
          IF done THEN                                                                                                                                                                                    
           LEAVE read_loop_clientes;                                                                                                                                                                      
          END IF;                                           
                                                                                                                                                                        
                                                                                                                                                                                                          
		                                                                                                          
                                                                                                                                                                                                          
        insert into act_valorizadas (                                                                                                                                                                    
                                       id_act,                                                                                                                                                            
                                       id_tpsoc,                                                                                                                                                          
                                       id_soc,                                                                                                                                                            
                                       id_socpad)                                                                                                                                                         
		  	                values (                                                                                                                                                          
							           pn_actividad,                                                                                                                          
									   pn_tpsocio,                                                                                                                    
									   pn_id,                                                                                                                         
									   pn_idpadre);                                                                                                                    
                                                                                                                                                                                                          
END LOOP;                                                                                                                                                                                                 
CLOSE clientes;                                                                                                                                                                                           
                                                                                                                                                                                                          
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `liquidacion` */;
DELIMITER ;;
CREATE DEFINER=`dba_user`@`%` PROCEDURE `liquidacion`(IN `pn_idcob` INT, IN `pd_periodo` DATE)
BEGIN
DECLARE done INT DEFAULT FALSE             ;   
DECLARE n_socio     INT(10)                ;
DECLARE v_apeynom   VARCHAR(60)            ;
DECLARE v_calle     VARCHAR(40)            ;
DECLARE v_altura    VARCHAR(10)            ;
DECLARE v_dpto      VARCHAR(2)             ;
DECLARE n_piso      INT(2)                 ;
DECLARE n_loca      INT(5)                 ;
DECLARE n_tpsoc     INT(5)                 ;
DECLARE n_cob       INT(10)                ;
DECLARE v_desctpsoc VARCHAR(60)            ;
DECLARE n_importe   DECIMAL(14,2) DEFAULT 0;
DECLARE n_deuda     DECIMAL(14,2)          ;
DECLARE n_grpofliar INT(10)                ;
DECLARE n_socfliar  INT(10)                ;
DECLARE n_socpad    INT(10)                ;
DECLARE n_can_act   INT(10)       DEFAULT 0;
DECLARE n_act       INT(5)                 ;
DECLARE n_act_fut   INT(5)                 ;
DECLARE n_act_bas   INT(5)                 ;
DECLARE n_act_pat   INT(5)                 ;
DECLARE n_act_pad   INT(5)                 ;
DECLARE n_act_ten   INT(5)                 ;
DECLARE n_act_vol   INT(5)                 ;
DECLARE n_act_fib   INT(5)                 ;
DECLARE n_imp_act   DECIMAL(14,4) DEFAULT 0;
DECLARE n_can_fut   INT(10)       DEFAULT 0;       
DECLARE n_imp_fut   DECIMAL(14,4) DEFAULT 0; 
DECLARE n_can_bas   INT(10)       DEFAULT 0;
DECLARE n_imp_bas   DECIMAL(14,4) DEFAULT 0;
DECLARE n_can_pat   INT(10)       DEFAULT 0;
DECLARE n_imp_pat   DECIMAL(14,4) DEFAULT 0;
DECLARE n_can_pad   INT(10)       DEFAULT 0;
DECLARE n_imp_pad   DECIMAL(14,4) DEFAULT 0;
DECLARE n_can_ten   INT(10)       DEFAULT 0;
DECLARE n_imp_ten   DECIMAL(14,4) DEFAULT 0;
DECLARE n_can_vol   INT(10)       DEFAULT 0;
DECLARE n_imp_vol   DECIMAL(14,4) DEFAULT 0;
DECLARE n_can_fib   INT(10)       DEFAULT 0;
DECLARE n_imp_fib   DECIMAL(14,4) DEFAULT 0;
DECLARE n_act_feme  INT(5)                 ;
DECLARE n_can_feme  INT(5)                 ;
DECLARE n_imp_feme  DECIMAL(14,4) DEFAULT 0;

DECLARE n_act_fvir  INT(5)                 ;
DECLARE n_can_fvir  INT(5)                 ;
DECLARE n_imp_fvir  DECIMAL(14,4) DEFAULT 0;
DECLARE n_act_finf  INT(5)                 ;
DECLARE n_can_finf  INT(5)                 ;
DECLARE n_imp_finf  DECIMAL(14,4)          ;

DECLARE n_total_act DECIMAL(14,4) DEFAULT 0;
DECLARE n_total_actb DECIMAL(14,4) DEFAULT 0;
DECLARE n_total_actp DECIMAL(14,4) DEFAULT 0;
DECLARE n_total_actt DECIMAL(14,4) DEFAULT 0;
DECLARE n_total_actpt DECIMAL(14,4) DEFAULT 0;
DECLARE n_total_actv DECIMAL(14,4) DEFAULT 0;
DECLARE n_total_acfi DECIMAL(14,4) DEFAULT 0;
DECLARE n_total_acfe DECIMAL(14,4) DEFAULT 0;
DECLARE v_cuota     VARCHAR(10)            ;
DECLARE v_leyenda   VARCHAR(200)            ;
DECLARE d_fec_vto   DATE                    ;
DECLARE n_recibo    INT(10)        DEFAULT 0;
DECLARE n_total_liq DECIMAL(14,4) DEFAULT 0;
DECLARE n_mes       INT(10)       DEFAULT 0;
DECLARE cur1 CURSOR FOR
                     SELECT  nro_socio,nombre_apellido,calle,altura,departamento,piso,id_loca,
                             id_tpsoc,id_cob,importe,total_deuda
                     FROM    v_cab_cta_cte
                     WHERE   importe <> 0 
                     AND     id_cob = pn_idcob  
                     AND     fecha_baja is null;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;



OPEN cur1;
     ciclo1 : LOOP 
     SET n_socio = 0;
     FETCH cur1 INTO n_socio,v_apeynom,v_calle,v_altura,v_dpto,n_piso,n_loca,n_tpsoc,n_cob,
                     n_importe,n_deuda;
     IF done THEN
        CLOSE cur1;
        LEAVE ciclo1;
     END IF;
     SET n_grpofliar = ob_grpo_fliar(n_socio);
     select month(pd_periodo) INTO n_mes;
     SET v_cuota = ob_cuota(pd_periodo);
     SET v_leyenda = obt_leyenda();
	 CALL act_val_titular(n_socio,n_act_fut,n_can_fut,n_imp_fut,n_act_bas,n_can_bas,n_imp_bas,n_act_pad,n_can_pad,n_imp_pad,
                             n_act_ten,n_can_ten,n_imp_ten,n_act_pat,n_can_pat,n_imp_pat,n_act_vol,n_can_vol,n_imp_vol,n_act_fib,
							 n_can_fib,n_imp_fib,n_act_feme,n_can_feme,n_imp_feme, n_act_fvir,n_can_fvir,n_imp_fvir,n_act_finf,
							 n_can_finf,n_imp_finf);
     if n_mes NOT IN(1,2) THEN
        
     
        SET v_cuota = ob_cuota(pd_periodo);
        SET v_leyenda = obt_leyenda();
        IF n_imp_fut != '' THEN
           set n_total_act = (n_imp_fut * n_can_fut);
        END IF;
        IF n_imp_bas != '' THEN
           IF  n_total_act != '' THEN  
               set n_total_act = n_total_act + (n_imp_bas * n_can_bas);
           ELSE
               set n_total_act = (n_imp_bas * n_can_bas);
           END IF;        
        END IF;
        IF n_imp_pad != '' THEN
           IF  n_total_act != '' THEN  
               set n_total_act = n_total_act + (n_imp_pad * n_can_pad);
           ELSE
               set n_total_act = (n_imp_pad * n_can_pad);
           END IF;        
        END IF;
        IF n_imp_ten != '' THEN
          IF  n_total_act != '' THEN  
              set n_total_act = n_total_act + (n_imp_ten * n_can_ten);
          ELSE
              set n_total_act = (n_imp_ten * n_can_ten);
          END IF;        
        END IF;
        IF n_imp_pat != '' THEN
           IF  n_total_act != '' THEN  
               set n_total_act = n_total_act + (n_imp_pat * n_can_pat);
           ELSE
               set n_total_act = (n_imp_pat * n_can_pat);
           END IF;        
        END IF;
        IF n_imp_vol != '' THEN
           IF  n_total_act != '' THEN  
               set n_total_act = n_total_act + (n_imp_vol * n_can_vol);
           ELSE
               set n_total_act = (n_imp_vol * n_can_vol);
           END IF;        
        END IF;
		IF n_imp_fib != '' THEN
           IF  n_total_act != '' THEN  
               set n_total_act = n_total_act + (n_imp_fib * n_can_fib);
           ELSE
               set n_total_act = (n_imp_fib * n_can_fib);
           END IF;        
        END IF;
		IF n_imp_feme != '' THEN
           IF  n_total_act != '' THEN  
               set n_total_act = n_total_act + (n_imp_feme * n_can_feme);
           ELSE
               set n_total_act = (n_imp_feme * n_can_feme);
           END IF;        
        END IF;
		IF n_imp_fvir != '' THEN
           IF  n_total_act != '' THEN  
               set n_total_act = n_total_act + (n_imp_fvir * n_can_fvir);
           ELSE
               set n_total_act = (n_imp_fvir * n_can_fvir);
           END IF;        
        END IF;
		
		IF n_imp_finf != '' THEN
           IF  n_total_act != '' THEN  
               set n_total_act = n_total_act + (n_imp_finf * n_can_finf);
           ELSE
               set n_total_act = (n_imp_finf * n_can_finf);
           END IF;        
        END IF;
		
									 
        IF n_deuda < 0 THEN
           set n_total_liq = n_deuda - (n_importe + n_total_act); 
        elseif  n_deuda = 0 THEN 
           set n_total_liq =  n_importe  + n_total_act;
        elseif  n_deuda > 0 THEN
           set n_total_liq = n_deuda + (n_importe + n_total_act); 
        END IF;    
     ELSE
	    
		
        IF n_deuda < 0 THEN
           set n_total_liq = n_deuda - (n_importe); 
        elseif  n_deuda = 0 THEN 
           set n_total_liq =  n_importe;
        elseif  n_deuda > 0 THEN
           set n_total_liq = n_deuda + (n_importe); 
        END IF;
     END IF;
     insert into recibos(soc_id,                                
                         cuota_nro,                             
                         ent_id,                                
                         fecha_emision,                         
                         forma_pago,                            
                         importe,                               
                         deuda_acumulada,                       
                         pago,                                  
                         leyenda,                               
                         cant_fliar,                            
                         act_fut,                               
                         imp_fut,                               
                         act_basq,                              
                         imp_basq,  
                         act_fib,
                         imp_fib,						 
                         act_vol,                               
                         imp_vol,                               
                         act_padd,                              
                         imp_padd,                              
                         act_patin,                             
                         imp_patin,                             
                         act_tenis,                             
                         imp_tenis, 
                         act_feme,
                         imp_feme,						 
                         periodo  ,
                         can_act_fut,
                         can_act_bas,
						 can_act_fib,
                         can_act_vol,
                         can_act_pad,
                         can_act_pat,
                         can_act_ten,
						 can_act_fem,
                         id_cob,
                         total_liq,
						 act_fvir,
						 imp_fvir,
						 cant_act_fvir,
						 act_finf    ,
						 imp_finf,
						 cant_act_finf
						 
                        )                                  
                  values(
                         n_socio,
                         v_cuota,
                         1      ,
                         current_date(),
                         1             ,
                         n_importe     ,
                         n_deuda       ,
                         'N'           ,
                         v_leyenda     ,
                         n_grpofliar   ,
                         n_act_fut     ,
                         n_imp_fut     ,
                         n_act_bas     ,
                         n_imp_bas     ,
						 n_act_fib     ,
						 n_imp_fib     ,
                         n_act_vol     ,
                         n_imp_vol     ,
                         n_act_pad     ,
                         n_imp_pad     ,
                         n_act_pat     ,
                         n_imp_pat     ,
                         n_act_ten     ,
                         n_imp_ten     ,
						 n_act_feme    ,
						 n_imp_feme    ,
                         pd_periodo    ,
                         n_can_fut     ,
                         n_can_bas     ,
						 n_can_fib     ,
                         n_can_vol     ,
                         n_can_pad     ,
                         n_can_pat     ,
                         n_can_ten     ,
						 n_can_feme    ,
                         pn_idcob      ,
                         n_total_liq   ,
						 n_act_fvir    ,
						 n_imp_fvir    ,
						 n_can_fvir    ,
						 n_act_finf    ,
						 n_imp_finf    ,
						 n_can_finf    
						 
                         ); 
    
    
    UPDATE cab_cta_cte
    SET    total_deuda = n_total_liq
    WHERE  id = n_socio
    AND    soc_id = n_socio;
    
    select DATE_ADD(pd_periodo, INTERVAL 1 MONTH) into d_fec_vto;
    
    select max(id) into n_recibo
    from recibos
    where soc_id = n_socio;
    INSERT INTO det_cta_cte(
                            ent_id,
                            ccte_id,
                            soc_id,
                            n_item,
                            fecha_vto,
                            debe,
                            haber,
                            saldo,
                            id_rcbo,
                            periodo
                           )
                    VALUES (
                            1,
                            n_socio,
                            n_socio,
                            v_cuota,
                            d_fec_vto,
                            n_total_liq,
                            0,
                            n_total_liq,
                            n_recibo,
                            date_format(pd_periodo , '%Y') 
                           );      
    SET n_imp_act     = 0;
		SET n_can_fut     = 0;       
		SET n_imp_fut     = 0; 
		SET n_can_bas     = 0;
		SET n_imp_bas     = 0;
		SET n_can_fib     = 0;
		SET n_imp_fib     = 0;
		SET n_can_pat     = 0;
		SET n_imp_pat     = 0;
		SET n_can_pad     = 0;
		SET n_imp_pad     = 0;
		SET n_can_ten     = 0;
		SET n_imp_ten     = 0;
		SET n_can_vol     = 0;
		SET n_imp_vol     = 0;
		SET n_total_act   = 0;
		SET n_total_actb  = 0;
		SET n_total_acfi  = 0;
		SET n_total_actp  = 0;
		SET n_total_actt  = 0;
		SET n_total_actpt = 0;
		SET n_total_actv  = 0;
		SET n_total_liq   = 0;         
        SET n_total_acfe  = 0;	
        SET n_act_fvir    = 0;
        SET n_can_fvir    = 0;
        SET n_imp_fvir    = 0;	
		SET n_act_finf    = 0;
        SET n_can_finf    = 0;
        SET n_imp_finf    = 0;
END LOOP ciclo1;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `liq_ind` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` PROCEDURE `liq_ind`(IN pn_idcob INT, IN pd_periodo  DATE, IN pn_socio INT(10))
BEGIN
DECLARE done INT DEFAULT FALSE             ;
DECLARE n_socio     INT(10)                ;
DECLARE v_apeynom   VARCHAR(60)            ;
DECLARE v_calle     VARCHAR(40)            ;
DECLARE v_altura    VARCHAR(10)            ;
DECLARE v_dpto      VARCHAR(2)             ;
DECLARE n_piso      INT(2)                 ;
DECLARE n_loca      INT(5)                 ;
DECLARE n_tpsoc     INT(5)                 ;
DECLARE n_cob       INT(10)                ;
DECLARE v_desctpsoc VARCHAR(60)            ;
DECLARE n_importe   DECIMAL(14,2) DEFAULT 0;
DECLARE n_deuda     DECIMAL(14,2)          ;
DECLARE n_grpofliar INT(10)                ;
DECLARE n_socfliar  INT(10)                ;
DECLARE n_socpad    INT(10)                ;
DECLARE n_can_act   INT(10)       DEFAULT 0;
DECLARE n_act       INT(5)                 ;
DECLARE n_act_fut   INT(5)                 ;
DECLARE n_act_bas   INT(5)                 ;
DECLARE n_act_pat   INT(5)                 ;
DECLARE n_act_pad   INT(5)                 ;
DECLARE n_act_ten   INT(5)                 ;
DECLARE n_act_vol   INT(5)                 ;
DECLARE n_act_fib   INT(5)                 ;
DECLARE n_imp_act   DECIMAL(14,4) DEFAULT 0;
DECLARE n_can_fut   INT(10)       DEFAULT 0;       
DECLARE n_imp_fut   DECIMAL(14,4) DEFAULT 0; 
DECLARE n_can_bas   INT(10)       DEFAULT 0;
DECLARE n_imp_bas   DECIMAL(14,4) DEFAULT 0;
DECLARE n_can_pat   INT(10)       DEFAULT 0;
DECLARE n_imp_pat   DECIMAL(14,4) DEFAULT 0;
DECLARE n_can_pad   INT(10)       DEFAULT 0;
DECLARE n_imp_pad   DECIMAL(14,4) DEFAULT 0;
DECLARE n_can_ten   INT(10)       DEFAULT 0;
DECLARE n_imp_ten   DECIMAL(14,4) DEFAULT 0;
DECLARE n_can_vol   INT(10)       DEFAULT 0;
DECLARE n_imp_vol   DECIMAL(14,4) DEFAULT 0;
DECLARE n_can_fib   INT(10)       DEFAULT 0;
DECLARE n_imp_fib   DECIMAL(14,4) DEFAULT 0;
DECLARE n_act_feme  INT(5)                 ;
DECLARE n_can_feme  INT(5)                 ;
DECLARE n_imp_feme  DECIMAL(14,4) DEFAULT 0;

DECLARE n_act_fvir  INT(5)                 ;
DECLARE n_can_fvir  INT(5)                 ;
DECLARE n_imp_fvir  DECIMAL(14,4) DEFAULT 0;
DECLARE n_act_finf  INT(5)                 ;
DECLARE n_can_finf  INT(5)                 ;
DECLARE n_imp_finf  DECIMAL(14,4)          ;

DECLARE n_total_act DECIMAL(14,4) DEFAULT 0;
DECLARE n_total_actb DECIMAL(14,4) DEFAULT 0;
DECLARE n_total_actp DECIMAL(14,4) DEFAULT 0;
DECLARE n_total_actt DECIMAL(14,4) DEFAULT 0;
DECLARE n_total_actpt DECIMAL(14,4) DEFAULT 0;
DECLARE n_total_actv DECIMAL(14,4) DEFAULT 0;
DECLARE n_total_acfi DECIMAL(14,4) DEFAULT 0;
DECLARE n_total_acfe DECIMAL(14,4) DEFAULT 0;
DECLARE v_cuota     VARCHAR(10)            ;
DECLARE v_leyenda   VARCHAR(200)            ;
DECLARE d_fec_vto   DATE                    ;
DECLARE n_recibo    INT(10)        DEFAULT 0;
DECLARE n_total_liq DECIMAL(14,4) DEFAULT 0;
DECLARE n_mes       INT(10)       DEFAULT 0;

DECLARE cur1 CURSOR FOR
                     SELECT  nro_socio,nombre_apellido,calle,altura,departamento,piso,id_loca,
                             id_tpsoc,id_cob,importe,total_deuda
                     FROM    v_cab_cta_cte
                     WHERE   importe <> 0 
                     AND     id_cob = pn_idcob  
                     AND     fecha_baja is null
                     AND     nro_socio = pn_socio;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;



OPEN cur1;
     ciclo1 : LOOP 
     SET n_socio = 0;
     FETCH cur1 INTO n_socio,v_apeynom,v_calle,v_altura,v_dpto,n_piso,n_loca,n_tpsoc,n_cob,
                     n_importe,n_deuda;
     IF done THEN
        CLOSE cur1;
        LEAVE ciclo1;
     END IF;
     SET n_grpofliar = ob_grpo_fliar(n_socio);
     
     CALL act_val_titular(n_socio,n_act_fut,n_can_fut,n_imp_fut,n_act_bas,n_can_bas,n_imp_bas,n_act_pad,n_can_pad,n_imp_pad,
                             n_act_ten,n_can_ten,n_imp_ten,n_act_pat,n_can_pat,n_imp_pat,n_act_vol,n_can_vol,n_imp_vol,n_act_fib,
							 n_can_fib,n_imp_fib,n_act_feme,n_can_feme,n_imp_feme, n_act_fvir,n_can_fvir,n_imp_fvir,n_act_finf,
							 n_can_finf,n_imp_finf);
     
     SET v_cuota = ob_cuota(pd_periodo);
     SET v_leyenda = obt_leyenda();
     IF n_imp_fut != '' THEN
       set n_total_act = (n_imp_fut * n_can_fut);
     END IF;
     IF n_imp_bas != '' THEN
       IF  n_total_act != '' THEN  
           set n_total_act = n_total_act + (n_imp_bas * n_can_bas);
       ELSE
           set n_total_act = (n_imp_bas * n_can_bas);
       END IF;        
     END IF;
     IF n_imp_pad != '' THEN
       IF  n_total_act != '' THEN  
           set n_total_act = n_total_act + (n_imp_pad * n_can_pad);
       ELSE
           set n_total_act = (n_imp_pad * n_can_pad);
       END IF;        
     END IF;
     IF n_imp_ten != '' THEN
       IF  n_total_act != '' THEN  
           set n_total_act = n_total_act + (n_imp_ten * n_can_ten);
       ELSE
           set n_total_act = (n_imp_ten * n_can_ten);
       END IF;        
     END IF;
     IF n_imp_pat != '' THEN
       IF  n_total_act != '' THEN  
           set n_total_act = n_total_act + (n_imp_pat * n_can_pat);
       ELSE
           set n_total_act = (n_imp_pat * n_can_pat);
       END IF;        
     END IF;
     IF n_imp_vol != '' THEN
       IF  n_total_act != '' THEN  
           set n_total_act = n_total_act + (n_imp_vol * n_can_vol);
       ELSE
           set n_total_act = (n_imp_vol * n_can_vol);
       END IF;        
     END IF;
	 
	 IF n_imp_fib != '' THEN
           IF  n_total_act != '' THEN  
               set n_total_act = n_total_act + (n_imp_fib * n_can_fib);
           ELSE
               set n_total_act = (n_imp_fib * n_can_fib);
           END IF;        
        END IF;
		IF n_imp_feme != '' THEN
           IF  n_total_act != '' THEN  
               set n_total_act = n_total_act + (n_imp_feme * n_can_feme);
           ELSE
               set n_total_act = (n_imp_feme * n_can_feme);
           END IF;        
        END IF;
		IF n_imp_fvir != '' THEN
           IF  n_total_act != '' THEN  
               set n_total_act = n_total_act + (n_imp_fvir * n_can_fvir);
           ELSE
               set n_total_act = (n_imp_fvir * n_can_fvir);
           END IF;        
        END IF;
		
		IF n_imp_finf != '' THEN
           IF  n_total_act != '' THEN  
               set n_total_act = n_total_act + (n_imp_finf * n_can_finf);
           ELSE
               set n_total_act = (n_imp_finf * n_can_finf);
           END IF;        
        END IF;
	 
     IF n_deuda < 0 THEN
       set n_total_liq = n_deuda - (n_importe + n_total_act); 
     elseif  n_deuda = 0 THEN 
       set n_total_liq =  n_importe  + n_total_act;
     elseif  n_deuda > 0 THEN
       set n_total_liq = n_deuda - (n_importe + n_total_act); 
     END IF;    
     insert into recibos(soc_id,                                
                         cuota_nro,                             
                         ent_id,                                
                         fecha_emision,                         
                         forma_pago,                            
                         importe,                               
                         deuda_acumulada,                       
                         pago,                                  
                         leyenda,                               
                         cant_fliar,                            
                         act_fut,                               
                         imp_fut,                               
                         act_basq,                              
                         imp_basq,  
                         act_fib,
                         imp_fib,						 
                         act_vol,                               
                         imp_vol,                               
                         act_padd,                              
                         imp_padd,                              
                         act_patin,                             
                         imp_patin,                             
                         act_tenis,                             
                         imp_tenis, 
                         act_feme,
                         imp_feme,						 
                         periodo  ,
                         can_act_fut,
                         can_act_bas,
						 can_act_fib,
                         can_act_vol,
                         can_act_pad,
                         can_act_pat,
                         can_act_ten,
						 can_act_fem,
                         id_cob,
                         total_liq,
						 act_fvir,
						 imp_fvir,
						 cant_act_fvir,
						 act_finf    ,
						 imp_finf,
						 cant_act_finf
                        )                                  
                  values(
                         n_socio,
                         v_cuota,
                         1      ,
                         current_date(),
                         1             ,
                         n_importe     ,
                         n_deuda       ,
                         'N'           ,
                         v_leyenda     ,
                         n_grpofliar   ,
                         n_act_fut     ,
                         n_imp_fut     ,
                         n_act_bas     ,
                         n_imp_bas     ,
						 n_act_fib     ,
						 n_imp_fib     ,
                         n_act_vol     ,
                         n_imp_vol     ,
                         n_act_pad     ,
                         n_imp_pad     ,
                         n_act_pat     ,
                         n_imp_pat     ,
                         n_act_ten     ,
                         n_imp_ten     ,
						 n_act_feme    ,
						 n_imp_feme    ,
                         pd_periodo    ,
                         n_can_fut     ,
                         n_can_bas     ,
						 n_can_fib     ,
                         n_can_vol     ,
                         n_can_pad     ,
                         n_can_pat     ,
                         n_can_ten     ,
						 n_can_feme    ,
                         pn_idcob      ,
                         n_total_liq   ,
						 n_act_fvir    ,
						 n_imp_fvir    ,
						 n_can_fvir    ,
						 n_act_finf    ,
						 n_imp_finf    ,
						 n_can_finf    
                         ); 
    
    
    UPDATE cab_cta_cte
    SET    total_deuda = n_total_liq
    WHERE  id = n_socio
    AND    soc_id = n_socio;
    
    select DATE_ADD(pd_periodo, INTERVAL 1 MONTH) into d_fec_vto;
    
    select max(id) into n_recibo
    from recibos
    where soc_id = n_socio;
    INSERT INTO det_cta_cte(
                            ent_id,
                            ccte_id,
                            soc_id,
                            n_item,
                            fecha_vto,
                            debe,
                            haber,
                            saldo,
                            id_rcbo,
                            periodo
                           )
                    VALUES (
                            1,
                            n_socio,
                            n_socio,
                            v_cuota,
                            d_fec_vto,
                            n_total_liq,
                            0,
                            n_total_liq,
                            n_recibo,
                            date_format(pd_periodo , '%Y') 
                           );  
 SET n_imp_act     = 0;
		SET n_can_fut     = 0;       
		SET n_imp_fut     = 0; 
		SET n_can_bas     = 0;
		SET n_imp_bas     = 0;
		SET n_can_fib     = 0;
		SET n_imp_fib     = 0;
		SET n_can_pat     = 0;
		SET n_imp_pat     = 0;
		SET n_can_pad     = 0;
		SET n_imp_pad     = 0;
		SET n_can_ten     = 0;
		SET n_imp_ten     = 0;
		SET n_can_vol     = 0;
		SET n_imp_vol     = 0;
		SET n_total_act   = 0;
		SET n_total_actb  = 0;
		SET n_total_acfi  = 0;
		SET n_total_actp  = 0;
		SET n_total_actt  = 0;
		SET n_total_actpt = 0;
		SET n_total_actv  = 0;
		SET n_total_liq   = 0;         
        SET n_total_acfe  = 0;	
        SET n_act_fvir    = 0;
        SET n_can_fvir    = 0;
        SET n_imp_fvir    = 0;	
		SET n_act_finf    = 0;
        SET n_can_finf    = 0;
        SET n_imp_finf    = 0;						   
END LOOP ciclo1;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `obt_fliar` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` PROCEDURE `obt_fliar`(in  pn_socio int(10),
									     out pv_apeynom varchar(60)  )
BEGIN
     DECLARE done INT DEFAULT FALSE             ; 
     DECLARE cur1 CURSOR FOR
                     select  apeynom                            
                     from soc_fliares
                     where id = pn_socio;
         
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
     OPEN cur1;
          ciclo1 : LOOP 
            
              FETCH cur1 INTO  pv_apeynom ;
            IF done THEN
               CLOSE cur1;
               LEAVE ciclo1;
            END IF;
            
        END LOOP ciclo1;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prueba` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` PROCEDURE `prueba`(OUT param1 VARCHAR(100))
BEGIN
set param1 = '12345';
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `p_baja_fliar` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` PROCEDURE `p_baja_fliar`()
    NO SQL
begin
DECLARE done INT DEFAULT FALSE             ;  
DECLARE n_id int(11);
DECLARE d_fec_baja date;
DECLARE cur1 CURSOR FOR
					   SELECT id, fecha_baja
                       FROM   socios
                       where  fecha_baja is not null;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
     ciclo1 : LOOP
     FETCH cur1 INTO n_id,d_fec_baja;
     IF done THEN
        CLOSE cur1;
        LEAVE ciclo1;
     END IF;
	 UPDATE soc_fliares
     SET fecha_baja = d_fec_baja
     WHERE id_socpad = n_id
     AND   fecha_baja is null;
END LOOP ciclo1;

end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `p_baja_socio` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` PROCEDURE `p_baja_socio`(IN `pn_socio` INT, IN `pn_cob` INT)
    NO SQL
BLOCK1: BEGIN
     DECLARE d_fec_baja date        ;
	 
     set d_fec_baja = current_date();
     
	 UPDATE socios
	 SET fecha_baja = d_fec_baja
     WHERE  id = pn_socio
	 AND    id_cob = pn_cob;
	 
	 DELETE FROM act_valorizadas
     WHERE id_soc = pn_socio
     AND   id_socpad = pn_socio;
	 BLOCK2: BEGIN
		  DECLARE done1 INT DEFAULT FALSE             ; 
		  DECLARE n_soc_fliar INT;				  
		  DECLARE cur2 CURSOR FOR
								SELECT id      
								FROM   soc_fliares       
								WHERE  id_socpad = pn_socio;

		  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;
		  OPEN cur2;
			  ciclo2 : LOOP 
			  FETCH cur2 INTO  n_soc_fliar;
			  IF done1 THEN
				 CLOSE cur2;
				 LEAVE ciclo2;
			  END IF;
			  UPDATE soc_fliares
			  SET fecha_baja = d_fec_baja
			  WHERE  id_socpad = pn_socio;
			  
			  DELETE FROM act_valorizadas
              WHERE id_soc = n_soc_fliar
              AND   id_socpad = pn_socio;
		  END LOOP ciclo2;

     END BLOCK2;
     
END BLOCK1 ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `p_reorganizo_ctacte` */;
DELIMITER ;;
CREATE DEFINER=`dba_user`@`%` PROCEDURE `p_reorganizo_ctacte`()
    NO SQL
BEGIN
DECLARE done INT DEFAULT FALSE             ;   
DECLARE n_cteid     INT(10)                ;
DECLARE n_nitem     INT(10)                ;
DECLARE n_saldo     DECIMAL(14,2)          ;
DECLARE cur1 CURSOR FOR
                     select det.ccte_id,max(n_item)
                     from det_cta_cte det
					 where periodo = '2021'
					 and ccte_id in(316,1413,1443,1446,1547,1968,1981)
                     group by det.ccte_id;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;



OPEN cur1;
     ciclo1 : LOOP 
     FETCH cur1 INTO n_cteid,n_nitem;
     IF done THEN
        CLOSE cur1;
        LEAVE ciclo1;
     END IF;
      
     SELECT saldo 
	 INTO   n_saldo
	 FROM   det_cta_cte
	 WHERE  ccte_id = n_cteid
	 and    n_item  = n_nitem
	 and    periodo = '2021';
    
    UPDATE cab_cta_cte
    SET    total_deuda = n_saldo
    WHERE  id = n_cteid;
    
    
END LOOP ciclo1;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `recupero_act_scio` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` PROCEDURE `recupero_act_scio`(IN pn_socio  INT(10), IN pn_socpad INT(10),IN pn_socnew INT(10), IN pn_socpad_new INT(10))
BEGIN
     DECLARE done INT DEFAULT FALSE             ; 
     DECLARE n_tpsoc     INT(5)                 ;
     DECLARE n_act       INT(10)                ;
     DECLARE n_socio     INT(10)                ;
     DECLARE n_socpad    INT(10)                ;
     DECLARE cur1 CURSOR FOR
                     select id_act,id_tpsoc,id_soc,id_socpad
                     from act_valorizadas
                     where id_soc = pn_socio
                     and   id_socpad = pn_socpad;
         
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
     OPEN cur1;
          ciclo1 : LOOP 
            
              FETCH cur1 INTO n_act, n_tpsoc, n_socio, n_socpad;
            IF done THEN
               CLOSE cur1;
               LEAVE ciclo1;
            END IF;
            
            Insert Into act_valorizadas(id_act    ,
                                        id_tpsoc  ,
                                        id_soc    ,
                                        id_socpad )
                       VALUES (n_act, 
                               n_tpsoc, 
                               pn_socnew, 
                               pn_socpad_new);    
            
        END LOOP ciclo1;                         
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `recupero_socfliar` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` PROCEDURE `recupero_socfliar`(IN pn_socio  INT(10), IN pn_socpad_new INT(10))
BEGIN
     DECLARE done INT DEFAULT FALSE             ; 
     DECLARE v_apeynom   VARCHAR(60)            ;
     DECLARE v_calle     VARCHAR(40)            ;
     DECLARE v_altura    VARCHAR(10)            ;
     DECLARE v_dpto      VARCHAR(2)             ;
     DECLARE n_piso      INT(2)                 ;
     DECLARE n_loca      INT(5)                 ;
     DECLARE n_tpsoc     INT(5)                 ;
     DECLARE n_cob       INT(10)                ;
     DECLARE n_tp_doc    INT(5)                 ;
     DECLARE v_nrodoc    VARCHAR(14)            ;
     DECLARE d_fec_nac   DATE                   ;
     DECLARE v_email     VARCHAR(60)            ;
     DECLARE v_tel       VARCHAR(20)            ;
     DECLARE v_fax       VARCHAR(20)            ;
     DECLARE l_obs       LONGTEXT               ;
     DECLARE n_edad      INT(3)                 ;
     DECLARE n_prof      INT(10)                ;
     DECLARE v_obsocial  VARCHAR(50)            ;
     DECLARE n_socio     INT(10)                ;
     DECLARE n_cant_act  INT(10)                ;
     DECLARE nro_socio   INT(10)                ;
     DECLARE n_socpad    INT(10)                ;
     DECLARE cur1 CURSOR FOR
                     SELECT id      ,
                            id_tpdoc,
                            nro_doc,
                            apeynom,
                            calle,
                            altura,
                            departamento,
                            piso,
                            fecha_nac,
                            e_mail,
                            tel,
                            cel,
                            id_loca,
                            id_tpsoc,
                            edad,
                            ob_social,
                            id_socpad
                     FROM   soc_fliares       
                     WHERE  id_socpad = pn_socio;
         
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
     OPEN cur1;
          ciclo1 : LOOP 
            
              FETCH cur1 INTO nro_socio,n_tp_doc, v_nrodoc, v_apeynom, v_calle, v_altura, v_dpto, n_piso, d_fec_nac, v_email, 
                              v_tel, v_fax, n_loca, n_tpsoc, n_edad, v_obsocial,n_socpad;
            IF done THEN
               CLOSE cur1;
               LEAVE ciclo1;
            END IF;
            IF nro_socio != pn_socio THEN
               SELECT ifnull( max( id ) , 0 ) + 1 
               into n_socio
               FROM soc_fliares
	             WHERE id_socpad = pn_socio;
            
               Insert Into soc_fliares(id           ,
                                       id_tpdoc     ,
                                       nro_doc      ,
                                       apeynom      ,
                                       calle        ,
                                       altura       ,
                                       departamento ,
                                       piso         ,
                                       fecha_nac    ,
                                       e_mail       ,
                                       tel          ,
                                       cel          ,
                                       id_loca      ,
                                       id_tpsoc     ,
                                       edad         ,
                                       id_socpad    ,
                                       ob_social    )
                               VALUES (n_socio      ,
                                       n_tp_doc     ,
                                       v_nrodoc     , 
                                       v_apeynom    , 
                                       v_calle      , 
                                       v_altura     , 
                                       v_dpto       , 
                                       n_piso       , 
                                       d_fec_nac    , 
                                       v_email      , 
                                       v_tel        , 
                                       v_fax        , 
                                       n_loca       , 
                                       n_tpsoc      , 
                                       n_edad       , 
                                       pn_socpad_new,
                                       v_obsocial   );        
             SET n_cant_act = ob_act_socios(pn_socio);    
             IF  n_cant_act > 0 THEN
                 call recupero_act_scio(nro_socio,n_socpad, n_socio,pn_socpad_new);
             END IF;
        END IF;                               
                           
            
        END LOOP ciclo1;                         
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `recupero_socio` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` PROCEDURE `recupero_socio`(IN `pn_socio` INT(10), IN `pn_cobrador` INT(10), IN `pn_tpsoc` INT(5))
BLOCK1: BEGIN
     DECLARE d_fec_baja date        ;
	 
     set d_fec_baja = current_date();
     
	 UPDATE socios
	 SET fecha_baja = null
     WHERE  id = pn_socio
	 AND    id_cob = pn_cobrador;
	 
	 Insert Into act_valorizadas(id_act    ,
                                 id_tpsoc  ,
                                 id_soc    ,
                                 id_socpad )
                         VALUES (7       , 
                                 pn_tpsoc, 
                                 pn_socio, 
                                 pn_socio);    
	 BLOCK2: BEGIN
		  DECLARE done1 INT DEFAULT FALSE             ; 
		  DECLARE n_soc_fliar INT;				  
		  DECLARE cur2 CURSOR FOR
								SELECT id      
								FROM   soc_fliares       
								WHERE  id_socpad = pn_socio;

		  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;
		  OPEN cur2;
			  ciclo2 : LOOP 
			  FETCH cur2 INTO  n_soc_fliar;
			  IF done1 THEN
				 CLOSE cur2;
				 LEAVE ciclo2;
			  END IF;
			  UPDATE soc_fliares
			  SET fecha_baja = null
			  WHERE  id_socpad = pn_socio;
			  
			  Insert Into act_valorizadas(id_act    ,
                                 id_tpsoc  ,
                                 id_soc    ,
                                 id_socpad )
                         VALUES (7       , 
                                 pn_tpsoc, 
                                 n_soc_fliar, 
                                 pn_socio);   
		  END LOOP ciclo2;

     END BLOCK2;
     
END BLOCK1 ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `reversa_manual` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` PROCEDURE `reversa_manual`()
    NO SQL
BEGIN
DECLARE done INT DEFAULT FALSE             ;   
DECLARE n_socio     INT(10)                ;
DECLARE n_recibo    INT(10)        DEFAULT 0;
DECLARE n_total_liq DECIMAL(14,4) DEFAULT 0;

DECLARE cur1 CURSOR FOR
                     select soc_id, saldo
                     FROM lypcom_sportivo.det_cta_cte
WHERE soc_id in(230,260,288,367,415,456,491,600,674,802,                                 1126,1206,1210,1264,1284,1323,1371,1390,
                1533,1614,1646,1784,1785,1788,1814,2001,
                2008,422,1037,1352,2002)
AND  periodo = '2020'
AND  n_item  = 7;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
     OPEN cur1;
          ciclo1 : LOOP 
          FETCH cur1 INTO n_socio,n_total_liq;
          IF done THEN
             CLOSE cur1;
             LEAVE ciclo1;
          END IF; 
    UPDATE lypcom_sportivo.cab_cta_cte
    SET    total_deuda = n_total_liq
    WHERE  id = n_socio
    AND    soc_id = n_socio;
END LOOP ciclo1;
	
DELETE FROM recibos
    WHERE  soc_id = n_socio
	AND    cuota_nro = '08'
	AND    date_format(periodo,'%Y') = '2020';


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `reversa_socio` */;
DELIMITER ;;
CREATE DEFINER=`sportivo_user`@`localhost` PROCEDURE `reversa_socio`(IN `pn_idsoc` INT(10), IN `pd_periodo` DATE)
BEGIN
DECLARE done INT DEFAULT FALSE             ;   
DECLARE n_socio     INT(10)                ;
DECLARE n_recibo    INT(10)        DEFAULT 0;
DECLARE n_total_liq DECIMAL(14,4) DEFAULT 0;


     SELECT recibos.id,
										        recibos.soc_id,
										        total_liq
     INTO  n_recibo,
                     n_socio,
                     n_total_liq                      
										 FROM recibos
										 WHERE date_format(periodo , '%Y-%m') =  date_format(pd_periodo,'%Y-%m')
										 AND   recibos.soc_id = pn_idsoc;
     DELETE FROM det_cta_cte
     WHERE soc_id  = n_socio
     AND   id_rcbo = n_recibo;
    
    
    UPDATE cab_cta_cte
    SET    total_deuda = total_deuda - n_total_liq
    WHERE  id = n_socio
    AND    soc_id = n_socio;
    
    DELETE FROM recibos
    WHERE  id = n_recibo
    AND    soc_id = n_socio;



END ;;
DELIMITER ;

--
-- Final view structure for view `act_socios`
--

/*!50001 DROP VIEW IF EXISTS `act_socios`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`dba_user`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `act_socios` AS select 1 AS `SOCIO FLIAR.`,1 AS `id_act`,1 AS `id_soc`,1 AS `id_socpad`,1 AS `id`,1 AS `apeynom`,1 AS `id_tpsoc`,1 AS `importe` */;

--
-- Final view structure for view `v_act_socios`
--

/*!50001 DROP VIEW IF EXISTS `v_act_socios`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`sportivo_user`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v_act_socios` AS select 'TITULARES' AS `TITULARES`,NULL AS `NULL`,`socios`.`id` AS `soc_id`,`socios`.`apeynom` AS `apeynom`,`actividades`.`id` AS `id`,`actividades`.`actividad` AS `actividad`,`actividades`.`importe` AS `importe` from ((`socios` join `act_valorizadas`) join `actividades`) where ((`act_valorizadas`.`id_act` = `actividades`.`id`) and (`socios`.`id` = `act_valorizadas`.`id_soc`) and (`socios`.`id` = `act_valorizadas`.`id_socpad`)) union select 'FLIARES' AS `FLIARES`,`soc_fliares`.`id_socpad` AS `id_socpad`,`soc_fliares`.`id` AS `soc_fliar`,`soc_fliares`.`apeynom` AS `apeynom`,`actividades`.`id` AS `id`,`actividades`.`actividad` AS `actividad`,`actividades`.`importe` AS `importe` from ((`soc_fliares` join `act_valorizadas`) join `actividades`) where ((`act_valorizadas`.`id_act` = `actividades`.`id`) and (`soc_fliares`.`id` = `act_valorizadas`.`id_soc`) and (`soc_fliares`.`id_socpad` = `act_valorizadas`.`id_socpad`)) */;

--
-- Final view structure for view `v_cab_cta_cte`
--

/*!50001 DROP VIEW IF EXISTS `v_cab_cta_cte`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`sportivo_user`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v_cab_cta_cte` AS select `socios`.`id` AS `nro_socio`,`socios`.`apeynom` AS `nombre_apellido`,`socios`.`calle` AS `calle`,`socios`.`altura` AS `altura`,`socios`.`departamento` AS `departamento`,`socios`.`piso` AS `piso`,`socios`.`id_loca` AS `id_loca`,`socios`.`id_tpsoc` AS `id_tpsoc`,`socios`.`id_cob` AS `id_cob`,`tipos_socios`.`importe` AS `importe`,`cab_cta_cte`.`total_deuda` AS `total_deuda`,`socios`.`fecha_baja` AS `fecha_baja` from ((`socios` join `tipos_socios` on((`socios`.`id_tpsoc` = `tipos_socios`.`id`))) join `cab_cta_cte` on((`cab_cta_cte`.`soc_id` = `socios`.`id`))) where (`socios`.`fecha_baja` is null) */;

--
-- Final view structure for view `v_estado_ctacte`
--

/*!50001 DROP VIEW IF EXISTS `v_estado_ctacte`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`sportivo_user`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v_estado_ctacte` AS select `recibos`.`id_cob` AS `id_cob`,`det_cta_cte`.`soc_id` AS `soc_id`,sum(`det_cta_cte`.`haber`) AS `haber`,sum(`det_cta_cte`.`saldo`) AS `saldo`,sum(`det_cta_cte`.`debe`) AS `debe` from (`det_cta_cte` join `recibos` on(((`det_cta_cte`.`soc_id` = `recibos`.`soc_id`) and (`det_cta_cte`.`n_item` = `recibos`.`cuota_nro`) and (`det_cta_cte`.`ent_id` = `recibos`.`ent_id`)))) group by `recibos`.`id_cob`,`det_cta_cte`.`soc_id` */;

--
-- Final view structure for view `v_logs_procesos`
--

/*!50001 DROP VIEW IF EXISTS `v_logs_procesos`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`sportivo_user`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v_logs_procesos` AS select `codigos_errores`.`id` AS `id`,`codigos_errores`.`tp_err` AS `tp_err`,`codigos_errores`.`descripcion` AS `texto_error`,`log_procesos`.`nomproceso` AS `nomproceso`,`log_procesos`.`fecha_proceso` AS `fecha_proceso`,`log_procesos`.`id_estado` AS `id_estado`,`log_procesos`.`descripcion` AS `descripcion` from (`log_procesos` join `codigos_errores` on((`log_procesos`.`id_estado` = `codigos_errores`.`id`))) */;

--
-- Final view structure for view `v_padron`
--

/*!50001 DROP VIEW IF EXISTS `v_padron`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`sportivo_user`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v_padron` AS select `socios`.`id` AS `id`,`socios`.`apeynom` AS `apeynom`,`socios`.`calle` AS `calle`,`socios`.`altura` AS `altura`,`socios`.`id_tpsoc` AS `id_tpsoc`,`soc_fliares`.`id` AS `soc_fliar`,`soc_fliares`.`apeynom` AS `apeynom_fliar`,`act_valorizadas`.`id_act` AS `id_act`,`actividades`.`actividad` AS `actividad`,`actividades`.`importe` AS `importe` from ((`socios` left join (`soc_fliares` join `act_valorizadas` on(((`soc_fliares`.`id_socpad` = `act_valorizadas`.`id_socpad`) and (`soc_fliares`.`id` = `act_valorizadas`.`id_soc`)))) on((`soc_fliares`.`id_socpad` = `socios`.`id`))) join `actividades` on((`actividades`.`id` = `act_valorizadas`.`id_act`))) where ((`socios`.`id_tpsoc` in (1,3,5,6)) and (`socios`.`fecha_baja` is null) and (`act_valorizadas`.`id_act` not in (7,8))) */;

--
-- Final view structure for view `v_socios`
--

/*!50001 DROP VIEW IF EXISTS `v_socios`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`sportivo_user`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v_socios` AS select `socios`.`id` AS `id`,`socios`.`apeynom` AS `apeynom`,`socios`.`razon_social` AS `razon_social`,substr(`socios`.`calle`,1,2) AS `calle`,`socios`.`calle` AS `domicilio`,`socios`.`altura` AS `altura`,`socios`.`departamento` AS `departamento`,`socios`.`piso` AS `piso`,`socios`.`id_tpdoc` AS `id_tpdoc`,`socios`.`nro_doc` AS `nro_doc`,`socios`.`fecha_nac` AS `fecha_nac`,`socios`.`e_mail` AS `e_mail`,`socios`.`tel` AS `tel`,`socios`.`tel_fax` AS `tel_fax`,`socios`.`observaciones` AS `observaciones`,`socios`.`id_loca` AS `id_loca`,`socios`.`ent_id` AS `ent_id`,`socios`.`fecha_inicio` AS `fecha_inicio`,`socios`.`fecha_baja` AS `fecha_baja`,`socios`.`id_tpsoc` AS `id_tpsoc`,`socios`.`edad` AS `edad` from `socios` */;


