· 6 years ago · Dec 26, 2019, 08:02 PM
1--ALTER PROCEDURE rpt.usp_OvertimeStep2_report
2-- (
3-- @payoutUID VARCHAR(36)
4-- , @employeeTypeUIDs NVARCHAR(MAX)
5-- , @departmentUIDs NVARCHAR(MAX)
6-- )
7--AS
8
9DECLARE @PayoutUID UNIQUEIDENTIFIER = '3e17b94f-64de-415f-ad1d-9908b025e8e7',
10 @employeeTypeUIDs NVARCHAR(MAX),
11 @departmentUIDs NVARCHAR(MAX);
12
13BEGIN
14 --DECLARE @empTypeIDList TABLE
15 -- (
16 -- EmpTypeID BIGINT
17 -- )
18 --DECLARE @departmentIDList TABLE
19 -- (
20 -- DepartmentID BIGINT
21 -- )
22
23 --INSERT INTO @empTypeIDList
24 -- SELECT
25 -- TET.EmpTypeID
26 -- FROM
27 -- [dbo].[SplitByComma](@employeeTypeUIDs) AS SBC
28 -- INNER JOIN dbo.tbl_EmpType AS TET
29 -- ON SBC.Data = TET.EmpTypeUID
30
31 --INSERT INTO @departmentIDList
32 -- SELECT
33 -- TD.DepartmentID
34 -- FROM
35 -- [dbo].[SplitByComma](@departmentUIDs) AS SBC
36 -- INNER JOIN cc.tbl_Department AS TD
37 -- ON SBC.Data = TD.DepartmentUID
38
39 --IF (
40 -- SELECT
41 -- COUNT(*)
42 -- FROM
43 -- @employeeTypeUIDList
44 -- WHERE
45 -- employeeTypeUID IS NOT NULL
46 -- ) = 0
47 -- BEGIN
48 -- INSERT INTO @employeeTypeUIDList
49 -- SELECT
50 -- CAST (EmpTypeUID AS VARCHAR(50))
51 -- FROM
52 -- dbo.tbl_EmpType
53 -- END
54
55 --IF (
56 -- SELECT
57 -- COUNT(*)
58 -- FROM
59 -- @departmentUIDList
60 -- WHERE
61 -- departmentUID IS NOT NULL
62 -- ) = 0
63 -- BEGIN
64 -- INSERT INTO @departmentUIDList
65 -- SELECT
66 -- CAST (DepartmentUID AS VARCHAR(50))
67 -- FROM
68 -- cc.vw__CCDepartment D
69 -- INNER JOIN dbo.tbl_Payout P
70 -- ON p.ClientID = D.ClientID
71 -- WHERE
72 -- payoutuid = @payoutuid
73
74 -- END
75
76
77 SELECT 'Normal' AS [ReportType];
78 /***================================*/
79
80
81 DECLARE @reportDetail TABLE
82 (
83 ReportName NVARCHAR(MAX),
84 ReportDescription NVARCHAR(MAX),
85 ReportSubDescription NVARCHAR(MAX)
86 ); --report header
87 DECLARE @reportHeaders TABLE
88 (
89 Title NVARCHAR(255) NOT NULL,
90 DataType NVARCHAR(255) NULL, -- SQL DataType (NVARCHAR(n), DECIMAL(18,4), etc..)
91 Width INT
92 DEFAULT (100), -- in pixel
93 AggregateFn VARCHAR(15) NULL
94 ); -- report columns
95 DECLARE @reportFooter TABLE
96 (
97 DataKey NVARCHAR(255),
98 DataValue NVARCHAR(255),
99 PosX INT,
100 PosY FLOAT
101 );
102
103 DECLARE @clientName VARCHAR(100),
104 @clientid INT;
105 DECLARE @payperiod VARCHAR(100);
106 SET @clientid =
107 (
108 SELECT ClientID FROM dbo.tbl_Payout WHERE PayoutUID = @PayoutUID
109 );
110 SET @clientName =
111 (
112 SELECT DISTINCT
113 REPLACE(ClientName, '&', '&')
114 FROM dbo.tbl_Client
115 WHERE ClientID = @clientid
116 );
117 SET @payperiod =
118 (
119 SELECT CONVERT(VARCHAR(20), PeriodStart, 107) + ' to ' + CONVERT(VARCHAR(20), PeriodEnd, 107)
120 FROM dbo.tbl_Payout
121 WHERE PayoutUID = @PayoutUID
122 );
123
124
125 INSERT INTO @reportDetail
126 VALUES
127 (@clientName, 'Overtime Summary', @payperiod);
128 SELECT *
129 FROM @reportDetail AS RD;
130 DECLARE @columns VARCHAR(MAX);
131
132 --SET @payoutuid = '3C812BF6-CAD0-43D8-B3AC-3D712586BE7B'
133 -- CREATE DYNAMIC TEMPORARY TABLE
134
135 IF OBJECT_ID('tempdb..#OvertimePays') IS NOT NULL
136 /*Then it exists*/
137 DROP TABLE #OvertimePays;
138
139 CREATE TABLE #OvertimePays
140 (
141 [Employee Number] NVARCHAR(50),
142 [Employee Name] NVARCHAR(100),
143 [Department] NVARCHAR(100),
144 [Section] NVARCHAR(100),
145 [Site] NVARCHAR(100),
146 [Current Basic Pay] NUMERIC(18, 2),
147 [OT Hours] NUMERIC(18, 4),
148 [OTRATEID] BIGINT,
149 [OTCode] NVARCHAR(30),
150 [OTRateName] NVARCHAR(100),
151 [Hourly Rate] NUMERIC(18, 5),
152 [Amount] DECIMAL(18, 2),
153 [PayoutId] BIGINT,
154 [Cut-Off From] DATE,
155 [Cut-Off To] DATE,
156 [EmployeeType] NVARCHAR(100),
157 [Company Name] NVARCHAR(100),
158 [Adjustments] NVARCHAR(100),
159 [AdjAmount] DECIMAL(18, 2),
160 [Total Amount] DECIMAL(18, 2)
161 );
162 INSERT INTO #OvertimePays
163 EXEC [rpt].[usp_OvertimePayStep1_report] @PayoutUID,
164 @employeeTypeUIDs,
165 @departmentUIDs;
166
167 ---------------------------------------------------------------------------------------------
168 DECLARE @ColumnName_Amount VARCHAR(8000),
169 @ColumnName_hours VARCHAR(8000),
170 @sql2 VARCHAR(MAX),
171 @sql3 VARCHAR(MAX),
172 @ReportColumnNames VARCHAR(MAX),
173 @SumColumnNames VARCHAR(MAX),
174 @SumColumnNames_Hours VARCHAR(MAX),
175 @SumColumnNames_Amount VARCHAR(MAX),
176 @dynacolumn VARCHAR(MAX);
177
178
179 DECLARE @RateTable TABLE
180 (
181 OTCode NVARCHAR(30),
182 OTratename VARCHAR(MAX)
183 );
184
185 INSERT INTO @RateTable
186 SELECT DISTINCT
187 OTCode,
188 OTRateName
189 FROM #OvertimePays AS OP
190 ORDER BY OTCode;
191
192
193 -- SET @dynacolumn = 'Employee_code' + ',' + 'Emplo'
194 SELECT @dynacolumn
195 = ISNULL(@dynacolumn + ',', '') + QUOTENAME(OTRateName_hours) + ',' + QUOTENAME([OTRateName_Amount])
196 FROM
197 (
198 SELECT DISTINCT
199 OTCode,
200 CONCAT(OTratename, ' Amount') OTRateName_Amount,
201 CONCAT(OTratename, ' Hours') OTRateName_hours
202 FROM @RateTable
203 WHERE OTratename IS NOT NULL
204
205 --SELECT
206 -- CONCAT(OTRateName, '_Amount') OTRateName_Amount
207 --, CONCAT(OTRateName, '_hours') OTRateName_hours
208 --FROM
209 -- #OvertimePays
210 --GROUP BY
211 -- OTRateName
212 --, OTRATEID
213 ) AS overtime
214 ORDER BY OTCode;
215
216 --SELECT @dynacolumn
217
218 IF OBJECT_ID('tempdb..#temp') IS NOT NULL
219 BEGIN
220 DROP TABLE #temp;
221 END;
222 CREATE TABLE #temp
223 (
224 [Employee Number] VARCHAR(20),
225 [Employee Name] VARCHAR(MAX),
226 [Hourly Rate] DECIMAL(15, 2)
227 );
228 DECLARE @counter INT;
229 DECLARE @count INT;
230 SET @count =
231 (
232 SELECT COUNT(*) FROM [dbo].[ufn_CSVToTable](@dynacolumn, ',')
233 );
234 DECLARE @Script AS VARCHAR(8000);
235 DECLARE @Script_prepare AS VARCHAR(8000);
236 DECLARE @fieldName VARCHAR(MAX);
237
238 SET @counter = 1;
239 WHILE @counter <= @count
240 BEGIN
241 --SET @fieldName = ( SELECT REPLACE([string], ' ', '_')
242 SET @fieldName =
243 (
244 SELECT [String]
245 FROM [dbo].[ufn_CSVToTable](@dynacolumn, ',')
246 WHERE ID = @counter
247 );
248
249
250 SET @Script = 'Alter table #temp Add ' + @fieldName + ' decimal (15,2)';
251 EXEC (@Script);
252
253 SET @counter = @counter + 1;
254 END;
255
256
257
258 DECLARE @AjdScript NVARCHAR(MAX);
259
260
261 DECLARE @Adjustment VARCHAR(154);
262
263 DECLARE c CURSOR FAST_FORWARD READ_ONLY FOR
264 SELECT DISTINCT
265 Adjustments
266 FROM #OvertimePays OP;
267 OPEN c;
268
269 FETCH NEXT FROM c
270 INTO @Adjustment;
271
272 WHILE @@FETCH_STATUS = 0
273 BEGIN
274
275 SET @AjdScript = N' ALTER TABLE #temp
276 ADD [' + @Adjustment + N'] NVARCHAR(MAX)';
277
278 EXEC (@AjdScript);
279
280
281 FETCH NEXT FROM c
282 INTO @Adjustment;
283
284 END;
285
286 CLOSE c;
287 DEALLOCATE c;
288
289 ALTER TABLE #temp ADD [Total Amount] DECIMAL(15, 2);
290
291 SELECT name Title,
292 CASE
293 WHEN name IN ( 'Employee Name', 'Employee Number' ) THEN
294 'VARCHAR(max)'
295 ELSE
296 'DECIMAL (15,2)'
297 END [datatype],
298 CASE
299 WHEN name = 'Employee Name' THEN
300 300
301 WHEN name = 'Employee Number' THEN
302 70
303 WHEN name LIKE '%hour%' THEN
304 90
305 ELSE
306 100
307 END width,
308 CASE
309 WHEN name IN ( 'Employee Name', 'Employee Number', 'Hourly Rate' ) THEN
310 NULL
311 ELSE
312 'SUM'
313 END AggregateFn
314 FROM tempdb.sys.columns
315 WHERE object_id = OBJECT_ID('tempdb..#temp');
316
317
318 --SELECT *
319 --FROM #temp
320 ---------------------------------------------------------------------------------------------
321
322 -- CREATE DYNAMIC TEMPORARY TABLE
323 SELECT @SumColumnNames_Hours
324 = ISNULL(@SumColumnNames_Hours + ',', '')
325 + CONCAT('SUM(ISNULL(', QUOTENAME(OTRateName_hours), ',0)) ', QUOTENAME(OTRateName_hours)) + ','
326 + CONCAT('SUM(ISNULL(', QUOTENAME(OTRateName_Amount), ',0)) ', QUOTENAME(OTRateName_Amount))
327 FROM
328 (
329 SELECT OTratename,
330 CONCAT(OTratename, ' Amount') OTRateName_Amount,
331 CONCAT(OTratename, ' Hours') OTRateName_hours
332 FROM @RateTable
333 WHERE OTratename IS NOT NULL
334 GROUP BY OTratename
335 ) AS overtime;
336 --ORDER BY overtime.otcode
337
338
339 --changes
340 --Get distinct values of the PIVOT Column Header
341 SELECT @ColumnName_Amount = ISNULL(@ColumnName_Amount + ',', '') + QUOTENAME(OTRateName_Amount),
342 @ColumnName_hours = ISNULL(@ColumnName_hours + ',', '') + QUOTENAME(OTRateName_hours)
343 FROM
344 (
345 SELECT OTRateName,
346 CONCAT(OTRateName, ' Hours') OTRateName_hours,
347 CONCAT(OTRateName, ' Amount') OTRateName_Amount
348 FROM #OvertimePays
349 WHERE OTRateName IS NOT NULL
350 GROUP BY OTRateName,
351 OTRATEID
352 ) AS deduction;
353
354
355
356 DECLARE @adjustmentTable TABLE
357 (
358 Adjustments NVARCHAR(254)
359 );
360
361 INSERT INTO @adjustmentTable
362 SELECT DISTINCT
363 Adjustments
364 FROM #OvertimePays OP
365 WHERE Adjustments IS NOT NULL;
366
367 DECLARE @AdjustmentColumn NVARCHAR(MAX);
368 SELECT @AdjustmentColumn
369 = STUFF(
370 (
371 SELECT ',' + QUOTENAME(Adjustments) FROM @adjustmentTable FOR XML PATH('')
372 ),
373 1,
374 1,
375 ''
376 );
377
378 DECLARE @sumColumn NVARCHAR(MAX);
379 SELECT @sumColumn = STUFF(
380 (
381 SELECT ',' + CONCAT('MAX(ISNULL(', QUOTENAME(Adjustments), ', 0))', QUOTENAME(Adjustments))
382 FROM @adjustmentTable
383 FOR XML PATH('')
384 ),
385 1,
386 1,
387 ''
388 );
389
390 -- For the summation of target column
391 --ISNULL(','+@SumColumnNames_hours,'')
392
393
394 SET @sql2
395 = 'SELECT
396 [Employee Number] [Employee number], REPLACE([Department], ''&'', ''&'') [Department],
397 REPLACE([Section], ''&'', ''&'') [Section], REPLACE([Site], ''&'', ''&'') [Site]
398 ,[Employee Name] [Employee Name]' + '
399 ,[Hourly Rate] ' + ISNULL(',' + @SumColumnNames_Hours, '') + ' ' + ISNULL(' , ' + @sumColumn, '')
400 + ',[Total Amount]' + ' FROM (' + 'SELECT ' + '[Employee Number]'
401 + ',[Employee Name] , [Department], [Section], [Site]' + ',[Hourly Rate]'
402 + ISNULL(', ' + @ColumnName_Amount, '') + ' ' + ISNULL(',' + @ColumnName_hours, '') + ' '
403 + ISNULL(', ' + @AdjustmentColumn, '') + ', [Total Amount]' + ' FROM (select *,concat(OTRateName,'
404 + ''' Hours''' + ') OTRateName_hours , concat(OTRateName,' + ''' Amount'''
405 + ') OTRateName_Amount from #OvertimePays) as tbl '
406 + ISNULL('PIVOT (MAX([Amount]) FOR [OTRateName_Amount] IN (' + @ColumnName_Amount + ')) AS Pivot1', '') + ' '
407 + +ISNULL('PIVOT (MAX([OT Hours]) FOR [OTRateName_hours] IN (' + @ColumnName_hours + ')) AS Pivot2', '')
408 + ' ' + +ISNULL('PIVOT (SUM([AdjAmount]) FOR [Adjustments] IN (' + @AdjustmentColumn + ')) AS Pivot3', '')
409 + ' '
410 +
411
412 --'PIVOT (MAX([Amount]) FOR [OTRateName_Amount] IN ( '
413 -- + @ColumnName_Amount + ' )) AS Pivot1 '
414 -- + 'PIVOT (MAX([OT Hours]) FOR [OTRateName_hours] IN ( '
415 -- + @ColumnName_hours + ' )) AS Pivot2 '
416 -- + 'PIVOT (SUM([AdjAmount]) FOR [Adjustments] IN ( '
417 -- + @AdjustmentColumn + ' )) AS Pivot3) '
418
419
420 +' ) tbl where [Total Amount] != 0 GROUP BY [Employee Name],[Employee Number],[Hourly Rate],[total amount],
421 [Department], [Section], [Site]' + ' ORDER BY [Employee Name]';
422
423
424 EXEC (@sql2);
425
426 INSERT INTO @reportFooter
427 (
428 DataKey,
429 DataValue,
430 PosX,
431 PosY
432 )
433 VALUES
434 ('KEY0', 'Grand Total', 0, 0),
435 ('KEY1', '="Total Number of Employees" + ": " + Count(Fields.[Employee number])', 0, 1);
436 --, ( 'KEY2', '=Count(Fields.[Employee number])', 200, 1 )
437
438 SELECT *
439 FROM @reportFooter AS RF;
440
441
442 DECLARE @reportSetting TABLE
443 (
444 Orientation NVARCHAR(30) NULL,
445 SIZE NVARCHAR(30) NULL,
446 Width NUMERIC(22, 8) NULL,
447 Height NUMERIC(22, 8) NULL,
448 MarginTop NUMERIC(22, 8) NULL,
449 MarginBottom NUMERIC(22, 8) NULL,
450 MarginLeft NUMERIC(22, 8) NULL,
451 MarginRight NUMERIC(22, 8) NULL,
452 scale FLOAT NULL
453 );
454
455 INSERT INTO @reportSetting
456 (
457 Orientation,
458 SIZE,
459 Width,
460 Height,
461 MarginTop,
462 MarginBottom,
463 MarginLeft,
464 MarginRight,
465 scale
466 )
467 VALUES
468 ('Landscape', 'Legal', '8.5', '13', '0.3', '0.5', '0.3', '0.3', '0');
469
470
471 SELECT *
472 FROM @reportSetting AS RS;
473
474
475END;
476
477
478
479GO