public void Insert(TransferCargoData TransferCargoData)
{
var sql = $@"insert into TransferCargoData(
Goods_ID,
Goods_Name,
Supplier_ID,
Supplier_Name,
OldWarehouse_ID,
OldWarehouse_Name,
OldQuantity,
NewWarehouse_ID,
NewWarehouse_Name,
NewQuantity,
Date)
VALUES (
@GoodsID,
@GoodsName,
@SupplierID,
@SupplierName,
@OldWarehouseID,
@OldWarehouseName,
@OldQuantity,
@NewWarehouseID,
@NewWarehouseName,
@NewQuantity,
@Date)";
_context.Execute(sql, new
{
GoodsID = TransferCargoData.GoodsID,
GoodsName = TransferCargoData.GoodsName,
SupplierID = TransferCargoData.SupplierID,
SupplierName = TransferCargoData.SupplierName,
OldWarehouseID = TransferCargoData.OldWarehouseID,
OldWarehouseName = TransferCargoData.OldWarehouseName,
OldQuantity = TransferCargoData.OldQuantity,
NewWarehouseID = TransferCargoData.NewWarehouseID,
NewWarehouseName = TransferCargoData.NewWarehouseName,
NewQuantity = TransferCargoData.NewQuantity,
Date = TransferCargoData.Date
});
}
public void Update(TransferCargoData TransferCargoData)
{
var sql = $@"update TransferCargoData set
Goods_ID=@GoodsID,
Goods_Name=@GoodsName,
Supplier_ID=@SupplierID,
Supplier_Name=@SupplierName,
OldWarehouse_ID=@OldWarehouseID,
OldWarehouse_Name=@OldWarehouseName,
OldQuantity=@OldQuantity,
NewWarehouse_ID=@NewWarehouseID,
NewWarehouse_Name=@NewWarehouseName,
NewQuantity=@NewQuantity,
Date=@Date
where ID=@ID";
_context.Execute(sql, new
{
GoodsID = TransferCargoData.GoodsID,
GoodsName = TransferCargoData.GoodsName,
SupplierID = TransferCargoData.SupplierID,
SupplierName = TransferCargoData.SupplierName,
OldWarehouseID = TransferCargoData.OldWarehouseID,
OldWarehouseName = TransferCargoData.OldWarehouseName,
OldQuantity = TransferCargoData.OldQuantity,
NewWarehouseID = TransferCargoData.NewWarehouseID,
NewWarehouseName = TransferCargoData.NewWarehouseName,
NewQuantity = TransferCargoData.NewQuantity,
Date = TransferCargoData.Date
});
}
public bool ClickTransferCargo(int id, int newNum, int newWarehouseID)
{
return(_dbHelper.ExecuteTransaction(
t =>
{
var inventoryData = _context.QuerySingleOrDefault <InventoryData>("select * from InventoryData where id = @id", new { id = id }, t);
//var inventoryData = GetById(id);
int?OldQuantity = inventoryData.InventoryQuantity;
//更改之前的库存信息
inventoryData.InventoryQuantity = inventoryData.InventoryQuantity - newNum;
inventoryData.InventorySum = inventoryData.CostPrice * inventoryData.InventoryQuantity;
inventoryData.ShipmentsQuantity = inventoryData.ShipmentsQuantity - newNum;
inventoryData.RemainingQuantity = inventoryData.RemainingQuantity - newNum;
//Update(inventoryData);
var sql = $@"update InventoryData set
Goods_ID=@GoodsID,
Goods_Name=@GoodsName,
Unit=@Unit,
Specification=@Specification,
GoodsType=@GoodsType,
Brand=@Brand,
InventoryQuantity=@InventoryQuantity,
CostPrice=@CostPrice,
InventorySum=@InventorySum,
LastInventoryDate=@LastInventoryDate,
FinalSaleDate=@FinalSaleDate,
PurchaseDate=@PurchaseDate,
ShipmentsDate=@ShipmentsDate,
Warehouse_ID=@WarehouseID,
Warehouse_Name=@WarehouseName,
Active=@Active,
ShipmentsQuantity=@ShipmentsQuantity,
RemainingQuantity=@RemainingQuantity
WHERE ID=@ID";
_context.Execute(sql, new
{
GoodsID = inventoryData.GoodsID,
GoodsName = inventoryData.GoodsName,
Unit = inventoryData.Unit,
Specification = inventoryData.Specification,
GoodsType = inventoryData.GoodsType,
Brand = inventoryData.Brand,
InventoryQuantity = inventoryData.InventoryQuantity,
CostPrice = inventoryData.CostPrice,
InventorySum = inventoryData.InventorySum,
LastInventoryDate = inventoryData.LastInventoryDate,
FinalSaleDate = inventoryData.FinalSaleDate,
PurchaseDate = inventoryData.PurchaseDate,
ShipmentsDate = inventoryData.ShipmentsDate,
WarehouseID = inventoryData.WarehouseID,
WarehouseName = inventoryData.WarehouseName,
Active = inventoryData.Active,
ShipmentsQuantity = inventoryData.ShipmentsQuantity,
RemainingQuantity = inventoryData.RemainingQuantity,
ID = inventoryData.ID
}, t);
//添加新的库存信息
//var newWarehouse = _warehouseService.GetById(newWarehouseID);
var newWarehouse = _context.QuerySingleOrDefault <Warehouse>("select * from Warehouse where id = @id", new { id = newWarehouseID }, t);
InventoryData newInventoryData = new InventoryData()
{
WarehouseID = newWarehouseID,
WarehouseName = newWarehouse.Name,
GoodsID = inventoryData.GoodsID,
GoodsName = inventoryData.GoodsName,
PurchaseDate = inventoryData.PurchaseDate,
ShipmentsDate = inventoryData.ShipmentsDate,
Unit = inventoryData.Unit,
Specification = inventoryData.Specification,
GoodsType = inventoryData.GoodsType,
Brand = inventoryData.Brand,
InventoryQuantity = newNum,
CostPrice = inventoryData.CostPrice,
InventorySum = inventoryData.CostPrice * newNum,
LastInventoryDate = inventoryData.LastInventoryDate,
FinalSaleDate = inventoryData.FinalSaleDate,
SupplierID = inventoryData.SupplierID,
SupplierName = inventoryData.SupplierName,
SupplierAddress = inventoryData.SupplierAddress,
Active = inventoryData.Active,
ShipmentsQuantity = inventoryData.ShipmentsQuantity - newNum,
RemainingQuantity = inventoryData.RemainingQuantity - newNum
};
//Insert(newInventoryData);
var sql1 = $@"insert into InventoryData(
Goods_ID,
Goods_Name,
Unit,
Specification,
GoodsType,
Brand,
InventoryQuantity,
CostPrice,
InventorySum,
LastInventoryDate,
FinalSaleDate,
Supplier_ID,
Supplier_Name,
Supplier_Address,
PurchaseDate,
ShipmentsDate,
Warehouse_ID,
Warehouse_Name,
Active,
ShipmentsQuantity,
RemainingQuantity)
VALUES (
@GoodsID,
@GoodsName,
@Unit,
@Specification,
@GoodsType,
@Brand,
@InventoryQuantity,
@CostPrice,
@InventorySum,
@LastInventoryDate,
@FinalSaleDate,
@SupplierID,
@SupplierName,
@SupplierAddress,
@PurchaseDate,
@ShipmentsDate,
@WarehouseID,
@WarehouseName,
@Active,
@ShipmentsQuantity,
@RemainingQuantity) select @@identity";
int newInventoryDataId = _context.QuerySingle <int>(sql1, new
{
GoodsID = newInventoryData.GoodsID,
GoodsName = newInventoryData.GoodsName,
Unit = newInventoryData.Unit,
Specification = newInventoryData.Specification,
GoodsType = newInventoryData.GoodsType,
Brand = newInventoryData.Brand,
InventoryQuantity = newInventoryData.InventoryQuantity,
CostPrice = newInventoryData.CostPrice,
InventorySum = newInventoryData.InventorySum,
LastInventoryDate = newInventoryData.LastInventoryDate,
FinalSaleDate = newInventoryData.FinalSaleDate,
SupplierID = newInventoryData.SupplierID,
SupplierName = newInventoryData.SupplierName,
SupplierAddress = newInventoryData.SupplierAddress,
PurchaseDate = newInventoryData.PurchaseDate,
ShipmentsDate = newInventoryData.ShipmentsDate,
WarehouseID = newInventoryData.WarehouseID,
WarehouseName = newInventoryData.WarehouseName,
Active = newInventoryData.Active,
ShipmentsQuantity = newInventoryData.ShipmentsQuantity,
RemainingQuantity = newInventoryData.RemainingQuantity
}, t);
//添加到调货信息一条记录
TransferCargoData transferCargoData = new TransferCargoData()
{
GoodsID = inventoryData.GoodsID,
GoodsName = inventoryData.GoodsName,
SupplierID = inventoryData.SupplierID,
SupplierName = inventoryData.SupplierName,
OldWarehouseID = inventoryData.WarehouseID,
OldWarehouseName = inventoryData.WarehouseName,
OldQuantity = OldQuantity,
NewWarehouseID = newWarehouseID,
NewWarehouseName = newWarehouse.Name,
NewQuantity = newNum,
Date = DateTime.Now,
InventoryDataID = newInventoryDataId,
OldInventoryDataID = inventoryData.ID
};
//_transferCargoDataService.Insert(transferCargoData);
var sql2 = $@"insert into TransferCargoData(
Goods_ID,
Goods_Name,
Supplier_ID,
Supplier_Name,
OldWarehouse_ID,
OldWarehouse_Name,
OldQuantity,
NewWarehouse_ID,
NewWarehouse_Name,
NewQuantity,
Date,
InventoryData_ID,
OldInventoryData_ID)
VALUES (
@GoodsID,
@GoodsName,
@SupplierID,
@SupplierName,
@OldWarehouseID,
@OldWarehouseName,
@OldQuantity,
@NewWarehouseID,
@NewWarehouseName,
@NewQuantity,
@Date,
@InventoryDataID,
@OldInventoryDataID)";
_context.Execute(sql2, new
{
GoodsID = transferCargoData.GoodsID,
GoodsName = transferCargoData.GoodsName,
SupplierID = transferCargoData.SupplierID,
SupplierName = transferCargoData.SupplierName,
OldWarehouseID = transferCargoData.OldWarehouseID,
OldWarehouseName = transferCargoData.OldWarehouseName,
OldQuantity = transferCargoData.OldQuantity,
NewWarehouseID = transferCargoData.NewWarehouseID,
NewWarehouseName = transferCargoData.NewWarehouseName,
NewQuantity = transferCargoData.NewQuantity,
Date = transferCargoData.Date,
InventoryDataID = transferCargoData.InventoryDataID,
OldInventoryDataID = transferCargoData.OldInventoryDataID
}, t);
}));
}
