J'aime 0
Fork 0

Dernière activité 1772129580

Carter a révisé ce gist 1772129563. Aller à la révision

1 file changed, 329 insertions

FakeDataConnection.sql(fichier créé)

@@ -0,0 +1,329 @@
1 + -- ============================================================================
2 + -- Setup Growth Plan Activity Demographic for Survey
3 + -- ============================================================================
4 + -- This script creates and populates a demographic to track Growth Plan activity
5 + -- (Not Started, Draft, Shared) for survey participants.
6 + --
7 + -- USAGE: Set the variables below for your target company/survey
8 + -- ============================================================================
9 +
10 + -- ========== CONFIGURATION VARIABLES ==========
11 + DECLARE @CompanyId INT = ; -- Analytics CompanyId
12 + DECLARE @SurveyId INT = ; -- Target Survey ID
13 + DECLARE @DemographicName NVARCHAR(100) = 'Growth Plan Activity';
14 +
15 + -- ========== INTERNAL VARIABLES (Auto-populated) ==========
16 + DECLARE @OrganizationId INT;
17 + DECLARE @DemographicSlot INT;
18 + DECLARE @CompanyDemographicId INT;
19 + DECLARE @CompanyDemographicNameMappingId INT;
20 + DECLARE @CompanyDemographicValueId INT;
21 + DECLARE @CompanyDemographicValueMappingId_NotStarted INT;
22 + DECLARE @CompanyDemographicValueMappingId_Draft INT;
23 + DECLARE @CompanyDemographicValueMappingId_Shared INT;
24 + DECLARE @SurveyDemographicId INT;
25 + DECLARE @DynamicSQL NVARCHAR(MAX);
26 + DECLARE @DemographicColumn NVARCHAR(50);
27 +
28 + BEGIN TRANSACTION;
29 + BEGIN TRY
30 +
31 + -- ========== STEP 1: Lookup OrganizationId from CompanyId ==========
32 + SELECT @OrganizationId = o.Id
33 + FROM Analytics.survey.Organization o
34 + WHERE o.CompanyId = @CompanyId;
35 +
36 + IF @OrganizationId IS NULL
37 + BEGIN
38 + RAISERROR('Unable to find OrganizationId for CompanyId: %d', 16, 1, @CompanyId);
39 + END
40 + ELSE
41 + BEGIN
42 + PRINT 'Found OrganizationId: ' + CAST(@OrganizationId AS VARCHAR) + ' for CompanyId: ' + CAST(@CompanyId AS VARCHAR);
43 + END;
44 +
45 + -- ========== STEP 3: Check if CompanyDemographic exists ==========
46 + SELECT
47 + @CompanyDemographicId = cd.Id,
48 + @CompanyDemographicNameMappingId = cdnm.Id
49 + FROM Analytics.survey.CompanyDemographic cd
50 + INNER JOIN Analytics.survey.CompanyDemographicNameMapping cdnm ON cdnm.CompanyDemographicId = cd.Id
51 + WHERE cd.CompanyId = @CompanyId
52 + AND cdnm.Text = @DemographicName;
53 +
54 + IF @CompanyDemographicId IS NULL
55 + BEGIN
56 + -- ========== Create all Company Demographic structures at once ==========
57 + PRINT 'CompanyDemographic not found. Creating new demographic structure for CompanyId: ' + CAST(@CompanyId AS VARCHAR);
58 +
59 + -- Create CompanyDemographic
60 + INSERT INTO Analytics.survey.CompanyDemographic (CompanyId)
61 + VALUES (@CompanyId);
62 + SET @CompanyDemographicId = SCOPE_IDENTITY();
63 + PRINT ' Created CompanyDemographicId: ' + CAST(@CompanyDemographicId AS VARCHAR);
64 +
65 + -- Create CompanyDemographicNameMapping
66 + INSERT INTO Analytics.survey.CompanyDemographicNameMapping (CompanyDemographicId, Text)
67 + VALUES (@CompanyDemographicId, @DemographicName);
68 + SET @CompanyDemographicNameMappingId = SCOPE_IDENTITY();
69 + PRINT ' Created CompanyDemographicNameMappingId: ' + CAST(@CompanyDemographicNameMappingId AS VARCHAR);
70 +
71 + -- Create CompanyDemographicValue
72 + INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId)
73 + VALUES (@CompanyDemographicId);
74 + SET @CompanyDemographicValueId = SCOPE_IDENTITY();
75 + PRINT ' Created CompanyDemographicValueId: ' + CAST(@CompanyDemographicValueId AS VARCHAR);
76 +
77 + -- Create all three CompanyDemographicValueMappings
78 + INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId)
79 + VALUES ('Not Started', @CompanyDemographicValueId);
80 + SET @CompanyDemographicValueMappingId_NotStarted = SCOPE_IDENTITY();
81 + PRINT ' Created CompanyDemographicValueMapping "Not Started": ' + CAST(@CompanyDemographicValueMappingId_NotStarted AS VARCHAR);
82 +
83 + INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId)
84 + VALUES ('Draft', @CompanyDemographicValueId);
85 + SET @CompanyDemographicValueMappingId_Draft = SCOPE_IDENTITY();
86 + PRINT ' Created CompanyDemographicValueMapping "Draft": ' + CAST(@CompanyDemographicValueMappingId_Draft AS VARCHAR);
87 +
88 + INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId)
89 + VALUES ('Shared', @CompanyDemographicValueId);
90 + SET @CompanyDemographicValueMappingId_Shared = SCOPE_IDENTITY();
91 + PRINT ' Created CompanyDemographicValueMapping "Shared": ' + CAST(@CompanyDemographicValueMappingId_Shared AS VARCHAR);
92 + END
93 + ELSE
94 + BEGIN
95 + -- ========== CompanyDemographic exists, retrieve all related IDs ==========
96 + PRINT 'Found existing CompanyDemographic structure:';
97 + PRINT ' CompanyDemographicId: ' + CAST(@CompanyDemographicId AS VARCHAR);
98 + PRINT ' CompanyDemographicNameMappingId: ' + CAST(@CompanyDemographicNameMappingId AS VARCHAR);
99 +
100 + -- Get CompanyDemographicValueId
101 + SELECT @CompanyDemographicValueId = Id
102 + FROM Analytics.survey.CompanyDemographicValue
103 + WHERE CompanyDemographicId = @CompanyDemographicId;
104 + PRINT ' CompanyDemographicValueId: ' + CAST(@CompanyDemographicValueId AS VARCHAR);
105 +
106 + -- Get all three CompanyDemographicValueMapping IDs
107 + SELECT @CompanyDemographicValueMappingId_NotStarted = Id
108 + FROM Analytics.survey.CompanyDemographicValueMapping
109 + WHERE CompanyDemographicValueId = @CompanyDemographicValueId
110 + AND Text = 'Not Started';
111 + PRINT ' ValueMapping "Not Started": ' + CAST(@CompanyDemographicValueMappingId_NotStarted AS VARCHAR);
112 +
113 + SELECT @CompanyDemographicValueMappingId_Draft = Id
114 + FROM Analytics.survey.CompanyDemographicValueMapping
115 + WHERE CompanyDemographicValueId = @CompanyDemographicValueId
116 + AND Text = 'Draft';
117 + PRINT ' ValueMapping "Draft": ' + CAST(@CompanyDemographicValueMappingId_Draft AS VARCHAR);
118 +
119 + SELECT @CompanyDemographicValueMappingId_Shared = Id
120 + FROM Analytics.survey.CompanyDemographicValueMapping
121 + WHERE CompanyDemographicValueId = @CompanyDemographicValueId
122 + AND Text = 'Shared';
123 + PRINT ' ValueMapping "Shared": ' + CAST(@CompanyDemographicValueMappingId_Shared AS VARCHAR);
124 + END
125 +
126 + -- ========== STEP 4: Check if SurveyDemographic exists ==========
127 + SELECT @SurveyDemographicId = Id
128 + FROM Analytics.survey.SurveyDemographic
129 + WHERE SurveyId = @SurveyId
130 + AND CompanyDemographicNameMappingId = @CompanyDemographicNameMappingId;
131 +
132 + IF @SurveyDemographicId IS NULL
133 + BEGIN
134 + -- ========== Create SurveyDemographic and all SurveyDemographicValues at once ==========
135 + PRINT 'SurveyDemographic not found. Creating demographic for SurveyId: ' + CAST(@SurveyId AS VARCHAR) + ', Slot: ' + CAST(@DemographicSlot AS VARCHAR);
136 +
137 +
138 + -- ========== Find next available demographic slot (<=200) ==========
139 + WITH UsedSlots AS (
140 + SELECT Slot
141 + FROM Analytics.survey.SurveyDemographic
142 + WHERE SurveyId = @SurveyId
143 + ),
144 + AvailableSlots AS (
145 + SELECT TOP 1 n AS Slot
146 + FROM (
147 + SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
148 + FROM sys.all_objects
149 + ) Numbers
150 + WHERE n <= 200
151 + AND n NOT IN (SELECT Slot FROM UsedSlots)
152 + ORDER BY n
153 + )
154 + SELECT @DemographicSlot = Slot
155 + FROM AvailableSlots;
156 +
157 + IF @DemographicSlot IS NULL
158 + BEGIN
159 + RAISERROR('No available demographic slots found (all slots 1-200 are in use for SurveyId: %d)', 16, 1, @SurveyId);
160 + END
161 + ELSE
162 + BEGIN
163 + PRINT 'Selected available demographic slot: ' + CAST(@DemographicSlot AS VARCHAR);
164 + END
165 +
166 + -- Create SurveyDemographic
167 + INSERT INTO Analytics.survey.SurveyDemographic (
168 + SurveyId,
169 + Slot,
170 + Name,
171 + DemographicType,
172 + SliceAccessLevel,
173 + FilterAccessLevel,
174 + IsSelfReported,
175 + SurveyQuestionId,
176 + VisibilityLevel,
177 + CompanyDemographicNameMappingId
178 + )
179 + VALUES (
180 + @SurveyId,
181 + @DemographicSlot,
182 + @DemographicName,
183 + 1, -- DemographicType
184 + 2, -- SliceAccessLevel
185 + 2, -- FilterAccessLevel
186 + 0, -- IsSelfReported
187 + NULL, -- SurveyQuestionId
188 + 1, -- VisibilityLevel
189 + @CompanyDemographicNameMappingId
190 + );
191 + SET @SurveyDemographicId = SCOPE_IDENTITY();
192 + PRINT ' Created SurveyDemographicId: ' + CAST(@SurveyDemographicId AS VARCHAR);
193 +
194 + -- Create all three SurveyDemographicValues
195 + INSERT INTO Analytics.survey.SurveyDemographicValue (
196 + SurveyDemographicId,
197 + SurveyId,
198 + Value,
199 + Code,
200 + TextValue1,
201 + CompanyDemographicValueMappingId
202 + )
203 + VALUES (
204 + @SurveyDemographicId,
205 + @SurveyId,
206 + 'Not Started',
207 + 0,
208 + 'Not Started',
209 + @CompanyDemographicValueMappingId_NotStarted
210 + );
211 + PRINT ' Created SurveyDemographicValue "Not Started"';
212 +
213 + INSERT INTO Analytics.survey.SurveyDemographicValue (
214 + SurveyDemographicId,
215 + SurveyId,
216 + Value,
217 + Code,
218 + TextValue1,
219 + CompanyDemographicValueMappingId
220 + )
221 + VALUES (
222 + @SurveyDemographicId,
223 + @SurveyId,
224 + 'Draft',
225 + 1,
226 + 'Draft',
227 + @CompanyDemographicValueMappingId_Draft
228 + );
229 + PRINT ' Created SurveyDemographicValue "Draft"';
230 +
231 + INSERT INTO Analytics.survey.SurveyDemographicValue (
232 + SurveyDemographicId,
233 + SurveyId,
234 + Value,
235 + Code,
236 + TextValue1,
237 + CompanyDemographicValueMappingId
238 + )
239 + VALUES (
240 + @SurveyDemographicId,
241 + @SurveyId,
242 + 'Shared',
243 + 2,
244 + 'Shared',
245 + @CompanyDemographicValueMappingId_Shared
246 + );
247 + PRINT ' Created SurveyDemographicValue "Shared"';
248 +
249 + -- Update the slot variable to the one we used
250 + SELECT @DemographicSlot = Slot
251 + FROM Analytics.survey.SurveyDemographic
252 + WHERE Id = @SurveyDemographicId;
253 + END
254 + ELSE
255 + BEGIN
256 + -- ========== SurveyDemographic exists, retrieve the slot ==========
257 + PRINT 'Found existing SurveyDemographic:';
258 + PRINT ' SurveyDemographicId: ' + CAST(@SurveyDemographicId AS VARCHAR);
259 +
260 + SELECT @DemographicSlot = Slot
261 + FROM Analytics.survey.SurveyDemographic
262 + WHERE Id = @SurveyDemographicId;
263 + PRINT ' Using existing Slot: ' + CAST(@DemographicSlot AS VARCHAR);
264 + END
265 +
266 +
267 + -- ========== STEP 5: Update SurveyParticipant with Growth Plan State ==========
268 + PRINT 'Updating SurveyParticipant Growth Plan state for demographic slot ' + CAST(@DemographicSlot AS VARCHAR) + '...';
269 +
270 + SET @DemographicColumn = 'DemographicValue' + CAST(@DemographicSlot AS VARCHAR);
271 + SET @DynamicSQL = N'
272 + UPDATE sp
273 + SET sp.' + @DemographicColumn + N' = growthUser.GrowthPlanState
274 + FROM Analytics.survey.SurveyParticipant sp
275 + JOIN (
276 + SELECT au.Id,
277 + CASE
278 + WHEN gp.PublishedAt IS NOT NULL THEN 2
279 + WHEN gp.LastActivityAt > gp.CreatedAt OR cv.UpdatedAt > cv.CreatedAt THEN 1
280 + ELSE 0
281 + END AS GrowthPlanState
282 + FROM Growth.develop.GrowthPlan gp
283 + JOIN Growth.ec.[User] u ON u.Id = gp.OwnerId
284 + JOIN Analytics.survey.Users au ON au.MembershipId = u.Id
285 + LEFT JOIN Growth.develop.CareerVision cv on cv.UserId = u.Id
286 + WHERE u.OrganizationId = @OrganizationId
287 + ) growthUser ON sp.UserId = growthUser.Id AND sp.SurveyId = @SurveyId';
288 +
289 + EXEC sp_executesql @DynamicSQL,
290 + N'@OrganizationId INT, @SurveyId INT',
291 + @OrganizationId = @OrganizationId,
292 + @SurveyId = @SurveyId;
293 +
294 + PRINT 'SurveyParticipant update complete.';
295 +
296 + COMMIT TRANSACTION;
297 + PRINT '';
298 + PRINT '========== SUCCESS: All operations completed successfully ==========';
299 + PRINT '';
300 + PRINT 'Summary:';
301 + PRINT ' CompanyId: ' + CAST(@CompanyId AS VARCHAR);
302 + PRINT ' OrganizationId: ' + CAST(@OrganizationId AS VARCHAR);
303 + PRINT ' SurveyId: ' + CAST(@SurveyId AS VARCHAR);
304 + PRINT ' Demographic Slot: ' + CAST(@DemographicSlot AS VARCHAR);
305 + PRINT ' Demographic Name: ' + @DemographicName;
306 + PRINT '';
307 + PRINT 'Generated IDs:';
308 + PRINT ' CompanyDemographicId: ' + CAST(@CompanyDemographicId AS VARCHAR);
309 + PRINT ' CompanyDemographicNameMappingId: ' + CAST(@CompanyDemographicNameMappingId AS VARCHAR);
310 + PRINT ' CompanyDemographicValueId: ' + CAST(@CompanyDemographicValueId AS VARCHAR);
311 + PRINT ' ValueMapping - Not Started: ' + CAST(@CompanyDemographicValueMappingId_NotStarted AS VARCHAR);
312 + PRINT ' ValueMapping - Draft: ' + CAST(@CompanyDemographicValueMappingId_Draft AS VARCHAR);
313 + PRINT ' ValueMapping - Shared: ' + CAST(@CompanyDemographicValueMappingId_Shared AS VARCHAR);
314 + PRINT ' SurveyDemographicId: ' + CAST(@SurveyDemographicId AS VARCHAR);
315 +
316 + END TRY
317 + BEGIN CATCH
318 + IF @@TRANCOUNT > 0
319 + ROLLBACK TRANSACTION;
320 +
321 + PRINT '';
322 + PRINT '========== ERROR: Transaction rolled back ==========';
323 + PRINT 'Error Message: ' + ERROR_MESSAGE();
324 + PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR);
325 + PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR);
326 + PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR);
327 +
328 + THROW;
329 + END CATCH;
Plus récent Plus ancien

Propulsé par Opengist ⋅ Load: 89ms⋅

Français