public void StockIn(OutInOrder entity)
{
if (entity == null)
{
throw new FriendlyException("单据不存在");
}
if (entity.Items.Count() == 0)
{
throw new FriendlyException("单据明细为空");
}
//记录库存批次
var productIdArray = entity.Items.Select(n => n.ProductId).ToArray();
var inventorys = _db.Table.FindAll <StoreInventory>("select * from storeinventory where productId in @ProductIds and StoreId=@StoreId", new { ProductIds = productIdArray, StoreId = entity.StoreId });
var inventoryBatchs = new List <StoreInventoryBatch>();
var inventoryHistorys = new List <StoreInventoryHistory>();
var batchNo = _sequenceService.GenerateBatchNo(entity.StoreId);
foreach (var item in entity.Items)
{
if (item.Quantity == 0)
{
continue;
}
var inventory = inventorys.FirstOrDefault(n => n.ProductId == item.ProductId);
if (inventory == null)
{
throw new FriendlyException(string.Format("商品{0}不存在", item.ProductId));
}
var inventoryQuantity = inventory.Quantity;
inventory.Quantity += item.Quantity;
inventory.SaleQuantity += item.Quantity;
inventory.AvgCostPrice = CalculatedAveragePrice(inventory.AvgCostPrice, inventoryQuantity, item.CostPrice, item.Quantity); // 修改库存均价
if (inventory.LastCostPrice == 0)
{
inventory.LastCostPrice = item.CostPrice > 0?item.CostPrice:item.LastCostPrice;
}
//记录库存流水
var history = new StoreInventoryHistory(item.ProductId, entity.StoreId, inventoryQuantity, item.Quantity,
item.CostPrice, batchNo, entity.Id, entity.Code, BillIdentity.OtherInOrder, entity.UpdatedBy, entity.SupplierId);
inventoryHistorys.Add(history);
//记录库存批次
var batchQuantity = CalculatedBatchQuantity(inventoryQuantity, item.Quantity);
var batch = new StoreInventoryBatch(item.ProductId, entity.StoreId, entity.SupplierId, batchQuantity,
item.LastCostPrice, item.CostPrice, batchNo, null, 0, entity.UpdatedBy);
inventoryBatchs.Add(batch);
}
_db.Update(entity.Items.ToArray());
_db.Update(inventorys.ToArray());
_db.Insert(inventoryHistorys.ToArray());
_db.Insert(inventoryBatchs.ToArray());
}
/// <summary>
/// 销售退单，入库
/// </summary>
/// <param name="entity"></param>
public void StockInRefundOrder(SaleOrder entity)
{
if (entity == null)
{
throw new FriendlyException("单据不存在");
}
if (entity.Items.Count() == 0)
{
throw new FriendlyException("单据明细为空");
}
//记录库存批次
var productIdArray = entity.Items.Select(n => n.ProductId).ToArray();
var inventorys = _db.Table.FindAll <StoreInventory>("select * from storeinventory where productId in @ProductIds and StoreId=@StoreId", new { ProductIds = productIdArray, StoreId = entity.StoreId });
var inventoryBatchs = new List <StoreInventoryBatch>();
var inventoryHistorys = new List <StoreInventoryHistory>();
var batchNo = _sequenceService.GenerateBatchNo(entity.StoreId);
foreach (var item in entity.Items)
{
var inventory = inventorys.FirstOrDefault(n => n.ProductId == item.ProductId);
if (inventory == null)
{
throw new FriendlyException(string.Format("商品{0}不存在", item.ProductId));
}
var returnQuantity = Math.Abs(item.Quantity); // 销售退单的数量，前端用负数记录
// 退货商品按照最新的合同进行入库，查询商品合同价
var contract = _db.Table.Find <PurchaseContract>(@"SELECT c.Id,c.SupplierId from purchasecontract c inner join purchasecontractitem i on c.id = i.PurchaseContractId where FIND_IN_SET(@StoreId,c.StoreIds) and c.`Status`=3 and i.ProductId = @productId order by c.Id desc", new { StoreId = entity.StoreId, ProductId = item.ProductId });
var contractItem = _db.Table.Find <PurchaseContractItem>("select * from purchasecontractitem where PurchaseContractId=@PurchaseContractId and ProductId=@ProductId", new { PurchaseContractId = contract.Id, ProductId = item.ProductId });
// 入库
var inventoryQuantity = inventory.Quantity;
inventory.Quantity += returnQuantity;
inventory.SaleQuantity += returnQuantity;
inventory.AvgCostPrice = CalculatedAveragePrice(inventory.AvgCostPrice, inventoryQuantity, contractItem.ContractPrice, returnQuantity);
//记录库存流水
var history = new StoreInventoryHistory(item.ProductId, entity.StoreId, inventoryQuantity, returnQuantity,
contractItem.ContractPrice, batchNo, entity.Id, entity.Code, BillIdentity.SaleRefund, entity.UpdatedBy, entity.UpdatedOn, contract.SupplierId, item.RealPrice);
inventoryHistorys.Add(history);
//记录库存批次
var batchQuantity = CalculatedBatchQuantity(inventoryQuantity, returnQuantity);
var batch = new StoreInventoryBatch(item.ProductId, entity.StoreId, contract.SupplierId, batchQuantity,
contractItem.ContractPrice, contractItem.ContractPrice, batchNo, null, 0, entity.UpdatedBy);
inventoryBatchs.Add(batch);
// 更新订单成本
item.AvgCostPrice = inventory.AvgCostPrice;
}
_db.Update(inventorys.ToArray());
_db.Insert(inventoryHistorys.ToArray());
_db.Insert(inventoryBatchs.ToArray());
_db.Update(entity.Items.ToArray()); // 更新订单成本
}
public List <StoreInventoryBatch> SaveBatch(StorePurchaseOrder entity)
{
var inventoryBatchs = new List <StoreInventoryBatch>();
var batchNo = _sequenceService.GenerateBatchNo(entity.StoreId);
foreach (var item in entity.Items)
{
//批次
item.BatchNo = batchNo;
var batch = new StoreInventoryBatch(item.ProductId, entity.StoreId, entity.SupplierId, item.ActualQuantity,
item.ContractPrice, item.Price, item.BatchNo, item.ProductionDate, item.ShelfLife, entity.StoragedBy);
inventoryBatchs.Add(batch);
}
return(inventoryBatchs);
}
public void FixedInventory(StocktakingPlan entity)
{
if (entity == null)
{
throw new FriendlyException("单据不存在");
}
if (entity.Items.Count() == 0)
{
throw new FriendlyException("单据明细为空");
}
//小盘不结转入库
if (entity.Method == StocktakingPlanMethod.SmallCap)
{
return;
}
//过滤掉盘点没有差异的产品
var differenceItems = entity.Items.Where(p => p.GetDifferenceQuantity() != 0).ToList();
if (differenceItems.Count == 0)
{
return; //没有差异商品，直接结束
}
var productIdArray = differenceItems.Select(n => n.ProductId).ToArray();
var inventorys = _db.Table.FindAll <StoreInventory>("select * from storeinventory where storeId=@StoreId and productId in @ProductIds", new { StoreId = entity.StoreId, ProductIds = productIdArray });
var inventoryBatchs = _db.Table.FindAll <StoreInventoryBatch>("select * from storeinventorybatch where storeId=@StoreId and productId in @ProductIds and Quantity>0", new { StoreId = entity.StoreId, ProductIds = productIdArray });
var inventoryHistorys = new List <StoreInventoryHistory>();
var inventoryLoss = new List <StoreInventoryBatch>(); //盘亏批次更新
var inventoryProfit = new List <StoreInventoryBatch>(); //盘盈批次数据
long batchNo = 0;
foreach (var item in differenceItems)
{
var inventory = inventorys.FirstOrDefault(n => n.ProductId == item.ProductId);
if (inventory == null)
{
var product = _db.Table.Find <Product>(item.ProductId);
throw new FriendlyException(string.Format("库存商品[{0}]{1}不存在！", product.Code, product.Name));
}
var inventoryQuantity = inventory.Quantity;
// 盘点差异数量，盘盈为整数，盘亏为负数
var differenceQuantity = item.GetDifferenceQuantity();
inventory.Quantity += differenceQuantity;
inventory.SaleQuantity += differenceQuantity;
//按盘盈，盘亏记录库存历史和批次
if (differenceQuantity > 0)
{
// 查询商品合同价
var contract = _db.Table.Find <PurchaseContract>(@"SELECT c.Id,c.SupplierId from purchasecontract c inner join purchasecontractitem i on c.id = i.PurchaseContractId where FIND_IN_SET(@StoreId,c.StoreIds) and c.`Status`=3 and i.ProductId = @productId order by c.Id desc", new { StoreId = entity.StoreId, ProductId = inventory.ProductId });
var contractItem = _db.Table.Find <PurchaseContractItem>("select * from purchasecontractitem where PurchaseContractId=@PurchaseContractId and ProductId=@ProductId", new { PurchaseContractId = contract.Id, ProductId = inventory.ProductId });
//重新计算均价成本
inventory.AvgCostPrice = CalculatedAveragePrice(inventory.AvgCostPrice, inventoryQuantity, contractItem.ContractPrice, differenceQuantity);
if (batchNo == 0)
{
batchNo = _sequenceService.GenerateBatchNo(entity.StoreId);
}
//入库批次
var batchQuantity = CalculatedBatchQuantity(inventoryQuantity, differenceQuantity);
var batch = new StoreInventoryBatch(inventory.ProductId, entity.StoreId, contract.SupplierId, batchQuantity,
contractItem.ContractPrice, contractItem.ContractPrice, batchNo, null, 0, entity.UpdatedBy);
inventoryProfit.Add(batch);
//入库历史记录
var history = new StoreInventoryHistory(inventory.ProductId, entity.StoreId, inventoryQuantity, differenceQuantity,
contractItem.ContractPrice, batchNo, entity.Id, entity.Code, ValueObject.BillIdentity.StoreStocktakingPlan, entity.UpdatedBy, entity.UpdatedOn, contract.SupplierId);
inventoryHistorys.Add(history);
}
else
{
// 盘亏
var leftQuantity = Math.Abs(differenceQuantity);
var productBatchs = inventoryBatchs.Where(n => n.ProductId == inventory.ProductId).OrderBy(n => n.BatchNo);
foreach (var batchItem in productBatchs)
{
if (batchItem.Quantity >= leftQuantity)
{
batchItem.Quantity -= leftQuantity;
inventoryLoss.Add(batchItem);
//记录修改历史
inventoryHistorys.Add(new StoreInventoryHistory(inventory.ProductId, entity.StoreId, inventoryQuantity, -leftQuantity,
batchItem.Price, batchItem.BatchNo, entity.Id, entity.Code, BillIdentity.StoreStocktakingPlan, entity.UpdatedBy, entity.UpdatedOn, batchItem.SupplierId));
leftQuantity = 0;
break;
}
else
{
inventoryHistorys.Add(new StoreInventoryHistory(inventory.ProductId, entity.StoreId, inventoryQuantity, -batchItem.Quantity,
batchItem.Price, batchItem.BatchNo, entity.Id, entity.Code, BillIdentity.StoreStocktakingPlan, entity.UpdatedBy, entity.UpdatedOn, batchItem.SupplierId));
// 剩余扣减数
inventoryQuantity = inventoryQuantity - batchItem.Quantity; // 第1+N次扣减后总库存
leftQuantity = leftQuantity - batchItem.Quantity;
batchItem.Quantity = 0;
inventoryLoss.Add(batchItem);
}
}
//当扣减批次后，剩余数量依然大于0，存在两种情况：1 无批次数据，2 批次数量不够扣
if (leftQuantity > 0)
{
//用最新的合同商品价来记录
var contract = _db.Table.Find <PurchaseContract>(@"SELECT c.Id,c.SupplierId from purchasecontract c inner join purchasecontractitem i on c.id = i.PurchaseContractId where FIND_IN_SET(@StoreId,c.StoreIds) and c.`Status`=3 and i.ProductId = @productId order by c.Id desc", new { StoreId = entity.StoreId, ProductId = inventory.ProductId });
var contractItem = _db.Table.Find <PurchaseContractItem>("select * from purchasecontractitem where PurchaseContractId=@PurchaseContractId and ProductId=@ProductId", new { PurchaseContractId = contract.Id, ProductId = inventory.ProductId });
inventoryHistorys.Add(new StoreInventoryHistory(inventory.ProductId, entity.StoreId, inventoryQuantity, -leftQuantity,
contractItem.ContractPrice, 0, entity.Id, entity.Code, BillIdentity.StoreStocktakingPlan, entity.CreatedBy, entity.UpdatedOn, contract.SupplierId));
// 第1+N次扣减后总库存
inventoryQuantity = inventoryQuantity - leftQuantity;
}
}
}
if (inventoryProfit.Count > 0)
{
_db.Insert(inventoryProfit.ToArray()); //盘盈批次
}
if (inventoryLoss.Count > 0)
{
_db.Update(inventoryLoss.ToArray());
}
_db.Update(inventorys.ToArray());
_db.Insert(inventoryHistorys.ToArray());
}
/// <summary>
/// 调拨单增减库存
/// </summary>
/// <param name="entity"></param>
public void TransaferInventory(TransferOrder entity)
{
if (entity == null)
{
throw new FriendlyException("单据不存在");
}
if (entity.Items.Count() == 0)
{
throw new FriendlyException("单据明细为空");
}
var productIdArray = entity.Items.Select(n => n.ProductId).ToArray();
//出库商品
var fromInventorys = _db.Table.FindAll <StoreInventory>("select * from storeinventory where productId in @ProductIds and StoreId=@StoreId", new { ProductIds = productIdArray, StoreId = entity.FromStoreId });
var fromInventoryBatchs = _db.Table.FindAll <StoreInventoryBatch>("select * from storeinventorybatch where storeId=@StoreId and productId in @ProductIds and Quantity>0 ", new { StoreId = entity.FromStoreId, ProductIds = productIdArray });
//入库商品
var toInventorys = _db.Table.FindAll <StoreInventory>("select * from storeinventory where productId in @ProductIds and StoreId=@StoreId", new { ProductIds = productIdArray, StoreId = entity.ToStoreId });
// 出库是按照先进先出扣减库存批次，入库同样按照对应批次价格入库；不按照单据进价入库
var inventoryHistorys = new List <StoreInventoryHistory>();
var inventoryBatchUpdates = new List <StoreInventoryBatch>(); //批次更新
var inventoryBatchInserts = new List <StoreInventoryBatch>(); //增加批次
var toBatchNo = _sequenceService.GenerateBatchNo(entity.ToStoreId); //统一入库批次号
foreach (var item in entity.Items)
{
var fromInventoryItem = fromInventorys.FirstOrDefault(n => n.ProductId == item.ProductId); //fromInventoryDic[item.ProductId];
if (fromInventoryItem == null)
{
throw new FriendlyException(string.Format("调出商品{0}不存在", item.ProductId));
}
var toInventoryItem = toInventorys.FirstOrDefault(n => n.ProductId == item.ProductId);
if (toInventoryItem == null)
{
throw new FriendlyException(string.Format("调入商品{0}不存在", item.ProductId));
}
if (item.Quantity == 0)
{
continue;
}
// 先检查总库存是否够扣减
if (fromInventoryItem.Quantity < item.Quantity)
{
var product = _db.Table.Find <Product>(item.ProductId);
throw new FriendlyException(string.Format("商品[{0}]{1}库存不足！", product.Code, product.Name));
}
// 出库总库存
var fromInventoryQuantity = fromInventoryItem.Quantity;
fromInventoryItem.Quantity -= item.Quantity;
fromInventoryItem.SaleQuantity -= item.Quantity;
//入库总库存
var toInventoryQuantity = toInventoryItem.Quantity;
toInventoryItem.Quantity += item.Quantity;
toInventoryItem.SaleQuantity += item.Quantity;
//把当前商品和它的所有批次重新排序：指定批次排第一，其他按照先进先出排序
var outInventoryBatchs = SortBatchByFIFO(fromInventoryBatchs.ToList(), item.ProductId, item.BatchNo);
var leftQuantity = item.Quantity;
foreach (var batchProduct in outInventoryBatchs)
{
//当前批次可调拨数量
var transaferQuantity = batchProduct.Quantity >= leftQuantity ? leftQuantity : batchProduct.Quantity;
// 出库
batchProduct.Quantity -= transaferQuantity;
inventoryBatchUpdates.Add(batchProduct);
var fromHistory = new StoreInventoryHistory(item.ProductId, entity.FromStoreId, fromInventoryQuantity, -transaferQuantity,
batchProduct.Price, batchProduct.BatchNo, entity.Id, entity.Code, BillIdentity.TransferOrder, entity.UpdatedBy, batchProduct.SupplierId);
inventoryHistorys.Add(fromHistory);
// 入库
// 记录入库价格, // 修改最后一次进价,如果是赠品，不修改价格
toInventoryItem.LastCostPrice = batchProduct.Price > 0 ? batchProduct.Price : toInventoryItem.LastCostPrice;
// 修改库存均价
toInventoryItem.AvgCostPrice = CalculatedAveragePrice(toInventoryItem.AvgCostPrice, toInventoryQuantity, batchProduct.Price, transaferQuantity);
//记录库存流水
var toHistory = new StoreInventoryHistory(item.ProductId, entity.ToStoreId, toInventoryQuantity, transaferQuantity,
batchProduct.Price, toBatchNo, entity.Id, entity.Code, BillIdentity.TransferOrder, entity.UpdatedBy, batchProduct.SupplierId);
inventoryHistorys.Add(toHistory);
// 入库批次时，先检查总库存是否为负库存，有负库存先抵扣负库存。抵扣后库存依然为0，入库批次数量为0，抵扣后有剩余，按剩余数记录入库批次库存
var batchQuantity = CalculatedBatchQuantity(toInventoryQuantity, transaferQuantity);
var batch = new StoreInventoryBatch(item.ProductId, entity.ToStoreId, batchProduct.SupplierId, batchQuantity,
batchProduct.ContractPrice, batchProduct.Price, toBatchNo, item.ProductionDate, item.ShelfLife, entity.UpdatedBy);
inventoryBatchInserts.Add(batch);
//总库存
fromInventoryQuantity = fromInventoryQuantity - transaferQuantity;
toInventoryQuantity = toInventoryQuantity + transaferQuantity;
//剩余还要扣减数
leftQuantity = leftQuantity - transaferQuantity;
if (leftQuantity == 0)
{
break; // 剩余库存扣减完毕，结束当前商品
}
}
}
//更新库存
_db.Update(fromInventorys.ToArray());
_db.Update(toInventorys.ToArray());
//更新批次
if (inventoryBatchUpdates.Count > 0)
{
_db.Update(inventoryBatchUpdates.ToArray());
}
_db.Insert(inventoryBatchInserts.ToArray());
_db.Insert(inventoryHistorys.ToArray());
}
