CREATE PROCEDURE [dbo].[GetReceipt] @PkFolio INT, @PkReceipt INT, @Aproved BIT, @PkDepartment INT, @AnnexTransIdCSV VARCHAR(1000)  AS
SET NOCOUNT ON
/*
nbl20221118: Created
Description: join get receipt info in one common place
@AnnexTransIdCSV
-NULL: No Annex
-0: Annex included
-csv: list transaction related annexes only
--debug header
DECLARE @PkReceipt INT, @pkFolio INT, @PkDepartment INT, @Aproved BIT, @AnnexTransIdCSV VARCHAR(1000)
SET @PkReceipt =  NULL 
SET @pkFolio =  78332 
SET @PkDepartment =  NULL 
SET @Aproved =  NULL 
SET @AnnexTransIdCSV =  NULL 

*/
CREATE TABLE dbo.#ReceiptInfo (PkReceipt INT, PkReceiptMaster INT, IsAnnex BIT, FolioType INT)
IF NOT @AnnexTransIdCSV IS NULL AND @AnnexTransIdCSV <> '0'
  INSERT INTO #ReceiptInfo
  SELECT DISTINCT ITX.FkInvoice, ITX.FkInvoice, 1, 0
  FROM dbo.TransactionTransactionXtab TX (NOLOCK)
    JOIN dbo.Transactions T (NOLOCK) ON T.TransactionNumber = TX.FK_TransactionNumber1
    JOIN dbo.InvoiceTransactionXtab ITX (NOLOCK) ON ITX.FkTransaction = T.TransactionNumber
  WHERE TX.FK_TransactionNumber2 IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AnnexTransIdCSV)) AND TX.RelationType = 2
ELSE
BEGIN
  IF ISNULL(@PkReceipt, 0) = 0
    INSERT INTO #ReceiptInfo
    SELECT DISTINCT ITX.FkInvoice AS PkReceipt, ITX.FkInvoice AS PkReceiptMaster, 0 AS IsAnnex, 0 AS FolioType
    FROM InvoiceTransactionXTab ITX (NOLOCK)
      INNER JOIN Transactions T (NOLOCK) ON ITX.FkTransaction = T.TransactionNumber
      INNER JOIN Invoice I (NOLOCK) ON ITX.FkInvoice = I.PkInvoice AND I.ReceiptType <> 0 AND (@Aproved IS NULL OR @Aproved = 1 AND I.CancelDate IS NULL OR @Aproved = 0 AND NOT I.CancelDate IS NULL)
    WHERE T.FolioNumber = @PkFolio
  ELSE IF ISNULL(@PkFolio, 0) = 0 OR EXISTS(
    SELECT * FROM InvoiceTransactionXTab ITX (NOLOCK) 
      JOIN Transactions T (NOLOCK) ON ITX.FkTransaction = T.TransactionNumber AND T.FolioNumber = @PkFolio
    WHERE FkInvoice = @PkReceipt)
    INSERT INTO #ReceiptInfo
    SELECT I.PkInvoice AS PkReceipt, I.PkInvoice AS PkReceiptMaster, 0 AS IsAnnex, 0 AS FolioType
    FROM Invoice I (NOLOCK) 
    WHERE I.PkInvoice = @PkReceipt AND (@Aproved IS NULL OR @Aproved = 1 AND I.CancelDate IS NULL OR @Aproved = 0 AND NOT I.CancelDate IS NULL)
  IF @AnnexTransIdCSV = '0'
  BEGIN
    ;WITH RelevantCTE (TransactionNumber, FolioNumber) AS (
    SELECT T.TransactionNumber, T.FolioNumber
    FROM Transactions T (NOLOCK)
    WHERE T.FolioNumber = @PkFolio and ISNULL(@PkReceipt, 0) <= 0 
    EXCEPT --Settled when Prebill
    SELECT T.Transactionnumber, T.FolioNumber
    FROM dbo.Transactions T WITH (NOLOCK) 
      JOIN dbo.InvoiceTransactionXtab ITX WITH(NOLOCK) ON ITX.FkTransaction = T.TransactionNumber
      JOIN dbo.Invoice I WITH(NOLOCK) ON ITX.FkInvoice = I.PkInvoice AND I.ReceiptType <> 0 AND NOT I.PkInvoice IS NULL AND I.CancelDate IS NULL
    WHERE T.FolioNumber = @PkFolio AND (@PkReceipt = -1 AND ITX.FkInvoice = @PkReceipt OR @PkReceipt = 0 AND (NOT I.PkInvoice IS NULL AND I.CancelDate IS NULL OR I.ReceiptType = 0))
	)
    INSERT INTO #ReceiptInfo
    SELECT DISTINCT ITXA.FkInvoice, @PkReceipt, CAST(1 AS BIT) AS IsAnnex
    , CASE
      when ISNULL(FRX.FolioNumber, 0) > 0 THEN 5 --ftTable
      when ISNULL(F.ReferenceNumber, 0) = 0 and ISNULL(F.ReservationAddressNumber , 0) = 0 THEN 1 --ftSales
      when ISNULL(F.ReferenceNumber, 0) = 0 and ISNULL(F.ReservationAddressNumber , 0) > 0 THEN 2 --ftInvoice
      when ISNULL(F.ReferenceNumber, 0) > 0 and ISNULL(F.FolioNumber, 0) > 0 THEN 3 --ftRoom/Reservation
    END --AS FolioType
	FROM RelevantCTE T 
      JOIN TransactionTransactionXTab TTX (NOLOCK) ON TTX.FK_TransactionNumber2 = T.TransactionNumber
      JOIN InvoiceTransactionXTab ITXA (NOLOCK) ON ITXA.FkTransaction = TTX.FK_TransactionNumber1
      JOIN Transactions TA (NOLOCK) ON ITXA.FkTransaction = TA.TransactionNumber
      JOIN Folios F (NOLocK) on TA.FolioNumber = F.FolioNumber
      JOIN Invoice IA (NOLOCK) ON ITXA.FkInvoice = IA.PkInvoice AND IA.CancelDate IS NULL AND IA.ReceiptType <> 0
      LEFT JOIN dbo.FolioRestResTableChairsXTab FRX with (nolock) on FRX.FolioNumber = F.FolioNumber
    WHERE T.FolioNumber = @PkFolio and ISNULL(@PkReceipt, 0) <= 0 --settlement and Prebill

	UNION
    SELECT ITXA.FkInvoice, ITX.FkInvoice, CAST(1 AS BIT) AS IsAnnex
    , CASE
      when ISNULL(FRX.FolioNumber, 0) > 0 THEN 5 --ftTable
      when ISNULL(F.ReferenceNumber, 0) = 0 and ISNULL(F.ReservationAddressNumber , 0) = 0 THEN 1 --ftSales
      when ISNULL(F.ReferenceNumber, 0) = 0 and ISNULL(F.ReservationAddressNumber , 0) > 0 THEN 2 --ftInvoice
      when ISNULL(F.ReferenceNumber, 0) > 0 and ISNULL(F.FolioNumber, 0) > 0 THEN 3 --ftRoom/Reservation
    END --AS FolioType
    FROM InvoiceTransactionXTab ITX (NOLOCK)
      JOIN Transactions T (NOLOCK) ON ITX.FkTransaction = T.TransactionNumber
      JOIN TransactionTransactionXTab TTX (NOLOCK) ON TTX.FK_TransactionNumber2 = ITX.FkTransaction
      JOIN InvoiceTransactionXTab ITXA (NOLOCK) ON ITXA.FkTransaction = TTX.FK_TransactionNumber1
      JOIN Transactions TA (NOLOCK) ON ITXA.FkTransaction = TA.TransactionNumber
      JOIN Folios F (NOLocK) on TA.FolioNumber = F.FolioNumber
      JOIN Invoice IA (NOLOCK) ON ITXA.FkInvoice = IA.PkInvoice AND IA.CancelDate IS NULL AND IA.ReceiptType <> 0
      --JOIN Invoice IDepricated (NOLOCK) ON ITX.FkInvoice = I.PkInvoice AND I.ReceiptType <> 0 AND (@Aproved IS NULL OR @Aproved = 1 AND I.CancelDate IS NULL OR @Aproved = 0 AND NOT I.CancelDate IS NULL) --perhaps this line not necessary
      LEFT JOIN dbo.FolioRestResTableChairsXTab FRX with (nolock) on FRX.FolioNumber = F.FolioNumber
    WHERE ITX.FkInvoice = @PkReceipt
  END
