public static string GetLexiconHandle(this Journals j)
{
using (var db = new CVDBContext())
{
var query = from lh in db.Lexicon_Handles
where lh.Lexicon_Handle_ID == j.Lexicon_Handle_ID
select lh;
var obj = query.FirstOrDefault();
return obj.First_Name.CapitalizeFirst() + " " + obj.Last_Name.CapitalizeFirst();
}
}
public void FillJournals(int i)
{
using (var db = new CVDBContext())
{
var query = from j in db.Journals
where j.User_ID == i
select new { Journal_ID = j.Journal_ID, Created = j.Created, Notes = j.Notes };
var tmp = query.ToList();
Page1 Page1 = new Page1();
Page1.DGJournals.ItemsSource = tmp;
}
}
public void FillGrdEdu()
{
try
{
using (var db = new CVDBContext())
{
var query = from rel in db.User_EDU_REL
join e in db.Educations on rel.EDU_ID equals e.EDU_ID
where rel.User_ID == uid
select new { e.EDU_ID, e.School, e.Course, e.Degree, e.Year };
GrdEdu.ItemsSource = query.ToList();
}
GrdEdu.SelectedIndex = -1;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ex.InnerException, "fill grid error");
}
}
private void BtnDeleteEdu_Click(object sender, RoutedEventArgs e)
{
try
{
using (var db = new CVDBContext())
{
var query = from rel in db.User_EDU_REL
where rel.User_ID == uid && rel.EDU_ID == IDtoshow
select rel;
User_EDU_REL uerToDelete = query.FirstOrDefault();
db.User_EDU_REL.Remove(uerToDelete);
db.SaveChanges();
//Educations eduToDelete = db.Educations.Find(IDtoshow);
//db.Educations.Remove(eduToDelete);
//db.SaveChanges();
FillGrdEdu();
ClearFields();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ex.InnerException, "delete user edu relation error");
}
}
private async Task<int?> Save_Ref()
{
//return 1;
//int tmp = 0;
//if (TxtbxCompany.Text.Length > 0)
//{
// tmp = await SaveCompany(TxtbxCompany.Text);
//}
try
{
using (var db = new CVDBContext())
{
int? tmp = null;
Employment_Histories e = db.Employment_Histories.Find(emp_hisID);
if (e != null)
{
tmp = e.REF_ID;
refID = e.REF_ID;
}
if (tmp < 1 || tmp == null)
{
if (TxtbxCompanyRef.Text.Length > 0 &&
TxtbxEmailRef.Text.Length > 0 &&
TxtbxFirstNameRef.Text.Length > 0 &&
TxtbxLastNameRef.Text.Length > 0 &&
TxtbxMobileRef.Text.Length > 0 &&
TxtbxPhoneRef.Text.Length > 0 &&
TxtbxTitleRef.Text.Length > 0)
{
return null;
}
else
{
User_References ur = new User_References
{
Company_ID = coID, //await SaveCompany(TxtbxCompany.Text),
First_Name = TxtbxFirstNameRef.Text,
Last_Name = TxtbxLastNameRef.Text,
Title = TxtbxTitleRef.Text,
Phone = TxtbxPhoneRef.Text,
Mobile = TxtbxMobileRef.Text,
Email = TxtbxEmailRef.Text
};
db.User_References.Add(ur);
db.Entry(ur).State = EntityState.Added;
await db.SaveChangesAsync();
PopupAddRef.IsOpen = false;
return ur.REF_ID;
}
}
User_References u_r = db.User_References.Find(refID);
u_r.First_Name = TxtbxFirstNameRef.Text;
u_r.Last_Name = TxtbxLastNameRef.Text;
u_r.Title = TxtbxTitleRef.Text;
u_r.Phone = TxtbxPhoneRef.Text;
u_r.Mobile = TxtbxMobileRef.Text;
u_r.Email = TxtbxEmailRef.Text;
db.Entry(u_r).State = EntityState.Modified;
db.SaveChanges();
return u_r.REF_ID;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ex.InnerException, "Save Error");
return null;
}
}
private async void BtnAddEdu_Click(object sender, RoutedEventArgs e)
{
int tmp = 0;
try
{
tmp = await AddEdu();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ex.InnerException, "save education error");
}
//eduID = eduToAdd.EDU_ID;
//eduID = eduToAdd.GetEduID();
try
{
using (var db = new CVDBContext())
{
var query = from rel in db.User_EDU_REL
where rel.User_ID == uid && rel.EDU_ID == tmp
select rel.User_EDU_ID;
int i = query.FirstOrDefault();
User_EDU_REL uer = db.User_EDU_REL.Find(i);
if (uer == null)
{
uer = new User_EDU_REL
{
User_ID = uid,
EDU_ID = tmp
};
db.User_EDU_REL.Add(uer);
db.Entry(uer).State = EntityState.Added;
}
else
{
db.Entry(uer).State = EntityState.Modified;
}
db.SaveChanges();
FillGrdEdu();
ClearFields();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ex.InnerException, "save user edu relationship error");
}
}
private void GrdEdu_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
try
{
BtnDeleteEdu.IsEnabled = false;
if (GrdEdu.SelectedIndex >= 0)
{
var obj = GrdEdu.SelectedItem;
System.Type type = obj.GetType();
IDtoshow = (int)type.GetProperty("EDU_ID").GetValue(obj, null);
using (var db = new CVDBContext())
{
Educations education = db.Educations.Find(IDtoshow);
TxtbxCourse.Text = education.Course;
TxtbxDegree.Text = education.Degree;
TxtbxNotes.Text = education.Notes;
TxtbxSchool.Text = education.School;
TxtbxYear.Text = education.Year.ToString();
}
BtnDeleteEdu.IsEnabled = true;
BtnAddEdu.Content = "Edit";
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ex.InnerException, "selection changed error");
}
}
private int GetCompanyID(string str)
{
try
{
using (var db = new CVDBContext())
{
var query = from comp in db.Companies
where comp.Company_Name == str
select comp;
if (query.Count() == 1)
{
return query.FirstOrDefault().Company_ID;
}
else return 0;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ex.InnerException, "get company id error");
return 0;
}
}
public static int GetEduID(this Educations e)
{
using (var db = new CVDBContext())
{
var query = from edu in db.Educations
where edu.Year == e.Year && edu.School == e.School && edu.Course == e.Course && edu.Degree == e.Degree && edu.Notes == e.Notes
select edu;
if (query.Count() > 0)
{
Educations newEdu = query.FirstOrDefault();
return newEdu.EDU_ID;
}
return 0;
}
}
private async Task<int> SaveCompany(string str)
{
using (var db = new CVDBContext())
{
Companies co = db.Companies.Find(GetCompanyID(str));
if (co == null)
{
co = new Companies
{
Company_Name = str
};
db.Companies.Add(co);
db.Entry(co).State = EntityState.Added;
await db.SaveChangesAsync();
coID = GetCompanyID(co.Company_Name);
return coID;
}
else
{
coID = co.Company_ID;
return coID;
}
}
}
public static async Task<int> Save(this Adresses a)
{
using (var db = new CVDBContext())
{
if (a.Adress_ID < 1)
{
db.Adresses.Add(a);
db.Entry(a).State = EntityState.Added;
}
else
{
db.Entry(a).State = EntityState.Modified;
}
await db.SaveChangesAsync();
return a.Adress_ID;
}
}
private void BtnDeleteExp_Click(object sender, RoutedEventArgs e)
{
ClearFields();
try
{
using (var db = new CVDBContext())
{
var query = from exp in db.Employment_Histories
where exp.EMP_HIS_ID == emp_hisID
select exp;
Employment_Histories ehToDelete = query.FirstOrDefault();
db.Employment_Histories.Remove(ehToDelete);
db.SaveChanges();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ex.InnerException, "Error");
}
FillGrdRef();
}
private async Task<int> AddEdu()
{
try
{
int tmp = Convert.ToInt16(TxtbxYear.Text);
using (var db = new CVDBContext())
{
var query = from edu in db.Educations
where edu.EDU_ID == IDtoshow
select edu;
if (query.Count() < 1)
{
eduToAdd = new Educations
{
Course = TxtbxCourse.Text,
Degree = TxtbxDegree.Text,
Year = Convert.ToInt16(TxtbxYear.Text),
Notes = TxtbxNotes.Text,
School = TxtbxSchool.Text
};
db.Educations.Add(eduToAdd);
db.Entry(eduToAdd).State = EntityState.Added;
await db.SaveChangesAsync();
eduID = eduToAdd.GetEduID();
}
else
{
eduToAdd = query.FirstOrDefault();
eduToAdd.Course = TxtbxCourse.Text;
eduToAdd.Degree = TxtbxDegree.Text;
eduToAdd.Year = Convert.ToInt16(TxtbxYear.Text);
eduToAdd.Notes = TxtbxNotes.Text;
eduToAdd.School = TxtbxSchool.Text;
eduID = eduToAdd.EDU_ID;
db.Entry(eduToAdd).State = EntityState.Modified;
db.SaveChanges();
}
}
return eduID;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ex.InnerException, "add education error");
return 0;
}
}
private void GrdEmpHis_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
BtnDeleteExp.IsEnabled = false;
BtnSaveExp.Content = "Edit Experience";
if (GrdEmpHis.SelectedIndex >= 0)
{
var obj = GrdEmpHis.SelectedItem;
System.Type type = obj.GetType();
emp_hisID = (int)type.GetProperty("EMP_HIS_ID").GetValue(obj, null);
try
{
using (var db = new CVDBContext())
{
Employment_Histories eh = db.Employment_Histories.Find(emp_hisID);
TxtbxDepartment.Text = eh.Department;
TxtbxNotes.Text = eh.Notes;
TxtbxPosition.Text = eh.Position;
TxtbxTitle.Text = eh.Title;
DPFrom.SelectedDate = eh.From_Date;
DPUntil.SelectedDate = eh.Until_Date;
if (eh.Curr_Emp)
ChkbxCurrentEMP.IsChecked = true;
if (eh.REF_ID != null)
FillRef((int)eh.REF_ID);
if (eh.Company_ID != null)
TxtbxCompany.Text = GetCompanyName((int)eh.Company_ID);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ex.InnerException, "Error");
}
BtnDeleteExp.IsEnabled = true;
}
}
private async void BtnSaveExp_Click(object sender, RoutedEventArgs e)
{
try
{
coID = await SaveCompany(TxtbxCompany.Text);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ex.InnerException, "Company Error");
}
try
{
refID = await Save_Ref();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ex.InnerException, "Reference Error");
}
try
{
using (var db = new CVDBContext())
{
if (emp_hisID < 1)
{
Employment_Histories eh = new Employment_Histories
{
Company_ID = coID,
User_ID = uid,
Department = TxtbxDepartment.Text,
Position = TxtbxPosition.Text,
Title = TxtbxTitle.Text,
From_Date = (DateTime)DPFrom.SelectedDate,
Until_Date = (DateTime?)DPUntil.SelectedDate,
Curr_Emp = (bool)ChkbxCurrentEMP.IsChecked ? true : false,
REF_ID = refID,
Notes = TxtbxNotes.Text
};
if ((bool)ChkbxCurrentEMP.IsChecked)
{
eh.Until_Date = null;
}
db.Employment_Histories.Add(eh);
db.Entry(eh).State = EntityState.Added;
db.SaveChanges();
}
else
{
Employment_Histories eh = db.Employment_Histories.Find(emp_hisID);
if (eh.REF_ID < 1 || eh.REF_ID == null)
{
int? tmp = await Save_Ref();
eh.REF_ID = tmp;
}
eh.Company_ID = coID;
eh.User_ID = uid;
eh.Department = TxtbxDepartment.Text;
eh.Position = TxtbxPosition.Text;
eh.Title = TxtbxTitle.Text;
eh.From_Date = (DateTime)DPFrom.SelectedDate;
eh.Until_Date = (DateTime?)DPUntil.SelectedDate;
eh.Curr_Emp = (bool)ChkbxCurrentEMP.IsChecked ? true : false;
eh.Notes = TxtbxNotes.Text;
if ((bool)ChkbxCurrentEMP.IsChecked)
{
eh.Until_Date = null;
}
db.Entry(eh).State = EntityState.Modified;
db.SaveChanges();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ex.InnerException, "Save emp_his error");
}
ClearFields();
FillGrdRef();
}
private string GetCompanyName(int id)
{
try
{
using (var db = new CVDBContext())
{
Companies c = db.Companies.Find(id);
return c.Company_Name;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ex.InnerException, "get company name error");
return "";
}
}
private void FillRef(int id)
{
try
{
using (var db = new CVDBContext())
{
User_References ur = db.User_References.Find(id);
TxtbxFirstNameRef.Text = ur.First_Name;
TxtbxLastNameRef.Text = ur.Last_Name;
TxtbxTitleRef.Text = ur.Title;
if (ur.Company_ID != null)
TxtbxCompanyRef.Text = GetCompanyName((int)ur.Company_ID);
TxtbxPhoneRef.Text = ur.Phone;
TxtbxMobileRef.Text = ur.Mobile;
TxtbxEmailRef.Text = ur.Email;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ex.InnerException, "fill reference error");
}
}
private void FillGrdRef()
{
try
{
using (var db = new CVDBContext())
{
var query = from exp in db.Employment_Histories
join com in db.Companies on exp.Company_ID equals com.Company_ID
join refe in db.User_References on exp.REF_ID equals refe.REF_ID
where exp.User_ID == uid
select new { exp.EMP_HIS_ID, exp.From_Date, exp.Until_Date, com.Company_Name, exp.Position, refe.First_Name, refe.Last_Name };
GrdEmpHis.ItemsSource = query.OrderBy(x => x.From_Date).ToList();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ex.InnerException, "fill grid error");
}
}
public static void UpdateGrid(this DataGrid grid)
{
List<person> gridlist = new List<person>();
using (var db = new CVDBContext())
{
var query1 = from u in db.Users
select u.User_ID;
foreach (var user in query1.ToList())
{
List<string> tmptags = new List<string>();
List<string> tmpskills = new List<string>();
var query2 = from rel in db.User_Skill_REL
join skill in db.Skills on rel.Skill_ID equals skill.Skill_ID
where rel.User_ID == user
select skill.Skill_Name;
string skillstring = "";
foreach (var v in query2)
{
tmpskills.Add(v);
skillstring += v;
}
var query3 = from rel in db.User_Tag_REL
join tag in db.Tags on rel.Tag_ID equals tag.Tag_ID
where rel.User_ID == user
select tag.Tag_Name;
string tagstring = "";
foreach (var v in query3)
{
tmptags.Add(v);
tagstring += v;
}
var query4 = from u in db.Users
join ud in db.User_Details on u.User_ID equals ud.User_ID
where u.User_ID == user
select new { u.User_ID, u.First_Name, u.Last_Name, ud.Available, ud.Available_Date /*, tagstring, skillstring*/ };
person p = new person
{
id = query4.First().User_ID,
first_name = query4.First().First_Name,
last_name = query4.First().Last_Name,
available = query4.First().Available,
date = query4.First().Available_Date,
tags = tmptags,
skills = tmpskills
};
gridlist.Add(p);
}
gridlist = gridlist.OrderByDescending(x => x.available)
.ThenBy(x => x.date)
.ThenBy(x => x.last_name)
.ThenBy(x => x.first_name)
.ToList();
grid.ItemsSource = gridlist;
}
}
private int GetRefID(string str)
{
try
{
using (var db = new CVDBContext())
{
var query = from r in db.User_References
where r.Email == str
select r;
return query.FirstOrDefault().REF_ID;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ex.InnerException, "Get ref id error");
return 0;
}
}