public object GetDetails(int page, int rows, string BillNo) { if (BillNo != "" && BillNo != null) { IQueryable <OutBillDetail> outBillDetailQuery = OutBillDetailRepository.GetQueryable(); var outBillDetail = outBillDetailQuery.Where(i => i.BillNo.Contains(BillNo)) .OrderBy(i => new { i.ProductCode, i.BillNo }).Select(i => i); int total = outBillDetail.Count(); outBillDetail = outBillDetail.Skip((page - 1) * rows).Take(rows); var temp = outBillDetail.ToArray().AsEnumerable().Select(i => new { i.ID, i.BillNo, i.ProductCode, i.Product.ProductName, i.UnitCode, i.Unit.UnitName, BillQuantity = i.BillQuantity / i.Unit.Count, AllotQuantity = i.AllotQuantity / i.Unit.Count, RealQuantity = i.RealQuantity / i.Unit.Count, i.Price, i.Description }); return(new { total, rows = temp.ToArray() }); } return(""); }
public bool Save(OutBillDetail outBillDetail, out string errorInfo) { bool result = false; errorInfo = string.Empty; var outbm = OutBillDetailRepository.GetQueryable().FirstOrDefault(i => i.BillNo == outBillDetail.BillNo && i.ProductCode == outBillDetail.ProductCode); var unit = UnitRepository.GetQueryable().FirstOrDefault(u => u.UnitCode == outBillDetail.UnitCode); var storage = StorageRepository.GetQueryable().Where(s => s.ProductCode == outBillDetail.ProductCode);// var storageQuantity = storage.Sum(s => (s.Quantity - s.OutFrozenQuantity)); if ((outbm != null && outbm.ID == outBillDetail.ID) || outbm == null) { if (outbm == null) { outbm = OutBillDetailRepository.GetQueryable().FirstOrDefault(i => i.BillNo == outBillDetail.BillNo && i.ID == outBillDetail.ID); } if (storageQuantity >= (outBillDetail.BillQuantity * unit.Count)) { outbm.BillNo = outBillDetail.BillNo; outbm.ProductCode = outBillDetail.ProductCode; outbm.UnitCode = outBillDetail.UnitCode; outbm.Price = outBillDetail.Price; outbm.BillQuantity = outBillDetail.BillQuantity * unit.Count; outbm.AllotQuantity = 0; outbm.RealQuantity = 0; outbm.Description = outBillDetail.Description; OutBillDetailRepository.SaveChanges(); result = true; } else { errorInfo = "当前库存小于您输入的数量!请从新输入!"; } } else if (outbm != null && outbm.ID != outBillDetail.ID) { string err = string.Empty; if (storageQuantity >= outbm.BillQuantity + (outBillDetail.BillQuantity * unit.Count)) { bool deltrue = this.Delete(outBillDetail.ID.ToString(), out err); outbm.BillNo = outBillDetail.BillNo; outbm.ProductCode = outBillDetail.ProductCode; outbm.UnitCode = outBillDetail.UnitCode; outbm.Price = outBillDetail.Price; outbm.BillQuantity = outbm.BillQuantity + (outBillDetail.BillQuantity * unit.Count); outbm.Description = outBillDetail.Description; OutBillDetailRepository.SaveChanges(); result = true; } else { errorInfo = "当前库存小于您输入的数量!请从新输入!"; } } return(result); }
public System.Data.DataTable GetOutBillDetail(int page, int rows, string BillNo) { System.Data.DataTable dt = new System.Data.DataTable(); if (BillNo != "" && BillNo != null) { IQueryable <OutBillDetail> outBillDetailQuery = OutBillDetailRepository.GetQueryable(); var outBillDetail = outBillDetailQuery.Where(i => i.BillNo.Contains(BillNo)) .OrderBy(i => i.BillNo).Select(i => i) .Select(i => new { i.ID, i.BillNo, i.ProductCode, i.Product.ProductName, i.UnitCode, i.Unit.UnitName, BillQuantity = i.BillQuantity / i.Unit.Count, AllotQuantity = i.AllotQuantity / i.Unit.Count, RealQuantity = i.RealQuantity / i.Unit.Count, i.Price, i.Description }); dt.Columns.Add("商品编码", typeof(string)); dt.Columns.Add("商品名称", typeof(string)); dt.Columns.Add("单位编码", typeof(string)); dt.Columns.Add("单位名称", typeof(string)); dt.Columns.Add("订单数量", typeof(decimal)); dt.Columns.Add("已分配数量", typeof(decimal)); dt.Columns.Add("实际出库量", typeof(decimal)); dt.Columns.Add("备注", typeof(string)); foreach (var o in outBillDetail) { dt.Rows.Add ( o.ProductCode , o.ProductName , o.UnitCode , o.UnitName , o.BillQuantity , o.AllotQuantity , o.RealQuantity , o.Description ); } if (outBillDetail.Count() > 0) { dt.Rows.Add( null, null, null, "总数:", outBillDetail.Sum(m => m.BillQuantity), outBillDetail.Sum(m => m.AllotQuantity), outBillDetail.Sum(m => m.RealQuantity), null); } } return(dt); }
public bool DownOutBillMaster(string beginDate, string endDate, out string errorInfo) { errorInfo = string.Empty; bool result = false; string outBillStr = ""; string outBillMasterStr = ""; try { var outBillNos = OutBillMasterRepository.GetQueryable().Where(i => i.BillNo == i.BillNo).Select(i => new { i.BillNo }).ToArray(); for (int i = 0; i < outBillNos.Length; i++) { outBillStr += outBillNos[i].BillNo + ","; } OutBillMaster[] outBillMasterList = OutBillMasterDownService.GetOutBillMaster(outBillStr); foreach (var master in outBillMasterList) { var outBillMaster = new OutBillMaster(); outBillMaster.BillNo = master.BillNo; outBillMaster.BillDate = master.BillDate; outBillMaster.BillTypeCode = master.BillTypeCode; outBillMaster.WarehouseCode = master.WarehouseCode; outBillMaster.Status = "1"; outBillMaster.IsActive = master.IsActive; outBillMaster.UpdateTime = DateTime.Now; OutBillMasterRepository.Add(outBillMaster); outBillMasterStr += master.BillNo + ","; } if (outBillMasterStr != string.Empty) { OutBillDetail[] outBillDetailList = OutBillMasterDownService.GetOutBillDetail(outBillMasterStr); foreach (var detail in outBillDetailList) { var outBillDetail = new OutBillDetail(); outBillDetail.BillNo = detail.BillNo; outBillDetail.ProductCode = detail.ProductCode; outBillDetail.UnitCode = detail.UnitCode; outBillDetail.Price = detail.Price; outBillDetail.BillQuantity = detail.BillQuantity; outBillDetail.AllotQuantity = detail.AllotQuantity; outBillDetail.RealQuantity = detail.RealQuantity; outBillDetail.Description = detail.Description; OutBillDetailRepository.Add(outBillDetail); } } OutBillMasterRepository.SaveChanges(); result = true; } catch (Exception e) { errorInfo = "出错,原因:" + e.Message; } return(result); }
public bool Delete(string ID, out string errorInfo) { errorInfo = string.Empty; IQueryable <OutBillDetail> outBillDetailQuery = OutBillDetailRepository.GetQueryable(); int id = Convert.ToInt32(ID); var outBillDetail = outBillDetailQuery.FirstOrDefault(o => o.ID == id); if (outBillDetail != null) { OutBillDetailRepository.Delete(outBillDetail); OutBillDetailRepository.SaveChanges(); } return(true); }
public bool Add(OutBillDetail outBillDetail, out string errorInfo) { errorInfo = string.Empty; bool result = false; IQueryable <OutBillDetail> outBillDetailQuery = OutBillDetailRepository.GetQueryable(); var isExistProduct = outBillDetailQuery.FirstOrDefault(i => i.BillNo == outBillDetail.BillNo && i.ProductCode == outBillDetail.ProductCode); var unit = UnitRepository.GetQueryable().FirstOrDefault(u => u.UnitCode == outBillDetail.UnitCode); var storage = StorageRepository.GetQueryable().Where(s => s.ProductCode == outBillDetail.ProductCode); var storageQuantity = storage.Sum(s => (s.Quantity - s.OutFrozenQuantity)); if (isExistProduct == null) { if (storageQuantity >= (outBillDetail.BillQuantity * unit.Count)) { var ibd = new OutBillDetail(); ibd.BillNo = outBillDetail.BillNo; ibd.ProductCode = outBillDetail.ProductCode; ibd.UnitCode = outBillDetail.UnitCode; ibd.Price = outBillDetail.Price; ibd.BillQuantity = outBillDetail.BillQuantity * unit.Count; ibd.AllotQuantity = 0; ibd.RealQuantity = 0; ibd.Description = outBillDetail.Description; OutBillDetailRepository.Add(ibd); OutBillDetailRepository.SaveChanges(); result = true; } else { errorInfo = "当前库存小于您输入的数量!请从新输入!"; } } else { if (storageQuantity >= isExistProduct.BillQuantity + (outBillDetail.BillQuantity * unit.Count)) { isExistProduct.BillQuantity = isExistProduct.BillQuantity + (outBillDetail.BillQuantity * unit.Count); isExistProduct.UnitCode = outBillDetail.UnitCode; OutBillDetailRepository.SaveChanges(); result = true; } else { errorInfo = "当前库存小于您输入的数量!请从新输入!"; } } return(result); }
public object GetDetailInfos(int page, int rows, string BillNo) { IQueryable <OutBillDetail> StockOutQuery = OutBillDetailRepository.GetQueryable(); var StockOutDetail = StockOutQuery.Where(i => i.BillNo.Contains(BillNo)).OrderBy(i => i.BillNo).AsEnumerable().Select(i => new { i.ID, i.BillNo, i.ProductCode, i.Product.ProductName, i.UnitCode, i.Unit.UnitName, i.BillQuantity, i.AllotQuantity, i.RealQuantity, i.Price, i.Description }); int total = StockOutDetail.Count(); StockOutDetail = StockOutDetail.Skip((page - 1) * rows).Take(rows); return(new { total, rows = StockOutDetail.ToArray() }); }
public DataSet Insert() { IQueryable <OutBillMaster> outBillMaster = OutBillMasterRepository.GetQueryable(); IQueryable <OutBillAllot> outBillAllot = OutBillAllotRepository.GetQueryable(); IQueryable <OutBillDetail> outBillDetail = OutBillDetailRepository.GetQueryable(); var outBillMasterQuery = outBillMaster.Where(i => i.Status == "6").Select(i => new { STORE_BILL_ID = i.BillNo, RELATE_BUSI_BILL_NUM = outBillAllot.Count(a => a.BillNo == i.BillNo), DIST_CTR_CODE = i.WarehouseCode, QUANTITY_SUM = outBillAllot.Where(a => a.BillNo == i.BillNo).Sum(a => a.AllotQuantity / 200), AMOUNT_SUM = outBillDetail.Where(d => d.BillNo == i.BillNo).Sum(d => d.Price * d.AllotQuantity / 200), DETAIL_NUM = outBillDetail.Count(d => d.BillNo == i.BillNo), personCode = i.VerifyPerson, personDate = i.VerifyDate, operater = i.OperatePerson, operateDate = i.BillDate, BILL_TYPE = i.BillTypeCode }); DataSet ds = this.GenerateEmptyTables(); DataRow inbrddr = ds.Tables["WMS_OUT_BILLMASTER"].NewRow(); foreach (var p in outBillMasterQuery) { inbrddr["STORE_BILL_ID"] = p.STORE_BILL_ID; inbrddr["RELATE_BUSI_BILL_NUM"] = p.RELATE_BUSI_BILL_NUM; inbrddr["DIST_CTR_CODE"] = p.DIST_CTR_CODE; inbrddr["AREA_TYPE"] = "0901"; inbrddr["QUANTITY_SUM"] = -p.QUANTITY_SUM; inbrddr["AMOUNT_SUM"] = p.AMOUNT_SUM; inbrddr["DETAIL_NUM"] = p.DETAIL_NUM; inbrddr["CREATOR_CODE"] = p.operater.ToString() ?? ""; inbrddr["CREATE_DATE"] = p.operateDate; inbrddr["AUDITOR_CODE"] = p.personCode.ToString() ?? ""; inbrddr["AUDIT_DATE"] = p.personDate; inbrddr["ASSIGNER_CODE"] = p.operater; inbrddr["ASSIGN_DATE"] = p.operateDate; inbrddr["AFFIRM_CODE"] = p.operater; inbrddr["AFFIRM_DATE"] = p.operateDate; inbrddr["IN_OUT_TYPE"] = "1203"; inbrddr["BILL_TYPE"] = p.BILL_TYPE; inbrddr["BILL_STATUS"] = "99"; inbrddr["DISUSE_STATUS"] = "0"; inbrddr["IS_IMPORT"] = "0"; ds.Tables["WMS_OUT_BILLMASTER"].Rows.Add(inbrddr); } DataRow inbrddrDetail = ds.Tables["WMS_OUT_BILLDETAIL"].NewRow(); var outBillDetailQuery = outBillDetail.Where(i => i.OutBillMaster.Status == "6").Select(i => new { STORE_BILL_DETAIL_ID = i.ID, STORE_BILL_ID = i.BillNo, BRAND_CODE = i.ProductCode, BRAND_NAME = i.Product.ProductName, QUANTITY = i.BillQuantity / 200 }); foreach (var p in outBillDetailQuery) { inbrddrDetail["STORE_BILL_DETAIL_ID"] = p.STORE_BILL_DETAIL_ID; inbrddrDetail["STORE_BILL_ID"] = p.STORE_BILL_ID; inbrddrDetail["BRAND_CODE"] = p.BRAND_CODE; inbrddrDetail["BRAND_NAME"] = p.BRAND_NAME; inbrddrDetail["QUANTITY"] = -p.QUANTITY; inbrddrDetail["IS_IMPORT"] = "0"; ds.Tables["WMS_OUT_BILLDETAIL"].Rows.Add(inbrddrDetail); } DataRow inbrddrAllot = ds.Tables["WMS_OUT_BILLALLOT"].NewRow(); var outBillAllotQuery = outBillAllot.Where(i => i.OutBillMaster.Status == "6").Select(i => new { BUSI_ACT_ID = i.ID, BUSI_BILL_DETAIL_ID = i.OutBillDetailId, BUSI_BILL_ID = i.BillNo, BRAND_CODE = i.ProductCode, BRAND_NAME = i.Product.ProductName, QUANTITY = i.AllotQuantity / 200, DIST_CTR_CODE = i.OutBillMaster.WarehouseCode, STORE_PLACE_CODE = i.Storage.CellCode, UPDATE_CODE = i.Operator, //BEGIN_STOCK_QUANTITY =StorageRepository.GetQueryable().Where(s=>s.ProductCode==i.ProductCode).Sum(s=>s.Quantity/200)-i.AllotQuantity, //END_STOCK_QUANTITY = i.AllotQuantity, BILL_TYPE = i.OutBillMaster.BillTypeCode }); foreach (var p in outBillAllotQuery) { inbrddrAllot["BUSI_ACT_ID"] = p.BUSI_ACT_ID; inbrddrAllot["BUSI_BILL_DETAIL_ID"] = p.BUSI_BILL_DETAIL_ID; inbrddrAllot["BUSI_BILL_ID"] = p.BUSI_BILL_ID; inbrddrAllot["BRAND_CODE"] = p.BRAND_CODE; inbrddrAllot["BRAND_NAME"] = p.BRAND_NAME; inbrddrAllot["QUANTITY"] = -p.QUANTITY; inbrddrAllot["DIST_CTR_CODE"] = p.DIST_CTR_CODE; inbrddrAllot["ORG_CODE"] = "01"; inbrddrAllot["STORE_ROOM_CODE"] = "001"; inbrddrAllot["STORE_PLACE_CODE"] = p.STORE_PLACE_CODE; inbrddrAllot["TARGET_NAME"] = p.STORE_PLACE_CODE; inbrddrAllot["IN_OUT_TYPE"] = "1203"; inbrddrAllot["BILL_TYPE"] = p.BILL_TYPE; inbrddrAllot["BEGIN_STOCK_QUANTITY"] = 0; inbrddrAllot["END_STOCK_QUANTITY"] = 0; inbrddrAllot["DISUSE_STATUS"] = "0"; inbrddrAllot["RECKON_STATUS"] = "1"; inbrddrAllot["RECKON_DATE"] = DateTime.Now.ToString("yyyy-MM-dd"); inbrddrAllot["UPDATE_CODE"] = p.UPDATE_CODE; inbrddrAllot["UPDATE_DATE"] = DateTime.Now.ToString("yyyy-MM-dd"); inbrddrAllot["IS_IMPORT"] = "0"; ds.Tables["WMS_OUT_BILLALLOT"].Rows.Add(inbrddrAllot); } return(ds); }
public System.Data.DataTable GetHistoryDetail(int page, int rows, string warehouseCode, string productCode, string beginDate, string endDate) { var inQuery = InBillDetailRepository.GetQueryable(); var outQuery = OutBillDetailRepository.GetQueryable(); var differQuery = ProfitLossBillDetailRepository.GetQueryable(); var Allquery = inQuery.Where(a => a.BillQuantity > 0 && a.RealQuantity > 0).Select(a => new { BillDate = a.InBillMaster.BillDate, a.InBillMaster.Warehouse.WarehouseCode, a.InBillMaster.Warehouse.WarehouseName, a.BillNo, a.InBillMaster.BillType.BillTypeCode, a.InBillMaster.BillType.BillTypeName, a.ProductCode, a.Product.ProductName, a.RealQuantity, a.Unit.Count, Count1 = a.Product.UnitList.Unit01.Count, //自然件单位 Count2 = a.Product.UnitList.Unit02.Count, //条单位 a.Unit.UnitName, Status = a.BillQuantity == a.RealQuantity ? "1" : "0" }).Union(outQuery.Where(a => a.BillQuantity > 0 && a.RealQuantity > 0).Select(a => new { BillDate = a.OutBillMaster.BillDate, a.OutBillMaster.Warehouse.WarehouseCode, a.OutBillMaster.Warehouse.WarehouseName, a.BillNo, a.OutBillMaster.BillType.BillTypeCode, a.OutBillMaster.BillType.BillTypeName, a.ProductCode, a.Product.ProductName, a.RealQuantity, a.Unit.Count, Count1 = a.Product.UnitList.Unit01.Count, //自然件单位 Count2 = a.Product.UnitList.Unit02.Count, //条单位 a.Unit.UnitName, Status = a.BillQuantity == a.RealQuantity ? "1" : "0" })).Union(differQuery.Where(a => a.Quantity > 0).Select(a => new { BillDate = a.ProfitLossBillMaster.BillDate, a.ProfitLossBillMaster.Warehouse.WarehouseCode, a.ProfitLossBillMaster.Warehouse.WarehouseName, a.BillNo, a.ProfitLossBillMaster.BillType.BillTypeCode, a.ProfitLossBillMaster.BillType.BillTypeName, a.ProductCode, a.Product.ProductName, RealQuantity = a.Quantity, a.Unit.Count, Count1 = a.Product.UnitList.Unit01.Count, //自然件单位 Count2 = a.Product.UnitList.Unit02.Count, //条单位 a.Unit.UnitName, Status = a.ProfitLossBillMaster.Status == "2" ? "1" : "0" })); if (!beginDate.Equals(string.Empty)) { DateTime begin = Convert.ToDateTime(beginDate); Allquery = Allquery.Where(i => i.BillDate >= begin).OrderByDescending(a => a.BillDate); } if (!endDate.Equals(string.Empty)) { DateTime end = Convert.ToDateTime(endDate); Allquery = Allquery.Where(i => i.BillDate <= end).OrderByDescending(a => a.BillDate); } Allquery = Allquery.Where(a => 1 == 1).OrderBy(a => a.WarehouseName).OrderByDescending(a => a.BillDate); var query = Allquery.Where(i => i.ProductCode.Contains(productCode) && i.WarehouseCode.Contains(warehouseCode)).ToArray().Select(i => new { BillDate = i.BillDate.ToString("yyyy-MM-dd"), i.WarehouseCode, i.WarehouseName, i.BillNo, i.BillTypeCode, i.BillTypeName, i.ProductCode, i.ProductName, RealQuantity = Convert.ToDouble(i.RealQuantity / i.Count), JQuantity = Convert.ToDouble(i.RealQuantity / i.Count1), TQuantity = Convert.ToInt32(i.RealQuantity / i.Count2), i.UnitName }); System.Data.DataTable dt = new System.Data.DataTable(); dt.Columns.Add("日期", typeof(string)); dt.Columns.Add("仓库名称", typeof(string)); dt.Columns.Add("单据编号", typeof(string)); dt.Columns.Add("单据业务", typeof(string)); dt.Columns.Add("商品代码", typeof(string)); dt.Columns.Add("商品名称", typeof(string)); dt.Columns.Add("账面数量", typeof(string)); dt.Columns.Add("数量(自然件)", typeof(string)); dt.Columns.Add("数量(条)", typeof(string)); dt.Columns.Add("单据单位", typeof(string)); foreach (var item in query) { dt.Rows.Add ( item.BillDate, item.WarehouseName, item.BillNo, item.BillTypeName, item.ProductCode, item.ProductName, item.RealQuantity, item.JQuantity, item.TQuantity, item.UnitName ); } return(dt); }
public void Dispatch(string connectionId, Model.ProgressState ps, CancellationToken cancellationToken, string workDispatchId, string userName) { Locker.LockKey = workDispatchId; ConnectionId = connectionId; ps.State = StateType.Start; NotifyConnection(ps.Clone()); IQueryable <SortOrderDispatch> sortOrderDispatchQuery = SortOrderDispatchRepository.GetQueryable(); IQueryable <SortOrder> sortOrderQuery = SortOrderRepository.GetQueryable(); IQueryable <SortOrderDetail> sortOrderDetailQuery = SortOrderDetailRepository.GetQueryable(); IQueryable <OutBillMaster> outBillMasterQuery = OutBillMasterRepository.GetQueryable(); IQueryable <OutBillDetail> outBillDetailQuery = OutBillDetailRepository.GetQueryable(); IQueryable <MoveBillMaster> moveBillMasterQuery = MoveBillMasterRepository.GetQueryable(); IQueryable <MoveBillDetail> moveBillDetailQuery = MoveBillDetailRepository.GetQueryable(); IQueryable <SortingLowerlimit> sortingLowerlimitQuery = SortingLowerlimitRepository.GetQueryable(); IQueryable <SortingLine> sortingLineQuery = SortingLineRepository.GetQueryable(); IQueryable <Storage> storageQuery = StorageRepository.GetQueryable(); IQueryable <SortWorkDispatch> sortWorkDispatchQuery = SortWorkDispatchRepository.GetQueryable(); IQueryable <THOK.Authority.DbModel.SystemParameter> systemParQuery = SystemParameterRepository.GetQueryable(); var IsUselowerlimit = systemParQuery.FirstOrDefault(s => s.ParameterName == "IsUselowerlimit"); //查询调度是否使用下限 0 否 1是 var isRoundingTray = systemParQuery.FirstOrDefault(s => s.ParameterName == "isRoundingTray"); //查询立式机总量大于20件的是否取整托盘 0表示不取整托盘。其它任意数字表示大于的总数 workDispatchId = workDispatchId.Substring(0, workDispatchId.Length - 1); int[] work = workDispatchId.Split(',').Select(s => Convert.ToInt32(s)).ToArray(); //调度表未作业的数据 var temp = sortOrderDispatchQuery.Join(sortOrderQuery, dp => new { dp.OrderDate, dp.DeliverLineCode }, om => new { om.OrderDate, om.DeliverLineCode }, (dp, om) => new { dp.ID, dp.WorkStatus, dp.OrderDate, dp.SortingLine, dp.DeliverLineCode, om.OrderID } ).Join(sortOrderDetailQuery, dm => new { dm.OrderID }, od => new { od.OrderID }, (dm, od) => new { dm.ID, dm.WorkStatus, dm.OrderDate, dm.SortingLine, od.Product, od.UnitCode, od.Price, od.RealQuantity } ).WhereIn(s => s.ID, work) .Where(s => s.WorkStatus == "1") .GroupBy(r => new { r.OrderDate, r.SortingLine, r.Product, r.UnitCode, r.Price }) .Select(r => new { r.Key.OrderDate, r.Key.SortingLine, r.Key.Product, r.Key.UnitCode, r.Key.Price, SumQuantity = r.Sum(p => p.RealQuantity * r.Key.Product.UnitList.Unit02.Count) }) .AsParallel() .GroupBy(r => new { r.OrderDate, r.SortingLine }) .Select(r => new { r.Key.OrderDate, r.Key.SortingLine, Products = r }) .OrderBy(s => s.SortingLine.SortingLineCode) //如果取整托盘多余的量是1号线就倒序排序,目前多余的量放入2号线,所以先调度一号线 .ToArray(); var temp1 = sortingLowerlimitQuery.GroupBy(r => new { r.Product, r.SortType }) .Select(s => new { s.Key.Product, s.Key.SortType }).ToArray(); var temp2 = sortOrderDispatchQuery.Join(sortOrderQuery, dp => new { dp.OrderDate, dp.DeliverLineCode }, om => new { om.OrderDate, om.DeliverLineCode }, (dp, om) => new { dp.ID, dp.WorkStatus, dp.OrderDate, om.OrderID } ).Join(sortOrderDetailQuery, dm => new { dm.OrderID }, od => new { od.OrderID }, (dm, od) => new { dm.ID, dm.WorkStatus, od.Product, od.RealQuantity } ).WhereIn(s => s.ID, work) .GroupBy(r => new { r.Product }) .Select(s => new { s.Key.Product, Quantity = s.Sum(p => p.RealQuantity * s.Key.Product.UnitList.Unit02.Count) }) .ToArray(); Dictionary <string, decimal> proQuan = new Dictionary <string, decimal>(); var employee = EmployeeRepository.GetQueryable().FirstOrDefault(i => i.UserName == userName); string operatePersonID = employee != null?employee.ID.ToString() : ""; if (employee == null) { ps.State = StateType.Error; ps.Errors.Add("未找到当前用户,或当前用户不可用!"); NotifyConnection(ps.Clone()); return; } decimal sumAllotQuantity = 0; decimal sumAllotLineQuantity = 0; MoveBillMaster lastMoveBillMaster = null; foreach (var item in temp) { try { if (cancellationToken.IsCancellationRequested) { return; } bool hasError = false; ps.State = StateType.Info; ps.Messages.Add("开始调度" + item.SortingLine.SortingLineName); NotifyConnection(ps.Clone()); //using (var scope = new TransactionScope()) //{ if (item.Products.Count() > 0) { if (cancellationToken.IsCancellationRequested) { return; } if (lastMoveBillMaster != null && lastMoveBillMaster.WarehouseCode != item.SortingLine.Cell.WarehouseCode) { if (MoveBillCreater.CheckIsNeedSyncMoveBill(lastMoveBillMaster.WarehouseCode)) { MoveBillCreater.CreateSyncMoveBillDetail(lastMoveBillMaster); } } sumAllotLineQuantity = 0; if (cancellationToken.IsCancellationRequested) { return; } MoveBillMaster moveBillMaster = MoveBillCreater.CreateMoveBillMaster(item.SortingLine.Cell.WarehouseCode, item.SortingLine.MoveBillTypeCode, operatePersonID); moveBillMaster.Origin = "2"; moveBillMaster.Description = item.SortingLine.SortingLineCode + " 分拣调度生成!"; lastMoveBillMaster = moveBillMaster; foreach (var product in item.Products.ToArray()) { if (product.SumQuantity > 0) { if (cancellationToken.IsCancellationRequested) { return; } decimal sumBillQuantity = temp.Sum(t => t.Products.Sum(p => p.SumQuantity)); sumAllotQuantity += product.SumQuantity; decimal sumBillProductQuantity = item.Products.Sum(p => p.SumQuantity); sumAllotLineQuantity += product.SumQuantity; ps.State = StateType.Processing; ps.TotalProgressName = "分拣作业调度"; ps.TotalProgressValue = (int)(sumAllotQuantity / sumBillQuantity * 100); ps.CurrentProgressName = "正在调度:" + item.SortingLine.SortingLineName; ps.CurrentProgressValue = (int)(sumAllotLineQuantity / sumBillProductQuantity * 100); NotifyConnection(ps.Clone()); if (cancellationToken.IsCancellationRequested) { return; } //获取分拣线下限数据 var sortingLowerlimitQuantity = sortingLowerlimitQuery.Where(s => s.ProductCode == product.Product.ProductCode && s.SortingLineCode == product.SortingLine.SortingLineCode); decimal lowerlimitQuantity = 0; if (sortingLowerlimitQuantity.Count() > 0) { lowerlimitQuantity = sortingLowerlimitQuantity.Sum(s => s.Quantity); } if (cancellationToken.IsCancellationRequested) { return; } //获取分拣备货区库存 var storageQuantity = storageQuery.Where(s => s.ProductCode == product.Product.ProductCode) .Join(sortingLineQuery, s => s.Cell, l => l.Cell, (s, l) => new { l.SortingLineCode, s.Quantity } ) .Where(r => r.SortingLineCode == product.SortingLine.SortingLineCode); decimal storQuantity = 0; if (storageQuantity.Count() > 0) { storQuantity = storageQuantity.Sum(s => s.Quantity); } //获取当前这个卷烟库存数量 string[] areaTypes = new string[] { "1", "2", "4" }; var areaSumQuantitys = storageQuery.Where(s => areaTypes.Any(t => t == s.Cell.Area.AreaType) && s.ProductCode == product.Product.ProductCode).ToArray(); decimal areaQuantiy = 0; if (areaSumQuantitys.Count() > 0) { areaQuantiy = areaSumQuantitys.Sum(s => s.Quantity - s.OutFrozenQuantity); } //是否使用下限 if (IsUselowerlimit != null && IsUselowerlimit.ParameterValue == "0") { lowerlimitQuantity = 0; } if (cancellationToken.IsCancellationRequested) { return; } //获取移库量(按整件计)出库量加上下限量减去备货区库存量取整 decimal quantity = 0; quantity = Math.Ceiling((product.SumQuantity + lowerlimitQuantity - storQuantity) / product.Product.Unit.Count) * product.Product.Unit.Count; //立式机大于20件的取整托盘 //查询这个卷烟是否是立式机的卷烟 if (isRoundingTray != null && Convert.ToInt32(isRoundingTray.ParameterValue) > 0) { var temp3 = temp1.FirstOrDefault(s => s.Product.ProductCode == product.Product.ProductCode && s.SortType == "1"); if (temp3 != null && temp.Count() >= 2 && quantity > 0) { //查询这个订单在分拣当中是否存在.大于20件取整托盘, var SumlowerlimitQuantity = temp2.FirstOrDefault(s => s.Product.ProductCode == temp3.Product.ProductCode); if (SumlowerlimitQuantity != null && SumlowerlimitQuantity.Quantity > (Convert.ToInt32(isRoundingTray.ParameterValue) * product.Product.Unit.Count)) { decimal WholeCare = 0; //托盘数 decimal SumSortingQuantity = 0; //整托盘的数量 decimal Quantity1 = 0; decimal Quantity2 = 0; //取2条线数量总和取整托盘 WholeCare = Math.Ceiling(SumlowerlimitQuantity.Quantity / (product.Product.CellMaxProductQuantity * product.Product.Unit.Count)); SumSortingQuantity = Convert.ToDecimal(WholeCare * (product.Product.Unit.Count * product.Product.CellMaxProductQuantity)); if (item.SortingLine.SortingLineCode == "1") { //总订单量减去当前分拣线订单量,这里是另一条线的量 Quantity1 = SumlowerlimitQuantity.Quantity - product.SumQuantity; if (Quantity1 > 0) { //整托盘数量减去另一条线的量, Quantity1 = Math.Ceiling(Quantity1 / product.Product.Unit.Count) * product.Product.Unit.Count; Quantity2 = SumSortingQuantity - Quantity1; if (Quantity2 >= quantity) { quantity = Quantity2; proQuan.Add(product.Product.ProductCode, SumSortingQuantity - quantity); } } } else { if (proQuan.Keys.Contains(product.Product.ProductCode)) { if (proQuan[product.Product.ProductCode] >= quantity) { quantity = proQuan[product.Product.ProductCode]; } } } } } } //取整托盘,查询这个卷烟是否是通道机的卷烟 decimal wholeTray = 0; var temp4 = temp1.FirstOrDefault(s => s.Product.ProductCode == product.Product.ProductCode && s.SortType == "2"); if (product.Product.IsRounding == "2" || temp4 != null) { wholeTray = Math.Ceiling(quantity / (product.Product.CellMaxProductQuantity * product.Product.Unit.Count)); quantity = Convert.ToDecimal(wholeTray * (product.Product.Unit.Count * product.Product.CellMaxProductQuantity)); } if (areaQuantiy < quantity) //判断当前这个卷烟库存是否小于移库量 { //出库量减去备货区库存量取整 quantity = Math.Ceiling((product.SumQuantity - storQuantity) / product.Product.Unit.Count) * product.Product.Unit.Count; if (areaQuantiy < quantity) { //出库量减去备货区库存量 quantity = product.SumQuantity - storQuantity; } } //不取整的烟直接出库。 if (product.Product.IsRounding == "1") { quantity = product.SumQuantity - storQuantity; } if (quantity > 0) { if (cancellationToken.IsCancellationRequested) { return; } AlltoMoveBill(moveBillMaster, product.Product, item.SortingLine.Cell, ref quantity, cancellationToken, ps, item.SortingLine.Cell.CellCode); } if (quantity > 0) { //生成移库不完整,可能是库存不足; hasError = true; ps.State = StateType.Error; ps.Errors.Add(item.SortingLine.SortingLineCode + "线," + product.Product.ProductCode + " " + product.Product.ProductName + ",库存不足!当前总量:" + Convert.ToDecimal(product.SumQuantity / product.Product.UnitList.Unit02.Count) + "(条),缺少:" + Convert.ToDecimal(quantity / product.Product.UnitList.Unit02.Count) + "(条)"); NotifyConnection(ps.Clone()); } } } if (!hasError) { if (cancellationToken.IsCancellationRequested) { return; } OutBillMaster outBillMaster = OutBillCreater.CreateOutBillMaster(item.SortingLine.Cell.WarehouseCode, item.SortingLine.OutBillTypeCode, operatePersonID); outBillMaster.Origin = "2"; outBillMaster.Description = item.SortingLine.SortingLineCode + " 分拣调度生成!"; //添加出库单细单 foreach (var product in item.Products.ToArray()) { if (cancellationToken.IsCancellationRequested) { return; } OutBillCreater.AddToOutBillDetail(outBillMaster, product.Product, product.Price, product.SumQuantity); } if (cancellationToken.IsCancellationRequested) { return; } //添加出库、移库主单和作业调度表 SortWorkDispatch sortWorkDisp = AddSortWorkDispMaster(moveBillMaster, outBillMaster, item.SortingLine.SortingLineCode, item.OrderDate); //修改线路调度作业状态和作业ID var sortDispTemp = sortOrderDispatchQuery.WhereIn(s => s.ID, work) .Where(s => s.OrderDate == item.OrderDate && s.SortingLineCode == item.SortingLine.SortingLineCode); foreach (var sortDisp in sortDispTemp.ToArray()) { if (cancellationToken.IsCancellationRequested) { return; } sortDisp.SortWorkDispatchID = sortWorkDisp.ID; sortDisp.WorkStatus = "2"; } if (cancellationToken.IsCancellationRequested) { return; } SortWorkDispatchRepository.SaveChanges(); //scope.Complete(); ps.Messages.Add(item.SortingLine.SortingLineName + " 调度成功!"); } else { ps.State = StateType.Info; ps.Messages.Add(item.SortingLine.SortingLineName + " 调度失败!"); NotifyConnection(ps.Clone()); return; } } //} } catch (Exception e) { ps.State = StateType.Info; ps.Errors.Add(item.SortingLine.SortingLineName + "作业调度失败! 详情:" + e.Message); NotifyConnection(ps.Clone()); return; } } if (cancellationToken.IsCancellationRequested) { return; } if (MoveBillCreater.CheckIsNeedSyncMoveBill(lastMoveBillMaster.WarehouseCode)) { MoveBillCreater.CreateSyncMoveBillDetail(lastMoveBillMaster); } MoveBillMasterRepository.SaveChanges(); ps.State = StateType.Info; ps.Messages.Add("调度完成!"); NotifyConnection(ps.Clone()); }
public bool Add(DateTime datetime, out string strResult) { bool result = false; strResult = string.Empty; var outBillMaster = OutBillMasterRepository.GetQueryable().Where(i => i.BillDate <= datetime); var outBillDetail = OutBillDetailRepository.GetQueryable().Where(i => i.OutBillMaster.BillDate <= datetime); var outBillAllot = OutBillAllotRepository.GetQueryable().Where(i => i.OutBillMaster.BillDate <= datetime); if (outBillMaster.Any()) { #region 主表移入历史表 try { foreach (var item in outBillMaster.ToArray()) { OutBillMasterHistory history = new OutBillMasterHistory(); history.BillNo = item.BillNo; history.BillDate = item.BillDate; history.BillTypeCode = item.BillTypeCode; history.WarehouseCode = item.WarehouseCode; history.OperatePersonID = item.OperatePersonID; history.Status = item.Status; history.VerifyPersonID = item.VerifyPersonID; history.VerifyDate = item.VerifyDate; history.Description = item.Description; history.IsActive = item.IsActive; history.UpdateTime = item.UpdateTime; history.Origin = item.Origin; history.TargetCellCode = item.TargetCellCode; OutBillMasterHistoryRepository.Add(history); } result = true; } catch (Exception e) { strResult = "主库单:" + e.InnerException.ToString(); result = false; } #endregion if (outBillDetail.Any()) { #region 细表移入历史表 try { foreach (var item in outBillDetail.ToArray()) { OutBillDetailHistory history = new OutBillDetailHistory(); history.ID = item.ID; history.BillNo = item.BillNo; history.ProductCode = item.ProductCode; history.UnitCode = item.UnitCode; history.Price = item.Price; history.BillQuantity = item.BillQuantity; history.AllotQuantity = item.AllotQuantity; history.RealQuantity = item.RealQuantity; history.Description = item.Description; OutBillDetailHistoryRepository.Add(history); } result = true; } catch (Exception e) { strResult = "细库单" + e.InnerException.ToString(); return(false); } #endregion if (outBillAllot.Any()) { #region 分配表移入历史表 try { foreach (var item3 in outBillAllot.ToArray()) { OutBillAllotHistory history3 = new OutBillAllotHistory(); history3.BillNo = item3.BillNo; history3.ProductCode = item3.ProductCode; history3.OutBillDetailId = item3.OutBillDetailId; history3.CellCode = item3.CellCode; history3.StorageCode = item3.StorageCode; history3.UnitCode = item3.UnitCode; history3.AllotQuantity = item3.AllotQuantity; history3.RealQuantity = item3.RealQuantity; history3.OperatePersonID = item3.OperatePersonID; history3.Operator = item3.Operator; history3.StartTime = item3.StartTime; history3.FinishTime = item3.FinishTime; history3.Status = item3.Status; OutBillAllotHistoryRepository.Add(history3); } result = true; } catch (Exception e) { strResult = "分配单:" + e.InnerException.ToString(); result = false; } #endregion } } if (result == true) { #region 除主细分配表 try { foreach (var item in outBillMaster.ToList()) { Del(OutBillAllotRepository, item.OutBillAllots); Del(OutBillDetailRepository, item.OutBillDetails); OutBillMasterRepository.Delete(item); result = true; } } catch (Exception e) { strResult = "删除情况:" + e.InnerException.ToString(); } OutBillMasterHistoryRepository.SaveChanges(); #endregion } } else { strResult = "数据不存在!"; } return(result); }
public object GetInfoDetails(int page, int rows, string warehouseCode, string productCode, string settleDate) { var inQuery = InBillDetailRepository.GetQueryable(); var outQuery = OutBillDetailRepository.GetQueryable(); var differQuery = ProfitLossBillDetailRepository.GetQueryable(); var Allquery = inQuery.Where(a => a.BillQuantity > 0 && a.RealQuantity > 0).Select(a => new { BillDate = a.InBillMaster.BillDate, a.InBillMaster.Warehouse.WarehouseCode, a.InBillMaster.Warehouse.WarehouseName, a.BillNo, a.InBillMaster.BillType.BillTypeCode, a.InBillMaster.BillType.BillTypeName, a.ProductCode, a.Product.ProductName, a.RealQuantity, a.Unit.Count, Count1 = a.Product.UnitList.Unit01.Count, //自然件单位 Count2 = a.Product.UnitList.Unit02.Count, //条单位 a.Unit.UnitName, Status = a.BillQuantity == a.RealQuantity ? "1" : "0" }).Union(outQuery.Where(a => a.BillQuantity > 0 && a.RealQuantity > 0).Select(a => new { BillDate = a.OutBillMaster.BillDate, a.OutBillMaster.Warehouse.WarehouseCode, a.OutBillMaster.Warehouse.WarehouseName, a.BillNo, a.OutBillMaster.BillType.BillTypeCode, a.OutBillMaster.BillType.BillTypeName, a.ProductCode, a.Product.ProductName, a.RealQuantity, a.Unit.Count, Count1 = a.Product.UnitList.Unit01.Count, //自然件单位 Count2 = a.Product.UnitList.Unit02.Count, //条单位 a.Unit.UnitName, Status = a.BillQuantity == a.RealQuantity ? "1" : "0" })).Union(differQuery.Where(a => a.Quantity > 0).Select(a => new { BillDate = a.ProfitLossBillMaster.BillDate, a.ProfitLossBillMaster.Warehouse.WarehouseCode, a.ProfitLossBillMaster.Warehouse.WarehouseName, a.BillNo, a.ProfitLossBillMaster.BillType.BillTypeCode, a.ProfitLossBillMaster.BillType.BillTypeName, a.ProductCode, a.Product.ProductName, RealQuantity = a.Quantity, a.Unit.Count, Count1 = a.Product.UnitList.Unit01.Count, //自然件单位 Count2 = a.Product.UnitList.Unit02.Count, //条单位 a.Unit.UnitName, Status = a.ProfitLossBillMaster.Status == "2" ? "1" : "0" })); if (!settleDate.Equals(string.Empty)) { DateTime date = Convert.ToDateTime(settleDate); Allquery = Allquery.Where(i => i.BillDate.Year == date.Year && i.BillDate.Month == date.Month && i.BillDate.Day == date.Day); } Allquery = Allquery.Where(i => i.ProductCode.Contains(productCode) && i.WarehouseCode.Contains(warehouseCode)).OrderBy(a => a.BillDate).ThenBy(a => a.WarehouseName); int total = Allquery.Count(); Allquery = Allquery.Skip((page - 1) * rows).Take(rows); var query = Allquery.ToArray().Select(i => new { BillDate = i.BillDate.ToString("yyyy-MM-dd"), i.WarehouseCode, i.WarehouseName, i.BillNo, i.BillTypeCode, i.BillTypeName, i.ProductCode, i.ProductName, RealQuantity = Convert.ToDouble(i.RealQuantity / i.Count), JQuantity = Convert.ToDouble(i.RealQuantity / i.Count1), TQuantity = Convert.ToInt32(i.RealQuantity / i.Count2), i.UnitName, i.Status }); return(new { total, rows = query.ToArray() }); }
public bool AllotAdd(string billNo, long id, string cellCode, string productName, out string strResult, out decimal allotQuantity) { bool result = false; var ibm = OutBillMasterRepository.GetQueryable().FirstOrDefault(i => i.BillNo == billNo); var cell = CellRepository.GetQueryable().Single(c => c.CellCode == cellCode); var obm = OutBillDetailRepository.GetQueryable().FirstOrDefault(o => o.ID == id); var stor = StorageRepository.GetQueryable().Single(c => c.CellCode == cellCode); decimal q1 = obm.BillQuantity - obm.AllotQuantity; allotQuantity = 0; if (ibm != null) { if (string.IsNullOrEmpty(ibm.LockTag)) { Storage storage = Locker.LockNoEmpty(cell, obm.Product); if (storage != null) { if (stor.Product.ProductName == productName) { if (q1 > 0) { OutBillAllot billAllot = null; decimal q3 = storage.Quantity - storage.OutFrozenQuantity; if (q1 <= q3) { try { billAllot = new OutBillAllot() { BillNo = billNo, OutBillDetailId = obm.ID, ProductCode = obm.ProductCode, CellCode = storage.CellCode, StorageCode = storage.StorageCode, UnitCode = obm.UnitCode, AllotQuantity = q1, RealQuantity = 0, Status = "0" }; allotQuantity = (q3 - q1) / storage.Product.Unit.Count; obm.AllotQuantity += q1; storage.OutFrozenQuantity += q1; ibm.OutBillAllots.Add(billAllot); ibm.Status = "3"; storage.LockTag = string.Empty; StorageRepository.SaveChanges(); strResult = "保存修改成功!"; result = true; } catch (Exception) { strResult = "保存添加失败,订单或储位其他人正在操作!"; } } else { try { billAllot = new OutBillAllot() { BillNo = billNo, OutBillDetailId = obm.ID, ProductCode = obm.ProductCode, CellCode = storage.CellCode, StorageCode = storage.StorageCode, UnitCode = obm.UnitCode, AllotQuantity = q3, RealQuantity = 0, Status = "0" }; obm.AllotQuantity += q3; storage.OutFrozenQuantity += q3; ibm.OutBillAllots.Add(billAllot); ibm.Status = "3"; storage.LockTag = string.Empty; StorageRepository.SaveChanges(); strResult = "保存修改成功!"; result = true; } catch (Exception) { strResult = "保存添加失败,订单或储位其他人正在操作!"; } } } else { strResult = "该产品已无分配任务!"; } } else { strResult = "该卷烟没有出库任务!"; } } else { strResult = "当前选择的储位不可用,其他人正在操作或没有库存!"; } } else { strResult = "当前订单其他人正在操作,请稍候重试!"; } } else { strResult = "当前订单状态不是已分配,或当前订单不存在!"; } return(result); }
public bool AllotAdd(string billNo, long id, string productCode, string cellCode, int allotQuantity, out string strResult) { bool result = false; var ibm = OutBillMasterRepository.GetQueryable().FirstOrDefault(i => i.BillNo == billNo); var cell = CellRepository.GetQueryable().Single(c => c.CellCode == cellCode); var obm = OutBillDetailRepository.GetQueryable().FirstOrDefault(o => o.ProductCode == productCode); if (ibm != null) { if (string.IsNullOrEmpty(ibm.LockTag)) { Storage storage = Locker.LockNoEmpty(cell, obm.Product); if (storage != null && allotQuantity > 0) { OutBillAllot billAllot = null; decimal q1 = obm.BillQuantity - obm.AllotQuantity; decimal q2 = allotQuantity * obm.Unit.Count; decimal q3 = storage.Quantity - storage.OutFrozenQuantity; if (q1 >= q2 && q2 <= q3) { try { billAllot = new OutBillAllot() { BillNo = billNo, OutBillDetailId = obm.ID, ProductCode = obm.ProductCode, CellCode = storage.CellCode, StorageCode = storage.StorageCode, UnitCode = obm.UnitCode, AllotQuantity = q2, RealQuantity = 0, Status = "0" }; obm.AllotQuantity += q2; storage.OutFrozenQuantity += q2; ibm.OutBillAllots.Add(billAllot); ibm.Status = "3"; storage.LockTag = string.Empty; StorageRepository.SaveChanges(); strResult = "保存修改成功!"; result = true; } catch (Exception) { strResult = "保存添加失败,订单或储位其他人正在操作!"; } } else { strResult = "分配数量超过订单数量,或者当前储位库存量不足!"; } } else { strResult = "当前选择的储位不可用,其他人正在操作或没有库存!"; } } else { strResult = "当前订单其他人正在操作,请稍候重试!"; } } else { strResult = "当前订单状态不是已分配,或当前订单不存在!"; } return(result); }
public Boolean DoDailyBalance(string warehouseCode, string settleDate, ref string errorInfo) { try { using (var scope = new TransactionScope()) { var inQuery = InBillDetailRepository.GetQueryable().AsEnumerable(); var outQuery = OutBillDetailRepository.GetQueryable().AsEnumerable(); var profitLossQuery = ProfitLossBillDetailRepository.GetQueryable().AsEnumerable(); var dailyBalanceQuery = DailyBalanceRepository.GetQueryable().AsEnumerable(); DateTime dt1 = Convert.ToDateTime(settleDate); if (DateTime.Now < dt1) { errorInfo = "选择日结日期大于当前日期,不可以进行日结!"; return(false); } var dailyBalance = dailyBalanceQuery.Where(d => d.SettleDate < dt1) .OrderByDescending(d => d.SettleDate) .FirstOrDefault(); string t = dailyBalance != null?dailyBalance.SettleDate.ToString("yyyy-MM-dd") : ""; var oldDailyBalance = dailyBalanceQuery.Where(d => (d.WarehouseCode == warehouseCode || string.IsNullOrEmpty(warehouseCode)) && d.SettleDate.ToString("yyyy-MM-dd") == settleDate) .ToArray(); DailyBalanceRepository.Delete(oldDailyBalance); DailyBalanceRepository.SaveChanges(); var query = inQuery.Where(a => (a.InBillMaster.WarehouseCode == warehouseCode || string.IsNullOrEmpty(warehouseCode)) && a.InBillMaster.BillDate.ToString("yyyy-MM-dd") == settleDate ).Select(a => new { BillDate = a.InBillMaster.BillDate.ToString("yyyy-MM-dd"), WarehouseCode = a.InBillMaster.Warehouse.WarehouseCode, ProductCode = a.ProductCode, UnitCode = a.Product.UnitCode, Beginning = decimal.Zero, EntryAmount = a.RealQuantity, DeliveryAmount = decimal.Zero, ProfitAmount = decimal.Zero, LossAmount = decimal.Zero, Ending = decimal.Zero }).Union(outQuery.Where(a => (a.OutBillMaster.WarehouseCode == warehouseCode || string.IsNullOrEmpty(warehouseCode)) && a.OutBillMaster.BillDate.ToString("yyyy-MM-dd") == settleDate ).Select(a => new { BillDate = a.OutBillMaster.BillDate.ToString("yyyy-MM-dd"), WarehouseCode = a.OutBillMaster.Warehouse.WarehouseCode, ProductCode = a.ProductCode, UnitCode = a.Product.UnitCode, Beginning = decimal.Zero, EntryAmount = decimal.Zero, DeliveryAmount = a.RealQuantity, ProfitAmount = decimal.Zero, LossAmount = decimal.Zero, Ending = decimal.Zero })).Union(profitLossQuery.Where(a => (a.ProfitLossBillMaster.WarehouseCode == warehouseCode || string.IsNullOrEmpty(warehouseCode)) && a.ProfitLossBillMaster.BillDate.ToString("yyyy-MM-dd") == settleDate ).Select(a => new { BillDate = a.ProfitLossBillMaster.BillDate.ToString("yyyy-MM-dd"), WarehouseCode = a.ProfitLossBillMaster.Warehouse.WarehouseCode, ProductCode = a.ProductCode, UnitCode = a.Product.UnitCode, Beginning = decimal.Zero, EntryAmount = decimal.Zero, DeliveryAmount = decimal.Zero, ProfitAmount = a.Quantity > 0 ? Math.Abs(a.Quantity) : decimal.Zero, LossAmount = a.Quantity < 0 ? Math.Abs(a.Quantity) : decimal.Zero, Ending = decimal.Zero })).Union(dailyBalanceQuery.Where(d => (d.WarehouseCode == warehouseCode || string.IsNullOrEmpty(warehouseCode)) && d.SettleDate.ToString("yyyy-MM-dd") == t && d.Ending > decimal.Zero ).Select(a => new { BillDate = settleDate, WarehouseCode = a.WarehouseCode, ProductCode = a.ProductCode, UnitCode = a.Product.UnitCode, Beginning = a.Ending, EntryAmount = decimal.Zero, DeliveryAmount = decimal.Zero, ProfitAmount = decimal.Zero, LossAmount = decimal.Zero, Ending = decimal.Zero } )); var newDailyBalance = query.GroupBy(a => new { a.BillDate, a.WarehouseCode, a.ProductCode, a.UnitCode }) .Select(a => new DailyBalance { SettleDate = Convert.ToDateTime(a.Key.BillDate), WarehouseCode = a.Key.WarehouseCode, ProductCode = a.Key.ProductCode, UnitCode = a.Key.UnitCode, Beginning = a.Sum(d => d.Beginning), EntryAmount = a.Sum(d => d.EntryAmount), DeliveryAmount = a.Sum(d => d.DeliveryAmount), ProfitAmount = a.Sum(d => d.ProfitAmount), LossAmount = a.Sum(d => d.LossAmount), Ending = a.Sum(d => d.Beginning) + a.Sum(d => d.EntryAmount) - a.Sum(d => d.DeliveryAmount) + a.Sum(d => d.ProfitAmount) - a.Sum(d => d.LossAmount), }).ToArray(); newDailyBalance.AsParallel().ForAll(b => b.ID = Guid.NewGuid()); foreach (var item in newDailyBalance) { item.ID = Guid.NewGuid(); DailyBalanceRepository.Add(item); } DailyBalanceRepository.SaveChanges(); scope.Complete(); } return(true); } catch (Exception e) { errorInfo = "日结时出现错误,详情:" + e.Message; return(false); } }
/// <summary> /// 出库分配 /// </summary> /// <param name="outBillMaster">出库主单</param> /// <returns></returns> public bool OutAllot(OutBillMaster outBillMaster, Guid employeeId) { try { bool result = false; //出库单出库 var storages = StorageRepository.GetQueryable().Where(s => s.CellCode == outBillMaster.TargetCellCode && s.Quantity - s.OutFrozenQuantity > 0).ToArray(); if (!Locker.Lock(storages)) { throw new Exception("锁定储位失败,储位其他人正在操作,无法取消分配请稍候重试!"); } var outDetails = OutBillDetailRepository.GetQueryableIncludeProduct() .Where(o => o.BillNo == outBillMaster.BillNo); outDetails.ToArray().AsParallel().ForAll( (Action <OutBillDetail>) delegate(OutBillDetail o) { var ss = storages.Where(s => s.ProductCode == o.ProductCode).ToArray(); foreach (var s in ss) { lock (s) { if (o.BillQuantity - o.AllotQuantity > 0) { decimal allotQuantity = s.Quantity; decimal billQuantity = o.BillQuantity - o.AllotQuantity; allotQuantity = allotQuantity < billQuantity ? allotQuantity : billQuantity; o.AllotQuantity += allotQuantity; o.RealQuantity += allotQuantity; s.Quantity -= allotQuantity; var billAllot = new OutBillAllot() { BillNo = outBillMaster.BillNo, OutBillDetailId = o.ID, ProductCode = o.ProductCode, CellCode = s.CellCode, StorageCode = s.StorageCode, UnitCode = o.UnitCode, AllotQuantity = allotQuantity, RealQuantity = allotQuantity, Status = "2" }; lock (outBillMaster.OutBillAllots) { outBillMaster.OutBillAllots.Add(billAllot); } } else { break; } } } if (o.BillQuantity - o.AllotQuantity > 0) { throw new Exception(o.ProductCode + " " + o.Product.ProductName + "库存不足,未能结单!"); } }); result = true; storages.AsParallel().ForAll(s => s.LockTag = string.Empty); //出库结单 outBillMaster.Status = "6"; outBillMaster.VerifyDate = DateTime.Now; outBillMaster.VerifyPersonID = employeeId; outBillMaster.UpdateTime = DateTime.Now; OutBillMasterRepository.SaveChanges(); return(result); } catch (AggregateException ex) { infoStr = "审核失败,详情:" + ex.InnerExceptions.Select(i => i.Message).Aggregate((m, n) => m + n); return(false); } }
public bool Settle(string id, ref string errorInfo) { try { Guid ID = new Guid(id); var sortWork = SortWorkDispatchRepository.GetQueryable().FirstOrDefault(s => s.ID == ID); if (sortWork == null) { errorInfo = "当前选择的调度记录不存在,未能结单!"; return(false); } if (sortWork.DispatchStatus == "1") { errorInfo = "当前选择的调度记录不是执行中,未能结单!"; return(false); } if (sortWork.MoveBillMaster.Status == "1") { errorInfo = "当前选择的调度记录移库单不是执行中,未能结单!"; return(false); } using (var scope = new TransactionScope()) { //移库细单解锁冻结量 var moveDetail = MoveBillDetailRepository.GetQueryable() .Where(m => m.BillNo == sortWork.MoveBillNo && m.Status != "2"); if (moveDetail.Any()) { var sourceStorages = moveDetail.Select(m => m.OutStorage).ToArray(); var targetStorages = moveDetail.Select(m => m.InStorage).ToArray(); if (!Locker.Lock(sourceStorages) || !Locker.Lock(targetStorages)) { errorInfo = "锁定储位失败,储位其他人正在操作,无法取消分配请稍候重试!"; return(false); } moveDetail.AsParallel().ForAll( (Action <MoveBillDetail>) delegate(MoveBillDetail m) { if (m.InStorage.ProductCode == m.ProductCode && m.OutStorage.ProductCode == m.ProductCode && m.InStorage.InFrozenQuantity >= m.RealQuantity && m.OutStorage.OutFrozenQuantity >= m.RealQuantity) { m.InStorage.InFrozenQuantity -= m.RealQuantity; m.OutStorage.OutFrozenQuantity -= m.RealQuantity; m.InStorage.LockTag = string.Empty; m.OutStorage.LockTag = string.Empty; } else { throw new Exception("储位的卷烟或移库冻结量与当前分配不符,信息可能被异常修改,不能结单!"); } } ); //解锁分拣线路调度的状态,以便重新做作业调度; foreach (var sortDisp in sortWork.SortOrderDispatchs) { sortDisp.SortWorkDispatchID = null; sortDisp.WorkStatus = "1"; } } else { //出库单作自动出库 var storages = StorageRepository.GetQueryable().Where(s => s.CellCode == sortWork.SortingLine.CellCode && s.Quantity - s.OutFrozenQuantity > 0).ToArray(); if (!Locker.Lock(storages)) { errorInfo = "锁定储位失败,储位其他人正在操作,无法取消分配请稍候重试!"; return(false); } var outAllots = sortWork.OutBillMaster.OutBillAllots; var outDetails = OutBillDetailRepository.GetQueryableIncludeProduct() .Where(o => o.BillNo == sortWork.OutBillMaster.BillNo); outDetails.ToArray().AsParallel().ForAll( (Action <OutBillDetail>) delegate(OutBillDetail o) { var ss = storages.Where(s => s.ProductCode == o.ProductCode).ToArray(); foreach (var s in ss) { lock (s) { if (o.BillQuantity - o.AllotQuantity > 0) { decimal allotQuantity = s.Quantity; decimal billQuantity = o.BillQuantity - o.AllotQuantity; allotQuantity = allotQuantity < billQuantity ? allotQuantity : billQuantity; o.AllotQuantity += allotQuantity; o.RealQuantity += allotQuantity; s.Quantity -= allotQuantity; var billAllot = new OutBillAllot() { BillNo = sortWork.OutBillMaster.BillNo, OutBillDetailId = o.ID, ProductCode = o.ProductCode, CellCode = s.CellCode, StorageCode = s.StorageCode, UnitCode = o.UnitCode, AllotQuantity = allotQuantity, RealQuantity = allotQuantity, FinishTime = DateTime.Now, Status = "2" }; lock (sortWork.OutBillMaster.OutBillAllots) { sortWork.OutBillMaster.OutBillAllots.Add(billAllot); } } else { break; } } } if (o.BillQuantity - o.AllotQuantity > 0) { throw new Exception(sortWork.SortingLine.SortingLineName + " " + o.ProductCode + " " + o.Product.ProductName + "分拣备货区库存不足,未能结单!"); } } ); storages.AsParallel().ForAll(s => s.LockTag = string.Empty); } //出库结单 var outMaster = OutBillMasterRepository.GetQueryable() .FirstOrDefault(o => o.BillNo == sortWork.OutBillNo); outMaster.Status = "6"; outMaster.UpdateTime = DateTime.Now; //移库结单 var moveMater = MoveBillMasterRepository.GetQueryable() .FirstOrDefault(m => m.BillNo == sortWork.MoveBillNo); moveMater.Status = "4"; moveMater.UpdateTime = DateTime.Now; //分拣作业结单 sortWork.DispatchStatus = "4"; sortWork.UpdateTime = DateTime.Now; SortWorkDispatchRepository.SaveChanges(); scope.Complete(); return(true); } } catch (AggregateException ex) { errorInfo = "结单失败,详情:" + ex.InnerExceptions.Select(i => i.Message).Aggregate((m, n) => m + n); return(false); } catch (Exception e) { errorInfo = "结单失败,详情:" + e.Message; return(false); } }
public System.Data.DataTable GetInfoDetail(int page, int rows, string warehouseCode, string productCode, string settleDate) { var inQuery = InBillDetailRepository.GetQueryable(); var outQuery = OutBillDetailRepository.GetQueryable(); var differQuery = ProfitLossBillDetailRepository.GetQueryable(); var Allquery = inQuery.Select(a => new { BillDate = a.InBillMaster.BillDate, a.InBillMaster.Warehouse.WarehouseCode, a.InBillMaster.Warehouse.WarehouseName, a.BillNo, a.InBillMaster.BillType.BillTypeCode, a.InBillMaster.BillType.BillTypeName, a.ProductCode, a.Product.ProductName, a.RealQuantity, a.Unit.UnitName }).Union(outQuery.Select(a => new { BillDate = a.OutBillMaster.BillDate, a.OutBillMaster.Warehouse.WarehouseCode, a.OutBillMaster.Warehouse.WarehouseName, a.BillNo, a.OutBillMaster.BillType.BillTypeCode, a.OutBillMaster.BillType.BillTypeName, a.ProductCode, a.Product.ProductName, a.RealQuantity, a.Unit.UnitName })).Union(differQuery.Select(a => new { BillDate = a.ProfitLossBillMaster.BillDate, a.ProfitLossBillMaster.Warehouse.WarehouseCode, a.ProfitLossBillMaster.Warehouse.WarehouseName, a.BillNo, a.ProfitLossBillMaster.BillType.BillTypeCode, a.ProfitLossBillMaster.BillType.BillTypeName, a.ProductCode, a.Product.ProductName, RealQuantity = a.Quantity, a.Unit.UnitName })); if (!settleDate.Equals(string.Empty)) { DateTime date = Convert.ToDateTime(settleDate); Allquery = Allquery.Where(i => i.BillDate.Year == date.Year && i.BillDate.Month == date.Month && i.BillDate.Day == date.Day); } Allquery = Allquery.Where(i => i.ProductCode.Contains(productCode) && i.WarehouseCode.Contains(warehouseCode)).OrderBy(a => a.BillDate).ThenBy(a => a.WarehouseName); var query = Allquery.ToArray().Select(i => new { BillDate = i.BillDate.ToString("yyyy-MM-dd"), i.WarehouseCode, i.WarehouseName, i.BillNo, i.BillTypeCode, i.BillTypeName, i.ProductCode, i.ProductName, i.RealQuantity, JQuantity = Convert.ToDouble(i.RealQuantity / 50), TQuantity = i.RealQuantity, i.UnitName }); System.Data.DataTable dt = new System.Data.DataTable(); dt.Columns.Add("日期", typeof(string)); dt.Columns.Add("单据编号", typeof(string)); dt.Columns.Add("单据业务", typeof(string)); dt.Columns.Add("商品代码", typeof(string)); dt.Columns.Add("商品名称", typeof(string)); dt.Columns.Add("账面数量", typeof(string)); dt.Columns.Add("数量(自然件)", typeof(string)); dt.Columns.Add("数量(条)", typeof(string)); dt.Columns.Add("单据单位", typeof(string)); foreach (var item in query) { dt.Rows.Add ( item.BillDate, item.BillNo, item.BillTypeName, item.ProductCode, item.ProductName, item.RealQuantity, item.JQuantity, item.TQuantity, item.UnitName ); } return(dt); }
public void Dispatch(string connectionId, Model.ProgressState ps, CancellationToken cancellationToken, string workDispatchId, string userName) { Locker.LockKey = workDispatchId; ConnectionId = connectionId; ps.State = StateType.Start; NotifyConnection(ps.Clone()); IQueryable <SortOrderDispatch> sortOrderDispatchQuery = SortOrderDispatchRepository.GetQueryable(); IQueryable <SortOrder> sortOrderQuery = SortOrderRepository.GetQueryable(); IQueryable <SortOrderDetail> sortOrderDetailQuery = SortOrderDetailRepository.GetQueryable(); IQueryable <OutBillMaster> outBillMasterQuery = OutBillMasterRepository.GetQueryable(); IQueryable <OutBillDetail> outBillDetailQuery = OutBillDetailRepository.GetQueryable(); IQueryable <MoveBillMaster> moveBillMasterQuery = MoveBillMasterRepository.GetQueryable(); IQueryable <MoveBillDetail> moveBillDetailQuery = MoveBillDetailRepository.GetQueryable(); IQueryable <SortingLowerlimit> sortingLowerlimitQuery = SortingLowerlimitRepository.GetQueryable(); IQueryable <SortingLine> sortingLineQuery = SortingLineRepository.GetQueryable(); IQueryable <Storage> storageQuery = StorageRepository.GetQueryable(); IQueryable <SortWorkDispatch> sortWorkDispatchQuery = SortWorkDispatchRepository.GetQueryable(); workDispatchId = workDispatchId.Substring(0, workDispatchId.Length - 1); int[] work = workDispatchId.Split(',').Select(s => Convert.ToInt32(s)).ToArray(); //调度表未作业的数据 var temp = sortOrderDispatchQuery.Where(s => work.Any(w => w == s.ID) && s.WorkStatus == "1") .Join(sortOrderQuery, dp => new { dp.OrderDate, dp.DeliverLineCode }, om => new { om.OrderDate, om.DeliverLineCode }, (dp, om) => new { dp.OrderDate, dp.SortingLine, dp.DeliverLineCode, om.OrderID } ).Join(sortOrderDetailQuery, dm => new { dm.OrderID }, od => new { od.OrderID }, (dm, od) => new { dm.OrderDate, dm.SortingLine, od.Product, od.UnitCode, od.Price, od.RealQuantity } ).GroupBy(r => new { r.OrderDate, r.SortingLine, r.Product, r.UnitCode, r.Price }) .Select(r => new { r.Key.OrderDate, r.Key.SortingLine, r.Key.Product, r.Key.UnitCode, r.Key.Price, SumQuantity = r.Sum(p => p.RealQuantity * r.Key.Product.UnitList.Unit02.Count) }) .GroupBy(r => new { r.OrderDate, r.SortingLine }) .Select(r => new { r.Key.OrderDate, r.Key.SortingLine, Products = r }) .ToArray(); var employee = EmployeeRepository.GetQueryable().FirstOrDefault(i => i.UserName == userName); string operatePersonID = employee != null?employee.ID.ToString() : ""; if (employee == null) { ps.State = StateType.Error; ps.Errors.Add("未找到当前用户,或当前用户不可用!"); NotifyConnection(ps.Clone()); return; } decimal sumAllotQuantity = 0; decimal sumAllotLineQuantity = 0; MoveBillMaster lastMoveBillMaster = null; foreach (var item in temp) { try { if (cancellationToken.IsCancellationRequested) { return; } bool hasError = false; ps.State = StateType.Info; ps.Messages.Add("开始调度" + item.SortingLine.SortingLineName); NotifyConnection(ps.Clone()); using (var scope = new TransactionScope()) { if (item.Products.Count() > 0) { if (cancellationToken.IsCancellationRequested) { return; } if (lastMoveBillMaster != null && lastMoveBillMaster.WarehouseCode != item.SortingLine.Cell.WarehouseCode) { if (MoveBillCreater.CheckIsNeedSyncMoveBill(lastMoveBillMaster.WarehouseCode)) { MoveBillCreater.CreateSyncMoveBillDetail(lastMoveBillMaster); } } sumAllotLineQuantity = 0; if (cancellationToken.IsCancellationRequested) { return; } MoveBillMaster moveBillMaster = MoveBillCreater.CreateMoveBillMaster(item.SortingLine.Cell.WarehouseCode, item.SortingLine.MoveBillTypeCode, operatePersonID); moveBillMaster.Origin = "2"; moveBillMaster.Description = "分拣调度生成!"; lastMoveBillMaster = moveBillMaster; foreach (var product in item.Products.ToArray()) { if (cancellationToken.IsCancellationRequested) { return; } decimal sumBillQuantity = temp.Sum(t => t.Products.Sum(p => p.SumQuantity)); sumAllotQuantity += product.SumQuantity; decimal sumBillProductQuantity = item.Products.Sum(p => p.SumQuantity); sumAllotLineQuantity += product.SumQuantity; ps.State = StateType.Processing; ps.TotalProgressName = "分拣作业调度"; ps.TotalProgressValue = (int)(sumAllotQuantity / sumBillQuantity * 100); ps.CurrentProgressName = "正在调度:" + item.SortingLine.SortingLineName; ps.CurrentProgressValue = (int)(sumAllotLineQuantity / sumBillProductQuantity * 100); NotifyConnection(ps.Clone()); if (cancellationToken.IsCancellationRequested) { return; } //获取分拣线下限数据 var sortingLowerlimitQuantity = sortingLowerlimitQuery.Where(s => s.ProductCode == product.Product.ProductCode && s.SortingLineCode == product.SortingLine.SortingLineCode); decimal lowerlimitQuantity = 0; if (sortingLowerlimitQuantity.Count() > 0) { lowerlimitQuantity = sortingLowerlimitQuantity.Sum(s => s.Quantity); } if (cancellationToken.IsCancellationRequested) { return; } //获取分拣备货区库存 var storageQuantity = storageQuery.Where(s => s.ProductCode == product.Product.ProductCode) .Join(sortingLineQuery, s => s.Cell, l => l.Cell, (s, l) => new { l.SortingLineCode, s.Quantity } ) .Where(r => r.SortingLineCode == product.SortingLine.SortingLineCode); decimal storQuantity = 0; if (storageQuantity.Count() > 0) { storQuantity = storageQuantity.Sum(s => s.Quantity); } //获取当前这个卷烟库存数量 string[] areaTypes = new string[] { "1", "2", "4" }; var areaSumQuantitys = storageQuery.Where(s => areaTypes.Any(t => t == s.Cell.Area.AreaType) && s.ProductCode == product.Product.ProductCode).ToArray(); decimal areaQuantiy = 0; if (areaSumQuantitys.Count() > 0) { areaQuantiy = areaSumQuantitys.Sum(s => s.Quantity - s.OutFrozenQuantity); } if (cancellationToken.IsCancellationRequested) { return; } //获取移库量(按整件计)出库量加上下限量减去备货区库存量取整 decimal quantity = 0; quantity = Math.Ceiling((product.SumQuantity + lowerlimitQuantity - storQuantity) / product.Product.Unit.Count) * product.Product.Unit.Count; if (areaQuantiy < quantity)//判断当前这个卷烟库存是否小于移库量 { //出库量减去备货区库存量取整 quantity = Math.Ceiling((product.SumQuantity - storQuantity) / product.Product.Unit.Count) * product.Product.Unit.Count; if (areaQuantiy < quantity) { //出库量减去备货区库存量 quantity = product.SumQuantity - storQuantity; } } if (quantity > 0) { if (cancellationToken.IsCancellationRequested) { return; } AlltoMoveBill(moveBillMaster, product.Product, item.SortingLine.Cell, ref quantity, cancellationToken, ps, item.SortingLine.Cell.CellCode); } if (quantity > 0) { //生成移库不完整,可能是库存不足; hasError = true; ps.State = StateType.Error; ps.Errors.Add(product.Product.ProductCode + " " + product.Product.ProductName + " 库存不足!"); NotifyConnection(ps.Clone()); } } if (!hasError) { if (cancellationToken.IsCancellationRequested) { return; } OutBillMaster outBillMaster = OutBillCreater.CreateOutBillMaster(item.SortingLine.Cell.WarehouseCode, item.SortingLine.OutBillTypeCode, operatePersonID); outBillMaster.Origin = "2"; outBillMaster.Description = "分拣调度生成!"; //添加出库单细单 foreach (var product in item.Products.ToArray()) { if (cancellationToken.IsCancellationRequested) { return; } OutBillCreater.AddToOutBillDetail(outBillMaster, product.Product, product.Price, product.SumQuantity); } if (cancellationToken.IsCancellationRequested) { return; } //添加出库、移库主单和作业调度表 SortWorkDispatch sortWorkDisp = AddSortWorkDispMaster(moveBillMaster, outBillMaster, item.SortingLine.SortingLineCode, item.OrderDate); //修改线路调度作业状态和作业ID var sortDispTemp = sortOrderDispatchQuery.Where(s => work.Any(w => w == s.ID) && s.OrderDate == item.OrderDate && s.SortingLineCode == item.SortingLine.SortingLineCode); foreach (var sortDisp in sortDispTemp.ToArray()) { if (cancellationToken.IsCancellationRequested) { return; } sortDisp.SortWorkDispatchID = sortWorkDisp.ID; sortDisp.WorkStatus = "2"; } if (cancellationToken.IsCancellationRequested) { return; } SortWorkDispatchRepository.SaveChanges(); scope.Complete(); ps.Messages.Add(item.SortingLine.SortingLineName + " 调度成功!"); } else { ps.State = StateType.Info; ps.Messages.Add(item.SortingLine.SortingLineName + " 调度失败!"); NotifyConnection(ps.Clone()); return; } } } if (cancellationToken.IsCancellationRequested) { return; } if (MoveBillCreater.CheckIsNeedSyncMoveBill(lastMoveBillMaster.WarehouseCode)) { MoveBillCreater.CreateSyncMoveBillDetail(lastMoveBillMaster); } MoveBillMasterRepository.SaveChanges(); } catch (Exception e) { ps.State = StateType.Info; ps.Errors.Add(item.SortingLine.SortingLineName + "作业调度失败! 详情:" + e.Message); NotifyConnection(ps.Clone()); return; } } ps.State = StateType.Info; ps.Messages.Add("调度完成!"); NotifyConnection(ps.Clone()); }
public object GetDailyBalanceInfos(int page, int rows, string warehouseCode, string settleDate) { var inQuery = InBillDetailRepository.GetQueryable().AsEnumerable(); var outQuery = OutBillDetailRepository.GetQueryable().AsEnumerable(); var profitLossQuery = ProfitLossBillDetailRepository.GetQueryable().AsEnumerable(); var query = inQuery.Where(a => a.InBillMaster.WarehouseCode.Contains(warehouseCode)).Select(a => new { BillDate = a.InBillMaster.BillDate.ToString("yyyy-MM-dd"), a.InBillMaster.Warehouse.WarehouseCode, a.InBillMaster.Warehouse.WarehouseName, a.BillNo, a.InBillMaster.BillType.BillTypeCode, a.InBillMaster.BillType.BillTypeName, a.ProductCode, a.Product.ProductName, RealQuantity = a.RealQuantity, Beginning = 0.00.ToString(), EntryAmount = a.RealQuantity.ToString(), ProfitAmount = 0.00.ToString(), LossAmount = 0.00.ToString(), Ending = 0.00.ToString(), a.Unit.UnitName }).Union(outQuery.Where(a => a.OutBillMaster.WarehouseCode.Contains(warehouseCode)).Select(a => new { BillDate = a.OutBillMaster.BillDate.ToString("yyyy-MM-dd"), a.OutBillMaster.Warehouse.WarehouseCode, a.OutBillMaster.Warehouse.WarehouseName, a.BillNo, a.OutBillMaster.BillType.BillTypeCode, a.OutBillMaster.BillType.BillTypeName, a.ProductCode, a.Product.ProductName, RealQuantity = a.RealQuantity, Beginning = 0.00.ToString(), EntryAmount = 0.00.ToString(), ProfitAmount = 0.00.ToString(), LossAmount = 0.00.ToString(), Ending = 0.00.ToString(), a.Unit.UnitName })).Union(profitLossQuery.Where(a => a.ProfitLossBillMaster.WarehouseCode.Contains(warehouseCode)).Select(a => new { BillDate = a.ProfitLossBillMaster.BillDate.ToString("yyyy-MM-dd"), a.ProfitLossBillMaster.Warehouse.WarehouseCode, a.ProfitLossBillMaster.Warehouse.WarehouseName, a.BillNo, a.ProfitLossBillMaster.BillType.BillTypeCode, a.ProfitLossBillMaster.BillType.BillTypeName, a.ProductCode, a.Product.ProductName, RealQuantity = a.Quantity, Beginning = 0.00.ToString(), EntryAmount = 0.00.ToString(), ProfitAmount = a.Quantity > 0 ? a.Quantity.ToString() : 0.00.ToString(), LossAmount = a.Quantity < 0 ? (-a.Quantity).ToString() : 0.00.ToString(), Ending = 0.00.ToString(), a.Unit.UnitName })); if (!settleDate.Equals(string.Empty)) { DateTime date = Convert.ToDateTime(settleDate); query = query.Where(i => Convert.ToDateTime(i.BillDate) == date); } int total = query.Count(); query = query.Skip((page - 1) * rows).Take(rows); return(new { total, rows = query.ToArray() }); }