/// <summary>
/// Returns the Podcast statistics
/// </summary>
/// <returns></returns>
public IEnumerable <PodcastForStatistic> GetStatistics()
{
List <PodcastForStatistic> statistics = new List <PodcastForStatistic>();
// Get all podcasts
using (SqlDatabaseCommand cmd = this.GetDatabaseCommand())
{
cmd.CommandText.AppendLine(" SELECT PodcastID, SummaryTitle, ISNULL(AudioAlreadyDownloaded, 0) AS Downloaded, 'mp3' AS FileType FROM Podcast ")
.AppendLine(" UNION ")
.AppendLine(" SELECT PodcastID, SummaryTitle, ISNULL(VideoAlreadyDownloaded, 0) AS Downloaded, 'mp4' AS FileType FROM Podcast ")
.AppendLine(" UNION ")
.AppendLine(" SELECT PodcastID, SummaryTitle, ISNULL(VideoAlreadyDownloaded, 0) AS Downloaded, 'youtube' AS FileType FROM Podcast ")
.AppendLine(" ORDER BY PodcastID, FileType ");
statistics = cmd.ExecuteTable <PodcastForStatistic>().ToList();
}
// Add statistics values
using (SqlDatabaseCommand cmd = this.GetDatabaseCommand())
{
cmd.CommandText.AppendLine(" SELECT PodcastID, FileType, COUNT(*) AS Downloaded ")
.AppendLine(" FROM PodcastStatistic ")
.AppendLine(" GROUP BY PodcastID, FileType ");
var data = cmd.ExecuteTable(new { PodcastID = 0, FileType = "", Downloaded = 0 });
foreach (var item in data)
{
statistics.First(i => i.PodcastID == item.PodcastID && i.FileType == item.FileType)
.Downloaded += item.Downloaded;
}
}
// Add youtube statistics
Dictionary <int, int?> youtubeStat = GetYoutubeStatistics();
foreach (var item in statistics)
{
if (item.FileType == "youtube")
{
item.Downloaded = youtubeStat.ContainsKey(item.PodcastID) ? youtubeStat[item.PodcastID].Value : 0;
}
}
return(statistics);
}
/// <summary>
/// Gets the statisctics about these youtube videos: a list of {PodcastID, NumberOfYoutubeViews}.
/// </summary>
/// <remarks>
/// 1. Connect you to https://console.developers.google.com
/// 2. Create a project and activate "YouTube Data API"
/// 3. Create a new API key in section Credentials, and use this key below
/// </remarks>
private Dictionary <int, int?> GetYoutubeStatistics()
{
//var youtubeKeys = null;
// Search all Youtube keys.
using (SqlDatabaseCommand cmd = this.GetDatabaseCommand())
{
cmd.CommandText.AppendLine(" SELECT PodcastID, VideoYoutubeKey, 0 AS Views FROM Podcast ");
var youtubeKeys = cmd.ExecuteTable(new { PodcastID = 0, VideoYoutubeKey = "", Views = 0 }).ToArray();
// Youtube statistics URL
string keys = String.Join(",", youtubeKeys.Select(i => i.VideoYoutubeKey).ToArray());
string googleapi = $"https://www.googleapis.com/youtube/v3/videos?part=statistics&id={keys}&key={this.Configuration.YoutubeStatisticsApiKey}";
Dictionary <int, int?> statistics = new Dictionary <int, int?>();
// Read web content
using (HttpClient client = new HttpClient())
{
string googleResult = client.GetStringAsync(googleapi).Result;
// Deserialize
var definition = new { items = new[] { new { id = "", statistics = new { viewCount = 0 } } } };
var data = JsonConvert.DeserializeAnonymousType(googleResult, definition);
if (data != null && data.items != null && data.items.Length > 0)
{
foreach (var item in data.items)
{
statistics.Add(youtubeKeys.First(i => i.VideoYoutubeKey == item.id).PodcastID,
item.statistics?.viewCount);
}
return(statistics);
}
else
{
return(null);
}
}
}
}
/// <summary>
/// Returns only the specified podcast
/// </summary>
/// <param name="podcastID"></param>
/// <returns></returns>
private IEnumerable <Podcast> GetLastPodcastsFromDatabase(int podcastID)
{
int maxRows = Configuration.MaximumOfPodcastsOnHomePage;
using (SqlDatabaseCommand cmd = this.GetDatabaseCommand())
{
string onlyOnePodcast = podcastID > 0 ? $" AND PodcastID = {podcastID}" : String.Empty;
// Search all authors for last podcasts
cmd.Clear();
cmd.CommandText.AppendLine($" SELECT PodcastID, Name, Url ");
cmd.CommandText.AppendLine($" FROM PodcastAuthor ");
cmd.CommandText.AppendLine($" INNER JOIN PodcastAuthorLink ON PodcastAuthorLink.PodcastAuthorID = PodcastAuthor.PodcastAuthorID ");
cmd.CommandText.AppendLine($" WHERE PodcastAuthorLink.PodcastID IN (SELECT TOP {maxRows} PodcastID FROM Podcast ORDER BY PublicationDate DESC) ");
cmd.CommandText.AppendLine(onlyOnePodcast);
var authors = cmd.ExecuteTable((row) =>
{
return(new
{
PodcastID = row.Field <int>("PodcastID"),
Name = row.Field <string>("Name"),
Url = row.Field <string>("Url"),
});
});
// Select last podcasts
cmd.Clear();
cmd.CommandText.AppendLine($" SELECT TOP {maxRows} * ");
cmd.CommandText.AppendLine($" FROM Podcast ");
cmd.CommandText.AppendLine($" WHERE 1=1 " + onlyOnePodcast);
cmd.CommandText.AppendLine($" ORDER BY PublicationDate DESC ");
return(cmd.ExecuteTable((row) =>
{
int id = row.Field <int>("PodcastID");
Podcast podcast = new Podcast(this.Configuration);
podcast.PodcastID = id;
podcast.PodcastKey = row.Field <string>("PodcastKey");
podcast.PublicationDate = row.Field <DateTime>("PublicationDate");
podcast.Summary = new PodcastSummary(podcast)
{
Title = row.Field <string>("SummaryTitle"),
Description = row.Field <string>("SummaryDescription"),
Authors = authors.Where(i => i.PodcastID == id)
.OrderBy(i => i.Name)
.Select(i => new PodcastAuthor(podcast)
{
Name = i.Name,
Email = String.Empty,
Url = i.Url
})
.ToArray()
};
podcast.Notes = row.Field <string>("Notes");
podcast.Audio = new PodcastAudio(podcast)
{
RemoteUrl = row.Field <string>("AudioUrl"),
Size = (uint)row.Field <int>("AudioSize"),
Duration = row.Field <TimeSpan>("AudioDuration"),
};
podcast.Video = new PodcastVideo(podcast)
{
RemoteUrl = row.Field <string>("VideoUrl"),
Size = (uint)row.Field <int>("VideoSize"),
Duration = row.Field <TimeSpan>("VideoDuration"),
YoutubeKey = row.Field <string>("VideoYoutubeKey"),
};
return podcast;
}));
}
}
/// <summary>
/// Load all configurations parameters et Header values
/// </summary>
private void LoadConfigurationAndHeaders()
{
if (_configuration == null)
{
_configuration = new Configuration();
}
if (_siteHeader == null)
{
_siteHeader = new SocialInformation();
}
using (SqlDatabaseCommand cmd = this.GetDatabaseCommand())
{
// Gets all configuration values
cmd.CommandText.AppendLine(" SELECT Name, Value FROM Configuration ");
var allConfigValues = cmd.ExecuteTable((row) =>
{
return(new
{
Name = row.Field <string>("Name"),
Value = row.Field <string>("Value")
});
});
// Filtering Name / Value
foreach (var item in allConfigValues)
{
switch (item.Name)
{
case "MaximumOfPodcastsOnHomePage":
_configuration.MaximumOfPodcastsOnHomePage = Int32.Parse(item.Value);
break;
case "DisqusShortnameKey":
_configuration.DisqusShortnameKey = item.Value;
break;
case "SiteUrl":
_configuration.SiteUrl = new Uri(item.Value);
_siteHeader.Url = item.Value;
break;
case "Version":
_configuration.Version = new Version(item.Value);
break;
case "AzureStorageCredentials":
string[] splitted = item.Value.Split(';');
_configuration.AzureStorageCredentials = new KeyValuePair <string, string>(splitted[0], splitted[1]);
break;
case "SiteName":
_siteHeader.Title = item.Value;
_siteHeader.Name = item.Value;
break;
case "SiteDescription":
_siteHeader.Description = item.Value;
break;
case "SiteAuthor":
_siteHeader.Authors = item.Value;
break;
case "SiteLanguage":
_siteHeader.Language = item.Value;
break;
case "GoogleAnalyticsKey":
_siteHeader.GoogleAnalyticsKey = item.Value;
break;
case "TwitterUrl":
_siteHeader.TwitterUrl = item.Value;
break;
case "TwitterAccount":
_siteHeader.TwitterAccount = item.Value;
break;
case "CopyrightName":
_siteHeader.CopyrightName = item.Value;
break;
case "CopyrightUrl":
_siteHeader.CopyrightUrl = item.Value;
break;
case "CopyrightEmail":
_siteHeader.CopyrightEmail = item.Value;
break;
case "CategoriesKeywords":
_siteHeader.CategoriesKeywords = item.Value;
break;
case "ImageUrl":
_siteHeader.ImageUrl = item.Value;
break;
case "Creator":
_siteHeader.Creator = item.Value;
break;
case "FeedAudioUrl":
_siteHeader.FeedAudioUrl = item.Value;
break;
case "FeedVideoUrl":
_siteHeader.FeedVideoUrl = item.Value;
break;
case "FeedTitle":
_siteHeader.FeedTitle = item.Value;
break;
case "YoutubeStatisticsApiKey":
_configuration.YoutubeStatisticsApiKey = item.Value;
break;
}
}
}
}