Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Procedures    : [JobMgr_30_ApplProceduresObjects_Import_Object_IrLatest.sql]'
Print ' Autor         : A.Becker'
Print ' Creation Date : 03.06.2015'
Print ' Last Update   : 03.06.2015'
Print '-------------------------------------------------------------------------'

use [#DB_N#]

Print '-- [IRs_of_ItemById_WithLatestRanking_UDF] -------------------------------'
If Exists (select Name
           from sysobjects
            where Name = "IRs_of_ItemById_WithLatestRanking_UDF")
DROP FUNCTION IRs_of_ItemById_WithLatestRanking_UDF
GO

/*
** Returns all revisions of an item with rankings for LatestAlpha, LatestCreated and LatestWorking
**
** SELECT * FROM IRs_of_ItemById_WithLatestRanking('{EC6EF27E-D0EA-4333-B584-FAEB4D290613}')
** IrId             IrNameRev         IrCreateDate   IrReleaseDate   LatestAlphaRank   LatestCreatedRank   LatestWorkingRank
** {C0648FBF-7BF5   65010000032/001   04.05.2007     04.10.2007      -3                -3                   0
** {BB5C5105-1661   65010000032/003   04.07.2007     04.05.2007      -1                 0                  -1
** {8E5B9D30-3180   65010000032/004   29.06.2007                      0                -1                  -2
** {B0E362A7-D80B   65010000032/002   04.05.2007     04.05.2007      -2                -2                  -3
** {EE08415C-B646   65010000032/000   27.04.2007     04.05.2007      -4                -4                  -4
*/
CREATE FUNCTION IRs_of_ItemById_WithLatestRanking_UDF (
  @Id uniqueidentifier
)
RETURNS TABLE
AS RETURN

SELECT
  IrId,
  IrNameRev,
  IrCreateDate,
  --IrReleaseDate,
  (ROW_NUMBER() OVER (ORDER BY IrCreateDate DESC) -1) * -1          AS LatestByCreationDateRank,
  (ROW_NUMBER() OVER (ORDER BY IrNameRev DESC) -1) * -1             AS LatestByAlphaRevOrderRank --,
  --(ROW_NUMBER() OVER (ORDER BY IrReleaseOrCreateDate DESC) -1) * -1 AS LatestWorkingRank
FROM (
      SELECT
        IR.Id AS IrId,
        IR.ObjectName AS IrNameRev,
        IR.ObjectCreateDate AS IrCreateDate --,
        -- IR.ObjectReleaseDate AS IrReleaseDate,
        -- CASE WHEN IR.ObjectReleaseDate IS NOT NULL AND IR.ObjectReleaseDate > IR.ObjectCreateDate
--           THEN IR.ObjectReleaseDate
--           ELSE IR.ObjectCreateDate
--         END AS IrReleaseOrCreateDate
      FROM
        dbo.Objects_Ir_Rel_tbl I_IR
        INNER JOIN dbo.Objects_tbl IR ON (I_IR.ObjectId = IR.Id)
      WHERE
        I_IR.IrId = @Id AND
        IR.ObjectClass = 'IR'
) AS Source
GO


Print '-- [Item_IrLatestRank_Update] ------------------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "Item_IrLatestRank_Update" and type = "P")
Drop Procedure Item_IrLatestRank_Update
GO

/*
** Updates the columns ObjectIrLatestRank, ObjectIsIrLatest and ObjectIsIrLatestWorking
** for all IRs and DSs of a specified item.
*/
CREATE PROCEDURE Item_IrLatestRank_Update
  @Id uniqueidentifier
AS
BEGIN

  SET NOCOUNT ON

  /* Get Item Site */
  DECLARE @itemFromSiteId NVARCHAR(32)
  SELECT @itemFromSiteId = ObjectFromSiteID
  FROM dbo.Objects_tbl
  WHERE Id = @Id


  /* Get IrLatestRule Configuration from SiteDefinition */
  DECLARE @latestByCreationDateRuleName  NVARCHAR(32) = N'Rul1_LatestByCreationDate'
  DECLARE @latestByAlphaRevOrderRuleName NVARCHAR(32) = N'Rul2_LatestByAlphaRevOrder'
  DECLARE @latestRule NVARCHAR(32)
  SELECT @latestRule = Config_IrRevLatestRule
  FROM Sites_tbl
  WHERE SiteID = @itemFromSiteId

  /* Validate @latestRule value */
  IF @latestRule IS NULL
     SET @latestRule = @latestByCreationDateRuleName
  IF (@latestRule <> @latestByCreationDateRuleName AND @latestRule <> @latestByAlphaRevOrderRuleName)
     SET @latestRule = @latestByCreationDateRuleName


  /* Get IRs with Rankings  */
  DECLARE @irRanks TABLE (
    IrId uniqueidentifier,
    LatestRank      INT,
    IsLatest        BIT --,
    -- IsLatestWorking BIT
  )
  INSERT INTO @irRanks
  SELECT IrID,
         CASE @latestRule
              WHEN @latestByCreationDateRuleName  THEN LatestByCreationDateRank
              WHEN @latestByAlphaRevOrderRuleName THEN LatestByAlphaRevOrderRank
              ELSE NULL
         END AS LatestRank,
         CASE @latestRule
              WHEN @latestByCreationDateRuleName  THEN CASE WHEN LatestByCreationDateRank=0  THEN 1 ELSE 0 END
              WHEN @latestByAlphaRevOrderRuleName THEN CASE WHEN LatestByAlphaRevOrderRank=0 THEN 1 ELSE 0 END
              ELSE NULL
         END AS IsLatest --,
       --  CASE LatestWorkingRank
       --       WHEN 0 THEN 1
       --       ELSE 0
       --  END AS IsLatestWorking
  FROM dbo.IRs_of_ItemById_WithLatestRanking_UDF(@Id)
  ;


  /* Update IR Records */
  MERGE INTO dbo.Objects_tbl AS IR
  USING      @irRanks AS IR_RANKS
        ON   IR.Id = IR_RANKS.IrId
  WHEN MATCHED
       AND (IR.ObjectIrLatestRank IS NULL      OR
            IR.ObjectIsIrLatest IS NULL        OR
           -- IR.ObjectIsIrLatestWorking IS NULL OR
            IR.ObjectIrLatestRank      <> IR_RANKS.LatestRank OR
            IR.ObjectIsIrLatest        <> IR_RANKS.IsLatest) --   OR
           -- IR.ObjectIsIrLatestWorking <> IR_RANKS.IsLatestWorking)
  THEN UPDATE
       SET IR.ObjectIrLatestRank      = IR_RANKS.LatestRank,
           IR.ObjectIsIrLatest        = IR_RANKS.IsLatest --,
          -- IR.ObjectIsIrLatestWorking = IR_RANKS.IsLatestWorking
  ;


  /* Update DS Records of IRs */
  WITH IR_RANKS_DS_CTE AS
  (
      SELECT IR_DS_Rel.ObjectId AS DsId,
             MAX(IR_RANKS.LatestRank)                                      AS LatestRank,
             MAX(CASE WHEN IR_RANKS.IsLatest = 1        THEN 1 ELSE 0 END) AS IsLatest --,
           --  MAX(CASE WHEN IR_RANKS.IsLatestWorking = 1 THEN 1 ELSE 0 END) AS IsLatestWorking
      FROM @irRanks AS IR_RANKS
      LEFT JOIN dbo.Objects_Ir_Rel_tbl IR_DS_Rel ON IR_DS_Rel.IrId = IR_RANKS.IrId
      GROUP BY IR_DS_Rel.ObjectId
  )
  MERGE INTO dbo.Objects_tbl AS DS
  USING      IR_RANKS_DS_CTE
        ON   DS.Id = IR_RANKS_DS_CTE.DsId
  WHEN MATCHED
       AND (DS.ObjectIrLatestRank IS NULL      OR
            DS.ObjectIsIrLatest IS NULL        OR
           -- DS.ObjectIsIrLatestWorking IS NULL OR
            DS.ObjectIrLatestRank      <> IR_RANKS_DS_CTE.LatestRank OR
            DS.ObjectIsIrLatest        <> IR_RANKS_DS_CTE.IsLatest) --   OR
           -- DS.ObjectIsIrLatestWorking <> IR_RANKS_DS_CTE.IsLatestWorking)
  THEN UPDATE
       SET DS.ObjectIrLatestRank      = IR_RANKS_DS_CTE.LatestRank,
           DS.ObjectIsIrLatest        = IR_RANKS_DS_CTE.IsLatest --,
          -- DS.ObjectIsIrLatestWorking = IR_RANKS_DS_CTE.IsLatestWorking
  ;

  -- RAISERROR('#Debug#', 1, 2)  ;
  -- INSERT INTO XXzzzzzDbMaintain_tbl (ID) VALUES ('debug') ;

