USP_CUSTOMFORMENTRY_SEARCHBYUSERNAME

CREATE procedure dbo.USP_CUSTOMFORMENTRY_SEARCHBYUSERNAME(
                            @FORMFIRSTNAME varchar(50),
                            @FORMLASTNAME varchar(100),
                            @USERNAME varchar(50),
                            @CLIENTSITESID int,
                            @CLIENTSITEFORMCREATEDON int,
                            @FORMELEMENTID int,
                            @USEDATEACCESSEDFILTER bit,
                            @ACCESSEDSTARTDATE date,
                            @ACCESSEDENDDATE date,
                            @USEDATECOMPLETEDFILTER bit,
                            @COMPLETEDSTARTDATE date,
                            @COMPLETEDENDDATE date,
                            @lBound int,
                            @uBound int)
as
begin
    /*
        Used in BBNC -> Forms - Search feature. Pass in the filter values and get a list of the corresponding transactions.
        lBound and uBound can also be passed in to return the requested subset for paging purposes (i.e. first record to return to last record).

        Spec located at: \Blackbaud\AppFx\ContentManagement\Catalog\CustomForms\USP_CUSTOMFORMENTRY_SEARCHBYUSERNAME.xml
        SPWrapped to: \ClassicCMS\Web\Content\SPWrap\CodeGen\USP_CUSTOMFORMENTRY_SEARCHBYUSERNAME.vb

        Parameter notes:
            - To not use a parameter to filter results:
                - varchars will accept blanks or nulls
                - ints, set them to -1
                - dates, exclude using the associated flags
    */

    set nocount on;

    -- MaryR 10.11.2012 Haven't figured out why yet, but we have sporadic cases where the form that has just been submitted is not updated in cache. This isn't

    --   a huge problem on the Data tab since it mainly means that one form is going to show up as Anonymous, but it is a real problem on the Dashboards where 

    --   it can put the totals off. So I'm putting all of these calls back in. Since the trigger is now handling the bulk of the work, these other calls should

    --   just be handling stragglers, and the sproc is optimized to only process records that need it, so performance shouldn't be an issue.

    -- MaryR 06.25.2012 Bug #216946  Hitting the UpdateCache USP too infrequently caused timeouts that prevented the Data tab from loading, so caching is now 

    --   handled as entries are added and updated (TR_CUSTOMFORMENTRY_INSERT_UPDATE) instead of here where we don't know how many records it might have to process.

    -- Ensure the cache is up-to-date

    exec dbo.USP_CUSTOMFORMDASHBOARD_UPDATEFORMENTRYCACHE

    if Len(ltrim(rtrim(@FORMFIRSTNAME))) = 0
        set @FORMFIRSTNAME = null;

    if Len(ltrim(rtrim(@FORMLASTNAME))) = 0
        set @FORMLASTNAME = null;

    if Len(ltrim(rtrim(@USERNAME))) = 0
        set @USERNAME = null;

    if @lBound is null
        set @lBound = 0;

    if @uBound is null
        set @uBound = 0;

    declare @FormInfo table (FormEntryGuid uniqueidentifier,
                            EntryState varchar(50),
                            FirstName varchar(50),
                            LastName varchar(100),
                            FormTitle varchar(200),
                            ChangeDate datetime,
                            PaymentMethod varchar(100));

    insert into @FormInfo 
        (FormEntryGuid,
        EntryState,
        FirstName,
        LastName,
        FormTitle,
        ChangeDate,
        PaymentMethod)

    SELECT 
        FormEntry.EntryID, 
        statusAttribute.AttributeValue EntryState,
        FirstName = CASE FormDataSource.DataSource
                        WHEN '132E3702-0207-4d32-AA6F-7B265B1DC7D0' THEN CachedInfoFromXML.StudentFirstName --Online Admissions

                        WHEN '1927B45A-F7F8-44d7-BF45-66899B8C7D43' THEN CachedInfoFromXML.StudentFirstName --Online Reenrollment

                        ELSE ClientUser.FirstName
                    END,
        LastName = CASE FormDataSource.DataSource
                        WHEN '132E3702-0207-4d32-AA6F-7B265B1DC7D0' THEN CachedInfoFromXML.StudentLastName --Online Admissions

                        WHEN '1927B45A-F7F8-44d7-BF45-66899B8C7D43' THEN CachedInfoFromXML.StudentLastName --Online Reenrollment

                        ELSE ClientUser.LastName
                    END,
        FormElement.Title, 
        statusAttribute.ChangeDate,
        PaymentMethod = CASE FormEntry.EntryState
                            WHEN 2 THEN FormEntry.PaymentMethod
                            ELSE 'None'
                        END

    FROM dbo.CustomFormEntry FormEntry
        INNER JOIN dbo.CustomFormEntryAttribute statusAttribute on FormEntry.EntryID = statusAttribute.EntryID 
        INNER JOIN dbo.CustomFormElement FormElement on FormEntry.FormGuid = FormElement.Guid 
        INNER JOIN dbo.CustomFormDataSource FormDataSource on FormElement.Guid = FormDataSource.FormGuid 
        INNER JOIN dbo.CustomFormSettings FormSettings on FormEntry.FormGuid = FormSettings.FormGUID 
        LEFT OUTER JOIN dbo.ClientUsers ClientUser on FormEntry.ClientUserID = ClientUser.ID
        LEFT OUTER JOIN dbo.CustomFormDashboardFormEntry CachedInfoFromXML on FormEntry.EntryID = CachedInfoFromXML.CustomFormEntryID

    WHERE FormEntry.EntryState = statusAttribute.AttributeType
        and (@CLIENTSITESID = -1 or FormEntry.ClientSitesID = @CLIENTSITESID)
        and (@CLIENTSITEFORMCREATEDON = -1 or FormSettings.ClientSitesID = @CLIENTSITEFORMCREATEDON)
        and (@FORMELEMENTID = -1 or FormElement.ID = @FORMELEMENTID)
        and (@USERNAME is null or ClientUser.UserName like '%' + @USERNAME + '%')
        and (@USEDATEACCESSEDFILTER = 0 or statusAttribute.ChangeDate BETWEEN @ACCESSEDSTARTDATE and @ACCESSEDENDDATE)
        and (@USEDATECOMPLETEDFILTER = 0 or CachedInfoFromXML.SubmitDate BETWEEN @COMPLETEDSTARTDATE and @COMPLETEDENDDATE)


    --Get rid of entries that don't match our name filters. If name filters are specified, anonymous entries should not show up either.

    --  The form.Name is null is to get rid of those.

    delete @FormInfo
    FROM @FormInfo form
    where (@FORMFIRSTNAME is not null and ((form.FirstName not like '%' + @FORMFIRSTNAME + '%') or (form.FirstName is null)))
        or (@FORMLASTNAME is not null and ((form.LastName not like '%' + @FORMLASTNAME + '%') or (form.LastName is null)))


    --PAGING

    if @uBound > 0 and @uBound >= @lBound
        begin
            SELECT dtOrdered.FormEntryGuid,
                dtOrdered.FormForName,
                dtOrdered.FormTitle,
                dtOrdered.ChangeDate,
                dtOrdered.EntryState,
                dtOrdered.PaymentMethod
            FROM
                (select TOP (@uBound)
                    form.FormEntryGuid,
                    form.FirstName + ' ' + form.LastName FormForName,
                    form.FormTitle,
                    form.ChangeDate,
                    form.EntryState,
                    form.PaymentMethod,
                    ROW_NUMBER() OVER(ORDER BY form.FormTitle) as RowNumber
                from @FormInfo form
                order by form.FormTitle
                ) dtOrdered
            WHERE dtOrdered.RowNumber between @lBound and @uBound
            ORDER BY dtOrdered.RowNumber
        end
    else
        begin
            SELECT
                form.FormEntryGuid,
                form.FirstName + ' ' + form.LastName FormForName,
                form.FormTitle,
                form.ChangeDate,
                form.EntryState,
                form.PaymentMethod
            FROM @FormInfo form
        end

    return
end