//查找订单列表 public List <AdminGoodsOrder> GetAdminList(string where, int pagesize, int pageindex, out int totalCount, string goodsName = "", string accName = "", string accPhone = "", string orderState = "", bool export = false) { List <AdminGoodsOrder> list = new List <AdminGoodsOrder>(); string sql; string sqlCount; sql = $@"select orders.Id,orders.OrderId,orders.BuyPrice,orders.UserId,orders.AccepterName as NickName,orders.AccepterTelePhone as TelePhone,orders.Message,orders.CreateDate,orders.State,orders.OrderNum,orders.Remark,orders.FreightPrice,orders.Address from miniappentgoodsorder orders {(string.IsNullOrEmpty(where) ? "" : " where " + where)} order by Id desc {(pagesize == 0 ? "" : " limit " + (pageindex <= 0 ? 0 : pageindex - 1) * pagesize + "," + pagesize)}"; //sql = $@"select orders.Id,orders.OrderId,orders.BuyPrice,orders.UserId,user.NickName,user.TelePhone,orders.Message,orders.CreateDate,orders.State,orders.OrderNum,orders.Remark,orders.FreightPrice,orders.Address from miniappgoodsorder orders inner join c_userinfo user on user.Id=orders.UserId {(string.IsNullOrEmpty(where) ? "" : " where " + where)} order by Id desc {(pagesize == 0 ? "" : " limit " + (pageindex <= 0 ? 0 : pageindex - 1) * pagesize + "," + pagesize)}"; if (export)//导出Excel的话,不需要分页 { sql = $@"select orders.Id,orders.OrderId,orders.BuyPrice,orders.UserId,orders.AccepterName as NickName,orders.AccepterTelePhone as TelePhone,orders.Message,orders.CreateDate,orders.State,orders.OrderNum,orders.Remark,orders.FreightPrice,orders.Address from miniappgoodsorder orders {(string.IsNullOrEmpty(where) ? "" : " where " + where)} order by Id desc"; //sql = $@"select orders.Id,orders.OrderId,orders.BuyPrice,orders.UserId,user.NickName,user.TelePhone,orders.Message,orders.CreateDate,orders.State,orders.OrderNum,orders.Remark,orders.FreightPrice,orders.Address from miniappgoodsorder orders inner join c_userinfo user on user.Id=orders.UserId {(string.IsNullOrEmpty(where) ? "" : " where " + where)} order by Id desc"; } sqlCount = $@"select count(*) from miniappgoodsorder orders {(string.IsNullOrEmpty(where) ? "" : " where " + where)}"; MiniappEntGoodsBLL _goodsbll = new MiniappEntGoodsBLL(); //拼接数据 using (MySqlDataReader dr = SqlMySql.ExecuteDataReaderMaster(connName, CommandType.Text, sql, null)) { while (dr.Read()) { var model = GetModel(dr); var cartlist = new MiniappEntGoodsCartBLL().GetList($"GoodsOrderId={model.Id}"); var detaillist = new List <OrderCardDetail>(); foreach (var item in cartlist) { var cart = new OrderCardDetail(); cart.Id = item.Id; var goods = _goodsbll.GetModel(item.FoodGoodsId); if (goods != null) { cart.GoodsName = goods.name; cart.ImgUrl = goods.img; } cart.SpecInfo = item.SpecInfo; cart.Price = item.Price; cart.Count = item.Count; detaillist.Add(cart); } model.GoodsList = detaillist; list.Add(model); } } totalCount = GetCountBySql(sqlCount); return(list); }
public List <GoodsOrder> getModelByWhere(int appId, int pagesize, int pageindex, out int totalCount, string goodsName = "", string accName = "", string accPhone = "", int orderState = -999, DateTime?startDate = null, DateTime?endDate = null, string orderNum = "") { string sqlstr = @"select o.* from miniappentgoodsorder o"; //-- left JOIN miniappentgoodscart c on c.GoodsOrderId = o.Id //-- left join miniappentgoods g on c.FoodGoodsId = g.id"; string sqlWhere = $" where o.aid = '{appId}' {(orderState != -999 ? $" and o.State = {orderState} " : $" ") }"; List <MySqlParameter> mysqlPameter = new List <MySqlParameter>(); if (!string.IsNullOrWhiteSpace(orderNum)) { sqlWhere += " and o.OrderNum like @orderNum "; mysqlPameter.Add(new MySqlParameter("@orderNum", orderNum)); } if (!string.IsNullOrWhiteSpace(goodsName)) { var lists = new List <int>(); lists.Add(0); goodsName = goodsName.Replace("'", ""); var goods = new MiniappEntGoodsBLL().GetList($" name Like '{goodsName}' "); if (goods != null && goods.Any()) { var goodCar = new MiniappEntGoodsCartBLL().GetList($" state = 1 and FoodGoodsId in ({string.Join(",", goods.Select(x => x.id))}) "); if (goodCar != null && goodCar.Any()) { lists = goodCar.Select(x => x.GoodsOrderId).ToList(); //sqlWhere += $" and o.id in ({string.Join(",", goodCar.Select(x => x.GoodsOrderId))}) "; } } sqlWhere += $" and o.id in ({string.Join(",", lists)}) "; } //if (!string.IsNullOrWhiteSpace(goodsName)) //{ // sqlWhere += " and g.name like @goodname "; // mysqlPameter.Add(new MySqlParameter("@goodname", goodsName)); //} if (!string.IsNullOrWhiteSpace(accName)) { sqlWhere += " and o.AccepterName like @acceptername "; mysqlPameter.Add(new MySqlParameter("@acceptername", accName)); } if (!string.IsNullOrWhiteSpace(accPhone)) { sqlWhere += " and o.AccepterTelePhone like @accpetertelephone "; mysqlPameter.Add(new MySqlParameter("@accpetertelephone", accPhone)); } if (startDate != null) { sqlWhere += " and o.CreateDate >= @startDate "; mysqlPameter.Add(new MySqlParameter("@startDate", startDate?.ToString("yyyy-MM-dd 00:00:00"))); } if (endDate != null) { sqlWhere += " and o.CreateDate <= @endDate "; mysqlPameter.Add(new MySqlParameter("@endDate", endDate?.ToString("yyyy-MM-dd 23:59:59"))); } List <GoodsOrder> list = new List <GoodsOrder>(); totalCount = 0; log4net.LogHelper.WriteInfo(GetType(), sqlstr + sqlWhere + $" order by id desc limit {(pageindex - 1) * pagesize},{pagesize} "); using (MySqlDataReader dr = SqlMySql.ExecuteDataReaderMaster(connName, CommandType.Text, sqlstr + sqlWhere + $" order by id desc limit {(pageindex - 1) * pagesize},{pagesize} ", mysqlPameter.ToArray())) { while (dr.Read()) { list.Add(GetModel(dr)); } string sqlCountStr = @"select Count(0) from miniappentgoodsorder o "; //-- left JOIN miniappentgoodscart c on c.GoodsOrderId = o.Id //-- left join miniappentgoods g on c.FoodGoodsId = g.id"; totalCount = GetCountBySql(sqlCountStr + sqlWhere, mysqlPameter.ToArray()); } return(list); }