private List <CreatorOfVideoFullDto> GetVideosCharacterIsInWithoutActors(long characterId, SqliteConnection db, string characterIdColumn)
{
var firstQuery = $"SELECT {SELECT_BASE_COLUMNS_STRING}, c.creator_id, 'Actor' as role FROM video v, actor_for_video_character c WHERE c.{characterIdColumn} = {characterId} AND c.video_id = v.video_id AND v.deleted = false";
var secondQuery = "";
if (characterIdColumn == "creator_id")
{
secondQuery = $"UNION SELECT {SELECT_BASE_COLUMNS_STRING}, vc.creator_id, vc.role FROM video v, video_creator vc WHERE v.deleted = false AND vc.video_id = v.video_id AND vc.creator_id = {characterId}";
}
var command = new SqliteCommand($"{firstQuery} {secondQuery}", db);
var query = command.ExecuteReader();
var list = new List <CreatorOfVideoFullDto>();
var characterIds = new Dictionary <long, CreatorOfVideoFullDto>();
while (query.Read())
{
var role = query.GetString(22);
var dto = new CreatorOfVideoFullDto(ParseVideo(query), QueryUtil.GetNullableId(query, 21), "", role);
var key = dto.Video.VideoId;
if (characterIds.ContainsKey(key))
{
var existingDto = characterIds[key];
existingDto.Role += $", {dto.Role}";
}
else
{
list.Add(dto);
characterIds[key] = dto;
}
}
return(list);
}
private Video ParseVideo(SqliteDataReader query)
{
return(new Video(
query.GetInt64(0), query.GetString(1), query.GetInt32(2), QueryUtil.GetDateTime(query, 3), QueryUtil.GetInt32(query, 4),
query.GetDouble(5), query.GetDouble(6), query.GetString(7), query.GetString(8), query.GetString(9),
query.GetString(10), QueryUtil.GetNullableId(query, 11), (VideoWatchStatus)query.GetInt16(12), QueryUtil.GetInt32(query, 13),
QueryUtil.GetNullableId(query, 14), query.GetInt64(15), QueryUtil.GetDateTime(query, 16), QueryUtil.GetDateTime(query, 17),
QueryUtil.GetNullableId(query, 18), query.GetBoolean(19), query.GetString(20)
));
}
public async Task <SeriesBasicDetails> GetSeriesBasicDetails(long seriesId)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var command = new SqliteCommand($"SELECT series_id, name, publisher_id FROM series WHERE series_id = {seriesId} AND deleted = false", db);
var query = command.ExecuteReader();
query.Read();
return(new SeriesBasicDetails(query.GetInt64(0), query.GetString(1), QueryUtil.GetNullableId(query, 2)));
}
}
private SeriesSequence ParseSeriesSequence(SqliteDataReader query)
{
return(new SeriesSequence(query.GetInt64(0), query.GetString(1), query.GetString(2), QueryUtil.GetNullableId(query, 3), query.GetBoolean(4), query.GetBoolean(5), query.GetInt32(6), query.GetInt64(7), query.GetString(8)));
}
private Character ParseCharacter(SqliteDataReader query)
{
return(new Character(query.GetInt64(0), query.GetString(1), query.GetString(2), QueryUtil.GetDateTime(query, 3), QueryUtil.GetDateTime(query, 4), QueryUtil.GetDateTime(query, 5), query.GetDouble(6), query.GetInt64(7), QueryUtil.GetNullableId(query, 8), QueryUtil.GetNullableId(query, 9), query.GetBoolean(10), query.GetBoolean(11), query.GetString(12)));
}
private Series ParseSeries(SqliteDataReader query)
{
return(new Series(query.GetInt64(0), query.GetString(1), query.GetString(2), QueryUtil.GetNullableId(query, 3), query.GetString(4), QueryUtil.GetNullableId(query, 5), query.GetInt64(6), QueryUtil.GetNullableId(query, 7), query.GetBoolean(8), query.GetString(9), query.GetBoolean(10)));
}
public async Task <List <SeriesBasicDetails> > GetAllActiveSeriesInLibrary(long libraryId)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var command = new SqliteCommand($"SELECT series_id, name, publisher_id FROM series WHERE library_id = {libraryId} AND deleted = false", db);
var query = command.ExecuteReader();
var items = new List <SeriesBasicDetails>();
while (query.Read())
{
items.Add(new SeriesBasicDetails(query.GetInt64(0), query.GetString(1), QueryUtil.GetNullableId(query, 2)));
}
return(items);
}
}
public List <Location> GetMediaFileLocations(MediaFile file)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var command = new SqliteCommand("SELECT l.location_id, l.name, l.description, l.cover_file_id FROM location_media_file m, location l WHERE m.media_id = @FileId AND l.location_id = m.location_id AND l.deleted = false", db);
command.Parameters.AddWithValue("@FileId", file.MediaId);
var reader = command.ExecuteReader();
var locations = new List <Location>();
while (reader.Read())
{
locations.Add(new Location(reader.GetInt64(0), reader.GetString(1), reader.GetString(2), QueryUtil.GetNullableId(reader, 3)));
}
return(locations);
}
}
private VideoSequence ParseVideoSequence(SqliteDataReader query)
{
return(new VideoSequence(query.GetInt64(0), query.GetString(1), query.GetString(2), QueryUtil.GetNullableId(query, 3)));
}
private Calendar ParseCalendar(SqliteDataReader query)
{
return(new Calendar(query.GetInt64(0), query.GetString(1), query.GetString(2), query.GetString(3), QueryUtil.GetNullableId(query, 4), query.GetBoolean(5), query.GetString(6)));
}
private Publisher ParsePublisher(SqliteDataReader query)
{
return(new Publisher(query.GetInt64(0), query.GetString(1), query.GetString(2), QueryUtil.GetNullableId(query, 3), query.GetString(4), query.GetInt64(5), query.GetBoolean(6), query.GetString(7)));
}
private VideoCharacterActorExportDto ParseVideoCharacterActor(SqliteDataReader query)
{
return(new VideoCharacterActorExportDto(query.GetString(0), query.GetString(1), QueryUtil.GetNullableId(query, 2)));
}
private PlaylistDto ParsePlaylist(SqliteDataReader query)
{
return(new PlaylistDto(query.GetInt64(0), query.GetString(1), query.GetString(2), QueryUtil.GetNullableId(query, 3), query.GetBoolean(6), QueryUtil.GetNullableId(query, 4), query.GetDateTime(5), query.GetString(7)));
}
private Location ParseLocation(SqliteDataReader reader)
{
return(new Location(reader.GetInt64(0), reader.GetString(1), reader.GetString(2), reader.GetInt64(3), QueryUtil.GetNullableId(reader, 4), QueryUtil.GetNullableId(reader, 5), reader.GetBoolean(6), reader.GetString(7)));
}
private Library ParseLibrary(SqliteDataReader query)
{
return(new Library(query.GetInt64(0), query.GetString(1), QueryUtil.GetNullableId(query, 2), query.GetBoolean(3), query.GetString(4)));
}