public MyResult GetList([FromQuery] OrderInfoParam param)
{
int total;
List <OrderInfo> roleList = OrderBLL.GetOrderList(param, out total);
var result = new
{
total,
list = roleList
};
return(MyResult.OK(result));
}
/// <summary>
/// 查询数据列表
/// </summary>
/// <param name="param"></param>
/// <param name="pageInfo"></param>
/// <returns></returns>
public IActionResult SearchListByPage(OrderInfoParam param, PageInfo pageInfo)
{
param.UserId = CurrentUser.Id;
if (param.BeginDate != null)
{
param.BeginDate = DateTime.Parse(string.Format("{0:yyyy-MM-dd}", param.BeginDate) + " 00:00:00");
}
if (param.EndDate != null)
{
param.EndDate = DateTime.Parse(string.Format("{0:yyyy-MM-dd}", param.EndDate) + " 23:59:59");
}
var result = _orderInfoRepository.SearchListByPage(param, pageInfo);
return(Json(result));
}
public PageModel <OrderInfoView> GetListByPages(PageInfo pageInfo, OrderInfoParam param)
{
string sqlWhere = " where o.isDel = 0 ";
var sqlParam = new List <DbParameter>();
if (param.Status != 0)
{
sqlWhere += " and o.Status = @Status ";
sqlParam.Add(new MySqlParameter("@Status", param.Status));
}
if (!string.IsNullOrEmpty(param.Phone))
{
sqlWhere += " and o.Phone like @Phone ";
sqlParam.Add(new MySqlParameter("@Phone", "%" + param.Phone + "%"));
}
if (!string.IsNullOrEmpty(param.Consignee))
{
sqlWhere += " and o.Consignee like @Consignee ";
sqlParam.Add(new MySqlParameter("@Consignee", "%" + param.Consignee + "%"));
}
if (param.BeginTime != null)
{
sqlWhere += " and o.CreateTime > @BeginTime ";
sqlParam.Add(new MySqlParameter("@BeginTime", param.BeginTime));
}
if (param.EndTime != null)
{
sqlWhere += " and o.CreateTime < @EndTime ";
sqlParam.Add(new MySqlParameter("@EndTime", param.EndTime));
}
string sql = @"select o.id as OrderId, o.OrderNo, CONCAT(o.ProvinceName,o.CityName,o.CountyName, ' ',o.DetailedAddress) Address,
o.Consignee,o.Phone,o.BuyQuantity,o.Message,o.OrderAmount,o.Status,o.CreateTime,g.SpecificationDesc,
sg.TrackingNo,sg.CourierCompany,sg.Remarks
from orderinfo o
LEFT JOIN goodsinfo g on o.GoodsId = g.Id
LEFT JOIN SendGoodsInfo sg on o.id = sg.OrderId " + sqlWhere + " order by o.createTime ";
string sqlCount = "select count(*) from OrderInfo o " + sqlWhere;
var list = _context.Database.SqlQuery <OrderInfoView>(sql.ToPaginationSql(pageInfo), sqlParam.ToArray());
var count = _context.Database.ExcuteSclare(sqlCount, sqlParam.ToArray());
return(new PageModel <OrderInfoView>(list, pageInfo.PageIndex, pageInfo.PageSize, Convert.ToInt32(count)));
}
/// <summary>
/// 后台订单列表查询
/// </summary>
/// <param name="param"></param>
/// <param name="pageInfo"></param>
/// <returns></returns>
public PageModel <OrderInfoView2> SearchListByPage(OrderInfoParam param, PageInfo pageInfo)
{
var sqlParam = new List <DbParameter>();
string sqlWhere = " where oi.isDel=0 and oi.userId=@UserId ";
sqlParam.Add(new MySqlParameter("@UserId", param.UserId));
if (param.BeginDate != null)
{
sqlWhere += " and oi.CreateTime > @BeginDate ";
sqlParam.Add(new MySqlParameter("@BeginDate", param.BeginDate));
}
if (param.EndDate != null)
{
sqlWhere += " and oi.CreateTime < @EndDate ";
sqlParam.Add(new MySqlParameter("@EndDate", param.EndDate));
}
if (!string.IsNullOrEmpty(param.ClientName))
{
sqlWhere += " and (ci.ClientName like @ClientName or ci.ClientPhone like @ClientName) ";
sqlParam.Add(new MySqlParameter("@ClientName", "%" + param.ClientName + "%"));
}
string sql = $@"SELECT oi.*,ci.ClientName,ci.ClientPhone
from orderinfo oi
LEFT JOIN clientinfo ci on oi.clientId = ci.id {sqlWhere} order by oi.id desc ";
string sqlCount = $@"SELECT count(*)
from orderinfo oi
LEFT JOIN clientinfo ci on oi.clientId = ci.id {sqlWhere} ";
var list = _context.Database.SqlQuery <OrderInfoView2>(sql.ToPaginationSql(pageInfo), sqlParam.ToArray());
var count = _context.Database.ExcuteSclare(sqlCount, sqlParam.ToArray());
return(new PageModel <OrderInfoView2>(list, pageInfo.PageIndex, pageInfo.PageSize, Convert.ToInt32(count)));
}
/// <summary>
/// 根据查询条件获取分页数据
/// </summary>
/// <param name="pageInfo"></param>
/// <param name="param"></param>
/// <returns></returns>
public IActionResult GetListByPages(PageInfo pageInfo, OrderInfoParam param)
{
var result = _orderInfoRepository.GetListByPages(pageInfo,param);
return JsonOk(result);
}
public MyResult GetDetail([FromQuery] OrderInfoParam param)
{
OrderInfo orderInfo = OrderBLL.GetOrderDetail(param);
return(MyResult.OK(orderInfo));
}
internal static List <OrderInfo> GetOrderList(OrderInfoParam searchParam, out int total)
{
int offset = (searchParam.PageIndex - 1) * searchParam.PageSize;
int rows = searchParam.PageSize;
List <OrderInfo> recordList = new List <OrderInfo>();
string sql = @" SELECT o.ID, ORDER_NUM, BATCH_NUMBER,JBBW_PHONE, JBBW_NAME,FLIGHT_NUM, LANDING_TIME, `STATUS`, o.CreateTime, e.ChineseName
FROM ex_orderinfo o
LEFT JOIN mt_employee e ON o.CreatedBy = e.UserName
{0}
ORDER by o.CreateTime DESC
LIMIT {1},{2} ";
string where = " WHERE 1=1 ";
List <MySqlParameter> param = new List <MySqlParameter>();
if (!string.IsNullOrEmpty(searchParam.OrderNumber))
{
where += " AND ORDER_NUM = @OrderNumber";
param.Add(new MySqlParameter("@OrderNumber", searchParam.OrderNumber));
}
if (!string.IsNullOrEmpty(searchParam.Status))
{
where += " AND `STATUS` = @Status";
param.Add(new MySqlParameter("@Status", searchParam.Status));
}
if (!string.IsNullOrEmpty(searchParam.CreateTimeStartStr))
{
where += " AND o.CreateTime > @CreateTimeStart";
param.Add(new MySqlParameter("@CreateTimeStart", Convert.ToDateTime(searchParam.CreateTimeStartStr)));
}
if (!string.IsNullOrEmpty(searchParam.CreateTimeEndStr))
{
where += " AND o.CreateTime < @CreateTimeEnd";
param.Add(new MySqlParameter("@CreateTimeEnd", Convert.ToDateTime(searchParam.CreateTimeEndStr)));
}
if (!string.IsNullOrEmpty(searchParam.BatchNo))
{
where += " AND BATCH_NUMBER = @BatchNo";
param.Add(new MySqlParameter("@BatchNo", searchParam.BatchNo));
}
if (!string.IsNullOrEmpty(searchParam.FlightNumber))
{
where += " AND FLIGHT_NUM = @FlightNumber";
param.Add(new MySqlParameter("@FlightNumber", searchParam.FlightNumber));
}
if (!string.IsNullOrEmpty(searchParam.KeyWord))
{
where += " AND JBBW_PHONE like CONCAT('%',@KeyWord,'%') OR JBBW_NAME like CONCAT('%',@KeyWord,'%')";
param.Add(new MySqlParameter("@KeyWord", searchParam.KeyWord));
}
DataTable dt = JabMySqlHelper.ExecuteDataTable(Config.DBConnection, string.Format(sql, where, offset, rows), param.ToArray());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
recordList.Add(new OrderInfo()
{
ID = Converter.TryToInt64(row["ID"]),
OrderNumber = Converter.TryToString(row["ORDER_NUM"]),
BatchNo = Converter.TryToString(row["BATCH_NUMBER"]),
JBBWPhone = Converter.TryToString(row["JBBW_PHONE"]),
JBBWName = Converter.TryToString(row["JBBW_NAME"]),
FlightNumber = Converter.TryToString(row["FLIGHT_NUM"]),
LandingTime = string.IsNullOrEmpty(Converter.TryToString(row["LANDING_TIME"])) ? "" : Converter.TryToDateTime(row["LANDING_TIME"]).ToString("yyyy-MM-dd HH:mm:ss"),
Status = Converter.TryToString(row["STATUS"]),
StatusStr = OrderStatus.GetStatus(Converter.TryToString(row["STATUS"])),
CreateTime = Converter.TryToDateTime(row["CreateTime"]).ToString("yyyy-MM-dd HH:mm:ss"),
CreatedBy = Converter.TryToString(row["ChineseName"])
});
}
}
// 查询总数
string sqlCount = @"SELECT count(*) FROM ex_orderinfo o {0}";
object re = JabMySqlHelper.ExecuteScalar(Config.DBConnection, string.Format(sqlCount, where), param.ToArray());
total = Convert.ToInt32(re);
return(recordList);
}
internal static OrderInfo GetOrderDetail(OrderInfoParam searchParam)
{
OrderInfo orderInfo = new OrderInfo();
string sql = @"SELECT `ID`,
`ORDER_NUM`,
`USER_ID`,
`SENDER_PHONE`,
`SENDER_NAME`,
`SENDER_ADDRESS`,
`JBBW_PHONE`,
`JBBW_NAME`,
`JBBW_ADDRESS`,
`REMARKS`,
`WEIGHT`,
`FLIGHT_NUM`,
`LANDING_TIME`,
`STATUS`,
`BATCH_NUMBER`,
`CreateTime`,
`CreatedBy`,
`UpdateTime`,
`UpdateBy`
FROM `ex_orderinfo`
{0};";
List <MySqlParameter> param = new List <MySqlParameter>();
string where;
if (searchParam.ID != null)
{
where = " WHERE `ID` = @ID";
param.Add(new MySqlParameter("@ID", searchParam.ID));
}
else if (!string.IsNullOrEmpty(searchParam.OrderNumber))
{
where = " WHERE ORDER_NUM = @OrderNumber";
param.Add(new MySqlParameter("@OrderNumber", searchParam.OrderNumber));
}
else
{
throw new MsgException("参数错误！");
}
DataTable dt = JabMySqlHelper.ExecuteDataTable(Config.DBConnection, string.Format(sql, where), param.ToArray());
if (dt != null && dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
OrderInfo orderinfo = new OrderInfo()
{
ID = Converter.TryToInt64(row["ID"]),
OrderNumber = Converter.TryToString(row["ORDER_NUM"]),
SenderPhone = Converter.TryToString(row["SENDER_PHONE"]),
SenderName = Converter.TryToString(row["SENDER_NAME"]),
SenderAddress = Converter.TryToString(row["SENDER_ADDRESS"]),
JBBWPhone = Converter.TryToString(row["JBBW_PHONE"]),
JBBWName = Converter.TryToString(row["JBBW_NAME"]),
JBBWAddress = Converter.TryToString(row["JBBW_ADDRESS"]),
Remarks = Converter.TryToString(row["REMARKS"]),
Weight = Converter.TryToString(row["WEIGHT"]),
FlightNumber = Converter.TryToString(row["FLIGHT_NUM"]),
LandingTime = string.IsNullOrEmpty(Converter.TryToString(row["LANDING_TIME"])) ? "" : Converter.TryToDateTime(row["LANDING_TIME"]).ToString("yyyy-MM-dd HH:mm:ss"),
Status = Converter.TryToString(row["STATUS"]),
StatusStr = OrderStatus.GetStatus(Converter.TryToString(row["STATUS"])),
BatchNo = Converter.TryToString(row["BATCH_NUMBER"]),
CreateTime = Converter.TryToDateTime(row["CreateTime"]).ToString("yyyy-MM-dd HH:mm:ss"),
CreatedBy = Converter.TryToString(row["CreatedBy"]),
UpdateTime = Converter.TryToDateTime(row["UpdateTime"]).ToString("yyyy-MM-dd HH:mm:ss"),
UpdatedBy = Converter.TryToString(row["UpdateBy"]),
};
orderinfo.BatchNo = string.IsNullOrEmpty(orderinfo.BatchNo) ? null : orderinfo.BatchNo;
return(orderinfo);
}
else
{
return(null);
}
}