DataCCIW

cust_CCIW_Update_profile_branch

Oct 27th, 2020
1,116
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE [ArenaDB]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[cust_CCIW_Update_profile_branch]    Script Date: 10/27/2020 4:03:20 PM ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. -- Author: Ethan Jordan/Tony Visconti
  9. -- Date: 3/12/2020
  10.  
  11. CREATE PROCEDURE [dbo].[cust_CCIW_Update_profile_branch]
  12.   @Root           INT,
  13.   @Activate       VARCHAR(1),
  14.   @OrganizationID INT,
  15.   @Update         VARCHAR(1),
  16.   @UpdateProfileBranchPageId INT
  17. AS
  18. BEGIN
  19.   -- If they don't specify what to change or if to change anything show all profiles
  20.   IF @Activate = ''
  21.   BEGIN
  22.     SELECT PT.Name,
  23.            Profile_ID = '<a href="/default.aspx?page='+ CONVERT(VARCHAR(10), @UpdateProfileBranchPageId)
  24.            +'&Root=' + CONVERT(VARCHAR(10), PT.profile_id) + '">' + CONVERT(VARCHAR(10), PT.profile_id) +
  25.            '</a>',
  26.            PT.Parent,
  27.            PT.Root,
  28.            PT.Root_ID,
  29.            PT.Branch,
  30.            [Current Status] = CASE WHEN PT.active = 1 THEN 'Active' ELSE 'Inactive' End
  31.     FROM   cust_CCIW_v_profile_tree PT
  32.     WHERE  Root_ID = @Root
  33.   END;
  34.  
  35.   IF @Update = '1'
  36.   BEGIN
  37.  
  38.     -- Create a temp list of profiles that need to be updated
  39.     SELECT PT.Name,
  40.            Profile_ID = '<a href="/default.aspx?page='+ CONVERT(VARCHAR(10), @UpdateProfileBranchPageId)
  41.            +'&Root=' + CONVERT(VARCHAR(10), PT.profile_id) + '">' + CONVERT(VARCHAR(10), PT.profile_id) +
  42.            '</a>',
  43.            PT.Parent,
  44.            PT.Root,
  45.            PT.Root_ID,
  46.            PT.Branch,
  47.            [New Status] = CASE WHEN PT.active = 1 THEN 'Inactive' ELSE 'Active' End
  48.     INTO #changes_temp
  49.     FROM   cust_CCIW_v_profile_tree PT
  50.     WHERE  Root_ID = @Root
  51.            AND PT.active != @Activate;
  52.    
  53.     --Apply Updates
  54.     UPDATE   core_profile
  55.       SET    active = @Activate
  56.     WHERE    profile_id IN
  57.     (
  58.         SELECT profile_id
  59.         FROM   cust_CCIW_v_profile_tree
  60.         WHERE  Root_ID = @Root
  61.     );
  62.  
  63.     -- Show what changed
  64.     SELECT *
  65.     FROM   #changes_temp;
  66.   END;
  67.        ELSE
  68.   BEGIN
  69.     -- Show what will change
  70.     SELECT PT.Name,
  71.            Profile_ID = '<a href="/default.aspx?page='+ CONVERT(VARCHAR(10), @UpdateProfileBranchPageId)
  72.            +'&Root=' + CONVERT(VARCHAR(10), PT.profile_id) + '">' + CONVERT(VARCHAR(10), PT.profile_id) +
  73.            '</a>',
  74.            PT.Parent,
  75.            PT.Root,
  76.            PT.Root_ID,
  77.            PT.Branch,
  78.            [Current Status] = CASE WHEN PT.active = 1 THEN 'Active' ELSE 'Inactive' End
  79.     FROM   cust_CCIW_v_profile_tree PT
  80.     WHERE  Root_ID = @Root
  81.            AND PT.active != @Activate;
  82.   END;
  83. END;
RAW Paste Data