public RDataBom LoadExcel()
{
string formatNo = Request.Query["formatNo"].ToString();
if (Request.Form.Files == null || Request.Form.Files.Count == 0)
{
throw new Exception("Excel不存在");
}
var excelFormat = Service.FetchExcelFormat(formatNo);
if (excelFormat == null)
{
throw new Exception("导入格式 不存在!");
}
var sheet = NpoiExcelHelper.GetSheet(Request.Form.Files[0], "0");
if (sheet == null)
{
throw new Exception("Sheet 找不到!");
}
RDataBom rInfo = new RDataBom();
rInfo.prd_no = NpoiExcelHelper.GetCellStringValue(excelFormat.PrdNoPos, sheet);
rInfo.prd_name = NpoiExcelHelper.GetCellStringValue(excelFormat.PrdNamePos, sheet);
rInfo.dep_no = NpoiExcelHelper.GetCellStringValue(excelFormat.DeptNoPos, sheet);
var fieldList = Service.GetDtls(excelFormat.Id).OrderBy(o => o.cell_index).ToList();
DataTable dt = new DataTable();
foreach (var item in fieldList)
{
dt.Columns.Add(item.field_no);
}
dt.Columns.Add("bom_level");
//物料大类 是节点Bom头 设3半成品，其他是4物料
dt.Columns.Add("bom_prdt_knd");
dt.Columns.Add("check_result");
dt.Columns.Add("check_err_code");
dt.Columns.Add("check_ask_radio");
for (int i = excelFormat.StartRow - 1; i <= sheet.LastRowNum; ++i)
{
var newRow = dt.NewRow();
var row = sheet.GetRow(i);
if (row == null)
{
break;
}
newRow["bom_level"] = NpoiExcelHelper.GetCellStringValue(row.GetCell(excelFormat.BomStructCell - 1));
newRow["check_result"] = "";
string bomLevel = NpoiExcelHelper.GetCellStringValue(row.GetCell(excelFormat.BomStructCell - 1));
//阶数中断了，代表最后
if (bomLevel.IsNullOrEmpty())
{
break;
}
foreach (var field in fieldList)
{
string val = NpoiExcelHelper.GetCellStringValue(row.GetCell(field.cell_index - 1));
//阶数中断了，代表最后
if (val.IsNullOrEmpty() && field.cell_index == excelFormat.BomStructCell)
{
break;
}
newRow[field.field_no] = val;
}
dt.Rows.Add(newRow);
}
rInfo.boms = dt;
return(rInfo);
}
public RDataBom ImportToBom([FromBody] RDataBom RDataBom)
{
var dtBoms = RDataBom.boms;
Debug.WriteLine(RDataBom.boms.Rows.Count);
if (dtBoms == null || dtBoms.Rows.Count <= 0)
{
throw new Exception("表格没有记录");
}
var excelFormat = Service.FetchExcelFormat(RDataBom.format_no);
if (excelFormat == null)
{
throw new Exception("导入格式 不存在!");
}
var excelFormatDtls = Service.GetDtls(excelFormat.Id);
//检查Bom阶级
ImportBomFormExcel importTool = new ImportBomFormExcel();
var headBomRow = dtBoms.NewRow();
headBomRow["prd_no"] = RDataBom.prd_no;
headBomRow["dep"] = RDataBom.dep_no;
headBomRow["name"] = RDataBom.prd_name;
//dtBoms.Rows.InsertAt(headBomRow, 0);
if (dtBoms.Columns.IndexOf("id_no") < 0)
{
dtBoms.Columns.Add(new DataColumn("id_no"));
}
if (dtBoms.Columns.IndexOf("wh_no") < 0)
{
dtBoms.Columns.Add(new DataColumn("wh_no"));
}
///清空之前的检测结果
foreach (DataRow row in dtBoms.Rows)
{
row["Check_Result"] = "";
row["check_err_code"] = "";
}
BomInfo bomInfo = importTool.DoCheckBomLevel(dtBoms, dtBoms.Rows[0], headBomRow, "", BomLevelType: "Split");
bool hitError = false;
//检测ERP中字段是否存在
var checkFields = excelFormatDtls.Where(o => o.check_exist.IsNotEmpty()).ToList();
foreach (DataRow item in dtBoms.Rows)
{
foreach (var item2 in checkFields)
{
string value = item[item2.field_no].ObjToString();
if (value.IsNotEmpty() && Service.CheckValueExsistInTable(item2.check_exist, value) == false)
{
item["Check_Result"] = "不存在:" + item2.check_exist + " 值:" + value;
if (item2.field_no == "prd_no")
{
item["check_err_code"] = "MISS_PRDT";
}
hitError = true;
break;
}
}
}
if (hitError == false)
{
hitError = !Service.ValidateSubBom(bomInfo, true, RDataBom.is_check);
}
if (hitError == false)
{
hitError = !Service.ValidateInNoBomConflit(bomInfo);
}
if (hitError == false)
{
Service.SetDefaultWh(bomInfo);
}
if (hitError == false && RDataBom.is_check == false)
{
var cmds = DoImportToBom(bomInfo, excelFormatDtls, true);
foreach (var item in cmds)
{
Debug.WriteLine(item.CommandText);
}
Service.ExeCmds(cmds);
}
if (RDataBom.is_check == false && hitError == true)
{
throw new Exception("检测失败不允许导入Bom");
}
return(RDataBom);
}
