移动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