Patriot Software Solutions, Inc.

alter Procedure [dbo].[qRptJournalExtract] --694848

(

    @prmSearchID Int

)

As

SET NOCOUNT ON

DECLARE @strSQL        VarChar(8000)

DECLARE @strWhere    VarChar(8000)

DECLARE @strDelim    VarChar(10)

DECLARE @strFieldValue    VarChar(4000)

SELECT @strDelim = ''

SELECT @strWhere = ''

DECLARE curFields CURSOR READ_ONLY FOR

SELECT    FieldValue

FROM     tblSearchFields

WHERE     tblSearchFields.SearchID = @prmSearchID AND

       (CHARINDEX('tlkActivityCodes',Fieldvalue) > 0 OR

        CHARINDEX('tblJournals',Fieldvalue) > 0 OR

        CHARINDEX('tlkJournalTypeCodes',Fieldvalue) > 0 OR

        CHARINDEX('tlkJournalClassifications',Fieldvalue) > 0 

 )

OPEN curFields

FETCH NEXT FROM curFields INTO @strFieldValue

WHILE @@FETCH_STATUS = 0

BEGIN

    IF      

        (CHARINDEX('tlkActivityCodes',@strFieldvalue) > 0 OR

        CHARINDEX('tblJournals',@strFieldvalue) > 0 OR

        CHARINDEX('tlkJournalTypeCodes',@strFieldvalue) > 0 OR

        CHARINDEX('tlkJournalClassifications',@strFieldvalue) > 0 )

     --CHARINDEX('tblpaymentappeals',@strFieldvalue) > 0

          BEGIN

        SELECT @strWhere = @strWhere + @strDelim + '(' + @strFieldValue +')'

        SELECT @strDelim = ' AND '

     END

    FETCH NEXT FROM curFields INTO @strFieldValue

END

CLOSE curFields

DEALLOCATE  curFields

SELECT @strSQL = ''

SELECT @strSQL = @strSQL + 'SELECT tblpeople.personID, '

SELECT @strSQL = @strSQL + 'dbo.tblPeople.PrefFirstName AS [tblPeople.PrefFirstName], dbo.tblPeople.MiddleName AS [tblPeople.MiddleName], dbo.tblPeople.LastName AS [tblPeople.LastName], '

SELECT @strSQL = @strSQL + 'PrefChapter.ChapterName AS PrefChapterName, PrefChapter.SchoolShortName AS PrefSchoolShortName, dbo.tblPeople.Prefix AS [tblPeople.Prefix], dbo.tblPeople.FirstName AS [tblPeople.FirstName], '

SELECT @strSQL = @strSQL + 'dbo.tblMemberships.MembershipTypeCode AS [tblMemberships.MembershipTypeCode], '

SELECT @strSQL = @strSQL + 'dbo.tblMemberships.MembershipStatusCode AS [tblMemberships.MembershipStatusCode], '

SELECT @strSQL = @strSQL + 'dbo.tblEmails.EmailAddress AS [tblEmails.EMailAddress], '

SELECT @strSQL = @strSQL + 'dbo.GetPhone(dbo.tblPeople.PersonID, ''HOME'') AS [Home Phone], '

SELECT @strSQL = @strSQL + 'dbo.GetPhone(dbo.tblPeople.PersonID, ''WORK'') AS [Work Phone], dbo.GetPhone(dbo.tblPeople.PersonID, ''CELL'') AS [Cell Phone], '

SELECT @strSQL = @strSQL + 'dbo.tblMemberships.InitDate AS [tblMemberships.InitDate], dbo.tblMemberships.GradDate AS [tblMemberships.GradDate], '

SELECT @strSQL = @strSQL + 'dbo.tblAddresses.Address1 AS [tblAddresses.Address1], '

SELECT @strSQL = @strSQL + 'dbo.tblAddresses.Address2 AS [tblAddresses.Address2], dbo.tblAddresses.City AS [tblAddresses.City], dbo.tblAddresses.State AS [tblAddresses.State], ' 

SELECT @strSQL = @strSQL + 'dbo.tblAddresses.Zip AS [tblAddresses.Zip], dbo.tblAddresses.CountryCode AS [tblAddresses.CountryCode], '

