Example #1
0
        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");
        }