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