public override void SetLastUpdateTime()
{
RelatedConn.Open();
var tx = RelatedConn.BeginTransaction();
try
{
var cmd = new OracleCommand
{
Connection = RelatedConn,
CommandText = $@"update LastUpdateTime set t3=sysdate where id={Source.Name}",
Transaction = tx
};
cmd.ExecuteNonQuery();
tx.Commit();
}
catch
{
tx.Rollback();
throw;
}
finally
{
RelatedConn.Close();
}
}
public override int BackUpdate(DateTime startTime, DateTime endTime)
{
var result = 0;
try
{
SourceConn.Open();
RelatedConn.Open();
var readCmd = new OracleCommand()
{
Connection = RelatedConn,
CommandText = $"select PlanCode from ProductionPlan where SourceDb='{ Source.Name}'"
};
var updateCmd = new OracleCommand
{
Connection = RelatedConn,
CommandText = $@"update ProductionPlan set {Source.Name}del=1 where PlanCode=:PlanCode"
};
updateCmd.Parameters.Add(new OracleParameter("PlanCode", OracleDbType.Char));
updateCmd.Prepare();
var reader = readCmd.ExecuteReader();
var sourceCmd = new SqlCommand
{
Connection = SourceConn,
CommandText = $@"SELECT FBillNo,(select FNumber from t_icitem where t_icitem.FItemID=ICmo.FItemID) as FShortNumber,
(select FName from t_User where t_User.FUserID=ICmo.FBillerID) FBillerID,FCheckDate,(select FBOMNumber from icbom where icbom.FInterID= ICmo.FBomInterID) as FBOMNumber,(select FVersion from icbom where icbom.FInterID= ICmo.FBomInterID) as FVersion,FStatus,FAuxQty,(SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=ICmo.FUnitID) FUnitID,FType,
FPlanCommitDate,FPlanFinishDate,(select FName from t_Department where t_Department.FItemID=ICmo.FWorkShop) FWorkShop,FWorkTypeID,FConfirmDate,FGMPBatchNo FROM ICmo
where FStatus=1 and FBillNo=@FBillNo",
CommandTimeout = 10
};
sourceCmd.Parameters.Add(new SqlParameter("FBillNo", System.Data.SqlDbType.Char, 8000));
sourceCmd.Prepare();
while (reader.Read())
{
sourceCmd.Parameters[0].Value = reader[0];
if (sourceCmd.ExecuteScalar() == null)//如果找不到了，则说明源对应的行被删除，需要标记中间表数据为删除状态
{
updateCmd.Parameters[0].Value = reader[0];
updateCmd.ExecuteNonQuery();
result++;
}
}
}
catch (Exception ex)
{
log4net.LogManager.GetLogger("Logger").Error(ex.ToString());
throw;
}
finally
{
SourceConn.Close();
RelatedConn.Close();
}
return(result);
}
public override int BackUpdate(DateTime startTime, DateTime endTime)
{
var result = 0;
try
{
SourceConn.Open();
RelatedConn.Open();
var readCmd = new OracleCommand()
{
Connection = RelatedConn,
CommandText = $"select Code from materiel where SourceDb='{Source.Name}'"
};
var updateCmd = new OracleCommand
{
Connection = RelatedConn,
CommandText = $"update materiel set {Source.Name}del = 1 where Code =:Code"
};
updateCmd.Parameters.Add(new OracleParameter("Code", OracleDbType.Char));
updateCmd.Prepare();
var reader = readCmd.ExecuteReader();
var sourceCmd = new SqlCommand
{
Connection = SourceConn,
CommandText = $@"select FNumber,FName,(select FName from t_SubMessage where t_SubMessage.FInterID=t_icitem.FErpClsID) FTypeName,(SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=t_icitem.FUnitID) unit,
FModel, FLastCheckDate, FItemID from t_icitem where FNumber like '30%' and FNumber=@FNumber",
CommandTimeout = 0
};
sourceCmd.Parameters.Add(new SqlParameter("FNumber", System.Data.SqlDbType.Char, 8000));
sourceCmd.Prepare();
while (reader.Read())
{
sourceCmd.Parameters[0].Value = reader[0];
var obj = sourceCmd.ExecuteScalar();
if (obj == null)//如果找不到了，则说明源对应的行被删除，需要标记中间表数据为删除状态
{
updateCmd.Parameters[0].Value = reader[0];
updateCmd.ExecuteNonQuery();
result++;
}
}
}
catch (Exception ex)
{
log4net.LogManager.GetLogger("Logger").Error(ex.ToString());
throw;
}
finally
{
SourceConn.Close();
RelatedConn.Close();
}
return(result);
}
public override DateTime GetLastUpdateTime()
{
RelatedConn.Open();
try
{
var cmd = new OracleCommand
{
Connection = RelatedConn,
CommandText = $@"select t3 from LastUpdateTime where id={Source.Name}"
};
return((DateTime)(cmd.ExecuteScalar()));
}
finally
{
RelatedConn.Close();
}
}
public override int InitImport(DateTime startTime, DateTime endTime)
{
var result = 0;
//定义批处理的
var models = new BaseModel[BatchNum];
SourceConn.Open();
RelatedConn.Open();
var sourceCmd = new SqlCommand()
{
Connection = SourceConn,
CommandText =
$@"SELECT FBillNo,(select FNumber from t_icitem where t_icitem.FItemID=ICmo.FItemID) as FShortNumber,
(select FName from t_User where t_User.FUserID=ICmo.FBillerID) FBillerID,FCheckDate,(select FBOMNumber from icbom where icbom.FInterID= ICmo.FBomInterID) as FBOMNumber,(select FVersion from icbom where icbom.FInterID= ICmo.FBomInterID) as FVersion,FStatus,FAuxQty,(SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=ICmo.FUnitID) FUnitID,FType,
FPlanCommitDate,FPlanFinishDate,(select FName from t_Department where t_Department.FItemID=ICmo.FWorkShop) FWorkShop,FWorkTypeID,FConfirmDate,FGMPBatchNo FROM ICmo
where FStatus=1 and FCheckDate between CONVERT(datetime, '{startTime}') and CONVERT(datetime, '{endTime}')"
};
var reader = sourceCmd.ExecuteReader();
var relatedCmd = new OracleCommand
{
Connection = RelatedConn,
CommandText = GetInsertCmdText()
};
var i = 0;
try
{
while (reader.Read())
{
var plan = new ProductionPlan();
if (reader["FBillNo"] == DBNull.Value)
{
continue;
}
//华为只要"JP"开头单据，烽火套账只需要“BB”开头单据
if (!reader["FBillNo"].ToString().ToUpper().StartsWith(Source.PlanCodePrefix))
{
continue;
}
plan.PlanCode = reader["FBillNo"].ToString();
plan.WorkOrder = reader["FGMPBatchNo"].ToString();
plan.MaterielCode = reader["fshortnumber"].ToString();
plan.Planner = reader["FBillerID"].ToString();
plan.BillDate = DateTime.Parse(reader["FCheckDate"].ToString());
plan.BOMCode = reader["FBOMNumber"].ToString();
plan.BOMVersion = reader["FVersion"].ToString();
plan.OrderState = reader["FStatus"].ToString();
plan.PlanQuantity = reader["FAuxQty"] == DBNull.Value ? 0 : double.Parse(reader["FAuxQty"].ToString());
plan.BaseUnit = reader["FUnitID"].ToString();
plan.ProductionType = reader["FType"].ToString();
if (reader["FPlanCommitDate"] != DBNull.Value)
{
plan.PlanStartTime = DateTime.Parse(reader["FPlanCommitDate"].ToString());
}
if (reader["FPlanFinishDate"] != DBNull.Value)
{
plan.PlanEndTime = DateTime.Parse(reader["FPlanFinishDate"].ToString());
}
plan.WorkShop = reader["FWorkShop"].ToString();
plan.Flag = 'C';
plan.K3TimeStamp = DateTime.Parse(reader["FCheckDate"].ToString());
plan.SourceDb = "XG";
plan.Line = "FH";
plan.ID = Guid.NewGuid().ToString();
models[i] = plan;
i++;
if (i == BatchNum)
{
CommitBatch(relatedCmd, models);
result += i;
i = 0;
models = new BaseModel[BatchNum];//重置批
}
}
if (i > 0)
{
var oddModels = new BaseModel[i];
for (int j = 0; j < i; j++)
{
oddModels[j] = models[j];
}
CommitBatch(relatedCmd, oddModels);
result += i;
}
reader.Close();
}
catch (Exception e)
{
log4net.LogManager.GetLogger("Logger").Error(e.ToString() + "\r\n" + sourceCmd.CommandText + "\r\n" + relatedCmd.CommandText);
throw;
}
finally
{
SourceConn.Close();
RelatedConn.Close();
}
return(result);
}
public override int Update(DateTime startTime, DateTime endTime)
{
var result = 0;
SourceConn.Open();
RelatedConn.Open();
//定义批处理的
var insertModels = new BaseModel[BatchNum];
var updateModels = new BaseModel[BatchNum];
var sourceCmd = new SqlCommand
{
Connection = SourceConn,
CommandText =
$@"SELECT FBillNo,(select FNumber from t_icitem where t_icitem.FItemID=ICmo.FItemID) as FShortNumber,
(select FName from t_User where t_User.FUserID=ICmo.FBillerID) FBillerID,FCheckDate,(select FBOMNumber from icbom where icbom.FInterID= ICmo.FBomInterID) as FBOMNumber,(select FVersion from icbom where icbom.FInterID= ICmo.FBomInterID) as FVersion,FStatus,FAuxQty,(SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=ICmo.FUnitID) FUnitID,FType,
FPlanCommitDate,FPlanFinishDate,(select FName from t_Department where t_Department.FItemID=ICmo.FWorkShop) FWorkShop,FWorkTypeID,FConfirmDate,FGMPBatchNo FROM ICmo
where FStatus=1 ",
CommandTimeout = 0
//and FConfirmDate between CONVERT(datetime, '{startTime}') and CONVERT(datetime, '{endTime}')
};
var relatedCmd = new OracleCommand
{
Connection = RelatedConn,
CommandText = "select ID||','||HashCode||','||SourceDb from ProductionPlan where PlanCode=:PlanCode"
};
relatedCmd.Parameters.Add(new OracleParameter("PlanCode", OracleDbType.Char));
relatedCmd.Prepare();
var reader = sourceCmd.ExecuteReader();
var insertCmd = new OracleCommand()
{
Connection = RelatedConn,
CommandText = GetInsertCmdText()
};
var updateCmd = new OracleCommand()
{
Connection = RelatedConn,
CommandText = GetUpdateCmdText() + $@" where PlanCode=:PlanCode"
};
try
{
var i = 0;
var j = 0;
while (reader.Read())
{
var plan = new ProductionPlan();
if (reader["FBillNo"] == DBNull.Value || reader["FGMPBatchNo"] == DBNull.Value || reader["fshortnumber"] == DBNull.Value || reader["FBillerID"] == DBNull.Value ||
reader["FCheckDate"] == DBNull.Value || reader["FBOMNumber"] == DBNull.Value || reader["FVersion"] == DBNull.Value || reader["FStatus"] == DBNull.Value)
{
continue;
}
//武汉华为只要"JP"开头单据，孝感烽火只需要“BB”开头单据
if (!reader["FBillNo"].ToString().ToUpper().StartsWith(Source.PlanCodePrefix))
{
continue;
}
plan.PlanCode = reader["FBillNo"].ToString();
plan.WorkOrder = reader["FGMPBatchNo"].ToString();
plan.MaterielCode = reader["fshortnumber"].ToString();
plan.Planner = reader["FBillerID"].ToString();
plan.BillDate = DateTime.Parse(reader["FCheckDate"].ToString());
plan.BOMCode = reader["FBOMNumber"].ToString();
plan.BOMVersion = reader["FVersion"].ToString();
plan.OrderState = reader["FStatus"].ToString();
plan.PlanQuantity = reader["FAuxQty"] == DBNull.Value ? 0 : double.Parse(reader["FAuxQty"].ToString());
plan.BaseUnit = reader["FUnitID"].ToString();
plan.ProductionType = reader["FType"].ToString();
if (reader["FPlanCommitDate"] != DBNull.Value)
{
plan.PlanStartTime = DateTime.Parse(reader["FPlanCommitDate"].ToString());
}
if (reader["FPlanFinishDate"] != DBNull.Value)
{
plan.PlanEndTime = DateTime.Parse(reader["FPlanFinishDate"].ToString());
}
plan.WorkShop = reader["FWorkShop"].ToString();
plan.Flag = 'C';
plan.K3TimeStamp = DateTime.Parse(reader["FCheckDate"].ToString());
plan.K3WriteTime = DateTime.Now;
plan.SourceDb = Source.Name;
plan.Line = Source.Line;
plan.CalculateHashCode();
if (CheckHasNull(plan.PlanCode, plan.WorkOrder, plan.MaterielCode, plan.Planner, plan.BOMCode, plan.BOMVersion, plan.OrderState))
{
continue;
}
relatedCmd.Parameters[0].Value = plan.PlanCode;
var obj = relatedCmd.ExecuteScalar();
if (obj == null)
{
plan.ID = Guid.NewGuid().ToString();
insertModels[i] = plan;
i++;
if (i == BatchNum)
{
CommitBatch(insertCmd, insertModels);
result += i;
i = 0;
insertModels = new BaseModel[BatchNum];//重置批
}
}
else
{
var arr = obj.ToString().Split(',');
if (plan.HashCode != arr[1])
{
if (arr[2] != Source.Name && Source.Name != Configuration.Current.MainData)
{
continue;
}
log4net.LogManager.GetLogger("Logger").Info($"检测到生产计划更新【{plan.PlanCode}】");
plan.Flag = 'U';
plan.ID = obj.ToString().Split(',')[0].ToString();
updateModels[j] = plan;
j++;
if (j == BatchNum)
{
CommitBatch(updateCmd, updateModels);
result += j;
j = 0;
updateModels = new BaseModel[BatchNum];//重置批
}
}
}
}
if (i > 0)
{
var oddModels = new BaseModel[i];
for (int k = 0; k < i; k++)
{
oddModels[k] = insertModels[k];
}
CommitBatch(insertCmd, oddModels);
result += i;
}
if (j > 0)
{
var oddModels = new BaseModel[j];
for (int k = 0; k < j; k++)
{
oddModels[k] = updateModels[k];
}
CommitBatch(updateCmd, oddModels);
result += j;
}
reader.Close();
}
catch (Exception e)
{
log4net.LogManager.GetLogger("Logger").Error(e.ToString() + "\r\n" + sourceCmd.CommandText + "\r\n" + insertCmd.CommandText);
throw;
}
finally
{
SourceConn.Close();
RelatedConn.Close();
}
return(result);
}
public override int InitImport(DateTime startTime, DateTime endTime)
{
var result = 0;
//定义批处理的
var models = new BaseModel[BatchNum];
SourceConn.Open();
RelatedConn.Open();
var sourceCmd = new SqlCommand
{
Connection = SourceConn,
CommandText =
$@"select FNumber,FName,(select FName from t_SubMessage where t_SubMessage.FInterID=t_icitem.FErpClsID) FTypeName,(SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=t_icitem.FUnitID) unit,
FModel,FLastCheckDate,FItemID from t_icitem
where FNumber like '30%' and FLastCheckDate between CONVERT(datetime, '{startTime}') and CONVERT(datetime, '{endTime}')"
};
var reader = sourceCmd.ExecuteReader();
var relatedCmd = new OracleCommand()
{
Connection = RelatedConn,
CommandText = GetInsertCmdText()
};
try
{
var i = 0;
while (reader.Read())
{
var materiel = new Materiel();
if (reader["FNumber"] == DBNull.Value)
{
continue;
}
materiel.Code = reader["FNumber"].ToString();
materiel.Name = reader["FName"].ToString();
materiel.Type = reader["FTypeName"].ToString();
materiel.BaseUnit = reader["unit"].ToString();
materiel.Specification = reader["FModel"].ToString();
materiel.Flag = 'C';
materiel.K3TimeStamp = DateTime.Now;
materiel.SourceDb = "XG";
materiel.ID = Guid.NewGuid().ToString();
models[i] = materiel;
i++;
if (i == BatchNum)
{
CommitBatch(relatedCmd, models);
result += i;
i = 0;
models = new BaseModel[BatchNum];//重置批
}
}
if (i > 0)
{
var oddModels = new BaseModel[i];
for (int j = 0; j < i; j++)
{
oddModels[j] = models[j];
}
CommitBatch(relatedCmd, oddModels);
result += i;
}
reader.Close();
}
catch (Exception e)
{
log4net.LogManager.GetLogger("Logger").Error(e.Message + "\r\n" + sourceCmd.CommandText + "\r\n" + relatedCmd.CommandText);
throw;
}
finally
{
SourceConn.Close();
RelatedConn.Close();
}
return(result);
}
public override int Update(DateTime startTime, DateTime endTime)
{
var result = 0;
SourceConn.Open();
RelatedConn.Open();
//定义批处理的
var insertModels = new BaseModel[BatchNum];
var updateModels = new BaseModel[BatchNum];
var sourceCmd = new SqlCommand
{
Connection = SourceConn,
CommandText =
$@"select FNumber,FName,(select FName from t_SubMessage where t_SubMessage.FInterID=t_icitem.FErpClsID) FTypeName,(SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=t_icitem.FUnitID) unit,
FModel,FLastCheckDate,FItemID from t_icitem
where FNumber like '30%' ",
// and FLastCheckDate between CONVERT(datetime, '{startTime}') and CONVERT(datetime, '{endTime}'),
CommandTimeout = 0
};
var relatedCmd = new OracleCommand
{
Connection = RelatedConn,
CommandText = "select ID||','||HashCode||','||sourceDb from Materiel where Code=:Code"
};
relatedCmd.Parameters.Add(new OracleParameter("Code", OracleDbType.Char));
relatedCmd.Prepare();
var reader = sourceCmd.ExecuteReader();
var insertCmd = new OracleCommand()
{
Connection = RelatedConn,
CommandText = GetInsertCmdText()
};
var updateCmd = new OracleCommand()
{
Connection = RelatedConn,
CommandText = GetUpdateCmdText() + $@" where Code=:Code"
};
try
{
var i = 0;
var j = 0;
while (reader.Read())
{
var materiel = new Materiel();
if (reader["FNumber"] == DBNull.Value)
{
continue;
}
materiel.Code = reader["FNumber"].ToString();
materiel.Name = reader["FName"].ToString();
materiel.Type = reader["FTypeName"].ToString();
materiel.BaseUnit = reader["unit"].ToString();
materiel.Specification = reader["FModel"].ToString();
materiel.Flag = 'C';
materiel.K3TimeStamp = DateTime.Now;
materiel.SourceDb = Source.Name;
materiel.CalculateHashCode();
materiel.K3WriteTime = DateTime.Now;
relatedCmd.Parameters[0].Value = materiel.Code;
var obj = relatedCmd.ExecuteScalar();
if (obj == null)
{
materiel.ID = Guid.NewGuid().ToString();
insertModels[i] = materiel;
i++;
if (i == BatchNum)
{
CommitBatch(insertCmd, insertModels);
result += i;
i = 0;
insertModels = new BaseModel[BatchNum];//重置批
}
}
else
{
var arr = obj.ToString().Split(',');
//对比哈希值决定是否需要更新
if (materiel.HashCode != arr[1])
{
if (arr[2] != Source.Name && Source.Name != Configuration.Current.MainData)
{
continue;
}
log4net.LogManager.GetLogger("Logger").Info($"检测到物料更新【{materiel.Code}】");
materiel.ID = obj.ToString().Split(',')[0].ToString();
materiel.Flag = 'U';
updateModels[j] = materiel;
j++;
if (j == BatchNum)
{
CommitBatch(updateCmd, updateModels);
result += j;
j = 0;
updateModels = new BaseModel[BatchNum];//重置批
}
}
}
}
if (i > 0)
{
var oddModels = new BaseModel[i];
for (int k = 0; k < i; k++)
{
oddModels[k] = insertModels[k];
}
CommitBatch(insertCmd, oddModels);
result += i;
}
if (j > 0)
{
var oddModels = new BaseModel[j];
for (int k = 0; k < j; k++)
{
oddModels[k] = updateModels[k];
}
CommitBatch(updateCmd, oddModels);
result += j;
}
reader.Close();
}
catch (Exception e)
{
log4net.LogManager.GetLogger("Logger").Error(e.ToString() + "\r\n" + sourceCmd.CommandText + "\r\n" + insertCmd.CommandText + "\r\n" + updateCmd.CommandText);
throw;
}
finally
{
SourceConn.Close();
RelatedConn.Close();
}
return(result);
}
public override int InitImport(DateTime startTime, DateTime endTime)
{
var result = 0;
//定义批处理的
var models = new BaseModel[BatchNum];
SourceConn.Open();
RelatedConn.Open();
var sourceCmd = new SqlCommand
{
Connection = SourceConn,
CommandText =
$@"select * from
(select FBOMNumber,(select FNumber from t_icitem where t_icitem.FItemID=icbom.FItemID) fshortnumber,FVersion,FStatus,FQty, (SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=icbom.FUnitID) FUnitID
,FInterID,FEnterTime from icbom) a right join (
select FEntryID,(select FNumber from t_icitem where t_icitem.FItemID=ICBOMCHILD.FItemID) detailfshortnumber,(select (SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=t_icitem.FUnitID) from t_icitem where t_icitem.FItemID=ICBOMCHILD.FItemID) detailFUnitID,FQty as detailfqty,FInterID from ICBOMCHILD ) b on a.FInterID=b.FInterID where fshortnumber like '30%' and detailfshortnumber like '30%'
and FEnterTime between CONVERT(datetime, '{startTime}') and CONVERT(datetime, '{endTime}')"
};
var reader = sourceCmd.ExecuteReader();
var relatedCmd = new OracleCommand
{
Connection = RelatedConn,
CommandText = GetInsertCmdText()
};
var i = 0;
try
{
while (reader.Read())
{
var bom = new BOM();
if (reader["FBOMNumber"] == DBNull.Value)
{
continue;
}
bom.BOMCode = reader["FBOMNumber"].ToString();
bom.MaterielCode = reader["fshortnumber"].ToString();
bom.Version = reader["FVersion"].ToString();
bom.UseState = reader["FStatus"].ToString();
bom.MaterielQuantity = reader["FQty"] == DBNull.Value ? 0 : double.Parse(reader["FQty"].ToString());
bom.MaterielUnit = reader["FunitID"].ToString();
bom.DetailCode = reader["FEntryID"].ToString();
bom.DetailMaterielCode = reader["detailfshortnumber"].ToString();
bom.DetailQuantity = reader["detailfqty"] == DBNull.Value ? 0 : double.Parse(reader["FQty"].ToString());
bom.DetailUnit = reader["detailFUnitID"].ToString();
bom.Flag = 'C';
bom.K3TimeStamp = DateTime.Parse(reader["FEnterTime"].ToString());
bom.SourceDb = "XG";
bom.ID = Guid.NewGuid().ToString();
models[i] = bom;
i++;
if (i == BatchNum)
{
CommitBatch(relatedCmd, models);
result += i;
i = 0;
models = new BaseModel[BatchNum];//重置批
}
}
if (i > 0)
{
var oddModels = new BaseModel[i];
for (int j = 0; j < i; j++)
{
oddModels[j] = models[j];
}
CommitBatch(relatedCmd, oddModels);
result += i;
}
reader.Close();
}
catch (Exception e)
{
log4net.LogManager.GetLogger("Logger").Error(e.Message + "\r\n" + sourceCmd.CommandText + "\r\n" + relatedCmd.CommandText);
throw;
}
finally
{
SourceConn.Close();
RelatedConn.Close();
}
return(result);
}
public override int Update(DateTime startTime, DateTime endTime)
{
var result = 0;
SourceConn.Open();
RelatedConn.Open();
//定义批处理的
var insertModels = new BaseModel[BatchNum];
var updateModels = new BaseModel[BatchNum];
var sourceCmd = new SqlCommand
{
Connection = SourceConn,
CommandText =
$@"select * from
(select FBOMNumber,(select FNumber from t_icitem where t_icitem.FItemID=icbom.FItemID) fshortnumber,FVersion,FStatus,FQty, (SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=icbom.FUnitID) FUnitID
,FInterID,FEnterTime from icbom) a right join (
select FEntryID,(select FNumber from t_icitem where t_icitem.FItemID=ICBOMCHILD.FItemID) detailfshortnumber,(select (SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=t_icitem.FUnitID) from t_icitem where t_icitem.FItemID=ICBOMCHILD.FItemID) detailFUnitID,FQty as detailfqty,FInterID from ICBOMCHILD ) b on a.FInterID=b.FInterID where fshortnumber like '30%' and detailfshortnumber like '30%'
",
CommandTimeout = 0
//and FEnterTime between CONVERT(datetime, '{startTime}') and CONVERT(datetime, '{endTime}'),
};
var relatedCmd = new OracleCommand
{
Connection = RelatedConn,
CommandText = "select ID||','||HashCode||','||SourceDb from BOM where BOMCode=:BOMCode and DetailCode=:DetailCode and Version=:Version"
};
relatedCmd.Parameters.Add(new OracleParameter("BOMCode", OracleDbType.Char));
relatedCmd.Parameters.Add(new OracleParameter("DetailCode", OracleDbType.Char));
relatedCmd.Parameters.Add(new OracleParameter("Version", OracleDbType.Char));
relatedCmd.Prepare();
var reader = sourceCmd.ExecuteReader();
var insertCmd = new OracleCommand()
{
Connection = RelatedConn,
CommandText = GetInsertCmdText()
};
var updateCmd = new OracleCommand()
{
Connection = RelatedConn,
CommandText = GetUpdateCmdText() + $@" where BOMCode=:BOMCode and DetailCode=:DetailCode and Version=:Version"
};
try
{
var i = 0;
var j = 0;
while (reader.Read())
{
var bom = new BOM();
if (reader["FBOMNumber"] == DBNull.Value || reader["FVersion"].ToString() == "")
{
continue;
}
bom.BOMCode = reader["FBOMNumber"].ToString();
bom.MaterielCode = reader["fshortnumber"].ToString();
bom.Version = reader["FVersion"].ToString();
bom.UseState = reader["FStatus"].ToString();
bom.MaterielQuantity = reader["FQty"] == DBNull.Value ? 0 : double.Parse(reader["FQty"].ToString());
bom.MaterielUnit = reader["FunitID"].ToString();
bom.DetailCode = reader["FEntryID"].ToString();
bom.DetailMaterielCode = reader["detailfshortnumber"].ToString();
bom.DetailQuantity = reader["detailfqty"] == DBNull.Value ? 0 : double.Parse(reader["FQty"].ToString());
bom.DetailUnit = reader["detailFUnitID"].ToString();
bom.Flag = 'C';
bom.K3TimeStamp = DateTime.Parse(reader["FEnterTime"].ToString());
bom.K3WriteTime = DateTime.Now;
bom.SourceDb = Source.Name;
bom.CalculateHashCode();
relatedCmd.Parameters[0].Value = bom.BOMCode;
relatedCmd.Parameters[1].Value = bom.DetailCode;
relatedCmd.Parameters[2].Value = bom.Version;
var obj = relatedCmd.ExecuteScalar();
if (obj == null)
{
bom.ID = Guid.NewGuid().ToString();
insertModels[i] = bom;
i++;
if (i == BatchNum)
{
CommitBatch(insertCmd, insertModels);
result += i;
i = 0;
insertModels = new BaseModel[BatchNum];//重置批
}
}
else
{
var arr = obj.ToString().Split(',');
if (bom.HashCode != arr[1])
{
if (arr[2] != Source.Name && Source.Name != Configuration.Current.MainData)
{
continue;
}
log4net.LogManager.GetLogger("Logger").Info($"检测到BOM更新【{bom.BOMCode}】");
bom.ID = obj.ToString().Split(',')[0].ToString();
bom.Flag = 'U';
updateModels[j] = bom;
j++;
if (j == BatchNum)
{
CommitBatch(updateCmd, updateModels);
result += j;
j = 0;
updateModels = new BaseModel[BatchNum];//重置批
}
}
}
}
if (i > 0)
{
var oddModels = new BaseModel[i];
for (int k = 0; k < i; k++)
{
oddModels[k] = insertModels[k];
}
CommitBatch(insertCmd, oddModels);
result += i;
}
if (j > 0)
{
var oddModels = new BaseModel[j];
for (int k = 0; k < j; k++)
{
oddModels[k] = updateModels[k];
}
CommitBatch(updateCmd, oddModels);
result += j;
}
reader.Close();
}
catch (Exception e)
{
log4net.LogManager.GetLogger("Logger").Error(e.ToString() + "\r\n" + sourceCmd.CommandText + "\r\n" + insertCmd.CommandText);
throw;
}
finally
{
SourceConn.Close();
RelatedConn.Close();
}
return(result);
}
public override int BackUpdate(DateTime startTime, DateTime endTime)
{
var result = 0;
SourceConn.Open();
RelatedConn.Open();
try
{
var readCmd = new OracleCommand()
{
Connection = RelatedConn,
CommandText = $"select BOMCode,DetailCode,Version from BOM where SourceDb='{ Source.Name}'"
};
var updateCmd = new OracleCommand
{
Connection = RelatedConn,
CommandText = $@"update BOM set {Source.Name}del=1 where BOMCode=:BOMCode and DetailCode=:DetailCode and Version=:Version"
};
updateCmd.Parameters.Add(new OracleParameter("BOMCode", OracleDbType.Char));
updateCmd.Parameters.Add(new OracleParameter("DetailCode", OracleDbType.Char));
updateCmd.Prepare();
var reader = readCmd.ExecuteReader();
var sourceCmd = new SqlCommand
{
Connection = SourceConn,
CommandText = $@"select * from
(select FBOMNumber,(select FNumber from t_icitem where t_icitem.FItemID=icbom.FItemID) fshortnumber,FVersion,FStatus,FQty, (SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=icbom.FUnitID) FUnitID
,FInterID,FEnterTime from icbom) a right join (
select FEntryID,(select FNumber from t_icitem where t_icitem.FItemID=ICBOMCHILD.FItemID) detailfshortnumber,(select (SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=t_icitem.FUnitID) from t_icitem where t_icitem.FItemID=ICBOMCHILD.FItemID) detailFUnitID,FQty as detailfqty,FInterID from ICBOMCHILD ) b on a.FInterID=b.FInterID where fshortnumber like '30%' and detailfshortnumber like '30%'
and (FBOMNumber=@FBOMNumber and FEntryID=@FEntryID and FVersion=@FVersion)",
CommandTimeout = 0
};
sourceCmd.Parameters.Add(new SqlParameter("FBOMNumber", System.Data.SqlDbType.Char, 8000));
sourceCmd.Parameters.Add(new SqlParameter("FEntryID", System.Data.SqlDbType.Char, 8000));
sourceCmd.Parameters.Add(new SqlParameter("FVersion", System.Data.SqlDbType.Char, 8000));
sourceCmd.Prepare();
var a = 0;
while (reader.Read())
{
sourceCmd.Parameters[0].Value = reader[0];
sourceCmd.Parameters[1].Value = reader[1];
sourceCmd.Parameters[2].Value = reader[2];
//log4net.LogManager.GetLogger("logger").Info($"a:{++a}BOMNumber:{reader[0]}");
if (sourceCmd.ExecuteScalar() == null)//如果找不到了，则说明源对应的行被删除，需要标记中间表数据为删除状态
{
updateCmd.Parameters[0].Value = reader[0];
updateCmd.Parameters[1].Value = reader[1];
updateCmd.Parameters[2].Value = reader[2];;
updateCmd.ExecuteNonQuery();
result++;
}
}
}
catch (Exception ex)
{
log4net.LogManager.GetLogger("Logger").Error(ex.ToString());
throw;
}
finally
{
SourceConn.Close();
RelatedConn.Close();
}
return(result);
}
