--begin tran
IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'PkPrinterType'
    WHERE SysObjects.Name = 'PrinterTypes'
)
BEGIN
  ALTER TABLE Countries ADD [FkCountryMasterTemplateAlt] Int NULL
  IF EXISTS (select TOP 1 id from sysobjects  WITH (NOLOCK) where id = object_id('FK_PrinterSetupXTab_PrinterTypes'))
  BEGIN 
    IF OBJECTPROPERTY(object_id('FK_PrinterSetupXTab_PrinterTypes'), N'IsConstraint') = 1  
	  ALTER TABLE [PrinterSetupXTab] DROP CONSTRAINT [FK_PrinterSetupXTab_PrinterTypes] 
  END 
  IF EXISTS (select TOP 1 id from sysobjects  WITH (NOLOCK) where id = object_id('FK_PaperTraySetup_PrinterTypes'))
  BEGIN 
    IF OBJECTPROPERTY(object_id('FK_PaperTraySetup_PrinterTypes'), N'IsConstraint') = 1  
	  ALTER TABLE [PaperTraySetup] DROP CONSTRAINT FK_PaperTraySetup_PrinterTypes 
  END 
  IF EXISTS (select TOP 1 id from sysobjects  WITH (NOLOCK) where id = object_id('FK_PrinterTypeTemplateXtab_PrinterTypes'))
  BEGIN 
    IF OBJECTPROPERTY(object_id('FK_PrinterTypeTemplateXtab_PrinterTypes'), N'IsConstraint') = 1  
	  ALTER TABLE [PrinterTypeTemplateXtab] DROP CONSTRAINT [FK_PrinterTypeTemplateXtab_PrinterTypes] 
  END 
  IF EXISTS (select TOP 1 id from sysobjects  WITH (NOLOCK) where id = object_id('PK_PrinterTypes_1'))
  BEGIN 
    IF OBJECTPROPERTY(object_id('PK_PrinterTypes_1'), N'IsConstraint') = 1  
	  ALTER TABLE [PrinterTypes] DROP CONSTRAINT [PK_PrinterTypes_1] 
  END 
  ELSE 
    DROP INDEX [PrinterTypes].[PK_PrinterTypes_1]
  IF EXISTS (select TOP 1 id from sysobjects  WITH (NOLOCK) where id = object_id('PrinterSetupXTab'))BEGIN IF OBJECTPROPERTY(object_id('PK_PrinterSetupXTab'), N'IsConstraint') = 1 
    ALTER TABLE dbo.PrinterSetupXTab DROP CONSTRAINT PK_PrinterSetupXTab END ELSE DROP INDEX PrinterSetupXTab.PK_PrinterSetupXTab
  IF EXISTS (select TOP 1 id from sysobjects  WITH (NOLOCK) where id = object_id('PrinterTypeTemplateXtab'))BEGIN IF OBJECTPROPERTY(object_id('PK_PrinterTypeTemplateXtab'), N'IsConstraint') = 1 
    ALTER TABLE dbo.PrinterTypeTemplateXtab DROP CONSTRAINT PK_PrinterTypeTemplateXtab END ELSE DROP INDEX [PrinterTypeTemplateXtab].[PK_PrinterTypeTemplateXtab]

  ALTER TABLE PrinterTypes ADD [PkPrinterType] Int NULL
  ALTER TABLE PrinterTypeTemplateXtab ADD [FkCustomerHotel] Int NULL
  ALTER TABLE PrinterSetupXtab ADD FkTemplate Int NULL
  ALTER TABLE PrinterSetupXtab ADD FkCountry Int NULL
  ALTER TABLE PrinterSetupXtab ADD FkInvoiceLayout Int NULL
  ALTER TABLE PrinterSetupXtab ADD OutputDevice Int NULL
  ALTER TABLE PrinterSetupXtab ADD SortNumber Int NULL 
  ALTER TABLE PrinterSetupXtab ADD CreatedByApp Int NULL 
  ALTER TABLE Templates ADD FkTemplateMaster Int NULL 
  ALTER TABLE Invoices ADD FkCountry Int NULL 
  ALTER TABLE Invoices ADD FkTemplate Int NULL 
