public void ValidateEndStatus(StocktakingPlan model)
{
if (model.Status != StocktakingPlanStatus.Replay)
{
throw new Exception("还没执行过合并盘点，不能结束。");
}
}
public void ValidatePlanDate(StocktakingPlan model)
{
if (_db.Table.Exists <StocktakingPlan>(n => n.StoreId == model.StoreId && n.StocktakingDate == model.StocktakingDate.Date))
{
throw new Exception("【" + model.StocktakingDate.ToString("yyyy-MM-dd") + "】已经存在一个盘点计划。");
}
}
/// <summary>
/// 修正库存
/// </summary>
public void FixedInventory(StocktakingPlan model)
{
//若是小盘，则只修改盘点计划表。不修改库存
if (model.Method == StocktakingPlanMethod.SmallCap)
{
return;
}
// 大盘：
}
public void ValidatePlan(StocktakingPlan model)
{
if (model.StoreId == 0)
{
throw new Exception("请选择门店。");
}
if (model.StocktakingDate < DateTime.Now.AddDays(-1))
{
throw new Exception("盘点日期不能在当前日期之前。");
}
}
public JsonResult QueryShelfProduct(int planId, int storeId, string productCodeOrBarCode)
{
var stocktakingPlan = new StocktakingPlan();
if (planId == 0)
{
stocktakingPlan = GetRunningPlan(storeId);
planId = stocktakingPlan.Id;
}
var model = _stocktakingQuery.QueryShelfProduct(planId, storeId, productCodeOrBarCode);
return(Json(new { success = true, data = model, plan = stocktakingPlan }));
}
public void AddInventoryItems(StocktakingPlan model)
{
string checkSql = "select count(*) from StocktakingPlan where StoreId=@StoreId and (Status=@ToBeInventory or Status=@Replay )";
//存在初盘和复盘的 计划，就不能开始一个新的盘点计划
if (_db.Table.Context.ExecuteScalar <int>(checkSql, new { StoreId = model.StoreId, ToBeInventory = StocktakingPlanStatus.FirstInventory, Replay = StocktakingPlanStatus.Replay }) > 0)
{
throw new Exception("有未完成的盘点计划，不能开启新盘点");
}
//把库存明细导入盘点表
string sql = @"insert into stocktakingPlanItem( StocktakingPlanId,ProductId,CostPrice,SalePrice,Quantity,CountQuantity )
SELECT s.Id,p.Id as ProductId ,i.LastCostPrice ,case when i.StoreSalePrice>0 then i.StoreSalePrice else p.SalePrice END as SalePrice ,i.Quantity ,0
FROM StoreInventory i
INNER JOIN StocktakingPlan s ON s.StoreId = i.StoreId
INNER JOIN Product p ON p.Id=i.ProductId
WHERE s.Id=@StocktakingPlanId and i.IsQuit=0";
_db.Command.AddExecute(sql, new { StocktakingPlanId = model.Id });
}
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());
}
