背景
因为网络、合作方、或者业务逻辑问题总会漏掉一些支付记录,这时候要找到这些订单号并查出原因。于是乎每日对账需求就出来了,不仅仅对每日金额,还需要把支付宝微信的所有记录拉取下来,再用sql对比订单号。在网页上显示出来。
支付宝开放平台
文档中心 – 》 全部API -》 账务API -》查询对账单下载地址
https://docs.open.alipay.com/api_15/alipay.data.dataservice.bill.downloadurl.query
支付宝接口返回压缩包,压缩包中一个是详细记录,另一个是汇总。
微信支付商户平台
最下层服务支持 – 》 开发文档 -》 JSAPI支付 – 》 API列表 – 》 下载对账单
https://pay.weixin.qq.com/wiki/doc/api/jsapi.php?chapter=9_6
微信接口直接返回记录,也可以返回压缩格式的文件流。
Java代码
支付宝
package com.gmtx.system.checkzwtask; import java.io.BufferedReader; import java.io.DataInputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.FileReader; import java.io.IOException; import java.io.InputStreamReader; import java.io.OutputStreamWriter; import java.io.UnsupportedEncodingException; import java.io.Writer; import java.net.URLEncoder; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Collections; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Properties; import javax.annotation.Resource; import org.springframework.scheduling.annotation.Scheduled; import org.springframework.stereotype.Component; import com.gmtx.system.dao.CommonDao; import com.gmtx.system.entitys.ZfbEntity2; import com.gmtx.system.tools.JsonUtil; import com.gmtx.system.tools.PropsUtil; import com.gmtx.system.tools.ZipUtil; /** * 支付宝对账单查询下载 */ @Component("ZfbBillCheck") public class ZfbBillCheck{ @Resource(name="commonDao") private CommonDao dao=null; public CommonDao getDao() { return dao; } private String url = "https://openapi.alipay.com/gateway.do"; private String method = "alipay.data.dataservice.bill.downloadurl.query"; private String app_id = "**********"; private String charset = "gbk"; // 加密验签RSA2或者RSA private String sign_type = "RSA2"; // yyyy-MM-dd HH:mm:ss private String timestamp; // 版本 private String version = "1.0"; private String biz_content = ""; // 对账时间。用来创建下载的压缩包名称 private String reconciliationTime; private String time; public ZfbCx(){} private boolean bl=true; private static final Properties properties = PropsUtil.loadProps("config.properties"); String zfbZipDirPath = PropsUtil.getString(properties, "zfbzipdownloadDir"); BillZfb zfb = null; /** * 判断是否有昨天的对账数据 * @param rDate * @return 存在返回true */ private boolean isExistDate(Date rDate) { SimpleDateFormat querySdf=new SimpleDateFormat("yyyy-MM-dd"); //设置时间格式 String queryTime = querySdf.format(rDate); //拉取时间 String sql = "SELECT count(*) FROM bill_zfb_total WHERE DATE = ?"; Integer result = (Integer) dao.queryOneColumnForSigetonRow(sql, new Object[]{queryTime}, Integer.class); if("1".equals(result+"")) return true; else return false; } /** * 读取文件夹中包含include字符串的文件名 * @param include 返回文件名包含的字符串 * @param path 文件夹路径 * @return */ private String getFolderContentFileName(String include,String path) { File file = new File(path); String fileName = ""; try { File[] tempList = file.listFiles(); for (int i = 0; i < tempList.length; i++) { if (tempList[i].isFile()) { fileName = tempList[i].getName(); if (fileName.indexOf(include) > 0) { break; } } if (tempList[i].isDirectory()) { return ""; } } }catch (Exception e) { e.printStackTrace(); } return fileName; } /** * 读取详细记录Excel,csv并插入到库中 * @param path * @throws Exception */ private void readDetailExcelAndInsertIntoTable(String path) throws Exception { transferFile(path,path); File csv = new File(path); // CSV文件路径 BufferedReader br = null; try { br = new BufferedReader(new FileReader(csv)); } catch (FileNotFoundException e) { e.printStackTrace(); } String line = ""; String everyLine = ""; List
微信
package com.gmtx.system.checkzwtask; import java.io.BufferedReader; import java.io.IOException; import java.io.Reader; import java.io.StringReader; import java.io.UnsupportedEncodingException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Collections; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.annotation.Resource; import org.springframework.scheduling.annotation.Scheduled; import org.springframework.stereotype.Component; import com.gmtx.system.dao.CommonDao; import com.gmtx.system.tools.MD5Util; @Component("wxBillCheck") public class WxBillCheck{ @Resource(name="commonDao") private CommonDao dao=null; public CommonDao getDao() { return dao; } //公众号 private String appid="***"; //微信支付分配的商户号 private String mch_id="***"; //随机字符串,不长于32位 private String nonce_str; //签名 private String sign; //签名类型,目前支持HMAC-SHA256和MD5,默认为MD5 private String sign_type="MD5"; private final String URL="https://api.mch.weixin.qq.com/pay/downloadbill"; private String charset="UTF-8"; private String time; private String queryTime; private String key = "***"; public WxSm(){ } /** * 获取微信账单记录 * @param dBefore 账单日期 * @return */ private String getDataFromWx(Date dBefore) { SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMdd"); //设置时间格式 time = sdf.format(dBefore); //微信账单日期 SimpleDateFormat sdf2=new SimpleDateFormat("yyyyMMddhhmmss"); nonce_str = sdf2.format(dBefore); //随机字符串 Mapparams=new HashMap (); params.put("appid", appid); params.put("mch_id", mch_id); params.put("nonce_str", nonce_str); params.put("sign_type", sign_type); params.put("bill_date", time); //下载对账单的日期,格式:20140603 /* * ALL,返回当日所有订单信息,默认值 SUCCESS,返回当日成功支付的订单 REFUND,返回当日退款订单 RECHARGE_REFUND,返回当日充值退款订单(相比其他对账单多一栏“返还手续费”) * */ params.put("bill_type", "ALL"); List ks=new ArrayList (params.keySet()); Collections.sort(ks); String str=""; for (int i = 0; i < ks.size(); i++) { String key = ks.get(i); Object value = params.get(key); if(null!=value&&!value.toString().trim().equals("")){ str+=key+"="+value+"&"; } } str = str + "key="+key; sign= new MD5Util().md5ByWx(str,charset).toUpperCase(); String xml=" "; String result=HttpsUtil.sendPostRequest(URL,xml,charset); return result; } /** * 将微信返回的记录插入到库里面 * TODO */ public void insertIntoTable(Date dBefore){ SimpleDateFormat querySdf=new SimpleDateFormat("yyyy-MM-dd"); //设置时间格式 queryTime = querySdf.format(dBefore); //拉取时间 LOGGER.info("开始拉取"+queryTime+"号微信账单"); String result_wx = getDataFromWx(dBefore); //拉取微信账单 Reader strReader = new StringReader(result_wx); BufferedReader reader = new BufferedReader(strReader); boolean result = false; /*//如果有某日的记录删除bill_wx_total和bill_wx那天的记录 String sql_del ="DELETE FROM bill_wx WHERE tradetime between ? and ? "; // 删除微信账单记录 String sqltotal_del ="DELETE FROM bill_wx_total WHERE DATE = ?"; // 删除汇总 dao.execute(sql_del, new Object[]{queryTime+" 00:00:00",queryTime+" 23:59:59"}); dao.execute(sqltotal_del, new Object[]{queryTime});*/ String sql_check ="SELECT COUNT(*) FROM bill_wx_total WHERE DATE = ?"; //查看某天库里是否已经存在 Integer r = (Integer)dao.queryOneColumnForSigetonRow(sql_check, new Object[]{queryTime}, Integer.class); if ("1".equals(r+"")) { //如果库里存在直接退出 return; } try { List "+appid+" "+mch_id+" "+nonce_str+" " + ""+sign_type+" "+time+" " + "ALL "+sign+" dataparamList = new ArrayList (); //微信所有记录参数 String line = reader.readLine(); //读取第一行(行头) while (line != null) { if (line.contains("`")&&line.lastIndexOf("`")>200) { //数据区域 String[] strArr=line.replace("`","").split(","); dataparamList.add(strArr); }else if (line.contains("`")&&line.lastIndexOf("`")>10) { //最后一行统计数据 String[] strArr=line.replace("`","").split(","); String sql_total = "INSERT INTO `bill_wx_total` (`date`,totalOrderCount, `payableTotalAmount`, `totalRefundAmount`, `totalDiscountRefundAmount`, `serviceAmount`, `orderTotalAmount`, `applyRefundtotalAmount`) VALUES ('"+queryTime+"',?, ?, ?, ?, ?, ?, ?);"; result = dao.execute(sql_total, strArr); } line = reader.readLine(); } //批量插入微信订单数据 String sql ="INSERT INTO `bill_wx` (`tradetime`, `pubaccountID`, `merchantNo`, `submerchantNo`, `devNo`, `wxorderNo`, `merchant_orderNo`, `userID`, `tradeType`, `tradeStatus`, `payBank`, `currencyType`, `amount_payable`, `amount_discount`, `wx_refundNo`, `merchant_refundNo`, `amount_refund`, `amount_discountRefund`, `refundType`, `refundStatus`, `commodityName`, `merchantPacket`, `servicefee`, `servicerate`,orderAmount,amount_applyrefund,servicerate_remark) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '');"; dao.executeBatch2(sql, dataparamList); } catch (IOException e) { LOGGER.error("微信账单拉取失败"+e); } catch (Exception e) { LOGGER.error("微信账单拉取失败"+e); } LOGGER.info("微信账单"+queryTime+"拉取成功"); //return result; } /** * 定时器执行 * TODO * @throws InterruptedException */ @Scheduled(cron="0 0 23 * * *") public void execute() throws InterruptedException { Thread.sleep(6000); Calendar calendar = Calendar.getInstance(); calendar.add(Calendar.DAY_OF_MONTH, -1); //得到前第一天的时间 Date dBefore = calendar.getTime(); insertIntoTable(dBefore); /*为了防止服务器宕机等特殊情况的发生,每天晚上除了拉取昨天的账单,还会拉取前天和大前天的账单*/ calendar.add(Calendar.DAY_OF_MONTH, -1); //得到前第两天的时间 dBefore = calendar.getTime(); insertIntoTable(dBefore); calendar.add(Calendar.DAY_OF_MONTH, -1); //得到前第三天的时间 dBefore = calendar.getTime(); insertIntoTable(dBefore); LOGGER.info("微信账单核对结束"); } /** * 手动拉取数据-遍历月份的每一天 */ //@Scheduled(cron="* * * * * *") public void dateInMonthIterator() throws InterruptedException { Thread.sleep(6000); SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd"); //设置时间格式 Calendar calendar = Calendar.getInstance(); //得到日历 calendar.set(2019, 11-1, 1); //设置年月日,月份从0开始 int maxDay = calendar.getActualMaximum(Calendar.DAY_OF_MONTH); //获取当月最大天数 for (int i = 0; i < maxDay; i++) { Date dBefore = calendar.getTime(); insertIntoTable(dBefore); calendar.add(Calendar.DAY_OF_MONTH, 1); //天数加一操作 } Thread.sleep(5*1000); } }
日志配置
偶然发现有两天的支付宝微信没拉取下来,添加日志,并添加核对前两天是否拉取账单成功,如果前两天失败,则重新拉取。
log4j.logger.com.gmtx.system.checkzwtask=INFO,zwFile
###指定包名下的输出到指定文件
log4j.appender.zwFile=org.apache.log4j.FileAppender
log4j.appender.zwFile.layout=org.apache.log4j.PatternLayout
log4j.appender.zwFile.File = D:/checkzwtask.log
log4j.appender.zwFile.encoding=UTF-8
log4j.appender.zwFile.Append=true
log4j.appender.zwFile.layout.ConversionPattern=[ %p ] %-d{yyyy-MM-dd HH:mm:ss} [ %t:%L ] %37c %3x - %m%n
Mysql数据库
建四张表sql语句
-- -------------------------------------------------------- -- 主机: 222.222.221.197 -- Server version: 5.5.40 - MySQL Community Server (GPL) -- Server OS: Win64 -- HeidiSQL 版本: 10.1.0.5464 -- -------------------------------------------------------- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET NAMES utf8 */; /*!50503 SET NAMES utf8mb4 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; CREATE TABLE IF NOT EXISTS `bill_wx` ( `tradetime` datetime NOT NULL COMMENT '交易时间', `pubaccountID` char(20) DEFAULT NULL COMMENT '公众账号ID', `merchantNo` varchar(50) DEFAULT NULL COMMENT '商户号', `submerchantNo` varchar(20) DEFAULT NULL COMMENT '特约商户号', `devNo` varchar(20) DEFAULT NULL COMMENT '设备号', `wxorderNo` char(30) COMMENT '微信订单号', `merchant_orderNo` varchar(50) NOT NULL COMMENT '商户订单号', `userID` char(30) DEFAULT NULL COMMENT '用户标识', `tradeType` varchar(20) DEFAULT NULL COMMENT '交易类型', `tradeStatus` varchar(20) DEFAULT NULL COMMENT '交易状态', `payBank` varchar(20) DEFAULT NULL COMMENT '付款银行', `currencyType` char(5) DEFAULT NULL COMMENT '货币种类', `amount_payable` decimal(10,2) DEFAULT NULL COMMENT '应结订单金额', `amount_discount` decimal(10,2) COMMENT '代金券金额', `wx_refundNo` varchar(50) DEFAULT NULL COMMENT '微信退款单号', `merchant_refundNo` varchar(50) DEFAULT NULL COMMENT '商户退款单号', `amount_refund` decimal(10,2) DEFAULT NULL COMMENT '退款金额', `amount_discountRefund` decimal(10,2) DEFAULT NULL COMMENT '充值券退款金额', `refundType` varchar(20) DEFAULT NULL COMMENT '退款类型', `refundStatus` varchar(20) DEFAULT NULL COMMENT '退款状态', `commodityName` varchar(30) DEFAULT NULL COMMENT '商品名称', `merchantPacket` varchar(50) DEFAULT NULL COMMENT '商户数据包', `servicefee` decimal(10,6) DEFAULT NULL COMMENT '手续费', `servicerate` varchar(10) DEFAULT NULL COMMENT '费率', `orderAmount` varchar(10) DEFAULT NULL COMMENT '订单金额', `amount_applyrefund` varchar(10) DEFAULT NULL COMMENT '申请退款金额', `servicerate_remark` varchar(10) DEFAULT NULL COMMENT '费率备注', PRIMARY KEY (`merchant_orderNo`,`tradetime`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='每日微信账单拉取'; CREATE TABLE IF NOT EXISTS `bill_wx_total` ( `date` date NOT NULL COMMENT '日期', `totalOrderCount` int(11) DEFAULT NULL, `payableTotalAmount` decimal(10,2) DEFAULT NULL COMMENT '应结订单总金额', `totalRefundAmount` decimal(10,2) DEFAULT NULL COMMENT '退款总金额', `totalDiscountRefundAmount` decimal(10,2) DEFAULT NULL COMMENT '充值券退款总金额', `serviceAmount` decimal(10,2) DEFAULT NULL COMMENT '手续费总金额', `orderTotalAmount` decimal(10,2) DEFAULT NULL COMMENT '订单总金额', `applyRefundtotalAmount` decimal(10,2) DEFAULT NULL COMMENT '申请退款总金额', `sysDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入库时间', PRIMARY KEY (`date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='微信日账单总金额'; CREATE TABLE IF NOT EXISTS `bill_zfb` ( `tradeNo` char(30) DEFAULT NULL COMMENT '支付宝交易号', `merchant_orderNo` char(35) NOT NULL COMMENT '商户订单号', `businessType` char(10) DEFAULT NULL COMMENT '业务类型', `commodityName` varchar(50) DEFAULT NULL COMMENT '商品名称', `createTime` timestamp NULL DEFAULT NULL COMMENT '创建时间', `finishTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '完成时间', `merchantNo` varchar(50) DEFAULT NULL COMMENT '门店编号', `merchantName` varchar(50) DEFAULT NULL COMMENT '门店名称', `operater` varchar(50) DEFAULT NULL COMMENT '操作员', `terminalNo` varchar(50) DEFAULT NULL COMMENT '终端号', `clientAccount` varchar(50) DEFAULT NULL COMMENT '对方账户', `orderAmount` decimal(10,2) DEFAULT NULL COMMENT '订单金额(元)', `realAmount` decimal(10,2) DEFAULT NULL COMMENT '商家实收(元)', `redPaperAmount` decimal(10,2) DEFAULT NULL COMMENT '支付宝红包(元)', `jifenbaoAmount` decimal(10,2) NOT NULL COMMENT '集分宝(元)', `zfbdiscountAmount` decimal(10,2) DEFAULT NULL COMMENT '支付宝优惠(元)', `merdiscountAmount` decimal(10,2) DEFAULT NULL COMMENT '商家优惠(元)', `juanhexiaoAmount` decimal(10,2) DEFAULT NULL COMMENT '券核销金额(元)', `juanName` varchar(50) DEFAULT NULL COMMENT '券名称', `merredPaperConsume` decimal(10,2) DEFAULT NULL COMMENT '商家红包消费金额(元)', `cardConsume` decimal(10,2) DEFAULT NULL COMMENT '卡消费金额(元)', `refundNo` varchar(50) DEFAULT NULL COMMENT '退款批次号/请求号', `serviceFee` decimal(10,2) DEFAULT NULL COMMENT '服务费(元)', `shareBenefit` decimal(10,2) DEFAULT NULL COMMENT '分润(元)', `remark` varchar(50) DEFAULT NULL COMMENT '备注', PRIMARY KEY (`merchant_orderNo`,`finishTime`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='支付宝每日账单拉取'; CREATE TABLE IF NOT EXISTS `bill_zfb_total` ( `date` date NOT NULL, `totalOrderCount` int(11) DEFAULT NULL COMMENT '交易订单总笔数', `refundOrderCount` int(11) DEFAULT NULL COMMENT '退款订单总笔数', `orderAmount` decimal(10,2) DEFAULT NULL COMMENT '订单金额(元)', `realAmount` decimal(10,2) DEFAULT NULL COMMENT '商家实收(元)', `zfbdiscountAmount` decimal(10,2) DEFAULT NULL COMMENT '支付宝优惠(元)', `merdiscountAmount` decimal(10,2) DEFAULT NULL COMMENT '商家优惠(元)', `cardConsumeAmount` decimal(10,2) DEFAULT NULL COMMENT '卡消费金额(元)', `serviceFee` decimal(10,2) DEFAULT NULL COMMENT '服务费(元)', `shareBenefit` decimal(10,2) DEFAULT NULL COMMENT '分润(元)', `realBenefit` decimal(10,2) DEFAULT NULL COMMENT '实收净额(元)', `sysDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入库时间', PRIMARY KEY (`date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='支付宝日账单总金额'; /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */; /*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
支付宝微信退款订单为单独的一条记录和过去的交易记录互不影响。
微信每日汇总有订单金额和退款金额分开的两个字段。
支付宝的订单金额(实收金额)为 交易订单金额 – 退款订单金额(退款订单金额为负)即所有订单的订单金额之和
部署在线上时遇到的问题
中文乱码
下载出来的支付宝Linux环境中读取为乱码(Windows下没问题),要将每行读出来的记录转为gbk,但出现了一些问题,被转为?。后采用文件编码转码解决。
everyLine = new String(line.getBytes(),”gbk”);
标点转义
分割符问题,Linux环境下逗号分割无效(Windows下没问题),要用转义字符,任何标点符号都要用转义字符
params = everyLine.split(“\\,”); //转义
参数个数问题
readLine后支付宝正常参数为25个,转码为gbk之后发现有些居然为24个(依然是Linux中有问题Windows没问题),是因为有的,变成了?,这尼玛。用其他的编码逗号就没有问题,转成GBK就有几率出现?。
机智的我先用乱码分割然后在用gbk转码然后出现了另一个乱码…
最后解决的方法是先把文件转成UTF-8然后再读取就没这么多事了。