dbo.LN_familias_

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE OR ALTER PROCEDURE dbo.LN_familias_
 -- Add the parameters for the stored procedure here
 @action         varchar(30),
 @p_familia        int,
 @p_nombre_familia      varchar(100) = null,
 @p_cliente        int = null,
 @p_country_code       varchar(2) = null,
 @p_visible        tinyint = null,
 @p_user_id        int = null,
 @p_set_doc        int = NULL,
 @p_subfamiia       tinyint = 0,
 @p_modalidad_login      varchar(1) = 'I',
 @p_uso_familia       varchar(1) = 'P',
 @p_id_producto       int = 47

 
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 (@action = 'INSERT') BEGIN

  DECLARE @familia INT

  insert into LN_familias 
  (nombre_familia, cliente, country_code, set_doc, modalidad_login, subfamilia, uso_familia, visible, created_by, id_producto)
  values
  (@p_nombre_familia, @p_cliente, @p_country_code, @p_set_doc, @p_modalidad_login, @p_subfamiia, @p_uso_familia, @p_visible, @p_user_id, @p_id_producto)

  SET @familia = SCOPE_IDENTITY()

  SELECT @familia AS familia from LN_familias

 END ELSE

  IF (@action = 'UPDATE') BEGIN

   UPDATE LN_familias set
   nombre_familia = @p_nombre_familia,
   cliente = @p_cliente,
   country_code = @p_country_code,
   set_doc = @p_set_doc,
   modalidad_login = @p_modalidad_login,
   subfamilia = @p_subfamiia,
   visible = @p_visible,
   uso_familia = @p_uso_familia,
   id_producto = @p_id_producto,
   modified = GETDATE(),
   modified_by = @p_user_id
   where familia = @p_familia


  END ELSE

   IF (@action = 'DELETE') BEGIN

    delete from LN_familias where familia = @p_familia

   END ELSE

    IF (@action = 'SELECT') BEGIN


     select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible, 
      LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
      LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
      CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
      CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
      users.first_name + ' ' + users.last_name as created_by_user,
      users2.first_name + ' ' + users2.last_name as modified_by_user,
      clientes.nombre_cliente, country.country_name,
      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 users on LN_familias.created_by = users.user_id
     left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
     left outer join users AS users2 on LN_familias.modified_by = users2.user_id
     left outer join 
       (select familia, COUNT(*) AS total_contrapartes
       from LN_contraparte_familias
       inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
       where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 and 
       familia = @p_familia
       group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
     where LN_familias.familia = @p_familia
     order by country.country_name, LN_familias.nombre_familia

    END ELSE

     -- acción específica para familias de clientes de Inquiro Vitae uso_familia='I'
     IF (@action = 'inquirovitae') BEGIN

      if (@p_cliente is not null) begin

       if (@p_visible is null)

        select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible, 
         LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
         LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
         CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
         CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
         users.first_name + ' ' + users.last_name as created_by_user,
         users2.first_name + ' ' + users2.last_name as modified_by_user,
         clientes.nombre_cliente, country.country_name,
         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 users on LN_familias.created_by = users.user_id
        left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
        left outer join users AS users2 on LN_familias.modified_by = users2.user_id
        left outer join 
          (select familia, COUNT(*) AS total_contrapartes
          from LN_contraparte_familias
          inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
          where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 and 
          familia = @p_familia
          group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
        where LN_familias.cliente = @p_cliente
        and (LN_familias.uso_familia = 'I' or ln_familias.servicio_contratado=10)
        order by country.country_name, LN_familias.nombre_familia

       else

        select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible, 
         LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
         LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
         CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
         CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
         users.first_name + ' ' + users.last_name as created_by_user,
         users2.first_name + ' ' + users2.last_name as modified_by_user,
         clientes.nombre_cliente, country.country_name,
         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 users on LN_familias.created_by = users.user_id
        left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
        left outer join users AS users2 on LN_familias.modified_by = users2.user_id
        left outer join 
          (select familia, COUNT(*) AS total_contrapartes
          from LN_contraparte_familias
          inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
          where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 and 
          familia = @p_familia
          group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
        where LN_familias.cliente = @p_cliente
        and LN_familias.visible = @p_visible
        and (LN_familias.uso_familia = 'I' or ln_familias.servicio_contratado=10)
        order by country.country_name, LN_familias.nombre_familia
      end

     END ELSE BEGIN
     
      IF (@p_country_code IS NULL) BEGIN


       IF (@p_cliente IS NULL and @p_visible IS NULL) BEGIN

        select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible, 
         LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
         LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
         CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
         CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
         users.first_name + ' ' + users.last_name as created_by_user,
         users2.first_name + ' ' + users2.last_name as modified_by_user,
         clientes.nombre_cliente, country.country_name,
         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 users on LN_familias.created_by = users.user_id
        left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
        left outer join users AS users2 on LN_familias.modified_by = users2.user_id
        left outer join 
          (select familia, COUNT(*) AS total_contrapartes
          from LN_contraparte_familias 
          inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
          where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1         
          group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
        where ISNULL(LN_familias.subfamilia, 0) = 0
        and LN_familias.uso_familia != 'I'
        order by country.country_name, LN_familias.nombre_familia

       end else

        IF (@p_cliente IS NULL and @p_visible IS NOT NULL) BEGIN

         select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible, 
          LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
          LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
          CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
          CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
          users.first_name + ' ' + users.last_name as created_by_user,
          users2.first_name + ' ' + users2.last_name as modified_by_user,
          clientes.nombre_cliente, country.country_name,
          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 users on LN_familias.created_by = users.user_id
         left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
         left outer join users AS users2 on LN_familias.modified_by = users2.user_id
         left outer join 
           (select familia, COUNT(*) AS total_contrapartes
           from LN_contraparte_familias
           inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
           where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 
           group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia

         WHERE LN_familias.visible = @p_visible
         AND ISNULL(LN_familias.subfamilia, 0) = 0
         and LN_familias.uso_familia != 'I'
         order by country.country_name, LN_familias.nombre_familia


        end else

         -- opcion general de familias por cliente
         if (@p_cliente IS NOT NULL and @p_visible IS NULL) BEGIN

          select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible, 
           LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
           LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
           CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
           CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
           users.first_name + ' ' + users.last_name as created_by_user,
           users2.first_name + ' ' + users2.last_name as modified_by_user,
           clientes.nombre_cliente, country.country_name,
           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 users on LN_familias.created_by = users.user_id
          left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
          left outer join users AS users2 on LN_familias.modified_by = users2.user_id
          left outer join 
            (select familia, COUNT(*) AS total_contrapartes
            from LN_contraparte_familias
            inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
            where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 
            group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia

          WHERE LN_familias.cliente = @p_cliente
          AND ISNULL(LN_familias.subfamilia, 0) = 0
          --and LN_familias.uso_familia != 'I'
          order by country.country_name, LN_familias.nombre_familia

         END ELSE

          select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible, 
           LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
           LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
           CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
           CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
           users.first_name + ' ' + users.last_name as created_by_user,
           users2.first_name + ' ' + users2.last_name as modified_by_user,
           clientes.nombre_cliente, country.country_name,
           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 users on LN_familias.created_by = users.user_id
          left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
          left outer join users AS users2 on LN_familias.modified_by = users2.user_id
          left outer join 
            (select familia, COUNT(*) AS total_contrapartes
            from LN_contraparte_familias
            inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
            where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 
            group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
          WHERE LN_familias.cliente = @p_cliente
          AND LN_familias.visible = @p_visible
          AND ISNULL(LN_familias.subfamilia, 0) = 0
          and LN_familias.uso_familia != 'I'
          order by country.country_name, LN_familias.nombre_familia
      
      END ELSE BEGIN

       /** SEARCH POR PAIS **/

       IF (@p_cliente IS NULL and @p_visible IS NULL) BEGIN

        select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible, 
         LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
         LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
         CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
         CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
         users.first_name + ' ' + users.last_name as created_by_user,
         users2.first_name + ' ' + users2.last_name as modified_by_user,
         clientes.nombre_cliente, country.country_name,
         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 users on LN_familias.created_by = users.user_id
        left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
        left outer join users AS users2 on LN_familias.modified_by = users2.user_id
        left outer join 
          (select familia, COUNT(*) AS total_contrapartes
          from LN_contraparte_familias
          inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
          where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 
          group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
        WHERE LN_familias.country_code = @p_country_code
        AND ISNULL(LN_familias.subfamilia, 0) = 0
        and LN_familias.uso_familia != 'I'
        order by country.country_name, LN_familias.nombre_familia

       end else

        IF (@p_cliente IS NULL and @p_visible IS NOT NULL) BEGIN

         select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible, 
          LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
          LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
          CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
          CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
          users.first_name + ' ' + users.last_name as created_by_user,
          users2.first_name + ' ' + users2.last_name as modified_by_user,
          clientes.nombre_cliente, country.country_name,
          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 users on LN_familias.created_by = users.user_id
         left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
         left outer join users AS users2 on LN_familias.modified_by = users2.user_id
         left outer join 
           (select familia, COUNT(*) AS total_contrapartes
           from LN_contraparte_familias
           inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
           where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 
           group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia
         WHERE LN_familias.visible = @p_visible
         AND LN_familias.country_code = @p_country_code
         AND ISNULL(LN_familias.subfamilia, 0) = 0
         and LN_familias.uso_familia != 'I'
         order by country.country_name, LN_familias.nombre_familia


        end else

         if (@p_cliente IS NOT NULL and @p_visible IS NULL) BEGIN

          select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible, 
           LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
           LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
           CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
           CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
           users.first_name + ' ' + users.last_name as created_by_user,
           users2.first_name + ' ' + users2.last_name as modified_by_user,
           clientes.nombre_cliente, country.country_name,
           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 users on LN_familias.created_by = users.user_id
          left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
          left outer join users AS users2 on LN_familias.modified_by = users2.user_id
          left outer join 
            (select familia, COUNT(*) AS total_contrapartes
            from LN_contraparte_familias
            inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
            where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1
            group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia

          WHERE LN_familias.cliente = @p_cliente
          AND LN_familias.country_code = @p_country_code
          AND ISNULL(LN_familias.subfamilia, 0) = 0
          and LN_familias.uso_familia != 'I'
          order by country.country_name, LN_familias.nombre_familia

         END ELSE

          select LN_familias.familia, LN_familias.nombre_familia, LN_familias.cliente, LN_familias.visible, 
           LN_familias.subfamilia, LN_familias.set_doc, LN_familias.modalidad_login, LN_set_docs.nombre_set,
           LN_familias.country_code, LN_familias.uso_familia, LN_familias.created_by, LN_familias.modified_by,
           CONVERT(VARCHAR(19), LN_familias.created, 121) AS created,
           CONVERT(VARCHAR(19), LN_familias.modified, 121) AS modified,
           users.first_name + ' ' + users.last_name as created_by_user,
           users2.first_name + ' ' + users2.last_name as modified_by_user,
           clientes.nombre_cliente, country.country_name,
           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 users on LN_familias.created_by = users.user_id
          left outer join LN_set_docs ON LN_familias.set_doc = LN_set_docs.set_doc
          left outer join users AS users2 on LN_familias.modified_by = users2.user_id
          left outer join 
            (select familia, COUNT(*) AS total_contrapartes
            from LN_contraparte_familias
            inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
            where LN_contrapartes.activo = 1 and LN_contraparte_familias.activo = 1 
            group by familia) contrap_fam ON LN_familias.familia = contrap_fam.familia

          WHERE LN_familias.cliente = @p_cliente
          AND LN_familias.visible = @p_visible
          AND LN_familias.country_code = @p_country_code
          AND ISNULL(LN_familias.subfamilia, 0) = 0
          and LN_familias.uso_familia != 'I'
          order by country.country_name, LN_familias.nombre_familia
      



      END

     END






 
END

GO