/// <summary>
/// 获取下级零售业绩
/// </summary>
protected override IEnumerable <RetailDistributionEntity> SearchData()
{
var lp = VMGlobal.DistributionQuery.LinqOP;
//var childOrganizations = OrganizationListVM.CurrentOrganization.ChildrenOrganizations;
var oids = OrganizationArray.Select(o => o.ID).ToArray();
var retailContext = lp.GetDataContext <BillRetail>();
//var orgContext = lp.Search<ViewOrganization>(o => oids.Contains(o.ID));
var data = from retail in retailContext
where oids.Contains(retail.OrganizationID)
select new RetailEntityForDistribution
{
CreateTime = retail.CreateTime.Date,
Quantity = retail.Quantity,
CostMoney = retail.CostMoney,
ReceiveMoney = retail.ReceiveMoney,
TicketMoney = retail.TicketMoney,
OrganizationID = retail.OrganizationID
};
var filtedData = (IQueryable <RetailEntityForDistribution>)data.Where(FilterDescriptors);
var result = filtedData.GroupBy(o => o.OrganizationID).Select(g => new RetailDistributionEntity
{
OrganizationID = g.Key,
Quantity = g.Sum(o => o.Quantity),
CostMoney = g.Sum(o => o.CostMoney),
ReceiveMoney = g.Sum(o => o.ReceiveMoney),
TicketMoney = g.Sum(o => o.TicketMoney)
}).ToList();
result.ForEach(o => o.OrganizationName = OrganizationArray.First(c => c.ID == o.OrganizationID).Name);
return(result);
}
private IEnumerable <OrganizationProductQuantity> GetSubordinateOrderAggregation(IEnumerable <int> pids)
{
var lp = VMGlobal.DistributionQuery.LinqOP;
//var organizations = OrganizationListVM.CurrentOrganization.ChildrenOrganizations;
var oids = OrganizationArray.Select(o => o.ID).ToArray();
var orderContext = lp.Search <BillOrder>(o => oids.Contains(o.OrganizationID));
var orderDetailsContext = lp.GetDataContext <BillOrderDetails>();
var brandIDs = VMGlobal.PoweredBrands.Select(o => o.ID);
orderContext = orderContext.Where(o => brandIDs.Contains(o.BrandID));
//var orgContext = lp.GetDataContext<ViewOrganization>();
var data = from order in orderContext
//from org in orgContext
//where order.OrganizationID == org.ID
from details in orderDetailsContext
where order.ID == details.BillID && pids.Contains(details.ProductID) && order.IsDeleted == false
select new OrganizationProductQuantity
{
OrganizationID = order.OrganizationID,
ProductID = details.ProductID,
Quantity = details.Quantity - details.QuaCancel - details.QuaDelivered
};
var temp = data.GroupBy(o => new { o.ProductID, o.OrganizationID }).Select(g => new { g.Key, Quantity = g.Sum(o => o.Quantity) }).Where(g => g.Quantity != 0).ToList();
var result = temp.Select(o => new OrganizationProductQuantity
{
OrganizationID = o.Key.OrganizationID,
ProductID = o.Key.ProductID,
Quantity = o.Quantity
}).ToList();
result.ForEach(o => o.OrganizationName = OrganizationArray.First(c => c.ID == o.OrganizationID).Name);
return(result);
}
protected override IEnumerable <DistributionProductShow> SearchData()
{
var lp = VMGlobal.DistributionQuery.LinqOP;
var oids = OrganizationArray.Select(o => o.ID).ToArray();
var brandIDs = VMGlobal.PoweredBrands.Select(o => o.ID);
var goodReturnContext = lp.Search <BillGoodReturn>(o => oids.Contains(o.OrganizationID) && brandIDs.Contains(o.BrandID));
var detailsContext = lp.GetDataContext <BillGoodReturnDetails>();
var productContext = lp.GetDataContext <ViewProduct>();
var data = from goodreturn in goodReturnContext
from details in detailsContext
where goodreturn.ID == details.BillID
from product in productContext
where product.ProductID == details.ProductID //&& brandIDs.Contains(product.BrandID)
select new MultiStatusBillEntityForAggregation
{
ProductID = product.ProductID,
BrandID = product.BrandID,
CreateTime = goodreturn.CreateTime.Date,
StyleCode = product.StyleCode,
Quantity = details.Quantity,
Status = goodreturn.Status,
NameID = product.NameID,
OrganizationID = goodreturn.OrganizationID
};
data = (IQueryable <MultiStatusBillEntityForAggregation>)data.Where(FilterDescriptors);
return(ReportDataContext.AggregateBill(data));
}
protected override IEnumerable <ShopExpense> SearchData()
{
var oids = OrganizationArray.Select(o => o.ID);
var data = (IQueryable <ShopExpense>)LinqOP.Search <ShopExpense>(o => oids.Contains(o.OrganizationID)).Where(FilterDescriptors);
TotalCount = data.Count();
return(data.OrderByDescending(o => o.ID).Skip(PageIndex * PageSize).Take(PageSize).ToList());
}
/// <summary>
/// 查询本级和下级订单
/// </summary>
protected override IEnumerable <OrderSearchEntity> SearchData()
{
int totalCount = 0;
var oids = OrganizationArray.Select(o => o.ID).ToArray();
var data = ReportDataContext.SearchBillOrder(FilterDescriptors, DetailsDescriptors, oids, PageIndex, PageSize, ref totalCount);
TotalCount = totalCount;
return(data);
}
protected override IEnumerable <BillGoodReturnForSearch> SearchData()
{
var lp = VMGlobal.DistributionQuery.LinqOP;
var oids = OrganizationArray.Select(o => o.ID).ToArray();
var brands = VMGlobal.PoweredBrands;
var brandIDs = brands.Select(b => b.ID);
var goodreturnContext = lp.Search <BillGoodReturn>(o => oids.Contains(o.OrganizationID) && brandIDs.Contains(o.BrandID));
var userContext = lp.GetDataContext <ViewUser>();
var billData = from d in goodreturnContext
from u in userContext
where d.CreatorID == u.ID
select new BillGoodReturnForSearch
{
ID = d.ID,
Remark = d.Remark,
Code = d.Code,
BrandID = d.BrandID,
CreateTime = d.CreateTime,
CreateDate = d.CreateTime.Date,
Status = d.Status,
OrganizationID = d.OrganizationID,
TotalPrice = d.TotalPrice,
Quantity = d.Quantity,
CreatorName = u.Name
};
var detailsContext = lp.GetDataContext <BillGoodReturnDetails>();
var pIDs = ProductHelper.GetProductIDArrayWithCondition(DetailsDescriptors, brandIDs);
if (pIDs != null)
{
if (pIDs.Count() == 0)
{
return(null);
}
billData = from d in billData
where detailsContext.Any(od => od.BillID == d.ID && pIDs.Contains(od.ProductID))
select d;
}
var filtedData = (IQueryable <BillGoodReturnForSearch>)billData.Where(FilterDescriptors);
TotalCount = filtedData.Count();
var goodreturns = filtedData.OrderByDescending(o => o.ID).Skip(PageIndex * PageSize).Take(PageSize).ToList();
var bIDs = goodreturns.Select(o => (int)o.ID);
var sum = detailsContext.Where(o => bIDs.Contains(o.BillID)).GroupBy(o => o.BillID).Select(g => new { BillID = g.Key, Quantity = g.Sum(o => o.Quantity) }).ToList();
goodreturns.ForEach(d =>
{
d.BrandName = brands.FirstOrDefault(o => d.BrandID == o.ID).Name;
//var details = sum.Find(o => o.BillID == d.ID);
//d.Quantity = details.Quantity;
d.OrganizationName = OrganizationArray.First(o => o.ID == d.OrganizationID).Name;
});
return(goodreturns);
}
protected override IEnumerable <RetailDayReportEntity> SearchData()
{
var lp = VMGlobal.DistributionQuery.LinqOP;
var oids = OrganizationArray.Select(o => o.ID).ToArray();
var retailContext = lp.Search <BillRetail>(o => o.CreateTime.Month == RetailDay.Month && oids.Contains(o.OrganizationID));
var detailsContext = lp.GetDataContext <BillRetailDetails>();
var productContext = lp.GetDataContext <ViewProduct>();
var data = from retail in retailContext
from details in detailsContext
where retail.ID == details.BillID
from product in productContext
where product.ProductID == details.ProductID && product.BrandID == BrandID
select new
{
retail.OrganizationID,
details.Quantity,
details.Discount,
details.CutMoney,
details.Price,
CreateDay = retail.CreateTime.Day
};
var result = data.GroupBy(o => o.OrganizationID).Select(g => new RetailDayReportEntity
{
OrganizationID = g.Key,
Quantity = g.Sum(o => o.CreateDay == RetailDay.Day ? o.Quantity : 0),
SaleMoney = g.Sum(o => o.CreateDay == RetailDay.Day ? o.Quantity * o.Price * o.Discount / 100 - o.CutMoney : 0),
SalePrice = g.Sum(o => o.CreateDay == RetailDay.Day ? o.Quantity * o.Price : 0),
MonthQuantity = g.Sum(o => o.Quantity),
MonthSaleMoney = g.Sum(o => o.Quantity * o.Price * o.Discount / 100 - o.CutMoney)
}).ToList();
var targets = lp.Search <RetailMonthTaget>(o => o.Year == RetailDay.Year && o.Month == RetailDay.Month && oids.Contains(o.OrganizationID)).ToList();
foreach (var r in result)
{
r.OrganizationCode = OrganizationArray.First(o => o.ID == r.OrganizationID).Code;
r.OrganizationName = OrganizationArray.First(o => o.ID == r.OrganizationID).Name;
if (r.SalePrice != 0)
{
r.Discount = r.SaleMoney / r.SalePrice;
}
var target = targets.FirstOrDefault(o => o.OrganizationID == r.OrganizationID && o.Year == RetailDay.Year && o.Month == RetailDay.Month);
if (target != null && target.SaleTaget != 0)
{
r.MonthTarget = target.SaleTaget;
r.DayTarget = r.MonthTarget / DateTime.DaysInMonth(RetailDay.Year, RetailDay.Month);
r.CompletionRate = r.SaleMoney / r.DayTarget;
r.MonthCompletionRate = r.MonthSaleMoney / r.MonthTarget;
r.MonthUndone = r.MonthTarget - r.MonthSaleMoney;
}
}
return(result);
}
protected override IEnumerable <ShopExpensesBO> SearchData()
{
var lp = VMGlobal.DistributionQuery.LinqOP;
var oids = OrganizationArray.Select(o => o.ID).ToArray();
int beginYear = BeginMonth.Year, endYear = EndMonth.Year, beginMonth = BeginMonth.Month, endMonth = EndMonth.Month;
var data = lp.Search <ShopExpenses>(o => o.Month >= beginMonth && o.Month <= endMonth && o.Year >= beginYear && o.Year <= endYear && oids.Contains(o.OrganizationID));
TotalCount = data.Count();
var pagedData = data.OrderByDescending(o => o.Year).ThenByDescending(o => o.Month).ThenByDescending(o => o.OrganizationID).Skip(PageIndex * PageSize).Take(PageSize).ToList();
return(new ObservableCollection <ShopExpensesBO>(pagedData.Select(o => new ShopExpensesBO(o))));
}
protected override IEnumerable <RetailAggregationEntity> SearchData()
{
var lp = VMGlobal.DistributionQuery.LinqOP;
var retailContext = lp.GetDataContext <BillRetail>();
var detailsContext = lp.GetDataContext <BillRetailDetails>();
var productContext = lp.GetDataContext <ViewProduct>();
var oids = OrganizationArray.Select(o => o.ID).ToArray();
//var orgContext = lp.Search<ViewOrganization>(o => oids.Contains(o.ID) && o.Flag);
var brandIDs = VMGlobal.PoweredBrands.Select(o => o.ID);
var data = from retail in retailContext
//from org in orgContext
//where retail.OrganizationID == org.ID
from details in detailsContext
where retail.ID == details.BillID && oids.Contains(retail.OrganizationID)
from product in productContext
where product.ProductID == details.ProductID && brandIDs.Contains(product.BrandID)
select new RetailEntityForAggregation
{
OrganizationID = retail.OrganizationID,
ProductID = product.ProductID,
BrandID = product.BrandID,
CreateTime = retail.CreateTime.Date,
StyleCode = product.StyleCode,
Quantity = details.Quantity,
//Year = product.Year,
//Quarter = product.Quarter,
BYQID = product.BYQID,
DiscountMoney = details.Price * details.Quantity * details.Discount / 100, //这里的CostMoney和Retail表里的CostMoney不一样，相差下面的CutMoney
CutMoney = details.CutMoney,
Year = product.Year,
Quarter = product.Quarter
};
data = (IQueryable <RetailEntityForAggregation>)data.Where(FilterDescriptors);
var result = ReportDataContext.AggregateBillRetail(data);
FloatPriceHelper fpHelper = new FloatPriceHelper();
result.ForEach(o =>
{
o.Price = fpHelper.GetFloatPrice(VMGlobal.CurrentUser.OrganizationID, o.BYQID, o.Price);
o.CostMoney = o.DiscountMoney - o.CutMoney;
});
return(result);
}
/// <summary>
/// 发货单汇总
/// </summary>
private DataTable SearchData()
{
var lp = VMGlobal.DistributionQuery.LinqOP;
var oids = OrganizationArray.Select(o => o.ID).ToArray();
var deliveryContext = lp.Search <BillDelivery>(o => o.OrganizationID == VMGlobal.CurrentUser.OrganizationID);
var deliveryDetailsContext = lp.GetDataContext <BillDeliveryDetails>();
var productContext = lp.GetDataContext <ViewProduct>();
var brandIDs = VMGlobal.PoweredBrands.Select(o => o.ID);
//FilterBillWithBrand(deliveryContext, filters, brandIDs);
//假如未设置机构条件，则默认下级所有机构
//if (!FilterConditionHelper.IsConditionSetted(filters, "ToOrganizationID"))
//{
// var oids = OrganizationListVM.CurrentOrganization.ChildrenOrganizations.Select(o => o.ID).ToArray();
// orgContext = orgContext.Where(o => oids.Contains(o.ID));
//}
var data = from delivery in deliveryContext
from deDetails in deliveryDetailsContext
where delivery.ID == deDetails.BillID && oids.Contains(delivery.ToOrganizationID) && brandIDs.Contains(delivery.BrandID)
from product in productContext
where product.ProductID == deDetails.ProductID //&& brandIDs.Contains(product.BrandID)
select new BillDeliveryForAggregation
{
ProductID = product.ProductID,
BrandID = product.BrandID,
CreateTime = delivery.CreateTime.Date,
//ProductCode = product.ProductCode,
//BrandCode = product.BrandCode,
StyleCode = product.StyleCode,
//ColorCode = product.ColorCode,
//SizeName = product.SizeName,
//Price = product.Price,
Quantity = deDetails.Quantity,
Status = delivery.Status,
NameID = product.NameID,
Year = product.Year,
Quarter = product.Quarter
};
data = (IQueryable <BillDeliveryForAggregation>)data.Where(FilterDescriptors);
return(new BillReportHelper().TransferSizeToHorizontal <DistributionProductShow>(ReportDataContext.AggregateBill(data)));
}
public List <OrderDistributionEntity> GetSubordinateOrderDistribution()
{
var lp = VMGlobal.DistributionQuery.LinqOP;
var oids = OrganizationArray.Select(o => o.ID).ToArray();
var data = ReportDataContext.GetSubordinateOrderAggregation(FilterDescriptors, oids);
var temp = data.GroupBy(o => new { o.ProductID, o.OrganizationID }).Select(g => new { g.Key, Quantity = g.Sum(o => o.Quantity), QuaDelivered = g.Sum(o => o.QuaDelivered) }).ToList();
var pids = temp.Select(o => o.Key.ProductID).ToArray();
var products = lp.Search <ViewProduct>(o => pids.Contains(o.ProductID)).ToList();
//var oids = temp.Select(o => o.Key.OrganizationID).ToArray();
//var organizations = lp.Search<ViewOrganization>(o => oids.Contains(o.ID)).ToList();
temp.RemoveAll(o => o.Quantity == 0 && o.QuaDelivered == 0);
var result = temp.Select(o =>
{
var product = products.First(p => p.ProductID == o.Key.ProductID);
var organization = OrganizationArray.First(p => p.ID == o.Key.OrganizationID);
return(new OrderDistributionEntity
{
OrganizationID = o.Key.OrganizationID,
OrganizationName = organization.Name,
ProductID = o.Key.ProductID,
ProductCode = product.ProductCode,
StyleCode = product.StyleCode,
BYQID = product.BYQID,
ColorID = product.ColorID,
SizeID = product.SizeID,
Quantity = o.Quantity,
QuaDelivered = o.QuaDelivered
});
}).ToList();
foreach (var r in result)
{
r.ColorCode = VMGlobal.Colors.Find(o => o.ID == r.ColorID).Code;
r.SizeName = VMGlobal.Sizes.Find(o => o.ID == r.SizeID).Name;
var byq = VMGlobal.BYQs.Find(o => o.ID == r.BYQID);
r.BrandID = byq.BrandID;
r.BrandCode = VMGlobal.PoweredBrands.Find(o => o.ID == r.BrandID).Code;
}
return(result);
}
protected override IEnumerable <FundAccountTotalEntity> SearchData()
{
var lp = VMGlobal.DistributionQuery.LinqOP;
var oids = OrganizationArray.Select(o => o.ID).ToArray();
var fundAccountContext = lp.Search <OrganizationFundAccount>(o => oids.Contains(o.OrganizationID));
if (BrandID != default(int))
{
fundAccountContext = fundAccountContext.Where(o => o.BrandID == BrandID);
}
var temp = fundAccountContext.GroupBy(o => new { o.OrganizationID, o.BrandID }).Select(g => new { g.Key, NeedIn = g.Sum(o => o.NeedIn), AlreadyIn = g.Sum(o => o.AlreadyIn) }).ToList();
return(temp.Select(o => new FundAccountTotalEntity
{
OrganizationID = o.Key.OrganizationID,
BrandID = o.Key.BrandID,
BrandName = VMGlobal.PoweredBrands.Find(b => b.ID == o.Key.BrandID).Name,
OrganizationCode = OrganizationArray.First(b => b.ID == o.Key.OrganizationID).Code,
OrganizationName = OrganizationArray.First(b => b.ID == o.Key.OrganizationID).Name,
AlreadyIn = o.AlreadyIn,
NeedIn = o.NeedIn,
Balance = o.AlreadyIn - o.NeedIn
}).ToList());
}
protected override IEnumerable <FundAccountSearchEntity> SearchData()
{
var oids = OrganizationArray.Select(o => o.ID);
var lp = VMGlobal.DistributionQuery.LinqOP;
var fundAccountContext = lp.Search <OrganizationFundAccount>(o => oids.Contains(o.OrganizationID));
var brands = VMGlobal.PoweredBrands;
var data = from fa in fundAccountContext
select new FundAccountSearchEntity
{
OrganizationID = fa.OrganizationID,
BrandID = fa.BrandID,
AlreadyIn = fa.AlreadyIn,
NeedIn = fa.NeedIn,
OccurDate = fa.CreateTime.Date,
CreateTime = fa.CreateTime,
RefrenceBillCode = fa.RefrenceBillCode,
RefrenceBillKind = Enum.GetName(typeof(BillTypeEnum), fa.BillKind),
Remark = fa.Remark,
BalanceAtThatTime = fundAccountContext.Where(o => o.OrganizationID == fa.OrganizationID && o.BrandID == fa.BrandID && o.CreateTime <= fa.CreateTime).Sum(o => o.AlreadyIn - o.NeedIn)
};
var filtedData = (IQueryable <FundAccountSearchEntity>)data.Where(FilterDescriptors);
TotalCount = filtedData.Count();
var result = filtedData.OrderByDescending(o => o.CreateTime).Skip(PageIndex * PageSize).Take(PageSize).ToList();
result.ForEach(d =>
{
d.BrandName = brands.FirstOrDefault(b => b.ID == d.BrandID).Name;
var typeField = typeof(BillTypeEnum).GetField(d.RefrenceBillKind);
var displayNames = typeField.GetCustomAttributes(typeof(EnumDescriptionAttribute), false);
d.RefrenceBillKind = ((EnumDescriptionAttribute)displayNames[0]).Description;
d.OrganizationCode = OrganizationArray.First(o => o.ID == d.OrganizationID).Code;
d.OrganizationName = OrganizationArray.First(o => o.ID == d.OrganizationID).Name;
});
return(result);
}
private IEnumerable <OrganizationProductQuantity> GetSubordinateOrderAggregation()
{
var lp = VMGlobal.DistributionQuery.LinqOP;
//var organizations = OrganizationListVM.CurrentOrganization.ChildrenOrganizations;
var oids = OrganizationArray.Select(o => o.ID).ToArray();
var brandIDs = VMGlobal.PoweredBrands.Select(o => o.ID);
var orderContext = lp.Search <BillOrder>(o => oids.Contains(o.OrganizationID) && brandIDs.Contains(o.BrandID));
var orderDetailsContext = lp.GetDataContext <BillOrderDetails>();
var productContext = lp.GetDataContext <ViewProduct>();
var data = from order in orderContext
from details in orderDetailsContext
where order.ID == details.BillID && order.IsDeleted == false
from product in productContext
where product.ProductID == details.ProductID
select new BillEntityForAggregation
{
OrganizationID = order.OrganizationID,
BrandID = product.BrandID,
ProductID = product.ProductID,
StyleCode = product.StyleCode,
Quantity = details.Quantity - details.QuaCancel - details.QuaDelivered
};
data = (IQueryable <BillEntityForAggregation>)data.Where(FilterDescriptors);
data = data.Where(o => o.Quantity != 0);
var temp = data.GroupBy(o => new { o.ProductID, o.OrganizationID }).Select(g => new { g.Key, Quantity = g.Sum(o => o.Quantity) }).ToList();
var result = temp.Select(o => new OrganizationProductQuantity
{
OrganizationID = o.Key.OrganizationID,
ProductID = o.Key.ProductID,
Quantity = o.Quantity
}).ToList();
result.ForEach(o => o.OrganizationName = OrganizationArray.First(c => c.ID == o.OrganizationID).Name);
return(result);
}
public void SearchData()
{
var lp = VMGlobal.DistributionQuery.LinqOP;
var oids = OrganizationArray.Select(o => o.ID).ToArray();
var endDate = EndDate.AddDays(1);
var retailContext = lp.Search <BillRetail>(o => o.CreateTime >= BeginDate && o.CreateTime <= endDate && oids.Contains(o.OrganizationID));
var detailsContext = lp.GetDataContext <BillRetailDetails>();
var productContext = lp.GetDataContext <ViewProduct>();
if (BrandID != default(int))
{
productContext = productContext.Where(p => p.BrandID == BrandID);
}
if (Quarter != default(int))
{
productContext = productContext.Where(p => p.Quarter == Quarter);
}
var data = from retail in retailContext
from details in detailsContext
where retail.ID == details.BillID
from product in productContext
where product.ProductID == details.ProductID && product.Year == Year
select new ABCEntity
{
StyleCode = product.StyleCode,
CostMoney = details.Price * details.Quantity * details.Discount / 100 - details.CutMoney,
ColorID = product.ColorID,
NameID = product.NameID
};
var result = data.ToList();
foreach (var r in result)
{
r.Name = VMGlobal.ProNames.Find(o => o.ID == r.NameID).Name;
}
AmountCostMoney = result.Sum(o => o.CostMoney);
StyleABCEntity = result.GroupBy(o => new { o.StyleCode, o.Name }).Select(g => new ABCEntity
{
StyleCode = g.Key.StyleCode,
Name = g.Key.Name,
CostMoney = g.Sum(o => o.CostMoney)
}).OrderByDescending(o => o.CostMoney).ToList();
decimal accumulativeProportion = 0;
foreach (var abc in StyleABCEntity)
{
abc.Proportion = abc.CostMoney / AmountCostMoney;
accumulativeProportion += abc.Proportion;
abc.AccuProportion = accumulativeProportion;
}
ProNameABCEntity = result.GroupBy(o => o.Name).Select(g => new ABCEntity
{
Name = g.Key,
CostMoney = g.Sum(o => o.CostMoney)
}).OrderByDescending(o => o.CostMoney).ToList();
accumulativeProportion = 0;
foreach (var abc in ProNameABCEntity)
{
abc.Proportion = abc.CostMoney / AmountCostMoney;
accumulativeProportion += abc.Proportion;
abc.AccuProportion = accumulativeProportion;
}
ColorABCEntity = result.GroupBy(o => o.ColorID).Select(g => new ABCEntity
{
ColorID = g.Key,
CostMoney = g.Sum(o => o.CostMoney)
}).OrderByDescending(o => o.CostMoney).ToList();
accumulativeProportion = 0;
foreach (var abc in ColorABCEntity)
{
abc.ColorName = VMGlobal.Colors.Find(o => o.ID == abc.ColorID).Name;
abc.Proportion = abc.CostMoney / AmountCostMoney;
accumulativeProportion += abc.Proportion;
abc.AccuProportion = accumulativeProportion;
}
OnPropertyChanged("StyleABCEntity");
OnPropertyChanged("ColorABCEntity");
OnPropertyChanged("ProNameABCEntity");
OnPropertyChanged("AmountCostMoney");
}
protected override IEnumerable <StocktakeSearchEntity> SearchData()
{
var lp = VMGlobal.DistributionQuery.LinqOP;
var oids = OrganizationArray.Select(o => o.ID).ToArray();
var brands = VMGlobal.PoweredBrands;
var brandIDs = brands.Select(b => b.ID);
var stocktakeContext = lp.Search <BillStocktake>(o => oids.Contains(o.OrganizationID) && brandIDs.Contains(o.BrandID));;
var billData = from o in stocktakeContext
select new StocktakeEntityForSearch
{
BrandID = o.BrandID,
Code = o.Code,
CreateTime = o.CreateTime,
CreateDate = o.CreateTime.Date,
CreatorID = o.CreatorID,
ID = o.ID,
IsDeleted = o.IsDeleted,
OrganizationID = o.OrganizationID,
Remark = o.Remark,
Status = o.Status
};
var detailsContext = lp.GetDataContext <BillStocktakeDetails>();
var pIDs = ProductHelper.GetProductIDArrayWithCondition(DetailsDescriptors, brandIDs);
if (pIDs != null)
{
if (pIDs.Count() == 0)
{
return(null);
}
billData = from d in billData
where detailsContext.Any(od => od.BillID == d.ID && pIDs.Contains(od.ProductID))
select d;
}
var filtedData = (IQueryable <StocktakeEntityForSearch>)billData.Where(FilterDescriptors);
TotalCount = filtedData.Count();
filtedData = filtedData.OrderByDescending(o => o.ID).Skip(PageIndex * PageSize).Take(PageSize);
var userContext = lp.GetDataContext <ViewUser>();
var result = from d in filtedData
from user in userContext
where d.CreatorID == user.ID
select new StocktakeSearchEntity
{
BillID = d.ID,
BrandID = d.BrandID,
Code = d.Code,
CreateTime = d.CreateTime,
CreatorName = user.Name,
Remark = d.Remark,
Status = d.Status,
IsDeleted = d.IsDeleted,
OrganizationID = d.OrganizationID
};
var stocktakes = result.ToList();
var bIDs = stocktakes.Select(o => (int)o.BillID);
var sum = detailsContext.Where(o => bIDs.Contains(o.BillID)).GroupBy(o => o.BillID).Select(g => new { BillID = g.Key, Quantity = g.Sum(o => o.Quantity) }).ToList();
stocktakes.ForEach(d =>
{
d.OrganizationName = OrganizationArray.First(o => o.ID == d.OrganizationID).Name;
d.BrandName = brands.FirstOrDefault(b => b.ID == d.BrandID).Name;
var details = sum.Find(o => o.BillID == d.BillID);
d.Quantity = details.Quantity;
});
return(stocktakes);
}
protected override IEnumerable <ContrastSearchEntity> SearchData()
{
var lp = VMGlobal.DistributionQuery.LinqOP;
var oids = OrganizationArray.Select(o => o.ID).ToArray();
var brands = VMGlobal.PoweredBrands;
var brandIDs = brands.Select(b => b.ID);
var contrastContext = lp.Search <BillStocktakeContrast, BillStocktakeContrast>(selector: o => new BillStocktakeContrast
{
BrandID = o.BrandID,
Code = o.Code,
CreateTime = o.CreateTime.Date,
CreatorID = o.CreatorID,
ID = o.ID,
OrganizationID = o.OrganizationID,
Remark = o.Remark,
StorageID = o.StorageID
},
condition: o => oids.Contains(o.OrganizationID) && brandIDs.Contains(o.BrandID));
var filtedData = (IQueryable <BillStocktakeContrast>)contrastContext.Where(FilterDescriptors);
var detailsContext = lp.GetDataContext <BillStocktakeContrastDetails>();
var pIDs = ProductHelper.GetProductIDArrayWithCondition(DetailsDescriptors, brandIDs);
if (pIDs != null)
{
if (pIDs.Count() == 0)
{
return(null);
}
filtedData = from d in filtedData
where detailsContext.Any(od => od.BillID == d.ID && pIDs.Contains(od.ProductID))
select d;
}
TotalCount = filtedData.Count();
filtedData = filtedData.OrderByDescending(o => o.ID).Skip(PageIndex * PageSize).Take(PageSize);
var userContext = lp.GetDataContext <ViewUser>();
var storageContext = lp.GetDataContext <Storage>();
var result = from d in filtedData
from user in userContext
where d.CreatorID == user.ID
from storage in storageContext
where d.StorageID == storage.ID
select new ContrastSearchEntity
{
ID = d.ID,
BrandID = d.BrandID,
Code = d.Code,
CreateTime = d.CreateTime,
CreatorName = user.Name,
Remark = d.Remark,
OrganizationID = d.OrganizationID
};
var contrasts = result.ToList();
var bIDs = contrasts.Select(o => (int)o.ID);
var sum = detailsContext.Where(o => bIDs.Contains(o.BillID)).GroupBy(o => o.BillID).Select(g => new
{
BillID = g.Key,
Quantity = g.Sum(o => o.Quantity),
QuaStocktake = g.Sum(o => o.QuaStocktake),
QuaStockOrig = g.Sum(o => o.QuaStockOrig),
QuaContrast = g.Sum(o => Math.Abs(o.QuaStocktake - o.QuaStockOrig))
}).ToList();
contrasts.ForEach(d =>
{
d.OrganizationName = OrganizationArray.First(o => o.ID == d.OrganizationID).Name;
d.BrandName = brands.First(b => b.ID == d.BrandID).Name;
var details = sum.Find(o => o.BillID == d.ID);
d.Quantity = details.Quantity;
d.QuaStockOrig = details.QuaStockOrig;
d.QuaStocktake = details.QuaStocktake;
d.QuaContrast = details.QuaContrast;
});
return(contrasts);
}
protected override IEnumerable <RetailSearchEntity> SearchData()
{
var lp = VMGlobal.DistributionQuery.LinqOP;
var oids = OrganizationArray.Select(o => o.ID).ToArray();
var retailContext = lp.Search <BillRetail>(o => oids.Contains(o.OrganizationID));
var userContext = lp.GetDataContext <ViewUser>();
var vipContext = lp.GetDataContext <VIPCard>();
var brands = VMGlobal.PoweredBrands;
var brandIDs = brands.Select(b => b.ID);
var billData = from d in retailContext
from user in userContext
where d.CreatorID == user.ID
//from vip in vipContext
//where (d.VIPID == vip.ID || d.VIPID == null) //可能会产生重复数据
join vip in vipContext on d.VIPID equals vip.ID into vips
from v in vips.DefaultIfEmpty() //生成Left [outer] join左联接语句，就不会产生重复数据了
select new RetailSearchEntity
{
OrganizationID = d.OrganizationID, //跟查询条件相关的属性需要显式声明[赋值]，即使父类里已经定义，否则在生成SQL语句的过程中会报错
ID = d.ID,
Remark = d.Remark,
Code = d.Code,
CreateDate = d.CreateTime.Date,
CreateTime = d.CreateTime,
CreatorName = user.Name,
VIPID = d.VIPID,
//VIPCode = (d.VIPID == null ? "" : vipContext.First(v => v.ID == d.VIPID).Code),//这样是可以滴
VIPCode = v.Code,
VIPName = v.CustomerName,
Quantity = d.Quantity,
CostMoney = d.CostMoney,
ReceiveMoney = d.ReceiveMoney,
TicketMoney = d.TicketMoney,
PredepositPay = d.PredepositPay
};
var detailsContext = lp.GetDataContext <BillRetailDetails>();
var pIDs = ProductHelper.GetProductIDArrayWithCondition(DetailsDescriptors, brandIDs);
if (pIDs != null)
{
if (pIDs.Count() == 0)
{
return(null);
}
billData = from d in billData
where detailsContext.Any(od => od.BillID == d.ID && pIDs.Contains(od.ProductID))
select d;
}
var filtedData = (IQueryable <RetailSearchEntity>)billData.Where(FilterDescriptors);
filtedData = filtedData.Distinct();
TotalCount = filtedData.Count();
if (TotalCount == 0)
{
return(null);
}
var retails = filtedData.OrderByDescending(o => o.ID).Skip(PageIndex * PageSize).Take(PageSize).ToList();
retails.ForEach(r =>
{
r.OrganizationName = OrganizationArray.First(o => o.ID == r.OrganizationID).Name;
});
return(retails);
}
protected override IEnumerable <DistributionEntity> SearchData()
{
var lp = VMGlobal.DistributionQuery.LinqOP;
var oids = OrganizationArray.Select(o => o.ID).ToArray();
var brandIDs = VMGlobal.PoweredBrands.Select(o => o.ID);
var storageContext = lp.Search <Storage>(o => oids.Contains(o.OrganizationID));
var stockContext = lp.GetDataContext <Stock>();
var productContext = lp.Search <ViewProduct>(o => brandIDs.Contains(o.BrandID));
var orgContext = lp.GetDataContext <ViewOrganization>();
var data = from stock in stockContext
from storage in storageContext
where stock.StorageID == storage.ID && stock.Quantity != 0
from org in orgContext
where storage.OrganizationID == org.ID
from product in productContext
where product.ProductID == stock.ProductID
select new BillEntityForAggregation
{
BrandID = product.BrandID,
ProductID = product.ProductID,
StyleCode = product.StyleCode,
Quantity = stock.Quantity,
OrganizationID = storage.OrganizationID,
NameID = product.NameID
};
var filtedData = (IQueryable <BillEntityForAggregation>)data.Where(FilterDescriptors);
var sum = filtedData.GroupBy(o => new { o.ProductID, o.OrganizationID }).Select(g => new
{
Key = g.Key,
Quantity = g.Sum(o => o.Quantity)
}).ToList();
var pids = sum.Select(o => o.Key.ProductID).ToArray();
var products = lp.Search <ViewProduct>(o => pids.Contains(o.ProductID)).ToList();
var result = sum.Select(o =>
{
var product = products.First(p => p.ProductID == o.Key.ProductID);
return(new DistributionEntity
{
ProductID = product.ProductID,
ProductCode = product.ProductCode,
StyleCode = product.StyleCode,
BYQID = product.BYQID,
ColorID = product.ColorID,
SizeID = product.SizeID,
Quantity = o.Quantity,
OrganizationID = o.Key.OrganizationID,
OrganizationName = OrganizationArray.First(c => c.ID == o.Key.OrganizationID).Name
});
}).ToList();
foreach (var r in result)
{
r.ColorCode = VMGlobal.Colors.Find(o => o.ID == r.ColorID).Code;
r.SizeName = VMGlobal.Sizes.Find(o => o.ID == r.SizeID).Name;
var byq = VMGlobal.BYQs.Find(o => o.ID == r.BYQID);
r.BrandID = byq.BrandID;
r.BrandCode = VMGlobal.PoweredBrands.Find(o => o.ID == r.BrandID).Code;
}
return(result);
}
protected override IEnumerable <ShopProfitEntity> SearchData()
{
var oids = OrganizationArray.Select(o => o.ID).ToArray();
var organizations = OrganizationArray.ToArray();
int by = BeginMonth.Year, bm = BeginMonth.Month, ey = EndMonth.Year, em = EndMonth.Month;
var endMonth = EndMonth.AddMonths(1);
var lp = VMGlobal.DistributionQuery.LinqOP;
var retailData = lp.Search <BillRetail>(o => o.CreateTime >= BeginMonth && o.CreateTime < endMonth);
var targets = lp.Search <RetailMonthTaget>(o => oids.Contains(o.OrganizationID) && o.Year >= by && o.Month >= bm && o.Year <= ey && o.Month <= em).ToArray();
var expenses = lp.Search <ShopExpense>(o => o.OccurDate >= BeginMonth && o.OccurDate < endMonth && oids.Contains(o.OrganizationID)).ToArray();
if (IsShowShopHasExpensesOnly)
{
var sids = expenses.Select(o => o.OrganizationID).Intersect(oids).ToArray();
retailData = retailData.Where(o => sids.Contains(o.OrganizationID));
organizations = organizations.Where(o => sids.Contains(o.ID)).ToArray();
}
else
{
retailData = retailData.Where(o => oids.Contains(o.OrganizationID));
}
var retails = retailData.Select(o => new
{
OrganizationID = o.OrganizationID,
RetailID = o.ID,
QuaRetail = o.Quantity,
MoneyRetail = o.CostMoney,
RetailTime = o.CreateTime
}).ToList();
if (IsShowShopHasRetailOnly)
{
var rids = retails.Select(o => o.OrganizationID);
expenses = expenses.Where(o => rids.Contains(o.OrganizationID)).ToArray();
organizations = organizations.Where(o => rids.Contains(o.ID)).ToArray();
}
var rbillIDs = retails.Select(o => o.RetailID).ToArray();
var rDetailData = lp.Search <BillRetailDetails>(o => rbillIDs.Contains(o.BillID));
var productData = lp.GetDataContext <ViewProduct>();
var rDetailQuery = from rDetail in rDetailData
from product in productData
where rDetail.ProductID == product.ProductID
select new
{
RetailID = rDetail.BillID,
rDetail.Price,
rDetail.ProductID,
rDetail.Quantity,
product.BYQID,
product.CostPrice
};
var rDetails = rDetailQuery.ToList();
List <ShopProfitEntity> result = new List <ShopProfitEntity>();
foreach (var organization in organizations)
{
var ryms = retails.Where(o => o.OrganizationID == organization.ID).Select(o => new DateTime(o.RetailTime.Year, o.RetailTime.Month, 1)).Distinct();
var eyms = expenses.Where(o => o.OrganizationID == organization.ID).Select(o => new DateTime(o.OccurDate.Year, o.OccurDate.Month, 1)).Distinct();
var yms = ryms.Union(eyms);
foreach (var ym in yms)
{
ShopProfitEntity entity = new ShopProfitEntity
{
OrganizationID = organization.ID,
OrganizationName = organization.Name,
YearMonth = ym.ToString("yyyy-MM")
};
var target = targets.FirstOrDefault(o => o.OrganizationID == organization.ID && ym.Year == o.Year && ym.Month == o.Month);
if (target != null)
{
entity.MonthTarget = target.SaleTaget;
}
var tempRetails = retails.Where(o => o.OrganizationID == organization.ID && ym.Year == o.RetailTime.Year && ym.Month == o.RetailTime.Month);
entity.SaleQuantity = tempRetails.Sum(o => o.QuaRetail);
entity.SaleMoney = tempRetails.Sum(o => o.MoneyRetail);
var tempRDetails = rDetails.Where(o => tempRetails.Select(r => r.RetailID).Contains(o.RetailID));
entity.OriginalPrice = tempRDetails.Sum(o => o.Quantity * o.Price);
if (VMGlobal.CurrentUser.OrganizationID == 1)
{
entity.CostPrice = tempRDetails.Sum(o => o.Quantity * o.CostPrice);
}
else
{
entity.CostPrice = tempRDetails.Sum(o => o.Quantity * (_fpHelper.GetFloatPrice(OrganizationListVM.CurrentOrganization.ParentID, o.BYQID, o.Price) * _cdhelper.GetDiscount(o.BYQID, VMGlobal.CurrentUser.OrganizationID) * 0.01M));
}
// var expense = expenses.FirstOrDefault(o => o.OrganizationID == organization.ID && ym.Year == o.Year && ym.Month == o.Month);
// if (expense != null)
// {
// entity.AdvertisingFee = expense.AdvertisingFee;
// entity.BaseCost = expense.BaseCost;
// entity.Bonus = expense.Bonus;
// entity.ElectricCharge = expense.ElectricCharge;
// entity.ExpressCharge = expense.ExpressCharge;
// entity.ManagerCost = expense.ManagerCost;
// entity.OrganizationID = expense.OrganizationID;
// entity.OtherCost = expense.OtherCost;
// entity.RenovationCost = expense.RenovationCost;
// entity.Rent = expense.Rent;
// entity.Salary = expense.Salary;
// entity.SocialSecurityCharge = expense.SocialSecurityCharge;
// entity.TelephoneCharge = expense.TelephoneCharge;
// }
result.Add(entity);
}
}
TableData = this.TransferToTable(result, expenses);
return(result);
}
protected override IEnumerable <DeliverySearchEntity> SearchData()
{
var lp = VMGlobal.DistributionQuery.LinqOP;
var oids = OrganizationArray.Select(o => o.ID).ToArray();
var brands = VMGlobal.PoweredBrands;
var brandIDs = brands.Select(b => b.ID);
var deliveryContext = lp.Search <BillDelivery>(o => o.OrganizationID == VMGlobal.CurrentUser.OrganizationID && oids.Contains(o.ToOrganizationID) && brandIDs.Contains(o.BrandID));
//var orgContext = lp.Search<ViewOrganization>(o => oids.Contains(o.ID) && o.Flag);
var userContext = lp.GetDataContext <ViewUser>();
var storageContext = lp.GetDataContext <Storage>();
var billData = from d in deliveryContext
from user in userContext
where d.CreatorID == user.ID
from storage in storageContext
where storage.ID == d.StorageID
select new DeliverySearchEntity
{
ToOrganizationID = d.ToOrganizationID, //跟查询条件相关的属性需要显式声明[赋值]，即使父类里已经定义，否则在生成SQL语句的过程中会报错
ID = d.ID,
Remark = d.Remark,
Code = d.Code,
BrandID = d.BrandID,
CreateTime = d.CreateTime,
CreateDate = d.CreateTime.Date,
CreatorName = user.Name,
Status = d.Status,
StorageName = storage.Name,
StorageID = d.StorageID,
DeliveryKind = d.DeliveryKind
};
var detailsContext = lp.GetDataContext <BillDeliveryDetails>();
var pIDs = ProductHelper.GetProductIDArrayWithCondition(DetailsDescriptors, brandIDs);
if (pIDs != null)
{
if (pIDs.Count() == 0)
{
return(null);
}
billData = from d in billData
where detailsContext.Any(od => od.BillID == d.ID && pIDs.Contains(od.ProductID))
select d;
}
var filtedData = (IQueryable <DeliverySearchEntity>)billData.Where(FilterDescriptors);
TotalCount = filtedData.Count();
var deliveries = filtedData.OrderByDescending(o => o.ID).Skip(PageIndex * PageSize).Take(PageSize).ToList();
var bIDs = deliveries.Select(o => (int)o.ID);
var sum = detailsContext.Where(o => bIDs.Contains(o.BillID)).GroupBy(o => o.BillID).Select(g => new { BillID = g.Key, Quantity = g.Sum(o => o.Quantity), TotalPrice = g.Sum(o => o.Price * o.Quantity), TotalCostPrice = g.Sum(o => o.Price * o.Quantity * o.Discount) }).ToList();
deliveries.ForEach(d =>
{
d.BrandName = brands.FirstOrDefault(o => d.BrandID == o.ID).Code;
var details = sum.Find(o => o.BillID == d.ID);
d.Quantity = details.Quantity;
d.TotalPrice = details.TotalPrice;
d.TotalCostMoney = details.TotalCostPrice * (0.01m);
d.ToOrganizationName = VMGlobal.ChildOrganizations.Find(o => o.ID == d.ToOrganizationID).Name;
});
return(deliveries);
}
