微信对账
功能描述
微信对账，可在明细查询中进行补缴和标记退费
项目：
weixin-manage-client组件：
组件别名：
we-chat
查询条件
没有特殊 不做阐述
查询列表
汇总页面主要查询sql
select f_orgname,
CONVERT(VARCHAR(10), f_time_end, 120) f_time_end,
Convert(decimal(18, 2), sum(f_total_fee)) f_total_fee,
Convert(decimal(18, 2), sum(f_collection)) f_collection
from (SELECT isnull((w.f_total_fee + 0.00) / (100 + 0.00), 0) f_total_fee,
w.f_attach,
w.id,
w.f_order_state,
w.f_order_type,
w.f_trade_type,
w.f_operator,
CONVERT(VARCHAR(10), isnull(w.f_time_ends, w.f_create_time), 120) f_time_end,
w.f_transaction_id,
isnull(w.f_userinfo_code, s.f_userinfo_code) f_userinfo_code,
isnull(w.f_userinfo_id, s.f_userinfo_id) f_userinfo_id,
isnull(w.f_userfiles_id, s.f_userfiles_id) f_userfiles_id,
isnull(w.f_meter_classify, s.f_meter_type) f_meter_type,
isnull(w.f_user_name, s.f_user_name) f_user_name,
s.f_orgid f_orgid,
s.f_orgname f_orgname,
s.f_type,
s.f_serial_id,
s.f_collection,
s.f_operate_date,
s.f_depname,
s.f_depid,
s.f_operatorid
FROM (SELECT wx.*,
CAST(convert(varchar(100), SUBSTRING(f_time_end, 1, 4) + '-' + SUBSTRING(f_time_end, 5, 2) + '-' +
SUBSTRING(f_time_end, 7, 2) + ' ' + SUBSTRING(f_time_end, 9, 2) + ':' +
SUBSTRING(f_time_end, 11, 2) + ':' + SUBSTRING(f_time_end, 13, 2),
23) as datetime) f_time_ends,
uf.f_meter_classify,
uf.f_userinfo_id,
ui.f_userinfo_code,
ui.f_user_name
FROM t_weixinreturnxml wx
LEFT JOIN t_userfiles uf ON wx.f_userfiles_id = uf.f_userfiles_id
LEFT JOIN t_userinfo ui ON uf.f_userinfo_id = ui.f_userinfo_id
LEFT JOIN t_organization org ON org.id = wx.f_orgid
WHERE wx.f_order_state = '已支付'
and wx.f_transaction_id is not null
and wx.f_transaction_id <> '') w
left join (SELECT s.f_serial_id,
s.f_userinfo_id,
s.f_userfiles_id,
s.f_meter_type,
s.f_user_name,
s.f_orgid,
s.f_orgname,
s.f_depid,
s.f_operatorid,
s.f_operator,
s.f_depname,
s.f_type,
s.f_collection,
s.f_operate_date,
ui.f_userinfo_code
FROM t_sellinggas s
LEFT JOIN t_userinfo ui ON s.f_userinfo_id = ui.f_userinfo_id
WHERE s.f_state = '有效'
union all
-- 其他收费
SELECT s.f_serial_id,
s.f_userinfo_id,
s.f_userfiles_id,
'其他收费' f_meter_type,
ui.f_user_name,
s.f_orgid,
s.f_orgname,
s.f_depid,
s.f_operatorid,
s.f_operator,
s.f_depname,
'其他收费' f_type,
s.f_collection,
s.f_operate_date,
ui.f_userinfo_code
FROM t_othercharge s
LEFT JOIN t_userinfo ui ON s.f_userinfo_id = ui.f_userinfo_id
WHERE s.f_state = '有效') s on w.f_transaction_id = s.f_serial_id) tt
where {condition} and f_time_end >='{startDate}' and f_time_end <='{endDate}'
GROUP BY f_time_end, f_orgname
order by f_orgname, f_time_end desc
明细页面主要查询sql
select tt.*
from (SELECT (CASE
WHEN s.f_serial_id IS NULL AND w.transaction_id IS NOT NULL THEN '微信单方面账单'
WHEN s.f_serial_id IS NOT NULL AND w.transaction_id IS NULL THEN '燃气方单方面账单'
else '对账成功'
END) msg,
isnull((w.f_total_fee + 0.00) / (100 + 0.00), 0) f_total_fee,
w.f_attach,
w.id,
w.f_order_state,
w.f_order_type,
w.f_trade_type,
isnull(w.f_time_ends, w.f_create_time) f_datatime,
w.transaction_id f_transaction_id,
isnull(w.f_userinfo_code, s.f_userinfo_code) f_userinfo_code,
isnull(w.f_userinfo_id, s.f_userinfo_id) f_userinfo_id,
isnull(w.f_userfiles_id, s.f_userfiles_id) f_userfiles_id,
isnull(w.f_meter_classify, s.f_meter_type) f_meter_type,
isnull(w.f_user_name, s.f_user_name) f_user_name,
s.f_orgid f_orgid,
s.f_orgname f_orgname,
w.f_meternumber,
w.f_address,
s.f_depid f_depid,
s.f_operatorid f_operatorid,
s.f_operator f_operator,
s.f_depname f_depname,
s.f_type,
s.f_serial_id,
s.f_collection,
s.f_operate_date
FROM (SELECT (CASE
WHEN wx.flag = 'MicroPayShunMing' THEN wx.f_out_trade_no
else wx.f_transaction_id
END) transaction_id,
wx.*,
CAST(convert(varchar(100), SUBSTRING(f_time_end, 1, 4) + '-' + SUBSTRING(f_time_end, 5, 2) + '-' +
SUBSTRING(f_time_end, 7, 2) + ' ' + SUBSTRING(f_time_end, 9, 2) + ':' +
SUBSTRING(f_time_end, 11, 2) + ':' + SUBSTRING(f_time_end, 13, 2),
23) as datetime) f_time_ends,
uf.f_meter_classify,
uf.f_userinfo_id,
uf.f_meternumber,
ui.f_userinfo_code,
ui.f_user_name,
address.f_address
FROM t_weixinreturnxml wx
LEFT JOIN t_userfiles uf ON wx.f_userfiles_id = uf.f_userfiles_id
LEFT JOIN t_userinfo ui ON uf.f_userinfo_id = ui.f_userinfo_id
left join t_user_address address on uf.f_userinfo_id = address.f_userinfo_id
LEFT JOIN t_organization org ON org.id = wx.f_orgid
WHERE wx.f_order_state in ('已支付', '退款中')
and (wx.f_transaction_id is not null or wx.f_out_trade_no is not null)) w
left join (SELECT s.f_serial_id,
s.f_userinfo_id,
s.f_userfiles_id,
s.f_meter_type,
s.f_user_name,
s.f_orgid,
s.f_orgname,
s.f_depid,
s.f_operatorid,
s.f_operator,
s.f_depname,
s.f_type,
s.f_collection,
s.f_operate_date,
ui.f_userinfo_code
FROM t_sellinggas s
LEFT JOIN t_userinfo ui ON s.f_userinfo_id = ui.f_userinfo_id
WHERE s.f_state = '有效'
union all
-- 其他收费
SELECT s.f_serial_id,
s.f_userinfo_id,
s.f_userfiles_id,
'其他收费' f_meter_type,
ui.f_user_name,
s.f_orgid,
s.f_orgname,
s.f_depid,
s.f_operatorid,
s.f_operator,
s.f_depname,
'其他收费' f_type,
s.f_collection,
s.f_operate_date,
ui.f_userinfo_code
FROM t_othercharge s
LEFT JOIN t_userinfo ui ON s.f_userinfo_id = ui.f_userinfo_id
WHERE s.f_state = '有效') s on w.transaction_id = s.f_serial_id) tt
where {condition}
ORDER BY f_datatime DESC
标记已退款
当订单状态处于
微信单方面账单账单时，可以进行标记已退款操作，即把中间表微信订单状态修改成已退款，在系统外退款的情况下使用。
缴费
当订单状态处于
微信单方面账单账单时，可以进行缴费操作，需要微信缴费权限,最新版已经移除该权限控制，因为大部分情况下拥有页面权限得操作人都可以进行缴费。