CREATE PROCEDURE [dbo].[OnlineWareWithRules] @AStartDate DATETIME, @AEndDate DATETIME, @AWareList VARCHAR(1000) AS
SET NOCOUNT ON

/*
	Created by Troels Buch Apr 2015
	
	Changes -
	TBU20150420: Removed RuleValue from #RecurringWare
	TBU20150414: Added Find The lowest availability half hour
		
*/

CREATE TABLE #RecurringWare (
	  WareNumber int NOT NULL ,
	  WareName varchar(100),
	  StartDate Datetime NULL,
	  EndTime Datetime NULL,
	  /*RuleValue int NULL,*/
	  Available int NULL)
  ON [PRIMARY];

CREATE TABLE #WareListTab (
	  listpos int NULL ,	  
	  number int NULL)
  ON [PRIMARY];

Declare @WareNumber int
Declare @WareName varchar(100)
Declare @StartDate Datetime
Declare @EndTime Datetime
Declare @RuleValue int
Declare @Available int

DECLARE @WLListPos INT
DECLARE @WLNumber INT
Declare @NumberOf int

INSERT INTO #WareListTab (listpos, number)
  SELECT * from iter_intlist_to_table(@AWareList)where number>0 

INSERT INTO #RecurringWare
  exec RecurringPatternGetWare @AStartDate, @AEndDate, @AWareList 

--select * from #RecurringWare -- TEST
DELETE FROM #RecurringWare where Available <= 0

DECLARE @DayDiff INT
SET @DayDiff = DATEDIFF(DAY, @AStartDate, @AEndDate)

IF LEN(@AWareList) > 0
BEGIN

--select * from #RecurringWare -- TEST
  DECLARE #WareListCursor CURSOR FAST_FORWARD FOR
  
  SELECT listpos, number FROM #WareListTab 
  OPEN #WareListCursor
  FETCH NEXT FROM #WareListCursor
  INTO @WLListPos, @WLNumber
  
  WHILE (@@FETCH_STATUS <> -1)
  BEGIN  
    --print '@WLNumber'
    --print @WLNumber
    
    -- Find The lowest availability half hour
    DECLARE @TimeHour INT
    DECLARE @TimeMinute INT
    SET @TimeHour = 5
    SET @TimeMinute = 0
    
    WHILE @TimeHour <= 23
    BEGIN 
	  --select Top 1 Available from #RecurringWare WHERE WareNumber=@WLNumber AND DATEPART(HOUR, StartDate)=@TimeHour AND DATEPART(MINUTE, StartDate)=@TimeMinute ORDER BY Available
        
		UPDATE #RecurringWare SET Available = (select Top 1 Available from #RecurringWare WHERE WareNumber=@WLNumber AND DATEPART(HOUR, StartDate)=@TimeHour AND DATEPART(MINUTE, StartDate)=@TimeMinute ORDER BY Available) 
		WHERE WareNumber=@WLNumber AND DATEPART(HOUR, StartDate)=@TimeHour AND DATEPART(MINUTE, StartDate)=@TimeMinute 
      
      IF @TimeMinute = 0
      BEGIN
        SET @TimeMinute = 30
      END
      ELSE
      BEGIN
		SET @TimeHour = @TimeHour+1
        SET @TimeMinute = 0
      END
    END
    
    DELETE FROM #RecurringWare WHERE WareNumber=@WLNumber AND CONVERT(VARCHAR(8), StartDate,108) IN (select CONVERT(VARCHAR(8), StartDate,108) from #RecurringWare WHERE WareNumber=@WLNumber GROUP BY CONVERT(VARCHAR(8), StartDate,108) HAVING count(CONVERT(VARCHAR(8), StartDate,108)) <=  @DayDiff)
    
    FETCH NEXT FROM #WareListCursor
    INTO @WLListPos, @WLNumber
  END
    
  CLOSE #WareListCursor
  DEALLOCATE #WareListCursor
  
  
END 

SELECT * FROM #RecurringWare WHERE DAY(StartDate) = DAY(@AStartDate)

DROP TABLE #RecurringWare
DROP TABLE #WareListTab



