public SigmaResultType AddEquipment(TypeEquipment objEquipment)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
SigmaResultType result = new SigmaResultType();
TransactionScope scope = null;
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@EquipmentCodeMain", objEquipment.EquipmentCodeMain.Trim()));
paramList.Add(new SqlParameter("@EquipmentCodeSub", objEquipment.EquipmentCodeSub.Trim()));
paramList.Add(new SqlParameter("@Description", objEquipment.Description.Trim()));
paramList.Add(new SqlParameter("@ThirdLevel", objEquipment.ThirdLevel.Trim()));
paramList.Add(new SqlParameter("@Spec", objEquipment.Spec.Trim()));
paramList.Add(new SqlParameter("@EquipmentType", objEquipment.EquipmentType.Trim()));
paramList.Add(new SqlParameter("@CreatedBy", userinfo.SigmaUserId.Trim()));
paramList.Add(new SqlParameter("@ModelNumber", objEquipment.ModelNumber.Trim()));
SqlParameter outParam = new SqlParameter("@NewId", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;
paramList.Add(outParam);
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddEquipment", paramList.ToArray());
result.IsSuccessful = true;
result.ScalarValue = (int)outParam.Value;
scope.Complete();
}
return result;
}
public SigmaResultType UpdateEquipment(TypeEquipment objEquipment)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
// Compose parameters
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@EquipmentId", objEquipment.EquipmentId),
new SqlParameter("@EquipmentCodeMain", objEquipment.EquipmentCodeMain.Trim()),
new SqlParameter("@EquipmentCodeSub", objEquipment.EquipmentCodeSub.Trim()),
new SqlParameter("@ThirdLevel", objEquipment.ThirdLevel.Trim()),
new SqlParameter("@Description", objEquipment.Description.Trim()),
new SqlParameter("@Spec", objEquipment.Spec.Trim()),
new SqlParameter("@EquipmentType", objEquipment.EquipmentType.Trim()),
new SqlParameter("@UpdatedBy", userinfo.SigmaUserId.Trim()),
new SqlParameter("@ModelNumber", objEquipment.ModelNumber.Trim())
};
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, "usp_UpdateEquipment", parameters);
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
public SigmaResultType RemoveEquipment(TypeEquipment objEquipment)
{
SigmaResultType result = new SigmaResultType();
TransactionScope scope = null;
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
// Compose parameters
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@EquipmentId", Utilities.ToInt32(objEquipment.EquipmentId.ToString().Trim())),
new SqlParameter("@NewId", SqlDbType.Int)
};
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, "usp_RemoveEquipment", parameters);
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
public SigmaResultType AddEquipmentInfo(TypeEquipment objEquipment)
{
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
SigmaResultType resultEquipment = new SigmaResultType();
bool isCostomField = true;
if (objEquipment.CustomField.Count > 0)
{
var count = objEquipment.CustomField.Where(p => p.SigmaOperation == "C" && (p.FieldName.Trim() == "" || p.Value.Trim() == "")).Count();
if (count > 0)
isCostomField = false;
}
if (!(string.IsNullOrEmpty(objEquipment.EquipmentCodeMain))
&& !(string.IsNullOrEmpty(objEquipment.EquipmentCodeSub))
&& !(string.IsNullOrEmpty(objEquipment.Spec))
)
{
using (scope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
if (objEquipment.SigmaOperation == SigmaOperation.INSERT)
{
resultEquipment = AddEquipment(objEquipment);
objEquipment.CustomField.ForEach(item => item.Parentid = resultEquipment.ScalarValue);
}
else if (objEquipment.SigmaOperation == SigmaOperation.UPDATE)
{
resultEquipment = UpdateEquipment(objEquipment);
objEquipment.CustomField.ForEach(item => item.Parentid = objEquipment.EquipmentId);
}
if (resultEquipment.IsSuccessful && objEquipment.CustomField.Count > 0)
{
foreach (var item in objEquipment.CustomField)
{
switch (item.SigmaOperation)
{
case SigmaOperation.INSERT:
AddCustomFieldWithEquipmentCustomField(item);
break;
case SigmaOperation.UPDATE:
UpdateCustomFieldWithEquipmentCustomField(item);
break;
case SigmaOperation.DELETE:
RemoveCustomFieldWithEquipmentCustomField(item);
break;
}
}
}
result.AffectedRow = resultEquipment.AffectedRow;
result.ScalarValue = resultEquipment.ScalarValue;
result.IsSuccessful = true;
scope.Complete();
}
}
else
{
result.AffectedRow = -1;
result.ErrorCode = "GlobalSetting0001";
result.ErrorMessage = "Validation";
result.IsSuccessful = false;
}
return result;
}
public SigmaResultType ImportEquipmentLib(string filePath, string exportfilepath)
{
EquipmentMgr equipmentMgr = new EquipmentMgr();
SigmaCodeMgr sigmaCodeMgr = new SigmaCodeMgr();
SigmaResultType sigmaResult = new SigmaResultType();
TypeEquipment typeEquipment = new TypeEquipment();
DataLoaderEquipmentLib loader = new DataLoaderEquipmentLib();
DataTable Exceldt = Element.Shared.Common.ImportHelper.ImportWorkSheet(filePath, true);
DataTable ErrDataTable = SetErrTable(Exceldt);
int failCount = 0;
int rowCount = Exceldt.Rows.Count;
int columnCount = Exceldt.Columns.Count;
if (rowCount > 0)
{
loader = MTOImportHelper.GetDataLoaderEquipmentLib(Exceldt);
string parentCodeCategory = "EQUIP%";
string categoryName = "ALL";
string codeCategory = "EQUIP_%";
DataSet SigmaCodeCategoryDS = sigmaCodeMgr.ListSigmaCodeCategoryByParentCodeCategory(parentCodeCategory, categoryName);
DataSet SigmaCodeDS = sigmaCodeMgr.ListSigmaCodeForEquipment(codeCategory);
foreach (DataRow row in Exceldt.Rows)
{
bool isValidation = true;
#region Mandatory Check (*)
for (int i = 0; i < columnCount; i++)
{
if (Exceldt.Columns[i].ToString().Substring(0, 1).ToUpper() == "*" && string.IsNullOrEmpty(row.ItemArray.GetValue(i).ToString()))
{
ErrDataTable.Rows.Add(row.ItemArray);
ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "The value of [" + Exceldt.Columns[i].ToString() + "] is required.";
failCount = failCount + 1;
isValidation = false;
break;
}
}
#endregion Mandatory Check
#region Reference Check (SigmaCodeCategory Table)
DataRow EquipmentCodeMain = null;
DataRow EquipmentCodeSub = null;
DataRow ThirdLevel = null;
if (isValidation)
{
EquipmentCodeMain = SigmaCodeCategoryDS.Tables[0].Select("CategoryName = '" + row[loader.Ord_EquipCodeMain] + "'").FirstOrDefault();
if (EquipmentCodeMain == null)
{
ErrDataTable.Rows.Add(row.ItemArray);
ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "There is no item in the library to match up with the value of [" + row[loader.Ord_EquipCodeMain].ToString() + "]";
failCount = failCount + 1;
isValidation = false;
}
}
if (isValidation)
{
EquipmentCodeSub = SigmaCodeCategoryDS.Tables[0].Select("ParentCodeCategory = '" + EquipmentCodeMain["CodeCategory"] + "' AND CategoryName = '" + row[loader.Ord_EquipCodeSub] + "'").FirstOrDefault();
if (EquipmentCodeSub == null)
{
ErrDataTable.Rows.Add(row.ItemArray);
ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "There is no item in the library to match up with the value of [" + row[loader.Ord_EquipCodeSub].ToString() + "]";
failCount = failCount + 1;
isValidation = false;
}
}
if (isValidation && !string.IsNullOrEmpty(row[loader.Ord_ThirdLevel].ToString()))
{
ThirdLevel = SigmaCodeDS.Tables[0].Select("CodeCategory = '" + EquipmentCodeSub["CodeCategory"] + "' AND CodeName = '" + row[loader.Ord_ThirdLevel] + "'").FirstOrDefault();
if (ThirdLevel == null)
{
ErrDataTable.Rows.Add(row.ItemArray);
ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "There is no item in the library to match up with the value of [" + row[loader.Ord_ThirdLevel].ToString() + "]";
failCount = failCount + 1;
isValidation = false;
}
}
#endregion Reference Check
#region Duplication Check
if (isValidation)
{
string equipmentCodeMain = row[loader.Ord_EquipCodeMain].ToString();
string equipmentCodeSub = row[loader.Ord_EquipCodeSub].ToString();
string thirdLevel = row[loader.Ord_ThirdLevel].ToString();
string spec = row[loader.Ord_Spec].ToString().Replace("'", "’");
string equipmentType = row[loader.Ord_EquipmentType].ToString();
DataSet EquipmentDS = equipmentMgr.ListMaterialByEquipmentCodeMain(equipmentCodeMain, equipmentCodeSub, thirdLevel, spec, equipmentType);
if (EquipmentDS.Tables[0].Rows.Count > 0)
{
ErrDataTable.Rows.Add(row.ItemArray);
ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "This data[Equipment] is duplicated.";
failCount = failCount + 1;
isValidation = false;
}
}
#endregion Duplication Check
#region AddEquipment
if (isValidation)
{
typeEquipment.EquipmentCodeMain = EquipmentCodeMain["CodeCategory"].ToString();
typeEquipment.EquipmentCodeSub = EquipmentCodeSub["CodeCategory"].ToString();
typeEquipment.ThirdLevel = (ThirdLevel == null) ? "" :ThirdLevel["Code"].ToString().Trim() ;
typeEquipment.Spec = row[loader.Ord_Spec].ToString().Replace("'", "’");
typeEquipment.EquipmentType = row[loader.Ord_EquipmentType].ToString();
typeEquipment.ModelNumber = row[loader.Ord_ModelNumber].ToString();
typeEquipment.Description = row[loader.Ord_Description].ToString();
typeEquipment.CreatedBy = userinfo.SigmaUserId;
sigmaResult = equipmentMgr.AddEquipment(typeEquipment);
if (sigmaResult.IsSuccessful)
{
// CustomField
for (int i = 0; i < columnCount; i++)
{
if (Exceldt.Columns[i].ToString().Substring(0, 3).ToUpper() == "UD_")
{
string RowValue = row.ItemArray.GetValue(i).ToString();
sigmaResult.IsSuccessful = CheckEquipmentCustomField(Exceldt, Exceldt.Columns[i].ToString().Trim(), sigmaResult.ScalarValue, RowValue);
}
}
}
}
#endregion AddEquipment
}
// Set ImportHistory(SuccessCount/FailCount)
sigmaResult = AddImportHistory(Exceldt.Rows.Count, failCount, Path.GetFileName(filePath).ToString(), "EquipmentLibrary");
// ConvertExcel file && CSV file
Export2Excel.ConvertExcelfromData(ErrDataTable, sigmaResult.ScalarValue.ToString() + ".xlsx", exportfilepath);
Export2Excel.ConvertCSVFile(ErrDataTable, sigmaResult.ScalarValue.ToString() + ".csv", exportfilepath);
sigmaResult.IsSuccessful = true;
}
else
{
sigmaResult.IsSuccessful = false;
sigmaResult.ErrorMessage = "no record from file.";
}
return sigmaResult;
}
public SigmaResultType UpdateEquipment(TypeEquipment objEquipment)
{
SigmaResultType result = new SigmaResultType();
try
{
EquipmentMgr materialMgr = new EquipmentMgr();
result = materialMgr.AddEquipmentInfo(objEquipment);
result.IsSuccessful = true;
return result;
}
catch (Exception ex)
{
// Log Exception
ExceptionHelper.logException(ex);
result.IsSuccessful = false;
result.ErrorMessage = ex.Message;
return result;
}
}