private async Task <RServiceResult <bool> > _ImportPoemSongsDataFromMySql(string jobName, RMuseumDbContext context, LongRunningJobProgressServiceEF jobProgressServiceEF, RLongRunningJobStatus job, bool approved) { try { job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, $"{jobName} - pre mysql data fetch")).Result; string connectionString = approved ? $"server={Configuration.GetSection("AudioMySqlServer")["Server"]};uid={Configuration.GetSection("AudioMySqlServer")["Username"]};pwd={Configuration.GetSection("AudioMySqlServer")["Password"]};database={Configuration.GetSection("AudioMySqlServer")["Database"]};charset=utf8;convert zero datetime=True" : $"server={Configuration.GetSection("AudioMySqlServer")["Server"]};uid={Configuration.GetSection("AudioMySqlServer")["SongsUsername"]};pwd={Configuration.GetSection("AudioMySqlServer")["SongsPassword"]};database={Configuration.GetSection("AudioMySqlServer")["SongsDatabase"]};charset=utf8;convert zero datetime=True"; using (MySqlConnection connection = new MySqlConnection ( connectionString )) { connection.Open(); using (MySqlDataAdapter src = new MySqlDataAdapter( "SELECT poem_id, artist_name, artist_beeptunesurl, album_name, album_beeptunesurl, track_name, track_beeptunesurl, ptrack_typeid FROM ganja_ptracks ORDER BY id", connection)) { job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, $"{jobName} - mysql")).Result; using (DataTable data = new DataTable()) { await src.FillAsync(data); job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, $"{jobName} - processing approved poem songs")).Result; foreach (DataRow row in data.Rows) { PoemMusicTrack track = new PoemMusicTrack() { TrackType = (PoemMusicTrackType)int.Parse(row["ptrack_typeid"].ToString()), PoemId = int.Parse(row["poem_id"].ToString()), ArtistName = row["artist_name"].ToString(), ArtistUrl = row["artist_beeptunesurl"].ToString(), AlbumName = row["album_name"].ToString(), AlbumUrl = row["album_beeptunesurl"].ToString(), TrackName = row["track_name"].ToString(), TrackUrl = row["track_beeptunesurl"].ToString(), ApprovalDate = DateTime.Now, Description = "", Approved = approved }; var poem = await context.GanjoorPoems.Where(p => p.Id == track.PoemId).SingleOrDefaultAsync(); if (poem == null) { continue; } switch (track.TrackType) { case PoemMusicTrackType.BeepTunesOrKhosousi: case PoemMusicTrackType.iTunes: { GanjoorTrack catalogueTrack = await context.GanjoorMusicCatalogueTracks.Where(m => m.Url == track.TrackUrl).FirstOrDefaultAsync(); if (catalogueTrack != null) { track.GanjoorTrackId = catalogueTrack.Id; } GanjoorSinger singer = await context.GanjoorSingers.Where(s => s.Url == track.ArtistUrl).FirstOrDefaultAsync(); if (singer != null) { track.SingerId = singer.Id; } } break; case PoemMusicTrackType.Golha: { track.AlbumName = $"{track.ArtistName} » {track.AlbumName}"; track.ArtistName = ""; track.GolhaTrackId = int.Parse(track.ArtistUrl); track.ArtistUrl = ""; } break; } context.GanjoorPoemMusicTracks.Add(track); } job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, $"{jobName} - finalizing approved poem songs data")).Result; await context.SaveChangesAsync(); } } } return(new RServiceResult <bool>(true)); } catch (Exception exp) { await jobProgressServiceEF.UpdateJob(job.Id, job.Progress, "", false, exp.ToString()); return(new RServiceResult <bool>(false, exp.ToString())); } }
/// <summary> /// import catalogue from ganjoor.net MySql db /// </summary> /// <param name="jobName"></param> /// <param name="jobProgressServiceEF"></param> /// <param name="job"></param> /// <returns></returns> public async Task <RServiceResult <bool> > ImportFromMySql(string jobName, LongRunningJobProgressServiceEF jobProgressServiceEF, RLongRunningJobStatus job) { try { using (RMuseumDbContext context = new RMuseumDbContext(Configuration)) //this is long running job, so _context might be already been freed/collected by GC { using (MySqlConnection connection = new MySqlConnection ( $"server={Configuration.GetSection("AudioMySqlServer")["Server"]};uid={Configuration.GetSection("AudioMySqlServer")["SongsUsername"]};pwd={Configuration.GetSection("AudioMySqlServer")["SongsPassword"]};database={Configuration.GetSection("AudioMySqlServer")["SongsDatabase"]};charset=utf8;convert zero datetime=True" )) { job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, $"{jobName} - import golha data - pre open connection")).Result; connection.Open(); using (MySqlDataAdapter src = new MySqlDataAdapter( "SELECT col_id, name FROM golha_collections ORDER BY col_id", connection)) { using (DataTable data = new DataTable()) { await src.FillAsync(data); foreach (DataRow row in data.Rows) { GolhaCollection collection = new GolhaCollection() { Id = int.Parse(row["col_id"].ToString()), Name = row["name"].ToString(), Programs = new List <GolhaProgram>() }; job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, $"{jobName} - import golha data - golha_collections: {collection.Id}")).Result; using (MySqlDataAdapter srcPrograms = new MySqlDataAdapter( $"SELECT program_id, title, progarm_order, url, mp3 FROM golha_programs WHERE col_id = {collection.Id} ORDER BY program_id", connection)) { using (DataTable programData = new DataTable()) { await srcPrograms.FillAsync(programData); foreach (DataRow golhaProgram in programData.Rows) { GolhaProgram program = new GolhaProgram() { Id = int.Parse(golhaProgram["program_id"].ToString()), Title = golhaProgram["title"].ToString(), ProgramOrder = int.Parse(golhaProgram["progarm_order"].ToString()), Url = golhaProgram["url"].ToString(), Mp3 = golhaProgram["mp3"].ToString(), Tracks = new List <GolhaTrack>() }; using (MySqlDataAdapter srcTracks = new MySqlDataAdapter( $"SELECT track_id, track_no, timing, title FROM golha_tracks WHERE program_id = {program.Id} ORDER BY track_no", connection)) { using (DataTable trackData = new DataTable()) { await srcTracks.FillAsync(trackData); foreach (DataRow golhaTrack in trackData.Rows) { program.Tracks.Add ( new GolhaTrack() { Id = int.Parse(golhaTrack["track_id"].ToString()), TrackNo = int.Parse(golhaTrack["track_no"].ToString()), Timing = golhaTrack["timing"].ToString(), Title = golhaTrack["title"].ToString(), Blocked = false, } ); } } } collection.Programs.Add(program); } } } context.GolhaCollections.Add(collection); await context.SaveChangesAsync(); } } } job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, $"{jobName} - import singers data")).Result; using (MySqlDataAdapter src = new MySqlDataAdapter( "SELECT artist_id, artist_name, artist_beeptunesurl FROM ganja_artists ORDER BY artist_id", connection)) { using (DataTable data = new DataTable()) { await src.FillAsync(data); foreach (DataRow row in data.Rows) { int artistId = int.Parse(row["artist_id"].ToString()); GanjoorSinger singer = new GanjoorSinger() { Name = row["artist_name"].ToString(), Url = row["artist_beeptunesurl"].ToString(), Albums = new List <GanjoorAlbum>() }; using (MySqlDataAdapter srcAlbums = new MySqlDataAdapter( $"SELECT album_id, album_name, album_beeptunesurl FROM ganja_albums WHERE album_artistid = {artistId} ORDER BY album_id", connection)) { using (DataTable dataAlbums = new DataTable()) { await srcAlbums.FillAsync(dataAlbums); foreach (DataRow rowAlbum in dataAlbums.Rows) { int albumId = int.Parse(rowAlbum["album_id"].ToString()); GanjoorAlbum album = new GanjoorAlbum() { Name = rowAlbum["album_name"].ToString(), Url = rowAlbum["album_beeptunesurl"].ToString(), Tracks = new List <GanjoorTrack>() }; using (MySqlDataAdapter srcTracks = new MySqlDataAdapter( $"SELECT track_name, track_beeptunesurl FROM ganja_tracks WHERE album_id = {albumId} ORDER BY track_id", connection)) { using (DataTable dataTracks = new DataTable()) { await srcTracks.FillAsync(dataTracks); foreach (DataRow rowTrack in dataTracks.Rows) { album.Tracks.Add ( new GanjoorTrack() { Name = rowTrack["track_name"].ToString(), Url = rowTrack["track_beeptunesurl"].ToString(), Blocked = false } ); } } } singer.Albums.Add(album); } } } context.GanjoorSingers.Add(singer); job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, $"{jobName} - import singers data - {singer.Name}")).Result; } } } job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, $"{jobName} - finalizing singers data")).Result; await context.SaveChangesAsync(); } } return(new RServiceResult <bool>(true)); } catch (Exception exp) { await jobProgressServiceEF.UpdateJob(job.Id, job.Progress, "", false, exp.ToString()); return(new RServiceResult <bool>(false, exp.ToString())); } }
private async Task _PerformMundexHouseKeepingAndPreparation(RMuseumDbContext context, LongRunningJobProgressServiceEF jobProgressServiceEF, RLongRunningJobStatus job) { var singers = await context.GanjoorSingers.ToListAsync(); foreach (var singer in singers) { if (!string.IsNullOrEmpty(singer.Url) && singer.Url.Length > 0 && singer.Url[singer.Url.Length - 1] == '/') { singer.Url = singer.Url.Substring(0, singer.Url.Length - 1); context.GanjoorSingers.Update(singer); } } await context.SaveChangesAsync(); await jobProgressServiceEF.UpdateJob(job.Id, 2); var poemMusicTracks = await context.GanjoorPoemMusicTracks .Where(m => m.TrackType == PoemMusicTrackType.BeepTunesOrKhosousi && m.Approved) .ToListAsync(); IConfigurationRoot configuration = new ConfigurationBuilder() .SetBasePath(Directory.GetCurrentDirectory()).AddJsonFile("appsettings.json") .Build(); ImageFileServiceEF imageFileService = new ImageFileServiceEF(context, configuration); using (HttpClient httpClient = new HttpClient()) foreach (var poemMusicTrack in poemMusicTracks) { if (!string.IsNullOrEmpty(poemMusicTrack.ArtistUrl) && poemMusicTrack.ArtistUrl.Length > 0 && poemMusicTrack.ArtistUrl[poemMusicTrack.ArtistUrl.Length - 1] == '/') { poemMusicTrack.ArtistUrl = poemMusicTrack.ArtistUrl.Substring(0, poemMusicTrack.ArtistUrl.Length - 1); context.GanjoorPoemMusicTracks.Update(poemMusicTrack); } var singer = poemMusicTrack.SingerId != null? context.GanjoorSingers.Where(s => s.Id == poemMusicTrack.SingerId).FirstOrDefault() : context.GanjoorSingers.Where(s => s.Url == poemMusicTrack.ArtistUrl).FirstOrDefault(); if (singer == null) { singer = new GanjoorSinger() { Name = poemMusicTrack.ArtistName, Url = poemMusicTrack.ArtistUrl }; context.GanjoorSingers.Add(singer); await context.SaveChangesAsync(); } if (poemMusicTrack.SingerId == null) { poemMusicTrack.SingerId = singer.Id; context.GanjoorPoemMusicTracks.Update(poemMusicTrack); } //singer image: if (singer.Url.Contains("beeptunes.com/artist/")) { if (singer.RImageId == null) { var bUrl = singer.Url; var beepId = bUrl.Substring(bUrl.LastIndexOf("/") + 1); var response = await httpClient.GetAsync($"https://newapi.beeptunes.com/public/artist/info/?artistId={beepId}"); if (response.IsSuccessStatusCode) { dynamic bpArtist = JsonConvert.DeserializeObject(await response.Content.ReadAsStringAsync()); if (bpArtist.artistImage != null) { try { var imageResult = await httpClient.GetAsync(bpArtist.artistImage.ToString()); if (imageResult.IsSuccessStatusCode) { using (Stream imageStream = await imageResult.Content.ReadAsStreamAsync()) { RServiceResult <RImage> image = await imageFileService.Add(null, imageStream, $"{beepId}.jpg", Path.Combine(configuration.GetSection("PictureFileService")["StoragePath"], "SingerImages")); if (string.IsNullOrEmpty(image.ExceptionString)) { image = await imageFileService.Store(image.Result); if (string.IsNullOrEmpty(image.ExceptionString)) { singer.RImageId = image.Result.Id; context.GanjoorSingers.Update(singer); await context.SaveChangesAsync(); } } } } } catch { } } } } } } await context.SaveChangesAsync(); await jobProgressServiceEF.UpdateJob(job.Id, 3); }