/// <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;
}
}
}
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;
}
}
}
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;
}
}
}
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;
}
}
}
public DataTable GetRoSetByFPNum(string finishedProductNum)
{
DataTable dt = null;
if (string.IsNullOrWhiteSpace(finishedProductNum))
{
return(null);
}
StringBuilder queryStrbd = new StringBuilder();
queryStrbd.Append("select n0.* from RoSet n0 left join PrintSet n1 on n0.FinishedProductNum=n1.Oid ")
.Append("where n1.FinishedProductNum=@FinishedProductNum");
List <SQLiteParameter[]> paramList = new List <SQLiteParameter[]>();
SQLiteParameter[] parameter =
{
SQLiteHelper.MakeSQLiteParameter("@FinishedProductNum", DbType.String, finishedProductNum),
};
paramList.Add(parameter);
dt = new SQLiteHelper().ExecuteQuery(queryStrbd.ToString(), parameter);
dt.TableName = "wcftable";
return(dt);
}
/// <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;
}
}
}
public string UploadItemCodeAndSNList(ItemCodeSN_UploadDTO value)
{
if (value == null || value.SNList == null || value.SNList.Count <= 0)
{
return("SN List is null !");
}
if (string.IsNullOrEmpty(value.RoNumber))
{
return("RoNumber is null !");
}
if (string.IsNullOrEmpty(value.ItemCode))
{
return("Raw Material Label isn't scanned !");
}
try
{
string roNumber = value.RoNumber;
string itemCode = value.ItemCode;
string itemName = string.Empty;
string finishedProductNum = value.FinishedProductNum;
List <ItemCodeSNDTO> snList = value.SNList;
#region 查询原材料备案
StringBuilder rawMaterialReaderStrbd = new StringBuilder();
rawMaterialReaderStrbd.Append(@"select m.Code from Material m where m.Name = @Name ");
SQLiteParameter[] readParameter = { SQLiteHelper.MakeSQLiteParameter("@Name", DbType.String, itemCode) };
object rawMaterialObject = new SQLiteHelper().ExecuteScalar(rawMaterialReaderStrbd.ToString(), readParameter);
if (rawMaterialObject == null)
{
return("Raw Material Name is empty !");
}
itemName = rawMaterialObject.ToString();
#endregion
#region 插入原材料SN列表
StringBuilder noQueryStrbd = new StringBuilder();
noQueryStrbd.Append(@"insert into ItemCodeSN (ItemCode,ItemName,FinishedProductNum,SerivalNum,RoNumber) ")
.Append(@"values ( ")
.Append(@"@ItemCode,@ItemName,@FinishedProductNum,@SerivalNum,@RoNumber ")
.Append(@") ");
List <SQLiteParameter[]> paramList = new List <SQLiteParameter[]>();
foreach (var dto in snList)
{
SQLiteParameter[] parameter =
{
SQLiteHelper.MakeSQLiteParameter("@ItemCode", DbType.String, dto.ItemCode),
SQLiteHelper.MakeSQLiteParameter("@ItemName", DbType.String, itemName),
SQLiteHelper.MakeSQLiteParameter("@FinishedProductNum", DbType.String, finishedProductNum),
SQLiteHelper.MakeSQLiteParameter("@SerivalNum", DbType.String, dto.SerivalNum),
SQLiteHelper.MakeSQLiteParameter("@RoNumber", DbType.String, roNumber)
};
paramList.Add(parameter);
}
new SQLiteHelper().ExecuteNonQueryBatch(noQueryStrbd.ToString(), paramList);
#endregion
return(string.Empty);
}
catch (Exception ex)
{
LogHelper.WriteLog("UploadItemCodeAndSNList", ex);
return(ex.Message);
}
}
public void InsertLabelRecord(List <FinishedProductLabelDTO> dtoList)
{
try
{
if (dtoList == null || dtoList.Count == 0)
{
return;
}
StringBuilder noQueryStrbd = new StringBuilder();
List <SQLiteParameter[]> paramList = new List <SQLiteParameter[]>();
foreach (var dto in dtoList)
{
SQLiteParameter[] parameter =
{
SQLiteHelper.MakeSQLiteParameter("@Oid", DbType.Int32, dto.Oid),
SQLiteHelper.MakeSQLiteParameter("@ID", DbType.String, dto.ID),
SQLiteHelper.MakeSQLiteParameter("@A", DbType.Boolean, dto.A),
SQLiteHelper.MakeSQLiteParameter("@B", DbType.Boolean, dto.B),
SQLiteHelper.MakeSQLiteParameter("@C", DbType.Boolean, dto.C),
SQLiteHelper.MakeSQLiteParameter("@D", DbType.Boolean, dto.D),
SQLiteHelper.MakeSQLiteParameter("@E", DbType.Boolean, dto.E),
SQLiteHelper.MakeSQLiteParameter("@F", DbType.Boolean, dto.F),
SQLiteHelper.MakeSQLiteParameter("@G", DbType.Boolean, dto.G),
SQLiteHelper.MakeSQLiteParameter("@H", DbType.Boolean, dto.H),
SQLiteHelper.MakeSQLiteParameter("@I", DbType.Boolean, dto.I),
SQLiteHelper.MakeSQLiteParameter("@J", DbType.Boolean, dto.J),
SQLiteHelper.MakeSQLiteParameter("@K", DbType.Boolean, dto.K),
SQLiteHelper.MakeSQLiteParameter("@L", DbType.Boolean, dto.L),
SQLiteHelper.MakeSQLiteParameter("@M", DbType.Boolean, dto.M),
SQLiteHelper.MakeSQLiteParameter("@HOME", DbType.Boolean, dto.HOME),
SQLiteHelper.MakeSQLiteParameter("@SME", DbType.Boolean, dto.SME),
SQLiteHelper.MakeSQLiteParameter("@MSI", DbType.Boolean, dto.MSI),
SQLiteHelper.MakeSQLiteParameter("@FTTH", DbType.Boolean, dto.FTTH),
SQLiteHelper.MakeSQLiteParameter("@MSIVOICEONLY", DbType.Boolean, dto.MSIVOICEONLY),
SQLiteHelper.MakeSQLiteParameter("@COPPERDATAONLY", DbType.Boolean, dto.COPPERDATAONLY),
SQLiteHelper.MakeSQLiteParameter("@FTTHDATAONLY", DbType.Boolean, dto.FTTHDATAONLY),
SQLiteHelper.MakeSQLiteParameter("@FTTHNONWIFI", DbType.Boolean, dto.FTTHNONWIFI),
SQLiteHelper.MakeSQLiteParameter("@FTTHNONWIFIDATAONLY", DbType.Boolean, dto.FTTHNONWIFIDATAONLY),
SQLiteHelper.MakeSQLiteParameter("@ONU", DbType.String, dto.ONU),
SQLiteHelper.MakeSQLiteParameter("@VDSL", DbType.String, dto.VVDSL),
SQLiteHelper.MakeSQLiteParameter("@TELSET", DbType.String, dto.TELSET),
SQLiteHelper.MakeSQLiteParameter("@BIZBOX", DbType.String, dto.BIZBOX),
SQLiteHelper.MakeSQLiteParameter("@Barcode", DbType.String, dto.Barcode),
SQLiteHelper.MakeSQLiteParameter("@GoodList", DbType.String, JsonConvert.SerializeObject(dto.GoodList)),
SQLiteHelper.MakeSQLiteParameter("@WorkStation", DbType.String, dto.WorkStation),
SQLiteHelper.MakeSQLiteParameter("@CreateTime", DbType.DateTime, DateTime.Now),
SQLiteHelper.MakeSQLiteParameter("@FinishedProductNum", DbType.String, dto.FinishedProductNum),
SQLiteHelper.MakeSQLiteParameter("@RoNumber", DbType.String, dto.RoNumber)
};
paramList.Add(parameter);
//添加新数据
}
noQueryStrbd.Append(@"Insert into LabelRecord (ID,A,B,C,D,E,F,G,H,I,J,K,L,M,HOME,SME,MSI,FTTH,MSIVOICEONLY,COPPERDATAONLY,FTTHDATAONLY,FTTHNONWIFI,FTTHNONWIFIDATAONLY,ONU,VDSL,TELSET,BIZBOX,Barcode,GoodList,CreateTime,WorkStation,FinishedProductNum,RoNumber) ")
.Append(@"values ( ")
.Append(@"@ID,@A,@B,@C,@D,@E,@F,@G,@H,@I,@J,@K,@L,@M,@HOME,@SME,@MSI,@FTTH,@MSIVOICEONLY,@COPPERDATAONLY,@FTTHDATAONLY,@FTTHNONWIFI,@FTTHNONWIFIDATAONLY,@ONU,@VDSL,@TELSET,@BIZBOX,@Barcode,@GoodList,@CreateTime,@WorkStation,@FinishedProductNum,@RoNumber ")
.Append(@")");
new SQLiteHelper().ExecuteNonQueryBatch(noQueryStrbd.ToString(), paramList);
return;
}
catch (Exception ex)
{
LogHelper.WriteLog("InsertLabelRecord", ex);
}
}
public bool ImportInventory(List <ExtractInventoryTool_Inventory> inventoryList, bool isCover, out string errorMessage)
{
lock (lockObj)
{
try
{
errorMessage = string.Empty;
if (inventoryList == null || inventoryList.Count == 0)
{
errorMessage = "库存信息为空";
return(false);
}
//先查询出所有的BOM唯一码
DataTable allInventory = GetAllUniqueCode("Inventory", "Material");
List <ExtractInventoryTool_Inventory> allBOMList = new List <ExtractInventoryTool_Inventory>();
foreach (DataRow row in allInventory.Rows)
{
allBOMList.Add(new ExtractInventoryTool_Inventory()
{
Oid = Int32.Parse(row["Oid"].ToString()),
Material = Int32.Parse(row["Material"].ToString())
});
}
StringBuilder insertStrbd = new StringBuilder();
StringBuilder updateStrbd = new StringBuilder();
List <SQLiteParameter[]> insertParamList = new List <SQLiteParameter[]>();
List <SQLiteParameter[]> updateParamList = new List <SQLiteParameter[]>();
foreach (var inventory in inventoryList)
{
ExtractInventoryTool_Inventory exitsInventory = allBOMList.FirstOrDefault(m => m.Material == inventory.Material);
if (exitsInventory != null && exitsInventory.Oid != 0)//导入数据如果表里有就更新，没有就新建
{
if (!isCover)
{
errorMessage = "存在重复数据，是否覆盖？";
return(true);
}
inventory.Oid = exitsInventory.Oid;
}
SQLiteParameter[] parameter =
{
SQLiteHelper.MakeSQLiteParameter("@Oid", DbType.Int32, inventory.Oid),
SQLiteHelper.MakeSQLiteParameter("@Material", DbType.Int32, inventory.Material),
SQLiteHelper.MakeSQLiteParameter("@SysInventory", DbType.Int32, inventory.SysInventory),
SQLiteHelper.MakeSQLiteParameter("@Min", DbType.Int32, inventory.Min),
SQLiteHelper.MakeSQLiteParameter("@Max", DbType.Int32, inventory.Max),
SQLiteHelper.MakeSQLiteParameter("@HUB", DbType.Int32, inventory.HUB),
SQLiteHelper.MakeSQLiteParameter("@InTransit", DbType.Int32, inventory.InTransit),
SQLiteHelper.MakeSQLiteParameter("@Total", DbType.Int32, inventory.Total)
};
if (exitsInventory != null && exitsInventory.Oid != 0)
{
updateParamList.Add(parameter);
}
else
{
insertParamList.Add(parameter);
}
}
//添加新数据
insertStrbd.Append(@"Insert into Inventory (Material,SysInventory,Min,Max,HUB,InTransit,Total) ")
.Append(@"values ( ")
.Append(@"@Material,@SysInventory,@Min,@Max,@HUB,@InTransit,@Total ")
.Append(@")");
new SQLiteHelper().ExecuteNonQueryBatch(insertStrbd.ToString(), insertParamList);
updateStrbd.Append(@"Update Inventory set Material=@Material,SysInventory=@SysInventory,Min=@Min,Max=@Max,HUB=@HUB,InTransit=@InTransit,Total=@Total ")
.Append(@" WHERE Oid=@Oid");
new SQLiteHelper().ExecuteNonQueryBatch(updateStrbd.ToString(), updateParamList);
return(true);
}
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;
}
}
}
/// <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;
}
}
}
