private string[] GetFinalColumnsToSelect(InternalItemsQuery query, string[] startColumns, IDbCommand cmd) { var list = startColumns.ToList(); foreach (var field in allFields) { if (!query.HasField(field)) { foreach (var fieldToRemove in GetColumnNamesFromField(field).ToList()) { list.Remove(fieldToRemove); } } } if (!query.DtoOptions.EnableImages) { list.Remove("Images"); } if (EnableJoinUserData(query)) { list.Add("UserDataDb.UserData.UserId"); list.Add("UserDataDb.UserData.lastPlayedDate"); list.Add("UserDataDb.UserData.playbackPositionTicks"); list.Add("UserDataDb.UserData.playcount"); list.Add("UserDataDb.UserData.isFavorite"); list.Add("UserDataDb.UserData.played"); list.Add("UserDataDb.UserData.rating"); } if (query.SimilarTo != null) { var item = query.SimilarTo; var builder = new StringBuilder(); builder.Append("("); builder.Append("((OfficialRating=@ItemOfficialRating) * 10)"); //builder.Append("+ ((ProductionYear=@ItemProductionYear) * 10)"); builder.Append("+(Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 10 Then 2 Else 0 End )"); builder.Append("+(Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 5 Then 2 Else 0 End )"); //// genres builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type=2 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=2)) * 10)"); //// tags builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type=4 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=4)) * 10)"); builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type=5 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=5)) * 10)"); builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type=3 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=3)) * 3)"); //builder.Append("+ ((Select count(Name) from People where ItemId=Guid and Name in (select Name from People where ItemId=@SimilarItemId)) * 3)"); ////builder.Append("(select group_concat((Select Name from People where ItemId=Guid and Name in (Select Name from People where ItemId=@SimilarItemId)), '|'))"); builder.Append(") as SimilarityScore"); list.Add(builder.ToString()); cmd.Parameters.Add(cmd, "@ItemOfficialRating", DbType.String).Value = item.OfficialRating; cmd.Parameters.Add(cmd, "@ItemProductionYear", DbType.Int32).Value = item.ProductionYear ?? 0; cmd.Parameters.Add(cmd, "@SimilarItemId", DbType.Guid).Value = item.Id; var excludeIds = query.ExcludeItemIds.ToList(); excludeIds.Add(item.Id.ToString("N")); query.ExcludeItemIds = excludeIds.ToArray(); query.ExcludeProviderIds = item.ProviderIds; } return list.ToArray(); }
private BaseItem GetItem(IDataReader reader, InternalItemsQuery query) { var typeString = reader.GetString(0); var type = _typeMapper.GetType(typeString); if (type == null) { //Logger.Debug("Unknown type {0}", typeString); return null; } BaseItem item = null; if (TypeRequiresDeserialization(type)) { using (var stream = reader.GetMemoryStream(1, _memoryStreamProvider)) { try { item = _jsonSerializer.DeserializeFromStream(stream, type) as BaseItem; } catch (SerializationException ex) { Logger.ErrorException("Error deserializing item", ex); } } } if (item == null) { try { item = Activator.CreateInstance(type) as BaseItem; } catch { } } if (item == null) { return null; } if (!reader.IsDBNull(2)) { var hasStartDate = item as IHasStartDate; if (hasStartDate != null) { hasStartDate.StartDate = reader.GetDateTime(2).ToUniversalTime(); } } if (!reader.IsDBNull(3)) { item.EndDate = reader.GetDateTime(3).ToUniversalTime(); } if (!reader.IsDBNull(4)) { item.IsOffline = reader.GetBoolean(4); } if (!reader.IsDBNull(5)) { item.ChannelId = reader.GetString(5); } var hasProgramAttributes = item as IHasProgramAttributes; if (hasProgramAttributes != null) { if (!reader.IsDBNull(6)) { hasProgramAttributes.IsMovie = reader.GetBoolean(6); } if (!reader.IsDBNull(7)) { hasProgramAttributes.IsSports = reader.GetBoolean(7); } if (!reader.IsDBNull(8)) { hasProgramAttributes.IsKids = reader.GetBoolean(8); } if (!reader.IsDBNull(9)) { hasProgramAttributes.IsSeries = reader.GetBoolean(9); } if (!reader.IsDBNull(10)) { hasProgramAttributes.IsLive = reader.GetBoolean(10); } if (!reader.IsDBNull(11)) { hasProgramAttributes.IsNews = reader.GetBoolean(11); } if (!reader.IsDBNull(12)) { hasProgramAttributes.IsPremiere = reader.GetBoolean(12); } if (!reader.IsDBNull(13)) { hasProgramAttributes.EpisodeTitle = reader.GetString(13); } if (!reader.IsDBNull(14)) { hasProgramAttributes.IsRepeat = reader.GetBoolean(14); } } var index = 15; if (!reader.IsDBNull(index)) { item.CommunityRating = reader.GetFloat(index); } index++; if (query.HasField(ItemFields.CustomRating)) { if (!reader.IsDBNull(index)) { item.CustomRating = reader.GetString(index); } index++; } if (!reader.IsDBNull(index)) { item.IndexNumber = reader.GetInt32(index); } index++; if (query.HasField(ItemFields.Settings)) { if (!reader.IsDBNull(index)) { item.IsLocked = reader.GetBoolean(index); } index++; if (!reader.IsDBNull(index)) { item.PreferredMetadataLanguage = reader.GetString(index); } index++; if (!reader.IsDBNull(index)) { item.PreferredMetadataCountryCode = reader.GetString(index); } index++; } if (!reader.IsDBNull(index)) { item.IsHD = reader.GetBoolean(index); } index++; if (!reader.IsDBNull(index)) { item.ExternalEtag = reader.GetString(index); } index++; if (!reader.IsDBNull(index)) { item.DateLastRefreshed = reader.GetDateTime(index).ToUniversalTime(); } index++; if (!reader.IsDBNull(index)) { item.Name = reader.GetString(index); } index++; if (!reader.IsDBNull(index)) { item.Path = reader.GetString(index); } index++; if (!reader.IsDBNull(index)) { item.PremiereDate = reader.GetDateTime(index).ToUniversalTime(); } index++; if (query.HasField(ItemFields.Overview)) { if (!reader.IsDBNull(index)) { item.Overview = reader.GetString(index); } index++; } if (!reader.IsDBNull(index)) { item.ParentIndexNumber = reader.GetInt32(index); } index++; if (!reader.IsDBNull(index)) { item.ProductionYear = reader.GetInt32(index); } index++; if (!reader.IsDBNull(index)) { item.OfficialRating = reader.GetString(index); } index++; if (query.HasField(ItemFields.OfficialRatingDescription)) { if (!reader.IsDBNull(index)) { item.OfficialRatingDescription = reader.GetString(index); } index++; } if (query.HasField(ItemFields.HomePageUrl)) { if (!reader.IsDBNull(index)) { item.HomePageUrl = reader.GetString(index); } index++; } if (query.HasField(ItemFields.DisplayMediaType)) { if (!reader.IsDBNull(index)) { item.DisplayMediaType = reader.GetString(index); } index++; } if (query.HasField(ItemFields.SortName)) { if (!reader.IsDBNull(index)) { item.ForcedSortName = reader.GetString(index); } index++; } if (!reader.IsDBNull(index)) { item.RunTimeTicks = reader.GetInt64(index); } index++; if (query.HasField(ItemFields.VoteCount)) { if (!reader.IsDBNull(index)) { item.VoteCount = reader.GetInt32(index); } index++; } if (query.HasField(ItemFields.DateCreated)) { if (!reader.IsDBNull(index)) { item.DateCreated = reader.GetDateTime(index).ToUniversalTime(); } index++; } if (!reader.IsDBNull(index)) { item.DateModified = reader.GetDateTime(index).ToUniversalTime(); } index++; item.Id = reader.GetGuid(index); index++; if (query.HasField(ItemFields.Genres)) { if (!reader.IsDBNull(index)) { item.Genres = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).ToList(); } index++; } if (!reader.IsDBNull(index)) { item.ParentId = reader.GetGuid(index); } index++; if (!reader.IsDBNull(index)) { item.Audio = (ProgramAudio)Enum.Parse(typeof(ProgramAudio), reader.GetString(index), true); } index++; // TODO: Even if not needed by apps, the server needs it internally // But get this excluded from contexts where it is not needed if (!reader.IsDBNull(index)) { item.ServiceName = reader.GetString(index); } index++; if (!reader.IsDBNull(index)) { item.IsInMixedFolder = reader.GetBoolean(index); } index++; if (!reader.IsDBNull(index)) { item.DateLastSaved = reader.GetDateTime(index).ToUniversalTime(); } index++; if (query.HasField(ItemFields.Settings)) { if (!reader.IsDBNull(index)) { item.LockedFields = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).Select(i => (MetadataFields)Enum.Parse(typeof(MetadataFields), i, true)).ToList(); } index++; } if (query.HasField(ItemFields.Studios)) { if (!reader.IsDBNull(index)) { item.Studios = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).ToList(); } index++; } if (query.HasField(ItemFields.Tags)) { if (!reader.IsDBNull(index)) { item.Tags = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).ToList(); } index++; } if (!reader.IsDBNull(index)) { item.SourceType = (SourceType)Enum.Parse(typeof(SourceType), reader.GetString(index), true); } index++; var trailer = item as Trailer; if (trailer != null) { if (!reader.IsDBNull(index)) { trailer.TrailerTypes = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).Select(i => (TrailerType)Enum.Parse(typeof(TrailerType), i, true)).ToList(); } } index++; if (query.HasField(ItemFields.OriginalTitle)) { if (!reader.IsDBNull(index)) { item.OriginalTitle = reader.GetString(index); } index++; } var video = item as Video; if (video != null) { if (!reader.IsDBNull(index)) { video.PrimaryVersionId = reader.GetString(index); } } index++; if (query.HasField(ItemFields.DateLastMediaAdded)) { var folder = item as Folder; if (folder != null && !reader.IsDBNull(index)) { folder.DateLastMediaAdded = reader.GetDateTime(index).ToUniversalTime(); } index++; } if (!reader.IsDBNull(index)) { item.Album = reader.GetString(index); } index++; if (!reader.IsDBNull(index)) { item.CriticRating = reader.GetFloat(index); } index++; if (query.HasField(ItemFields.CriticRatingSummary)) { if (!reader.IsDBNull(index)) { item.CriticRatingSummary = reader.GetString(index); } index++; } if (!reader.IsDBNull(index)) { item.IsVirtualItem = reader.GetBoolean(index); } index++; var hasSeries = item as IHasSeries; if (hasSeries != null) { if (!reader.IsDBNull(index)) { hasSeries.SeriesName = reader.GetString(index); } } index++; var episode = item as Episode; if (episode != null) { if (!reader.IsDBNull(index)) { episode.SeasonName = reader.GetString(index); } index++; if (!reader.IsDBNull(index)) { episode.SeasonId = reader.GetGuid(index); } } else { index++; } index++; if (hasSeries != null) { if (!reader.IsDBNull(index)) { hasSeries.SeriesId = reader.GetGuid(index); } } index++; if (hasSeries != null) { if (!reader.IsDBNull(index)) { hasSeries.SeriesSortName = reader.GetString(index); } } index++; if (!reader.IsDBNull(index)) { item.PresentationUniqueKey = reader.GetString(index); } index++; if (!reader.IsDBNull(index)) { item.InheritedParentalRatingValue = reader.GetInt32(index); } index++; if (!reader.IsDBNull(index)) { item.InheritedTags = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).ToList(); } index++; if (!reader.IsDBNull(index)) { item.ExternalSeriesId = reader.GetString(index); } index++; if (query.HasField(ItemFields.ShortOverview)) { if (!reader.IsDBNull(index)) { item.ShortOverview = reader.GetString(index); } index++; } if (query.HasField(ItemFields.Taglines)) { if (!reader.IsDBNull(index)) { item.Tagline = reader.GetString(index); } index++; } if (query.HasField(ItemFields.Keywords)) { if (!reader.IsDBNull(index)) { item.Keywords = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).ToList(); } index++; } if (!reader.IsDBNull(index)) { DeserializeProviderIds(reader.GetString(index), item); } index++; if (query.DtoOptions.EnableImages) { if (!reader.IsDBNull(index)) { DeserializeImages(reader.GetString(index), item); } index++; } if (query.HasField(ItemFields.ProductionLocations)) { if (!reader.IsDBNull(index)) { item.ProductionLocations = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).ToList(); } index++; } if (query.HasField(ItemFields.ThemeSongIds)) { if (!reader.IsDBNull(index)) { item.ThemeSongIds = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).Select(i => new Guid(i)).ToList(); } index++; } if (query.HasField(ItemFields.ThemeVideoIds)) { if (!reader.IsDBNull(index)) { item.ThemeVideoIds = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).Select(i => new Guid(i)).ToList(); } index++; } if (string.IsNullOrWhiteSpace(item.Tagline)) { var movie = item as Movie; if (movie != null && movie.Taglines.Count > 0) { movie.Tagline = movie.Taglines[0]; } } if (type == typeof(Person) && item.ProductionLocations.Count == 0) { var person = (Person)item; if (!string.IsNullOrWhiteSpace(person.PlaceOfBirth)) { item.ProductionLocations = new List<string> { person.PlaceOfBirth }; } } return item; }