public async Task <ActionResult> Save(MaterialBOMViewModel model)
{
using (MaterialBOMServiceClient client = new MaterialBOMServiceClient())
{
MaterialBOM obj = new MaterialBOM()
{
Key = new MaterialBOMKey()
{
MaterialCode = model.MaterialCode.ToUpper(),
ItemNo = model.ItemNo
},
RawMaterialCode = model.RawMaterialCode.ToUpper(),
MaterialUnit = model.MaterialUnit,
Qty = model.Qty,
StoreLocation = model.StoreLocation,
WorkCenter = model.WorkCenter,
Description = model.Description,
CreateTime = DateTime.Now,
Creator = User.Identity.Name,
Editor = User.Identity.Name,
EditTime = DateTime.Now,
};
MethodReturnResult rst = await client.AddAsync(obj);
if (rst.Code == 0)
{
rst.Message = string.Format(FMMResources.StringResource.MaterialBOM_Save_Success
, obj.Key);
}
return(Json(rst));
}
}
public static List <MaterialBOM> ConvertExcelToMaterialListFromBOM(string filePath, RichTextBox processTextBox)
{
List <MaterialBOM> materialList = new List <MaterialBOM>();
List <string> errorList = new List <string>();
ShowChangesRichTextBox(processTextBox, "\nAbriendo Libro de Excel...");
Excel.Application app = new Excel.Application();
Excel.Workbook workbook;
try
{
workbook = app.Workbooks.Open(filePath, UpdateLinks: 2);
}
catch (Exception e)
{
Util.ShowMessage(AlarmType.ERROR, e.Message);
return(null);
}
foreach (Excel._Worksheet sheet in workbook.Sheets)
{
if (sheet.Visible == Excel.XlSheetVisibility.xlSheetHidden)
{
continue;
}
Excel.Range range = sheet.UsedRange;
int rowCount = range.Rows.Count;
int colCount = range.Columns.Count;
List <Column> headercolumns = new List <Column>(colCount);
string sheetName = sheet.Name.ToUpper();
if (sheetName == Defs.EXCLUDED_SHEET_COSTOS || sheetName == Defs.EXCLUDED_SHEET_LEYENDA)
{
continue;
}
int rowToleranceIndex = 0;
for (int rowIndex = 1; rowIndex <= rowCount; rowIndex++)
{
MaterialBOM material = new MaterialBOM();
if (headercolumns.Count < MIN_COLUMNS_BOM_AMOUNT) //Get Columns names
{
colCount = colCount > NORMAL_COLUMN_AMOUNT ? NORMAL_COLUMN_AMOUNT : colCount;
for (int colIndex = 1; colIndex <= colCount; colIndex++)
{
try
{
if (range.Cells[rowIndex, colIndex] != null && range.Cells[rowIndex, colIndex].Value2 != null)
{
string columnName = (string)range.Cells[rowIndex, colIndex].Value2.ToString();
Column column = new Column(columnName, colIndex);
headercolumns.Add(column);
}
}
catch (Exception e)
{
break;
}
}
if (rowIndex == HEADER_COLUMN_TOLERANCE)
{
break;
}
}
else
{
string errorItem = String.Empty;
string materialMessageItem = String.Empty;
Column colMaterialCode = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_MATERIAL_CODE));
Column colAmount = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_AMOUNT));
Column colStatus = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_STATUS));
Column colUnit = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_UNIT));
if (colMaterialCode == null)
{
errorItem = $"\n*ERROR: Formato no reconocido en la hoja {sheet.Name.ToString()}, la columna del número de material, debe de decir: \"TEXTO BREVE Clave de material del proveedor\"";
errorList.Add(errorItem);
ShowChangesRichTextBox(processTextBox, errorItem);
break;
}
if (colAmount == null)
{
errorItem = $"\n*ERROR: Formato no reconocido en la hoja {sheet.Name.ToString()}, la columna de la Cantidad del producto debe de decir: \"Cantidad\"";
errorList.Add(errorItem);
ShowChangesRichTextBox(processTextBox, errorItem);
break;
}
if (errorList.Count > 0)
{
break;
}
var dynamicCode = range.Cells[rowIndex, colMaterialCode.Index].Value;
var dynamicAmount = range.Cells[rowIndex, colAmount.Index].Value;
var dynamicStatus = range.Cells[rowIndex, colStatus.Index].Value;
var dynamicUnit = range.Cells[rowIndex, colUnit.Index].Value;
string status = Util.ConvertDynamicToString(dynamicStatus);
if (!Util.IsEmptyString(status) && status.ToUpper() != Defs.STATE_PENDING)
{
continue;
}
string materialCode = Util.ConvertDynamicToString(dynamicCode);
if (Util.IsEmptyString(materialCode))
{
rowToleranceIndex++;
if (EMPTINESS_ROW_TOLERANCE < rowToleranceIndex)
{
break;
}
}
material.Code = Util.NormalizeString(materialCode);
material.Unit = Util.ConvertDynamicToString(dynamicUnit);
material.Amount = Util.ConvertDynamicToDouble(dynamicAmount);
material.Sheet = sheet.Name;
material.Row = rowIndex;
//Add Valid material to list
if (!Util.IsEmptyString(material.Code) && material.Amount != 0)
{
if (Util.IsEmptyString(material.Unit))
{
material.Unit = "(Sin Unidad)";
}
materialList.Add(material);
materialMessageItem = $"\nPROCESANDO: Hoja: {sheet.Name} Num Parte: {material.Code} Cantidad: {material.Amount} Unidad {material.Unit}";
ShowChangesRichTextBox(processTextBox, materialMessageItem);
}
else if (!Util.IsEmptyString(material.Code))
{
errorItem = $"No hay información en la columna de {Defs.COL_AMOUNT} en la fila {rowIndex} de la hoja {sheet.Name.ToString()}";
errorList.Add(errorItem);
}
}
}
Marshal.ReleaseComObject(range);
Marshal.ReleaseComObject(sheet);
}
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//close and release
workbook.Close(0);
Marshal.ReleaseComObject(workbook);
//quit and release
app.Quit();
Marshal.ReleaseComObject(app);
if (errorList.Count > 0)
{
materialList.Clear();
Util.ShowMessage(AlarmType.ERROR, errorList);
}
return(materialList);
}
