-------------
-- ROLES
-------------
	USE [IHBoxSystem]
	GO
	CREATE ROLE powerforecast_read AUTHORIZATION dbo 
	GO  

	USE [IHBoxSystem]
	GO
	EXEC sp_addrolemember 'powerforecast_read', 'admin'
	GO

	USE [IHBoxSystem]
	GO
	EXEC sp_addrolemember 'powerforecast_read', 'SDI/australtek'
	GO


	CREATE ROLE powerforecast_readWrite AUTHORIZATION dbo 
	GO  

	USE [IHBoxSystem]
	GO
	EXEC sp_addrolemember 'powerforecast_readWrite', 'admin'
	GO

	USE [IHBoxSystem]
	GO
	EXEC sp_addrolemember 'powerforecast_readWrite', 'SDI/australtek'
	GO


-------------------
-- Menu and Route
-------------------
	declare @parent int, @result_routeId INT
	select @parent=id from [FrontEnd].[Menu] where [Name] = 'MES' and Parent is null

	INSERT INTO [FrontEnd].[Routes]
			   ([Route]
			   ,[ModuleName]
			   ,[ModulePath]
			   ,[RequiredRole]
		   )
		 VALUES 
			   (
			   '!/power-forecast(/:start)(/)'
			   , 'power-forecast'
			   , 'custom-screens/power-forecast/'
			   , 'powerforecast_read'           
			   )
			   SET @result_routeId = @@IDENTITY
	INSERT INTO [FrontEnd].[Menu]
			   ([Parent]
			   ,[Name]
			   ,[RouteID]
			   ,[SortOrder]
			   ,[Enabled]
			   ,[Visible]
			   ,[RequiredRole]
			   ,[Group]
			  )
		 VALUES
			   (
			   @parent
			   ,'Power Forecast'
			   ,@result_routeId 
			   ,5
			   ,1
			   ,1
			   ,'powerforecast_read'
			   ,NULL
			 )
	GO


-------------------
-- SP Permissions
-------------------
EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [powerforecast_readWrite] -- The role
,@name = N'securedsp-PWR.GetPowerForecast' -- the procedure
,@value = N'' 
GO

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [powerforecast_read] -- The role
,@name = N'securedsp-PWR.GetPowerForecast' -- the procedure
,@value = N'' 
GO

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [powerforecast_readWrite] -- The role
,@name = N'securedsp-PWR.UpdatePowerForecast' -- the procedure
,@value = N'SendCurrentUser=TRUE' 
GO

EXECUTE sys.sp_addextendedproperty 
@level0type = N'USER' -- The object type
,@level0name = [powerforecast_read] -- The role
,@name = N'securedsp-PWR.UpdatePowerForecast' -- the procedure
,@value = N'SendCurrentUser=TRUE' 
GO

-------------------
--	Profiles
-------------------
	USE [IHBoxSystem]
	GO

	DECLARE @ModuleId INT

	INSERT INTO [SECURITY].[Modules]([Code],[Name])
	VALUES('Power Forecast','Power Forecast')

	SET @ModuleId = @@IDENTITY

	INSERT INTO [SECURITY].[ModuleActions]([ModuleId],[Code],[Name],[Role])
	VALUES(@ModuleId,'readWrite','Read/Write','powerforecast_readWrite')


	INSERT INTO [SECURITY].ModuleActions(ModuleId,Code,[Name],[Role])
	VALUES (@ModuleId,'read','Read','powerforecast_read')


	

-------------------
--	Query 
-------------------

	declare @table table(
	[Year] int
	,[Month] int
	,[Hour] int
	,[HourEnding01] int
	,[HourEnding02] int
	--,[HourEnding03] int
	--,[HourEnding04] int
	--,[HourEnding05] int
	--,[HourEnding06] int
	--,[HourEnding07] int
	--,[HourEnding08] int
	--,[HourEnding09] int
	--,[HourEnding10] int
	--,[HourEnding11] int	
	--,[HourEnding12] int	
)

insert into @table([Year],[Month],[HourEnding01],[HourEnding02])
values
 (2014,1,235.2,1)
