//查找订单列表
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);
}
