/// <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;
}
#endregion
#region 装载client
ExtractInventoryTool_Client client = new ExtractInventoryTool_Client();
int oid = 0;
client.Oid = int.TryParse(textBox1.Text.Trim(), out oid) ? oid : 0;
client.Name = textBox2.Text.Trim();
client.UniqueCode = textBox3.Text.Trim();
client.RegexRule = textBox4.Text.Trim();
client.Remark = richTextBox1.Text.Trim();
#endregion
Task.Run(() => InsertOrUpdateClient(client));
return;
}
/// <summary>
/// 上传BOM
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
#region 勾选客户
ExtractInventoryTool_Client selectedPrint = (ExtractInventoryTool_Client)comboBox1.SelectedItem;
if (selectedPrint == null)
{
MessageBox.Show("请选中一条客户进行导入BOM", "Warning");
return;
}
string regexRule = selectedPrint.RegexRule;
#endregion
#region 导入Excel
OpenFileDialog openFile = new OpenFileDialog();
openFile.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
openFile.Multiselect = false;
if (openFile.ShowDialog() == DialogResult.Cancel)
{
return;
}
textBox1.Text = openFile.FileName;
LoadingHelper.ShowLoadingScreen();
Task.Run(() => ImportBOMExcel(openFile.FileName, selectedPrint));
//ImportBOMExcel(openFile.FileName, selectedPrint);
#endregion
}
/// <summary>
/// 更新客户
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e)
{
ExtractInventoryTool_Client client = 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)
{
client = new ExtractInventoryTool_Client();
int oid = 0;
client.Oid = int.TryParse(row.Cells[0].Value.ToString().Trim(), out oid) ? oid : 0;
client.Name = row.Cells[1].Value.ToString().Trim();
client.UniqueCode = row.Cells[2].Value.ToString().Trim();
client.Remark = row.Cells[3].Value.ToString().Trim();
client.RegexRule = row.Cells[4].Value.ToString().Trim();
}
Form_ClientEditor editor = new Form_ClientEditor(client);
editor.ShowDialog(this);
if (editor.DialogResult == DialogResult.OK)
{
string limit = pagerControl1.PageSize.ToString();
string offset = (pagerControl1.PageIndex - 1).ToString();
Task.Run(() => QueryClient(limit, offset));
}
return;
}
public void ImportBOMExcel(string fileName, ExtractInventoryTool_Client client)
{
_excelData = null;
string errorMessage = string.Empty;
_excelData = new NPOIHelper().ExcelToBOMList(fileName, client, out errorMessage);
ImportBOMExcelCallbackDel del = ImportBOMExcelCallback;
this.BeginInvoke(del, _excelData, errorMessage);
}
/// <summary>
/// 添加/更新成品Ro#配置
/// </summary>
/// <param name="client"></param>
private void InsertOrUpdateClient(ExtractInventoryTool_Client client)
{
string errorMessage = string.Empty;
new ExtractInventoryTool_ClientBLL().InsertOrUpdateClient(client, out errorMessage);
InsertOrUpdateClientCallBackDel del = InsertOrUpdateClientCallBack;
this.BeginInvoke(del, errorMessage);
return;
}
public int InsertOrUpdateClient(ExtractInventoryTool_Client client, out string errorMessage)
{
lock (lockObj)
{
try
{
errorMessage = string.Empty;
if (client == null)
{
errorMessage = "客户信息为空";
return(0);
}
string oid = client.Oid == 0 ? string.Empty : client.Oid.ToString();
if (IsExistsUniqueCode(client.UniqueCode, "Client", oid))
{
errorMessage = "该客户已存在，无法添加相同客户";
return(0);
}
StringBuilder noQueryStrbd = new StringBuilder();
List <SQLiteParameter[]> paramList = new List <SQLiteParameter[]>();
SQLiteParameter[] parameter =
{
SQLiteHelper.MakeSQLiteParameter("@Oid", DbType.Int32, client.Oid),
SQLiteHelper.MakeSQLiteParameter("@Name", DbType.String, client.Name),
SQLiteHelper.MakeSQLiteParameter("@UniqueCode", DbType.String, client.UniqueCode),
SQLiteHelper.MakeSQLiteParameter("@Remark", DbType.String, client.Remark),
SQLiteHelper.MakeSQLiteParameter("@RegexRule", DbType.String, client.RegexRule)
};
paramList.Add(parameter);
if (client.Oid == 0)
{
//添加新数据
noQueryStrbd.Append(@"Insert into Client (Name,UniqueCode,Remark,RegexRule) ")
.Append(@"values ( ")
.Append(@"@Name,@UniqueCode,@Remark,@RegexRule ")
.Append(@")");
}
else
{
//更新数据
noQueryStrbd.Append(@"Update Client set Name=@Name,UniqueCode=@UniqueCode,Remark=@Remark,RegexRule=@RegexRule ")
.Append(@" WHERE Oid=@Oid");
}
new SQLiteHelper().ExecuteNonQueryBatch(noQueryStrbd.ToString(), paramList);
return(0);
}
catch (Exception ex)
{
throw ex;
}
}
}
private void QueryClientCallback(List <ExtractInventoryTool_Client> clientList)
{
comboBox1.DataSource = clientList;
comboBox1.DisplayMember = "Name";
comboBox1.ValueMember = "Oid";
comboBox1.SelectedItem = null;
if (_material != null)
{
ExtractInventoryTool_Client selectedPrint = clientList.FirstOrDefault(p => p.Oid == _material.Client);
if (selectedPrint != null)
{
comboBox1.SelectedIndex = comboBox1.Items.IndexOf(selectedPrint);
}
}
return;
}
public void InsertOrUpdateClient(ExtractInventoryTool_Client client)
{
lock (lockObj)
{
try
{
if (client == null)
{
MessageBox.Show("客户信息为空", "Error");
}
StringBuilder noQueryStrbd = new StringBuilder();
List <SQLiteParameter[]> paramList = new List <SQLiteParameter[]>();
SQLiteParameter[] parameter =
{
SQLiteHelper.MakeSQLiteParameter("@Oid", DbType.Int32, client.Oid),
SQLiteHelper.MakeSQLiteParameter("@Name", DbType.String, client.Name),
SQLiteHelper.MakeSQLiteParameter("@Code", DbType.String, client.UniqueCode),
SQLiteHelper.MakeSQLiteParameter("@Remark", DbType.String, client.Remark),
SQLiteHelper.MakeSQLiteParameter("@RegexRule", DbType.String, client.RegexRule)
};
paramList.Add(parameter);
if (client.Oid == 0)
{
//添加新数据
noQueryStrbd.Append(@"Insert into Client (Name,Code,Remark,RegexRule) ")
.Append(@"values ( ")
.Append(@"@Name,@Code,@Remark,@RegexRule ")
.Append(@")");
}
else
{
//更新数据
noQueryStrbd.Append(@"Update Client set Name=@Name,Code=@Code,Remark=@Remark,RegexRule=@RegexRule ")
.Append(@" WHERE Oid=@Oid");
}
new SQLiteHelper().ExecuteNonQueryBatch(noQueryStrbd.ToString(), paramList);
return;
}
catch (Exception ex)
{
throw ex;
}
}
}
private void QueryClient()
{
try
{
DataTable dt = new ExtractInventoryTool_ClientBLL().QueryClient();
_clientList = new List <ExtractInventoryTool_Client>();
foreach (DataRow row in dt.Rows)
{
ExtractInventoryTool_Client client = new ExtractInventoryTool_Client();
client.Oid = row["Oid"] == DBNull.Value ? 0 : Convert.ToInt32(row["Oid"]);
client.Name = row["Name"] == DBNull.Value ? "" : (string)row["Name"];
client.UniqueCode = row["UniqueCode"] == DBNull.Value ? "" : (string)row["UniqueCode"];
_clientList.Add(client);
}
QueryClientCallbackDel del = QueryClientCallback;
comboBox1.BeginInvoke(del, _clientList);
return;
}
catch (Exception ex)
{
LogHelper.WriteLog("QueryClient", ex);
}
}
public Form_ClientEditor(ExtractInventoryTool_Client client)
{
InitializeComponent();
_client = client;
}
/// <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表，返回ProductionPlan集合
/// </summary>
/// <param name="fileName">文件路径</param>
/// <param name="client">客户信息</param>
/// <returns></returns>
public List <ExtractInventoryTool_ProductionPlan> ExcelToProductionPlanList(string fileName, ExtractInventoryTool_Client client, out string errorMessage)
{
List <ExtractInventoryTool_ProductionPlan> result = new List <ExtractInventoryTool_ProductionPlan>();
errorMessage = string.Empty;
#region 读取客户配置
string clientRuleConfig = ConfigurationManager.AppSettings[client.UniqueCode + "PP"].ToString();
ExtractInventoryTool_ProductionPlanImportRule clientRule = JsonConvert.DeserializeObject <ExtractInventoryTool_ProductionPlanImportRule>(clientRuleConfig);
// Meritar_Jeffrey 2021/04/02 11:24:11
#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 dateRow = sheet.GetRow(clientRule.DR);
#region 读取生产计划的思路
#endregion
#region 获取所有BOM的车型代码
DataTable dt = new ExtractInventoryTool_BOMBLL().QueryVehicleModelCode();
List <string> vehicleCodeList = new List <string>();
foreach (DataRow row in dt.Rows)
{
vehicleCodeList.Add(row["VehicleModelCode"].ToString());
}
#endregion
for (int i = clientRule.VR; i <= sheet.LastRowNum; i++)//外循环--行
{
IRow row = sheet.GetRow(i);
if (row == null || row.GetCell(clientRule.VC) == null)
{
continue;
}
string vehicleModelCode = ConvertCellToString(row.GetCell(clientRule.VC));
if (string.IsNullOrEmpty(vehicleModelCode))
{
continue;
}
List <string> dateList = new List <string>();
//判断车型代码是否已备案
if (!vehicleCodeList.Contains(vehicleModelCode))
{
errorMessage = string.Format("第{0}行车型代码{1}没有在系统备案", i + 1, vehicleModelCode);
return(result);
}
//判断是否存在重复行
if (uniqueCodeList.Contains(vehicleModelCode))//这里判断一下有没有同种车型代码重复，如果有，直接返回，输出重复物料
{
errorMessage = string.Format("第{0}行车型代码{1}存在重复记录", i + 1, vehicleModelCode);
return(result);
}
uniqueCodeList.Add(vehicleModelCode);
for (int j = clientRule.UC; j <= row.LastCellNum - 2; j++)//内循环--列
{
if (dateRow.GetCell(j).CellType != CellType.Numeric)
{
break;
}
ICell cell = row.GetCell(j);
if (cell == null)
{
continue;
}
int unitNum = 0;
string unitNumStr = cell.ToString();
if (string.IsNullOrEmpty(unitNumStr))
{
continue;
}
unitNum = Convert.ToInt32(unitNumStr);
if (unitNum == 0)
{
continue;
}
string dateCell = dateRow.GetCell(j).DateCellValue.Date.ToString("yyyy-MM-dd");
if (dateList.Contains(dateCell))
{
errorMessage = string.Format("第{0}行第{1}和{2}列日期{3}存在重复记录", i + 1, j, j + 1, dateCell);
return(result);
}
else
{
dateList.Add(dateCell);
}
ExtractInventoryTool_ProductionPlan ppCell = new ExtractInventoryTool_ProductionPlan();
ppCell.VehicleModelCode = vehicleModelCode;
//bomCell.ProductionDate = Regex.Match(dateRow.GetCell(j).ToString(), client.RegexRule).Value;
ppCell.ProductionDate = dateRow.GetCell(j).DateCellValue;
ppCell.UnitNum = unitNum;
ppCell.UpdateTime = DateTime.Now;
ppCell.Client = client.Oid;
ppCell.UniqueCode = "c" + client.UniqueCode + "v" + ppCell.VehicleModelCode + "d" + dateCell;
result.Add(ppCell);
}
}
#endregion
}
return(result);
}
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);
}
