/**************************************************************************************
****** - Cálculo de Comisiones Bancarias Incorrectas. *********************************
*************   - Corrección con interes pactado. - ***********************************
***************************************************************************************
*** Desarrollado por: Carlos Briso (c) 2014 *******************************************
***************************************************************************************
* Para obtener el detalle de la infraestructura y despliegue de BB. de DD. : **********
** --> Tinkering around Linux - www.cadilinea.com/blog | www.BuyOne.es <--*************
*** Puede utilizar este software, modificarlo, o compartirlo **************************
*** Rogamos unicamente que cite al autor. *********************************************
********************  Versión: LIO 1.2 Estable **** 2014-02-22 ************************/

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_cal_interes_pactado`(v_fecha date)
pricipal: begin
	-- ********************************************************
	-- Declaración de Variables
	declare v_fecha_cal 					date;	-- Fecha de Cálculo.
	-- Fechas de Apuntes.
	declare v_fecha_cargo 					date;
	declare v_fecha_valor 					date;
	declare v_concepto						varchar(55);
	declare v_comision						decimal(10,2);	-- Monto de la Comisión.
	declare v_num_reg_apunte		 		int default 1;	-- Control del Id_apunte del Bucle.
	declare v_num_reg_apunte_anterior		int default 0;
	declare v_num_total_reg_apunte			int default 0;	-- Total Registros bd_apunte.
	declare v_num_reg_interes_pactado		 	int default 1;
	declare v_num_total_reg_interes_pactado	int default 0;	-- Total Registros bd_interes_pactado.
	-- De Control del Período.
	declare v_per_num						int default 1;
	declare	v_per_desc						varchar(55) default 'Periodo-';
	-- De cálculo de Fechas.
	declare v_fecha_per_ini					date;
	declare v_fecha_per_fin					date;	
	-- Controlar Sub-Períodos de Interés y interes con diferentes tipos.
	declare v_num_total_per					int default 1;
	declare v_num_per						int default 1;
	declare v_num_total_subper				int default 1;
	declare v_num_subper					int default 1;
	-- Variables interes_pactado y interes para bd_detalle, y bd_apunte.
	declare	v_dias							int default 0;
	declare v_interes_pactado					decimal(5,2) default 0.00;	-- Porcentaje.
	declare v_interes						decimal(5,2) default 0.00;	-- Porcentaje.
	declare v_total_interes_pactado			decimal(10,2) default 0.00;	-- Importe.
	declare v_total_interes					decimal(10,2) default 0.00;	-- Importe.
	declare v_Acumulado_interes_pactado		decimal(10,2) default 0.00;	-- Importe de Interés por Registro bd_apunte.
	declare v_Acumulado_interes				decimal(10,2) default 0.00;	-- Importe de interes por Registro bd_apunte.

	-- ********************************************************
-- ***********************************************************************************************************
-- Asignación de variables.
-- Asignamos Variables de Calculo por Fecha Valor.
set v_fecha_cal		=		v_fecha;
set v_fecha_valor	=	(select min(fecha_valor) from bd_apunte);		-- Fecha de Calculo Minima.
-- Comprobamos Existencia de Registros.
set v_num_total_reg_apunte 	= (SELECT count(Id_apunte) FROM bd_apunte);	-- Total Registros bd_apunte.
set v_num_total_reg_interes_pactado 	= (SELECT count(Id_interes_pactado) 	-- Total Registros bd_interes_pactado.
	from bd_interes_pactado);													

if v_num_total_reg_apunte <=0 or v_num_total_reg_interes_pactado <= 0		-- Error Inexistencia de cualquier B. de Datos.
	or v_fecha_cal <= v_fecha_valor										-- Fecha de Calculo Minima.
	then
	select '' '** PROBLEMA ** *************************************************' ;
	select '     ' 'Registros bd_apunte --> ',v_num_total_reg_apunte '*****'; 
	select '     ' 'Registros bd_interes_pactado --> ',v_num_total_reg_interes_pactado '*****';
	select '     ' 'Fecha de Calculo Minima --> ',v_fecha_valor '**********';
else
	#select 'Ok: Registros bd_apunte --> ', v_num_total_reg_apunte, ' Registros bd_interes_pactado --> ', 
	#v_num_total_reg_interes_pactado;
     
truncate  bd_detalle  ; -- Eliminamos Registros de la Tabla de Detalle.
#------------------------------------------------------------------------------------------
Seleccion_Registros: repeat 	-- (v_num_reg_apunte > v_num_total_reg_apunte)

set v_fecha_cargo 	= (select apunte.fecha_cargo 	from bd_apunte apunte limit v_num_reg_apunte_anterior,1);

set v_fecha_valor 	= (select apunte.fecha_valor 	from bd_apunte apunte limit v_num_reg_apunte_anterior,1);

set v_comision 		= (select apunte.comision 		from bd_apunte apunte limit v_num_reg_apunte_anterior,1);

#----------------------------------------------------------
-- Preparamos fechas para rutina de Periodos.
set v_fecha_per_ini = v_fecha_valor;
set v_fecha_per_fin = concat(year(v_fecha_per_ini),'-','12','-','31')  ;	

-- No podemos superar la Fecha de Cálculo.
if v_fecha_per_fin >= v_fecha_cal then
		set v_fecha_per_fin = v_fecha_cal;
end if;
		
set v_num_total_per	= (year(v_fecha_cal) - year(v_fecha_valor)) + 1;	-- Total Periodos.

	Periodos: repeat		-- (v_num_per > v_num_total_per)
	
		-- Comprobamos si Existen Sub-Períodos dentro del Periodo..
		set v_num_total_subper = (select count(*) from bd_interes_pactado where 
			v_fecha_per_ini <= fecha_fin and year(v_fecha_per_ini) = year(fecha_fin) );

		set v_fecha_per_fin = (select interes_pactado.fecha_fin from bd_interes_pactado interes_pactado where 
			v_fecha_per_ini between interes_pactado.fecha_inicio and interes_pactado.fecha_fin);

		if v_fecha_per_fin >= v_fecha_cal then		-- No superar Fecha de Calculo.
			set v_fecha_per_fin = v_fecha_cal ;
		end if ;

		set v_concepto = concat('Periodo: ',lpad(v_num_per,2,0), ' de ', lpad(v_num_total_per,2,0), 
			' ** ',' Sub-Periodo: ',lpad(v_num_subper,2,0), ' de ',lpad(v_num_total_subper,2,0));
		#---------------------------------------------------------------------
		SubPeriodos: repeat		-- (v_num_subper > v_num_total_subper)
		if v_num_per >= v_num_total_per then
			set v_dias = datediff(v_fecha_per_fin, v_fecha_per_ini) ; -- No se considera el dia de cálculo. 
		else
			set v_dias = datediff(v_fecha_per_fin, v_fecha_per_ini) + 1;
		end if;

		-- Cálculo de interes_pactado. Efectuamos un Cast para evitar un Warnig de Datos Truncados.
		set v_interes_pactado = (select interes_pactado.interes_pactado from bd_interes_pactado interes_pactado 
			where v_fecha_per_ini between interes_pactado.fecha_inicio and interes_pactado.fecha_fin);

		set v_total_interes_pactado = cast(((v_interes_pactado/100) * v_comision * v_dias /365) as decimal(10,2))  ;

		-- Inserción de Registros en bd_detalle.
		insert into bd_detalle
		(apunte,fecha_cargo, fecha_valor, per_ini, per_fin, dias, comision, concepto, interes,  total_interes)
		values
		(v_num_reg_apunte,v_fecha_cargo, v_fecha_valor, v_fecha_per_ini, v_fecha_per_fin, v_dias, 
		v_comision, v_concepto, v_interes_pactado, v_total_interes_pactado);
		
		-- Acumulados de interes_pactado y interes.
		set v_Acumulado_interes_pactado = v_Acumulado_interes_pactado + v_total_interes_pactado;
	
		-- No podemos superar la Fecha de Cálculo.
		if v_fecha_per_fin >= v_fecha_cal then
			set v_fecha_per_fin = v_fecha_cal ;
		end if ;

		set v_num_subper 		= v_num_subper + 1;

		set v_concepto = concat('Periodo: ',lpad(v_num_per,2,0), ' de ', lpad(v_num_total_per,2,0), 
						' ** ',' Sub-Periodo: ',lpad(v_num_subper,2,0), ' de ',lpad(v_num_total_subper,2,0));
		set v_fecha_per_ini = timestampadd(day, 1, v_fecha_per_fin);
		set v_fecha_per_fin = (select interes_pactado.fecha_fin from bd_interes_pactado interes_pactado where 
						v_fecha_per_ini between interes_pactado.fecha_inicio and interes_pactado.fecha_fin);
		-- No podemos superar la Fecha de Cálculo.
		if v_fecha_per_fin >= v_fecha_cal then
			set v_fecha_per_fin = v_fecha_cal ;
			set v_num_subper 	= v_num_subper + 999; -- Rompemos el Bucle.
		end if ; 
		
		until v_num_subper > v_num_total_subper end repeat SubPeriodos;
		#---------------------------------------------------------------------		
		set v_num_subper 		= 1;
		set v_num_total_subper 	= 1;
	
	-- Establecemos Períodos de Inicio y Fin.
	set v_fecha_per_ini = concat(year(v_fecha_per_ini),'-','01','-','01');
	set v_fecha_per_fin = (select interes_pactado.fecha_fin from bd_interes_pactado interes_pactado where 
						v_fecha_per_ini between interes_pactado.fecha_inicio and interes_pactado.fecha_fin);
	-- No podemos superar la Fecha de Cálculo.
	if v_fecha_per_fin >= v_fecha_cal then
			set v_fecha_per_fin = v_fecha_cal ;
	end if ; 

	set v_num_per = v_num_per + 1;
	until v_num_per > v_num_total_per end repeat Periodos;
	
	set v_num_per 		= 1;
	set v_num_total_per = 1;
	#----------------------------------------------------------

-- Actualizamos totales en la Tabla bd_apunte.
update bd_apunte apunte set apunte.total_interes=v_Acumulado_interes_pactado
	 where Id_apunte = v_num_reg_apunte;
-- Reseteamos Acumulados para el siguiente Registro.
set v_Acumulado_interes_pactado 	= 0;
set v_num_reg_apunte 			= v_num_reg_apunte + 1;
set v_num_reg_apunte_anterior 	= v_num_reg_apunte_anterior + 1;

until v_num_reg_apunte > v_num_total_reg_apunte  end repeat Seleccion_Registros;
#------------------------------------------------------------------------------------------
-- Resultados Finales y Formateo de Salida -------------------------------------------------------------------------
select 	lpad(apunte,5,0) "Apunte",per_ini "Per. Inicio", per_fin "Per. Fin", dias "Días", 
		lpad(format(comision,2,'de_DE'),10," ") Comisión, concepto "Concepto", 
		concat(lpad(interes,5," "), " %") "% Interés",
		concat(lpad(format(total_interes,2,'de_DE'),10," ")," €") "interes pacto" from bd_detalle;
select 	concat("Apunte: ", lpad(Id_apunte,5,0)) "*************",Fecha_cargo "Fecha Cargo", Fecha_valor "Fecha Valor", 
		concat(lpad(format(comision,2,'de_DE'),10," ")," €") Comisión, concepto Concepto, 
		concat(lpad(format(total_interes,2,'de_DE'),10," ")," €") "Total interes" from bd_apunte;

select 	current_date() "Fecha de Cálculo", 
		concat(lpad(format(sum(comision),2,'de_DE'),10," ")," €") "Total Comisiones", 
		concat(lpad(format(sum(total_interes),2,'de_DE'),10," "), " €") "Total interes pactado", 
		concat(lpad(format(sum(comision)+sum(total_interes),2,'de_DE'),10," ")," €") "***** TOTAL *****" 
		from bd_apunte;
#------------------------------------------------------------------------------------------------------------------
end if ;	-- Error Inexistencia de cualquier B. de Datos.
end pricipal$$
DELIMITER ;