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