dbo.LN_oportunidades

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

/*
-- =============================================
-- Author: María Inés Soria
-- Create date: 04/01/2021
-- Description: Card en riesgo - Indicador 
-- =============================================

-- Si no tiene nada defino en dias_alertas para tabla like 'riesgo%', se calcula tomando todos los defaults
-- Si lo tiene se toma los datos definidos para el cliente
-- Si tiene cosas en dias_alertas, pero no especificamente para alguno de los items --> no lo va a tomar en cuenta

OPORTUNIDADES
Contrapartes que cumpla con cualquiera y al menos una de estas condiciones, 
pero ÚNICAMENTE si el Score Informe Comercial > 40.

Rentabilidad sobre Ventas > 10%, Y, Rentabilidad Operativa > 15%, Y Dependencia Económica > 40%
Liquidez Corriente >= 2, Y Rentabilidad sobre Ventas >= 4%, Y Solvencia >= 0,8
Dependencia Económica >= 50%, Y Rentabilidad sobre Ventas >= 12%
Liquidez Corriente <= 1,1, Y Rentabilidad sobre Ventas >= 9%

DECLARE @oportunidades bit 
DECLARE @porque varchar(max) 
EXEC [dbo].[LN_oportunidades] 107, @oportunidades output, @porque output 
select @oportunidades as valor, @porque as porque
*/

CREATE OR ALTER PROCEDURE dbo.LN_oportunidades 
  @contraparte int,
  @oportunidades1 bit = null output, @porque1 varchar(max) output 
AS
BEGIN
 SET NOCOUNT ON;
 declare @cliente int = null
 set @cliente = (select cliente from ln_contrapartes where contraparte = @contraparte)
 
 declare @oportunidades bit = 0
 declare @porque varchar(max) = '' 

 declare @gestion int = (select gestion 
       from LN_gestiones 
       where contraparte = @contraparte 
         AND LN_gestiones.desde <= GETDATE()
       AND LN_gestiones.hasta >= GETDATE()
       AND LN_gestiones.vigente = 1)

 declare @etiqueta varchar(100) = null
 
 declare @ind_oportunidades  int = (select indicador from ln_indicadores where clave_indicador = 'OPORTINIDADES')
 
 /* Podemos tener definidos los valores ln_dias_alerta, si hay los tomamos de ahi, sino usamos los defaults */
 
 declare @p_1_valorrenventas decimal(10,2) 
 declare @p_1_valorrenope   decimal(10,2) 
 declare @p_1_valordepecon  decimal(10,2) 

 declare @p_2_valorliquidez  decimal(10,2) 
 declare @p_2_valorrenventas  decimal(10,2) 
 declare @p_2_valorsolvencia decimal(10,2) 

 declare @p_3_valordepecon   decimal(10,2) 
 declare @p_3_valorrenventas  decimal(10,2) 

 declare @p_4_valorrenventas  decimal(10,2)
 declare @p_4_valorliquidez  decimal(10,2)

 declare @p_valoric   decimal(10,2) 
 
 declare @tiene int = (select COUNT(*) from ln_dias_alertas where tabla like 'oporunidades_%' and cliente = @Cliente)

 if (@tiene = 0) -- Toma los defaults 
 begin 
 
  set @p_1_valorrenventas  = 10
  set @p_1_valorrenope     = 15
  set @p_1_valordepecon    = 40

  set @p_2_valorliquidez   = 2 
  set @p_2_valorrenventas  = 4 
  set @p_2_valorsolvencia  = 0.8

  set @p_3_valordepecon    = 50
  set @p_3_valorrenventas  = 12

  set @p_4_valorrenventas  = 9
  set @p_4_valorliquidez   = 1.1 
  
 end 
 
 if exists (select * from ln_dias_alertas where tabla = 'oportunidades_score_ic' and cliente = @Cliente)
  select @p_valoric = valor from ln_dias_alertas where tabla = 'oportunidades_score_ic' and cliente = @Cliente
 else 
  set @p_valoric = 40


 -- ÚNICAMENTE si el Score Informe Comercial > 40 (o el valor definido en la tabla) 
 declare @p_score_ic decimal(10,2) = null
 select @p_score_ic = valor_numerico 
    from ln_contraparte_indicadores 
    where contraparte = @contraparte and gestion = @gestion
    and visible = 1 
    and indicador in (select indicador from ln_indicadores where clave_indicador = 'SCORE_IC') 
 -- Si no es termina acá
 if (@p_score_ic is null or @p_score_ic <= @p_valoric) 
 begin
  set @oportunidades1 = 0
  set @porque1 = 'Score IC no es mayor a ' +  convert(varchar,@p_valoric)
  return
 end 

 set @porque = 'Score IC es mayor a ' +  convert(varchar,@p_valoric)
   
