/// <summary>
/// 更新BOM
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e)
{
ExtractInventoryTool_BOM bom = null;
DataGridViewSelectedRowCollection rowCollection = dataGridView1.SelectedRows;
DataGridViewRow row = null;
if (rowCollection.Count != 1)
{
MessageBox.Show("只能选中一条记录进行更新", "Warning");
return;
}
row = rowCollection[0];
if (row != null && row.Cells[0].Value != null)
{
bom = new ExtractInventoryTool_BOM();
int oid = 0;
bom.Oid = int.TryParse(row.Cells[0].Value.ToString().Trim(), out oid) ? oid : 0;
bom.VehicleModelCode = row.Cells[1].Value.ToString().Trim();
int materialOid = 0;
bom.Material = int.TryParse(row.Cells[2].Value.ToString().Trim(), out materialOid) ? materialOid : 0;
int unitUsage = 0;
bom.UnitUsage = int.TryParse(row.Cells[9].Value.ToString().Trim(), out unitUsage) ? unitUsage : 0;
bom.UpdateTime = (DateTime)row.Cells[10].Value;
}
Form_BOMEditor editor = new Form_BOMEditor(bom);
editor.ShowDialog(this);
if (editor.DialogResult == DialogResult.OK)
{
Task.Run(() => QueryBOM());
}
return;
}
/// <summary>
/// 提交
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
#region 判空处理
if (string.IsNullOrWhiteSpace(textBox2.Text))
{
MessageBox.Show("车辆代码不能为空", "Warning");
return;
}
if (numericUpDown1.Value <= 0)
{
MessageBox.Show("单位用量必须大于1", "Warning");
return;
}
#endregion
#region 装载Ro
ExtractInventoryTool_BOM bom = new ExtractInventoryTool_BOM();
int oid = 0;
bom.Oid = int.TryParse(textBox1.Text.Trim(), out oid) ? oid : 0;
bom.Material = ((ExtractInventoryTool_MaterialExtension)comboBox1.SelectedItem).Oid;
bom.VehicleModelCode = textBox2.Text.Trim();
bom.UnitUsage = Convert.ToInt32(numericUpDown1.Value);
bom.UpdateTime = DateTime.Now;
bom.UniqueCode = _materialList.First(m => m.Oid == bom.Material).UniqueCode + "v" + bom.VehicleModelCode;
#endregion
Task.Run(() => InsertOrUpdateMaterial(bom));
return;
}
public void InsertOrUpdateBOM(ExtractInventoryTool_BOM bom, out string errorMessage)
{
lock (lockObj)
{
try
{
errorMessage = string.Empty;
if (bom == null)
{
errorMessage = "BOM信息为空";
return;
}
string oid = bom.Oid == 0 ? string.Empty : bom.Oid.ToString();
if (IsExistsUniqueCode(bom.UniqueCode, "BOM", oid))
{
errorMessage = "该BOM已存在，无法添加相同BOM";
return;
}
StringBuilder noQueryStrbd = new StringBuilder();
List <SQLiteParameter[]> paramList = new List <SQLiteParameter[]>();
SQLiteParameter[] parameter =
{
SQLiteHelper.MakeSQLiteParameter("@Oid", DbType.Int32, bom.Oid),
SQLiteHelper.MakeSQLiteParameter("@VehicleModelCode", DbType.String, bom.VehicleModelCode),
SQLiteHelper.MakeSQLiteParameter("@Material", DbType.Int32, bom.Material),
SQLiteHelper.MakeSQLiteParameter("@UnitUsage", DbType.Int32, bom.UnitUsage),
SQLiteHelper.MakeSQLiteParameter("@UpdateTime", DbType.DateTime, bom.UpdateTime),
SQLiteHelper.MakeSQLiteParameter("@UniqueCode", DbType.String, bom.UniqueCode)
};
paramList.Add(parameter);
if (bom.Oid == 0)
{
//添加新数据
noQueryStrbd.Append(@"Insert into BOM (VehicleModelCode,Material,UnitUsage,UpdateTime,UniqueCode) ")
.Append(@"values ( ")
.Append(@"@VehicleModelCode,@Material,@UnitUsage,@UpdateTime,@UniqueCode ")
.Append(@")");
}
else
{
//更新数据
noQueryStrbd.Append(@"Update BOM set VehicleModelCode=@VehicleModelCode,Material=@Material,UnitUsage=@UnitUsage,UpdateTime=@UpdateTime,UniqueCode=@UniqueCode ")
.Append(@" WHERE Oid=@Oid");
}
new SQLiteHelper().ExecuteNonQueryBatch(noQueryStrbd.ToString(), paramList);
return;
}
catch (Exception ex)
{
throw ex;
}
}
}
/// <summary>
/// 添加/更新物料配置
/// </summary>
/// <param name="bom"></param>
private void InsertOrUpdateMaterial(ExtractInventoryTool_BOM bom)
{
try
{
string errorMessage = string.Empty;
new ExtractInventoryTool_BOMBLL().InsertOrUpdateBOM(bom, out errorMessage);
InsertOrUpdateBOMCallBackDel del = InsertOrUpdateBOMCallBack;
this.BeginInvoke(del, errorMessage);
return;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 导入BOM到数据库
/// </summary>
/// <param name="bomList"></param>
/// <param name="isCover">是否覆盖</param>
/// <param name="errorMessage"></param>
/// <returns></returns>
public bool ImportBOM(List <ExtractInventoryTool_BOM> bomList, bool isCover, out string errorMessage)
{
lock (lockObj)
{
try
{
errorMessage = string.Empty;
if (bomList == null || bomList.Count == 0)
{
errorMessage = "BOM信息为空";
return(false);
}
//先查询出所有的BOM唯一码
DataTable allMaterial = GetAllUniqueCode("BOM");
List <ExtractInventoryTool_BOM> allBOMList = new List <ExtractInventoryTool_BOM>();
foreach (DataRow row in allMaterial.Rows)
{
allBOMList.Add(new ExtractInventoryTool_BOM()
{
Oid = Int32.Parse(row["Oid"].ToString()),
UniqueCode = row["UniqueCode"].ToString()
});
}
StringBuilder insertStrbd = new StringBuilder();
StringBuilder updateStrbd = new StringBuilder();
List <SQLiteParameter[]> insertParamList = new List <SQLiteParameter[]>();
List <SQLiteParameter[]> updateParamList = new List <SQLiteParameter[]>();
foreach (var bom in bomList)
{
ExtractInventoryTool_BOM exitsBOM = allBOMList.FirstOrDefault(m => m.UniqueCode.Trim().Equals(bom.UniqueCode.Trim()));
if (exitsBOM != null && exitsBOM.Oid != 0)//导入数据如果表里有就更新，没有就新建
{
if (!isCover)
{
errorMessage = "存在重复数据，是否覆盖？";
return(true);
}
bom.Oid = exitsBOM.Oid;
}
SQLiteParameter[] parameter =
{
SQLiteHelper.MakeSQLiteParameter("@Oid", DbType.Int32, bom.Oid),
SQLiteHelper.MakeSQLiteParameter("@VehicleModelCode", DbType.String, bom.VehicleModelCode),
SQLiteHelper.MakeSQLiteParameter("@Material", DbType.Int32, bom.Material),
SQLiteHelper.MakeSQLiteParameter("@UnitUsage", DbType.Int32, bom.UnitUsage),
SQLiteHelper.MakeSQLiteParameter("@UpdateTime", DbType.DateTime, bom.UpdateTime),
SQLiteHelper.MakeSQLiteParameter("@UniqueCode", DbType.String, bom.UniqueCode)
};
if (exitsBOM != null && exitsBOM.Oid != 0)
{
updateParamList.Add(parameter);
}
else
{
insertParamList.Add(parameter);
}
}
//添加新数据
insertStrbd.Append(@"Insert into BOM (VehicleModelCode,Material,UnitUsage,UpdateTime,UniqueCode) ")
.Append(@"values ( ")
.Append(@"@VehicleModelCode,@Material,@UnitUsage,@UpdateTime,@UniqueCode ")
.Append(@")");
new SQLiteHelper().ExecuteNonQueryBatch(insertStrbd.ToString(), insertParamList);
updateStrbd.Append(@"Update BOM set VehicleModelCode=@VehicleModelCode,Material=@Material,UnitUsage=@UnitUsage,UpdateTime=@UpdateTime,UniqueCode=@UniqueCode ")
.Append(@" WHERE Oid=@Oid");
new SQLiteHelper().ExecuteNonQueryBatch(updateStrbd.ToString(), updateParamList);
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
}
/// <summary>
/// 读取Excel表，返回BOM集合
/// </summary>
/// <param name="fileName">文件路径</param>
/// <param name="client">客户信息</param>
/// <returns></returns>
public List <ExtractInventoryTool_BOM> ExcelToBOMList(string fileName, ExtractInventoryTool_Client client, out string errorMessage)
{
List <ExtractInventoryTool_BOM> result = new List <ExtractInventoryTool_BOM>();
errorMessage = string.Empty;
#region 读取客户配置
string clientRuleConfig = ConfigurationManager.AppSettings[client.UniqueCode + "BOM"].ToString();
ExtractInventoryTool_BOMImportRule clientRule = JsonConvert.DeserializeObject <ExtractInventoryTool_BOMImportRule>(clientRuleConfig);
#endregion
IWorkbook workbook = null;
ISheet sheet = null;
List <string> uniqueCodeList = new List <string>();
try
{
using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
#region 获取Excel
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fs);
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fs);
}
sheet = workbook.GetSheetAt(clientRule.St);
if (sheet == null)
{
return(result);
}
#endregion
#region 读取Excel
IRow vehicleCodeRow = sheet.GetRow(clientRule.VR);
#region 读取BOM的思路
// 每一行都是唯一一个物料，供应商代码和物料号作为唯一标识，这里做一个行列的双循环，在每一行中，有相同车型代码
// 的不同用量，先收集用量不为0的所有用量，然后按车型代码分组，取同种车型代码用量最大的用量，加入到最终结果中
// Meritar_Jeffrey 2021/04/05 02:25:25
#endregion
#region 获取所有当前客户的物料
DataTable dt = new ExtractInventoryTool_MaterialBLL().QueryMaterialByClientID(client.Oid.ToString());
List <ExtractInventoryTool_Material> materialList = new List <ExtractInventoryTool_Material>();
foreach (DataRow row in dt.Rows)
{
materialList.Add(new ExtractInventoryTool_Material()
{
Oid = Int32.Parse(row["Oid"].ToString()),
Code = row["Code"].ToString(),
SupplierCode = row["SupplierCode"].ToString(),
UniqueCode = row["UniqueCode"].ToString()
});
}
#endregion
for (int i = clientRule.MR; i <= sheet.LastRowNum; i++)//外循环--行
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (row.GetCell(clientRule.MC) == null)
{
continue;
}
string materialCode = ConvertCellToString(row.GetCell(clientRule.MC));
string supplierCode = ConvertCellToString(row.GetCell(clientRule.SC));
if (string.IsNullOrEmpty(materialCode))
{
continue;
}
List <ExtractInventoryTool_BOM> rowBomList = new List <ExtractInventoryTool_BOM>();
//判断物料是否已备案
ExtractInventoryTool_Material rowMaterial = materialList
.FirstOrDefault(m => m.Code.Trim().Equals(materialCode) &&
m.SupplierCode.Trim().Equals(supplierCode));
if (rowMaterial == null)
{
errorMessage = string.Format("第{0}行物料号{1}供应商代码{2}没有在系统备案", i + 1, materialCode, supplierCode);
return(result);
}
bool isExist = false;
string uniqueCode = rowMaterial.UniqueCode;
//判断是否存在重复行
// 这里如果有重复行不再直接返回，而且新增bom和老bom取最大值
// Meritar_Jeffrey 2021/04/16 09:45:14
if (uniqueCodeList.Contains(uniqueCode))//这里判断一下有没有同种物料重复，如果有，直接返回，输出重复物料
{
//errorMessage = string.Format("第{0}行物料号{1}供应商代码{2}存在重复BOM记录", i + 1, materialCode, supplierCode);
//return result;
isExist = true;
}
else
{
uniqueCodeList.Add(uniqueCode);
}
for (int j = clientRule.VC; j <= row.LastCellNum - 3; j++)//内循环--列
{
ICell cell = row.GetCell(j);
if (cell == null || string.IsNullOrEmpty(cell.ToString()))
{
continue;
}
int unitUsage = 0;
unitUsage = Convert.ToInt32(cell.ToString());
if (unitUsage == 0)
{
continue;
}
ExtractInventoryTool_BOM bomCell = new ExtractInventoryTool_BOM();
bomCell.VehicleModelCode = Regex.Match(ConvertCellToString(vehicleCodeRow.GetCell(j)), client.RegexRule).Value;
bomCell.UnitUsage = unitUsage;
rowBomList.Add(bomCell);
}
IEnumerable <IGrouping <string, ExtractInventoryTool_BOM> > group = rowBomList.GroupBy(v => v.VehicleModelCode);
foreach (var groupItem in group)
{
ExtractInventoryTool_BOM bom = new ExtractInventoryTool_BOM();
bom.UnitUsage = groupItem.Max(v => v.UnitUsage);
bom.VehicleModelCode = groupItem.Key;
bom.Material = rowMaterial.Oid;
bom.UpdateTime = DateTime.Now;
bom.UniqueCode = uniqueCode + "v" + groupItem.Key;
if (isExist)
{
var existBom = result.FirstOrDefault(b => b.UniqueCode.Equals(bom.UniqueCode));
if (existBom == null)
{
result.Add(bom);
}
else
{
existBom.UnitUsage = Math.Max(existBom.UnitUsage, bom.UnitUsage);
existBom.UpdateTime = DateTime.Now;
}
}
else
{
result.Add(bom);
}
}
}
#endregion
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public Form_BOMEditor(ExtractInventoryTool_BOM bom)
{
InitializeComponent();
_bom = bom;
}
