/*
begin tran
DECLARE @RecCount int
SELECT @RecCount = Count(*) FROM CanceledBillNumbers (NOLOCK) 
PRINT 'CanceledBillNumbers: '+CONVERT(VARCHAR, @RecCount)
SELECT @RecCount = Count(*) FROM Invoices WITH (NOLOCK)
PRINT 'Invoices: '+CONVERT(VARCHAR, @RecCount)
SELECT @RecCount = Count(*) FROM Transactions T WITH (NOLOCK) 
WHERE T.ShowOnBillFlag = 1 and T.MovedDeleted = 0 and ISNULL(T.InvoiceBillNumber, 0) <> 0 and NOT T.HotelCustomerNumber IS NULL AND T.HotelCustomerNumber > 0
PRINT 'TransactionsInvoice: '+CONVERT(VARCHAR, @RecCount)
*/
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE id = object_id(N'[dbo].[Invoices]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) AND
  NOT EXISTS (SELECT * FROM dbo.SysObjects WHERE id = object_id(N'[dbo].[Invoice]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE dbo.[Invoice](
		[PkInvoice] [int] IDENTITY (1, 1) NOT NULL,
		[FkCustomerHotel] [int] NOT NULL,
		[FkDepartment] [int] NOT NULL,
		[FkStaff] [int] NOT NULL,
		[FkBillLayout] [int] NULL,
		[BillNumber] [int] NULL,
		[InvoiceNumber] [int] NULL,
		[CreatedDate] [datetime] NOT NULL,
		[CancelDate] [datetime] NULL,
		[CopyPrinted] [bit] NULL,
		[Note] [nvarchar](MAX) NULL,
		[Picture] [image] NULL,
		[Report] [image] NULL,
		[FkInvoiceRelated] [int] NULL,
  	        [GiroId] [varchar](21) NULL,
	        [CreditorId] [varchar](8) NULL,
		[FkTemplate] [int] NULL,
		[FkCountry] [int] NULL,
		[ReceiptType] [smallint] NOT NULL,
		[ReceiptNumber] [int] NOT NULL,

  	  CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED 
	  (
		[PkInvoice] DESC
	  )
	) ON [PRIMARY]

  CREATE TABLE dbo.[InvoiceTransactionXTab](
		[FkInvoice] [int] NOT NULL,
		[FkTransaction] [int] NOT NULL,
  	  CONSTRAINT [PK_InvoiceTransactionXTab] PRIMARY KEY CLUSTERED 
	  (
		[FkInvoice] DESC,
		[FkTransaction] DESC
	  )
	) ON [PRIMARY]

  CREATE TABLE [dbo].[ReceiptFiscal](
	  [PkReceiptFiscal] [int] IDENTITY (1, 1) NOT NULL,
  	  [FkInvoice] [int] NOT NULL,
	  [CashId] [int] NULL,
	  [ExternalNumber] [varchar](50) NULL,
	  [ManufactIdFCU] [varchar](17) NULL,
	  [CashRegisterMarking] [varchar](16) NULL,
	  [Terminal] [varchar](50) NULL,
  CONSTRAINT [PK_ReceiptFiscal] PRIMARY KEY CLUSTERED 
  (
	  [PkReceiptFiscal] ASC
  ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
END
IF NOT EXISTS (SELECT Name FROM SysIndexes  WITH (NOLOCK) WHERE Name = 'IX_Invoice_BillNumber')
begin
  CREATE NONCLUSTERED INDEX [IX_Invoice_BillNumber] ON [dbo].[Invoice] 
  (
    [BillNumber] ASC
  )
end
IF (SELECT COUNT(*) FROM Invoices (NOLOCK) WHERE BillNumber NOT IN (SELECT BillNumber FROM Invoice (NOLOCK) WHERE NOT BillNumber is NULL)) > 0
BEGIN
  SET ANSI_WARNINGS OFF
  INSERT INTO Invoice (FkCustomerHotel, FkDepartment, FkStaff, FkBillLayout, BillNumber, InvoiceNumber, CreatedDate, CopyPrinted, Note, Picture, GiroId, CreditorId, FkTemplate, FkCountry, ReceiptType, ReceiptNumber)
    SELECT HotelCustomerNumber, FkDepartment, UserIdNumber, BillTypeNumber, BillNumber, InvoiceNumber, DateTimeStamp, CopyPrinted, CONVERT(NVARCHAR(MAX), Note), Picture, GiroId, CreditorId, FkTemplate, FkCountry, 
	  CASE WHEN ISNULL(InvoiceNumber, 0) > 0 THEN 2 ELSE 1 END, CASE WHEN ISNULL(InvoiceNumber, 0) > 0 THEN InvoiceNumber ELSE BillNumber END 
	FROM Invoices TABLOCKX WHERE ISNULL(HotelCustomerNumber, 0) > 0 AND BillNumber NOT IN (SELECT BillNumber FROM Invoice (NOLOCK) WHERE NOT BillNumber is NULL) AND NOT DateTimeStamp IS NULL
	ORDER BY BillNumber, InvoiceNumber 
  SET ANSI_WARNINGS ON
  INSERT INTO Invoice (FkCustomerHotel, FkDepartment, FkStaff, FkBillLayout, BillNumber, InvoiceNumber, CreatedDate, CancelDate, ReceiptType, ReceiptNumber)
    SELECT HotelCustomerNumber, FkDepartment, UserIdNumber, 0, BillNumber, InvoiceNumber, DateTimeStamp, DateTimeStamp, CASE WHEN ISNULL(InvoiceNumber, 0) > 0 THEN 2 ELSE 1 END, 
	  CASE WHEN ISNULL(InvoiceNumber, 0) > 0 THEN InvoiceNumber ELSE BillNumber END 
	FROM CanceledBillNumbers TABLOCKX INNER JOIN Customer (NOLOCK) ON Customer.CustomerNumber = HotelCustomerNumber
	WHERE BillNumber NOT IN (SELECT BillNumber FROM Invoice (NOLOCK) WHERE NOT BillNumber is NULL) ORDER BY BillNumber, InvoiceNumber 
  INSERT INTO ReceiptFiscal (FkInvoice, CashId, ExternalNumber, ManufactIdFCU, CashRegisterMarking)
    SELECT R.PkInvoice, RS.CashId, RS.ExternalNumber, RS.ManufactIdFCU, RS.CashRegisterMarking FROM Invoices RS 
	  INNER JOIN Invoice R on R.BillNumber = RS.BillNumber  
	WHERE NOT RS.CashRegisterMarking IS NULL AND R.PkInvoice NOT IN (SELECT FkInvoice FROM ReceiptFiscal (NOLOCK))
	ORDER BY RS.BillNumber, RS.InvoiceNumber
  SET NOCOUNT ON
  DECLARE @PkInvoice int, @FkCustomerHotel int, @FkDepartment int, @FkStaff int, @BillNumber int, @InvoiceNumber int
  DECLARE @PkFolio int, @FkCustomerHotelFolio int, @FolioBillCount int, @ErrorMsg VARCHAR(100), @IbnExist BIT
  IF EXISTS (SELECT SysColumns.Name 
    FROM SysObjects WITH (NOLOCK) 
      INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID AND SysColumns.Name = 'InvoiceBillNumber'
    WHERE SysObjects.Name = 'Transactions')
    SET @IbnExist = 1
  ELSE
    SET @IbnExist = 0
  DECLARE #InvoiceCur CURSOR FAST_FORWARD READ_ONLY FOR
  SELECT PkInvoice, FkCustomerHotel, FkDepartment, FkStaff, BillNumber, InvoiceNumber FROM Invoice WITH (NOLOCK) WHERE PkInvoice NOT IN (SELECT DISTINCT FkInvoice FROM InvoiceTransactionXTab (NOLOCK))

  OPEN #InvoiceCur
  FETCH NEXT FROM #InvoiceCur
  INTO @PkInvoice, @FkCustomerHotel, @FkDepartment, @FkStaff, @BillNumber, @InvoiceNumber
  WHILE @@FETCH_STATUS = 0 -- safe
  BEGIN
    --Update Invoice.FkCustomerHotel with Folio.HotelCustomerNumber (they have to match) 
	SET @FkCustomerHotelFolio = 0
    SELECT @PkFolio = F.FolioNumber, @FkCustomerHotelFolio = F.HotelCustomerNumber
	FROM FolioBillXTab FBX WITH (NOLOCK) 
	  INNER JOIN FOLIOS F WITH (NOLOCK) ON FBX.FolioNumber = F.FolioNumber
	WHERE FBX.BillNumber = @BillNumber AND FBX.HotelCustomerNumber = @FkCustomerHotel
	IF ISNULL(@FkCustomerHotelFolio, 0) = 0 
	BEGIN
    SET @PkFolio = 0
    SET @FkCustomerHotelFolio = 0
	  SELECT @FolioBillCount = COUNT(*) FROM FolioBillXTab WITH (NOLOCK) WHERE BillNumber = @BillNumber 
      IF @FolioBillCount = 1 --find it by BillNumber only (no risk of hotel conflicts)
        SELECT @PkFolio = F.FolioNumber, @FkCustomerHotelFolio = F.HotelCustomerNumber
	    FROM FolioBillXTab FBX WITH (NOLOCK) 
	      INNER JOIN FOLIOS F WITH (NOLOCK) ON FBX.FolioNumber = F.FolioNumber
	    WHERE FBX.BillNumber = @BillNumber 
	  ELSE IF @IbnExist = 1 AND @FolioBillCount > 1 AND ISNULL(@FkCustomerHotelFolio, 0) = 0
	  BEGIN
	    --find it by recent transaction (on hotel)
  		SELECT TOP 1 @PkFolio = FolioNumber FROM Transactions WITH (NOLOCK) 
	  	WHERE InvoiceBillNumber = @BillNumber AND HotelCustomerNumber = @FkCustomerHotel AND MovedDeleted = 0 AND ShowOnBillflag=1
		  ORDER BY TransactionNumber DESC
  		IF @PkFolio = 0 AND (SELECT Count(*) FROM Invoice WITH (NOLOCK) WHERE BillNumber = @BillNumber) = 1
	  	BEGIN
		    --or if not found on same property and no concurrent bills find it by recent transaction only
  		  SELECT TOP 1 @PkFolio = FolioNumber FROM Transactions WITH (NOLOCK) 
		    WHERE InvoiceBillNumber = @BillNumber AND MovedDeleted = 0 AND ShowOnBillflag=1
  		  ORDER BY TransactionNumber DESC
	  	  IF @PkFolio > 0 --if folio found
   	      SELECT @FkCustomerHotelFolio = HotelCustomerNumber FROM Folios WITH (NOLOCK) WHERE FolioNumber = @PkFolio		
		  --ELSE Ignore this orphant Bill
      END
      ELSE IF @PkFolio > 0 --if folio found
   	    SELECT @FkCustomerHotelFolio = HotelCustomerNumber FROM Folios WITH (NOLOCK) WHERE FolioNumber = @PkFolio		
		  ELSE 
		  BEGIN
		    SET @ErrorMsg = 'Property relation can not be determinated. Cancel Update and inform Development team (@FolioBillCount='+CONVERT(VARCHAR, @FolioBillCount)+'; @BillNumber='+CONVERT(VARCHAR, @BillNumber)  
          RAISERROR ('@FolioBillCount > 1 and Property relation could not be determined either with FolioBill or transaction relation: cancel update and contact Techotel development team!', 15, 1);
		  END
	  END
  END
	IF ISNULL(@FkCustomerHotelFolio, 0) > 0 AND @FkCustomerHotel <> @FkCustomerHotelFolio or @PkFolio = 0 
	BEGIN
      IF @PkFolio <> 0 --if folio found
        UPDATE Invoice SET FkCustomerHotel = @FkCustomerHotelFolio WHERE PkInvoice = @PkInvoice
	  ELSE
        UPDATE Invoice SET CancelDate = CreatedDate WHERE PkInvoice = @PkInvoice --orphant bills are cancelled
	END

    IF @IbnExist = 1 AND @PkFolio <> 0 and @BillNumber <> 0
      INSERT INTO InvoiceTransactionXTab (FkInvoice, FkTransaction)
        SELECT @PkInvoice, TransactionNumber FROM Transactions WITH (NOLOCK) 
        WHERE InvoiceBillNumber = @BillNumber AND FolioNumber = @PkFolio	 

    FETCH NEXT FROM #InvoiceCur
    INTO @PkInvoice, @FkCustomerHotel, @FkDepartment, @FkStaff, @BillNumber, @InvoiceNumber
  END
  CLOSE #InvoiceCur
  DEALLOCATE #InvoiceCur

--  UPDATE DebtorAccounting SET DebtorAccounting.FK_Invoice = (SELECT Invoice.PKInvoice FROM Invoice WITH (NOLOCK) WHERE Invoice.BillNumber = DebtorAccounting.FK_Invoice)
/*
  UPDATE DebtorAccounting 
  SET DebtorAccounting.FK_Invoice = 
  (
    SELECT Invoice.PKInvoice FROM Invoice WITH (NOLOCK) WHERE Invoice.BillNumber = DebtorAccounting.FK_Invoice 
    AND Invoice.BillNumber IN (SELECT Invoices.BillNumber FROM Invoices WITH (NOLOCK))
  )
*/--nb20161101 cannot update with null values fix
  UPDATE DebtorAccounting 
  SET DebtorAccounting.FK_Invoice = 
  (
    SELECT Invoice.PKInvoice FROM Invoice WITH (NOLOCK) WHERE Invoice.BillNumber = DebtorAccounting.FK_Invoice 
    AND Invoice.BillNumber IN (SELECT Invoices.BillNumber FROM Invoices WITH (NOLOCK))
  )
  WHERE NOT DebtorAccounting.FK_Invoice IS NULL
  
  SET NOCOUNT OFF
END
/*
  SELECT @RecCount = Count(*) FROM InvoiceTransactionXTab WITH (NOLOCK)
PRINT 'InvoiceTransactionXTab: '+CONVERT(VARCHAR, @RecCount)
  SELECT @RecCount = Count(*) FROM Invoice WITH (NOLOCK) WHERE CancelDate IS NULL
PRINT 'Invoice: '+CONVERT(VARCHAR, @RecCount)
  SELECT @RecCount = Count(*) FROM Invoice WITH (NOLOCK) WHERE NOT CancelDate IS NULL
PRINT 'InvoiceCancelled: '+CONVERT(VARCHAR, @RecCount)
  SELECT @RecCount = Count(*) FROM DebtorAccounting WITH (NOLOCK)
PRINT 'DebtorAccounting: '+CONVERT(VARCHAR, @RecCount)
*/
/*
  rollback tran
  commit tran
*/