END 
GO
IF (SELECT COUNT(*) FROM PrinterTypes WHERE PkPrinterType IS NULL) <> 0 
BEGIN
  DECLARE @HotelCountryNumber INT, @Sql VARCHAR(100)
  SELECT TOP 1 @HotelCountryNumber = CS.CountryNumber FROM Countries CS WITH (NOLOCK) 
    INNER JOIN Customer CR WITH (NOLOCK) ON CS.CountryNumber = CR.CountryNumber   
  WHERE CR.CustomerTypeNumber = 7 AND (CR.LocalHotel = 1 OR CR.HotelNumber > 0) AND ISNULL(CR.MARKASDELETED, 0) = 0
  ORDER BY CR.LocalHotel DESC, CR.HotelNumber
  --Countries Table
  UPDATE Countries SET FkCountryMasterTemplateAlt = @HotelCountryNumber WHERE CountryNumber <> @HotelCountryNumber
  --Templates Table
  --Prepare for Change TemplateGroup and Name fields not to allow null 
  UPDATE Templates SET TemplateGroup = 11 WHERE TemplateGroup IS NULL
  UPDATE Templates SET CountryNumber = @HotelCountryNumber WHERE CountryNumber IS NULL
  --PrinterTypes Table
  --Maintain PrinterTypes Table   
  DECLARE @LType INT, @Id INT, @Name VARCHAR(30)
  SET @LType = 2
  SET @Name = 'GiftCard'
  IF (SELECT COUNT(*) FROM PrinterTypes WITH (NOLOCK) WHERE PrinterTypeNumber = @LType) = 0
    INSERT INTO PrinterTypes (PrinterTypeNumber, Name) VALUES (@LType, @Name)
  SET @LType = 7
  SET @Name = 'Letter'
  IF (SELECT COUNT(*) FROM PrinterTypes WITH (NOLOCK) WHERE PrinterTypeNumber = @LType) = 0
    INSERT INTO PrinterTypes (PrinterTypeNumber, Name) VALUES (@LType, @Name)
  SET @LType = 8
  SET @Name = 'Confirmation'
  IF (SELECT COUNT(*) FROM PrinterTypes WITH (NOLOCK) WHERE PrinterTypeNumber = @LType) = 0
    INSERT INTO PrinterTypes (PrinterTypeNumber, Name) VALUES (@LType, @Name)
  SET @LType = 9
  SET @Name = 'SlipStand'
  IF (SELECT COUNT(*) FROM PrinterTypes WITH (NOLOCK) WHERE PrinterTypeNumber = @LType) = 0
    INSERT INTO PrinterTypes (PrinterTypeNumber, Name) VALUES (@LType, @Name)
  SET @LType = 10
  SET @Name = 'CourseCertificate'
  IF (SELECT COUNT(*) FROM PrinterTypes WITH (NOLOCK) WHERE PrinterTypeNumber = @LType) = 0
    INSERT INTO PrinterTypes (PrinterTypeNumber, Name) VALUES (@LType, @Name)
  SET @LType = 18
  SET @Name = 'NoSale'
  IF (SELECT COUNT(*) FROM PrinterTypes WITH (NOLOCK) WHERE PrinterTypeNumber = @LType) = 0
    INSERT INTO PrinterTypes (PrinterTypeNumber, Name) VALUES (@LType, @Name)
--Update it from PrinterTypeNumber
  IF (SELECT COUNT(*) FROM PrinterTypes WITH (NOLOCK) WHERE NOT PkPrinterType IS NULL) = 0 
  BEGIN
    SET @Sql = 'UPDATE PrinterTypes SET PkPrinterType = PrinterTypeNumber WHERE PkPrinterType IS NULL' --stupid fucking SQL server  does take it prehand that it will be performed and complain
    EXEC (@Sql)
  END

  --change PkPrinterType to be a identity field, updates are proceeded later
  --currently done using xml, but needs to be done here to be able to use the script in update mode

