public object GetDetails(int page, int rows, string BillNo) { if (BillNo != "" && BillNo != null) { IQueryable <InBillDetail> inBillDetailQuery = InBillDetailRepository.GetQueryable(); var inBillDetail = inBillDetailQuery.Where(i => i.BillNo.Contains(BillNo)).OrderBy(i => i.BillNo).Select(i => i); int total = inBillDetail.Count(); inBillDetail = inBillDetail.Skip((page - 1) * rows).Take(rows); var temp = inBillDetail.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, RealQuantity = i.RealQuantity / i.Unit.Count, AllotQuantity = i.AllotQuantity / i.Unit.Count, i.Price, i.Description }); return(new { total, rows = temp.ToArray() }); } return(""); }
public new bool Add(InBillDetail inBillDetail) { IQueryable <InBillDetail> inBillDetailQuery = InBillDetailRepository.GetQueryable(); var isExistProduct = inBillDetailQuery.FirstOrDefault(i => i.BillNo == inBillDetail.BillNo && i.ProductCode == inBillDetail.ProductCode); var unit = UnitRepository.GetQueryable().FirstOrDefault(u => u.UnitCode == inBillDetail.UnitCode); if (isExistProduct == null) { var ibd = new InBillDetail(); ibd.BillNo = inBillDetail.BillNo; ibd.ProductCode = inBillDetail.ProductCode; ibd.UnitCode = inBillDetail.UnitCode; ibd.Price = inBillDetail.Price; ibd.BillQuantity = inBillDetail.BillQuantity * unit.Count; ibd.AllotQuantity = 0; ibd.RealQuantity = 0; ibd.Description = inBillDetail.Description; InBillDetailRepository.Add(ibd); InBillDetailRepository.SaveChanges(); } else { var ibd = inBillDetailQuery.FirstOrDefault(i => i.BillNo == inBillDetail.BillNo && i.ProductCode == inBillDetail.ProductCode); ibd.UnitCode = inBillDetail.UnitCode; ibd.BillQuantity = ibd.BillQuantity + inBillDetail.BillQuantity * unit.Count; InBillDetailRepository.SaveChanges(); } return(true); }
public object GetInfoDetails(int page, int rows, string warehouseCode, string productCode, string settleDate) { var inQuery = InBillDetailRepository.GetQueryable().AsEnumerable(); var outQuery = OutBillDetailRepository.GetQueryable().AsEnumerable(); var Allquery = inQuery.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, a.RealQuantity, a.Unit.UnitName }).Union(outQuery.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, a.RealQuantity, a.Unit.UnitName })); var query = Allquery.Where(i => i.ProductCode.Contains(productCode) && i.WarehouseCode.Contains(warehouseCode) ).OrderBy(i => i.BillDate).OrderBy(i => i.WarehouseName ).AsEnumerable().Select(i => new { i.BillDate, 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 }); 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() }); }
public bool Delete(string ID) { IQueryable <InBillDetail> inBillDetailQuery = InBillDetailRepository.GetQueryable(); int intID = Convert.ToInt32(ID); var ibd = inBillDetailQuery.FirstOrDefault(i => i.ID == intID); InBillDetailRepository.Delete(ibd); InBillDetailRepository.SaveChanges(); return(true); }
public bool DownInBillMaster(string BeginDate, string EndDate, out string errorInfo) { errorInfo = string.Empty; bool result = false; string inBillStr = ""; string inBillMasterStr = ""; try { var inBillNos = InBillMasterRepository.GetQueryable().Where(i => i.BillNo == i.BillNo).Select(i => new { i.BillNo }).ToArray(); for (int i = 0; i < inBillNos.Length; i++) { inBillStr += inBillNos[i].BillNo + ","; } InBillMaster[] inBillMasterList = InBillMasterDownService.GetInBillMaster(inBillStr); foreach (var master in inBillMasterList) { var inBillMaster = new InBillMaster(); inBillMaster.BillNo = master.BillNo; inBillMaster.BillDate = master.BillDate; inBillMaster.BillTypeCode = master.BillTypeCode; inBillMaster.WarehouseCode = master.WarehouseCode; inBillMaster.Status = "1"; inBillMaster.IsActive = master.IsActive; inBillMaster.UpdateTime = DateTime.Now; InBillMasterRepository.Add(inBillMaster); inBillMasterStr += master.BillNo + ","; } if (inBillMasterStr != string.Empty) { InBillDetail[] inBillDetailList = InBillMasterDownService.GetInBillDetail(inBillMasterStr); foreach (var detail in inBillDetailList) { var inBillDetail = new InBillDetail(); inBillDetail.BillNo = detail.BillNo; inBillDetail.ProductCode = detail.ProductCode; inBillDetail.UnitCode = detail.UnitCode; inBillDetail.Price = detail.Price; inBillDetail.BillQuantity = detail.BillQuantity; inBillDetail.AllotQuantity = detail.AllotQuantity; inBillDetail.RealQuantity = detail.RealQuantity; inBillDetail.Description = detail.Description; InBillDetailRepository.Add(inBillDetail); } } InBillMasterRepository.SaveChanges(); result = true; } catch (Exception e) { errorInfo = "出错,原因:" + e.Message; } return(result); }
public System.Data.DataTable GetInBillDetail(int page, int rows, string BillNo) { System.Data.DataTable dt = new System.Data.DataTable(); if (BillNo != "" && BillNo != null) { IQueryable <InBillDetail> inBillDetailQuery = InBillDetailRepository.GetQueryable(); var inBillDetail = inBillDetailQuery.Where(i => i.BillNo.Contains(BillNo)).OrderBy(i => i.BillNo).Select(i => i); var temp = inBillDetail.ToArray().AsEnumerable().Select(i => new { i.BillNo, i.ProductCode, i.Product.ProductName, i.UnitCode, i.Unit.UnitName, BillQuantity = i.BillQuantity / i.Unit.Count, RealQuantity = i.RealQuantity / i.Unit.Count, AllotQuantity = i.AllotQuantity / 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 item in temp) { dt.Rows.Add ( item.ProductCode, item.ProductName, item.UnitCode, item.UnitName, item.BillQuantity, item.AllotQuantity, item.RealQuantity, item.Description ); } if (temp.Count() > 0) { dt.Rows.Add( null, null, null, "总数:", temp.Sum(m => m.BillQuantity), temp.Sum(m => m.AllotQuantity), temp.Sum(m => m.RealQuantity), null); } } return(dt); }
public bool Save(InBillDetail inBillDetail, out string strResult) { strResult = string.Empty; bool result = false; try { IQueryable <InBillDetail> inBillDetailQuery = InBillDetailRepository.GetQueryable(); var ibd = inBillDetailQuery.FirstOrDefault(i => i.BillNo == inBillDetail.BillNo && i.ProductCode == inBillDetail.ProductCode); var unit = UnitRepository.GetQueryable().FirstOrDefault(u => u.UnitCode == inBillDetail.UnitCode); if ((ibd != null && ibd.ID == inBillDetail.ID) || ibd == null) { if (ibd == null) { ibd = inBillDetailQuery.FirstOrDefault(i => i.BillNo == inBillDetail.BillNo && i.ID == inBillDetail.ID); } ibd.BillNo = inBillDetail.BillNo; ibd.ProductCode = inBillDetail.ProductCode; ibd.UnitCode = inBillDetail.UnitCode; ibd.Price = inBillDetail.Price; ibd.BillQuantity = inBillDetail.BillQuantity * unit.Count; ibd.Description = inBillDetail.Description; InBillDetailRepository.SaveChanges(); result = true; } else if (ibd != null && ibd.ID != inBillDetail.ID) { bool delDetail = this.Delete(inBillDetail.ID.ToString(), out strResult); ibd.BillNo = inBillDetail.BillNo; ibd.ProductCode = inBillDetail.ProductCode; ibd.UnitCode = inBillDetail.UnitCode; ibd.Price = inBillDetail.Price; ibd.BillQuantity = ibd.BillQuantity + inBillDetail.BillQuantity * unit.Count; ibd.Description = inBillDetail.Description; InBillDetailRepository.SaveChanges(); result = true; } } catch (Exception ex) { strResult = "修改失败,原因:" + ex.Message; } return(result); }
public bool Delete(string ID, out string strResult) { strResult = string.Empty; bool result = false; try { IQueryable <InBillDetail> inBillDetailQuery = InBillDetailRepository.GetQueryable(); int intID = Convert.ToInt32(ID); var ibd = inBillDetailQuery.FirstOrDefault(i => i.ID == intID); InBillDetailRepository.Delete(ibd); InBillDetailRepository.SaveChanges(); result = true; } catch (Exception ex) { strResult = "删除失败,原因:" + ex.Message; } return(result); }
public bool Add(InBillDetail inBillDetail, out string strResult) { strResult = string.Empty; bool result = false; try { IQueryable <InBillDetail> inBillDetailQuery = InBillDetailRepository.GetQueryable(); var isExistProduct = inBillDetailQuery.FirstOrDefault(i => i.BillNo == inBillDetail.BillNo && i.ProductCode == inBillDetail.ProductCode); var unit = UnitRepository.GetQueryable().FirstOrDefault(u => u.UnitCode == inBillDetail.UnitCode); if (isExistProduct == null) { var ibd = new InBillDetail(); ibd.BillNo = inBillDetail.BillNo; ibd.ProductCode = inBillDetail.ProductCode; ibd.UnitCode = inBillDetail.UnitCode; ibd.Price = inBillDetail.Price; ibd.BillQuantity = inBillDetail.BillQuantity * unit.Count; ibd.AllotQuantity = 0; ibd.RealQuantity = 0; ibd.Description = inBillDetail.Description; InBillDetailRepository.Add(ibd); InBillDetailRepository.SaveChanges(); result = true; } else { var ibd = inBillDetailQuery.FirstOrDefault(i => i.BillNo == inBillDetail.BillNo && i.ProductCode == inBillDetail.ProductCode); ibd.UnitCode = inBillDetail.UnitCode; ibd.BillQuantity = ibd.BillQuantity + inBillDetail.BillQuantity * unit.Count; InBillDetailRepository.SaveChanges(); result = true; } } catch (Exception ex) { strResult = "新增失败,原因:" + ex.Message; } return(result); }
public object GetDetailInfos(int page, int rows, string BillNo) { IQueryable <InBillDetail> StockIntoQuery = InBillDetailRepository.GetQueryable(); var StockIntoDetail = StockIntoQuery.Where(i => i.BillNo.Contains(BillNo)).OrderBy(i => i.BillNo).Select(i => new { i.ID, i.BillNo, i.ProductCode, i.Product.ProductName, i.UnitCode, i.Unit.UnitName, i.BillQuantity, i.RealQuantity, i.Price, i.Description }); int total = StockIntoDetail.Count(); StockIntoDetail = StockIntoDetail.Skip((page - 1) * rows).Take(rows); return(new { total, rows = StockIntoDetail.ToArray() }); }
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() }); }
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 bool Add(DateTime datetime, out string strResult) { bool result = false; strResult = string.Empty; var inBillMaster = InBillMasterRepository.GetQueryable().Where(i => i.BillDate <= datetime); var inBillDetail = InBillDetailRepository.GetQueryable().Where(i => i.InBillMaster.BillDate <= datetime); var inBillAllot = InBillAllotRepository.GetQueryable().Where(i => i.InBillMaster.BillDate <= datetime); if (inBillMaster.Any()) { #region 主表移入历史表 try { foreach (var item in inBillMaster.ToArray()) { InBillMasterHistory history = new InBillMasterHistory(); 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.TargetCellCode = item.TargetCellCode; InBillMasterHistoryRepository.Add(history); } result = true; } catch (Exception e) { strResult = "迁移主表时:" + e.InnerException.ToString(); result = false; } #endregion if (inBillDetail.Any()) { #region 细表移入历史表 try { foreach (var item2 in inBillDetail.ToArray()) { InBillDetailHistory history2 = new InBillDetailHistory(); history2.ID = item2.ID; history2.BillNo = item2.BillNo; history2.ProductCode = item2.ProductCode; history2.UnitCode = item2.UnitCode; history2.Price = item2.Price; history2.BillQuantity = item2.BillQuantity; history2.AllotQuantity = item2.AllotQuantity; history2.RealQuantity = item2.RealQuantity; history2.Description = item2.Description; InBillDetailHistoryRepository.Add(history2); } result = true; } catch (Exception e) { strResult = "迁移细表时:" + e.InnerException.ToString(); result = false; } #endregion if (inBillAllot.Any()) { #region 分配表移入历史表 try { foreach (var item3 in inBillAllot.ToArray()) { InBillAllotHistory history3 = new InBillAllotHistory(); history3.BillNo = item3.BillNo; history3.ProductCode = item3.ProductCode; history3.InBillDetailId = item3.InBillDetailId; 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; InBillAllotHistoryRepository.Add(history3); } result = true; } catch (Exception e) { strResult = "迁移分配表时:" + e.InnerException.ToString(); result = false; } #endregion } } if (result == true) { #region 除主细分配表 try { foreach (var item in inBillMaster.ToList()) { Del(InBillAllotRepository, item.InBillAllots); Del(InBillDetailRepository, item.InBillDetails); InBillMasterRepository.Delete(item); result = true; } } catch (Exception e) { strResult = "删除操作时:" + e.InnerException.ToString(); result = false; } InBillMasterRepository.SaveChanges(); #endregion } } 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); } }
public bool AllotAdd(string billNo, long id, string cellCode, decimal allotQuantity, string productname, out string strResult) { bool result = false; decimal quantity = 0; var ibm = InBillMasterRepository.GetQueryable().FirstOrDefault(i => i.BillNo == billNo); var cell = CellRepository.GetQueryable().Single(c => c.CellCode == cellCode); var cell0 = CellRepository.GetQueryable(); var storages0 = StorageRepository.GetQueryable(); var cellstorage = cell0.Join(storages0, c => c.CellCode, s => s.CellCode, (c, s) => new { cell0 = c, storages0 = s }); var cellstorages = cellstorage.Where(c => c.cell0.CellCode == cellCode).Select(c => new { product = c.storages0.Product.ProductName }).ToArray(); var storages = StorageRepository.GetQueryable().Single(s => s.CellCode == cellCode); if (storages != null) { quantity = storages.Quantity; } var ibd = InBillDetailRepository.GetQueryable().FirstOrDefault(i => i.ID == id); if (ibm != null) { if (string.IsNullOrEmpty(ibm.LockTag)) { Storage storage = Locker.LockStorage(cell); if (storage != null) { if (productname == cellstorages[0].product || quantity == 0) { if (allotQuantity > 0) { InBillAllot billAllot = null; decimal q1 = ibd.BillQuantity - ibd.AllotQuantity; decimal q2 = allotQuantity * ibd.Unit.Count; if (q2 <= q1) { if (q2 <= (cell.MaxQuantity * ibd.Unit.Count - (storage.Quantity + storage.InFrozenQuantity))) { try { billAllot = new InBillAllot() { BillNo = billNo, InBillDetailId = ibd.ID, ProductCode = ibd.ProductCode, CellCode = storage.CellCode, StorageCode = storage.StorageCode, UnitCode = ibd.UnitCode, AllotQuantity = q2, RealQuantity = 0, Status = "0" }; ibd.AllotQuantity += q2; storage.InFrozenQuantity += q2; storage.ProductCode = ibd.ProductCode; ibm.InBillAllots.Add(billAllot); ibm.Status = "3"; storage.LockTag = string.Empty; StorageRepository.SaveChanges(); strResult = ""; result = true; } catch (Exception) { strResult = "保存添加失败,订单或储位其他人正在操作!"; } } else { strResult = "分配数量大于储位的最大数量!"; } } else { strResult = "分配数量超过订单数量!"; } } else { strResult = "分配数量必须大于0!"; } } else { strResult = "该货位已存在其他产品,不能放入该产品!"; } } else { strResult = "当前选择的储位不可用,或者分配数量超过最大托盘数,其他人正在操作!"; } } else { strResult = "当前订单其他人正在操作,请稍候重试!"; } } else { strResult = "当前订单状态不是已分配,或当前订单不存在!"; } return(result); }
/// <summary> /// 手工分配入库单 /// </summary> /// <param name="billNo"></param> /// <param name="id"></param> /// <param name="cellCode"></param> /// <param name="allotQuantity"></param> /// <param name="strResult"></param> /// <returns></returns> public bool AllotAdd(string billNo, long id, string cellCode, decimal allotQuantity, out string strResult) { bool result = false; var ibm = InBillMasterRepository.GetQueryable().FirstOrDefault(i => i.BillNo == billNo); var cell = CellRepository.GetQueryable().Single(c => c.CellCode == cellCode); var ibd = InBillDetailRepository.GetQueryable().FirstOrDefault(i => i.ID == id); if (ibm != null) { if (string.IsNullOrEmpty(ibm.LockTag)) { Storage storage = Locker.LockStorage(cell); if (storage != null) { if (allotQuantity > 0) { InBillAllot billAllot = null; decimal q1 = ibd.BillQuantity - ibd.AllotQuantity; decimal q2 = allotQuantity * ibd.Unit.Count; if (q2 <= q1 && q2 <= (cell.MaxQuantity * ibd.Unit.Count - (storage.Quantity + storage.InFrozenQuantity))) { try { billAllot = new InBillAllot() { BillNo = billNo, InBillDetailId = ibd.ID, ProductCode = ibd.ProductCode, CellCode = storage.CellCode, StorageCode = storage.StorageCode, UnitCode = ibd.UnitCode, AllotQuantity = q2, RealQuantity = 0, Status = "0" }; ibd.AllotQuantity += q2; storage.InFrozenQuantity += q2; storage.ProductCode = ibd.ProductCode; ibm.InBillAllots.Add(billAllot); ibm.Status = "3"; storage.LockTag = string.Empty; StorageRepository.SaveChanges(); strResult = "手工分配成功!"; result = true; } catch (Exception) { strResult = "保存添加失败,订单或储位其他人正在操作!"; } } else { strResult = "分配数量超过订单数量,或者大于储位的最大数量!"; } } else { strResult = "分配数量必须大于0!"; } } else { strResult = "当前选择的储位不可用,或者分配数量超过最大托盘数,其他人正在操作!"; } } else { strResult = "当前订单其他人正在操作,请稍候重试!"; } } else { strResult = "当前订单状态不是已分配,或当前订单不存在!"; } return(result); }
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 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 DataSet Insert() { IQueryable <InBillMaster> inBillMaster = InBillMasterRepository.GetQueryable(); IQueryable <InBillAllot> inBillAllot = InBillAllotRepository.GetQueryable(); IQueryable <InBillDetail> inBillDetail = InBillDetailRepository.GetQueryable(); var inBillMasterQuery = inBillMaster.Where(i => i.Status == "6").Select(i => new { STORE_BILL_ID = i.BillNo, RELATE_BUSI_BILL_NUM = inBillAllot.Count(a => a.BillNo == i.BillNo), DIST_CTR_CODE = i.WarehouseCode, QUANTITY_SUM = inBillAllot.Where(a => a.BillNo == i.BillNo).Sum(a => a.AllotQuantity / 200), AMOUNT_SUM = inBillDetail.Where(d => d.BillNo == i.BillNo).Sum(d => d.Price * d.AllotQuantity / 200), DETAIL_NUM = inBillDetail.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_IN_BILLMASTER"].NewRow(); foreach (var p in inBillMasterQuery) { 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"] = "1202"; inbrddr["BILL_TYPE"] = p.BILL_TYPE; inbrddr["BILL_STATUS"] = "99"; inbrddr["DISUSE_STATUS"] = "0"; inbrddr["IS_IMPORT"] = "1"; ds.Tables["WMS_IN_BILLMASTER"].Rows.Add(inbrddr); } DataRow inbrddrDetail = ds.Tables["WMS_IN_BILLDETAIL"].NewRow(); var inBillDetailQuery = inBillDetail.Where(i => i.InBillMaster.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 inBillDetailQuery) { 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_IN_BILLDETAIL"].Rows.Add(inbrddrDetail); } DataRow inbrddrAllot = ds.Tables["WMS_IN_BILLALLOT"].NewRow(); var inBillAllotQuery = inBillAllot.Where(i => i.InBillMaster.Status == "6").Select(i => new { BUSI_ACT_ID = i.ID, BUSI_BILL_DETAIL_ID = i.InBillDetailId, BUSI_BILL_ID = i.BillNo, BRAND_CODE = i.ProductCode, BRAND_NAME = i.Product.ProductName, QUANTITY = i.AllotQuantity / 200, DIST_CTR_CODE = i.InBillMaster.WarehouseCode, STORE_PLACE_CODE = i.Storage.CellCode, UPDATE_CODE = i.Operator, BILL_TYPE = i.InBillMaster.BillTypeCode //BEGIN_STOCK_QUANTITY = StorageRepository.GetQueryable().Where(s => s.ProductCode == i.ProductCode).Sum(s => s.Quantity / 200) + i.AllotQuantity, //END_STOCK_QUANTITY = i.AllotQuantity, }); foreach (var p in inBillAllotQuery) { 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"] = "1202"; 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_IN_BILLALLOT"].Rows.Add(inbrddrAllot); } return(ds); }