public SigmaResultType ImportConsumableLib(string filePath, string exportfilepath) { MaterialMgr materialMgr = new MaterialMgr(); SigmaCodeMgr sigmaCodeMgr = new SigmaCodeMgr(); CommonCodeMgr commonCodeMgr = new CommonCodeMgr(); SigmaResultType sigmaResult = new SigmaResultType(); TypeMaterial typeMaterial = new TypeMaterial(); DataLoaderConsumableLib loader = new DataLoaderConsumableLib(); 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; string codeName = "ALL"; string codeCategory = "UOM"; if (rowCount > 0) { loader = MTOImportHelper.GetDataLoaderConsumableLib(Exceldt); DataSet SigmaCodeDS = sigmaCodeMgr.ListSigmaCodeByCodeCategory(codeName, 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 (SigmaCode Table) DataRow UOM = null; if (isValidation) { UOM = SigmaCodeDS.Tables[0].Select("CodeName = '" + row[loader.Ord_UOM] + "'").FirstOrDefault(); if (UOM == 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_UOM].ToString() + "]"; failCount = failCount + 1; isValidation = false; } } #endregion Reference Check #region Duplication Check if (isValidation) { string vendor = row[loader.Ord_Vendor].ToString().Trim(); string description = row[loader.Ord_Description].ToString().Trim(); string partNumber = row[loader.Ord_PartNumber].ToString().Trim(); string uomCode = UOM["Code"].ToString().Trim(); DataSet ConsumableDS = materialMgr.ListMaterialByVendorPartNumber(vendor, description, partNumber, uomCode); if (ConsumableDS.Tables[0].Rows.Count > 0) { ErrDataTable.Rows.Add(row.ItemArray); ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "This data[Consumable] is duplicated."; failCount = failCount + 1; isValidation = false; } } #endregion Duplication Check #region AddMaterial if (isValidation) { typeMaterial.DisciplineCode = ""; typeMaterial.TaskCategoryId = 0; typeMaterial.TaskTypeId = 0; typeMaterial.Manhours = 0; typeMaterial.UomCode = UOM["Code"].ToString().Trim(); typeMaterial.Vendor = row[loader.Ord_Vendor].ToString().Trim(); typeMaterial.Description = row[loader.Ord_Description].ToString().Trim(); typeMaterial.PartNumber = row[loader.Ord_PartNumber].ToString().Trim(); typeMaterial.CostCodeId = 0; typeMaterial.CreatedBy = userinfo.SigmaUserId; sigmaResult = materialMgr.AddMaterial(typeMaterial); 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 = CheckMaterialCustomField(Exceldt, Exceldt.Columns[i].ToString().Trim(), sigmaResult.ScalarValue, RowValue); } } } } #endregion AddMaterial } // Set ImportHistory(SuccessCount/FailCount) sigmaResult = AddImportHistory(Exceldt.Rows.Count, failCount, Path.GetFileName(filePath).ToString(), "ConsumableLibrary"); // 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 Consumable Libarary Data(Excel) /// </summary> /// <param name="dt"></param> /// <returns></returns> public static DataLoaderConsumableLib GetDataLoaderConsumableLib(DataTable dt) { DataLoaderConsumableLib loader = new DataLoaderConsumableLib(); foreach (DataColumn column in dt.Columns) { string mycol = column.ColumnName.Trim().ToUpper().Replace("*", "").Replace(" ", "").Replace("-", "").Replace("_", ""); switch (mycol) { case "VENDOR": loader.Ord_Vendor = column.Ordinal; break; case "DESCRIPTION": loader.Ord_Description = column.Ordinal; break; case "PARTNUMBER": loader.Ord_PartNumber = column.Ordinal; break; case "UOM": loader.Ord_UOM = column.Ordinal; break; } } return loader; }