Tetszik 0
Fork 0

Utoljára aktív 1778279618

Carter gist felülvizsgálása 1778279618. Revízióhoz ugrás

1 file changed, 413 insertions

FakedDataConnection-GrowthPlans-MacLeanFogg-NotLoggedIn.sql(fájl létrehozva)

@@ -0,0 +1,413 @@
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, Not Logged In) for survey participants.
6 + --
7 + -- USAGE: Set the variables below for your target company/survey
8 + -- ============================================================================
9 +
10 + -- ========== CONFIGURATION VARIABLES ==========
11 + DECLARE @CompanyId INT = 2077; -- Analytics CompanyId
12 + DECLARE @SurveyId INT = 54446; -- 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_NotStarted INT;
21 + DECLARE @CompanyDemographicValueId_Draft INT;
22 + DECLARE @CompanyDemographicValueId_Shared INT;
23 + DECLARE @CompanyDemographicValueId_NotLoggedIn INT;
24 + DECLARE @CompanyDemographicValueMappingId_NotStarted INT;
25 + DECLARE @CompanyDemographicValueMappingId_Draft INT;
26 + DECLARE @CompanyDemographicValueMappingId_Shared INT;
27 + DECLARE @CompanyDemographicValueMappingId_NotLoggedIn INT;
28 + DECLARE @SurveyDemographicId INT;
29 + DECLARE @DynamicSQL NVARCHAR(MAX);
30 + DECLARE @DemographicColumn NVARCHAR(50);
31 +
32 + BEGIN TRANSACTION;
33 + BEGIN TRY
34 +
35 + -- ========== STEP 1: Lookup OrganizationId from CompanyId ==========
36 + SELECT @OrganizationId = o.Id
37 + FROM Analytics.survey.Organization o
38 + WHERE o.CompanyId = @CompanyId;
39 +
40 + IF @OrganizationId IS NULL
41 + BEGIN
42 + RAISERROR('Unable to find OrganizationId for CompanyId: %d', 16, 1, @CompanyId);
43 + END
44 + ELSE
45 + BEGIN
46 + PRINT 'Found OrganizationId: ' + CAST(@OrganizationId AS VARCHAR) + ' for CompanyId: ' + CAST(@CompanyId AS VARCHAR);
47 + END;
48 +
49 + -- ========== STEP 3: Check if CompanyDemographic exists ==========
50 + SELECT
51 + @CompanyDemographicId = cd.Id,
52 + @CompanyDemographicNameMappingId = cdnm.Id
53 + FROM Analytics.survey.CompanyDemographic cd
54 + INNER JOIN Analytics.survey.CompanyDemographicNameMapping cdnm ON cdnm.CompanyDemographicId = cd.Id
55 + WHERE cd.CompanyId = @CompanyId
56 + AND cdnm.Text = @DemographicName;
57 +
58 + IF @CompanyDemographicId IS NULL
59 + BEGIN
60 + -- ========== Create all Company Demographic structures at once ==========
61 + PRINT 'CompanyDemographic not found. Creating new demographic structure for CompanyId: ' + CAST(@CompanyId AS VARCHAR);
62 +
63 + -- Create CompanyDemographic
64 + INSERT INTO Analytics.survey.CompanyDemographic (CompanyId)
65 + VALUES (@CompanyId);
66 + SET @CompanyDemographicId = SCOPE_IDENTITY();
67 + PRINT ' Created CompanyDemographicId: ' + CAST(@CompanyDemographicId AS VARCHAR);
68 +
69 + -- Create CompanyDemographicNameMapping
70 + INSERT INTO Analytics.survey.CompanyDemographicNameMapping (CompanyDemographicId, Text)
71 + VALUES (@CompanyDemographicId, @DemographicName);
72 + SET @CompanyDemographicNameMappingId = SCOPE_IDENTITY();
73 + PRINT ' Created CompanyDemographicNameMappingId: ' + CAST(@CompanyDemographicNameMappingId AS VARCHAR);
74 +
75 + INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId)
76 + VALUES (@CompanyDemographicId);
77 + SET @CompanyDemographicValueId_NotStarted = SCOPE_IDENTITY();
78 + INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId)
79 + VALUES ('Not Started', @CompanyDemographicValueId_NotStarted);
80 + SET @CompanyDemographicValueMappingId_NotStarted = SCOPE_IDENTITY();
81 + PRINT ' Created CompanyDemographicValue/Mapping "Not Started": value=' + CAST(@CompanyDemographicValueId_NotStarted AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_NotStarted AS VARCHAR);
82 +
83 + INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId)
84 + VALUES (@CompanyDemographicId);
85 + SET @CompanyDemographicValueId_Draft = SCOPE_IDENTITY();
86 + INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId)
87 + VALUES ('Draft', @CompanyDemographicValueId_Draft);
88 + SET @CompanyDemographicValueMappingId_Draft = SCOPE_IDENTITY();
89 + PRINT ' Created CompanyDemographicValue/Mapping "Draft": value=' + CAST(@CompanyDemographicValueId_Draft AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_Draft AS VARCHAR);
90 +
91 + INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId)
92 + VALUES (@CompanyDemographicId);
93 + SET @CompanyDemographicValueId_Shared = SCOPE_IDENTITY();
94 + INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId)
95 + VALUES ('Shared', @CompanyDemographicValueId_Shared);
96 + SET @CompanyDemographicValueMappingId_Shared = SCOPE_IDENTITY();
97 + PRINT ' Created CompanyDemographicValue/Mapping "Shared": value=' + CAST(@CompanyDemographicValueId_Shared AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_Shared AS VARCHAR);
98 +
99 + INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId)
100 + VALUES (@CompanyDemographicId);
101 + SET @CompanyDemographicValueId_NotLoggedIn = SCOPE_IDENTITY();
102 + INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId)
103 + VALUES ('Not Logged In', @CompanyDemographicValueId_NotLoggedIn);
104 + SET @CompanyDemographicValueMappingId_NotLoggedIn = SCOPE_IDENTITY();
105 + PRINT ' Created CompanyDemographicValue/Mapping "Not Logged In": value=' + CAST(@CompanyDemographicValueId_NotLoggedIn AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_NotLoggedIn AS VARCHAR);
106 + END
107 + ELSE
108 + BEGIN
109 + -- ========== CompanyDemographic exists, retrieve all related IDs ==========
110 + PRINT 'Found existing CompanyDemographic structure:';
111 + PRINT ' CompanyDemographicId: ' + CAST(@CompanyDemographicId AS VARCHAR);
112 + PRINT ' CompanyDemographicNameMappingId: ' + CAST(@CompanyDemographicNameMappingId AS VARCHAR);
113 +
114 + -- Get each CompanyDemographicValueMapping ID (and its parent ValueId) by text
115 + SELECT @CompanyDemographicValueMappingId_NotStarted = cdvm.Id,
116 + @CompanyDemographicValueId_NotStarted = cdvm.CompanyDemographicValueId
117 + FROM Analytics.survey.CompanyDemographicValueMapping cdvm
118 + JOIN Analytics.survey.CompanyDemographicValue cdv ON cdv.Id = cdvm.CompanyDemographicValueId
119 + WHERE cdv.CompanyDemographicId = @CompanyDemographicId
120 + AND cdvm.Text = 'Not Started';
121 + PRINT ' ValueMapping "Not Started": value=' + CAST(@CompanyDemographicValueId_NotStarted AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_NotStarted AS VARCHAR);
122 +
123 + SELECT @CompanyDemographicValueMappingId_Draft = cdvm.Id,
124 + @CompanyDemographicValueId_Draft = cdvm.CompanyDemographicValueId
125 + FROM Analytics.survey.CompanyDemographicValueMapping cdvm
126 + JOIN Analytics.survey.CompanyDemographicValue cdv ON cdv.Id = cdvm.CompanyDemographicValueId
127 + WHERE cdv.CompanyDemographicId = @CompanyDemographicId
128 + AND cdvm.Text = 'Draft';
129 + PRINT ' ValueMapping "Draft": value=' + CAST(@CompanyDemographicValueId_Draft AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_Draft AS VARCHAR);
130 +
131 + SELECT @CompanyDemographicValueMappingId_Shared = cdvm.Id,
132 + @CompanyDemographicValueId_Shared = cdvm.CompanyDemographicValueId
133 + FROM Analytics.survey.CompanyDemographicValueMapping cdvm
134 + JOIN Analytics.survey.CompanyDemographicValue cdv ON cdv.Id = cdvm.CompanyDemographicValueId
135 + WHERE cdv.CompanyDemographicId = @CompanyDemographicId
136 + AND cdvm.Text = 'Shared';
137 + PRINT ' ValueMapping "Shared": value=' + CAST(@CompanyDemographicValueId_Shared AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_Shared AS VARCHAR);
138 +
139 + SELECT @CompanyDemographicValueMappingId_NotLoggedIn = cdvm.Id,
140 + @CompanyDemographicValueId_NotLoggedIn = cdvm.CompanyDemographicValueId
141 + FROM Analytics.survey.CompanyDemographicValueMapping cdvm
142 + JOIN Analytics.survey.CompanyDemographicValue cdv ON cdv.Id = cdvm.CompanyDemographicValueId
143 + WHERE cdv.CompanyDemographicId = @CompanyDemographicId
144 + AND cdvm.Text = 'Not Logged In';
145 + IF @CompanyDemographicValueId_NotLoggedIn IS NULL
146 + BEGIN
147 + INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId)
148 + VALUES (@CompanyDemographicId);
149 + SET @CompanyDemographicValueId_NotLoggedIn = SCOPE_IDENTITY();
150 + INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId)
151 + VALUES ('Not Logged In', @CompanyDemographicValueId_NotLoggedIn);
152 + SET @CompanyDemographicValueMappingId_NotLoggedIn = SCOPE_IDENTITY();
153 + PRINT ' Created (new) CompanyDemographicValue/Mapping "Not Logged In": value=' + CAST(@CompanyDemographicValueId_NotLoggedIn AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_NotLoggedIn AS VARCHAR);
154 + END
155 + ELSE
156 + BEGIN
157 + PRINT ' ValueMapping "Not Logged In": value=' + CAST(@CompanyDemographicValueId_NotLoggedIn AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_NotLoggedIn AS VARCHAR);
158 + END
159 + END
160 +
161 + -- ========== STEP 4: Check if SurveyDemographic exists ==========
162 + SELECT @SurveyDemographicId = Id
163 + FROM Analytics.survey.SurveyDemographic
164 + WHERE SurveyId = @SurveyId
165 + AND CompanyDemographicNameMappingId = @CompanyDemographicNameMappingId;
166 +
167 + IF @SurveyDemographicId IS NULL
168 + BEGIN
169 + -- ========== Create SurveyDemographic and all SurveyDemographicValues at once ==========
170 + PRINT 'SurveyDemographic not found. Creating demographic for SurveyId: ' + CAST(@SurveyId AS VARCHAR) + ', Slot: ' + CAST(@DemographicSlot AS VARCHAR);
171 +
172 +
173 + -- ========== Find next available demographic slot (<=200) ==========
174 + WITH UsedSlots AS (
175 + SELECT Slot
176 + FROM Analytics.survey.SurveyDemographic
177 + WHERE SurveyId = @SurveyId
178 + ),
179 + AvailableSlots AS (
180 + SELECT TOP 1 n AS Slot
181 + FROM (
182 + SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
183 + FROM sys.all_objects
184 + ) Numbers
185 + WHERE n <= 200
186 + AND n NOT IN (SELECT Slot FROM UsedSlots)
187 + ORDER BY n
188 + )
189 + SELECT @DemographicSlot = Slot
190 + FROM AvailableSlots;
191 +
192 + IF @DemographicSlot IS NULL
193 + BEGIN
194 + RAISERROR('No available demographic slots found (all slots 1-200 are in use for SurveyId: %d)', 16, 1, @SurveyId);
195 + END
196 + ELSE
197 + BEGIN
198 + PRINT 'Selected available demographic slot: ' + CAST(@DemographicSlot AS VARCHAR);
199 + END
200 +
201 + -- Create SurveyDemographic
202 + INSERT INTO Analytics.survey.SurveyDemographic (
203 + SurveyId,
204 + Slot,
205 + Name,
206 + DemographicType,
207 + SliceAccessLevel,
208 + FilterAccessLevel,
209 + IsSelfReported,
210 + SurveyQuestionId,
211 + VisibilityLevel,
212 + CompanyDemographicNameMappingId
213 + )
214 + VALUES (
215 + @SurveyId,
216 + @DemographicSlot,
217 + @DemographicName,
218 + 1, -- DemographicType
219 + 2, -- SliceAccessLevel
220 + 2, -- FilterAccessLevel
221 + 0, -- IsSelfReported
222 + NULL, -- SurveyQuestionId
223 + 1, -- VisibilityLevel
224 + @CompanyDemographicNameMappingId
225 + );
226 + SET @SurveyDemographicId = SCOPE_IDENTITY();
227 + PRINT ' Created SurveyDemographicId: ' + CAST(@SurveyDemographicId AS VARCHAR);
228 +
229 + -- Create all four SurveyDemographicValues
230 + INSERT INTO Analytics.survey.SurveyDemographicValue (
231 + SurveyDemographicId,
232 + SurveyId,
233 + Value,
234 + Code,
235 + TextValue1,
236 + CompanyDemographicValueMappingId
237 + )
238 + VALUES (
239 + @SurveyDemographicId,
240 + @SurveyId,
241 + 'Not Started',
242 + 0,
243 + 'Not Started',
244 + @CompanyDemographicValueMappingId_NotStarted
245 + );
246 + PRINT ' Created SurveyDemographicValue "Not Started"';
247 +
248 + INSERT INTO Analytics.survey.SurveyDemographicValue (
249 + SurveyDemographicId,
250 + SurveyId,
251 + Value,
252 + Code,
253 + TextValue1,
254 + CompanyDemographicValueMappingId
255 + )
256 + VALUES (
257 + @SurveyDemographicId,
258 + @SurveyId,
259 + 'Draft',
260 + 1,
261 + 'Draft',
262 + @CompanyDemographicValueMappingId_Draft
263 + );
264 + PRINT ' Created SurveyDemographicValue "Draft"';
265 +
266 + INSERT INTO Analytics.survey.SurveyDemographicValue (
267 + SurveyDemographicId,
268 + SurveyId,
269 + Value,
270 + Code,
271 + TextValue1,
272 + CompanyDemographicValueMappingId
273 + )
274 + VALUES (
275 + @SurveyDemographicId,
276 + @SurveyId,
277 + 'Shared',
278 + 2,
279 + 'Shared',
280 + @CompanyDemographicValueMappingId_Shared
281 + );
282 + PRINT ' Created SurveyDemographicValue "Shared"';
283 +
284 + INSERT INTO Analytics.survey.SurveyDemographicValue (
285 + SurveyDemographicId,
286 + SurveyId,
287 + Value,
288 + Code,
289 + TextValue1,
290 + CompanyDemographicValueMappingId
291 + )
292 + VALUES (
293 + @SurveyDemographicId,
294 + @SurveyId,
295 + 'Not Logged In',
296 + 3,
297 + 'Not Logged In',
298 + @CompanyDemographicValueMappingId_NotLoggedIn
299 + );
300 + PRINT ' Created SurveyDemographicValue "Not Logged In"';
301 +
302 + -- Update the slot variable to the one we used
303 + SELECT @DemographicSlot = Slot
304 + FROM Analytics.survey.SurveyDemographic
305 + WHERE Id = @SurveyDemographicId;
306 + END
307 + ELSE
308 + BEGIN
309 + -- ========== SurveyDemographic exists, retrieve the slot ==========
310 + PRINT 'Found existing SurveyDemographic:';
311 + PRINT ' SurveyDemographicId: ' + CAST(@SurveyDemographicId AS VARCHAR);
312 +
313 + SELECT @DemographicSlot = Slot
314 + FROM Analytics.survey.SurveyDemographic
315 + WHERE Id = @SurveyDemographicId;
316 + PRINT ' Using existing Slot: ' + CAST(@DemographicSlot AS VARCHAR);
317 +
318 + IF NOT EXISTS (
319 + SELECT 1 FROM Analytics.survey.SurveyDemographicValue
320 + WHERE SurveyDemographicId = @SurveyDemographicId AND Code = 3
321 + )
322 + BEGIN
323 + INSERT INTO Analytics.survey.SurveyDemographicValue (
324 + SurveyDemographicId,
325 + SurveyId,
326 + Value,
327 + Code,
328 + TextValue1,
329 + CompanyDemographicValueMappingId
330 + )
331 + VALUES (
332 + @SurveyDemographicId,
333 + @SurveyId,
334 + 'Not Logged In',
335 + 3,
336 + 'Not Logged In',
337 + @CompanyDemographicValueMappingId_NotLoggedIn
338 + );
339 + PRINT ' Created (new) SurveyDemographicValue "Not Logged In"';
340 + END
341 + END
342 +
343 +
344 + -- ========== STEP 5: Update SurveyParticipant with Growth Plan State ==========
345 + PRINT 'Updating SurveyParticipant Growth Plan state for demographic slot ' + CAST(@DemographicSlot AS VARCHAR) + '...';
346 +
347 + SET @DemographicColumn = 'DemographicValue' + CAST(@DemographicSlot AS VARCHAR);
348 + SET @DynamicSQL = N'
349 + UPDATE sp
350 + SET sp.' + @DemographicColumn + N' = growthUser.GrowthPlanState
351 + FROM Analytics.survey.SurveyParticipant sp
352 + JOIN (
353 + SELECT au.Id,
354 + CASE
355 + WHEN gp.PublishedAt IS NOT NULL THEN 2
356 + WHEN gp.LastActivityAt > gp.CreatedAt OR cv.UpdatedAt > cv.CreatedAt THEN 1
357 + WHEN gp.OwnerId IS NOT NULL THEN 0
358 + ELSE 3
359 + END AS GrowthPlanState
360 + FROM Growth.ec.[User] u
361 + JOIN Growth.ec.[UserRoles] ur ON ur.UserId = u.Id
362 + JOIN Analytics.survey.Users au ON au.MembershipId = u.Id
363 + LEFT JOIN Growth.develop.GrowthPlan gp ON gp.OwnerId = u.Id
364 + LEFT JOIN Growth.develop.CareerVision cv ON cv.UserId = u.Id
365 + WHERE u.OrganizationId = @OrganizationId
366 + AND (ur.GrowthUser = 1 or ur.GrowthAdministrator = 1)
367 + ) growthUser ON sp.UserId = growthUser.Id AND sp.SurveyId = @SurveyId';
368 +
369 + EXEC sp_executesql @DynamicSQL,
370 + N'@OrganizationId INT, @SurveyId INT',
371 + @OrganizationId = @OrganizationId,
372 + @SurveyId = @SurveyId;
373 +
374 + PRINT 'SurveyParticipant update complete.';
375 +
376 + COMMIT TRANSACTION;
377 + PRINT '';
378 + PRINT '========== SUCCESS: All operations completed successfully ==========';
379 + PRINT '';
380 + PRINT 'Summary:';
381 + PRINT ' CompanyId: ' + CAST(@CompanyId AS VARCHAR);
382 + PRINT ' OrganizationId: ' + CAST(@OrganizationId AS VARCHAR);
383 + PRINT ' SurveyId: ' + CAST(@SurveyId AS VARCHAR);
384 + PRINT ' Demographic Slot: ' + CAST(@DemographicSlot AS VARCHAR);
385 + PRINT ' Demographic Name: ' + @DemographicName;
386 + PRINT '';
387 + PRINT 'Generated IDs:';
388 + PRINT ' CompanyDemographicId: ' + CAST(@CompanyDemographicId AS VARCHAR);
389 + PRINT ' CompanyDemographicNameMappingId: ' + CAST(@CompanyDemographicNameMappingId AS VARCHAR);
390 + PRINT ' CompanyDemographicValueId - Not Started: ' + CAST(@CompanyDemographicValueId_NotStarted AS VARCHAR);
391 + PRINT ' CompanyDemographicValueId - Draft: ' + CAST(@CompanyDemographicValueId_Draft AS VARCHAR);
392 + PRINT ' CompanyDemographicValueId - Shared: ' + CAST(@CompanyDemographicValueId_Shared AS VARCHAR);
393 + PRINT ' CompanyDemographicValueId - Not Logged In: ' + CAST(@CompanyDemographicValueId_NotLoggedIn AS VARCHAR);
394 + PRINT ' ValueMapping - Not Started: ' + CAST(@CompanyDemographicValueMappingId_NotStarted AS VARCHAR);
395 + PRINT ' ValueMapping - Draft: ' + CAST(@CompanyDemographicValueMappingId_Draft AS VARCHAR);
396 + PRINT ' ValueMapping - Shared: ' + CAST(@CompanyDemographicValueMappingId_Shared AS VARCHAR);
397 + PRINT ' ValueMapping - Not Logged In: ' + CAST(@CompanyDemographicValueMappingId_NotLoggedIn AS VARCHAR);
398 + PRINT ' SurveyDemographicId: ' + CAST(@SurveyDemographicId AS VARCHAR);
399 +
400 + END TRY
401 + BEGIN CATCH
402 + IF @@TRANCOUNT > 0
403 + ROLLBACK TRANSACTION;
404 +
405 + PRINT '';
406 + PRINT '========== ERROR: Transaction rolled back ==========';
407 + PRINT 'Error Message: ' + ERROR_MESSAGE();
408 + PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR);
409 + PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR);
410 + PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR);
411 +
412 + THROW;
413 + END CATCH;
Újabb Régebbi

Ez az oldal Opengist alapú ⋅ Load: 44ms⋅

Magyar