//using System; //using System.Collections.Concurrent; //using System.Collections.Generic; //using System.Globalization; //using System.IO; //using System.IO.Compression; //using System.Linq; //using System.Threading.Tasks; //using MoreLinq; //using MySql.Data.MySqlClient; //using NLog; //using Sony.Filtr.Buzz; //using Sony.Filtr.Contracts.Abstractions; //using Sony.Filtr.Contracts.Definitions; //using Sony.Filtr.Contracts.Entities; //using Sony.Filtr.Contracts.Entities.Buzz; //using Sony.Filtr.Core.EditorialPlaylists; //using Sony.Filtr.Core.SpotifyBrowse; //using Sony.Filtr.Core.SpotifyRegion; //using Sony.Filtr.Database; //using Sony.Filtr.Playlists.Spotify; //using Sony.Filtr.SpotifyAnalytics; //using Sony.Filtr.SpotifyAnalytics.Data; //using Sony.Filtr.SpotifyAnalytics.Models; //using Sony.Filtr.Utility; //using Sony.Filtr.Utility.Extensions; //namespace Sony.Filtr.Tasks.Tasks.Spotify //{ // public class UpdateSpotifyAnalyticsTask : IScheduledTask // { // private const string _streamsBaseFileName = "streamsdata"; // private const string _tracksBaseFileName = "tracksdata"; // private const string _usersBaseFileName = "usersdata"; // private const string _unzippedStreamsBaseFileName = "unzippedstreams"; // private const string _fileDirectory = @"spotifyanalytics\"; // private const int _versionNumber = 2; // private readonly IApplicationInstanceManager _applicationInstanceManager; // private readonly EditorialPlaylistManager _editorialPlaylistManager; // private readonly SpotifyAnalyticsManager _spotifyAnalyticsManager; // private readonly SpotifyBrowseManager _spotifyBrowseManager; // private readonly SpotifyRegionManager _spotifyRegionManager; // private readonly SpotifyAnalyticsApi _spotifyAnalyticsApi; // private readonly SpotifyAnalyticsAnalyzer _spotifyAnalyticsAnalyzer; // private readonly BuzzAccountManager _buzzAccountManager; // private readonly SpotifyPlaylistManager _spotifyPlaylistManager; // private readonly ScheduledTaskManager _scheduledTaskManager; // private readonly Logger _logger; // private readonly List _accounts; // public UpdateSpotifyAnalyticsTask(IApplicationInstanceManager applicationInstanceManager, EditorialPlaylistManager editorialPlaylistManager, // SpotifyAnalyticsManager spotifyAnalyticsManager, SpotifyBrowseManager spotifyBrowseManager, SpotifyRegionManager spotifyRegionManager, // SpotifyAnalyticsApi spotifyAnalyticsApi, SpotifyAnalyticsAnalyzer spotifyAnalyticsAnalyzer, BuzzAccountManager buzzAccountManager, // SpotifyPlaylistManager spotifyPlaylistManager, // ScheduledTaskManager scheduledTaskManager) // { // _applicationInstanceManager = applicationInstanceManager; // _editorialPlaylistManager = editorialPlaylistManager; // _spotifyAnalyticsManager = spotifyAnalyticsManager; // _spotifyBrowseManager = spotifyBrowseManager; // _spotifyRegionManager = spotifyRegionManager; // _spotifyAnalyticsApi = spotifyAnalyticsApi; // _spotifyAnalyticsAnalyzer = spotifyAnalyticsAnalyzer; // _buzzAccountManager = buzzAccountManager; // _spotifyPlaylistManager = spotifyPlaylistManager; // _scheduledTaskManager = scheduledTaskManager; // _logger = LogManager.GetLogger("SpotifyAnalyticsUpdate"); // _accounts = new List() // { // new SpotifyAnalyticsAccountConfiguration() { AccountId = (int)SpotifyAnalyticsAccount.Sony, S3BucketPrefix = "" }, // new SpotifyAnalyticsAccountConfiguration() { AccountId = (int)SpotifyAnalyticsAccount.Orchard, S3BucketPrefix = "theorchard" }, // new SpotifyAnalyticsAccountConfiguration() { AccountId = (int)SpotifyAnalyticsAccount.SonyMusicEntertainmentJapan, S3BucketPrefix = "smej" }, // new SpotifyAnalyticsAccountConfiguration() { AccountId = (int)SpotifyAnalyticsAccount.SonyMusicEntertainmentJapanInternational, S3BucketPrefix = "smejintl" }, // }; // } // public async Task ExecuteAsync(Guid scheduledTaskLogId) // { // var taskLog = new ScheduledTaskLog(); // var totalRowsImported = 0; // var allRegions = await GetAllRegionsAsync(); // EnsureFilePathExists(_fileDirectory); // AnalysisInputData inputData = null; // ConcurrentBag files = new ConcurrentBag(); // await _accounts.ForEachAsync(3, async spotifyAnalyticsAccount => // { // foreach (var region in allRegions) // { // var dates = await _spotifyAnalyticsApi.GetStreamDatesAsync(region, spotifyAnalyticsAccount.S3BucketPrefix); // dates.Select(p => new SpotifyAnalyticsFileReference() // { // AccountId = spotifyAnalyticsAccount.AccountId, // Region = region, // DateTime = p.Date // }).ForEach(files.Add); // } // }); // var existingData = await _spotifyAnalyticsManager.GetSpotifyAnalyticsLogAsync(AnalyticsLogType.Finished, _versionNumber); // var existingDataFileReferences = existingData.Select(p => new SpotifyAnalyticsFileReference() { AccountId = p.AccountId, Region = p.Market, DateTime = p.Date }).ToList(); // var filesWithoutExistingData = files.Except(existingDataFileReferences).ToList(); // foreach (var dateAccount in filesWithoutExistingData.GroupBy(p => new { p.AccountId, p.DateTime }).OrderBy(p => p.Key.DateTime)) // { // var spotifyAnalyticsAccount = _accounts.Single(p => p.AccountId == dateAccount.Key.AccountId); // var date = dateAccount.Key.DateTime; // try // { // _logger.Info($"Checking for {date} and account {spotifyAnalyticsAccount.AccountId}"); // var marketsToFetch = dateAccount.Select(p => p.Region).ToList(); // if (marketsToFetch.Any()) // { // if (inputData == null) // { // _logger.Info("Getting all Sony playlists..."); // var sonyEditorialPlaylistMarketsByUri = await GetSonyEditorialPlaylistMarketsByUriAsync(); // var allPlaylists = _spotifyPlaylistManager.GetAllPlaylistUris(); // var buzzUsers = await _buzzAccountManager.GetBuzzUsersAsync(musicServiceId: (int)MusicService.Spotify); // inputData = new AnalysisInputData() // { // AllPlaylistUris = allPlaylists, // SonyPlaylistsByMarket = sonyEditorialPlaylistMarketsByUri, // BuzzUsers = buzzUsers, // }; // } // var dataLog = new ScheduledTaskDataLog("SpotifyAnalytics-global", DateTimeOffset.UtcNow, scheduledTaskLogId); // dataLog.RepresentationDate = date; // dataLog = await _scheduledTaskManager.AddTaskDataLogAsync(dataLog); // var updatedRows = await UpdateAnalyticsDataForMarkets(spotifyAnalyticsAccount, date, marketsToFetch.ToList(), inputData, scheduledTaskLogId); // dataLog.Finished = DateTimeOffset.UtcNow; // dataLog.Rows = updatedRows; // await _scheduledTaskManager.UpdateTaskDataLogAsync(dataLog); // if (updatedRows > 0) // { // await SetPlaylistCategoryStreamsAsync(spotifyAnalyticsAccount.AccountId, date); // totalRowsImported += updatedRows; // } // } // } // catch (Exception ex) // { // taskLog.Error = true; // taskLog.ErrorMessage = ex.Message; // _logger.Error(ex); // } // } // if (totalRowsImported > 0) // { // //await SetPlaylistStreamsSummaryAsync(); // } // _logger.Info("All done with Spotify Analytics."); // Console.WriteLine("Here's an otter:\n"); // AsciiUtility.PrintOtter(); // return taskLog; // } // //private async Task> GetMarketsWithAnalyticsDataAsync(SpotifyAnalyticsAccountConfiguration spotifyAnalyticsAccount, List marketsWithoutData, DateTime date) // //{ // // ConcurrentBag marketsToFetch = new ConcurrentBag(); // // await marketsWithoutData.ForEachAsync(10, async market => // // { // // var fileExists = await _spotifyAnalyticsApi.CheckStreamsFileExistsAsync(date, market, spotifyAnalyticsAccount.S3BucketPrefix); // // if (fileExists) // // { // // marketsToFetch.Add(market); // // } // // }); // // return marketsToFetch; // //} // public async Task SetPlaylistCategoryStreamsAsync(int spotifyAnalyticsAccountId, DateTime analyticsDate) // { // _logger.Info($"Calculating category streams for date {analyticsDate.ToString("yyyy-MM-dd")}..."); // var playlistCategoryStreams = await CalculatePlaylistCategoryStreamsAsync(spotifyAnalyticsAccountId, analyticsDate); // if (playlistCategoryStreams.Any()) // { // var categoryStreamsFileName = await WritePlaylistCategoryStreamsToFile(playlistCategoryStreams, analyticsDate); // await BulkAddToDatabaseAsync(categoryStreamsFileName, "tblSpotifyAnalyticsAccountPlaylistCategoryStreamSummary", MySqlBulkLoaderConflictOption.Replace); // File.Delete(categoryStreamsFileName); // } // _logger.Info("Done calculating category streams!"); // } // private void EnsureFilePathExists(string fileDirectory) // { // if (!Directory.Exists(fileDirectory)) // { // Directory.CreateDirectory(fileDirectory); // } // } // //private async Task SetPlaylistStreamsSummaryAsync() // //{ // // _logger.Debug("Begin SetPlaylistStreamsSummaryAsync"); // // var playlists = await GetPlaylistWithRecentStreamsDataAsync(); // // playlists = playlists.ToList(); // // var latestDayWithData = await _spotifyAnalyticsManager.GetLatestDayWithDataAsyncOld(); // // _logger.Debug($"Will calculate stream summary for {playlists.Count} playlists"); // // ConcurrentBag allPlayliststreamsSummaries = new ConcurrentBag(); // // await playlists.ForEachAsync(10, async playlist => // // { // // Console.WriteLine($"Calculating for playlist {playlist} / {playlist.Count()}"); // // Dictionary> mergedData = new Dictionary>(); // // foreach(var account in _accounts) // // { // // var data = await _spotifyAnalyticsManager.CalculatePlaylistStreamSummaryAsync(account.AccountId, playlistBatch.Item, latestDayWithData); // // mergedData.Add(account.AccountId, data); // // } // // var groupedPlaylistStreamSummaries = mergedData.SelectMany(p => p.Value, (pair, summary) => new // // { // // AccountId = pair.Key, // // Summary = summary, // // }).GroupBy(p => new { p.Summary.PlaylistUri, p.Summary.Market }).Select(playlistMarketData => BuildPlaylistStreamsSummary(playlistMarketData.Key.PlaylistUri, playlistMarketData.Key.Market, playlistMarketData.Select(p => p.Summary).ToList(), playlistMarketData.FirstOrDefault(p => p.AccountId == 1)?.Summary)); // // groupedPlaylistStreamSummaries.ForEach(p => allPlayliststreamsSummaries.Add(p)); // // }); // // _logger.Debug($"Done calculating stream summary."); // // _logger.Debug($"Writing to disk..."); // // var streamPlaylistSummaryFilename = await WritePlaylistStreamsSummaryFile(allPlayliststreamsSummaries.ToList(), DateTime.Today); // // _logger.Debug($"Importing to db..."); // // var columns = new List() // // { // // "playlistUri", "market", // // "Streams56days", "Streams28days", "Streams14days", "Streams7days", // // "Listeners56days", "Listeners28days", "Listeners14days", "Listeners7days", // // "StreamsPerListener56days", "StreamsPerListener28days", "StreamsPerListener14days", "StreamsPerListener7days", // // "StreamsLatest", "ListenersLatest", "StreamsPerListenerLatest", // // "StreamDays56days", "StreamDays28days", "StreamDays14days", "StreamDays7days", // // "TotalStreamDays", // // "LatestDate", "Updated" // // }; // // await BulkAddToDatabaseAsync(streamPlaylistSummaryFilename, "tblSpotifyPlaylistStreamSummaryNew", MySqlBulkLoaderConflictOption.Replace, columns); // // File.Delete(streamPlaylistSummaryFilename); // // await _spotifyAnalyticsManager.ClearPlaylistStreamSummaryCacheAsync(playlists); // // _logger.Debug("Done SetPlaylistStreamsSummaryAsync"); // //} // private PlaylistStreamsSummary BuildPlaylistStreamsSummary(string playlistUri, string market, List allAccounts, PlaylistStreamsSummary sonyData) // { // if (sonyData == null) // { // return new PlaylistStreamsSummary() // { // Market = market, // PlaylistUri = playlistUri, // LatestDate = allAccounts.Max(p => p.LatestDate), // ListenersLatest = 0, // Listeners7days = 0, // Listeners14days = 0, // Listeners28days = 0, // Listeners56days = 0, // TotalStreamDays = 0, // StreamDays56Days = 0, // StreamDays28Days = 0, // StreamDays14Days = 0, // StreamDays7Days = 0, // StreamsPerListenerLatest = 0, // StreamsPerListener7days = 0, // StreamsPerListener14days = 0, // StreamsPerListener28days = 0, // StreamsPerListener56days = 0, // StreamsLatest = allAccounts.Sum(p => p.StreamsLatest), // Streams7days = allAccounts.Sum(p => p.Streams7days), // Streams14days = allAccounts.Sum(p => p.Streams14days), // Streams28days = allAccounts.Sum(p => p.Streams28days), // Streams56days = allAccounts.Sum(p => p.Streams56days), // }; // } // return new PlaylistStreamsSummary() // { // Market = market, // PlaylistUri = playlistUri, // LatestDate = allAccounts.Max(p => p.LatestDate), // ListenersLatest = sonyData.ListenersLatest, // Listeners7days = sonyData.Listeners7days, // Listeners14days = sonyData.Listeners14days, // Listeners28days = sonyData.Listeners28days, // Listeners56days = sonyData.Listeners56days, // StreamsPerListenerLatest = SafePercentage(sonyData.StreamsLatest, sonyData.ListenersLatest), // StreamsPerListener7days = SafePercentage(sonyData.Streams7days, sonyData.Listeners7days), // StreamsPerListener14days = SafePercentage(sonyData.Streams14days, sonyData.Listeners14days), // StreamsPerListener28days = SafePercentage(sonyData.Streams28days, sonyData.Listeners28days), // StreamsPerListener56days = SafePercentage(sonyData.Streams56days, sonyData.Listeners56days), // StreamsLatest = allAccounts.Sum(p => p.StreamsLatest), // Streams7days = allAccounts.Sum(p => p.Streams7days), // Streams14days = allAccounts.Sum(p => p.Streams14days), // Streams28days = allAccounts.Sum(p => p.Streams28days), // Streams56days = allAccounts.Sum(p => p.Streams56days), // TotalStreamDays = sonyData.TotalStreamDays, // StreamDays56Days = sonyData.StreamDays56Days, // StreamDays28Days = sonyData.StreamDays28Days, // StreamDays14Days = sonyData.StreamDays14Days, // StreamDays7Days = sonyData.StreamDays7Days, // }; // } // private double SafePercentage(int sonyDataStreamsLatest, int sonyDataListenersLatest) // { // if (sonyDataListenersLatest == 0) // return 0; // return (double)sonyDataStreamsLatest / (double)sonyDataListenersLatest; // } // private async Task> CalculatePlaylistCategoryStreamsAsync(int spotifyAnalyticsAccountId, DateTime streamsDate) // { // var categoryStreamsForDate = new ConcurrentBag(); // var applications = _applicationInstanceManager.GetApplications().Where(a => !a.GlobalPushApplication && !a.WorkoutMarket).DistinctBy(a => a.SpotifyRegionCode); // await applications.ForEachAsync(5, async app => // { // var appRegions = _applicationInstanceManager.GetApplicationRegions(app); // _logger.Info($"Calculating category streams for market '{app.SpotifyRegionCode}' and account {spotifyAnalyticsAccountId} for Sony's playlists in {streamsDate}..."); // var sonyPlaylistsCategoryStreams = await _spotifyAnalyticsManager.CalculatePlaylistCategoryStreams(spotifyAnalyticsAccountId, streamsDate, app, appRegions, StaticBuzzCategory.SonyMusic); // categoryStreamsForDate.Add(sonyPlaylistsCategoryStreams); // _logger.Info($"Calculating category streams for market '{app.SpotifyRegionCode}' and account {spotifyAnalyticsAccountId} for Spotify's playlists in {streamsDate}..."); // var spotifyPlaylistsCategoryStreams = await _spotifyAnalyticsManager.CalculatePlaylistCategoryStreams(spotifyAnalyticsAccountId, streamsDate, app, appRegions, StaticBuzzCategory.Spotify); // categoryStreamsForDate.Add(spotifyPlaylistsCategoryStreams); // }); // return categoryStreamsForDate.ToList(); // } // private async Task> GetPlaylistWithRecentStreamsDataAsync() // { // DateTime startDate = DateTime.Today.AddDays(-56); //The longest time period we aggregate for is 56 days. // List playlists = new List(); // using (var conn = await DatabaseHandler.GetOpenReadOnlyConnectionAsync()) // { // //TODO: Try and optimize. Use actual dates? // MySqlCommand cmd = new MySqlCommand("SELECT DISTINCT PlaylistUri FROM tblSpotifyAnalyticsAccountPlaylistStreamInfo WHERE Date >= @startDate ", conn); // cmd.Parameters.AddWithValue("@startDate", startDate); // var reader = await cmd.ExecuteReaderAsync(); // while (await reader.ReadAsync()) // { // playlists.Add(reader.GetString(0)); // } // } // return playlists; // } // private async Task> GetAllRegionsAsync() // { // var applicationRegions = _applicationInstanceManager.GetApplications().Select(a => a.SpotifyRegionCode).Where(r => r.Length == 2).ToList(); // var spotifyRegions = await _spotifyRegionManager.GetAvailableRegionsAsync(); // return applicationRegions.Union(spotifyRegions).Select(r => r.ToLowerInvariant()).Distinct().ToList(); // } // //private async Task> GetMarketsWithoutStoredDataForDateAsync(int accountId, List allRegions, DateTime analyticsDateTime) // //{ // // var regionsWithData = await _spotifyAnalyticsManager.GetSpotifyAnalyticsLogAsync(accountId, analyticsDateTime, AnalyticsLogType.Finished, _versionNumber); // // var regionsWithoutData = allRegions.Except(regionsWithData.Select(r => r.Key), StringComparer.InvariantCultureIgnoreCase).ToList(); // // _logger.Info("Found {0} markets with no data for {1} and {2}.", regionsWithoutData.Count, analyticsDateTime.ToString("yyyy-MM-dd"), accountId); // // // Exception to avoid backfill for only CN (China) and ID (Indonesia) since analytics data has not been available for these markets // // if (regionsWithoutData.Count == 2 && regionsWithoutData.Contains("cn") && regionsWithoutData.Contains("id")) // // { // // _logger.Info("Only cn and id without data, no download will be attempted."); // // return new List(); // // } // // return regionsWithoutData; // //} // private async Task UpdateAnalyticsDataForMarkets(SpotifyAnalyticsAccountConfiguration spotifyAnalyticsAccount, DateTime analyticsDate, List markets, AnalysisInputData inputData, Guid scheduledTaskLogId) // { // var totalRows = 0; // if (markets == null || !markets.Any()) // { // _logger.Info("No markets to download data for."); // return 0; // } // var tracksFilePath = _fileDirectory + _tracksBaseFileName + analyticsDate.ToString("yyyyMMdd") + ".gz"; // var tracksDictionary = await DownloadTracksDataAsync(tracksFilePath, analyticsDate, spotifyAnalyticsAccount.S3BucketPrefix); // foreach (var market in markets) // { // await _spotifyAnalyticsManager.SetSpotifyAnalyticsLogAsync(spotifyAnalyticsAccount.AccountId, market, AnalyticsLogType.Started, analyticsDate, _versionNumber); // var decompressedStreamsDataFilePath = await DownloadStreamsDataFileAsync(analyticsDate, market, spotifyAnalyticsAccount.S3BucketPrefix); // if (decompressedStreamsDataFilePath == null) // { // _logger.Warn($"Could not find streams files for market {market} and date {analyticsDate}"); // continue; // } // var dataLog = new ScheduledTaskDataLog($"SpotifyAnalytics-{market}", DateTimeOffset.UtcNow, scheduledTaskLogId); // dataLog.RepresentationDate = analyticsDate; // dataLog.DataSource = decompressedStreamsDataFilePath; //TODO: source file instead. // dataLog = await _scheduledTaskManager.AddTaskDataLogAsync(dataLog); // try // { // var streamsAnalyticsData = await _spotifyAnalyticsAnalyzer.AnalyzeStreamsMarketDataFileAsync(decompressedStreamsDataFilePath, tracksDictionary, market, analyticsDate, inputData); // File.Delete(decompressedStreamsDataFilePath); // await SaveAnalyzedDataAsync(spotifyAnalyticsAccount, analyticsDate, streamsAnalyticsData, market, inputData); // await _spotifyAnalyticsManager.SetSpotifyAnalyticsLogAsync(spotifyAnalyticsAccount.AccountId, market, AnalyticsLogType.Finished, analyticsDate, _versionNumber); // dataLog.Finished = DateTimeOffset.UtcNow; // dataLog.Rows = streamsAnalyticsData.StreamInformation.TotalStreams; // totalRows += streamsAnalyticsData.StreamInformation.TotalStreams; // await _scheduledTaskManager.UpdateTaskDataLogAsync(dataLog); // } // catch (Exception ex) // { // dataLog.Error = true; // dataLog.ErrorMessage = ex.Message; // _logger.Error(ex); //, "Exception in stream data handling."); // } // } // File.Delete(tracksFilePath); // return totalRows; // } // private async Task DownloadStreamsDataFileAsync(DateTime analyticsDate, string market, string customerId) // { // var streamsFilePath = await DownloadCompressedStreamsDataFileAsync(analyticsDate, market, customerId); // var decompressedStreamsDataFilePath = await DecompressToFileAsync(streamsFilePath, market, customerId, analyticsDate); // File.Delete(streamsFilePath); // return decompressedStreamsDataFilePath; // } // private async Task SaveAnalyzedDataAsync(SpotifyAnalyticsAccountConfiguration spotifyAnalyticsAccount, DateTime analyticsDate, AnalyticsData analyticsData, string market, AnalysisInputData inputData) // { // await SaveSonyTracksDataAsync(analyticsData, market); // await _spotifyAnalyticsManager.SaveStreamSummaryAsync(spotifyAnalyticsAccount.AccountId, analyticsData.StreamInformation); // await SavePlaylistStreamsDataAsync(spotifyAnalyticsAccount.AccountId, analyticsDate, analyticsData, market); // await SaveNewPlaylistsDataAsync(analyticsData, market, inputData.BuzzUsers); // } // private async Task SaveNewPlaylistsDataAsync(AnalyticsData analyticsData, string market, List inputDataBuzzUsers) // { // if (analyticsData.NewPlaylists != null && analyticsData.NewPlaylists.Any()) // { // var playlistToAdd = GetPlaylistsToAdd(analyticsData, inputDataBuzzUsers); // if (playlistToAdd.Any()) // { // foreach (var playlist in playlistToAdd) // { // _logger.Debug($"Adding playlist {playlist}"); // } // await AddPlaylistsAsync(market, playlistToAdd); // } // //var playlistUsers = analyticsData.NewPlaylists.Select(p => new SpotifyLink(p.Key).ExtractSpotifyPlaylistUserName()).FilterNull().Distinct(); // //await AddPlaylistUsersAsync(playlistUsers); // } // } // private List GetPlaylistsToAdd(AnalyticsData analyticsData, List inputDataBuzzUsers) // { // var addPlaylistForUsers = inputDataBuzzUsers.Where(p => p.BuzzCategoryId.HasValue && (p.BuzzCategoryId.Value == (int)StaticBuzzCategory.Spotify || p.BuzzCategoryId.Value == (int)StaticBuzzCategory.SonyMusic)); // var userLookup = addPlaylistForUsers.ToLookup(p => p.Username, StringComparer.InvariantCultureIgnoreCase); // var playlistBySpotify = analyticsData.NewPlaylists.Select(p => p.Key).Where(p => userLookup.Contains(new SpotifyLink(p).ExtractSpotifyPlaylistUserName())).ToList(); // return playlistBySpotify; // } // private async Task SavePlaylistStreamsDataAsync(int accountId, DateTime analyticsDate, AnalyticsData analyticsData, string market) // { // var playlistStreamDataFile = await WritePlaylistStreamsDataToFile(accountId, market, analyticsData.PlaylistStreamInformation, analyticsDate); // await BulkAddToDatabaseAsync(playlistStreamDataFile, "tblSpotifyAnalyticsAccountPlaylistStreamInfo", MySqlBulkLoaderConflictOption.Replace); // File.Delete(playlistStreamDataFile); // } // private async Task SaveSonyTracksDataAsync(AnalyticsData analyticsData, string market) // { // var matchFileName = await WriteMatchesToFileAsync(analyticsData.TrackInformation, market); // await BulkAddToDatabaseAsync(matchFileName, "tblSpotifyAnalyticsSonyTracks"); // File.Delete(matchFileName); // } // private async Task AddPlaylistUsersAsync(IEnumerable playlistUsers) // { // var spotifyBuzzUsers = await _buzzAccountManager.GetBuzzUsersAsync(musicServiceId: (int)MusicService.Spotify); // var spotifyBuzzUsersDic = spotifyBuzzUsers.ToLookup(s => s.Username, StringComparer.InvariantCultureIgnoreCase); // var playlistUsersToAdd = playlistUsers.Where(p => !spotifyBuzzUsersDic.Contains(p)); // foreach (var playlistUser in playlistUsersToAdd) // { // try // { // await _buzzAccountManager.AddBuzzUserAsync(new BuzzUser() { Username = playlistUser, ServiceType = ServiceType.Spotify, MusicServiceId = (int)MusicService.Spotify, ImportPlaylists = false, }); // } // catch (Exception e) // { // _logger.Error(e); // } // } // } // private async Task AddPlaylistsAsync(string market, ICollection analyticsDataNewPlaylists) // { // _logger.Debug($"Adding playlist "); // var filename = await WriteNewPlaylistsToFileAsync(market, analyticsDataNewPlaylists); // await BulkAddToDatabaseAsync(filename, "tblSpotifyPlaylist", MySqlBulkLoaderConflictOption.Ignore, new List() { "PlaylistUri" }); // File.Delete(filename); // } // private async Task WriteNewPlaylistsToFileAsync(string market, ICollection analyticsDataNewPlaylists) // { // var fileDateString = DateTime.Today.ToString("yyyyMMdd"); // var filePath = _fileDirectory + $"NewPlaylistImport-{market}-{fileDateString}.csv"; // using (var saveFile = File.Open(filePath, FileMode.OpenOrCreate)) // { // using (var fileWriter = new StreamWriter(saveFile) { AutoFlush = false }) // { // foreach (var playlistUri in analyticsDataNewPlaylists) // { // var csvLine = string.Join("|", playlistUri); // await fileWriter.WriteLineAsync(csvLine); // } // } // } // _logger.Info("Wrote " + analyticsDataNewPlaylists.Count() + " playlists to file: " + filePath); // return filePath; // } // private async Task> DownloadTracksDataAsync(string tracksDataFilePath, DateTime analyticsDate, string customerId) // { // await DownloadCompressedTracksDataFile(analyticsDate, tracksDataFilePath, customerId); // var tracksDataTask = await DecompressAndDeserializeDataFile(tracksDataFilePath); // var dictionary = tracksDataTask.ToDictionary(k => k.track_id, v => new AdditionalTrackData() { ISRC = v.isrc, UPC = v.album_code, TrackUri = new SpotifyLink(v.uri).ExtractTrackID() }); // return new ConcurrentDictionary(dictionary); // } // private async Task> GetSonyEditorialPlaylistMarketsByUriAsync() // { // return await _editorialPlaylistManager.GetUniqueSonySpotifyPlaylistsAsync(); // } // private async Task DownloadCompressedStreamsDataFileAsync(DateTime analyticsDate, string market, string customerId) // { // _logger.Info($"Downloading streams data for market {market} and account {customerId}..."); // var filePath = _fileDirectory + BuildStreamsFilename(customerId, analyticsDate, market) + ".gz"; // filePath = await _spotifyAnalyticsApi.DownloadStreamsDataFileAsync(analyticsDate, market, customerId, filePath); // _logger.Info($"Finished downloading streams data for market {market} and account {customerId}."); // return filePath; // } // private string BuildStreamsFilename(string customerId, DateTime analyticsDate, string market) // { // return _streamsBaseFileName + customerId + analyticsDate.ToString("yyyyMMdd") + "_" + market.ToUpperInvariant(); // } // private async Task DownloadCompressedTracksDataFile(DateTime analyticsDate, string tracksFilePath, string customerId) // { // _logger.Info($"Downloading tracks data for {customerId}..."); // await _spotifyAnalyticsApi.DownloadTracksDataFileAsync(analyticsDate, customerId, tracksFilePath); // _logger.Info($"Finished downloading tracks data and writing to file for account {customerId}."); // return tracksFilePath; // } // private async Task> DecompressAndDeserializeDataFile(string filePath) where T : class // { // _logger.Info($"Decompressing and deserializing file {filePath}..."); // IEnumerable apiObjects; // using (FileStream originalFileStream = File.Open(filePath, FileMode.Open)) // { // apiObjects = await DecompressGzipDataAsync(originalFileStream); // } // _logger.Info($"Finished decompressing and deserializing file {filePath}."); // return apiObjects; // } // private async Task> DecompressGzipDataAsync(Stream gzippedStream) where T : class // { // var deserializedDataObjects = new List(); // using (var decompressionReader = new StreamReader(new GZipStream(gzippedStream, CompressionMode.Decompress))) // { // while (!decompressionReader.EndOfStream) // { // var decompressedLine = await decompressionReader.ReadLineAsync(); // var deserializedObject = Jil.JSON.Deserialize(decompressedLine); // deserializedDataObjects.Add(deserializedObject); // } // _logger.Info("Decompressed stream (" + gzippedStream.Length + " bytes)"); // } // return deserializedDataObjects; // } // private async Task DecompressToFileAsync(string filePath, string market, string customerId, DateTime analyticsDateTime) // { // var unzippedFileName = _fileDirectory + BuildStreamsFilename(customerId, analyticsDateTime, market); // using (var gzipFileStream = File.OpenRead(filePath)) // { // using (var decompressedStream = new GZipStream(gzipFileStream, CompressionMode.Decompress)) // { // using (var fileDestination = File.Open(unzippedFileName, FileMode.OpenOrCreate)) // { // await decompressedStream.CopyToAsync(fileDestination); // } // _logger.Info("Decompressed stream (" + gzipFileStream.Length + " bytes)"); // } // } // return unzippedFileName; // } // private async Task WriteMatchesToFileAsync(ConcurrentDictionary trackMatchDictionary, string region) // { // var filePath = _fileDirectory + "matches_" + region + ".csv"; // using (var saveFile = File.Open(filePath, FileMode.OpenOrCreate)) // { // using (var fileWriter = new StreamWriter(saveFile)) // { // //fileWriter.NewLine = "\n"; // foreach (var uriAndIsrc in trackMatchDictionary) // { // var line = string.Join("|", region, uriAndIsrc.Key, uriAndIsrc.Value.ISRC, uriAndIsrc.Value.UPC); // await fileWriter.WriteLineAsync(line); // } // } // } // _logger.Info("Wrote " + trackMatchDictionary.Keys.Count() + " tracks to file: " + filePath); // return filePath; // } // private async Task WritePlaylistStreamsSummaryFile(List playlistStreamsSummaries, DateTime currentDate) // { // var filePath = _fileDirectory + "PlaylistStreamsSummary_" + currentDate.ToString("yyyyMMdd") + ".csv"; // var defaultNumberFormat = new NumberFormatInfo() // { // NumberDecimalSeparator = "." // }; // using (var saveFile = File.Open(filePath, FileMode.OpenOrCreate)) // { // using (var fileWriter = new StreamWriter(saveFile) { AutoFlush = false }) // { // foreach (var streams in playlistStreamsSummaries) // { // var csvLine = string.Join("|", // streams.PlaylistUri, // streams.Market, // streams.Streams56days, // streams.Streams28days, // streams.Streams14days, // streams.Streams7days, // streams.Listeners56days, // streams.Listeners28days, // streams.Listeners14days, // streams.Listeners7days, // streams.StreamsPerListener56days.ToString(defaultNumberFormat), // streams.StreamsPerListener28days.ToString(defaultNumberFormat), // streams.StreamsPerListener14days.ToString(defaultNumberFormat), // streams.StreamsPerListener7days.ToString(defaultNumberFormat), // streams.StreamsLatest, // streams.ListenersLatest, // streams.StreamsPerListenerLatest.ToString(defaultNumberFormat), // streams.StreamDays56Days, // streams.StreamDays28Days, // streams.StreamDays14Days, // streams.StreamDays7Days, // streams.TotalStreamDays, // FormatDateForImportFile(streams.LatestDate), // FormatDateForImportFile(DateTime.UtcNow) // ); // await fileWriter.WriteLineAsync(csvLine); // } // } // } // _logger.Info("Wrote " + playlistStreamsSummaries.Count() + " market stream summaries to file: " + filePath); // return filePath; // } // private async Task WritePlaylistCategoryStreamsToFile(List playlistCategoryStreams, DateTime currentDate) // { // var filePath = _fileDirectory + "PlaylistCategoryStreams_" + currentDate.ToString("yyyyMMdd") + ".csv"; // using (var saveFile = File.Open(filePath, FileMode.OpenOrCreate)) // { // using (var fileWriter = new StreamWriter(saveFile) { AutoFlush = false }) // { // foreach (var streams in playlistCategoryStreams) // { // var csvLine = string.Join("|", // streams.AccountId, // streams.Market, // streams.BuzzCategoryId, // FormatDateForImportFile(streams.Date), // streams.TotalListStreams, // streams.LocalListStreams, // streams.TotalListListeners, // streams.LocalListListeners // ); // await fileWriter.WriteLineAsync(csvLine); // } // } // } // _logger.Info("Wrote " + playlistCategoryStreams.Count() + " category stream summaries to file: " + filePath); // return filePath; // } // private async Task WriteStreamsDataToFile(Dictionary streamData, DateTime currentDate) // { // var filePath = _fileDirectory + "streamdata_" + currentDate.ToString("yyyyMMdd") + ".csv"; // using (var saveFile = File.Open(filePath, FileMode.OpenOrCreate)) // { // using (var fileWriter = new StreamWriter(saveFile)) // { // //fileWriter.NewLine = "\n"; // foreach (var streams in streamData) // { // var csvLine = string.Join("|", // streams.Value.AccountId, // streams.Key, // FormatDateForImportFile(currentDate), // streams.Value.TotalStreams, // streams.Value.DeviceDesktopStreams, // streams.Value.DeviceTabletStreams, // streams.Value.DeviceMobileStreams, // streams.Value.OSAndroidStreams, // streams.Value.OSiOSStreams, // streams.Value.OSWindowsStreams, // streams.Value.OSMacOSStreams, // streams.Value.OSOtherStreams, // streams.Value.SourcePlaylist, // streams.Value.SourceSearch, // streams.Value.SourceArtist, // streams.Value.SourceAlbum, // streams.Value.SourceCollection, // streams.Value.SourceOther, // streams.Value.StreamsFromSonyPlaylists, // streams.Value.StreamsFromLocalSonyPlaylists, // streams.Value.UniqueUsers // ); // await fileWriter.WriteLineAsync(csvLine); // } // } // } // _logger.Info("Wrote " + streamData.Count() + " market stream summaries to file: " + filePath); // return filePath; // } // private async Task WritePlaylistStreamsDataToFile(int accountId, string market, ConcurrentDictionary playlistStreamData, DateTime currentDate) // { // var filePath = _fileDirectory + "playliststreamdata_" + currentDate.ToString("yyyyMMdd") + ".csv"; // using (var saveFile = File.Open(filePath, FileMode.OpenOrCreate)) // { // using (var fileWriter = new StreamWriter(saveFile)) // { // //fileWriter.NewLine = "\n"; // foreach (var streams in playlistStreamData) // { // var csvLine = string.Join("|", // accountId, // market, // streams.Key, // FormatDateForImportFile(currentDate), // streams.Value.TotalStreams, // streams.Value.DeviceDesktopStreams, // streams.Value.DeviceTabletStreams, // streams.Value.DeviceMobileStreams, // streams.Value.OSAndroidStreams, // streams.Value.OSiOSStreams, // streams.Value.OSWindowsStreams, // streams.Value.OSMacOSStreams, // streams.Value.OSOtherStreams, // streams.Value.UniqueUsers // ); // await fileWriter.WriteLineAsync(csvLine); // } // } // } // _logger.Info("Wrote " + playlistStreamData.Count() + " playlist stream summaries to file: " + filePath); // return filePath; // } // private static string FormatDateForImportFile(DateTime currentDate) // { // return currentDate.Year + "-" + currentDate.Month.ToString("D2") + "-" + currentDate.Day.ToString("D2"); // } // private async Task BulkAddToDatabaseAsync(string fileName, string tableName, MySqlBulkLoaderConflictOption conflictOption = MySqlBulkLoaderConflictOption.Ignore, List columns = null) // { // using (var conn = await DatabaseHandler.GetOpenConnectionAsync()) // { // var bulkLoader = new MySqlBulkLoader(conn); // bulkLoader.Local = true; // bulkLoader.FileName = fileName; // bulkLoader.TableName = tableName; // bulkLoader.FieldTerminator = "|"; // bulkLoader.LineTerminator = Environment.NewLine; // bulkLoader.ConflictOption = conflictOption; // bulkLoader.CharacterSet = "utf8mb4"; // if (columns != null) // { // bulkLoader.Columns.AddRange(columns); // } // await bulkLoader.LoadAsync(); // } // _logger.Info("Added " + fileName + " to database: " + tableName); // } // private class SpotifyAnalyticsAccountConfiguration // { // public string S3BucketPrefix { get; set; } // public int AccountId { get; set; } // } // private class SpotifyAnalyticsFileReference : IEquatable // { // public int AccountId { get; set; } // public string Region { get; set; } // public DateTime DateTime { get; set; } // public bool Equals(SpotifyAnalyticsFileReference other) // { // if (ReferenceEquals(null, other)) // return false; // if (ReferenceEquals(this, other)) // return true; // return AccountId == other.AccountId && string.Equals(Region, other.Region) && DateTime.Date.Equals(other.DateTime.Date); // } // public override bool Equals(object obj) // { // if (ReferenceEquals(null, obj)) // return false; // if (ReferenceEquals(this, obj)) // return true; // if (obj.GetType() != this.GetType()) // return false; // return Equals((SpotifyAnalyticsFileReference)obj); // } // public override int GetHashCode() // { // unchecked // { // var hashCode = AccountId; // hashCode = (hashCode * 397) ^ (Region != null ? Region.GetHashCode() : 0); // hashCode = (hashCode * 397) ^ DateTime.Date.GetHashCode(); // return hashCode; // } // } // } // } //}