--PrinterTypeTemplateXtab Table  
--read existing relations and hotels, add field FkCustomerHotel and copy the existing records to have one for each hotel
  DECLARE @Hotel INT, @HotelRef INT
  SET @HotelRef=0
  DECLARE #TransCur CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT CustomerNumber FROM Customer WITH (NOLOCK) 
    WHERE CustomerTypeNumber = 7 and (LocalHotel = 1 or HotelNumber > 0) AND ISNULL(MarkAsDeleted, 0) = 0
  OPEN #TransCur
  FETCH NEXT FROM #TransCur
  INTO @Hotel
  WHILE @@FETCH_STATUS = 0 -- safe
  BEGIN
    IF @HotelRef = 0 
	BEGIN
	  SET @HotelRef = @Hotel
	  UPDATE PrinterTypeTemplateXtab SET FkCustomerHotel= @Hotel WHERE ISNULL(FkCustomerHotel, 0) = 0
    END
	ELSE
      INSERT INTO PrinterTypeTemplateXtab
        SELECT PrinterTypeNumber, TemplateNumber, @Hotel AS FkCustomerHotel FROM PrinterTypeTemplateXtab WITH (NOLOCK) 
        WHERE FkCustomerHotel = @HotelRef    
    FETCH NEXT FROM #TransCur
    INTO @Hotel
  END
  DELETE PrinterTypeTemplateXtab WHERE ISNULL(FkCustomerHotel, 0) = 0    
  CLOSE #TransCur
  DEALLOCATE  #TransCur
  --PrinterSetup Table
  UPDATE PrinterSetupXTab SET SortNumber = 1 WHERE SortNumber IS NULL 
END
GO
IF EXISTS (select TOP 1 id from sysobjects  WITH (NOLOCK) where id = object_id('FK_PaperTraySetup_PrinterTypes'))BEGIN IF OBJECTPROPERTY(object_id('FK_PaperTraySetup_PrinterTypes'), N'IsConstraint') = 1  
  ALTER TABLE [PaperTraySetup] DROP CONSTRAINT [FK_PaperTraySetup_PrinterTypes] END 
CREATE TABLE dbo.[Tmp_PrinterTypes] 
(
  [PrinterTypeNumber] int NOT NULL,
  [Name] varchar (30) NULL,
  [LedgerVoucherNoOfCopies] smallint NULL,
  [CashNoOfCopies] smallint NULL,
  [PkPrinterType] int IDENTITY (1, 1) NOT NULL
  PRIMARY KEY CLUSTERED (PkPrinterType)
)
go
SET IDENTITY_INSERT dbo.Tmp_PrinterTypes ON
go
IF EXISTS(SELECT * FROM PrinterTypes)
  INSERT INTO dbo.Tmp_PrinterTypes ([PrinterTypeNumber],[Name],[LedgerVoucherNoOfCopies],[CashNoOfCopies],[PkPrinterType]) 
