Support #2621 ยป sp_GETDISTANCE.txt
| 1 |
USE [MinovaES_BBG_Prod] |
|---|---|
| 2 |
GO |
| 3 |
/****** Object: StoredProcedure [dbo].[GETDISTANCE] Script Date: 01/03/2024 11:12:13 ******/ |
| 4 |
SET ANSI_NULLS ON |
| 5 |
GO |
| 6 |
SET QUOTED_IDENTIFIER ON |
| 7 |
GO |
| 8 |
|
| 9 |
ALTER PROCEDURE [dbo].[GETDISTANCE] |
| 10 |
( |
| 11 |
@locStarLat FLOAT, |
| 12 |
@locStartLong FLOAT, |
| 13 |
@Employee varchar (max) |
| 14 |
) |
| 15 |
AS |
| 16 |
|
| 17 |
--DECLARE @locStarLat FLOAT = 37.3336581 DECLARE @locStartLong FLOAT = -121.8907041 DECLARE @MetersPerMile FLOAT = 1000 DECLARE @Employee varchar (max)='00000050' |
| 18 |
|
| 19 |
|
| 20 |
declare @empoffice VARCHAR(10) |
| 21 |
DECLARE @now VARCHAR(10) |
| 22 |
DECLARE @distance FLOAT; |
| 23 |
DECLARE @distDiff FLOAT; |
| 24 |
DECLARE @unit VARCHAR(8); |
| 25 |
SELECT |
| 26 |
@distDiff = Value1, |
| 27 |
@unit = Value2 |
| 28 |
FROM |
| 29 |
dbo.PCMEPGENPARAM |
| 30 |
WHERE |
| 31 |
Parameter = 'WFATTDISTANCE'; |
| 32 |
SET @now = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd') |
| 33 |
|
| 34 |
set @empoffice =(select top (1) EmployeeOffice from PHRPA0002 pa2 where EmployeeID= @Employee and pa2.StartDate <= @now |
| 35 |
AND pa2.EndDate >= @now ) |
| 36 |
|
| 37 |
|
| 38 |
SELECT |
| 39 |
TOP(1) @distance = tbl1.JarakKM |
| 40 |
FROM |
| 41 |
( |
| 42 |
SELECT |
| 43 |
EmployeeOffice, |
| 44 |
EmployeeOfficeDesc, |
| 45 |
LintangSelatan, |
| 46 |
LintangUtara, |
| 47 |
( |
| 48 |
SELECT |
| 49 |
( |
| 50 |
6371 * ACOS( |
| 51 |
COS( |
| 52 |
RADIANS(@locStarLat) |
| 53 |
) * COS( |
| 54 |
RADIANS(LintangSelatan) |
| 55 |
) * COS( |
| 56 |
RADIANS(LintangUtara) - RADIANS(@locStartLong) |
| 57 |
) + SIN( |
| 58 |
RADIANS(@locStarLat) |
| 59 |
) * SIN( |
| 60 |
RADIANS(LintangSelatan) |
| 61 |
) |
| 62 |
) |
| 63 |
) |
| 64 |
) AS JarakKM , |
| 65 |
''WarningType |
| 66 |
|
| 67 |
FROM |
| 68 |
dbo.PCMEPEMPOFF |
| 69 |
) AS tbl1 |
| 70 |
WHERE |
| 71 |
tbl1.JarakKM IS NOT NULL and EmployeeOffice = @empoffice |
| 72 |
ORDER BY |
| 73 |
tbl1.JarakKM ASC |
| 74 |
|
| 75 |
IF(@unit = 'm') BEGIN |
| 76 |
SET |
| 77 |
@distance = @distance * 1000; |
| 78 |
END; |
| 79 |
IF(@distance > @distDiff) BEGIN |
| 80 |
|
| 81 |
SELECT |
| 82 |
EmployeeOffice, |
| 83 |
EmployeeOfficeDesc, |
| 84 |
LintangSelatan, |
| 85 |
LintangUtara, |
| 86 |
( |
| 87 |
SELECT |
| 88 |
( |
| 89 |
6371 * ACOS( |
| 90 |
COS( |
| 91 |
RADIANS(@locStarLat) |
| 92 |
) * COS( |
| 93 |
RADIANS(LintangSelatan) |
| 94 |
) * COS( |
| 95 |
RADIANS(LintangUtara) - RADIANS(@locStartLong) |
| 96 |
) + SIN( |
| 97 |
RADIANS(@locStarLat) |
| 98 |
) * SIN( |
| 99 |
RADIANS(LintangSelatan) |
| 100 |
) |
| 101 |
) |
| 102 |
) |
| 103 |
) AS JarakKM , |
| 104 |
'1'WarningType, |
| 105 |
( |
| 106 |
SELECT |
| 107 |
[Message] |
| 108 |
FROM |
| 109 |
SMESSAGECATALOG |
| 110 |
WHERE |
| 111 |
MessageId = 'MATTVALDISTANCE' |
| 112 |
AND [LangId] = 'EN' |
| 113 |
) WarningId |
| 114 |
FROM |
| 115 |
dbo.PCMEPEMPOFF |
| 116 |
where EmployeeOffice = @empoffice |
| 117 |
|
| 118 |
END; |
| 119 |
ELSE BEGIN |
| 120 |
SELECT |
| 121 |
'' WarningType, |
| 122 |
'' WarningId; |
| 123 |
END; |