· 10 years ago · Aug 26, 2016, 01:24 PM
1USE [DEVCOM_DELPHI_FULL]
2GO
3/****** Object: StoredProcedure [dbo].[api_CreateTempTables] Script Date: 8/26/2016 4:27:49 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9
10
11-- =============================================
12-- Author: <Author,,Name>
13-- Create date: <Create Date,,>
14-- Description: <Description,,>
15-- =============================================
16ALTER PROCEDURE [dbo].[api_CreateTempTables]
17 @projectName varchar(512),
18 @start char(10),
19 @end char(10),
20 @employees varchar(4096)
21AS
22BEGIN
23SET NOCOUNT ON;
24
25DECLARE @startDate as smalldatetime;
26DECLARE @endDate as smalldatetime;
27
28SET @startDate = CAST (@start AS smalldatetime);
29SET @endDate = CAST (@end AS smalldatetime);
30
31DECLARE @projectId AS INT;
32
33-- table with reportings from database
34
35IF NOT EXISTS (SELECT * FROM sys.objects
36WHERE object_id = OBJECT_ID(N'[dbo].[aperture_tempDBTable]') AND type in (N'U'))
37 CREATE TABLE aperture_tempDBTable(
38 employeeId int,
39 reportdate smalldatetime,
40 starttime smalldatetime,
41 task varchar(4096),
42 rephours int,
43 invoiced int,
44 descr varchar(4096),
45 taskId int,
46 reportingId int
47 )
48ELSE
49 DELETE FROM aperture_tempDBTable;
50
51
52
53-- table with reportings from spreadsheet
54
55IF NOT EXISTS (SELECT * FROM sys.objects
56WHERE object_id = OBJECT_ID(N'[dbo].[aperture_tempSSTable]') AND type in (N'U'))
57 CREATE TABLE aperture_tempSSTable(
58 employeeId int,
59 reportdate smalldatetime,
60 starttime smalldatetime,
61 task varchar(4096),
62 rephours int,
63 invoiced int,
64 descr varchar(4096)
65 )
66ELSE
67 DELETE FROM aperture_tempSSTable;
68
69
70
71-- employees from spreadsheet ---------------------------------------------------
72
73IF NOT EXISTS (SELECT * FROM sys.objects
74WHERE object_id = OBJECT_ID(N'[dbo].[aperture_tempEmployees]') AND type in (N'U'))
75 CREATE TABLE aperture_tempEmployees( name varchar(128), id int );
76ELSE
77 DELETE FROM aperture_tempEmployees;
78
79INSERT INTO aperture_tempEmployees(name) SELECT * FROM fn_ParseDelimitedString(@employees,',');
80
81UPDATE aperture_tempEmployees
82 SET id = (SELECT Employee_ID FROM Employee WHERE Name + ' ' + Last_Name = aperture_tempEmployees.name AND Is_Deleted = 'False');
83
84--------------------------------------------------------------------------------
85
86-- dates range from spreadsheet -------------------------------------------------
87IF NOT EXISTS (SELECT * FROM sys.objects
88WHERE object_id = OBJECT_ID(N'[dbo].[aperture_dates]') AND type in (N'U'))
89 CREATE TABLE aperture_dates( [date] smalldatetime );
90ELSE
91 DELETE FROM aperture_dates;
92
93
94
95WITH DateRanges AS
96(
97 SELECT @startDate AS 'DateValue'
98 UNION ALL
99 SELECT DATEADD(DAY, 1, DateValue)
100 FROM DateRanges
101 WHERE DateValue < @endDate
102)
103INSERT INTO aperture_dates SELECT DateValue FROM DateRanges;
104
105
106-----------------------------------------------------------------------------------
107----prepare projectID----
108DECLARE @TaskPathTable AS TABLE ([Order] int identity(1, 1), Folder varchar(4096));
109
110INSERT INTO @TaskPathTable
111 SELECT Value AS Folder
112 FROM fn_ParseDelimitedString(@projectName, '/');
113
114DECLARE @folderQty AS int;
115SELECT @folderQty = @@ROWCOUNT;
116
117DECLARE @client AS varchar(50);
118SELECT @client = Folder
119 FROM @TaskPathTable
120 WHERE [Order] = 1;
121DECLARE @project AS varchar(50);
122SELECT @project = Folder
123 FROM @TaskPathTable
124 WHERE [Order] = 2;
125
126SELECT @projectId = Project_ID
127 FROM Project
128 WHERE Project_Group = @client AND [Description] = @project;
129IF @projectId IS NULL BEGIN
130 RAISERROR('Invalid client/project combination ''%s/%s''.', 18, 1, @client, @project);
131 RETURN 0;
132 END;
133
134------------------------------------------------------------------------------------------------
135--populate aperture_tempDBTable
136
137INSERT INTO aperture_tempDBTable(employeeId, reportdate, starttime, rephours, invoiced, taskId, reportingId, activityType)
138 SELECT Employee_ID,
139 [Start_Date],
140 [Start_Time],
141 DATEPART(hour,[Report_Dur]),
142 DATEPART(hour,[Invoice_Dur]),
143 Task_ID,
144 Reporting_ID,
145 Activity_Type
146 FROM tblReporting
147 WHERE Project_ID = @projectId
148 AND Employee_ID IN (SELECT id FROM aperture_tempEmployees)
149 AND [Start_Date] IN (SELECT [date] FROM aperture_dates);
150 --AND [Start_Time] = '00:00';
151
152
153-- set task and description
154DECLARE dbCursor CURSOR FOR
155SELECT reportingId FROM aperture_tempDBTable
156DECLARE @reportingID AS INT;
157DECLARE @tsk AS VARCHAR(1000);
158DECLARE @tskID AS INT;
159
160OPEN dbCursor
161FETCH NEXT FROM dbCursor INTO @reportingID
162
163WHILE @@FETCH_STATUS = 0
164BEGIN
165 SET @tsk = '';
166 SET @tskID = ( SELECT Task_ID FROM tblReporting WHERE Reporting_ID = @reportingID );
167 WHILE ( @tskID IS NOT NULL )
168 BEGIN
169 SET @tsk = '/' + ( SELECT Name FROM Tasks WHERE Task_ID = @tskID ) + @tsk;
170 SET @tskID = (SELECT Parent_Task_ID FROM Tasks WHERE Task_ID = @tskID);
171 END
172
173 SET @tsk = '/' + ( SELECT Project_Group FROM Project WHERE Project_ID = @projectID ) + '/' + ( SELECT [Description] FROM Project WHERE Project_ID = @projectID ) + @tsk;
174
175 UPDATE aperture_tempDBTable
176 SET task = @tsk, descr = (SELECT [Description] FROM tblReporting WHERE Reporting_ID = @reportingID) WHERE reportingID = @reportingID
177
178 FETCH NEXT FROM dbCursor INTO @reportingID
179END
180
181CLOSE dbCursor
182DEALLOCATE dbCursor
183
184RETURN @projectId;
185END