CREATE PROCEDURE [dbo].[copyTables] @sourcedate Varchar(8), @targetdate Varchar(8), @firstconferenceroomnumber int, @deleteExistingConferencerooms bit, @AHotelNumber int =-1  AS

--Last Changed RN 2012 0411 Altered DELETE statements for ConferenceRoomsXtab and ConferenceRooms if there is a HotelNumber
--Last Changed RN 2012 0313 Now using HotelNumber when deleting existing conferencerooms
--Last Changed RN 2011 0323 Now using HotelNumber when checking for existing ConferenceRooms
--Last Changed AV 2009 0107 also copy the pax on the table + optimized sql
--Last Changed AV 2008 1112 also copy the Conferenceroomtype
--Last changed AV 2008 0719 fix for not removing tables on target day
--Last Changed AV 2007 0924 also copy the tablename
--Last Changed AV 2008 0122 problem wiht not deleting tables with chair > 0 
--Last Changed AV 2008 0123 a fix for orphen records in conferneceroomsxtab

DECLARE @ConferenceRoomNumberid INT
DECLARE @ConferenceRoomNumber INT
DECLARE @tabledate DATETIME
DECLARE @ConferenceRoomname VARCHAR(100)
DECLARE @FoundConferenceRoomName VARCHAR(100)
DECLARE @FoundKey INT
DECLARE @FoundConferenceRoomTypeNumber INT
DECLARE @ConferenceRoomTypeNumber INT
DECLARE @ConferenceroomDescription VARCHAR (100)
DECLARE @Color INT
DECLARE @istable INT
DECLARE @chairnumber INT
DECLARE @HotelNumber INT
DECLARE @Pax INT

DECLARE @Key INT
DECLARE @Conflineid INT
DECLARE @WasUpdated BIT

DECLARE @ShowTableNameInTableBooking BIT
SELECT @ShowTableNameInTableBooking = CONVERT(Bit,KeyValue) FROM HotELSETUp WITH (NOLOCK)  WHERE  SearchKey = 'ShowTableNameInTableBooking'


DECLARE CopyCur cursor Forward_Only For
SELECT Conferenceroomnumber,Number,name,conferenceroomtypenumber,description, color, istable,chairnumber,HotelNumber, Pax  FROM Conferencerooms WITH (NOLOCK)
WHERE Tabledate = Convert(Datetime,@Sourcedate) AND IsTable = 1 
AND ((@AHotelNumber >-1 and HotelNumber = @AHotelNumber) OR (@AHotelNumber = -1) )   

