
DECLARE @dateCreated DATE, @recId int;

CREATE TABLE #excelTable (
	[id] [int] NOT NULL,
	[excelEmail] [nvarchar](max) NULL,
	[excelDate] [nvarchar](max) NULL,
)

DECLARE @id int, @excelEmail nvarchar(255), @excelDate datetime;
SET rowcount 0

--RENAME FILE PATH
BULK INSERT #excelTable
FROM 'C:\Users\Branimir-simpleTask\Documents\EvolutionSoft\tickets\CLOS-1\CLOS-11\First_ten_test.csv'
WITH
(
  FIRSTROW = 2,
  DATAFILETYPE='widechar', -- UTF-16
  FIELDTERMINATOR = ';',
  ROWTERMINATOR = '\n',
  TABLOCK,
  KEEPNULLS
)

CREATE TABLE #user(
	[recId] [int] NOT NULL,
	[email] [varchar](500) NULL,
	[dateCreated] [datetime] NULL,
)

SET rowcount 1
select @id = id,
@excelEmail = LTRIM(RTRIM(excelEmail)),
@excelDate = CAST(RTRIM(excelDate) AS datetime)
from #excelTable;

WHILE @@rowcount <> 0
BEGIN
    SET rowcount 0
	DELETE #excelTable where id = @id;
	BEGIN

		INSERT INTO #user
		SELECT TOP(1)[recId]
				,[email]
				,[dateCreated]
		FROM [Framework_EVOL-1].[dbo].[pal_User]
		WHERE customerCode = 'CLOS-1' AND email like '%'+@excelEmail+'%';

		IF (SELECT COUNT(recId) FROM #user) <> 0
		BEGIN
			select @recId = recId,
			@dateCreated = CAST(RTRIM(dateCreated) AS date)
			from #user[0];

			delete #user where recId = @recId;
			/*if user exists
			if excel date != @dateCreated or != @dateUpdated
			--call proc
			else
			--do nothing
			*/
			IF (CAST(@dateCreated as date) <> CAST(@excelDate as date))
			BEGIN
				PRINT(CAST(@id AS VARCHAR) + ',email:'+@excelEmail+'->updating user');
				UPDATE [Framework_EVOL-1].[dbo].[pal_User]
				SET dateCreated = @excelDate
				WHERE recId = @recId;
			END;
			ELSE
			BEGIN
				PRINT(CAST(@id AS VARCHAR) + ',email:'+@excelEmail+'->date the same');
			END;
		END;

		ELSE
		BEGIN
			/*if user not exists*/
			PRINT(CAST(@id AS VARCHAR) +',email:'+@excelEmail+' does NOT exist!');
			
		END;

	END;
	SET rowcount 1
	select @id = id,
	@excelEmail = LTRIM(RTRIM(excelEmail)),
	@excelDate = CAST(RTRIM(excelDate) AS datetime)
	from #excelTable;

END
set rowcount 0;
DROP TABLE #user;
DROP TABLE #excelTable;
