public GridResponse <SalesExtReportViewModel> GetAll(IUnitOfWork db, SalesExtReportFiltersViewModel filters)
{
var query = from o in db.Orders.GetAll()
join oi in db.OrderItems.GetAll() on o.Id equals oi.OrderId
join l in db.Listings.GetViewListings() on oi.ListingId equals l.Id
join stCache in db.StyleCaches.GetAll() on l.StyleId equals stCache.Id
where o.OrderStatus != OrderStatusEnumEx.Canceled
select new { o, oi, l, stCache };
if (filters.DateFrom.HasValue)
{
query = query.Where(o => o.o.OrderDate >= filters.DateFrom.Value);
}
if (filters.DateTo.HasValue)
{
query = query.Where(o => o.o.OrderDate <= filters.DateTo.Value);
}
if (filters.Market.HasValue)
{
query = query.Where(o => o.o.Market == filters.Market.Value);
}
if (!String.IsNullOrEmpty(filters.MarketplaceId))
{
query = query.Where(o => o.o.MarketplaceId == filters.MarketplaceId);
}
if (!String.IsNullOrEmpty(filters.Keywords))
{
query = query.Where(o => o.oi.StyleString == filters.Keywords ||
o.l.Barcode == filters.Keywords);
}
if (filters.ItemStyles != null && filters.ItemStyles.Any())
{
var itemStyleQuery = db.StyleFeatureValues.GetAll()
.Where(f => filters.ItemStyles.Contains(f.FeatureValueId))
.Select(f => f.StyleId)
.ToList();
query = from st in query
join i in itemStyleQuery on st.stCache.Id equals i
select st;
}
if (filters.Genders != null && filters.Genders.Any())
{
var genderIds = filters.Genders.Select(g => g.ToString()).ToList();
query = query.Where(st => genderIds.Contains(st.stCache.Gender));
}
if (filters.MainLicense.HasValue)
{
if (filters.MainLicense == 0)
{
query = query.Where(st => String.IsNullOrEmpty(st.stCache.MainLicense));
}
else
{
query = query.Where(st => st.stCache.MainLicense == filters.MainLicense.Value.ToString());
}
}
if (filters.SubLicense.HasValue)
{
query = query.Where(st => st.stCache.SubLicense == filters.SubLicense.Value.ToString());
}
var baseItemsQuery = query.GroupBy(o => o.oi.StyleId).Select(o =>
new
{
StyleId = o.Key,
SoldUnits = o.Sum(i => i.oi.QuantityOrdered),
});
var remainingQuery = from sic in db.StyleItemCaches.GetAll()
group sic by sic.StyleId into byStyle
select new
{
StyleId = byStyle.Key,
RemainingQuantity = byStyle.Sum(i => i.RemainingQuantity)
};
var items = (from i in baseItemsQuery
join st in db.Styles.GetAll() on i.StyleId equals st.Id
join stc in remainingQuery on st.Id equals stc.StyleId into withRem
from stc in withRem.DefaultIfEmpty()
select new SalesExtReportViewModel()
{
Image = st.Image,
StyleId = st.Id,
StyleString = st.StyleID,
RemainingUnits = stc.RemainingQuantity,
SoldUnits = i.SoldUnits,
}).ToList();
switch (filters.SortField)
{
case "StyleString":
if (filters.SortMode == 0)
{
items = items.OrderBy(o => o.StyleString).ToList();
}
else
{
items = items.OrderByDescending(o => o.StyleString).ToList();
}
break;
//case "BrandName":
// if (filter.SortMode == 0)
// styleWithCacheQuery = styleWithCacheQuery.OrderBy(s => s.stCache.Brand);
// else
// styleWithCacheQuery = styleWithCacheQuery.OrderByDescending(s => s.stCache.Brand);
// break;
case "SoldUnits":
if (filters.SortMode == 0)
{
items = items.OrderBy(s => s.SoldUnits).ToList();
}
else
{
items = items.OrderByDescending(s => s.SoldUnits).ToList();
}
break;
case "RemainingUnits":
if (filters.SortMode == 0)
{
items = items.OrderBy(s => s.SoldUnits).ToList();
}
else
{
items = items.OrderByDescending(s => s.SoldUnits).ToList();
}
break;
default:
items = items.OrderBy(s => s.StyleString).ToList();
break;
}
return(new GridResponse <SalesExtReportViewModel>(items, items.Count));
}
public GridResponse <SalesReturnReportViewModel> GetAll(IUnitOfWork db, SalesExtReportFiltersViewModel filters)
{
var query = from o in db.Orders.GetAll()
join oi in db.OrderItems.GetAll() on o.Id equals oi.OrderId
join l in db.Listings.GetViewListings() on oi.ListingId equals l.Id
join stCache in db.StyleCaches.GetAll() on l.StyleId equals stCache.Id
where o.OrderStatus == OrderStatusEnumEx.Shipped
select new { o, oi, l, stCache };
if (filters.DateFrom.HasValue)
{
query = query.Where(o => o.o.OrderDate >= filters.DateFrom.Value);
}
if (filters.DateTo.HasValue)
{
query = query.Where(o => o.o.OrderDate >= filters.DateTo.Value);
}
if (filters.Market.HasValue)
{
query = query.Where(o => o.o.Market == filters.Market.Value);
}
if (!String.IsNullOrEmpty(filters.MarketplaceId))
{
query = query.Where(o => o.o.MarketplaceId == filters.MarketplaceId);
}
if (!String.IsNullOrEmpty(filters.Keywords))
{
query = query.Where(o => o.oi.StyleString == filters.Keywords ||
o.l.Barcode == filters.Keywords);
}
if (filters.ItemStyles != null && filters.ItemStyles.Any())
{
var itemStyleQuery = db.StyleFeatureValues.GetAll()
.Where(f => filters.ItemStyles.Contains(f.FeatureValueId))
.Select(f => f.StyleId)
.ToList();
query = from st in query
join i in itemStyleQuery on st.stCache.Id equals i
select st;
}
if (filters.Genders != null && filters.Genders.Any())
{
var genderIds = filters.Genders.Select(g => g.ToString()).ToList();
query = query.Where(st => genderIds.Contains(st.stCache.Gender));
}
if (filters.MainLicense.HasValue)
{
if (filters.MainLicense == 0)
{
query = query.Where(st => String.IsNullOrEmpty(st.stCache.MainLicense));
}
else
{
query = query.Where(st => st.stCache.MainLicense == filters.MainLicense.Value.ToString());
}
}
if (filters.SubLicense.HasValue)
{
query = query.Where(st => st.stCache.SubLicense == filters.SubLicense.Value.ToString());
}
var baseItemsQuery = query.GroupBy(o => o.oi.StyleId).Select(o =>
new
{
StyleId = o.Key,
ReturnsUnits = o.Sum(i => i.oi.QuantityRefunded)
});
var returnUnits = baseItemsQuery.Where(i => i.ReturnsUnits > 0).ToList();
if (filters.DateFrom.HasValue)
{
filters.DateFrom = filters.DateFrom.Value.AddMonths(-2);
}
var allItems = new SalesExtReportViewModel().GetAll(db,
filters).Items;
var items = new List <SalesReturnReportViewModel>();
foreach (var returnUnit in returnUnits)
{
var item = allItems.FirstOrDefault(i => i.StyleId == returnUnit.StyleId);
items.Add(new SalesReturnReportViewModel()
{
StyleId = item.StyleId,
StyleString = item.StyleString,
Image = item.Image,
RemainingUnits = item.RemainingUnits,
ReturnsUnits = returnUnit.ReturnsUnits,
SoldUnits = item.SoldUnits,
});
}
switch (filters.SortField)
{
case "StyleString":
if (filters.SortMode == 0)
{
items = items.OrderBy(o => o.StyleString).ToList();
}
else
{
items = items.OrderByDescending(o => o.StyleString).ToList();
}
break;
//case "BrandName":
// if (filter.SortMode == 0)
// styleWithCacheQuery = styleWithCacheQuery.OrderBy(s => s.stCache.Brand);
// else
// styleWithCacheQuery = styleWithCacheQuery.OrderByDescending(s => s.stCache.Brand);
// break;
case "SoldUnits":
if (filters.SortMode == 0)
{
items = items.OrderBy(s => s.SoldUnits).ToList();
}
else
{
items = items.OrderByDescending(s => s.SoldUnits).ToList();
}
break;
case "RemainingUnits":
if (filters.SortMode == 0)
{
items = items.OrderBy(s => s.SoldUnits).ToList();
}
else
{
items = items.OrderByDescending(s => s.SoldUnits).ToList();
}
break;
case "ReturnsUnits":
if (filters.SortMode == 0)
{
items = items.OrderBy(s => s.ReturnsUnits).ToList();
}
else
{
items = items.OrderByDescending(s => s.ReturnsUnits).ToList();
}
break;
default:
items = items.OrderBy(s => s.StyleString).ToList();
break;
}
return(new GridResponse <SalesReturnReportViewModel>(items, items.Count));
}
