CREATE PROCEDURE [dbo].[ConvertToDomino] AS

/*
  Changes:
    KJ 20100607: Bug: Fix for "Duplicate Key when update AccountDaily"
    KJ: Economy updates was not runned
    KJ 20091124: Better handling of HotelNumber and HotelCustomerNumber
    nb20091120 script created as SP in db
    AV2008 1112 Pia wanted all the accounts to point on at least one department on hotelnumber 0
*/

DECLARE @HotelNumber INT
DECLARE @HotelCustomerNumber INT

SELECT @HotelCustomerNumber = CustomerNumber, @HotelNumber = HotelNumber
FROM Customer WITH (NOLOCK) 
WHERE HotelNumber >= 0 
AND LocalHotel=1 
AND CustomerTypeNumber = 7
AND ISNULL(MarkAsDeleted, 0) = 0

IF (@HotelCustomerNumber >= 0) AND (@HotelNumber >= 0)
BEGIN
  SELECT HotelNumber, CustomerNumber
  INTO #tblCustomer FROM Customer WITH (NOLOCK)
  WHERE HotelNumber >= 0 
  AND CustomerTypeNumber = 7
  AND ISNULL(MarkAsDeleted, 0) = 0
  
  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'AccountDaily') > 0)
  BEGIN
    DELETE AccountDaily WHERE Date >= (SELECT TOP 1 SystemDate FROM System WITH (NOLOCK))
	update AccountDaily set HotelCustomerNumber=@HotelCustomerNumber where ISNULL(HotelCustomerNumber, 0) NOT IN (SELECT CustomerNumber FROM #tblCustomer)
  END	

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'AdditionalSalesFromTurnover') > 0)
	update AdditionalSalesFromTurnover set HotelCustomerNumber=@HotelCustomerNumber where ISNULL(HotelCustomerNumber, 0) NOT IN (SELECT CustomerNumber FROM #tblCustomer)

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'CanceledBillNumbers') > 0)
  update CanceledBillNumbers set HotelCustomerNumber=@HotelCustomerNumber where ISNULL(HotelCustomerNumber, 0) NOT IN (SELECT CustomerNumber FROM #tblCustomer)

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'CustomerOrdreBook') > 0)
	update CustomerOrdreBook set HotelCustomerNumber=@HotelCustomerNumber where ISNULL(HotelCustomerNumber, 0) NOT IN (SELECT CustomerNumber FROM #tblCustomer)

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'Folios') > 0)
    update Folios set HotelCustomerNumber=@HotelCustomerNumber where ISNULL(HotelCustomerNumber, 0) NOT IN (SELECT CustomerNumber FROM #tblCustomer)

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'MISTable') > 0)
	update MISTable set HotelCustomerNumber=@HotelCustomerNumber where ISNULL(HotelCustomerNumber, 0) NOT IN (SELECT CustomerNumber FROM #tblCustomer)

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'Transactions') > 0)
	update Transactions set HotelCustomerNumber=@HotelCustomerNumber where ISNULL(HotelCustomerNumber, 0) NOT IN (SELECT CustomerNumber FROM #tblCustomer)

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'CardTypeTemplateXTab') > 0)
	update CardTypeTemplateXTab set HotelCustomerNumber=@HotelCustomerNumber where ISNULL(HotelCustomerNumber, 0) NOT IN (SELECT CustomerNumber FROM #tblCustomer)

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'AllotmentHotelXTAB') > 0)
	update AllotmentHotelXTAB set HotelCustomerNumber=@HotelCustomerNumber where ISNULL(HotelCustomerNumber, 0) NOT IN (SELECT CustomerNumber FROM #tblCustomer)

---------------------------------------------------------------------------------
/*
select sysobjects.name from syscolumns 
inner join sysobjects on syscolumns.id = sysobjects.id and syscolumns.name Like 'hotelnumber' and sysobjects.xtype = 'u'
*/


  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'AllocationProfile') > 0)
	update AllocationProfile set HotelNumber=@HotelNumber where ISNULL(HotelNumber, -1) NOT IN (SELECT HotelNumber FROM #tblCustomer)

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'BudgetMonth') > 0)
	update BudgetMonth set HotelNumber=@HotelNumber where ISNULL(HotelNumber, -1) NOT IN (SELECT HotelNumber FROM #tblCustomer)

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'ConferenceRooms') > 0)
	update ConferenceRooms set HotelNumber=@HotelNumber where ISNULL(HotelNumber, -1) NOT IN (SELECT HotelNumber FROM #tblCustomer)

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'ConferenceRoomTypes') > 0)
	update ConferenceRoomTypes set HotelNumber=@HotelNumber where ISNULL(HotelNumber, -1) NOT IN (SELECT HotelNumber FROM #tblCustomer)

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'CustomerFieldsPerHotel') > 0)
	update CustomerFieldsPerHotel set HotelNumber=@HotelNumber where ISNULL(HotelNumber, -1) NOT IN (SELECT HotelNumber FROM #tblCustomer)

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'Departments') > 0)
	update Departments set HotelNumber=@HotelNumber where ISNULL(HotelNumber, -1) NOT IN (SELECT HotelNumber FROM #tblCustomer)

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'FollowUp') > 0)
	update FollowUp set HotelNumber=@HotelNumber where ISNULL(HotelNumber, -1) NOT IN (SELECT HotelNumber FROM #tblCustomer)

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'MISBudget') > 0)
	update MISBudget set HotelNumber=@HotelNumber where ISNULL(HotelNumber, -1) NOT IN (SELECT HotelNumber FROM #tblCustomer)

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'ReservationMain') > 0)
	update ReservationMain set HotelNumber=@HotelNumber where ISNULL(HotelNumber, -1) NOT IN (SELECT HotelNumber FROM #tblCustomer)

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'RoomBookingStatus') > 0)
	update RoomBookingStatus set HotelNumber=@HotelNumber where ISNULL(HotelNumber, -1) NOT IN (SELECT HotelNumber FROM #tblCustomer)

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'RoomBookingStatusInsale') > 0)
	update RoomBookingStatusInsale set HotelNumber=@HotelNumber where ISNULL(HotelNumber, -1) NOT IN (SELECT HotelNumber FROM #tblCustomer)

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'RoomBookingStatusYield') > 0)
	update RoomBookingStatusYield set HotelNumber=@HotelNumber where ISNULL(HotelNumber, -1) NOT IN (SELECT HotelNumber FROM #tblCustomer)

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'Rooms') > 0)
	update Rooms set HotelNumber=@HotelNumber where ISNULL(HotelNumber, -1) NOT IN (SELECT HotelNumber FROM #tblCustomer)

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'RoomTypes') > 0)
	update RoomTypes set HotelNumber=@HotelNumber where ISNULL(HotelNumber, -1) NOT IN (SELECT HotelNumber FROM #tblCustomer)

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'Templates') > 0)
	update Templates set HotelNumber=@HotelNumber where ISNULL(HotelNumber, -1) NOT IN (SELECT HotelNumber FROM #tblCustomer)

/*
  if ((select count(*) from sysobjects (nolock) where xtype = 'U' and name LIKE 'UserXtabHotel') > 0)
	update UserXtabHotel set HotelNumber=@HotelNumber where ISNULL(HotelNumber, -1) NOT IN (SELECT HotelNumber FROM #tblCustomer)
*/

  if ((select count(*) from sysobjects WITH (nolock) where xtype = 'U' and name LIKE 'AccountDepartmentXtab') > 0)
  begin
    --AV 2008 1112 Pia wanted all the accounts to point on at least one department on hotelnumber 0
    DECLARE @AccNumber INT 
    DECLARE @IDX INT
    DECLARE @PK_Department INT 

    SELECT top 1 @pk_department = ISNULL(PK_Department, 0) FROM Departments WITH (NOLOCK) WHERE HotelNumber = @HotelNumber
    IF @PK_Department > 0
    BEGIN 
      DECLARE AccCur  CURSOR FORWARD_ONLY FOR 
      SELECT DISTINCT Accounts.AccountNumber
      FROM Accounts WITH (nolock) 
      OPEN AccCur
      FETCH NEXT FROM AccCur
      INTO  @AccNumber
      WHILE (@@FETCH_STATUS <> -1)
      BEGIN
        IF NOT EXISTS (SELECT AccountNumber, FK_Department FROM AccountDepartmentXTab WITH (NOLOCK) WHERE FK_Department = @PK_Department AND AccountNumber=@AccNumber)
          INSERT INTO AccountDepartmentXTab(AccountNumber,FK_Department)  VALUES(@AccNumber,@PK_Department)
        FETCH NEXT FROM AccCur INTO  @AccNumber
      END
      Close AccCur
      Deallocate AccCur
    END
  end

  DROP TABLE #tblCustomer
END
