-- AZIMUTH AND ELEVATION FUNCTION into CommonDB 
USE COMMONDB
GO
CREATE FUNCTION [dbo].[Get_Azimuth] (@Date_and_Time DATETIME, @longitude REAL, @latitude REAL) RETURNS REAL 
AS BEGIN

   DECLARE @LST int --local solar time 
   DECLARE @LSoT INT--local solar time 
   DECLARE @Number_of_Day INT-- day number 
   DECLARE @Rad REAL -- degrees to radians 
   DECLARE @Hour REAL -- hour angle 
   DECLARE @Temp1 REAL -- temp variable 
   DECLARE @Temp2 REAL --temp variable 
   DECLARE @Declination REAL -- declination 
   DECLARE @Elevation REAL --altitude angle 
   DECLARE @Azimut REAL --azimuth angle 
   DECLARE @Timezoneoffset int 
   DECLARE @gLongitude REAL 
   DECLARE @gLatitude REAL 
   --SET @gLongitude = (SELECT Value FROM xxx_Plant_Device_Properties WHERE Value_Key='Longitude') 
   --SET @gLatitude = (SELECT Value FROM xxx_Plant_Device_Properties WHERE Value_Key='Latitude') 
   SET @gLongitude = @longitude
   SET @gLatitude = @latitude

   SET @Rad = 180 / 3.14159265359 -- used to convert to radians 
   SET @Number_of_Day = DateDiff(d, '01.01.' + cast(Datepart(yy,@Date_and_Time) AS VARCHAR(4)) + ' 00:00:00', @Date_and_Time) 
   SET @LST = (DATEPART(hh,@Date_and_Time) * 60) + datepart(n,@Date_and_Time) 
   SET @Timezoneoffset=0 
   SET @LSoT = @LST + 4 * (@gLongitude - (@Timezoneoffset * 15)) 
   SET @Temp1 = ROUND((@LSoT / 60),0) 
   SET @Temp2 = @LSoT % 60 
   If ROUND(@LSoT,0) = 719 
   BEGIN 
      SET @Temp1 = 12 
      SET @Temp2 = 0 
   END 
   If @LSoT < 0 
   BEGIN 
      SET @LSoT = (1440 + @LSoT) 
      SET @Temp1 = ROUND((@LSoT / 60),0) 
      SET @Temp2 = @LSoT % 60 
   END 
   DECLARE @MinNoon INT 
   DECLARE @Noon INT 
   SET @Noon = 60 * 12 
   SET @MinNoon = ROUND((@Noon - @LSoT),0) 
   SET @Hour = @MinNoon / 4.0 
   SET @Declination = 23.45 * Sin((360.0 / 365.0 * (284.0 + @Number_of_Day)) / @Rad) 
   DECLARE @Value REAL 
   SET @Value = Cos(@gLatitude / @Rad) * Cos(@Declination / @Rad) * Cos(@Hour / @Rad) + Sin(@gLatitude / @Rad) * Sin(@Declination / @Rad) 
   IF (-@Value * @Value + 1)>0 BEGIN 
      SET @Elevation = ATAN(@Value / SQRT(-@Value * @Value + 1.0)) 
   END ELSE BEGIN 
      SET @Elevation = 0 
   END 
   SET @Elevation = @Elevation * @Rad 
   SET @Value = (Sin(@Elevation / @Rad) * Sin(@gLatitude / @Rad) - Sin(@Declination / @Rad)) / (Cos(@Elevation / @Rad) * Cos(@gLatitude / @Rad)) 
   IF (-@Value * @Value + 1)>0 BEGIN 
      SET @Azimut = ATAN(-@Value / SQRT(-@Value * @Value + 1)) + 2 * ATAN(1) 
   END ELSE BEGIN 
      SET @Azimut = 0 
   END 
   SET @Azimut = @Azimut * @Rad 
   If @Hour > 0  
   BEGIN  
      SET @Azimut = @Azimut * -1 
   END 
   SET @Azimut = @Azimut + 180 
   RETURN @Azimut 
