跳到主要内容

微信对账

功能描述​

将微信公众号下单记录中支付成功的记录与营收中生成的收费记录进行对比，将对比无误或对比存在问题的记录进行展示，并赋予退款、缴费等处理功能

项目：weixin-manage-client

组件：WeChat

组件别名：we-chat

功能权限​

需要给角色授权 微信缴费权限/微信退款权限 的功能权限

相关表​

t_userinfo 用户档案表 t_userfiles 表档案表 t_sellinggas 燃气收费记录表 t_otherchange 其他收费记录表 t_weixinreturn 微信中间表

主要字段说明​

主要字段说明​

t_userinfo

用户编号 f_userinfo_code 用户姓名 f_user_name 用户 id f_userinfo_id

t_userfiles

用户类型 f_user_type 表具编号 f_meternumber 表具 id f_userfiles_id 表具类型 f_meter_classify

t_weixinreturnxml

订单类型 f_order_type 订单流水号 f_transaction_id 订单金额(单位:分) f_total_fee 订单自定义信息 f_attach 中间表 id id 订单状态 f_order_state 交易类型 f_trade_type 订单创建时间 f_create_time 组织机构 id f_orgid 交易结束时间 f_time_ends

t_sellinggas

交易流水号 f_serial_id 用户 id f_userinfo_id 表具 id f_userfiles_id 用户姓名 f_user_name 用户类型 f_user_type 组织机构 id f_orgid 组织机构名 f_orgname 部门 id f_depid 部门名称 f_depname 操作员 id f_operatorid 操作员姓名 f_operator 交易金额 f_collection 操作时间 f_operate_date

t_othercharge

交易流水号 f_serial_id 用户 id f_userinfo_id 表具 id f_userfiles_id 用户姓名 f_user_name 用户类型 f_user_type 组织机构 id f_orgid 组织机构名 f_orgname 部门 id f_depid 部门名称 f_depname 操作员 id f_operatorid 操作员姓名 f_operator 交易金额 f_collection 操作时间 f_operate_date

##微信对账汇总(WeChatPayment.sql)

查询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  {entity.findDialect()==$SqlServer$: $isnull$,$NVL$}(( 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),{entity.findDialect()==$SqlServer$: $isnull$,$NVL$}(w.f_time_ends, w.f_create_time),120) f_time_end,
						w.f_transaction_id,

						{entity.findDialect()==$SqlServer$: $isnull$,$NVL$}(w.f_userinfo_code,s.f_userinfo_code) f_userinfo_code,
						{entity.findDialect()==$SqlServer$: $isnull$,$NVL$}(w.f_userinfo_id,s.f_userinfo_id) f_userinfo_id,
						{entity.findDialect()==$SqlServer$: $isnull$,$NVL$}(w.f_userfiles_id,s.f_userfiles_id) f_userfiles_id,
						{entity.findDialect()==$SqlServer$: $isnull$,$NVL$}(w.f_meter_classify,s.f_meter_type ) f_meter_type,
						{entity.findDialect()==$SqlServer$: $isnull$,$NVL$}(w.f_user_name,s.f_user_name ) f_user_name,
						w.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 varchar) 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

##微信对账明细(WeChatPaydetail.sql)

查询sql
select  tt.*
from (
		SELECT  (CASE
                     WHEN s.f_serial_id IS NULL AND w.f_transaction_id IS NOT NULL THEN '微信单方面账单'
                     WHEN s.f_serial_id IS NOT NULL AND w.f_transaction_id IS NULL THEN '燃气方单方面账单'
                     else '对账成功'
				END) msg,
		        {entity.findDialect()==$SqlServer$: $isnull$,$NVL$}(( 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,
                {entity.findDialect()==$SqlServer$: $isnull$,$NVL$}(w.f_time_ends, w.f_create_time) f_datatime,
                w.f_transaction_id,
                {entity.findDialect()==$SqlServer$: $isnull$,$NVL$}(w.f_userinfo_id,s.f_userinfo_id) f_userinfo_id,
                {entity.findDialect()==$SqlServer$: $isnull$,$NVL$}(w.f_userfiles_id,s.f_userfiles_id) f_userfiles_id,
                {entity.findDialect()==$SqlServer$: $isnull$,$NVL$}(w.f_meter_classify,s.f_meter_type ) f_meter_type,
                {entity.findDialect()==$SqlServer$: $isnull$,$NVL$}(w.f_user_name,s.f_user_name ) f_user_name,
                {entity.findDialect()==$SqlServer$: $isnull$,$NVL$}(w.f_user_type,s.f_user_type ) f_user_type,
                w.f_orgid,
                w.f_meternumber,
                -- w.f_address,
                w.f_userinfo_code,
                s.f_orgname,
                s.f_depid,
                s.f_operatorid,
                s.f_operator,
                s.f_depname,
                s.f_type,
                s.f_serial_id,
                s.f_collection,
                s.f_operate_date
		FROM (
          SELECT
                wx.f_order_type,
                wx.f_transaction_id,
                wx.f_total_fee,
                wx.f_attach,
                wx.id,
                wx.f_order_state,
                wx.f_trade_type,
                wx.f_create_time,
                wx.f_userfiles_id,
                wx.f_orgid,
                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 varchar) f_time_ends,
                uf.f_meter_classify,
                uf.f_userinfo_id,
                uf.f_meternumber,
                uf.f_user_type,
                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 = '已支付' 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_user_type,
                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 in ('有效','冲正')  and s.f_serial_id is not null
            and f_operate_date >= { entity.findDialect() == $SqlServer$: $'{startDate} 00:00:00'$, $to_date('{startDate} 00:00:00','yyyy-mm-dd HH24:mi:ss')}
            and f_operate_date <= { entity.findDialect() == $SqlServer$: $'{endDate} 23:59:59'$, $to_date('{endDate} 23:59:59','yyyy-mm-dd HH24:mi:ss')}
		   union all
-- 		   其他收费
		   SELECT
                s.f_serial_id,
                s.f_userinfo_id,
                s.f_userfiles_id,
                '其他收费' f_meter_type,
                s.f_user_name,
                s.f_user_type,
                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 in ('有效','冲正') and s.f_serial_id is not null
            and f_operate_date >= { entity.findDialect() == $SqlServer$: $'{startDate} 00:00:00'$, $to_date('{startDate} 00:00:00','yyyy-mm-dd HH24:mi:ss')}
            and f_operate_date <= { entity.findDialect() == $SqlServer$: $'{endDate} 23:59:59'$, $to_date('{endDate} 23:59:59','yyyy-mm-dd HH24:mi:ss')}
		) s on w.f_transaction_id = s.f_serial_id
)tt
where {condition}
ORDER BY f_datatime DESC