USE [ArandaDB]
GO
/****** Object:  StoredProcedure [dbo].[PRC_ASS_SEARCHER]    Script Date: 02/03/2018 9:22:46 a. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[PRC_ASS_SEARCHER]
(
   @i_operation                CHAR(1),
   @i_option                   CHAR(1),
   @i_user_id                  INT		= NULL,
   @i_category_id              INT		= NULL,
   @i_type_id                  INT		= NULL,
   @i_project_id               INT		= NULL,
   @i_keyw_word                VARCHAR(4000)	= NULL,      
   @o_err_cod                  INT              OUTPUT,
   @o_err_msg                  VARCHAR(255)     OUTPUT
) AS
BEGIN
   DECLARE @keyword_criteria VARCHAR(255)
   DECLARE @search_criteria VARCHAR(255)
   SELECT @keyword_criteria=dbo.FUN_AFW_CONTAINS_CRITERIA(@i_keyw_word,2)
   SELECT @search_criteria=dbo.FUN_AFW_CONTAINS_CRITERIA(@i_keyw_word,1)


   IF @i_operation = 'S' 
   BEGIN
      IF @i_option = 'A' 
         BEGIN
            SELECT ass_article.fl_int_solution_id,
                   fl_str_solution_title,
                   fl_str_solution_description,
				   fl_date_solution_last_modified,
                   keyw_relevance,
                   keyw_lru,
                   1 ass_source
            FROM   ass_keyword
            JOIN   ass_article ON (keyw_solution_id = fl_int_solution_id )
            JOIN   ass_category_article ON (ass_category_article.fl_int_solution_id = ass_article.fl_int_solution_id )
            JOIN   asdk_category_type_record ON (caty_category_id = ass_category_article.fl_int_category_id AND caty_project_id = @i_project_id AND caty_type_rec_id = 7)
            JOIN   asdk_status_behavior SB ON (SB.stat_id = ass_article.fl_int_solution_state AND SB.solved =1 )
            WHERE  CONTAINS (keyw_word, @keyword_criteria)
				AND (
					EXISTS (
						SELECT 1
						FROM ASS_ARTICLE_GROUP ag
						LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid
						WHERE ISNULL(userid, user_id) = @i_user_id
						AND articleid = ass_article.fl_int_solution_id
					) OR NOT EXISTS (
						SELECT 1
						FROM ASS_ARTICLE_GROUP ag
						LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid					
						WHERE  articleid = ass_article.fl_int_solution_id
					)
					OR @i_user_id = 0
				)
            UNION
            SELECT ass_article.fl_int_solution_id,
                   fl_str_solution_title,
                   fl_str_solution_description,
				   fl_date_solution_last_modified,
                   0 keyw_relevance,
                   fl_date_solution_created keyw_lru,
                   2 ass_source
            FROM   ass_article
            JOIN   ass_category_article ON (ass_category_article.fl_int_solution_id = ass_article.fl_int_solution_id )
            JOIN   asdk_category_type_record ON (caty_category_id = ass_category_article.fl_int_category_id AND caty_project_id = @i_project_id AND caty_type_rec_id = 7)            
            JOIN   asdk_status_behavior SB ON (SB.stat_id = ass_article.fl_int_solution_state AND SB.solved =1)
            WHERE ((@search_criteria = '"*"' OR CONTAINS (fl_str_solution_description, @search_criteria))
            OR    (@search_criteria = '"*"' OR CONTAINS (fl_str_solution_content, @search_criteria))
            OR    (@search_criteria = '"*"' OR CONTAINS (fl_str_solution_title, @search_criteria))
            OR    (@search_criteria = '"*"' OR CONTAINS (fl_str_solution_id, @search_criteria))
            OR    (@search_criteria = '"*"' OR CONTAINS (fl_str_solution_keyword, @search_criteria)))
				AND (
					EXISTS (
						SELECT 1
						FROM ASS_ARTICLE_GROUP ag
						LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid
						WHERE ISNULL(userid, user_id) = @i_user_id
						AND articleid = ass_article.fl_int_solution_id
					) OR NOT EXISTS (
						SELECT 1
						FROM ASS_ARTICLE_GROUP ag
						LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid					
						WHERE articleid = ass_article.fl_int_solution_id
					)
					OR @i_user_id = 0
				)
            ORDER  BY ass_source, keyw_relevance DESC , keyw_lru DESC
            
            IF @@ROWCOUNT=0
		    BEGIN		
				EXECUTE PRC_ASS_SEARCH_RESULT
				@i_operation       = 'I',
				@i_option          = 'A',
				@i_sere_user_id     = @i_user_id,
				@i_sere_category_id = @i_category_id,
				@i_sere_type_id     = @i_type_id,
				@i_sere_project_id  = @i_project_id,
				@i_sere_word        = @i_keyw_word,				
				@o_err_cod          = @o_err_cod OUTPUT,
				@o_err_msg          = @o_err_msg OUTPUT   
			END         
      END
      
      IF @i_option = 'B' 
         BEGIN           
            SELECT ass_article.fl_int_solution_id,
                   fl_str_solution_title,
                   fl_str_solution_description,
				   fl_date_solution_last_modified,
                   keyw_relevance,
                   keyw_lru,
                   1 ass_source
            FROM   ass_keyword
            JOIN   ass_article ON (keyw_solution_id = fl_int_solution_id)
            JOIN   ass_category_article ON (ass_category_article.fl_int_solution_id = ass_article.fl_int_solution_id)
            JOIN   asdk_category_type_record ON (caty_category_id = ass_category_article.fl_int_category_id AND caty_project_id = @i_project_id AND caty_type_rec_id = 7 AND caty_category_id = @i_category_id)
            JOIN   asdk_status_behavior SB ON (SB.stat_id = ass_article.fl_int_solution_state AND SB.solved =1)
            WHERE  CONTAINS (keyw_word, @keyword_criteria)
				AND (
					EXISTS (
						SELECT 1
						FROM ASS_ARTICLE_GROUP ag
						LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid
						WHERE ISNULL(userid, user_id) = @i_user_id
						AND articleid = ass_article.fl_int_solution_id
					) OR NOT EXISTS (
						SELECT 1
						FROM ASS_ARTICLE_GROUP ag
						LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid					
						WHERE articleid = ass_article.fl_int_solution_id
					)
					OR @i_user_id = 0
				)
            UNION
            SELECT ass_article.fl_int_solution_id,
                   fl_str_solution_title,
                   fl_str_solution_description,
			       fl_date_solution_last_modified,
                   0 keyw_relevance,
                   fl_date_solution_created keyw_lru,
                   2 ass_source
            FROM   ass_article
            JOIN   ass_category_article ON (ass_category_article.fl_int_solution_id = ass_article.fl_int_solution_id AND    ass_article.fl_int_public = 1)
            JOIN   asdk_category_type_record ON (caty_category_id = ass_category_article.fl_int_category_id AND caty_project_id = @i_project_id AND caty_type_rec_id = 7  AND caty_category_id = @i_category_id)
            JOIN   asdk_status_behavior SB ON SB.stat_id = ass_article.fl_int_solution_state
            WHERE ((@search_criteria = '"*"' OR  CONTAINS (fl_str_solution_description, @search_criteria) )
            OR    (@search_criteria = '"*"' OR  CONTAINS (fl_str_solution_content, @search_criteria))
            OR    (@search_criteria = '"*"' OR  CONTAINS (fl_str_solution_title, @search_criteria))
            OR    (@search_criteria = '"*"' OR  CONTAINS (fl_str_solution_id, @search_criteria))
            OR    (@search_criteria = '"*"' OR  CONTAINS (fl_str_solution_keyword, @search_criteria)))
            AND    SB.solved =1
				AND (
					EXISTS (
						SELECT 1
						FROM ASS_ARTICLE_GROUP ag
						LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid
						WHERE ISNULL(userid, user_id) = @i_user_id
						AND articleid = ass_article.fl_int_solution_id
					) OR NOT EXISTS (
						SELECT 1
						FROM ASS_ARTICLE_GROUP ag
						LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid					
						where articleid = ass_article.fl_int_solution_id
					)
					OR @i_user_id = 0
				)
            ORDER  BY ass_source, keyw_relevance DESC, keyw_lru DESC
            
            IF @@ROWCOUNT=0
		    BEGIN	
				EXECUTE PRC_ASS_SEARCH_RESULT
				@i_operation       = 'I',
				@i_option          = 'A',
				@i_sere_user_id     = @i_user_id,
				@i_sere_category_id = @i_category_id,
				@i_sere_type_id     = @i_type_id,
				@i_sere_project_id  = @i_project_id,
				@i_sere_word        = @i_keyw_word,				
				@o_err_cod          = @o_err_cod OUTPUT,
				@o_err_msg          = @o_err_msg OUTPUT              
			END
      END
      
      IF @i_option = 'C' 
         BEGIN
            SELECT ass_article.fl_int_solution_id,
                   fl_str_solution_title,
                   fl_str_solution_description,
			       fl_date_solution_last_modified,
                   keyw_relevance,
                   keyw_lru,
                   1 ass_source
            FROM   ass_keyword
            JOIN   ass_article ON (keyw_solution_id = fl_int_solution_id AND fl_int_type_id = @i_type_id )
            JOIN   ass_category_article ON (ass_category_article.fl_int_solution_id = ass_article.fl_int_solution_id)
            JOIN   asdk_category_type_record ON (caty_category_id = ass_category_article.fl_int_category_id AND caty_project_id = @i_project_id AND caty_type_rec_id = 7 AND caty_category_id = @i_category_id)
            JOIN   asdk_status_behavior SB ON (SB.stat_id = ass_article.fl_int_solution_state)
            WHERE  CONTAINS (keyw_word, @keyword_criteria)  
            AND    ass_article.fl_int_public = 1
            AND    SB.solved =1   
				AND (
					EXISTS (
						SELECT 1
						FROM ASS_ARTICLE_GROUP ag
						LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid
						WHERE ISNULL(userid, user_id) = @i_user_id
						AND articleid = ass_article.fl_int_solution_id
					) OR NOT EXISTS (
						SELECT 1
						FROM ASS_ARTICLE_GROUP ag
						LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid					
						WHERE articleid = ass_article.fl_int_solution_id
					)
					OR @i_user_id = 0
				)       
            UNION
            SELECT ass_article.fl_int_solution_id,
                   fl_str_solution_title,
                   fl_str_solution_description,
                   fl_date_solution_last_modified,
                   0 keyw_relevance,
                   fl_date_solution_created keyw_lru,
                   2 ass_source
            FROM   ass_article
            JOIN   ass_category_article ON (ass_category_article.fl_int_solution_id = ass_article.fl_int_solution_id )
            JOIN   asdk_category_type_record ON (caty_category_id = ass_category_article.fl_int_category_id AND caty_project_id = @i_project_id AND caty_type_rec_id = 7 AND caty_category_id = @i_category_id)
            JOIN   asdk_status_behavior SB ON (SB.stat_id = ass_article.fl_int_solution_state)
            WHERE  ((@search_criteria = '"*"' OR CONTAINS (fl_str_solution_description, @search_criteria))
            OR     (@search_criteria = '"*"' OR CONTAINS (fl_str_solution_content, @search_criteria))
            OR     (@search_criteria = '"*"' OR CONTAINS (fl_str_solution_title, @search_criteria))
            OR     (@search_criteria = '"*"' OR CONTAINS (fl_str_solution_id, @search_criteria))
            OR     (@search_criteria = '"*"' OR CONTAINS (fl_str_solution_keyword, @search_criteria)))
            AND    fl_int_type_id = @i_type_id
            AND    SB.solved =1
				AND (
					EXISTS (
						SELECT 1
						FROM ASS_ARTICLE_GROUP ag
						LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid
						WHERE ISNULL(userid, user_id) = @i_user_id
						AND articleid = ass_article.fl_int_solution_id
					) OR NOT EXISTS (
						SELECT 1
						FROM ASS_ARTICLE_GROUP ag
						LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid					
						WHERE articleid = ass_article.fl_int_solution_id
					)
					OR @i_user_id = 0
				)
            ORDER  BY ass_source, keyw_relevance DESC, keyw_lru DESC
            
            IF @@ROWCOUNT=0
		    BEGIN	
				EXECUTE PRC_ASS_SEARCH_RESULT
				@i_operation        = 'I',
				@i_option           = 'A',
				@i_sere_user_id     = @i_user_id,
				@i_sere_category_id = @i_category_id,
				@i_sere_type_id     = @i_type_id,
				@i_sere_project_id  = @i_project_id,
				@i_sere_word        = @i_keyw_word,
				@o_err_cod          = @o_err_cod OUTPUT,
				@o_err_msg          = @o_err_msg OUTPUT   
			END
      END      

	  --IF @i_option = 'D' 
   --      BEGIN
   --         SELECT ass_article.fl_int_solution_id,
   --                fl_str_solution_title,
   --                fl_str_solution_description,
			--	   fl_date_solution_last_modified,
   --                keyw_relevance,
   --                keyw_lru,
   --                1 ass_source
   --         FROM   ass_keyword
   --         JOIN   ass_article ON (keyw_solution_id = fl_int_solution_id AND  ass_article.fl_int_public = 1)
   --         JOIN   ass_category_article ON (ass_category_article.fl_int_solution_id = ass_article.fl_int_solution_id)
   --         JOIN   asdk_category_type_record ON (caty_category_id = ass_category_article.fl_int_category_id AND caty_project_id = @i_project_id AND caty_type_rec_id = 7)
   --         JOIN   asdk_status_behavior SB ON (SB.stat_id = ass_article.fl_int_solution_state)
   --         WHERE  CONTAINS (keyw_word, @keyword_criteria)   
   --         AND    ass_article.fl_int_public = 1   
   --         AND    SB.solved =1  
			--AND (
			--	EXISTS (
			--		SELECT 1
			--		FROM ASS_ARTICLE_GROUP ag
			--		LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid
			--		WHERE ISNULL(userid, user_id) = @i_user_id
			--		AND articleid = ass_article.fl_int_solution_id
			--	) OR NOT EXISTS (
			--		SELECT 1
			--		FROM ASS_ARTICLE_GROUP ag
			--		LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid					
			--		WHERE articleid = ass_article.fl_int_solution_id
			--	)
			--)   
   --         UNION
   --         SELECT ass_article.fl_int_solution_id,
   --                fl_str_solution_title,
   --                fl_str_solution_description,
			--	   fl_date_solution_last_modified,
   --                0 keyw_relevance,
   --                fl_date_solution_created keyw_lru,
   --                2 ass_source
   --         FROM   ass_article
   --         JOIN   ass_category_article ON (ass_category_article.fl_int_solution_id = ass_article.fl_int_solution_id AND  ass_article.fl_int_public = 1)
   --         JOIN   asdk_category_type_record ON (caty_category_id = ass_category_article.fl_int_category_id AND caty_project_id = @i_project_id AND caty_type_rec_id = 7)            
   --         JOIN   asdk_status_behavior SB ON (SB.stat_id = ass_article.fl_int_solution_state)
			--/*Se modifica el criterio de busqueda para que acepte valores nulos*/
   --         WHERE ((@search_criteria = '"*"' OR CONTAINS (fl_str_solution_description, @search_criteria))
   --         OR    (@search_criteria = '"*"' OR CONTAINS (fl_str_solution_content, @search_criteria))
   --         OR    (@search_criteria = '"*"' OR CONTAINS (fl_str_solution_title, @search_criteria))
   --         OR    (@search_criteria = '"*"' OR CONTAINS (fl_str_solution_id, @search_criteria))
   --         OR    (@search_criteria = '"*"' OR CONTAINS (fl_str_solution_keyword, @search_criteria)))
   --         AND    ass_article.fl_int_public = 1
   --         AND    SB.solved =1
			--AND (
			--	EXISTS (
			--		SELECT 1
			--		FROM ASS_ARTICLE_GROUP ag
			--		LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid
			--		WHERE ISNULL(userid, user_id) = @i_user_id
			--		AND articleid = ass_article.fl_int_solution_id
			--	) OR NOT EXISTS (
			--		SELECT 1
			--		FROM ASS_ARTICLE_GROUP ag
			--		LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid					
			--		WHERE articleid = ass_article.fl_int_solution_id
			--	)
			--)
   --         ORDER  BY ass_source, keyw_relevance DESC, keyw_lru DESC
            
   --         IF @@ROWCOUNT=0
		 --   BEGIN		
			--	EXECUTE PRC_ASS_SEARCH_RESULT
			--	@i_operation       = 'I',
			--	@i_option          = 'A',
			--	@i_sere_user_id     = @i_user_id,
			--	@i_sere_category_id = @i_category_id,
			--	@i_sere_type_id     = @i_type_id,
			--	@i_sere_project_id  = @i_project_id,
			--	@i_sere_word        = @i_keyw_word,				
			--	@o_err_cod          = @o_err_cod OUTPUT,
			--	@o_err_msg          = @o_err_msg OUTPUT   
			--END         
   --   END
      
      IF @i_option = 'E' 
         BEGIN           
            SELECT ass_article.fl_int_solution_id,
                   fl_str_solution_title,
                   fl_str_solution_description,
				   fl_date_solution_last_modified,
                   keyw_relevance,
                   keyw_lru,
                   1 ass_source
            FROM   ass_keyword
            JOIN   ass_article ON (keyw_solution_id = fl_int_solution_id AND  ass_article.fl_int_public = 1)
            JOIN   ass_category_article ON (ass_category_article.fl_int_solution_id = ass_article.fl_int_solution_id)
            JOIN   asdk_category_type_record ON (caty_category_id = ass_category_article.fl_int_category_id AND caty_project_id = @i_project_id AND caty_type_rec_id = 7 AND caty_category_id = @i_category_id)
            JOIN   asdk_status_behavior SB ON (SB.stat_id = ass_article.fl_int_solution_state)
            WHERE  CONTAINS (keyw_word, @keyword_criteria)                     
            AND    SB.solved =1 
			AND (
				EXISTS (
					SELECT 1
					FROM ASS_ARTICLE_GROUP ag
					LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid
					WHERE ISNULL(userid, user_id) = @i_user_id
					AND articleid = ass_article.fl_int_solution_id
				) OR NOT EXISTS (
					SELECT 1
					FROM ASS_ARTICLE_GROUP ag
					LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid					
					WHERE articleid = ass_article.fl_int_solution_id
				)
			)
            UNION
            SELECT ass_article.fl_int_solution_id,
                   fl_str_solution_title,
                   fl_str_solution_description,
			       fl_date_solution_last_modified,
                   0 keyw_relevance,
                   fl_date_solution_created keyw_lru,
                   2 ass_source
            FROM   ass_article
            JOIN   ass_category_article ON (ass_category_article.fl_int_solution_id = ass_article.fl_int_solution_id AND  ass_article.fl_int_public = 1)
            JOIN   asdk_category_type_record ON (caty_category_id = ass_category_article.fl_int_category_id AND caty_project_id = @i_project_id AND caty_type_rec_id = 7  AND caty_category_id = @i_category_id)
            JOIN   asdk_status_behavior SB ON (SB.stat_id = ass_article.fl_int_solution_state)
            WHERE ((@search_criteria = '"*"' OR  CONTAINS (fl_str_solution_description, @search_criteria)) 
            OR    (@search_criteria = '"*"' OR  CONTAINS (fl_str_solution_content, @search_criteria))
            OR    (@search_criteria = '"*"' OR  CONTAINS (fl_str_solution_title, @search_criteria))
            OR    (@search_criteria = '"*"' OR  CONTAINS (fl_str_solution_id, @search_criteria))   
            OR    (@search_criteria = '"*"' OR  CONTAINS (fl_str_solution_keyword, @search_criteria)))         
            AND    SB.solved =1
			AND (
				EXISTS (
					SELECT 1
					FROM ASS_ARTICLE_GROUP ag
					LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid
					WHERE ISNULL(userid, user_id) = @i_user_id
					AND articleid = ass_article.fl_int_solution_id
				) OR NOT EXISTS (
					SELECT 1
					FROM ASS_ARTICLE_GROUP ag
					LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid					
					WHERE  articleid = ass_article.fl_int_solution_id
				)
			)
            ORDER  BY ass_source, keyw_relevance DESC, keyw_lru DESC
            
            IF @@ROWCOUNT=0
		    BEGIN	
				EXECUTE PRC_ASS_SEARCH_RESULT
				@i_operation       = 'I',
				@i_option          = 'A',
				@i_sere_user_id     = @i_user_id,
				@i_sere_category_id = @i_category_id,
				@i_sere_type_id     = @i_type_id,
				@i_sere_project_id  = @i_project_id,
				@i_sere_word        = @i_keyw_word,				
				@o_err_cod          = @o_err_cod OUTPUT,
				@o_err_msg          = @o_err_msg OUTPUT              
			END
      END
      
      IF @i_option = 'F' 
         BEGIN
            SELECT ass_article.fl_int_solution_id,
                   fl_str_solution_title,
                   fl_str_solution_description,
			       fl_date_solution_last_modified,
                   keyw_relevance,
                   keyw_lru,
                   1 ass_source
            FROM   ass_keyword
            JOIN   ass_article ON (keyw_solution_id = fl_int_solution_id AND fl_int_type_id = @i_type_id AND  ass_article.fl_int_public = 1)
            JOIN   ass_category_article ON (ass_category_article.fl_int_solution_id = ass_article.fl_int_solution_id)
            JOIN   asdk_category_type_record ON (caty_category_id = ass_category_article.fl_int_category_id AND caty_project_id = @i_project_id AND caty_type_rec_id = 7 AND caty_category_id = @i_category_id)
            JOIN   asdk_status_behavior SB ON (SB.stat_id = ass_article.fl_int_solution_state)
            WHERE  CONTAINS (keyw_word, @keyword_criteria)                
            AND    SB.solved =1      
			AND (
				EXISTS (
					SELECT 1
					FROM ASS_ARTICLE_GROUP ag
					LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid
					WHERE ISNULL(userid, user_id) = @i_user_id
					AND articleid = ass_article.fl_int_solution_id
				) OR NOT EXISTS (
					SELECT 1
					FROM ASS_ARTICLE_GROUP ag
					LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid					
					WHERE articleid = ass_article.fl_int_solution_id
				)
			)
            UNION
            SELECT ass_article.fl_int_solution_id,
                   fl_str_solution_title,
                   fl_str_solution_description,
                   fl_date_solution_last_modified,
                   0 keyw_relevance,
                   fl_date_solution_created keyw_lru,
                   2 ass_source
            FROM   ass_article
            JOIN   ass_category_article ON (ass_category_article.fl_int_solution_id = ass_article.fl_int_solution_id AND  ass_article.fl_int_public = 1)
            JOIN   asdk_category_type_record ON (caty_category_id = ass_category_article.fl_int_category_id AND caty_project_id = @i_project_id AND caty_type_rec_id = 7 AND caty_category_id = @i_category_id)
            JOIN   asdk_status_behavior SB ON (SB.stat_id = ass_article.fl_int_solution_state)
            WHERE  ((@search_criteria = '"*"' OR CONTAINS (fl_str_solution_description, @search_criteria))
            OR     (@search_criteria = '"*"' OR CONTAINS (fl_str_solution_content, @search_criteria))
            OR     (@search_criteria = '"*"' OR CONTAINS (fl_str_solution_title, @search_criteria))
            OR     (@search_criteria = '"*"' OR CONTAINS (fl_str_solution_id, @search_criteria))
            OR     (@search_criteria = '"*"' OR CONTAINS (fl_str_solution_keyword, @search_criteria)))
            AND    fl_int_type_id = @i_type_id            
            AND    SB.solved =1
			AND (
				EXISTS (
					SELECT 1
					FROM ASS_ARTICLE_GROUP ag
					LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid
					WHERE ISNULL(userid, user_id) = @i_user_id
					AND articleid = ass_article.fl_int_solution_id
				) OR NOT EXISTS (
					SELECT 1
					FROM ASS_ARTICLE_GROUP ag
					LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid					
					WHERE articleid = ass_article.fl_int_solution_id
				)
			)
            ORDER  BY ass_source, keyw_relevance DESC, keyw_lru DESC
            
            IF @@ROWCOUNT=0
		    BEGIN	
				EXECUTE PRC_ASS_SEARCH_RESULT
				@i_operation        = 'I',
				@i_option           = 'A',
				@i_sere_user_id     = @i_user_id,
				@i_sere_category_id = @i_category_id,
				@i_sere_type_id     = @i_type_id,
				@i_sere_project_id  = @i_project_id,
				@i_sere_word        = @i_keyw_word,
				@o_err_cod          = @o_err_cod OUTPUT,
				@o_err_msg          = @o_err_msg OUTPUT   
			END
      END    

      IF @i_option = 'G' 
      BEGIN
         SELECT ass_article.fl_int_solution_id,
                fl_str_solution_title,
                fl_str_solution_description,
		          fl_date_solution_last_modified
         FROM   ass_keyword
         JOIN   ass_article ON (keyw_solution_id = fl_int_solution_id AND ass_article.fl_int_public = 1)
         JOIN   asdk_category_type_record ON (caty_project_id = @i_project_id )
         JOIN   asdk_status_behavior SB ON (SB.stat_id = ass_article.fl_int_solution_state)
         WHERE  CONTAINS (keyw_word, @keyword_criteria)                
         AND    SB.solved =1      
		 AND (
				EXISTS (
					SELECT 1
					FROM ASS_ARTICLE_GROUP ag
					LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid
					WHERE ISNULL(userid, user_id) = @i_user_id
					AND articleid = ass_article.fl_int_solution_id
				) OR NOT EXISTS (
					SELECT 1
					FROM ASS_ARTICLE_GROUP ag
					LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid					
					WHERE articleid = ass_article.fl_int_solution_id
				)
			)
         UNION
         SELECT ass_article.fl_int_solution_id,
                fl_str_solution_title,
                fl_str_solution_description,
                fl_date_solution_last_modified
         FROM   ass_article
         JOIN   ass_category_article ON (ass_category_article.fl_int_solution_id = ass_article.fl_int_solution_id AND  ass_article.fl_int_public = 1)
         JOIN   asdk_category_type_record ON (caty_project_id = @i_project_id)
         JOIN   asdk_status_behavior SB ON (SB.stat_id = ass_article.fl_int_solution_state)
         WHERE ((@search_criteria = '"*"' OR  CONTAINS (fl_str_solution_description, @search_criteria) )
         OR    (@search_criteria = '"*"' OR  CONTAINS (fl_str_solution_content, @search_criteria))
         OR    (@search_criteria = '"*"' OR  CONTAINS (fl_str_solution_title, @search_criteria))
         OR    (@search_criteria = '"*"' OR  CONTAINS (fl_str_solution_id, @search_criteria))
         OR    (@search_criteria = '"*"' OR  CONTAINS (fl_str_solution_keyword, @search_criteria)))
         AND    SB.solved =1
		 AND (
				EXISTS (
					SELECT 1
					FROM ASS_ARTICLE_GROUP ag
					LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid
					WHERE ISNULL(userid, user_id) = @i_user_id
					AND articleid = ass_article.fl_int_solution_id
				) OR NOT EXISTS (
					SELECT 1
					FROM ASS_ARTICLE_GROUP ag
					LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid					
					WHERE articleid = ass_article.fl_int_solution_id
				)
			)
            
         IF @@ROWCOUNT=0
	      BEGIN	
				EXECUTE PRC_ASS_SEARCH_RESULT
				@i_operation        = 'I',
				@i_option           = 'A',
				@i_sere_user_id     = @i_user_id,
				@i_sere_category_id = @i_category_id,
				@i_sere_type_id     = @i_type_id,
				@i_sere_project_id  = @i_project_id,
				@i_sere_word        = @i_keyw_word,
				@o_err_cod          = @o_err_cod OUTPUT,
				@o_err_msg          = @o_err_msg OUTPUT   
			END
      END    
      
      /*============================================================================*/
      /* CONSULTA TODOS LOS ARTICULOS PERSONALIZADOS  TANTO PUBLICAS COMO PRIVADAS  */
      /*============================================================================*/  
      IF @i_option = 'H' 
      BEGIN
            SELECT ass_article.fl_int_solution_id,
                   fl_str_solution_title,
                   fl_str_solution_description,
				   fl_date_solution_last_modified,
                   keyw_relevance,
                   keyw_lru,
                   1 ass_source
            FROM   ass_keyword
            JOIN   ass_article ON (keyw_solution_id = fl_int_solution_id  )
            JOIN   ass_category_article ON (ass_category_article.fl_int_solution_id = ass_article.fl_int_solution_id )
            JOIN   asdk_category_type_record ON (caty_category_id = ass_category_article.fl_int_category_id AND caty_project_id = @i_project_id )
            JOIN   asdk_status_behavior SB ON (SB.stat_id = ass_article.fl_int_solution_state AND SB.solved =1 )
            WHERE  CONTAINS (keyw_word, @keyword_criteria)
			AND (
				EXISTS (
					SELECT 1
					FROM ASS_ARTICLE_GROUP ag
					LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid
					WHERE ISNULL(userid, user_id) = @i_user_id
					AND articleid = ass_article.fl_int_solution_id
				) OR NOT EXISTS (
					SELECT 1
					FROM ASS_ARTICLE_GROUP ag
					LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid					
					WHERE articleid = ass_article.fl_int_solution_id
				)
			)
            UNION
            SELECT ass_article.fl_int_solution_id,
                   fl_str_solution_title,
                   fl_str_solution_description,
				   fl_date_solution_last_modified,
                   0 keyw_relevance,
                   fl_date_solution_created keyw_lru,
                   2 ass_source
            FROM   ass_article
            JOIN   ass_category_article ON (ass_category_article.fl_int_solution_id = ass_article.fl_int_solution_id )
            JOIN   asdk_category_type_record ON (caty_category_id = ass_category_article.fl_int_category_id AND caty_project_id = @i_project_id )            
            JOIN   asdk_status_behavior SB ON (SB.stat_id = ass_article.fl_int_solution_state AND SB.solved =1)
            WHERE ((@search_criteria = '"*"' OR   CONTAINS (fl_str_solution_description, @search_criteria))
            OR    (@search_criteria = '"*"' OR  CONTAINS (fl_str_solution_content, @search_criteria))
            OR    (@search_criteria = '"*"' OR  CONTAINS (fl_str_solution_title, @search_criteria))
            OR    (@search_criteria = '"*"' OR  CONTAINS (fl_str_solution_id, @search_criteria))
            OR    (@search_criteria = '"*"' OR  CONTAINS (fl_str_solution_keyword, @search_criteria)))
			AND (
				EXISTS (
					SELECT 1
					FROM ASS_ARTICLE_GROUP ag
					LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid
					WHERE ISNULL(userid, user_id) = @i_user_id
					AND articleid = ass_article.fl_int_solution_id
				) OR NOT EXISTS (
					SELECT 1
					FROM ASS_ARTICLE_GROUP ag
					LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid					
					WHERE articleid = ass_article.fl_int_solution_id
				)
			)
            ORDER  BY ass_source, keyw_relevance DESC , keyw_lru DESC
            
            IF @@ROWCOUNT=0
		    BEGIN		
				EXECUTE PRC_ASS_SEARCH_RESULT
				@i_operation       = 'I',
				@i_option          = 'A',
				@i_sere_user_id     = @i_user_id,
				@i_sere_category_id = @i_category_id,
				@i_sere_type_id     = @i_type_id,
				@i_sere_project_id  = @i_project_id,
				@i_sere_word        = @i_keyw_word,				
				@o_err_cod          = @o_err_cod OUTPUT,
				@o_err_msg          = @o_err_msg OUTPUT   
			END         
      END      
      
      IF @i_option = 'Z' 
         BEGIN
            SELECT fl_int_solution_id,
                   fl_str_solution_title,
                   fl_str_solution_description,
                   fl_date_solution_last_modified,
                   keyw_relevance,
                   keyw_lru,
                   1 ass_source
            FROM   ass_keyword
            JOIN   ass_article ON (keyw_solution_id = fl_int_solution_id)
            JOIN   asdk_status_behavior SB ON (SB.stat_id = ass_article.fl_int_solution_state)
            WHERE  CONTAINS (keyw_word, @keyword_criteria) 
            AND    ass_article.fl_int_public = 1
            AND    SB.solved =1
			AND (
				EXISTS (
					SELECT 1
					FROM ASS_ARTICLE_GROUP ag
					LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid
					WHERE ISNULL(userid, user_id) = @i_user_id
					AND articleid = ass_article.fl_int_solution_id
				) OR NOT EXISTS (
					SELECT 1
					FROM ASS_ARTICLE_GROUP ag
					LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid					
					WHERE articleid = ass_article.fl_int_solution_id
				)
			)
            UNION
            SELECT ass_article.fl_int_solution_id,
                   fl_str_solution_title,
                   fl_str_solution_description,
                   fl_date_solution_last_modified,
                   0 keyw_relevance,
                   fl_date_solution_created keyw_lru,
                   2 ass_source
            FROM   ass_article
            JOIN   asdk_status_behavior SB ON (SB.stat_id = ass_article.fl_int_solution_state)
            WHERE  ((@search_criteria = '"*"' OR CONTAINS (fl_str_solution_description, @search_criteria))
            OR     (@search_criteria = '"*"' OR CONTAINS (fl_str_solution_content, @search_criteria))
            OR     (@search_criteria = '"*"' OR CONTAINS (fl_str_solution_title, @search_criteria))
            OR     (@search_criteria = '"*"' OR CONTAINS (fl_str_solution_id, @search_criteria))
            OR     (@search_criteria = '"*"' OR CONTAINS (fl_str_solution_keyword, @search_criteria)))
            AND    ass_article.fl_int_public = 1
            AND    SB.solved =1
			AND (
				EXISTS (
					SELECT 1
					FROM ASS_ARTICLE_GROUP ag
					LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid
					WHERE ISNULL(userid, user_id) = @i_user_id
					AND articleid = ass_article.fl_int_solution_id
				) OR NOT EXISTS (
					SELECT 1
					FROM ASS_ARTICLE_GROUP ag
					LEFT JOIN ASDK_SPECIAL_GROUP_USER gu ON gu.group_id = ag.groupid					
					WHERE articleid = ass_article.fl_int_solution_id
				)
			)
            ORDER  BY ass_source, keyw_relevance DESC, keyw_lru DESC
            
            IF @@ROWCOUNT=0
		    BEGIN	
				EXECUTE PRC_ASS_SEARCH_RESULT
				@i_operation        = 'I',
				@i_option           = 'A',
				@i_sere_user_id     = @i_user_id,
				@i_sere_category_id = @i_category_id,
				@i_sere_type_id     = @i_type_id,
				@i_sere_project_id  = @i_project_id,
				@i_sere_word        = @i_keyw_word,
				@o_err_cod          = @o_err_cod OUTPUT,
				@o_err_msg          = @o_err_msg OUTPUT   
			END
      END
	END    
END