private async Task <List <GanjoorCat> > _FindTargetCategories(RMuseumDbContext context, GanjoorCat startCat, int?endCatId) { if (endCatId == null || startCat.Id == endCatId) { List <GanjoorCat> cats = new List <GanjoorCat>(); cats.Add(startCat); cats.AddRange(await _FindAllSubCategories(context, startCat.Id)); return(cats); } else { return(await _FindCategoryRanges(context, startCat, (int)endCatId)); } }
private async Task <List <GanjoorCat> > _FindCategoryRanges(RMuseumDbContext context, GanjoorCat startCat, int endCatId) { var parents = await context.GanjoorCategories.AsNoTracking().Where(c => c.ParentId == startCat.ParentId && c.Id >= startCat.Id && c.Id <= endCatId).OrderBy(c => c.Id).ToListAsync(); List <GanjoorCat> cats = new List <GanjoorCat>(); foreach (GanjoorCat parentCat in parents) { cats.Add(parentCat); var subCats = await _FindAllSubCategories(context, parentCat.Id); if (subCats.Count > 0) { cats.AddRange(subCats); } } return(cats); }
private async Task ExportCatToSqlite(RMuseumDbContext context, SqliteConnection sqliteConnection, GanjoorCat cat) { int parentId = cat.ParentId == null ? 0 : (int)cat.ParentId; await sqliteConnection.ExecuteAsync($"INSERT INTO cat (id, poet_id, text, parent_id, url) VALUES ({cat.Id}, {cat.PoetId}, '{cat.Title}', {parentId}, 'https://ganjoor.net{cat.FullUrl}');"); var poems = await context.GanjoorPoems.AsNoTracking().Where(p => p.CatId == cat.Id).ToListAsync(); foreach (var poem in poems) { await sqliteConnection.ExecuteAsync($"INSERT INTO poem (id, cat_id, title, url) VALUES ({poem.Id}, {poem.CatId}, '{poem.Title}', 'https://ganjoor.net{poem.FullUrl}');"); foreach (var verse in await context.GanjoorVerses.AsNoTracking().Where(v => v.PoemId == poem.Id).OrderBy(v => v.VOrder).ToListAsync()) { await sqliteConnection.ExecuteAsync($"INSERT INTO verse (poem_id, vorder, position, text) VALUES ({poem.Id}, {verse.VOrder}, {(int)verse.VersePosition}, '{verse.Text}');"); } } foreach (var child in await context.GanjoorCategories.AsNoTracking().Where(c => c.ParentId == cat.Id).ToListAsync()) { await ExportCatToSqlite(context, sqliteConnection, child); } }
private async Task <string> _ImportSQLiteCatChildren(RMuseumDbContext context, IDbConnection sqlite, int poetId, int sqliteParentCatId, GanjoorCat parentCat, string parentFullTitle, LongRunningJobProgressServiceEF jobProgressServiceEF, RLongRunningJobStatus job, int parentPagId) { try { string catHtmlText = ""; foreach (var cat in await sqlite.QueryAsync($"SELECT * FROM cat WHERE parent_id = {sqliteParentCatId} ORDER BY id")) { await jobProgressServiceEF.UpdateJob(job.Id, 0, $"Importing - {cat.text}"); var poetCatId = 1 + await context.GanjoorCategories.MaxAsync(c => c.Id); string catTitle = cat.text; string url = GPersianTextSync.Farglisize(catTitle); if (catTitle.IndexOf('|') != -1) { string[] catParts = catTitle.Split('|', StringSplitOptions.RemoveEmptyEntries); if (catParts.Length == 2) { catTitle = catParts[0].Trim(); url = catParts[1].Trim(); } } else { switch (catTitle) { case "دیوان اشعار": url = "divan"; break; case "قصاید": case "قصائد": case "قصیده": case "قصیده ها": url = "ghaside"; break; case "غزلیات": case "غزل": case "غزل ها": url = "ghazal"; break; case "قطعات": case "مقطعات": case "قطعه": url = "ghete"; break; case "مثنویات": case "مثنوی": case "مثنوی ها": url = "masnavi"; break; case "ترکیبات": case "ترکیب بند": url = "tarkib"; break; case "ترجیعات": case "ترجیع بند": url = "tarjee"; break; case "مسمطات": case "مسمط": url = "mosammat"; break; case "مخمسات": case "مخمس": url = "mokhammas"; break; case "رباعیات": case "رباعی": case "رباعی ها": url = "robaee"; break; case "ملمعات": case "ملمع": url = "molamma"; break; case "هجویات": case "هجو": url = "hajv"; break; case "هزلیات": case "هزل": url = "hazl"; break; case "مراثی": case "مرثیه": case "رثا": case "مرثیه ها": url = "marsie"; break; case "مفردات": url = "mofradat"; break; case "ملحقات": url = "molhaghat"; break; case "اشعار عربی": url = "arabi"; break; case "ماده تاریخها": case "ماده تاریخها": case "ماده تاریخ": url = "tarikh"; break; case "معمیات": url = "moammiyat"; break; case "چیستان": url = "chistan"; break; case "لغز": case "لغزها": url = "loghaz"; break; } } GanjoorCat dbCat = new GanjoorCat() { Id = poetCatId, PoetId = poetId, Title = catTitle, UrlSlug = url, FullUrl = $"{parentCat.FullUrl}/{url}", ParentId = parentCat.Id, TableOfContentsStyle = GanjoorTOC.Analyse, Published = true, }; context.GanjoorCategories.Add(dbCat); var maxPageId = await context.GanjoorPages.MaxAsync(p => p.Id); if (await context.GanjoorPoems.MaxAsync(p => p.Id) > maxPageId) { maxPageId = await context.GanjoorPoems.MaxAsync(p => p.Id); } var catPageId = 1 + maxPageId; while (await context.GanjoorPoems.Where(p => p.Id == catPageId).AnyAsync()) { catPageId++; } GanjoorPage dbPageCat = new GanjoorPage() { Id = catPageId, GanjoorPageType = GanjoorPageType.CatPage, Published = false, PageOrder = -1, Title = dbCat.Title, FullTitle = $"{parentFullTitle} » {dbCat.Title}", UrlSlug = dbCat.UrlSlug, FullUrl = dbCat.FullUrl, HtmlText = "", PoetId = poetId, CatId = poetCatId, PostDate = DateTime.Now, ParentId = parentPagId }; context.GanjoorPages.Add(dbPageCat); await context.SaveChangesAsync(); catHtmlText += $"<p><a href=\"{dbCat.FullUrl}\">{dbCat.Title}</a></p>{Environment.NewLine}"; var resChild = await _ImportSQLiteCatChildren(context, sqlite, poetId, (int)cat.id, dbCat, $"{parentFullTitle} » {dbCat.Title}", jobProgressServiceEF, job, dbPageCat.Id); if (!string.IsNullOrEmpty(resChild)) { return(resChild); } } var maxPoemId = await context.GanjoorPoems.MaxAsync(p => p.Id); if (await context.GanjoorPages.MaxAsync(p => p.Id) > maxPoemId) { maxPoemId = await context.GanjoorPages.MaxAsync(p => p.Id); } var poemId = 1 + maxPoemId; int poemNumber = 0; foreach (var poem in await sqlite.QueryAsync($"SELECT * FROM poem WHERE cat_id = {sqliteParentCatId} ORDER BY id")) { poemNumber++; await jobProgressServiceEF.UpdateJob(job.Id, poemNumber, "", false); string title = poem.title; string urlSlug = $"sh{poemNumber}"; if (title.IndexOf('|') != -1) { string[] titleParts = title.Split('|', StringSplitOptions.RemoveEmptyEntries); if (titleParts.Length == 2) { title = titleParts[0].Trim(); urlSlug = titleParts[1].Trim(); } } GanjoorPoem dbPoem = new GanjoorPoem() { Id = poemId, CatId = parentCat.Id, Title = title, UrlSlug = urlSlug, FullTitle = $"{parentFullTitle} » {title}", FullUrl = $"{parentCat.FullUrl}/{urlSlug}", Published = true, }; List <GanjoorVerse> poemVerses = new List <GanjoorVerse>(); foreach (var verse in await sqlite.QueryAsync($"SELECT * FROM verse WHERE poem_id = {poem.id} ORDER BY vorder")) { int vOrder = int.Parse(verse.vorder.ToString()); int position = int.Parse(verse.position.ToString()); string text = verse.text; GanjoorVerse dbVerse = new GanjoorVerse() { PoemId = poemId, VOrder = vOrder, VersePosition = (VersePosition)position, Text = text.Replace("ـ", "").Replace(" ", " ").ApplyCorrectYeKe().Trim() }; poemVerses.Add(dbVerse); } if (poemVerses.Count == 0) { poemNumber--; continue; } dbPoem.PlainText = PreparePlainText(poemVerses); dbPoem.HtmlText = PrepareHtmlText(poemVerses); context.GanjoorPoems.Add(dbPoem); await context.SaveChangesAsync(); foreach (var dbVerse in poemVerses) { context.GanjoorVerses.Add(dbVerse); await context.SaveChangesAsync();//id set should be in order } await _FillPoemCoupletIndices(context, poemId); try { var poemRhymeLettersRes = LanguageUtils.FindRhyme(poemVerses); if (!string.IsNullOrEmpty(poemRhymeLettersRes.Rhyme)) { dbPoem.RhymeLetters = poemRhymeLettersRes.Rhyme; context.GanjoorPoems.Update(dbPoem); } } catch { } GanjoorPage dbPoemPage = new GanjoorPage() { Id = poemId, GanjoorPageType = GanjoorPageType.PoemPage, Published = false, PageOrder = -1, Title = dbPoem.Title, FullTitle = dbPoem.FullTitle, UrlSlug = dbPoem.UrlSlug, FullUrl = dbPoem.FullUrl, HtmlText = dbPoem.HtmlText, PoetId = poetId, CatId = parentCat.Id, PoemId = poemId, PostDate = DateTime.Now, ParentId = parentPagId }; context.GanjoorPages.Add(dbPoemPage); await context.SaveChangesAsync(); catHtmlText += $"<p><a href=\"{dbPoemPage.FullUrl}\">{dbPoemPage.Title}</a></p>{Environment.NewLine}"; poemId++; } if (!string.IsNullOrEmpty(catHtmlText)) { var parentCatPage = await context.GanjoorPages.Where(p => p.FullUrl == parentCat.FullUrl).SingleAsync(); parentCatPage.HtmlText += catHtmlText; context.GanjoorPages.Update(parentCatPage); } await context.SaveChangesAsync(); } catch (Exception exp) { return(exp.ToString()); } return(""); }
/// <summary> /// import GanjoorPage entity data from MySql /// </summary> /// <returns></returns> public RServiceResult <bool> ImportFromMySql() { try { _backgroundTaskQueue.QueueBackgroundWorkItem ( async token => { using (RMuseumDbContext context = new RMuseumDbContext(Configuration)) //this is long running job, so _context might be already been freed/collected by GC using (RMuseumDbContext contextReport = new RMuseumDbContext(Configuration)) //this is long running job, so _context might be already been freed/collected by GC { LongRunningJobProgressServiceEF jobProgressServiceEF = new LongRunningJobProgressServiceEF(contextReport); var job = (await jobProgressServiceEF.NewJob("GanjoorService:ImportFromMySql", "pre open connection")).Result; if (string.IsNullOrEmpty(Configuration.GetSection("AudioMySqlServer")["ReportedCommentsDatabase"])) { await jobProgressServiceEF.UpdateJob(job.Id, job.Progress, "", false, "ReportedCommentsDatabase is not set"); return; } MusicCatalogueService catalogueService = new MusicCatalogueService(Configuration, context); RServiceResult <bool> musicCatalogueRes = await catalogueService.ImportFromMySql("MusicCatalogueImportFromMySql", jobProgressServiceEF, job); if (!musicCatalogueRes.Result) { return; } try { using (MySqlConnection connection = new MySqlConnection ( $"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" )) { connection.Open(); using (MySqlDataAdapter src = new MySqlDataAdapter( "SELECT ID, post_author, post_date, post_date_gmt, post_content, post_title, post_category, post_excerpt, post_status, comment_status, ping_status, post_password, post_name, to_ping, pinged, post_modified, post_modified_gmt, post_content_filtered, post_parent, guid, menu_order, post_type, post_mime_type, comment_count, " + "COALESCE((SELECT meta_value FROM ganja_postmeta WHERE post_id = ID AND meta_key='_wp_page_template'), '') AS template," + "(SELECT meta_value FROM ganja_postmeta WHERE post_id = ID AND meta_key='otherpoetid') AS other_poet_id " + "FROM ganja_posts", connection)) { using (DataTable srcData = new DataTable()) { job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, "phase 1 - mysql 1")).Result; await src.FillAsync(srcData); job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, "phase 1 - processing mysql data")).Result; foreach (DataRow row in srcData.Rows) { GanjoorPageType pageType = row["post_type"].ToString() == "post" && row["comment_status"].ToString() != "closed" ? GanjoorPageType.PoemPage : row["template"].ToString() == "comspage.php" ? GanjoorPageType.AllComments : row["template"].ToString() == "relations.php" ? GanjoorPageType.ProsodySimilars : row["template"].ToString() == "vazn.php" ? GanjoorPageType.ProsodyAndStats : GanjoorPageType.None; int?poetId = row["post_author"].ToString() == "1" ? (int?)null : int.Parse(row["post_author"].ToString()); if (poetId == 36)//رشحه { continue; } if (poetId != null) { if (!await context.GanjoorPoets.Where(poet => poet.Id == poetId).AnyAsync()) { continue; } } GanjoorPage page = new GanjoorPage() { Id = int.Parse(row["ID"].ToString()), GanjoorPageType = pageType, Published = true, PageOrder = -1, Title = row["post_title"].ToString(), UrlSlug = row["post_name"].ToString(), HtmlText = row["post_content"].ToString(), ParentId = row["post_parent"].ToString() == "0" ? (int?)null : int.Parse(row["post_parent"].ToString()), PoetId = poetId, SecondPoetId = row["other_poet_id"] == DBNull.Value ? (int?)null : int.Parse(row["other_poet_id"].ToString()), PostDate = (DateTime)row["post_date"] }; if (pageType == GanjoorPageType.PoemPage) { var poem = await context.GanjoorPoems.Where(p => p.Id == page.Id).FirstOrDefaultAsync(); if (poem == null) { continue; } page.PoemId = poem.Id; } if (poetId != null && pageType == GanjoorPageType.None) { GanjoorCat cat = await context.GanjoorCategories.Where(c => c.PoetId == poetId && c.ParentId == null && c.UrlSlug == page.UrlSlug).SingleOrDefaultAsync(); if (cat != null) { page.GanjoorPageType = GanjoorPageType.PoetPage; page.CatId = cat.Id; } else { cat = await context.GanjoorCategories.Where(c => c.PoetId == poetId && c.ParentId != null && c.UrlSlug == page.UrlSlug).SingleOrDefaultAsync(); if (cat != null) { page.GanjoorPageType = GanjoorPageType.CatPage; page.CatId = cat.Id; } } } context.GanjoorPages.Add(page); } } } } job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, "phase 1 - finalizing")).Result; await context.SaveChangesAsync(); job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, "phase 2 - pre fetch data")).Result; var orphanPages = await context.GanjoorPages.Include(p => p.Poem).Where(p => p.FullUrl == null).ToListAsync(); job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, "phase 2 - post fetch data")).Result; double count = orphanPages.Count; int i = 0; foreach (var page in orphanPages) { job = (await jobProgressServiceEF.UpdateJob(job.Id, i++, "phase 2")).Result; string fullUrl = page.UrlSlug; string fullTitle = page.Title; if (page.GanjoorPageType == GanjoorPageType.PoemPage) { fullTitle = page.Poem.FullTitle; fullUrl = page.Poem.FullUrl; } else { if (page.ParentId != null) { GanjoorPage parent = await context.GanjoorPages.Where(p => p.Id == page.ParentId).SingleAsync(); while (parent != null) { fullUrl = parent.UrlSlug + "/" + fullUrl; fullTitle = parent.Title + " » " + fullTitle; parent = parent.ParentId == null ? null : await context.GanjoorPages.Where(p => p.Id == parent.ParentId).SingleAsync(); } } else { GanjoorCat cat = await context.GanjoorCategories.Where(c => c.PoetId == page.PoetId && c.UrlSlug == page.UrlSlug).SingleOrDefaultAsync(); if (cat != null) { fullUrl = cat.FullUrl; while (cat.ParentId != null) { cat = await context.GanjoorCategories.Where(c => c.Id == cat.ParentId).SingleOrDefaultAsync(); if (cat != null) { fullTitle = cat.Title + " » " + fullTitle; } } } else { cat = await context.GanjoorCategories.Where(c => c.PoetId == page.PoetId && c.ParentId == null).SingleOrDefaultAsync(); if (cat != null) { fullUrl = $"{cat.UrlSlug}/{page.UrlSlug}"; } } } } if (!string.IsNullOrEmpty(fullUrl) && fullUrl.IndexOf('/') != 0) { fullUrl = $"/{fullUrl}"; } page.FullUrl = fullUrl; page.FullTitle = fullTitle; context.Update(page); } job = (await jobProgressServiceEF.UpdateJob(job.Id, job.Progress, "phase 2 - finalizing")).Result; await context.SaveChangesAsync(); job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, "phase 3 - pre mysql data fetch")).Result; using (MySqlConnection connection = new MySqlConnection ( $"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" )) { connection.Open(); using (MySqlDataAdapter src = new MySqlDataAdapter( "SELECT meta_key, post_id, meta_value FROM ganja_postmeta WHERE meta_key IN ( 'vazn', 'ravi', 'src', 'srcslug', 'oldtag' )", connection)) { job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, "phase 3 - mysql 2")).Result; using (DataTable srcData = new DataTable()) { await src.FillAsync(srcData); job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, "phase 3 - processing meta data")).Result; int r = 0; foreach (DataRow row in srcData.Rows) { job = (await jobProgressServiceEF.UpdateJob(job.Id, r++, "phase 3 - processing meta data")).Result; int poemId = int.Parse(row["post_id"].ToString()); var poem = await context.GanjoorPoems.Where(p => p.Id == poemId).FirstOrDefaultAsync(); if (poem == null) { continue; } string metaKey = row["meta_key"].ToString(); string metaValue = row["meta_value"].ToString(); switch (metaKey) { case "vazn": { GanjoorMetre metre = await context.GanjoorMetres.Where(m => m.Rhythm == metaValue).SingleOrDefaultAsync(); if (metre == null) { metre = new GanjoorMetre() { Rhythm = metaValue, VerseCount = 0 }; context.GanjoorMetres.Add(metre); await context.SaveChangesAsync(); } poem.GanjoorMetreId = metre.Id; } break; case "ravi": poem.RhymeLetters = metaValue; break; case "src": poem.SourceName = metaValue; break; case "srcslug": poem.SourceUrlSlug = metaValue; break; case "oldtag": poem.OldTag = metaValue; switch (poem.OldTag) { case "بدایع": poem.OldTagPageUrl = "/saadi/badaye"; break; case "خواتیم": poem.OldTagPageUrl = "/saadi/khavatim"; break; case "طیبات": poem.OldTagPageUrl = "/saadi/tayyebat"; break; case "غزلیات قدیم": poem.OldTagPageUrl = "/saadi/ghazaliyat-e-ghadim"; break; case "ملمعات": poem.OldTagPageUrl = "/saadi/molammaat"; break; } break; } context.GanjoorPoems.Update(poem); } } } } job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, "phase 3 - finalizing meta data")).Result; await context.SaveChangesAsync(); var resApprovedPoemSongs = await _ImportPoemSongsDataFromMySql("_ImportPoemSongsDataFromMySql", context, jobProgressServiceEF, job, true); if (!resApprovedPoemSongs.Result) { return; } var resPendingPoemSongs = await _ImportPoemSongsDataFromMySql("_ImportPoemSongsDataFromMySql", context, jobProgressServiceEF, job, false); if (!resPendingPoemSongs.Result) { return; } using (MySqlConnection connection = new MySqlConnection ( $"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" )) { connection.Open(); using (MySqlDataAdapter src = new MySqlDataAdapter( "SELECT poem_id, mimage_id FROM ganja_mimages", connection)) { job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, "phase N - mysql N")).Result; using (DataTable srcData = new DataTable()) { await src.FillAsync(srcData); job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, "phase N - processing meta data")).Result; int r = 0; foreach (DataRow row in srcData.Rows) { job = (await jobProgressServiceEF.UpdateJob(job.Id, r++, "phase N - processing meta data")).Result; int poemId = int.Parse(row["poem_id"].ToString()); Guid imageId = Guid.Parse(row["mimage_id"].ToString()); var link = await context.GanjoorLinks.Include(l => l.Item).ThenInclude(i => i.Images). Where(l => l.GanjoorPostId == poemId && l.Item.Images.First().Id == imageId) .FirstOrDefaultAsync(); if (link != null) { link.DisplayOnPage = true; context.GanjoorLinks.Update(link); } } } } } job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, "phase N - finalizing meta data")).Result; await context.SaveChangesAsync(); } catch (Exception jobExp) { await jobProgressServiceEF.UpdateJob(job.Id, job.Progress, "", false, jobExp.ToString()); } var resComments = await _ImportCommentsDataFromMySql("_ImportCommentsDataFromMySql", context, jobProgressServiceEF, job); if (!resComments.Result) { return; } await jobProgressServiceEF.UpdateJob(job.Id, 100, "Finished", true); } }); return(new RServiceResult <bool>(true)); } catch (Exception exp) { return(new RServiceResult <bool>(false, exp.ToString())); } }