SET @key = @FirstConferenceroomnumber

  IF @DeleteExistingconferenceRooms = 1 -- We must DEALLOCATE bookings which uses a number we are about to delete (the bookings which has a number which is not present in the source day)
  BEGIN

     DECLARE UpdateCur cursor Forward_Only For
     SELECT Reservationconflinenumber FROM reservationconferenceline WITH (NOLOCK) 
     LEFT OUTER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = ReservationConferenceLine.ReferenceNumber
     WHERE TableBooking = 1 and Arrivaldate =@Targetdate
     AND DBO.GetScreenTableNumber(conferenceroomnumber) not in (SELECT Number FROM conferencerooms WITH (nolock) WHERE istable=1 and tabledate=convert(datetime,@sourcedate) )
     AND (@AHotelNumber = -1 OR (@AHotelNumber > -1 AND ReservationMain.HotelNumber = @AHotelNumber))       
 
     OPEN Updatecur
     FETCH NEXT FROM UpdateCur INTO @ConfLineId
     WHILE (@@FETCH_status=0)
     BEGIN
       UPDATE ReservationConferenceLine SET conferenceroomnumber = NULL WHERE ReservationConflinenumber = @Conflineid
       FETCH NEXT FROM UpdateCur INTO @ConfLineId      
     END
     CLOSE updatecur
     DEALLOCATE updatecur

     if @AhotelNumber= -1
     BEGIN 
       -- We dont run domino here or we have SELECTed all hotels in the box and therefore we can wipe everytying
       DELETE FROM conferenceroomsXtab WHERE  conferenceroomnumber in (SELECT conferenceroomnumber FROM conferencerooms WITH (nolock) WHERE tabledate=Convert(datetime,@targetdate)) 
       DELETE FROM conferencerooms  WHERE TableDate = Convert(datetime,@targetdate)
     END
     ELSE
     BEGIN
       -- We have SELECTed a hotel and we can not wipe everything here....
       /* 20120411/RN: CR041504, New DELETE statements: */
       DELETE FROM ConferenceRoomsXtab
       WHERE ConferenceRoomNumber IN (SELECT ConferenceRoomNumber FROM ConferenceRooms WITH (NOLOCK) WHERE TableDate = Convert(datetime, @targetdate) AND HotelNumber = @AHotelNumber)
       
       DELETE FROM ConferenceRooms
       WHERE TableDate = Convert(datetime,@targetdate)
         AND Istable = 1
         AND HotelNumber = @AHotelNumber
       
       /* 20120411/RN: CR041504, Removed...
       DELETE FROM ConferenceRoomsXtab 
       WHERE conferenceroomnumber in (SELECT conferenceroomnumber FROM conferencerooms(nolock) WHERE tabledate=Convert(datetime,@targetdate) and hotelnumber=@AhotelNumber)
         AND ((dbo.GetScreenTableNumber(conferenceroomnumber) not in (SELECT Number FROM conferencerooms(nolock) WHERE istable=1 and tabledate=convert(datetime,@sourcedate) AND HotelNumber = @AHotelNumber)) 
          OR  (ConferenceRoomNumber IN (SELECT ConferenceRoomNumber FROM ConferenceRooms(NOLOCK) WHERE ISNULL(ChairNumber,0) > 0 AND HotelNumber = @AHotelNumber))
      )
      
       DELETE FROM conferencerooms 
       WHERE Tabledate =Convert(datetime,@targetdate) 
         AND hotelNumber=@AHotelNumber
         AND ((dbo.GetScreenTableNumber(conferenceroomnumber) NOT IN (SELECT Number FROM conferencerooms(nolock) WHERE istable=1 and tabledate=convert(datetime,@sourcedate) AND HotelNumber = @AHotelNumber ))
         OR   (ISNULL(ChairNumber, 0) > 0 )
             )
       */
     END
    
  END

Open CopyCur
FETCH NEXT FROM CopyCur
INTO @Conferenceroomnumberid,@Conferenceroomnumber, @ConferenceroomName,@ConferenceRoomTypeNumber,@Conferenceroomdescription, @color, @Istable,@Chairnumber,@HotelNumber, @Pax
While(@@FETCH_status=0)
BEGIN
  SET @WasUpdated = 0
  if not exists (SELECT conferenceroomnumber FROM conferencerooms WITH (nolock) WHERE conferenceroomnumber=@key)
    if not exists (SELECT conferenceroomnumber FROM conferencerooms WITH (nolock) WHERE number=@ConferenceRoomNumber and convert(varchar(8),TableDate,112)=@Targetdate and istable=1 and chairnumber=@ChairNumber and HotelNumber=@AHotelNumber)
    BEGIN
      INSERT INTO conferencerooms (Conferenceroomnumber,number,tabledate,conferenceroomtypenumber,name,description,color,istable,chairnumber,HotelNumber, Pax)  values (@key,@conferenceroomnumber,Convert(datetime,@targetdate),@conferenceroomtypenumber,@conferenceroomname,@conferenceroomdescription,@color,@Istable,@ChairNumber,@HotelNumber, @Pax)
      SET @WasUpdated = 1
    END
    ELSE
    BEGIN
        SELECT @FoundConferenceroomname = ISNULL(Name, ''), @FoundKey = ConferenceroomNumber, @FoundConferenceRoomTypeNumber = ConferenceRoomTypeNumber FROM conferencerooms WITH (nolock) WHERE number=@ConferenceRoomNumber and convert(varchar(8),TableDate,112)=@Targetdate and istable=1 and chairnumber=@ChairNumber and HotelNumber=@AHotelNumber
--        if (@FoundConferenceRoomName <> @ConferenceRoomName) and (ISNULL(@FoundKey, 0) > 0)
          if (ISNULL(@FoundKey, 0) > 0) AND ((@ShowTableNameInTableBooking = 1 AND @FoundConferenceRoomName <> ISNULL(@ConferenceRoomName, '')) OR @ConferenceRoomTypeNumber <> @FoundConferenceRoomTypeNumber)
            UPDATE ConferenceRooms SET Name = @ConferenceRoomName, ConferenceRoomTypeNumber = @ConferenceRoomTypeNumber WHERE ConferenceRoomNumber = @FoundKey
    END 

  if not exists (SELECT conferenceroomnumber FROM conferenceroomsXtab WITH (nolock) WHERE conferenceroomnumber=@key and CollectsConferenceRoomnumber=@key) and (@WasUpdated =1)
      INSERT INTO conferenceroomsXtab (Conferenceroomnumber,CollectsConferenceRoomNumber)  values (@key,@key) 

  SET @key = @key +1

  FETCH NEXT FROM CopyCur
  INTO @Conferenceroomnumberid,@Conferenceroomnumber, @ConferenceroomName,@ConferenceRoomTypeNumber,@Conferenceroomdescription, @color, @Istable,@Chairnumber,@HotelNumber, @Pax  

