public static void CreatePopTable(Relativity.API.IDBContext workspaceDBContext, int jobArtifactID, List <int> searchArtifacts, bool imagesOnly)
{
string sql = String.Format(@"
IF OBJECT_ID('[REF_POP_{0}]') IS NULL
CREATE TABLE [REF_POP_{0}](
[FileID] [int] NOT NULL,
[Filename] [nvarchar](200) NOT NULL,
[Location] [nvarchar](2000) NOT NULL,
[Status] [int] NOT NULL);" , jobArtifactID.ToString());
workspaceDBContext.ExecuteNonQuerySQLStatement(sql);
foreach (int documentArtifactID in searchArtifacts)
{
if (imagesOnly)
{
sql = String.Format(@"
INSERT INTO [REF_POP_{0}]
SELECT F.[FileID]
, F.[Filename]
, F.[Location]
, 0
FROM [File] AS F
LEFT JOIN [REF_POP_{0}] AS REF
ON REF.[FileID] = F.[FileID]
WHERE F.[Type] IN (1,3)
AND F.[Filename] NOT LIKE '%.%'
AND REF.[FileID] IS NULL
AND F.[DocumentArtifactID] = {1}", jobArtifactID.ToString(), documentArtifactID.ToString());
}
else
{
sql = String.Format(@"
INSERT INTO [REF_POP_{0}]
SELECT F.[FileID]
, F.[Filename]
, F.[Location]
, 0
FROM [File] AS F
LEFT JOIN [REF_POP_{0}] AS REF
ON REF.[FileID] = F.[FileID]
WHERE F.[Filename] NOT LIKE '%.%'
AND REF.[FileID] IS NULL
AND F.[DocumentArtifactID] = {1}", jobArtifactID.ToString(), documentArtifactID.ToString());
}
workspaceDBContext.ExecuteNonQuerySQLStatement(sql);
}
}
public static void CleanQueueTable(Relativity.API.IDBContext eddsDBContext)
{
string sql = @"
DELETE FROM [EDDS].[eddsdbo].[ExtensionFixerQueue]
WHERE [Status] = 4";
eddsDBContext.ExecuteNonQuerySQLStatement(sql);
}
public static void UpdateJobStatus(Relativity.API.IDBContext eddsDBContext, Int32 agentArtifactID, Int32 workspaceArtifactID, Int32 jobArtifactID, Int32 statusCode)
{
string sql = String.Format(@"
UPDATE [EDDS].[eddsdbo].[ExtensionFixerQueue]
SET [Status] = {0}
WHERE [WorkspaceArtifactID] = {1}
AND [JobArtifactID] = {2}
AND [AgentArtifactID] = {3}", statusCode, workspaceArtifactID, jobArtifactID, agentArtifactID);
eddsDBContext.ExecuteNonQuerySQLStatement(sql);
}
public static void UpdateJobTable(Relativity.API.IDBContext workspaceDBContext, DataTable completedBatch, Int32 jobArtifactID)
{
foreach (DataRow row in completedBatch.Rows)
{
string sql = String.Format(@"
UPDATE [EDDSDBO].[REF_POP_{0}]
SET [Filename] = '{1}',
[Status] = 3
WHERE FileID = {2}", jobArtifactID.ToString(), row["Filename"].ToString(), row["FileID"].ToString());
workspaceDBContext.ExecuteNonQuerySQLStatement(sql);
}
}
public static void UpdateFileTable(Relativity.API.IDBContext workspaceDBContext, Int32 jobArtifactID)
{
string sql = String.Format(@"
UPDATE F
SET F.[Filename] = REF.[Filename]
FROM [EDDSDBO].[File] AS F
INNER JOIN [EDDSDBO].[REF_POP_{0}] AS REF
ON F.[FileID] = REF.[FileID]
WHERE [REF].[Status] = 3;
UPDATE [EDDSDBO].[REF_POP_{0}]
SET [Status] = 4
WHERE [Status] = 3;
DELETE FROM [EDDSDBO].[REF_POP_{0}]
WHERE [Status] = 4;", jobArtifactID);
workspaceDBContext.ExecuteNonQuerySQLStatement(sql);
}
public static Dictionary <string, int> GetJob(Relativity.API.IDBContext eddsDBContext, Int32 agentArtifactID)
{
Int32 workspaceArtifactID;
Int32 jobArtifactID;
Int32 sourceSearchArtifactID;
if (DoesAgentHaveJob(eddsDBContext, agentArtifactID))
{
string sql = String.Format(@"
SELECT TOP 1 [JobArtifactID], [WorkspaceArtifactID], [SourceSearchArtifactID]
FROM [EDDS].[eddsdbo].[ExtensionFixerQueue]
WHERE AgentArtifactID = {0}
AND [Status] NOT IN (4, 5)", agentArtifactID);
DataTable jobInfo = eddsDBContext.ExecuteSqlStatementAsDataTable(sql);
DataRow row = jobInfo.Rows[0];
workspaceArtifactID = Int32.Parse(row["WorkspaceArtifactID"].ToString());
jobArtifactID = Int32.Parse(row["JobArtifactID"].ToString());
sourceSearchArtifactID = Int32.Parse(row["SourceSearchArtifactID"].ToString());
Dictionary <string, int> outputDict = new Dictionary <string, int>
{
{ "WorkspaceArtifactID", workspaceArtifactID },
{ "JobArtifactID", jobArtifactID },
{ "SourceSearchArtifactID", sourceSearchArtifactID }
};
return(outputDict);
}
else if (JobQueueDepth(eddsDBContext) > 0)
{
string sql = @"
SELECT TOP 1 [JobArtifactID], [WorkspaceArtifactID], [SourceSearchArtifactID]
FROM [EDDS].[eddsdbo].[ExtensionFixerQueue]
WHERE [Status] = 0";
DataTable jobInfo = eddsDBContext.ExecuteSqlStatementAsDataTable(sql);
DataRow row = jobInfo.Rows[0];
workspaceArtifactID = Int32.Parse(row["WorkspaceArtifactID"].ToString());
jobArtifactID = Int32.Parse(row["JobArtifactID"].ToString());
sourceSearchArtifactID = Int32.Parse(row["SourceSearchArtifactID"].ToString());
sql = String.Format(@"
UPDATE [EDDS].[eddsdbo].[ExtensionFixerQueue]
SET [Status] = 2, [AgentArtifactID] = {0}
WHERE [WorkspaceArtifactID] = {1}
AND [JobArtifactID] = {2}", agentArtifactID, workspaceArtifactID, jobArtifactID);
eddsDBContext.ExecuteNonQuerySQLStatement(sql);
Dictionary <string, int> outputDict = new Dictionary <string, int>
{
{ "WorkspaceArtifactID", workspaceArtifactID },
{ "JobArtifactID", jobArtifactID },
{ "SourceSearchArtifactID", sourceSearchArtifactID }
};
return(outputDict);
}
else
{
return(null);
}
}