· 6 years ago · Dec 17, 2019, 08:14 PM
1DECLARE @ReportId int
2SELECT @ReportId=CusReportId from CUSCustomReports where CusReportName= '1. Request PTO'
3
4
5DELETE CusGridQueries FROM CusGridQueries g join CUSCustomReports r on g.CUSGridQueryId=r.CUSGridQueryId and r.CUSReportId=@ReportId
6DELETE CusCustomReportParams where CUSReportID=@ReportID
7DELETE CusCustomReportSections where CUSReportID=@ReportID
8DELETE CusCustomReportModules where CUSReportID=@ReportID
9DELETE CusCustomReportApplets where CUSReportID=@ReportID
10DELETE CusCustomSubReports where CUSReportID=@ReportID
11DELETE cusDrillDowns where CUSReportid=@ReportID
12
13
14
15UPDATE [dbo].[CUSCustomReports]
16 SET [CusReportName] = '1. Request PTO'
17 ,[ReportLayout] = null
18 ,[isActive] = 1
19 ,[isDataOnly] = 0
20 ,[isStoredProcedure] = 0
21 ,[Query] = 'if not exists (select 1 from z_PTOrequests where startdate = ''^startdate^'' and enddate = ''^enddate^'' and empid = ^empid^ and isrejected = convert(bit,0) and approvedate is null)
22 begin
23 insert z_PTOrequests
24 (
25 empid
26 , requestdate
27 , PTOprojectid
28 , startdate
29 , enddate
30 , perdayhrs
31 , availablehrs
32 , totalhrs
33 , approvedate
34 , approveby
35 , isrejected
36 )
37
38 select
39 empid = ^empid^
40 , requestdate = dbo.trimtime(getdate())
41 , PTOprojectid = ^ptoprojectid^
42 , startdate = ''^startdate^''
43 , enddate = ''^enddate^''
44 , perdayhrs = ^perdayhrs^
45 , availablehrs = ^availablehrs^
46 , totalhrs = ^requestedhrs^
47 , approvedate = null
48 , approveby = null
49 , isrejected = convert(bit,0)
50
51 select reterr=0, retmsg = ''Request Submitted!''
52 end
53else
54 begin
55 select
56 reterr = -1
57 , retmsg = ''You already have a pending request for this time period.''
58 end'
59 ,[DatasetCount] = 0
60 ,[ModifyDate] = getdate()
61 ,[ModifyBy] = 'Exchange Update'
62 ,[UseInProjectRoles] = 0
63 ,[AllowInWidget] = 0
64 ,[IsAction] = 1
65 ,[IsStacked] = 0
66 ,[IsWarning] = 0
67 ,[PromptOptions] = '{
68 "LabelFontSize": 9.0,
69 "ControlFontSize": 12.0,
70 "DescriptionFontSize": 10.0,
71 "RequiredForeColor": "Red",
72 "ForeColor": "Black",
73 "HeaderBackColor": "43, 126, 179",
74 "HeaderForeColor": "White",
75 "DescriptionForeColor": "43, 126, 179",
76 "DescriptionBackColor": "White",
77 "BackButtonText": "Back",
78 "NextButtonText": "Next",
79 "CompleteButtonText": "Complete",
80 "PrintButtonText": "Confirm Request",
81 "CancelButtonText": "Cancel",
82 "GridButtonText": "Ok",
83 "InputWidth": 150,
84 "ShowWizardDescription": true,
85 "AllowViewData": false,
86 "WindowSize": "525, 450",
87 "DescriptionHeight": 48,
88 "TextDataTypeHeight": 60,
89 "DataSourceName": ""
90}'
91 ,[PromptStyle] = 'Wizard'
92 ,[QueryLanguage] = 'SQL'
93 ,[CVSGridQueryId] = null
94 ,[CUSGridQueryId] = null
95 ,[Description] = null
96 WHERE CusReportId=@ReportId
97
98DECLARE @SectionId int
99
100
101--REPORT PARAMETERS
102INSERT INTO [dbo].[CUSCustomReportParams]
103 ([CusReportID]
104 ,[ParamName]
105 ,[LabelName]
106 ,[HidePrompt]
107 ,[DefaultValue]
108 ,[DataType]
109 ,[LookupType]
110 ,[SecLookupType]
111 ,[ListText]
112 ,[allowMultiSelect]
113 ,[SortOrder]
114 ,[isRequired]
115 ,[ModifyDate]
116 ,[CreateDate]
117 ,[ModifyBy]
118 ,[CreateBy]
119 ,[CusReportSectionID]
120 ,[DefaultValueQuery]
121 ,[Description]
122 ,[EmptyValue]
123 ,[HidePromptQuery]
124 ,[IgnorePreviousValue]
125 ,[isReadOnly]
126 ,[isReadOnlyQuery]
127 ,[LabelNameQuery])
128 VALUES
129 (
130 @ReportId
131 ,'^empid^'
132 ,'empid'
133 ,1
134 ,'@MYID@'
135 ,'int'
136 ,'na'
137 ,null
138 ,null
139 ,0
140 ,1
141 ,0
142 ,getdate()
143 ,getdate()
144 ,coalesce(91,'Exchange Import')
145 ,'Exchange Import'
146 ,NULL
147 ,null
148 ,null
149 ,'null'
150 ,null
151 ,1
152 ,0
153 ,null
154 ,null
155 )
156
157
158
159--REPORT PARAMETERS
160INSERT INTO [dbo].[CUSCustomReportParams]
161 ([CusReportID]
162 ,[ParamName]
163 ,[LabelName]
164 ,[HidePrompt]
165 ,[DefaultValue]
166 ,[DataType]
167 ,[LookupType]
168 ,[SecLookupType]
169 ,[ListText]
170 ,[allowMultiSelect]
171 ,[SortOrder]
172 ,[isRequired]
173 ,[ModifyDate]
174 ,[CreateDate]
175 ,[ModifyBy]
176 ,[CreateBy]
177 ,[CusReportSectionID]
178 ,[DefaultValueQuery]
179 ,[Description]
180 ,[EmptyValue]
181 ,[HidePromptQuery]
182 ,[IgnorePreviousValue]
183 ,[isReadOnly]
184 ,[isReadOnlyQuery]
185 ,[LabelNameQuery])
186 VALUES
187 (
188 @ReportId
189 ,'^ptoprojectid^'
190 ,'PTO Type'
191 ,0
192 ,null
193 ,'SQL Query (Optional)'
194 ,'na'
195 ,null
196 ,'
197select
198displaytext = ProjectName
199, displayvalue = p.ProjectID
200from AccrualProjects ap
201 join projects p on ap.projectid = p.projectid
202 join projectfacts pf on p.factid = pf.factid
203where pf.isactive = 1
204order by projectname'
205 ,0
206 ,2
207 ,1
208 ,getdate()
209 ,getdate()
210 ,coalesce(91,'Exchange Import')
211 ,'Exchange Import'
212 ,NULL
213 ,null
214 ,null
215 ,'null'
216 ,null
217 ,1
218 ,0
219 ,null
220 ,null
221 )
222
223
224
225--REPORT PARAMETERS
226INSERT INTO [dbo].[CUSCustomReportParams]
227 ([CusReportID]
228 ,[ParamName]
229 ,[LabelName]
230 ,[HidePrompt]
231 ,[DefaultValue]
232 ,[DataType]
233 ,[LookupType]
234 ,[SecLookupType]
235 ,[ListText]
236 ,[allowMultiSelect]
237 ,[SortOrder]
238 ,[isRequired]
239 ,[ModifyDate]
240 ,[CreateDate]
241 ,[ModifyBy]
242 ,[CreateBy]
243 ,[CusReportSectionID]
244 ,[DefaultValueQuery]
245 ,[Description]
246 ,[EmptyValue]
247 ,[HidePromptQuery]
248 ,[IgnorePreviousValue]
249 ,[isReadOnly]
250 ,[isReadOnlyQuery]
251 ,[LabelNameQuery])
252 VALUES
253 (
254 @ReportId
255 ,'^startdate^'
256 ,'Start Date'
257 ,0
258 ,null
259 ,'datetime'
260 ,'na'
261 ,null
262 ,null
263 ,0
264 ,3
265 ,1
266 ,getdate()
267 ,getdate()
268 ,coalesce(91,'Exchange Import')
269 ,'Exchange Import'
270 ,NULL
271 ,null
272 ,null
273 ,'null'
274 ,null
275 ,0
276 ,0
277 ,null
278 ,null
279 )
280
281
282
283--REPORT PARAMETERS
284INSERT INTO [dbo].[CUSCustomReportParams]
285 ([CusReportID]
286 ,[ParamName]
287 ,[LabelName]
288 ,[HidePrompt]
289 ,[DefaultValue]
290 ,[DataType]
291 ,[LookupType]
292 ,[SecLookupType]
293 ,[ListText]
294 ,[allowMultiSelect]
295 ,[SortOrder]
296 ,[isRequired]
297 ,[ModifyDate]
298 ,[CreateDate]
299 ,[ModifyBy]
300 ,[CreateBy]
301 ,[CusReportSectionID]
302 ,[DefaultValueQuery]
303 ,[Description]
304 ,[EmptyValue]
305 ,[HidePromptQuery]
306 ,[IgnorePreviousValue]
307 ,[isReadOnly]
308 ,[isReadOnlyQuery]
309 ,[LabelNameQuery])
310 VALUES
311 (
312 @ReportId
313 ,'^enddate^'
314 ,'End Date'
315 ,0
316 ,null
317 ,'datetime'
318 ,'na'
319 ,null
320 ,null
321 ,0
322 ,4
323 ,1
324 ,getdate()
325 ,getdate()
326 ,coalesce(91,'Exchange Import')
327 ,'Exchange Import'
328 ,NULL
329 ,null
330 ,null
331 ,'null'
332 ,null
333 ,0
334 ,0
335 ,null
336 ,null
337 )
338
339
340
341--REPORT PARAMETERS
342INSERT INTO [dbo].[CUSCustomReportParams]
343 ([CusReportID]
344 ,[ParamName]
345 ,[LabelName]
346 ,[HidePrompt]
347 ,[DefaultValue]
348 ,[DataType]
349 ,[LookupType]
350 ,[SecLookupType]
351 ,[ListText]
352 ,[allowMultiSelect]
353 ,[SortOrder]
354 ,[isRequired]
355 ,[ModifyDate]
356 ,[CreateDate]
357 ,[ModifyBy]
358 ,[CreateBy]
359 ,[CusReportSectionID]
360 ,[DefaultValueQuery]
361 ,[Description]
362 ,[EmptyValue]
363 ,[HidePromptQuery]
364 ,[IgnorePreviousValue]
365 ,[isReadOnly]
366 ,[isReadOnlyQuery]
367 ,[LabelNameQuery])
368 VALUES
369 (
370 @ReportId
371 ,'^perdayhrs^'
372 ,'Hours per day'
373 ,0
374 ,'8'
375 ,'money'
376 ,'na'
377 ,null
378 ,null
379 ,0
380 ,5
381 ,1
382 ,getdate()
383 ,getdate()
384 ,coalesce(91,'Exchange Import')
385 ,'Exchange Import'
386 ,NULL
387 ,null
388 ,null
389 ,'null'
390 ,null
391 ,0
392 ,0
393 ,null
394 ,null
395 )
396
397
398
399--REPORT PARAMETERS
400INSERT INTO [dbo].[CUSCustomReportParams]
401 ([CusReportID]
402 ,[ParamName]
403 ,[LabelName]
404 ,[HidePrompt]
405 ,[DefaultValue]
406 ,[DataType]
407 ,[LookupType]
408 ,[SecLookupType]
409 ,[ListText]
410 ,[allowMultiSelect]
411 ,[SortOrder]
412 ,[isRequired]
413 ,[ModifyDate]
414 ,[CreateDate]
415 ,[ModifyBy]
416 ,[CreateBy]
417 ,[CusReportSectionID]
418 ,[DefaultValueQuery]
419 ,[Description]
420 ,[EmptyValue]
421 ,[HidePromptQuery]
422 ,[IgnorePreviousValue]
423 ,[isReadOnly]
424 ,[isReadOnlyQuery]
425 ,[LabelNameQuery])
426 VALUES
427 (
428 @ReportId
429 ,'^availablehrs^'
430 ,'Available Hours'
431 ,0
432 ,null
433 ,'nvarchar'
434 ,'na'
435 ,null
436 ,null
437 ,0
438 ,9
439 ,0
440 ,getdate()
441 ,getdate()
442 ,coalesce(92,'Exchange Import')
443 ,'Exchange Import'
444 ,NULL
445 ,'
446declare
447 @EmpID int=^empid^,
448 @IncUnSub bit=0,
449 @IncUnApp bit=0
450
451create table #beneYears(yearstart datetime,accProjID int)
452
453insert into #beneYears (yearstart,accProjID)
454select max(benefityearstart),AccProjID from Accruals where empid=@empid group by AccProjID
455
456select
457 --projectlongname as Project,
458 --sum(accrued) as Accrued,
459 --sum(used) as Used,
460 --sum(requested) as Requested,
461 --accrued=sum(accrued),
462 --used=sum(used),
463 --requested=sum(requested),
464 sum(accrued)-sum(used)-sum(requested) as Balance
465from
466
467(
468--used hours
469select
470 projectid=p.projectid,
471 projectlongname=p.projectlongname,
472 accrued=0,
473 used=tsi.workhrs,
474 requested=0
475from
476 #beneYears a
477 join accrualprojects ap on a.accprojid=ap.accprojid
478 join AccrualMembers am on am.AccProjID=ap.AccProjID and am.EmpID=@EmpID
479 join projects p on ap.projectid = p.projectid
480 join projects p2 on p.factid=p2.factid
481 join projectfacts pf on p2.factid=pf.factid and pf.isactive=1
482 join timesheets ts on ts.empid=@empid
483 join timesheetitems tsi on ts.timeid=tsi.timeid and tsi.projectid=p2.projectid
484 and tsi.workdate>=a.yearstart
485 and (
486 (tsi.WorkDate<''9998/12/31'' and am.doOverride=1 and am.AccrueTypeID=2) --override and hours accrue type
487 or
488 (tsi.WorkDate<convert(datetime,convert(varchar(4),year(a.yearstart))+''/12/31'') and am.doOverride=1 and am.AccCalTypeID=2 and am.AccrueTypeID=1) --calendar
489 or
490 (tsi.WorkDate<''9998/12/31'' and am.doOverride=0 and ap.AccrueTypeID=2) --not override and hours accrue type
491 or
492 (tsi.WorkDate<convert(datetime,convert(varchar(4),year(a.yearstart))+''/12/31'') and am.doOverride=0 and ap.AccCalTypeID=2 and ap.AccrueTypeID=1) --calendar
493 or
494 (tsi.workdate<dateadd(year,1,a.yearstart) and ap.AccCalTypeID=3)
495 or
496 (tsi.workdate<dateadd(year,1,a.yearstart) and ((am.doOverride=1 and am.AccrueTypeId=1) or (am.doOverride=0 and ap.AccrueTypeId=1)))
497 )
498where
499 ((ts.submitdate is not null and ts.approvedate is not null) and @incUnSub=0) or ((ts.approvedate is null and ts.submitdate is null) and @incUnSub=1)
500 or
501 ((ts.approvedate is not null and ts.submitdate is not null) and @incUnapp=0) or ((ts.approvedate is null and ts.submitdate is not null) and @incUnApp=1)
502 or
503 (ts.submitdate is not null and ts.approvedate is not null)
504
505union all
506
507--accrued hours
508select
509 projectid=p.projectid,
510 projectlongname=p.projectlongname,
511 accrued=amount,
512 used=0,
513 requested=0
514from
515 #beneYears b
516 join accruals a on b.accprojid=a.accprojid and a.benefityearstart=b.yearstart and a.empid=@empid
517 join accrualprojects ap on a.accprojid=ap.accprojid
518 join projects p on ap.projectid=p.projectid
519 join projectfacts pf on p.factid=pf.factid and pf.isactive=1
520
521
522union all
523
524--requested hours
525select
526 projectid=p.projectid,
527 projectlongname=p.projectlongname,
528 accrued=0,
529 used=0,
530 requested=case when z.isrejected=1 then 0 else totalhrs end
531from
532 z_ptorequests z
533 --#beneYears b
534 --join accruals a on b.accprojid=a.accprojid and a.benefityearstart=b.yearstart and a.empid=@empid
535 --join z_ptorequests z on z.empid=a.empid and a.empid=@empid
536 --join accrualprojects ap on a.accprojid=ap.accprojid
537 join projects p on z.ptoprojectid=p.projectid
538 --join projectfacts pf on p.factid=pf.factid and pf.isactive=1
539 where empid=@empid
540
541) x
542
543where projectid =^ptoprojectid^
544
545group by projectlongname
546
547
548
549'
550 ,null
551 ,'null'
552 ,null
553 ,0
554 ,1
555 ,null
556 ,null
557 )
558
559
560
561--REPORT PARAMETERS
562INSERT INTO [dbo].[CUSCustomReportParams]
563 ([CusReportID]
564 ,[ParamName]
565 ,[LabelName]
566 ,[HidePrompt]
567 ,[DefaultValue]
568 ,[DataType]
569 ,[LookupType]
570 ,[SecLookupType]
571 ,[ListText]
572 ,[allowMultiSelect]
573 ,[SortOrder]
574 ,[isRequired]
575 ,[ModifyDate]
576 ,[CreateDate]
577 ,[ModifyBy]
578 ,[CreateBy]
579 ,[CusReportSectionID]
580 ,[DefaultValueQuery]
581 ,[Description]
582 ,[EmptyValue]
583 ,[HidePromptQuery]
584 ,[IgnorePreviousValue]
585 ,[isReadOnly]
586 ,[isReadOnlyQuery]
587 ,[LabelNameQuery])
588 VALUES
589 (
590 @ReportId
591 ,'^accruedhrs^'
592 ,'Accrued Hours'
593 ,0
594 ,null
595 ,'nvarchar'
596 ,'na'
597 ,null
598 ,null
599 ,0
600 ,6
601 ,0
602 ,getdate()
603 ,getdate()
604 ,coalesce(92,'Exchange Import')
605 ,'Exchange Import'
606 ,NULL
607 ,'
608declare
609 @EmpID int=^empid^,
610 @IncUnSub bit=0,
611 @IncUnApp bit=0
612
613create table #beneYears(yearstart datetime,accProjID int)
614
615insert into #beneYears (yearstart,accProjID)
616select max(benefityearstart),AccProjID from Accruals where empid=@empid group by AccProjID
617
618select
619 --projectlongname as Project,
620 sum(accrued) as Accrued
621 --sum(used) as Used,
622 --sum(accrued)-sum(used) as Balance
623from
624
625(
626--used hours
627select
628 projectid=p.projectid,
629 projectlongname=p.projectlongname,
630 accrued=0,
631 used=tsi.workhrs
632from
633 #beneYears a
634 join accrualprojects ap on a.accprojid=ap.accprojid
635 join AccrualMembers am on am.AccProjID=ap.AccProjID and am.EmpID=@EmpID
636 join projects p on ap.projectid = p.projectid
637 join projects p2 on p.factid=p2.factid
638 join projectfacts pf on p2.factid=pf.factid and pf.isactive=1
639 join timesheets ts on ts.empid=@empid
640 join timesheetitems tsi on ts.timeid=tsi.timeid and tsi.projectid=p2.projectid
641 and tsi.workdate>=a.yearstart
642 and (
643 (tsi.WorkDate<''9998/12/31'' and am.doOverride=1 and am.AccrueTypeID=2) --override and hours accrue type
644 or
645 (tsi.WorkDate<convert(datetime,convert(varchar(4),year(a.yearstart))+''/12/31'') and am.doOverride=1 and am.AccCalTypeID=2 and am.AccrueTypeID=1) --calendar
646 or
647 (tsi.WorkDate<''9998/12/31'' and am.doOverride=0 and ap.AccrueTypeID=2) --not override and hours accrue type
648 or
649 (tsi.WorkDate<convert(datetime,convert(varchar(4),year(a.yearstart))+''/12/31'') and am.doOverride=0 and ap.AccCalTypeID=2 and ap.AccrueTypeID=1) --calendar
650 or
651 (tsi.workdate<dateadd(year,1,a.yearstart) and ap.AccCalTypeID=3)
652 or
653 (tsi.workdate<dateadd(year,1,a.yearstart) and ((am.doOverride=1 and am.AccrueTypeId=1) or (am.doOverride=0 and ap.AccrueTypeId=1)))
654 )
655where
656 ((ts.submitdate is not null and ts.approvedate is not null) and @incUnSub=0) or ((ts.approvedate is null and ts.submitdate is null) and @incUnSub=1)
657 or
658 ((ts.approvedate is not null and ts.submitdate is not null) and @incUnapp=0) or ((ts.approvedate is null and ts.submitdate is not null) and @incUnApp=1)
659 or
660 (ts.submitdate is not null and ts.approvedate is not null)
661
662union all
663
664select
665 projectid=p.projectid,
666 projectlongname=p.projectlongname,
667 accrued=amount,
668 used=0
669from
670 #beneYears b
671 join accruals a on b.accprojid=a.accprojid and a.benefityearstart=b.yearstart and a.empid=@empid
672 join accrualprojects ap on a.accprojid=ap.accprojid
673 join projects p on ap.projectid=p.projectid
674 join projectfacts pf on p.factid=pf.factid and pf.isactive=1
675) x
676
677where projectid = ^ptoprojectid^
678
679group by projectlongname
680
681
682
683'
684 ,null
685 ,'null'
686 ,null
687 ,0
688 ,1
689 ,null
690 ,null
691 )
692
693
694
695--REPORT PARAMETERS
696INSERT INTO [dbo].[CUSCustomReportParams]
697 ([CusReportID]
698 ,[ParamName]
699 ,[LabelName]
700 ,[HidePrompt]
701 ,[DefaultValue]
702 ,[DataType]
703 ,[LookupType]
704 ,[SecLookupType]
705 ,[ListText]
706 ,[allowMultiSelect]
707 ,[SortOrder]
708 ,[isRequired]
709 ,[ModifyDate]
710 ,[CreateDate]
711 ,[ModifyBy]
712 ,[CreateBy]
713 ,[CusReportSectionID]
714 ,[DefaultValueQuery]
715 ,[Description]
716 ,[EmptyValue]
717 ,[HidePromptQuery]
718 ,[IgnorePreviousValue]
719 ,[isReadOnly]
720 ,[isReadOnlyQuery]
721 ,[LabelNameQuery])
722 VALUES
723 (
724 @ReportId
725 ,'^usedhrs^'
726 ,'Used Hours'
727 ,0
728 ,null
729 ,'nvarchar'
730 ,'na'
731 ,null
732 ,null
733 ,0
734 ,7
735 ,0
736 ,getdate()
737 ,getdate()
738 ,coalesce(92,'Exchange Import')
739 ,'Exchange Import'
740 ,NULL
741 ,'
742declare
743 @EmpID int=^empid^,
744 @IncUnSub bit=0,
745 @IncUnApp bit=0
746
747create table #beneYears(yearstart datetime,accProjID int)
748
749insert into #beneYears (yearstart,accProjID)
750select max(benefityearstart),AccProjID from Accruals where empid=@empid group by AccProjID
751
752select
753 --projectlongname as Project,
754 --sum(accrued) as Accrued,
755 sum(used) as Used
756 --sum(accrued)-sum(used) as Balance
757from
758
759(
760--used hours
761select
762 projectid=p.projectid,
763 projectlongname=p.projectlongname,
764 accrued=0,
765 used=tsi.workhrs
766from
767 #beneYears a
768 join accrualprojects ap on a.accprojid=ap.accprojid
769 join AccrualMembers am on am.AccProjID=ap.AccProjID and am.EmpID=@EmpID
770 join projects p on ap.projectid = p.projectid
771 join projects p2 on p.factid=p2.factid
772 join projectfacts pf on p2.factid=pf.factid and pf.isactive=1
773 join timesheets ts on ts.empid=@empid
774 join timesheetitems tsi on ts.timeid=tsi.timeid and tsi.projectid=p2.projectid
775 and tsi.workdate>=a.yearstart
776 and (
777 (tsi.WorkDate<''9998/12/31'' and am.doOverride=1 and am.AccrueTypeID=2) --override and hours accrue type
778 or
779 (tsi.WorkDate<convert(datetime,convert(varchar(4),year(a.yearstart))+''/12/31'') and am.doOverride=1 and am.AccCalTypeID=2 and am.AccrueTypeID=1) --calendar
780 or
781 (tsi.WorkDate<''9998/12/31'' and am.doOverride=0 and ap.AccrueTypeID=2) --not override and hours accrue type
782 or
783 (tsi.WorkDate<convert(datetime,convert(varchar(4),year(a.yearstart))+''/12/31'') and am.doOverride=0 and ap.AccCalTypeID=2 and ap.AccrueTypeID=1) --calendar
784 or
785 (tsi.workdate<dateadd(year,1,a.yearstart) and ap.AccCalTypeID=3)
786 or
787 (tsi.workdate<dateadd(year,1,a.yearstart) and ((am.doOverride=1 and am.AccrueTypeId=1) or (am.doOverride=0 and ap.AccrueTypeId=1)))
788 )
789where
790 ((ts.submitdate is not null and ts.approvedate is not null) and @incUnSub=0) or ((ts.approvedate is null and ts.submitdate is null) and @incUnSub=1)
791 or
792 ((ts.approvedate is not null and ts.submitdate is not null) and @incUnapp=0) or ((ts.approvedate is null and ts.submitdate is not null) and @incUnApp=1)
793 or
794 (ts.submitdate is not null and ts.approvedate is not null)
795
796union all
797
798select
799 projectid=p.projectid,
800 projectlongname=p.projectlongname,
801 accrued=amount,
802 used=0
803from
804 #beneYears b
805 join accruals a on b.accprojid=a.accprojid and a.benefityearstart=b.yearstart and a.empid=@empid
806 join accrualprojects ap on a.accprojid=ap.accprojid
807 join projects p on ap.projectid=p.projectid
808 join projectfacts pf on p.factid=pf.factid and pf.isactive=1
809) x
810
811where projectid = ^ptoprojectid^
812
813group by projectlongname
814
815
816
817'
818 ,null
819 ,'null'
820 ,null
821 ,0
822 ,1
823 ,null
824 ,null
825 )
826
827
828
829--REPORT PARAMETERS
830INSERT INTO [dbo].[CUSCustomReportParams]
831 ([CusReportID]
832 ,[ParamName]
833 ,[LabelName]
834 ,[HidePrompt]
835 ,[DefaultValue]
836 ,[DataType]
837 ,[LookupType]
838 ,[SecLookupType]
839 ,[ListText]
840 ,[allowMultiSelect]
841 ,[SortOrder]
842 ,[isRequired]
843 ,[ModifyDate]
844 ,[CreateDate]
845 ,[ModifyBy]
846 ,[CreateBy]
847 ,[CusReportSectionID]
848 ,[DefaultValueQuery]
849 ,[Description]
850 ,[EmptyValue]
851 ,[HidePromptQuery]
852 ,[IgnorePreviousValue]
853 ,[isReadOnly]
854 ,[isReadOnlyQuery]
855 ,[LabelNameQuery])
856 VALUES
857 (
858 @ReportId
859 ,'^requestedhrs^'
860 ,'Current Requested Hours'
861 ,0
862 ,null
863 ,'nvarchar'
864 ,'na'
865 ,null
866 ,null
867 ,0
868 ,10
869 ,0
870 ,getdate()
871 ,getdate()
872 ,coalesce(92,'Exchange Import')
873 ,'Exchange Import'
874 ,NULL
875 ,'
876select (count(*)) * ^perdayhrs^ from dbo.weekdaysinrange(''^startdate^'',dateadd(d,1,''^enddate^''))'
877 ,null
878 ,'null'
879 ,null
880 ,0
881 ,1
882 ,null
883 ,null
884 )
885
886
887
888--REPORT PARAMETERS
889INSERT INTO [dbo].[CUSCustomReportParams]
890 ([CusReportID]
891 ,[ParamName]
892 ,[LabelName]
893 ,[HidePrompt]
894 ,[DefaultValue]
895 ,[DataType]
896 ,[LookupType]
897 ,[SecLookupType]
898 ,[ListText]
899 ,[allowMultiSelect]
900 ,[SortOrder]
901 ,[isRequired]
902 ,[ModifyDate]
903 ,[CreateDate]
904 ,[ModifyBy]
905 ,[CreateBy]
906 ,[CusReportSectionID]
907 ,[DefaultValueQuery]
908 ,[Description]
909 ,[EmptyValue]
910 ,[HidePromptQuery]
911 ,[IgnorePreviousValue]
912 ,[isReadOnly]
913 ,[isReadOnlyQuery]
914 ,[LabelNameQuery])
915 VALUES
916 (
917 @ReportId
918 ,'^prevrequestedhrs^'
919 ,'Previously Requested Hrs'
920 ,0
921 ,null
922 ,'nvarchar'
923 ,'na'
924 ,null
925 ,null
926 ,0
927 ,8
928 ,0
929 ,getdate()
930 ,getdate()
931 ,coalesce(92,'Exchange Import')
932 ,'Exchange Import'
933 ,NULL
934 ,'
935declare
936 @EmpID int=^empid^,
937 @IncUnSub bit=0,
938 @IncUnApp bit=0
939
940create table #beneYears(yearstart datetime,accProjID int)
941
942insert into #beneYears (yearstart,accProjID)
943select max(benefityearstart),AccProjID from Accruals where empid=@empid group by AccProjID
944
945select
946 --projectlongname as Project,
947 --sum(accrued) as Accrued,
948 --sum(used) as Used,
949 --sum(requested) as Requested,
950 --accrued=sum(accrued),
951 --used=sum(used),
952 requested=sum(requested)
953 --sum(accrued)-sum(used)-sum(requested) as Balance
954from
955
956(
957--used hours
958select
959 projectid=p.projectid,
960 projectlongname=p.projectlongname,
961 accrued=0,
962 used=tsi.workhrs,
963 requested=0
964from
965 #beneYears a
966 join accrualprojects ap on a.accprojid=ap.accprojid
967 join AccrualMembers am on am.AccProjID=ap.AccProjID and am.EmpID=@EmpID
968 join projects p on ap.projectid = p.projectid
969 join projects p2 on p.factid=p2.factid
970 join projectfacts pf on p2.factid=pf.factid and pf.isactive=1
971 join timesheets ts on ts.empid=@empid
972 join timesheetitems tsi on ts.timeid=tsi.timeid and tsi.projectid=p2.projectid
973 and tsi.workdate>=a.yearstart
974 and (
975 (tsi.WorkDate<''9998/12/31'' and am.doOverride=1 and am.AccrueTypeID=2) --override and hours accrue type
976 or
977 (tsi.WorkDate<convert(datetime,convert(varchar(4),year(a.yearstart))+''/12/31'') and am.doOverride=1 and am.AccCalTypeID=2 and am.AccrueTypeID=1) --calendar
978 or
979 (tsi.WorkDate<''9998/12/31'' and am.doOverride=0 and ap.AccrueTypeID=2) --not override and hours accrue type
980 or
981 (tsi.WorkDate<convert(datetime,convert(varchar(4),year(a.yearstart))+''/12/31'') and am.doOverride=0 and ap.AccCalTypeID=2 and ap.AccrueTypeID=1) --calendar
982 or
983 (tsi.workdate<dateadd(year,1,a.yearstart) and ap.AccCalTypeID=3)
984 or
985 (tsi.workdate<dateadd(year,1,a.yearstart) and ((am.doOverride=1 and am.AccrueTypeId=1) or (am.doOverride=0 and ap.AccrueTypeId=1)))
986 )
987where
988 ((ts.submitdate is not null and ts.approvedate is not null) and @incUnSub=0) or ((ts.approvedate is null and ts.submitdate is null) and @incUnSub=1)
989 or
990 ((ts.approvedate is not null and ts.submitdate is not null) and @incUnapp=0) or ((ts.approvedate is null and ts.submitdate is not null) and @incUnApp=1)
991 or
992 (ts.submitdate is not null and ts.approvedate is not null)
993
994union all
995
996--accrued hours
997select
998 projectid=p.projectid,
999 projectlongname=p.projectlongname,
1000 accrued=amount,
1001 used=0,
1002 requested=0
1003from
1004 #beneYears b
1005 join accruals a on b.accprojid=a.accprojid and a.benefityearstart=b.yearstart and a.empid=@empid
1006 join accrualprojects ap on a.accprojid=ap.accprojid
1007 join projects p on ap.projectid=p.projectid
1008 join projectfacts pf on p.factid=pf.factid and pf.isactive=1
1009
1010
1011union all
1012
1013--requested hours
1014select
1015 projectid=p.projectid,
1016 projectlongname=p.projectlongname,
1017 accrued=0,
1018 used=0,
1019 requested=case when z.isrejected=1 then 0 else totalhrs end
1020from
1021 z_ptorequests z
1022 --#beneYears b
1023 --join accruals a on b.accprojid=a.accprojid and a.benefityearstart=b.yearstart and a.empid=@empid
1024 --join z_ptorequests z on z.empid=a.empid and a.empid=@empid
1025 --join accrualprojects ap on a.accprojid=ap.accprojid
1026 join projects p on z.ptoprojectid=p.projectid
1027 --join projectfacts pf on p.factid=pf.factid and pf.isactive=1
1028 where empid=@empid
1029
1030) x
1031
1032where projectid = ^ptoprojectid^
1033
1034group by projectlongname
1035
1036
1037
1038'
1039 ,null
1040 ,'null'
1041 ,null
1042 ,0
1043 ,1
1044 ,null
1045 ,null
1046 )
1047
1048
1049
1050
1051--REPORT SECTIONS
1052INSERT INTO [dbo].[CusCustomReportSections]
1053 ([CusReportSectionName]
1054 ,[Description]
1055 ,[SortOrder]
1056 ,[ModifyDate]
1057 ,[CreateDate]
1058 ,[ModifyBy]
1059 ,[CreateBy]
1060 ,[CusReportID]
1061 ,[ValScript])
1062 VALUES
1063 (
1064 'Request Info'
1065 ,null
1066 ,1
1067 ,getdate()
1068 ,getdate()
1069 ,'91'
1070 ,'Exchange Import'
1071 ,@ReportId
1072 ,null
1073 )
1074
1075set @SectionId=@@identity
1076
1077update CusCustomReportParams set CUSReportSectionId=@SectionId,ModifyBy='Exchange Import' where
1078ModifyBy='91' and CusReportId=@ReportId
1079--REPORT SECTIONS
1080INSERT INTO [dbo].[CusCustomReportSections]
1081 ([CusReportSectionName]
1082 ,[Description]
1083 ,[SortOrder]
1084 ,[ModifyDate]
1085 ,[CreateDate]
1086 ,[ModifyBy]
1087 ,[CreateBy]
1088 ,[CusReportID]
1089 ,[ValScript])
1090 VALUES
1091 (
1092 'Request Summary'
1093 ,null
1094 ,2
1095 ,getdate()
1096 ,getdate()
1097 ,'92'
1098 ,'Exchange Import'
1099 ,@ReportId
1100 ,null
1101 )
1102
1103set @SectionId=@@identity
1104
1105update CusCustomReportParams set CUSReportSectionId=@SectionId,ModifyBy='Exchange Import' where
1106ModifyBy='92' and CusReportId=@ReportId
1107
1108
1109--REPORT MODULES
1110INSERT INTO [dbo].[CUSCustomReportModules]
1111 ([ModuleID]
1112 ,[CUSReportID]
1113 ,[ModifyDate]
1114 ,[CreateDate]
1115 ,[ModifyBy]
1116 ,[CreateBy])
1117 VALUES
1118 (
1119 5
1120 ,@ReportId
1121 ,getdate()
1122 ,getdate()
1123 ,'Exchange Import'
1124 ,'Exchange Import'
1125 )
1126
1127
1128
1129INSERT INTO [dbo].[CUSCustomReportApplets]
1130 ([AppID]
1131 ,[ActionTypeID]
1132 ,[CUSReportID]
1133 ,[ModifyDate]
1134 ,[CreateDate]
1135 ,[ModifyBy]
1136 ,[CreateBy]
1137 ,[DoReload])
1138 VALUES
1139 (4
1140 ,3
1141 ,@ReportId
1142 ,getdate()
1143 ,getdate()
1144 ,'Exchange Import'
1145 ,'Exchange Import'
1146 ,0)
1147
1148
1149
1150
1151--add something