新短信管理(针对阿里云)
功能描述
查询并生成短信生成的短信，支持短信模板修改
项目：
saleClient组件：
MessageManageNew组件别名：
message-manage-new
查询条件
无特殊，不做阐述
查询列表
没有特殊 不做阐述
查询sqL（salemodel中）
select
u.f_userinfo_code, --客户编号
u.f_userinfo_id, --户编号
u.f_user_name,
u.f_user_phone,
uf.f_user_type, --用户类型
ua.f_address,
f_meter_classify, --气表类型
f_pcd, --区域
uf.f_gasproperties, -- 用气性质
isnull(u.f_balance,0) f_balance, --余额
isnull(h.f_last_tablebase,0) f_last_tablebase, --上期指数
isnull(h.f_tablebase,0) f_tablebase, --本期指数
h.f_hand_date, --抄表日期
isnull(h.f_oughtamount,0) f_oughtamount, --用气量
isnull(h.f_oughtfee,0) f_oughtfee, --用气金额
isnull(h.f_debt_money,0) f_debt_money, --已交金额
isnull(h.f_oughtfee_new,0) f_oughtfee_new, --气费金额
isnull(h.overdue,0) overdue, --滞纳金
isnull(h.f_oughtfee_all,0) f_oughtfee_all, --合计欠费金额
case when h.f_balance >= f_oughtfee_all then 0 else f_oughtfee_all-h.f_balance end f_oughtfee_col
from t_userinfo u left join t_userfiles uf on u.f_userinfo_id = uf.f_userinfo_id
left join t_user_address ua on u.f_userinfo_id=ua.f_userinfo_id
left join
(
select
f_userinfo_code, --客户编号
f_userinfo_id, --户编号
f_balance,
(SELECT f_last_tablebase from t_handplan where id= MIN(h.handplan_id)) f_last_tablebase,
(select f_tablebase from t_handplan where id = max(h.handplan_id)) f_tablebase,
--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, --已交金额
sum(f_oughtfee) f_oughtfee_new, --气费金额
sum(alloverdue) overdue,--滞纳金
sum(f_oughtfee)+sum(alloverdue) f_oughtfee_all --合计欠费金额
from (
SELECT
handplan_id, --抄表记录id
f_last_tablebase, --上期指数
f_tablebase, --本期指数
f_hand_date, --抄表日期
f_userinfo_id,
f_userinfo_code,
p.f_userfiles_id,
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)
CASE WHEN overduedays<1 THEN 0 ELSE overduedays END overduedays, --滞纳天数
f_balance, --余额
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
FROM
(
SELECT
h.id as handplan_id,
h.f_last_tablebase,
h.f_tablebase,
h.f_hand_date,
h.f_oughtamount,
h.f_oughtfee,
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,
h.f_end_date,
h.f_userinfo_code,
h.f_userfiles_id,
h.f_overdue,
h.f_userinfo_id,
lr.f_latefee_ought,
lr.zhengchang,
lr.jianmian,
(case when u.f_balance<0 then 0 else u.f_balance end) f_balance,
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=h.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=h.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
FROM t_handplan h
left join t_userinfo u on h.f_userinfo_id=u.f_userinfo_id
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,
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 (
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_hand_date>='{startDate}'
and h.f_hand_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
) h
group by f_userinfo_code,f_userinfo_id,f_balance
) h on u.f_userinfo_id=h.f_userinfo_id
where 1=1 and f_table_state='正常' and {condition}
order by u.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
导出指定条件的短信
注意
新建模板要配置模板ID要与短信平台ID一致不然无法发送短信 选择模板处有自动短信开启与停止（只有在后台配置了该类自动短信才能生效）