public void EditWordSeries(SubmitWordsModel submitWords, int userId)
{
var modelForDelete = new WordOverviewModel
{
BookId = submitWords.Book.Id,
ChapterId = submitWords.Chapter.Id,
AddedBy = userId,
BaseLanguageId = submitWords.BaseLanguage.Id,
TargetLanguageId = submitWords.TargetLanguage.Id
};
var sql = @"
delete WordsToWords
FROM dbo.WordsToWords
left join dbo.words as wordsBase
on WordsToWords.BaseWordId = wordsBase.Id
left join dbo.words as wordsTarget
on WordsToWords.TargetWordId = wordsTarget.Id
where
WordsToWords.AddedBy = @AddedBy and
wordsBase.LanguageId = @BaseLanguageId and
WordsToWords.BookId = @BookId and
WordsToWords.ChapterId = @ChapterId and
wordsTarget.LanguageId = @TargetLanguageId
";
db.Query(sql, modelForDelete).ToList();
SubmitWordSeries(submitWords, userId);
}
public void SubmitWordSeries(SubmitWordsModel submitWords, int userId)
{
notFoundLanguageCodes = new List <NotFoundLanguageCode>();
var baseLanguage = db.Query <Language>(
@"select top 1 * from language where id = @Id", new { submitWords.BaseLanguage.Id })
.SingleOrDefault();
var targetLanguage = db.Query <Language>(
@"select top 1 * from language where id = @Id", new { submitWords.TargetLanguage.Id })
.SingleOrDefault();
foreach (var word in submitWords.Words)
{
List <int?> targetIds = new List <int?>();
foreach (var target in word.Targets)
{
var foundTargetWord = db.Query <Words>(
@"select top 1 * from Words where word = N'" + target.Value +
@"' and LanguageId = " + submitWords.TargetLanguage.Id)
.SingleOrDefault();
// Add word if it is not exist in table
if (foundTargetWord == null)
{
foundTargetWord = new Words();
var wordToAdd = new Words()
{
LanguageId = submitWords.TargetLanguage.Id,
Word = target.Value,
AddedBy = userId,
AddedDate = DateTime.Now,
};
wordToAdd.SpeechId = GetVoicFromText(new SpeechModel
{
Text = wordToAdd.Word,
Gender = SsmlVoiceGender.Female,
LanguageCode = targetLanguage.Code
}).Id;
var sql = @"
insert into [dbo].[words] (LanguageId, Word, AddedBy, AddedDate, SpeechId)
VALUES (@LanguageId, @Word, @AddedBy, @AddedDate, @SpeechId);SELECT CAST(SCOPE_IDENTITY() as int)";
foundTargetWord.Id = db.Query <int>(sql, wordToAdd).SingleOrDefault();
}
else if (foundTargetWord.SpeechId == 0)
{
var Speech = GetVoicFromText(new SpeechModel
{
Text = target.Value,
Gender = SsmlVoiceGender.Female,
LanguageCode = targetLanguage.Code
});
db.Query("update dbo.Words SET SpeechId = @SpeechId WHERE Id = @Id", new Words()
{
SpeechId = Speech.Id,
Id = foundTargetWord.Id
});
}
else if (foundTargetWord.SpeechId > 0)
{
var speech = db.Query <Speech>("select top 1 * from speech WHERE Id = @Id", new SpeechModel()
{
Id = foundTargetWord.SpeechId
}).SingleOrDefault();
CheckForSpeechInDisk(speech, target.Value, targetLanguage.Code, SsmlVoiceGender.Female);
}
targetIds.Add(foundTargetWord.Id);
}
var qry = @"select top 1 * from Words where word = N'" + word.Base.Value + "' and LanguageId = " + submitWords.BaseLanguage.Id;
var foundBaseWord = db.Query <Words>(qry)
.SingleOrDefault();
// Add word if it is not exist in table
if (foundBaseWord == null)
{
foundBaseWord = new Words();
var wordToAdd = new Words()
{
LanguageId = submitWords.BaseLanguage.Id,
Word = word.Base.Value,
AddedBy = userId,
AddedDate = DateTime.Now,
};
wordToAdd.SpeechId = GetVoicFromText(new SpeechModel
{
Text = wordToAdd.Word,
Gender = SsmlVoiceGender.Female,
LanguageCode = baseLanguage.Code
}).Id;
var sql = @"
insert into [dbo].[words] (LanguageId, Word, AddedBy, AddedDate, SpeechId)
VALUES (@LanguageId, @Word, @AddedBy, @AddedDate, @SpeechId);
SELECT CAST(SCOPE_IDENTITY() as int)";
foundBaseWord.Id = db.Query <int>(sql, wordToAdd).SingleOrDefault();
}
else if (foundBaseWord.SpeechId == 0)
{
var Speech = GetVoicFromText(new SpeechModel
{
Text = word.Base.Value,
Gender = SsmlVoiceGender.Female,
LanguageCode = baseLanguage.Code
});
db.Query("update dbo.Words SET SpeechId = @SpeechId WHERE Id = @Id", new Words()
{
SpeechId = Speech.Id,
Id = foundBaseWord.Id
});
}
else if (foundBaseWord.SpeechId > 0)
{
var speech = db.Query <Speech>("select top 1 * from speech WHERE Id = @Id", new SpeechModel()
{
Id = foundBaseWord.SpeechId
}).SingleOrDefault();
CheckForSpeechInDisk(speech, word.Base.Value, baseLanguage.Code, SsmlVoiceGender.Female);
}
foreach (var item in targetIds)
{
var modelToAdd = new WordToWord()
{
TargetWordId = (int)item,
BaseWordId = foundBaseWord.Id,
AddedBy = userId,
AddedDate = DateTime.Now,
ChapterId = submitWords.Chapter.Id,
BookId = submitWords.Book.Id
};
if (submitWords.Chapter.Id == 0)
{
modelToAdd.ChapterId = null;
}
if (submitWords.Book.Id == 0)
{
modelToAdd.BookId = null;
}
var sql = @"
insert into [dbo].[WordsToWords] (BaseWordId, TargetWordId, AddedBy, AddedDate, BookId, ChapterId)
VALUES (@BaseWordId, @TargetWordId, @AddedBy, @AddedDate, @BookId, @ChapterId)";
db.Query <int>(sql, modelToAdd).ToList();
}
}
}
