
DECLARE @dateCreated DATE, @dateUpdated DATE, @recId int, @url NVARCHAR(MAX), @json NVARCHAR(MAX),
@Response NVARCHAR(MAX);
SET @url = 'https://portal.cabriere.co.za/ironwifi-register';

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

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

--RENAME FILE PATH
BULK INSERT #excelTable
FROM 'C:\Doroteja\scripts\CLOS-1_ironwifi_user_registration_scripts\CLOS_User_Script_20191221.csv'
WITH
(
  FIRSTROW = 2,
  DATAFILETYPE='widechar', -- UTF-16
  FIELDTERMINATOR = ';',
  ROWTERMINATOR = '\n',
  TABLOCK,
  KEEPNULLS
)

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

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

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

		INSERT INTO #user
		SELECT TOP(1)[recId]
				,[name]
				,[email]
				,[dateCreated]
				,[dateUpdated]
		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),
			@dateUpdated = CAST(RTRIM(dateUpdated) 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 (@dateCreated <> @excelDate AND @dateUpdated <> @excelDate) AND (@dateCreated <> CONVERT(date, GETDATE()) AND @dateUpdated <> CONVERT (date, GETDATE()))
			BEGIN
				PRINT(CAST(@id AS VARCHAR) + ',email:'+@excelEmail+'->sending when user exists but dates dont match');
				SET @json = '{
				"username":"' + @excelName + '",
				"email": "' + @excelEmail + '",
				"date": "' + @excelDate + '"
				}';

				EXECUTE SampleWSPOST @url, @json,@Response OUT
				--SELECT @Response
			END;
		END;

		ELSE
		BEGIN
			/*if user not exists*/
			PRINT(CAST(@id AS VARCHAR) +',email:'+@excelEmail+'->sending when no user');
			SET @json = '{
			"username":"' + @excelName + '",
			"email": "' + @excelEmail + '",
			"date": "' + @excelDate + '"
			}';

			EXECUTE SampleWSPOST @url, @json,@Response OUT
			--SELECT @Response
		END;

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

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