- 浏览: 102175 次
- 性别:
- 来自: 郑州
文章分类
- 全部博客 (60)
- oracle (4)
- JAVA (12)
- js (2)
- tomcat (4)
- jdk (3)
- myeclipse (6)
- mysql (9)
- linux (6)
- hibernate (2)
- 解密 (1)
- 程序 (2)
- 故事 (1)
- html (2)
- xml (2)
- Jquery (3)
- ExtJs (1)
- IDE (2)
- JAVA Spring (0)
- Spring (1)
- JeasyUI (2)
- 验证 (1)
- HttpClient (1)
- php (2)
- apache (2)
- centos (4)
- kaptcha (1)
- SpringMVC (1)
- 验证码 (1)
- 网络 (1)
- 打印 (1)
最新评论
-
hy2012_campus:
总结的不错,顶一个
kaptcha 验证码组件结合springMVC示例 -
lirenhai2008:
感谢了。
PLSQL注册码,绝对能用!
自己写的短信指令查询燃气费oracle触发器
最近做了个天然气的短信平台项目,以下内容是通过短信指令实现该用户的费用反馈信息。
希望对大家有帮助,也方便自己以后使用。。
CREATE OR REPLACE TRIGGER "TRI_SMS_RECEIVE"
BEFORE insert on sms_receive
FOR EACH ROW
Declare
phone number(11);/*手机(查询条件用)*/
phone_gr number(11);/*居民手机*/
phone_dw number(11);/*单位手机*/
dy_phone number(11);/*订阅手机*/
detail varchar2(500);/*内容*/
v_yhzh varchar2(36);/*用户号(截取用)*/
v_yhzh_gr varchar2(36);/*居民用户号*/
v_yhzh_dw varchar2(36);/*单位用户号*/
v_zl varchar2(36);/*短信指令*/
orgId_gr varchar2(36);/*居民区域码*/
orgId_dw varchar2(36);/*单位区域码*/
tq_text varchar2(1000);/*停气信息*/
fee number(11,2);/*当月费用*/
fee_yu number(11,2);/*可用余额*/
fee_qf number(11,2);/*目前欠费总额*/
time_month varchar2(50); /*用于定义当前的年月*/
time_js varchar2(50); /*用于接受用户发送的年月*/
Begin
begin
select sms_receive_sid_seq.nextval
into :new.receive_sid
from dual;
end;
time_month := to_char(sysdate,'yyyymm'); /*赋值:当前的年月*/
/*获得手机号码和短信内容*/
begin
select :new.sm_org_mobile,:new.sm_content into phone,detail from dual ;
end;
v_zl := substr(detail,1,4);/*赋值:截取前四位指令*/
v_yhzh := substr(detail,6,10);/*赋值:截取用户号*/
time_js := substr(detail,17,6);/*赋值:截取年月*/
dbms_output.put_line('得到手机和用户号'||phone||'--'||v_yhzh);
begin
/*查询出居民的区域码,手机,用户号*/
select hnpy.g.org_id,hnpy.g.gr_sj,hnpy.s.yqdz_userid_old into orgId_gr,phone_gr,v_yhzh_gr
from hnpy.v_yongqidizhi s, hnpy.v_gerenkehu g
where hnpy.s.yqdz_kh_id=hnpy.g.gr_id and hnpy.s.yqdz_kh_lx=2
and hnpy.g.gr_sj=phone and s.yqdz_userid_old=v_yhzh;
exception
WHEN NO_DATA_FOUND THEN
orgId_gr:=NULL;
phone_gr:=null;
v_yhzh_gr:=null;
end;
dbms_output.put_line('居民查询结束!得到手机phone_gr'||phone_gr||'用户号v_yhzh_gr'||v_yhzh_gr||'orgId_gr'||orgId_gr);
dbms_output.put_line('条件'||phone||'-'||v_yhzh);
begin
/*查询出单位的区域码,手机,用户号*/
select hnpy.g.org_id,hnpy.g.kh_dh_bg,hnpy.s.yqdz_userid_old into orgId_dw,phone_dw,v_yhzh_dw
from hnpy.v_yongqidizhi s, hnpy.v_danweikehu g
where hnpy.s.yqdz_kh_id=hnpy.g.kh_id and hnpy.s.yqdz_kh_lx=1
and g.kh_dh_bg=phone and s.yqdz_userid_old=v_yhzh;
exception
when no_data_found then
orgId_dw:=null;
phone_dw:=null;
v_yhzh_dw:=null;
end;
dbms_output.put_line('单位查询结束!得到手机phone_dw'||phone_dw||'用户号v_yhzh_dw'||v_yhzh_dw||'orgId_dw'||orgId_dw);
if v_zl='cxfy' or v_zl='CXFY' then /*查询费用-------*/
dbms_output.put_line('进入cxfy!');
dbms_output.put_line('phone_dw '||phone_dw);
dbms_output.put_line('v_yhzh_dw '||v_yhzh_dw);
dbms_output.put_line('用户号v_yhzh--'||v_yhzh);
begin
/*北京方面提供的sql语句beijing---------*/
select nvl(a.费用总金额, 0), NVL(c.余额, 0), NVL(b.总欠费金额, 0) into fee,fee_yu,fee_qf
from (select cbjl_yqzh, SUM(nvl(cbjl.cbjl_sjjfe, 0)) 费用总金额
from hnpy.v_chaobiao_jilu cbjl
where cbjl_cbqijian like time_month||'%' --日期格式YYYYMM 如:201101%
and cbjl_yqzh = v_yhzh --用户号
group by cbjl_yqzh) a
left join
(select cbjl_yqzh,SUM(nvl(cbjl.cbjl_yje + cbjl.cbjl_gwf, 0)) 总欠费金额
from hnpy.v_chaobiao_jilu cbjl
where cbjl_yqzh = v_yhzh --用户号
and cbjl_zt = '4'
group by cbjl_yqzh) b
on a.cbjl_yqzh = b.cbjl_yqzh
left join (select yqdz_userid_old, zjzh.khzj_keyong_yue 余额
from hnpy.v_zijinzhanghu zjzh
left join hnpy.v_yongqidizhi yqdz
on yqdz.yqdz_id = zjzh.khzj_yqdz_id
where yqdz_userid_old = v_yhzh) c --用户号
on a.cbjl_yqzh = c.yqdz_userid_old
left join (select yqdz_userid_old, dwzjzh.dwzj_keyong_yue 余额
from hnpy.v_danweizijinzhanghu dwzjzh
left join hnpy.v_yongqidizhi yqdz
on yqdz.yqdz_kh_id = dwzjzh.kh_id
where yqdz_userid_old = v_yhzh) d --用户号
on a.cbjl_yqzh = d.yqdz_userid_old; /*北京方面提供的sql语句-----------beijing*/
exception
when no_data_found then
fee := null;
fee_qf := null;
fee_yu := null;
end;
dbms_output.put_line(time_month);
dbms_output.put_line('得到费用'||fee||' '||fee_yu||' '||fee_qf);
/*验证手机号码是否是登记时的手机号码*/
if phone_gr is not null or v_yhzh_gr is not null or phone_dw is not null or v_yhzh_dw is not null then
begin
/*查找停气信息*/
select gas.info_content
into tq_text
from dx.gas_info gas
where rownum<2 and sysdate>=gas.start_date and sysdate<=gas.end_date
and gas.area_sid=orgId_gr or gas.area_sid=orgId_dw;
exception
when no_data_found then
tq_text := null;
end;
dbms_output.put_line('停气内容:'||tq_text);
/*将停气信息发送至用户手机*/
if tq_text is not null then/*2--*/
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,tq_text,sysdate,sysdate);
end if;/*--2*/
dbms_output.put_line('手机phone_gr '||phone_gr||'phone_dw '||phone_dw);
/*发送费用信息*/
if phone_gr is not null and fee is not null then/*1--*/
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone_gr,'尊敬的居民用户!您本月的天燃气费共'||fee||'元,当前余额'||fee_yu||'元,目前共欠费'||fee_qf||'元!如需帮助请回复BZXX!',sysdate,sysdate);
elsif phone_dw is not null and fee is not null then
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone_dw,'尊敬的单位用户!您本月的天燃气费共'||fee||'元,当前余额'||fee_yu||'元,目前共欠费'||fee_qf||'元!如需帮助请回复BZXX!',sysdate,sysdate);
else
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone_dw,'尊敬的用户!暂时没有当月的相关信息!如需帮助请回复BZXX!',sysdate,sysdate);
end if;/*--1*/
else
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'指令有误!请用您登记的手机号码来查询!如需帮助请回复BZXX!',sysdate,sysdate);
end if;
elsif v_zl='cxwy' or v_zl='CXWY' then /*查询往月费用-------*/
dbms_output.put_line('进入cxwy!');
begin
/*北京方面提供的sql语句beijing---------*/
select nvl(a.费用总金额, 0), NVL(c.余额, 0), NVL(b.总欠费金额, 0) into fee,fee_yu,fee_qf
from (select cbjl_yqzh, SUM(nvl(cbjl.cbjl_sjjfe, 0)) 费用总金额
from hnpy.v_chaobiao_jilu cbjl
where cbjl_cbqijian like time_js||'%' --日期格式YYYYMM 如:201101%
and cbjl_yqzh = v_yhzh --用户号
group by cbjl_yqzh) a
left join
(select cbjl_yqzh,SUM(nvl(cbjl.cbjl_yje + cbjl.cbjl_gwf, 0)) 总欠费金额
from hnpy.v_chaobiao_jilu cbjl
where cbjl_yqzh = v_yhzh --用户号
and cbjl_zt = '4'
group by cbjl_yqzh) b
on a.cbjl_yqzh = b.cbjl_yqzh
left join (select yqdz_userid_old, zjzh.khzj_keyong_yue 余额
from hnpy.v_zijinzhanghu zjzh
left join hnpy.v_yongqidizhi yqdz
on yqdz.yqdz_id = zjzh.khzj_yqdz_id
where yqdz_userid_old = v_yhzh) c --用户号
on a.cbjl_yqzh = c.yqdz_userid_old
left join (select yqdz_userid_old, dwzjzh.dwzj_keyong_yue 余额
from hnpy.v_danweizijinzhanghu dwzjzh
left join hnpy.v_yongqidizhi yqdz
on yqdz.yqdz_kh_id = dwzjzh.kh_id
where yqdz_userid_old = v_yhzh) d --用户号
on a.cbjl_yqzh = d.yqdz_userid_old; /*北京方面提供的sql语句-----------beijing*/
exception
when no_data_found then
fee := null;
fee_qf := null;
fee_yu := null;
end;
dbms_output.put_line('time_js--'||time_js);
/*验证手机号码是否是登记时的手机号码*/
if phone_gr is not null or v_yhzh_gr is not null or phone_dw is not null or v_yhzh_dw is not null then
begin
/*查找停气信息*/
select gas.info_content into tq_text
from dx.gas_info gas
where rownum<2 and sysdate>=gas.start_date and sysdate<=gas.end_date
and gas.area_sid=orgId_gr or gas.area_sid=orgId_dw;
exception
when no_data_found then
tq_text := null;
end;
/*将停气信息发送至用户手机*/
if tq_text is not null then/*2--*/
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,tq_text,sysdate,sysdate);
end if;/*--2*/
/*发送费用信息*/
if phone_gr is not null and fee is not null then/*1--*/
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone_gr,'尊敬的用户!您'||time_js||'月的天燃气费共'||fee||'元,当前余额'||fee_yu||'元,目前共欠费'||fee_qf||'元!如需帮助请回复BZXX!',sysdate,sysdate);
elsif phone_dw is not null and fee is not null then
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone_dw,'尊敬的用户!您'||time_js||'月的天燃气费共'||fee||'元,当前余额'||fee_yu||'元,目前共欠费'||fee_qf||'元!如需帮助请回复BZXX!',sysdate,sysdate);
else
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone_dw,'尊敬的用户!您没有该月的相关信息!如需帮助请回复BZXX!',sysdate,sysdate);
end if;/*--1*/
else
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'指令有误!请用您登记的手机号码来查询!如需帮助请回复BZXX!',sysdate,sysdate);
end if;
elsif v_zl='cxtq' or v_zl='CXTQ' then /*查询停气-------*/
dbms_output.put_line('进入cxtq!');
/*验证手机号码是否是登记时的手机号码*/
if phone_gr is not null or v_yhzh_gr is not null or phone_dw is not null or v_yhzh_dw is not null then
/*查找停气信息内容*/
begin
select gas.info_content into tq_text
from dx.gas_info gas
where rownum<2 and sysdate>=gas.start_date and sysdate<=gas.end_date
and gas.area_sid=orgId_gr or gas.area_sid=orgId_dw;
exception
when no_data_found then
tq_text := null;
end;
/*将停气信息发送至用户手机*/
if tq_text is not null then/*2--*/
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,tq_text,sysdate,sysdate);
else /*没有停气信息*/
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'没有找到近期停气信息!如需帮助请回复BZXX!',sysdate,sysdate);
end if;/*--2*/
else
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'指令有误!请用您登记的手机号码来查询!如需帮助请回复BZXX!',sysdate,sysdate);
end if;
elsif v_zl='dyxx' or v_zl='DYXX' then/*订阅-------*/
dbms_output.put_line('进入dyxx!');
/*验证手机号码是否是登记时的手机号码*/
if phone_gr is not null or v_yhzh_gr is not null or phone_dw is not null or v_yhzh_dw is not null then
/*查询出是否订阅过*/
begin
select r.user_mobile into dy_phone from dx.dy_order r where r.user_mobile=phone ;
exception
when no_data_found then
dy_phone := null;
end;
/*判断是否订阅*/
if dy_phone is not null then
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'您已经订阅!请不要重复订阅!如需帮助请回复BZXX!',sysdate,sysdate);
else/*添加到订阅表,同时发送通知短信*/
insert into dy_order (user_mobile,user_sid,create_date,add_staff)
values (phone,v_yhzh,sysdate,'admin') ;
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'订阅成功!系统将每月定时向您发送您的费用信息!如需帮助请回复BZXX!',sysdate,sysdate);
end if;
else/*不是登记时的手机号码*/
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'指令有误!请用您登记的手机号码查询!如需帮助请回复BZXX!',sysdate,sysdate);
end if;
elsif v_zl='tdxx' or v_zl='TDXX' then /*退订-------*/
dbms_output.put_line('进入tdxx!');
/*验证手机号码是否是登记时的手机号码*/
if phone_gr is not null or v_yhzh_gr is not null or phone_dw is not null or v_yhzh_dw is not null then
/*查询出是否订阅过*/
begin
select r.user_mobile into dy_phone from dx.dy_order r where r.user_mobile=phone and r.user_sid=v_yhzh ;
exception
when no_data_found then
dy_phone := null;
end;
if dy_phone is null then
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'未发现您有订阅信息!如需帮助请回复BZXX!',sysdate,sysdate);
else
/*从订阅表里删除,同时发送通知短信*/
delete from dx.dy_order d where d.user_mobile=phone and d.user_sid=v_yhzh ;
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'退订成功!欢迎再次使用!如需帮助请回复BZXX!',sysdate,sysdate);
end if;
else /*不是登记时的手机号码*/
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'指令有误!请用您登记的手机号码查询!如需帮助请回复BZXX!',sysdate,sysdate);
end if;
elsif v_zl='bzxx' or v_zl='BZXX' then /*帮助-------*/
dbms_output.put_line('进入bzxx!');
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'欢迎使用查询帮助指令!1.查询费用:CXFY#用户号 2.查询往月费用:CXWY#用户号#年年年年月月 3.查询停气信息:CXTQ#用户号 4.订阅信息:DYXX#用户号 5.退订信息:TDXX#用户号',sysdate,sysdate);
else /*其他的指令-------*/
dbms_output.put_line('进入其他!');
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'指令有误!如需帮助请回复BZXX!',sysdate,sysdate);
end if;
end;
希望对大家有帮助,也方便自己以后使用。。
CREATE OR REPLACE TRIGGER "TRI_SMS_RECEIVE"
BEFORE insert on sms_receive
FOR EACH ROW
Declare
phone number(11);/*手机(查询条件用)*/
phone_gr number(11);/*居民手机*/
phone_dw number(11);/*单位手机*/
dy_phone number(11);/*订阅手机*/
detail varchar2(500);/*内容*/
v_yhzh varchar2(36);/*用户号(截取用)*/
v_yhzh_gr varchar2(36);/*居民用户号*/
v_yhzh_dw varchar2(36);/*单位用户号*/
v_zl varchar2(36);/*短信指令*/
orgId_gr varchar2(36);/*居民区域码*/
orgId_dw varchar2(36);/*单位区域码*/
tq_text varchar2(1000);/*停气信息*/
fee number(11,2);/*当月费用*/
fee_yu number(11,2);/*可用余额*/
fee_qf number(11,2);/*目前欠费总额*/
time_month varchar2(50); /*用于定义当前的年月*/
time_js varchar2(50); /*用于接受用户发送的年月*/
Begin
begin
select sms_receive_sid_seq.nextval
into :new.receive_sid
from dual;
end;
time_month := to_char(sysdate,'yyyymm'); /*赋值:当前的年月*/
/*获得手机号码和短信内容*/
begin
select :new.sm_org_mobile,:new.sm_content into phone,detail from dual ;
end;
v_zl := substr(detail,1,4);/*赋值:截取前四位指令*/
v_yhzh := substr(detail,6,10);/*赋值:截取用户号*/
time_js := substr(detail,17,6);/*赋值:截取年月*/
dbms_output.put_line('得到手机和用户号'||phone||'--'||v_yhzh);
begin
/*查询出居民的区域码,手机,用户号*/
select hnpy.g.org_id,hnpy.g.gr_sj,hnpy.s.yqdz_userid_old into orgId_gr,phone_gr,v_yhzh_gr
from hnpy.v_yongqidizhi s, hnpy.v_gerenkehu g
where hnpy.s.yqdz_kh_id=hnpy.g.gr_id and hnpy.s.yqdz_kh_lx=2
and hnpy.g.gr_sj=phone and s.yqdz_userid_old=v_yhzh;
exception
WHEN NO_DATA_FOUND THEN
orgId_gr:=NULL;
phone_gr:=null;
v_yhzh_gr:=null;
end;
dbms_output.put_line('居民查询结束!得到手机phone_gr'||phone_gr||'用户号v_yhzh_gr'||v_yhzh_gr||'orgId_gr'||orgId_gr);
dbms_output.put_line('条件'||phone||'-'||v_yhzh);
begin
/*查询出单位的区域码,手机,用户号*/
select hnpy.g.org_id,hnpy.g.kh_dh_bg,hnpy.s.yqdz_userid_old into orgId_dw,phone_dw,v_yhzh_dw
from hnpy.v_yongqidizhi s, hnpy.v_danweikehu g
where hnpy.s.yqdz_kh_id=hnpy.g.kh_id and hnpy.s.yqdz_kh_lx=1
and g.kh_dh_bg=phone and s.yqdz_userid_old=v_yhzh;
exception
when no_data_found then
orgId_dw:=null;
phone_dw:=null;
v_yhzh_dw:=null;
end;
dbms_output.put_line('单位查询结束!得到手机phone_dw'||phone_dw||'用户号v_yhzh_dw'||v_yhzh_dw||'orgId_dw'||orgId_dw);
if v_zl='cxfy' or v_zl='CXFY' then /*查询费用-------*/
dbms_output.put_line('进入cxfy!');
dbms_output.put_line('phone_dw '||phone_dw);
dbms_output.put_line('v_yhzh_dw '||v_yhzh_dw);
dbms_output.put_line('用户号v_yhzh--'||v_yhzh);
begin
/*北京方面提供的sql语句beijing---------*/
select nvl(a.费用总金额, 0), NVL(c.余额, 0), NVL(b.总欠费金额, 0) into fee,fee_yu,fee_qf
from (select cbjl_yqzh, SUM(nvl(cbjl.cbjl_sjjfe, 0)) 费用总金额
from hnpy.v_chaobiao_jilu cbjl
where cbjl_cbqijian like time_month||'%' --日期格式YYYYMM 如:201101%
and cbjl_yqzh = v_yhzh --用户号
group by cbjl_yqzh) a
left join
(select cbjl_yqzh,SUM(nvl(cbjl.cbjl_yje + cbjl.cbjl_gwf, 0)) 总欠费金额
from hnpy.v_chaobiao_jilu cbjl
where cbjl_yqzh = v_yhzh --用户号
and cbjl_zt = '4'
group by cbjl_yqzh) b
on a.cbjl_yqzh = b.cbjl_yqzh
left join (select yqdz_userid_old, zjzh.khzj_keyong_yue 余额
from hnpy.v_zijinzhanghu zjzh
left join hnpy.v_yongqidizhi yqdz
on yqdz.yqdz_id = zjzh.khzj_yqdz_id
where yqdz_userid_old = v_yhzh) c --用户号
on a.cbjl_yqzh = c.yqdz_userid_old
left join (select yqdz_userid_old, dwzjzh.dwzj_keyong_yue 余额
from hnpy.v_danweizijinzhanghu dwzjzh
left join hnpy.v_yongqidizhi yqdz
on yqdz.yqdz_kh_id = dwzjzh.kh_id
where yqdz_userid_old = v_yhzh) d --用户号
on a.cbjl_yqzh = d.yqdz_userid_old; /*北京方面提供的sql语句-----------beijing*/
exception
when no_data_found then
fee := null;
fee_qf := null;
fee_yu := null;
end;
dbms_output.put_line(time_month);
dbms_output.put_line('得到费用'||fee||' '||fee_yu||' '||fee_qf);
/*验证手机号码是否是登记时的手机号码*/
if phone_gr is not null or v_yhzh_gr is not null or phone_dw is not null or v_yhzh_dw is not null then
begin
/*查找停气信息*/
select gas.info_content
into tq_text
from dx.gas_info gas
where rownum<2 and sysdate>=gas.start_date and sysdate<=gas.end_date
and gas.area_sid=orgId_gr or gas.area_sid=orgId_dw;
exception
when no_data_found then
tq_text := null;
end;
dbms_output.put_line('停气内容:'||tq_text);
/*将停气信息发送至用户手机*/
if tq_text is not null then/*2--*/
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,tq_text,sysdate,sysdate);
end if;/*--2*/
dbms_output.put_line('手机phone_gr '||phone_gr||'phone_dw '||phone_dw);
/*发送费用信息*/
if phone_gr is not null and fee is not null then/*1--*/
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone_gr,'尊敬的居民用户!您本月的天燃气费共'||fee||'元,当前余额'||fee_yu||'元,目前共欠费'||fee_qf||'元!如需帮助请回复BZXX!',sysdate,sysdate);
elsif phone_dw is not null and fee is not null then
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone_dw,'尊敬的单位用户!您本月的天燃气费共'||fee||'元,当前余额'||fee_yu||'元,目前共欠费'||fee_qf||'元!如需帮助请回复BZXX!',sysdate,sysdate);
else
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone_dw,'尊敬的用户!暂时没有当月的相关信息!如需帮助请回复BZXX!',sysdate,sysdate);
end if;/*--1*/
else
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'指令有误!请用您登记的手机号码来查询!如需帮助请回复BZXX!',sysdate,sysdate);
end if;
elsif v_zl='cxwy' or v_zl='CXWY' then /*查询往月费用-------*/
dbms_output.put_line('进入cxwy!');
begin
/*北京方面提供的sql语句beijing---------*/
select nvl(a.费用总金额, 0), NVL(c.余额, 0), NVL(b.总欠费金额, 0) into fee,fee_yu,fee_qf
from (select cbjl_yqzh, SUM(nvl(cbjl.cbjl_sjjfe, 0)) 费用总金额
from hnpy.v_chaobiao_jilu cbjl
where cbjl_cbqijian like time_js||'%' --日期格式YYYYMM 如:201101%
and cbjl_yqzh = v_yhzh --用户号
group by cbjl_yqzh) a
left join
(select cbjl_yqzh,SUM(nvl(cbjl.cbjl_yje + cbjl.cbjl_gwf, 0)) 总欠费金额
from hnpy.v_chaobiao_jilu cbjl
where cbjl_yqzh = v_yhzh --用户号
and cbjl_zt = '4'
group by cbjl_yqzh) b
on a.cbjl_yqzh = b.cbjl_yqzh
left join (select yqdz_userid_old, zjzh.khzj_keyong_yue 余额
from hnpy.v_zijinzhanghu zjzh
left join hnpy.v_yongqidizhi yqdz
on yqdz.yqdz_id = zjzh.khzj_yqdz_id
where yqdz_userid_old = v_yhzh) c --用户号
on a.cbjl_yqzh = c.yqdz_userid_old
left join (select yqdz_userid_old, dwzjzh.dwzj_keyong_yue 余额
from hnpy.v_danweizijinzhanghu dwzjzh
left join hnpy.v_yongqidizhi yqdz
on yqdz.yqdz_kh_id = dwzjzh.kh_id
where yqdz_userid_old = v_yhzh) d --用户号
on a.cbjl_yqzh = d.yqdz_userid_old; /*北京方面提供的sql语句-----------beijing*/
exception
when no_data_found then
fee := null;
fee_qf := null;
fee_yu := null;
end;
dbms_output.put_line('time_js--'||time_js);
/*验证手机号码是否是登记时的手机号码*/
if phone_gr is not null or v_yhzh_gr is not null or phone_dw is not null or v_yhzh_dw is not null then
begin
/*查找停气信息*/
select gas.info_content into tq_text
from dx.gas_info gas
where rownum<2 and sysdate>=gas.start_date and sysdate<=gas.end_date
and gas.area_sid=orgId_gr or gas.area_sid=orgId_dw;
exception
when no_data_found then
tq_text := null;
end;
/*将停气信息发送至用户手机*/
if tq_text is not null then/*2--*/
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,tq_text,sysdate,sysdate);
end if;/*--2*/
/*发送费用信息*/
if phone_gr is not null and fee is not null then/*1--*/
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone_gr,'尊敬的用户!您'||time_js||'月的天燃气费共'||fee||'元,当前余额'||fee_yu||'元,目前共欠费'||fee_qf||'元!如需帮助请回复BZXX!',sysdate,sysdate);
elsif phone_dw is not null and fee is not null then
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone_dw,'尊敬的用户!您'||time_js||'月的天燃气费共'||fee||'元,当前余额'||fee_yu||'元,目前共欠费'||fee_qf||'元!如需帮助请回复BZXX!',sysdate,sysdate);
else
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone_dw,'尊敬的用户!您没有该月的相关信息!如需帮助请回复BZXX!',sysdate,sysdate);
end if;/*--1*/
else
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'指令有误!请用您登记的手机号码来查询!如需帮助请回复BZXX!',sysdate,sysdate);
end if;
elsif v_zl='cxtq' or v_zl='CXTQ' then /*查询停气-------*/
dbms_output.put_line('进入cxtq!');
/*验证手机号码是否是登记时的手机号码*/
if phone_gr is not null or v_yhzh_gr is not null or phone_dw is not null or v_yhzh_dw is not null then
/*查找停气信息内容*/
begin
select gas.info_content into tq_text
from dx.gas_info gas
where rownum<2 and sysdate>=gas.start_date and sysdate<=gas.end_date
and gas.area_sid=orgId_gr or gas.area_sid=orgId_dw;
exception
when no_data_found then
tq_text := null;
end;
/*将停气信息发送至用户手机*/
if tq_text is not null then/*2--*/
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,tq_text,sysdate,sysdate);
else /*没有停气信息*/
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'没有找到近期停气信息!如需帮助请回复BZXX!',sysdate,sysdate);
end if;/*--2*/
else
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'指令有误!请用您登记的手机号码来查询!如需帮助请回复BZXX!',sysdate,sysdate);
end if;
elsif v_zl='dyxx' or v_zl='DYXX' then/*订阅-------*/
dbms_output.put_line('进入dyxx!');
/*验证手机号码是否是登记时的手机号码*/
if phone_gr is not null or v_yhzh_gr is not null or phone_dw is not null or v_yhzh_dw is not null then
/*查询出是否订阅过*/
begin
select r.user_mobile into dy_phone from dx.dy_order r where r.user_mobile=phone ;
exception
when no_data_found then
dy_phone := null;
end;
/*判断是否订阅*/
if dy_phone is not null then
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'您已经订阅!请不要重复订阅!如需帮助请回复BZXX!',sysdate,sysdate);
else/*添加到订阅表,同时发送通知短信*/
insert into dy_order (user_mobile,user_sid,create_date,add_staff)
values (phone,v_yhzh,sysdate,'admin') ;
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'订阅成功!系统将每月定时向您发送您的费用信息!如需帮助请回复BZXX!',sysdate,sysdate);
end if;
else/*不是登记时的手机号码*/
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'指令有误!请用您登记的手机号码查询!如需帮助请回复BZXX!',sysdate,sysdate);
end if;
elsif v_zl='tdxx' or v_zl='TDXX' then /*退订-------*/
dbms_output.put_line('进入tdxx!');
/*验证手机号码是否是登记时的手机号码*/
if phone_gr is not null or v_yhzh_gr is not null or phone_dw is not null or v_yhzh_dw is not null then
/*查询出是否订阅过*/
begin
select r.user_mobile into dy_phone from dx.dy_order r where r.user_mobile=phone and r.user_sid=v_yhzh ;
exception
when no_data_found then
dy_phone := null;
end;
if dy_phone is null then
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'未发现您有订阅信息!如需帮助请回复BZXX!',sysdate,sysdate);
else
/*从订阅表里删除,同时发送通知短信*/
delete from dx.dy_order d where d.user_mobile=phone and d.user_sid=v_yhzh ;
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'退订成功!欢迎再次使用!如需帮助请回复BZXX!',sysdate,sysdate);
end if;
else /*不是登记时的手机号码*/
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'指令有误!请用您登记的手机号码查询!如需帮助请回复BZXX!',sysdate,sysdate);
end if;
elsif v_zl='bzxx' or v_zl='BZXX' then /*帮助-------*/
dbms_output.put_line('进入bzxx!');
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'欢迎使用查询帮助指令!1.查询费用:CXFY#用户号 2.查询往月费用:CXWY#用户号#年年年年月月 3.查询停气信息:CXTQ#用户号 4.订阅信息:DYXX#用户号 5.退订信息:TDXX#用户号',sysdate,sysdate);
else /*其他的指令-------*/
dbms_output.put_line('进入其他!');
insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
values (1,phone,'指令有误!如需帮助请回复BZXX!',sysdate,sysdate);
end if;
end;
相关推荐
用于阶梯价格的,水费、电费、燃气费的自动计算器
[C#]自己写的合租水电煤气费计算程序.rar
因为这个代码是建立在郑州燃气项目中的最新框架代码,呵呵。 2、数据库连接信息写在n_connect_db里面,自己修改,或者你的用户名是zzgas密码seastar,实例名或连接别名zzgasmydb。或者你直接建立一个名为zzgasmydb的...
数据库课程设计 燃气气费收费管理系统
电信设备-短信燃气报警系统.zip
一种非民用智能燃气表费控方式的切换方法及装置 .pdf
电信设备-一种带短信通讯的智能燃气表.zip
电信设备-短信燃气报警系统及其报警方法.zip
电信设备-一种基于GSM模块发短信的燃气检测与报警装置.zip
燃气费管理系统,2M左右的大小,功能比几十M的同类软件还强大,你想到的功能她有,你没想到的功能她也有,界面美观,流程清晰,一看就懂,一用就会。用户交费时女声提示欠费金
燃气壁挂锅炉最大的优点是可以任意调节不同居室的温度,那么如何在方便使用的基础上让其做到省气省钱,这就与智能温度控制器有关系了。 在欧洲,温控器一般是壁挂炉的必配件,两者一配一的同时交付用户,而且配备的...
并配备烟雾传感器和燃气泄漏传感器,实现防火、防燃气泄漏的作用。 关键词:单片机 GSM模块 传感器 基于GSM短信模块的家庭防盗报警系统.doc 基于GSM短信模块的家庭防盗报警系统.doc 基于GSM短信模块的家庭防盗报警...
QR票据 QR扫描仪,用于支付电费,燃气费和水费。 使用复制/粘贴功能输出格式化的数据。
城镇燃气管道设计规范最新版本 燃气的保护 间距 埋深
燃气新兴技术.pptx
燃气轮机
通用-室内燃气管道
一个协助小区房产物业部门收取业主物业费、电费、水费、燃气费、取暖费、热水费、燃气费、停车费、租金等各项物业费用进行全面管理的系统。通过简单录入房屋基本信息,很方便的把房屋基本信息复制到收费信息中,减少...
基于微信小程序的燃气数据远程监控查询系统
燃气管道强度试验报告(表格模板、XLS格式).XLS