Radarr/src/NzbDrone.Core/MovieStats/MovieStatisticsRepository.cs

89 lines
3.3 KiB
C#

using System.Collections.Generic;
using System.Linq;
using Dapper;
using NzbDrone.Core.Datastore;
using NzbDrone.Core.MediaFiles;
using NzbDrone.Core.Movies;
namespace NzbDrone.Core.MovieStats
{
public interface IMovieStatisticsRepository
{
List<MovieStatistics> MovieStatistics();
List<MovieStatistics> MovieStatistics(int movieId);
}
public class MovieStatisticsRepository : IMovieStatisticsRepository
{
private const string _selectMoviesTemplate = "SELECT /**select**/ FROM \"Movies\" /**join**/ /**innerjoin**/ /**leftjoin**/ /**where**/ /**groupby**/ /**having**/ /**orderby**/";
private const string _selectMovieFilesTemplate = "SELECT /**select**/ FROM \"MovieFiles\" /**join**/ /**innerjoin**/ /**leftjoin**/ /**where**/ /**groupby**/ /**having**/ /**orderby**/";
private readonly IMainDatabase _database;
public MovieStatisticsRepository(IMainDatabase database)
{
_database = database;
}
public List<MovieStatistics> MovieStatistics()
{
return MapResults(Query(MoviesBuilder(), _selectMoviesTemplate),
Query(MovieFilesBuilder(), _selectMovieFilesTemplate));
}
public List<MovieStatistics> MovieStatistics(int movieId)
{
return MapResults(Query(MoviesBuilder().Where<Movie>(x => x.Id == movieId), _selectMoviesTemplate),
Query(MovieFilesBuilder().Where<MovieFile>(x => x.MovieId == movieId), _selectMovieFilesTemplate));
}
private List<MovieStatistics> MapResults(List<MovieStatistics> moviesResult, List<MovieStatistics> filesResult)
{
moviesResult.ForEach(e =>
{
var file = filesResult.SingleOrDefault(f => f.MovieId == e.MovieId);
e.SizeOnDisk = file?.SizeOnDisk ?? 0;
e.ReleaseGroupsString = file?.ReleaseGroupsString;
});
return moviesResult;
}
private List<MovieStatistics> Query(SqlBuilder builder, string template)
{
var sql = builder.AddTemplate(template).LogQuery();
using var conn = _database.OpenConnection();
return conn.Query<MovieStatistics>(sql.RawSql, sql.Parameters).ToList();
}
private SqlBuilder MoviesBuilder()
{
return new SqlBuilder(_database.DatabaseType)
.Select(@"""Movies"".""Id"" AS MovieId,
SUM(CASE WHEN ""MovieFileId"" > 0 THEN 1 ELSE 0 END) AS MovieFileCount")
.GroupBy<Movie>(x => x.Id);
}
private SqlBuilder MovieFilesBuilder()
{
if (_database.DatabaseType == DatabaseType.SQLite)
{
return new SqlBuilder(_database.DatabaseType)
.Select(@"""MovieId"",
SUM(COALESCE(""Size"", 0)) AS SizeOnDisk,
GROUP_CONCAT(""ReleaseGroup"", '|') AS ReleaseGroupsString")
.GroupBy<MovieFile>(x => x.MovieId);
}
return new SqlBuilder(_database.DatabaseType)
.Select(@"""MovieId"",
SUM(COALESCE(""Size"", 0)) AS SizeOnDisk,
string_agg(""ReleaseGroup"", '|') AS ReleaseGroupsString")
.GroupBy<MovieFile>(x => x.MovieId);
}
}
}