public static bool AnyBought()
{
using (var db = new DataBase())
{
return db.Lessons.Any(x => x.IsBought);
}
}
public static IQueryable<Lesson> getAll()
{
using (var db = new DataBase())
{
return db.Lessons.AsQueryable();
}
}
public static Word AddNewWord()
{
try
{
lock (lockdb)
{
using (var db = new DataBase())
{
Word w = db.Words.Where(x => x.Level == 0
&& x.Lesson.IsBought)
.OrderBy(x => x.LessonId)
.FirstOrDefault();
if (w != null)
{
w.Level = 1;
db.SubmitChanges();
}
return w;
}
}
}
catch (Exception ex)
{
return null;
}
}
public static Phrase AddNewPhrase()
{
try
{
lock (lockdb)
{
using (var db = new DataBase())
{
Phrase w = db.Phrases.Where(x => x.Level == 0
&& x.Lesson.IsBought)
.OrderBy(x => x.LessonId)
.FirstOrDefault();
//Phrase w = db.Phrases.Where(x => x.Level == 0
//&& x.Lesson.IsBought)
//.OrderBy(x => x.LessonId)
//.ThenBy(x=>x.EPhrase)
//.FirstOrDefault();
if(w !=null)
{
w.Level = 1;
db.SubmitChanges();
}
return w;
}
}
}
catch (Exception ex)
{
return null;
}
}
public static List<wordAndPhrase> Learned()
{
value v;
using (var db = new DataBase())
{
return db.Lessons
.Where(x=>x.IsBought==true)
.Select(x =>
new wordAndPhrase()
{
Phrase = new value()
{
val = db.Phrases.Where(y => y.LessonId == x.LessonId && y.Level > 1).Count()
,
max = db.Phrases.Where(y => y.LessonId == x.LessonId).Count()
},
Word = new value()
{
val = db.Words.Where(y => y.LessonId == x.LessonId && y.Level > 1).Count()
,
max = db.Words.Where(y => y.LessonId == x.LessonId).Count()
},
Name =x.Name,
lesson=x.LessonId
})
.OrderByDescending(x => x.lesson)
.ToList();
}
}
public static void Create()
{
try{
using (var db = new DataBase())
{
if(!db.DatabaseExists() || AppSettings.GetVersion() !="v1.0")
{
db.DeleteDatabase();
//db.CreateDatabase();
//createfordebug();
CopyDataBase();
AppSettings.SetSound(false);
AppSettings.SetPro(false);
AppSettings.SetVersion("v1.0");
}
}
}
catch(Exception ex)
{
}
}
public void GetLevelWithElements3Test()
{
DataBase.Create();
using (var b = new DataBase())
{
var temp = b.Lessons;
Word w;
int i;
foreach (var t in temp)
{
i = 5;
w = new Word() { Level = i++ };
t.AttachWord(w);
b.Words.InsertOnSubmit(w);
w = new Word() { Level = i++ };
t.AttachWord(w);
b.Words.InsertOnSubmit(w);
w = new Word() { Level = i++ };
t.AttachWord(w);
b.Words.InsertOnSubmit(w);
w = new Word() { Level = i++ };
t.AttachWord(w);
b.Words.InsertOnSubmit(w);
}
b.SubmitChanges();
Assert.AreEqual(5, WordEngine.GetLevelWithElements());
}
}
public static List<Lesson> AvailableLessons()
{
using (var db = new DataBase())
{
return db.Lessons //.Where(x => x.IsBought == false)
.ToList();
}
}
public static value TotalLearned()
{
value v;
using (var db = new DataBase())
{
v.max = db.Words.Count();
v.val = db.Words.Where(x => x.Level > 1).Count();
return v;
}
}
public static List<DictionaryItem> Words(string word)
{
using (var db = new DataBase())
{
return db.Words
.AsEnumerable()
.OrderByDescending(x => word.FuzzySearch(x.EWord))
.Take(20)
.Select(x => new DictionaryItem() { english = x.EWord, russian = CutWord(x.RWord) })
.ToList();
}
}
public static int CountElementsInLevels(int min, int max)
{
try{
using (var db = new DataBase())
{
return db.Words.Where(x => x.Level >= min && x.Level <= max).Count();
}
}
catch(Exception ex)
{
return int.MaxValue;
}
}
public static int CountElementsInLevel(int val)
{
try
{
using (var db = new DataBase())
{
return db.Phrases.Where(x => x.Level == val).Count();
}
}
catch (Exception ex)
{
return int.MaxValue;
}
}
public static bool AnyElementsInLevel(int i)
{
try
{
using (var db = new DataBase())
{
return db.Phrases.Any(x => x.Level == i);
}
}
catch (Exception ex)
{
return false;
}
}
public static void Buy(string Key)
{
// s.Buy(Key);
using (var db = new DataBase())
{
var les = db.Lessons.FirstOrDefault(x => x.Key == Key);
if(les != null)
{
les.IsBought = !les.IsBought;
db.SubmitChanges();
Init();
}
}
}
public void InitializeTest()
{
DataBase.Create();
using(var db = new DataBase())
{
var z =db.Lessons.Where(x => x.Key == "Series1").First();
Assert.AreEqual(true, z.IsBought);
z.IsBought = false;
db.SubmitChanges();
Assert.AreEqual(false, db.Lessons.Where(x => x.Key == "Series1").First().IsBought);
}
LessonsManager.Initilize();
using (var db = new DataBase())
{
var z = db.Lessons.Where(x => x.Key == "Series1").First();
Assert.AreEqual(true, z.IsBought);
}
}
public void DettachWordTest()
{
DataBase.Create();
using (var db = new DataBase())
{
db.Lessons.InsertOnSubmit(new Lesson() { LessonId = 1, Name = "lesson" });
db.SubmitChanges();
var w = new Word() { WordId = 1, EWord = "go" };
var l = db.Lessons.First();
l.AttachWord(w);
db.Words.InsertOnSubmit(w);
db.SubmitChanges();
Assert.AreNotEqual(null, w.Lesson);
l.DetachWord(w);
Assert.AreEqual(null, w.Lesson);
}
}
public void DettachPhraseTest()
{
DataBase.Create();
using (var db = new DataBase())
{
db.Lessons.InsertOnSubmit(new Lesson() { LessonId = 1, Name = "lesson" });
db.SubmitChanges();
var w = new Phrase() { PhraseId = 1 };
var l = db.Lessons.First();
l.AttacPhrase(w);
db.Phrases.InsertOnSubmit(w);
db.SubmitChanges();
Assert.AreNotEqual(null, w.Lesson);
l.DetachPhrase(w);
Assert.AreEqual(null, w.Lesson);
}
}
public void AddNewWordTest2()
{
DataBase.Create();
using (var b = new DataBase())
{
var temp = b.Lessons;
Word w;
int i;
foreach (var t in temp)
{
i = 0;
w = new Word() { Level = 0 };
t.AttachWord(w);
b.Words.InsertOnSubmit(w);
w = new Word() { Level = 0 };
t.AttachWord(w);
b.Words.InsertOnSubmit(w);
w = new Word() { Level = 0 };
t.AttachWord(w);
b.Words.InsertOnSubmit(w);
w = new Word() { Level = 0 };
t.AttachWord(w);
b.Words.InsertOnSubmit(w);
}
b.SubmitChanges();
var l = b.Lessons.First(x => x.LessonId == 1);
l.IsBought = false;
// b.Lessons.InsertOnSubmit(l);
b.SubmitChanges();
Assert.IsFalse(b.Words.Any(x => x.Level == 1));
//l = b.Lessons.First(x => x.LessonId == 3);
//l.IsBought = true;
// b.Lessons.InsertOnSubmit(l);
b.SubmitChanges();
WordRepository.AddNewWord();
Assert.AreEqual(0, b.Words.Count(x => x.Level == 1 && x.LessonId == 3));
}
}
internal static void Save(Phrase phrase)
{
try
{
using (var db = new DataBase())
{
Phrase w = db.Phrases.First(x => x.PhraseId == phrase.PhraseId);
w.Right = phrase.Right;
w.Level = phrase.Level;
w.Count = phrase.Count;
db.SubmitChanges();
}
}
catch (Exception ex)
{
}
}
internal static int LevelWitheMaxCountElements()
{
try
{
using (var db = new DataBase())
{
return db.Phrases.Where(x => x.Level > 0)
.GroupBy(x => x.Level)
.First(x => x.Count() == db.Phrases
.Where(q => q.Level > 0)
.GroupBy(y => y.Level)
.Max(z => z.Count())).Key;
}
}
catch (Exception ex)
{
return 1;
}
}
public static void SetLevel(int Lesson, int maxLearningLevel)
{
try
{
using (var db = new DataBase())
{
var phrases = db.Phrases.Where(x => x.Lesson.LessonId == Lesson);
foreach (var phrase in phrases)
phrase.Level = maxLearningLevel;
db.SubmitChanges();
}
}
catch (Exception ex)
{
}
}
public static void Save()
{
try{
using (var db = new DataBase())
{
db.SubmitChanges();
}
}
catch (Exception ex)
{
}
}
public static Phrase GetElement(int Id)
{
try
{
using (var db = new DataBase())
{
return db.Phrases
.Where(x => x.PhraseId == Id)
.First();
}
}
catch (Exception ex)
{
return null;
}
}
static void createfordebug()
{
using (var db = new DataBase())
{
var les = new List<Lesson>();
int i = 1;
les.Add(new Lesson() { LessonId = i++, Description = "The One With the East German Laundry Detergent", IsBought = true, Key = "Series1", Name = "1 сезон 1 серия" });
les.Add(new Lesson() { LessonId = i++, Description = "One time You have then bee", IsBought = false, Key = "Series2", Name = "1 сезон 2 серия" });
les.Add(new Lesson() { LessonId = i++, Description = "One time You have then bee", IsBought = false, Key = "Series3", Name = "1 сезон 3 серия" });
les.Add(new Lesson() { LessonId = i++, Description = "One time You have then bee", IsBought = false, Key = "Series4", Name = "1 сезон 4 серия" });
les.Add(new Lesson() { LessonId = i++, Description = "One time You have then bee", IsBought = false, Key = "Series5", Name = "1 сезон 5 серия" });
les.Add(new Lesson() { LessonId = i++, Description = "One time You have then bee", IsBought = false, Key = "Series6", Name = "1 сезон 6 серия" });
les.Add(new Lesson() { LessonId = i++, Description = "One time You have then bee", IsBought = false, Key = "Series7", Name = "1 сезон 7 серия" });
les.Add(new Lesson() { LessonId = i++, Description = "One time You have then bee", IsBought = false, Key = "Series8", Name = "1 сезон 8 серия" });
les.Add(new Lesson() { LessonId = i++, Description = "One time You have then bee", IsBought = false, Key = "Series9", Name = "1 сезон 9 серия" });
db.Lessons.InsertAllOnSubmit(les);
db.SubmitChanges();
var temp = db.Lessons;
Word w =new Word();
foreach (var t in temp)
{
for(int j=0; j<2;j++)
{
w = new Word() { Level = 0, EWord=GetRandomString(4), RWord=GetRandomString(5), Count=0, EPhrase=GetRandomString(10), RPhrase=GetRandomString(10), Transcription=GetRandomString(10), Right=0 };
t.AttachWord(w);
db.Words.InsertOnSubmit(w);
}
}
Phrase ph;
foreach (var t in temp)
{
for (int j = 0; j < 2; j++)
{
ph = new Phrase() { Level = 0, Count = 0, EPhrase = GetRandomString(40), RPhrase = GetRandomString(40), Right = 0 };
t.AttacPhrase(ph);
db.Phrases.InsertOnSubmit(ph);
}
}
db.SubmitChanges();
}
}
public static int[] CountElementsInLevels()
{
try
{
using (var db = new DataBase())
{
var temp = db.Phrases
.GroupBy(x => x.Level)
.ToDictionary(x=>x.Key, x=>x.Count());
return Enumerable.Range(0, 6)
.Select(x => temp.ContainsKey(x)?temp[x]:0)
.ToArray();
}
}
catch (Exception ex)
{
return new int[5];
}
}
public void IdLevelWitheMaxCountElementsTest()
{
DataBase.Create();
using (var b = new DataBase())
{
b.DeleteDatabase();
b.CreateDatabase();
b.Lessons.InsertOnSubmit(new Lesson());
b.Lessons.InsertOnSubmit(new Lesson());
b.Lessons.InsertOnSubmit(new Lesson());
b.Lessons.InsertOnSubmit(new Lesson());
b.Lessons.InsertOnSubmit(new Lesson());
b.SubmitChanges();
var temp = b.Lessons;
Word w;
int i;
foreach (var t in temp)
{
i = 2;
w = new Word() { Level = i++};
t.AttachWord(w);
b.Words.InsertOnSubmit(w);
w = new Word() { Level = i++ };
t.AttachWord(w);
b.Words.InsertOnSubmit(w);
w = new Word() { Level = i };
t.AttachWord(w);
b.Words.InsertOnSubmit(w);
w = new Word() { Level = i++ };
t.AttachWord(w);
b.Words.InsertOnSubmit(w);
w = new Word() { Level = i++ };
t.AttachWord(w);
b.Words.InsertOnSubmit(w);
}
b.SubmitChanges();
Assert.AreEqual(4, WordRepository.LevelWitheMaxCountElements());
}
}
public void CountElementsInLevelsTest()
{
DataBase.Create();
using(var b = new DataBase())
{
var temp = b.Lessons;
Word w;
int i;
foreach(var t in temp)
{
i = 0;
w = new Word() { Level = i++};
t.AttachWord(w);
b.Words.InsertOnSubmit(w);
w = new Word() { Level = i++ };
t.AttachWord(w);
b.Words.InsertOnSubmit(w);
w = new Word() { Level = i++ };
t.AttachWord(w);
b.Words.InsertOnSubmit(w);
w = new Word() { Level = i++ };
t.AttachWord(w);
b.Words.InsertOnSubmit(w);
}
b.SubmitChanges();
Assert.AreEqual(18,WordRepository.CountElementsInLevels(1, 2));
}
}
public static Phrase GetElement(int level,int index)
{
try
{
using (var db = new DataBase())
{
return db.Phrases.Where(x => x.Level == level)
// .OrderBy(x=>x.PhraseId)
.Skip(index)
.FirstOrDefault();
}
}
catch (Exception ex)
{
return null;
}
}
public void GetElementTest()
{
DataBase.Create();
using (var b = new DataBase())
{
var temp = b.Lessons;
Word w;
int i;
foreach (var t in temp)
{
i = 0;
w = new Word() { Level = i++ };
t.AttachWord(w);
b.Words.InsertOnSubmit(w);
w = new Word() { Level = i++ };
t.AttachWord(w);
b.Words.InsertOnSubmit(w);
w = new Word() { Level = i++ };
t.AttachWord(w);
b.Words.InsertOnSubmit(w);
w = new Word() { Level = i++ };
t.AttachWord(w);
b.Words.InsertOnSubmit(w);
}
b.SubmitChanges();
Word wor = WordRepository.GetElement(1, 8);
Assert.AreEqual(1, wor.Level);
Assert.AreEqual(9, wor.LessonId);
}
}
public static void Create()
{
try
{
using (var db = new DataBase2())
{
List<Word> words,words2;
List<Phrase> phrases,phrases2;
List<Lesson> lessons,lessons2;
if (db.DatabaseExists())
db.DeleteDatabase();
db.CreateDatabase();
using (var z = new DataBase())
{
words = z.Words.Select(x=>x).ToList().Select(x => new Word() {
Count=x.Count,
WordId=x.WordId,
RWord=x.RWord,
C_version= x.C_version,
EPhrase=x.EPhrase,
EWord=x.EWord,
Lesson=null,
LessonId=x.LessonId,
Level=x.Level,
Right=x.Right,
RPhrase=x.RPhrase,
Transcription=x.Transcription}).ToList();
phrases = z.Phrases.Select(x=>x).ToList().
Select(x => new Phrase()
{ PhraseId=x.PhraseId,
C_version=x.C_version,
Count=x.Count,
EPhrase=x.EPhrase,
Lesson =null,
LessonId =x.LessonId,
Level =x.Level,
Right =x.Right,
RPhrase =x.RPhrase
}).ToList();
lessons = z.Lessons.Select(x=>x).ToList().
Select(x => new Lesson()
{
C_version= x.C_version,
Description = x.Description,
IsBought =x.IsBought,
Key =x.Key,
LessonId =x.LessonId,
Name =x.Name,
}).ToList();
words2 = z.Words.ToList();
phrases2 = z.Phrases.ToList();
lessons2 = z.Lessons.ToList();
//z.Words.DeleteAllOnSubmit(words2);
//z.Lessons.DeleteAllOnSubmit(lessons2);
//z.Phrases.DeleteAllOnSubmit(phrases2);
//z.SubmitChanges();
//z.DeleteDatabase();
}
using(var z = new DataBase())
{
// db.CreateDatabase();
words = words.Where(x => !String.IsNullOrEmpty(x.RWord.Trim())).ToList();
words = words.Select(x => new { z = Guid.NewGuid(), x }).OrderBy(x => x.z).Select(x => x.x).ToList();
phrases = phrases.Select(x => new { z = Guid.NewGuid(), x }).OrderBy(x => x.z).ToList().Select(x => x.x).ToList();
int i=1;
words.ForEach((x) => x.WordId = i++);
i = 1;
phrases.ForEach(x => x.PhraseId = i++);
i = 1;
db.Lessons.InsertAllOnSubmit(lessons);
db.SubmitChanges();
var temp = db.Lessons;
foreach (var l in temp)
{
foreach (var w in words)
{
if (w.LessonId == l.LessonId)
l.AttachWord(w);
}
foreach (var p in phrases)
{
if (p.LessonId == l.LessonId)
l.AttacPhrase(p);
}
}
db.Words.InsertAllOnSubmit(words);
db.Phrases.InsertAllOnSubmit(phrases);
db.SubmitChanges();
}
}
}
catch (Exception ex)
{
}
}