Synear_API/ivr/sql/sql_P_GetSea...

/****** Object:  StoredProcedure [dbo].[P_GetSeatGroup]    Script Date: 03/10/2018 09:48:37 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_GetSeatGroup]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_GetSeatGroup]
GO

/****** Object:  StoredProcedure [dbo].[P_GetSeatGroup]    Script Date: 03/10/2018 09:48:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






CREATE 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
		--处理主叫号码 获取手机号码前7位
		declare @first char(5)
		declare @Top7 char(10)
		set @first=LEFT(@Caller, 1);
		if @first='0'
		begin
			set @Top7=SubString(@Caller, 2,8);
		end
		else
		begin
			set @Top7=LEFT(@Caller, 1);
		end
		--根据前7位获取区号
		select @areacode=F_ZipCode FROM T_Sys_MobileData where F_MobileNum=@Top7
		--根据区号获取地区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





GO