,(2014,2,15.8,1)
,(2014,3,42.9,1)
,(2014,4,50.1,1)
,(2014,5,1050.26,1)
,(2014,6,89.5,1)
,(2014,7,24.7,1)
,(2014,8,895.6,1)
,(2014,9,47.4,1)
,(2014,10,34.12,1)
,(2014,11,10.5,1)
,(2014,12,17.9,1)

,(2015,1,235.2,2)
,(2015,2,15.8,2)
,(2015,3,42.9,2)
,(2015,4,50.1,2)
,(2015,5,1050.26,2)
,(2015,6,89.5,2)
,(2015,7,24.7,2)
,(2015,8,895.6,2)
,(2015,9,47.4,2)
,(2015,10,34.12,2)
,(2015,11,10.5,2)
,(2015,12,17.9,2)

select * from @table




select * from (
	select [Year],[Hour]  = 'HourEnding01', [Month], [HourEnding01] from @table
) s
pivot(
	max([HourEnding01])
	for [Month] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) p

union all
select * from (
	select [Year], [Hour]  = 'HourEnding02',[Month], [HourEnding02] from @table
) s
pivot(
	max([HourEnding02])
	for [Month] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) p



/*---------------------25 Nov --------------------------------*/

/*---------------------25 Nov --------------------------------*/

/*---------------------25 Nov --------------------------------*/

/*---------------------25 Nov --------------------------------*/

GO


ALTER PROCEDURE [PWR].[GetPowerForecast]
AS
BEGIN
		declare @table table ([Date] varchar(10), [Hour] int, [MWHLimit] float)

		insert into @table([Date],[Hour],[MWHLimit])
		select 		
			convert(varchar, [Date], 11)
			,[Hour]
			,[MWHLimit]
		from [PWR].[catDayAheadContracts] 
		where [Date] between '2021-10-25' and '2021-11-25'
		order by [Date] desc, [Hour] asc
	
		select * from @table order by [Hour] asc
		select distinct ([Date]) from @table
		




END

GO


ALTER PROCEDURE [PWR].[UpdatePowerForecast]
 @Date datetime
 ,@Hour int
 ,@MWHLimit float
AS
BEGIN
		
		UPDATE  [PWR].[catDayAheadContracts] 
			SET MWHLimit = @MWHLimit
		Where [Hour] = @Hour and [Date] = @Date
		




END


/*----------------------------------------------------------------*/