SELECT [PrinterTypeNumber],[Name],[LedgerVoucherNoOfCopies],[CashNoOfCopies],[PkPrinterType] FROM PrinterTypes TABLOCKX
go
SET IDENTITY_INSERT dbo.Tmp_PrinterTypes OFF
go
DROP TABLE PrinterTypes
go
EXEC sp_rename Tmp_PrinterTypes, PrinterTypes
go
ALTER TABLE PrinterSetupXTab ADD [PkOutputRule] int IDENTITY (1, 1) NOT NULL
go
ALTER TABLE dbo.PrinterSetupXTab ADD CONSTRAINT
	PK_PrinterSetupXTab PRIMARY KEY CLUSTERED 
	(
	PkOutputRule
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
go
--PrinterTypes Table
IF (SELECT COUNT(*) FROM PrinterTypes WITH (NOLOCK) WHERE PrinterTypeNumber <> PkPrinterType) = 0 
BEGIN
  DECLARE @LType INT, @Id INT, @Ledger INT, @Cash INT, @Name VARCHAR(30)
  SET @LType = 18 --NoSale Type for opening Tray
  SET @Name = 'NoSale'
  IF (SELECT COUNT(*) FROM PrinterTypes WITH (NOLOCK) WHERE PrinterTypeNumber = @LType) = 0
    INSERT INTO PrinterTypes (PrinterTypeNumber, Name) VALUES (@LType, @Name)
  --if field Name does not exist in PrinterTypes Table then perform conversion
  SET @LType = 14 --sale replaces bon 5
  SET @Name = 'Sale'
  IF (SELECT COUNT(*) FROM PrinterTypes WITH (NOLOCK) WHERE PrinterTypeNumber = @LType) = 0
    IF (SELECT COUNT(*) FROM PrinterTypes WITH (NOLOCK) WHERE PrinterTypeNumber = 5) = 0
      INSERT INTO PrinterTypes (PrinterTypeNumber, Name) VALUES (@LType, @Name)
    ELSE
      UPDATE PrinterTypes SET PrinterTypeNumber = @LType, Name = @Name WHERE PrinterTypeNumber = 5
  SET @LType = 17 --table replaces slip 6
  SET @Name = 'Table'
  IF (SELECT COUNT(*) FROM PrinterTypes WITH (NOLOCK) WHERE PrinterTypeNumber = @LType) = 0
    IF (SELECT COUNT(*) FROM PrinterTypes WITH (NOLOCK) WHERE PrinterTypeNumber = 6) = 0
      INSERT INTO PrinterTypes (PrinterTypeNumber, Name) VALUES (@LType, @Name)
    ELSE
      UPDATE PrinterTypes SET PrinterTypeNumber = @LType, Name = @Name WHERE PrinterTypeNumber = 6
  --Split Bill/Invoice by keep this kind as Invoice and adding new Room
  SET @LType = 15 --Invoice replaces Bill/Invoice 1
  SET @Name = 'Invoice'
  IF (SELECT COUNT(*) FROM PrinterTypes WITH (NOLOCK) WHERE PrinterTypeNumber = @LType) = 0
    IF (SELECT COUNT(*) FROM PrinterTypes WITH (NOLOCK) WHERE PrinterTypeNumber = 1) = 0
      INSERT INTO PrinterTypes (PrinterTypeNumber, Name) VALUES (@LType, @Name)
    ELSE
      UPDATE PrinterTypes SET PrinterTypeNumber = @LType, Name = @Name WHERE PrinterTypeNumber = 1
  SET @LType = 16 --Room a copy of Bill/Invoice 1
  SET @Name = 'Room'
  IF (SELECT COUNT(*) FROM PrinterTypes WITH (NOLOCK) WHERE PrinterTypeNumber = @LType) = 0
    AND (SELECT COUNT(*) FROM PrinterTypes WITH (NOLOCK) WHERE PrinterTypeNumber = 15) = 1
  BEGIN
    SELECT @Ledger = LedgerVoucherNoOfCopies, @Cash = CashNoOfCopies FROM PrinterTypes WHERE PrinterTypeNumber = 15
    INSERT INTO PrinterTypes (PrinterTypeNumber, LedgerVoucherNoOfCopies, CashNoOfCopies, Name) VALUES (@LType, @Ledger, @Cash, @Name)
    SELECT @Id = PkPrinterType FROM PrinterTypes WITH (NOLOCK) WHERE PrinterTypeNumber = @LType
    IF (SELECT COUNT(*) FROM PaperTraySetup PTS WITH (NOLOCK) 
	  INNER JOIN PrinterTypes PT WITH (NOLOCK) ON PTS.DocumentPart = PT.PrinterTypeNumber
      WHERE PT.PrinterTypeNumber = 15) > 0
      INSERT INTO PaperTraySetup (PrinterNumber, DocTypeNumber, DocumentPart, Tray )
        SELECT PrinterNumber, @LType, DocumentPart, Tray FROM PaperTraySetup PTS WITH (NOLOCK) 
	      INNER JOIN PrinterTypes PT WITH (NOLOCK) ON PTS.DocumentPart = PT.PrinterTypeNumber
    	  WHERE PT.PrinterTypeNumber = 15
	IF (SELECT COUNT(*) FROM PrinterSetupXTab PSX WITH (NOLOCK) 
	    INNER JOIN PrinterTypes PT WITH (NOLOCK) ON PSX.PrinterTypeNumber = PT.PkPrinterType
	  WHERE PT.PrinterTypeNumber = 15) > 0
      INSERT INTO PrinterSetupXTab (TerminalNumber, PrinterNumber, PrinterTypeNumber, Name, FkTemplate, FkInvoiceLayout, FkCountry, OutputDevice, SortNumber)
	    SELECT TerminalNumber, PrinterNumber, @Id, PSX.Name, FkTemplate, FkInvoiceLayout, FkCountry, OutputDevice, SortNumber 
	    FROM PrinterSetupXTab PSX WITH (NOLOCK) 
	      INNER JOIN PrinterTypes PT WITH (NOLOCK) ON PSX.PrinterTypeNumber = PT.PkPrinterType
	    WHERE PT.PrinterTypeNumber = 15
	IF (SELECT COUNT(*)  FROM PrinterTypeTemplateXtab PTT WITH (NOLOCK) 
	    INNER JOIN PrinterTypes PT WITH (NOLOCK) ON PTT.PrinterTypeNumber = PT.PkPrinterType
	  WHERE PT.PrinterTypeNumber = 15) > 0
      INSERT INTO PrinterTypeTemplateXtab (PrinterTypeNumber, TemplateNumber, FkCustomerHotel)
	    SELECT @Id, TemplateNumber, FkCustomerHotel FROM PrinterTypeTemplateXtab PTT WITH (NOLOCK) 
	      INNER JOIN PrinterTypes PT WITH (NOLOCK) ON PTT.PrinterTypeNumber = PT.PkPrinterType
	    WHERE PT.PrinterTypeNumber = 15
  END
  --Convert Bon/A4 Rule to Sale/Template Rule in PrinterSetup and Cleanup
  DECLARE @A4TemplateId INT, @Hcn INT, @OutputKindIdSource INT, @OutputKindIdDest INT
  SELECT Top 1 @Hcn= CustomerNumber FROM Customer WITH (NOLOCK) 
  WHERE CustomerTypeNumber = 7 and (LocalHotel = 1 OR HotelNumber = 0)
  ORDER BY LocalHotel Desc, HotelNumber 
  SELECT @A4TemplateId = TemplateNumber FROM PrinterTypeTemplateXtab WITH (NOLOCK) WHERE PrinterTypeNumber = 11 and FkCustomerHotel = @Hcn 
  IF ISNULL(@A4TemplateId, 0) > 0 
  BEGIN
    SELECT @OutputKindIdSource = PkPrinterType FROM PrinterTypes WITH (NOLOCK) WHERE PrinterTypeNumber = 11 --Bon/A4
    SELECT @OutputKindIdDest = PkPrinterType FROM PrinterTypes WITH (NOLOCK) WHERE PrinterTypeNumber = 15 --Sale
    UPDATE PrinterSetupXtab SET PrinterTypeNumber = @OutputKindIdDest, FkTemplate = @A4TemplateId WHERE PrinterTypeNumber = @OutputKindIdSource
	DELETE PrinterTypeTemplateXtab WHERE PrinterTypeNumber = @OutputKindIdSource
  END
END
--rollback tran