END 

GO

CREATE FUNCTION .[dbo].[Get_Elevation] (@Date_and_Time DATETIME, @longitude REAL, @latitude REAL) RETURNS REAL 
AS BEGIN 
   DECLARE @LST int --local solar time 
   DECLARE @LSoT INT--local solar time 
   DECLARE @Number_of_Day INT-- day number 
   DECLARE @Rad REAL -- degrees to radians 
   DECLARE @Hour REAL -- hour angle 
   DECLARE @Temp1 REAL -- temp variable 
   DECLARE @Temp2 REAL --temp variable 
   DECLARE @Declination REAL -- declination 
   DECLARE @Elevation REAL --altitude angle 
   DECLARE @Azimut REAL --azimuth angle 
   DECLARE @Timezoneoffset int 
   DECLARE @gLongitude REAL 
   DECLARE @gLatitude REAL 
   --SET @gLongitude = (SELECT Value FROM xxx_Plant_Device_Properties WHERE Value_Key='Longitude') 
   --SET @gLatitude = (SELECT Value FROM xxx_Plant_Device_Properties WHERE Value_Key='Latitude') 
   SET @gLongitude = @longitude
   SET @gLatitude = @latitude
   
   SET @Rad = 180 / 3.14159265359 -- used to convert to radians 
   SET @Number_of_Day = DateDiff(d, '01.01.' + cast(Datepart(yy,@Date_and_Time) AS VARCHAR(4)) + ' 00:00:00', @Date_and_Time) 
   SET @LST = (DATEPART(hh,@Date_and_Time) * 60) + datepart(n,@Date_and_Time) 
   SET @Timezoneoffset=0 
   SET @LSoT = @LST + 4 * (@gLongitude - (@Timezoneoffset * 15)) 
   SET @Temp1 = ROUND((@LSoT / 60),0) 
   SET @Temp2 = @LSoT % 60 
   If ROUND(@LSoT,0) = 719 
   BEGIN 
      SET @Temp1 = 12 
      SET @Temp2 = 0 
   END 
   If @LSoT < 0 
   BEGIN 
      SET @LSoT = (1440 + @LSoT) 
      SET @Temp1 = ROUND((@LSoT / 60),0) 
      SET @Temp2 = @LSoT % 60 
   END 
   DECLARE @MinNoon INT 
   DECLARE @Noon INT 
   SET @Noon = 60 * 12 
   SET @MinNoon = ROUND((@Noon - @LSoT),0) 
   SET @Hour = @MinNoon / 4.0 
   SET @Declination = 23.45 * Sin((360.0 / 365.0 * (284.0 + @Number_of_Day)) / @Rad) 
   DECLARE @Value REAL 
   SET @Value = Cos(@gLatitude / @Rad) * Cos(@Declination / @Rad) * Cos(@Hour / @Rad) + Sin(@gLatitude / @Rad) * Sin(@Declination / @Rad) 
   IF (-@Value * @Value + 1)>0 BEGIN 
      SET @Elevation = ATAN(@Value / SQRT(-@Value * @Value + 1.0)) 
   END ELSE BEGIN 
      SET @Elevation = 0 
   END 
   SET @Elevation = @Elevation * @Rad 
   SET @Value = (Sin(@Elevation / @Rad) * Sin(@gLatitude / @Rad) - Sin(@Declination / @Rad)) / (Cos(@Elevation / @Rad) * Cos(@gLatitude / @Rad)) 
   IF (-@Value * @Value + 1)>0 BEGIN 
      SET @Azimut = ATAN(-@Value / SQRT(-@Value * @Value + 1)) + 2 * ATAN(1) 
   END ELSE BEGIN 
      SET @Azimut = 0 
   END 
   SET @Azimut = @Azimut * @Rad 
   If @Hour > 0  
   BEGIN  
      SET @Azimut = @Azimut * -1 
   END 
   SET @Azimut = @Azimut + 180 
   RETURN @Elevation 
END 

GO



