SET NOCOUNT on
go


DECLARE @exists as varchar(300)
DECLARE @result int
DECLARE @failedstores as int
DECLARE @date as varchar(100) 
DECLARE @sql as varchar(200)
DECLARE @storeno as smallint
DECLARE @tdate as char(3)
DECLARE @errordescrip as varchar(100)
DECLARE @rowc as int
DECLARE storecursor2 CURSOR FOR 
	Select store_no From
	retail2 Where store_no in (SELECT storenum FROM RetailChoiseStore)
DECLARE daycursor CURSOR FOR 
	Select tdate From
	adhoctaurex 
DECLARE @textdate as varchar(200)


--drop and re-create items tables
DROP TABLE dbo.tmpisaleimport
CREATE TABLE dbo.tmpisaleimport ([salesdata] [varchar] (1000)
				 	)
DROP TABLE dbo.itemsales_stores_input_error
CREATE TABLE [itemsales_stores_input_error] (
	[StoreNumber] [smallint] NULL ,
	[Description] varchar(200),
        [DateInserted] [datetime] NULL CONSTRAINT [DF__itemsales__Date66I2__43F17C88] DEFAULT (getdate())
) ON [PRIMARY]

--drop and re-create offer tables
DROP TABLE dbo.tmpoffersaleimport 
CREATE TABLE dbo.tmpoffersaleimport ([salesdata] [varchar] (1000)
				      	)
				
DROP TABLE dbo.offersales_stores_input_error
CREATE TABLE dbo.[offersales_stores_input_error] (
	[ErrorDescription] varchar(200) NULL ,
	[DateInserted] [datetime] NULL CONSTRAINT [DF__offersales__Date66I2__43F17C88] DEFAULT (getdate())
) ON [PRIMARY]


OPEN daycursor
FETCH NEXT FROM daycursor INTO @tdate
WHILE @@FETCH_STATUS = 0 
BEGIN

OPEN storecursor2
FETCH NEXT FROM storecursor2 INTO @storeno
WHILE @@FETCH_STATUS = 0 
BEGIN
	
	SELECT @exists = '"' + 'DIR /B \\jy2003smem012\Data\Iris6\Data\HOSTIN\Itemsale\isale'+ cast(@storeno as char(3))+'.'+@tdate+'"'
	EXEC @result = master..xp_cmdshell @exists , no_output
	-- if it does then bulk insert it into the tmpisaleimport table
	IF (@result = 0)
       begin
	
		select @date =''''+ '\\jy2003smem012\Data\Iris6\Data\HOSTIN\Itemsale\isale'+ cast(@storeno as char(3))+'.'+@tdate+''''
		Set @sql='
		BULK INSERT [tmpisaleimport]
		FROM '+@date		
		Exec(@sql)
	       IF @@rowcount = 0         
	   		Begin
            			INSERT INTO itemsales_stores_input_error(StoreNumber, [Description])
	 		  	 VALUES (@storeno,'File does exist but has no data')
         	        End
       end
       ELSE
       -- if it doesn't plug it into the the error table which shows all failed store imports
       begin
	INSERT INTO itemsales_stores_input_error(StoreNumber, [Description])
	VALUES (@storeno,'File does not exist')
       end
	
FETCH NEXT FROM storecursor2 INTO @storeno
END
CLOSE storecursor2



	
	--find if the current offersale file exists in the itemsale folder 
	select @exists = '"' + 'DIR /B \\jy2003smem012\Data\Iris6\Data\HOSTOUT\OffrSale.'+@tdate+'"'
	EXEC @result = master..xp_cmdshell @exists 
	-- if it does then bulk insert it into the tmpoffersaleimport table
	IF (@result = 0)
       begin
	select @date =''''+ '\\jy2003smem012\Data\Iris6\Data\HOSTOUT\OffrSale.'+@tdate+''''
	Set @sql='
	BULK INSERT [tmpoffersaleimport]
	FROM '+@date		
	Exec(@sql)
       end
	ELSE
       -- if it doesn't plug it into the the error table which shows all failed store imports
       begin
	set @errordescrip = 'The offer sale file did not exist for the date specified'
	INSERT INTO offersales_stores_input_error(ErrorDescription)
	VALUES (@errordescrip)
	set @rowc = @@rowcount       
       end

IF @rowc > 0
	begin
		SELECT @textdate = 'The offersales file for ' +@tdate+ 'did not get imported please check offersales_stores_input_error table to check the time of failure'
		RAISERROR (@textdate,16,1)
	end
		

	exec checkitemsales

	TRUNCATE TABLE tmpisaleimport
	TRUNCATE TABLE tmpoffersaleimport 

FETCH NEXT FROM daycursor INTO @tdate
END


DEALLOCATE storecursor2 

CLOSE daycursor
DEALLOCATE daycursor