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");
}