移动1099存储过程
declare @phone_id as int
declare @phone as varchar( 50)
declare @province as varchar( 50) --号码归属省
declare @price as decimal( 10, 2 )
declare @cityareacode as varchar( 50) --号码归属市
declare @callerid as int --拨号机
declare @CenterId as int --中心id
declare @centerQueue as varchar( 1024) --中心队列
declare @msgqueue as varchar( 1024) --信息队列
declare @ccid as varchar( 50) --cid
declare @yys int
declare @owner_card_id as int --自己卡的ID
declare @cardflag as varchar( 50) --卡类型
declare @cardwantmoney as int --卡面额
declare @currentMinuteOfDay as int --已到账时间
--wzhadd 如果时间小于60秒就返回空
set @phone_id= 0
set @owner_card_id= 0
set @callerid= 0
set @cardflag= ‘‘
set @phone = ‘‘
set @province= ‘‘
set @price = 0
set @cityareacode= ‘‘
set @cardwantmoney= 0
set @centerQueue = ‘‘
set @msgqueue = ‘‘
set @ccid= ‘‘
set @currentMinuteOfDay = DATEPART( hour, getdate())*60 +DATEPART( minute, getdate());
-- -100 ，-200不走卡密的
----with(readpast):查询不到任何记录（记录被锁住将查询不到该记录）;CROSS JOIN连接用于生成两张表的笛卡尔集。将两张表的情况全部列举出来
--isEnable激活的
select top 1 @yys=a .yys, @phone_id=a .id, @phone=phone ,@province= a.province ,
@cityareacode=a .cityareacode, @cardflag=b .CardFlag, @cardwantmoney=b .cardwantmoney
from do_phone a with (readpast) cross join Setting_Card_Alloc b
where a. id=118109241 and a. yys=0
and a. currentmoney<a .wantmoney and a.dotype not in(- 100 ,-200 ) and a.isfinal =0
and a. del_flag=0 and a. isowner=0 and a. maxendtime<dateadd (minute, 10,getdate ()) and b.isEnable =1
--and dbo.isSTKCanProcess(a.province,a.cityareacode,a.yys,a.wantmoney-a.currentmoney)=0
--关键词 DISTINCT 用于返回唯一不同的值。
and b. cardflag in (select cardflag from Owner_CardFlag where cardmoney=b .cardwantmoney and isenable=1 and
CardFlag in (select Distinct CardFlag from Owner_Card with( readpast) where state =0 and CardMoney=b. cardwantmoney))
and a. yys=b .yys
and ( a.Province =b. Province or b .Province is NULL)
and ( a.cityareacode =b. city or b .city is NULL)
and ( a.wantmoney -a. currentmoney>=cardwantmoney )
and (
(
a .wantmoney- a.currentmoney =b. doPhoneWantmoneyDecCurrentmoney and
(
(b. doPhoneWantmoneyLimit=1 and a. wantmoney=b .doPhoneWantmoneyDecCurrentmoney)
or
(b. doPhoneWantmoneyLimit=0 and a. wantmoney<>b .doPhoneWantmoneyDecCurrentmoney)
or
b .doPhoneWantmoneyLimit is NULL
)
)
or
b .doPhoneWantMoneyDecCurrentmoney is NULL
)
--15
and ( a.province =b. Province or b .Province is null)
and ( b.splitflag is NULL or b .splitflag like ‘%‘+ cast(a .splitflag as varchar( 10))+‘,%‘ or b .splitflag like ‘%,‘+cast (a. splitflag as varchar(10 ))+‘‘
or b. splitflag like ‘%,‘+cast (a. splitflag as varchar(10 ))+‘,%‘)
and (
b .CardFlag<> ‘chinaszx‘
or
(
b .CardFlag= ‘chinaszx‘ and
a .id not in
(
--DATEDIFF ( datepart , startdate , enddate )时间差
select phoneid from Setting_Card_PhoneDisable where datediff(s ,getdate(), MaxEnableTime)>0
)
)
)
and a. phone not in(SELECT phone FROM Do_Phone with (nolock) WHERE state in( 1))
--控制dbo.Setting_Card_Alloc中卡的可用时间段2010-11-29
and ( datediff(minute ,CONVERT( varchar(100 ), GETDATE (), 23), getdate()) between b. TimeBegin and b.TimeEnd)
--14.[dbo].[Setting_Disable_City] 里面的城市不处理 移动的表
and ( a.cityareacode not in( select city from [dbo].[Setting_Disable_City] where cardflag= ‘chinaszx‘) or a.yys<> 0)
--15 瀚鹏接口24小时内不准重复提交相同的号码
--and ((b.cardflag=‘HP_V_CARD‘ and a.phone not in (select distinct phone from do_log with(nolock)
-- where createtime>dateadd(n,-1450,getdate()) and cardflag=‘HP_V_CARD‘ and province in(‘天津‘,‘浙江‘,‘广东‘)))
--or b.cardflag<>‘HP_V_CARD‘)
--错误次数大于0的不准走hp，jy，tl
--and ((b.cardflag in (‘HP_V_CARD‘,‘JY_YD_CARD‘,‘TL_YD_CARD‘) and a.errcount=0) or b.cardflag not in (‘HP_V_CARD‘,‘JY_YD_CARD‘,‘TL_YD_CARD‘))
--dotype=-1003
and (( b.cardflag in ( ‘HP_V_CARD‘,‘JY_YD_CARD‘ ,‘TL_YD_CARD‘, ‘JY_DX_CARD‘,‘YX_YD_CARD‘ ) and a.errcount= 0)
or ( b.cardflag not in (‘HP_V_CARD‘ ,‘JY_YD_CARD‘, ‘TL_YD_CARD‘,‘JY_DX_CARD‘ ,‘YX_YD_CARD‘))
or (b. cardflag in (‘HP_V_CARD‘, ‘JY_YD_CARD‘,‘TL_YD_CARD‘ ,‘JY_DX_CARD‘, ‘YX_YD_CARD‘)
and (a. dotype=-1003 or a. WantMoney-a .CurrentMoney= 5)))
--and ((b.cardflag in (‘JY_YD_CARD‘,‘TL_YD_CARD‘) and a.errcount=0) or (b.cardflag not in (‘JY_YD_CARD‘,‘TL_YD_CARD‘))
--or (b.cardflag in (‘JY_YD_CARD‘,‘TL_YD_CARD‘) and a.dotype=-1003))
--17
--exists （sql 返回结果集为真）
--not exists (sql 不返回结果集为真）
and not exists(select 1 from dbo .Setting_CardFLag_Disable_PhoneChannel s where s.FromChannel= a.from_channel and s. FromChannelChild=a .from_channel_child and b.CardFlag =s. CardFlag and isEnable= 1)
and not exists(select 1 from dbo .Setting_CardFLag_Disable_PhoneChannel s where s.FromChannel= a.from_channel and s. FromChannelChild is null and b.CardFlag =s. CardFlag and isEnable =1)
and a. Province in
(
SELECT PhoneProvince
FROM Setting_Caller_PhoneProvinceCityArea_OperateFlag_Relate c
WHERE (c .yys= a.yys ) and (c .isenable = 1) AND
(
(c. PhoneProvince + c .OperateFlag IN
(SELECT d .PhoneProvince + d.OperateFlag
FROM Setting_CardFlag_PhoneProvinceCityArea_OperateFlag_Relate d
WHERE (d .yys= a.yys ) and (d .CardFlag = b.CardFlag ) AND (d .PhoneProvince = a.province ) AND ( d.ProvinceOrCityAreaCode = 0)
)
)
OR
(c. PhoneCityAreaCode + c .OperateFlag IN
(SELECT e .PhoneCityAreaCode + e.OperateFlag
FROM Setting_CardFlag_PhoneProvinceCityArea_OperateFlag_Relate e
WHERE (e .yys= a.yys ) and( e.CardFlag = b. CardFlag) AND ( e.PhoneCityAreaCode = a.CityAreaCode) AND (e. ProvinceOrCityAreaCode = 1 )
)
)
)
and c. callerid in (select callerid from Setting_Caller where isenable=1 and activestate= 0 )
)
order by a.maxendtime ,b. Urgency desc
print ‘@province=‘ +@province;
print ‘@cardflag=‘ +@cardflag;
print ‘@cardwantmoney=‘ +cast( @cardwantmoney as varchar(20 ));
return;
if(@phone_id <>0)
begin
select TOP 1 @callerid=CallerID, @msgqueue=msgqueue ,@CenterId= Centerid,@centerQueue =CenterQueue, @ccid=ccid
from(
SELECT TOP 1 CASE province WHEN @province THEN 0 ELSE 1 END AS isThisProvince, *
FROM Setting_Caller z
WHERE (z .ActiveState = 0) and
--(z.callerid<>1001) and
(z. isEnable = 1 ) and
(z. CallerID IN
(
SELECT CallerID
FROM Setting_Caller_PhoneProvinceCityArea_OperateFlag_Relate a
WHERE (a .yys= @yys) and ( a.isenable = 1) AND
(
(a. PhoneProvince + a .OperateFlag IN
(SELECT b .PhoneProvince + b.OperateFlag
FROM Setting_CardFlag_PhoneProvinceCityArea_OperateFlag_Relate b
WHERE (b .yys= @yys) and ( b.CardFlag = @cardFlag) AND (b. PhoneProvince = @province) AND (b .ProvinceOrCityAreaCode = 0)
)
)
OR
(a. PhoneCityAreaCode + a .OperateFlag IN
(SELECT c .PhoneCityAreaCode + c.OperateFlag
FROM Setting_CardFlag_PhoneProvinceCityArea_OperateFlag_Relate c
WHERE (c .yys= @yys) and(c. CardFlag = @cardFlag ) AND (c .PhoneCityAreaCode = @cityareacode) AND (c .ProvinceOrCityAreaCode = 1)
)
)
)
)
)
and Centerid not in (
select distinct Centerid from setting_centerid_disable_phoneseg where @phone like phoneseg+‘%‘
)
--ORDER BY CallerLevel,isThisProvince,ActiveTime
--张明峰临时修改
--ORDER BY case when callerid>=858 and callerid<866 then 1 else 2 end, CallerLevel,isThisProvince,ActiveTime
--20110921修改，超时的号码有限走中心12拨号机
ORDER BY case when Centerid= 3 then 1 else 2 end , CallerLevel, isThisProvince,ActiveTime
) as z
if(@callerid <>0)
begin
if(@cardflag =‘chinaszx‘)
begin
--取一个字符串的前若干位 select left(‘abcdefg‘, 3) 输出: abc 也可以用select left(FIELD, 3) from TABLE
--输出这个表中所有数据FIELD列的前三个字符
if(@cardwantmoney =20 and @yys=0 and left( @phone,2 )<>‘15‘ and @province<> ‘青海‘ and @province<>‘*‘ and @province<> ‘广东‘)
begin
select top 1 @owner_card_id=id ,@price= OurFeeMoney
from owner_card
where state =0 and cardflag=@cardflag and cardmoney= @cardwantmoney
--zmf update 20110501
--and left(cardpass,3)<>‘151‘
and dbo.[isCardCanChangePhoneWithProvinceAndCity_2](cardflag ,cardid, cardpass,@phone ,@province, @cityareacode)=1
and left( cardpass,2 ) not in
(
select passwordleft2 FROM Setting_CardGroup_SZX_ProvinceDisable with(nolock ) WHERE MaxEnableTime > dateadd( hour,1 ,getdate()) and isEnable = 1 and Disable_Province=@Province
)
end
else
begin
select top 1 @owner_card_id=id ,@price= OurFeeMoney
from owner_card
where state =0 and cardflag=@cardflag and cardmoney= @cardwantmoney
and dbo.[isCardCanChangePhoneWithProvinceAndCity_2](cardflag ,cardid, cardpass,@phone ,@province, @cityareacode)=1
and left( cardpass,2 ) not in
(
select passwordleft2 FROM Setting_CardGroup_SZX_ProvinceDisable with(nolock ) WHERE MaxEnableTime > dateadd( hour,1 ,getdate()) and isEnable = 1 and Disable_Province=@Province
)
end
--order by dbo.[isCardCanChangePhoneWithProvinceAndCity](cardpass,@phone,@province,@cityareacode) desc
-- order by PhoneId desc
if(@owner_card_id <=0)
begin
insert into Setting_Card_PhoneDisable( Phoneid,Phone ,MaxEnableTime, cardflag,cardwantmoney ) values (@phone_id, @phone,DATEADD ([minute], 1,GETDATE ()),‘chinaszx‘, @cardwantmoney)
end
end
else
begin
print ‘‘
select top 1 @owner_card_id=id ,@price= OurFeeMoney from owner_card where state =0 and cardflag=@cardflag and cardmoney=@cardwantmoney
end
end
end
Print @phone_id
Print @cardflag
Print @cardwantmoney
Print @callerid
Print @owner_card_id
Print @price