/*----------------------------------------------------------
1 - Rentabilidad sobre Ventas > 10%, Y, Rentabilidad Operativa > 15%, Y Dependencia Económica > 40%
----------------------------------------------------------*/ 
-- Vemos si tiene cosas definidas en ln_dias_alerta:
declare @p_rentabilidad_ventas    decimal(10,2)  
declare @p_rentabilidad_operativa decimal(10,2)
declare @p_dependencia_economica  decimal(10,2)  

if exists (select * from ln_dias_alertas where tabla = 'oportunidades_1_rentablidad_ventas' and cliente = @Cliente)
begin
 select @p_1_valorrenventas = valor 
   from ln_dias_alertas where tabla = 'oportunidades_1_rentablidad_ventas' and cliente = @Cliente
end 

if (@tiene = 0 or exists (select * from ln_dias_alertas where tabla = 'oportunidades_1_rentablidad_ventas' and cliente = @Cliente))
begin
 select @p_rentabilidad_ventas = valor_numerico 
    from ln_contraparte_indicadores 
    where contraparte = @contraparte and gestion = @gestion
    and visible = 1 
    and indicador in (select indicador from ln_indicadores where clave_indicador like '%RENTABILIDAD_VENTAS%')  
end
else
begin
 set @p_rentabilidad_ventas = -999999
end 

if exists (select * from ln_dias_alertas where tabla = 'oportunidades_1_rentablidad_operativa' and cliente = @Cliente)
begin 

 select @p_1_valorrenope = valor 
   from ln_dias_alertas where tabla = 'oportunidades_1_rentablidad_operativa' and cliente = @Cliente
end 

if (@tiene = 0 or exists (select * from ln_dias_alertas where tabla = 'oportunidades_1_rentablidad_operativa' and cliente = @Cliente))
begin

 select @p_rentabilidad_operativa = valor_numerico 
    from ln_contraparte_indicadores 
    where contraparte = @contraparte and gestion = @gestion
    and visible = 1 
    and indicador in (select indicador from ln_indicadores where clave_indicador like '%RENTABILIDAD_OPERATIVA%') 
end
else 
begin 
 set @p_rentabilidad_operativa = -999999
end 

--select @p_rentabilidad_operativa as rentabilidad_operativa


if exists (select * from ln_dias_alertas where tabla = 'oportunidades_1_dependencia_economica' and cliente = @Cliente)
begin 

 select @p_1_valordepecon = valor 
   from ln_dias_alertas where tabla = 'oportunidades_1_dependencia_economica' and cliente = @Cliente
end 

if (@tiene = 0 or exists (select * from ln_dias_alertas where tabla = 'oportunidades_1_dependencia_economica' and cliente = @Cliente))
begin

 select @p_dependencia_economica = valor_numerico 
    from ln_contraparte_indicadores 
    where contraparte = @contraparte and gestion = @gestion
    and visible = 1 
    and indicador in (select indicador from ln_indicadores where clave_indicador in('DEP_ECON_CALC','DEP_ECON_BALANCE'))
end
else 
begin 
 set @p_dependencia_economica = -999999
end 

--select @p_dependencia_economica as dependencia_economica
   
if (@p_rentabilidad_ventas    > @p_1_valorrenventas and
    @p_rentabilidad_operativa > @p_1_valorrenope and 
 @p_dependencia_economica  > @p_1_valordepecon) 
begin 
 set @oportunidades = 1
 if (@porque != '') set @porque = @porque + '; '
 set @porque = @porque + 'Condición 1 - Rentabilidad sobre Ventas > ' + convert(varchar,@p_1_valorrenventas) + 
               ', Y, Rentabilidad Operativa > '  + convert(varchar,@p_1_valorrenope) + 
      ', Y Dependencia Económica > '+ convert(varchar,@p_1_valordepecon) 
 --return  
end 

/*----------------------------------------------------------
2 - Liquidez corriente >= 2, Y Rentabilidad ventas >= 4%, Y Solvencia >= 0,8
----------------------------------------------------------*/ 
-- Vemos si tiene cosas definidas en ln_dias_alerta:
declare @p_solvencia decimal(10,2)
declare @p_liquidez  decimal(10,2)  

