Synear_API/sql/sql_P_GetSea...

USE [ANTU20480330]
GO
/****** Object:  StoredProcedure [dbo].[P_GetSeatGroup]    Script Date: 05/17/2018 17:36:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[P_GetSeatGroup] 
(
	@Caller NVARCHAR(50), --主叫号码
	@Callee NVARCHAR(50) --被叫号码
)
AS
BEGIN

	SET NOCOUNT ON;
	
	declare @locationID int--主叫号码的归属地ID
	declare @areacode nvarchar(50)--区号
	declare @usercode nvarchar(50)--需要返回的坐席
	declare @zxzid int --坐席组ID
	
	--根据被叫号码获取对应坐席组
	if @Callee='8001'
	begin
		select @zxzid= F_ZXZID from T_Sys_SeatGroup where F_ZXZCode='KFZ'
	end
	else
	begin
		select @areacode=dbo.GetPhoneLocation(@Caller)
		--根据区号获取地区ID
		select @locationID=id FROM tel_location where tel=@areacode
		--根据地区ID获取对应负责的坐席组ID
		select @zxzid=userid from telloc_users where tellocid=@locationID
	end
	
	select @zxzid
	
END