protected override IEnumerable <ProductShow> SearchData()
{
var lp = VMGlobal.DistributionQuery.LinqOP;
var brandIDs = VMGlobal.PoweredBrands.Select(o => o.ID);
var storeoutContext = lp.GetDataContext <BillStoreOut>().Where(o => o.OrganizationID == VMGlobal.CurrentUser.OrganizationID && brandIDs.Contains(o.BrandID));
var detailsContext = lp.GetDataContext <BillStoreOutDetails>();
var productContext = lp.GetDataContext <ViewProduct>();
var data = from storeout in storeoutContext
from details in detailsContext
where storeout.ID == details.BillID
from product in productContext
where product.ProductID == details.ProductID //&& brandIDs.Contains(product.BrandID)
select new StoreOIEntityForAggregation
{
ProductID = product.ProductID,
BrandID = product.BrandID,
StorageID = storeout.StorageID,
CreateTime = storeout.CreateTime.Date,
BillType = storeout.BillType,
StyleCode = product.StyleCode,
NameID = product.NameID,
Quantity = details.Quantity
};
data = (IQueryable <StoreOIEntityForAggregation>)data.Where(FilterDescriptors);
return(ReportDataContext.AggregateBill(data));
}
/// <summary>
/// 查询本级订单
/// </summary>
protected override IEnumerable <OrderSearchEntity> SearchData()
{
var lp = VMGlobal.DistributionQuery.LinqOP;
var orderContext = lp.GetDataContext <BillOrder>();
var userContext = lp.GetDataContext <ViewUser>();
var billData = from order in orderContext
from user in userContext
where order.CreatorID == user.ID && order.OrganizationID == VMGlobal.CurrentUser.OrganizationID && order.IsDeleted == false
select new OrderSearchEntity
{
BillID = order.ID,
BrandID = order.BrandID,
单据编号 = order.Code,
CreateDate = order.CreateTime.Date,
CreateTime = order.CreateTime,
开单人 = user.Name,
备注 = order.Remark,
订单状态 = !order.IsDeleted
};
int totalCount = 0;
var result = ReportDataContext.SearchOrder(billData, FilterDescriptors, DetailsDescriptors, PageIndex, PageSize, ref totalCount);
TotalCount = totalCount;
return(result);
}
protected override IEnumerable <DistributionProductShow> SearchData()
{
var lp = VMGlobal.DistributionQuery.LinqOP;
var brandIDs = VMGlobal.PoweredBrands.Select(o => o.ID);
var childOrganizations = OrganizationListVM.CurrentOrganization.ChildrenOrganizations;
var oids = childOrganizations.Select(o => o.ID);
var stocktakeContext = lp.Search <BillStocktake>(o => oids.Contains(o.OrganizationID) && !o.IsDeleted && brandIDs.Contains(o.BrandID));
var detailsContext = lp.GetDataContext <BillStocktakeDetails>();
var productContext = lp.GetDataContext <ViewProduct>();
var data = from st in stocktakeContext
from details in detailsContext
where st.ID == details.BillID
from product in productContext
where product.ProductID == details.ProductID
select new StocktakeEntityForAggregation
{
ID = st.ID,
ProductID = product.ProductID,
BrandID = product.BrandID,
Code = st.Code,
CreateTime = st.CreateTime.Date,
Status = st.Status,
StorageID = st.StorageID,
StyleCode = product.StyleCode,
Quantity = details.Quantity,
OrganizationID = st.OrganizationID
};
var filtedData = (IQueryable <StocktakeEntityForAggregation>)data.Where(FilterDescriptors);
return(ReportDataContext.AggregateBill(filtedData));
}
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 <AllocateEntity> SearchData()
{
var lp = VMGlobal.DistributionQuery.LinqOP;
var stocks = ReportDataContext.GetAvailableStock(StorageID, Styles).Where(o => o.Quantity > 0);
var pids = stocks.Select(o => o.ProductID);
var products = lp.Search <ViewProduct>(o => pids.Contains(o.ProductID)).ToList().OrderBy(o => o.ProductCode);
var orders = this.GetOrderAggregation(pids);
var result = products.Select(o =>
{
var entity = new AllocateEntity
{
ProductID = o.ProductID,
ProductCode = o.ProductCode,
StyleCode = o.StyleCode,
SizeID = o.SizeID
};
entity.BrandCode = VMGlobal.PoweredBrands.FirstOrEmpty(b => b.ID == o.BrandID).Code;
entity.ColorCode = VMGlobal.Colors.FirstOrEmpty(b => b.ID == o.ColorID).Code;
entity.SizeName = VMGlobal.Sizes.FirstOrEmpty(b => b.ID == entity.SizeID).Name;
entity.AvailableQuantity = stocks.FirstOrEmpty(b => b.ProductID == o.ProductID).Quantity;
entity.OrderQuantity = orders.FirstOrEmpty(b => b.ProductID == o.ProductID).Quantity;
entity.AllocateQuantity = Math.Min(entity.AvailableQuantity, entity.OrderQuantity);
entity.Discount = _discountHelper.GetDiscount(o.BYQID, OrganizationID);
entity.Price = _fpHelper.GetFloatPrice(VMGlobal.CurrentUser.OrganizationID, o.BYQID, o.Price);
return(entity);
}).ToList();
return(result);
}
/// <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);
}
//IEnumerable<ItemPropertyDefinition> _itemPropertyDefinitions;
//public override IEnumerable<ItemPropertyDefinition> ItemPropertyDefinitions
//{
// get
// {
// if (_itemPropertyDefinitions == null)
// {
// _itemPropertyDefinitions = new List<ItemPropertyDefinition>()
// {
// new ItemPropertyDefinition { DisplayName = "开单日期", PropertyName = "CreateTime", PropertyType = typeof(DateTime)},
// new ItemPropertyDefinition { DisplayName = "订货品牌", PropertyName = "BrandID", PropertyType = typeof(int)},
// new ItemPropertyDefinition { DisplayName = "款号", PropertyName = "StyleCode", PropertyType = typeof(string) }
// };
// }
// return _itemPropertyDefinitions;
// }
//}
//CompositeFilterDescriptorCollection _filterDescriptors;
//public override CompositeFilterDescriptorCollection FilterDescriptors
//{
// get
// {
// if (_filterDescriptors == null)
// {
// _filterDescriptors = new CompositeFilterDescriptorCollection()
// {
// new FilterDescriptor("CreateTime", FilterOperator.IsGreaterThanOrEqualTo, DateTime.Now.Date),
// new FilterDescriptor("CreateTime", FilterOperator.IsLessThanOrEqualTo, DateTime.Now.Date),
// new FilterDescriptor("StyleCode", FilterOperator.Contains, FilterDescriptor.UnsetValue, false)
// };
// }
// return _filterDescriptors;
// }
//}
protected override IEnumerable <OrderAggregationEntity> SearchData()
{
var data = ReportDataContext.GetSubordinateOrderAggregation(FilterDescriptors, OrganizationArray.Select(o => o.ID).ToArray());
var result = this.AggregateOrder(data);
if (IsShowStock)
{
ShowStock(result);
}
return(result);
}
public OPResult SearchData()
{
if (StorageID == default(int))
{
return(new OPResult {
IsSucceed = false, Message = "请选择分货仓库."
});
}
//if (OrganizationIDArray == null || OrganizationIDArray.Count() == 0)
//{
// return new OPResult { IsSucceed = false, Message = "请选择分货机构." };
//}
if (Styles == null || Styles.Count() == 0)
{
return(new OPResult {
IsSucceed = false, Message = "请选择分货款式."
});
}
var stocks = ReportDataContext.GetAvailableStock(StorageID, Styles);
var pids = stocks.Select(o => o.ProductID);
var orders = this.GetSubordinateOrderAggregation(pids);
var table = this.GenerateDataTable(orders);
pids = orders.Select(o => o.ProductID).Distinct();//可用库存和订单交集的productid
var products = _linqOP.Search <ViewProduct>(o => pids.Contains(o.ProductID)).ToList().OrderBy(o => o.ProductCode);
foreach (var p in products)
{
DataRow row = table.NewRow();
table.Rows.Add(row);
row["SKU码"] = p.ProductCode;
row["品牌"] = VMGlobal.PoweredBrands.Find(o => o.ID == p.BrandID).Code;
row["款号"] = p.StyleCode;
row["色号"] = VMGlobal.Colors.Find(o => o.ID == p.ColorID).Code;
row["尺码"] = VMGlobal.Sizes.Find(o => o.ID == p.SizeID).Name;
row["剩余可用库存"] = row["可用库存"] = stocks.First(s => s.ProductID == p.ProductID).Quantity;
for (int i = 6; i < table.Columns.Count - 1; i++)
{
var on = table.Columns[i].ColumnName;
var order = orders.FirstOrDefault(o => o.ProductID == p.ProductID && o.OrganizationName == on);
if (order != null)
{
row[on] = order.Quantity;
}
}
}
Entities = table.DefaultView;
return(new OPResult {
IsSucceed = true
});
}
/// <summary>
/// 获取可用库存
/// </summary>
internal static IEnumerable <ProductQuantity> GetAvailableStock(int storageID, IEnumerable <ProStyle> styles)
{
var lp = VMGlobal.DistributionQuery.LinqOP;
var provider = VMGlobal.DistributionQuery.QueryProvider;
var stockContext = lp.Search <Stock>(o => o.StorageID == storageID && o.Quantity != 0);
var productContext = provider.GetTable <Product>("SysProcess.dbo.Product");
var styleContext = provider.GetTable <ProStyle>("SysProcess.dbo.ProStyle");
var styleIDs = styles.Select(o => o.ID);
var data = from stock in stockContext
from product in productContext
where stock.ProductID == product.ID
from style in styleContext
where product.StyleID == style.ID && styleIDs.Contains(style.ID)
select new ProductQuantity
{
Quantity = stock.Quantity,
ProductID = product.ID
};
var stocks = data.ToList();//即使filters中有data没有的过滤属性，也不会出错，但是会产生0<>0的恒为假条件
var pids = stocks.Select(o => o.ProductID);
//int status = (int)BillDeliveryStatusEnum.已装箱未配送;
//var deliveries = lp.Search<BillDelivery>(o => o.OrganizationID == VMGlobal.CurrentUser.OrganizationID && o.Status == status);
//var deliveryDetails = lp.GetDataContext<BillDeliveryDetails>();
//var deliveryData = from delivery in deliveries
// from details in deliveryDetails
// where delivery.ID == details.BillID && pids.Contains(details.ProductID)
// select new ProductQuantity
// {
// Quantity = details.Quantity,
// ProductID = details.ProductID
// };
////占用库存
//var deliveryResult = deliveryData.GroupBy(o => o.ProductID).Select(g => new ProductQuantity { ProductID = g.Key, Quantity = g.Sum(o => o.Quantity) }).ToList();
//stocks.ForEach(o =>
//{
// var dtemp = deliveryResult.Find(d => d.ProductID == o.ProductID);
// o.Quantity = o.Quantity - (dtemp == null ? 0 : dtemp.Quantity);
//});
var allocateResult = ReportDataContext.GetOccupationStock(storageID, pids);
stocks.ForEach(o =>
{
var dtemp = allocateResult.Find(d => d.ProductID == o.ProductID);
o.Quantity = o.Quantity - (dtemp == null ? 0 : dtemp.Quantity);
});
return(stocks);
}
protected override IEnumerable <ProductForStoringWhenReceiving> GetBillReceiveDetails(int billID)
{
var detailData = ReportDataContext.SearchBillDetails <BillGoodReturnDetails>(billID);
return(detailData.Select(o => new ProductForStoringWhenReceiving
{
ProductID = o.ProductID,
ProductCode = o.ProductCode,
BrandCode = o.BrandCode,
StyleCode = o.StyleCode,
ColorCode = o.ColorCode,
SizeName = o.SizeName,
Quantity = o.Quantity,
ReceiveQuantity = 0
}));
}
protected override IEnumerable <RetailAggregationEntity> SearchData()
{
var lp = VMGlobal.DistributionQuery.LinqOP;
var retailContext = lp.Search <BillRetail>(o => o.OrganizationID == VMGlobal.CurrentUser.OrganizationID);
var detailsContext = lp.GetDataContext <BillRetailDetails>();
var productContext = lp.GetDataContext <ViewProduct>();
var vipContext = lp.GetDataContext <VIPCard>();
var brandIDs = VMGlobal.PoweredBrands.Select(o => o.ID);
var data = from retail in retailContext
from details in detailsContext
where retail.ID == details.BillID
from product in productContext
join vip in vipContext on retail.VIPID equals vip.ID into vips
from v in vips.DefaultIfEmpty()//生成Left [outer] join左联接语句，就不会产生重复数据了
where product.ProductID == details.ProductID && brandIDs.Contains(product.BrandID)
select new RetailEntityForAggregation
{
OrganizationID = retail.OrganizationID,
ProductID = product.ProductID,
BrandID = product.BrandID,
StorageID = retail.StorageID,
CreateTime = retail.CreateTime.Date,
StyleCode = product.StyleCode,
Quantity = details.Quantity,
DiscountMoney = details.Price * details.Quantity * details.Discount / 100,
CutMoney = details.CutMoney,
VIPCode = v.Code,
ShiftID = retail.ShiftID,
GuideID = retail.GuideID,
//Year = product.Year,
//Quarter = product.Quarter,
BYQID = product.BYQID
};
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);
}
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);
}
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);
}
/// <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)));
}
protected override IEnumerable <ProductForStoringWhenReceiving> GetBillReceiveDetails(int billID)
{
return(ReportDataContext.GetBillDeliveryDetails <ProductForStoringWhenReceiving>(billID));
}