public List <PrivilegeEditModel> GetUserEditPrivilege(string LoginUserEmp, string EditEmp, string EmpLevel, MESDBHelper.OleExec SFCDB)
{
string sql = string.Empty;
/////9 emp_leve
sql = $@" SELECT A.ID,
A.SYSTEM_NAME,
A.PRIVILEGE_NAME,
A.PRIVILEGE_DESC
FROM C_PRIVILEGE A, C_USER B, C_USER_PRIVILEGE C
WHERE C.USER_ID = B.ID
AND C.PRIVILEGE_ID = A.ID AND B.EMP_NO = '{EditEmp}' ";
DataSet res = SFCDB.ExecSelect(sql);
List <PrivilegeEditModel> Privilegelist = new List <PrivilegeEditModel>();
if (res.Tables[0].Rows.Count > 0)
{
foreach (DataRow item in res.Tables[0].Rows)
{
Privilegelist.Add(new PrivilegeEditModel
{
ID = item["ID"].ToString(),
SYSTEM_NAME = item["SYSTEM_NAME"].ToString(),
PRIVILEGE_NAME = item["PRIVILEGE_NAME"].ToString(),
PRIVILEGE_DESC = item["PRIVILEGE_DESC"].ToString()
});
}
}
return(Privilegelist);
}
/// <summary>
/// 查詢該用戶已有的ModelType權限
/// </summary>
public List <ModelTypePrivilege> GetPrivilegeForUser(string LoginUserEmp, string EditEmp, string EmpLevel, MESDBHelper.OleExec SFCDB)
{
string sql = string.Empty;
sql = $@"Select b.id,a.type_no, a.department,a.model_name,a.program_name,a.description,b.EDIT_EMP,b.EDIT_TIME
From sfcbase.c_model_type a,sfcbase.c_model_user b,c_user c
Where a.id =b.type_id and b.user_id=c.id and emp_no='{EditEmp}'";
DataSet res = SFCDB.ExecSelect(sql);
List <ModelTypePrivilege> list = new List <ModelTypePrivilege>();
if (res.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in res.Tables[0].Rows)
{
if (!(dr["EDIT_TIME"] is System.DBNull))
{
list.Add(new ModelTypePrivilege
{
ID = dr["ID"].ToString(),
TYPE_NO = dr["TYPE_NO"].ToString(),
DEPARTMENT = dr["DEPARTMENT"].ToString(),
MODEL_NAME = dr["MODEL_NAME"].ToString(),
PROGRAM_NAME = dr["PROGRAM_NAME"].ToString(),
DESCRIPTION = dr["DESCRIPTION"].ToString(),
EDIT_EMP = dr["EDIT_EMP"].ToString(),
EDIT_TIME = (DateTime)dr["EDIT_TIME"]
});
}
else
{
list.Add(new ModelTypePrivilege
{
ID = dr["ID"].ToString(),
TYPE_NO = dr["TYPE_NO"].ToString(),
DEPARTMENT = dr["DEPARTMENT"].ToString(),
MODEL_NAME = dr["MODEL_NAME"].ToString(),
PROGRAM_NAME = dr["PROGRAM_NAME"].ToString(),
DESCRIPTION = dr["DESCRIPTION"].ToString(),
EDIT_EMP = dr["EDIT_EMP"].ToString(),
EDIT_TIME = null
});
}
}
}
return(list);
}
public List <MENUS> GetMenuNextID(string ColumnName, string Input, MESDBHelper.OleExec SFCDB)
{
string strSql = "";
strSql = $@" select ID,
SYSTEM_NAME,
MENU_NAME,
PAGE_PATH,
PARENT_CODE,
SORT,
STYLE_NAME,
CLASS_NAME,
LANGUAGE_ID,
MENU_DESC from c_menu WHERE {ColumnName} = '{Input}' ORDER BY SORT ";
DataSet res = SFCDB.ExecSelect(strSql);
List <MENUS> Privileges = new List <MENUS>();
if (res.Tables[0].Rows.Count > 0)
{
foreach (DataRow item in res.Tables[0].Rows)
{
Privileges.Add(new MENUS
{
ID = item["ID"].ToString(),
SYSTEM_NAME = item["SYSTEM_NAME"].ToString(),
MENU_NAME = item["MENU_NAME"].ToString(),
PAGE_PATH = item["PAGE_PATH"].ToString(),
PARENT_CODE = item["PARENT_CODE"].ToString(),
SORT = item["SORT"].ToString(),
STYLE_NAME = item["STYLE_NAME"].ToString(),
CLASS_NAME = item["CLASS_NAME"].ToString(),
LANGUAGE_ID = item["LANGUAGE_ID"].ToString(),
MENU_DESC = item["MENU_DESC"].ToString(),
MENU_ITEM = GetMenuNextID(ColumnName, item["ID"].ToString(), SFCDB)
});
}
}
return(Privileges);
}
/// <summary>
/// 從登陸用戶有的權限列表中,只取受分配員工沒有權限的列表
/// </summary>
public List <ModelTypePrivilege> GetModelTypeForUser(string LoginUserEmp, string EditEmp, string EmpLevel, MESDBHelper.OleExec SFCDB)
{
string sql = string.Empty;
if (EmpLevel == "9")
{
sql = $@"select id,type_no,department,model_name,program_name,description
from sfcbase.c_model_type a
where a.id not in (select type_id from sfcbase.c_model_user b,c_user c
where b.user_id=c.id and emp_no = '{EditEmp}') ";
}
else
{
sql = $@"select a.id,a.type_no,a.department,a.model_name,a.program_name,a.description
from sfcbase.c_model_type a,sfcbase.c_model_user d,c_user e
where d.type_id=a.id and d.USER_ID=e.ID and e.emp_no='{LoginUserEmp}' and
a.id not in (select type_id from sfcbase.c_model_user b,c_user c
where b.user_id=c.id and emp_no like '{EditEmp}') ";
}
DataSet res = SFCDB.ExecSelect(sql);
List <ModelTypePrivilege> list = new List <ModelTypePrivilege>();
if (res.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in res.Tables[0].Rows)
{
list.Add(new ModelTypePrivilege
{
ID = dr["ID"].ToString(),
TYPE_NO = dr["TYPE_NO"].ToString(),
DEPARTMENT = dr["DEPARTMENT"].ToString(),
MODEL_NAME = dr["MODEL_NAME"].ToString(),
PROGRAM_NAME = dr["PROGRAM_NAME"].ToString(),
DESCRIPTION = dr["DESCRIPTION"].ToString()
});
}
}
return(list);
}
public void testQueryTable(OleExec SFCDB)
{
String skuno = "03054639";
String sql = $@"select bu,skuno from C_SKU WHERE SKUNO ='{skuno.ToUpper()}'";
DataTable table = SFCDB.ExecSelect(sql).Tables[0];
List <SKU> skus = new List <SKU>();
for (int i = 0; i < table.Rows.Count; i++)
{
// table.Load(sku);
SKU sku = new SKU();
sku.Bu = table.Rows[i][0].ToString();
sku.Skuno = table.Rows[i][1].ToString();
System.Console.Out.WriteLine(table.Rows[i][0].ToString());
System.Console.Out.WriteLine(table.Rows[i][1].ToString());
skus.Add(sku);
}
System.Console.Out.WriteLine("bu:" + skus[0].Bu + ",skuno:" + skus[0].Skuno);
}
public List <MENUS> GetMenu(string emp_l, string User_No, MESDBHelper.OleExec SFCDB)
{
string strSql = "";
if (emp_l != "9")
{
#region sql1
//strSql = $@" SELECT b.ID,
// b.SYSTEM_NAME,
// b.MENU_NAME,
// b.PAGE_PATH,
// b.PARENT_CODE,
// b.SORT,
// b.STYLE_NAME,
// b.CLASS_NAME,
// b.LANGUAGE_ID,
// b.MENU_DESC
// FROM C_USER_PRIVILEGE a, C_menu b, c_user c
// WHERE C.ID = A.USER_ID
// AND A.PRIVILEGE_ID = B.ID
// AND b.PARENT_CODE = '0'
// AND C.EMP_NO = '{User_No}' ORDER BY SORT ";
strSql = $@" SELECT B.ID,
B.SYSTEM_NAME,
B.MENU_NAME,
B.PAGE_PATH,
B.PARENT_CODE,
B.SORT,
B.STYLE_NAME,
B.CLASS_NAME,
B.LANGUAGE_ID,
B.MENU_DESC
FROM C_MENU B
WHERE EXISTS
(SELECT 1
FROM C_PRIVILEGE GH
WHERE EXISTS
(SELECT 1
FROM C_USER_PRIVILEGE A, C_USER C
WHERE C.ID = A.USER_ID
AND C.EMP_NO = '{User_No}'
AND GH.ID = A.PRIVILEGE_ID
UNION
SELECT 1
FROM C_USER_ROLE D,
C_USER E,
C_ROLE_PRIVILEGE F
WHERE E.ID = D.USER_ID
AND F.ROLE_ID = D.ROLE_ID
AND E.EMP_NO = '{User_No}'
AND GH.ID = F.PRIVILEGE_ID)
AND GH.MENU_ID = B.ID)
AND B.PARENT_CODE = '0'
ORDER BY SORT ";
#endregion
}
else
{
#region sql2
strSql = @" select ID,
SYSTEM_NAME,
MENU_NAME,
PAGE_PATH,
PARENT_CODE,
SORT,
STYLE_NAME,
CLASS_NAME,
LANGUAGE_ID,
MENU_DESC from c_menu WHERE PARENT_CODE = '0' ORDER BY SORT ";
#endregion
}
DataSet res = SFCDB.ExecSelect(strSql);
List <MENUS> Privileges = new List <MENUS>();
if (res.Tables[0].Rows.Count > 0)
{
foreach (DataRow item in res.Tables[0].Rows)
{
Privileges.Add(new MENUS
{
ID = item["ID"].ToString(),
SYSTEM_NAME = item["SYSTEM_NAME"].ToString(),
MENU_NAME = item["MENU_NAME"].ToString(),
PAGE_PATH = item["PAGE_PATH"].ToString(),
PARENT_CODE = item["PARENT_CODE"].ToString(),
SORT = item["SORT"].ToString(),
STYLE_NAME = item["STYLE_NAME"].ToString(),
CLASS_NAME = item["CLASS_NAME"].ToString(),
LANGUAGE_ID = item["LANGUAGE_ID"].ToString(),
MENU_DESC = item["MENU_DESC"].ToString(),
MENU_ITEM = getmenus(item["ID"].ToString(), User_No, emp_l, SFCDB)
});
}
}
return(Privileges);
}
public List <MENUS> getmenus(string PARENT_CODE, string User_No, string ul, MESDBHelper.OleExec SFCDB)
{
string sql = "";
if (ul != "9")
{
sql = $@" SELECT B.ID,
B.SYSTEM_NAME,
B.MENU_NAME,
B.PAGE_PATH,
B.PARENT_CODE,
B.SORT,
B.STYLE_NAME,
B.CLASS_NAME,
B.LANGUAGE_ID,
B.MENU_DESC
FROM C_MENU B
WHERE EXISTS
(SELECT 1
FROM C_PRIVILEGE GH
WHERE EXISTS
(SELECT 1
FROM C_USER_PRIVILEGE A, C_USER C
WHERE C.ID = A.USER_ID
AND C.EMP_NO = '{User_No}'
AND GH.ID = A.PRIVILEGE_ID
UNION
SELECT 1
FROM C_USER_ROLE D,
C_USER E,
C_ROLE_PRIVILEGE F
WHERE E.ID = D.USER_ID
AND F.ROLE_ID = D.ROLE_ID
AND E.EMP_NO = '{User_No}'
AND GH.ID = F.PRIVILEGE_ID)
AND GH.MENU_ID = B.ID)
AND B.PARENT_CODE = '{PARENT_CODE}'
ORDER BY SORT ";
}
else
{
sql = $@" SELECT ID,
SYSTEM_NAME,
MENU_NAME,
PAGE_PATH,
PARENT_CODE,
SORT,
STYLE_NAME,
CLASS_NAME,
LANGUAGE_ID,
MENU_DESC
FROM c_menu
WHERE PARENT_CODE = '{PARENT_CODE}' ORDER BY SORT ";
}
DataSet res = SFCDB.ExecSelect(sql);
List <MENUS> pi = new List <MENUS>();
if (res.Tables[0].Rows.Count > 0)
{
foreach (DataRow item in res.Tables[0].Rows)
{
pi.Add(new MENUS
{
ID = item["ID"].ToString(),
SYSTEM_NAME = item["SYSTEM_NAME"].ToString(),
MENU_NAME = item["MENU_NAME"].ToString(),
PAGE_PATH = item["PAGE_PATH"].ToString(),
PARENT_CODE = item["PARENT_CODE"].ToString(),
SORT = item["SORT"].ToString(),
STYLE_NAME = item["STYLE_NAME"].ToString(),
CLASS_NAME = item["CLASS_NAME"].ToString(),
LANGUAGE_ID = item["LANGUAGE_ID"].ToString(),
MENU_DESC = item["MENU_DESC"].ToString(),
MENU_ITEM = getmenus(item["ID"].ToString(), User_No, ul, SFCDB)
});
}
}
return(pi);
}
public List <PrivilegeEditModel> GetUserRolePrivilege(string LoginUserEmp, string EditEmp, string EmpLevel, MESDBHelper.OleExec SFCDB)
{
string sql = string.Empty;
/////9 emp_leve
if (EmpLevel == "9")
{
sql = $@" SELECT A.ID,
A.SYSTEM_NAME,
A.PRIVILEGE_NAME,
A.PRIVILEGE_DESC
FROM C_PRIVILEGE A
WHERE A.ID NOT IN
(SELECT PRIVILEGE_ID
FROM C_USER_PRIVILEGE
WHERE USER_ID IN (SELECT ID
FROM C_USER
WHERE EMP_NO = '{EditEmp}'))
UNION
SELECT A.ID,
A.SYSTEM_NAME,
A.PRIVILEGE_NAME,
A.PRIVILEGE_DESC
FROM C_PRIVILEGE A
WHERE A.ID NOT IN
(SELECT Q.PRIVILEGE_ID
FROM C_ROLE_PRIVILEGE Q, C_USER_ROLE W
WHERE Q.ROLE_ID = W.ROLE_ID
AND W.USER_ID IN (SELECT ID
FROM C_USER
WHERE EMP_NO = '{EditEmp}')) ";
}
else
{
sql = $@" SELECT A.ID,
A.SYSTEM_NAME,
A.PRIVILEGE_NAME,
A.PRIVILEGE_DESC
FROM C_PRIVILEGE A, C_USER B, C_USER_PRIVILEGE C
WHERE C.USER_ID = B.ID
AND C.PRIVILEGE_ID = A.MENU_ID
AND B.EMP_NO = '{LoginUserEmp}'
AND C.PRIVILEGE_ID NOT IN (SELECT PRIVILEGE_ID
FROM C_USER_PRIVILEGE
WHERE USER_ID IN (SELECT ID
FROM C_USER
WHERE EMP_NO =
'{EditEmp}'))
UNION
SELECT A.ID,
A.SYSTEM_NAME,
A.PRIVILEGE_NAME,
A.PRIVILEGE_DESC
FROM C_PRIVILEGE A,
C_USER_ROLE B,
C_ROLE_PRIVILEGE C,
C_USER D
WHERE C.ROLE_ID = B.ROLE_ID
AND A.ID = C.PRIVILEGE_ID
AND D.EMP_NO = '{LoginUserEmp}'
AND D.ID = B.USER_ID
AND C.PRIVILEGE_ID NOT IN (SELECT Q.PRIVILEGE_ID
FROM C_ROLE_PRIVILEGE Q, C_USER_ROLE W
WHERE Q.ROLE_ID = W.ROLE_ID
AND W.USER_ID IN (SELECT ID
FROM C_USER
WHERE EMP_NO =
'{EditEmp}')) ";
}
DataSet res = SFCDB.ExecSelect(sql);
List <PrivilegeEditModel> Privilegelist = new List <PrivilegeEditModel>();
if (res.Tables[0].Rows.Count > 0)
{
foreach (DataRow item in res.Tables[0].Rows)
{
Privilegelist.Add(new PrivilegeEditModel
{
ID = item["ID"].ToString(),
SYSTEM_NAME = item["SYSTEM_NAME"].ToString(),
PRIVILEGE_NAME = item["PRIVILEGE_NAME"].ToString(),
PRIVILEGE_DESC = item["PRIVILEGE_DESC"].ToString()
});
}
}
return(Privilegelist);
}
/// <summary>
/// 加載所有用戶列表
/// </summary>
public void LoadInformation(Newtonsoft.Json.Linq.JObject requestValue, Newtonsoft.Json.Linq.JObject Data, MESStationReturn StationReturn)
{
MESDBHelper.OleExec SFCDB = _DBPools["SFCDB"].Borrow();
MESDataObject.Module.T_c_user GetInformation = new MESDataObject.Module.T_c_user(SFCDB, DB_TYPE_ENUM.Oracle);
MESDataObject.Module.Row_c_user InformationRow = (MESDataObject.Module.Row_c_user)GetInformation.NewRow();
string r = Data["PageRow"].ToString() == "" ? "10" : Data["PageRow"].ToString();
string i = Data["PageCount"].ToString() == "" ? "1" : Data["PageCount"].ToString();
int PageRow = Convert.ToInt32(r == "0" ? "10" : r);
int PageCount = Convert.ToInt32(i == "0" ? "1" : i);
string emp_no = Data["emp_no"].ToString();
string strSql = $@"select * from c_user order by edit_time ";
System.Data.DataSet res = SFCDB.ExecSelect(strSql);
int MaxPage = res.Tables[0].Rows.Count / PageRow + 1;
if (MaxPage >= PageCount)
{
DataTable dt = GetInformation.SelectC_User(PageRow, PageCount, emp_no, SFCDB, DB_TYPE_ENUM.Oracle);
if (dt.Rows.Count > 0)
{
List <c_user_model> lsit = new List <c_user_model>();
foreach (DataRow item in dt.Rows)
{
lsit.Add(new c_user_model
{
ID = item["ID"].ToString(),
FACTORY = item["FACTORY"].ToString(),
BU_NAME = item["BU_NAME"].ToString(),
EMP_NO = item["EMP_NO"].ToString(),
EMP_NAME = item["EMP_NAME"].ToString(),
EMP_PASSWORD = item["EMP_PASSWORD"].ToString(),
EMP_LEVEL = item["EMP_LEVEL"].ToString(),
DPT_NAME = item["DPT_NAME"].ToString(),
POSITION_NAME = item["POSITION_NAME"].ToString(),
MAIL_ADDRESS = item["MAIL_ADDRESS"].ToString(),
PHONE_NUMBER = item["PHONE_NUMBER"].ToString(),
LOCATION = item["LOCATION"].ToString(),
LOCK_FLAG = item["LOCK_FLAG"].ToString(),
AGENT_EMP_NO = item["AGENT_EMP_NO"].ToString(),
EMP_DESC = item["EMP_DESC"].ToString(),
EDIT_EMP = item["EDIT_EMP"].ToString(),
EMP_EN_NAME = item["EMP_EN_NAME"].ToString()
});
}
StationReturn.Status = StationReturnStatusValue.Pass;
StationReturn.Message = "成功取到用戶信息！";
StationReturn.Data = lsit;
}
else
{
StationReturn.Status = StationReturnStatusValue.Fail;
StationReturn.Message = "無此用戶信息！";
}
}
else
{
StationReturn.Status = StationReturnStatusValue.Fail;
StationReturn.Message = "所需分頁數(" + PageCount + ")大於最大分頁(" + MaxPage + ")";
}
this.DBPools["SFCDB"].Return(SFCDB);
}