/// <summary>
/// Updates the mutation status of a mutation record in the AIS.
/// </summary>
/// <param name="info">Info object with detailed information about the new status</param>
/// <returns>The number of affected records.</returns>
public int UpdateMutationStatus(MutationStatusInfo info)
{
int affectedRecords;
using (var cn = AISConnection.GetConnection())
{
var sw = new Stopwatch();
var sql = new StringBuilder();
try
{
sql.Append("Update tbk_viaduc_mttn set aktn_stts = :stts");
// If we have a completed or failed attempt, update the counter
if (info.NewStatus == ActionStatus.SyncCompleted || info.NewStatus == ActionStatus.SyncFailed)
{
sql.Append(", SYNC_ANZ_VRSCH = nvl(SYNC_ANZ_VRSCH, 0) + 1");
}
sql.Append(" where mttn_id = :mttn_id ");
// If the status udpate is only allowed from a specific existing status,
// add the required where clause.
if (info.ChangeFromStatus.HasValue)
{
sql.Append(" and aktn_stts = :sttsFrom ");
}
using (var cmd = new OracleCommand(sql.ToString(), cn))
{
cmd.Parameters.AddWithValue("mttn_id", info.MutationId);
cmd.Parameters.AddWithValue("stts", info.NewStatus);
if (info.ChangeFromStatus.HasValue)
{
cmd.Parameters.AddWithValue("sttsFrom", info.ChangeFromStatus);
}
affectedRecords = cmd.ExecuteNonQuery();
}
LogExecutionTimes(sql.ToString(), sw);
}
catch (Exception ex)
{
Log.Error(ex, ex.Message);
throw;
}
finally
{
cn.Close();
}
// Insert Log record into the log table
if (affectedRecords > 0)
{
InsertMutationStatusLog(info);
}
}
return(affectedRecords);
}
/// <summary>
/// Executes a simple SQL statement against the db.
/// </summary>
/// <param name="sql">The SQL.</param>
/// <param name="parameters">The parameters.</param>
/// <returns>The number of affected records</returns>
private T ExecuteSqlScalar <T>(string sql, OracleParameter[] parameters = null)
{
using (var cn = AISConnection.GetConnection())
{
var sw = new Stopwatch();
sw.Start();
try
{
using (var cmd = new OracleCommand(sql, cn))
{
if (parameters != null)
{
foreach (var parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
}
var obj = cmd.ExecuteScalar();
return((T)obj);
}
}
catch (Exception ex)
{
Log.Error(ex, ex.Message);
throw;
}
finally
{
cn.Close();
LogExecutionTimes(sql, sw);
}
}
}
/// <summary>
/// Executes a simple SQL statement against the db for one or more values.
/// </summary>
/// <param name="sql">The SQL.</param>
/// <param name="parameters">The parameters.</param>
/// <param name="elementCount">The number of elements in the batch.</param>
/// <returns>The number of affected records</returns>
private int ExecuteBatchSql(string sql, OracleParameter[] parameters, int elementCount)
{
using (var cn = AISConnection.GetConnection())
{
var sw = new Stopwatch();
sw.Start();
try
{
using (var cmd = new OracleCommand(sql, cn))
{
if (parameters != null)
{
foreach (var parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
}
return(cmd.ExecuteArray(elementCount));
}
}
catch (Exception ex)
{
Log.Error(ex, ex.Message);
throw;
}
finally
{
cn.Close();
LogExecutionTimes(sql, sw);
}
}
}
public HarvestLogInfoResult GetHarvestLogInfo(HarvestLogInfoRequest request)
{
var retVal = new HarvestLogInfoResult();
var sql = SqlStatements.HarvestLogInfo;
sql = string.Format(sql,
string.IsNullOrEmpty(request.ArchiveRecordIdFilter) ? "" : " AND m.gsft_obj_id = :gsft_obj_id ",
request.ActionStatusFilterList.Any()
? $" AND aktn_stts in ({string.Join(",", request.ActionStatusFilterList.Select(a => ((int) a).ToString()))}) "
: "",
"" /* no sort order yet */);
using (var cn = AISConnection.GetConnection())
{
var dateRange = new QueryDateRange(request.DateRangeFilter);
var cmd = new OracleCommand(sql, cn);
cmd.Parameters.Add("cur1", OracleDbType.Cursor, ParameterDirection.Output);
cmd.Parameters.Add("cur2", OracleDbType.Cursor, ParameterDirection.Output);
cmd.Parameters.AddWithValue("dateFrom", dateRange.From);
cmd.Parameters.AddWithValue("dateTo", dateRange.To);
cmd.Parameters.AddWithValue("recordFrom", request.PageSize * (request.Page - 1));
cmd.Parameters.AddWithValue("recordTo", request.PageSize * request.Page);
if (!string.IsNullOrEmpty(request.ArchiveRecordIdFilter))
{
cmd.Parameters.AddWithValue("gsft_obj_id", request.ArchiveRecordIdFilter);
}
var ds = new DataSet();
var da = new OracleDataAdapter(cmd);
da.TableMappings.Add("Table", "ResultCount");
da.TableMappings.Add("Table1", "LogInfo");
da.Fill(ds);
var dsDetail = new DataSet();
if (ds.Tables["LogInfo"].Rows.Count > 0)
{
var ids = string.Join(",", ds.Tables["LogInfo"].AsEnumerable().Select(t => (int)t.Field <double>("mttn_id")));
dsDetail = GetDataSetFromSql <DataSet>(string.Format(SqlStatements.HarvestLogInfoDetail, ids));
}
retVal.TotalResultSetSize = (int)(decimal)ds.Tables["ResultCount"].Rows[0][0];
retVal.ResultSet = (from r in ds.Tables["LogInfo"].AsEnumerable()
select new HarvestLogInfo
{
MutationId = (int)r.Field <double>("mttn_id"),
ArchiveRecordId = r.Field <double>("gsft_obj_id").ToString("F0"),
ArchiveRecordIdName = r.Field <string>("gsft_obj_kurz_nm"),
ActionName = r.Field <string>("aktn"),
CurrentStatus = (ActionStatus)r.Field <int>("aktn_stts"),
CreationDate = r.Field <DateTime>("erfsg_dt"),
LastChangeDate = r.Field <DateTime>("mttn_dt"),
NumberOfSyncRetries = r.Field <int>("sync_anz_vrsch"),
Details = GetLogDetails(dsDetail, (int)r.Field <double>("mttn_id"))
}
).ToList();
}
return(retVal);
}
/// <summary>
/// Gets a generic DataSet given an SQL statement and optional parameters.
/// </summary>
/// <typeparam name="T">A DataSet or a typed DataSet</typeparam>
/// <param name="sql">The sql statement to execute</param>
/// <param name="parameters">An array or OracleParameters if needed.</param>
/// <returns>The populated DataSet</returns>
private T GetDataSetFromSql <T>(string sql, OracleParameter[] parameters = null) where T : DataSet, new()
{
var ds = new T();
using (var cn = AISConnection.GetConnection())
{
var sw = new Stopwatch();
sw.Start();
try
{
using (var cmd = new OracleCommand(sql, cn))
{
if (parameters != null)
{
foreach (var parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
}
var da = new OracleDataAdapter(cmd);
if (ds.Tables.Count > 0)
{
da.Fill(ds.Tables[0]);
}
else
{
da.Fill(ds);
}
}
}
catch (Exception ex)
{
Log.Error(ex, ex.Message);
throw;
}
finally
{
cn.Close();
LogExecutionTimes(sql, sw);
}
}
return(ds);
}
/// <summary>
/// Loads the node context of a specific record
/// </summary>
/// <param name="recordId"></param>
/// <returns></returns>
public NodeContext LoadNodeContext(long recordId)
{
var retVal = new NodeContext();
using (var cn = AISConnection.GetConnection())
{
var sw = new Stopwatch();
try
{
using (var cmd = new OracleCommand(SqlStatements.SqlNodeContext, cn))
{
// Command is Stored Procedure
cmd.CommandType = CommandType.StoredProcedure;
// Parameter setzen
cmd.Parameters.Add(new OracleParameter("pi_gsft_obj_bzhng_typ_id", OracleDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = vrzngEnhtHierarchieBzhngTypId
});
cmd.Parameters.Add(new OracleParameter("pi_item_id", OracleDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = recordId
});
cmd.Parameters.Add(new OracleParameter("pio_pred_id", OracleDbType.Integer)
{
Direction = ParameterDirection.Output,
Value = -1
});
cmd.Parameters.Add(new OracleParameter("pio_succ_id", OracleDbType.Integer)
{
Direction = ParameterDirection.Output,
Value = -1
});
cmd.Parameters.Add(new OracleParameter("pio_parent_id", OracleDbType.Integer)
{
Direction = ParameterDirection.Output,
Value = -1
});
cmd.Parameters.Add(new OracleParameter("pio_first_child_id", OracleDbType.Integer)
{
Direction = ParameterDirection.Output,
Value = -1
});
cmd.ExecuteNonQuery();
// Set the return values
retVal.ParentArchiveRecordId = Convert.ToInt32(cmd.Parameters["pio_parent_id"].Value).ToString();
retVal.FirstChildArchiveRecordId = Convert.ToInt32(cmd.Parameters["pio_first_child_id"].Value).ToString();
retVal.NextArchiveRecordId = Convert.ToInt32(cmd.Parameters["pio_succ_id"].Value).ToString();
retVal.PreviousArchiveRecordId = Convert.ToInt32(cmd.Parameters["pio_pred_id"].Value).ToString();
retVal.ArchiveRecordId = recordId.ToString();
}
}
catch (Exception ex)
{
Log.Error(ex, ex.Message);
throw;
}
finally
{
cn.Close();
LogExecutionTimes(SqlStatements.SqlNodeContext, sw);
}
}
return(retVal);
}
public int BulkUpdateMutationStatus(List <MutationStatusInfo> infos)
{
int affectedRecords;
// Target status must be the same for all elements
var statusGroup = infos.GroupBy(g => g.NewStatus).ToList();
if (statusGroup.Count > 1)
{
throw new ArgumentException("All elements in the list must have the same NewStatus value.");
}
using (var cn = AISConnection.GetConnection())
{
var sw = new Stopwatch();
var sql = new StringBuilder();
try
{
sql.Append("Update tbk_viaduc_mttn set aktn_stts = :stts");
// If we have a completed or failed attempt, update the counter
if (statusGroup.First().Key == ActionStatus.SyncCompleted || statusGroup.First().Key == ActionStatus.SyncFailed)
{
sql.Append(", SYNC_ANZ_VRSCH = nvl(SYNC_ANZ_VRSCH, 0) + 1");
}
sql.Append(" where mttn_id = :mttn_id ");
// If the status udpate is only allowed from a specific existing status,
// add the required where clause.
if (statusGroup.First().Count(s => s.ChangeFromStatus.HasValue) > 0)
{
sql.Append(" and aktn_stts = :sttsFrom ");
}
using (var cmd = new OracleCommand(sql.ToString(), cn))
{
cmd.Parameters.Add("mttn_id", OracleDbType.Long);
cmd.Parameters["mttn_id"].Value = statusGroup.First().Select(s => s.MutationId).ToArray();
cmd.Parameters.Add("stts", OracleDbType.Integer);
cmd.Parameters["stts"].Value = statusGroup.First().Select(s => (int)s.NewStatus).ToArray();
if (statusGroup.First().Count(s => s.ChangeFromStatus.HasValue) > 0)
{
cmd.Parameters.AddWithValue("sttsFrom", OracleDbType.Integer);
cmd.Parameters["sttsFrom"].Value = statusGroup.First()
.Select(s => s.ChangeFromStatus.HasValue ? (int)s.ChangeFromStatus.Value : -1).ToArray();
}
affectedRecords = cmd.ExecuteArray(infos.Count);
}
LogExecutionTimes(sql.ToString(), sw);
}
catch (Exception ex)
{
Log.Error(ex, ex.Message);
throw;
}
finally
{
cn.Close();
}
// Insert Log record into the log table
if (affectedRecords > 0)
{
InsertMutationStatusLog(infos);
}
}
return(affectedRecords);
}