cust_CCIW_get_universal_search

USE [ArenaDB]
GO
/****** Object:  StoredProcedure [dbo].[cust_CCIW_get_universal_search]    Script Date: 11/17/2020 3:53:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Tony Visconti/Ethan Jordan
-- Create date: 11-11-2020
-- Description: Search for various objects in Arena
-- =============================================
ALTER PROCEDURE [dbo].[cust_CCIW_get_universal_search] @SearchEntry VARCHAR(50)
    ,@OrganizationID INT
    ,@ProfilePage1 VARCHAR(5) = '25'
    ,@ProfilePage2 VARCHAR(5) = '376'
    ,@AttributePage VARCHAR(5) = '2294'
    ,@ReportPage VARCHAR(5) = '3201'
    ,@AttendancePage VARCHAR(5) = '1437'
    ,@CommsPage VARCHAR(5) = '21'
    ,@FormsPage VARCHAR(5) = '3512'
    ,@FormTemplatePage VARCHAR(5) = '3508'
    ,@GroupPage VARCHAR(5) = '2338'
    ,@MissionsPage VARCHAR(5) = '3205'
    ,@AssignmentsPage VARCHAR(5) = '3210'
    ,@CurrentUser INT = -1
    ,@GlobalAdminID INT = 43
    ,@Filter VARCHAR(5)
AS
BEGIN

    DECLARE @IsGlobalAdmin BIT = 0,
    @AnchorHTMLStart VARCHAR(50) = '<a href="./default.aspx?page='


    IF EXISTS (
            SELECT *
            FROM secu_person_role R
            WHERE R.person_id = @CurrentUser AND R.role_id = @GlobalAdminID
            )
    BEGIN
        SET @IsGlobalAdmin = 1
    END
    IF @SearchEntry = ''
        SELECT 'Please Enter a Search Term'
    ELSE
        SELECT *
        FROM (
            SELECT 'Tag' AS Item_Type
                ,@AnchorHTMLStart + CASE WHEN Obj.profile_type = 4 THEN @ProfilePage2 ELSE @ProfilePage1 END + '&profile=' + CONVERT(VARCHAR, Obj.profile_id) + '">' + profile_name + '</a>' AS ITEM
                ,3 AS [Object_Type]
                ,profile_id AS [Object_Key]
                , '' AS [Public_Flag]
            FROM core_profile Obj
            WHERE profile_name LIKE '%' + @SearchEntry + '%' OR profile_desc LIKE '%' + @SearchEntry + '%'

            UNION

            SELECT 'Attribute'
                ,@AnchorHTMLStart + @AttributePage + '&AttributeGroup=' + CONVERT(VARCHAR, Obj.attribute_group_id) + '">' + attribute_name + '</a>'
                ,4 AS [Object_Type]
                ,attribute_id AS [Object_Key]
                , '' AS [Public_Flag]
            FROM core_attribute Obj
            WHERE attribute_name LIKE '%' + @SearchEntry + '%'

            UNION

            SELECT 'List'
                ,@AnchorHTMLStart + @ReportPage + '&reportid=' + CONVERT(VARCHAR, Obj.report_id) + '&reporttype=' + CONVERT(VARCHAR, Obj.report_type_id) + '">' + report_name + '</a>'
                ,-2 AS [Object_Type]
                ,report_id AS [Object_Key]
                , OBJ.report_public_status AS [Public_Flag]
            FROM list_save_reports Obj
            left join secu_login l on l.login_id = obj.created_by
            WHERE report_name LIKE '%' + @SearchEntry + '%' OR report_desc LIKE '%' + @SearchEntry + '%'

            UNION

            SELECT 'Attendance'
                ,@AnchorHTMLStart + @AttendancePage + '&Type=' + CONVERT(VARCHAR, Obj.occurrence_type_id) + '">' + [type_name] + '</a>'
                ,-3 AS [Object_Type]
                ,occurrence_type_id AS [Object_Key]
                , '' AS [Public_Flag]
            FROM core_occurrence_type Obj
            WHERE [type_name] LIKE '%' + @SearchEntry + '%'

            UNION

            SELECT Distinct 'Communications' --Distinct here eliminate duplicate results for individuals with multiple logins
                ,@AnchorHTMLStart + @CommsPage + '&communicationid=' + CONVERT(VARCHAR, Obj.communication_id) + '">' + [subject] + '</a>'
                ,-1 AS [Object_Type] -- Note The ability to view a list of previous emails is controlled via module security. Once you have read access to the mailmerge module you can read any previous communication if you know the communication id.
                ,l.person_id AS [Object_Key]
                , '' AS [Public_Flag]
            FROM core_communication Obj
            left join secu_login l on l.login_id = obj.created_by
            WHERE [subject] LIKE '%' + @SearchEntry + '%' OR text_message LIKE '%' + @SearchEntry + '%'

            UNION

            SELECT 'Forms'
                ,@AnchorHTMLStart + @FormsPage + '&form=' + CONVERT(VARCHAR, Obj.form_id) + '">' + form_title + '</a>'
                ,-4 AS [Object_Type]
                ,form_id AS [Object_Key]
                , '' AS [Public_Flag]
            FROM cust_kfs_custom_form Obj
            WHERE form_title LIKE '%' + @SearchEntry + '%'

            UNION

            SELECT 'Form Templates'
                ,@AnchorHTMLStart + @FormTemplatePage + '&formtemplateid=' + CONVERT(VARCHAR, Obj.form_template_id) + '">' + title + '</a>'
                ,99 AS [Object_Type]
                ,form_template_id AS [Object_Key]
                , '' AS [Public_Flag]
            FROM cust_kfs_custom_form_template Obj
            WHERE title LIKE '%' + @SearchEntry + '%'

            UNION

            SELECT 'Groups'
                ,@AnchorHTMLStart + @GroupPage + '&group=' + CONVERT(VARCHAR, Obj.group_id) + '">' + group_name + '</a>'
                ,-5 AS [Object_Type]
                ,group_id AS [Object_Key]
                , '' AS [Public_Flag]
            FROM smgp_group Obj
            WHERE group_name LIKE '%' + @SearchEntry + '%'

            UNION

            SELECT 'Missions Trips'
                ,@AnchorHTMLStart + @MissionsPage + '&mission=' + CONVERT(VARCHAR, Obj.mission_id) + '">' + [name] + '</a>'
                ,-6 AS [Object_Type]
                ,mission_id AS [Object_Key]
                , '' AS [Public_Flag]
            FROM trip_mission Obj
            WHERE [name] LIKE '%' + @SearchEntry + '%' OR [description] LIKE '%' + @SearchEntry + '%'

            UNION

            SELECT 'Assignments'
                ,@AnchorHTMLStart + @AssignmentsPage + '&assignmentID=' + CONVERT(VARCHAR, Obj.assignment_id) + '">' + title + '</a>'
                ,13 AS [Object_Type]
                ,assignment_id AS [Object_Key]
                , '' AS [Public_Flag]
            FROM asgn_assignment Obj
            WHERE title LIKE '%' + @SearchEntry + '%' OR [description] LIKE '%' + @SearchEntry + '%'
            ) Results
            -- -1 Comms, -2 Lists, -3 Attendance, -4 Forms, -5 Groups, -6 Mission Trips
        LEFT JOIN secu_v_person_permissions P ON (
                P.Object_type = Results.[Object_Type]
                AND p.Object_key = Results.[Object_Key]
                AND p.operation_type = 0 -- Read Permission
                AND p.person_id = @CurrentUser
                AND P.organization_id = @OrganizationID
                ) -- Find out which objects the user had direct read access to
       WHERE
        (( p.Object_type IS NOT NULL -- Where the user has read permission
          OR Results.[Object_Type] < -1 -- Return all objects that don't directly use permissions minus Communications
          OR (Results.[Object_Type] = -1 AND @CurrentUser = Results.[Object_Key]) -- Return Communications if they were created by the CurrentUser executing the search
        )
        OR @IsGlobalAdmin = 1) -- If the user is a global admin return all results
        AND ((@Filter = '') OR (Results.Object_Type = CONVERT(INT,@Filter)))-- Allows Filter to be NULL or Filter on Object Type
        AND (Results.Public_Flag = 1 OR @IsGlobalAdmin = 1)
        ORDER BY Item_Type
END