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