USE [MinovaES_BBG_Prod]
GO
/****** Object:  StoredProcedure [dbo].[GETDISTANCE]    Script Date: 01/03/2024 11:12:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GETDISTANCE]
 ( 
 @locStarLat FLOAT,
 @locStartLong FLOAT,
 @Employee varchar (max)
 )
AS

--DECLARE @locStarLat FLOAT = 37.3336581 DECLARE @locStartLong FLOAT = -121.8907041 DECLARE @MetersPerMile FLOAT = 1000 DECLARE @Employee varchar (max)='00000050'


declare @empoffice  VARCHAR(10)
DECLARE @now VARCHAR(10)
DECLARE @distance FLOAT;
DECLARE @distDiff FLOAT;
DECLARE @unit VARCHAR(8);
SELECT 
  @distDiff = Value1, 
  @unit = Value2 
FROM 
  dbo.PCMEPGENPARAM 
WHERE 
  Parameter = 'WFATTDISTANCE';
SET @now = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')

set @empoffice =(select top (1) EmployeeOffice from PHRPA0002 pa2 where EmployeeID= @Employee and pa2.StartDate <= @now
                                               AND pa2.EndDate >= @now )


SELECT 
  TOP(1) @distance = tbl1.JarakKM
FROM 
  (
    SELECT 
      EmployeeOffice, 
      EmployeeOfficeDesc, 
      LintangSelatan, 
      LintangUtara, 
      (
        SELECT 
          (
            6371 * ACOS(
              COS(
                RADIANS(@locStarLat)
              ) * COS(
                RADIANS(LintangSelatan)
              ) * COS(
                RADIANS(LintangUtara) - RADIANS(@locStartLong)
              ) + SIN(
                RADIANS(@locStarLat)
              ) * SIN(
                RADIANS(LintangSelatan)
              )
            )
          )
      ) AS JarakKM ,
	  ''WarningType

    FROM 
      dbo.PCMEPEMPOFF
  ) AS tbl1 
WHERE 
  tbl1.JarakKM IS NOT NULL  and EmployeeOffice = @empoffice
ORDER BY 
  tbl1.JarakKM ASC
  
IF(@unit = 'm') BEGIN 
SET 
  @distance = @distance * 1000;
END;
IF(@distance > @distDiff) BEGIN 

    SELECT 
      EmployeeOffice, 
      EmployeeOfficeDesc, 
      LintangSelatan, 
      LintangUtara, 
      (
        SELECT 
          (
            6371 * ACOS(
              COS(
                RADIANS(@locStarLat)
              ) * COS(
                RADIANS(LintangSelatan)
              ) * COS(
                RADIANS(LintangUtara) - RADIANS(@locStartLong)
              ) + SIN(
                RADIANS(@locStarLat)
              ) * SIN(
                RADIANS(LintangSelatan)
              )
            )
          )
      ) AS JarakKM ,
	  '1'WarningType,
		  (
			SELECT 
			  [Message] 
			FROM 
			  SMESSAGECATALOG 
			WHERE 
			  MessageId = 'MATTVALDISTANCE' 
			  AND [LangId] = 'EN'
		  ) WarningId
    FROM 
      dbo.PCMEPEMPOFF
where EmployeeOffice = @empoffice

END;
ELSE BEGIN 
SELECT 
  '' WarningType, 
  '' WarningId;
END;