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; } }