/// <summary>
/// Function to Update values in Bom Table
/// </summary>
/// <param name="bominfo"></param>
public void BomEdit(BomInfo bominfo)
{
try
{
SPBom.BomEdit(bominfo);
}
catch (Exception ex)
{
MessageBox.Show("BOMBll3:" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// Function to insert values to Bom Table
/// </summary>
/// <param name="bominfo"></param>
/// <param name="decId"></param>
public void BomFromDatatable(BomInfo bominfo, decimal decId)
{
try
{
SPBom.BomFromDatatable(bominfo, decId);
}
catch (Exception ex)
{
MessageBox.Show("BOMBll2:" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// Function to get particular values from Bom Table based on the parameter
/// </summary>
/// <param name="bomId"></param>
/// <returns></returns>
public BomInfo BomView(decimal bomId)
{
BomInfo bominfo = new BomInfo();
try
{
bominfo = SPBom.BomView(bomId);
}
catch (Exception ex)
{
MessageBox.Show("BOMBll5:" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return(bominfo);
}
public BomInfo BomView(decimal bomId)
{
BomInfo bominfo = new BomInfo();
SqlDataReader sdrreader = null;
try
{
if (base.sqlcon.State == ConnectionState.Closed)
{
base.sqlcon.Open();
}
SqlCommand sccmd = new SqlCommand("BomView", base.sqlcon);
sccmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam2 = new SqlParameter();
sprmparam2 = sccmd.Parameters.Add("@bomId", SqlDbType.Decimal);
sprmparam2.Value = bomId;
sdrreader = sccmd.ExecuteReader();
while (sdrreader.Read())
{
bominfo.BomId = decimal.Parse(((DbDataReader)sdrreader)[0].ToString());
bominfo.ProductId = decimal.Parse(((DbDataReader)sdrreader)[1].ToString());
bominfo.RowmaterialId = decimal.Parse(((DbDataReader)sdrreader)[2].ToString());
bominfo.Quantity = decimal.Parse(((DbDataReader)sdrreader)[3].ToString());
bominfo.UnitId = decimal.Parse(((DbDataReader)sdrreader)[4].ToString());
bominfo.Extra1 = ((DbDataReader)sdrreader)[5].ToString();
bominfo.Extra2 = ((DbDataReader)sdrreader)[6].ToString();
bominfo.ExtraDate = DateTime.Parse(((DbDataReader)sdrreader)[7].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sdrreader.Close();
base.sqlcon.Close();
}
return(bominfo);
}
public void UpdateBom(BomInfo bominfo)
{
try
{
if (base.sqlcon.State == ConnectionState.Closed)
{
base.sqlcon.Open();
}
SqlCommand sccmd = new SqlCommand("UpdateBom", base.sqlcon);
sccmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam9 = new SqlParameter();
sprmparam9 = sccmd.Parameters.Add("@bomId", SqlDbType.Decimal);
sprmparam9.Value = bominfo.BomId;
sprmparam9 = sccmd.Parameters.Add("@productId", SqlDbType.Decimal);
sprmparam9.Value = bominfo.ProductId;
sprmparam9 = sccmd.Parameters.Add("@rowmaterialId", SqlDbType.Decimal);
sprmparam9.Value = bominfo.RowmaterialId;
sprmparam9 = sccmd.Parameters.Add("@quantity", SqlDbType.Decimal);
sprmparam9.Value = bominfo.Quantity;
sprmparam9 = sccmd.Parameters.Add("@unitId", SqlDbType.Decimal);
sprmparam9.Value = bominfo.UnitId;
sprmparam9 = sccmd.Parameters.Add("@extra1", SqlDbType.VarChar);
sprmparam9.Value = bominfo.Extra1;
sprmparam9 = sccmd.Parameters.Add("@extra2", SqlDbType.VarChar);
sprmparam9.Value = bominfo.Extra2;
sprmparam9 = sccmd.Parameters.Add("@extraDate", SqlDbType.DateTime);
sprmparam9.Value = bominfo.ExtraDate;
sccmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
base.sqlcon.Close();
}
}
public Form1()
{
InitializeComponent();
DoubleBuffered = true;
导出处理后的数据();
List <分类表> 读取并分析分类表(string Path = "分类表.xlsx")
{
var 分类列表 = new List <分类表>();
using (var reader =
ExcelReaderFactory.CreateReader(new FileStream(Path, FileMode.Open, FileAccess.Read,
FileShare.ReadWrite)))
{
var TempS = "";
var TempS2 = "";
foreach (DataRow Row in reader.AsDataSet().Tables[0].Rows)
{
if (string.IsNullOrWhiteSpace(Row[0].ToString()))
{
分类列表.Add(new 分类表(TempS, Row[1].ToString(), Row[2].ToString(), TempS2));
}
else
{
TempS = Row[0].ToString();
TempS2 = Row[3].ToString();
分类列表.Add(new 分类表(TempS, Row[1].ToString(), Row[2].ToString(), TempS2));
}
}
}
return(分类列表);
}
Tuple <List <object>, Dictionary <string, List <object[]> > > 读取并分析原始数据(string path = "品号信息20180417.xls")
{
Tuple <List <object>, Dictionary <string, List <object[]> > > Ret;
using (var reader = ExcelReaderFactory.CreateReader(File.Open(path, FileMode.Open)))
{
var Sheel = reader.AsDataSet().Tables[0].Rows;
Ret = new Tuple <List <object>, Dictionary <string, List <object[]> > >(Sheel[0].ItemArray.ToList(),
new Dictionary <string, List <object[]> >());
Sheel.RemoveAt(0);
var 分类号Index = Ret.Item1.FindIndex(x => x.ToString() == "品号");
foreach (DataRow Row in Sheel)
{
var 分类号 = Row[分类号Index].ToString().Split('-')[0];
if (Ret.Item2.ContainsKey(分类号))
{
Ret.Item2[分类号].Add(Row.ItemArray);
}
else
{
Ret.Item2.Add(分类号, new List <object[]> {
Row.ItemArray
});
}
//Interlocked.Increment(ref AllCount);
}
}
return(Ret);
}
void 导出处理后的数据()
{
void InitSheet(ref Worksheet worksheet)
{
worksheet.Cells.ColumnWidth[0] = 3200;
worksheet.Cells.ColumnWidth[1] = 3200;
worksheet.Cells.ColumnWidth[2] = 10000;
worksheet.Cells.ColumnWidth[3] = 10000;
worksheet.Cells.ColumnWidth[4] = 2000;
worksheet.Cells.ColumnWidth[5] = 2000;
worksheet.Cells.ColumnWidth[6] = 10000;
worksheet.Cells.ColumnWidth[7] = 2000;
worksheet.Cells.ColumnWidth[8] = 3200;
worksheet.Cells.ColumnWidth[9] = 2000;
worksheet.Cells.ColumnWidth[10] = 2000;
worksheet.Cells[0, 0] = new Cell("物料编码");
worksheet.Cells[1, 0] = new Cell("Id$");
worksheet.Cells[0, 1] = new Cell("分类（系列号）");
worksheet.Cells[1, 1] = new Cell("classification$<name>");
worksheet.Cells[0, 2] = new Cell("品名（按命名规则）");
worksheet.Cells[1, 2] = new Cell("name$");
worksheet.Cells[0, 3] = new Cell("规格（按命名规则）");
worksheet.Cells[1, 3] = new Cell("Specification");
worksheet.Cells[0, 4] = new Cell("单位");
worksheet.Cells[1, 4] = new Cell("UOM<description>");
worksheet.Cells[0, 5] = new Cell("品号属性");
worksheet.Cells[1, 5] = new Cell("SourceType<name>");
worksheet.Cells[0, 6] = new Cell("快捷码");
worksheet.Cells[1, 6] = new Cell("KJM");
worksheet.Cells[0, 7] = new Cell("主要仓库");
worksheet.Cells[1, 7] = new Cell("ZYCK<name>");
worksheet.Cells[0, 8] = new Cell("会计分类");
worksheet.Cells[1, 8] = new Cell("KJFL<name>");
worksheet.Cells[0, 9] = new Cell("备注");
worksheet.Cells[1, 9] = new Cell("Remark");
worksheet.Cells[0, 10] = new Cell("文件夹(默认即可，不输入）");
worksheet.Cells[1, 10] = new Cell("Folder");
}
/* void 全部数据验证(Workbook book)
* {
* var Count = 0;
* foreach (var Item in book.Worksheets) Count = Count + Item.Cells.Rows.Count - 1;
* Console.WriteLine();
* }*/
var ClassifyList = 读取并分析分类表();
var WorkbookDic = new Dictionary <string, Workbook>();
var OpenXls = new OpenFileDialog();
OpenXls.Filter = @"Excel|*.xls";
OpenXls.ShowDialog();
if (!File.Exists(OpenXls.FileName))
{
return;
}
var OriData = 读取并分析原始数据(OpenXls.FileName);
var HeaderIndex = new int[10];
for (var i = 0; i < HeaderIndex.Length; i++)
{
switch (i)
{
case 0:
HeaderIndex[i] = OriData.Item1.FindIndex(x => x.ToString() == "品号");
break;
case 1:
HeaderIndex[i] = OriData.Item1.FindIndex(x => x.ToString() == "品名");
break;
case 2:
HeaderIndex[i] = OriData.Item1.FindIndex(x => x.ToString() == "规格");
break;
case 3:
HeaderIndex[i] = OriData.Item1.FindIndex(x => x.ToString() == "单位");
break;
case 4:
HeaderIndex[i] = OriData.Item1.FindIndex(x => x.ToString() == "品号属性");
break;
case 5:
HeaderIndex[i] = OriData.Item1.FindIndex(x => x.ToString() == "快捷码");
break;
case 6:
HeaderIndex[i] = OriData.Item1.FindIndex(x => x.ToString() == "主要仓库");
break;
case 7:
HeaderIndex[i] = OriData.Item1.FindIndex(x => x.ToString() == "会计");
break;
case 8:
HeaderIndex[i] = OriData.Item1.FindIndex(x => x.ToString() == "备注");
break;
}
}
foreach (var Sheets in OriData.Item2)
{
Workbook workbook;
Worksheet worksheet;
var Add = false;
var SheetName = (from Item in ClassifyList
where Sheets.Key.StartsWith(Item.分类号)
select Item).FirstOrDefault();
if (string.IsNullOrWhiteSpace(SheetName.一级))
{
continue;
}
if (SheetName.一级 == "未知分类")
{
// continue;
}
if (WorkbookDic.ContainsKey(SheetName.一级))
{
workbook = WorkbookDic[SheetName.一级];
worksheet = workbook.Worksheets[0];
}
else
{
Add = true;
workbook = new Workbook();
worksheet = new Worksheet(SheetName.页名);
InitSheet(ref worksheet);
}
var DicCount = worksheet.Cells.Rows.Count;
for (var i = 0; i < Sheets.Value.Count; i++, DicCount++)
{
worksheet.Cells[DicCount, 0] = new Cell(Sheets.Value[i][HeaderIndex[0]].ToString()); //品号
worksheet.Cells[DicCount, 1] = new Cell(Sheets.Key); //系列号
worksheet.Cells[DicCount, 2] = new Cell(Sheets.Value[i][HeaderIndex[1]].ToString()); //品名
worksheet.Cells[DicCount, 3] = new Cell(Sheets.Value[i][HeaderIndex[2]].ToString()); //规格
worksheet.Cells[DicCount, 4] = new Cell(Sheets.Value[i][HeaderIndex[3]].ToString()); //库存单位
worksheet.Cells[DicCount, 5] = new Cell(Sheets.Value[i][HeaderIndex[4]].ToString()); //品号属性
worksheet.Cells[DicCount, 6] = new Cell(Sheets.Value[i][HeaderIndex[5]].ToString()); //快捷码
worksheet.Cells[DicCount, 7] = new Cell(Sheets.Value[i][HeaderIndex[6]].ToString()); //主要仓库
worksheet.Cells[DicCount, 8] = new Cell(Sheets.Value[i][HeaderIndex[7]].ToString()); //会计
worksheet.Cells[DicCount, 9] = new Cell(HeaderIndex[8] == -1 ? "" : Sheets.Value[i][HeaderIndex[8]].ToString()); //备注缺失
worksheet.Cells[DicCount, 10] = new Cell(@"L:\"); //文件夹
}
if (Add)
{
workbook.Worksheets.Add(worksheet);
WorkbookDic.Add(SheetName.一级, workbook);
}
}
foreach (var item in WorkbookDic)
{
item.Value.Save($"{item.Key}.xls");
}
//全部数据验证(workbook);
}
void ReadXls(string path)
{
using (var reader = ExcelReaderFactory.CreateReader(File.Open(path, FileMode.Open)))
{
foreach (DataTable item in reader.AsDataSet().Tables)
{
var DataSet = new DataGridView
{
ReadOnly = true,
AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells,
AllowUserToAddRows = false,
Dock = DockStyle.Fill,
Name = item.TableName
};
ShowTab.TabPages.Add(item.TableName, item.TableName);
foreach (var FirstRow in item.Rows[0].ItemArray)
{
DataSet.Columns.Add(FirstRow.ToString(), FirstRow.ToString());
DataSet.Columns[DataSet.Columns.Count - 1].SortMode =
DataGridViewColumnSortMode.NotSortable;
}
item.Rows.RemoveAt(0);
foreach (DataRow DataBase in item.Rows)
{
DataSet.Rows.Add(DataBase.ItemArray);
}
DataSet.Update();
ShowTab.TabPages[item.TableName].Controls.Add(DataSet);
}
}
}
void SaveXls(string path, Dictionary <string, List <BomInfo> > SaveInfo)
{
var workbook = new Workbook();
var count = 0;
foreach (var BomItem in SaveInfo)
{
var S1 = BomItem.Key.Split(new[] { '&' }, StringSplitOptions.RemoveEmptyEntries);
var S2 = S1[0].Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
if (S1.Length != 1)
{
var S3 = S1[1].Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
var worksheet = new Worksheet("Sheet" + count);
worksheet.Cells.ColumnWidth[0] = 3200;
worksheet.Cells.ColumnWidth[1] = 10000;
worksheet.Cells.ColumnWidth[2] = 10000;
worksheet.Cells[0, 0] = new Cell("元件组合");
worksheet.Cells[1, 0] = new Cell(S2[0]);
worksheet.Cells[1, 1] = new Cell(S2[1]);
worksheet.Cells[2, 0] = new Cell(S3[0]);
worksheet.Cells[2, 1] = new Cell(S3[1]);
worksheet.Cells[3, 0] = new Cell("品号");
worksheet.Cells[3, 1] = new Cell("品名");
for (var i = 0; i < BomItem.Value.Count; i++)
{
worksheet.Cells[4 + i, 0] = new Cell(BomItem.Value[i].主件品号);
worksheet.Cells[4 + i, 1] = new Cell(BomItem.Value[i].品名);
}
workbook.Worksheets.Add(worksheet);
}
else
{
var worksheet = new Worksheet("Sheet" + count);
worksheet.Cells.ColumnWidth[0] = 3200;
worksheet.Cells.ColumnWidth[1] = 3200;
worksheet.Cells.ColumnWidth[2] = 3200;
worksheet.Cells[0, 0] = new Cell("元件组合");
worksheet.Cells[1, 0] = new Cell(S2[0]);
worksheet.Cells[1, 1] = new Cell(S2[1]);
worksheet.Cells[2, 0] = new Cell("品号");
worksheet.Cells[2, 1] = new Cell("品名");
for (var i = 0; i < BomItem.Value.Count; i++)
{
worksheet.Cells[3 + i, 0] = new Cell(BomItem.Value[i].主件品号);
worksheet.Cells[3 + i, 1] = new Cell(BomItem.Value[i].品名);
}
workbook.Worksheets.Add(worksheet);
}
count++;
}
workbook.Save(path);
}
LoadXls.Click += delegate
{
var OpenFile = new OpenFileDialog
{
Filter = @" Excel文件 | *.xls;*.xlsx",
Title = @"打开表格"
};
OpenFile.ShowDialog();
if (File.Exists(OpenFile.FileName))
{
ReadXls(OpenFile.FileName);
}
};
Handler.Click += delegate
{
// try
{
var 元件品号序列 = -1;
var 品名序列 = -1;
var 主件品号序列 = -1;
var DataGrid = ShowTab.SelectedTab.Controls[0] as DataGridView;
for (var i = 0; i < DataGrid.ColumnCount; i++)
{
switch (DataGrid.Columns[i].Name)
{
case "元件品号":
元件品号序列 = i;
break;
case "品 名":
品名序列 = i;
break;
case "主件品号":
主件品号序列 = i;
break;
}
}
var 品名 = "";
var TempBomInfo = new BomInfo();
var SaveInfo = new Dictionary <string, List <BomInfo> >();
foreach (DataGridViewRow item in DataGrid.Rows)
{
if (string.IsNullOrWhiteSpace(item.Cells[元件品号序列].Value.ToString()))
{
if (品名 != "")
{
if (SaveInfo.ContainsKey(品名))
{
SaveInfo[品名].Add(TempBomInfo);
}
else
{
SaveInfo.Add(品名, new List <BomInfo> {
TempBomInfo
});
}
}
TempBomInfo = new BomInfo
{
品名 = item.Cells[品名序列].Value.ToString(),
主件品号 = item.Cells[主件品号序列].Value.ToString()
};
品名 = "";
}
else
{
品名 += item.Cells[元件品号序列].Value + "|" + item.Cells[品名序列].Value + "&";
}
}
var SaveFile = new SaveFileDialog
{
Filter = @" Excel文件 | *.xls",
Title = @"保存表格"
};
SaveFile.ShowDialog();
if (!string.IsNullOrWhiteSpace(SaveFile.FileName))
{
SaveXls(SaveFile.FileName, SaveInfo);
}
}
/* catch (Exception)
* {
* }*/
};
// this.Close();
}
/// <summary>
/// Function to add data to tbl_Bom
/// </summary>
public void BomTableFill()
{
try
{
BomInfo infoBom = new BomInfo();
ProductBomBll BllProductBom = new ProductBomBll();
decimal decProductId;
if (btnSave.Text == "Update")
{
decProductId = decProductIdForEdit;
}
else
{
decProductId = decSaveProduct;
}
for (int i = 0; i < dtblBom.Rows.Count; i++)
{
infoBom.RowmaterialId = Convert.ToDecimal(dtblBom.Rows[i]["dgvcmbRawMaterial"]);
infoBom.UnitId = Convert.ToDecimal(dtblBom.Rows[i]["dgvtxtUnitId"]);
infoBom.Quantity = Convert.ToDecimal(dtblBom.Rows[i]["dgvtxtQty"]);
infoBom.Extra1 = dtblBom.Rows[i]["extra1"].ToString();
infoBom.Extra2 = dtblBom.Rows[i]["extra2"].ToString();
infoBom.ExtraDate = Convert.ToDateTime(dtblBom.Rows[i]["extraDate"]);
BllProductBom.BomFromDatatable(infoBom, decProductId);
}
}
catch (Exception ex)
{
MessageBox.Show("PC:2" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// Function to Edit tbl_Bom
/// </summary>
public void BomTableEditFill()
{
try
{
BomInfo infoBom = new BomInfo();
ProductBomBll BllProductBom = new ProductBomBll();
for (int i = 0; i < dtblFromBomForUpdate.Rows.Count; i++)
{
infoBom.RowmaterialId = Convert.ToDecimal(dtblFromBomForUpdate.Rows[i]["dgvcmbRawMaterial"]);
infoBom.UnitId = Convert.ToDecimal(dtblFromBomForUpdate.Rows[i]["dgvtxtUnitId"]);
infoBom.Quantity = Convert.ToDecimal(dtblFromBomForUpdate.Rows[i]["dgvtxtQty"]);
infoBom.Extra1 = dtblFromBomForUpdate.Rows[i]["extra1"].ToString();
infoBom.Extra2 = dtblFromBomForUpdate.Rows[i]["extra2"].ToString();
infoBom.ExtraDate = Convert.ToDateTime(dtblFromBomForUpdate.Rows[i]["extraDate"]);
infoBom.BomId = Convert.ToDecimal(dtblFromBomForUpdate.Rows[i]["bomId"]);
infoBom.ProductId = decProductIdForEdit;
BllProductBom.UpdateBom(infoBom);
}
}
catch (Exception ex)
{
MessageBox.Show("PC:10" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// Function for delete data from tbl_BOM
/// </summary>
public void RemoveBom()
{
try
{
BomInfo infoBom = new BomInfo();
ProductBomBll BllProductBom = new ProductBomBll();
int inC = 0;
while (strArrOfRemoveForBom[inC] != null)
{
decimal decId = Convert.ToDecimal(strArrOfRemoveForBom[inC]);
BllProductBom.BomRemoveRows(decId);
inC++;
}
}
catch (Exception ex)
{
MessageBox.Show("PC:65" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// Function for new row added for Bom
/// </summary>
public void NewRowAddedForBom()
{
try
{
BomInfo infoBom = new BomInfo();
ProductBomBll BllProductBom = new ProductBomBll();
for (int inI = 0; inI < dtblFromBomForUpdate.Rows.Count; inI++)
{
if (Convert.ToDecimal(dtblFromBomForUpdate.Rows[inI]["bomId"]) == 0)
{
infoBom.RowmaterialId = Convert.ToDecimal(dtblFromBomForUpdate.Rows[inI]["dgvcmbRawMaterial"]);
infoBom.UnitId = Convert.ToDecimal(dtblFromBomForUpdate.Rows[inI]["dgvtxtUnitId"]);
infoBom.Quantity = Convert.ToDecimal(dtblFromBomForUpdate.Rows[inI]["dgvtxtQty"]);
infoBom.Extra1 = dtblFromBomForUpdate.Rows[inI]["extra1"].ToString();
infoBom.Extra2 = dtblFromBomForUpdate.Rows[inI]["extra2"].ToString();
infoBom.ExtraDate = Convert.ToDateTime(dtblFromBomForUpdate.Rows[inI]["extraDate"]);
BllProductBom.BomFromDatatable(infoBom, decProductIdForEdit);
}
}
}
catch (Exception ex)
{
MessageBox.Show("PC:78" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private List <SqlCommand> createBomHeaderCmd(string BomNo, BomInfo BomInfo, DataRow HeadRow, List <BomFormatDetail> ExcelFormatDtls)
{
string str = @"Insert Into MF_BOM({0}) values ({1})";
string strZ = @"Insert Into MF_BOM_Z({0}) values ({1})";
List <SqlCommand> cmds = new List <SqlCommand>();
var headerFields = ExcelFormatDtls
.Where(o => o.cell_type == "SYS" && (o.diy_type == "1" || o.diy_type == "3"))
.ToList();
Dictionary <string, string> dicts = new Dictionary <string, string>();
dicts.Add("bom_no", BomNo);
foreach (var item in headerFields)
{
//Bom头主数量=1
if (item.field_no == "qty")
{
dicts.Add(item.field_no, "1");
}
//货名以 ERP的为准
else if (item.field_no == "name")
{
dicts.Add(item.field_no, Service.GetPrdtName(HeadRow.GetSting("prd_no")));
}
else
{
dicts.Add(item.field_no, HeadRow.GetSting(item.field_no));
}
}
if (dicts.ContainsKey("prd_knd") == false)
{
dicts.Add("prd_knd", BomInfo.prd_knd);
}
foreach (var item in BomService.DefaultBomHeaderSection)
{
//有了设值，覆盖默认值
if (dicts.ContainsKey(item.Key.ToLower()) == true)
{
continue;
}
dicts.Add(item.Key, GetDefaultVariable(item.Value));
}
cmds.Add(new SqlCommand(str.FormatOrg(
string.Join(",", dicts.Select(o => o.Key)),
string.Join(",", dicts.Select(o => "'" + o.Value + "'"))
)));
var headerZFields = ExcelFormatDtls
.Where(o => o.cell_type == "DIY" && (o.diy_type == "1" || o.diy_type == "3"))
.ToList();
if (headerZFields.Count > 0)
{
Dictionary <string, string> zDicts = new Dictionary <string, string>();
zDicts.Add("bom_no", BomNo);
foreach (var item in headerZFields)
{
zDicts.Add(item.field_no, HeadRow.GetSting(item.field_no));
}
cmds.Add(new SqlCommand(strZ.FormatOrg(
string.Join(",", zDicts.Select(o => o.Key)),
string.Join(",", zDicts.Select(o => "'" + o.Value + "'"))
)));
}
return(cmds);
}
private List <SqlCommand> DoImportToBom(BomInfo BomInfo, List <BomFormatDetail> ExcelFormatDtls, bool IsTopLevelBom)
{
//导入到BOM上，
// 要不要区分开 T8， Sunlike 不同，做工厂模式？ 答：不用区分，使用：DefaultFieldValue
// 先从 顶->叶 还是 叶->顶生成 Bom?
// 非顶级的，要看 子BOM是否存在，如果已存在则不用生成子BOM
// 字段 SYS + DefaultFieldValue + Z
// SYS + DefaultFieldValue冲突时 考虑优先级
// 防呆：如何BOM已存在， BOM 是否本人的？ 是否已使用[MRP, MO]
// In_NO 要不要计算？
List <SqlCommand> cmds = new List <SqlCommand>();
string bomNo = BomInfo.bom_no;
if (bomNo.IsNullOrEmpty())
{
throw new Exception("配方号[{0}]为空!".FormatOrg(BomInfo.HeadRow.GetSting("prd_no")));
}
bool isNew = Service.GetBom(BomInfo.prd_no, BomInfo.bom_no) == null ? true : false;
if (IsTopLevelBom == true && isNew == false)
{
isNew = true;
//二次导入时,重构顶级Bom
cmds.AddRange(removeBom(bomNo));
}
//2019-12-25 考虑升级BOM与覆盖BOM
if (IsTopLevelBom == false && isNew == false)
{
if (BomInfo.HeadRow.GetSting("check_ask_radio").IsNotEmpty() &&
BomInfo.HeadRow.GetSting("check_ask_radio").GetInt() > 0)
{
//1.覆盖 2.升级BOM版本
int bomConflictHandleWay = BomInfo.HeadRow.GetSting("check_ask_radio").GetInt();
if (bomConflictHandleWay == 1)
{
isNew = true;
cmds.AddRange(removeBom(bomNo));
}
else
{
throw new Exception("BOM冲突未处理!");
}
//else
//{ 有问题，如果系统上存在 多个BOM，也会报错
// //升级BOM版本，找出最新的版本号 + 1
// var maxPF_Bom = Service.GetMaxPF_Bom(BomInfo.prd_no);
// int maxPF = maxPF_Bom == null ? 2 : ++maxPF_Bom.pf_no;
//}
}
}
List <SqlCommand> thisCmds = new List <SqlCommand>();
if (isNew == true)
{
thisCmds.AddRange(createBomHeaderCmd(bomNo, BomInfo, BomInfo.HeadRow, ExcelFormatDtls));
}
foreach (BomInfo item in BomInfo.BodyInfos)
{
cmds.AddRange(DoImportToBom(item, ExcelFormatDtls, false));
}
if (isNew == true)
{
thisCmds.AddRange(createBomBodyCmd(bomNo, BomInfo.BodyRows, ExcelFormatDtls));
}
cmds.AddRange(thisCmds);
return(cmds);
}
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);
}