/* -- Query for diagnostics purposes
SELECT
  I.Id,
  IR.Id,
  DS.Id,
  I.ObjectClass,
  I.ObjectName,

  IR.ObjectClass,
  IR.ObjectName,
  IR.ObjectIrLatestRank,
  IR.ObjectIsIrLatest --,
  --IR.ObjectIsIrLatestWorking,

  DS.ObjectClass,
  DS.ObjectName,
  DS.ObjectIrLatestRank,
  DS.ObjectIsIrLatest --,
  --DS.ObjectIsIrLatestWorking
FROM
  dbo.Objects_Ir_Rel_tbl I_IR
  INNER JOIN dbo.Objects_tbl I ON (I_IR.IrId = I.Id)
  INNER JOIN dbo.Objects_tbl IR ON (I_IR.ObjectId = IR.Id)
  INNER JOIN dbo.Objects_Ir_Rel_tbl ON (IR.Id = dbo.Objects_Ir_Rel_tbl.IrId)
  INNER JOIN dbo.Objects_tbl DS ON (dbo.Objects_Ir_Rel_tbl.ObjectId = DS.Id)
WHERE
  I.Id = @Id
ORDER BY
  I.Id,
  I.ObjectCliName,
  IR.ObjectCliName,
  DS.ObjectCliName
*/

