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); }