SELECT @strSQL = @strSQL + 'dbo.tblPeople.SpouseFirstName AS [tblPeople.SpouseFirstName], '

SELECT @strSQL = @strSQL + 'dbo.tblPeople.SpouseLastName AS [tblPeople.SpouseLastName],'

SELECT @strSQL = @strSQL + 'dbo.tblMemberships.NationalSeqNum AS BadgeNum, '

SELECT @strSQL = @strSQL + 'tblJournals.JournalClassificationCode, tblJournals.JournalTypeCode, tblJournals.JournalDesc, '

SELECT @strSQL = @strSQL + 'tlkActivityCodes.ActivityName,tblJournals.ActivityBeginDt, dbo.fn_IsPersonDeceased(tblPeople.PersonId) as [IsDeceased], '

SELECT @strSQL = @strSQL + 'tblAddresses.AddressStatusCode '

SELECT @strSQL = @strSQL + 'FROM tblJournals '

SELECT @strSQL = @strSQL + 'Join tblPeople on tblPeople.PersonID = tblJournals.PersonID '

SELECT @strSQL = @strSQL + 'Join tblSearchResults ON tblSearchResults.ID = tblPeople.PersonID '

SELECT @strSQL = @strSQL + 'LEFT OUTER Join  dbo.tblAddresses WITH (nolock) ON dbo.tblAddresses.PersonID = dbo.tblPeople.PersonID AND dbo.tblAddresses.Preferred = 1 LEFT OUTER JOIN '

SELECT @strSQL = @strSQL + 'dbo.tlkCountries WITH (nolock) ON dbo.tlkCountries.CountryCode = dbo.tblAddresses.CountryCode LEFT OUTER JOIN ' 

SELECT @strSQL = @strSQL + 'dbo.tblEmails WITH (nolock) ON dbo.tblEmails.PersonID = dbo.tblPeople.PersonID AND dbo.tblEmails.Preferred = 1 LEFT OUTER JOIN ' 

SELECT @strSQL = @strSQL + 'dbo.tblMemberships WITH (nolock) ON dbo.tblMemberships.PersonID = dbo.tblPeople.PersonID AND dbo.tblMemberships.Preferred = 1 LEFT OUTER JOIN '

SELECT @strSQL = @strSQL + 'dbo.tblChapterAffiliations WITH (nolock) ON dbo.tblChapterAffiliations.MembershipID = dbo.tblMemberships.MembershipID AND '

SELECT @strSQL = @strSQL + '(dbo.tblChapterAffiliations.AffiliationTypeCode = ''INIT'' OR ' 

SELECT @strSQL = @strSQL + 'dbo.tblChapterAffiliations.AffiliationTypeCode = ''ASSOC'') LEFT OUTER JOIN '

SELECT @strSQL = @strSQL + 'dbo.tblChapters WITH (nolock) ON dbo.tblChapters.ChapterID = dbo.tblChapterAffiliations.ChapterID LEFT OUTER JOIN '

SELECT @strSQL = @strSQL + 'dbo.tblChapterAffiliations AS PCA WITH (nolock) ON PCA.MembershipID = dbo.tblMemberships.MembershipID AND PCA.Preferred = 1 LEFT OUTER JOIN '

SELECT @strSQL = @strSQL + 'dbo.tblChapters AS PrefChapter WITH (nolock) ON PrefChapter.ChapterID = PCA.ChapterID '

SELECT @strSQL = @strSQL + 'LEFT JOIN tlkActivityCodes on tblJournals.ActivityCode = tlkActivityCodes.ActivityCode '

SELECT @strSQL = @strSQL + 'Left JOIN tlkJournalTypeCodes on tlkJournalTypeCodes.JournalTypeCode = tblJournals.JournalTypeCode ' 

SELECT @strSQL = @strSQL + 'LEFT JOIN tlkJournalClassifications on tblJournals.JournalClassificationCode = tlkJournalClassifications.JournalClassificationCode '

SELECT @strSQL = @strSQL + 'WHERE tblSearchResults.SearchID = ' + CAST(@prmSearchID As VarChar(20))

If @strWhere <> '' 

BEGIN

    SELECT @strSQL = @strSQL  + ' AND ' + @strWhere

END

PRINT(@strSQL)

EXEC(@strSQL)