/// <summary>
/// 获取某天的对账单的下载地址(商户可通过接口下载指定日期（当天除外）的业务明细账单文件，并结合自身业务系统实现自动对账。该下载链接仅30秒，在得到链接后系统需要立刻请求下载账单文件。)
/// </summary>
public string GetBillDownloadUrl(DateTime date)
{
string url = string.Empty;
//组装业务参数
var model = new AlipayDataDataserviceBillDownloadurlQueryModel()
{
BillType = "trade",
BillDate = date.ToString("yyyy-MM-dd")
};
var request = new AlipayDataDataserviceBillDownloadurlQueryRequest();
request.SetBizModel(model);
AlipayDataDataserviceBillDownloadurlQueryResponse response = aopClient.Execute(request);
if (response != null && response.Code == "10000")
{
url = response.BillDownloadUrl;
}
//记日志response.ToJson();
return(url);
}
public void TestDownloadBill()
{
AlipayDataDataserviceBillDownloadurlQueryResponse response = Factory.Payment.Common().DownloadBill("trade", "2020-01");
Assert.AreEqual(response.Code, "10000");
Assert.AreEqual(response.Msg, "Success");
Assert.IsNull(response.SubCode);
Assert.IsNull(response.SubMsg);
Assert.NotNull(response.HttpBody);
Assert.IsTrue(response.BillDownloadUrl.StartsWith("http://dwbillcenter.alipay.com/"));
}
protected void Page_Load(object sender, EventArgs e)
{
IAopClient client = new DefaultAopClient("https://openapi.alipay.com/gateway.do", "app_id", "merchant_private_key", "json", "1.0", "RSA2", "alipay_public_key", "GBK", false);
AlipayDataDataserviceBillDownloadurlQueryRequest request = new AlipayDataDataserviceBillDownloadurlQueryRequest();
request.BizContent = "{" +
"\"bill_type\":\"trade\"," +
"\"bill_date\":\"2016-04-05\"" +
" }";
AlipayDataDataserviceBillDownloadurlQueryResponse response = client.Execute(request);
Console.WriteLine(response.Body);
}
private void BillDownloadurl(HttpContext context, IAopClient client, string adminHotelId, int hotelId, DateTime bill_date, string appAuthToken, AlipayDataDataserviceBillDownloadurlQueryRequest request)
{
try
{
string session = "";
AlipayDataDataserviceBillDownloadurlQueryResponse response = client.Execute(request, session, appAuthToken);
if (response != null && !string.IsNullOrEmpty(response.BillDownloadUrl))
{
string url = response.BillDownloadUrl;//得到下载路径
string filename = HttpContext.Current.Server.MapPath("/File/Zip/AlipayBill.zip");
//使用WebClient 下载文件
System.Net.WebClient myWebClient = new System.Net.WebClient();
byte[] data = myWebClient.DownloadData(url);
Stream stream = new MemoryStream(data); //byte[] 转换成 流
FileHelper.StreamToFile(stream, filename); //保存下载zip
string destinationFile = HttpContext.Current.Server.MapPath("/File/"); //解压路径
GZipHelper.UnZip(filename, destinationFile); //解压
string[] files = FileHelper.GetDirectoryFileList(destinationFile);
for (int i = 0; i < files.Length; i++)
{
string fn = files[i];
context.Response.Write("\r\n");
context.Response.Write(fn);
string filePath = HttpContext.Current.Server.MapPath("/File/" + fn);//文件路径
DataTable dt = GetdataFromCVS(filePath);
FileHelper.DeleteFile(filePath);
if (dt == null || dt.Rows.Count < 1)
{
continue;
}
context.Response.Write(".<br />");
for (int k = 0; k < dt.Rows.Count; k++)
{
if (fn.IndexOf("汇总") > 0)
{
Hashtable htStatistics = new Hashtable();
htStatistics["AdminHotelId"] = adminHotelId;
htStatistics["HotelId"] = hotelId;
htStatistics["Bill_Date"] = bill_date.ToString("yyyy-MM-dd");
htStatistics["PaySource"] = 2;
htStatistics["Transaction_Number"] = dt.Rows[k]["交易订单总笔数"].ToString(); //总交易单数
htStatistics["Transaction_Money"] = dt.Rows[k]["订单金额（元）"].ToString(); //总交易额
htStatistics["Refund_Money"] = dt.Rows[k]["退款订单总笔数"].ToString(); //总退款金额
htStatistics["Vouchers_Discounts"] = dt.Rows[k]["支付宝优惠（元）"].ToString(); //总代金券或立减优惠退款金额
htStatistics["Service_Charge"] = dt.Rows[k]["服务费（元）"].ToString(); //手续费总金额
DataFactory.SqlDataBase().InsertByHashtable("Hotel_Bill_Total", htStatistics);
}
else
{
Hashtable ht = new Hashtable();
ht["AdminHotelId"] = adminHotelId;
ht["HotelId"] = hotelId;
ht["Bill_Date"] = bill_date.ToString("yyyy-MM-dd");
ht["PaySource"] = 2;
ht["Transaction_Time"] = dt.Rows[k]["完成时间"].ToString(); //交易时间
ht["Appid"] = dt.Rows[k]["商品名称"].ToString(); //公众账号ID
ht["Mch_Id"] = dt.Rows[k]["门店编号"].ToString(); //商户号
ht["Sub_Mch_Id"] = dt.Rows[k]["门店名称"].ToString(); //子商户号
ht["Device_Number"] = dt.Rows[k]["终端号"].ToString(); //设备号
ht["WX_Order_Numbe"] = dt.Rows[k]["支付宝交易号"].ToString(); //微信订单号
ht["Order_Numbe"] = dt.Rows[k]["商户订单号"].ToString(); //商户订单号
ht["Openid"] = dt.Rows[k]["对方账户"].ToString(); //用户标识
ht["Transaction_Type"] = dt.Rows[k]["商品名称"].ToString() == "扫码支付" ? "MICROPAY" : "JSAPI"; //交易类型
if (dt.Rows[k]["业务类型"].ToString().IndexOf("退款") >= 0)
{
ht["Transaction_State"] = "REFUND"; //交易状态
ht["Total_Money"] = "0.00"; //总金额
ht["Refund_Money"] = CommonHelper.GetDouble(dt.Rows[k]["订单金额（元）"].ToString()) * -1; //退款金额
ht["WX_Refund_Numbe"] = dt.Rows[k]["支付宝交易号"].ToString(); //微信退款单号
ht["Refund_Numbe"] = dt.Rows[k]["商户订单号"].ToString(); //商户退款单号
ht["Refund_Type"] = "ORIGINAL"; //退款类型
ht["Refund_State"] = "SUCCESS"; //退款状态
ht["Refund_Vouchers_Discounts"] = dt.Rows[k]["支付宝优惠（元）"].ToString(); //代金券或立减优惠退款金额
}
else
{
ht["Transaction_State"] = "SUCCESS"; //交易状态
ht["Total_Money"] = dt.Rows[k]["订单金额（元）"].ToString(); //总金额
ht["Refund_Money"] = "0.00"; //退款金额
}
ht["Paying_Bank"] = ""; //付款银行
ht["Currency_Type"] = "CNY"; //货币种类
ht["Data_Package"] = ""; //商户数据包
ht["Vouchers_Discounts"] = dt.Rows[k]["支付宝优惠（元）"].ToString(); //代金券或立减优惠金额
ht["Commodity_Name"] = dt.Rows[k]["商品名称"].ToString(); //商品名称
ht["Service_Charge"] = CommonHelper.GetDouble(dt.Rows[k]["服务费（元）"].ToString()) * -1; //手续费
ht["Fee_Rate"] = "0.60%"; //费率
DataFactory.SqlDataBase().InsertByHashtable("Hotel_Bill", ht);
}
}
}
}
}
catch (Exception ex)
{
}
}
private void DownloadZFBAccount(DateTime beginDate, DateTime endDate, string bill_type)
{
IAopClient client = GetAopClient();
AlipayDataDataserviceBillDownloadurlQueryRequest request = new AlipayDataDataserviceBillDownloadurlQueryRequest();
string root = Environment.CurrentDirectory.Replace("\\bin\\Debug", "") + "\\Download";
//校验Download文件夹是否存在
if (!Directory.Exists(root))
{
//不存在 创建Download文件夹
Directory.CreateDirectory(root);
}
//压缩包路径
string zipPath = root + "\\支付宝账单.zip";
//文件路径
string filePath = "";
//遍历天数
do
{
#region 账单参数
//bill_type格式:trade指商户基于支付宝交易收单的业务账单；signcustomer是指基于商户支付宝余额收入及支出等资金变动的帐务账单
//bill_date格式：2018-09-13
string bill_date = beginDate.ToString("yyyy-MM-dd");
request.BizContent = "{" +
"\"bill_type\":\"" + bill_type + "\"," +
"\"bill_date\":\"" + bill_date + "\"" +
"}";
#endregion
//获取已授权商户集合
IList <Store> storeList = storeDao.GetOauthList().Where(o => string.IsNullOrEmpty(o.ZFBMch_Id) == false).ToList();
//遍历商户
foreach (var store in storeList)
{
//appAuthToken：某个商户的appAuthToken(201809BBb2b88d5c11f348b2a92be0c394b05A11)
string appAuthToken = store.app_auth_token;
AlipayDataDataserviceBillDownloadurlQueryResponse response = client.Execute(request, "", appAuthToken);
if (response.Msg == "Success")
{
#region 载账单
try
{
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(response.BillDownloadUrl);
req.ServicePoint.Expect100Continue = false;
req.Method = "GET";
req.KeepAlive = true;
//req.ContentType = "zip";// "image/png";
using (HttpWebResponse rsp = (HttpWebResponse)req.GetResponse())
{
using (Stream reader = rsp.GetResponseStream())
{
using (FileStream writer = new FileStream(zipPath, FileMode.OpenOrCreate, FileAccess.Write))
{
byte[] buff = new byte[512];
int c = 0; //实际读取的字节数
while ((c = reader.Read(buff, 0, buff.Length)) > 0)
{
writer.Write(buff, 0, c);
}
}
}
}
}
catch (Exception ex)
{
errorLogDao.SaveErrorLog("支付宝对账单下载出错：" + ex.Message);
}
#endregion
#region 解压账单
ZipInputStream zipStream = new ZipInputStream(File.Open(zipPath, FileMode.Open));
ZipEntry entry = zipStream.GetNextEntry();
while (entry != null)
{
if (!entry.IsFile)
{
continue;
}
//不解压汇总压缩包
if (entry.Name.Contains("汇总"))
{
//读取下一个压缩包
entry = zipStream.GetNextEntry();
continue;
}
//20885225452401150156_20180912_业务明细.csv
//20885225452401150156_20180912_业务明细(汇总).csv
filePath = Environment.CurrentDirectory.Replace("\\bin\\Debug", "") + "\\Download\\" + entry.Name;
//解压后的文件
FileStream writer = File.Create(filePath);
int bufferSize = 2048; //缓冲区大小
int readCount = 0; //读入缓冲区的实际字节
byte[] buffer = new byte[bufferSize];
readCount = zipStream.Read(buffer, 0, bufferSize);
while (readCount > 0)
{
writer.Write(buffer, 0, readCount);
readCount = zipStream.Read(buffer, 0, bufferSize);
}
writer.Close();
writer.Dispose();
//读取下一个压缩包
entry = zipStream.GetNextEntry();
}
zipStream.Close();
zipStream.Dispose();
#endregion
#region 遍历账单
StreamReader sr = new StreamReader(filePath, Encoding.GetEncoding("gb2312"));
string line = ""; //每行内容
int lineIndex = 0; //第一行为标题
int count = 0; //总行数
int maxCount = 0; //所需读取数据数
int maxIndex = 0; //所属读取索引数
ZFBAccount entity = null;
#region 计算读取数
while (line != null)
{
line = sr.ReadLine();
count++;
}
//所需读取数据数
maxCount = count - 10;
//所属读取索引数
maxIndex = count - 5;
//重置
line = "";
//关闭流
sr.Close();
#endregion
#region 校验是否需要保存数据
#region 过滤条件
List <DataFilter> filters = new List <DataFilter>();
//匹配商户号
filters.Add(new DataFilter {
comparison = "eq", field = "MchId", type = "string", value = store.ZFBMch_Id
});
//大于等于起始日期
filters.Add(new DataFilter {
comparison = "gteq", field = "PayTime", type = "date", value = beginDate.ToString("yyyy-MM-dd 00:00:00")
});
//小于等于起始日期
filters.Add(new DataFilter {
comparison = "lteq", field = "PayTime", type = "date", value = beginDate.ToString("yyyy-MM-dd 23:59:59")
});
#endregion
//校验是否需要保存支付宝账单
bool isSave = ZFBAccountDao.CheckIsSave(filters, maxCount);
if (isSave)
{
//需要保存
//删除历史数据
ZFBAccountDao.Delete(filters);
}
else
{
//不需要保存
//换下一个商家
continue;
}
#endregion
sr = new StreamReader(filePath, Encoding.GetEncoding("gb2312"));
//商户号
string mchId = "";
//支付宝交易号,商户订单号,业务类型,商品名称,创建时间,完成时间,门店编号,门店名称,操作员,终端号,对方账户,订单金额（元）,商家实收（元）,支付宝红包（元）,集分宝（元）,支付宝优惠（元）,商家优惠（元）,券核销金额（元）,券名称,商家红包消费金额（元）,卡消费金额（元）,退款批次号/请求号,服务费（元）,分润（元）,备注
while (line != null && lineIndex < maxIndex)
{
line = sr.ReadLine();
#region 读取商户号行
if (lineIndex == 1)
{
//#账号：[20885225452401150156] 处理后得到 2088522545240115
mchId = line.Split('[')[1].Substring(0, 16);
}
#endregion
#region 读取数据行，保存账单
if (lineIndex > 4 && line != null)
{
string[] accounts = line.Split(',');
//创建新账单
entity = new ZFBAccount
{
MchId = mchId, //商户号
OrderNo = accounts[0].Trim(), //支付宝交易号
MchOrderNo = accounts[1].Trim(), //商户订单号
PayType = accounts[2].Trim(), //业务类型
GoodsName = accounts[3].Trim(), //商品名称
CreateTime = Convert.ToDateTime(accounts[4].Trim()), //创建时间
PayTime = Convert.ToDateTime(accounts[5].Trim()), //完成时间
StoreNo = accounts[6].Trim(), //门店编号
StoreName = accounts[7].Trim(), //门店名称
Handler = accounts[8].Trim(), //操作员
DeviceId = accounts[9].Trim(), //终端号
PayUser = accounts[10].Trim(), //对方账户
Total = Convert.ToDecimal(accounts[11].Trim()), //订单金额
DiscountsTotal = Convert.ToDecimal(accounts[12].Trim()), //商家实收（元）
ZFB_RedPacket = Convert.ToDecimal(accounts[13].Trim()), //支付宝红包（元）
SetPoints = Convert.ToDecimal(accounts[14].Trim()), //集分宝（元）
ZFB_Discounts = Convert.ToDecimal(accounts[15].Trim()), //支付宝优惠（元）
Store_Discounts = Convert.ToDecimal(accounts[16].Trim()), //商家优惠（元）
CouponPrice = Convert.ToDecimal(accounts[17].Trim()), //券核销金额（元）
CouponName = accounts[18].Trim(), //券名称
Store_RedPacket = Convert.ToDecimal(accounts[19].Trim()), //商家红包消费金额（元）
CardPrice = Convert.ToDecimal(accounts[20].Trim()), //卡消费金额（元）
RefundOrderNo = accounts[21].Trim(), //退款批次号/请求号
ServicePrice = Convert.ToDecimal(accounts[22].Trim()), //服务费（元）
SharePrice = Convert.ToDecimal(accounts[23].Trim()), //分润（元）
Remark = accounts[24].Trim() //备注
};
//保存账单
ZFBAccountDao.Stateless_Insert(entity);
}
#endregion
lineIndex++;
}
//关闭流
sr.Close();
#endregion
#region 除文件
//删除文件
File.Delete(filePath);
//删除压缩包
File.Delete(zipPath);
#endregion
}
}
//增加天数
beginDate = beginDate.AddDays(1);
} while (beginDate <= endDate);
}
