SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MX_OutsideSalesNameFix_Workaround]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MX_OutsideSalesNameFix_Workaround]
GO

-- ==========================================================================================
-- Description:	This will check whether the specified configuration is enabled for a single 
-- user or enabled for all stores linked to a user
-- ==========================================================================================
CREATE PROCEDURE MX_OutsideSalesNameFix_Workaround
	@EntityId BIGINT,
	@StartDate DATE
AS
BEGIN

CREATE TABLE #CustomerSale_Data(
		TransactionSummaryID BIGINT, 
		TransactionDate DATETIME,
		EntityID BIGINT, 
		BusinessDay DATETIME, 
		POSTransactionID BIGINT, 
		Destination VARCHAR(50), 
		GrossSales MONEY, 
		GSTAmount MONEY, 
		TaxExempt BIT, 
		Cashier VARCHAR(MAX), 
		ShiftNumber BIGINT, 
		RefundPOSId BIGINT, 
		PaymentAmount MONEY,
		OnAccountSaleId BIGINT, 
		CustomerID BIGINT, 
		InvoiceNumber VARCHAR(MAX), 
		[Description] VARCHAR(MAX), 
		OnAccountSaleType INT, 
		Paid BIT, 
		UncollectedFlag BIT
	)
    
 DECLARE @TBCPaymentTypeId BIGINT =   
  (SELECT PaymentTypeID  
  FROM tbPaymentType WITH (NOLOCK)  
  WHERE [Description] = 'ToBeCollected')    

 DECLARE @Refunds TABLE (ControlInfoID bigint, SystemTransactionID bigint, OtherInfo3 bigint, POSTransactionID bigint)  
 INSERT INTO @Refunds  
 SELECT TCI.ControlInfoID, TCI.SystemTransactionID, TCI.OtherInfo3, TCI.POSTransactionID  
 FROM tbTransactionControlInfo TCI WITH (NOLOCK)  
 JOIN tbControlInfo CI WITH (NOLOCK) ON CI.ControlInfoID = TCI.ControlInfoID  
  AND CI.ControlInfo = 'RetrieveandRefund' and CI.ControlInfoSystemCode = 'refunds'  
 WHERE TCI.EntityID = @EntityId  
   AND TCI.BusinessDay = @StartDate  
  
 INSERT INTO #CustomerSale_Data(
        TransactionSummaryID, 
		TransactionDate,
		EntityID , 
		BusinessDay , 
		POSTransactionID , 
		Destination , 
		GrossSales , 
		GSTAmount , 
		TaxExempt , 
		Cashier , 
		ShiftNumber , 
		RefundPOSId , 
		PaymentAmount ,
		OnAccountSaleId , 
		CustomerID , 
		InvoiceNumber, 
		[Description] , 
		OnAccountSaleType , 
		Paid , 
		UncollectedFlag  )
 SELECT   
  TS.TransactionSummaryID,  
  TS.TransactionDate,  
  TS.EntityID,
  TS.BusinessDay,  
  TS.POSTransactionID,  
  CASE WHEN TS.InOutDel = 2 THEN 'Outside' ELSE 'Inside' END,  
  CAST((TS.Sales_Incl_GST_EXCL + ISNULL(TS.Rounding,0)) AS MONEY),  
  TS.GSTAmount,  
  TS.TaxExempt,  
  TS.ClerkIDName,  
  TS.CurrentShiftNumber,  
  CI.POSTransactionID,  
  payments.TotalAmount,  
    
  OA.OnAccountSaleId,   
  OA.CustomerID,  
  OA.InvoiceNumber,   
  OA.[Description],   
  OA.[Type],  
  OA.Paid,  
  OA.UncollectedFlag  
  
 FROM tbTransactionSummary TS WITH (NOLOCK)  
 LEFT JOIN tbOnAccountSale OA WITH (NOLOCK)  
  ON OA.TransactionSummaryID = TS.TransactionSummaryID AND OA.EntityId = TS.EntityID  
 LEFT JOIN @Refunds CI ON (CI.SystemTransactionID = TS.TransactionSummaryID OR CI.OtherInfo3 = TS.POSTransactionID)  
 LEFT OUTER JOIN (SELECT TransactionSummaryID, COUNT(*) TotalCount, SUM(CASE WHEN TP.PaymentTypeId = @TBCPaymentTypeId THEN 1 ELSE 0 END) TBCCount, SUM(Amount) TotalAmount  
     FROM tbTransactionPayment TP WITH (NOLOCK) GROUP BY TransactionSummaryID) payments ON payments.TransactionSummaryID = TS.TransactionSummaryID  
 WHERE   
  TS.EntityID = @EntityId   
  AND TS.BusinessDay = @StartDate  
  AND COALESCE(TS.TransactionVersion,0) = 0   
  AND COALESCE(OA.ReversalFlag, 0) = 0   
  AND (  
   (TS.InOutDel = 2 AND payments.TBCCount = 0 AND payments.TotalCount > 0)   
  ) 
  AND  OA.OnAccountSaleId is null and CI.POSTransactionID is null
  
  DECLARE @Customerid bigint
  SELECT @Customerid = CustomerInformationID from CustomerInformation  with (nolock) where entityid = @EntityId and CustomerName = 'Systematic Cust - Outside Sls'
    
DECLARE @id bigint, @tranid as bigint

DECLARE cur CURSOR FOR SELECT EntityID, Transactionsummaryid
	FROM #CustomerSale_Data  
	OPEN cur

	FETCH NEXT FROM cur INTO @id, @tranid
	
	WHILE @@FETCH_STATUS = 0 
	BEGIN 
	Insert into tbOnAccountSale(TransactionSummaryID, EntityId, CustomerId, UncollectedFlag, Paid, Type,PaidInFullTimestamp, ReversalFlag)
	values (@tranid, @id, @Customerid, 0,1,2,GETDATE(), 0)
	
FETCH NEXT FROM cur INTO @id, @tranid
END
CLOSE cur
DEALLOCATE cur	

SELECT * from #CustomerSale_Data
drop table #CustomerSale_Data

END
GO