public async Task <IEnumerable <OrderSummaryViewModel> > OrdersByUserId(SearchOrdersViewModel searchOrders, string userId, string UserTableName)
{
StringBuilder whereClose = new StringBuilder();
int totalSkip = searchOrders.PageSize * (searchOrders.PageNumber - 1);
if (searchOrders.InvoiceNo != null)
{
whereClose.Append($" o.InvoiceNo ='{searchOrders.InvoiceNo}'");
}
else
{
if (searchOrders.OrderStartDate != null)
{
whereClose.Append($" UserId='{userId}' and DATE_TRUNC('day', CreatedDateTime)>= '{searchOrders.OrderStartDate.Value.Date}' and DATE_TRUNC('day',CreatedDateTime)<='{searchOrders.OrderEndDate.Value.Date}'");
}
if (searchOrders.Status != null)
{
whereClose.Append($" and o.OrderStatusId ='{searchOrders.Status}'");
}
whereClose.Append($" order by o.CreatedDateTime desc OFFSET {totalSkip} ROWS FETCH NEXT {searchOrders.PageSize} ROWS ONLY;");
}
string tab = $"select o.Id,o.CreatedDateTime,o.UpdatedDateTime,'' as UserId,'' as IP,o.OrderStatusId,os.statusname,o.ItemCount,o.IsActive,o.ModifyBy,o.TotalAmount," +
$"o.Discount,o.DeliveryCost,o.PromoCode,o.DeliveryStatus,o.DeliveryDate,o.InvoiceNo,o.PaymentStatus,o.UserAddressDetails,o.ShopAddressDetails,o.OrdersDetails,o.CurrencyType,o.ShopId,os.StatusName as OrderStatus " +
$"from UserOrders.User{UserTableName}Orders o left join OrderStatuses os on o.OrderStatusId = os.Id " +
$"where {whereClose}";
var orderSummary = await _context.OrderSummaries.FromSqlRaw(tab).ToListAsync();
List <OrderSummaryViewModel> orderSummaryList = _mapper.Map <List <OrderSummaryViewModel> >(orderSummary);
return(orderSummaryList);
}
public async Task <IEnumerable <OrderSummaryViewModel> > OrdersByVendorId(SearchOrdersViewModel searchOrders, string userId)
{
var shop = _context.Shops.Where(x => x.UserId == userId).FirstOrDefault();
string userTable = shop.tableName;
StringBuilder whereClose = new StringBuilder();
whereClose.Append($"where o.shopId ='{shop.Id}' ");
int totalSkip = searchOrders.PageSize * (searchOrders.PageNumber - 1);
if (!string.IsNullOrEmpty(searchOrders.InvoiceNo))
{
whereClose.Append($"and o.InvoiceNo ='{searchOrders.InvoiceNo}'");
}
else
{
if (searchOrders.OrderStartDate != null)
{
whereClose.Append($"and DATE_TRUNC('day', CreatedDateTime)>= '{searchOrders.OrderStartDate.Value.Date}' and DATE_TRUNC('day',CreatedDateTime)<='{searchOrders.OrderEndDate.Value.Date}'");
}
//whereClose.Append($"convert(varchar, CreatedDateTime, 1) between convert(varchar,'{searchOrders.OrderStartDate.Value.Date}',1) and convert(varchar,'{searchOrders.OrderEndDate.Value.Date}',1)");
if (searchOrders.Status != null)
{
whereClose.Append($" and o.OrderStatusId ='{searchOrders.Status}'");
}
whereClose.Append($" order by o.CreatedDateTime desc OFFSET {totalSkip} ROWS FETCH NEXT {searchOrders.PageSize} ROWS ONLY;");
}
string tab = $"select o.Id,o.CreatedDateTime,o.UpdatedDateTime,'' as UserId,'' as IP,o.OrderStatusId,os.statusName,o.ItemCount,o.IsActive,o.ModifyBy,o.TotalAmount," +
$"o.Discount,o.DeliveryCost,o.PromoCode,o.DeliveryStatus,o.DeliveryDate,o.InvoiceNo,o.PaymentStatus,o.UserAddressDetails,o.ShopAddressDetails,o.OrdersDetails,o.CurrencyType,o.ShopId,os.StatusName as OrderStatus " +
$"from VendorOrder.Vendor{userTable}Orders o left join OrderStatuses os on o.OrderStatusId = os.Id " +
$"{whereClose};";
var orderSummary = await _context.OrderSummaries.FromSqlRaw(tab).ToListAsync();
List <OrderSummaryViewModel> orderSummaryList = _mapper.Map <List <OrderSummaryViewModel> >(orderSummary);
return(orderSummaryList);
}
public async Task <ActionResult <IEnumerable <OrderSummaryViewModel> > > getUserOrdersDetails(SearchOrdersViewModel searchOrders)
{
string UserId = User.FindFirst(ClaimTypes.NameIdentifier)?.Value;
string userTableName = await _singleton.userRepository.GetUserOrdersTableName(UserId);
try
{
var orderSummary = await _singleton.orderRepository.OrdersByUserId(searchOrders, UserId, userTableName);
return(orderSummary.ToList());
}
catch (Exception e)
{
throw;
}
}
