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