END


--OK so far the tables are copied, we need to syncronize the table links.
    DECLARE @RecordCount int
    DECLARE @Link int
    DECLARE @Targetid int
    DECLARE @FirstLoop bit
    CLOSE CopyCur

    Open CopyCur
    FETCH NEXT FROM CopyCur
    INTO @Conferenceroomnumberid,@Conferenceroomnumber, @ConferenceroomName,@ConferenceRoomTypeNumber,@Conferenceroomdescription, @color, @Istable,@Chairnumber,@HotelNumber, @Pax
    While(@@FETCH_status=0)
    BEGIN
      SELECT @RecordCount = count (*) FROM conferenceRoomsXtab WITH (NOLOCK)  WHERE conferenceRoomsXtab.ConferenceRoomNumber=@ConferenceRoomNumberId and collectsConferenceRoomNumber <>@ConferenceRoomNumberId
      if @RecordCount > 0
      BEGIN
        DECLARE @collectsId int
        DECLARE @ScrNumber int
        SET @FirstLoop = 1

        DECLARE XTabCur cursor Forward_Only For
        SELECT CollectsConferenceRoomNumber,Number FROM conferenceRoomsXtab WITH (NOLOCK) Inner join conferenceRooms WITH (nolock) on conferenceRoomsxTab.CollectsConferenceRoomNumber = ConferenceRooms.conferenceroomNumber
        WHERE conferenceRoomsXtab.ConferenceRoomNumber=@ConferenceRoomNumberId and collectsConferenceRoomNumber <>@ConferenceRoomNumberId
        Open XTabCur
        FETCH NEXT FROM XTabCur INTO @CollectsId,@ScrNumber
        While(@@FETCH_status=0)
        BEGIN
            SELECT @Link = ConferenceRoomNumber FROM ConferenceRooms WITH (nolock) WHERE isTable = 1 and tabledate=Convert(datetime,@targetdate) and Number = @ScrNumber
            SELECT @Targetid = ConferenceRoomNumber FROM ConferenceRooms WITH (nolock) WHERE tabledate=Convert(datetime,@targetdate) and Number = @Conferenceroomnumber

             if @firstloop = 1 and @deleteExistingconferenceRooms = 1
               delete FROM conferenceRoomsXtab WHERE conferenceRoomsXtab.ConferenceRoomNumber=@TargetId and collectsConferenceRoomNumber <>@TargetId
             SET @firstloop = 0

            if IsNull(@Link,0) > 0 and IsNull(@Targetid,0) > 0
              if not exists (SELECT conferenceroomnumber FROM conferenceroomsXtab WITH (nolock) WHERE conferenceroomnumber=@Targetid and CollectsConferenceRoomnumber=@Link)
                INSERT INTO conferenceroomsXtab (Conferenceroomnumber,CollectsConferenceRoomNumber)  values (@TargetId,@Link)              

          FETCH NEXT FROM XTabCur INTO @CollectsId,@ScrNumber      
        END
        CLOSE XTabCur
        DEALLOCATE XTabCur
      END

    FETCH NEXT FROM CopyCur
    INTO @Conferenceroomnumberid,@Conferenceroomnumber, @ConferenceroomName,@ConferenceRoomTypeNumber,@Conferenceroomdescription,@color, @Istable,@Chairnumber,@HotelNumber, @Pax  

  END
  IF @deleteExistingconferenceRooms = 1
  BEGIN
    DELETE FROM conferenceroomsxtab  WHERE collectsconferenceroomnumber not in (SELECT conferenceroomnumber FROM conferencerooms WITH (nolock)) 
    DELETE FROM conferenceroomsxtab  WHERE conferenceroomnumber not in (SELECT conferenceroomnumber FROM conferencerooms WITH (nolock)) 
  END
CLOSE copycur
DEALLOCATE copycur
