Carter / FakeDataConnection.sql

-- ============================================================================ -- Setup Growth Plan Activity Demographic for Survey -- ============================================================================ -- This script creates and populates a demographic to track Growth Plan activity -- (Not Started, Draft, Shared) for survey participants. -- -- USAGE: Set the variables below for your target company/survey -- ============================================================================ -- ========== CONFIGURATION VARIABLES ========== DECLARE @CompanyId INT = ; -- Analytics CompanyId DECLARE @SurveyId INT = ; -- 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 INT; DECLARE @CompanyDemographicValueMappingId_NotStarted INT; DECLARE @CompanyDemographicValueMappingId_Draft INT; DECLARE @CompanyDemographicValueMappingId_Shared 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); -- Create CompanyDemographicValue INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId) VALUES (@CompanyDemographicId); SET @CompanyDemographicValueId = SCOPE_IDENTITY(); PRINT ' Created CompanyDemographicValueId: ' + CAST(@CompanyDemographicValueId AS VARCHAR); -- Create all three CompanyDemographicValueMappings INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId) VALUES ('Not Started', @CompanyDemographicValueId); SET @CompanyDemographicValueMappingId_NotStarted = SCOPE_IDENTITY(); PRINT ' Created CompanyDemographicValueMapping "Not Started": ' + CAST(@CompanyDemographicValueMappingId_NotStarted AS VARCHAR); INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId) VALUES ('Draft', @CompanyDemographicValueId); SET @CompanyDemographicValueMappingId_Draft = SCOPE_IDENTITY(); PRINT ' Created CompanyDemographicValueMapping "Draft": ' + CAST(@CompanyDemographicValueMappingId_Draft AS VARCHAR); INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId) VALUES ('Shared', @CompanyDemographicValueId); SET @CompanyDemographicValueMappingId_Shared = SCOPE_IDENTITY(); PRINT ' Created CompanyDemographicValueMapping "Shared": ' + CAST(@CompanyDemographicValueMappingId_Shared 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 CompanyDemographicValueId SELECT @CompanyDemographicValueId = Id FROM Analytics.survey.CompanyDemographicValue WHERE CompanyDemographicId = @CompanyDemographicId; PRINT ' CompanyDemographicValueId: ' + CAST(@CompanyDemographicValueId AS VARCHAR); -- Get all three CompanyDemographicValueMapping IDs SELECT @CompanyDemographicValueMappingId_NotStarted = Id FROM Analytics.survey.CompanyDemographicValueMapping WHERE CompanyDemographicValueId = @CompanyDemographicValueId AND Text = 'Not Started'; PRINT ' ValueMapping "Not Started": ' + CAST(@CompanyDemographicValueMappingId_NotStarted AS VARCHAR); SELECT @CompanyDemographicValueMappingId_Draft = Id FROM Analytics.survey.CompanyDemographicValueMapping WHERE CompanyDemographicValueId = @CompanyDemographicValueId AND Text = 'Draft'; PRINT ' ValueMapping "Draft": ' + CAST(@CompanyDemographicValueMappingId_Draft AS VARCHAR); SELECT @CompanyDemographicValueMappingId_Shared = Id FROM Analytics.survey.CompanyDemographicValueMapping WHERE CompanyDemographicValueId = @CompanyDemographicValueId AND Text = 'Shared'; PRINT ' ValueMapping "Shared": ' + CAST(@CompanyDemographicValueMappingId_Shared AS VARCHAR); 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 three 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"'; -- 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); 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 ELSE 0 END AS GrowthPlanState FROM Growth.develop.GrowthPlan gp JOIN Growth.ec.[User] u ON u.Id = gp.OwnerId JOIN Analytics.survey.Users au ON au.MembershipId = u.Id LEFT JOIN Growth.develop.CareerVision cv on cv.UserId = u.Id WHERE u.OrganizationId = @OrganizationId ) 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: ' + CAST(@CompanyDemographicValueId 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 ' 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;