public void FixBatchInventory()
{
IDBContext db = new DapperDBContext("masterDB");
// 查询 异常批次数据
var sql = @"select p.id,p.`Code`,p.BarCode,s.StoreId,s.Quantity,t.Quantity as bqty,c.Quantity as hqty from storeinventory s left JOIN
(select b.StoreId,b.ProductId,sum(b.Quantity) as Quantity from storeinventorybatch b group by b.StoreId,b.ProductId) t
on s.StoreId = t.StoreId and s.ProductId = t.ProductId
left join
(select h.StoreId,h.ProductId,sum( h.ChangeQuantity) as Quantity from storeinventoryhistory h GROUP BY h.StoreId,h.ProductId ) c
on s.StoreId = c.StoreId and s.ProductId = c.ProductId
left join product p on p.id = s.ProductId
where s.Quantity<>c.Quantity or c.Quantity<>t.Quantity or s.Quantity<>t.Quantity ";
var waitFixProducts = db.Table.FindAll <FixProduct>(sql, null);
foreach (var item in waitFixProducts)
{
if (item.Quantity <= 0)
{
//更新当前所有商品批次库存为0
if (item.Bqty > 0)
{
string sqlUpdate = "update storeinventorybatch set Quantity = 0 where StoreId=@StoreId and ProductId=@ProductId and Quantity>0";
db.Command.AddExecute(sqlUpdate, new { StoreId = item.StoreId, ProductId = item.Id });
}
}
else
{
// 把批次库存数修复成与总库存一致
var inventoryBatchs = db.Table.FindAll <StoreInventoryBatch>("select * from storeinventorybatch where storeId=@StoreId and productId = @ProductIds and Quantity>0", new { StoreId = item.StoreId, ProductIds = item.Id }).OrderBy(n => n.BatchNo).ToList();
if (item.Quantity < item.Bqty)
{
var updateList = MinusInventory(inventoryBatchs, item.Bqty - item.Quantity);
if (updateList.Count > 0)
{
db.Update(updateList.ToArray());
}
}
else
{
}
}
}
// db.SaveChange();
Assert.AreEqual(1, 1);
}
public void TestTimespan_MySql()
{
var strConn = ConfigurationManager.ConnectionStrings["ebsdb"].ConnectionString;
DapperDBContext db = new DapperDBContext("ebsdb");
var model = db.Table <temp_test>().FirstOrDefault(n => n.Id == 1);
var version1 = model.RowVersion;
model.Quantity += 100;
db.Update(model);
db.SaveChange();
var model2 = db.Table <temp_test>().FirstOrDefault(n => n.Id == 1);
Assert.AreNotEqual(version1, model2.RowVersion);
}