/// <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>
/// 导入生产计划
/// </summary>
/// <param name="productionPlanList"></param>
/// <returns></returns>
public bool ImportProductionPlan(List <ExtractInventoryTool_ProductionPlan> productionPlanList, bool isCover, out string errorMessage)
{
lock (lockObj)
{
try
{
errorMessage = string.Empty;
if (productionPlanList == null || productionPlanList.Count == 0)
{
errorMessage = "生产计划为空";
return(false);
}
//先查询出所有的生产计划唯一码
DataTable allMaterial = GetAllUniqueCode("ProductionPlan");
List <ExtractInventoryTool_ProductionPlan> allProductionPlanList = new List <ExtractInventoryTool_ProductionPlan>();
foreach (DataRow row in allMaterial.Rows)
{
allProductionPlanList.Add(new ExtractInventoryTool_ProductionPlan()
{
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 productionPlan in productionPlanList)
{
ExtractInventoryTool_ProductionPlan exitsPP = allProductionPlanList.FirstOrDefault(m => m.UniqueCode.Trim().Equals(productionPlan.UniqueCode.Trim()));
if (exitsPP != null && exitsPP.Oid != 0)//导入数据如果表里有就更新，没有就新建
{
if (!isCover)
{
errorMessage = "存在重复数据，是否覆盖？";
return(true);
}
productionPlan.Oid = exitsPP.Oid;
}
SQLiteParameter[] parameter =
{
SQLiteHelper.MakeSQLiteParameter("@Oid", DbType.Int32, productionPlan.Oid),
SQLiteHelper.MakeSQLiteParameter("@VehicleModelCode", DbType.String, productionPlan.VehicleModelCode),
SQLiteHelper.MakeSQLiteParameter("@ProductionDate", DbType.Date, productionPlan.ProductionDate),
SQLiteHelper.MakeSQLiteParameter("@UnitNum", DbType.Int32, productionPlan.UnitNum),
SQLiteHelper.MakeSQLiteParameter("@UpdateTime", DbType.DateTime, productionPlan.UpdateTime),
SQLiteHelper.MakeSQLiteParameter("@Client", DbType.Int32, productionPlan.Client),
SQLiteHelper.MakeSQLiteParameter("@UniqueCode", DbType.String, productionPlan.UniqueCode)
};
if (exitsPP != null && exitsPP.Oid != 0)
{
updateParamList.Add(parameter);
}
else
{
insertParamList.Add(parameter);
}
}
//添加新数据
insertStrbd.Append(@"Insert into ProductionPlan (VehicleModelCode,ProductionDate,UnitNum,UpdateTime,Client,UniqueCode) ")
.Append(@"values ( ")
.Append(@"@VehicleModelCode,@ProductionDate,@UnitNum,@UpdateTime,@Client,@UniqueCode ")
.Append(@")");
new SQLiteHelper().ExecuteNonQueryBatch(insertStrbd.ToString(), insertParamList);
updateStrbd.Append(@"Update ProductionPlan set VehicleModelCode=@VehicleModelCode,ProductionDate=@ProductionDate,UnitNum=@UnitNum,UpdateTime=@UpdateTime,Client=@Client,UniqueCode=@UniqueCode ")
.Append(@" WHERE Oid=@Oid");
new SQLiteHelper().ExecuteNonQueryBatch(updateStrbd.ToString(), updateParamList);
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
}