USE [MES]
GO
/****** Object:  StoredProcedure [PWR].[GetPowerForecast]    Script Date: 26 nov. 2021 16:16:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [PWR].[GetPowerForecast]
	@Start date = null
AS
BEGIN


	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300)

	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)

	PRINT @procName + ' Parameters:'
		+ ', @Start: ' + ISNULL(CAST(@Start AS VARCHAR), '')

	

	------------------------------------
	-- Tables and Variables Declaration
	------------------------------------		
	DECLARE @t TABLE ([Date] varchar(10), [Hour] int, [MWHLimit] float)
	
	
	DECLARE @missingDates TABLE ([Date] varchar(10), [Hour] int, [MWHLimit] float)

	DECLARE @hourRange TABLE ([Hour] int)
	DECLARE @calendar TABLE ([Date] varchar(10))

	DECLARE @End DATE
		

	-----------------------
	-- Begin Query
	-----------------------
	IF @Start is null
			SET @Start = DATEADD(DAY,-29,GETUTCDATE())
				
	SET @End = DATEADD(DAY,29,@Start)

	-- Get all Dates between @Start and @End
	;WITH DateRange(DateData) AS 
	(
		SELECT @Start as Date
		UNION ALL
		SELECT DATEADD(d,1,DateData)
		FROM DateRange 
		WHERE DateData < @End
	)
	insert into @calendar
	SELECT convert(varchar, [DateData], 11)
	FROM DateRange
	OPTION (MAXRECURSION 0)
	

	-- Get Data from catDayAheadContracts
	insert into @t([Date],[Hour],[MWHLimit])
	select 		
		convert(varchar, [Date], 11)
		,[Hour]
		,[MWHLimit]
	from [PWR].[catDayAheadContracts] 
	where [Date] between @Start and @End
	order by [Date] desc, [Hour] asc
	
	--Get Missing Dates
	--Generate the missing table Dates
	
	--select  [Date], 0, null from @calendar where [Date] not in (select [Date] from @t)
	


	;WITH HourRange([Hour]) AS 
	(
		SELECT 0 as INT
		UNION ALL
		SELECT [Hour] + 1
		FROM HourRange 
		WHERE [Hour] < 23
	)
	insert into @hourRange([Hour])
	SELECT [Hour]
	FROM HourRange


	


	DECLARE @Date AS VARCHAR(10)
	DECLARE cur CURSOR FOR SELECT [Date]  from @calendar where [Date] not in (select [Date] from @t)
	OPEN cur
	FETCH NEXT FROM cur INTO @Date
	WHILE @@fetch_status = 0
	BEGIN
		PRINT @Date

		insert into @missingDates([Date],[Hour])
		select 
			@Date,
			[Hour] 
		from @hourRange

		FETCH NEXT FROM cur INTO @Date
	END
	CLOSE cur
	DEALLOCATE cur



	-------


	select * from @t 
	union all 
	select * from @missingDates
	order by [Hour] asc
		
	select distinct ([Date]) from @t
		


END

GO

EXEC [PWR].[GetPowerForecast] @Start = '2021-11-10'



GO




/*------------------------------2 Dec 2021-------------------------------*/
/*------------------------------2 Dec 2021-------------------------------*/
/*------------------------------2 Dec 2021-------------------------------*/
/*------------------------------2 Dec 2021-------------------------------*/
/*------------------------------2 Dec 2021-------------------------------*/
/*------------------------------2 Dec 2021-------------------------------*/
/*------------------------------2 Dec 2021-------------------------------*/
/*------------------------------2 Dec 2021-------------------------------*/

USE [MES]
GO
/****** Object:  StoredProcedure [PWR].[UpdatePowerForecast]    Script Date: 2 dic. 2021 17:31:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [PWR].[UpdatePowerForecast]

 @CurrentUser varchar(50) = NULL 
 ,@LayoutMWHLimitsByDateAndHour XML = NULL

 
AS
BEGIN

	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300)

	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)

	PRINT @procName + ' Parameters:'

	
	-------------------------------------
	---- Files convertion to VARCHAR
	--------------------------------------
	declare @MWHTable table([Date] date, [Hour] int, [MWHLimit] float)

	INSERT INTO @MWHTable([Date],[Hour],[MWHLimit])
		SELECT
			T.Variable.value('@Date','DATE'),			
			T.Variable.value('@Hour','INT'),
			T.Variable.value('@MWHLimit','FLOAT')
		FROM
		@LayoutMWHLimitsByDateAndHour.nodes('XMLRoot/Row') AS T(Variable)


	MERGE [PWR].[catDayAheadContracts]  AS TARGET
	USING @MWHTable AS SOURCE 
	ON TARGET.[Date] = SOURCE.[Date] AND TARGET.[Hour]  = SOURCE.[Hour]
	WHEN MATCHED 												  
	THEN 
	UPDATE SET 
			TARGET.[MWHLimit]	= SOURCE.[MWHLimit]	
	
	
	WHEN NOT MATCHED BY TARGET THEN 
	INSERT ([Date],[Hour],[MWHLimit]) 
	VALUES (SOURCE.[Date],
			SOURCE.[Hour],
			SOURCE.[MWHLimit]);
	
	SELECT * from @MWHTable

	

END
GO




/*------------------------------6 Dec 2021-------------------------------*/
/*------------------------------6 Dec 2021-------------------------------*/
/*------------------------------6 Dec 2021-------------------------------*/
/*------------------------------6 Dec 2021-------------------------------*/
/*------------------------------6 Dec 2021-------------------------------*/
/*------------------------------6 Dec 2021-------------------------------*/
/*------------------------------6 Dec 2021-------------------------------*/
/*------------------------------6 Dec 2021-------------------------------*/


