短信管理
功能描述
查询并生成短信生成的短信，支持短信模板修改
项目：
saleClient组件：
MessageManage组件别名：
message-manage
查询条件
| 条件描述 | 实际取值字段 | 查询 sql |
|---|---|---|
| 用户状态 | 无 | 欠费：欠费查询 ；全部为全部查询 |
其他无特殊，不做阐述
查询列表
没有特殊 不做阐述
用户状态为欠费时的查询sql(managemodel中)
select
*,
case when f_balance > 0
then
case when f_balance >= f_oughtfee_all then 0 else f_oughtfee_all-f_balance end
else f_oughtfee_all
end f_oughtfee_col
from (
select
f_meter_classify,
f_olduserinfo_code,
f_userinfo_code, --客户编号
f_userinfo_id, --户编号
f_user_name, --用户姓名
f_meter_book_sort,
f_book_no,
f_cost_type,
f_user_type,
f_gasproperties,
f_residential_area, --小区
f_zones,
f_meter_book_num,
f_meter_classify f_meter_type,
f_dainputtores,
f_book_name,
f_inputtor,
f_price_name,
f_address, --地址
f_user_phone, --用户电话
f_rent_phone, --备用电话
f_comments, --备注
f_depid,f_depname,f_orgid,f_orgname,
(
select f_tablebase from t_handplan where id = max(h.handplan_id)
) f_tablebase,
convert(varchar(100),max(f_operate_date),111) f_operate_date, -- 缴费时间
count(*) c, --欠费次数
case when CONVERT(varchar(7), min(f_hand_date), 23)=CONVERT(varchar(7), max(f_hand_date), 23)
then CONVERT(varchar(7), min(f_hand_date), 23)
else CONVERT(varchar(7), min(f_hand_date), 23)+'至'+CONVERT(varchar(7), max(f_hand_date), 23) end f_hand_date, --欠费区间
sum(f_oughtamount) f_oughtamount, --用气量
sum(f_oughtfee_all) f_oughtfee, --用气金额
sum(isnull(f_debt_money,0)) f_debt_money, --已交金额
convert(decimal(18,2),sum(isnull(f_debt_money,0))/sum(f_oughtfee_all)*100) f_hand_bv, --回收率
sum(f_oughtfee) f_oughtfee_new, --气费金额
--sum(overdue) overdue,--滞纳金
sum(alloverdue) overdue,
sum(f_garbage_fee) f_garbage_fee,--附加费
(SELECT f_last_tablebase from t_handplan where id= MIN(h.handplan_id)) f_last_tablebase,
f_operatorid,
f_balance,
f_sendsums,
sum(f_oughtfee)+sum(alloverdue)+sum(f_garbage_fee) f_oughtfee_all --合计欠费金额
from (
SELECT
f_olduserinfo_code,
f_meter_book_sort,
f_book_no,
handplan_id, --抄表记录id
f_sell_id,
f_last_tablebase, --上期指数
f_tablebase, --本期指数
f_hand_date, --抄表日期
f_input_date,
f_userinfo_id,
f_user_id,
f_userinfo_code,
f_user_name,
f_address,
f_residential_area,
f_meter_classify,
f_slice_area as f_zones,
f_user_phone, --用户电话
f_rent_phone, --备用电话
f_comments, --备注
f_user_type,
f_cost_type,
f_inputtor,
f_meter_book_num,
f_dainputtores,
f_book_name,
f_gasproperties,
p.f_userfiles_id,
f_surplus_gas,
f_haircut_gas,
f_oughtamount, --应交气量 convert(decimal(18,2),f_oughtamount) ROUND(f_oughtamount,2)
f_oughtfee f_oughtfee_all,
ROUND(f_oughtfee-f_debt_money,2) f_oughtfee, --实际应交金额 convert(decimal(18,2),f_oughtfee-f_debt_money) ROUND(f_oughtfee-f_debt_money,2)
f_debt_money, --已交气费 convert(decimal(18,2),f_debt_money) ROUND(f_debt_money,2)
f_charge_money, --已交总金额 convert(decimal(18,2),f_charge_money) ROUND(f_charge_money,2)
f_selloverdue, --已交滞纳金 convert(decimal(18,2),f_selloverdue) ROUND(f_selloverdue,2)
f_allfee, --总应交金额 convert(decimal(18,2),f_oughtfee) ROUND(f_oughtfee,2)
f_totalowe_gas, -- convert(decimal(18,2),f_totalowe_gas) ROUND(f_totalowe_gas,2)
f_totalowe_fee, -- convert(decimal(18,2),f_totalowe_fee) ROUND(f_totalowe_fee,2)
f_whether_pay, --是否缴费
f_end_date, --截止日期
f_price_name,
CASE WHEN overduedays<1 THEN 0 ELSE overduedays END overduedays, --滞纳天数
f_balance, --余额
f_last_balance,
f_operate_date, -- 缴费时间
--滞纳天数小于0，不计算滞纳金，返回0
convert(decimal(18,3),
CASE WHEN overduedays<=0 THEN
0
ELSE
--滞纳金天数大于0，若应交金额小于0
CASE WHEN overduedays*zhinajinbilv*(f_oughtfee-f_debt_money -(case when mh.minid is null then 0 else (case when f_balance<0 then 0 else f_balance end) end ))<0
THEN
0
ELSE
--判断应交金额是否小于0
(CASE WHEN overduedays*zhinajinbilv*(f_oughtfee-f_debt_money -(case when mh.minid is null then 0 else (case when f_balance<0 then 0 else f_balance end) end))>=f_oughtfee-f_debt_money -(case when mh.minid is null then 0 else (case when f_balance<0 then 0 else f_balance end) end)
THEN
CASE WHEN f_oughtfee-f_debt_money -f_balance<0
THEN 0
ELSE f_oughtfee-f_debt_money -f_balance
END
ELSE
overduedays*zhinajinbilv*(f_oughtfee-f_debt_money -(case when mh.minid is null then 0 else (case when f_balance<0 then 0 else f_balance end) end))
END)
END
END) overdue,
convert(decimal(18,2),
CASE WHEN overduedays<=0 THEN
isnull(f_latefee_ought,0)
ELSE
--滞纳金天数大于0，若应交金额小于0
CASE WHEN overduedays*zhinajinbilv*(f_oughtfee-f_debt_money -(case when mh.minid is null then 0 else f_balance end))<=0
THEN
isnull(f_latefee_ought,0)
ELSE
--判断应交金额是否小于0
(CASE WHEN overduedays*zhinajinbilv*(f_oughtfee-f_debt_money -case when mh.minid is null then 0 else f_balance end)+isnull(zhengchang,0)>=
f_oughtfee-f_debt_money -(case when mh.minid is null then 0 else f_balance end)
THEN
CASE WHEN f_oughtfee-f_debt_money -(case when mh.minid is null then 0 else f_balance end)<0
THEN 0
ELSE
case when f_oughtfee-f_debt_money -(case when mh.minid is null then 0 else f_balance end)-isnull(jianmian,0)<=0
then 0
else f_oughtfee-f_debt_money -(case when mh.minid is null then 0 else f_balance end)-isnull(jianmian,0)
end
END
ELSE
overduedays*zhinajinbilv*(f_oughtfee-f_debt_money -(case when mh.minid is null then 0 else f_balance end))+isnull(f_latefee_ought,0)
END)
END
END) alloverdue,
0 f_garbage_fee,
f_sendsums,
f_depid,f_depname,f_orgid,f_orgname,f_operatorid,f_operator
FROM(
SELECT
h.id as handplan_id,
h.f_sell_id,
h.f_last_tablebase,
h.f_tablebase,
h.f_hand_date,
h.f_input_date,
h.f_oughtamount,
h.f_oughtfee,
h.f_user_type,
h.f_gasproperties,
h.f_meter_classify,
isnull(f_debt_money,0) f_debt_money,
isnull(f_charge_money,0) f_charge_money,
isnull(f_selloverdue,0) f_selloverdue,
f_oughtfee f_allfee,
f_whether_pay,
f_loss_gas,
h.f_end_date,
h.f_user_id,
h.f_userinfo_code,
h.f_user_name,
h.f_address,
h.f_userfiles_id,
h.f_surplus_gas,
h.f_haircut_gas,
f_share_gas,
f_totalowe_fee,
f_totalowe_gas,
h.f_overdue,
h.f_userinfo_id,
mb.f_inputtor,
case when u.f_balance<0 then 0 else u.f_balance end f_balance,
uf.f_garbage_fee,
uf.f_meter_book_num,
uf.f_meter_book_sort,
mb.f_book_name,
ua.f_residential_area, --小区
ua.f_slice_area,
u.f_user_phone, --用户电话
u.f_olduserinfo_code, --用户电话
u.f_rent_phone, --备用电话
u.f_comments, --备注
u.f_cost_type,
u.f_book_no,
sh.f_last_balance, -- 上期余额
ss.f_operate_date,
sp.f_price_name,
lr.f_latefee_ought,
lr.zhengchang,
lr.jianmian,
isnull(f_sendsums,0) f_sendsums,
DATEDIFF(dd,h.f_end_date,getdate()) overduedays, --滞纳天数 sql server
CASE WHEN h.f_user_type='民用'
THEN isnull((SELECT cast(value as float) FROM t_singlevalue WHERE name='民用违约金' and f_filialeids=u.f_orgid),(SELECT cast(value as float) FROM t_singlevalue WHERE name='民用违约金'))
ELSE
isnull((SELECT cast(value as float) FROM t_singlevalue WHERE name='非民用违约金' and f_filialeids=u.f_orgid),(SELECT cast(value as float) FROM t_singlevalue WHERE name='非民用违约金'))
END zhinajinbilv,
(SELECT cast(value as int) FROM t_singlevalue WHERE name='垃圾费') f_lajifei,
h.f_depid,h.f_depname,h.f_orgid,h.f_orgname,uf.f_operatorid,h.f_operator,uf.f_inputtor as f_dainputtores
FROM t_handplan h
LEFT JOIN t_sellinggas ss on ss.id=h.f_sell_id
LEFT JOIN (
SELECT
f_hand_id,SUM(f_debt_money) f_debt_money,
(SELECT f_last_balance from t_sellinghand where id=min(d.id)) f_last_balance,
SUM(f_charge_money) f_charge_money,SUM(f_overdue) f_selloverdue
FROM t_sellinghand d
WHERE f_state = '有效'
GROUP BY f_hand_id
) sh on h.id = sh.f_hand_id
LEFT JOIN t_userinfo u ON h.f_userinfo_id = u.f_userinfo_id
left join (
select f_userinfo_id,count(f_userinfo_id) f_sendsums
from t_sms where f_send_type = '欠费通知'
and f_createdate>='{startDate1}'
and f_createdate<='{endDate1}'
GROUP BY f_userinfo_id) sms
on U.f_userinfo_id = sms.f_userinfo_id
left join t_userfiles uf on h.f_userfiles_id = uf.f_userfiles_id
left join (select f_price_name,id from t_stairprice) sp on cast(h.f_stairprice_id as varchar) = cast(sp.id as varchar)
left join t_meter_book mb on uf.f_meter_book_num=mb.id
left join t_user_address ua on u.f_userinfo_id = ua.f_userinfo_id
left join (
select f_hand_id,sum(f_latefee_ought) f_latefee_ought,sum(jianmian) jianmian,sum(zhengchang) zhengchang
from (
select
f_hand_id, f_latefee_ought,
case when f_latefee_ought<0 then 0-f_latefee_ought else 0 end jianmian,
case when f_latefee_ought>0 then f_latefee_ought else 0 end zhengchang
from t_latefee_record where f_state='有效'
) l group by f_hand_id
) lr on h.id = lr.f_hand_id
WHERE f_whether_pay='否' AND f_hand_state = '有效' and h.f_meter_state = '已抄表' AND f_oughtfee>0
and h.f_audit_date>='{startDate}'
and h.f_audit_date<='{endDate}'
) p
left join (
select f_userfiles_id,min(id) minid from t_handplan
where f_hand_state = '有效' and f_meter_state = '已抄表' AND f_oughtfee>0 and f_whether_pay='否'
group by f_userfiles_id
) mh on p.handplan_id = mh.minid and p.f_userfiles_id = mh.f_userfiles_id
where {condValue}
) h
group by f_userinfo_code,f_olduserinfo_code,f_meter_book_sort,f_book_no,f_userinfo_id,f_user_name,
f_zones,f_inputtor,f_price_name,f_operatorid,f_cost_type,f_dainputtores,f_user_type,f_gasproperties,
f_residential_area,f_address,f_user_phone,f_rent_phone,f_comments,f_depid,f_depname,f_orgid,
f_orgname,f_meter_book_num,f_book_name,f_meter_classify,f_balance,f_sendsums
) s
where {condition}
order by f_userinfo_code
用户状态为正常时的查询sqL（salemodel中）
SELECT * FROM(
select
u.f_userinfo_code,
u.f_user_name,
u.f_user_phone,
u.f_createfile_date,
u.f_user_state,
uf.f_meter_base,
case when uf.f_meter_classify = '物联网表' then uf.f_balance_amount else u.f_balance end f_balance,
u.f_orgid,
address.f_address,
address.f_residential_area,
address.f_building,
address.f_pcd,
address.f_unit,
address.f_room,
uf.f_total_gas,
uf.f_gasproperties,
uf.f_install_date,
uf.f_user_type,
uf.f_total_fee,
gb.f_meter_brand,
uf.f_userfiles_id,
gm.f_meter_style,
isnull(f_sendsums,0) f_sendsums
from(select * from t_userfiles where f_table_state = '正常') uf
LEFT JOIN t_user_address address on uf.f_useraddress_id = address.id
left join t_userinfo u on u.f_userinfo_id = uf.f_userinfo_id
LEFT JOIN t_gasbrand gb on uf.f_gasbrand_id = gb.id
LEFT JOIN t_gasmodel gm on uf.f_gasmodel_id = gm.id
left join (select f_userinfo_id,count(f_userinfo_id) f_sendsums
from t_sms where {condValue} GROUP BY f_userinfo_id) sms
on uf.f_userinfo_id = sms.f_userinfo_id
) tt
where {condition}
order by f_userinfo_code
短信信息查询sql
select * from (
select
i.*,
1 as sumsend,
case when f_send='未发送' then 1 else 0 end unsend,
case when f_send='待发送' then 1 else 0 end tosend,
case when f_send='提交成功' then 1 else 0 end hassend,
case when f_send='提交失败' then 1 else 0 end failsend,
CONVERT(varchar(100), i.f_arrears_date, 102) f_arr_date,
CONVERT(varchar(100), i.f_createdate, 102) f_create_date,
u.f_residential_area,
u.f_unit,
u.f_building,
u.f_userinfo_code code,
u.f_userinfo_code ,
u.f_user_type,
u.f_user_phone
from t_sms i
left join (
select ua.id,ua.f_residential_area,ua.f_unit,ua.f_building,ua.f_floor,ua.f_room
,uf.f_userinfo_id,uf.f_user_type,u.f_userinfo_code f_userinfo_code,u.f_user_phone f_user_phone,u.f_user_state
from (select f_userinfo_id,f_user_type from t_userfiles where f_table_state!='换表' group by f_userinfo_id,f_user_type) uf
left join t_userinfo u on uf.f_userinfo_id=u.f_userinfo_id
left join t_user_address ua on uf.f_userinfo_id=ua.f_userinfo_id
where u.f_user_state = '正常'
) u on i.f_userinfo_id = u.f_userinfo_id
) i where {condition}
order by f_createdate desc
按钮操作
新增模板（用户状态为全部）
新增一个短信模板
选择模板（用户状态为全部）
选择指定内容的模板进行批量生成短信（只能选择一个短信模板）
生成短信（用户状态为欠费）
对欠费用户进行进行批量生成欠费短信
发送
将指定短信改为待发送，交由短信后台扫描发送（截止日期大于此时的短信不再发送）
删除
删除指定短信
删除全部
删除全部短信
导出 excel
导出指定条件的短信
配置说明
system 模块中 vues 目录下 arrearslist.json 文件中配置欠费短信内容
{
"msg": “case when f_user_type = '民用' then '温馨提醒：缴费号['+f_userinfo_code+']用户您好！您位于['+f_address+']用气地燃气['+cast(f_oughtfee_all as varchar)+']元，请您在每月1-10日到营业厅或通过微信公众号（hbgnrg）和支付宝进行缴费！'
case when f_user_type = '非民用' then '温馨提醒：您的['+f_userinfo_code+']['+f_address+']['+'账期'+']天然气费['+cast(f_oughtfee_all as varchar)+']元，累计应交气费['+cast(f_oughtfee_all as varchar)+']元，请于每月28-30日到营业网点缴费，逾期气费将按每日0.3%计收违约金' end
”
}