public ActionResult GetCsOrderPage(CsOrderView.CsOrderWhere para)
{
var data = GetList(para);
if (!data.Data.Any())
{
return(Json(new
{
data = data.Data,
sql = data.Sql,
total = data.Total
}));
}
var ds = _csOrderDetailBll.GetModelList($" AND OrderId IN ({string.Join(",", data.Data.Select(x => x.OrderId))}) ");
var list = data.Data.Select(x => new
{
x.OrderId,
x.OrderNumber,
OrderDate = x.OrderDate.ToString("yyyy-M-d HH:mm:ss"),
OrderState = ((OrderState)x.OrderState).ToString(),
RowStatus = ((RowStatus)x.RowStatus).ToString(),
DeleteDate = x.DeleteDate.ToString("yyyy-M-d HH:mm:ss"),
x.DeleteDescribe,
UserName = x.UserId + " / " + x.UserName + $"({x.UserSex}) / " + x.UserPhone,
ShortUserName = (x.UserId + " / " + x.UserName + $"({x.UserSex}) / " + x.UserPhone).SubString(24),
OrderAddress = x.OrderAddress.Trim('$').Replace("$$$", " / ").Replace("$$", " / ").Replace("$", " / "),
ShortOrderAddress = x.OrderAddress.Trim('$').Replace("$$$", " / ").Replace("$$", " / ").Replace("$", " / ").SubString(30),
TotalMoney = "￥" + x.TotalMoney.ToString("N2"),
OrderSource = ds.Any(d => d.OrderId == x.OrderId && d.ChoseType == ChoseType.套餐.GetHashCode()) ? "企业团购" : "电商代发",
IsInvoice = x.IsInvoice == 0 ? "否" : "是",
ShortOrderRemarks = x.OrderRemarks.SubString(16).ShowNullOrEmpty("-无-"),
OrderRemarks = x.OrderRemarks.ShowNullOrEmpty("-无-")
});
return(Json(new
{
data = list,
sql = data.Sql,
total = data.Total
}));
}
/// <summary>
/// 获取当前条件的列表(用于分页查询和导出)
/// </summary>
/// <param name="para">条件参数</param>
/// <param name="isPage">是否分页</param>
/// <returns></returns>
private PageInfo <CsOrderView.CsOrderPage> GetList(CsOrderView.CsOrderWhere para, bool isPage = true)
{
var sh = new SqlHelper <CsOrderView.CsOrderPage>("CsOrder")
{
PageConfig = new PageConfig
{
PageIndex = isPage ? para.CurrentPage : 0,
PageSize = isPage ? PageSize : 0,
PageSortField = "co." + CsOrderEnum.OrderId,
SortEnum = SortEnum.Desc
},
Alia = "co"
};
sh.AddShow("co." + CsOrderEnum.OrderId);
sh.AddShow(CsOrderEnum.OrderAddress);
sh.AddShow(CsOrderEnum.OrderNumber);
sh.AddShow("co." + CsOrderEnum.UserId);
sh.AddShow(CsOrderEnum.TotalMoney);
sh.AddShow(CsOrderEnum.IsInvoice);
sh.AddShow(CsOrderEnum.OrderRemarks);
sh.AddShow(CsOrderEnum.DiscountMoney);
sh.AddShow(CsOrderEnum.ActualMoney);
sh.AddShow(CsOrderEnum.OrderDate);
sh.AddShow(CsOrderEnum.OrderState);
sh.AddShow(CsOrderEnum.RowStatus);
sh.AddShow(CsOrderEnum.DeleteDate);
sh.AddShow(CsOrderEnum.DeleteDescribe);
sh.AddShow(CsUsersEnum.UserName);
sh.AddShow(CsUsersEnum.UserPhone);
sh.AddShow(CsUsersEnum.UserSex);
sh.AddShow(CsOrderEnum.SendAddress);
sh.AddShow(CsOrderEnum.BillWeight);
sh.AddShow(CsOrderEnum.TotalWeight);
sh.AddShow(CsOrderEnum.CargoNumber);
sh.AddShow(CsOrderEnum.OrderCopies);
sh.AddJoin(JoinEnum.LeftJoin, "CsUsers", "cu", "UserId", "UserId");
if (para.RowStatus > -1)
{
sh.AddWhere(CsOrderEnum.RowStatus, para.RowStatus);
}
//if (para.ActualStart > 0)
// sh.AddWhere(CsOrderEnum.ActualMoney, para.ActualStart, RelationEnum.GreaterEqual);
//if (para.ActualEnd > 0)
// sh.AddWhere(CsOrderEnum.ActualMoney, para.ActualEnd, RelationEnum.LessEqual);
//if (para.DiscountStart > 0)
// sh.AddWhere(CsOrderEnum.DiscountMoney, para.DiscountStart, RelationEnum.GreaterEqual);
//if (para.DiscountEnd > 0)
// sh.AddWhere(CsOrderEnum.DiscountMoney, para.DiscountEnd, RelationEnum.LessEqual);
if (para.TotalStart > 0)
{
sh.AddWhere(CsOrderEnum.TotalMoney, para.TotalStart, RelationEnum.GreaterEqual);
}
if (para.TotalEnd > 0)
{
sh.AddWhere(CsOrderEnum.TotalMoney, para.TotalEnd, RelationEnum.LessEqual);
}
if (!para.OrderId.IsNullOrEmpty())
{
sh.AddWhere(CsOrderEnum.OrderNumber, para.OrderId, RelationEnum.Like);
}
if (para.Status > -1)
{
sh.AddWhere(CsOrderEnum.OrderState, para.Status);
}
if (!para.UserName.IsNullOrEmpty())
{
if (para.UserName.ToInt() > 0)
{
sh.AddWhere("cu." + CsUsersEnum.UserId, para.UserName.ToInt());
}
else
{
sh.AddWhere("cu." + CsUsersEnum.UserName, para.UserName, RelationEnum.Like);
}
}
if (!para.UserPhone.IsNullOrEmpty())
{
sh.AddWhere("cu." + CsUsersEnum.UserPhone, para.UserPhone, RelationEnum.Like);
}
//#region 订单来源条件
//if (!para.OrderSource.IsNullOrEmpty() && para.OrderSource.ToInt() == ChoseType.套餐.GetHashCode()) // 电商代发
//{
// sh.AddJoin(JoinEnum.LeftJoin, "CsOrderDetail", "cod", "OrderId", "OrderId", $" AND cod.ChoseType = {ChoseType.套餐.GetHashCode()} ");
//}
//#endregion
if (para.Time.Count > 0)
{
if (!para.Time[0].IsNullOrEmpty())
{
sh.AddWhere(CsOrderEnum.OrderDate, para.Time[0], RelationEnum.GreaterEqual);
}
if (!para.Time[1].IsNullOrEmpty())
{
sh.AddWhere(CsOrderEnum.OrderDate, para.Time[1], RelationEnum.LessEqual);
}
}
if (!para.IsInvoice.IsNullOrEmpty())
{
sh.AddWhere(CsOrderEnum.IsInvoice, para.IsInvoice.ToInt());
}
if (!para.OrderAddress.IsNullOrEmpty())
{
sh.AddWhere(CsOrderEnum.OrderAddress, para.OrderAddress, RelationEnum.Like);
}
return(new PageInfo <CsOrderView.CsOrderPage>
{
Data = sh.Select().ToList(),
Total = sh.Total,
Sql = sh.SqlString.ToString()
});
}
public ActionResult ExportCsOrder(CsOrderView.CsOrderWhere para)
{
var data = GetList(para, false);
if (!data.Data.Any())
{
return(Json(new ResModel
{
Data = "没有任何可以导出数据",
ResStatus = ResStatue.No
}));
}
var list = new List <CsOrderView.CsOrderExcel>();
var detail = _csOrderDetailBll.GetModelList($" AND OrderId IN ({string.Join(",", data.Data.Select(x => x.OrderId))})");
// 配件/螃蟹/套餐
var parts = _csPartsBll.GetModelList($" AND PartId IN ({string.Join(",", detail.Where(x => x.ChoseType == ChoseType.配件.GetHashCode()).Select(x => x.ProductId)).ShowNullOrEmpty("0")}) AND PartType = {PartType.可选配件.GetHashCode()}");
var products = _csProductsBll.GetModelList($" AND ProductId IN ({string.Join(",", detail.Where(x => x.ChoseType == ChoseType.螃蟹.GetHashCode()).Select(x => x.ProductId)).ShowNullOrEmpty("0")})");
IEnumerable <CsPackage> packages = null;
if (detail.Any(x => x.ChoseType == ChoseType.套餐.GetHashCode()))
{
packages = _csPackageBll.GetModelList($" AND PackageId IN ({string.Join(",", detail.Where(x => x.ChoseType == ChoseType.套餐.GetHashCode()).Select(x => x.ProductId))})");
}
foreach (var order in data.Data)
{
var total = string.Empty;
foreach (var d in detail.Where(x => x.OrderId == order.OrderId).OrderBy(x => x.ChoseType))
{
if (d.ChoseType == ChoseType.套餐.GetHashCode())
{
total += packages?.FirstOrDefault(x => x.PackageId == d.ProductId)?.PackageNumber + "" + d.ProductNumber;
continue;
}
if (d.ChoseType == ChoseType.螃蟹.GetHashCode())
{
total += products.FirstOrDefault(x => x.ProductId == d.ProductId)?.ProductNumber + "" + d.ProductNumber;
continue;
}
if (total.IndexOf("P", StringComparison.Ordinal) < 0 &&
total.IndexOf("-", StringComparison.Ordinal) < 0)
{
total += "-";
}
total += parts.FirstOrDefault(x => x.PartId == d.ProductId)?.PartNumber;
}
var sendInfo = order.SendAddress.Split('$');
var putInfo = order.OrderAddress.Split('$');
list.Add(new CsOrderView.CsOrderExcel
{
用户订单号 = order.OrderNumber,
寄件公司 = "-",
寄联系人 = sendInfo.Length > 0 ? sendInfo[0] : "-",
寄联系电话 = sendInfo.Length > 1 ? sendInfo[1] : "-",
寄件地址 = sendInfo.Length > 2 ? sendInfo[2] : "-",
收件公司 = putInfo.Length > 0 ? putInfo[0] : "-",
联系人 = putInfo.Length > 1 ? putInfo[1] : "-",
联系电话 = putInfo.Length > 2 ? putInfo[2] : "-",
手机号码 = putInfo.Length > 3 ? putInfo[3] : "-",
收件详细地址 = putInfo.Length > 4 ? putInfo[4] : "-",
付款方式 = "寄付月结",
第三方付月结卡号 = "",
寄托物品 = "其他",
寄托物内容 = total.TrimEnd('-').IsNullOrEmpty() ? "" : $"{total.TrimEnd('-')}-T{detail.Where(x => x.OrderId == order.OrderId && (x.ChoseType == ChoseType.螃蟹.GetHashCode() || x.ChoseType == ChoseType.套餐.GetHashCode())).Sum(x => x.ProductNumber)}",
寄托物编号 = "",
寄托物数量 = order.CargoNumber.ToString(),
件数 = order.OrderCopies.ToString(),
实际重量单位KG = "-",
计费重量单位KG = "-",
业务类型 = "大闸蟹专递",
扩展字段1 = detail.Any(x => x.OrderId == order.OrderId && x.ChoseType == ChoseType.套餐.GetHashCode()) ? "企业团购" : "电商代发",
扩展字段2 = order.OrderRemarks
});
}
var path = $"excel/{DateTime.Now:yyyyMMddHHmmssffff}.xls";
try
{
NpoiHelper.ExportToExcel(list.ToDataTable(), "D:/" + path);
}
catch (Exception e)
{
return(Json(new ResModel
{
Data = e.Message,
ResStatus = ResStatue.No
}));
}
return(Json(new ResModel
{
Data = "../" + path,
ResStatus = ResStatue.Yes
}));
}
