X2qGxrvK

1if exists(select * from sysindexes where object_name(id) = 'TRP_PE_Detail' AND name = 'IDX_TRP_PE_PersonPK_PersonNo')
2BEGIN
3
4DROP INDEX [IDX_TRP_PE_PersonPK_PersonNo] ON [dbo].[TRP_PE_Detail]
5END
6
7
8GO
9/****** Object: Index [IDX_TRP_PE_PersonPK_PersonNo] Script Date: 25/07/2013 14:20:53 ******/
10CREATE NONCLUSTERED INDEX [IDX_TRP_PE_PersonPK_PersonNo] ON [dbo].[TRP_PE_Detail]
11(
12[SiteFK] ASC
13)
14INCLUDE ( [PersonPK], [PersonNo]) ON [PRIMARY]
15GO
16
17if exists(select * from sysobjects where name = 'MembershipStatus' and xType = 'V')
18BEGIN
19DROP VIEW [dbo].[MembershipStatus]
20END
21GO
22
23CREATE VIEW [dbo].[MembershipStatus]
24
25AS
26
27SELECT SiteNo, PersonNo AS MemberNo, MEM.SeqNo AS MembershipSeqNo, MEM.AddSeqNo AS MembershipAddSeqNo, LKP.Code AS MembershipItem, 
28CASE WHEN DATEDIFF(d, DateJoined, GETDATE()) < 0 THEN 1 ELSE
29CASE [Status]
30WHEN 'Live' THEN 7
31WHEN 'Frozen' THEN 7
32WHEN 'Expired' THEN 5
33WHEN 'Void' THEN 2
34WHEN 'Cancelled' THEN 4
35WHEN 'Suspended' THEN 6
36ELSE [Status]
37END END AS MembershipStatus,
38DateJoined StartDate, 
39dbo.MinDate(NextRenewalDate, Cancelled.StartDate, 
40CASE WHEN MEM.[Status] = 'VOID'
41THEN ISNULL(Voided.StartDate, '30 Dec 1899')
42ELSE ISNULL(Transferred.StartDate, '30 Dec 1899') END) AS EndDate
43FROM TRP_PE_Membership AS MEM 
44JOIN TRP_ME_PriceStructure ON MembershipPriceStructureFK = MembershipPriceStructurePK 
45JOIN TRP_ME_Lookup LKP ON MembershipLookupFK = MembershipLookupPK 
46JOIN TRP_PE_Detail ON PersonFK = PersonPK 
47JOIN TRP_SD_Sites ON SiteFK = SitePK LEFT JOIN dbo.TRP_PE_MembershipHistory Voided
48ON MEM.PersonMembershipPK = Voided.PersonMembershipFK
49AND Voided.HistoryAction = 'VOIDED'
50AND Voided.Active = 1 LEFT JOIN dbo.TRP_PE_MembershipHistory Transferred
51ON MEM.PersonMembershipPK = Transferred.PersonMembershipFK
52AND Transferred.HistoryAction = 'TRANSFERRED'
53AND Transferred.Active = 1 LEFT JOIN dbo.TRP_PE_MembershipHistory Cancelled
54ON MEM.PersonMembershipPK = Cancelled.PersonMembershipFK
55AND Cancelled.HistoryAction = 'CANCELLED'
56AND Cancelled.Active = 1
57
58GO
59
60if exists(select * from sysobjects where name = 'MemberStatus' and xType = 'V')
61BEGIN
62DROP VIEW [dbo].[MemberStatus]
63END
64
65GO
66
67CREATE VIEW [dbo].[MemberStatus]
68
69/*
70JTP 24/9/2009
71returns the current persons state using the membershipstatus table.
72needs to left join to member table so that partials can be detected
73
74*/
75
76AS
77
78select [SITE].SiteNo SiteNo, PersonNo AS MemberNo, ISNULL(MAX(membershipStatus),0) memberStatus 
79from TRP_PE_Detail DET
80INNER JOIN TRP_SD_Sites [SITE] ON SiteFK = SitePK
81left join membershipStatus MS on DET.PersonNo = MS.MemberNo
82GROUP BY [SITE].SiteNo,PersonNo
83
84GO
85
86if exists(select * from sysobjects where name = 'SPU_PE_FindPerson' and xType = 'P')
87BEGIN
88DROP PROCEDURE [dbo].[SPU_PE_FindPerson]
89END
90
91GO
92
93CREATE PROCEDURE [dbo].[SPU_PE_FindPerson] 
94@SiteNo SMALLINT= 0,
95@LoginSiteNo smallint = 0,
96@PersonNo INT = 0,
97@Forenames VARCHAR(35) = '',
98@Surname VARCHAR(35) = '',
99@AddressLine1 VARCHAR(35) = '',
100@PostCode VARCHAR(10) = '',
101@HomeTelNo VARCHAR(25) = '',
102@MemberCasual SMALLINT, -- JTP 19/9/2006. amended to include prospects in results using bitwise operators to determine the results to return
103@AllMembers bit, --JTP 13/2/2006. Parameter added to match back office search
104@MaxRowCount int = 0, --JTP 31/7/2007. sets a maximum rowcount to be returned
105@MaxRowsExceeded bit OUTPUT,
106@CardNumber varchar(60) = '',
107@MembershipsView BIT = 0, --If 1 the name of the membership latest membership is returned 
108@AllMembersShowStatus BIT =0, --If an all members search is completed, and this is 1 the members status will be returned
109@ReturnMembershipLink BIT =0, --If 1 returns a link column that can be clicked to trigger membership details being displayed
110@ReturnEarliestMembership BIT =0, -- when set to 1 the most recent membership is returned in a membershipView search, when 0 the oldest (Still live) membership is returned
111@MembershipsViewShowOnlyLiveMemberships BIT = 0 -- (ignored unlesss @AllMembers = 1 & @MembershipsView =1) if 1 only the names of live memberships are returned 0 =
112
113/** JTP 10 august 2007 
114handled null titles 
115JTP 24/8/2007 temporarily removed the link the membership types so searches return all
116members. This is to speed up searches when integrated with dimension
117JTP 6/9/2007 added card number to search options
118
119CJS 12 Nov 2007 Include external references through common view (if it exists)
120
121If only one external result is found then this will not be automatically selected.
122
123NOTE the PersonType Value is now used in Advantage to identify the type of person found. If
124this is an X then the external person will be added as a new person when they are selected.
125A membership may also be added if the user type value is mapped to a membership.
126
127
128JTP 21/11/2007 Order of names changed to surname, forename title (issue 3050)
129JTP 23/01/2008 added login site to parameters so casual searches aren't completed for other sites
130JTP 7/7/2008 3865. fixed non active person search
131
132CJS 11 July 2008 Change external data working table postcode from 8 to 10 characters
133CJS 11 July 2008 Don't check for external bit in the SELECT as it will reference the linked server and
134populate the cache (20 seconds for surrey) even if the external data bit isn't set!
135
136JTP 4/10/2012 updated to optionally return the latest membership name, required introduction of the 
137#results table and associated upates.
138
139JTP 1/11/2012 - TFS 945, fixed membership order by when more than one membership issued in a day 
140JTP 25/7/2013 - TFS1971 - dimension integrated version of this procedure produced 
141**/
142
143AS
144
145DECLARE @MemberBit tinyint 
146DECLARE @CasualBit tinyint 
147DECLARE @ProspectBit tinyint 
148DECLARE @ExternalBit tinyint
149
150SELECT @MemberBit = 1
151SELECT @CasualBit = 2
152SELECT @ProspectBit = 4
153SELECT @ExternalBit = 8
154
155
156CREATE TABLE #Results(
157SiteNo integer, 
158MemberNo integer, 
159CardNo varchar(60), 
160name varchar(80),
161Address1 varchar(35), 
162Postcode varchar(10), 
163HomeTelNo varchar(25),
164PersonType CHAR(1), 
165Source varchar(8), 
166Reference varchar(20)
167
168)
169
170-- Check if the external members view exists before populating
171IF EXISTS(select * from dbo.sysobjects where id = object_id(N'[dbo].[V_API_ExternalMembers]') and OBJECTPROPERTY(id, N'IsView') = 1)
172AND (@MemberCasual & @ExternalBit = @ExternalBit) --external bit selected
173
174BEGIN
175
176
177INSERT INTO
178#Results(SiteNo, MemberNo, CardNo, name,Address1, Postcode, HomeTelNo, PersonType, Source, Reference)
179SELECT
180DISTINCT
181SiteNo, 
182MemberNo, 
183(Source + '/' + Reference), 
184(ISNULL(Surname, '') + ', ' + ISNULL(Forenames, '')),
185Address1 ,
186Postcode ,
187HomeTelNo , 
188'X',
189Source, 
190Reference -- return external source and reference
191FROM dbo.V_API_ExternalMembers -- view all external members
192INNER JOIN TRP_SD_Sites SIT ON SIT.SiteNo = SiteNo
193
194WHERE (SiteNo = @SiteNo)
195AND MemberNo = 0 -- only if not already a member (will find elsewhere)
196AND (CardNo = @CardNumber OR @CardNumber = '') -- If a card has been pre-issued
197AND (Forenames LIKE(@Forenames + '%') OR @Forenames = '')
198AND (Surname LIKE(@Surname + '%') OR @Surname = '')
199AND (Address1 LIKE(@AddressLine1 + '%') OR @AddressLine1 = '')
200AND (PostCode LIKE(@PostCode + '%') OR @PostCode = '')
201AND (HomeTelNo LIKE('%' + @HomeTelNo + '%') OR @HomeTelNo = '')
202
203END
204--Endif
205
206
207IF @MemberCasual & @memberBit = @memberBit
208BEGIN
209
210INSERT INTO #Results
211SELECT 
212SIT.SiteNo, 
213DET.PersonNo, 
214DET.CardNo, 
215isnull(DET.Surname,'') +', ' + isnull(DET.Forenames,'') + ' ' + isnull(TITL.Title,''),
216ADDR.Address1, 
217ADDR.PostCode, 
218DET.HomeTelNo, 
219'M', 
220'TLMS', 
221''
222
223FROM TRP_PE_Detail DET
224INNER JOIN TRP_SD_Sites SIT on SIT.SitePK = DET.SiteFK
225INNER JOIN TRP_PE_Address ADDR ON DET.PersonPK = ADDR.PersonFK AND ADDR.AddType = 'P'
226LEFT JOIN TRP_EW_Title TITL on DET.Title = CAST(TITL.TitlePK AS CHAR(10))
227INNER JOIN MemberStatus ON MemberStatus.MemberNo = DET.PersonNo AND MemberStatus.SiteNo = SIT.SiteNo
228
229WHERE (SIT.SiteNo = @SiteNo OR @SiteNo = 0)
230AND (DET.PersonNo = @PersonNo OR @PersonNo = 0)
231AND (DET.Forenames LIKE @Forenames + '%' OR DET.Forenames IS NULL)
232AND (DET.Surname LIKE @Surname + '%' OR DET.Surname IS NULL)
233AND (ADDR.Address1 LIKE @AddressLine1 + '%' OR ADDR.Address1 IS NULL)
234AND (ADDR.PostCode LIKE @PostCode + '%' OR ADDR.PostCode IS NULL)
235AND (DET.HomeTelNo LIKE '%' + @HomeTelNo + '%' OR DET.HomeTelNo IS NULL)
236AND (DET.CardNo = @CardNumber OR @CardNumber = '')
237AND (MemberStatus.memberStatus = 7 OR @AllMembers = 1 )
238AND (DET.PersonType = 1)
239
240END
241
242
243IF @MemberCasual & @CasualBit = @casualBit
244BEGIN
245
246INSERT INTO #Results
247SELECT 
248casVenue, 
249casCasualNo, 
250'Casual', 
251IsNull(RTrim(casSurname), '') + ', ' + IsNull(RTrim(casForenames), '') + ' ' + LTRIM(IsNull(TITL.Title, '') ),
252casAddress1, 
253casPostCode, 
254casHomeTel, 
255'C', 
256'TLMS', 
257''
258
259FROM CasualCustomer 
260LEFT JOIN TRP_EW_Title TITL ON casTitle = TITL.Title
261--JTP 3199 23/1/2008 - only include site number
262WHERE (casVenue = @LoginSiteNo)
263AND (casCasualNo = @PersonNo OR @PersonNo = 0)
264AND (casForenames LIKE @Forenames + '%' OR casForenames IS NULL)
265AND (casSurname LIKE @Surname + '%' OR casSurname IS NULL)
266AND (casAddress1 LIKE @AddressLine1 + '%' OR casAddress1 IS NULL)
267AND (casPostCode LIKE @PostCode + '%' OR casPostCode IS NULL)
268AND (casHomeTel LIKE '%' + @HomeTelNo + '%' OR casHomeTel IS NULL)
269
270
271END
272
273IF @MemberCasual & @ProspectBit = @ProspectBit
274BEGIN
275
276INSERT INTO #Results
277SELECT distinct
278
279SIT.SiteNo, 
280DET.PersonNo, 
281DET.CardNo, 
282isnull(DET.Surname,'') +', ' + isnull(DET.Forenames,'') + ' ' + isnull(TITL.Title,''),
283ADDR.Address1, 
284ADDR.PostCode, 
285DET.HomeTelNo, 
286'P', 
287'TLMS', 
288''
289
290FROM TRP_PE_Detail DET
291INNER JOIN TRP_SD_Sites SIT on SIT.SitePK = DET.SiteFK
292INNER JOIN TRP_PE_Address ADDR ON DET.PersonPK = ADDR.PersonFK AND ADDR.AddType = 'P'
293LEFT JOIN TRP_EW_Title TITL on DET.Title = CAST(TITL.TitlePK AS CHAR(10))
294
295
296WHERE (SIT.SiteNo = @SiteNo OR @SiteNo = 0)
297AND (DET.PersonNo = @PersonNo OR @PersonNo = 0)
298AND (DET.Forenames LIKE @Forenames + '%' OR DET.Forenames IS NULL)
299AND (DET.Surname LIKE @Surname + '%' OR DET.Surname IS NULL)
300AND (ADDR.Address1 LIKE @AddressLine1 + '%' OR ADDR.Address1 IS NULL)
301AND (ADDR.PostCode LIKE @PostCode + '%' OR ADDR.PostCode IS NULL)
302AND (DET.HomeTelNo LIKE '%' + @HomeTelNo + '%' OR DET.HomeTelNo IS NULL)
303AND (DET.CardNo = @CardNumber OR @CardNumber = '')
304AND (DET.PersonType = 0)
305
306
307END
308
309DECLARE @ResultCount INT
310
311SELECT @ResultCount = COUNT(*) FROM #Results
312
313
314IF @ResultCount > @MaxRowCount 
315BEGIN
316SELECT @maxrowsExceeded = 1
317END
318ELSE
319BEGIN
320SELECT @maxrowsExceeded = 0
321END
322
323
324IF @MembershipsView = 1
325BEGIN
326
327
328SELECT TOP (@MaxRowCount) 
329
330R1.SiteNo , 
331R1.MemberNo [Person], 
332CardNo [Card], 
333Name ,
334Address1 [Address] , 
335Postcode , 
336HomeTelNo [Home Tel] ,
337PersonType , 
338[Source] , 
339Reference,
340
341[Membership] = CASE WHEN R1.PersonType = 'M'
342THEN
343CASE WHEN @ReturnEarliestMembership = 0 THEN
344(SELECT TOP(1) ISNULL( Mi.[Description],'')
345FROM #Results R2 
346
347LEFT JOIN MembershipStatus MS ON MS.MemberNo = R2.MemberNo AND MS.SiteNo = R2.SiteNo AND R2.PersonType = 'M'
348LEFT JOIN TRP_ME_Lookup MI ON MI.code = MS.MembershipItem
349AND (MS.MembershipStatus = 7 OR (@AllMembers = 1 AND @MembershipsViewShowOnlyLiveMemberships = 0))
350WHERE R2.SiteNo = R1.SiteNo AND R2.MemberNo = R1.MemberNo
351ORDER BY [Name],MS.MembershipStatus DESC, MS.MembershipSeqNo DESC) 
352ELSE
353(SELECT TOP(1) ISNULL( Mi.[Description],'')
354FROM #Results R2 
355
356LEFT JOIN MembershipStatus MS ON MS.MemberNo = R2.MemberNo AND MS.SiteNo = R2.SiteNo AND R2.PersonType = 'M'
357LEFT JOIN TRP_ME_Lookup MI ON MI.Code = MS.MembershipItem
358AND (MS.MembershipStatus = 7 OR(@AllMembers = 1 AND @MembershipsViewShowOnlyLiveMemberships = 0))
359WHERE R2.SiteNo = R1.SiteNo AND R2.MemberNo = R1.MemberNo
360ORDER BY [Name],MS.MembershipStatus DESC, MS.MembershipSeqNo ASC) --oldest membership first
361END
362ELSE
363'' --if it's not a member (person type <> 'M') don't attempt a membership lookup
364END,
365[Status] = CASE WHEN R1.PersonType = 'M'
366THEN
367CASE WHEN @AllMembers = 1 
368THEN
369CASE WHEN @AllMembersShowStatus = 1 
370THEN
371(SELECT MSL.StatusDescription FROM Memberstatus MS 
372LEFT JOIN MembershipStatusLookup MSL ON MSL.StatusPK = MS.memberStatus
373WHERE MS.MemberNo = R1.MemberNo AND MS.SiteNo= R1.SiteNo)
374ELSE
375'' -- all members search but status is not requested
376END
377ELSE
378'Live' --Only search for live members so don't waste time calculating the best status (it's live or it wouldn't be in #results)
379END
380ELSE
381'' --if it's not a member (person type <> 'M') don't attempt a status lookup
382END,
383[Memberships] = CASE WHEN R1.PersonType = 'M'
384THEN
385CASE WHEN @ReturnMembershipLink = 1 
386THEN 
387'TLMS_Link:Details' 
388ELSE 
389'' 
390END 
391ELSE
392'' --if it's not a member (person type <> 'M') don't return a memberships link
393END
394FROM #Results R1
395
396ORDER BY [Name]
397
398
399END
400ELSE
401BEGIN
402
403
404SELECT TOP (@MaxRowCount) 
405
406SiteNo , 
407MemberNo [Person], 
408CardNo [Card], 
409Name ,
410Address1 [Address] , 
411Postcode , 
412HomeTelNo [Home Tel] ,
413PersonType , 
414[Source] , 
415Reference,
416'' [Membership], 
417[Status] = CASE WHEN #Results.PersonType = 'M'
418THEN
419CASE WHEN @AllMembers = 1 
420THEN
421CASE WHEN @AllMembersShowStatus = 1 
422THEN
423(SELECT MSL.StatusDescription FROM Memberstatus MS 
424LEFT JOIN MembershipStatusLookup MSL ON MSL.StatusPK = MS.memberStatus
425WHERE MS.MemberNo = #Results.MemberNo AND MS.SiteNo= #Results.SiteNo)
426ELSE
427'' -- all members search but status is not requested
428END
429ELSE
430'Live' --Only search for live members so don't waste time calculating the best status (it's live or it wouldn't be in #results)
431END
432ELSE
433'' --if it's not a member (person type <> 'M') don't attempt a status lookup
434END , 
435[Memberships] = CASE WHEN #Results.PersonType = 'M'
436THEN
437CASE WHEN @ReturnMembershipLink = 1 
438THEN 
439'TLMS_Link:Details' 
440ELSE 
441'' 
442END 
443ELSE
444'' --if it's not a member (person type <> 'M') don't return a memberships link
445END
446FROM #Results
447
448ORDER BY [Name]
449END
450DROP TABLE #Results
451
452 
453
454GO