END

GO

Print '-- [Item_IrLatestRank_UpdateAll] ------------------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "Item_IrLatestRank_UpdateAll" and type = "P")
Drop Procedure Item_IrLatestRank_UpdateAll
GO


/*
** Updates the columns ObjectIrLatestRank, ObjectIsIrLatest
** for all IRs and DSs of all items within the database.
*/
CREATE PROCEDURE Item_IrLatestRank_UpdateAll
AS
BEGIN
  -- SET NOCOUNT ON
  DECLARE @Id uniqueidentifier,
          @ERRMsg nvarchar(64),
          @ObjectFromSiteID nvarchar(32),
          @ObjectName      nvarchar(128), -- 25.10.2017 S.Gueth (size from 64 to 128)
          @ObjectCreateDate DATETIME,
          @lastPrintTime DATETIME = GETDATE(),
          @printTimeInterval INT  = 5,
          @ActionDoneCounter INT  = 0,
          @ActionToDoN INT        = 0,
          @PrintEvery INT         = 250,
          @DoAction bit,
          @DoPrint bit


 print '-----------------------------------------------------------------------'
 print 'Processing: Updated Ir.Rank via Rev.Rule for Entire Database'


 -- #Doc: count Items
 SELECT @ActionToDoN=count(*)
    FROM dbo.Objects_tbl O
    WHERE O.ObjectClass = N'I'

 -- #Doc: define Cursor
 DECLARE ItemsCursor CURSOR LOCAL FOR
    SELECT O.Id, O.ObjectName, O.ObjectFromSiteID, O.ObjectCreateDate
    FROM dbo.Objects_tbl O
    WHERE O.ObjectClass = N'I'
    ORDER BY O.ObjectFromSiteID ASC, O.ObjectCreateDate ASC

  OPEN ItemsCursor
  FETCH NEXT FROM ItemsCursor INTO @Id, @ObjectName, @ObjectFromSiteID, @ObjectCreateDate

  WHILE @@FETCH_STATUS = 0
  BEGIN

    SET @ActionDoneCounter = @ActionDoneCounter + 1
    if (@ActionDoneCounter = 1) or (@ActionDoneCounter = @ActionToDoN) or (@ActionDoneCounter % @PrintEvery = 0) or (DATEDIFF(SECOND, @lastPrintTime, GETDATE()) >= @printTimeInterval)
    begin
      set @DoPrint = 1
    end
    ELSE
    BEGIN
      set @DoPrint = 0
    END

    --PRINT N'Updating IrLatestRank for Item ' + CONVERT(NVARCHAR(50), @Id)
    BEGIN TRY
      EXEC dbo.Item_IrLatestRank_Update @Id
    END TRY
    BEGIN CATCH
      set @ERRMsg = 'ErrNum:' + CONVERT(nvarchar(64),@@ERROR) + ' /ErrMsg:' + ERROR_MESSAGE()
      print('ERR: processing f.SiteID:' + @ObjectFromSiteID + ' ItemId:' + @ObjectName + ' -> ' + @ERRMsg)
    END CATCH

    if @DoPrint = 1
    begin
      SET @lastPrintTime = GETDATE()
      --  PRINT N'Updated IrLatestRank for ' + CONVERT(NVARCHAR(10), @itemsProcessedCount) + N' Items so far...'
      print('Upd. Ir.Rank via Rev.Rule:[' +
             CONVERT(nvarchar(64),@ActionDoneCounter) + '/' + CONVERT(nvarchar(64),@ActionToDoN) + ':' +
            Str((100.00*@ActionDoneCounter/@ActionToDoN),6,2)  + '%]' + ' Last Item:' + CONVERT(nvarchar(64),@ObjectCreateDate) + ' /f.Site:' + @ObjectFromSiteID + ' ItemId:' + @ObjectName
            )
    end

    FETCH NEXT FROM ItemsCursor INTO @Id, @ObjectName, @ObjectFromSiteID, @ObjectCreateDate
  END

END

GO

-- Exec Item_IrLatestRank_UpdateAll

-- GO