if exists (select * from ln_dias_alertas where tabla = 'oportunidades_2_liquidez_corriente' and cliente = @Cliente)
begin
 select @p_2_valorliquidez = valor 
   from ln_dias_alertas where tabla = 'oportunidades_2_liquidez_corriente' and cliente = @Cliente
end 

if (@tiene = 0 or exists (select * from ln_dias_alertas where tabla = 'oportunidades_2_liquidez_corriente' and cliente = @Cliente))
begin
 select @p_liquidez = valor_numerico 
    from ln_contraparte_indicadores 
    where contraparte = @contraparte and gestion = @gestion
    and visible = 1 
    and indicador in (select indicador from ln_indicadores where clave_indicador like '%LIQUIDEZ_CORRIENTE%') 
end
else
begin
 set @p_liquidez = -999999
end 

--select @p_liquidez as liquidez

if exists (select * from ln_dias_alertas where tabla = 'oportunidades_2_rentablidad_ventas' and cliente = @Cliente)
begin 

 select @p_2_valorrenventas = valor 
   from ln_dias_alertas where tabla = 'oportunidades_2_rentablidad_ventas' and cliente = @Cliente
end 

if (@tiene = 0 or exists (select * from ln_dias_alertas where tabla = 'oportunidades_2_rentablidad_ventas' and cliente = @Cliente))
begin

 select @p_rentabilidad_ventas = valor_numerico 
    from ln_contraparte_indicadores 
    where contraparte = @contraparte and gestion = @gestion
    and visible = 1 
    and indicador in (select indicador from ln_indicadores where clave_indicador like '%RENTABILIDAD_VENTAS%') 
end
else 
begin 
 set @p_rentabilidad_ventas = -999999
end 

--select @p_rentabilidad_ventas as rentabilidad_ventas

if exists (select * from ln_dias_alertas where tabla = 'oportunidades_2_solvencia' and cliente = @Cliente)
begin 

 select @p_2_valorsolvencia = valor 
   from ln_dias_alertas where tabla = 'oportunidades_2_solvencia' and cliente = @Cliente
end 

if (@tiene = 0 or exists (select * from ln_dias_alertas where tabla = 'oportunidades_2_solvencia' and cliente = @Cliente))
begin

 select @p_solvencia = valor_numerico 
    from ln_contraparte_indicadores 
    where contraparte = @contraparte and gestion = @gestion
    and visible = 1 
    and indicador in (select indicador from ln_indicadores where clave_indicador like '%SOLVENCIA%')
end
else 
begin 
 set @p_solvencia = -999999
end 
   
--select @p_solvencia as solvencia
  
if (@p_liquidez            >= @p_2_valorliquidez and 
 @p_rentabilidad_ventas >= @p_2_valorrenventas and 
 @p_solvencia           >= @p_2_valorsolvencia) 
begin 
 set @oportunidades = 1
 if (@porque != '') set @porque = @porque + '; '
 set @porque = @porque + 'Condición 2 - Liquidez corriente >= ' + convert(varchar,@p_2_valorliquidez) + 
               ', Y Rentabilidad sobre ventas >= ' + convert(varchar,@p_2_valorrenventas) + 
      ', Y Solvencia >= ' + convert(varchar,@p_2_valorsolvencia) 
 --return  
end 

--select '3' as paso

/*----------------------------------------------------------
3 - Dependencia Económica >= 50%, Y Rentabilidad Final >= 12%
----------------------------------------------------------*/ 
-- Vemos si tiene cosas definidas en ln_dias_alerta:

if exists (select * from ln_dias_alertas where tabla = 'oportunidades_3_dependencia_economica' and cliente = @Cliente)
begin 

 select @p_3_valordepecon = valor 
   from ln_dias_alertas where tabla = 'oportunidades_3_dependencia_economica' and cliente = @Cliente
end 

if (@tiene = 0 or exists (select * from ln_dias_alertas where tabla = 'oportunidades_3_dependencia_economica' and cliente = @Cliente))
begin

 select @p_dependencia_economica = valor_numerico 
    from ln_contraparte_indicadores 
    where contraparte = @contraparte and gestion = @gestion
    and visible = 1 
    and indicador in (select indicador from ln_indicadores where clave_indicador in('DEP_ECON_CALC','DEP_ECON_BALANCE'))
