EnzmtZL0

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