• Home
  • Features
  • Pricing
  • Docs
  • Announcements
  • Sign In

HicServices / RDMP / 12051172428

27 Nov 2024 01:13PM UTC coverage: 57.448% (-0.001%) from 57.449%
12051172428

push

github

JFriel
codeql updates

11297 of 21215 branches covered (53.25%)

Branch coverage included in aggregate %.

3 of 11 new or added lines in 5 files covered. (27.27%)

1 existing line in 1 file now uncovered.

32146 of 54407 relevant lines covered (59.08%)

17100.4 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

16.02
/Rdmp.Core/Curation/Data/Overview/OverviewModel.cs
1
// Copyright (c) The University of Dundee 2024-2024
2
// This file is part of the Research Data Management Platform (RDMP).
3
// RDMP is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
4
// RDMP is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
5
// You should have received a copy of the GNU General Public License along with RDMP. If not, see <https://www.gnu.org/licenses/>.
6

7
using NPOI.SS.Formula.Functions;
8
using Rdmp.Core.CommandExecution;
9
using Rdmp.Core.DataExport.Data;
10
using Rdmp.Core.DataLoad.Triggers;
11
using Rdmp.Core.DataViewing;
12
using Rdmp.Core.Logging;
13
using Rdmp.Core.QueryBuilding;
14
using Rdmp.Core.Repositories;
15
using Rdmp.Core.ReusableLibraryCode.DataAccess;
16
using System;
17
using System.Collections;
18
using System.Collections.Generic;
19
using System.Data;
20
using System.Linq;
21

22
namespace Rdmp.Core.Curation.Data.Overview;
23

