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