Changeset 17586 for branches/2521_ProblemRefactoring/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/HiveStatistics

Timestamp:

06/04/20 15:55:13 (5 years ago)

Author:

mkommend

Message:

#2521: Merged trunk changes into problem refactoring branch.

Location:

branches/2521_ProblemRefactoring

Files:

• . (modified) (1 prop)
• HeuristicLab.Services.Hive.DataAccess (modified) (1 prop)
• HeuristicLab.Services.Hive.DataAccess/3.3/Daos/HiveStatistics/DimJobDao.cs (modified) (2 diffs)

branches/2521_ProblemRefactoring/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/HiveStatistics/DimJobDao.cs

@@ -44 +44 @@
     }
 
+    public void UpdateExistingDimJobs() {
+      DataContext.ExecuteCommand(UpdateExistingDimJobsQuery);
+    }
+
     #region Compiled queries
     private static readonly Func<DataContext, Guid, DimJob> GetByIdQuery =
@@ -51 +55 @@
          select dimJob).SingleOrDefault());
     #endregion
+
+    #region String queries
+    private const string UpdateExistingDimJobsQuery = @"
+UPDATE u
+SET
+  u.JobName = case when x.JobId is null then u.JobName else x.JobName end,
+  u.TotalTasks = x.TotalTasks,
+  u.CompletedTasks = x.CompletedTasks,
+  u.DateCompleted =
+    case when x.totaltasks = x.CompletedTasks
+      then (case when x.JobId is null and x.DateCompleted is null then GETDATE() else x.DateCompleted end)
+    else u.DateCompleted
+  end,
+  u.ProjectId = case when x.JobId is null then u.ProjectId else x.ProjectId end
+FROM [statistics].dimjob u
+JOIN (
+  SELECT
+    dj.JobId as DimJobId,
+    j.JobId as JobId,
+    j.Name as JobName,
+    COUNT(*) as TotalTasks,
+    SUM(
+    CASE
+    WHEN TaskState in ('Finished', 'Aborted', 'Failed') then 1
+    ELSE 0
+    END) as CompletedTasks,
+    MAX(EndTime) as DateCompleted,
+    dp.ProjectId as ProjectId
+  from [statistics].DimJob dj
+  join [statistics].FactTask ft on dj.JobId = ft.JobId
+  left join Job j on j.JobId = dj.JobId
+  left join [statistics].DimProject dp on j.ProjectId = dp.ProjectId 
+  where dj.DateCompleted is null and dp.DateExpired is null
+  group by dj.JobId, j.JobId, j.Name, dp.ProjectId 
+) as x on u.JobId = x.DimJobId";
+    #endregion
   }
 }