/*
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))) > 0
BEGIN
  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, 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)) ORDER BY BillNumber, InvoiceNumber 
  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)) 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)
  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 @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 @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))
  )
  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
*/
