public BaseServiceResponse <Material> CreateMaterial(Material material)
{
try {
_db.Materials.Add(material);
var newStock = new MaterialStock
{
Material = material,
AvailableQuantity = 0,
IdealQuantity = 13
};
_db.MaterialStocks.Add(newStock);
_db.SaveChanges();
return(new BaseServiceResponse <Material>
{
Data = material,
Time = DateTime.UtcNow,
Message = "New Material Added!",
IsSuccess = true
});
}catch (Exception e) {
return(new BaseServiceResponse <Material>
{
Data = material,
Time = DateTime.UtcNow,
Message = e.StackTrace,
IsSuccess = false
});
}
}
protected override void AfterInsert(ProductionMoving item, DbContext dbContext)
{
//移库以后，要调整库存：移入部门库存增加，移出部门库存减少
lock (typeof(ProductMovingLogic))
{
WorkshopLogic WorkshopLogic = new WorkshopLogic(dbContext);
Workshop fromWorkshop = dbContext.Set <Workshop>().Where(x => x.RecordId == item.WorkshopIdFrom).First();
bool isFinishedProductionWorkshop = WorkshopLogic.IsFinishedProductionWorkshop(fromWorkshop);
MaterialStock stockIn = AssureInStock(item, isFinishedProductionWorkshop, dbContext);
MaterialStock stockOut = AssureOutStock(item, isFinishedProductionWorkshop, dbContext);
if (stockIn != null)
{
stockIn.QtyMoveIn = stockIn.QtyMoveIn + item.Qty; // 移入
stockIn.QtyStock = stockIn.QtyStock + item.Qty; // 库存增加
GlobalConstants.ModifyEntityStatus(stockIn, dbContext);
}
if (stockOut != null)
{
stockOut.QtyMoveOut = stockOut.QtyMoveOut + item.Qty; // 移出
stockOut.QtyStock = stockOut.QtyStock - item.Qty; // 库存减少
GlobalConstants.ModifyEntityStatus(stockOut, dbContext);
}
dbContext.SaveChanges();
}
}
private static void UpdateItemStock(ProductionOrderProgress item, DbContext dbContext)
{
MaterialStock stock = AssureStock(item, dbContext);
stock.QtyStock = stock.QtyStock + item.Qty;
stock.QtyGood = stock.QtyGood + item.Qty;
GlobalConstants.ModifyEntityStatus(stock, dbContext);
}
private static void UpdateItemStock(QualityCheck item, DbContext dbContext)
{
MaterialStock stock = AssureStock(item, dbContext);
stock.QtyStock = stock.QtyStock + item.Qty;
stock.QtyDefect = stock.QtyDefect + item.Qty;
GlobalConstants.ModifyEntityStatus(stock, dbContext);
}
public bool fnIsExist(MaterialStock materialStock)
{
string sSql = " SELECT COUNT([material_id]) " +
" FROM [MNDTmaterial_stock] " +
" WHERE [material_id] = '" + materialStock.material_id + "' " +
" AND [warehouse_id] = '" + materialStock.warehouse_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public DataTable fnSelect(MaterialStock materialStock)
{
string sSql = " SELECT TOP 1 * " +
" FROM [MNDTmaterial_stock] [materialStock] " +
" WHERE [materialStock].[material_id] = '" + materialStock.material_id + "' " +
" AND [materialStock].[warehouse_id] = '" + materialStock.warehouse_id + "' ";
return(PublicApi.fnGetDt(sSql, "MNDT"));
}
public DataTable fnSelects(MaterialStock materialStock)
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[material_id]", materialStock.material_id);
sCondition += PublicApi.fnAddCondition("[warehouse_id]", materialStock.warehouse_id);
sCondition += " ORDER BY [" + materialStock.order + "] ";
string sInquireSql =
" SELECT * " +
" FROM MNDTmaterial_stock " +
" WHERE 1 = 1 " + sCondition;
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
//public string fnDelete(MaterialStock materialStock, string sIP, string sId)
//{
// string sSql = " UPDATE [dbo].[MNDTmaterial_stock] " +
// " SET [status] = 'D' " +
// " WHERE [material_id] = '" + materialStock.material_id + "' ";
// MaterialStockTran materialStockTran = new MaterialStockTran();
// materialStockTran.MaterialStockId(materialStock.material_id)
// .IP(sIP)
// .Status("D")
// .CreateId(sId);
// sSql += fnInsertSql(materialStockTran);
// return PublicApi.fnExecuteSQL(sSql, "MNDT");
//}
//public string fnDeletes(MaterialStock materialStock, string sIP, string sId)
//{
// string sSql = " UPDATE [dbo].[MNDTmaterial_stock] " +
// " SET [status] = 'D' " +
// " WHERE [material_id] IN (" + materialStock.material_id + ")";
// MaterialStockTran materialStockTran = new MaterialStockTran();
// materialStockTran.MaterialStockId(materialStock.material_id)
// .IP(sIP)
// .Status("D")
// .CreateId(sId);
// sSql += fnInsertsSql(materialStockTran);
// return PublicApi.fnExecuteSQL(sSql, "MNDT");
//}
public string fnCount(MaterialStock materialStock)
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[material_id]", materialStock.material_id);
sCondition += PublicApi.fnAddCondition("[warehouse_id]", materialStock.warehouse_id);
string sCountSql =
" SELECT COUNT(material_id) " +
" FROM [MNDTmaterial_stock] " +
" WHERE 1 = 1 " + sCondition;
string sPageSize = PublicApi.fnGetValue(sCountSql, "MNDT");
return(sPageSize);
}
public IActionResult New(MaterialStock materialStock, string message)
{
if (message.Equals("Update"))
{
_context.MaterialStocks.Update(materialStock);
_context.SaveChanges();
_client.AddToastNotification("WELL DONE!! Your Data Has Been Successfully Updated", NotificationType.success, new ToastNotificationOption {
PositionClass = "toast-top-right"
});
}
else
{
_context.MaterialStocks.Add(materialStock);
_context.SaveChanges();
}
return(RedirectToAction("Index"));
}
// sNum 排行
public DataTable fnSelectStockId(MaterialStock materialStock, string sNum)
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[material_id]", materialStock.material_id);
sCondition += PublicApi.fnAddCondition("[warehouse_id]", materialStock.warehouse_id);
string sSql = " SELECT [materialStock].[material_id] " +
" ,[materialStock].[warehouse_id] " +
" FROM (SELECT Row_number() OVER (ORDER BY [" + materialStock.order + "] ASC) NUM, " +
" * " +
" FROM [MNDTmaterial_stock] " +
" WHERE 1 = 1" + sCondition + ") AS [materialStock] " +
" WHERE [materialStock].[NUM] = '" + sNum + "' ";
return(PublicApi.fnGetDt(sSql, "MNDT"));
}
public DataTable fnSelectStocks(MaterialStock materialStock)
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[material_id]", materialStock.material_id);
sCondition += " AND [material_s].[amount] <> 0 ";
sCondition += " ORDER BY [parameter] ";
string sInquireSql =
" SELECT [material_s].[warehouse_id], " +
" [material_s].[amount] " +
" FROM [mndtmaterial_stock] [material_s] " +
" LEFT JOIN [mndtkind_details] [kind_d] " +
" ON [kind_d].[kind_id] = 'WAR' " +
" AND [material_s].[warehouse_id] = [kind_d].[code_id] " +
" WHERE 1 = 1 " + sCondition;
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
public string fnUpdateAmountSql(MaterialStock materialStock)
{
MaterialStockTran materialStockTran = new MaterialStockTran();
materialStockTran.WarehouseId(materialStock.warehouse_id)
.MaterialId(materialStock.material_id)
.Description("系統進出貨正常變更")
.CreateId(materialStock.create_id);
string sSql = fnInsertSql(materialStockTran);
sSql +=
" UPDATE [dbo].[MNDTmaterial_stock] " +
" SET [amount] = [amount] + " + materialStock.amount + " " +
" WHERE [material_id] = '" + materialStock.material_id + "' " +
" AND [warehouse_id] = '" + materialStock.warehouse_id + "' ";
return(sSql);
}
private MaterialStock AssureOutStock(ProductionMoving item, bool isFinishedProductionWorkshop, DbContext dbContext)
{
MaterialStock stock = dbContext.Set <MaterialStock>().Where(x => x.StoreId == item.WorkshopIdFrom && x.MaterialId == item.ProductionId).FirstOrDefault();
if (stock == null && !isFinishedProductionWorkshop)
{
stock = new MaterialStock();
stock.MaterialId = item.ProductionId;
stock.MaterialCode = item.ProductionCode;
stock.MaterialName = item.ProductionName;
stock.StoreId = item.WorkshopIdFrom;
stock.StoreCode = item.WorkshopCodeFrom;
stock.StoreName = item.WorkshopNameFrom;
dbContext.Set <MaterialStock>().Add(stock);
dbContext.SaveChanges();
}
return(stock);
}
private static MaterialStock AssureStock(ProductionOrderProgress item, DbContext dbContext)
{
MaterialStock stock = dbContext.Set <MaterialStock>().Where(x => x.StoreId == item.WorkshopId && x.MaterialId == item.ProductionId).FirstOrDefault();
if (stock == null)
{
stock = new MaterialStock();
stock.MaterialId = item.ProductionId;
stock.MaterialCode = item.ProductionCode;
stock.MaterialName = item.ProductionName;
stock.StoreId = item.WorkshopId;
stock.StoreCode = item.WorkshopCode;
stock.StoreName = item.WorkshopName;
dbContext.Set <MaterialStock>().Add(stock);
dbContext.SaveChanges();
}
return(stock);
}
public string fnInsert(MaterialStock materialStock)
{
string sSql =
" INSERT INTO [dbo].[MNDTmaterial_stock] " +
" ([material_id] " +
" ,[warehouse_id] " +
" ,[amount] " +
" ,[safe_amount] " +
" ,[create_id] " +
" ,[create_datetime]) " +
" VALUES " +
" ('" + materialStock.material_id + "' " +
" ,'" + materialStock.warehouse_id + "' " +
" ,'0' " +
" ,'0' " +
" ,'" + materialStock.create_id + "' " +
" ,GETDATE()) ";
return(PublicApi.fnExecuteSQL(sSql, "MNDT"));
}
public string fnUpdate(MaterialStock materialStock, string sDescription, string sIP)
{
MaterialStockTran materialStockTran = new MaterialStockTran();
materialStockTran.WarehouseId(materialStock.warehouse_id)
.MaterialId(materialStock.material_id)
.Description(sDescription)
.CreateId(materialStock.create_id);
string sSql = fnInsertSql(materialStockTran);
sSql +=
" UPDATE [dbo].[MNDTmaterial_stock] " +
" SET [amount] = '" + materialStock.amount + "' " +
" ,[safe_amount] = '" + materialStock.safe_amount + "' " +
" WHERE [material_id] = '" + materialStock.material_id + "' " +
" AND [warehouse_id] = '" + materialStock.warehouse_id + "' ";
return(PublicApi.fnExecuteSQL(sSql, "MNDT"));
}
public DataTable fnExport(MaterialStock materialStock)
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[material_id]", materialStock.material_id);
sCondition += " ORDER BY [" + materialStock.order + "] ";
string sSql =
" select [materialstock].[material_id] '編號', " +
" [materialstock].[company_id] '公司id', " +
" [materialstock].[name] '名子', " +
" [materialstock].[unit] '單位', " +
" [materialstock].[currency] '貨幣', " +
" [materialstock].[price] '價格', " +
" [materialstock].[shelf_life] '期限', " +
" [materialstock].[description] '描述', " +
" [materialstock].[status] '狀態' " +
" FROM MNDTmaterial_stock as [materialstock] " +
" WHERE 1 = 1 " + sCondition;
return(PublicApi.fnGetDt(sSql, "mndt"));
}
public IActionResult New(Purchase purchase)
{
var productName = _context.Products.FirstOrDefault(x => x.Id == purchase.ProductId).PurchaseName;
var ratePerKg = purchase.TotalAmount / purchase.Quantity;
var existingPurchase = _context.MaterialStocks.FirstOrDefault(x => x.ProductName.Equals(productName));
if (existingPurchase != null)
{
var mStock = _context.MaterialStocks.FirstOrDefault(x => x.ProductName.Equals(productName));
mStock.Quantity = mStock.Quantity + purchase.Quantity;
mStock.TotalAmount = mStock.TotalAmount + purchase.TotalAmount;
mStock.RatePerKg = mStock.TotalAmount / mStock.Quantity;
_context.Update(mStock);
_context.SaveChanges();
Load();
_client.AddToastNotification("Congratulations, You Data HAs been SucessFully Added", NotificationType.success, null);
}
else
{
var materialStock = new MaterialStock();
materialStock.ProductName = productName;
materialStock.Quantity = purchase.Quantity;
materialStock.TotalAmount = purchase.TotalAmount;
materialStock.RatePerKg = ratePerKg;
_context.MaterialStocks.Add(materialStock);
_context.SaveChanges();
Load();
_client.AddToastNotification("Congratulations, You Data HAs been SucessFully Added", NotificationType.success, null);
}
purchase.AmountOfOneQuantity = ratePerKg;
_context.Purchases.Add(purchase);
_context.SaveChanges();
Load();
_client.AddToastNotification("Congratulations, You Data HAs been SucessFully Added", NotificationType.success, null);
return(View());
}
// iPage 第N頁
// iSize 最大顯示數量
public DataTable fnSelects(MaterialStock materialStock, int iPage, int iSize)
{
int iStart = (iPage - 1) * iSize + 1;
int iEnd = iPage * iSize;
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[material_id]", materialStock.material_id);
sCondition += PublicApi.fnAddCondition("[warehouse_id]", materialStock.warehouse_id);
string sInquireSql =
" SELECT [materialStock].[material_id], " +
" [materialStock].[warehouse_id], " +
" [materialStock].[amount], " +
" [materialStock].[safe_amount], " +
" [materialStock].[NUM] " +
" FROM (SELECT Row_number() OVER (ORDER BY [" + materialStock.order + "] ASC) NUM, " +
" * " +
" FROM [MNDTmaterial_stock] " +
" WHERE 1 = 1 " + sCondition + ") AS [materialStock] " +
" WHERE NUM BETWEEN " + iStart.ToString() + " AND " + iEnd.ToString() + " ";
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
public static MaterialStock CreateMaterialStock(long materialStockID, decimal stockQuantity, decimal issuedQuantity, global::System.DateTime inStockDateTime, long orderID, long subMaterialsNameID, short uOMID)
{
MaterialStock materialStock = new MaterialStock();
materialStock.MaterialStockID = materialStockID;
materialStock.StockQuantity = stockQuantity;
materialStock.IssuedQuantity = issuedQuantity;
materialStock.InStockDateTime = inStockDateTime;
materialStock.OrderID = orderID;
materialStock.SubMaterialsNameID = subMaterialsNameID;
materialStock.UOMID = uOMID;
return materialStock;
}
public void AddToMaterialStocks(MaterialStock materialStock)
{
base.AddObject("MaterialStocks", materialStock);
}
public string fnUpdateAmount(MaterialStock materialStock)
{
string sSql = fnUpdateAmountSql(materialStock);
return(PublicApi.fnExecuteSQL(sSql, "MNDT"));
}
