public List <WXBill> getOneDay(DateTime time)
{
List <WXBill> _billList = new List <WXBill>();
StringBuilder strSql = new StringBuilder();
strSql.Append(@" select limit 100 RecordID, TranDate, AppID, MCHID, SubMCHID, DeviceID, TransactionID, OutTradeNo, UserInfo, TranType , TranSta, PayBank , Currency , Cost , EnterpriseLuckyMoney , RefundID , OutRefundNo , RefundMoney , EnterpriseRefundLuckyMoney, RefundType , RefundSta , GoodsName , MerchantData , ExFee , Rates ");
strSql.Append(" from TD_Order_WeiXinPay_dz ");
strSql.Append(" where dzzt=0 and trandate>'" + time.ToString("yyyy-MM-dd") + "' and trandate<'" + time.AddDays(1).ToString("yyyy-MM-dd") + "'");
MySqlParameter[] parameters = { };
using (DbDataReader dr = MySqlHelperUtil.ExecuteReader(strSql.ToString(), parameters))
{
while (dr.HasRows && dr.Read())
{
int i = -1;
//ID,jysj,gzzhid,shh,zshh,sbh,wxddh,shddh,wbbh,jylx,jyzt,fkyh,hbzl,zje,qyhbje,wxtkdh,shtkdh,tkje,qyhbtkje,tklx,tkzt,spmc,shsjb,sxf,fl
WXBill _bill = new WXBill();
if (!dr.IsDBNull(++i))
{
_bill.RecordID = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.TranDate = dr.GetDateTime(i);
}
if (!dr.IsDBNull(++i))
{
_bill.AppID = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.MCHID = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.SubMCHID = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.DeviceID = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.TransactionID = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.OutTradeNo = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.UserInfo = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.TranType = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.TranSta = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.PayBank = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.Currency = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.Cost = dr.GetDecimal(i);
}
if (!dr.IsDBNull(++i))
{
_bill.EnterpriseLuckyMoney = dr.GetDecimal(i);
}
if (!dr.IsDBNull(++i))
{
_bill.RefundID = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.OutRefundNo = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.RefundMoney = dr.GetDecimal(i);
}
if (!dr.IsDBNull(++i))
{
_bill.EnterpriseRefundLuckyMoney = dr.GetDecimal(i);
}
if (!dr.IsDBNull(++i))
{
_bill.RefundType = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.RefundSta = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.GoodsName = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.MerchantData = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.ExFee = dr.GetDecimal(i);
}
if (!dr.IsDBNull(++i))
{
_bill.Rates = dr.GetString(i);
}
_billList.Add(_bill);
}
}
if (_billList.Count <= 0)
{
return(null);
}
return(_billList);
}
public bool insertBills(List <WXBill> _BillList)
{
bool ret = false;
string transid = MySqlHelperUtil.BeginTran();
try
{
foreach (WXBill _bill in _BillList)
{
//通过商户订单号查找记录
WXBill oldOne = selectOneBillBySHDDH(_bill);
if (null != oldOne)
{
if (!oldOne.Equals(_bill))
{//若存在记录且数据不一直 更新
ret = updateBillByID(oldOne.RecordID, _bill, transid);
if (false == ret)
{
break;
}
}
else
{
ret = true;
}
}
else
{//不存在记录 插入
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into TD_Order_WeiXinPay_dz(");
strSql.Append("RecordID, TranDate, AppID, MCHID, SubMCHID, DeviceID, TransactionID, OutTradeNo, UserInfo, TranType , TranSta, PayBank , Currency , Cost , EnterpriseLuckyMoney , RefundID , OutRefundNo , RefundMoney , EnterpriseRefundLuckyMoney, RefundType , RefundSta , GoodsName , MerchantData , ExFee , Rates");
strSql.Append(") values (");
strSql.Append("@RecordID, @TranDate,@AppID, @MCHID, @SubMCHID, @DeviceID, @TransactionID, @OutTradeNo, @UserInfo, @TranType , @TranSta, @PayBank , @Currency , @Cost , @EnterpriseLuckyMoney , @RefundID , @OutRefundNo , @RefundMoney , @EnterpriseRefundLuckyMoney, @RefundType , @RefundSta , @GoodsName , @MerchantData , @ExFee , @Rates");
strSql.Append(") ");
MySqlParameter[] parameters =
{
new MySqlParameter("@RecordID", Guid.NewGuid().ToString()),
new MySqlParameter("@TranDate", _bill.TranDate),
new MySqlParameter("@AppID", _bill.AppID),
new MySqlParameter("@MCHID", _bill.MCHID),
new MySqlParameter("@SubMCHID", _bill.SubMCHID),
new MySqlParameter("@DeviceID", _bill.DeviceID),
new MySqlParameter("@TransactionID", _bill.TransactionID),
new MySqlParameter("@OutTradeNo", _bill.OutTradeNo),
new MySqlParameter("@UserInfo", _bill.UserInfo),
new MySqlParameter("@TranType", _bill.TranType),
new MySqlParameter("@TranSta", _bill.TranSta),
new MySqlParameter("@PayBank", _bill.PayBank),
new MySqlParameter("@Currency", _bill.Currency),
new MySqlParameter("@Cost", _bill.Cost),
new MySqlParameter("@EnterpriseLuckyMoney", _bill.EnterpriseLuckyMoney),
new MySqlParameter("@RefundID", _bill.RefundID),
new MySqlParameter("@OutRefundNo", _bill.OutRefundNo),
new MySqlParameter("@RefundMoney", _bill.RefundMoney),
new MySqlParameter("@EnterpriseRefundLuckyMoney", _bill.EnterpriseRefundLuckyMoney),
new MySqlParameter("@RefundType", _bill.RefundType),
new MySqlParameter("@RefundSta", _bill.RefundSta),
new MySqlParameter("@GoodsName", _bill.GoodsName),
new MySqlParameter("@MerchantData", _bill.MerchantData),
new MySqlParameter("@ExFee", _bill.ExFee),
new MySqlParameter("@Rates", _bill.Rates)
};
if (MySqlHelperUtil.ExecuteNonQuery(transid, strSql.ToString(), parameters) != 1)
{
MySqlHelperUtil.RollbackTran(transid);
ret = false;
break;
}
else
{
ret = true;
}
}
}
}
catch (Exception ex)
{
MySqlHelperUtil.RollbackTran(transid);
throw ex;
}
if (!ret)
{
MySqlHelperUtil.RollbackTran(transid);
}
else
{
MySqlHelperUtil.CommitTran(transid);
}
return(ret);
}
public WXBill selectOneBillBySHDDH(WXBill _NewBill)
{
WXBill _bill = null;
try
{
StringBuilder strSql = new StringBuilder();
//"select ID,jysj,gzzhid,shh,zshh,sbh,wxddh,shddh,wbbh,jylx,jyzt,fkyh,hbzl,zje,qyhbje,wxtkdh,shtkdh,tkje,qyhbtkje,tklx,tkzt,spmc,shsjb,sxf,fl from " + SQL_TABLE_NAME + " where shddh = :shddh and wxddh = :wxddh and wxtkdh = :wxtkdh and shtkdh = :shtkdh";
strSql.Append(@" select RecordID, TranDate, AppID, MCHID, SubMCHID, DeviceID, TransactionID, OutTradeNo, UserInfo, TranType , TranSta, PayBank , Currency , Cost , EnterpriseLuckyMoney , RefundID , OutRefundNo , RefundMoney , EnterpriseRefundLuckyMoney, RefundType , RefundSta , GoodsName , MerchantData , ExFee , Rates ");
strSql.Append(" from TD_Order_WeiXinPay_dz ");
strSql.Append(" where TransactionID=@TransactionID and OutTradeNo=@OutTradeNo and RefundID=@RefundID and OutRefundNo=@OutRefundNo");
MySqlParameter[] parameters =
{
new MySqlParameter("@TransactionID", _NewBill.TransactionID),
new MySqlParameter("@OutTradeNo", _NewBill.OutTradeNo),
new MySqlParameter("@RefundID", _NewBill.RefundID),
new MySqlParameter("@OutRefundNo", _NewBill.OutRefundNo)
};
using (DbDataReader dr = MySqlHelperUtil.ExecuteReader(strSql.ToString(), parameters))
{
if (dr.HasRows)
{
int i = -1;
dr.Read();
//ID,jysj,gzzhid,shh,zshh,sbh,wxddh,shddh,wbbh,jylx,jyzt,fkyh,hbzl,zje,qyhbje,wxtkdh,shtkdh,tkje,qyhbtkje,tklx,tkzt,spmc,shsjb,sxf,fl
_bill = new WXBill();
if (!dr.IsDBNull(++i))
{
_bill.RecordID = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.TranDate = dr.GetDateTime(i);
}
if (!dr.IsDBNull(++i))
{
_bill.AppID = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.MCHID = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.SubMCHID = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.DeviceID = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.TransactionID = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.OutTradeNo = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.UserInfo = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.TranType = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.TranSta = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.PayBank = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.Currency = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.Cost = dr.GetDecimal(i);
}
if (!dr.IsDBNull(++i))
{
_bill.EnterpriseLuckyMoney = dr.GetDecimal(i);
}
if (!dr.IsDBNull(++i))
{
_bill.RefundID = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.OutRefundNo = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.RefundMoney = dr.GetDecimal(i);
}
if (!dr.IsDBNull(++i))
{
_bill.EnterpriseRefundLuckyMoney = dr.GetDecimal(i);
}
if (!dr.IsDBNull(++i))
{
_bill.RefundType = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.RefundSta = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.GoodsName = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.MerchantData = dr.GetString(i);
}
if (!dr.IsDBNull(++i))
{
_bill.ExFee = dr.GetDecimal(i);
}
if (!dr.IsDBNull(++i))
{
_bill.Rates = dr.GetString(i);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(_bill);
}
public bool updateBillByID(string _recordid, WXBill _bill, string _transid = null)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update TD_Order_WeiXinPay_dz set ");
strSql.Append(" TranDate = @TranDate , ");
strSql.Append("AppID = @AppID , ");
strSql.Append(" MCHID = @MCHID , ");
strSql.Append("SubMCHID= @SubMCHID , ");
strSql.Append(" DeviceID = @DeviceID , ");
strSql.Append(" TransactionID = @TransactionID , ");
strSql.Append(" OutTradeNo = @OutTradeNo , ");
strSql.Append(" UserInfo = @UserInfo , ");
strSql.Append(" TranType = @TranType , ");
strSql.Append(" TranSta = @TranSta , ");
strSql.Append(" PayBank = @PayBank , ");
strSql.Append(" Currency = @Currency , ");
strSql.Append(" Cost = @Cost , ");
strSql.Append(" EnterpriseLuckyMoney = @EnterpriseLuckyMoney , ");
strSql.Append(" RefundID = @RefundID , ");
strSql.Append(" OutRefundNo = @OutRefundNo , ");
strSql.Append(" RefundMoney = @RefundMoney , ");
strSql.Append(" EnterpriseRefundLuckyMoney = @EnterpriseRefundLuckyMoney , ");
strSql.Append(" RefundType = @RefundType , ");
strSql.Append(" RefundSta = @RefundSta , ");
strSql.Append(" GoodsName = @GoodsName , ");
strSql.Append(" MerchantData = @MerchantData , ");
strSql.Append(" ExFee = @ExFee , ");
strSql.Append(" Rates = @Rates ,dzzt=0 ");
strSql.Append(" where RecordID=@RecordID ");
MySqlParameter[] parameters =
{
new MySqlParameter("@RecordID", _recordid),
new MySqlParameter("@TranDate", _bill.TranDate),
new MySqlParameter("@AppID", _bill.AppID),
new MySqlParameter("@MCHID", _bill.MCHID),
new MySqlParameter("@SubMCHID", _bill.SubMCHID),
new MySqlParameter("@DeviceID", _bill.DeviceID),
new MySqlParameter("@TransactionID", _bill.TransactionID),
new MySqlParameter("@OutTradeNo", _bill.OutTradeNo),
new MySqlParameter("@UserInfo", _bill.UserInfo),
new MySqlParameter("@TranType", _bill.TranType),
new MySqlParameter("@TranSta", _bill.TranSta),
new MySqlParameter("@PayBank", _bill.PayBank),
new MySqlParameter("@Currency", _bill.Currency),
new MySqlParameter("@Cost", _bill.Cost),
new MySqlParameter("@EnterpriseLuckyMoney", _bill.EnterpriseLuckyMoney),
new MySqlParameter("@RefundID", _bill.RefundID),
new MySqlParameter("@OutRefundNo", _bill.OutRefundNo),
new MySqlParameter("@RefundMoney", _bill.RefundMoney),
new MySqlParameter("@EnterpriseRefundLuckyMoney", _bill.EnterpriseRefundLuckyMoney),
new MySqlParameter("@RefundType", _bill.RefundType),
new MySqlParameter("@RefundSta", _bill.RefundSta),
new MySqlParameter("@GoodsName", _bill.GoodsName),
new MySqlParameter("@MerchantData", _bill.MerchantData),
new MySqlParameter("@ExFee", _bill.ExFee),
new MySqlParameter("@Rates", _bill.Rates)
};
if (string.IsNullOrEmpty(_transid))
{
return(MySqlHelperUtil.ExecuteNonQuery(strSql.ToString(), parameters) == 1);
}
else
{
return(MySqlHelperUtil.ExecuteNonQuery(_transid, strSql.ToString(), parameters) == 1);
}
}
public static m_return DownloadBill(DateTime _time)
{
/**
* 下载对账单
* @param DateTime _time 账单日期
* @return m_return DM为SUCCESS表示成功 FALL表示失败 Msg为错误信息成功返回OK
**/
m_return ret = new m_return()
{
DM = "FAIL", Msg = "no result"
};
try
{
//（格式：20140603，一次只能下载一天的对账单）
string strTime = _time.ToString("yyyyMMdd");
//下载所有对账单 时间strTime 类型 ALL
WxPayData result = WXPaySDK.DownloadBill.Run(strTime, "ALL");
if (result.IsSet("return_code"))
{//下载失败
ret.DM = result.GetValue("return_code").ToString();
if (result.IsSet("return_msg"))
{
ret.Msg = result.GetValue("return_msg").ToString();
}
else
{
ret.Msg = "no return_msg";
}
}
else if (result.IsSet("result"))
{//下载成功 返回结果
List <WXBill> BillList = new List <WXBill>();
//合计支付总金额
decimal totalPay = 0;
//合计退款总金额
decimal totalRefund = 0;
//获取数据
string staticData = result.GetValue("result").ToString();
//每一行数据使用"\r\n" 隔开
string[] bilList = staticData.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
//第一行数据为字段名称 每个字段名以','隔开
string[] headList = bilList[0].Split(',');
for (int i = 1; i < bilList.Count() - 2; ++i)
{
string info = bilList[i];
string[] tmpStrList = info.Split(',');
for (int j = 0; j < tmpStrList.Count(); ++j)
{
tmpStrList[j] = tmpStrList[j].Replace("`", "");
}
//账单明细处理 传入字段名称和数据
WXBill bill = new WXBill(headList, tmpStrList);
//加入到明细列表中
BillList.Add(bill);
//统计
totalPay += bill.Cost;
totalRefund += bill.RefundMoney;
}
//获取账单中合计数据 进行比对
string[] totalHead = bilList[bilList.Count() - 2].Split(',');
string[] totalInfo = bilList[bilList.Count() - 1].Split(',');
for (int j = 0; j < totalInfo.Count(); ++j)
{
totalInfo[j] = totalInfo[j].Replace("`", "");
}
WXBillAmount BillAmount = new WXBillAmount(totalHead, totalInfo);
//本地比对成功
if (totalPay == BillAmount.TotalCost && totalRefund == BillAmount.TotalRefundMoney && BillList.Count() == BillAmount.Amount)
{//本地对账成功 插入数据表
//using 帐号可使用zfb 也可在配置中获取
if (billProcessDal.insertBills(BillList))
{
ret.DM = "SUCCESS";
ret.Msg = "OK";
}
else
{
ret.DM = "FAIL";
ret.Msg = "insert record fail";
}
}
}//else DM = "FAIL", Msg = "no result"
}
catch (Exception ex)
{
ret.DM = "FAIL";
ret.Msg = "proccess catch a exception";
Log.Error("bl_billproccess", ex.ToString());
}
return(ret);
}
