· 8 years ago · Jun 06, 2018, 07:22 PM
1insert into DocumentTypes(Code, DisplayName, InputDate, IsActive) values ('SchoolOpenRoute', 'School Open Route', getdate(), 1)
2
3CREATE TABLE [dbo].[NotificationSignup](
4 Id int IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
5 NotificationId int,
6 PersonNumber nvarchar(150),
7 InputDate datetime default getdate()
8)
9GO
10
11ALTER proc [dbo].[u_NotificationSignupList]
12 @NotificationId int
13as
14begin
15 select
16 a.Id,
17 a.NotificationId,
18 a.PersonNumber,
19 b.LAST_NAME,
20 b.FIRST_NAME
21 from
22 NotificationSignup a
23 inner join [SQL3].[DATAFLEX].[dbo].[EMPLOYEE] b on a.PersonNumber = b.PERSON#
24 where
25 a.NotificationId = @NotificationId
26end
27go
28
29alter proc [dbo].[u_NotificationSignupInsert]
30 @NotificationId int,
31 @PersonNumber nvarchar(150)
32as
33begin
34 if (
35 not exists(
36 select Id
37 from NotificationSignup
38 where
39 Notificationid = @NotificationId
40 and PersonNumber = @PersonNumber
41 )
42 )
43 begin
44 insert into NotificationSignup(NotificationId, PersonNumber) values(@NotificationId, @PersonNumber)
45 select scope_identity() as Id
46 end
47 else
48 begin
49 select -9 as Id
50 end
51end
52go
53
54alter proc [dbo].[u_NotificationSignupDelete]
55 @Id int
56as
57begin
58 delete from NotificationSignup where Id = @Id
59end
60go
61
62ALTER proc [dbo].[u_NotificationList]
63 @BeginDate datetime = '',
64 @Name varchar(max) = '',
65 @LimitDateRange bit = 0,
66 @ShowPast bit = 1,
67 @PersonNumber varchar(150) = null
68as
69begin
70 Set NoCount On
71
72 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
73
74 Select
75 a.Id,
76 a.NotificationTypeId,
77 a.EnteredByAdministrativeUserId,
78 a.Name,
79 a.StartDate,
80 a.EndDate,
81 a.InputDate,
82 b.DisplayName as Type,
83 c.LoginName as InputBy,
84 d.DocumentKey,
85 case
86 when exists(select Id from NotificationSignup where a.Id = NotificationId and PersonNumber = @PersonNumber) then 1
87 else 0
88 end as HasSignedUp
89 from
90 Notifications a Inner Join DocumentTypes b on a.NotificationTypeId = b.Id
91 Inner Join AdministrativeUsers c on a.EnteredByAdministrativeUserId = c.ID
92 left outer join Documents d on a.NotificationTypeId = d.DocumentTypeId and a.Id = d.Refnum
93 left outer join NotificationSignup e on a.Id = e.Notificationid and e.PersonNumber = @PersonNumber
94 where
95 (a.Name LIKE '%' + @Name + '%' or @Name = '' or @Name is null)
96 AND ((a.StartDate <= @BeginDate or NULLIF(@BeginDate,'') is null) OR @LimitDateRange = 0)
97 AND (a.EndDate > GetDate() OR @ShowPast = 1)
98 Order by a.InputDate
99end
100go
101
102--insert into [NotificationSignup] (Notificationid, PersonNumber) values (168, 'AASCN')
103--insert into [NotificationSignup] (Notificationid, PersonNumber) values (168, 'AASCP')
104
105--[u_NotificationSignupList] 168
106
107-- [u_NotificationSignupInsert] 168, '00188'
108
109-- u_NotificationList '4/27/2018', null, true, false, '00188'
110
111--delete from NotificationSignup where PersonNumber = '00188'