public static Employee Insert(User user, Employee u)
{
var q = new Entities.Employee();
q.Insert(new { HRE_FIRST_NAME = u.Name, HRE_FAMILY_NAME = u.FamilyName, HRE_LAST_NAME = u.LastName, ACL_USER_ID = user.Id, HRP_ID = 1, HRD_ID = 1 });
return(Get(user).FirstOrDefault());
}
public static long CountAll()
{
var q = new Entities.Employee();
var u = q.Scalar(@"select count(*) from
HR_EMPLOYEE e, HR_POSITION p, HR_DEPARTMENT d, HR_SALARY s, ACL_USER u
where p.HRP_ID = e.HRP_ID and d.HRD_ID = e.HRD_ID and s.HRD_ID = e.HRD_ID and s.HRP_ID = e.HRP_ID and u.ACL_USER_ID = e.ACL_USER_ID
");
return((long)u);
}
public static IEnumerable <Employee> All()
{
var q = new Entities.Employee();
var u = q.Query(@"select e.HRE_FIRST_NAME, e.HRE_FAMILY_NAME, e.HRE_LAST_NAME, d.HRD_NAME, p.HRP_NAME, s.HRS_SUM, e.HRE_ID, e.ACL_USER_ID, u.ACLU_EMAIL from
HR_EMPLOYEE e, HR_POSITION p, HR_DEPARTMENT d, HR_SALARY s, ACL_USER u
where p.HRP_ID = e.HRP_ID and d.HRD_ID = e.HRD_ID and s.HRD_ID = e.HRD_ID and s.HRP_ID = e.HRP_ID and u.ACL_USER_ID = e.ACL_USER_ID
");
return((from d in u select(Employee) FromEntity(d)).AsEnumerable <Employee>());
}
public static Employee[] Get(string login)
{
var q = new Entities.Employee();
var u = q.Query(@"select e.HRE_FIRST_NAME, e.HRE_FAMILY_NAME, e.HRE_LAST_NAME, d.HRD_NAME, p.HRP_NAME, s.HRS_SUM, e.HRE_ID, e.ACL_USER_ID, u.ACLU_EMAIL from
ACL_USER u, HR_EMPLOYEE e, HR_POSITION p, HR_DEPARTMENT d, HR_SALARY s
where u.ACLU_LOGIN = @0 and e.ACL_USER_ID = u.ACL_USER_ID and p.HRP_ID = e.HRP_ID and d.HRD_ID = e.HRD_ID and s.HRD_ID = e.HRD_ID and s.HRP_ID = e.HRP_ID
", args: login);
return((from d in u select(Employee) FromEntity(d)).ToArray());
}
public static Employee Get(int id)
{
var q = new Entities.Employee();
var u = q.Query(@"select e.HRE_FIRST_NAME, e.HRE_FAMILY_NAME, e.HRE_LAST_NAME, d.HRD_NAME, p.HRP_NAME, s.HRS_SUM, e.HRE_ID, e.ACL_USER_ID, u.ACLU_EMAIL from
HR_EMPLOYEE e, HR_POSITION p, HR_DEPARTMENT d, HR_SALARY s, ACL_USER u
where e.HRE_ID = @0 and p.HRP_ID = e.HRP_ID and d.HRD_ID = e.HRD_ID and s.HRD_ID = e.HRD_ID and s.HRP_ID = e.HRP_ID and u.ACL_USER_ID = e.ACL_USER_ID
", args: id).FirstOrDefault();
return(FromEntity(u));
}
public static IEnumerable <Employee> Page(int now, out int pages, int max = 10)
{
var q = new Entities.Employee();
var u = q.Paged(sql: @"select e.HRE_FIRST_NAME, e.HRE_FAMILY_NAME, e.HRE_LAST_NAME, d.HRD_NAME, p.HRP_NAME, s.HRS_SUM, e.HRE_ID, e.ACL_USER_ID, u.ACLU_EMAIL from
HR_EMPLOYEE e, HR_POSITION p, HR_DEPARTMENT d, HR_SALARY s, ACL_USER u
where p.HRP_ID = e.HRP_ID and d.HRD_ID = e.HRD_ID and s.HRD_ID = e.HRD_ID and s.HRP_ID = e.HRP_ID and u.ACL_USER_ID = e.ACL_USER_ID
", primaryKey: "HRE_FAMILY_NAME", pageSize: max, currentPage: now);
pages = u.TotalPages;
return((from d in (IEnumerable <dynamic>)u.Items select(Employee) FromEntity(d)).AsEnumerable <Employee>());
}
public static IEnumerable <Employee> PageSearchName(string nameStartsWith, string departmentStartsWith, int now, out int pages, int max = 10)
{
var q = new Entities.Employee();
string sql = @"select e.HRE_FIRST_NAME, e.HRE_FAMILY_NAME, e.HRE_LAST_NAME, d.HRD_NAME, p.HRP_NAME, s.HRS_SUM, e.HRE_ID, e.ACL_USER_ID, u.ACLU_EMAIL from
HR_EMPLOYEE e, HR_POSITION p, HR_DEPARTMENT d, HR_SALARY s, ACL_USER u
where $inc1
p.HRP_ID = e.HRP_ID and d.HRD_ID = e.HRD_ID $inc2 and s.HRD_ID = e.HRD_ID and s.HRP_ID = e.HRP_ID and u.ACL_USER_ID = e.ACL_USER_ID
";
string inc1 = "(e.HRE_FIRST_NAME like @0 or e.HRE_FAMILY_NAME like @0 or e.HRE_LAST_NAME like @0 ) and ";
string inc2 = "and ( d.HRD_NAME like @1 )";
sql = sql.Replace("$inc1", string.IsNullOrWhiteSpace(nameStartsWith) ? "" : inc1);
sql = sql.Replace("$inc2", string.IsNullOrWhiteSpace(departmentStartsWith) ? "" : inc2);
var u = q.Paged(sql: sql, primaryKey: "HRE_FAMILY_NAME", pageSize: max, currentPage: now, args: new object[] { nameStartsWith + "%", departmentStartsWith + "%" });
pages = u.TotalPages;
return((from d in (IEnumerable <dynamic>)u.Items select(Employee) FromEntity(d)).AsEnumerable <Employee>());
}
public static bool Update(Employee u)
{
User usr = User.Get(u.UserId);
usr.Email = u.Email;
if (!User.Update(usr))
{
return(false);
}
var q = new Entities.Employee();
var emp = q.All(where : "WHERE HRE_ID = @0", args: u.Id).FirstOrDefault();
if (emp == null)
{
return(false);
}
q.Update(new { HRE_FIRST_NAME = u.Name, HRE_FAMILY_NAME = u.FamilyName, HRE_LAST_NAME = u.LastName }, u.Id);
return(true);
}