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