USE [MES]
GO
/****** Object:  StoredProcedure [PWR].[GetPowerForecast]    Script Date: 6 dic. 2021 11:51:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [PWR].[GetPowerForecastPivoted]
	@Start date = null
AS
BEGIN


	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300)

	SET @procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)

	PRINT @procName + ' Parameters:'
		+ ', @Start: ' + ISNULL(CAST(@Start AS VARCHAR), '')

	

	------------------------------------
	-- Tables and Variables Declaration
	------------------------------------		
	DECLARE @End DATE
	
	DECLARE @columns NVARCHAR(MAX) = ''
	DECLARE @sql     NVARCHAR(MAX) = ''
	
	DECLARE @calendar TABLE ([Date] varchar(10))
	-----------------------
	-- Begin Query
	-----------------------
	IF @Start is null
			SET @Start = DATEADD(DAY,-28,GETUTCDATE())
				
	SET @End = DATEADD(DAY,28,@Start)
	
	-----------------------------------------
	--- Get all Dates between @Start and @End
	------------------------------------------
	;WITH DateRange(DateData) AS 
	(
		SELECT @Start as Date
		UNION ALL
		SELECT DATEADD(d,1,DateData)
		FROM DateRange 
		WHERE DateData < @End
	)
	insert into @calendar
	SELECT cast([DateData] as varchar(10))
	FROM DateRange
	OPTION (MAXRECURSION 0)


	------------------------------------
	--- Get Columns Dynamically
	------------------------------------	
	SELECT 
		@columns+=QUOTENAME([Date]) + ','
	FROM @calendar 
	ORDER BY [Date] ASC
 
	SET @columns = LEFT(@columns, LEN(@columns) - 1)
	
	-- dynamic query
	SET @sql ='
	SELECT * FROM   
	(
	   select [Date],[Hour],[MWHLimit] from [PWR].[catDayAheadContracts] where [Date] between '''+ cast(@Start as varchar) +''' and '''+ cast(@End as varchar) +'''
	) 
	s 
	PIVOT(
		max([MWHLimit])
		for [Date] in ('+ @columns +')
	) as p'

	EXECUTE(@sql)

END


GO
EXEC [PWR].[GetPowerForecastPivot] @start = '2021-11-20'




-------------------------------------
	---- Send results by Email
	--------------------------------------
	IF @Start is null
			SET @Start = DATEADD(DAY,-28,GETUTCDATE())

	DECLARE @querypivoted NVARCHAR(MAX) = 'set nocount on; EXEC [PWR].[GetPowerForecastPivoted] ''' + cast(@start as varchar) + ''' '

	select @querypivoted
	--EXEC msdb.dbo.sp_send_dbmail @profile_name='MES',
	--@recipients='dsilvestri@australtek.com',
	--@subject='MES',
	--@body='TEST',

	--@query= @querypivoted,
	--@attach_query_result_as_file = 1,
	--@query_result_header = 0,
	----@exclude_query_output = 1,
	--@query_result_width = 32767,
	--@query_attachment_filename   = 'Results.csv',
	--@query_result_separator      = ','










	-------------------------------------
	---- FTP UPLOAD FILE
	--------------------------------------


	
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Web;

namespace FTPUploadConsole
{
    class Program
    {

        static void Main(string[] args)
        {
             // Get the object used to communicate with the server.
            FtpWebRequest request = (FtpWebRequest)WebRequest.Create("ftp://192.168.1.113/Buckets.csv");
            request.Method = WebRequestMethods.Ftp.UploadFile;

            request.Credentials = new NetworkCredential("FTP-User", "admin");

            // Copy the contents of the file to the request stream.
            byte[] fileContents;
            using (StreamReader sourceStream = new StreamReader("C:/Users/David/Desktop/David/Buckets.csv"))
            {
                fileContents = Encoding.UTF8.GetBytes(sourceStream.ReadToEnd());
            }

            request.ContentLength = fileContents.Length;

            using (Stream requestStream = request.GetRequestStream())
            {
                requestStream.Write(fileContents, 0, fileContents.Length);
            }

            using (FtpWebResponse response = (FtpWebResponse)request.GetResponse())
            {
                Console.WriteLine($"Upload File Complete, status {response.StatusDescription}");
            }




        }

       
    }
}
