//查询单个（查ID）
public int Select(ref M_Material Obj, int ID, ref string ErrMsg)
{
SqlConnection conn;
using (conn = CreatConn())
{
SqlCommand cmd = new SqlCommand($"select ID,Disable_BL,ProductName,MaterialCode,ProductModel from {TableName} where ID = {ID}", conn);
SqlDataReader sdr;
try
{
conn.Open();
sdr = cmd.ExecuteReader();
while (sdr.Read())
{
Obj.ID = (int)sdr["ID"];
Obj.Disable_BL = (int)sdr["Disable_BL"];
Obj.MaterialCode = (string)sdr["MaterialCode"];
Obj.ProductModel = (string)sdr["ProductModel"];
Obj.ProductName = (string)sdr["ProductName"];
return 1;
}
return -1;
}
catch (Exception ex)
{
ErrMsg = ex.Message;
return -1;
}
finally
{
cmd.Dispose();
}
}
}
public async Task <IActionResult> Edit([Bind("MaterialCode,MaterialName,MaterialDesc1,MaterialDesc2,RawMatTypeId,UnitId,PackageStdQty,WarehouseId,LocationId,CompanyCode,MaterialImagePath,Id,Is_Active,Created_Date,Created_By,Updated_Date,Updated_By")] M_Material m_Material)
{
if (ModelState.IsValid)
{
m_Material.Updated_By = await base.CurrentUserId();
ResultObject resultObj;
try
{
using (var matBll = new MaterialBLL())
{
resultObj = await matBll.UpdateMaterial(m_Material);
_cache.Remove("CACHE_MASTER_MATERIAL");
_cache.Remove("CACHE_MASTER_MATERIAL_BYRAWTYPE");
}
return(Json(new { success = true, data = (M_Material)resultObj.ObjectValue, message = "Material Update." }));
}
catch (Exception ex)
{
return(Json(new { success = false, data = m_Material, message = ex.Message }));
}
}
var err = ModelState.Values.SelectMany(x => x.Errors).Select(x => x.ErrorMessage).ToList();
return(Json(new { success = false, errors = err, data = m_Material, message = "Update Failed" }));
}
public async Task <ResultObject> DeleteMaterial(M_Material mat)
{
var resultObj = new ResultObject {
RowAffected = -1, ObjectValue = mat
};
using (var context = new MasterDbContext(contextOptions))
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
MySqlParameter[] sqlParams = new MySqlParameter[] {
new MySqlParameter("strId", mat.Id),
new MySqlParameter("strDelete_By", mat.Updated_By)
};
resultObj.RowAffected = await context.Database.ExecuteSqlCommandAsync("call sp_material_delete( ?, ?)", parameters : sqlParams);
transaction.Commit();
return(resultObj);
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
}
//查询多个（查ID）
public int Select(ref List<M_Material> Obj, List<int> ID, ref string ErrMsg)
{
SqlConnection conn;
using (conn = CreatConn())
{
SqlCommand cmd = new SqlCommand($"select ID,Disable_BL,ProductName,MaterialCode,ProductModel from {TableName} where ID = @ID", conn);
SqlDataReader sdr;
SqlParameter par = new SqlParameter("@ID", SqlDbType.Int);
cmd.Parameters.Add(par);
int tmpOut = 0;
try
{
conn.Open();
foreach (int i in ID)
{
cmd.Parameters["@ID"].Value = i;
sdr = cmd.ExecuteReader();
while (sdr.Read())
{
M_Material TmpObj = new M_Material();
TmpObj.ID = (int)sdr["ID"];
TmpObj.Disable_BL = (int)sdr["Disable_BL"];
TmpObj.MaterialCode = (string)sdr["MaterialCode"];
TmpObj.ProductModel = (string)sdr["ProductModel"];
TmpObj.ProductName = (string)sdr["ProductName"];
Obj.Add(TmpObj);
tmpOut++;
}
sdr.Close();
}
}
catch (Exception ex)
{
ErrMsg = ex.Message;
return -1;
}
finally
{
cmd.Dispose();
}
if (ID.Count() == tmpOut)
{
return 1;
}
else
{
return -1;
}
}
}
//更新单个（查ID）
public int Update(M_Material Obj, ref string ErrMsg)
{
SqlConnection conn;
using (conn = CreatConn())
{
SqlCommand cmd = new SqlCommand($"update {TableName} set Disable_BL = @Disable_BL, ProductName = @ProductName, MaterialCode = @MaterialCode, ProductModel = @ProductModel where ID = @ID", conn);
SqlParameter par = new SqlParameter("@Disable_BL", SqlDbType.Int);
par.Value = Obj.Disable_BL;
cmd.Parameters.Add(par);
par = new SqlParameter("@ProductName", SqlDbType.Char, 50);
par.Value = Obj.ProductName;
cmd.Parameters.Add(par);
par = new SqlParameter("@MaterialCode", SqlDbType.Char, 50);
par.Value = Obj.MaterialCode;
cmd.Parameters.Add(par);
par = new SqlParameter("@ProductModel", SqlDbType.Char, 50);
par.Value = Obj.ProductModel;
cmd.Parameters.Add(par);
par = new SqlParameter("@ID", SqlDbType.Int);
par.Value = Obj.ID;
cmd.Parameters.Add(par);
try
{
conn.Open();
if (cmd.ExecuteNonQuery() > 0)
{
return 1;
}
else
{
return -1;
}
}
catch (Exception ex)
{
ErrMsg = ex.Message;
return -1;
}
finally
{
cmd.Dispose();
}
}
}
//插入单个
public int Insert(ref M_Material Obj, ref string ErrMsg)
{
SqlConnection conn;
using (conn = CreatConn())
{
SqlCommand cmd = new SqlCommand($"insert into {TableName} (Disable_BL,ProductName,MaterialCode,ProductModel) values (@Disable_BL,@ProductName,@MaterialCode,@ProductModel);select @@IDENTITY as int", conn);
SqlParameter par = new SqlParameter("@Disable_BL", SqlDbType.Int);
par.Value = Obj.Disable_BL;
cmd.Parameters.Add(par);
par = new SqlParameter("@ProductName", SqlDbType.Char, 50);
par.Value = Obj.ProductName;
cmd.Parameters.Add(par);
par = new SqlParameter("@MaterialCode", SqlDbType.Char, 50);
par.Value = Obj.MaterialCode;
cmd.Parameters.Add(par);
par = new SqlParameter("@ProductModel", SqlDbType.Char, 50);
par.Value = Obj.ProductModel;
cmd.Parameters.Add(par);
try
{
conn.Open();
decimal d = (decimal)cmd.ExecuteScalar();
Obj.ID = (int)d;
if (Obj.ID > 0)
{
return 1;
}
else
{
return -1;
}
}
catch (Exception ex)
{
ErrMsg = ex.Message;
return -1;
}
finally
{
cmd.Dispose();
}
}
}
public async Task <ResultObject> UpdateMaterial(M_Material mat)
{
var resultObj = new ResultObject {
RowAffected = -1, ObjectValue = mat
};
using (var context = new MasterDbContext(contextOptions))
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
context.Entry(mat).State = EntityState.Modified;
MySqlParameter[] sqlParams = new MySqlParameter[] {
new MySqlParameter("strId", mat.Id),
new MySqlParameter("strMaterialCode", mat.MaterialCode),
new MySqlParameter("strMaterialName", mat.MaterialName),
new MySqlParameter("strMaterialDesc1", mat.MaterialDesc1),
new MySqlParameter("strMaterialDesc2", mat.MaterialDesc2),
new MySqlParameter("strRawMatTypeId", mat.RawMatTypeId),
new MySqlParameter("strUnitId", mat.UnitId),
new MySqlParameter("strPackageStdQty", mat.PackageStdQty),
new MySqlParameter("strWarehouseId", mat.WarehouseId),
new MySqlParameter("strLocationId", mat.LocationId),
new MySqlParameter("strMaterialImagePath", mat.MaterialImagePath),
new MySqlParameter("strCompanyCode", mat.CompanyCode),
new MySqlParameter("strIs_Active", mat.Is_Active),
new MySqlParameter("strUpdated_By", mat.Updated_By)
};
//Output Parameter no need to define. @`strId`
resultObj.RowAffected = await context.Database.ExecuteSqlCommandAsync("call sp_material_update(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", parameters : sqlParams);
transaction.Commit();
return(resultObj);
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
}
public async Task <ResultObject> InsertMaterial(M_Material mat)
{
var resultObj = new ResultObject {
RowAffected = -1, ObjectValue = mat
};
using (var context = new MasterDbContext(contextOptions))
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
MySqlParameter[] sqlParams = new MySqlParameter[] {
new MySqlParameter("strMaterialCode", mat.MaterialCode),
new MySqlParameter("strMaterialName", mat.MaterialName),
new MySqlParameter("strMaterialDesc1", mat.MaterialDesc1),
new MySqlParameter("strMaterialDesc2", mat.MaterialDesc2),
new MySqlParameter("strRawMatTypeId", mat.RawMatTypeId),
new MySqlParameter("strUnitId", mat.UnitId),
new MySqlParameter("strPackageStdQty", mat.PackageStdQty),
new MySqlParameter("strWarehouseId", mat.WarehouseId),
new MySqlParameter("strLocationId", mat.LocationId),
new MySqlParameter("strMaterialImagePath", mat.MaterialImagePath),
new MySqlParameter("strCompanyCode", mat.CompanyCode),
new MySqlParameter("strIs_Active", mat.Is_Active),
new MySqlParameter("strCreated_By", mat.Created_By)
};
resultObj.RowAffected = await context.Database.ExecuteSqlCommandAsync("call sp_material_insert(@`strId`, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", parameters : sqlParams);
//new department after insert.
var newMat = context.Material.FromSql("SELECT * FROM m_material WHERE Id = @`strId`;").ToListAsync();
resultObj.ObjectValue = newMat.Result[0];
transaction.Commit();
return(resultObj);
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
}
//查询单个（查ID）
public static int Select(ref M_Material Obj, int ID, ref string ErrMsg)
{
I_Material I = (D_Material)SimpleFactory.CreateObject(DBType.Material);
return I.Select(ref Obj, ID, ref ErrMsg);
}
//更新单个（查ID）
public static int Update(M_Material Obj, ref string ErrMsg)
{
I_Material I = (D_Material)SimpleFactory.CreateObject(DBType.Material);
return I.Update(Obj, ref ErrMsg);
}
public async Task <IActionResult> GetMaterial(string vcode, int?rawMatTypeId, string companyCode)
{
try
{
object matTypeId = TempData.Peek("MatTypeId");
if ((int?)matTypeId == rawMatTypeId)
{
if (_cache.TryGetValue("CACHE_MASTER_MATERIAL_BYRAWTYPE", out List <M_Material> c_lstMat))
{
if (string.IsNullOrEmpty(vcode))
{
return(Json(new { success = true, data = c_lstMat, message = "Get Material Success" }));
}
else
{
M_Material m_Material = c_lstMat.Find(m => m.MaterialCode == vcode);
if (m_Material != null)
{
return(Json(new { success = true, data = m_Material, message = "Get Material Success" }));
}
else
{
return(Json(new { success = false, data = string.Empty, message = "Material Not Found" }));
}
}
}
}
TempData["MatTypeId"] = rawMatTypeId;
TempData.Keep("MatTypeId");
MemoryCacheEntryOptions options = new MemoryCacheEntryOptions
{
AbsoluteExpirationRelativeToNow = TimeSpan.FromSeconds(300),
SlidingExpiration = TimeSpan.FromSeconds(60),
Priority = CacheItemPriority.NeverRemove
};
using (var matBll = new MaterialBLL())
{
var lstMat = await matBll.GetMaterialByRawType(rawMatTypeId, companyCode);
_cache.Set("CACHE_MASTER_MATERIAL_BYRAWTYPE", lstMat, options);
if (string.IsNullOrEmpty(vcode))
{
return(Json(new { success = true, data = lstMat, message = "Get Material Success" }));
}
else
{
M_Material m_Material = lstMat.Find(m => m.MaterialCode == vcode);
if (m_Material != null)
{
return(Json(new { success = true, data = m_Material, message = "Get Material Success" }));
}
else
{
return(Json(new { success = false, data = string.Empty, message = "Material Not Found" }));
}
}
}
}
catch (Exception ex)
{
return(BadRequest(new { success = false, message = ex.Message }));
}
}
