dbo.LN_subfamilias_

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE OR ALTER PROCEDURE dbo.LN_subfamilias_ 
 -- Add the parameters for the stored procedure here

 @accion          varchar(30) = null,
 @p_familia         int = null,
 @p_familia_padre       int = null,
 @p_cliente         int = null
 
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here

 if (@accion = 'insert') begin

  insert into LN_subfamilias (familia, familia_padre) 
     values (@p_familia, @p_familia_padre)

 end else

  if (@accion = 'delete') begin

   delete from LN_subfamilias
   where familia = @p_familia
   and familia_padre = @p_familia_padre

  end else

   if (@accion = 'parents') begin

    /* obtener las familias de orden principal de las cuales depende la 
     subfamilia pasada en el parámetro @p_famiila */

    select clientes.cliente, clientes.nombre_cliente, 
      LN_familias.familia, LN_familias.nombre_familia,
      LN_familias.visible, country.country_code, country.country_name
    from LN_subfamilias 
    inner join LN_familias on LN_subfamilias.familia_padre = LN_familias.familia
    inner join country on LN_familias.country_code = country.country_code
    inner join clientes on LN_familias.cliente = clientes.cliente
    where LN_subfamilias.familia = @p_familia
    order by LN_familias.nombre_familia

   end else

    if (@accion = 'children') begin

     /* obtener las subfamilias dependientes de una familia principal
      pasada en el parámetro @p_familia */

     select clientes.cliente, clientes.nombre_cliente, 
       LN_familias.familia, LN_familias.nombre_familia, LN_familias.set_doc,
       LN_familias.visible, country.country_code, country.country_name,
       LN_set_docs.nombre_set
     from LN_subfamilias
     inner join LN_familias on LN_subfamilias.familia = LN_familias.familia
     inner join country on LN_familias.country_code = country.country_code
     inner join clientes on LN_familias.cliente = clientes.cliente
     left outer join LN_set_docs on LN_familias.set_doc = LN_set_docs.set_doc
     where LN_subfamilias.familia_padre = @p_familia_padre
     order by LN_familias.nombre_familia

    end else

     if (@accion = 'cliente') begin

      /* tener en cuenta que esta consulta multiplica registros de la subfamilia por cada familia padre asociada */

      select clientes.cliente, clientes.nombre_cliente, 
        LN_familias.familia, LN_familias.nombre_familia, LN_familias.subfamilia,
        LN_familias.visible, country.country_code, country.country_name,
        LN_subfamilias.familia_padre, parent.nombre_familia as nombre_familia_padre,
        LN_familias.set_doc, LN_set_docs.nombre_set, contrap_fam.total_contrapartes
      from LN_familias inner join clientes on LN_familias.cliente = clientes.cliente
      inner join country on LN_familias.country_code = country.country_code
      left outer join LN_subfamilias on LN_familias.familia = LN_subfamilias.familia
      left outer join LN_familias parent on LN_subfamilias.familia_padre = parent.familia
      left outer join LN_set_docs on LN_familias.set_doc = LN_set_docs.set_doc

      left outer join 
       (select LN_familias.familia, COUNT(*) AS total_contrapartes
       from LN_contraparte_familias
       inner join LN_familias on LN_contraparte_familias.familia = LN_familias.familia
       where LN_familias.cliente = @p_cliente
       group by LN_familias.familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
      where LN_familias.cliente = @p_cliente
      and LN_familias.subfamilia = 1
      order by LN_familias.nombre_familia, parent.nombre_familia

     end else

      if (@accion = 'empty') begin

       delete from LN_subfamilias
       where familia = @p_familia

      end
 
END
GO