public List <SpotsPerServer> GetAgentsPerServerByPool(int agentTypeArtifactId, int poolId)
{
List <SpotsPerServer> outputList = new List <SpotsPerServer>();
string SQL = @"
SELECT AG.[ServerArtifactID],
Count(AG.[ArtifactID]) AS [Count]
FROM [Agent] AG WITH(NOLOCK)
INNER JOIN [Artifact] A WITH(NOLOCK)
ON AG.[ArtifactID] = A.[ArtifactID]
INNER JOIN [ServerResourceGroup] S WITH(NOLOCK)
ON AG.[ServerArtifactID] = S.[ResourceServerArtifactID]
WHERE A.[DeleteFlag] = 0
AND AG.[AgentTypeArtifactID] = @AgentTypeArtifactID
AND S.[ResourceGroupArtifactID] = @ResourceGroupArtifactID
GROUP BY AG.[ServerArtifactID]";
SqlParameter agentTypeArtifactIdParam = new SqlParameter("@AgentTypeArtifactID", System.Data.SqlDbType.Char)
{
Value = agentTypeArtifactId
};
SqlParameter poolIdParam = new SqlParameter("@ResourceGroupArtifactID", System.Data.SqlDbType.Char)
{
Value = poolId
};
DataTable results = _eddsDbContext.ExecuteSqlStatementAsDataTable(SQL, new SqlParameter[] { agentTypeArtifactIdParam, poolIdParam });
if (results != null)
{
foreach (DataRow row in results.Rows)
{
int serverArtifactId;
int count;
if (!int.TryParse(row["ServerArtifactID"].ToString(), out serverArtifactId))
{
throw new Exception("Unable to cast agent server ArtifactID returned from database to Int32");
}
if (!int.TryParse(row["Count"].ToString(), out count))
{
throw new Exception("Unable to cast agent count returned from database to Int32");
}
outputList.Add(new SpotsPerServer()
{
AgentServerArtifactId = serverArtifactId,
Spots = count
});
}
}
return(outputList);
}
public Theme getTheme()
{
DataRowCollection data;
Theme theme = new Theme();
using (IRSAPIClient proxy = _helper.GetServicesManager().CreateProxy <IRSAPIClient>(ExecutionIdentity.System))
{
int workspace = _workspaceID;
IDBContext dbContext = _helper.GetDBContext(-1);
string sql = $@"SELECT
[Value]
FROM
[eddsdbo].[InstanceSetting]
WHERE
Name LIKE '%Theme UI (light/dark)%'";
data = dbContext.ExecuteSqlStatementAsDataTable(sql).Rows;
foreach (DataRow item in data)
{
foreach (var d in item.ItemArray)
{
theme.textValue = (string)d;
}
}
theme.value = theme.textValue.Equals("true") ? true : false;
theme.textValue = theme.value ? "LIGHT" : "DARK";
}
return(theme);
}
public DataTable RetrieveApplicationWorkspaces(IDBContext eddsDbContext, Guid applicationGuid)
{
const string sql = @"DECLARE @appArtifactID INT
SET @appArtifactID = (SELECT ArtifactID FROM ArtifactGuid WHERE ArtifactGuid = @appGuid)
SELECT C.ArtifactID, C.Name
FROM CaseApplication (NOLOCK) CA
INNER JOIN eddsdbo.[ExtendedCase] C ON CA.CaseID = C.ArtifactID
INNER JOIN eddsdbo.ResourceServer RS ON C.ServerID = RS.ArtifactID
INNER JOIN eddsdbo.Artifact A (NOLOCK) ON C.ArtifactID = A.ArtifactID
INNER JOIN eddsdbo.[ApplicationInstall] as AI on CA.CurrentApplicationInstallID = AI.ApplicationInstallID
WHERE CA.ApplicationID = @appArtifactId
AND AI.[Status] = 6 --Installed
ORDER BY A.CreatedOn" ;
var sqlParams = new List <SqlParameter>
{
new SqlParameter("@appGuid", SqlDbType.UniqueIdentifier)
{
Value = applicationGuid
}
};
return(eddsDbContext.ExecuteSqlStatementAsDataTable(sql, sqlParams));
}
public override Response PreMassOperation()
{
ConsoleEventHandlerComment console = new ConsoleEventHandlerComment();
kCura.EventHandler.Response retVal = new kCura.EventHandler.Response();
retVal.Success = true;
retVal.Message = "Successful Pre Execute Operation method";
IDBContext dbContext = this.Helper.GetDBContext(this.Helper.GetActiveCaseID());
string sqlText = $" SELECT * FROM [Comment] WHERE ArtifactID IN (SELECT ARTIFACTID from[RESOURCE].[{ this.MassActionTableName}]) ";
System.Data.DataRowCollection results = dbContext.ExecuteSqlStatementAsDataTable(sqlText).Rows;
foreach (System.Data.DataRow row in results)
{
DTOs.RDO comme = new DTOs.RDO((int)row.ItemArray[0]);
comme.ArtifactTypeGuids.Add(new Guid(ARTIFACT_TYPE));
comme.Fields.Add(new DTOs.FieldValue(new Guid(COMMENT_FIELD_GUID.ToString()), row.ItemArray[1]));
console.insertJob(dbContext, this.Helper.GetAuthenticationManager().UserInfo.FullName, comme);
DTOs.Choice choice = new DTOs.Choice(ERROR_TYPE_FIELD_GUID);
comme.Fields.Add(new DTOs.FieldValue(TYPE_FIELD_GUID, choice));
using (kCura.Relativity.Client.IRSAPIClient client =
this.Helper.GetServicesManager().CreateProxy <kCura.Relativity.Client.IRSAPIClient>(Relativity.API.ExecutionIdentity.System))
{
client.APIOptions.WorkspaceID = this.Helper.GetActiveCaseID();
client.Repositories.RDO.UpdateSingle(comme);
}
}
return(retVal);
}
public override AgentsDesired GetAgentsDesired()
{
int agentCount = 0;
string SQL = @"
SELECT D.[WorkspaceArtifactID],
D.[SetArtifactID]
FROM [dtSearchIndexQueue] D WITH(NOLOCK)
INNER JOIN [Case] C WITH(NOLOCK)
ON D.[WorkspaceArtifactID] = C.[ArtifactID]
WHERE C.[ResourceGroupArtifactID] = @ResourceGroupArtifactID";
SqlParameter poolIdParam = new SqlParameter("@ResourceGroupArtifactID", System.Data.SqlDbType.Int)
{
Value = AgentAgentResourcePool
};
DataTable queueResults = _eddsDbContext.ExecuteSqlStatementAsDataTable(SQL, new SqlParameter[] { poolIdParam });
if (queueResults.Rows.Count > 0)
{
foreach (DataRow row in queueResults.Rows)
{
int workspaceId;
int setId;
if (!int.TryParse(row["WorkspaceArtifactID"].ToString(), out workspaceId))
{
throw new Exception("Unable to cast WorkspaceArtifactID returned from database to Int32");
}
if (!int.TryParse(row["SetArtifactID"].ToString(), out setId))
{
throw new Exception("Unable to cast SetArtifactId returned from database to Int32");
}
IDBContext workspaceDbContext = _agentHelper.GetDBContext(workspaceId);
SQL = @"
SELECT Count(ID)
FROM [dtSearchSubIndex] WITH(NOLOCK)
WHERE [dtSearchIndexID] = @DtSearchIndexID";
SqlParameter jobIdParam = new SqlParameter("@DtSearchIndexID", System.Data.SqlDbType.Char)
{
Value = setId
};
agentCount += workspaceDbContext.ExecuteSqlStatementAsScalar <int>(SQL, new SqlParameter[] { jobIdParam });
}
}
return(new AgentsDesired()
{
Guid = Guid,
RespectsResourcePool = RespectsResourcePool,
Count = agentCount
});
}
public DataRowCollection showCommentChilds(IDBContext dbcontext, int parentCommentId)
{
string comment_child_query = $@"SELECT [ArtifactID]
FROM [{dbcontext.Database}].[EDDSDBO].[Comment]
where RelatedComments = {parentCommentId}";
DataRowCollection data = dbcontext.ExecuteSqlStatementAsDataTable(comment_child_query).Rows;
return(data);
}
public DataRow RetrieveSingleInManagerQueueByArtifactId(IDBContext dbContext, Int32 artifactId, Int32 workspaceArtifactId)
{
var sql = String.Format(@"
DECLARE @offset INT SET @offset = (SELECT DATEDIFF(HOUR,GetUTCDate(),GetDate()))
SELECT
Q.[ID]
,DATEADD(HOUR,@offset,Q.[TimeStampUTC]) [Added On]
,Q.WorkspaceArtifactID [Workspace Artifact ID]
,C.Name [Workspace Name]
,CASE Q.[QueueStatus]
WHEN @notStartedStatusId THEN 'Waiting'
WHEN @inProgressStatusId THEN 'In Progress'
WHEN @errorStatusId THEN 'Error'
END [Status]
,Q.AgentID [Agent Artifact ID]
,Q.ExtractorSetArtifactID [Job ID]
FROM EDDSDBO.{0} Q
INNER JOIN EDDS.EDDSDBO.ExtendedCase C ON Q.WorkspaceArtifactID = C.ArtifactID
WHERE Q.ExtractorSetArtifactID = @artifactId
AND Q.WorkspaceArtifactID = @workspaceArtifactId" , Constant.Tables.ManagerQueue);
var sqlParams = new List <SqlParameter>
{
new SqlParameter("@notStartedStatusId", SqlDbType.Int)
{
Value = Constant.QueueStatus.NotStarted
},
new SqlParameter("@inProgressStatusId", SqlDbType.Int)
{
Value = Constant.QueueStatus.InProgress
},
new SqlParameter("@errorStatusId", SqlDbType.Int)
{
Value = Constant.QueueStatus.Error
},
new SqlParameter("@artifactId", SqlDbType.Int)
{
Value = artifactId
},
new SqlParameter("@workspaceArtifactId", SqlDbType.Int)
{
Value = workspaceArtifactId
}
};
var dt = dbContext.ExecuteSqlStatementAsDataTable(sql, sqlParams);
if (dt.Rows.Count > 0)
{
return(dt.Rows[0]);
}
return(null);
}
public DataTable RetrieveWorkspaces(IDBContext eddsDbContext)
{
const string sql = @"
SELECT
ArtifactId [CaseArtifactID]
,Name [CaseName]
,DBLocation [ServerName]
FROM EDDSDBO.[ExtendedCase] WITH(NOLOCK) " ;
return(eddsDbContext.ExecuteSqlStatementAsDataTable(sql));
}
public List <Comment> GetCommentsChild(int commentId)
{
string sql = $@"SELECT [ArtifactID]
FROM [{DBContextSql.Database}].[EDDSDBO].[Comment]
Where
RelatedComments = {commentId}";
List <Comment> commentsChild = new List <Comment>();
DataRowCollection CommentsChildsList = DBContextSql.ExecuteSqlStatementAsDataTable(sql).Rows;
foreach (DataRow childs in CommentsChildsList)
{
foreach (var childArtifacId in childs.ItemArray)
{
Comment comment = new Comment((int)childArtifacId);
commentsChild.Add(comment);
}
}
return(commentsChild);
}
public dynamic ExecuteSql(IDBContext dbContext, Action action, DapiJson dapiJson)
{
List <SqlParameter> sqlParameters = new List <SqlParameter>();
foreach (Parameter param in action.Parameters)
{
SqlParameter sqlParam = new SqlParameter(param.Name, Mapper.Map <SqlDbType>(param.DataType));
if (param.Length > 0)
{
sqlParam.Size = param.Length ?? sqlParam.Size;
}
var dParam = dapiJson.Parameters.Find(d => d.name.Equals(param.Name, StringComparison.OrdinalIgnoreCase));
sqlParam.Value = dParam?.value;
sqlParameters.Add(sqlParam);
}
dynamic result;
try
{
switch (action.Outputs.OutputMode)
{
case OutputMode.NoReturnValue:
result = dbContext.ExecuteNonQuerySQLStatement(action.SQL, sqlParameters);
result = "";
break;
case OutputMode.Scalar:
result = dbContext.ExecuteSqlStatementAsScalar <dynamic>(action.SQL, sqlParameters);
break;
case OutputMode.Rowset:
result = dbContext.ExecuteSqlStatementAsDataTable(action.SQL, sqlParameters);
break;
default:
result = "";
break;
}
DidActionExecute = true;
}
catch (Exception ex)
{
DidActionExecute = false;
result = ex.Message;
throw;
}
return(result);
}
public SmtpSettings GetSmptSettings(IDBContext context)
{
SmtpSettings settings = null;
DataTable result = null;
string sql = @"SELECT [Name],[Value] FROM [EDDS].[EDDSDBO].[Configuration] WITH (NOLOCK) WHERE Section='kCura.Notification'";
result = context.ExecuteSqlStatementAsDataTable(sql);
if (result.Rows.Count > 0)
{
settings = new SmtpSettings();
// ENHANCEMENT: Good task to figure out a better way to map the values to SmtpSetting object (than using switch-statement)
string name;
string value;
foreach (DataRow dataRow in result.Rows)
{
name = dataRow["Name"] as string;
value = dataRow["Value"] as string;
if ((name == "AuthenticationEmailFrom" || name == "SMTPPassword" || name == "SMTPPort" || name == "SMTPServer" || name == "SMTPUserName") && String.IsNullOrEmpty(value) == true)
{
throw new TextExtractor.Helpers.CustomExceptions.IncorrectSmtpSettingsException("There are one or more missing SMTP setting. ");
}
switch (name)
{
case "AuthenticationEmailFrom":
settings.RelativityInstanceFromEmailAddress = value;
break;
case "SMTPPassword":
settings.Password = value;
break;
case "SMTPPort":
settings.Port = Convert.ToInt32(value);
break;
case "SMTPServer":
settings.Server = value;
break;
case "SMTPUserName":
settings.UserName = value;
break;
}
}
}
return(settings);
}
public List <Int32> RetrieveExtractorProfilesForField(IDBContext eddsDbContext, String fieldGuid1, String fieldGuid2, Int32 fieldArtifactId)
{
var sql = String.Format(@"
DECLARE @FieldArtifactID1 INT, @FieldArtifactID2 INT
DECLARE @MultiObjectTableName VARCHAR(50), @MultiObjectFieldName1 VARCHAR(50), @MultiObjectFieldName2 VARCHAR(50)
SELECT @FieldArtifactID1 = ArtifactID FROM [EDDSDBO].[ArtifactGuid] WITH(NOLOCK)
WHERE ArtifactGuid = @FieldGuid1
SELECT @FieldArtifactID2 = ArtifactID FROM [EDDSDBO].[ArtifactGuid] WITH(NOLOCK)
WHERE ArtifactGuid = @FieldGuid2
SELECT
@MultiObjectTableName = RelationalTableSchemaName,
@MultiObjectFieldName1 = RelationalTableFieldColumnName1,
@MultiObjectFieldName2 = RelationalTableFieldColumnName2
FROM [EDDSDBO].[ObjectsFieldRelation] WITH(NOLOCK)
WHERE
FieldArtifactId1 = @FieldArtifactID1
AND FieldArtifactId2 = @FieldArtifactID2
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = '
SELECT ' + @MultiObjectFieldName2 + ' [TextExtractorProfileArtifactID]
FROM [EDDSDBO].[' + @MultiObjectTableName + '] WITH(NOLOCK)
WHERE ' + @MultiObjectFieldName1 + ' = ' + CONVERT(VARCHAR(20), {0})
EXEC(@sql)" , fieldArtifactId);
var sqlParams = new List <SqlParameter>
{
new SqlParameter("@FieldGuid1", SqlDbType.VarChar)
{
Value = fieldGuid1
},
new SqlParameter("@FieldGuid2", SqlDbType.VarChar)
{
Value = fieldGuid2
},
};
DataTable dtProfiles = eddsDbContext.ExecuteSqlStatementAsDataTable(sql, sqlParams);
if (dtProfiles.Rows.Count > 0)
{
var list = dtProfiles.Rows.OfType <DataRow>().Select(dr => dr.Field <int>("TextExtractorProfileArtifactID")).ToList();
return(list);
}
return(null);
}
//Returns the artifactId of a particular Guid
public static Int32 FindArtifactIdByGuid(IDBContext workspaceContext, Guid guid)
{
var parameters = new List <SqlParameter> {
new SqlParameter {
ParameterName = "@Guid", SqlDbType = SqlDbType.UniqueIdentifier, Value = guid
}
};
var artifactIdQuery = workspaceContext.ExecuteSqlStatementAsDataTable(SQL.ArtifactIdByGuid, parameters);
if (artifactIdQuery.Rows.Count < 1)
{
throw new Exception("Guid does not exist in workspace: " + guid);
}
return((Int32)artifactIdQuery.Rows[0]["ArtifactID"]);
}
//Returns the first ArtifactId of the workspace that has the oldest install of the provided App Guid
public static Int32 FindFirstWorkspaceWhereAppIsInstalled(IDBContext eddsDbContext, Guid appGuid)
{
var applicationGuid = appGuid;
var parameters = new List <SqlParameter> {
new SqlParameter {
ParameterName = "@ApplicationGuid", SqlDbType = SqlDbType.UniqueIdentifier, Value = applicationGuid
}
};
var workspaceQuery = eddsDbContext.ExecuteSqlStatementAsDataTable(SQL.WorkspacesWhereAppIsInstalled, parameters);
if (workspaceQuery.Rows.Count < 1)
{
throw new Exceptions.DisclaimerAppIsNotInstalledExcepetion();
}
return((Int32)workspaceQuery.Rows[0]["CaseID"]);
}
//Returns a list of UserIds that represent the users that need to accept the disclaimer
public static IEnumerable <Int32> FindUsersWhoNeedToAcceptDisclaimer(IDBContext eddsDbContext, Models.Disclaimer disclaimer)
{
var usersWhoNeedToAccept = new List <Int32>();
var groupListFormattedForSql = String.Join(",", disclaimer.ApplicableGroups);
var retrieveEligibleUsers = false;
var query = string.Empty;
var parameters = new List <SqlParameter> {
new SqlParameter {
ParameterName = "@DisclaimerArtifactID", SqlDbType = SqlDbType.Int, Value = disclaimer.DisclaimerId
}
};
if (disclaimer.AllUsers && disclaimer.ReacceptancePeriod > 0)
{
query = SQL.NonAccepptedAllUsersTimeCriteria;
parameters.Add(new SqlParameter {
ParameterName = "@ReacceptancePeriod", SqlDbType = SqlDbType.Int, Value = disclaimer.ReacceptancePeriod
});
retrieveEligibleUsers = true;
}
else if (disclaimer.AllUsers)
{
query = SQL.NonAccepptedAllUsers;
retrieveEligibleUsers = true;
}
else if (disclaimer.ReacceptancePeriod > 0 && disclaimer.ApplicableGroups.Any())
{
query = String.Format(SQL.NonAcceptedUsersByGroupsTimeCriteria, groupListFormattedForSql);
parameters.Add(new SqlParameter {
ParameterName = "@ReacceptancePeriod", SqlDbType = SqlDbType.Int, Value = disclaimer.ReacceptancePeriod
});
retrieveEligibleUsers = true;
}
else if (disclaimer.ApplicableGroups.Any())
{
query = String.Format(SQL.NonAcceptedUsersByGroups, groupListFormattedForSql);
retrieveEligibleUsers = true;
}
if (retrieveEligibleUsers)
{
usersWhoNeedToAccept.AddRange(eddsDbContext.ExecuteSqlStatementAsDataTable(query, parameters).AsEnumerable().Select(x => Int32.Parse(x["ArtifactID"].ToString())).ToList());
}
return(usersWhoNeedToAccept);
}
public IEnumerable <AuditComment> getCommentAudit(int commentId)
{
List <AuditComment> audit = new List <AuditComment>();
DataRowCollection data;
using (IRSAPIClient proxy = _helper.GetServicesManager().CreateProxy <IRSAPIClient>(ExecutionIdentity.System))
{
int workspace = _workspaceID;
IDBContext dbContext = _helper.GetDBContext(workspace);
string sql = $@"SELECT TOP (1000) [ArtifactId]
,[CommentId]
,[CreatedOn]
,[CreateByUserId]
,[CreatedByUserName]
,[ModifiedOn]
,[ModifiedByUserId]
,[ModifiedByUserName]
,[ReplysAmount]
,[comment]
,[type]
FROM [EDDSDBO].[AuditComment]
WHERE [CommentId] ={commentId};";
data = dbContext.ExecuteSqlStatementAsDataTable(sql).Rows;
foreach (DataRow item in data)
{
AuditComment commentAudit = new AuditComment();
commentAudit.commentId = (int)item.ItemArray[1];
commentAudit.createdOn = (item.ItemArray[2]).ToString();
commentAudit.createByUserId = commentAudit.createdOn == string.Empty ? 0 : (int)item.ItemArray[3];
commentAudit.createdByUserName = commentAudit.createdOn == string.Empty ? "" : (string)item.ItemArray[4];
commentAudit.modifiedOn = (item.ItemArray[5]).ToString();
commentAudit.modifiedByUserId = commentAudit.createdOn == string.Empty ? (int)item.ItemArray[6] : 0;
commentAudit.modifiedByUserName = commentAudit.createdOn == string.Empty ? (string)item.ItemArray[7] : "";
commentAudit.replysAmount = (int)item.ItemArray[8];
commentAudit.comment = (string)item.ItemArray[9];
commentAudit.type = (string)item.ItemArray[10];
audit.Add(commentAudit);
}
}
return(audit);
}
public DataTable RetrieveCommonRow(IDBContext eddsDbContext, Int32 agentId, String queueTableName)
{
var sql = String.Format(@"SELECT TOP 1
[WorkspaceArtifactID],
[ExtractorSetArtifactID]
FROM
[EDDSDBO].{0}
WHERE [AgentID] = @agentID" , queueTableName);
var sqlParams = new List <SqlParameter>
{
new SqlParameter("@agentID", SqlDbType.Int)
{
Value = agentId
}
};
var table = eddsDbContext.ExecuteSqlStatementAsDataTable(sql, sqlParams);
return(table);
}
public bool IsOkToDelete(IDBContext eddsDbContext, string tempTable, string database)
{
string sql = $@"
SELECT j.Status
FROM [EDDSResource].[EDDSDBO].[{tempTable}] AS t WITH(NOLOCK),[{database}].[EDDSDBO].[{GetJobType()}] AS j WITH(NOLOCK)
WHERE t.ArtifactID = j.ArtifactID ";
var exportJobs = eddsDbContext.ExecuteSqlStatementAsDataTable(sql);
var statusOkToDelete = new List <string>
{
Constant.Status.Job.NEW,
Constant.Status.Job.CANCELLED,
Constant.Status.Job.COMPLETED,
Constant.Status.Job.COMPLETED_WITH_ERRORS,
Constant.Status.Job.COMPLETED_WITH_ERRORS_AND_SKIPPED_DOCUMENTS,
Constant.Status.Job.COMPLETED_WITH_SKIPPED_DOCUMENTS,
Constant.Status.Job.ERROR,
Constant.Status.Job.REVERTED,
Constant.Status.Job.VALIDATED,
};
return(exportJobs?.Rows == null || (from DataRow dataRow in exportJobs.Rows select(string) dataRow[0]).All(status => statusOkToDelete.Contains(status)));
}
public override void Execute()
{
// Update Security Protocol
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
// Get logger
Relativity.API.IAPILog _logger = this.Helper.GetLoggerFactory().GetLogger().ForContext <Agent>();
// Get current Agent ID
int agentArtifactId = this.AgentID;
_logger.LogDebug("Audit Log Elastic Search, current Agent ID: {agentArtifactId}", agentArtifactId.ToString());
// Display initial message
this.RaiseMessageNoLogging("Getting Instance Settings.", 10);
// Get ES URI Instance Settings
List <Uri> elasticUris = new List <Uri>();
try
{
string[] uris = this.Helper.GetInstanceSettingBundle().GetString("Relativity.AuditLogElasticSearch", "ElasticSearchUris").Split(';');
foreach (string uri in uris)
{
if (Uri.IsWellFormedUriString(uri, UriKind.Absolute))
{
elasticUris.Add(new Uri(uri));
}
else
{
_logger.LogError("Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchUri), single URI error ({uri})", agentArtifactId.ToString(), uri);
this.RaiseMessageNoLogging(string.Format("Instance Settings error (ElasticSearchUri), single URI error ({0}).", uri), 1);
return;
}
}
}
catch (Exception e)
{
_logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchUri)", agentArtifactId.ToString());
this.RaiseMessageNoLogging("Instance Settings error (ElasticSearchUri).", 1);
return;
}
// Get ES authentication API Key Instance Settings
string[] elasticApiKey = new string[] { "", "" };
try
{
string apiKey = this.Helper.GetInstanceSettingBundle().GetString("Relativity.AuditLogElasticSearch", "ElasticSearchApiKey");
if (apiKey.Length > 0)
{
if (apiKey.Split(':').Length == 2)
{
elasticApiKey = apiKey.Split(':');
}
else
{
_logger.LogError("Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchApiKey), API Key format error ({apiKey})", agentArtifactId.ToString(), apiKey);
this.RaiseMessageNoLogging(string.Format("Instance Settings error (ElasticSearchApiKey), API Key format error ({0}).", apiKey), 1);
return;
}
}
}
catch (Exception e)
{
_logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchApiKey)", agentArtifactId.ToString());
this.RaiseMessageNoLogging("Instance Settings error (ElasticSearchApiKey).", 1);
return;
}
// Get ES index prefix Instance Settings (must by lowercase)
string elasticIndexPrefix = "";
try
{
elasticIndexPrefix = this.Helper.GetInstanceSettingBundle().GetString("Relativity.AuditLogElasticSearch", "ElasticSearchIndexPrefix").ToLower();
}
catch (Exception e)
{
_logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchIndexPrefix)", agentArtifactId.ToString());
this.RaiseMessageNoLogging("Instance Settings error (ElasticSearchIndexPrefix).", 1);
return;
}
// Get ES index number of replicas Instance Settings
int elasticIndexReplicas = 1;
try
{
elasticIndexReplicas = this.Helper.GetInstanceSettingBundle().GetInt("Relativity.AuditLogElasticSearch", "ElasticSearchIndexReplicas").Value;
if (elasticIndexReplicas < 0)
{
elasticIndexReplicas = 1;
}
}
catch (Exception e)
{
_logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchIndexReplicas)", agentArtifactId.ToString());
this.RaiseMessageNoLogging("Instance Settings error (ElasticSearchIndexReplicas).", 1);
return;
}
// Get ES index number of shards Instance Settings
int elasticIndexShards = 1;
try
{
elasticIndexShards = this.Helper.GetInstanceSettingBundle().GetInt("Relativity.AuditLogElasticSearch", "ElasticSearchIndexShards").Value;
if (elasticIndexShards < 0)
{
elasticIndexShards = 1;
}
}
catch (Exception e)
{
_logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchIndexShards)", agentArtifactId.ToString());
this.RaiseMessageNoLogging("Instance Settings error (ElasticSearchIndexShards).", 1);
return;
}
// Get ES synchronization threshold for one agent run
int elasticSyncSize = 1000;
try
{
elasticSyncSize = this.Helper.GetInstanceSettingBundle().GetInt("Relativity.AuditLogElasticSearch", "ElasticSearchSyncSize").Value;
if (elasticSyncSize < 1000)
{
elasticSyncSize = 1000;
}
}
catch (Exception e)
{
_logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchSyncSize)", agentArtifactId.ToString());
this.RaiseMessageNoLogging("Instance Settings error (ElasticSearchSyncSize).", 1);
return;
}
// Get database context of the instance
IDBContext instanceContext = Helper.GetDBContext(-1);
// Check if management table exists
try
{
int exists = instanceContext.ExecuteSqlStatementAsScalar <int>("IF OBJECT_ID('[eddsdbo].[" + this.tableName + "]', 'U') IS NOT NULL SELECT 1 ELSE SELECT 0");
_logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), application management table " + (exists == 1 ? "exists" : "does not exist"), agentArtifactId.ToString());
if (exists != 1)
{
_logger.LogError("Audit Log Elastic Search, Agent ({agentArtifactId}), application management table does not exist", agentArtifactId.ToString());
this.RaiseMessageNoLogging("Application management table does not exist.", 1);
return;
}
}
catch (Exception e)
{
_logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), application management table existence check error", agentArtifactId.ToString());
}
_logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), selecting Workspace", agentArtifactId.ToString());
this.RaiseMessageNoLogging("Selecting Workspace.", 10);
// Check what needs to be done
int workspaceId = -1;
long auditRecordId = -1;
int status = -1;
instanceContext.BeginTransaction();
try
{
// Get workspace that was synchronized latest
DataTable dataTable = instanceContext.ExecuteSqlStatementAsDataTable(@"
SELECT TOP(1)
[CaseArtifactID],
[AuditRecordID],
[Status]
FROM [eddsdbo].[" + this.tableName + @"]
WHERE [AgentArtifactID] IS NULL
ORDER BY
[Status] ASC,
[LastUpdated] ASC
");
// If there is no workspace check if table is empty and if it is, delete it
_logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), Workspace selection row count: {count}", agentArtifactId.ToString(), dataTable.Rows.Count.ToString());
if (dataTable.Rows.Count == 0)
{
int count = instanceContext.ExecuteSqlStatementAsScalar <int>("SELECT COUNT(*) FROM [eddsdbo].[" + this.tableName + "]");
_logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), application management table row count: {count}", agentArtifactId.ToString(), count.ToString());
// If there are no rows in the application management table better to drop it
if (count == 0)
{
instanceContext.ExecuteNonQuerySQLStatement("DROP TABLE [eddsdbo].[" + this.tableName + "]");
_logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), application management table was deleted", agentArtifactId.ToString());
}
}
// Else we have workspace to work with
else
{
DataRow dataRow = dataTable.Rows[0];
workspaceId = Convert.ToInt32(dataRow["CaseArtifactID"]);
auditRecordId = Convert.ToInt64(dataRow["AuditRecordID"]);
status = Convert.ToInt32(dataRow["Status"]);
// Update the application management table with Agent ID lock
SqlParameter agentArtifactIdParam = new SqlParameter("@agentArtifactId", agentArtifactId);
SqlParameter workspaceIdParam = new SqlParameter("@workspaceId", workspaceId);
instanceContext.ExecuteNonQuerySQLStatement("UPDATE [eddsdbo].[" + this.tableName + "] SET [AgentArtifactID] = @agentArtifactId WHERE [CaseArtifactID] = @workspaceId", new SqlParameter[] { agentArtifactIdParam, workspaceIdParam });
}
instanceContext.CommitTransaction();
}
catch (Exception e)
{
instanceContext.RollbackTransaction();
_logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), application management table querying error", agentArtifactId.ToString());
this.RaiseMessageNoLogging("Application management table querying error.", 1);
return;
}
// If we have Workspace ID we have to do something
if (workspaceId > 0)
{
// Construct ES index name
string elasticIndexName = elasticIndexPrefix + workspaceId.ToString();
// Construct connector to ES cluster
Nest.ElasticClient elasticClient = null;
try
{
Elasticsearch.Net.StaticConnectionPool pool = new Elasticsearch.Net.StaticConnectionPool(elasticUris, true);
elasticClient = new Nest.ElasticClient(new Nest.ConnectionSettings(pool).DefaultIndex(elasticIndexName).ApiKeyAuthentication(elasticApiKey[0], elasticApiKey[1]).EnableHttpCompression());
}
catch (Exception e)
{
this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId);
_logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}) Elastic Search connection call error ({elasticUris}, {indexName})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName);
this.RaiseMessageNoLogging(string.Format("Elastic Search connection call error ({0}, {1}).", string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName), 1);
return;
}
// Check ES cluster connection
Nest.PingResponse pingResponse = elasticClient.Ping();
if (pingResponse.IsValid)
{
_logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), Ping succeeded ({elasticUris}, {indexName})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName);
}
else
{
this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId);
_logger.LogError("Audit Log Elastic Search, Agent ({agentArtifactId}), Ping failed, check cluster health and connection settings ({elasticUris}, {indexName}, {elasticError})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName, pingResponse.DebugInformation);
this.RaiseMessageNoLogging(string.Format("Elastic Search ping failed, check cluster health and connection settings ({0}, {1}, {2}).", string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName, pingResponse.DebugInformation), 1);
return;
}
switch (status)
{
// If the status is 0 we will be deleting ES index
case 0:
_logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), deleting ES index ({indexName})", agentArtifactId.ToString(), elasticIndexName);
this.RaiseMessageNoLogging(string.Format("Deleting ES index ({0}).", elasticIndexName), 10);
// Delete ES index
try
{
Nest.DeleteIndexResponse deleteIndexResponse = elasticClient.Indices.Delete(elasticIndexName);
if (deleteIndexResponse.Acknowledged)
{
_logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), Elastic Search index deleted ({elasticUris}, {indexName})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName);
}
else
{
this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId);
_logger.LogError("Audit Log Elastic Search, Agent ({agentArtifactId}), Elastic Search index deletion error ({elasticUris}, {indexName})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName);
this.RaiseMessageNoLogging(string.Format("Elastic Search index deletion error ({0}, {1}).", string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName), 1);
return;
}
}
catch (Exception e)
{
this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId);
_logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}) Elastic Search deletion call error ({indexName})", agentArtifactId.ToString(), elasticIndexName);
this.RaiseMessageNoLogging(string.Format("Elastic Search deletion call error ({0}).", elasticIndexName), 1);
return;
}
// Delete related row from the application management table
try
{
SqlParameter workspaceIdParam = new SqlParameter("@workspaceId", workspaceId);
instanceContext.ExecuteNonQuerySQLStatement("DELETE FROM [eddsdbo].[" + this.tableName + "] WHERE [Status] = 0 AND [CaseArtifactID] = @workspaceId", new SqlParameter[] { workspaceIdParam });
}
catch (Exception e)
{
this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId);
_logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), application management table delete error", agentArtifactId.ToString());
this.RaiseMessageNoLogging("Application management table delete error.", 1);
return;
}
break;
// If the status is 1 we will be synchronizing Audit Log with ES index
case 1:
_logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), synchronizing Audit Log of Workspace ({workspaceId}) to ES index ({indexName})", agentArtifactId.ToString(), workspaceId.ToString(), elasticIndexName);
this.RaiseMessageNoLogging(string.Format("Synchronizing Audit Log of Workspace ({0}) to ES index ({1})", workspaceId.ToString(), elasticIndexName), 10);
// If there is no records synchronized yet, we have to create ES index first
if (auditRecordId == 0)
{
// Create ES index
try {
Nest.CreateIndexResponse createIndexResponse = elasticClient.Indices.Create(elasticIndexName, c => c.Settings(s => s.NumberOfShards(elasticIndexShards).NumberOfReplicas(elasticIndexReplicas)).Map <AuditRecord>(m => m.AutoMap()));
if (createIndexResponse.Acknowledged)
{
_logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), Elastic Search index created ({elasticUris}, {indexName})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName);
}
else
{
this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId);
_logger.LogError("Audit Log Elastic Search, Agent ({agentArtifactId}), Elastic Search index creation error ({elasticUris}, {indexName}, {serverError})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName, createIndexResponse.ServerError.ToString());
this.RaiseMessageNoLogging(string.Format("Elastic Search index creation error ({0}, {1}).", string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName), 1);
return;
}
}
catch (Exception e)
{
this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId);
_logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}) Elastic Search index creation call error ({elasticUris}, {indexName})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName);
this.RaiseMessageNoLogging(string.Format("Elastic Search index creation call error ({0}, {1}).", string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName), 1);
return;
}
}
// Get database context of the given workspace
IDBContext workspaceContext = Helper.GetDBContext(workspaceId);
// Synchronize until threshold is reached
int syncCount = 0;
while (syncCount < elasticSyncSize)
{
try
{
// Get Audit Log to synchronize
SqlParameter auditRecordIdParam = new SqlParameter("@auditRecordId", auditRecordId);
DataTable dataTable = workspaceContext.ExecuteSqlStatementAsDataTable(@"
SELECT TOP (1000)
[AuditRecord].[ID],
[AuditRecord].[TimeStamp],
[AuditRecord].[ArtifactID],
[AuditRecord].[Action] AS [ActionID],
[AuditAction].[Action],
[AuditRecord].[UserID],
[AuditUser].[FullName] AS [User],
[AuditRecord].[ExecutionTime],
[AuditRecord].[Details],
[AuditRecord].[RequestOrigination],
[AuditRecord].[RecordOrigination]
FROM [EDDSDBO].[AuditRecord] WITH (NOLOCK)
JOIN [EDDSDBO].[AuditUser] WITH (NOLOCK) ON [AuditRecord].[UserID] = [AuditUser].[UserID]
JOIN [EDDSDBO].[AuditAction] WITH (NOLOCK) ON [AuditRecord].[Action] = [AuditAction].[AuditActionID]
WHERE [AuditRecord].[ID] > @auditRecordId
ORDER BY [AuditRecord].[ID] ASC
", new SqlParameter[] { auditRecordIdParam });
// If there is nothing to synchronize end
_logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), Audit Log row count to synchronize: {count}", agentArtifactId.ToString(), dataTable.Rows.Count.ToString());
if (dataTable.Rows.Count == 0)
{
// Log end of Agent execution
this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId);
_logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), completed, nothing to synchronize", agentArtifactId.ToString());
this.RaiseMessageNoLogging("Completed.", 10);
return;
}
// Else synchronize workspace Audit Log with ES index
else
{
// Synchronizing workspace Audit Log with ES index
List <AuditRecord> auditRecords = new List <AuditRecord>();
long newAuditRecordId = auditRecordId;
for (int i = 0; i < dataTable.Rows.Count; i++)
{
// Read Audit Log data
AuditRecord auditRecord = new AuditRecord();
DataRow dataRow = dataTable.Rows[i];
auditRecord.AuditRecordId = Convert.ToInt64(dataRow["ID"]);
auditRecord.TimeStamp = Convert.ToDateTime(dataRow["TimeStamp"]);
auditRecord.ArtifactId = Convert.ToInt32(dataRow["ArtifactID"]);
auditRecord.ActionId = Convert.ToInt32(dataRow["ActionID"]);
auditRecord.Action = Convert.ToString(dataRow["Action"]);
auditRecord.UserId = Convert.ToInt32(dataRow["UserID"]);
auditRecord.User = Convert.ToString(dataRow["User"]);
auditRecord.ExecutionTime = dataRow["ExecutionTime"] is DBNull ? default : Convert.ToInt32(dataRow["ExecutionTime"]);
auditRecord.Details = dataRow["Details"] is DBNull ? default : Convert.ToString(dataRow["Details"]);
auditRecord.RequestOrigination = dataRow["RequestOrigination"] is DBNull ? default : Convert.ToString(dataRow["RequestOrigination"]);
auditRecord.RecordOrigination = dataRow["RecordOrigination"] is DBNull ? default : Convert.ToString(dataRow["RecordOrigination"]);
auditRecords.Add(auditRecord);
// Record last Audit Log ID
if (newAuditRecordId < auditRecord.AuditRecordId)
{
newAuditRecordId = auditRecord.AuditRecordId;
}
// Index data in threshold is reached or we are at the last row
if (auditRecords.Count >= 500 || i + 1 >= dataTable.Rows.Count)
{
try
{
Nest.BulkResponse bulkResponse = elasticClient.Bulk(b => b.Index(elasticIndexName).IndexMany(auditRecords, (descriptor, s) => descriptor.Id(s.AuditRecordId.ToString())));
if (!bulkResponse.Errors)
{
auditRecords.Clear();
_logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), documents synchronized to Elastic Serach index ({indexName})", agentArtifactId.ToString(), elasticIndexName);
}
else
{
this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId);
foreach (Nest.BulkResponseItemBase itemWithError in bulkResponse.ItemsWithErrors)
{
_logger.LogError("Audit Log Elastic Search, Agent ({agentArtifactId}), Elastic Serach bulk index error to index {indexName} ({elasticUris}) on document {docIs}:{docError}", agentArtifactId.ToString(), elasticIndexName, string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), itemWithError.Id, itemWithError.Error.ToString());
}
this.RaiseMessageNoLogging(string.Format("Elastic Serach bulk index error to index {0} ({1}).", elasticIndexName, string.Join(";", elasticUris.Select(x => x.ToString()).ToArray())), 1);
return;
}
}
catch (Exception e)
{
this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId);
_logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}) Elastic Search bulk index call error ({elasticUris}, {indexName})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName);
this.RaiseMessageNoLogging(string.Format("Elastic Search bulk index call error ({0}, {1}).", string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName), 1);
return;
}
}
}
// After successful indexing assign new Audit Log ID
auditRecordId = newAuditRecordId;
}
}
public DataTable RetrieveNextBatchInWorkerQueue(IDBContext eddsDbContext, Int32 agentId, Int32 batchSize, String uniqueTableName, Int32 resourceServerArtifactId)
{
var sql = String.Format(@"
BEGIN TRAN
IF NOT OBJECT_ID('EDDSDBO.{1}') IS NULL BEGIN
DROP TABLE EDDSDBO.{1}
END
CREATE TABLE EDDSDBO.{1}(ID INT)
DECLARE @workspaceArtifactID INT
DECLARE @extractorSetArtifactID INT
SELECT TOP 1
@workspaceArtifactID = [WorkspaceArtifactID],
@extractorSetArtifactID = [ExtractorSetArtifactID]
FROM EDDSDBO.{0}
WHERE
QueueStatus = @notStartedQueueStatus
AND [WorkspaceArtifactID] IN (
SELECT ArtifactID FROM [EDDSDBO].[ExtendedCase] WITH(NOLOCK) WHERE ResourceGroupArtifactID IN (
SELECT ResourceGroupArtifactID FROM [EDDSDBO].[ServerResourceGroup] WITH(NOLOCK) WHERE ResourceServerArtifactID = @resourceServerArtifactId)
)
ORDER BY [ID] ASC
INSERT INTO EDDSDBO.{1}(ID)
SELECT TOP (@batchSize) ID
FROM EDDSDBO.{0} WITH(UPDLOCK,READPAST)
WHERE
[WorkspaceArtifactID] = @workspaceArtifactID
AND
[ExtractorSetArtifactID] = @extractorSetArtifactID
AND
[QueueStatus] = @notStartedQueueStatus
AND
[DocumentArtifactID] NOT IN
(
SELECT DISTINCT [DocumentArtifactID]
FROM EDDSDBO.{0}
WHERE [AgentID] IS NOT NULL
)
ORDER BY
[ID] ASC,
[ExtractorSetArtifactID] ASC,
[DocumentArtifactID] ASC
UPDATE S
SET AgentID = @agentID,
QueueStatus = @inProgressQueueStatus
FROM EDDSDBO.{1} B
INNER JOIN EDDSDBO.{0} S ON B.ID = S.ID
COMMIT
SELECT
S.ID [QueueID]
,S.[WorkspaceArtifactID]
,S.[QueueStatus]
,S.[AgentID]
,S.[ExtractorSetArtifactID]
,S.[DocumentArtifactID]
,S.[ExtractorProfileArtifactID]
,S.[SourceLongTextFieldArtifactID]
FROM EDDSDBO.{1} B
INNER JOIN EDDSDBO.{0} S ON B.ID = S.ID " , Constant.Tables.WorkerQueue, uniqueTableName);
var sqlParams = new List <SqlParameter>
{
new SqlParameter("@agentID", SqlDbType.Int)
{
Value = agentId
},
new SqlParameter("@batchSize", SqlDbType.Int)
{
Value = batchSize
},
new SqlParameter("@notStartedQueueStatus", SqlDbType.Int)
{
Value = Constant.QueueStatus.NotStarted
},
new SqlParameter("@inProgressQueueStatus", SqlDbType.Int)
{
Value = Constant.QueueStatus.InProgress
},
new SqlParameter("@resourceServerArtifactID", SqlDbType.Int)
{
Value = resourceServerArtifactId
}
};
return(eddsDbContext.ExecuteSqlStatementAsDataTable(sql, sqlParams));
}
public DataTable RetrieveNextBatchInManagerQueue(IDBContext eddsDbContext, Int32 batchSize, Int32 agentId)
{
var sql = String.Format(@"
-- Assigns a batch of rows to the Agent, respecting resource pool
UPDATE [EDDSDBO].[{0}]
SET [AgentID] = @agentID,
[QueueStatus] = @inProgressQueueStatus
FROM (SELECT TOP (@batchSize)
[ID],
[AgentID],
[QueueStatus],
[WorkspaceArtifactID]
FROM [EDDSDBO].[TextExtractor_ManagerQueue] WITH (UPDLOCK, READPAST)
WHERE [QueueStatus] = @notStartedQueueStatus
AND [WorkspaceArtifactID] IN (
SELECT [ArtifactID] AS 'WorkspacesInResourceGroup'
FROM [EDDSDBO].[ExtendedCase]
WHERE [ResourceGroupArtifactID] IN (
SELECT [ResourceGroupArtifactID] AS 'AgentsResourceGroup'
FROM [EDDSDBO].[ServerResourceGroup]
WHERE [ResourceServerArtifactID] = (
SELECT [ServerArtifactID]
FROM [EDDSDBO].[Agent]
WHERE ArtifactID = @agentID
)
)
)
ORDER BY [TimeStampUTC]
) innerTable
WHERE innerTable.[ID] = [EDDSDBO].[{0}].[ID]
SELECT TOP (@batchSize)
[ID],
[WorkspaceArtifactID],
[QueueStatus],
[SavedSearchArtifactID],
[ExtractorSetArtifactID],
[ExtractorProfileArtifactID],
[SourceLongTextFieldArtifactID]
FROM [EDDSDBO].[{0}] WITH (NOLOCK)
WHERE [AgentID] = @agentID
ORDER BY [TimeStampUTC]
" , Constant.Tables.ManagerQueue);
var sqlParams = new List <SqlParameter>
{
new SqlParameter("@agentID", SqlDbType.Int)
{
Value = agentId
},
new SqlParameter("@batchSize", SqlDbType.Int)
{
Value = batchSize
},
new SqlParameter("@notStartedQueueStatus", SqlDbType.Int)
{
Value = Constant.QueueStatus.NotStarted
},
new SqlParameter("@inProgressQueueStatus", SqlDbType.Int)
{
Value = Constant.QueueStatus.InProgress
}
};
return(eddsDbContext.ExecuteSqlStatementAsDataTable(sql, sqlParams));
}
public override void Execute()
{
int contador = 0;
//Get the current Agent artifactID
Int32 agentArtifactID = this.AgentID;
//Get a dbContext for the EDDS database
IDBContext eddsDBContext = this.Helper.GetDBContext(-1);
List <int> worksapcesID = new List <int>();
Comment comment = new Comment();
try
{
using (IRSAPIClient proxy =
Helper.GetServicesManager().CreateProxy <IRSAPIClient>(ExecutionIdentity.System))
{
RelativityAppCore.BLL.Service.RSAPIService.CommentRSAPIService commentRSAPIService = new CORE.BLL.Service.RSAPIService.CommentRSAPIService(proxy);
IDBContext DBContext = this.Helper.GetDBContext(-1);
DataRowCollection data = DBContext.ExecuteSqlStatementAsDataTable(Queries.GetWorkspacesWithApp).Rows;
RaiseMessage("Find for the workspaces with the application", 1);
foreach (var item in data[0].ItemArray)
{
worksapcesID.Add((int)item);
}
foreach (var item in worksapcesID)
{
proxy.APIOptions.WorkspaceID = item;
DTOs.Query <DTOs.RDO> query = new DTOs.Query <DTOs.RDO>();
DTOs.QueryResultSet <DTOs.RDO> results = new DTOs.QueryResultSet <DTOs.RDO>();
query.ArtifactTypeGuid = new Guid(comment.ARTIFACT_TYPE);
query.Fields = DTOs.FieldValue.AllFields;
// query.Condition = new BooleanCondition(new Guid(guidSet), BooleanConditionEnum.EqualTo, false);
try
{
results = proxy.Repositories.RDO.Query(query);
foreach (var c in results.Results)
{
RaiseMessage($"verifying if the comment: {c.Artifact.ArtifactID} already has the thumnails", 1);
DTOs.RDO commentDto = new DTOs.RDO(c.Artifact.ArtifactID);
commentDto.ArtifactTypeGuids.Add(new Guid(comment.ARTIFACT_TYPE));
commentDto.Fields = DTOs.FieldValue.AllFields;
commentDto.Fields.Add(new DTOs.FieldValue(new Guid(guidSet)));
commentDto = proxy.Repositories.RDO.ReadSingle(c.Artifact.ArtifactID);
//bool fieldValue = (bool)commentDto[new Guid(guidSet)].Value;
string image = (string)commentDto[new Guid(Image_guid_field)].Value;
if (!string.IsNullOrEmpty(image))
{
RaiseMessage($"Creating Thumbnails for the comment {c.Artifact.ArtifactID}", 1);
string thumbnail = getImage(c.Artifact.ArtifactID, this.Helper.GetDBContext(proxy.APIOptions.WorkspaceID));
commentDto.Fields.Add(new DTOs.FieldValue(new Guid(thumbnailsImage), thumbnail));
commentDto.Fields.Add(new DTOs.FieldValue(new Guid(guidSet), true));
proxy.Repositories.RDO.UpdateSingle(commentDto);
}
else
{
contador = contador + 1;
commentDto.Fields.Add(new DTOs.FieldValue(new Guid(guidSet), false));
proxy.Repositories.RDO.UpdateSingle(commentDto);
}
}
}
catch (Exception)
{
throw;
}
}
}
RaiseMessage($"There are {contador} comments without thumbnail", 1);
}
catch (System.Exception ex)
{
//Your Agent caught an exception
this.RaiseError(ex.Message, ex.Message);
}
}