public PageOf <RefundInfoModel> GetRefundOrderList(RefundQueryInfo queryInfo, PageDTO page, LanguageEnum languageVersion, int receiptCountry)
{
var db = DbSFO2OMain;
var parameters = db.CreateParameterCollection();
var sql = @"( select r.OrderCode,r.RefundCode,r.UserId,c.UserName,r.RefundStatus,r.CreateTime,r.RefundType,rp.Spu,rp.Sku,p.Name,s.MainDicValue,
s.SubDicValue,s.MainValue,s.SubValue,rp.UnitPrice,rp.RMBUnitPrice,rp.TaxRate,r.TotalAmount,o.ExchangeRate,rp.Quantity,pImg.ImagePath as ProductImagePath
FROM RefundOrderInfo r(NOLOCK)
inner join RefundOrderProducts rp(NOLOCK) on r.RefundCode= rp.RefundCode
inner join SkuInfo s(NOLOCK) on s.Sku= rp.Sku
inner join ProductInfo p(NOLOCK) on p.Id=s.SpuId and p.LanguageVersion=@LanguageVersion
left join ProductImage pImg(NOLOCK) ON pImg.Spu = p.Spu AND pImg.SortValue = 1 AND pImg.ImageType = 1
inner join OrderInfo o(NOLOCK) on r.OrderCode = o.OrderCode
inner join customer c(NOLOCK) on c.ID=r.UserId
where p.SupplierId= @SupplierId ";
if (receiptCountry != 0)
{
sql = sql + " and o.ReceiptCountry=@ReceiptCountry";
}
var query = BindQuery(queryInfo, parameters);
sql = sql + query;
sql += ") pp ";
string SQL = string.Format(@" select * from (select ROW_NUMBER() OVER(order by pp.CreateTime desc) AS RowNum,* from {0}
) as A where A.RowNum BETWEEN (@PageIndex-1)* @PageSize+1 AND @PageIndex*@PageSize ORDER BY RowNum;" ,
sql);
SQL += string.Format(@" SELECT COUNT(1) FROM {0};", sql);
parameters.Append("LanguageVersion", (int)languageVersion);
parameters.Append("SupplierId", queryInfo.SupplierId);
parameters.Append("ReceiptCountry", receiptCountry);
parameters.Append("PageIndex", page.PageIndex);
parameters.Append("PageSize", page.PageSize);
DataSet ds = db.ExecuteSqlDataSet(SQL, parameters);
return(new PageOf <RefundInfoModel>()
{
PageIndex = page.PageIndex,
PageSize = page.PageSize,
Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
Items = DataMapHelper.DataSetToList <RefundInfoModel>(ds)
});
}
private string BindQuery(RefundQueryInfo queryInfo, ParameterCollection dbParameters)
{
var stringBuilder = new StringBuilder();
if (!string.IsNullOrEmpty(queryInfo.refundCode))
{
stringBuilder.Append(" and r.RefundCode=@RefundCode");
dbParameters.Append("RefundCode", queryInfo.refundCode);
}
else
{
if (!string.IsNullOrEmpty(queryInfo.orderCode))
{
stringBuilder.Append(" and r.OrderCode=@OrderCode");
dbParameters.Append("OrderCode", queryInfo.orderCode);
}
if (queryInfo.refundStatus != -1)
{
stringBuilder.Append(" and r.RefundStatus=@RefundStatus");
dbParameters.Append("RefundStatus", queryInfo.refundStatus);
}
if (queryInfo.refundType != 0)
{
stringBuilder.Append(" and r.RefundType=@RefundType");
dbParameters.Append("RefundType", queryInfo.refundType);
}
if (queryInfo.startTime != null)
{
stringBuilder.Append(" and o.CreateTime> @StartTime");
dbParameters.Append("StartTime", queryInfo.startTime.ToString("yyyy-MM-dd 00:00:00"));
}
if (queryInfo.endTime != null)
{
stringBuilder.Append(" and o.CreateTime< @EndTime");
dbParameters.Append("EndTime", queryInfo.endTime.AddDays(1).ToString("yyyy-MM-dd 00:00:00"));
}
}
return(stringBuilder.ToString());
}
public RefundTotalInfo GetRefundTotal(RefundQueryInfo queryInfo, LanguageEnum languageEnum)
{
var db = DbSFO2OMain;
var parameters = db.CreateParameterCollection();
var sql = @" select count(distinct r.RefundCode) as RefundCount,count (distinct rp.Sku) as ProductCount,sum(r.TotalAmount) as RefundAmountTotal
from RefundOrderInfo r (NOLOCK)
inner join RefundOrderProducts rp(NOLOCK) on r.RefundCode= rp.RefundCode
inner join SkuInfo s(NOLOCK) on s.Sku= rp.Sku
inner join ProductInfo p(NOLOCK) on p.Id=s.SpuId and p.LanguageVersion=@LanguageVersion
inner join OrderInfo o(NOLOCK) on r.OrderCode = o.OrderCode
where p.SupplierId= @SupplierId";
var query = BindQuery(queryInfo, parameters);
sql = sql + query;
parameters.Append("LanguageVersion", (int)languageEnum);
parameters.Append("SupplierId", queryInfo.SupplierId);
return(db.ExecuteSqlFirst <RefundTotalInfo>(sql, parameters));
}
public RefundTotalInfo GetRefundTotal(RefundQueryInfo queryInfo, LanguageEnum languageEnum)
{
return(dal.GetRefundTotal(queryInfo, languageEnum));
}
public PageOf <RefundInfoModel> GetRefundOrderList(RefundQueryInfo queryInfo, PageDTO page, LanguageEnum languageVersion, int receiptCountry)
{
return(dal.GetRefundOrderList(queryInfo, page, languageVersion, receiptCountry));
}
public ActionResult RefundList(string startTime, string endTime, string orderCode, string refundCode, int refundType = 0, int receiptCountry = 0, int refundStatus = -1, int page = 1)
{
RefundQueryInfo queryInfo = new RefundQueryInfo();
if (string.IsNullOrEmpty(startTime) || string.IsNullOrEmpty(endTime))
{
queryInfo.startTime = DateTime.Now.AddMonths(-3);
queryInfo.endTime = DateTime.Now;
}
else
{
queryInfo.startTime = DateTime.Parse(startTime);
queryInfo.endTime = DateTime.Parse(endTime);
}
queryInfo.orderCode = orderCode;
queryInfo.refundCode = refundCode;
queryInfo.refundStatus = refundStatus;
queryInfo.SupplierId = CurrentUser.SupplierID;
ViewBag.QueryInfo = queryInfo;
ViewBag.ReceiptCountry = receiptCountry;
ViewBag.Page = page;
try
{
if (receiptCountry == 1)
{
ViewBag.ChinaRefund = bll.GetRefundOrderList(queryInfo, new PageDTO()
{
PageIndex = PageNo, PageSize = 20
}, LanguageEnum.TraditionalChinese, 1);
ViewBag.HKRefund = bll.GetRefundOrderList(queryInfo, new PageDTO()
{
PageIndex = page, PageSize = 20
}, LanguageEnum.TraditionalChinese, 2);
}
else if (receiptCountry == 2)
{
ViewBag.ChinaRefund = bll.GetRefundOrderList(queryInfo, new PageDTO()
{
PageIndex = page, PageSize = 20
}, LanguageEnum.TraditionalChinese, 1);
ViewBag.HKRefund = bll.GetRefundOrderList(queryInfo, new PageDTO()
{
PageIndex = PageNo, PageSize = 20
}, LanguageEnum.TraditionalChinese, 2);
}
else
{
ViewBag.ChinaRefund = bll.GetRefundOrderList(queryInfo, new PageDTO()
{
PageIndex = 1, PageSize = 20
}, LanguageEnum.TraditionalChinese, 1);
ViewBag.HKRefund = bll.GetRefundOrderList(queryInfo, new PageDTO()
{
PageIndex = 1, PageSize = 20
}, LanguageEnum.TraditionalChinese, 2);
}
var total = bll.GetRefundTotal(queryInfo, LanguageEnum.TraditionalChinese);
ViewBag.RefundTotal = total;
}
catch (Exception ex)
{
LogHelper.Error(ex);
}
return(View());
}
public ActionResult ExportRefundList(string startTime, string endTime, string orderCode, string refundCode, int refundType, int refundStatus = -1)
{
RefundQueryInfo queryInfo = new RefundQueryInfo();
if (string.IsNullOrEmpty(startTime) || string.IsNullOrEmpty(endTime))
{
queryInfo.startTime = DateTime.Now.AddMonths(-3);
queryInfo.endTime = DateTime.Now;
}
else
{
queryInfo.startTime = DateTime.Parse(startTime);
queryInfo.endTime = DateTime.Parse(endTime);
}
queryInfo.orderCode = orderCode;
queryInfo.refundStatus = refundStatus;
queryInfo.refundCode = refundCode;
queryInfo.SupplierId = CurrentUser.SupplierID;
try
{
PageOf <RefundInfoModel> list = bll.GetRefundOrderList(queryInfo, new PageDTO()
{
PageIndex = PageNo, PageSize = int.MaxValue
}, LanguageEnum.TraditionalChinese, 0);
var dt = new DataTable();
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 <RefundInfoModel> result = list.Items.ToList <RefundInfoModel>();
result.ForEach(l =>
{
dt.Rows.Add(new object[] {
l.OrderCode,
l.UserId,
l.RefundCode,
l.CreateTime.ToString("yyyy-MM-dd"),
EnumHelper.GetDescription(l.RefundType, typeof(RefundType)),
l.Sku,
l.Name,
"￥" + l.UnitPrice * l.Quantity,
"",
"$" + l.TotalAmount,
EnumHelper.GetDescription(l.RefundStatus, typeof(RefundStatus))
});
});
ExcelHelper.ExportByWeb(dt, "退款單列表", "refundlist" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
}
catch (Exception ext)
{
LogHelper.Error(ext);
}
return(View());
}
/// <summary>
/// 退款
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public RefundQueryResult QueryRefund(RefundQueryInfo info)
{
var result = _httpHelper.Post <RefundQueryResult>(info);
return(result);
}
