模板消息推送
功能描述
生成各种类型推送消息，并推送到微信公众号
项目：
weixin-manage-client组件：
MsgPushManage组件别名：
msg-push-manage
功能权限
需要给角色授权
欠费推送/停气推送/账单推送/余额不足提醒的功能权限
相关表
t_template 微信模板表(需要在公众号选 择) t_template_expression 模板 Id 表达式表 t_template_push 模板推送表
主要字段说明
t_template
模板 id f_template_id 模板标题 f_title 模板内容 f_data_content 模板示例 f_example 分公司 id f_orgid 分公司文件夹名 f_orgname(与微信后台文件夹名一致)
t_template_expression 模板 id f_template_id 分公司 id f_orgid 字体颜色 f_mark 推送模板的字段 f_key 对应的数据库字段 f_value
t_template_push 微信 openid f_open_id 模板 id f_template_id 模板消息数据包 f_data 发送状态 f_send_state(未推送/推送成功/推送失败) 发送时间 f_send_date 生成时间 f_create_date 推送类型 f_send_type(欠费推送/停气推送/账单推送/余额不足提醒/...) 用户姓名 f_user_name 用户编号 f_userinfo_id 表档案 id f_userfiles_id 分公司 id f_orgid 推送随机编号 f_push_number(台生成推送数据后, 调用推送服务的条件) 用户点击模板消息时跳转的链接 f_url
##欠费推送(getOweUser.sql)
查询sql
select * from (
select
CONVERT(varchar(7), GETDATE(), 23) senddate,
f_userinfo_code, --客户编号
f_userinfo_id, --户编号
f_userfiles_id,
f_user_name, --用户姓名
f_user_type,
f_gasproperties,
f_residential_area, --小区
f_zones,
f_open_id,
f_inputtor,
--CONVERT(varchar(10),f_hand_date, 23)f_last_hand_date,
f_address, --地址
f_user_phone, --用户电话
f_rent_phone, --备用电话
f_comments, --备注
f_orgid,
count(*) c, --欠费次数
min(f_hand_date) aaa,
max(f_hand_date) bbb,
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(isnull(f_oughtfee,0)) f_oughtfee_new, --气费金额
sum(isnull(overdue,0)) overdue,--滞纳金
sum(isnull(f_garbage_fee,0)) f_garbage_fee,--附加费
cast(cast(SUM(f_oughtfee+overdue+f_garbage_fee) as decimal(18,2)) as varchar) f_oughtfee_all
from (
SELECT
handplan_id, --抄表记录id
f_last_tablebase, --上期指数
f_tablebase, --本期指数
f_hand_date, --抄表日期
f_input_date,
f_userinfo_id,
f_user_id,
f_open_id,
f_userinfo_code,
f_user_name,
f_address,
f_residential_area,
f_slice_area as f_zones,
f_user_phone, --用户电话
f_rent_phone, --备用电话
f_comments, --备注
f_user_type,
f_inputtor,
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, --截止日期
CASE WHEN overduedays<1 THEN 0 ELSE overduedays END overduedays, --滞纳天数
f_balance, --余额
--滞纳天数小于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 f_balance<0 then 0 else f_balance end ))<0
THEN
0
ELSE
--判断应交金额是否小于0
(CASE WHEN overduedays*zhinajinbilv*(f_oughtfee-f_debt_money -(case when f_balance<0 then 0 else f_balance end ))>=f_oughtfee-f_debt_money -(case when f_balance<0 then 0 else f_balance 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 f_balance<0 then 0 else f_balance end ))
END)
END
END) overdue,
minid,
case when f_garbage_fee = '是' and f_hand_date >= '2017-08-01' then
(case when handplan_id = minid or minid is null then f_lajifei else 0 end)
else 0 end
f_garbage_fee,
f_depid,f_depname,f_orgid,f_orgname,f_operatorid,f_operator
FROM(
SELECT
h.id as handplan_id,
f_last_tablebase,
f_tablebase,
h.f_hand_date,
h.f_input_date,
f_oughtamount,
f_oughtfee,
b.f_open_id,
h.f_user_type,
h.f_gasproperties,
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,
f_surplus_gas,
f_haircut_gas,
f_share_gas,
f_totalowe_fee,
f_totalowe_gas,
h.f_overdue,
h.f_userinfo_id,
h.f_inputtor,
u.f_balance,
uf.f_garbage_fee,
ua.f_residential_area, --小区
ua.f_slice_area,
u.f_user_phone, --用户电话
u.f_rent_phone, --备用电话
u.f_comments, --备注
DATEDIFF(dd,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,h.f_operatorid,h.f_operator
FROM t_handplan h
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
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 t_banduser b on h.f_userinfo_id=b.f_userinfo_id
left join t_userfiles uf on h.f_userfiles_id = uf.f_userfiles_id
left join t_user_address ua on u.f_userinfo_id = ua.f_userinfo_id
WHERE
f_whether_pay='否' AND f_hand_state = '有效' and
h.f_meter_state = '已抄表' AND f_oughtfee>0
) p
left join(
select f_userfiles_id,CONVERT(varchar(7), f_hand_date, 23) f_date,min(id) minid
from t_handplan
where f_hand_state = '有效' and f_meter_state = '已抄表' AND f_oughtfee>0
group by CONVERT(varchar(7), f_hand_date, 23),f_userfiles_id
) gh on CONVERT(varchar(7), p.f_hand_date, 23) = gh.f_date and p.f_userfiles_id = gh.f_userfiles_id
) h
where f_hand_date>='1900-01-01' and f_hand_date<=GETDATE()
group by f_userinfo_code,f_userinfo_id,f_user_name,f_zones,f_inputtor,f_open_id,f_userfiles_id,
f_user_type,f_gasproperties,f_residential_area,f_address,f_user_phone,f_rent_phone,f_comments,f_orgid
) s where {condition} and (f_oughtfee_new+overdue+f_garbage_fee)>0 and f_open_id is not null
order by f_userinfo_code
按钮操作
生成推送信息
选择需要生成的推送类型，先查询信息，在点击生成推送数据，选择相应的模板，生成推送信息
推送信息
在右侧栏中，先查询未推送信息，在点击推送