/// <summary>
/// 新增物料
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
int clientOid = 0;
ExtractInventoryTool_Material material = 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)
{
material = new ExtractInventoryTool_Material();
material.Oid = 0;
material.Client = int.TryParse(row.Cells[0].Value.ToString().Trim(), out clientOid) ? clientOid : 0;
}
Form_MaterialEditor editor = new Form_MaterialEditor(material);
editor.ShowDialog(this);
if (editor.DialogResult == DialogResult.OK)
{
Task.Run(() => QueryMaterialByClientID(clientOid.ToString()));
}
return;
}
/// <summary>
/// 添加/更新物料配置
/// </summary>
/// <param name="material"></param>
public void InsertOrUpdateMaterial(ExtractInventoryTool_Material material, out string errorMessage)
{
lock (lockObj)
{
try
{
errorMessage = string.Empty;
if (material == null)
{
MessageBox.Show("物料信息为空", "Error");
}
string oid = material.Oid == 0 ? string.Empty : material.Oid.ToString();
if (IsExistsUniqueCode(material.UniqueCode, "Material", oid))
{
errorMessage = "该物料已存在，无法添加相同物料";
return;
}
StringBuilder noQueryStrbd = new StringBuilder();
List <SQLiteParameter[]> paramList = new List <SQLiteParameter[]>();
SQLiteParameter[] parameter =
{
SQLiteHelper.MakeSQLiteParameter("@Oid", DbType.Int32, material.Oid),
SQLiteHelper.MakeSQLiteParameter("@Client", DbType.Int32, material.Client),
SQLiteHelper.MakeSQLiteParameter("@Name", DbType.String, material.Name),
SQLiteHelper.MakeSQLiteParameter("@Code", DbType.String, material.Code),
SQLiteHelper.MakeSQLiteParameter("@Supplier", DbType.String, material.Supplier),
SQLiteHelper.MakeSQLiteParameter("@SupplierCode", DbType.String, material.SupplierCode),
SQLiteHelper.MakeSQLiteParameter("@UniqueCode", DbType.String, material.UniqueCode)
};
paramList.Add(parameter);
if (material.Oid == 0)
{
//添加新数据
noQueryStrbd.Append(@"Insert into Material (Client,Name,Code,Supplier,SupplierCode,UniqueCode) ")
.Append(@"values ( ")
.Append(@"@Client,@Name,@Code,@Supplier,@SupplierCode,@UniqueCode ")
.Append(@")");
}
else
{
//更新数据
noQueryStrbd.Append(@"Update Material set Client=@Client,Name=@Name,Code=@Code,Supplier=@Supplier,SupplierCode=@SupplierCode,UniqueCode=@UniqueCode ")
.Append(@" WHERE Oid=@Oid");
}
new SQLiteHelper().ExecuteNonQueryBatch(noQueryStrbd.ToString(), paramList);
return;
}
catch (Exception ex)
{
throw ex;
}
}
}
/// <summary>
/// 添加/更新物料配置
/// </summary>
/// <param name="material"></param>
private void InsertOrUpdateMaterial(ExtractInventoryTool_Material material)
{
try
{
string errorMessage = string.Empty;
new ExtractInventoryTool_MaterialBLL().InsertOrUpdateMaterial(material, out errorMessage);
InsertOrUpdateMaterialCallBackDel del = InsertOrUpdateMaterialCallBack;
this.BeginInvoke(del, errorMessage);
return;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 更新物料
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e)
{
int clientOid = 0;
ExtractInventoryTool_Material material = null;
DataGridViewSelectedRowCollection clientRowCollection = dataGridView1.SelectedRows;
DataGridViewSelectedRowCollection materialRowCollection = dataGridView2.SelectedRows;
DataGridViewRow clientRow = null;
DataGridViewRow materialRow = null;
if (clientRowCollection.Count != 1)
{
MessageBox.Show("请选中一条客户进行更新物料备案", "Warning");
return;
}
if (materialRowCollection.Count != 1)
{
MessageBox.Show("请选中一条物料进行更新", "Warning");
return;
}
clientRow = clientRowCollection[0];
materialRow = materialRowCollection[0];
if (clientRow != null && clientRow.Cells[0].Value != null &&
materialRow != null && materialRow.Cells[0].Value != null)
{
material = new ExtractInventoryTool_Material();
int oid = 0;
material.Oid = int.TryParse(materialRow.Cells[0].Value.ToString().Trim(), out oid) ? oid : 0;
material.Name = materialRow.Cells[1].Value.ToString().Trim();
material.Code = materialRow.Cells[2].Value.ToString().Trim();
material.Supplier = materialRow.Cells[3].Value.ToString().Trim();
material.SupplierCode = materialRow.Cells[4].Value.ToString().Trim();
material.Client = int.TryParse(clientRow.Cells[0].Value.ToString().Trim(), out clientOid) ? clientOid : 0;
}
Form_MaterialEditor editor = new Form_MaterialEditor(material);
editor.ShowDialog(this);
if (editor.DialogResult == DialogResult.OK)
{
Task.Run(() => QueryMaterialByClientID(clientOid.ToString()));
}
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 (string.IsNullOrWhiteSpace(textBox3.Text))
{
MessageBox.Show("代码不能为空", "Warning");
return;
}
if (string.IsNullOrWhiteSpace(textBox4.Text))
{
MessageBox.Show("供应商名称不能为空", "Warning");
return;
}
if (string.IsNullOrWhiteSpace(textBox5.Text))
{
MessageBox.Show("供应商代码不能为空", "Warning");
return;
}
#endregion
#region 装载Ro
ExtractInventoryTool_Material material = new ExtractInventoryTool_Material();
int oid = 0;
material.Oid = int.TryParse(textBox1.Text.Trim(), out oid) ? oid : 0;
int clientOid = 0;
material.Client = int.TryParse(comboBox1.SelectedValue.ToString(), out clientOid) ? clientOid : 0;
material.Name = textBox2.Text.Trim();
material.Code = textBox3.Text.Trim();
material.Supplier = textBox4.Text.Trim();
material.SupplierCode = textBox5.Text.Trim();
material.UniqueCode = "c" + _clientList.First(c => c.Oid == material.Client).UniqueCode + "m" + material.Code + "s" + material.SupplierCode;
#endregion
Task.Run(() => InsertOrUpdateMaterial(material));
return;
}
public Form_MaterialEditor(ExtractInventoryTool_Material material)
{
InitializeComponent();
_material = material;
}
/// <summary>
/// 读取Excel表，返回库存集合
/// </summary>
/// <param name="sheetName">sheet名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <param name="fileName">文件路径</param>
/// <returns></returns>
public List <ExtractInventoryTool_Inventory> ExcelToInventoryList(string sheetName, string fileName, ExtractInventoryTool_Client client, out string errorMessage)
{
IWorkbook workbook = null;
ISheet sheet = null;
List <ExtractInventoryTool_Inventory> result = new List <ExtractInventoryTool_Inventory>();
errorMessage = string.Empty;
List <string> uniqueCodeList = new List <string>();
try
{
using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fs);
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fs);
}
if (string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheetAt(0);
}
else
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
if (sheet == null)
{
return(result);
}
#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(),
Name = row["Name"].ToString(),
SupplierCode = row["SupplierCode"].ToString(),
Supplier = row["Supplier"].ToString(),
UniqueCode = row["UniqueCode"].ToString()
});
}
#endregion
#region 读取Sheet
int rowNum = sheet.LastRowNum;
for (int i = 5; i <= rowNum; i++)
{
//一行就是一条库存信息
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (row.GetCell(1) == null)
{
continue;
}
string materialCode = ConvertCellToString(row.GetCell(1));
string supplierCode = ConvertCellToString(row.GetCell(3));
//判断物料是否已备案
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);
}
string uniqueCode = rowMaterial.UniqueCode;
//判断是否存在重复行
if (uniqueCodeList.Contains(uniqueCode))//这里判断一下有没有同种物料重复，如果有，直接返回，输出重复物料
{
errorMessage = string.Format("第{0}行物料号{1}供应商代码{2}存在重复库存记录", i + 1, materialCode, supplierCode);
return(result);
}
uniqueCodeList.Add(uniqueCode);
ExtractInventoryTool_Inventory inventory = new ExtractInventoryTool_Inventory();
inventory.SysInventory = Convert.ToInt32(ConvertCellToString(row.GetCell(5), "0")); //系统库存
inventory.Min = Convert.ToInt32(ConvertCellToString(row.GetCell(6), "0")); //MIN
inventory.Max = Convert.ToInt32(ConvertCellToString(row.GetCell(7), "0")); //MAX
inventory.HUB = Convert.ToInt32(ConvertCellToString(row.GetCell(8), "0")); //HUB库存
inventory.InTransit = Convert.ToInt32(ConvertCellToString(row.GetCell(9), "0")); //在途库存
inventory.Total = Convert.ToInt32(ConvertCellToString(row.GetCell(10), "0")); //总库存
inventory.Material = rowMaterial.Oid;
result.Add(inventory);
}
#endregion
}
return(result);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 读取Excel表，返回物料备案集合
/// </summary>
/// <param name="sheetName">sheet名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <param name="fileName">文件路径</param>
/// <returns></returns>
public List <ExtractInventoryTool_Material> ExcelToMaterialList(string sheetName, bool isFirstRowColumn, string fileName, int clientOid, string clientUniqueCode, out string errorMessage)
{
errorMessage = string.Empty;
IWorkbook workbook = null;
ISheet sheet = null;
List <ExtractInventoryTool_Material> result = new List <ExtractInventoryTool_Material>();
List <string> uniqueCodeList = new List <string>();
try
{
using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fs);
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fs);
}
if (string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheetAt(0);
}
else
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
if (sheet == null)
{
return(result);
}
#region 读取Sheet
int rowNum = sheet.LastRowNum;
int firstRowNum = isFirstRowColumn ? sheet.FirstRowNum : sheet.FirstRowNum + 1;
for (int i = firstRowNum; i <= rowNum; i++)
{
//一行就是一条物料备案
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (row.GetCell(3) == null)
{
continue;
}
ExtractInventoryTool_Material material = new ExtractInventoryTool_Material();
material.SupplierCode = ConvertCellToString(row.GetCell(0)); //供应商代码
material.Supplier = ConvertCellToString(row.GetCell(1)); //供应商
material.Code = ConvertCellToString(row.GetCell(2)); //零件号
material.Name = ConvertCellToString(row.GetCell(3)); //零件名称
material.Client = clientOid;
material.UniqueCode = "c" + clientUniqueCode + "m" + material.Code + "s" + material.SupplierCode;
if (string.IsNullOrEmpty(material.Code))
{
continue;
}
if (uniqueCodeList.Contains(material.UniqueCode))
{
errorMessage = string.Format("零件号{0}存在重复值", material.Code);
return(result);
}
uniqueCodeList.Add(material.UniqueCode);
result.Add(material);
}
#endregion
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
/// <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);
}
/// <summary>
/// 导入物料备案
/// </summary>
/// <param name="materialList">物料信息</param>
/// <param name="isCover">是否覆盖已有数据</param>
/// <param name="errorMessage">报错信息</param>
/// <returns></returns>
/// <remarks>
/// Meritar_Jeffrey 2021/04/14 16:31:13
/// 功能 :
/// </remarks>
public bool ImportMaterial(List <ExtractInventoryTool_Material> materialList, bool isCover, out string errorMessage)
{
lock (lockObj)
{
try
{
errorMessage = string.Empty;
if (materialList == null || materialList.Count == 0)
{
errorMessage = "物料信息为空";
return(false);
}
//先查询出所有的物料唯一码
DataTable allMaterial = GetAllUniqueCode("Material");
List <ExtractInventoryTool_Material> allMaterialList = new List <ExtractInventoryTool_Material>();
foreach (DataRow row in allMaterial.Rows)
{
allMaterialList.Add(new ExtractInventoryTool_Material()
{
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 material in materialList)
{
ExtractInventoryTool_Material exitsMaterial = allMaterialList.FirstOrDefault(m => m.UniqueCode.Trim().Equals(material.UniqueCode.Trim()));
if (exitsMaterial != null && exitsMaterial.Oid != 0)//导入数据如果表里有就更新，没有就新建
{
if (!isCover)
{
errorMessage = "存在重复数据，是否覆盖？";
return(true);
}
material.Oid = exitsMaterial.Oid;
}
SQLiteParameter[] parameter =
{
SQLiteHelper.MakeSQLiteParameter("@Oid", DbType.Int32, material.Oid),
SQLiteHelper.MakeSQLiteParameter("@Client", DbType.Int32, material.Client),
SQLiteHelper.MakeSQLiteParameter("@Name", DbType.String, material.Name),
SQLiteHelper.MakeSQLiteParameter("@Code", DbType.String, material.Code),
SQLiteHelper.MakeSQLiteParameter("@Supplier", DbType.String, material.Supplier),
SQLiteHelper.MakeSQLiteParameter("@SupplierCode", DbType.String, material.SupplierCode),
SQLiteHelper.MakeSQLiteParameter("@UniqueCode", DbType.String, material.UniqueCode)
};
if (exitsMaterial != null && exitsMaterial.Oid != 0)
{
updateParamList.Add(parameter);
}
else
{
insertParamList.Add(parameter);
}
}
//添加新数据
insertStrbd.Append(@"Insert into Material (Client,Name,Code,Supplier,SupplierCode,UniqueCode) ")
.Append(@"values ( ")
.Append(@"@Client,@Name,@Code,@Supplier,@SupplierCode,@UniqueCode ")
.Append(@")");
new SQLiteHelper().ExecuteNonQueryBatch(insertStrbd.ToString(), insertParamList);
updateStrbd.Append(@"Update Material set Client=@Client,Name=@Name,Code=@Code,Supplier=@Supplier,SupplierCode=@SupplierCode,UniqueCode=@UniqueCode ")
.Append(@" WHERE Oid=@Oid");
new SQLiteHelper().ExecuteNonQueryBatch(updateStrbd.ToString(), updateParamList);
return(true);
}
catch (Exception ex)
{
//唯一码重复，须提示
//debug();
throw ex;
}
}
}
