· 9 years ago · Nov 10, 2016, 04:06 PM
1GO
2/****** Object: StoredProcedure [dbo].[usp_MobileJobMonitor] Script Date: 09-11-2016 PM 02:28:21 ******/
3IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_MobileJobMonitor]') AND type in (N'P', N'PC'))
4DROP PROCEDURE [dbo].[usp_MobileJobMonitor]
5GO
6/****** Object: StoredProcedure [dbo].[usp_MobileJobMonitor] Script Date: 09-11-2016 PM 02:28:21 ******/
7SET ANSI_NULLS ON
8GO
9SET QUOTED_IDENTIFIER ON
10GO
11IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_MobileJobMonitor]') AND type in (N'P', N'PC'))
12BEGIN
13EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[usp_MobileJobMonitor] @TaskDescription varchar(60) = '''',
14@UnitNumber varchar(20) = '''',
15@ViewSelection varchar(30) = '''',
16@Incomplete varchar(12),
17@WorkOrderNumber numeric(10, 0) = 0,
18@ResourceType varchar(12) = '''',
19@ResourceGroup varchar(12) = '''',
20@Resource varchar(12) = '''',
21@CurrentUserCode varchar(10) = '''',
22@RestrictToUserAssets bit = 0,
23@CurrentPage int = 1,
24@PageSize int = 20,
25@TotalRecords int OUTPUT
26AS
27
28 DECLARE @qry nvarchar(max)
29 DECLARE @viewSelectionQry nvarchar(max) = ''''
30 DECLARE @unitRestrictionQry nvarchar(max) = ''''
31 DECLARE @orderByQry nvarchar(max) = '' WorkOrder DESC'';
32
33 IF @ViewSelection = ''CreationDate''
34 BEGIN
35 SET @orderByQry = '' CreationDate DESC, WorkOrder DESC, Task DESC ''
36 END
37 ELSE
38 IF @ViewSelection = ''RequiredByDate''
39 BEGIN
40 SET @viewSelectionQry = '' AND RequiredByDate > 0 ''
41 SET @orderByQry = '' RequiredByDate DESC, WorkOrder DESC, Task DESC ''
42 END
43 ELSE
44 IF @ViewSelection = ''StartDateAscending''
45 BEGIN
46 SET @viewSelectionQry = '' AND StartDate > 0 ''
47 SET @orderByQry = '' StartDate DESC, WorkOrder DESC, Task DESC ''
48 END
49 ELSE
50 IF @ViewSelection = ''Priority''
51 BEGIN
52 SET @viewSelectionQry = '' AND Priority <> '''''''' ''
53 SET @orderByQry = '' Priority ASC, WorkOrder DESC, Task DESC ''
54 END
55 ELSE
56 IF @ViewSelection = ''Asset''
57 BEGIN
58 SET @viewSelectionQry = '' AND Asset <> '''''''' ''
59 SET @orderByQry = '' Asset ASC, WorkOrder DESC, Task DESC''
60 END
61 ELSE
62 IF @ViewSelection = ''Task''
63 BEGIN
64 SET @orderByQry = '' WorkOrder DESC, Task DESC ''
65 END
66
67 IF @CurrentUserCode != ''''
68 AND @RestrictToUserAssets = 1
69 BEGIN
70 SET @unitRestrictionQry = '' AND ( Asset = '''''''' OR Asset IN (SELECT UUXUNI FROM IDUSUNP WHERE rtrim(ltrim(UUXUPR)) = rtrim(ltrim('''''' + @CurrentUserCode + '''''')) AND UUXRDN = 0) ) ''
71 END
72
73 SET @qry = N'' SELECT TOP '' + LTRIM(STR(@PageSize)) + '' *
74 FROM (
75 SELECT row_number() OVER (
76 ORDER BY '' + @orderByQry + ''
77 ) AS RowNumber
78 ,OverJobMonitorInner.*
79 ,COUNT(1) OVER () AS TotalRecords
80 FROM (
81 SELECT WorkOrder
82 ,Task
83 ,[Resource]
84 ,ResourceType
85 ,ResourceGroup
86 ,CreationDate
87 ,StartDate
88 ,RequiredByDate
89 ,[Priority]
90 ,TaskDescription
91 ,Asset
92 ,CompletionDate
93 ,[Time]
94 FROM vJobMonitor WHERE (( 1 = 1 ) '' + @viewSelectionQry + @unitRestrictionQry + ''
95 AND ( '''''' + @ResourceType + '''''' = '''''''' OR
96 (
97 rtrim(ltrim(ResourceType)) = rtrim(ltrim('''''' + @ResourceType + '''''')) AND
98 (
99 rtrim(ltrim('''''' + @ResourceGroup + '''''')) = '''''''' OR
100 (
101 rtrim(ltrim(ResourceGroup)) = rtrim(ltrim('''''' + @ResourceGroup + '''''')) AND
102 (
103 '''''' + @Resource + '''''' = '''''''' OR rtrim(ltrim(Resource)) = rtrim(ltrim('''''' + @Resource + ''''''))
104 )
105 )
106 )
107 )
108 )
109 AND (@WorkOrderNumber = 0 OR ([WorkOrder] <= @WorkOrderNumber)))
110 AND (@TaskDescription = '''''''' OR ([TaskDescription] LIKE @TaskDescription))
111 AND (@Incomplete <> ''''Incomplete'''' OR ([CompletionDate] = ''''0''''))
112 AND (@UnitNumber = '''''''' OR ([Asset] LIKE @UnitNumber))
113 ) OverJobMonitorInner
114 ) OverJobMonitorOuter
115 WHERE (
116 '' + LTRIM(STR(@PageSize)) + '' IS NULL
117 OR RowNumber > (( '' + LTRIM(STR(@CurrentPage)) + '' - 1) * '' + LTRIM(STR(@PageSize)) + '' )
118 ) ''
119
120 SET @qry = REPLACE(@qry, ''@TaskDescription'', '''''''' + ''%'' + @TaskDescription + ''%'' + '''''''');
121 SET @qry = REPLACE(@qry, ''@UnitNumber'', '''''''' + @UnitNumber + ''%'' + '''''''');
122 SET @qry = REPLACE(@qry, ''@WorkOrderNumber'', @WorkOrderNumber);
123 SET @qry = REPLACE(@qry, ''@Incomplete'', '''''''' + @Incomplete + '''''''');
124
125 CREATE TABLE #JobMonitorTempTable (
126 RowNumber int,
127 WorkOrder numeric(10, 0),
128 Task char(2),
129 [Resource] char(12),
130 ResourceType char(12),
131 ResourceGroup char(12),
132 CreationDate numeric(7, 0),
133 StartDate numeric(7, 0),
134 RequiredByDate numeric(7, 0),
135 [Priority] char(12),
136 TaskDescription char(60),
137 Asset char(20),
138 CompletionDate numeric(7, 0),
139 [Time] numeric(4, 0),
140 TotalRecords int
141 )
142
143INSERT INTO #JobMonitorTempTable (RowNumber,
144WorkOrder, Task, [Resource], ResourceType,
145ResourceGroup, CreationDate, StartDate, RequiredByDate, [Priority]
146, TaskDescription, Asset, CompletionDate, [Time], TotalRecords)
147 EXEC sp_executesql @qry
148 SELECT
149 *
150 FROM #JobMonitorTempTable
151 SELECT TOP 1
152 @TotalRecords = CAST(TotalRecords AS int)
153 FROM #JobMonitorTempTable
154 DROP TABLE #JobMonitorTempTable'
155END
156GO