public UrlVO FindUrlByUrl(AdminContext ctx, string url)
{
UrlVO model = new UrlVO();
try
{
var query = (from u in ctx.Url
where u.Url == url
select new
{
u.Id,
u.GUID,
u.CreationDate,
u.ExpireDate
}).Single();
model.Id = FillItemForDatabase.FillItem(query.Id);
model.Url = url;
model.GUID = FillItemForDatabase.FillItem(query.GUID);
model.CreationDate = FillItemForDatabase.FillItem(query.CreationDate);
model.ExpireDate = FillItemForDatabase.FillItem(query.ExpireDate);
}
catch
{
throw;
}
return(model);
}
public async Task <List <EmailVO> > FindEmailsByPersonId(EmailContext ctx, int personId)
{
List <EmailVO> model = new List <EmailVO>();
try
{
var query = await(from email in ctx.Emails
where email.PersonId == personId
select new
{
email.Id,
email.Domain
}).ToListAsync();
foreach (var email in query)
{
EmailVO em = new EmailVO()
{
Id = FillItemForDatabase.FillItem(email.Id),
Domain = FillItemForDatabase.FillItem(email.Domain)
};
model.Add(em);
}
}
catch
{
throw;
}
return(model);
}
public EducationVO FindEducationById(int id, EducationContext ctx)
{
EducationVO model = new EducationVO();
try
{
var query = (from edu in ctx.Educations.AsEnumerable()
where edu.Id == id
select new EducationVO
{
Id = edu.Id,
MemberId = edu.MemberId,
Degree = edu.Degree,
School = edu.School,
Department = edu.Department,
StartYear = edu.StartYear,
GraduationYear = edu.GraduationYear,
Grade = edu.Grade
}).Single();
model.Id = FillItemForDatabase.FillItem(query.Id);
model.MemberId = FillItemForDatabase.FillItem(query.MemberId);
model.Degree = FillItemForDatabase.FillItem(query.Degree);
model.School = FillItemForDatabase.FillItem(query.School);
model.Department = FillItemForDatabase.FillItem(query.Department);
model.StartYear = FillItemForDatabase.FillItem(query.StartYear);
model.GraduationYear = FillItemForDatabase.FillItem(query.GraduationYear);
model.Grade = FillItemForDatabase.FillItem(query.Grade);
}
catch (Exception ex)
{
throw ex;
}
return(model);
}
public MemberVO FindMemberById(MemberContext ctx, int id)
{
MemberVO member = new MemberVO();
try
{
var query = (from mem in ctx.Member
join rol in ctx.Role on mem.RoleId equals rol.Id
where mem.Id == id
select new
{
mem.Username,
mem.Id,
mem.DefaultCultrure,
rol.Name
}).Single();
member.Username = FillItemForDatabase.FillItem(query.Username);
member.Id = FillItemForDatabase.FillItem(query.Id);
member.DefaultCultrure = FillItemForDatabase.FillItem(query.DefaultCultrure);
member.Role = new RoleVO();
member.Role.Name = FillItemForDatabase.FillItem(query.Name);
}
catch
{
throw;
}
return(member);
}
public MemberVO FindMemberByUserName(MemberContext ctx, string username)
{
MemberVO member = new MemberVO();
try
{
var query = (from m in ctx.Member
where m.Username == username
select new
{
m.Password,
m.Id,
m.RoleId,
m.DefaultCultrure,
m.PersonId,
m.UrlId
}).Single();
member.Id = FillItemForDatabase.FillItem(query.Id);
member.Username = username;
member.Password = FillItemForDatabase.FillItem(query.Password);
member.RoleId = FillItemForDatabase.FillItem(query.RoleId);
member.DefaultCultrure = FillItemForDatabase.FillItem(query.DefaultCultrure);
member.PersonId = FillItemForDatabase.FillItem(query.PersonId);
member.UrlId = FillItemForDatabase.FillItem(query.UrlId);
}
catch (Exception ex)
{
throw ex;
}
return(member);
}
/// <summary>
/// Get Cv included member,person from memberId
/// </summary>
/// <param name="ctx">Application ctx</param>
/// <param name="memberId">filter for take cv</param>
/// <returns>CvVO</returns>
public CvVO FindCvByMemberId(CvContext ctx, int memberId)
{
CvVO model = new CvVO();
try
{
var query = (from cv in ctx.Cv
join member in ctx.Member on cv.MemberId equals member.Id
join person in ctx.Person on member.PersonId equals person.Id
where member.Id == memberId
select new
{
cv.Id,
cv.MemberId,
member.Username,
member.PersonId,
person.BirthDate,
person.FirstName_el,
person.LastName_el,
person.FirstName_en,
person.LastName_en
}).Single();
model.Id = FillItemForDatabase.FillItem(query.Id);
model.MemberId = FillItemForDatabase.FillItem(query.MemberId);
model.Member = new MemberVO()
{
Id = model.MemberId,
Username = FillItemForDatabase.FillItem(query.Username),
PersonId = FillItemForDatabase.FillItem(query.PersonId)
};
model.Member.Person = new PersonVO()
{
Id = model.Member.PersonId,
BirthDate = FillItemForDatabase.FillItem(query.BirthDate),
FirstName_el = FillItemForDatabase.FillItem(query.FirstName_el),
LastName_el = FillItemForDatabase.FillItem(query.LastName_el),
FirstName_en = FillItemForDatabase.FillItem(query.FirstName_en),
LastName_en = FillItemForDatabase.FillItem(query.LastName_en)
};
}
catch
{
throw;
}
return(model);
}
public RoleVO FindRoleById(RoleContext ctx, int id)
{
RoleVO role = new RoleVO();
try
{
var query = (from r in ctx.Role
where r.Id == id
select new { r.Name }).Single();
role.Id = id;
role.Name = FillItemForDatabase.FillItem(query.Name);
}
catch
{
throw;
}
return(role);
}
public int FindIdByUsername(MemberContext ctx, string username)
{
int id = 0;
try
{
var query = (from mem in ctx.Member
where mem.Username == username
select new
{
mem.Id
}).Single();
id = FillItemForDatabase.FillItem(query.Id);
}
catch (Exception ex)
{
throw ex;
}
return(id);
}
public List <EducationVO> FindEducationsByMemberId(int memberId, EducationContext ctx)
{
List <EducationVO> model = new List <EducationVO>();
try
{
var query = (from edu in ctx.Educations.AsEnumerable()
where edu.MemberId == memberId
select new EducationVO
{
Id = edu.Id,
Degree = edu.Degree,
School = edu.School,
Department = edu.Department,
StartYear = edu.StartYear,
GraduationYear = edu.GraduationYear,
Grade = edu.Grade
}).ToList();
foreach (EducationVO edu in query)
{
EducationVO education = new EducationVO()
{
Id = FillItemForDatabase.FillItem(edu.Id),
Degree = FillItemForDatabase.FillItem(edu.Degree),
School = FillItemForDatabase.FillItem(edu.School),
Department = FillItemForDatabase.FillItem(edu.Department),
StartYear = FillItemForDatabase.FillItem(edu.StartYear),
GraduationYear = FillItemForDatabase.FillItem(edu.GraduationYear),
Grade = FillItemForDatabase.FillItem(edu.Grade),
};
model.Add(education);
}
}
catch (Exception ex)
{
throw ex;
}
return(model);
}
public async Task <List <AddressVO> > FindAddressesByPersonId(AddressContext ctx, int personId)
{
List <AddressVO> model = new List <AddressVO>();
try
{
var query = await(from addr in ctx.Addresses
where addr.PersonId == personId
select new
{
addr.Id,
addr.City,
addr.Country,
addr.Street,
addr.Number,
addr.PostCode
}).ToListAsync();
foreach (var addr in query)
{
AddressVO address = new AddressVO()
{
City = FillItemForDatabase.FillItem(addr.City),
Country = FillItemForDatabase.FillItem(addr.Country),
Number = FillItemForDatabase.FillItem(addr.Number),
Street = FillItemForDatabase.FillItem(addr.Street),
PostCode = FillItemForDatabase.FillItem(addr.PostCode),
PersonId = FillItemForDatabase.FillItem(personId)
};
model.Add(address);
}
}
catch
{
throw;
}
return(model);
}