CREATE PROCEDURE DBO.[UpdateCustomerStatus] AS 
SET NOCOUNT ON

--vd April 2016 Revised

DECLARE @LockTimeOut INT
SET @LockTimeOut = @@LOCK_TIMEOUT
SET LOCK_TIMEOUT 1000

DECLARE @CountLocks INT
DECLARE @TurnPast MONEY
DECLARE @TurnFuture MONEY
DECLARE @CustomerNumber INT
DECLARE @SystemDate VARCHAR(8)  
DECLARE @CustomerStatus INT

DECLARE @csNone INT
DECLARE @csPotential INT
DECLARE @csPending INT
DECLARE @csPrevious INT
DECLARE @csCurrent INT
DECLARE @csFuture INT

SET @csNone = 0
SET @csPotential = 1
SET @csPending = 2
SET @csPrevious = 3
SET @csCurrent = 4
SET @csFuture = 5

SELECT @CountLocks = COUNT (*)
FROM sys.dm_tran_locks l JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id
WHERE OBJECT_NAME(p.OBJECT_ID) = 'Customer' --TableName

IF @CountLocks = 0
BEGIN
  SELECT @SystemDate = SystemDate 
  FROM System WITH (NOLOCK) 

  DECLARE CustomerCursor CURSOR FOR 
  SELECT DISTINCT CO.CustomerNumber,
  (
    SELECT ISNULL(SUM(
	  ISNULL(TurnOverRooms, 0) +
	  ISNULL(TurnOverHalls, 0) +
	  ISNULL(TurnOverDayMeetings, 0) +
	  ISNULL(TurnOverRestaurant, 0) +
	  ISNULL(TurnOverOther, 0)), 0)
    FROM CustomerOrdreBook WITH (NOLOCK)
    WHERE CustomerOrdreBook.CustomerNumber = CO.CustomerNumber
      AND CONVERT(VARCHAR,DateForValue,112) <=  @systemdate
  ),
  (
    SELECT ISNULL(SUM(
	  ISNULL(TurnOverRooms, 0) +
	  ISNULL(TurnOverHalls, 0) +
	  ISNULL(TurnOverDayMeetings, 0) +
	  ISNULL(TurnOverRestaurant, 0) +
	  ISNULL(TurnOverOther, 0)), 0)
    FROM CustomerOrdreBook WITH (NOLOCK)
    WHERE CustomerOrdreBook.CustomerNumber = CO.CustomerNumber
      AND CONVERT(VARCHAR,DateForValue,112) >  @systemdate
  )
  FROM CustomerOrdreBook CO WITH (NOLOCK) 
  INNER JOIN Customer C WITH (NOLOCK) ON C.CustomerNumber = CO.CustomerNumber 
    AND ISNULL(C.MarkAsDeleted, 0) = 0
  GROUP BY CO.CustomerNumber
  OPEN CustomerCursor

  FETCH NEXT FROM CustomerCursor INTO @CustomerNumber, @TurnPast, @TurnFuture

  WHILE @@fetch_status = 0
  BEGIN
    IF @TurnFuture = 0 AND @TurnPast = 0 
      SET @CustomerStatus = @csNone 
    ELSE IF @TurnFuture <> 0 AND @TurnPast <> 0 
      SET @CustomerStatus = @csCurrent
    ELSE IF @TurnFuture <> 0 AND @TurnPast = 0 
      SET @CustomerStatus = @csFuture  
    ELSE IF @TurnFuture = 0 AND @TurnPast <> 0 
      SET @CustomerStatus = @csPrevious 

    IF @CustomerStatus <> @csNone
      UPDATE Customer 
      SET CustomerStatus = @CustomerStatus
      WHERE CustomerNumber = @CustomerNumber AND CustomerStatus <> @CustomerStatus

    FETCH NEXT FROM CustomerCursor INTO @CustomerNumber, @TurnPast, @TurnFuture
  END

  CLOSE CustomerCursor
  DEALLOCATE CustomerCursor
END

DECLARE @SqlStr VARCHAR(25)
SET @SqlStr = 'SET LOCK_TIMEOUT '+ CONVERT(VARCHAR, @LockTimeOut)
EXEC (@SqlStr)

GO