Ting I 的程式碼集中營

USE [neit_demo]
GO
/****** Object:  StoredProcedure [dbo].[recycler_to_all_community_miles_total_pre_calc]    Script Date: 2019/4/12 下午 11:40:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--執行範例：exec recycler_to_all_community_miles_total_pre_calc @recycler_id = 5
Create procedure [dbo].[recycler_to_all_community_miles_total_pre_calc]
	@recycler_id int  --出車的業者代號
as

create table #recycler_to_community_position_temp
(
	rec_no int identity(1,1) not null,
	id int null,             --業者代號
	position varchar(100),   --經緯度(業者的起始點)
)

insert into #recycler_to_community_position_temp   --寫入業者代號及起始經緯度
	select id , position  from neit_user_info where id = @recycler_id 


insert into #recycler_to_community_position_temp

	select      --從派車單中篩選出可用的經緯度資料
       a.community_id , 
       b.position
	from neit_community_recycler_mapping a left join 
	neit_check_order d on a.community_id = d.community_id 
	left join
	  neit_community b on a.community_id = b.community_id 
        left join
	  neit_user_info c on  a.recycler_id= c.id
	where recycler_id = @recycler_id
	and d.closeYN is null
	and d.community_id is not null
	and d.order_id is not null

	order by   --離業者家最遠的經緯度做距離計算排序(由近至遠)
	(
		SQRT  
		(              -- (x1-x2)^2 + (y1-y2)^2 的開根號
			SQUARE
			(
				convert(float,SUBSTRING(b.position,0, CHARINDEX(',', b.position , 1))) - convert(float,SUBSTRING(c.position,0, CHARINDEX(',', c.position , 1)))
			) 
			+
			SQUARE
			(
				convert(float,SUBSTRING(b.position,CHARINDEX(',', b.position,1) + 1 , LEN(b.position))) - convert(float,SUBSTRING(c.position,CHARINDEX(',', c.position,1) + 1 , LEN(c.position)))
			)
		) 
	) 

insert into #recycler_to_community_position_temp   --寫入業者代號及起始位置(最終站)
	select id , position  from neit_user_info where id = @recycler_id 

	declare @position1 varchar(50)
	declare @position2 varchar(50)
	declare @lat1 float(20)
	declare @lon1 float(20)
	declare @lat2 float(20)
	declare @lon2 float(20)
	declare @distance float(20)
	declare @count int
	set @distance = 0
	set @count = 0
    
    --使用cursor計算直線距離的總公里數
	declare driver_log_cursor cursor for (select position from #recycler_to_community_position_temp)
	open driver_log_cursor
	fetch next from driver_log_cursor into @position1	

    --透過不斷的改變@position1(舊)及@position2(新)來計算彼此間的距離
	while(@@FETCH_STATUS = 0)
	begin
		set @count = @count + 1 
		set @lat1 = convert(float(20),substring(@position1, 0, CHARINDEX(',', @position1,1)))
		set @lon1 = convert(float(20),substring(@position1 ,CHARINDEX(',', @position1,1) + 1 , LEN(@position1) ))
		
		print '第' + convert(varchar,@count) + '次移動 position1 = ' + @position1 

		fetch next from driver_log_cursor into @position2
		
		set @lat2 = convert(float(20),substring(@position2, 0, CHARINDEX(',', @position2,1)))
		set @lon2 = convert(float(20),substring(@position2 ,CHARINDEX(',', @position2,1) + 1 , LEN(@position2) ))
		print '第' + convert(varchar,@count) + '次移動 position2 = ' + @position2 		
            
        --距離公式計算
		set @distance = @distance + sqrt(square((@lat2-@lat1)) + square((@lon2-@lon1))) * 60 * 1.1515 *1.38
		print @distance

		set @position1 = @position2	--將新的距離變成舊的距離，讓下一次的@position2永遠保持最新的			
	end
	close driver_log_cursor
	deallocate driver_log_cursor

	select isnull(@distance,0) as km

本範例的計算及判斷方式較為粗糙，因省略了部分實體Table的Schema，單一SP程式無法產生執行結果，且離實際計算的邏輯有所出入，其參考價值在於路線的排定及計算，請讀者斟酌使用。