24
/// <summary>
25
/// Used to populate information about a catalogue for use in the overview UI
26
/// </summary>
27
public class OverviewModel
28
{
29

30
    private readonly ICatalogue _catalogue;
31
    private readonly IBasicActivateItems _activator;
32

33
    private DataTable _dataLoads;
34

35
    private int _numberOfPeople;
36
    private int _numberOfRecords;
37

38
    public OverviewModel(IBasicActivateItems activator, ICatalogue catalogue)
2✔
39
    {
40
        _activator = activator;
2✔
41
        _catalogue = catalogue;
2✔
42
        if (catalogue != null)
2✔
43
        {
44
            Regen("");
2✔
45
        }
46
    }
2✔
47

48
    public void Regen(string whereClause)
49
    {
50
        DataTable dt = new();
2✔
51
        bool hasExtractionIdentifier = true;
2✔
52
        var column = _catalogue.CatalogueItems.Where(ci => ci.ExtractionInformation.IsExtractionIdentifier).FirstOrDefault();
2✔
53
        if (column is null)
2✔
54
        {
55
            column = _catalogue.CatalogueItems.FirstOrDefault();
2✔
56
            hasExtractionIdentifier = false;
2✔
57
        }
58
        if (column is null) return;
4!
59
        var discoveredColumn = column.ColumnInfo.Discover(DataAccessContext.InternalDataProcessing);
×
60
        var server = discoveredColumn.Table.Database.Server;
×
61
        using var con = server.GetConnection();
×
62
        con.Open();
×
63
        string populatedWhere = !string.IsNullOrWhiteSpace(whereClause) ? $"WHERE {whereClause}" : "";
×
64
        var sql = $"SELECT {column.ColumnInfo.GetRuntimeName()} FROM {discoveredColumn.Table.GetRuntimeName()} {populatedWhere}";
×
65
        using var cmd = server.GetCommand(sql, con);
×
66
        cmd.CommandTimeout = 30000;
×
67
        using var da = server.GetDataAdapter(cmd);
×
68
        dt.BeginLoadData();
×
69
        da.Fill(dt);
×
70
        dt.EndLoadData();
×
71
        con.Dispose();
×
72
        _numberOfRecords = dt.Rows.Count;
×
73
        _numberOfPeople = hasExtractionIdentifier ? dt.DefaultView.ToTable(true, column.ColumnInfo.GetRuntimeName()).Rows.Count : 0;
×
74
        GetDataLoads();
×
NEW
75
        dt.Dispose();
×
UNCOV
76
    }
×
77

78
    public int GetNumberOfRecords()
79
    {
80
        return _numberOfRecords;
×
81
    }
82

83
    public int GetNumberOfPeople()
84
    {
85
        return _numberOfPeople;
×
86
    }
87

88
    public Tuple<DateTime, DateTime> GetStartEndDates(ColumnInfo dateColumn, string whereClause)
89
    {
90
        DataTable dt = new();
×
91

92
        var discoveredColumn = _catalogue.CatalogueItems.First().ColumnInfo.Discover(DataAccessContext.InternalDataProcessing);
×
93
        var server = discoveredColumn.Table.Database.Server;
×
94
        var populatedWhereClause = !string.IsNullOrWhiteSpace(whereClause) ? $"WHERE {whereClause}" : "";
×
95
        using var con = server.GetConnection();
×
96
        con.Open();
×
97
        if (server.DatabaseType == FAnsi.DatabaseType.MicrosoftSQLServer)
×
98
        {
99
            var sql = $@"
×
100
        select min({dateColumn.GetRuntimeName()}) as min, max({dateColumn.GetRuntimeName()}) as max
×
101
        from
×
102
        (select {dateColumn.GetRuntimeName()},
×
103
        count(1) over (partition by year({dateColumn.GetRuntimeName()})) as occurs 
×
104
        from {discoveredColumn.Table.GetRuntimeName()} {populatedWhereClause}) as t
×
105
        where occurs >1
×
106
        ";
×
107

108
            using var cmd = server.GetCommand(sql, con);
×
109
            cmd.CommandTimeout = 30000;
×
110
            using var da = server.GetDataAdapter(cmd);
×
111
            dt.BeginLoadData();
×
112
            da.Fill(dt);
×
113
            dt.EndLoadData();
×
114
        }
115
        else
116
        {
117
            var repo = new MemoryCatalogueRepository();
×
118
            var qb = new QueryBuilder(null, null);
×
119
            qb.AddColumn(new ColumnInfoToIColumn(repo, dateColumn));
×
120
            qb.AddCustomLine($"{dateColumn.Name} IS NOT NULL", FAnsi.Discovery.QuerySyntax.QueryComponent.WHERE);
×
121
            var cmd = server.GetCommand(qb.SQL, con);
×
122
            using var da = server.GetDataAdapter(cmd);
×
123
            dt.BeginLoadData();
×
124
            da.Fill(dt);
×
125
            var latest = dt.AsEnumerable()
×
126
               .Max(r => r.Field<DateTime>(dateColumn.Name));
×
127
            var earliest = dt.AsEnumerable()
×
128
              .Min(r => r.Field<DateTime>(dateColumn.Name));
×
129
            dt = new();
×
130
            dt.Rows.Add([earliest, latest]);
×
131
        }
132
        con.Dispose();
×
133
        return new Tuple<DateTime, DateTime>(DateTime.Parse(dt.Rows[0].ItemArray[0].ToString()), DateTime.Parse(dt.Rows[0].ItemArray[1].ToString()));
×
134
    }
×
135

136

137
    public static DataTable GetCountsByDatePeriod(ColumnInfo dateColumn, string datePeriod, string optionalWhere = "")
138
    {
139
        DataTable dt = new();
×
140
        if (!(new[] { "Day", "Month", "Year" }).Contains(datePeriod))
×
141
        {
142
            throw new Exception("Invalid Date period");
×
143
        }
144
        var discoveredColumn = dateColumn.Discover(DataAccessContext.InternalDataProcessing);
×
145
        var server = discoveredColumn.Table.Database.Server;
×
146
        using var con = server.GetConnection();
×
147
        con.Open();
×
148
        var dateString = "yyyy-MM";
×
149
        switch (datePeriod)
150
        {
151
            case "Day":
152
                dateString = "yyyy-MM-dd";
×
153
                break;
×
154
            case "Month":
155
                dateString = "yyyy-MM";
×
156
                break;
×
157
            case "Year":
158
                dateString = "yyyy";
×
159
                break;
160
        }
161
        if (server.DatabaseType == FAnsi.DatabaseType.MicrosoftSQLServer)
×
162
        {
163
            var sql = @$"
×
164
        SELECT format({dateColumn.GetRuntimeName()}, '{dateString}') as YearMonth, count(*) as '# Records'
×
165
        FROM {discoveredColumn.Table.GetRuntimeName()}
×
166
        WHERE {dateColumn.GetRuntimeName()} IS NOT NULL
×
167
        {(optionalWhere != "" ? "AND" : "")} {optionalWhere.Replace('"', '\'')}
×
168
        GROUP BY format({dateColumn.GetRuntimeName()}, '{dateString}')
×
169
        ORDER BY 1
×
170
        ";
×
171

172
            using var cmd = server.GetCommand(sql, con);
×
173
            cmd.CommandTimeout = 30000;
×
174
            using var da = server.GetDataAdapter(cmd);
×
175
            dt.BeginLoadData();
×
176
            da.Fill(dt);
×
177
            dt.EndLoadData();
×
178
        }
179
        else
180
        {
181
            var repo = new MemoryCatalogueRepository();
×
182
            var qb = new QueryBuilder(null, null);
×
183
            qb.AddColumn(new ColumnInfoToIColumn(repo, dateColumn));
×
184
            qb.AddCustomLine($"{dateColumn.Name} IS NOT NULL", FAnsi.Discovery.QuerySyntax.QueryComponent.WHERE);
×
185
            var cmd = server.GetCommand(qb.SQL, con);
×
186
            using var da = server.GetDataAdapter(cmd);
×
187
            dt.BeginLoadData();
×
188
            da.Fill(dt);
×
189
            Dictionary<string, int> counts = [];
×
NEW
190
            foreach (var key in dt.AsEnumerable().Select(row => DateTime.Parse(row.ItemArray[0].ToString()).ToString(dateString)))
×
191
            {
192
                counts[key]++;
×
193
            }
194
            dt = new DataTable();
×
195
            foreach (var item in counts)
×
196
            {
197
                DataRow dr = dt.NewRow();
×
198
                dr["YearMonth"] = item.Key;
×
199
                dr["# Records"] = item.Value;
×
200
                dt.Rows.Add(dr);
×
201
            }
202
            dt.EndLoadData();
×
203

204
        }
205
        con.Dispose();
×
206
        return dt;
×
207
    }
×
208

209
    private void GetDataLoads()
210
    {
211
        _dataLoads = new();
2✔
212
        var repo = new MemoryCatalogueRepository();
2✔
213
        var qb = new QueryBuilder(null, null);
2✔
214
        var columnInfo = _catalogue.CatalogueItems.Where(c => c.Name == SpecialFieldNames.DataLoadRunID).Select(c => c.ColumnInfo).FirstOrDefault();
2✔
215
        if (columnInfo != null)
2!
216
        {
217
            qb.AddColumn(new ColumnInfoToIColumn(repo, columnInfo));
×
218
            qb.AddCustomLine($"{columnInfo.Name} IS NOT NULL", FAnsi.Discovery.QuerySyntax.QueryComponent.WHERE);
×
219
            var sql = qb.SQL;
×
220
            var server = columnInfo.Discover(DataAccessContext.InternalDataProcessing).Table.Database.Server;
×
221
            using var con = server.GetConnection();
×
222
            con.Open();
×
223

224
            using var cmd = server.GetCommand(sql, con);
×
225
            cmd.CommandTimeout = 30000;
×
226
            using var da = server.GetDataAdapter(cmd);
×
227
            _dataLoads.BeginLoadData();
×
228
            da.Fill(_dataLoads);
×
229
            _dataLoads.EndLoadData();
×
230
        }
231

232
    }
2✔
233

234
    public DataTable GetMostRecentDataLoad()
235
    {
236
        if (_dataLoads == null) GetDataLoads();
6✔
237
        if (_dataLoads.Rows.Count == 0) return null;
8!
238
        var maxDataLoadId = _dataLoads.AsEnumerable().Select(r => int.Parse(r[0].ToString())).Distinct().Max();
×
239
        var loggingServers = _activator.RepositoryLocator.CatalogueRepository.GetAllObjectsWhere<ExternalDatabaseServer>("CreatedByAssembly", "Rdmp.Core/Databases.LoggingDatabase");
×
240
        var columnInfo = _catalogue.CatalogueItems.Where(c => c.Name == SpecialFieldNames.DataLoadRunID).Select(c => c.ColumnInfo).First();
×
241
        var server = columnInfo.Discover(DataAccessContext.InternalDataProcessing).Table.Database.Server;
×
242

243
        DataTable dt = new();
×
244
        foreach (var loggingServer in loggingServers)
×
245
        {
246
            var logCollection = new ViewLogsCollection(loggingServer, new LogViewerFilter(LoggingTables.DataLoadRun));
×
247
            var dataLoadRunSql = $"{logCollection.GetSql()} WHERE ID={maxDataLoadId}";
×
248
            var logServer = loggingServer.Discover(DataAccessContext.InternalDataProcessing).Server;
×
249
            using var loggingCon = logServer.GetConnection();
×
250
            loggingCon.Open();
×
251
            using var loggingCmd = logServer.GetCommand(dataLoadRunSql, loggingCon);
×
252
            loggingCmd.CommandTimeout = 30000;
×
253
            using var loggingDa = server.GetDataAdapter(loggingCmd);
×
254
            dt.BeginLoadData();
×
255
            loggingDa.Fill(dt);
×
256
            dt.EndLoadData();
×
257
            loggingCon.Dispose();
×
258
            if (dt.Rows.Count > 0)
×
259
            {
260
                break;
×
261
            }
262
        }
263
        return dt;
×
264
    }
265

266
    public List<CumulativeExtractionResults> GetExtractions()
267
    {
268
        var datasets = _activator.RepositoryLocator.DataExportRepository.GetAllObjectsWhere<ExtractableDataSet>("Catalogue_ID", _catalogue.ID).Select(d => d.ID);
4✔
269
        var results = _activator.RepositoryLocator.DataExportRepository.GetAllObjects<CumulativeExtractionResults>().Where(result => datasets.Contains(result.ExtractableDataSet_ID)).ToList();
16✔
270
        return results;
4✔
271

272
    }
273

274
}
STATUS · Troubleshooting · Open an Issue · Sales · Support · CAREERS · ENTERPRISE · START FREE · SCHEDULE DEMO
ANNOUNCEMENTS · TWITTER · TOS & SLA · Supported CI Services · What's a CI service? · Automated Testing

© 2025 Coveralls, Inc