Print ''
Print '-------------------------------------------------------------------------'
Print ' Script for JobMgr:'
Print ' Autor........: J.Feuerstein'
Print ' Description..: resets processstatus to N for all non Processed Objects'
Print ' #New.........: 03.03.2017/J.Fe'
Print '-------------------------------------------------------------------------'
GO

use [#DB_N#]

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

/*
 resets for Objects_tbl.@ObjectId status regarding @ProcStateCol
*/
CREATE Procedure ObjectsUpdate_ProcessStatusUpdateToN
                    (
                    @DoAction bit,
                    @ObjectId         uniqueidentifier , -- ID des zu bearbeitenden Datensatzes in Objects_tbl
                    @ObjectFromSiteID nvarchar(32) ,    -- von welcher site
                    @ObjectCliName    nvarchar(255) ,   -- CliName des Objectes
                    @ProcStateCol     nvarchar(32) ,    -- Info: PCol
                    @PState_Cur       nvarchar(34) ,    -- Process Status Cur
                    @PState_New       nvarchar(34) ,    -- Process Status New
                    @ProgressTxt_p    nvarchar(255)
                    )

As
BEGIN

 DECLARE @ExecuteSQLstr nvarchar(max)

 if @PState_Cur = @PState_New
  begin
   return
  end

 if upper(left(@PState_Cur,1)) <> 'X'
  begin
   set @ExecuteSQLstr =  'UPDATE Objects_tbl SET [' + @ProcStateCol + ']=''' + @PState_New + ''' WHERE Objects_tbl.Id=''' + CONVERT(nvarchar(128), @ObjectId) + ''''
   print ('  |-> (' + @ProgressTxt_p + ') #Update:[' + @ObjectFromSiteID + ':' + @ObjectCliName + '] [' + @ProcStateCol + '] from:''' + @PState_Cur + ''' to:''' + @PState_New + '''')
   if @DoAction = 1
   begin
     execute sp_executesql @ExecuteSQLstr
   end
  end
END
GO
--------------------------------------------------------------------------------

DECLARE @Id  uniqueidentifier,
        @ObjectCliName nvarchar(255),
        @PrintTxt nvarchar(255),
        @ProgressTxt nvarchar(255),
        @ObjectFromSiteID nvarchar(32),
        @ObjectOwningSiteID nvarchar(32),
        @PState_New nvarchar(255),
        @ActionDoneCounter INT,
        @ActionToDoN INT,
        @PrintEvery INT,
        @DoAction bit,
        @DoPrint bit

DECLARE
        @ProcStateColValueNew nvarchar(32),
        @ProcStateCol01ValueCur nvarchar(32),
        @ProcStateCol02ValueCur nvarchar(32),
        @ProcStateCol03ValueCur nvarchar(32),
        @ProcStateCol04ValueCur nvarchar(32),
        @ProcStateCol05ValueCur nvarchar(32),
        @ProcStateCol06ValueCur nvarchar(32),
        @ProcStateCol07ValueCur nvarchar(32),
        @ProcStateCol08ValueCur nvarchar(32),
        @ProcStateCol09ValueCur nvarchar(32),
        @ProcStateCol10ValueCur nvarchar(32),
        @ProcStateCol11ValueCur nvarchar(32),
        @ProcStateCol12ValueCur nvarchar(32),
        @ProcStateCol13ValueCur nvarchar(32),
        @ProcStateCol14ValueCur nvarchar(32),
        @ProcStateCol15ValueCur nvarchar(32),
        @ProcStateCol16ValueCur nvarchar(32),
        @ProcStateCol17ValueCur nvarchar(32),
        @ProcStateCol18ValueCur nvarchar(32),
        @ProcStateCol19ValueCur nvarchar(32),
        @ProcStateCol20ValueCur nvarchar(32),
        @ProcStateColRfValueCur nvarchar(255)


SET @ActionToDoN = 0
SET @ActionDoneCounter = 0
-- #ToDo: Setup: print del ervery n'times
set @PrintEvery=1000
-- #ToDo: Setup: @DoAction Action
--  if @DoAction = 1 then Data is processed
--  if @DoAction = 0 then only Print is done
set @DoAction=1

-- #ToDo: set new Status
set @ProcStateColValueNew = 'N'


-- #Doc: count objects need to be processed
select @ActionToDoN=count(*)
FROM
  dbo.Objects_tbl o
  LEFT OUTER JOIN dbo.JobControl_ObjectsProcessData_tbl p ON (o.Id = p.Objects_tbl_ID)
WHERE
  p.ID IS NULL

print('.')
Print '-------------------------------------------------------------------------'
print('Set status in DB.: ' + '#DB_N#')
print('N to process.....: [' + CONVERT(nvarchar(64),@ActionToDoN) + ']')
Print '-------------------------------------------------------------------------'

-- Curor fuer alle zu durchlaufenden Objekte erstellen
declare tblCursor cursor fast_forward for
select o.Id,
       o.ObjectCliName,
       o.ObjectFromSiteID,
       o.ObjectOwningSiteID,
       o.ProcStateCol01,
       o.ProcStateCol02,
       o.ProcStateCol03,
       o.ProcStateCol04,
       o.ProcStateCol05,
       o.ProcStateCol06,
       o.ProcStateCol07,
       o.ProcStateCol08,
       o.ProcStateCol09,
       o.ProcStateCol10,
       o.ProcStateCol11,
       o.ProcStateCol12,
       o.ProcStateCol13,
       o.ProcStateCol14,
       o.ProcStateCol15,
       o.ProcStateCol16,
       o.ProcStateCol17,
       o.ProcStateCol18,
       o.ProcStateCol19,
       o.ProcStateCol20,
       o.ProcStateColRf
FROM
  dbo.Objects_tbl o
  LEFT OUTER JOIN dbo.JobControl_ObjectsProcessData_tbl p ON (o.Id = p.Objects_tbl_ID)
WHERE
  p.ID IS NULL

set nocount on
open tblCursor
 -- process all Objects via Cursor
 while 1=1
  begin

    fetch next from tblCursor into
       @Id,
       @ObjectCliName,
       @ObjectFromSiteID,
       @ObjectOwningSiteID,
       @ProcStateCol01ValueCur,
       @ProcStateCol02ValueCur,
       @ProcStateCol03ValueCur,
       @ProcStateCol04ValueCur,
       @ProcStateCol05ValueCur,
       @ProcStateCol06ValueCur,
       @ProcStateCol07ValueCur,
       @ProcStateCol08ValueCur,
       @ProcStateCol09ValueCur,
       @ProcStateCol10ValueCur,
       @ProcStateCol11ValueCur,
       @ProcStateCol12ValueCur,
       @ProcStateCol13ValueCur,
       @ProcStateCol14ValueCur,
       @ProcStateCol15ValueCur,
       @ProcStateCol16ValueCur,
       @ProcStateCol17ValueCur,
       @ProcStateCol18ValueCur,
       @ProcStateCol19ValueCur,
       @ProcStateCol20ValueCur,
       @ProcStateColRfValueCur

    -- if (@ActionDoneCounter >=1000) break
    -- exit on last object
    if (@@fetch_status <> 0) break

    SET @ActionDoneCounter = @ActionDoneCounter + 1
    if (@ActionDoneCounter = 1) or (@ActionDoneCounter = @ActionToDoN) or (@ActionDoneCounter % @PrintEvery = 0)
    begin
      set @DoPrint = 1
    end
    ELSE
    BEGIN
      set @DoPrint = 0
    END

    set @ProgressTxt = Str((100.00*@ActionDoneCounter/@ActionToDoN),6,2)  + ' perc.'
    if @DoPrint = 1
    begin
      set @PrintTxt='#Process:[' + CONVERT(nvarchar(64),@ActionDoneCounter) + '/' + CONVERT(nvarchar(64),@ActionToDoN) + ':' + @ProgressTxt + ']'
      RAISERROR (@PrintTxt, 0, 1) WITH NOWAIT ;
    end

    EXECUTE ObjectsUpdate_ProcessStatusUpdateToN @DoAction, @Id, @ObjectFromSiteID, @ObjectCliName, 'ProcStateCol01', @ProcStateCol01ValueCur, @ProcStateColValueNew, @ProgressTxt
    EXECUTE ObjectsUpdate_ProcessStatusUpdateToN @DoAction, @Id, @ObjectFromSiteID, @ObjectCliName, 'ProcStateCol02', @ProcStateCol02ValueCur, @ProcStateColValueNew, @ProgressTxt
    EXECUTE ObjectsUpdate_ProcessStatusUpdateToN @DoAction, @Id, @ObjectFromSiteID, @ObjectCliName, 'ProcStateCol03', @ProcStateCol03ValueCur, @ProcStateColValueNew, @ProgressTxt
    EXECUTE ObjectsUpdate_ProcessStatusUpdateToN @DoAction, @Id, @ObjectFromSiteID, @ObjectCliName, 'ProcStateCol04', @ProcStateCol04ValueCur, @ProcStateColValueNew, @ProgressTxt
    EXECUTE ObjectsUpdate_ProcessStatusUpdateToN @DoAction, @Id, @ObjectFromSiteID, @ObjectCliName, 'ProcStateCol05', @ProcStateCol05ValueCur, @ProcStateColValueNew, @ProgressTxt
    EXECUTE ObjectsUpdate_ProcessStatusUpdateToN @DoAction, @Id, @ObjectFromSiteID, @ObjectCliName, 'ProcStateCol06', @ProcStateCol06ValueCur, @ProcStateColValueNew, @ProgressTxt
    EXECUTE ObjectsUpdate_ProcessStatusUpdateToN @DoAction, @Id, @ObjectFromSiteID, @ObjectCliName, 'ProcStateCol07', @ProcStateCol07ValueCur, @ProcStateColValueNew, @ProgressTxt
    EXECUTE ObjectsUpdate_ProcessStatusUpdateToN @DoAction, @Id, @ObjectFromSiteID, @ObjectCliName, 'ProcStateCol08', @ProcStateCol08ValueCur, @ProcStateColValueNew, @ProgressTxt
    EXECUTE ObjectsUpdate_ProcessStatusUpdateToN @DoAction, @Id, @ObjectFromSiteID, @ObjectCliName, 'ProcStateCol09', @ProcStateCol09ValueCur, @ProcStateColValueNew, @ProgressTxt
    EXECUTE ObjectsUpdate_ProcessStatusUpdateToN @DoAction, @Id, @ObjectFromSiteID, @ObjectCliName, 'ProcStateCol10', @ProcStateCol10ValueCur, @ProcStateColValueNew, @ProgressTxt
    EXECUTE ObjectsUpdate_ProcessStatusUpdateToN @DoAction, @Id, @ObjectFromSiteID, @ObjectCliName, 'ProcStateCol11', @ProcStateCol11ValueCur, @ProcStateColValueNew, @ProgressTxt
    EXECUTE ObjectsUpdate_ProcessStatusUpdateToN @DoAction, @Id, @ObjectFromSiteID, @ObjectCliName, 'ProcStateCol12', @ProcStateCol12ValueCur, @ProcStateColValueNew, @ProgressTxt
    EXECUTE ObjectsUpdate_ProcessStatusUpdateToN @DoAction, @Id, @ObjectFromSiteID, @ObjectCliName, 'ProcStateCol13', @ProcStateCol13ValueCur, @ProcStateColValueNew, @ProgressTxt
    EXECUTE ObjectsUpdate_ProcessStatusUpdateToN @DoAction, @Id, @ObjectFromSiteID, @ObjectCliName, 'ProcStateCol14', @ProcStateCol14ValueCur, @ProcStateColValueNew, @ProgressTxt
    EXECUTE ObjectsUpdate_ProcessStatusUpdateToN @DoAction, @Id, @ObjectFromSiteID, @ObjectCliName, 'ProcStateCol15', @ProcStateCol15ValueCur, @ProcStateColValueNew, @ProgressTxt
    EXECUTE ObjectsUpdate_ProcessStatusUpdateToN @DoAction, @Id, @ObjectFromSiteID, @ObjectCliName, 'ProcStateCol16', @ProcStateCol16ValueCur, @ProcStateColValueNew, @ProgressTxt
    EXECUTE ObjectsUpdate_ProcessStatusUpdateToN @DoAction, @Id, @ObjectFromSiteID, @ObjectCliName, 'ProcStateCol17', @ProcStateCol17ValueCur, @ProcStateColValueNew, @ProgressTxt
    EXECUTE ObjectsUpdate_ProcessStatusUpdateToN @DoAction, @Id, @ObjectFromSiteID, @ObjectCliName, 'ProcStateCol18', @ProcStateCol18ValueCur, @ProcStateColValueNew, @ProgressTxt
    EXECUTE ObjectsUpdate_ProcessStatusUpdateToN @DoAction, @Id, @ObjectFromSiteID, @ObjectCliName, 'ProcStateCol19', @ProcStateCol19ValueCur, @ProcStateColValueNew, @ProgressTxt
    EXECUTE ObjectsUpdate_ProcessStatusUpdateToN @DoAction, @Id, @ObjectFromSiteID, @ObjectCliName, 'ProcStateCol20', @ProcStateCol20ValueCur, @ProcStateColValueNew, @ProgressTxt
    EXECUTE ObjectsUpdate_ProcessStatusUpdateToN @DoAction, @Id, @ObjectFromSiteID, @ObjectCliName, 'ProcStateColRf', @ProcStateColRfValueCur, @ProcStateColValueNew, @ProgressTxt


  end

 -- #Doc: Close Cursor
close tblCursor
deallocate tblCursor

Print ''
Print '-------------------------------------------------------------------------'
Print ' Script executed'
Print '-------------------------------------------------------------------------'
GO
