private async Task <RServiceResult <bool> > _DeleteComment(RMuseumDbContext context, int commentId)
{
GanjoorComment comment = await context.GanjoorComments.Where(c => c.Id == commentId).SingleOrDefaultAsync();
if (comment == null)
{
return(new RServiceResult <bool>(false)); //not found
}
foreach (var reply in await _FindReplies(context, comment))
{
await _DeleteComment(context, reply.Id);
}
context.GanjoorComments.Remove(comment);
await _context.SaveChangesAsync();
return(new RServiceResult <bool>(true));
}
/// <summary>
/// Apply corrections from sqlite
/// </summary>
/// <param name="poetId"></param>
/// <param name="file"></param>
/// <param name="note"></param>
/// <returns></returns>
public async Task <RServiceResult <bool> > ApplyCorrectionsFromSqlite(int poetId, IFormFile file, string note)
{
try
{
string dir = Path.Combine($"{Configuration.GetSection("PictureFileService")["StoragePath"]}", "SQLiteImports");
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
string filePath = Path.Combine(dir, file.FileName);
if (File.Exists(filePath))
{
File.Delete(filePath);
}
using (FileStream fsMain = new FileStream(filePath, FileMode.Create))
{
await file.CopyToAsync(fsMain);
}
string email = $"{Configuration.GetSection("Ganjoor")["SystemEmail"]}";
var userId = (await _appUserService.FindUserByEmail(email)).Result.Id;
_backgroundTaskQueue.QueueBackgroundWorkItem
(
async token =>
{
using (RMuseumDbContext context = new RMuseumDbContext(new DbContextOptions <RMuseumDbContext>())) //this is long running job, so _context might be already been freed/collected by GC
{
LongRunningJobProgressServiceEF jobProgressServiceEF = new LongRunningJobProgressServiceEF(context);
var job = (await jobProgressServiceEF.NewJob("ApplyCorrectionsFromSqlite", "Query data")).Result;
try
{
SqliteConnectionStringBuilder connectionStringBuilder = new SqliteConnectionStringBuilder();
connectionStringBuilder.DataSource = filePath;
using (SqliteConnection sqliteConnection = new SqliteConnection(connectionStringBuilder.ToString()))
{
await sqliteConnection.OpenAsync();
IDbConnection sqlite = sqliteConnection;
var poets = (await sqlite.QueryAsync("SELECT * FROM poet")).ToList();
if (poets.Count != 1)
{
await jobProgressServiceEF.UpdateJob(job.Id, 100, "", false, "poets count in sqlite db is not equal to 1");
}
int poemNumber = 0;
foreach (var poem in await sqlite.QueryAsync($"SELECT * FROM poem ORDER BY id"))
{
poemNumber++;
await jobProgressServiceEF.UpdateJob(job.Id, poemNumber, "", false);
int poemId = (int)poem.id;
GanjoorPoem dbPoem = await context.GanjoorPoems.Include(p => p.Cat).Where(p => p.Id == poemId).SingleOrDefaultAsync();
if (dbPoem == null)
{
continue;
}
if (dbPoem.Cat.PoetId != poetId)
{
continue;
}
string comment = $"<p>تغییرات حاصل از پردازش {note}</p>{Environment.NewLine}";
bool anyChanges = false;
var dbPage = await context.GanjoorPages.Where(p => p.Id == poemId).SingleOrDefaultAsync();
GanjoorPageSnapshot snapshot = new GanjoorPageSnapshot()
{
GanjoorPageId = poemId,
MadeObsoleteByUserId = (Guid)userId,
RecordDate = DateTime.Now,
Note = note,
Title = dbPage.Title,
UrlSlug = dbPage.UrlSlug,
HtmlText = dbPage.HtmlText,
};
string poemTitle = poem.title;
if (poemTitle != dbPoem.Title)
{
anyChanges = true;
comment += $"<p>تغییر عنوان از «{dbPoem.Title}» به «{poemTitle}»</p>{Environment.NewLine}";
dbPoem.Title = poemTitle;
dbPoem.FullTitle = $"{dbPoem.Cat.FullUrl} » {dbPoem.Title}";
context.GanjoorPoems.Update(dbPoem);
}
var sqliteVerses = new List <dynamic>(await sqlite.QueryAsync($"SELECT * FROM verse WHERE poem_id = {poem.id} ORDER BY vorder"));
var dbVerses = await context.GanjoorVerses.Where(v => v.PoemId == poemId).OrderBy(v => v.VOrder).ToListAsync();
int vIndex = 0;
while (vIndex < sqliteVerses.Count && vIndex < dbVerses.Count)
{
if (sqliteVerses[vIndex].vorder != dbVerses[vIndex].VOrder)
{
vIndex = -1;
break;
}
string text = sqliteVerses[vIndex].text;
text = text.Replace("ـ", "").Replace(" ", " ").ApplyCorrectYeKe().Trim();
if (text == dbVerses[vIndex].Text)
{
vIndex++;
continue;
}
comment += $"<p>تغییر مصرع {vIndex + 1} از «{dbVerses[vIndex].Text}» به «{text}»</p>{Environment.NewLine}".ToPersianNumbers();
dbVerses[vIndex].Text = text;
context.GanjoorVerses.Update(dbVerses[vIndex]);
anyChanges = true;
vIndex++;
}
if (vIndex != -1)
{
while (vIndex < dbVerses.Count)
{
comment += $"<p>حذف مصرع {vIndex + 1} با متن «{dbVerses[vIndex].Text}»</p>{Environment.NewLine}".ToPersianNumbers();
context.GanjoorVerses.Remove(dbVerses[vIndex]);
vIndex++;
anyChanges = true;
}
while (vIndex < sqliteVerses.Count)
{
string text = sqliteVerses[vIndex].text;
text = text.Replace("ـ", "").Replace(" ", " ").ApplyCorrectYeKe().Trim();
int vOrder = int.Parse(sqliteVerses[vIndex].vorder.ToString());
int position = int.Parse(sqliteVerses[vIndex].position.ToString());
comment += $"<p>اضافه شدن مصرع {vIndex + 1} با متن «{text}»</p>{Environment.NewLine}".ToPersianNumbers();
context.GanjoorVerses.Add
(
new GanjoorVerse()
{
PoemId = poemId,
VOrder = vOrder,
VersePosition = (VersePosition)position,
Text = text
}
);
vIndex++;
anyChanges = true;
}
if (anyChanges)
{
await _FillPoemCoupletIndices(context, poemId);
GanjoorComment sysComment = new GanjoorComment()
{
UserId = userId,
AuthorIpAddress = "127.0.0.1",
CommentDate = DateTime.Now,
HtmlComment = comment,
PoemId = poemId,
Status = PublishStatus.Published,
};
context.GanjoorComments.Add(sysComment);
context.GanjoorPageSnapshots.Add(snapshot);
await context.SaveChangesAsync();
var poemVerses = await context.GanjoorVerses.Where(v => v.PoemId == poemId).OrderBy(v => v.VOrder).ToListAsync();
bool needsNewVOrder = false;
for (int i = 0; i < poemVerses.Count; i++)
{
if (poemVerses[i].VOrder != (i + 1))
{
poemVerses[i].VOrder = i + 1;
needsNewVOrder = true;
}
}
if (needsNewVOrder)
{
context.GanjoorVerses.UpdateRange(poemVerses);
}
dbPoem.PlainText = PreparePlainText(poemVerses);
dbPoem.HtmlText = PrepareHtmlText(poemVerses);
dbPage.HtmlText = dbPoem.HtmlText;
dbPage.Title = dbPoem.Title;
dbPage.FullTitle = dbPoem.FullTitle;
try
{
var poemRhymeLettersRes = LanguageUtils.FindRhyme(poemVerses);
if (!string.IsNullOrEmpty(poemRhymeLettersRes.Rhyme))
{
dbPoem.RhymeLetters = poemRhymeLettersRes.Rhyme;
}
}
catch
{
}
context.GanjoorPoems.Update(dbPoem);
context.GanjoorPages.Update(dbPage);
await context.SaveChangesAsync();
}
}
}
await jobProgressServiceEF.UpdateJob(job.Id, 100, "", true);
}
}
catch (Exception exp)
{
await jobProgressServiceEF.UpdateJob(job.Id, 100, "", false, exp.ToString());
}
}
File.Delete(filePath);
}
);
}
catch (Exception exp)
{
return(new RServiceResult <bool>(false, exp.ToString()));
}
return(new RServiceResult <bool>(true));
}
private async Task <RServiceResult <bool> > _ImportCommentsDataFromMySql(string jobName, RMuseumDbContext context, LongRunningJobProgressServiceEF jobProgressServiceEF, RLongRunningJobStatus job)
{
try
{
job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, $"{jobName} - processing reported comments")).Result;
List <GanjoorCommentAbuseReport> reportedComments = await _MySqlImportReportedComments();
job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, $"{jobName} - pre mysql data fetch")).Result;
string connectionString =
$"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";
using (MySqlConnection connection = new MySqlConnection
(
connectionString
))
{
connection.Open();
using (MySqlDataAdapter src = new MySqlDataAdapter(
"SELECT comment_ID, comment_post_ID, comment_author, comment_author_email, comment_author_url, comment_author_IP, comment_date, comment_content, comment_approved FROM ganja_comments WHERE comment_type <> 'pingback' ORDER BY comment_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;
int count = data.Rows.Count;
int i = 0;
int percent = -1;
foreach (DataRow row in data.Rows)
{
GanjoorComment comment = new GanjoorComment()
{
PoemId = int.Parse(row["comment_post_ID"].ToString()),
AuthorName = row["comment_author"].ToString(),
AuthorEmail = row["comment_author_email"].ToString(),
AuthorUrl = row["comment_author_url"].ToString(),
AuthorIpAddress = row["comment_author_IP"].ToString(),
CommentDate = (DateTime)row["comment_date"],
HtmlComment = _PrepareCommentHtml(row["comment_content"].ToString()),
Status = row["comment_approved"].ToString() == "1" ? PublishStatus.Published : PublishStatus.Awaiting
};
var poem = await context.GanjoorPoems.Where(p => p.Id == comment.PoemId).SingleOrDefaultAsync();
if (poem == null)
{
continue;
}
context.GanjoorComments.Add(comment);
int originalCommentId = int.Parse(row["comment_post_ID"].ToString());
var complaints = reportedComments.Where(c => c.GanjoorCommentId == originalCommentId).ToList();
if (complaints.Count > 0)
{
await context.SaveChangesAsync(); //save this comment to make its ID valid
foreach (var complaint in complaints)
{
context.GanjoorReportedComments.Add
(
new GanjoorCommentAbuseReport()
{
GanjoorCommentId = comment.Id,
ReasonCode = complaint.ReasonCode,
ReasonText = complaint.ReasonText,
}
);
}
}
i++;
if (i * 100 / count > percent)
{
percent = i * 100 / count;
job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, $"{jobName} - {i} of {count}")).Result;
}
}
await context.SaveChangesAsync();
}
}
}
job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, $"{jobName} - assigning comments to users")).Result;
foreach (var user in await context.Users.ToListAsync())
{
foreach (var comment in await context.GanjoorComments.Where(u => u.AuthorEmail == user.Email).ToListAsync())
{
comment.UserId = user.Id;
context.GanjoorComments.Update(comment);
}
}
await context.SaveChangesAsync();
job = (await jobProgressServiceEF.UpdateJob(job.Id, 0, $"{jobName} - finished")).Result;
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 <List <GanjoorComment> > _FindReplies(RMuseumDbContext context, GanjoorComment comment)
{
return(await context.GanjoorComments.Where(c => c.InReplyToId == comment.Id).AsNoTracking().ToListAsync());
}