end
else 
begin 
 set @p_dependencia_economica = -999999
end 
  
--select @p_dependencia_economica as dependencia_economica

if exists (select * from ln_dias_alertas where tabla = 'oportunidades_3_rentablidad_ventas' and cliente = @Cliente)
begin 

 select @p_3_valorrenventas = valor 
   from ln_dias_alertas where tabla = 'oportunidades_3_rentablidad_ventas' and cliente = @Cliente
end 

if (@tiene = 0 or exists (select * from ln_dias_alertas where tabla = 'oportunidades_3_rentablidad_ventas' and cliente = @Cliente))
begin

 select @p_rentabilidad_ventas = valor_numerico 
    from ln_contraparte_indicadores 
    where contraparte = @contraparte and gestion = @gestion
    and visible = 1 
    and indicador in (select indicador from ln_indicadores where clave_indicador like '%RENTABILIDAD_VENTAS%') 
end
else 
begin 
 set @p_rentabilidad_ventas = -999999
end 
--select @p_rentabilidad_ventas as rentabilidad_ventas

if (@p_dependencia_economica >= @p_3_valordepecon and 
 @p_rentabilidad_ventas   >= @p_3_valorrenventas) 
begin 
 set @oportunidades = 1
 if (@porque != '') set @porque = @porque + '; '
 set @porque = @porque + 'Condición 3 - Dependencia Económica >= '+ convert(varchar,@p_3_valordepecon) + 
               ', Y Rentabilidad Final >= ' + convert(varchar,@p_3_valorrenventas) 
 --return  
end 

--select '4' as paso

/*----------------------------------------------------------
4 - Liquidez <= 1,1, Y Rentabilidad >= 9%
----------------------------------------------------------*/ 
-- Vemos si tiene cosas definidas en ln_dias_alerta:

if exists (select * from ln_dias_alertas where tabla = 'oportunidades_4_liquidez_corriente' and cliente = @Cliente)
begin
 select @p_4_valorliquidez = valor 
   from ln_dias_alertas where tabla = 'oportunidades_4_liquidez_corriente' and cliente = @Cliente
end 

if (@tiene = 0 or exists (select * from ln_dias_alertas where tabla = 'oportunidades_4_liquidez_corriente' and cliente = @Cliente))
begin
 select @p_liquidez = valor_numerico 
    from ln_contraparte_indicadores 
    where contraparte = @contraparte and gestion = @gestion
    and visible = 1 
    and indicador in (select indicador from ln_indicadores where clave_indicador like '%LIQUIDEZ_CORRIENTE%') 
end
else
begin
 set @p_liquidez = 999999
end 

--select @p_liquidez as liquidez

if exists (select * from ln_dias_alertas where tabla = 'oportunidades_4_rentablidad_ventas' and cliente = @Cliente)
begin 

 select @p_4_valorrenventas = valor 
   from ln_dias_alertas where tabla = 'oportunidades_4_rentablidad_ventas' and cliente = @Cliente
end 

if (@tiene = 0 or exists (select * from ln_dias_alertas where tabla = 'oportunidades_4_rentablidad_ventas' and cliente = @Cliente))
begin

 select @p_rentabilidad_ventas = valor_numerico 
    from ln_contraparte_indicadores 
    where contraparte = @contraparte and gestion = @gestion
    and visible = 1 
    and indicador in (select indicador from ln_indicadores where clave_indicador like '%RENTABILIDAD_VENTAS%') 
end
else 
begin 
 set @p_rentabilidad_ventas = -999999
end 

--select @p_rentabilidad_ventas as  rentabilidad_ventas

if (@p_liquidez            <= @p_4_valorliquidez and 
 @p_rentabilidad_ventas >= @p_4_valorrenventas) 
begin 
 set @oportunidades = 1
 if (@porque != '') set @porque = @porque + '; '
 set @porque = @porque + 'Condición 4 - Liquidez <= ' + convert(varchar,@p_4_valorliquidez) + 
               ' Y Rentabilidad >= ' + convert(varchar,@p_4_valorrenventas) 
 --return  
end 

if (@oportunidades = 0) 
begin
 if (@porque != '') set @porque = @porque + ' Y '
 set @porque = @porque + 'no cumple ninguna de las condiciones' 
end 
set @oportunidades1 = @oportunidades
set @porque1 = @porque

END

GO