public async Task <IHttpActionResult> GetOrdersHistory(int UserId, int SignInType, bool IsCurrentOrder, int PageSize, int PageNo)
{
try
{
using (SkriblContext ctx = new SkriblContext())
{
BasketApi.AppsViewModels.OrdersHistoryViewModel orderHistory = new AppsViewModels.OrdersHistoryViewModel();
if (SignInType == (int)RoleTypes.User)
{
if (IsCurrentOrder)
{
orderHistory.Count = ctx.Orders.Count(x => x.User_ID == UserId && x.IsDeleted == false && x.Status != (int)OrderStatuses.Completed && x.RemoveFromUserHistory == false);
}
else
{
orderHistory.Count = ctx.Orders.Count(x => x.User_ID == UserId && x.IsDeleted == false && x.Status == (int)OrderStatuses.Completed && x.RemoveFromUserHistory == false);
}
}
else
{
if (IsCurrentOrder)
{
orderHistory.Count = ctx.Orders.Count(x => x.DeliveryMan_Id == UserId && x.IsDeleted == false && x.Status != (int)OrderStatuses.Completed && x.RemoveFromDelivererHistory == false);
}
else
{
orderHistory.Count = ctx.Orders.Count(x => x.DeliveryMan_Id == UserId && x.IsDeleted == false && x.Status == (int)OrderStatuses.Completed && x.RemoveFromDelivererHistory == false);
}
}
if (orderHistory.Count == 0)
{
return(Ok(new CustomResponse <AppsViewModels.OrdersHistoryViewModel> {
Message = Global.ResponseMessages.Success, StatusCode = (int)HttpStatusCode.OK, Result = orderHistory
}));
}
#region OrderQuery
string orderQuery = String.Empty;
if (SignInType == (int)RoleTypes.User)
{
orderQuery = @"
SELECT *, Users.FullName as UserFullName FROM Orders
join Users on Users.ID = Orders.User_ID
where Orders.User_Id = " + UserId + @" and Orders.IsDeleted = 0 and Users.IsDeleted = 0 and Orders.RemoveFromUserHistory = 0 and " + (IsCurrentOrder ? " Orders.Status <> " + (int)OrderStatuses.Completed : " Orders.Status = " + (int)OrderStatuses.Completed) +
@"ORDER BY Orders.id desc OFFSET " + PageNo * PageSize + " ROWS FETCH NEXT " + PageSize + " ROWS ONLY;";
}
else
{
orderQuery = @"
SELECT *, Users.FullName as UserFullName FROM Orders
join Users on Users.ID = Orders.User_ID
join DeliveryMen on DeliveryMen.ID = Orders.DeliveryMan_Id
where Orders.DeliveryMan_Id = " + UserId + @" and Orders.IsDeleted = 0 and Orders.RemoveFromDelivererHistory = 0 and " + (IsCurrentOrder ? " Orders.Status <> " + (int)OrderStatuses.Completed : " Orders.Status = " + (int)OrderStatuses.Completed) +
@"ORDER BY Orders.id desc OFFSET " + PageNo * PageSize + " ROWS FETCH NEXT " + PageSize + " ROWS ONLY;";
}
#endregion
orderHistory.orders = ctx.Database.SqlQuery <BasketApi.AppsViewModels.OrderViewModel>(orderQuery).ToList();
if (orderHistory.orders.Count == 0)
{
return(Ok(new CustomResponse <AppsViewModels.OrdersHistoryViewModel> {
Message = Global.ResponseMessages.Success, StatusCode = (int)HttpStatusCode.OK, Result = orderHistory
}));
}
var orderIds = string.Join(",", orderHistory.orders.Select(x => x.Id.ToString()));
#region StoreOrderQuery
string storeOrderQuery = String.Empty;
if (SignInType == (int)RoleTypes.User)
{
storeOrderQuery = @"
select
StoreOrders.*,
Stores.Name as StoreName,
Stores.ImageUrl from StoreOrders
join Stores on Stores.Id = StoreOrders.Store_Id
where
Order_Id in (" + orderIds + @")
and StoreOrders.RemoveFromUserHistory = 0
";
}
else
{
storeOrderQuery = @"
select
StoreOrders.*,
Stores.Name as StoreName,
Stores.ImageUrl from StoreOrders
join Stores on Stores.Id = StoreOrders.Store_Id
where
Order_Id in (" + orderIds + @")
and StoreOrders.RemoveFromDelivererHistory = 0
";
}
#endregion
var storeOrders = ctx.Database.SqlQuery <BasketApi.AppsViewModels.StoreOrderViewModel>(storeOrderQuery).ToList();
if (storeOrders.Count == 0)
{
orderHistory.orders.Clear();
return(Ok(new CustomResponse <AppsViewModels.OrdersHistoryViewModel> {
Message = Global.ResponseMessages.Success, StatusCode = (int)HttpStatusCode.OK, Result = orderHistory
}));
}
var storeOrderIds = string.Join(",", storeOrders.Select(x => x.Id.ToString()));
#region OrderItemsQuery
var orderItemsQuery = @"
SELECT
CASE
WHEN ISNULL(Order_Items.Product_Id, 0) <> 0 THEN Products.Id
WHEN ISNULL(Order_Items.Package_Id, 0) <> 0 THEN Packages.Id
WHEN ISNULL(Order_Items.Offer_Product_Id, 0) <> 0 THEN Offer_Products.Id
WHEN ISNULL(Order_Items.Offer_Package_Id, 0) <> 0 THEN Offer_Packages.Id
END AS ItemId,
Order_Items.Name AS Name,
Order_Items.Price AS Price,
CASE
WHEN ISNULL(Order_Items.Product_Id, 0) <> 0 THEN Products.ImageUrl
WHEN ISNULL(Order_Items.Package_Id, 0) <> 0 THEN Packages.ImageUrl
WHEN ISNULL(Order_Items.Offer_Product_Id, 0) <> 0 THEN Offer_Products.ImageUrl
WHEN ISNULL(Order_Items.Offer_Package_Id, 0) <> 0 THEN Offer_Packages.ImageUrl
END AS ImageUrl,
Order_Items.Id,
Order_Items.Qty,
ISNULL(Products.WeightInGrams,0) as WeightInGrams,
ISNULL(Products.WeightInKiloGrams,0) as WeightInKiloGrams,
Order_Items.StoreOrder_Id
FROM Order_Items
LEFT JOIN products
ON products.Id = Order_Items.Product_Id
LEFT JOIN Packages
ON Packages.Id = Order_Items.Package_Id
LEFT JOIN Offer_Products
ON Offer_Products.Id = Order_Items.Offer_Product_Id
LEFT JOIN Offer_Packages
ON Offer_Packages.Id = Order_Items.Offer_Package_Id
WHERE StoreOrder_Id IN (" + storeOrderIds + ")";
#endregion
var orderItems = ctx.Database.SqlQuery <BasketApi.AppsViewModels.OrderItemViewModel>(orderItemsQuery).ToList();
var userFavourites = ctx.Favourites.Where(x => x.User_ID == UserId && x.IsDeleted == false).ToList();
foreach (var orderItem in orderItems)
{
orderItem.Weight = Convert.ToString(orderItem.WeightInGrams) + " gm";
if (userFavourites.Any(x => x.Product_Id == orderItem.Id))
{
orderItem.IsFavourite = true;
}
else
{
orderItem.IsFavourite = false;
}
}
foreach (var orderItem in orderItems)
{
storeOrders.FirstOrDefault(x => x.Id == orderItem.StoreOrder_Id).OrderItems.Add(orderItem);
}
foreach (var storeOrder in storeOrders)
{
orderHistory.orders.FirstOrDefault(x => x.Id == storeOrder.Order_Id).StoreOrders.Add(storeOrder);
}
return(Ok(new CustomResponse <AppsViewModels.OrdersHistoryViewModel> {
Message = Global.ResponseMessages.Success, StatusCode = (int)HttpStatusCode.OK, Result = orderHistory
}));
#region CommentedOldLogic
//OrdersHistoryViewModel ordersHistoryViewModel = new OrdersHistoryViewModel();
//if (IsCurrentOrder)
//{
// if (SignInType == (int)RoleTypes.User)
// {
// ordersHistoryViewModel.Count = ctx.Orders.Count(x => x.User_ID == UserId && x.IsDeleted == false && x.Status != (int)OrderStatuses.Completed);
// ordersHistoryViewModel.orders = ctx.Orders.Include(x => x.StoreOrders.Select(x1 => x1.Order_Items.Select(x2 => x2.Product.Store)))
// .Where(x => x.User_ID == UserId && x.IsDeleted == false && x.Status != (int)OrderStatuses.Completed).OrderBy(x => x.Id).Page(PageSize, PageNo).ToList();
// }
// else
// {
// ordersHistoryViewModel.Count = ctx.Orders.Count(x => x.DeliveryMan_Id == UserId && x.IsDeleted == false && x.Status != (int)OrderStatuses.Completed);
// ordersHistoryViewModel.orders = ctx.Orders.Include(x => x.StoreOrders.Select(x1 => x1.Order_Items.Select(x2 => x2.Product.Store)))
// .Where(x => x.DeliveryMan_Id == UserId && x.IsDeleted == false && x.Status != (int)OrderStatuses.Completed).OrderBy(x => x.Id).Page(PageSize, PageNo).ToList();
// }
//}
//else
//{
// if (SignInType == (int)RoleTypes.User)
// {
// ordersHistoryViewModel.Count = ctx.Orders.Count(x => x.User_ID == UserId && x.IsDeleted == false && x.Status == (int)OrderStatuses.Completed);
// ordersHistoryViewModel.orders = ctx.Orders.Include(x => x.StoreOrders.Select(x1 => x1.Order_Items.Select(x2 => x2.Product.Store)))
// .Where(x => x.User_ID == UserId && x.IsDeleted == false && x.Status == (int)OrderStatuses.Completed).OrderBy(x => x.Id).Page(PageSize, PageNo).ToList();
// }
// else
// {
// ordersHistoryViewModel.Count = ctx.Orders.Count(x => x.DeliveryMan_Id == UserId && x.IsDeleted == false && x.Status == (int)OrderStatuses.Completed);
// ordersHistoryViewModel.orders = ctx.Orders.Include(x => x.StoreOrders.Select(x1 => x1.Order_Items.Select(x2 => x2.Product.Store)))
// .Where(x => x.DeliveryMan_Id == UserId && x.IsDeleted == false && x.Status == (int)OrderStatuses.Completed).OrderBy(x => x.Id).Page(PageSize, PageNo).ToList();
// }
//}
//return Ok(new CustomResponse<OrdersHistoryViewModel> { Message = Global.ResponseMessages.Success, StatusCode = (int)HttpStatusCode.OK, Result = ordersHistoryViewModel });
#endregion
}
}
catch (Exception ex)
{
return(StatusCode(Utility.LogError(ex)));
}
}
public async Task <IHttpActionResult> GetDeliveryScheduleByDateRange(int DelivererId, string StartDate, string EndDate, int PageSize, int PageNo)
{
try
{
DateTime startDateTime;
DateTime endDateTime;
DateTime.TryParse(StartDate, out startDateTime);
DateTime.TryParse(EndDate, out endDateTime);
using (SkriblContext ctx = new SkriblContext())
{
BasketApi.AppsViewModels.OrdersHistoryViewModel model = new AppsViewModels.OrdersHistoryViewModel();
//BasketApi.AppsViewModels.OrdersScheduleViewModel model = new AppsViewModels.OrdersScheduleViewModel();
model.Count = ctx.Orders.Count(x => x.DeliveryMan_Id == DelivererId && DbFunctions.TruncateTime(x.DeliveryTime_From) >= startDateTime.Date && DbFunctions.TruncateTime(x.DeliveryTime_From) <= endDateTime.Date);
string orderQuery = String.Empty;
#region ordersQuery
orderQuery = @"
SELECT *, Users.FullName as UserFullName, Users.profilepictureurl as UserProfilePictureUrl FROM Orders
join Users on Users.ID = Orders.User_ID
where Orders.DeliveryMan_Id = " + DelivererId +
@" and CAST(DeliveryTime_From AS DATE) >= '" + startDateTime + @"' and CAST(DeliveryTime_From AS DATE) <= '" + endDateTime.Date +
@"' and Orders.IsDeleted = 0 and Users.IsDeleted = 0
ORDER BY Orders.id desc OFFSET " + PageNo * PageSize + " ROWS FETCH NEXT " + PageSize + " ROWS ONLY;";
#endregion
model.orders = ctx.Database.SqlQuery <BasketApi.AppsViewModels.OrderViewModel>(orderQuery).ToList();
if (model.orders.Count == 0)
{
return(Ok(new CustomResponse <AppsViewModels.OrdersHistoryViewModel> {
Message = Global.ResponseMessages.Success, StatusCode = (int)HttpStatusCode.OK, Result = model
}));
}
var orderIds = string.Join(",", model.orders.Select(x => x.Id.ToString()));
string storeOrderQuery = String.Empty;
#region storeOrdersQuery
storeOrderQuery = @"
select
StoreOrders.*,
Stores.Name as StoreName,
Stores.ImageUrl from StoreOrders
join Stores on Stores.Id = StoreOrders.Store_Id
where
Order_Id in (" + orderIds + @")
";
#endregion
var storeOrders = ctx.Database.SqlQuery <BasketApi.AppsViewModels.StoreOrderViewModel>(storeOrderQuery).ToList();
if (storeOrders.Count == 0)
{
model.orders.Clear();
return(Ok(new CustomResponse <AppsViewModels.OrdersHistoryViewModel> {
Message = Global.ResponseMessages.Success, StatusCode = (int)HttpStatusCode.OK, Result = model
}));
}
var storeOrderIds = string.Join(",", storeOrders.Select(x => x.Id.ToString()));
#region OrderItemsQuery
var orderItemsQuery = @"
SELECT
CASE
WHEN ISNULL(Order_Items.Product_Id, 0) <> 0 THEN Products.Id
WHEN ISNULL(Order_Items.Package_Id, 0) <> 0 THEN Packages.Id
WHEN ISNULL(Order_Items.Offer_Product_Id, 0) <> 0 THEN Offer_Products.Id
WHEN ISNULL(Order_Items.Offer_Package_Id, 0) <> 0 THEN Offer_Packages.Id
END AS ItemId,
Order_Items.Name AS Name,
Order_Items.Price AS Price,
CASE
WHEN ISNULL(Order_Items.Product_Id, 0) <> 0 THEN Products.ImageUrl
WHEN ISNULL(Order_Items.Package_Id, 0) <> 0 THEN Packages.ImageUrl
WHEN ISNULL(Order_Items.Offer_Product_Id, 0) <> 0 THEN Offer_Products.ImageUrl
WHEN ISNULL(Order_Items.Offer_Package_Id, 0) <> 0 THEN Offer_Packages.ImageUrl
END AS ImageUrl,
Order_Items.Id,
Order_Items.Qty,
ISNULL(Products.WeightInGrams,0) as WeightInGrams,
ISNULL(Products.WeightInKiloGrams,0) as WeightInKiloGrams,
Order_Items.StoreOrder_Id
FROM Order_Items
LEFT JOIN products
ON products.Id = Order_Items.Product_Id
LEFT JOIN Packages
ON Packages.Id = Order_Items.Package_Id
LEFT JOIN Offer_Products
ON Offer_Products.Id = Order_Items.Offer_Product_Id
LEFT JOIN Offer_Packages
ON Offer_Packages.Id = Order_Items.Offer_Package_Id
WHERE StoreOrder_Id IN (" + storeOrderIds + ")";
#endregion
var orderItems = ctx.Database.SqlQuery <BasketApi.AppsViewModels.OrderItemViewModel>(orderItemsQuery).ToList();
foreach (var orderItem in orderItems)
{
storeOrders.FirstOrDefault(x => x.Id == orderItem.StoreOrder_Id).OrderItems.Add(orderItem);
}
foreach (var storeOrder in storeOrders)
{
model.orders.FirstOrDefault(x => x.Id == storeOrder.Order_Id).StoreOrders.Add(storeOrder);
}
return(Ok(new CustomResponse <AppsViewModels.OrdersHistoryViewModel> {
Message = Global.ResponseMessages.Success, StatusCode = (int)HttpStatusCode.OK, Result = model
}));
//var orders = ctx.Orders.Include(x => x.User).Where(x => x.DeliveryMan_Id == DelivererId && DbFunctions.TruncateTime(x.DeliveryTime_From) >= startDateTime.Date && DbFunctions.TruncateTime(x.DeliveryTime_From) <= endDateTime.Date).OrderByDescending(x => x.Id).Page(PageSize, PageNo).ToList();
//model.Orders = orders;
//return Ok(new CustomResponse<BasketApi.AppsViewModels.OrdersScheduleViewModel> { Message = Global.ResponseMessages.Success, StatusCode = (int)HttpStatusCode.OK, Result = model });
}
}
catch (Exception ex)
{
return(StatusCode(Utility.LogError(ex)));
}
}
