Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_sql_-_move_all_open_artwork_groups_to_produced [2019/01/27 11:29] (current)
Line 1: Line 1:
 +====== ​ ======
 +
 +
 +
 +===== Explanation of SQL =====
 +
 +
 +
 +This SQL marks all active artwork groups as Produced. ​ It will also create the history entries as well.  SQL Bridge is required for this query to work correctly.
 +
 +
 +
 +===== Risk of Data Corruption if Run Improperly =====
 +
 +**HIGH**. This query modifies data; therefore, you should make a backup of the database prior to running this query.
 +
 +
 +
 +===== SQL =====
 +
 +
 +
 +<code sql>
 +DECLARE @GroupsToChange TABLE(ArtworkGroupID INT PRIMARY KEY, ArtworkGroupClassTypeID INT, SeqID INT, OldStatusID INT, PlayerID INT);
 +DECLARE @NewHistory TABLE(ID INT PRIMARY KEY, ClassTypeID INT, SeqID INT, ArtworkGroupID INT, FromStatusID INT, ToStatusID INT, StatusDT DATETIME, PlayerID INT);
 +INSERT INTO @GroupsToChange
 +SELECT ID, ClassTypeID,​ SeqID+1, StatusID, ​
 +       ​(SELECT TOP 1 ID
 +        FROM ArtworkPlayer
 +        WHERE TransHeaderID = ArtworkGroup.TransHeaderID
 +        ORDER BY CASE WHEN EmployeeID IS NULL THEN 1 ELSE 0 END,
 +                 ​EmployeeID,​ ContactID) ​
 +FROM ArtworkGroup
 +WHERE StatusID < 7
 +;
 +DECLARE @GroupCount INT;
 +DECLARE @FirstHistoryID INT;
 +SET @GroupCount = (SELECT COUNT(1) FROM @GroupsToChange WHERE PlayerID IS NOT NULL);
 +SET @FirstHistoryID = (SELECT dbo.csf_chapi_nextid(30302,​ @GroupCount));​
 +DECLARE @ModifiedDate DATETIME;
 +SET @ModifiedDate = GETDATE();
 +UPDATE AG
 +SET SeqID = T.SeqID,
 +    StatusID = 7,
 +    StatusDT = @ModifiedDate
 +FROM ArtworkGroup AG
 +     JOIN @GroupsToChange T ON T.ArtworkGroupID = AG.ID
 +WHERE ID in (SELECT ArtworkGroupID FROM @GroupsToChange)
 +;
 +INSERT INTO @NewHistory
 +(ID, ClassTypeID,​ SeqID, ArtworkGroupID,​ FromStatusID,​ ToStatusID, StatusDT, PlayerID)
 +SELECT @FirstHistoryID + ROW_NUMBER() OVER (ORDER BY ArtworkGroupID)-1,​ 30302, 0, ArtworkGroupID,​ OldStatusID,​ 7, @ModifiedDate,​ PlayerID
 +FROM @GroupsToChange
 +WHERE PlayerID IS NOT NULL
 +;
 +INSERT INTO ArtworkGroupStatusHistory
 +(ID, ClassTypeID,​ SeqID, ArtworkGroupID,​ FromStatusID,​ ToStatusID, StatusDT, PlayerID)
 +SELECT *
 +FROM @NewHistory
 +;
 +DECLARE @MyCursor CURSOR;
 +DECLARE @ID INT;
 +DECLARE @ClassTypeID INT;
 +DECLARE @SeqID INT;
 +SET @MyCursor = CURSOR FOR
 +SELECT ArtworkGroupID,​ ArtworkGroupClassTypeID,​ SeqID
 +FROM @GroupsToChange
 +OPEN @MyCursor ​
 +FETCH NEXT FROM @MyCursor ​
 +INTO @ID, @ClassTypeID,​ @SeqID
 +WHILE @@FETCH_STATUS = 0
 +BEGIN
 +    EXEC dbo.csf_chapi_refresh @ID, @ClassTypeID,​ @SeqID;
 +    FETCH NEXT FROM @MyCursor ​
 +    INTO @ID, @ClassTypeID,​ @SeqID
 +END; 
 +CLOSE @MyCursor ;
 +SET @MyCursor = CURSOR FOR
 +SELECT ID, ClassTypeID,​ SeqID
 +FROM @NewHistory
 +OPEN @MyCursor ​
 +FETCH NEXT FROM @MyCursor ​
 +INTO @ID, @ClassTypeID,​ @SeqID
 +WHILE @@FETCH_STATUS = 0
 +BEGIN
 +    EXEC dbo.csf_chapi_refresh @ID, @ClassTypeID,​ @SeqID;
 +    FETCH NEXT FROM @MyCursor ​
 +    INTO @ID, @ClassTypeID,​ @SeqID
 +END; 
 +CLOSE @MyCursor ;
 +DEALLOCATE @MyCursor;
 +</​code>​
 +
 +
 +
 +===== Version Information =====
 +  * Entered : 12/02/2016
 +  * Version : 6.0+
 +
 +
 +