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