public List <Order> GetOrdersByConditions(string username, DateTime dateFrom, DateTime dateTo,
int orderIdFrom, int orderIdTo, int orderId, int offset)
{
SearchOrderCondition conditions = new SearchOrderCondition()
{
Username = username,
OrderIdFrom = orderIdFrom,
OrderIdTo = orderIdTo,
Offset = offset,
OrderId = orderId,
};
DateTime systemDefault = new DateTime();
if (dateFrom != systemDefault)
{
conditions.DateFrom = dateFrom;
}
if (dateTo != systemDefault)
{
conditions.DateTo = dateTo;
}
OrderDal dal = new OrderDal();
return(dal.ReadAllByCondition(conditions));
}
//public List<Order> ReadAll(int offset, string userId = "")
//{
// List<Order> orders = new List<Order>();
// using (SqlConnection connection = new SqlConnection(Constant.ConnectionString))
// {
// connection.Open();
// string sql = string.Empty;
// if(userId != string.Empty)
// sql = $"SELECT Id, CreatedOn, CreatedBy FROM OrderDetail WHERE CreatedBy = @UserId";
// else
// sql = $"SELECT Id, CreatedOn, CreatedBy FROM OrderDetail";
// sql = sql + " ORDER BY CreatedOn DESC OFFSET @OffSet ROWS FETCH NEXT 50 ROWS ONLY";
// SqlCommand command = new SqlCommand(sql, connection);
// command.Parameters.AddWithValue("@UserId", userId);
// command.Parameters.AddWithValue("@OffSet", offset);
// using (SqlDataReader dataReader = command.ExecuteReader())
// {
// while (dataReader.Read())
// {
// Order order = new Order();
// order.Id = Convert.ToInt32(dataReader["Id"]);
// order.CreatedOn = Convert.ToDateTime(dataReader["CreatedOn"]);
// order.CreatedBy = Convert.ToString(dataReader["CreatedBy"]);
// orders.Add(order);
// }
// }
// connection.Close();
// }
// return orders;
//}
public List <Order> ReadAllByCondition(SearchOrderCondition condition)
{
List <Order> orders = new List <Order>();
using (SqlConnection connection = new SqlConnection(Constant.ConnectionString))
{
connection.Open();
string sql = ConstructSearchOrderQuery(condition);
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@Username", condition.Username.Trim());
command.Parameters.AddWithValue("@OffSet", condition.Offset);
command.Parameters.AddWithValue("@OrderId", condition.OrderId);
command.Parameters.AddWithValue("@OrderIdFrom", condition.OrderIdFrom);
command.Parameters.AddWithValue("@OrderIdTo", condition.OrderIdTo);
command.Parameters.AddWithValue("@DateFrom", condition.DateFrom);
command.Parameters.AddWithValue("@DateTo", condition.DateTo);
using (SqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
Order order = new Order();
order.Id = Convert.ToInt32(dataReader["Id"]);
order.CreatedOn = Convert.ToDateTime(dataReader["CreatedOn"]);
order.CreatedBy = Convert.ToString(dataReader["CreatedBy"]);
orders.Add(order);
}
}
connection.Close();
}
return(orders);
}
public ActionResult GetOrders(SearchOrderCondition condition)
{
var orders = BS.GetOrders(condition);
var result = new JsonNetResult()
{
Data = orders
};
result.Settings.DateFormatString = "yyyy-MM-dd";
return(result);
}
public string ConstructSearchOrderQuery(SearchOrderCondition condition)
{
DateTime systemDefaultDate = new DateTime();
string sql = "SELECT Id, CreatedOn, CreatedBy FROM OrderDetail";
// Ensure there are some condition set
if (condition.Username.Trim() != string.Empty ||
condition.OrderId != 0 ||
condition.OrderIdFrom != 0 ||
condition.OrderIdTo != 0 ||
condition.DateFrom != systemDefaultDate ||
condition.DateTo != systemDefaultDate)
{
sql += " WHERE ";
}
if (condition.Username.Trim() != string.Empty)
{
UserDal userDal = new UserDal();
User user = userDal.Read(condition.Username);
if (!user.Admin)
{
sql += "CreatedBy = @Username ";
}
}
if (condition.OrderId != 0)
{
sql += "AND Id = @OrderId ";
}
else
{
if (condition.OrderIdFrom != 0)
{
sql += "AND Id >= @OrderIdFrom ";
}
if (condition.OrderIdTo != 0)
{
sql += "AND Id <= @OrderIdTo ";
}
}
if (condition.DateFrom != systemDefaultDate)
{
sql += "AND CreatedOn >= @DateFrom ";
}
if (condition.DateTo != systemDefaultDate)
{
sql += "AND CreatedOn <= @DateTo ";
}
// Clean up
if (!sql.Contains("="))
{
sql = sql.Replace("WHERE", string.Empty);
}
if (sql.Contains("WHERE AND"))
{
sql = sql.Replace("WHERE AND", "WHERE");
}
sql = sql + " ORDER BY CreatedOn DESC OFFSET @OffSet ROWS FETCH NEXT 50 ROWS ONLY";
return(sql);
}
public List <OrderReview> GetOrders(SearchOrderCondition condition)
{
var orderPred = PredicateBuilder.New <ZY_Order>(true);
if (condition.From.HasValue)
{
orderPred = orderPred.And(x => x.OrderDate >= condition.From.Value);
}
if (condition.To.HasValue)
{
orderPred = orderPred.And(x => x.OrderDate <= condition.To.Value);
}
if (condition.Status.HasValue)
{
orderPred = orderPred.And(x => x.Status.Equals(condition.Status.Value));
}
if (!string.IsNullOrEmpty(condition.CustomerOpenId))
{
orderPred = orderPred.And(x => x.CustomerOpenId.Contains(condition.CustomerOpenId));
}
var shopPred = PredicateBuilder.New <ZY_Shop>(true);
if (!condition.ShopId.HasValue)
{
condition.ShopId = Guid.NewGuid();
}
shopPred = shopPred.And(x => condition.ShopId.Value == (x.ShopId));
using (var db = GetDbContext())
{
var positions = (from bp in db.ZY_Booked_Position
join o in db.ZY_Order.AsExpandable().Where(orderPred) on bp.OrderId equals o.OrderId
join desk in db.ZY_Shop_Desk on bp.DeskId equals desk.DeskId
join shop in db.ZY_Shop.AsExpandable().Where(shopPred) on desk.ShopId equals shop.ShopId
select new
{
o.OrderId,
ShopName = shop.Name,
desk.DeskName,
o.OrderDate,
bp.Position,
o.Status,
o.Amount,
o.IsInternal,
o.CustomerOpenId,
o.PickTime
}).ToList();
return(positions.GroupBy(
x => new { x.OrderId, x.OrderDate, x.ShopName, x.DeskName, x.Status, x.IsInternal, x.Amount, x.CustomerOpenId, x.PickTime })
.Select(x => new OrderReview()
{
OrderId = x.Key.OrderId,
OrderDate = x.Key.OrderDate,
ShopName = x.Key.ShopName,
DeskName = x.Key.DeskName,
Status = x.Key.Status,
IsInternal = x.Key.IsInternal,
Amount = x.Key.Amount,
Positions = x.Select(p => p.Position).ToList(),
PickTime = x.Key.PickTime,
CustomerOpenId = x.Key.CustomerOpenId
}).ToList());
}
}
