ALTER PROCEDURE qryMMS_HD_FRLRefresh_Execute

	@EntityID BIGINT,

	@SelectedDate DATETIME

AS



BEGIN





DECLARE @storeNumbers	VARCHAR(MAX)	= 'CH001,CH002,CH003,CH004,CH005,CH006,CH007,CH008,CH009,CH010,CH011,CH012,CH013,CH014,CH015,CH016,CH017,CH018,CH019,CH020,CH021,CH022,CH023,CH024,CH025,CH026,CH027,CH028,CH029,CH030,CH031,CH032'

DECLARE @GODate			DATETIME		= @SelectedDate

DECLARE @StartingFLoat  MONEY			= 2500

DECLARE @MyEntityID		BIGINT



DECLARE entityCursor CURSOR FOR

SELECT EntityID FROM tbEntity WITH (NOLOCK)

WHERE	StoreNumber in ( Select Value FROM fn_Split(@StoreNumbers, ','))

		

ORDER BY StoreNumber



	OPEN entityCursor



	FETCH NEXT FROM entityCursor

				INTO @MyEntityID



	WHILE @@FETCH_STATUS = 0

	BEGIN



		INSERT INTO tbBankingClose

           ([EntityID]

           ,[BankingDate]

           ,[TransactionDate]

           ,[PeriodNo]

           ,[FiscalYear]

           ,[RegisterSales]

           ,[Voids]

           ,[PromoFree]

           ,[Discounts]

           ,[NetSalesWithTax]

           ,[NetSales]

           ,[Tax]

           ,[Eftpos]

           ,[DepositAM]

           ,[DepositPM]

           ,[Withhold]

           ,[Rebank]

           ,[CashVariance]

           ,[TotalTransactions]

           ,[Authorised]

           ,[AuthorisedByUsername]

           ,[DepositPMActual]

           ,[CashDrops]

           ,[PettyCash]

           ,[SpecialDeposits]

           ,[Comments]

           ,[Vouchers]

           ,[Cheques]

           ,[Catering]

           ,[CashOut]

           ,[VoucherCheques]

           ,[RegisterFloatMovement]

           ,[SafeFloatMovement]

           ,[ChangeReceive]

           ,[OpeningRegisterFloatVariance]

           ,[ClosingRegisterFloatVariance]

           ,[SafeFloatVariance]

           ,[BankingVariance]

           ,[CashupDeposit]

           ,[CashupPreviousDeposit]

           ,[LoyaltyCard]

           ,[NonSales]

           ,[PaidIn]

           ,[GiftCertificateSold]

           ,[GiftCertificateRedeemed]

           ,[CharityDonation]

           ,[CouponOverShort])

		select @MyEntityID

			  ,d.DayDate

			  ,DATEADD(HOUR, 23, d.DayDate) 

			  ,d.PeriodNo

			  ,d.sFiscalYear

			  ,0 --[RegisterSales]

			  ,0

			  ,0

			  ,0

			  ,0 --[NetSalesWithTax]

			  ,0 --[NetSales]

			  ,0 --[Tax]

			  ,0

			  ,0

			  ,0

			  ,0

			  ,0

			  ,0

			  ,0

			  ,1

			  ,'CMOperator'

			  ,0

			  ,0

			  ,0

			  ,0

			  ,'Finalized' --[Comments]

			  ,0

			  ,0

			  ,0

			  ,0

			  ,0

			  ,0

			  ,0

			  ,0

			  ,0

			  ,0

			  ,0

			  ,0

			  ,0

			  ,0

			  ,0

			  ,0

			  ,0

			  ,0

			  ,0

			  ,0

			  ,0

		from tbPeriodDay d

		where d.DayDate < @GODate

		and d.DayDate >= '2012-01-01 00:00:00.00'

		and d.DayDate not in (select BankingDate from tbBankingClose where EntityID = @MyEntityID)



		order by d.DayDate desc



		UPDATE tbBankingClose

		set Authorised = 1, AuthorisedByUsername = 'CMOperator', Comments = 'Finalized', TransactionDate = DATEADD(HOUR, 23, BankingDate)

		WHERE EntityID = @MyEntityID

		and BankingDate < @GODate

		and Authorised <> 1



		DECLARE @PreviousDay DATETIME = (select TOP 1 DayDate FROM tbPeriodDay WITH (NOLOCK) WHERE DayDate < @GODate ORDER BY DayDate DESC)

		DECLARE @PrevDayOpen DATETIME 

		DECLARE @PrevDayClose DATETIME 

		

		IF DATENAME(WEEKDAY, @PreviousDay) = 'Sunday'

		BEGIN

			SET @PreviousDay = DATEADD(DD,-1, @PreviousDay)

			SET @PrevDayOpen = DATEADD(HH, 2, @PreviousDay)

			SET @PrevDayClose = DATEADD(HH, 23, @PreviousDay)

		END

		

		SELECT	@PrevDayOpen = CASE DayOpen WHEN '1753-01-01' THEN DATEADD(HH, 2, @PreviousDay) ELSE DayOpen END,

				@PrevDayClose = CASE DayClose WHEN '1753-01-01' THEN DATEADD(HH, 23, @PreviousDay) ELSE DayClose END

		FROM fn_GetTradingForCalendarDay(@MyEntityID, @PreviousDay) d



		IF (Exists(SELECT * FROM tbBankingFloatCount WITH (NOLOCK) WHERE FloatDate = @PreviousDay AND BankingTypeID = 3))

		BEGIN

			delete	from tbTransactionCashCount

			where entityID = @MyEntityID

			and TransactioNCashCountID in

			(

				select ReferenceID from tbFloatMovement with (nolock)

				where EntityID = @MyEntityID

				and FloatMovementDate = @PreviousDay

				and FloatMovementTypeID = 3

			)

			Print '-- Deleted Cash Counts --'

			delete	from tbBankingFloatCount where entityID = @MyEntityID and FloatDate = @PreviousDay and BankingTypeID = 3

			Print '-- Deleted Float Counts --'

			delete  from tbFloatMovement where EntityID = @MyEntityID and FloatMovementDate = @PreviousDay and FLoatMovementTypeID = 3

			Print '-- Deleted Float Movement --'

		END

		

		INSERT INTO tbTransactionCashCount (EntityID, Total, AmountToCompare, Variance, Registers, Custom1, Custom2, Custom3, Custom4, Custom5, Custom6, Custom7, Custom8, ExcludeTotalCustom1, CompareAmountDetail1, CompareAmountDetail2, CompareAmountDetail3, TimeStamp, ClerkName,BusinessDay)

		VALUES (@MyEntityID, @StartingFloat, @StartingFloat, 0, 0, @StartingFloat, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, DATEADD(SS, 1, @PrevDayClose), 'CMOperator', @PreviousDay)



		DECLARE @CashCountID BIGINT = (Select top 1 TransactionCashCountID from tbTransactionCashCount with (nolock) where EntityID = @MyEntityID and TimeStamp = DATEADD(SS, 1, @PrevDayClose))



		INSERT INTO tbBankingFloatCount (EntityID, FloatDate, TimeStamp, Float, Total, Variance, FloatCountRefID, AuthorisedBy1, Authorised, BankingTypeID)

		VALUES (@MyEntityID, @PreviousDay, DATEADD(SS, 1, @PrevDayClose), @StartingFloat, @StartingFloat, 0, @CashCountID, 'CMOperator',1,3)



		INSERT INTO tbFloatMovement (EntityID, FloatMovementTypeID, StoreTimestamp, Amount, ExpectedAmount, RunningTotal, ManagerID, Variance, ReferenceID, FloatMovementDate)

		VALUES (@MyEntityID, 3, DATEADD(SS, 1, @PrevDayClose), @StartingFloat, @StartingFloat, @StartingFloat, 1, 0, @CashCountID, @PreviousDay)



		Update tbEntityFloat set DefaultStoreFloat = @StartingFloat where EntityId = @MyEntityID

		

		-- Delete Any Prior Workflow Instances

		delete p from tbWorkFlowTemplateInstance i

		JOIN tbWorkflowpendingtasks p 

		on i.InstanceID = p.InstanceID

		where i.EntityID = @MyEntityID



		delete p from tbWorkFlowTemplateInstance i

		JOIN tbWorkFlowTasksStatus p 

		on i.InstanceID = p.InstanceID

		where i.EntityID = @MyEntityID



		delete from tbWorkFlowTemplateInstance where EntityID = @MyEntityID



		-- Add Store to the Workflow Template

		IF (NOT EXISTS(SELECT * FROM tbWorkFlowTemplateEntity WHERE EntityID = @MyEntityID AND TemplateID = 1))

		BEGIN

			INSERT INTO tbWorkFlowTemplateEntity (EntityID, TemplateID)

			VALUES (@MyEntityID, 1)

		END



		-- Insert Prior Business Day's Workflow Instance as Completed

		INSERT INTO tbWorkFlowTemplateInstance (TemplateID, EntityID, UserID, StartDate, EndDate, Description, StatusID, Override, PreviousInstanceStatusID, BusinessDay)

		values (1, @MyEntityID, 4, DATEADD(SS, 1, @PrevDayOpen), DATEADD(SS, -1, @PrevDayClose),'Daily Work Flow ' + CONVERT(VARCHAR(10), @PreviousDay, 101), 4, 0, 4, @PreviousDay)



		FETCH NEXT FROM entityCursor

					INTO @MyEntityID

	END



	CLOSE entityCursor

	DEALLOCATE entityCursor



END

