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