public object Search(string billNo, int page, int rows)
{
var allotQuery = InBillAllotRepository.GetQueryable();
var query = allotQuery.Where(a => a.BillNo == billNo).OrderBy(a => a.ID).Select(i => i);
int total = query.Count();
query = query.Skip((page - 1) * rows).Take(rows);
var temp = query.ToArray().Select(a => new
{
a.ID,
a.BillNo,
a.ProductCode,
a.Product.ProductName,
a.CellCode,
a.Cell.CellName,
a.StorageCode,
a.UnitCode,
a.Unit.UnitName,
AllotQuantity = a.AllotQuantity / a.Unit.Count,
RealQuantity = a.RealQuantity / a.Unit.Count,
a.OperatePersonID,
StartTime = a.StartTime == null ? "" : ((DateTime)a.StartTime).ToString("yyyy-MM-dd"),
FinishTime = a.FinishTime == null ? "" : ((DateTime)a.FinishTime).ToString("yyyy-MM-dd"),
Status = WhatStatus(a.Status)
});
return(new { total, rows = temp.ToArray() });
}
public object SearchInBillAllot(string billNo, int page, int rows)
{
var query = InBillAllotRepository.GetQueryable()
.Where(a => a.BillNo == billNo && a.Status != "2")
.OrderByDescending(a => a.Status == "1")
.Select(i => i);
int total = query.Count();
query = query.Skip((page - 1) * rows).Take(rows);
var temp = query.ToArray().Select(a => new
{
a.ID,
a.BillNo,
a.ProductCode,
a.Product.ProductName,
a.CellCode,
a.Cell.CellName,
BillType = "入库",
a.StorageCode,
a.UnitCode,
a.Unit.UnitName,
PieceQuantity = Math.Floor(a.AllotQuantity / a.Product.UnitList.Unit01.Count),
BarQuantity = Math.Floor(a.AllotQuantity % a.Product.UnitList.Unit01.Count / a.Product.UnitList.Unit02.Count),
Status = WhatStatus(a.Status),
a.Operator
});
return(new { total, rows = temp.ToArray() });
}
public System.Data.DataTable AllotSearch(int page, int rows, string billNo)
{
System.Data.DataTable dt = null;
var allotQuery = InBillAllotRepository.GetQueryable();
if (allotQuery != null)
{
var query = allotQuery.Where(a => a.BillNo == billNo).OrderBy(a => a.ID).Select(a => new
{
a.ProductCode,
a.Product.ProductName,
a.Cell.CellName,
a.StorageCode,
a.UnitCode,
a.Unit.UnitName,
AllotQuantity = a.AllotQuantity / a.Unit.Count,
RealQuantity = a.RealQuantity / a.Unit.Count,
a.OperatePersonID,
StartTime = a.StartTime,
FinishTime = a.FinishTime,
Status = a.Status == "0" ? "未开始" : a.Status == "1" ? "已申请" : a.Status == "2" ? "已完成" : ""
});
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(decimal));
dt.Columns.Add("实际数量", typeof(decimal));
dt.Columns.Add("作业人员", typeof(string));
dt.Columns.Add("开始时间", typeof(string));
dt.Columns.Add("完成时间", typeof(string));
dt.Columns.Add("作业状态", typeof(string));
foreach (var q in query)
{
dt.Rows.Add
(
q.ProductCode,
q.ProductName,
q.CellName,
q.UnitName,
q.AllotQuantity,
q.RealQuantity,
q.OperatePersonID,
q.StartTime,
q.FinishTime,
q.Status
);
}
if (query.Count() > 0)
{
dt.Rows.Add(null, null, null, "总数：",
query.Sum(m => m.AllotQuantity),
query.Sum(m => m.RealQuantity),
null, null, null, null);
}
}
return(dt);
}
/// <summary>
/// 入库单据作业
/// </summary>
/// <param name="billNo">单据号</param>
/// <param name="errInfo">错误消息</param>
/// <returns></returns>
public bool InBIllTask(string billNo, out string errInfo)
{
bool result = true;
errInfo = string.Empty;
var originPositionSystem = SystemParameterRepository.GetQueryable().FirstOrDefault(s => s.PARAMETER_VALUE == "IsDefaultProduct");//入库查询其实位置ID
var allotQuery = InBillAllotRepository.GetQueryable().Where(i => i.BillNo == billNo);
try
{
if (allotQuery.Any())
{
foreach (var inItem in allotQuery.ToArray())
{
//根据入库货位去找货位位置信息
var targetCellPosition = CellPositionRepository.GetQueryable().FirstOrDefault(c => c.CellCode == inItem.CellCode);
//根据入库位置ID去找目标区域ID信息
var targetPosition = PositionRepository.GetQueryable().FirstOrDefault(p => p.ID == targetCellPosition.StockInPositionID);
//根据起始位置ID去找起始区域ID信息
var originPosition = PositionRepository.GetQueryable().FirstOrDefault(p => p.ID == Convert.ToInt32(originPositionSystem.PARAMETER_VALUE));
//根据入库的目标区域和起始位置区域去找路径信息
var path = PathRepository.GetQueryable().FirstOrDefault(p => p.OriginRegionID == originPosition.RegionID && p.TargetRegionID == targetPosition.RegionID);
var inTask = new Task();
inTask.TaskType = "01";
inTask.TaskLevel = 0;
inTask.PathID = path.ID;
inTask.ProductCode = inItem.Product.ProductCode;
inTask.ProductName = inItem.Product.ProductName;
inTask.OriginStorageCode = "";
inTask.TargetStorageCode = inItem.CellCode;
inTask.OriginPositionID = Convert.ToInt32(originPositionSystem.PARAMETER_VALUE);
inTask.TargetPositionID = targetPosition.ID;
inTask.CurrentPositionID = Convert.ToInt32(originPositionSystem.PARAMETER_VALUE);
inTask.CurrentPositionState = "01";
inTask.State = "01";
inTask.TagState = "01";
inTask.Quantity = Convert.ToInt32(inItem.RealQuantity);
inTask.TaskQuantity = Convert.ToInt32(inItem.RealQuantity);
inTask.OperateQuantity = Convert.ToInt32(inItem.AllotQuantity);
inTask.OrderID = inItem.BillNo;
inTask.OrderType = "01";
inTask.AllotID = inItem.ID;
TaskRepository.Add(inTask);
}
TaskRepository.SaveChanges();
}
else
{
errInfo = "当前选择订单没有分配数据，请重新选择！";
}
}
catch (Exception e)
{
result = false;
errInfo = e.Message;
}
return(result);
}
/// <summary>
/// 入库单结单
/// </summary>
/// <param name="BillNo">入库单号</param>
/// <param name="strResult">提示信息</param>
/// <returns></returns>
public bool Settle(string BillNo, out string strResult)
{
bool result = false;
strResult = string.Empty;
var ibm = InBillMasterRepository.GetQueryable().FirstOrDefault(i => i.BillNo == BillNo);
if (ibm != null && ibm.Status == "5")
{
using (var scope = new TransactionScope())
{
try
{
//修改分配入库冻结量
var inAllot = InBillAllotRepository.GetQueryable()
.Where(o => o.BillNo == ibm.BillNo &&
o.Status != "2")
.ToArray();
var storages = inAllot.Select(i => i.Storage).ToArray();
if (!Locker.Lock(storages))
{
strResult = "锁定储位失败，储位其他人正在操作，无法结单请稍候重试！";
return(false);
}
inAllot.AsParallel().ForAll(
(Action <InBillAllot>) delegate(InBillAllot i)
{
if (i.Storage.ProductCode == i.ProductCode &&
i.Storage.InFrozenQuantity >= i.AllotQuantity)
{
i.Storage.InFrozenQuantity -= i.AllotQuantity;
i.Storage.LockTag = string.Empty;
}
else
{
throw new Exception("储位的卷烟或入库冻结量与当前分配不符，信息可能被异常修改，不能结单！");
}
}
);
ibm.Status = "6";
ibm.UpdateTime = DateTime.Now;
InBillMasterRepository.SaveChanges();
scope.Complete();
result = true;
}
catch (Exception e)
{
strResult = "入库单结单出错！原因：" + e.Message;
}
}
}
return(result);
}
public bool EditAllot(string id, string status, string operater, out string strResult)
{
strResult = string.Empty;
bool result = false;
string[] ids = id.Split(',');
string strId = "";
InBillAllot allot = null;
var employee = EmployeeRepository.GetQueryable().FirstOrDefault(e => e.UserName == operater);
for (int i = 0; i < ids.Length; i++)
{
strId = ids[i].ToString();
allot = InBillAllotRepository.GetQueryable().ToArray().FirstOrDefault(a => strId == a.ID.ToString());
if (allot != null)
{
if (allot.Status == "0" && status == "1" ||
allot.Status == "1" && status == "0" ||
allot.Status == "1" && status == "2")
{
try
{
allot.Status = status;
if (operater != "")
{
allot.Operator = employee.EmployeeName;
}
else
{
allot.Operator = "";
}
InBillAllotRepository.SaveChanges();
result = true;
}
catch (Exception ex)
{
strResult = "原因：" + ex.Message;
}
}
else
{
strResult = "原因：操作错误！";
}
}
else
{
strResult = "原因：未找到该记录！";
}
}
return(result);
}
/// <summary>
/// 根据参数查询要生成的盘点数据 --异动查询预览
/// </summary>
/// <param name="page"></param>
/// <param name="rows"></param>
/// <param name="beginDate">开始时间</param>
/// <param name="endDate">结束时间</param>
/// <returns></returns>
public object GetChangedCellDetails(int page, int rows, string beginDate, string endDate)
{
IQueryable <Storage> storageQuery = StorageRepository.GetQueryable();
IQueryable <InBillAllot> inAllotQuery = InBillAllotRepository.GetQueryable();
IQueryable <OutBillAllot> outAllotQuery = OutBillAllotRepository.GetQueryable();
IQueryable <MoveBillDetail> moveBillQuery = MoveBillDetailRepository.GetQueryable();
if (beginDate == string.Empty || beginDate == null)
{
beginDate = DateTime.Now.AddDays(-3).ToString("yyyy-MM-dd");
}
if (endDate == string.Empty || endDate == null)
{
endDate = DateTime.Now.ToString("yyyy-MM-dd");
}
DateTime begin = Convert.ToDateTime(beginDate);
DateTime end = Convert.ToDateTime(endDate);
var inCells = inAllotQuery.Where(i => i.FinishTime >= begin && i.FinishTime <= end).OrderBy(i => i.CellCode).Select(i => i.CellCode);
var outCells = outAllotQuery.Where(o => o.FinishTime >= begin && o.FinishTime <= end).OrderBy(o => o.CellCode).Select(o => o.CellCode);
var moveInCells = moveBillQuery.Where(m => m.FinishTime >= begin && m.FinishTime <= end).OrderBy(m => m.InCell.CellCode).Select(m => m.InCell.CellCode);
var moveOutCells = moveBillQuery.Where(m => m.FinishTime >= begin && m.FinishTime <= end).OrderBy(m => m.OutCell.CellCode).Select(m => m.OutCell.CellCode);
var storages = storageQuery.Where(s => s.Quantity > 0 && s.IsLock == "0" && (inCells.Any(i => i == s.CellCode) || outCells.Any(o => o == s.CellCode) || moveInCells.Any(mi => mi == s.CellCode) || moveOutCells.Any(mo => mo == s.CellCode)))
.OrderBy(s => s.ProductCode)
.Select(s => s);
int total = storages.Count();
storages = storages.Skip((page - 1) * rows).Take(rows);
var temp = storages.ToArray().Select(s => new
{
s.StorageCode,
s.Cell.CellCode,
s.Cell.CellName,
s.Product.ProductCode,
s.Product.ProductName,
s.Product.Unit.UnitCode,
s.Product.Unit.UnitName,
Quantity = s.Quantity / s.Product.Unit.Count,
IsActive = s.IsActive == "1" ? "可用" : "不可用",
StorageTime = s.StorageTime.ToString("yyyy-MM-dd"),
UpdateTime = s.UpdateTime.ToString("yyyy-MM-dd")
});
return(new { total, rows = temp.ToArray() });
}
private bool FinishInBillTask(Task task)
{
var inAllot = InBillAllotRepository.GetQueryable()
.Where(i => i.BillNo == task.OrderID &&
i.ID == task.AllotID &&
i.Status == "1")
.FirstOrDefault();
if (inAllot != null &&
(inAllot.InBillMaster.Status == "4" ||
inAllot.InBillMaster.Status == "5"
))
{
decimal quantity = inAllot.AllotQuantity;
if (string.IsNullOrEmpty(inAllot.Storage.LockTag) &&
inAllot.AllotQuantity >= quantity &&
inAllot.Storage.InFrozenQuantity >= quantity)
{
inAllot.Status = "2";
inAllot.Storage.Rfid = "";
inAllot.RealQuantity += quantity;
inAllot.Storage.Quantity += quantity;
inAllot.Storage.StorageTime = DateTime.Now;
inAllot.Storage.InFrozenQuantity -= quantity;
inAllot.InBillDetail.RealQuantity += quantity;
inAllot.InBillMaster.Status = "5";
inAllot.FinishTime = DateTime.Now;
if (inAllot.InBillMaster.InBillAllots.All(c => c.Status == "2"))
{
inAllot.InBillMaster.Status = "6";
}
InBillAllotRepository.SaveChanges();
return(true);
}
else
{
//"需确认入库的数据别人在操作或完成的数量不对，完成出错！";
return(false);
}
}
else
{
//"需确认入库的数据查询为空或者主单状态不对，完成出错！";
return(false);
}
}
public object GetInBillMaster()
{
string str = "";
var inBillAllot = InBillAllotRepository.GetQueryable().Where(i => i.Status != "2").Select(b => b.BillNo).ToArray();
for (int i = 0; i < inBillAllot.Length; i++)
{
str += inBillAllot[i];
}
var inBillMaster = InBillMasterRepository.GetQueryable().ToArray().Where(i => str.Contains(i.BillNo) && i.Status != "6")
.Distinct()
.OrderByDescending(t => t.BillDate)
.Select(i => new
{
BillNo = i.BillNo
});
return(inBillMaster);
}
/// <summary>
/// 根据参数生成并保存盘点数据 --异动生成
/// </summary>
/// <param name="beginDate">开始时间</param>
/// <param name="endDate">结束时间</param>
/// <param name="UserName">登陆用户</param>
/// <returns></returns>
public bool ChangedAdd(string beginDate, string endDate, string UserName, string billType, out string info)
{
bool result = false;
info = string.Empty;
IQueryable <Warehouse> wareQuery = WarehouseRepository.GetQueryable();
IQueryable <Storage> storageQuery = StorageRepository.GetQueryable();
IQueryable <InBillAllot> inAllotQuery = InBillAllotRepository.GetQueryable();
IQueryable <OutBillAllot> outAllotQuery = OutBillAllotRepository.GetQueryable();
IQueryable <MoveBillDetail> moveBillQuery = MoveBillDetailRepository.GetQueryable();
var employee = EmployeeRepository.GetQueryable().FirstOrDefault(e => e.UserName == UserName);
if (employee != null)
{
if (beginDate == string.Empty || beginDate == null)
{
beginDate = DateTime.Now.AddDays(-3).ToString("yyyy-MM-dd");
}
if (endDate == string.Empty || endDate == null)
{
endDate = DateTime.Now.ToString("yyyy-MM-dd");
}
DateTime begin = Convert.ToDateTime(beginDate);
DateTime end = Convert.ToDateTime(endDate);
try
{
using (var scope = new TransactionScope())
{
Console.WriteLine("start");
var sw = new Stopwatch();
sw.Start();
#region 循环所有仓库的订单，一个仓库一个盘点单据
var warehouses = wareQuery.OrderBy(w => w.WarehouseCode);
foreach (var item in warehouses.ToArray())
{
var inCells = inAllotQuery.Where(i => i.FinishTime >= begin && i.FinishTime <= end && i.Cell.WarehouseCode == item.WarehouseCode).OrderBy(i => i.CellCode).Select(i => i.CellCode);
var outCells = outAllotQuery.Where(o => o.FinishTime >= begin && o.FinishTime <= end && o.Cell.WarehouseCode == item.WarehouseCode).OrderBy(o => o.CellCode).Select(o => o.CellCode);
var moveInCells = moveBillQuery.Where(m => m.FinishTime >= begin && m.FinishTime <= end && m.InCell.WarehouseCode == item.WarehouseCode).OrderBy(m => m.InCell.CellCode).Select(m => m.InCell.CellCode);
var moveOutCells = moveBillQuery.Where(m => m.FinishTime >= begin && m.FinishTime <= end && m.OutCell.WarehouseCode == item.WarehouseCode).OrderBy(m => m.OutCell.CellCode).Select(m => m.OutCell.CellCode);
var storages = storageQuery.Where(s => s.Quantity > 0 && s.IsLock == "0" && (inCells.Any(i => i == s.CellCode) || outCells.Any(o => o == s.CellCode) || moveInCells.Any(mi => mi == s.CellCode) || moveOutCells.Any(mo => mo == s.CellCode)))
.OrderBy(s => s.ProductCode)
.Select(s => s).ToArray();
if (storages.Any())
{
string billNo = GetCheckBillNo().ToString();
var check = new CheckBillMaster();
check.BillNo = billNo;
check.BillDate = DateTime.Now;
check.BillTypeCode = billType;
check.WarehouseCode = item.WarehouseCode;
check.OperatePersonID = employee.ID;
check.Status = "1";
check.IsActive = "1";
check.UpdateTime = DateTime.Now;
CheckBillMasterRepository.Add(check);
storages.AsParallel().ForAll(stor =>
{
var checkDetail = new CheckBillDetail();
checkDetail.CellCode = stor.CellCode;
checkDetail.StorageCode = stor.StorageCode;
checkDetail.ProductCode = stor.ProductCode;
checkDetail.UnitCode = stor.Product.UnitCode;
checkDetail.Quantity = stor.Quantity;
checkDetail.RealProductCode = stor.ProductCode;
checkDetail.RealUnitCode = stor.Product.UnitCode;
checkDetail.RealQuantity = stor.Quantity;
checkDetail.Status = "0";
lock (check.CheckBillDetails)
{
check.CheckBillDetails.Add(checkDetail);
}
stor.IsLock = "1";
});
result = true;
}
else
{
info = "所选择查询的时间无数据！";
}
}
CheckBillMasterRepository.SaveChanges();
#endregion
sw.Stop();
Console.WriteLine(sw.ElapsedMilliseconds);
sw.Restart();
CheckBillMasterRepository.SaveChanges();
sw.Stop();
Console.WriteLine(sw.ElapsedMilliseconds);
scope.Complete();
}
}
catch (Exception e)
{
result = false;
info = e.Message;
}
}
return(result);
}
/// <summary>
/// 入库单据作业
/// </summary>
/// <param name="billNo">单据号</param>
/// <param name="errInfo">错误消息</param>
/// <returns></returns>
public bool InBillTask(string billNo, out string errorInfo)
{
bool result = true;
errorInfo = string.Empty;
try
{
//查询“起始位置参数”
var originPositionSystem = SystemParameterRepository.GetQueryable().FirstOrDefault(s => s.ParameterName == "IsDefaultProduct");
int paramterValue = Convert.ToInt32(originPositionSystem.ParameterValue);
//入库分配信息
var inBillAllot = InBillAllotRepository.GetQueryable().Where(i => i.BillNo == billNo);
if (inBillAllot.Any())
{
foreach (var inItem in inBillAllot.ToArray())
{
//根据“入库货位编码”查找“目标货位位置”
var targetCellPosition = CellPositionRepository.GetQueryable().FirstOrDefault(c => c.CellCode == inItem.CellCode);
if (targetCellPosition != null)
{
//根据“起始位置参数”查找“起始位置信息”
var originPosition = PositionRepository.GetQueryable().FirstOrDefault(p => p.ID == paramterValue);
if (originPosition != null)
{
//根据“货位位置中的入库位置ID”查找“目标位置信息”
var targetPosition = PositionRepository.GetQueryable().FirstOrDefault(p => p.ID == targetCellPosition.StockInPositionID);
if (targetPosition != null)
{
//根据“入库（目标和起始）位置信息的区域ID”查找“路径信息”
var path = PathRepository.GetQueryable().FirstOrDefault(p => p.OriginRegionID == originPosition.RegionID && p.TargetRegionID == targetPosition.RegionID);
if (path != null)
{
var inTask = new Task();
inTask.TaskType = "01";
inTask.TaskLevel = 0;
inTask.PathID = path.ID;
inTask.ProductCode = inItem.Product.ProductCode;
inTask.ProductName = inItem.Product.ProductName;
inTask.OriginStorageCode = inItem.CellCode;
inTask.TargetStorageCode = inItem.CellCode;
inTask.OriginPositionID = Convert.ToInt32(originPositionSystem.Id);
inTask.TargetPositionID = targetPosition.ID;
inTask.CurrentPositionID = Convert.ToInt32(originPositionSystem.Id);
inTask.CurrentPositionState = "01";
inTask.State = "04";
inTask.TagState = "01";
inTask.Quantity = Convert.ToInt32(inItem.RealQuantity);
inTask.TaskQuantity = Convert.ToInt32(inItem.RealQuantity);
inTask.OperateQuantity = Convert.ToInt32(inItem.AllotQuantity);
inTask.OrderID = inItem.BillNo;
inTask.OrderType = "01";
inTask.AllotID = inItem.ID;
inTask.DownloadState = "0";
TaskRepository.Add(inTask);
}
else
{
errorInfo = "未找到【路径信息】起始位置ID：" + originPosition.RegionID + "，目标位置ID：" + targetPosition.RegionID;
result = false;
}
}
else
{
errorInfo = "未找到【位置信息】目标货位位置ID：" + targetCellPosition.StockInPositionID;
result = false;
}
}
else
{
errorInfo = "请检查【系统参数】设置！";
result = false;
}
}
else
{
errorInfo = "未找到【货位位置】入库货位编码：" + inItem.CellCode;
result = false;
}
}
TaskRepository.SaveChanges();
}
else
{
errorInfo = "当前选择订单没有分配数据，请重新选择！";
}
}
catch (Exception e)
{
errorInfo = e.Message;
result = false;
}
return(result);
}
public object GetCellDetails(int page, int rows, string beginDate, string endDate, string type, string id)
{
IQueryable <InBillAllot> InBillAllotQuery = InBillAllotRepository.GetQueryable();
IQueryable <OutBillAllot> OutBillAllotQuery = OutBillAllotRepository.GetQueryable();
IQueryable <MoveBillDetail> MoveBillDetailQuery = MoveBillDetailRepository.GetQueryable();
IQueryable <ProfitLossBillDetail> ProfitLossBillDetailQuery = ProfitLossBillDetailRepository.GetQueryable();
if (type == "cell")
{
var CellHistoryAllQuery = InBillAllotQuery.Where(i => i.Status == "2" && i.CellCode == id)
.Select(i => new
{
i.CellCode,
i.Cell.CellName,
i.InBillMaster.BillDate,
i.InBillMaster.BillType.BillTypeName,
i.InBillMaster.BillNo,
i.ProductCode,
i.Product.ProductName,
i.Unit.UnitName,
i.RealQuantity,
i.Unit.Count,
IfMove = "0"
}).Union(OutBillAllotQuery.Where(o => o.Status == "2" && o.CellCode == id)
.Select(o => new
{
o.CellCode,
o.Cell.CellName,
o.OutBillMaster.BillDate,
o.OutBillMaster.BillType.BillTypeName,
o.OutBillMaster.BillNo,
o.ProductCode,
o.Product.ProductName,
o.Unit.UnitName,
o.RealQuantity,
o.Unit.Count,
IfMove = "0"
})).Union(MoveBillDetailQuery.Where(m => m.Status == "2" && (m.InCellCode == id || m.OutCellCode == id))
.Select(m => new
{
CellCode = m.InCellCode == id ? m.InCellCode : m.OutCellCode,
CellName = m.InCellCode == id ? m.InCell.CellName : m.OutCell.CellName,
m.MoveBillMaster.BillDate,
m.MoveBillMaster.BillType.BillTypeName,
m.MoveBillMaster.BillNo,
m.ProductCode,
m.Product.ProductName,
m.Unit.UnitName,
m.RealQuantity,
m.Unit.Count,
IfMove = m.InCellCode == id ? "1":"2"
}
)).Union(ProfitLossBillDetailQuery.Where(p => p.ProfitLossBillMaster.Status == "3" && p.CellCode == id)
.Select(p => new
{
p.CellCode,
p.Storage.Cell.CellName,
p.ProfitLossBillMaster.BillDate,
p.ProfitLossBillMaster.BillType.BillTypeName,
p.ProfitLossBillMaster.BillNo,
p.ProductCode,
p.Product.ProductName,
p.Unit.UnitName,
RealQuantity = p.Quantity,
p.Unit.Count,
IfMove = "0"
}));
if (!beginDate.Equals(string.Empty) && beginDate != "null01")
{
DateTime begin = Convert.ToDateTime(beginDate);
CellHistoryAllQuery = CellHistoryAllQuery.Where(c => c.BillDate >= begin);
}
//else
//{
// DateTime begin = DateTime.Now.AddDays(-30);
// CellHistoryAllQuery = CellHistoryAllQuery.Where(i => i.BillDate >= begin);
//}
if (!endDate.Equals(string.Empty) && endDate != "null02")//null02 同null01 代表传回来的日期是空值
{
DateTime end = Convert.ToDateTime(endDate);
CellHistoryAllQuery = CellHistoryAllQuery.Where(c => c.BillDate <= end);
}
CellHistoryAllQuery = CellHistoryAllQuery.OrderByDescending(c => c.BillDate).Where(c => c.RealQuantity > 0);
int total = CellHistoryAllQuery.Count();
CellHistoryAllQuery = CellHistoryAllQuery.Skip((page - 1) * rows).Take(rows);
var CellHistoryAll = CellHistoryAllQuery.ToArray().ToArray().Select(c => new
{
c.CellCode,
c.CellName,
BillDate = c.BillDate.ToString("yyyy-MM-dd"),
BillTypeName = c.IfMove == "0" ? c.BillTypeName.ToString() : c.IfMove == "1" ? c.BillTypeName.ToString() + "-移入" : c.BillTypeName.ToString() + "-移出",
c.BillNo,
c.ProductCode,
c.ProductName,
c.UnitName,
Quantity = c.RealQuantity / c.Count
});
return(new { total, rows = CellHistoryAll.ToArray() });
}
return(null);
}
public System.Data.DataTable GetCellHistory(int page, int rows, string beginDate, string endDate, string type, string id)
{
IQueryable <InBillAllot> InBillAllotQuery = InBillAllotRepository.GetQueryable();
IQueryable <OutBillAllot> OutBillAllotQuery = OutBillAllotRepository.GetQueryable();
IQueryable <MoveBillDetail> MoveBillDetailQuery = MoveBillDetailRepository.GetQueryable();
IQueryable <ProfitLossBillDetail> ProfitLossBillDetailQuery = ProfitLossBillDetailRepository.GetQueryable();
if (type == "cell")
{
var CellHistoryAllQuery = InBillAllotQuery.Where(i => i.Status == "2" && i.CellCode == id)
.Select(i => new
{
i.CellCode,
i.Cell.CellName,
i.InBillMaster.BillDate,
i.InBillMaster.BillType.BillTypeName,
i.InBillMaster.BillNo,
i.ProductCode,
i.Product.ProductName,
i.Unit.UnitName,
i.RealQuantity,
i.Unit.Count,
IfMove = "0"
}).Union(OutBillAllotQuery.Where(o => o.Status == "2" && o.CellCode == id)
.Select(o => new
{
o.CellCode,
o.Cell.CellName,
o.OutBillMaster.BillDate,
o.OutBillMaster.BillType.BillTypeName,
o.OutBillMaster.BillNo,
o.ProductCode,
o.Product.ProductName,
o.Unit.UnitName,
o.RealQuantity,
o.Unit.Count,
IfMove = "0"
})).Union(MoveBillDetailQuery.Where(m => m.Status == "2" && (m.InCellCode == id || m.OutCellCode == id))
.Select(m => new
{
CellCode = m.InCellCode == id ? m.InCellCode : m.OutCellCode,
CellName = m.InCellCode == id ? m.InCell.CellName : m.OutCell.CellName,
m.MoveBillMaster.BillDate,
m.MoveBillMaster.BillType.BillTypeName,
m.MoveBillMaster.BillNo,
m.ProductCode,
m.Product.ProductName,
m.Unit.UnitName,
m.RealQuantity,
m.Unit.Count,
IfMove = m.InCellCode == id ? "1" : "2"
}
)).Union(ProfitLossBillDetailQuery.Where(p => p.ProfitLossBillMaster.Status == "3" && p.CellCode == id)
.Select(p => new
{
p.CellCode,
p.Storage.Cell.CellName,
p.ProfitLossBillMaster.BillDate,
p.ProfitLossBillMaster.BillType.BillTypeName,
p.ProfitLossBillMaster.BillNo,
p.ProductCode,
p.Product.ProductName,
p.Unit.UnitName,
RealQuantity = p.Quantity,
p.Unit.Count,
IfMove = "0"
}));
if (!beginDate.Equals(string.Empty) && beginDate != "null01")
{
DateTime begin = Convert.ToDateTime(beginDate);
CellHistoryAllQuery = CellHistoryAllQuery.Where(c => c.BillDate >= begin);
}
if (!endDate.Equals(string.Empty) && endDate != "null02") //null02 同null01 代表传回来的日期是空值
{
DateTime end = Convert.ToDateTime(endDate);
CellHistoryAllQuery = CellHistoryAllQuery.Where(c => c.BillDate <= end);
}
CellHistoryAllQuery = CellHistoryAllQuery.OrderByDescending(c => c.BillDate).Where(c => c.RealQuantity > 0);
var CellHistoryAll = CellHistoryAllQuery.ToArray().ToArray().Select(c => new
{
c.CellCode,
c.CellName,
BillDate = c.BillDate.ToString("yyyy-MM-dd"),
BillTypeName = c.IfMove == "0" ? c.BillTypeName.ToString() : c.IfMove == "1" ? c.BillTypeName.ToString() + "-移入" : c.BillTypeName.ToString() + "-移出",
c.BillNo,
c.ProductCode,
c.ProductName,
c.UnitName,
Quantity = c.RealQuantity / c.Count
});
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));
foreach (var item in CellHistoryAll)
{
dt.Rows.Add
(
item.CellName,
item.BillDate,
item.BillTypeName,
item.BillNo,
item.ProductCode,
item.ProductName,
item.Quantity,
item.UnitName
);
}
return(dt);
}
return(null);
}
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 bool AllotCancel(string billNo, out string strResult)
{
Locker.LockKey = billNo;
bool result = false;
var ibm = InBillMasterRepository.GetQueryable()
.FirstOrDefault(i => i.BillNo == billNo &&
i.Status == "3");
if (ibm != null)
{
if (string.IsNullOrEmpty(ibm.LockTag))
{
try
{
using (var scope = new TransactionScope())
{
var inAllot = InBillAllotRepository.GetQueryable()
.Where(o => o.BillNo == ibm.BillNo)
.ToArray();
var storages = inAllot.Select(i => i.Storage).ToArray();
if (!Locker.Lock(storages))
{
strResult = "锁定储位失败，储位其他人正在操作，无法取消分配请稍候重试！";
return(false);
}
inAllot.AsParallel().ForAll(
(Action <InBillAllot>) delegate(InBillAllot i)
{
if (i.Storage.ProductCode == i.ProductCode &&
i.Storage.InFrozenQuantity >= i.AllotQuantity)
{
lock (i.InBillDetail)
{
i.InBillDetail.AllotQuantity -= i.AllotQuantity;
}
i.Storage.InFrozenQuantity -= i.AllotQuantity;
i.Storage.LockTag = string.Empty;
}
else
{
throw new Exception("储位的卷烟或入库冻结量与当前分配不符，信息可能被异常修改，不能取消当前入库分配！");
}
}
);
InBillAllotRepository.SaveChanges();
InBillAllotRepository.GetObjectSet()
.DeleteEntity(i => i.BillNo == ibm.BillNo);
//InBillAllotRepository.GetObjectQuery()
// .DeleteAll(i => i.BillNo == ibm.BillNo,null);
ibm.Status = "2";
ibm.UpdateTime = DateTime.Now;
InBillMasterRepository.SaveChanges();
result = true;
strResult = "取消分配成功！";
scope.Complete();
}
}
catch (Exception e)
{
strResult = "取消分配失败，详情：" + e.Message;
}
}
else
{
strResult = "当前订单其他人正在操作，请稍候重试！";
}
}
else
{
strResult = "当前订单状态不是已分配，或当前订单不存在！";
}
return(result);
}
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);
}