/// <summary>
/// Возвращает слова для определенной группы
/// </summary>
/// <param name="userLanguages">язык</param>
/// <param name="groupId">идентификатор группы, для которой нужно получить слова</param>
/// <returns>список слов для группы</returns>
public List <SourceWithTranslation> GetWordsByGroup(UserLanguages userLanguages, long groupId)
{
long sourceLanguageId = userLanguages.From.Id;
long translationLanguageId = userLanguages.To.Id;
List <SourceWithTranslation> result = Adapter.ReadByContext(c => {
var wordsWithTranslationsQuery = (from w1 in c.Word
join wt in c.WordTranslation on w1.Id equals wt.WordId1
join gw in c.GroupWord on wt.Id equals gw.WordTranslationId
join w2 in c.Word on wt.WordId2 equals w2.Id
where gw.GroupId == groupId &&
((w1.LanguageId == sourceLanguageId &&
w2.LanguageId == translationLanguageId)
||
(w1.LanguageId == translationLanguageId &&
w2.LanguageId == sourceLanguageId))
orderby gw.Rating descending, gw.Id
select new { wt, w1, w2 });
List <SourceWithTranslation> innerResult =
wordsWithTranslationsQuery.AsEnumerable().Select(
e =>
ConverterEntities.ConvertToSourceWithTranslation(e.wt.Id, e.wt.Image, sourceLanguageId, e.w1,
e.w2)).
ToList();
return(innerResult);
});
return(result);
}
/// <summary>
/// Получает count случайных предложений с переводом для текущего пользователя
/// </summary>
/// <param name="userId">идентификатор пользователя</param>
/// <param name="sourceLanguageId">язык, с которого нужно переводить</param>
/// <param name="translationLanguageId">язык, на который нужно переводить</param>
/// <param name="count">кол-во предложений, которое необходимо получить</param>
/// <returns>>список случайных предложений</returns>
protected override List <SourceWithTranslation> GetSourceWithTranslations(long userId,
long sourceLanguageId,
long translationLanguageId,
int count)
{
List <SourceWithTranslation> result = Adapter.ReadByContext(c => {
var joinedSequence = (from w1 in c.Word
join wt in c.WordTranslation on w1.Id equals wt.WordId1
join w2 in c.Word on wt.WordId2 equals w2.Id
join sw in c.ShuffleWord on wt.Id equals sw.WordTranslationId
where (sw.UserId == userId && sw.IsShown == false &&
sw.Type == _shuffleType &&
(w1.Type & _wordType) == _wordType && (w2.Type & _wordType) == _wordType &&
((w1.LanguageId == sourceLanguageId && w1.Pronunciation != null &&
w2.LanguageId == translationLanguageId)
||
(w1.LanguageId == translationLanguageId &&
w2.LanguageId == sourceLanguageId && w2.Pronunciation != null)))
orderby sw.Id ascending
select new { sw.WordTranslationId, wt.Image, w1, w2 }).Take(count);
return
(joinedSequence.AsEnumerable().Select(
e => ConverterEntities.ConvertToSourceWithTranslation(e.WordTranslationId, e.Image, sourceLanguageId, e.w1, e.w2)).ToList());
});
return(result);
}
/// <summary>
/// Возвращает слова по типу популярности
/// </summary>
/// <param name="userLanguages">язык</param>
/// <param name="type">тип популярных слов</param>
/// <returns>список слов по типу</returns>
public List <SourceWithTranslation> GetWordsByType(UserLanguages userLanguages, PopularWordType type)
{
long sourceLanguageId = userLanguages.From.Id;
long translationLanguageId = userLanguages.To.Id;
var parsedType = (int)type;
List <SourceWithTranslation> result = Adapter.ReadByContext(c => {
var wordsWithTranslationsQuery = (from w1 in c.Word
join wt in c.WordTranslation on w1.Id equals wt.WordId1
join pw in c.PopularWord on wt.Id equals pw.WordTranslationId
join w2 in c.Word on wt.WordId2 equals w2.Id
where pw.Type == parsedType &&
((w1.LanguageId == sourceLanguageId &&
w2.LanguageId == translationLanguageId)
||
(w1.LanguageId == translationLanguageId &&
w2.LanguageId == sourceLanguageId))
orderby pw.Id
select new { wt, w1, w2 });
List <SourceWithTranslation> innerResult =
wordsWithTranslationsQuery.AsEnumerable().Select(
e =>
ConverterEntities.ConvertToSourceWithTranslation(e.wt.Id, e.wt.Image, sourceLanguageId, e.w1,
e.w2)).
ToList();
return(innerResult);
});
return(result);
}
protected override SourceWithTranslation GetBySourceAndTranslation(long userId,
long sourceId,
long translationId)
{
SourceWithTranslation result = Adapter.ReadByContext(c => {
var sourceWithTranslation = (from s1 in c.Sentence
join st in c.SentenceTranslation on s1.Id equals st.SentenceId1
join s2 in c.Sentence on st.SentenceId2 equals s2.Id
join ss in c.ShuffleSentence on st.Id equals ss.SentenceTranslationId
where (ss.UserId == userId &&
((st.SentenceId1 == sourceId &&
st.SentenceId2 == translationId)
||
(st.SentenceId2 == sourceId &&
st.SentenceId1 == translationId)))
select new { st.Id, st.Image, s1, s2 }).FirstOrDefault();
if (sourceWithTranslation == null)
{
return(null);
}
long sourceLanguageId = sourceWithTranslation.s1.Id == sourceId
? sourceWithTranslation.s1.LanguageId
: sourceWithTranslation.s2.LanguageId;
return(ConverterEntities.ConvertToSourceWithTranslation(sourceWithTranslation.Id,
sourceWithTranslation.Image,
sourceLanguageId,
sourceWithTranslation.s1,
sourceWithTranslation.s2));
});
return(result);
}
protected override List <SourceWithTranslation> GetNextById(long userId,
long id,
long sourceLanguageId,
long translationLanguageId,
int count)
{
List <SourceWithTranslation> result = Adapter.ReadByContext(c => {
long shuffleId = GetShuffleIdById(userId, id, c);
if (IdValidator.IsInvalid(shuffleId))
{
return(new List <SourceWithTranslation>(0));
}
var joinedSequence = (from s1 in c.Sentence
join st in c.SentenceTranslation on s1.Id equals st.SentenceId1
join s2 in c.Sentence on st.SentenceId2 equals s2.Id
join ss in c.ShuffleSentence on st.Id equals ss.SentenceTranslationId
where (ss.UserId == userId && ss.Id > shuffleId &&
((s1.LanguageId == sourceLanguageId &&
s2.LanguageId == translationLanguageId)
||
(s1.LanguageId == translationLanguageId &&
s2.LanguageId == sourceLanguageId)))
orderby ss.Id ascending
select new { st.Id, st.Image, ss, s1, s2 }).Take(count);
return
(joinedSequence.AsEnumerable().OrderBy(e => e.ss.Id).Select(
e => ConverterEntities.ConvertToSourceWithTranslation(e.Id, e.Image, sourceLanguageId, e.s1, e.s2)).
ToList());
});
return(result);
}
private SourceWithTranslation CreateSentencencesWithTranslation(SentenceType type,
PronunciationForUser source,
PronunciationForUser translation,
byte[] image = null,
int?rating = null)
{
SourceWithTranslation result = null;
Adapter.ActionByContext(context => {
Sentence sourceSentence = GetOrAddSentenceToContext(source.LanguageId, source.Text, context);
Sentence translationSentence = GetOrAddSentenceToContext(translation.LanguageId, translation.Text,
context);
context.SaveChanges();
if (IdValidator.IsInvalid(sourceSentence.Id) || IdValidator.IsInvalid(translationSentence.Id))
{
return;
}
var sentenceTranslation = new SentenceTranslation
{
SentenceId1 = sourceSentence.Id, SentenceId2 = translationSentence.Id
};
SetSentenceTranslation(sentenceTranslation, type, image, rating);
context.SentenceTranslation.Add(sentenceTranslation);
context.SaveChanges();
if (IdValidator.IsInvalid(sentenceTranslation.Id))
{
return;
}
result = ConverterEntities.ConvertToSourceWithTranslation(sentenceTranslation.Id, sentenceTranslation.Image, source.LanguageId,
sourceSentence,
translationSentence);
});
return(result);
}
public Dictionary <long, SourceWithTranslation> GetByTranslationsIds(List <long> wordsTrandlationsIds,
long sourceLanguageId,
long translationLanguageId)
{
return(Adapter.ReadByContext(c => {
var joinedSequence = (from w1 in c.Word
join wt in c.WordTranslation on w1.Id equals wt.WordId1
join w2 in c.Word on wt.WordId2 equals w2.Id
where (wordsTrandlationsIds.Contains(wt.Id) &&
((w1.LanguageId == sourceLanguageId &&
w2.LanguageId == translationLanguageId)
||
(w1.LanguageId == translationLanguageId &&
w2.LanguageId == sourceLanguageId)))
select new { wt.Id, wt.Image, w1, w2 });
return
joinedSequence.AsEnumerable().ToDictionary(e => e.Id,
e =>
ConverterEntities.ConvertToSourceWithTranslation(e.Id,
e.Image,
sourceLanguageId,
e.w1,
e.w2));
}));
}
/// <summary>
/// Из двух предложений создает предложение с переводом
/// </summary>
/// <param name="id">уникальный идентификатор предложения с переводом</param>
/// <param name="image">изображение для предложения с переводом</param>
/// <param name="sourceLanguageId">язык, с которого нужно переводить</param>
/// <param name="sentence1">первое предложение</param>
/// <param name="sentence2">второе предложение</param>
/// <returns>предложение с переводом</returns>
private static SourceWithTranslation ConvertToGroupSentenceWithTranslation(long id,
byte[] image,
long sourceLanguageId,
Sentence sentence1,
Sentence sentence2)
{
return(ConverterEntities.ConvertToSourceWithTranslation(id, image, sourceLanguageId, sentence1, sentence2));
}
/// <summary>
/// Создает слова для группы
/// </summary>
/// <param name="groupForUser">группа, к которой нужно добавить слово</param>
/// <param name="source">слово</param>
/// <param name="translation">перевод</param>
/// <param name="image">изображение для слова</param>
/// <param name="rating">рейтинг</param>
/// <returns>созданные слова для группы, или ничего</returns>
public SourceWithTranslation GetOrCreate(GroupForUser groupForUser,
PronunciationForUser source,
PronunciationForUser translation,
byte[] image,
int?rating)
{
var wordsQuery = new WordsQuery();
WordWithTranslation wordWithTranslation = wordsQuery.GetOrCreate(source, translation, image,
WordType.Default, null);
if (wordWithTranslation == null)
{
return(null);
}
SourceWithTranslation result = Adapter.ReadByContext(c => {
var wordsWithTranslationsQuery = (from w1 in c.Word
join wt in c.WordTranslation on w1.Id equals wt.WordId1
join gw in c.GroupWord on wt.Id equals gw.WordTranslationId
join w2 in c.Word on wt.WordId2 equals w2.Id
where gw.GroupId == groupForUser.Id && wt.Id == wordWithTranslation.Id
select new { gw, wt, w1, w2 });
var firstRecord = wordsWithTranslationsQuery.AsEnumerable().FirstOrDefault();
if (firstRecord == null)
{
return(null);
}
//сохранить возможно изменившийся рейтинг
GroupWord groupWord = firstRecord.gw;
groupWord.Rating = rating;
c.SaveChanges();
SourceWithTranslation innerResult = ConverterEntities.ConvertToSourceWithTranslation(firstRecord.wt.Id,
firstRecord.wt.
Image,
wordWithTranslation
.Source.
LanguageId,
firstRecord.w1,
firstRecord.w2);
return(innerResult);
});
if (result == null)
{
result = Create(groupForUser, wordWithTranslation, rating);
}
return(result);
}
/// <summary>
/// Создает слова по типу популярности
/// </summary>
/// <param name="source">слово</param>
/// <param name="translation">перевод</param>
/// <param name="type">тип популярности</param>
/// <returns>созданные слова для группы, или ничего</returns>
public SourceWithTranslation GetOrCreate(PronunciationForUser source,
PronunciationForUser translation,
PopularWordType type)
{
var wordsQuery = new WordsQuery();
WordWithTranslation wordWithTranslation = wordsQuery.GetOrCreate(source, translation, null, WordType.Default,
null);
if (wordWithTranslation == null)
{
return(null);
}
var parsedType = (int)type;
SourceWithTranslation result = Adapter.ReadByContext(c => {
var wordsWithTranslationsQuery = (from w1 in c.Word
join wt in c.WordTranslation on w1.Id equals wt.WordId1
join pw in c.PopularWord on wt.Id equals pw.WordTranslationId
join w2 in c.Word on wt.WordId2 equals w2.Id
where pw.Type == parsedType && wt.Id == wordWithTranslation.Id
select new { wt, w1, w2 });
var firstRecord = wordsWithTranslationsQuery.AsEnumerable().FirstOrDefault();
if (firstRecord == null)
{
return(null);
}
SourceWithTranslation innerResult = ConverterEntities.ConvertToSourceWithTranslation(firstRecord.wt.Id,
firstRecord.wt.
Image,
wordWithTranslation
.Source.
LanguageId,
firstRecord.w1,
firstRecord.w2);
return(innerResult);
});
if (result == null)
{
result = Create(wordWithTranslation, parsedType);
}
return(result);
}
public Dictionary <long, SourceWithTranslation> GetByTranslationsIds(List <long> sentencesTrandlationsIds,
long sourceLanguageId,
long translationLanguageId)
{
return(Adapter.ReadByContext(c => {
var joinedSequence = (from s1 in c.Sentence
join st in c.SentenceTranslation on s1.Id equals st.SentenceId1
join s2 in c.Sentence on st.SentenceId2 equals s2.Id
where (sentencesTrandlationsIds.Contains(st.Id) &&
((s1.LanguageId == sourceLanguageId &&
s2.LanguageId == translationLanguageId)
||
(s1.LanguageId == translationLanguageId &&
s2.LanguageId == sourceLanguageId)))
select new { st.Id, st.Image, s1, s2 });
return
joinedSequence.AsEnumerable().ToDictionary(e => e.Id,
e => ConverterEntities.ConvertToSourceWithTranslation(e.Id, e.Image, sourceLanguageId, e.s1, e.s2));
}));
}
public Dictionary <long, List <SourceWithTranslation> > GetForAllGroups(UserLanguages userLanguages)
{
long sourceLanguageId = userLanguages.From.Id;
long translationLanguageId = userLanguages.To.Id;
Dictionary <long, List <SourceWithTranslation> > result = Adapter.ReadByContext(c => {
var wordsWithTranslationsQuery = (from w1 in c.Word
join wt in c.WordTranslation on w1.Id equals wt.WordId1
join gw in c.GroupWord on wt.Id equals gw.WordTranslationId
join w2 in c.Word on wt.WordId2 equals w2.Id
where ((w1.LanguageId == sourceLanguageId &&
w2.LanguageId == translationLanguageId)
||
(w1.LanguageId == translationLanguageId &&
w2.LanguageId == sourceLanguageId))
orderby gw.GroupId, gw.Rating descending, gw.Id
select new { gw.GroupId, wt, w1, w2 });
var innerResult = new Dictionary <long, List <SourceWithTranslation> >();
foreach (var e in wordsWithTranslationsQuery.AsEnumerable())
{
long groupId = e.GroupId;
List <SourceWithTranslation> wordsInGroup;
if (!innerResult.TryGetValue(groupId, out wordsInGroup))
{
wordsInGroup = new List <SourceWithTranslation>();
innerResult.Add(groupId, wordsInGroup);
}
SourceWithTranslation groupWord = ConverterEntities.ConvertToSourceWithTranslation(e.wt.Id,
e.wt.Image,
sourceLanguageId,
e.w1, e.w2);
wordsInGroup.Add(groupWord);
}
return(innerResult);
});
return(result);
}
public List <SourceWithTranslation> GetByCount(UserLanguages userLanguages, int count)
{
long sourceLanguageId = userLanguages.From.Id;
long translationLanguageId = userLanguages.To.Id;
List <SourceWithTranslation> result = Adapter.ReadByContext(c => {
var joinedSequence = (from w1 in c.Word
join wt in c.WordTranslation on w1.Id equals wt.WordId1
join w2 in c.Word on wt.WordId2 equals w2.Id
where ((w1.Type & _wordType) == _wordType && (w2.Type & _wordType) == _wordType &&
((w1.LanguageId == sourceLanguageId && w1.Pronunciation != null &&
w2.LanguageId == translationLanguageId)
||
(w1.LanguageId == translationLanguageId &&
w2.LanguageId == sourceLanguageId && w2.Pronunciation != null)))
select new { wt.Id, wt.Image, w1, w2 }).Take(count);
return
(joinedSequence.AsEnumerable().Select(
e => ConverterEntities.ConvertToSourceWithTranslation(e.Id, e.Image, sourceLanguageId, e.w1, e.w2)).
ToList());
});
return(result);
}
public List <SourceWithTranslation> GetExact(long userId, long sourceId, long translationId)
{
if (IdValidator.IsValid(userId))
{
//пользователя знаем - может быть это его энтити - попробуем найти
List <SourceWithTranslation> result = GetUserEntities(userId, sourceId, translationId);
if (result.Count > 0)
{
return(result);
}
}
Tuple <long, byte[], PronunciationEntity, PronunciationEntity> sourceAndTranslationWithId =
GetBySourceAndTranslation(sourceId, translationId);
long id = sourceAndTranslationWithId.Item1;
PronunciationEntity source = sourceAndTranslationWithId.Item3;
PronunciationEntity translation = sourceAndTranslationWithId.Item4;
if (IdValidator.IsInvalid(id) || source == null || translation == null)
{
LoggerWrapper.LogTo(LoggerName.Errors).ErrorFormat(
"BaseRandomQuery.GetExact для пользователя {0} не удалось получить данные с идентификаторами {1} и {2}",
userId, sourceId, translationId);
return(null);
}
long sourceLanguageId = source.Id == sourceId ? source.LanguageId : translation.LanguageId;
SourceWithTranslation sentence = ConverterEntities.ConvertToSourceWithTranslation(id,
sourceAndTranslationWithId
.Item2,
sourceLanguageId, source,
translation);
sentence.IsCurrent = true;
return(new List <SourceWithTranslation> {
sentence
});
}
protected override SourceWithTranslation GetBySourceAndTranslation(long userId,
long sourceId,
long translationId)
{
SourceWithTranslation result = Adapter.ReadByContext(c => {
var sourceWithTranslation = (from w1 in c.Word
join wt in c.WordTranslation on w1.Id equals wt.WordId1
join w2 in c.Word on wt.WordId2 equals w2.Id
join sw in c.ShuffleWord on wt.Id equals sw.WordTranslationId
where (sw.UserId == userId &&
sw.Type == _shuffleType &&
(w1.Type & _wordType) == _wordType &&
(w2.Type & _wordType) == _wordType &&
((wt.WordId1 == sourceId &&
wt.WordId2 == translationId)
||
(wt.WordId2 == sourceId &&
wt.WordId1 == translationId)))
orderby sw.Id descending
select new { sw.WordTranslationId, wt.Image, w1, w2 }).FirstOrDefault();
if (sourceWithTranslation == null)
{
return(null);
}
long sourceLanguageId = sourceWithTranslation.w1.Id == sourceId
? sourceWithTranslation.w1.LanguageId
: sourceWithTranslation.w2.LanguageId;
return(ConverterEntities.ConvertToSourceWithTranslation(sourceWithTranslation.WordTranslationId,
sourceWithTranslation.Image, sourceLanguageId,
sourceWithTranslation.w1, sourceWithTranslation.w2));
});
return(result);
}
public List <SourceWithTranslation> GetByCount(UserLanguages userLanguages, SentenceType type, int count)
{
long sourceLanguageId = userLanguages.From.Id;
long translationLanguageId = userLanguages.To.Id;
var convertedType = (int)type;
List <SourceWithTranslation> result = Adapter.ReadByContext(c => {
var joinedSequence = (from s1 in c.Sentence
join st in c.SentenceTranslation on s1.Id equals st.SentenceId1
join s2 in c.Sentence on st.SentenceId2 equals s2.Id
where (st.Type & convertedType) == convertedType &&
((s1.LanguageId == sourceLanguageId &&
s2.LanguageId == translationLanguageId)
||
(s1.LanguageId == translationLanguageId &&
s2.LanguageId == sourceLanguageId))
select new { st.Id, st.Image, s1, s2 }).Take(count);
return
(joinedSequence.AsEnumerable().Select(
e => ConverterEntities.ConvertToSourceWithTranslation(e.Id, e.Image, sourceLanguageId, e.s1, e.s2)).
ToList());
});
return(result);
}
/// <summary>
/// Получает группу сравнения по названию
/// </summary>
/// <param name="userLanguages">языковые настройки пользователя</param>
/// <param name="title">название представления</param>
/// <returns>представление или null если не найдено</returns>
public ComparisonForUser GetWithFullInfo(UserLanguages userLanguages, string title)
{
long sourceLanguageId = userLanguages.From.Id;
long translationLanguageId = userLanguages.To.Id;
ComparisonForUser result = Adapter.ReadByContext(c => {
var comparisonsQuery = (from gc in c.GroupComparison
join ci in c.ComparisonItem on gc.Id equals ci.GroupComparisonId
join cr in c.ComparisonRule on ci.Id equals cr.ComparisonItemId
join cre in c.ComparisonRuleExample on cr.Id equals cre.ComparisonRuleId
join st in c.SentenceTranslation on cre.SentenceTranslationId equals st.Id
join s1 in c.Sentence on st.SentenceId1 equals s1.Id
join s2 in c.Sentence on st.SentenceId2 equals s2.Id
where gc.Title == title && gc.LanguageId == _languageId &&
((s1.LanguageId == sourceLanguageId &&
s2.LanguageId == translationLanguageId)
||
(s1.LanguageId == translationLanguageId &&
s2.LanguageId == sourceLanguageId))
orderby ci.Order, cr.Order, cre.Order
select new { gc, ci, cr, cre, st, s1, s2 });
var comparisonsInfos = comparisonsQuery.AsEnumerable();
var firstComparison = comparisonsInfos.FirstOrDefault();
if (firstComparison == null)
{
return(null);
}
var innerResult = new ComparisonForUser(firstComparison.gc);
long prevComparisonItemId = IdValidator.INVALID_ID;
long prevComparisonRuleId = IdValidator.INVALID_ID;
ComparisonItemForUser comparisonItemForUser = null;
ComparisonRuleForUser comparisonRuleForUser = null;
foreach (var comparisonInfo in comparisonsInfos)
{
ComparisonItem comparisonItem = comparisonInfo.ci;
if (prevComparisonItemId != comparisonItem.Id)
{
prevComparisonItemId = comparisonItem.Id;
if (comparisonItemForUser != null)
{
innerResult.AddItem(comparisonItemForUser);
}
comparisonItemForUser = new ComparisonItemForUser(comparisonItem);
}
ComparisonRule comparisonRule = comparisonInfo.cr;
if (comparisonRule.Id != prevComparisonRuleId)
{
prevComparisonRuleId = comparisonRule.Id;
comparisonRuleForUser = new ComparisonRuleForUser(comparisonRule);
comparisonItemForUser.AddRule(comparisonRuleForUser);
}
SourceWithTranslation sourceWithTranslation =
ConverterEntities.ConvertToSourceWithTranslation(comparisonInfo.st.Id,
comparisonInfo.st.Image,
comparisonInfo.s1.LanguageId,
comparisonInfo.s1,
comparisonInfo.s2);
sourceWithTranslation.IsCurrent = false;
var example = new ComparisonRuleExampleForUser(comparisonInfo.cre, sourceWithTranslation);
comparisonRuleForUser.AddExample(example);
}
if (comparisonItemForUser != null)
{
innerResult.AddItem(comparisonItemForUser);
}
return(innerResult);
});
return(result);
}
