· 6 years ago · Jun 22, 2019, 05:34 AM
1ALTER PROCEDURE [dbo].[proc_Upload] WITH RECOMPILE
2as
3set NoCount on
4
5DECLARE
6 @StartTime datetime,
7 @EndTime datetime,
8 @DataID uniqueidentifier,
9 @CollectionDate datetime,
10 @Status int,
11 @PeriodID int,
12 @EndDate datetime,
13 @GroupID uniqueidentifier,
14 @ProjectID INT,
15 @FAID uniqueidentifier,
16 @UploadID int ,
17 @Createdate datetime
18
19
20declare @projects table(
21 [ProjectID] [INT],
22 [Title] [varchar](255) ,
23 [currency] [int] ,
24 [Cost_Until_Today] [float] ,
25 [StartDate] [datetime] ,
26 [EndDate] [datetime] ,
27 [MisID] [uniqueidentifier] ,
28 [SystemStatus] [int] ,
29 [FacilitatingAgency] [uniqueidentifier] ,
30 [SyncMode] [int] )
31
32 INSERT @projects
33 SELECT ID, PROJECTNAME, PROJECTCURRENCY_ID, Cost_Until_Today, PROJECTESTABLISHEDDATE, EndDate, MisID, 4, FacilitatingAgency, SyncMode
34 FROM [dbo].[T_PROJECTS] /*thsi is the source table where every projectIDs need to be processed*/
35
36
37
38 IF exists ( select *
39 from dbo.sysobjects
40 where id = object_id(N'[dbo].[datasets]') and objectproperty(id, N'IsTable') = 1 )
41 DROP Table [dbo].datasets
42
43
44 SELECT mgd.Gd_ID, mg.Grp_ID, mgd.Gd_CollectionDate, mgd.Gd_IsDeleted, mgd.Gd_CreateDate, mg.Grp_Project, mg.Grp_IsDeleted , mg.Grp_Legacy_ID, p.LegacyProjectId
45 INTO datasets
46 FROM Savix_Service_Group..Group_Data mgd
47 INNER JOIN Savix_Service_Group..Group_Cycle mgc ON mgc.Gc_ID = mgd.Gd_CycleID
48 INNER JOIN Savix_Service_Group..Groups mg ON mg.Grp_ID = mgc.Gc_GroupID
49 inner join SG_Dynamic_Forms..v_projects p ON p.ProjectID = mg.Grp_Project
50
51
52DECLARE projects1 CURSOR LOCAL FOR Select distinct ProjectID, isnull([FacilitatingAgency], '00000000-0000-0000-0000-000000000000') from @projects P WHERE P.SystemStatus = 4 AND P.SyncMode = 1
53/*First cursor - fetch the cursor from ProjectaTable*/
54
55
56OPEN projects1
57FETCH NEXT FROM projects1 INTO @ProjectID, @FAID
58WHILE @@FETCH_STATUS = 0
59BEGIN
60 BEGIN TRY
61 BEGIN TRAN
62
63 DELETE FROM T_PROJECTGROUPSDATA
64 WHERE T_PROJECTGROUPSDATA.UPLOAD_ID IN (SELECT ID FROM T_UPLOADS WHERE project_savix_ID = @ProjectID AND UPLOADFILENAME = 'Automatic upload from web MIS')
65
66 DECLARE datasets CURSOR LOCAL FAST_FORWARD FOR SELECT Gd_ID, Grp_ID, Gd_CollectionDate, Gd_IsDeleted, Gd_CreateDate
67 FROM datasets
68 WHERE LegacyProjectId = @ProjectID AND Grp_IsDeleted = 0 AND Gd_IsDeleted != 1
69 /*Second cursor - this will get the 'collectionDate'field from datasetsTable for every project fetched in above cursor and also get @dataID which is used to insert value in to other table-T_PROJECTGROUPSDATA*/
70 OPEN datasets
71
72 FETCH NEXT FROM datasets INTO @DataID, @GroupID, @CollectionDate, @Status, @Createdate
73 WHILE @@FETCH_STATUS = 0
74 BEGIN
75 DECLARE period CURSOR LOCAL FAST_FORWARD FOR SELECT ID, dbo.fn_GetEndOfPeriod(ID) FROM T_PERIODS
76 /* dbo.fn_GetEndOfPeriod(ID) - this function will give the end of the date of that specifc quarter for any given date*/
77 WHERE DATEDIFF(dd,@CollectionDate,dbo.fn_GetEndOfPeriod(ID)) >= 0
78 ORDER BY [YEAR],[Quarter]
79 /*Third Cursor - this will process the records from another table called period with above fetched @collectionDate*/
80
81 OPEN period
82 FETCH NEXT FROM period INTO @PeriodID, @EndDate
83 WHILE @@FETCH_STATUS = 0
84 BEGIN
85
86 IF EXISTS (SELECT * FROM Savix_Service_Group..Group_Data mgd
87 INNER JOIN Savix_Service_Group..Group_Cycle mgc ON mgc.Gc_ID = mgd.Gd_CycleID
88 WHERE mgc.Gc_GroupID = @GroupID
89 AND DATEDIFF(dd,mgd.Gd_CollectionDate,@EndDate) >= 0
90 AND (mgd.Gd_CollectionDate > @CollectionDate )
91 AND mgd.Gd_IsDeleted != 1)
92
93 BEGIN
94 BREAK
95 END
96
97 IF EXISTS (SELECT ID FROM T_UPLOADS u
98 WHERE u.project_savix_ID = @ProjectID AND u.PERIOD_ID = @PeriodID AND u.STATUS = 3 AND UPLOADFILENAME != 'Automatic upload from web MIS')
99 BEGIN
100 FETCH NEXT FROM period INTO @PeriodID, @EndDate
101 CONTINUE
102 END
103
104 SET @UploadID = (SELECT ID FROM T_UPLOADS u WHERE u.project_savix_ID = @ProjectID AND u.PERIOD_ID = @PeriodID AND u.STATUS = 3)
105
106 /*If T_uploads doesn't have appropirate period ID from cursor fetch then create a new entry in T_uploads with current projectID*/
107
108 IF @UploadID IS NULL
109 BEGIN
110
111 declare @Project_ID_Legacy int = ISNULL((select distinct PROJECT_ID from T_UPLOADS where project_savix_ID = @ProjectID),@ProjectID)
112
113 INSERT INTO T_UPLOADSFIRSTSTEP
114 (PROJECT_ID
115 ,UPLOADDATE
116 ,UPLOADFILENAME
117 ,UPLOADUSER_ID
118 ,CURRENTSTEP
119 ,STATUS
120 ,Project_ID_MIS)
121 SELECT @Project_ID_Legacy , --mm 06/12
122 GETDATE(),
123 'Automatic upload from web MIS',
124 2,
125 2,
126 0,
127 @ProjectID
128
129 INSERT INTO T_UPLOADS
130 (ID, periodID, projectID,UPLOADDATE,UPLOADFILENAME,UPLOADUSER_ID )
131 SELECT uf.ID,
132 @PeriodID,
133 uf.PROJECT_ID,
134 uf.UPLOADDATE,
135 uf.UPLOADFILENAME,
136 uf.UPLOADUSER_ID
137
138 FROM T_UPLOADSFIRSTSTEP uf
139 INNER JOIN @projects mp ON uf.Project_ID_MIS = mp.ProjectID
140 WHERE uf.Project_ID_MIS = @ProjectID AND uf.STATUS = 0
141 AND NOT EXISTS (SELECT * FROM T_UPLOADS u WHERE u.PROJECT_ID = uf.PROJECT_ID AND u.PERIOD_ID = @PeriodID AND u.STATUS = 3)
142
143 DELETE FROM T_UPLOADSFIRSTSTEP WHERE STATUS = 0 AND PROJECT_ID = @Project_ID_Legacy
144
145 SET @UploadID = (SELECT ID FROM T_UPLOADS u WHERE u.project_savix_ID = @ProjectID AND u.PERIOD_ID = @PeriodID AND u.STATUS = 3)
146 END ELSE
147 UPDATE T_UPLOADS SET
148 TOTALEXPENDITURES = CASE WHEN DATEDIFF(d,mp.StartDate,mp.EndDate) != 0
149 THEN mp.Cost_Until_Today*DATEDIFF(d,mp.StartDate,dbo.fn_GetEndOfPeriod(@PeriodID))/DATEDIFF(d,mp.StartDate,mp.EndDate)/dbo.fn_RateAtDate(mp.Currency,dbo.fn_GetEndOfPeriod(@PeriodID))
150 ELSE 0 END,
151 TotalExpendituresNative = CASE WHEN DATEDIFF(d,mp.StartDate,mp.EndDate) != 0
152 THEN mp.Cost_Until_Today*DATEDIFF(d,mp.StartDate,dbo.fn_GetEndOfPeriod(@PeriodID))/DATEDIFF(d,mp.StartDate,mp.EndDate)
153 ELSE 0 END
154 FROM @projects mp
155 WHERE T_UPLOADS.ID = @UploadID AND mp.ProjectID = @ProjectID
156
157 INSERT INTO T_PROJECTGROUPSDATA
158 (uploadID, fieldA,fieldB,......fieldN )
159
160 SELECT @UploadID,p.fieldA,mg.fieldB,......mgc.fieldN
161 FROM @projects mp
162 inner join SG_Dynamic_Forms..v_projects p ON p.LegacyProjectId = mp.projectID
163 inner join Savix_Service_Group..Groups mg ON mg.Grp_Project = p.ProjectID
164 INNER JOIN Savix_Service_Group..Group_Cycle mgc ON mgc.Gc_GroupID = mg.Grp_ID
165 INNER JOIN Savix_Service_Group..Group_Data mgd ON mgd.Gd_CycleID = mgc.Gc_ID
166 LEFT JOIN Savix_Service_Trainers..Trainers me ON me.Tr_ID = mgc.Gc_MonitoredBy
167 LEFT JOIN Savix_Service_Dictionaries..Dictionary mgt ON mgt.Dny_ID = me.Tr_Type
168 left join v1_Report_UDF_Data_UploadToSavix udf on udf.DataID = mgd.Gd_ID
169 WHERE mgd.Gd_ID = @DataID
170
171 FETCH NEXT FROM period INTO @PeriodID, @EndDate
172 END
173 CLOSE period
174 DEALLOCATE period
175
176 FETCH NEXT FROM datasets INTO @DataID, @GroupID, @CollectionDate, @Status, @Createdate
177 END
178
179 CLOSE datasets
180 DEALLOCATE datasets
181
182 COMMIT
183 END TRY
184 BEGIN CATCH
185
186
187
188 SELECT ERROR_NUMBER(), ERROR_MESSAGE(),@PeriodID, @ProjectID, @UploadID,@DataID
189 IF CURSOR_STATUS('global' , 'period') >= 0
190 BEGIN
191 CLOSE period
192 DEALLOCATE uploadID
193 END
194
195 IF CURSOR_STATUS('global' , 'datasets') >= 0
196 BEGIN
197 CLOSE datasets
198 DEALLOCATE datasets
199 END
200
201 IF @@TRANCOUNT > 0
202 ROLLBACK
203
204 INSERT INTO error_catch_UploadtoSavix
205
206 SELECT cast(ERROR_NUMBER() as nvarchar), ERROR_MESSAGE(),@PeriodID, @ProjectID, @UploadID,@DataID, getdate()
207 END CATCH
208
209
210 FETCH NEXT FROM projects1 INTO @ProjectID, @FAID
211END
212
213
214CLOSE projects1
215DEALLOCATE projects1
216
217SELECT 1 as success
218
219select s.ID,u.*
220from T_PROJECTS_TEMP pt
221INNER JOIN
222 (SELECT mgd.Gd_ID, mg.Grp_ID, mgd.Gd_CollectionDate, mgd.Gd_IsDeleted, mgd.Gd_CreateDate, mg.Grp_Project, mg.Grp_IsDeleted , mg.Grp_Legacy_ID, p.LegacyProjectId
223 FROM Savix_Service_Group..Group_Data mgd
224 INNER JOIN Savix_Service_Group..Group_Cycle mgc ON mgc.Gc_ID = mgd.Gd_CycleID
225 INNER JOIN Savix_Service_Group..Groups mg ON mg.Grp_ID = mgc.Gc_GroupID
226 inner join SG_Dynamic_Forms..v_projects p ON p.ProjectID = mg.Grp_Project
227 WHERE LegacyProjectId = 5047 AND Grp_IsDeleted = 0 AND Gd_IsDeleted != 1 ) dataset on pt.ID = dataset.LegacyProjectId
228INNER JOIN T_PERIODS s ON DATEDIFF(DAY,dataset.Gd_CollectionDate,dbo.fn_GetEndOfPeriod(s.ID)) >= 0
229LEFT JOIN T_UPLOADS u ON u.project_savix_ID = pt.ID AND u.PERIOD_ID = s.ID AND u.STATUS = 3 --AND u.UPLOADFILENAME != 'Automatic upload from web MIS'
230
231WHERE NOT EXISTS (SELECT * FROM Savix_Service_Group..Group_Data mgd
232 INNER JOIN Savix_Service_Group..Group_Cycle mgc ON mgc.Gc_ID = mgd.Gd_CycleID
233 WHERE mgc.Gc_GroupID = Grp_ID
234 AND DATEDIFF(dd,mgd.Gd_CollectionDate,(select dbo.fn_GetEndOfPeriod(s.ID))) >= 0
235 AND (mgd.Gd_CollectionDate > Gd_CollectionDate) AND mgd.Gd_IsDeleted != 1 )
236 AND NOT EXISTS (SELECT ID FROM T_UPLOADS u
237 WHERE u.project_savix_ID = pt.ID AND u.PERIOD_ID = s.ID AND u.STATUS = 3 AND UPLOADFILENAME != 'Automatic upload from web MIS')
238
239ORDER BY s.ID