111,098
社区成员




select a.BranjobId
,a.Jobno
,b.Jobna
,Planqty
,Jobqty = (select count(Manno) from BgMan c where a.Branno = c.Branno and a.Jobno = c.Jobno) --人事档案表
,Overtime = sum(a.Overtime)
from BranJob a --部门职位表
left join JobSet b on a.Jobno =b.Jobno --职位表
where a.Branno = '006'
group by a.BranjobId,a.Branno,a.Jobno,b.Jobna,Planqty
order by a.BranjobId
/// <summary>
/// 获取部门职务信息
/// </summary>
/// <param name="branno">部门编号</param>
/// <returns></returns>
public static DataTable GetBranjob(string branno)
{
try
{
using(CusProContext db = new CusProContext())
{
var BranjobObj = (from p in db.BranJob
where p.Branno == branno
join a in db.JobSet on p.Jobno equals a.Jobno into t1
from a in t1.DefaultIfEmpty()
join b in db.BgMan on new { p.Branno, p.Jobno } equals new { b.Branno, b.Jobno } into t2
from c in t2.DefaultIfEmpty()
group c by new
{
p.BranjobId,
p.Jobno,
a.Jobna,
p.Planqty,
p.Overtime
} into grp
select new
{
grp.Key.BranjobId,
grp.Key.Jobno,
grp.Key.Jobna,
grp.Key.Planqty,
Jobqty = grp.Count(x => x.Manno != null),
Lackqty = grp.Key.Planqty - grp.Count(x => x.Manno != null),
grp.Key.Overtime
}).ToList();
return LinqToDataTable(BranjobObj);
}
}
catch(Exception ex)
{
throw ex;
}
}