/// <summary>
/// 取得所有部門資料(CTE)
/// </summary>
/// <param name="depid"></param>
/// <returns></returns>
public List <ENT_DEPARTMENT> getDepartmentOrg(int depid)
{
string sql = @"WITH Dep_CTE (DEP_ID,DEPT_CODE,DEPT_NAME,PARENT_ID,DEP_LEVEL) AS
(
--頂層
select DEP_ID,DEPT_CODE,DEPT_NAME,PARENT_ID,0 AS DT_LEVEL
from ENT_DEPARTMENT RD
where RD.DEP_ID=@depid
UNION ALL
--成員
select D.DEP_ID,D.DEPT_CODE,D.DEPT_NAME,D.PARENT_ID,DC.DEP_LEVEL+1
from ENT_DEPARTMENT D INNER JOIN Dep_CTE DC on D.PARENT_ID=DC.DEP_ID
)
select * from Dep_CTE";
var parameters = new Dictionary <string, Object>();
//設定專案名編號資料
parameters.Add("depid", depid);
DataSet ds = ExecuteStoreQuery(sql, CommandType.Text, parameters);
Org_department = new List <ENT_DEPARTMENT>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
ENT_DEPARTMENT dept = new ENT_DEPARTMENT();
dept.DEP_ID = Convert.ToInt64(dr["DEP_ID"]);
dept.DEPT_CODE = Convert.ToString(dr["DEPT_CODE"]);
dept.DEPT_NAME = Convert.ToString(dr["DEPT_NAME"]);
//dept.DESC = Convert.ToString(dr["DESC"]);
dept.PARENT_ID = Convert.ToInt64(dr["PARENT_ID"]);
Org_department.Add(dept);
}
return(Org_department);
}
//新增或修改部門
public String addDepartment(FormCollection form)
{
logger.Info("form:" + form.Count);
string msg = "";
//懶得把Form綁SYS_USER 直接先把Form 值填滿
ENT_DEPARTMENT d = new ENT_DEPARTMENT();
if (form.Get("d_depId").Trim() != "")
{
d.DEP_ID = Convert.ToInt64(form.Get("d_depId").Trim());
}
d.DEPT_CODE = form.Get("d_deptCode").Trim();
d.DEPT_NAME = form.Get("d_deptName").Trim();
d.MANAGER = form.Get("d_Manager").Trim();
d.DESC = form.Get("d_desc").Trim();
if (null != form.Get("d_parentId") && "" != form.Get("d_parentId").Trim())
{
d.PARENT_ID = Convert.ToInt64(form.Get("d_parentId").Trim());
}
SYS_USER loginUser = (SYS_USER)Session["user"];
d.CREATE_ID = loginUser.USER_ID;
d.CREATE_DATE = DateTime.Now;
msg = "新增部門成功(" + service.addDepartment(d) + ")";
//logger.Info("Request:user_ID=" + form["u_userid"]);
return(msg);
}
//取得部門資料
public ENT_DEPARTMENT getDepartmentById(string depid)
{
using (var context = new topmepEntities())
{
department = context.ENT_DEPARTMENT.SqlQuery("select dep.* from ENT_DEPARTMENT dep "
+ "where dep.DEP_ID = @depid "
, new SqlParameter("depid", depid)).First();
}
return(department);
}
/// <summary>
/// 刪除部門資料
/// </summary>
/// <param name="depId"></param>
public void delDepartment(long depId)
{
using (var context = new topmepEntities())
{
//2.取得
ENT_DEPARTMENT d = context.ENT_DEPARTMENT.Find(depId);
logger.Info("del Department =" + d.DEPT_NAME + "," + d.DEP_ID);
context.ENT_DEPARTMENT.Remove(d);
int i = context.SaveChanges();
logger.Debug("remove dep=" + i);
}
}
//新增部門資料
public long addDepartment(ENT_DEPARTMENT dep)
{
logger.Info("create new Department ");
using (var context = new topmepEntities())
{
//2.取得供應商編號
context.ENT_DEPARTMENT.AddOrUpdate(dep);
int i = context.SaveChanges();
logger.Debug("Add dep=" + i);
}
return(dep.DEP_ID);
}
