/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (1000) [ID]
      ,[NIP]
      ,[TANGGAL_ABSEN]
      ,[JAM_EFEKTIF]
      ,[JAM_LEMBUR]
      ,[TIPE_HARI]
      ,[JAM_MASUK]
      ,[JAM_KELUAR]
      ,[TIPE]
      ,[HADIR]
      ,[SAKIT]
      ,[IJIN]
      ,[ALPA]
      ,[LIBUR]
      ,[DINAS_LUAR]
      ,[TUGAS_LUAR]
      ,[CUTI_TAHUNAN]
      ,[CUTI_KHUSUS]
      ,[MELAHIRKAN]
      ,[DIKLAT]
      ,[LH]
      ,[JML_POTONGAN]
      ,[JML_BAYAR]
      ,[ALASAN]
      ,[TERLAMBAT]
      ,[PULANG_CEPAT]
      ,[MENIT_TERLAMBAT]
      ,[MENIT_PULANG_CEPAT]
      ,[UPDATE_DATE]
      ,[STATUS_KELUAR]
      ,[STATUS_MASUK]
      ,[STATUS_MASUK_KELUAR]
      ,[MOBILE_MASUK]
      ,[MOBILE_KELUAR]
      ,[MOBILE_MASUK_LOC]
      ,[MOBILE_KELUAR_LOC]
      ,[LOKASI_KERJA]
  FROM [HRIS].[dbo].[SPG_REPORT_ABSENSI]
where tanggal_absen = '20220328'

select *, left('106.972612269915;-6.25969666535174', charindex(';', '106.972612269915;-6.25969666535174') - 1) from spg_absensi where tanggal_absen = '20220328'

select left('106.972612269915;-6.25969666535174', charindex(';', '106.972612269915;-6.25969666535174')-1),
substring('106.972612269915;-6.25969666535174',charindex(';', '106.972612269915;-6.25969666535174')+1, len('106.972612269915;-6.25969666535174')-charindex(';', '106.972612269915;-6.25969666535174'))



select SUBSTRING('106.972612269915;-6.25969666535174', LEN(LEFT('106.972612269915;-6.25969666535174', CHARINDEX ('/', '106.972612269915;-6.25969666535174'))) + 1, LEN('106.972612269915;-6.25969666535174') - LEN(LEFT('106.972612269915;-6.25969666535174', 
    CHARINDEX ('/', '106.972612269915;-6.25969666535174'))) - LEN(RIGHT('106.972612269915;-6.25969666535174', LEN('106.972612269915;-6.25969666535174') - CHARINDEX ('.', '106.972612269915;-6.25969666535174'))) - 1);

select * from spg_absensi where tanggal_absen = '20220328' 

DECLARE
	@wismul_geo GEOGRAPHY,
	@wismul_lat VARCHAR(10) = '-6.234280',
	@wismul_long VARCHAR(10) = '106.822200',
	@mrp_geo GEOGRAPHY,
	@mrp_lat VARCHAR(10) = '-6.182116',
	@mrp_long VARCHAR(10) = '106.821558',
	@sumitro_geo GEOGRAPHY,
	@sumitro_lat VARCHAR(10) = '-6.169032',
	@sumitro_long VARCHAR(10) = '106.837630'

SET @wismul_geo= geography::Point(@wismul_lat, @wismul_long, 4326)
SET @mrp_geo= geography::Point(@mrp_lat, @mrp_long, 4326)
SET @sumitro_geo= geography::Point(@sumitro_lat, @sumitro_long, 4326)

SELECT
	d.NIP,
	d.NM_PEG,
	CASE
		WHEN a.jarak_masuk_wismul <= 250 THEN 'Wismul'
		WHEN a.jarak_masuk_mrp <= 250 THEN 'MRP'
		WHEN a.jarak_masuk_sumitro <= 250 THEN 'Sumitro'
		ELSE 'Lainnya'
	END AS Lokasi_Presensi_Masuk
			
FROM
	SPG_DATA_CURRENT AS d LEFT JOIN
	(
		SELECT 
			*,
			(@wismul_geo.STDistance(geography::Point(substring(COALESCE(MASUK_TERM_ID,'0;0'),charindex(';', COALESCE(MASUK_TERM_ID,'0;0'))+1, len(COALESCE(MASUK_TERM_ID,'0;0'))-charindex(';', COALESCE(MASUK_TERM_ID,'0;0'))), left(COALESCE(MASUK_TERM_ID,'0;0'), charindex(';', COALESCE(MASUK_TERM_ID,'0;0'))-1), 4326))) as jarak_masuk_wismul,
			(@mrp_geo.STDistance(geography::Point(substring(COALESCE(MASUK_TERM_ID,'0;0'),charindex(';', COALESCE(MASUK_TERM_ID,'0;0'))+1, len(COALESCE(MASUK_TERM_ID,'0;0'))-charindex(';', COALESCE(MASUK_TERM_ID,'0;0'))), left(COALESCE(MASUK_TERM_ID,'0;0'), charindex(';', COALESCE(MASUK_TERM_ID,'0;0'))-1), 4326))) as jarak_masuk_mrp,
			(@sumitro_geo.STDistance(geography::Point(substring(COALESCE(MASUK_TERM_ID,'0;0'),charindex(';', COALESCE(MASUK_TERM_ID,'0;0'))+1, len(COALESCE(MASUK_TERM_ID,'0;0'))-charindex(';', COALESCE(MASUK_TERM_ID,'0;0'))), left(COALESCE(MASUK_TERM_ID,'0;0'), charindex(';', COALESCE(MASUK_TERM_ID,'0;0'))-1), 4326))) as jarak_masuk_sumitro
		FROM 
			dbo.SPG_ABSENSI 
		WHERE
			tanggal_absen = '20220328' 
			AND MASUK_TERM_ID <> '9999'
	) AS a ON d.NIP = a.NIP AND a.TANGGAL_ABSEN = '20220328'
WHERE
	d.STS_PENSIUN = '0'