/// <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; } } }