public void ReleaseAndStartStockTakeMaster(string stNo)
{
StockTakeMaster stockTakeMaster = this.genericMgr.FindById<StockTakeMaster>(stNo);
if (stockTakeMaster.Status != CodeMaster.StockTakeStatus.Create)
{
throw new BusinessException(Resources.INV.StockTake.Error_StatusErrorWhenSubmit,
stockTakeMaster.StNo,
this.systemMgr.GetCodeDetailDescription(com.Sconit.CodeMaster.CodeMaster.StockTakeStatus, ((int)stockTakeMaster.Status).ToString()));
}
IList<string> stockTakeLocationList = this.genericMgr.FindAll<string>(SelectStockTakeLocationStatement, stockTakeMaster.StNo);
if (stockTakeLocationList == null || stockTakeLocationList.Count == 0)
{
throw new BusinessException("请选择盘点的库位。");
}
User user = SecurityContextHolder.Get();
stockTakeMaster.Status = CodeMaster.StockTakeStatus.InProcess;
stockTakeMaster.StartUserId = user.Id;
stockTakeMaster.StartUserName = user.FullName;
stockTakeMaster.StartDate = DateTime.Now;
this.genericMgr.Update(stockTakeMaster);
IList<StockTakeLocationLotDet> locLotDets = this.genericMgr.FindAll<StockTakeLocationLotDet>(" from StockTakeLocationLotDet as s where exists( select 1 from StockTakeLocation as l where l.Location=s.Location and l.StNo=? ) and s.RefNo=? order by Location asc ", new object[] { stNo, stockTakeMaster.RefNo });
if (locLotDets != null && locLotDets.Count > 0)
{
int i = 1;
foreach (var locLotDet in locLotDets)
{
StockTakeDetail stockTakeDetail = new StockTakeDetail();
stockTakeDetail.StNo = stNo;
stockTakeDetail.Item = locLotDet.Item;
stockTakeDetail.RefItemCode = locLotDet.RefItemCode;
stockTakeDetail.ItemDescription = locLotDet.ItemDesc;
stockTakeDetail.Uom = locLotDet.Uom;
stockTakeDetail.BaseUom = locLotDet.Uom;
stockTakeDetail.Location = locLotDet.Location;
stockTakeDetail.QualityType = locLotDet.QualityType;
stockTakeDetail.IsConsigement = locLotDet.IsConsigement;
stockTakeDetail.CSSupplier = locLotDet.CSSupplier;
stockTakeDetail.UnitQty = 1;
stockTakeDetail.Qty = 0;
this.genericMgr.Create(stockTakeDetail);
}
}
}
public void ImportStockTakeDetailFromXls(Stream inputStream, string stNo)
{
if (inputStream.Length == 0)
{
throw new BusinessException("Import.Stream.Empty");
}
#region 清空明细
string hql = @"from StockTakeDetail as s where s.StNo = ?";
genericMgr.Delete(hql, new object[] { stNo }, new IType[] { NHibernateUtil.String });
genericMgr.FlushSession();
genericMgr.CleanSession();
#endregion
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
ISheet sheet = workbook.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
var cell = sheet.GetRow(2).Cells[4];
var cellStNo = ImportHelper.GetCellStringValue(cell);
if (cellStNo != stNo)
{
throw new BusinessException("导入的模板不正确,盘点单号不一致");
}
ImportHelper.JumpRows(rows, 11);
#region 列定义
int colItem = 1;//物料代码
int colUom = 7;//单位
int colLocation = 3;// 库位
int colQty = 6;//数量
int colHu = 8;//条码
int colBin = 9;//库格
#endregion
DateTime dateTimeNow = DateTime.Now;
BusinessException errorMessage = new BusinessException();
IList<StockTakeDetail> stockTakeDetailList = new List<StockTakeDetail>();
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
if (!ImportHelper.CheckValidDataRow(row, 1, 6))
{
break;//边界
}
if (true)
{
string itemCode = string.Empty;
decimal qty = 0;
string uomCode = string.Empty;
string locationCode = string.Empty;
#region 读取数据
#region 读取物料代码
itemCode = ImportHelper.GetCellStringValue(row.GetCell(colItem));
if (itemCode == null || itemCode.Trim() == string.Empty)
{
errorMessage.AddMessage(new Message(com.Sconit.CodeMaster.MessageType.Error,
"读取物料失败在{0}行{1}列", (row.RowNum + 1).ToString(), (colItem+1).ToString()));
continue;
}
#endregion
#region 读取单位
uomCode = ImportHelper.GetCellStringValue(row.GetCell(colUom));
if (uomCode == null || uomCode.Trim() == string.Empty)
{
errorMessage.AddMessage(new Message(com.Sconit.CodeMaster.MessageType.Error,
"读取单位失败在{0}行{1}列", (row.RowNum + 1).ToString(), (colUom+1).ToString()));
continue;
}
#endregion
#region 读取单位和库存单位不一致则抛错
if (uomCode != itemMgr.GetCacheItem(itemCode).Uom)
{
errorMessage.AddMessage(new Message(com.Sconit.CodeMaster.MessageType.Error,
"读取单位失败在{0}行{1}列,单位和库存单位不一致", (row.RowNum + 1).ToString(), (colUom+1).ToString()));
continue;
}
#endregion
#endregion
#region 读取库位
locationCode = ImportHelper.GetCellStringValue(row.GetCell(colLocation));
if (locationCode == null || locationCode.Trim() == string.Empty)
{
errorMessage.AddMessage(new Message(com.Sconit.CodeMaster.MessageType.Error,
"读取库位失败在{0}行{1}列", (row.RowNum + 1).ToString(), (colLocation+1).ToString()));
continue;
}
#region 读取数量
try
{
qty = Convert.ToDecimal(ImportHelper.GetCellStringValue(row.GetCell(colQty)));
}
catch
{
errorMessage.AddMessage(new Message(com.Sconit.CodeMaster.MessageType.Error,
"读取数量失败在{0}行{1}列", (row.RowNum + 1).ToString(), (colQty+1).ToString()));
continue;
}
#endregion
#endregion
#region 填充数据
Item item = null;
try
{
item = genericMgr.FindById<Item>(itemCode);
}
catch (Exception)
{
errorMessage.AddMessage(new Message(com.Sconit.CodeMaster.MessageType.Error, "物料号{0}不存在", itemCode));
continue;
}
try
{
Uom uom = genericMgr.FindById<Uom>(uomCode);
}
catch (Exception)
{
errorMessage.AddMessage(new Message(com.Sconit.CodeMaster.MessageType.Error, "单位{0}不存在", uomCode));
continue;
}
try
{
Location location = genericMgr.FindById<Location>(locationCode);
}
catch (Exception)
{
errorMessage.AddMessage(new Message(com.Sconit.CodeMaster.MessageType.Error, "库位{0}不存在", locationCode));
continue;
}
StockTakeDetail stockTakeDetail = new StockTakeDetail();
stockTakeDetail.StNo = stNo;
stockTakeDetail.Item = itemCode;
stockTakeDetail.Uom = uomCode;
stockTakeDetail.Location = locationCode;
stockTakeDetail.BaseUom = item.Uom;
stockTakeDetail.Qty = qty;
stockTakeDetailList.Add(stockTakeDetail);
#endregion
}
else
{
string huId = string.Empty;
string binCode = string.Empty;
string locationCode = string.Empty;
#region 读取数据
#region 读取条码
huId = row.GetCell(colHu) != null ? row.GetCell(colHu).StringCellValue : string.Empty;
if (string.IsNullOrEmpty(huId))
{
throw new BusinessException("Import.Read.Error.Empty", (row.RowNum + 1).ToString(), colHu.ToString());
}
var i = (
from c in stockTakeDetailList
where c.HuId != null && c.HuId.Trim().ToUpper() == huId.Trim().ToUpper()
select c).Count();
if (i > 0)
{
throw new BusinessException("Import.Business.Error.Duplicate", huId, (row.RowNum + 1).ToString(), colHu.ToString());
}
#endregion
#region 读取库位
locationCode = row.GetCell(colLocation) != null ? row.GetCell(colLocation).StringCellValue : string.Empty;
if (locationCode == null || locationCode.Trim() == string.Empty)
{
throw new BusinessException("Import.Read.Error.Empty", (row.RowNum + 1).ToString(), colUom.ToString());
}
#region 读取库格
binCode = row.GetCell(colBin) != null ? row.GetCell(colBin).StringCellValue : null;
#endregion
#endregion
#endregion
#region 填充数据
Hu hu = genericMgr.FindById<Hu>(huId);
Location location = genericMgr.FindById<Location>(locationCode);
LocationBin bin = null;
if (binCode != null && binCode.Trim() != string.Empty)
{
bin = genericMgr.FindById<LocationBin>(binCode);
}
StockTakeDetail stockTakeDetail = new StockTakeDetail();
stockTakeDetail.StNo = stNo;
stockTakeDetail.Item = hu.Item;
stockTakeDetail.Qty = hu.Qty;
stockTakeDetail.Uom = hu.Uom;
stockTakeDetail.BaseUom = hu.BaseUom;
stockTakeDetail.HuId = hu.HuId;
stockTakeDetail.LotNo = hu.LotNo;
stockTakeDetail.Location = location.Code;
stockTakeDetail.Bin = bin.Code;
stockTakeDetailList.Add(stockTakeDetail);
#endregion
}
}
if (errorMessage.HasMessage)
{
throw errorMessage;
}
if (stockTakeDetailList.Count == 0)
{
throw new BusinessException("Import.Result.Error.ImportNothing");
}
#region 校验物料,库位
var stockTakeDetailGroup = stockTakeDetailList.GroupBy(p => new { p.Item, p.Location }, (k, g) => new { k, Count = g.Count() })
.Where(p => p.Count > 1).Select(p => new { p.k.Item, p.k.Location });
foreach (var stockTakeDetail in stockTakeDetailGroup)
{
errorMessage.AddMessage(new Message(com.Sconit.CodeMaster.MessageType.Error,
"存在重复的明细:库位{0}物料{1}", stockTakeDetail.Location, stockTakeDetail.Item));
}
#endregion
if (errorMessage.HasMessage)
{
throw errorMessage;
}
BatchUpdateStockTakeDetails(stNo, stockTakeDetailList, null, null);
}
public void ImportStockTakeDetailFromXls(Stream inputStream, string stNo)
{
if (inputStream.Length == 0)
{
throw new BusinessException("Import.Stream.Empty");
}
#region 清空明细
//string hql = @"from StockTakeDetail as s where s.StNo = ?";
//genericMgr.Delete(hql, new object[] { stNo }, new IType[] { NHibernateUtil.String });
//genericMgr.FlushSession();
//genericMgr.CleanSession();
#endregion
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
ISheet sheet = workbook.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
var cell = sheet.GetRow(1).Cells[0];
var cellStNo = ImportHelper.GetCellStringValue(cell);
if (cellStNo != stNo)
{
throw new BusinessException("导入的模板不正确,盘点单号不一致");
}
ImportHelper.JumpRows(rows, 1);
#region 列定义
//质量类型 寄售 寄售供应商 盘点数
int colLocation = 1;// 库位
int colItem = 2;//物料代码
int colQualitype = 5;//质量类型
int colIsConsigement = 6;//是否寄售
int colCSSupplier = 7;//寄售供应商
int colQty = 8;//盘点数
#endregion
DateTime dateTimeNow = DateTime.Now;
BusinessException errorMessage = new BusinessException();
IList<StockTakeDetail> stockTakeDetailList = new List<StockTakeDetail>();
IList<StockTakeDetail> existsList = this.genericMgr.FindAll<StockTakeDetail>(" from StockTakeDetail as d where d.StNo=?", stNo);
IList<StockTakeLocation> allLocs = genericMgr.FindAll<StockTakeLocation>(" from StockTakeLocation as s where s.StNo=? ", stNo);
IList<Item> allItems = genericMgr.FindAll<Item>();
int rowCount = 1;
while (rows.MoveNext())
{
rowCount++;
HSSFRow row = (HSSFRow)rows.Current;
if (!ImportHelper.CheckValidDataRow(row, 1, 8))
{
break;//边界
}
StockTakeDetail stockTakeDetail = new StockTakeDetail();
string locationCode = string.Empty;
string itemCode = string.Empty;
string qualityType = string.Empty;
bool isConsigement = false;
string csSupplier = string.Empty;
decimal qty = 0;
#region 读取数据
#region 读取库位
locationCode = ImportHelper.GetCellStringValue(row.GetCell(colLocation));
if (string.IsNullOrWhiteSpace(locationCode))
{
errorMessage.AddMessage(string.Format("第{0}行：库位不能为空。", rowCount));
continue;
}
else
{
var locationList = allLocs.Where(a => a.Location == locationCode);
if (locationList == null && locationList.Count() == 0)
{
errorMessage.AddMessage(string.Format("第{0}行：库位{1}不存在盘点单的盘点库位中", rowCount, locationCode));
continue;
}
else
{
stockTakeDetail.Location = locationCode;
}
}
// Location locationFrom = genericMgr.FindById<Location>(locationFromCode);
#endregion
#region 读取物料代码
itemCode = ImportHelper.GetCellStringValue(row.GetCell(colItem));
if (string.IsNullOrWhiteSpace(itemCode))
{
errorMessage.AddMessage(string.Format("第{0}行：物料代码不能为空。", rowCount));
continue;
}
else
{
var items = allItems.Where(a => a.Code == itemCode);
if (items == null && items.Count() == 0)
{
errorMessage.AddMessage(string.Format("第{0}行：物料代码{1}不存在", rowCount, itemCode));
continue;
}
else
{
var currentItem = items.First();
stockTakeDetail.Item = currentItem.Code;
stockTakeDetail.ItemDescription = currentItem.Description;
stockTakeDetail.RefItemCode = currentItem.ReferenceCode;
stockTakeDetail.Uom = currentItem.Uom;
stockTakeDetail.BaseUom = currentItem.Uom;
}
}
#endregion
#region 质量类型
qualityType = ImportHelper.GetCellStringValue(row.GetCell(colQualitype));
if (qualityType == "0" || qualityType == "正常")
{
stockTakeDetail.QualityType = com.Sconit.CodeMaster.QualityType.Qualified;
}
else if (qualityType == "1" || qualityType == "待验")
{
stockTakeDetail.QualityType = com.Sconit.CodeMaster.QualityType.Inspect;
}
else if (qualityType == "2" || qualityType == "不合格")
{
stockTakeDetail.QualityType = com.Sconit.CodeMaster.QualityType.Reject;
}
else
{
errorMessage.AddMessage(string.Format("第{0}行：质量类型{1}填写有误。", rowCount, qualityType));
continue;
}
#endregion
#region 是否寄售
string isConsigementRead = ImportHelper.GetCellStringValue(row.GetCell(colIsConsigement));
if (isConsigementRead == "0" || string.IsNullOrWhiteSpace(isConsigementRead))
{
stockTakeDetail.IsConsigement = false;
}
else if (isConsigementRead == "1" || isConsigementRead == "√")
{
stockTakeDetail.IsConsigement = true;
}
else
{
errorMessage.AddMessage(string.Format("第{0}行：是否寄售填写有误。", rowCount));
continue;
}
#endregion
#region 寄售的才有寄售供应商
if (stockTakeDetail.IsConsigement)
{
csSupplier = ImportHelper.GetCellStringValue(row.GetCell(colCSSupplier));
if (string.IsNullOrWhiteSpace(csSupplier))
{
errorMessage.AddMessage(string.Format("第{0}行：寄售物料请填写寄售供应商。", rowCount));
continue;
}
else
{
var supplis = this.genericMgr.FindAll<Party>(" from Party as p where p.Code=? ", csSupplier);
if (supplis == null || supplis.Count() == 0)
{
errorMessage.AddMessage(string.Format("第{0}行：寄售供应商{1}不存在", rowCount, csSupplier));
continue;
}
else
{
stockTakeDetail.CSSupplier = csSupplier;
}
}
}
#endregion
#region 读取数量
try
{
string qtyRead = ImportHelper.GetCellStringValue(row.GetCell(colQty));
if (string.IsNullOrWhiteSpace(qtyRead))
{
continue;
}
else
{
if (decimal.TryParse(qtyRead, out qty))
{
if (qty < 0)
{
errorMessage.AddMessage(string.Format("第{0}行：数量{1}不能小于0", rowCount, qty));
continue;
}
else
{
stockTakeDetail.Qty = qty;
}
}
else
{
errorMessage.AddMessage(string.Format("第{0}行：数量{1}填写有误", rowCount, qty));
continue;
}
}
}
catch
{
errorMessage.AddMessage(string.Format("第{0}行：数量{1}填写有误", rowCount, qty));
continue;
}
#endregion
#endregion
#region 检验模版中的重复
if (stockTakeDetailList != null && stockTakeDetailList.Count > 0)
{
bool isExists = stockTakeDetailList.Where(s => s.Item == stockTakeDetail.Item && s.Location == stockTakeDetail.Location && s.QualityType == stockTakeDetail.QualityType
&& s.IsConsigement == stockTakeDetail.IsConsigement && s.CSSupplier == stockTakeDetail.CSSupplier).Count() > 0;
if (isExists)
{
errorMessage.AddMessage(string.Format("第{0}行：物料{1}+库位{2}+质量状态{3}+寄售类型{4}+寄售供应商{5}在模版中重复。", rowCount, stockTakeDetail.Item, stockTakeDetail.Location, stockTakeDetail.QualityType, stockTakeDetail.IsConsigement, stockTakeDetail.CSSupplier));
continue;
}
}
if (existsList != null && existsList.Count > 0)
{
var currentDetail = existsList.Where(s => s.Item == stockTakeDetail.Item && s.Location == stockTakeDetail.Location && s.QualityType == stockTakeDetail.QualityType
&& s.IsConsigement == stockTakeDetail.IsConsigement && s.CSSupplier == stockTakeDetail.CSSupplier);
if (currentDetail != null && currentDetail.Count() > 0)
{
currentDetail.First().Qty = stockTakeDetail.Qty;
currentDetail.First().IsUpdate = true;
stockTakeDetailList.Add(currentDetail.First());
continue;
}
}
#endregion
stockTakeDetail.StNo = stNo;
stockTakeDetailList.Add(stockTakeDetail);
}
if (errorMessage.HasMessage)
{
throw errorMessage;
}
if (stockTakeDetailList.Count == 0)
{
throw new BusinessException("Import.Result.Error.ImportNothing");
}
foreach (var stockDet in stockTakeDetailList)
{
if (stockDet.IsUpdate.HasValue && stockDet.IsUpdate.Value)
{
this.genericMgr.Update(stockDet);
}
else
{
this.genericMgr.Create(stockDet);
}
}
BatchUpdateStockTakeDetails(stNo, stockTakeDetailList, null, null);
}
