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