public static List <SpotifyInfo> GetAllSongsFromStaticSheetVN() { try { ServiceAccountCredential credential1; string[] Scopes = { SheetsService.Scope.Spreadsheets }; string serviceAccountEmail = "*****@*****.**"; string jsonfile = "trackingNewData.json"; string spreadsheetID = "1k0G4J_HXLzOvaOvoUPHt8m7S-ogMxaeF53SE6ZfgXfo"; string range = "Danh sách nhạc tổng!A2:K"; using (Stream stream = new FileStream(@jsonfile, FileMode.Open, FileAccess.Read, FileShare.Read)) { credential1 = (ServiceAccountCredential) GoogleCredential.FromStream(stream).UnderlyingCredential; var initializer = new ServiceAccountCredential.Initializer(credential1.Id) { User = serviceAccountEmail, Key = credential1.Key, Scopes = Scopes }; credential1 = new ServiceAccountCredential(initializer); } var serices = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential1, ApplicationName = ApplicationName, }); SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum valueRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum) 0; SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum dateTimeRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum) 0; SpreadsheetsResource.ValuesResource.GetRequest request = serices.Spreadsheets.Values.Get(spreadsheetID, range); request.ValueRenderOption = valueRenderOption; request.DateTimeRenderOption = dateTimeRenderOption; // To execute asynchronously in an async method, replace `request.Execute()` as shown: Data.ValueRange response = request.Execute(); IList <IList <Object> > values = response.Values; List <SpotifyInfo> listSongs = new List <SpotifyInfo>(); foreach (var item in values) { if (item.Count >= 6) { SpotifyInfo song = new SpotifyInfo(); song.TrackTitle = item[4].ToString(); song.Artists = item[5].ToString(); song.Range = "I" + (values.IndexOf(item) + 2).ToString() + ":" + "J" + (values.IndexOf(item) + 2).ToString(); listSongs.Add(song); } } return(listSongs); } catch (Exception ex) { return(null); } }
public static async Task <SpotifyInfo> GetAlbumId(SpotifyInfo spotifyInfo) { try { var searchQuery = ""; var url = ""; if (!string.IsNullOrEmpty(spotifyInfo.Code)) { searchQuery = $"isrc:{spotifyInfo.Code}"; searchQuery = HttpUtility.UrlEncode(searchQuery); url = $"https://api.spotify.com/v1/search?q={searchQuery}" + "&market=VN&type=track&offset=0&limit=20"; var result = await GetAlbumIdByCode(spotifyInfo, url); return(result); } else { if (string.IsNullOrEmpty(spotifyInfo.Artists) || spotifyInfo.Artists.ToLower().Equals("đang cập nhật")) { return(null); } else { searchQuery = spotifyInfo.TrackTitle + " " + spotifyInfo.Artists; searchQuery = HttpUtility.UrlEncode(searchQuery); url = $"https://api.spotify.com/v1/search?q={searchQuery}" + "&market=VN&type=track&offset=0&limit=20"; var result = await GetAlbumIdByName(spotifyInfo, url); return(result); } } } catch (Exception ex) { Console.WriteLine(ex.Message + " " + spotifyInfo.TrackTitle); return(null); } }
static void UpdateSpotifySongsToWMGStatistic(SouceTypes souceTypes) { List <SpotifyInfo> listSongAfter = new List <SpotifyInfo>(); List <SpotifyInfo> listSongsFromStatistic = new List <SpotifyInfo>(); bool check = false; if (souceTypes == SouceTypes.GoogleSheet) { listSongsFromStatistic = GetDataFromGoogleSheet.GetAllSongsFromStaticSheet(); numberOfOldSongs = listSongsFromStatistic.Count(); List <SpotifyInfo> listSongsFromSource = WMGSource.GetAllSongFromServer(); foreach (var item in listSongsFromSource) { if (string.IsNullOrEmpty(item.Code)) { if (listSongsFromStatistic.Any(p => p.TrackTitle.ToLower().Equals(item.TrackTitle.ToLower()) && p.Artists.ToLower().Equals(item.Artists.ToLower())) == false) { listSongAfter.Add(item); } } else { if (listSongsFromStatistic.Any(p => p.Code.ToLower().Equals(item.Code.ToLower())) == false || string.IsNullOrEmpty(item.Code)) { listSongAfter.Add(item); } } } } else { Console.WriteLine("Reading data from file"); string excutingPath = System.Reflection.Assembly.GetExecutingAssembly().Location; string excelDirPath = Path.Combine(Path.GetDirectoryName(excutingPath), "ExcelFiles"); var excelFiles = Directory.GetFiles(excelDirPath, "*.xlsx"); //var fileName = @"C:\ExcelFile.xlsx"; foreach (var file in excelFiles) { using (var stream = File.Open(file, FileMode.Open, FileAccess.Read)) { // Auto-detect format, supports: // - Binary Excel files (2.0-2003 format; *.xls) // - OpenXml Excel files (2007 format; *.xlsx, *.xlsb) using (var reader = ExcelReaderFactory.CreateReader(stream)) { // Choose one of either 1 or 2: // 1. Use the reader methods do { while (reader.Read()) { // reader.GetDouble(0); } } while (reader.NextResult()); // 2. Use the AsDataSet extension method var result = reader.AsDataSet(); for (int i = 1; i < result.Tables[0].Rows.Count; i++) { var row = result.Tables[0].Rows[i]; SpotifyInfo spotifyInfo = new SpotifyInfo(); spotifyInfo.Code = row[2].ToString(); try { spotifyInfo.TrackTitle = RemoveBadChars(row[3].ToString()); spotifyInfo.Artists = RemoveBadChars(row[4].ToString()); } catch (Exception ex) { continue; } if (!string.IsNullOrEmpty(spotifyInfo.TrackTitle) && !string.IsNullOrEmpty(spotifyInfo.Artists)) { listSongAfter.Add(spotifyInfo); } } } } } Console.WriteLine($"Read {listSongAfter.Count} items done!"); } var listNew = new List <SpotifyInfo>(); var songAfterGetGenre = new List <SpotifyInfo>(); //kiểm tra xem có bài mới không, nếu có thì lấy dữ liệu về và chạy api để lấy genre, track id, album id, year, stream count if (listSongAfter.Count > 0) { var groupCount = (int)Math.Ceiling((double)listSongAfter.Count / (double)SP_GROUP_SIZE); for (var i = 0; i < groupCount; i++) { var groupSongs = listSongAfter.Skip(i * SP_GROUP_SIZE).Take(SP_GROUP_SIZE).ToList(); Console.WriteLine($"Running {i}..."); var list = SpotifyService.GetSpotifyInfoAsync(groupSongs, spotifyNewSongs).GetAwaiter().GetResult(); var listAfterGenres = SpotifyService.GetArtistGenresAndRegion(list); listNew.AddRange(listAfterGenres); Console.WriteLine($"Done {(i + 1)* SP_GROUP_SIZE} / {listSongAfter.Count}. Wating 2s..."); if (souceTypes == SouceTypes.Excel) { string excutingPath = System.Reflection.Assembly.GetExecutingAssembly().Location; string excelDirPath = Path.Combine(Path.GetDirectoryName(excutingPath), "ExcelFiles"); // save to file string outputFile = Path.Combine(excelDirPath, "result.csv"); //before your loop var csv = new StringBuilder(); foreach (var song in listAfterGenres) { if (song.StreamCount < 500000) { continue; } var newLine = string.Format("{0},{1},{2},{3},{4},{5},{6}", song.Code, song.TrackTitle, song.Artists, song.LinkSpotify, song.StreamCount.ToString(), song.ReleaseDate, song.Genres); csv.AppendLine(newLine); } File.AppendAllText(outputFile, csv.ToString()); } Thread.Sleep(5000); } //lấy thể loại của song trên spotify if (souceTypes == SouceTypes.GoogleSheet) { var genres = SpotifyService.GetArtistGenresAndRegion(listNew); songAfterGetGenre.AddRange(genres); } check = true; } // add bài mới vào sheet bao gồm các trường : tên, code, nhạc sỹ, genre, quốc gia if (check && souceTypes == SouceTypes.GoogleSheet) { UpdateDataToGoogleSheet.AppendNewSongs(songAfterGetGenre, listSongsFromStatistic.Count); } //get stream count của cả bài cũ + mới var ytbGroupCount = (int)Math.Ceiling((double)listSongsFromStatistic.Count / (double)SP_GROUP_SIZE); var listAll = new List <SpotifyInfo>(); for (var i = 0; i < ytbGroupCount; i++) { var groupSongs = listSongsFromStatistic.Skip(i * SP_GROUP_SIZE).Take(SP_GROUP_SIZE).ToList(); Console.WriteLine($"Running {i}..."); var list = SpotifyService.GetSpotifyInfoAsync(groupSongs, spotifyAllSongs, false).GetAwaiter().GetResult(); listAll.AddRange(list); Console.WriteLine($"Done. Wating 2s..."); Thread.Sleep(5000); } if (check) { listAll.AddRange(songAfterGetGenre); } // insert stream count và popularity của all songs UpdateDataToGoogleSheet.InserViewCountToNewColumn(listAll, column); UpdateDataToGoogleSheet.InserViewCountToPopularityColumn(listAll, "I"); }
public static List <SpotifyInfo> GetAllSongsFromStaticSheet() { try { ServiceAccountCredential credential1; string[] Scopes = { SheetsService.Scope.Spreadsheets }; string serviceAccountEmail = "*****@*****.**"; string jsonfile = "trackingNewData.json"; string spreadsheetID = "1XsrVqD-Fz1ggj2VX6wEbpt_FO0qguTMJR5YWnytYXV4"; string range = "All"; using (Stream stream = new FileStream(@jsonfile, FileMode.Open, FileAccess.Read, FileShare.Read)) { credential1 = (ServiceAccountCredential) GoogleCredential.FromStream(stream).UnderlyingCredential; var initializer = new ServiceAccountCredential.Initializer(credential1.Id) { User = serviceAccountEmail, Key = credential1.Key, Scopes = Scopes }; credential1 = new ServiceAccountCredential(initializer); } var serices = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential1, ApplicationName = ApplicationName, }); SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum valueRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum) 0; SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum dateTimeRenderOption = (SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum) 0; SpreadsheetsResource.ValuesResource.GetRequest request = serices.Spreadsheets.Values.Get(spreadsheetID, range); request.ValueRenderOption = valueRenderOption; request.DateTimeRenderOption = dateTimeRenderOption; // To execute asynchronously in an async method, replace `request.Execute()` as shown: Data.ValueRange response = request.Execute(); IList <IList <Object> > values = response.Values; values.RemoveAt(0); List <SpotifyInfo> listSongs = new List <SpotifyInfo>(); foreach (var item in values) { if (item.Count != 0) { SpotifyInfo song = new SpotifyInfo(); for (int i = 0; i < item.Count; i++) { if (i == 1) { if (item[i] != null) { song.TrackTitle = item[i].ToString(); } } else if (i == 2) { song.Code = item[i].ToString(); } else if (i == 3) { if (item[i] != null) { song.Artists = item[i].ToString(); } } else if (i == 4) { if (!string.IsNullOrEmpty(item[i].ToString())) { song.LinkSpotify = item[i].ToString(); song.TrackId = item[i].ToString().Split(new string[] { "=" }, StringSplitOptions.None)[1].Split(new string[] { ":" }, StringSplitOptions.None)[2]; song.AlbumId = item[i].ToString().Split(new string[] { "?" }, StringSplitOptions.None)[0].Split(new string[] { "https://open.spotify.com/album/" }, StringSplitOptions.None)[1]; } } else if (i == 5) { if (item[i] != null) { song.Genres = item[i].ToString(); } } else if (i == 6) { if (item[i] != null) { song.Country = item[i].ToString(); } } else if (i == 7) { if (item[i] != null) { song.ReleaseDate = item[i].ToString(); } } else if (i == 8) { if (item[i] != null) { song.Popularity = item[i].ToString(); } } else if (i == 9) { if (item[i] != null) { if (item[i].ToString() != "") { song.StreamCount = long.Parse(item[i].ToString()); } } } } listSongs.Add(song); } } return(listSongs); } catch (Exception ex) { return(null); } }
public static List <SpotifyInfo> GetAllSongFromServer() { try { List <SpotifyInfo> songs = new List <SpotifyInfo>(); List <SpotifyInfo> result = new List <SpotifyInfo>(); string currentDir = Directory.GetCurrentDirectory(); FFmpeg.SetExecutablesPath(currentDir); string route1 = "http://118.69.82.99:9001/film_video_processing/List?length=1000000000"; string route = "http://1.53.252.34:9001/film_video_processing/List?length=1000000000"; List <SongInfo> webSongInfos = new List <SongInfo>(); using (var client = new System.Net.Http.HttpClient()) { try { var response = client.GetAsync(route).GetAwaiter().GetResult(); if (response.IsSuccessStatusCode) { var jsonResponse = response.Content.ReadAsStringAsync().Result; webSongInfos = Newtonsoft.Json.JsonConvert.DeserializeObject <WebResponse>(jsonResponse).Data; } else { throw new Exception("Get web data failed"); } } catch { var response = client.GetAsync(route1).GetAwaiter().GetResult(); if (response.IsSuccessStatusCode) { var jsonResponse = response.Content.ReadAsStringAsync().Result; webSongInfos = Newtonsoft.Json.JsonConvert.DeserializeObject <WebResponse>(jsonResponse).Data; } else { throw new Exception("Get web data failed"); } } } foreach (var item in webSongInfos) { var song = new SpotifyInfo(); song.TrackTitle = item.Title == null ? "" : item.Title; song.Code = item.Code == null ? "" : item.Code; song.Artists = item.Actor == null ? "" : item.Actor; string date = ""; if (string.IsNullOrEmpty(date)) { CultureInfo culture = new CultureInfo("en-US"); date = Convert.ToDateTime(item.datecreated, culture).ToString("yyyy-MM-dd"); } song.CreateDate = date; songs.Add(song); } return(songs); } catch (Exception ex) { return(null); } }
public static async Task <SpotifyInfo> GetAlbumIdByName(SpotifyInfo spotifyInfo, string url) { try { using (var http = new HttpClient()) { http.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json")); http.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", _accessToken); var response = await http.GetAsync(url); if (response.StatusCode == System.Net.HttpStatusCode.Unauthorized || response.StatusCode == HttpStatusCode.ServiceUnavailable) // 401 - access token expired { await UpdateAccessToken(); http.DefaultRequestHeaders.Remove("Authorization"); http.DefaultRequestHeaders.Add("Authorization", $"Bearer {_accessToken}"); response = await http.GetAsync(url); if (response.StatusCode != System.Net.HttpStatusCode.OK) { throw new Exception($"Error when search album '{spotifyInfo.TrackTitle}' of '{spotifyInfo.Artists}'. Status code: {response.StatusCode}"); } } else if (response.StatusCode != System.Net.HttpStatusCode.OK) { throw new Exception($"Error when search album '{spotifyInfo.TrackTitle}' of '{spotifyInfo.Artists}'. Status code: {response.StatusCode}"); } var result = JsonConvert.DeserializeObject <dynamic>(await response.Content.ReadAsStringAsync()); if (result.tracks.items == null || result.tracks.items.Count == 0) { return(null); } else { for (var i = 0; i < result.tracks.items.Count; i++) { var track = result.tracks.items[i]; if (RemoveDiacritics(((string)track.name)).ToLower().Contains(RemoveDiacritics(spotifyInfo.TrackTitle.ToLower())) || (RemoveDiacritics(spotifyInfo.TrackTitle.ToLower())).Contains(RemoveDiacritics((string)track.name))) { var artists = track.artists; for (var j = 0; j < artists.Count; j++) { if (RemoveDiacritics((string)artists[j].name).ToLower().Contains(RemoveDiacritics(spotifyInfo.Artists.ToLower())) || (RemoveDiacritics(spotifyInfo.Artists.ToLower())).Contains(RemoveDiacritics((string)artists[j].name))) { var albumId = ((string)track.album.uri).Replace("spotify:album:", ""); spotifyInfo.TrackId = track.id; spotifyInfo.Popularity = (string)track.popularity; spotifyInfo.ReleaseDate = ((string)track.album.release_date).Substring(0, 4); spotifyInfo.AlbumId = albumId; spotifyInfo.AlbumTitle = track.album.name; spotifyInfo.ArtistId = ((string)(track.artists[0].uri)).Replace("spotify:artist:", ""); return(spotifyInfo); } } } } for (var i = 0; i < result.tracks.item.Count; i++) { var track = result.tracks.items[i]; if (RemoveDiacritics((string)track.name).ToLower().Contains(RemoveDiacritics(spotifyInfo.TrackTitle.ToLower())) || (RemoveDiacritics(spotifyInfo.TrackTitle.ToLower())).Contains(RemoveDiacritics((string)track.name))) { var albumId = ((string)track.album.uri).Replace("spotify:album:", ""); spotifyInfo.TrackId = track.id; spotifyInfo.Popularity = (string)track.popularity; spotifyInfo.ReleaseDate = ((string)track.album.release_date).Substring(0, 4); spotifyInfo.AlbumId = albumId; spotifyInfo.AlbumTitle = track.album.name; spotifyInfo.ArtistId = ((string)(track.artists[0].uri)).Replace("spotify:artist:", ""); return(spotifyInfo); } } return(null); } } } catch (Exception ex) { Console.WriteLine(ex.Message + " " + spotifyInfo.TrackTitle); return(null); } }
public static async Task <SpotifyInfo> GetAlbumIdByCode(SpotifyInfo spotifyInfo, string url) { try { using (var http = new HttpClient()) { http.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json")); http.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", _accessToken); var response = await http.GetAsync(url); if (response.StatusCode == System.Net.HttpStatusCode.Unauthorized || response.StatusCode == HttpStatusCode.ServiceUnavailable) // 401 - access token expired { await UpdateAccessToken(); http.DefaultRequestHeaders.Remove("Authorization"); http.DefaultRequestHeaders.Add("Authorization", $"Bearer {_accessToken}"); http.DefaultRequestHeaders.Add("scope", "user-read-private"); response = await http.GetAsync(url); if (response.StatusCode != System.Net.HttpStatusCode.OK) { throw new Exception($"Error when search album '{spotifyInfo.TrackTitle}' of '{spotifyInfo.Artists}'. Status code: {response.StatusCode}"); } } else if (response.StatusCode != System.Net.HttpStatusCode.OK) { throw new Exception($"Error when search album '{spotifyInfo.TrackTitle}' of '{spotifyInfo.Artists}'. Status code: {response.StatusCode}"); } var result = JsonConvert.DeserializeObject <dynamic>(await response.Content.ReadAsStringAsync()); if (result.tracks.items == null || result.tracks.items.Count == 0) { return(null); } else { List <SpotifyInfo> newList = new List <SpotifyInfo>(); for (var i = 0; i < result.tracks.items.Count; i++) { var track = result.tracks.items[i]; var artists = track.artists; for (var j = 0; j < artists.Count; j++) { SpotifyInfo item = new SpotifyInfo(); var albumId = ((string)track.album.uri).Replace("spotify:album:", ""); item.TrackId = track.id; item.Popularity = (string)track.popularity; item.ReleaseDate = ((string)track.album.release_date); item.AlbumId = albumId; item.AlbumTitle = track.album.name; item.ArtistId = ((string)(track.artists[0].uri)).Replace("spotify:artist:", ""); newList.Add(item); } } var items = newList.Where((x) => int.Parse(x.ReleaseDate.Substring(0, 4)) == newList.Min(y => int.Parse(y.ReleaseDate.Substring(0, 4)))).ToList(); if (items == null || items.Count == 0) { return(null); } else { if (items.Count == 1) { spotifyInfo.TrackId = items[0].TrackId; spotifyInfo.ReleaseDate = items[0].ReleaseDate.Substring(0, 4);; spotifyInfo.AlbumId = items[0].AlbumId; spotifyInfo.AlbumTitle = items[0].AlbumTitle; spotifyInfo.ArtistId = items[0].ArtistId; spotifyInfo.Popularity = items[0].Popularity; return(spotifyInfo); } else { bool check = items.Any(i => i.ReleaseDate.Length == 4); if (check) { var spo = items.FirstOrDefault(x => x.ReleaseDate.Length == 4); spotifyInfo.TrackId = spo.TrackId; spotifyInfo.ReleaseDate = spo.ReleaseDate.Substring(0, 4);; spotifyInfo.AlbumId = spo.AlbumId; spotifyInfo.AlbumTitle = spo.AlbumTitle; spotifyInfo.ArtistId = spo.ArtistId; spotifyInfo.Popularity = items[0].Popularity; return(spotifyInfo); } else { var spo = new SpotifyInfo(); try { spo = items.Find((x) => DateTime.Parse(x.ReleaseDate) == newList.Min(y => DateTime.Parse(y.ReleaseDate))); } catch { spo = items.FirstOrDefault(); } spotifyInfo.TrackId = spo.TrackId; spotifyInfo.ReleaseDate = spo.ReleaseDate.Substring(0, 4);; spotifyInfo.AlbumId = spo.AlbumId; spotifyInfo.AlbumTitle = spo.AlbumTitle; spotifyInfo.ArtistId = spo.ArtistId; spotifyInfo.Popularity = items[0].Popularity; return(spotifyInfo); } } } } } } catch (Exception ex) { Console.WriteLine(ex.Message + " " + spotifyInfo.TrackTitle); return(null); } }
public static async Task <SpotifyInfo> CountTracksPlay(SpotifyInfo spotifyInfo, bool getAlbumId = true) { try { var url = ""; if (getAlbumId) { var trackInfo = await GetAlbumId(spotifyInfo); if (trackInfo == null) { return(spotifyInfo); } url = $"http://localhost:8080/albumPlayCount?albumid={trackInfo.AlbumId}"; } else { url = $"http://localhost:8080/albumPlayCount?albumid={spotifyInfo.AlbumId}"; } using (var client = new HttpClient()) { Thread.Sleep(1000); var response = await client.GetAsync(url); var result = JsonConvert.DeserializeObject <dynamic>(await response.Content.ReadAsStringAsync()); if (result.success == true) { var discs = result.data.discs; foreach (var item in discs) { var tracks = item.tracks; for (var i = 0; i < tracks.Count; i++) { var id = ((string)tracks[i].uri).Replace("spotify:track:", ""); if (spotifyInfo.TrackId.Equals(id)) { spotifyInfo.StreamCount = long.Parse(tracks[i].playcount.ToString()); if (spotifyInfo.StreamCount == 0) { spotifyInfo.StreamCount = 1000; } if (string.IsNullOrEmpty(spotifyInfo.Popularity)) { spotifyInfo.Popularity = (string)tracks[i].popularity; } return(spotifyInfo); } } } return(spotifyInfo); } else { return(spotifyInfo); } } } catch (Exception ex) { Console.WriteLine(ex.Message + " " + spotifyInfo.TrackTitle); return(spotifyInfo); } }