Carter / FakedDataConnection-GrowthPlans-MacLeanFogg-NotLoggedIn.sql

-- ============================================================================ -- Setup Growth Plan Activity Demographic for Survey -- ============================================================================ -- This script creates and populates a demographic to track Growth Plan activity -- (Not Started, Draft, Shared, Not Logged In) for survey participants. -- -- USAGE: Set the variables below for your target company/survey -- ============================================================================ -- ========== CONFIGURATION VARIABLES ========== DECLARE @CompanyId INT = 2077; -- Analytics CompanyId DECLARE @SurveyId INT = 54446; -- Target Survey ID DECLARE @DemographicName NVARCHAR(100) = 'Growth Plan Activity'; -- ========== INTERNAL VARIABLES (Auto-populated) ========== DECLARE @OrganizationId INT; DECLARE @DemographicSlot INT; DECLARE @CompanyDemographicId INT; DECLARE @CompanyDemographicNameMappingId INT; DECLARE @CompanyDemographicValueId_NotStarted INT; DECLARE @CompanyDemographicValueId_Draft INT; DECLARE @CompanyDemographicValueId_Shared INT; DECLARE @CompanyDemographicValueId_NotLoggedIn INT; DECLARE @CompanyDemographicValueMappingId_NotStarted INT; DECLARE @CompanyDemographicValueMappingId_Draft INT; DECLARE @CompanyDemographicValueMappingId_Shared INT; DECLARE @CompanyDemographicValueMappingId_NotLoggedIn INT; DECLARE @SurveyDemographicId INT; DECLARE @DynamicSQL NVARCHAR(MAX); DECLARE @DemographicColumn NVARCHAR(50); BEGIN TRANSACTION; BEGIN TRY -- ========== STEP 1: Lookup OrganizationId from CompanyId ========== SELECT @OrganizationId = o.Id FROM Analytics.survey.Organization o WHERE o.CompanyId = @CompanyId; IF @OrganizationId IS NULL BEGIN RAISERROR('Unable to find OrganizationId for CompanyId: %d', 16, 1, @CompanyId); END ELSE BEGIN PRINT 'Found OrganizationId: ' + CAST(@OrganizationId AS VARCHAR) + ' for CompanyId: ' + CAST(@CompanyId AS VARCHAR); END; -- ========== STEP 3: Check if CompanyDemographic exists ========== SELECT @CompanyDemographicId = cd.Id, @CompanyDemographicNameMappingId = cdnm.Id FROM Analytics.survey.CompanyDemographic cd INNER JOIN Analytics.survey.CompanyDemographicNameMapping cdnm ON cdnm.CompanyDemographicId = cd.Id WHERE cd.CompanyId = @CompanyId AND cdnm.Text = @DemographicName; IF @CompanyDemographicId IS NULL BEGIN -- ========== Create all Company Demographic structures at once ========== PRINT 'CompanyDemographic not found. Creating new demographic structure for CompanyId: ' + CAST(@CompanyId AS VARCHAR); -- Create CompanyDemographic INSERT INTO Analytics.survey.CompanyDemographic (CompanyId) VALUES (@CompanyId); SET @CompanyDemographicId = SCOPE_IDENTITY(); PRINT ' Created CompanyDemographicId: ' + CAST(@CompanyDemographicId AS VARCHAR); -- Create CompanyDemographicNameMapping INSERT INTO Analytics.survey.CompanyDemographicNameMapping (CompanyDemographicId, Text) VALUES (@CompanyDemographicId, @DemographicName); SET @CompanyDemographicNameMappingId = SCOPE_IDENTITY(); PRINT ' Created CompanyDemographicNameMappingId: ' + CAST(@CompanyDemographicNameMappingId AS VARCHAR); INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId) VALUES (@CompanyDemographicId); SET @CompanyDemographicValueId_NotStarted = SCOPE_IDENTITY(); INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId) VALUES ('Not Started', @CompanyDemographicValueId_NotStarted); SET @CompanyDemographicValueMappingId_NotStarted = SCOPE_IDENTITY(); PRINT ' Created CompanyDemographicValue/Mapping "Not Started": value=' + CAST(@CompanyDemographicValueId_NotStarted AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_NotStarted AS VARCHAR); INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId) VALUES (@CompanyDemographicId); SET @CompanyDemographicValueId_Draft = SCOPE_IDENTITY(); INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId) VALUES ('Draft', @CompanyDemographicValueId_Draft); SET @CompanyDemographicValueMappingId_Draft = SCOPE_IDENTITY(); PRINT ' Created CompanyDemographicValue/Mapping "Draft": value=' + CAST(@CompanyDemographicValueId_Draft AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_Draft AS VARCHAR); INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId) VALUES (@CompanyDemographicId); SET @CompanyDemographicValueId_Shared = SCOPE_IDENTITY(); INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId) VALUES ('Shared', @CompanyDemographicValueId_Shared); SET @CompanyDemographicValueMappingId_Shared = SCOPE_IDENTITY(); PRINT ' Created CompanyDemographicValue/Mapping "Shared": value=' + CAST(@CompanyDemographicValueId_Shared AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_Shared AS VARCHAR); INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId) VALUES (@CompanyDemographicId); SET @CompanyDemographicValueId_NotLoggedIn = SCOPE_IDENTITY(); INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId) VALUES ('Not Logged In', @CompanyDemographicValueId_NotLoggedIn); SET @CompanyDemographicValueMappingId_NotLoggedIn = SCOPE_IDENTITY(); PRINT ' Created CompanyDemographicValue/Mapping "Not Logged In": value=' + CAST(@CompanyDemographicValueId_NotLoggedIn AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_NotLoggedIn AS VARCHAR); END ELSE BEGIN -- ========== CompanyDemographic exists, retrieve all related IDs ========== PRINT 'Found existing CompanyDemographic structure:'; PRINT ' CompanyDemographicId: ' + CAST(@CompanyDemographicId AS VARCHAR); PRINT ' CompanyDemographicNameMappingId: ' + CAST(@CompanyDemographicNameMappingId AS VARCHAR); -- Get each CompanyDemographicValueMapping ID (and its parent ValueId) by text SELECT @CompanyDemographicValueMappingId_NotStarted = cdvm.Id, @CompanyDemographicValueId_NotStarted = cdvm.CompanyDemographicValueId FROM Analytics.survey.CompanyDemographicValueMapping cdvm JOIN Analytics.survey.CompanyDemographicValue cdv ON cdv.Id = cdvm.CompanyDemographicValueId WHERE cdv.CompanyDemographicId = @CompanyDemographicId AND cdvm.Text = 'Not Started'; PRINT ' ValueMapping "Not Started": value=' + CAST(@CompanyDemographicValueId_NotStarted AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_NotStarted AS VARCHAR); SELECT @CompanyDemographicValueMappingId_Draft = cdvm.Id, @CompanyDemographicValueId_Draft = cdvm.CompanyDemographicValueId FROM Analytics.survey.CompanyDemographicValueMapping cdvm JOIN Analytics.survey.CompanyDemographicValue cdv ON cdv.Id = cdvm.CompanyDemographicValueId WHERE cdv.CompanyDemographicId = @CompanyDemographicId AND cdvm.Text = 'Draft'; PRINT ' ValueMapping "Draft": value=' + CAST(@CompanyDemographicValueId_Draft AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_Draft AS VARCHAR); SELECT @CompanyDemographicValueMappingId_Shared = cdvm.Id, @CompanyDemographicValueId_Shared = cdvm.CompanyDemographicValueId FROM Analytics.survey.CompanyDemographicValueMapping cdvm JOIN Analytics.survey.CompanyDemographicValue cdv ON cdv.Id = cdvm.CompanyDemographicValueId WHERE cdv.CompanyDemographicId = @CompanyDemographicId AND cdvm.Text = 'Shared'; PRINT ' ValueMapping "Shared": value=' + CAST(@CompanyDemographicValueId_Shared AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_Shared AS VARCHAR); SELECT @CompanyDemographicValueMappingId_NotLoggedIn = cdvm.Id, @CompanyDemographicValueId_NotLoggedIn = cdvm.CompanyDemographicValueId FROM Analytics.survey.CompanyDemographicValueMapping cdvm JOIN Analytics.survey.CompanyDemographicValue cdv ON cdv.Id = cdvm.CompanyDemographicValueId WHERE cdv.CompanyDemographicId = @CompanyDemographicId AND cdvm.Text = 'Not Logged In'; IF @CompanyDemographicValueId_NotLoggedIn IS NULL BEGIN INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId) VALUES (@CompanyDemographicId); SET @CompanyDemographicValueId_NotLoggedIn = SCOPE_IDENTITY(); INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId) VALUES ('Not Logged In', @CompanyDemographicValueId_NotLoggedIn); SET @CompanyDemographicValueMappingId_NotLoggedIn = SCOPE_IDENTITY(); PRINT ' Created (new) CompanyDemographicValue/Mapping "Not Logged In": value=' + CAST(@CompanyDemographicValueId_NotLoggedIn AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_NotLoggedIn AS VARCHAR); END ELSE BEGIN PRINT ' ValueMapping "Not Logged In": value=' + CAST(@CompanyDemographicValueId_NotLoggedIn AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_NotLoggedIn AS VARCHAR); END END -- ========== STEP 4: Check if SurveyDemographic exists ========== SELECT @SurveyDemographicId = Id FROM Analytics.survey.SurveyDemographic WHERE SurveyId = @SurveyId AND CompanyDemographicNameMappingId = @CompanyDemographicNameMappingId; IF @SurveyDemographicId IS NULL BEGIN -- ========== Create SurveyDemographic and all SurveyDemographicValues at once ========== PRINT 'SurveyDemographic not found. Creating demographic for SurveyId: ' + CAST(@SurveyId AS VARCHAR) + ', Slot: ' + CAST(@DemographicSlot AS VARCHAR); -- ========== Find next available demographic slot (<=200) ========== WITH UsedSlots AS ( SELECT Slot FROM Analytics.survey.SurveyDemographic WHERE SurveyId = @SurveyId ), AvailableSlots AS ( SELECT TOP 1 n AS Slot FROM ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects ) Numbers WHERE n <= 200 AND n NOT IN (SELECT Slot FROM UsedSlots) ORDER BY n ) SELECT @DemographicSlot = Slot FROM AvailableSlots; IF @DemographicSlot IS NULL BEGIN RAISERROR('No available demographic slots found (all slots 1-200 are in use for SurveyId: %d)', 16, 1, @SurveyId); END ELSE BEGIN PRINT 'Selected available demographic slot: ' + CAST(@DemographicSlot AS VARCHAR); END -- Create SurveyDemographic INSERT INTO Analytics.survey.SurveyDemographic ( SurveyId, Slot, Name, DemographicType, SliceAccessLevel, FilterAccessLevel, IsSelfReported, SurveyQuestionId, VisibilityLevel, CompanyDemographicNameMappingId ) VALUES ( @SurveyId, @DemographicSlot, @DemographicName, 1, -- DemographicType 2, -- SliceAccessLevel 2, -- FilterAccessLevel 0, -- IsSelfReported NULL, -- SurveyQuestionId 1, -- VisibilityLevel @CompanyDemographicNameMappingId ); SET @SurveyDemographicId = SCOPE_IDENTITY(); PRINT ' Created SurveyDemographicId: ' + CAST(@SurveyDemographicId AS VARCHAR); -- Create all four SurveyDemographicValues INSERT INTO Analytics.survey.SurveyDemographicValue ( SurveyDemographicId, SurveyId, Value, Code, TextValue1, CompanyDemographicValueMappingId ) VALUES ( @SurveyDemographicId, @SurveyId, 'Not Started', 0, 'Not Started', @CompanyDemographicValueMappingId_NotStarted ); PRINT ' Created SurveyDemographicValue "Not Started"'; INSERT INTO Analytics.survey.SurveyDemographicValue ( SurveyDemographicId, SurveyId, Value, Code, TextValue1, CompanyDemographicValueMappingId ) VALUES ( @SurveyDemographicId, @SurveyId, 'Draft', 1, 'Draft', @CompanyDemographicValueMappingId_Draft ); PRINT ' Created SurveyDemographicValue "Draft"'; INSERT INTO Analytics.survey.SurveyDemographicValue ( SurveyDemographicId, SurveyId, Value, Code, TextValue1, CompanyDemographicValueMappingId ) VALUES ( @SurveyDemographicId, @SurveyId, 'Shared', 2, 'Shared', @CompanyDemographicValueMappingId_Shared ); PRINT ' Created SurveyDemographicValue "Shared"'; INSERT INTO Analytics.survey.SurveyDemographicValue ( SurveyDemographicId, SurveyId, Value, Code, TextValue1, CompanyDemographicValueMappingId ) VALUES ( @SurveyDemographicId, @SurveyId, 'Not Logged In', 3, 'Not Logged In', @CompanyDemographicValueMappingId_NotLoggedIn ); PRINT ' Created SurveyDemographicValue "Not Logged In"'; -- Update the slot variable to the one we used SELECT @DemographicSlot = Slot FROM Analytics.survey.SurveyDemographic WHERE Id = @SurveyDemographicId; END ELSE BEGIN -- ========== SurveyDemographic exists, retrieve the slot ========== PRINT 'Found existing SurveyDemographic:'; PRINT ' SurveyDemographicId: ' + CAST(@SurveyDemographicId AS VARCHAR); SELECT @DemographicSlot = Slot FROM Analytics.survey.SurveyDemographic WHERE Id = @SurveyDemographicId; PRINT ' Using existing Slot: ' + CAST(@DemographicSlot AS VARCHAR); IF NOT EXISTS ( SELECT 1 FROM Analytics.survey.SurveyDemographicValue WHERE SurveyDemographicId = @SurveyDemographicId AND Code = 3 ) BEGIN INSERT INTO Analytics.survey.SurveyDemographicValue ( SurveyDemographicId, SurveyId, Value, Code, TextValue1, CompanyDemographicValueMappingId ) VALUES ( @SurveyDemographicId, @SurveyId, 'Not Logged In', 3, 'Not Logged In', @CompanyDemographicValueMappingId_NotLoggedIn ); PRINT ' Created (new) SurveyDemographicValue "Not Logged In"'; END END -- ========== STEP 5: Update SurveyParticipant with Growth Plan State ========== PRINT 'Updating SurveyParticipant Growth Plan state for demographic slot ' + CAST(@DemographicSlot AS VARCHAR) + '...'; SET @DemographicColumn = 'DemographicValue' + CAST(@DemographicSlot AS VARCHAR); SET @DynamicSQL = N' UPDATE sp SET sp.' + @DemographicColumn + N' = growthUser.GrowthPlanState FROM Analytics.survey.SurveyParticipant sp JOIN ( SELECT au.Id, CASE WHEN gp.PublishedAt IS NOT NULL THEN 2 WHEN gp.LastActivityAt > gp.CreatedAt OR cv.UpdatedAt > cv.CreatedAt THEN 1 WHEN gp.OwnerId IS NOT NULL THEN 0 ELSE 3 END AS GrowthPlanState FROM Growth.ec.[User] u JOIN Growth.ec.[UserRoles] ur ON ur.UserId = u.Id JOIN Analytics.survey.Users au ON au.MembershipId = u.Id LEFT JOIN Growth.develop.GrowthPlan gp ON gp.OwnerId = u.Id LEFT JOIN Growth.develop.CareerVision cv ON cv.UserId = u.Id WHERE u.OrganizationId = @OrganizationId AND (ur.GrowthUser = 1 or ur.GrowthAdministrator = 1) ) growthUser ON sp.UserId = growthUser.Id AND sp.SurveyId = @SurveyId'; EXEC sp_executesql @DynamicSQL, N'@OrganizationId INT, @SurveyId INT', @OrganizationId = @OrganizationId, @SurveyId = @SurveyId; PRINT 'SurveyParticipant update complete.'; COMMIT TRANSACTION; PRINT ''; PRINT '========== SUCCESS: All operations completed successfully =========='; PRINT ''; PRINT 'Summary:'; PRINT ' CompanyId: ' + CAST(@CompanyId AS VARCHAR); PRINT ' OrganizationId: ' + CAST(@OrganizationId AS VARCHAR); PRINT ' SurveyId: ' + CAST(@SurveyId AS VARCHAR); PRINT ' Demographic Slot: ' + CAST(@DemographicSlot AS VARCHAR); PRINT ' Demographic Name: ' + @DemographicName; PRINT ''; PRINT 'Generated IDs:'; PRINT ' CompanyDemographicId: ' + CAST(@CompanyDemographicId AS VARCHAR); PRINT ' CompanyDemographicNameMappingId: ' + CAST(@CompanyDemographicNameMappingId AS VARCHAR); PRINT ' CompanyDemographicValueId - Not Started: ' + CAST(@CompanyDemographicValueId_NotStarted AS VARCHAR); PRINT ' CompanyDemographicValueId - Draft: ' + CAST(@CompanyDemographicValueId_Draft AS VARCHAR); PRINT ' CompanyDemographicValueId - Shared: ' + CAST(@CompanyDemographicValueId_Shared AS VARCHAR); PRINT ' CompanyDemographicValueId - Not Logged In: ' + CAST(@CompanyDemographicValueId_NotLoggedIn AS VARCHAR); PRINT ' ValueMapping - Not Started: ' + CAST(@CompanyDemographicValueMappingId_NotStarted AS VARCHAR); PRINT ' ValueMapping - Draft: ' + CAST(@CompanyDemographicValueMappingId_Draft AS VARCHAR); PRINT ' ValueMapping - Shared: ' + CAST(@CompanyDemographicValueMappingId_Shared AS VARCHAR); PRINT ' ValueMapping - Not Logged In: ' + CAST(@CompanyDemographicValueMappingId_NotLoggedIn AS VARCHAR); PRINT ' SurveyDemographicId: ' + CAST(@SurveyDemographicId AS VARCHAR); END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; PRINT ''; PRINT '========== ERROR: Transaction rolled back =========='; PRINT 'Error Message: ' + ERROR_MESSAGE(); PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR); PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR); PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR); THROW; END CATCH;