-- Date to run and how many shifts

SET NOCOUNT ON
DECLARE @Date datetime = '2018/08/14' -- '2018-7-20'
DECLARE @NumberOfShifts int = 5
------------------------------------------

DECLARE @ClerkID BIGINT 
DECLARE @ClerkName VARCHAR(150)
DECLARE @TransactionCashCountID BIGINT
DECLARE @POSShiftID BIGINT
DECLARE @CurrentDate datetime = '2018/08/14'


IF object_id('tempdb..#temp_PeriodDetails') IS NOT NULL  
DROP TABLE [dbo].[#temp_PeriodDetails]  
  
CREATE TABLE #temp_PeriodDetails (  
 sFiscalYear varchar(5),  
 iPeriodBegin_dt datetime,  
 iPeriodEnd_dt datetime,  
 PeriodNo int,  
 SequenceCode bigint,
 MonthNo int, 
 [MonthName] varchar(10),
  sPeriod_nam varchar(50)

)  
  
INSERT INTO #temp_PeriodDetails EXECUTE qryMMSPeriodDetail_GetPeriodForDate @Date  
DECLARE @FiscalYear VARCHAR(50)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @PeriodNo INT

SELECT
	@FiscalYear = sFiscalYear
,	@StartDate = iPeriodBegin_dt
,	@EndDate = iPeriodEnd_dt
,	@PeriodNo  = PeriodNo
FROM
	#temp_PeriodDetails


DECLARE @ShiftStartDate DATETIME = DATEADD(hour,6,@Date)
DECLARE @ShiftEndDate DATETIME = DATEADD(hour,18,@Date)



IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tmp].[loadTestData]') AND [type] IN (N'U'))
BEGIN


IF OBJECT_ID('TEMPDB..#TRANSACTIONCASHCOUNTID') IS NOT NULL  
DROP TABLE [DBO].[#TRANSACTIONCASHCOUNTID]  
  
CREATE TABLE #TRANSACTIONCASHCOUNTID (  
 TRANSACTIONCASHCOUNTID BIGINT
)  

INSERT INTO #TRANSACTIONCASHCOUNTID SELECT TRANSACTIONCASHCOUNTID FROM TBTRANSACTIONCASHCOUNT WHERE CLERKNAME LIKE '%LOAD, CASHIER%' AND BUSINESSDAY = @DATE AND ENTITYID  IN (SELECT ENTITYID FROM TMP.LOADTESTDATA )

	DELETE FROM TBMXSHIFT WHERE CLERKNAME LIKE '%LOAD, CASHIER%' AND BUSINESSDAY = @DATE AND ENTITYID IN (SELECT ENTITYID FROM TMP.LOADTESTDATA )
	DELETE FROM TBTRANSACTIONCASHDEPOSIT WHERE REGISTERCOUNTREFID IN (SELECT TRANSACTIONCASHCOUNTID FROM #TRANSACTIONCASHCOUNTID)
	--DELETE FROM TBTRANSACTIONCASHCOUNT WHERE TRANSACTIONCASHCOUNTID IN (SELECT TRANSACTIONCASHCOUNTID FROM #TRANSACTIONCASHCOUNTID)

	DECLARE store_cursor CURSOR FOR  
    select EntityID from tmp.loadTestData 
		
	DECLARE @EntityId BIGINT
	
	OPEN store_cursor
	
	FETCH NEXT FROM store_cursor INTO @EntityId
	WHILE @@FETCH_STATUS = 0  
	BEGIN  
	

    DECLARE @counter int
	SET @counter = 1
	WHILE @counter <= @NumberOfShifts BEGIN
		set @ClerkName = 'Load, Cashier'+ cast(@counter as varchar)
        set @ClerkID = cast(cast(@EntityId as varchar(50)) + cast(@counter as varchar(2)) as int);
     
		DELETE FROM tbTransactionCashDeposit WHERE EntityId = @EntityId AND OperatorName = @ClerkName AND EntityTypeID = 4 AND FiscalYear = @FiscalYear AND StartDate = @StartDate AND EndDate = @EndDate AND CashDepositDate = @Date
		DELETE FROM tbMXShift WHERE EntityID = @EntityId AND BusinessDay = @Date AND ClerkID = @ClerkID
		
		insert into tbMXShift values(NULL,0,1,@ShiftStartDate,@ShiftEndDate,'Cashier Open','',0,0,'POS','',1,@ClerkID,@ClerkName,@entityId,@Date)
		SET @POSShiftID = SCOPE_IDENTITY()

		insert into tbTransactionCashCount values(@entityId,0,0,0,0,0,0,@Date,@ClerkName,0,0,0,0.00,0.00,0.00,0.00,0.00,NULL,0,0.00,0.00,0.00,0.00,0.00,@Date)
		SET @TransactionCashCountID = SCOPE_IDENTITY()
		insert into tbTransactionCashDeposit values(@entityId,4,3100,@FiscalYear,@StartDate,@EndDate,@PeriodNo,@Date,@ClerkName,NULL,0,0,0,0,0,0,0,0,0,0,'Cashier Close',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
		@CurrentDate,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,@ClerkName,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,@ClerkID,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,0,NULL,NULL,NULL,
		0,0,0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,0,NULL,NULL,NULL,@ShiftStartDate,@ShiftEndDate,@TransactionCashCountID,NULL,NULL,NULL,0,-1,NULL,@ClerkName,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
		NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,@POSShiftID,NULL,NULL,0.00,0.00,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,0.00,NULL,NULL,NULL,NULL,NULL,0.00,0.00,0.00,0.00,1)

     
     SET @counter = @counter + 1
	END

				
	FETCH NEXT FROM store_cursor INTO @EntityId
	END

	CLOSE store_cursor
	DEALLOCATE store_cursor  
	
END
GO