· 4 years ago · Sep 07, 2021, 04:18 PM
1USE [ShopMgt]
2GO
3/****** Object: StoredProcedure [SM].[prTechPayHoursSelect] Script Date: 9/7/2021 12:10:41 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8ALTER PROCEDURE [SM].[prTechPayHoursSelect]
9 (
10 @StartTime DATETIME,
11 @EndTime DATETIME,
12 @EmployeeId INT = -1
13 )
14AS
15BEGIN
16 IF (OBJECT_ID('tempdb..#ItemCountTable') IS NOT NULL)
17 DROP TABLE #ItemCountTable
18
19 CREATE TABLE #ItemCountTable (
20 CountOfDescriptions INT
21 ,[Description] NVARCHAR(255)
22 ,LineItemId INT);
23
24 IF @EmployeeId <> -1
25 BEGIN
26 INSERT INTO #ItemCountTable
27 SELECT COUNT(li.Description),li.Description, li.LineItemId
28 FROM
29 SM.RepairOrder ro
30 JOIN SM.RecordNumber rn ON ro.RepairOrderId = rn.RepairOrderId
31 JOIN SM.RepairOrderLineItemSequence lis ON ro.RepairOrderId = lis.RepairOrderId
32 JOIN SM.LaborItem lb ON lis.LineItemId = lb.LineItemId
33 JOIN SM.LineItem li ON lb.LineItemId = li.LineItemId
34 LEFT OUTER JOIN SM.LineItemTechs lit ON lb.LineItemId = lit.LineItemId
35 LEFT OUTER JOIN SM.Employee e ON lit.EmployeeId = e.EmployeeId
36 LEFT OUTER JOIN SM.EmployeeType et ON e.EmployeeTypeId = et.EmployeeTypeId
37 LEFT JOIN SM.UserVehicleAttributes uva ON ro.VehicleId = uva.VehicleId
38 LEFT OUTER JOIN SM.Vehicle v ON ro.VehicleId = v.VehicleId
39 LEFT OUTER JOIN DMV.Make make ON v.MakeId = make.MakeId
40 LEFT OUTER JOIN DMV.SubModel subModel ON v.SubModelId = subModel.SubModelId
41 LEFT OUTER JOIN DMV.Model model ON v.MakeId = model.MakeId and subModel.ModelId = model.ModelId
42 WHERE
43 ro.RepairOrderPhaseId in (6, 7)
44 AND ro.DatePosted BETWEEN @StartTime AND @EndTime
45 AND li.nocommission = 0
46 AND lit.EmployeeId = @EmployeeId
47 GROUP BY li.Description, li.LineItemId
48 ORDER BY li.LineItemId
49 END
50 ELSE
51 BEGIN
52 INSERT INTO #ItemCountTable
53 SELECT COUNT(li.Description), li.Description, li.LineItemId
54 FROM
55 SM.RepairOrder ro
56 JOIN SM.RecordNumber rn ON ro.RepairOrderId = rn.RepairOrderId
57 JOIN SM.RepairOrderLineItemSequence lis ON ro.RepairOrderId = lis.RepairOrderId
58 JOIN SM.LaborItem lb ON lis.LineItemId = lb.LineItemId
59 JOIN SM.LineItem li ON lb.LineItemId = li.LineItemId
60 LEFT OUTER JOIN SM.LineItemTechs lit ON lb.LineItemId = lit.LineItemId
61 LEFT OUTER JOIN SM.Employee e ON lit.EmployeeId = e.EmployeeId
62 LEFT OUTER JOIN SM.EmployeeType et ON e.EmployeeTypeId = et.EmployeeTypeId
63 LEFT JOIN SM.UserVehicleAttributes uva ON ro.VehicleId = uva.VehicleId
64 LEFT OUTER JOIN SM.Vehicle v ON ro.VehicleId = v.VehicleId
65 LEFT OUTER JOIN DMV.Make make ON v.MakeId = make.MakeId
66 LEFT OUTER JOIN DMV.SubModel subModel ON v.SubModelId = subModel.SubModelId
67 LEFT OUTER JOIN DMV.Model model ON v.MakeId = model.MakeId and subModel.ModelId = model.ModelId
68 WHERE
69 ro.RepairOrderPhaseId in (6, 7)
70 AND ro.DatePosted BETWEEN @StartTime AND @EndTime
71 AND li.nocommission = 0
72 GROUP BY li.Description, li.LineItemId
73 ORDER BY li.LineItemId
74 END
75
76IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'#TechPayHoursTable'))
77 DROP TABLE #TechPayHoursTable
78
79CREATE TABLE #TechPayHoursTable (
80 EmployeeId INT
81 ,EmployeeType NVARCHAR(20)
82 ,Sale MONEY
83 ,LineItemDescription NVARCHAR(255)
84 ,Invoice INT
85 ,DatePosted DATETIME
86 ,HoursPay DECIMAL(18,12)
87 ,LineItemType INT
88 ,TechName VARCHAR(100)
89 ,CarNotes NVARCHAR(255)
90 ,[Year] INT
91 ,MakeName NVARCHAR(50)
92 ,ModelName NVARCHAR(50)
93 ,SubModelName NVARCHAR(50)
94 ,LineItemId INT
95 ,DeleteFlag INT
96 );
97
98 BEGIN
99 INSERT INTO #TechPayHoursTable (
100 EmployeeId
101 ,EmployeeType
102 ,Sale
103 ,LineItemDescription
104 ,Invoice
105 ,DatePosted
106 ,HoursPay
107 ,LineItemType
108 ,TechName
109 ,CarNotes
110 ,[Year]
111 ,MakeName
112 ,ModelName
113 ,SubModelName
114 ,LineItemId)
115 (SELECT
116 lit.EmployeeId,
117 et.Description AS EmployeeType,
118 li.Sale AS Sale,
119 LineItemDescription = li.Description,
120 rn.RecordNumberId AS Invoice,
121 ro.DatePosted,
122 ISNULL(lit.HoursPay, 0) AS HoursPay,
123 1 AS LineItemType, -- Labor
124 SM.fnFormatEmployeeName(lit.EmployeeId) AS TechName,
125 NULL,
126 [SM].[VehicleYear](ro.VehicleId),
127 [SM].[VehicleMakeName](ro.VehicleId) as MakeName,
128 SM.VehicleModelNameNoSubmodel(ro.VehicleId) as ModelName,
129 SM.VehicleSubModelName(ro.VehicleId) as SubModelName,
130 li.LineItemId
131 FROM
132 SM.RepairOrder ro
133 JOIN SM.RecordNumber rn ON ro.RepairOrderId = rn.RepairOrderId
134 JOIN SM.RepairOrderLineItemSequence lis ON ro.RepairOrderId = lis.RepairOrderId
135 JOIN SM.LaborItem lb ON lis.LineItemId = lb.LineItemId
136 JOIN SM.LineItem li ON lb.LineItemId = li.LineItemId
137 LEFT OUTER JOIN SM.LineItemTechs lit ON lb.LineItemId = lit.LineItemId
138 LEFT OUTER JOIN SM.Employee e ON lit.EmployeeId = e.EmployeeId
139 LEFT OUTER JOIN SM.EmployeeType et ON e.EmployeeTypeId = et.EmployeeTypeId
140 LEFT OUTER JOIN SM.Vehicle v ON ro.VehicleId = v.VehicleId
141 LEFT OUTER JOIN DMV.Make make ON v.MakeId = make.MakeId
142 LEFT OUTER JOIN DMV.SubModel subModel ON v.SubModelId = subModel.SubModelId
143 LEFT OUTER JOIN DMV.Model model ON v.MakeId = model.MakeId and subModel.ModelId = model.ModelId
144 WHERE
145 ro.RepairOrderPhaseId in (6, 7)
146 AND ro.DatePosted BETWEEN @StartTime AND @EndTime
147 AND li.nocommission = 0
148 AND li.LineItemId in (SELECT LineItemId FROM #ItemCountTable))
149 ORDER BY li.LineItemId
150 END
151
152 SELECT *
153 FROM #TechPayHoursTable
154 ORDER BY LineItemId, LineItemDescription
155
156END