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; }
/// <summary> /// Import Equipment Libarary Data(Excel) /// </summary> /// <param name="dt">Excel File DataTable</param> /// <returns></returns> public static DataLoaderEquipmentLib GetDataLoaderEquipmentLib(DataTable dt) { DataLoaderEquipmentLib loader = new DataLoaderEquipmentLib(); foreach (DataColumn column in dt.Columns) { string mycol = column.ColumnName.Trim().ToUpper().Replace("*", "").Replace(" ", "").Replace("-", "").Replace("_", ""); switch (mycol) { //case "CATEGORY 1": case "CATEGORY1": loader.Ord_EquipCodeMain = column.Ordinal; break; //case "CATEGORY 2": case "CATEGORY2": loader.Ord_EquipCodeSub = column.Ordinal; break; //case "CATEGORY 3": case "CATEGORY3": loader.Ord_ThirdLevel = column.Ordinal; break; case "SPEC": loader.Ord_Spec = column.Ordinal; break; case "TYPE": loader.Ord_EquipmentType = column.Ordinal; break; case "MODELNUMBER": loader.Ord_ModelNumber = column.Ordinal; break; case "DESCRIPTION": loader.Ord_Description = column.Ordinal; break; } } return loader; }