// GET: Refund
public ActionResult RefundIndex(int isFinance = 0)
{
var dtNow = DateTime.Now;
ViewBag.SallerNames = new SupplierBLL().GetSupplierNames();
var qm = new RefundQueryModel()
{
Sku = Request["Sku"] == null ? "" : Request["Sku"].ToString(),
RegionCode = Request["RegionCode"] == null ? 1 : Convert.ToInt32(Request["RegionCode"].ToString()),
RefundType = Request["RefundType"] == null ? -1 : Convert.ToInt32(Request["RefundType"].ToString()),
RefundStatus = Request["RefundStatus"] == null ? -1 : Convert.ToInt32(Request["RefundStatus"].ToString()),
RefundCode = Request["RefundCode"] == null ? "" : Request["RefundCode"].ToString(),
BuyerName = Request["BuyerName"] == null ? "" : Request["BuyerName"].ToString(),
SellerName = Request["SellerName"] == null ? 0 : Convert.ToInt32(Request["SellerName"].ToString()),
OrderCode = Request["OrderCode"] == null ? "" : Request["OrderCode"].ToString(),
EndTime = Request["EndTime"] == null ? dtNow : Convert.ToDateTime(Request["EndTime"].ToString()),
StartTime = Request["StartTime"] == null?dtNow.AddMonths(-6) : Convert.ToDateTime(Request["StartTime"].ToString()),
IsFinance = isFinance
};
ViewBag.PageNo = Request["PageNo"] == null ? 1 : Convert.ToInt32(Request["PageNo"].ToString());
ViewBag.PageSize = Request["PageSize"] == null ? 50 : Convert.ToInt32(Request["PageSize"].ToString());
return(View("RefundIndex", qm));
}
public async Task <IActionResult> RefundQuery(RefundQueryModel refundQuery)
{
if (string.IsNullOrWhiteSpace(refundQuery.NonceStr))
{
refundQuery.NonceStr = TenPayV3Util.GetNoncestr();
}
if (string.IsNullOrWhiteSpace(refundQuery.OutTradeNo) &&
string.IsNullOrWhiteSpace(refundQuery.TransactionId) &&
string.IsNullOrWhiteSpace(refundQuery.OutRefundNo) &&
string.IsNullOrWhiteSpace(refundQuery.RefundId))
{
return(BadRequest("需要OutTradeNo,TransactionId,OutRefundNo,RefundId之一"));
}
refundQuery.SignType = "MD5";
TenPayV3RefundQueryRequestData dataInfo = new TenPayV3RefundQueryRequestData(
appId: TenPyConfigRead.AppId,
mchId: TenPyConfigRead.MchId,
key: TenPyConfigRead.Key,
nonceStr: refundQuery.NonceStr,
deviceInfo: refundQuery.DeviceInfo,
transactionId: refundQuery.TransactionId,
outTradeNo: refundQuery.OutTradeNo,
outRefundNo: refundQuery.OutRefundNo,
refundId: refundQuery.RefundId,
offset: refundQuery.Offset
);
var result = await TenPayV3.RefundQueryAsync(dataInfo);
var log = _logger.CreateLogger("查询退款");
if (result.return_code == "FAIL")
{
log.LogError($"商家订单号(OutTradeNo):{refundQuery.OutTradeNo} 通讯标记(return_code):{result.return_code} {result.return_msg}");
}
if (result.result_code == "FAIL")
{
log.LogError($"商家订单号(OutTradeNo):{refundQuery.OutTradeNo} 业务结果(result_code):{result.result_code}\n{result.err_code}:{result.err_code_des}");
}
else if (result.result_code == "SUCCESS")
{
log.LogInformation($"商家订单号(OutTradeNo):{refundQuery.OutTradeNo} 业务结果(result_code):{result.result_code}");
}
return(Ok(new
{
respond = result,
request = refundQuery
}));
}
public ActionResult RefundList(RefundQueryModel qm)
{
PagedList <RefundOrderListInfo> list = null;
try
{
list = refund.getOrderList(qm, new Models.PagingModel {
IsPaging = true, PageIndex = this.PageNo, PageSize = this.PageSize
});
ViewBag.IsFinance = qm.IsFinance;
}
catch (Exception ext)
{
LogHelper.Error(ext);
}
return(View(list));
}
/// <summary>
/// 获取退款单列表
/// </summary>
/// <param name="queryModel"></param>
/// <param name="pModel"></param>
/// <returns></returns>
public PagedList <RefundOrderListInfo> getOrderList(RefundQueryModel queryModel, PagingModel pModel)
{
return(dao.getOrderList(queryModel, pModel));
}
/// <summary>
/// 获取退款单列表
/// </summary>
/// <param name="queryModel"></param>
/// <param name="pModel"></param>
/// <returns></returns>
public PagedList <RefundOrderListInfo> getOrderList(RefundQueryModel queryModel, PagingModel pModel)
{
var sql = @"
SELECT *
FROM (
SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY roi.Createtime DESC),
roi.OrderCode,
BuyerName = c.UserName,
roi.RefundCode,
roi.CreateTime,
roi.RefundType,
roi.ImagePath,
SellerName = s.CompanyName,
rop.Sku,
ProductName = p.Name,
ProductImgPath = pimg.ImagePath,
RmbProductAmount = ISNULL(rop.RmbUnitPrice,0) * ISNULL(Quantity,0),
ProductAmount = ISNULL(UnitPrice,0) * ISNULL(Quantity,0),
roi.RmbDutyAmount,
roi.DutyAmount,
roi.RmbTotalAmount,
roi.TotalAmount,
roi.RefundStatus,
si.MainDicValue,
si.MainValue,
si.SubDicValue,
si.SubValue,
p.IsReturn,
rop.Spu
FROM RefundOrderInfo roi(NOLOCK)
INNER JOIN RefundOrderProducts rop(NOLOCK) ON rop.RefundCode = roi.RefundCode
INNER JOIN ProductInfo p(NOLOCK) ON p.Spu = rop.Spu AND p.LanguageVersion = 2
INNER JOIN SkuInfo si(NOLOCK) ON si.Sku = rop.Sku AND si.Spu = rop.Spu AND si.SpuId = p.Id
LEFT JOIN ProductImage pimg(NOLOCK) ON pimg.Spu = rop.Spu AND pimg.SortValue = 1
INNER JOIN Customer c(NOLOCK) ON c.ID = roi.UserId
INNER JOIN Supplier s(NOLOCK) ON s.SupplierID = roi.SupplierId
WHERE 1=1
AND roi.RegionCode = @RegionCode
{0}
{1}
{2}
{3}
{4}
{5}
{6}
{7}
{8}
{9}
) a
WHERE 1=1
{10}
SELECT TotalCount = COUNT(roi.RefundCode),
RmbProductAmount = SUM(ISNULL(ISNULL(rop.RmbUnitPrice,0) * ISNULL(Quantity,0),0)),
ProductAmount = SUM(ISNULL(ISNULL(UnitPrice,0) * ISNULL(Quantity,0),0)),
RmbDutyAmount = SUM(ISNULL(roi.RmbDutyAmount,0)),
DutyAmount = SUM(ISNULL(roi.DutyAmount,0)),
RmbTotalAmount = SUM(ISNULL(roi.RmbTotalAmount,0)),
TotalAmount = SUM(ISNULL(roi.TotalAmount,0)),
ProductQuantity = SUM(ISNULL(rop.Quantity,0))
FROM RefundOrderInfo roi(NOLOCK)
INNER JOIN RefundOrderProducts rop(NOLOCK) ON rop.RefundCode = roi.RefundCode
INNER JOIN ProductInfo p(NOLOCK) ON p.Spu = rop.Spu AND p.LanguageVersion = 2
INNER JOIN SkuInfo si(NOLOCK) ON si.Sku = rop.Sku AND si.Spu = rop.Spu AND si.SpuId = p.Id
LEFT JOIN ProductImage pimg(NOLOCK) ON pimg.Spu = rop.Spu AND pimg.SortValue = 1
INNER JOIN Customer c(NOLOCK) ON c.ID = roi.UserId
INNER JOIN Supplier s(NOLOCK) ON s.SupplierID = roi.SupplierId
WHERE 1=1
AND roi.RegionCode = @RegionCode
{0}
{1}
{2}
{3}
{4}
{5}
{6}
{7}
{8}
{9}
";
sql = string.Format(sql, queryModel.StartTime != null ? " AND roi.CreateTime >=@StartTime" : "",
queryModel.EndTime != null ? " AND roi.CreateTime <=@EndTime" : "",
queryModel.RefundStatus > -1 ? " AND roi.RefundStatus = @RefundStatus " : "",
queryModel.RefundType > -1 ? " AND roi.RefundType = @RefundType " : "",
!string.IsNullOrEmpty(queryModel.BuyerName) ? " AND c.UserName LIKE '%'+@BuyerName+'%' " : "",
!(queryModel.SellerName <= 0) ? " AND (s.SupplierID=@SellerName) " : "",
!string.IsNullOrEmpty(queryModel.Sku) ? " AND si.Sku LIKE '%'+@Sku+'%' " : "",
!string.IsNullOrEmpty(queryModel.OrderCode) ? " AND roi.OrderCode LIKE '%'+@OrderCode+'%' " : "",
!string.IsNullOrEmpty(queryModel.RefundCode) ? " AND roi.RefundCode LIKE '%'+@RefundCode+'%' " : "",
queryModel.IsFinance == 1 ? " AND roi.RefundStatus IN (3,4) " : "",
pModel.IsPaging ? " AND a.RowNumber >(@PageIndex-1)*@PageSize AND a.RowNumber <= @PageIndex*@PageSize" : "");
var db = DbSFO2ORead;
var parameters = db.CreateParameterCollection();
parameters.Append("@RegionCode", queryModel.RegionCode);
parameters.Append("@StartTime", queryModel.StartTime);
parameters.Append("@EndTime", queryModel.EndTime.AddDays(1));
parameters.Append("@RefundStatus", queryModel.RefundStatus);
parameters.Append("@RefundType", queryModel.RefundType);
parameters.Append("@BuyerName", queryModel.BuyerName);
parameters.Append("@SellerName", queryModel.SellerName);
parameters.Append("@Sku", queryModel.Sku);
parameters.Append("@RefundCode", queryModel.RefundCode);
parameters.Append("@OrderCode", queryModel.OrderCode);
parameters.Append("@PageIndex", pModel.PageIndex);
parameters.Append("@PageSize", pModel.PageSize);
var ds = db.ExecuteSqlDataSet(sql, parameters);
PagedList <RefundOrderListInfo> result = new PagedList <RefundOrderListInfo>();
result.ContentList = ds.Tables[0].ToList <RefundOrderListInfo>();
result.CurrentIndex = pModel.PageIndex;
result.PageSize = pModel.PageSize;
result.TotalObject = ds.Tables[1].ToList <RefundOrderListInfo>().First();
result.RecordCount = result.TotalObject.TotalCount;
return(result);
}
public ActionResult ExportRefundList(DateTime startTime, DateTime endTime, string sku, string refundCode, string buyerName, string sellerName, string orderCode, int refundType, int refundStatus, int regionCode, int isFinance = 0)
{
var dtNow = DateTime.Now;
PagedList <RefundOrderListInfo> list = null;
var qm = new RefundQueryModel()
{
Sku = string.IsNullOrEmpty(sku) ? "" : sku,
RegionCode = regionCode == 0 ? 1 : regionCode,
RefundType = refundType == 0 ? -1 : refundType,
RefundStatus = refundStatus == 0 ? -1 : refundStatus,
RefundCode = string.IsNullOrEmpty(refundCode) ? "" : refundCode,
BuyerName = string.IsNullOrEmpty(buyerName) ? "" : buyerName,
SellerName = string.IsNullOrEmpty(sellerName) ? 0 : Convert.ToInt32(sellerName),
OrderCode = string.IsNullOrEmpty(orderCode) ? "" : orderCode,
EndTime = endTime == null ? dtNow : endTime,
StartTime = startTime == null?dtNow.AddMonths(-6) : startTime,
IsFinance = isFinance
};
try
{
list = refund.getOrderList(qm, new Models.PagingModel {
IsPaging = false, PageIndex = this.PageNo, PageSize = this.PageSize
});
var dt = new DataTable();
dt.Columns.Add("訂單號");
dt.Columns.Add("買家");
dt.Columns.Add("退款單號");
dt.Columns.Add("生成時間");
dt.Columns.Add("退款類型");
dt.Columns.Add("賣家賬號");
dt.Columns.Add("SKU");
dt.Columns.Add("商品");
dt.Columns.Add("商品金額");
dt.Columns.Add("關稅");
dt.Columns.Add("退款金額");
dt.Columns.Add("狀態");
list.ContentList.ForEach(l =>
{
var attr = " ";
if (!string.IsNullOrEmpty(l.MainDicValue))
{
attr += l.MainDicValue + "：" + l.MainValue + " ";
}
if (!string.IsNullOrEmpty(l.SubDicValue))
{
attr += l.SubDicValue + "：" + l.SubValue + " ";
}
dt.Rows.Add(new object[] {
l.OrderCode,
l.BuyerName,
l.RefundCode,
l.CreateTime.ToString("yyyy-MM-dd"),
SFO2O.Admin.Common.EnumUtils.GetEnumDescription((SFO2O.Admin.Models.RefundType)l.RefundType),
l.SellerName,
l.Sku,
l.ProductName + attr,
"￥" + l.RmbProductAmount,
"￥" + l.RmbDutyAmount,
"￥" + l.RmbTotalAmount,
SFO2O.Admin.Common.EnumUtils.GetEnumDescription((SFO2O.Admin.Models.RefundStatus)l.RefundStatus)
});
});
ExcelHelper.ExportByWeb(dt, "退款單列表", "refundlist" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
}
catch (Exception ext)
{
LogHelper.Error(ext);
}
return(View());
}