END

DECLARE @Bit BIT, @int INT
SELECT @int = CAST(dbo.GetHotelSetupKeyValue('CentralBookingType', -1) AS INT)
IF @int <> 7
  SELECT @Bit =  CAST(dbo.GetHotelSetupKeyValue('SeparateEconomyByStaffDep', -1) AS BIT)
SET @Bit = ISNULL(@Bit,0)
IF @Bit = 1 
BEGIN --handle department series
  SELECT @int = COUNT(*) FROM IdentityFields WHERE DBTableName = 'BILLIDENTITY' AND FkDepartment > 0
  IF @int > 0
  BEGIN
    SELECT @int = COUNT(*) FROM IdentityFields WHERE DBTableName = 'BILLIDENTITY' AND FkDepartment = @PkDepartment
	IF @int > 0 
      DELETE #ReceiptInfo WHERE PkReceipt in (
        SELECT PkInvoice 
	    FROM #ReceiptInfo RI
	      JOIN dbo.Invoice I (NOLOCK) ON I.PkInvoice = RI.PkReceipt AND I.FkDepartment <> @PkDepartment)
	ELSE
      DELETE #ReceiptInfo WHERE PkReceipt in (
        SELECT PkInvoice 
	    FROM #ReceiptInfo RI
	      JOIN dbo.Invoice I (NOLOCK) ON I.PkInvoice = RI.PkReceipt 
		  JOIN dbo.IdentityFields IFS (NOLOCK) ON IFS.FkDepartment = I.FkDepartment)
  END
END

SELECT RI.IsAnnex, RI.PkReceiptMaster AS PkInvoiceMaster, RI.FolioType, I.*
, RF.CashId, RF.ExternalNumber, RF.ManufactIdFCU, RF.CashRegisterMarking
FROM #ReceiptInfo  RI
  JOIN Invoice I (NOLOCK) ON RI.PkReceipt = I.PkInvoice
  LEFT JOIN ReceiptFiscal RF WITH (NOLOCK) ON RF.FkInvoice = I.PkInvoice
ORDER BY RI.PkReceiptMaster, RI.IsAnnex, I.PkInvoice

DROP TABLE #ReceiptInfo