/// <summary>
/// 檢核使用者是否有使用特定功能的權限
/// </summary>
/// <param name="userId"></param>
/// <param name="funcId"></param>
/// <returns></returns>
public String[] qryOpScope(String userId, String funcId)
{
String[] result = new string[] { };
using (dbTreasuryEntities db = new dbTreasuryEntities())
{
//AwarkMainDetailModel awarkMainDetalModel = new AwarkMainDetailModel();
var rows = (from userRole in db.CODE_USER_ROLE
join role in db.CODE_ROLE on userRole.ROLE_ID equals role.ROLE_ID
join roleFunc in db.CODE_ROLE_FUNC on userRole.ROLE_ID.Trim() equals roleFunc.ROLE_ID.Trim()
join func in db.CODE_FUNC on roleFunc.FUNC_ID.Trim() equals func.FUNC_ID.Trim()
where userRole.USER_ID == userId
& func.FUNC_URL.StartsWith(funcId)
& func.IS_DISABLED.Trim() == "N"
& role.IS_DISABLED.Trim() == "N"
select new //AwarkMainDetailModel
{
roleId = role.ROLE_ID
}).FirstOrDefault();
if (rows != null)
{
result = new String[] { rows.roleId.Trim() };
}
return(result);
}
}
/// <summary>
/// 使用者角色權限查詢
/// </summary>
/// <returns></returns>
public List <UserMgrModel> qryUserRole()
{
List <UserMgrModel> rows = new List <UserMgrModel>();
using (dbTreasuryEntities db = new dbTreasuryEntities())
{
rows = (from u in db.CODE_USER
join ur in db.CODE_USER_ROLE on u.USER_ID equals ur.USER_ID
join r in db.CODE_ROLE on ur.ROLE_ID equals r.ROLE_ID
where 1 == 1
& u.IS_DISABLED == "N"
select new UserMgrModel()
{
cUserID = u.USER_ID.Trim(),
roleAuthType = r.ROLE_AUTH_TYPE.Trim(),
roleName = r.ROLE_NAME.Trim()
}).Distinct()
.ToList <UserMgrModel>();
return(rows);
}
}
//角色管理-查詢存取項目權限
public List <FuncRoleModel> qryForRoleMgr(string roleId)
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = IsolationLevel.ReadUncommitted
}))
{
using (dbTreasuryEntities db = new dbTreasuryEntities())
{
var rows = (from func in db.CODE_FUNC
join role in db.CODE_ROLE_FUNC on func.FUNC_ID equals role.FUNC_ID
where 1 == 1
& func.IS_DISABLED == "N"
& role.ROLE_ID == roleId
orderby(func.FUNC_LEVEL == 1 ? func.FUNC_ID : func.PARENT_FUNC_ID), func.PARENT_FUNC_ID, func.FUNC_ORDER
select new FuncRoleModel
{
cFunctionID = func.FUNC_ID,
cFunctionName = func.FUNC_NAME,
iFunctionLevel = func.FUNC_LEVEL,
cParentFunctionID = func.PARENT_FUNC_ID
}).ToList <FuncRoleModel>();
return(rows);
}
}
}
/// <summary>
/// 依申請單號查詢角色功能資料異動檔
/// </summary>
/// <param name="aplyNo"></param>
/// <returns></returns>
public List <RoleFuncHisModel> qryByAplyNo(String aplyNo)
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
}))
{
using (dbTreasuryEntities db = new dbTreasuryEntities())
{
List <RoleFuncHisModel> rows = (from main in db.CODE_ROLE_FUNC_HIS
join func in db.CODE_FUNC on main.FUNC_ID equals func.FUNC_ID
join cType in db.SYS_CODE.Where(x => x.CODE_TYPE == "EXEC_ACTION") on main.EXEC_ACTION equals cType.CODE into psCType
from xType in psCType.DefaultIfEmpty()
where main.APLY_NO == aplyNo
select new RoleFuncHisModel
{
aplyNo = main.APLY_NO.Trim(),
cFunctionID = main.FUNC_ID.Trim(),
cFunctionName = func.FUNC_NAME.Trim(),
execAction = main.EXEC_ACTION,
execActionDesc = xType == null ? "" : xType.CODE_VALUE.Trim()
}).ToList();
return(rows);
}
}
}
/// <summary>
/// 取得金庫設備下拉選單
/// </summary>
/// <param name="cType"></param>
/// <returns></returns>
public SelectList loadSelectList()
{
dbTreasuryEntities context = new dbTreasuryEntities();
//var result = context.TypeDefine.Where(x => x.CTYPE == cType).OrderBy(x => x.ISORTBY);
var result1 = (from equip in context.TREA_EQUIP
join syscode in context.SYS_CODE
on equip.CONTROL_MODE equals syscode.CODE
where equip.IS_DISABLED == "N" &&
syscode.CODE_TYPE == "CONTROL_MODE"
orderby equip.TREA_EQUIP_ID
select new
{
CCODE = equip.TREA_EQUIP_ID.Trim(),
CVALUE = equip.EQUIP_NAME.Trim() + "(" + syscode.CODE_VALUE + ")"
}
);
var items = new SelectList
(
items: result1,
dataValueField: "CCODE",
dataTextField: "CVALUE",
selectedValue: (object)null
);
return(items);
}
/// <summary>
/// 畫面下拉選單
/// </summary>
/// <param name="cType"></param>
/// <returns></returns>
public SelectList loadSelectList(String cType)
{
dbTreasuryEntities context = new dbTreasuryEntities();
//var result = context.TypeDefine.Where(x => x.CTYPE == cType).OrderBy(x => x.ISORTBY);
var result1 = (from type in context.SYS_CODE
where type.CODE_TYPE == cType
orderby type.ISORTBY
select new
{
CCODE = type.CODE.Trim(),
CVALUE = type.CODE_VALUE.Trim()
}
);
var items = new SelectList
(
items: result1,
dataValueField: "CCODE",
dataTextField: "CVALUE",
selectedValue: (object)null
);
return(items);
}
/// <summary>
/// 取得金庫設備下拉選單
/// </summary>
/// <param name="cType"></param>
/// <returns></returns>
public SelectList loadSelectList(string opType)
{
bool bopType = StringUtil.isEmpty(opType);
dbTreasuryEntities context = new dbTreasuryEntities();
//var result = context.TypeDefine.Where(x => x.CTYPE == cType).OrderBy(x => x.ISORTBY);
var result1 = (from equip in context.TREA_ITEM
where equip.IS_DISABLED == "N"
& (bopType || (equip.ITEM_OP_TYPE == opType.Trim()))
orderby equip.ITEM_ID
select new
{
CCODE = equip.ITEM_ID.Trim(),
CVALUE = equip.ITEM_DESC.Trim()
}
);
var items = new SelectList
(
items: result1,
dataValueField: "CCODE",
dataTextField: "CVALUE",
selectedValue: (object)null
);
return(items);
}
/// <summary>
/// 依角色查詢
/// </summary>
/// <param name="roleId"></param>
/// <returns></returns>
public List <RoleFuncHisModel> qryForAppr(string roleId)
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
}))
{
using (dbTreasuryEntities db = new dbTreasuryEntities())
{
List <RoleFuncHisModel> rows = (from main in db.CODE_ROLE_FUNC
join func in db.CODE_FUNC on main.FUNC_ID equals func.FUNC_ID
where main.ROLE_ID == roleId
select new RoleFuncHisModel
{
aplyNo = "",
cFunctionID = main.FUNC_ID.Trim(),
cFunctionName = func.FUNC_NAME.Trim(),
execAction = "",
execActionDesc = ""
}).ToList();
return(rows);
}
}
}
public List <RoleMgrModel> roleMgrQry(string codeRole, string roleAuthType, string isDIsabled, string vMemo, string cUpdUserID)
{
bool bCodeRole = StringUtil.isEmpty(codeRole);
bool bRoleAuthType = StringUtil.isEmpty(roleAuthType);
bool bisDIsabled = StringUtil.isEmpty(isDIsabled);
bool bcUpdUserID = StringUtil.isEmpty(cUpdUserID);
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
}))
{
using (dbTreasuryEntities db = new dbTreasuryEntities())
{
List <RoleMgrModel> roleList = (from role in db.CODE_ROLE
join codeFlag in db.SYS_CODE.Where(x => x.CODE_TYPE == "IS_DISABLED") on role.IS_DISABLED equals codeFlag.CODE into psFlag
from xFlag in psFlag.DefaultIfEmpty()
join codeReview in db.SYS_CODE.Where(x => x.CODE_TYPE == "DATA_STATUS") on role.DATA_STATUS equals codeReview.CODE into psReview
from xReview in psReview.DefaultIfEmpty()
join codeAuthType in db.SYS_CODE.Where(x => x.CODE_TYPE == "ROLE_AUTH_TYPE") on role.ROLE_AUTH_TYPE equals codeAuthType.CODE into psAuthType
from xAuthType in psAuthType.DefaultIfEmpty()
where 1 == 1
& (bCodeRole || (role.ROLE_ID == codeRole.Trim()))
& (bRoleAuthType || (role.ROLE_AUTH_TYPE == roleAuthType.Trim()))
& (bisDIsabled || (role.IS_DISABLED == isDIsabled.Trim()))
& (bcUpdUserID || (role.LAST_UPDATE_UID == cUpdUserID.Trim()))
orderby xAuthType.ISORTBY, role.ROLE_NAME
select new RoleMgrModel
{
cRoleID = role.ROLE_ID,
cRoleName = role.ROLE_NAME.Trim(),
roleAuthType = role.ROLE_AUTH_TYPE,
roleAuthTypeDesc = (xAuthType == null ? String.Empty : xAuthType.CODE_VALUE),
isDisabled = (xFlag == null ? String.Empty : xFlag.CODE_VALUE),
vMemo = role.MEMO,
freezeUid = role.FREEZE_UID == null ? "" : role.FREEZE_UID,
cUpdUserID = role.LAST_UPDATE_UID == null ? "" : role.LAST_UPDATE_UID,
cUpdDateTime = role.LAST_UPDATE_DT == null ? "" : SqlFunctions.DateName("year", role.LAST_UPDATE_DT) + "/" +
SqlFunctions.DatePart("m", role.LAST_UPDATE_DT) + "/" +
SqlFunctions.DateName("day", role.LAST_UPDATE_DT).Trim() + " " +
SqlFunctions.DateName("hh", role.LAST_UPDATE_DT).Trim() + ":" +
SqlFunctions.DateName("n", role.LAST_UPDATE_DT).Trim() + ":" +
SqlFunctions.DateName("s", role.LAST_UPDATE_DT).Trim(),
dataStatus = (xReview == null ? String.Empty : xReview.CODE_VALUE)
}).ToList();
return(roleList);
}
}
}
/// <summary>
/// 以鍵項查詢金庫設備
/// </summary>
/// <param name="equip"></param>
/// <returns></returns>
public TREA_EQUIP qryByKey(String equipId)
{
dbTreasuryEntities context = new dbTreasuryEntities();
var result = context.TREA_EQUIP.Where(x => x.TREA_EQUIP_ID == equipId).FirstOrDefault();
return(result);
}
/// <summary>
/// 依type查代碼
/// </summary>
/// <param name="cType"></param>
/// <returns></returns>
public List <SYS_CODE> qryByType(String cType)
{
dbTreasuryEntities context = new dbTreasuryEntities();
var result = context.SYS_CODE.Where(x => x.CODE_TYPE == cType).OrderBy(x => x.ISORTBY).ToList();
return(result);
}
/// <summary>
/// 查詢修改前後的資料
/// </summary>
/// <param name="db"></param>
/// <param name="cRoleID"></param>
/// <param name="apprStatus"></param>
/// <param name="updDateB"></param>
/// <param name="updDateE"></param>
/// <returns></returns>
public List <CodeRoleItemModel> qryForRoleMgrHis(dbTreasuryEntities db, string cRoleID, string apprStatus, string updDateB, string updDateE)
{
bool bapprStatus = StringUtil.isEmpty(apprStatus);
bool bDateB = StringUtil.isEmpty(updDateB);
bool bDateE = StringUtil.isEmpty(updDateE);
DateTime sB = DateTime.Now.AddDays(1);
if (!bDateB)
{
sB = Convert.ToDateTime(updDateB);
}
DateTime sE = DateTime.Now.AddDays(1);
if (!bDateE)
{
sE = Convert.ToDateTime(updDateE);
}
sE = sE.AddDays(1);
var roleItemHis = (from m in db.CODE_ROLE_ITEM_HIS
join item in db.TREA_ITEM on m.ITEM_ID equals item.ITEM_ID
join appr in db.AUTH_APPR on m.APLY_NO equals appr.APLY_NO into psAppr
from xAppr in psAppr.DefaultIfEmpty()
join cType in db.SYS_CODE.Where(x => x.CODE_TYPE == "ITEM_OP_TYPE") on item.ITEM_OP_TYPE equals cType.CODE into psCType
from xType in psCType.DefaultIfEmpty()
where m.ROLE_ID == cRoleID
& (bapprStatus || xAppr.APPR_STATUS == apprStatus.Trim())
& (bDateB || xAppr.CREATE_DT >= sB)
& (bDateE || xAppr.CREATE_DT <= sE)
select new CodeRoleItemModel
{
authType = m.AUTH_TYPE,
aplyNo = m.APLY_NO.Trim(),
apprUid = xAppr.APPR_UID.Trim(),
apprStatus = xAppr.APPR_STATUS.Trim(),
updateDT = SqlFunctions.DateName("year", xAppr.CREATE_DT) + "/" +
SqlFunctions.DatePart("m", xAppr.CREATE_DT) + "/" +
SqlFunctions.DateName("day", xAppr.CREATE_DT).Trim(),
updateUid = xAppr.CREATE_UID.Trim(),
execAction = m.EXEC_ACTION.Trim(),
itemOpType = xType == null ? item.ITEM_OP_TYPE : xType.CODE_VALUE.Trim(),
itemId = m.ITEM_ID.Trim(),
itemDesc = item.ITEM_DESC.Trim()
}).ToList <CodeRoleItemModel>();
return(roleItemHis);
}
/// <summary>
/// 以鍵項查詢
/// </summary>
/// <param name="codeType"></param>
/// <param name="code"></param>
/// <returns></returns>
public SYS_CODE qryByKey(string codeType, string code)
{
dbTreasuryEntities context = new dbTreasuryEntities();
SYS_CODE sysCode = new SYS_CODE();
sysCode = context.SYS_CODE.Where(x => x.CODE_TYPE == codeType && x.CODE == code)
.OrderBy(x => x.ISORTBY).FirstOrDefault();
return(sysCode);
}
/// <summary>
/// 查歷史異動
/// </summary>
/// <param name="db"></param>
/// <param name="userId"></param>
/// <param name="apprStatus"></param>
/// <param name="updDateB"></param>
/// <param name="updDateE"></param>
/// <returns></returns>
public List <UserRoleHisModel> qryForUserMgrHis(dbTreasuryEntities db, string userId, string apprStatus, string updDateB, string updDateE)
{
bool bapprStatus = StringUtil.isEmpty(apprStatus);
bool bDateB = StringUtil.isEmpty(updDateB);
bool bDateE = StringUtil.isEmpty(updDateE);
DateTime sB = DateTime.Now.AddDays(1);
if (!bDateB)
{
sB = Convert.ToDateTime(updDateB);
}
DateTime sE = DateTime.Now.AddDays(1);
if (!bDateE)
{
sE = Convert.ToDateTime(updDateE);
}
sE = sE.AddDays(1);
var userRoleHis = (from m in db.CODE_USER_ROLE_HIS
join appr in db.AUTH_APPR on m.APLY_NO equals appr.APLY_NO into psAppr
from xAppr in psAppr.DefaultIfEmpty()
join role in db.CODE_ROLE on m.ROLE_ID equals role.ROLE_ID
join cAuthType in db.SYS_CODE.Where(x => x.CODE_TYPE == "ROLE_AUTH_TYPE") on role.ROLE_AUTH_TYPE equals cAuthType.CODE into psAuthType
from xAuthType in psAuthType.DefaultIfEmpty()
where m.USER_ID == userId
& (bapprStatus || xAppr.APPR_STATUS == apprStatus.Trim())
& (bDateB || xAppr.CREATE_DT >= sB)
& (bDateE || xAppr.CREATE_DT <= sE)
select new UserRoleHisModel
{
aplyNo = m.APLY_NO.Trim(),
updateDT = SqlFunctions.DateName("year", xAppr.CREATE_DT) + "/" +
SqlFunctions.DatePart("m", xAppr.CREATE_DT) + "/" +
SqlFunctions.DateName("day", xAppr.CREATE_DT).Trim(),
updateUid = xAppr.CREATE_UID.Trim(),
execAction = m.EXEC_ACTION.Trim(),
cRoleID = m.ROLE_ID.Trim(),
cRoleName = role.ROLE_NAME.Trim(),
roleAuthType = xAuthType == null ? "" : xAuthType.CODE_VALUE.Trim(),
apprStatus = xAppr.APPR_STATUS.Trim()
}).ToList <UserRoleHisModel>();
return(userRoleHis);
}
public JsonResult LoadData(String cReviewType)
{
AuthApprDao authApprDao = new AuthApprDao();
List <AuthReviewModel> rows = new List <AuthReviewModel>();
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = IsolationLevel.ReadUncommitted
}))
{
using (dbTreasuryEntities db = new dbTreasuryEntities())
{
try
{
//查出待覆核的資料
rows = authApprDao.qryAuthReview("R", "1", db);
}
catch (Exception e)
{
logger.Error("其它錯誤：" + e.ToString());
return(Json(new { success = false, err = "其它錯誤，請洽系統管理員!!" }));
}
}
}
using (DB_INTRAEntities dbIntra = new DB_INTRAEntities())
{
Dictionary <string, string> userNameMap = new Dictionary <string, string>();
OaEmpDao oaEmpDao = new OaEmpDao();
string createUid = "";
foreach (AuthReviewModel d in rows)
{
createUid = StringUtil.toString(d.createUid);
if (!"".Equals(createUid))
{
if (!userNameMap.ContainsKey(createUid))
{
userNameMap = oaEmpDao.qryUsrName(userNameMap, createUid, dbIntra);
}
d.createUid = createUid + " " + userNameMap[createUid];
}
}
}
var jsonData = new { success = true, rows };
return(Json(jsonData, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 查詢修改前後的資料
/// </summary>
/// <param name="db"></param>
/// <param name="cRoleID"></param>
/// <param name="apprStatus"></param>
/// <param name="updDateB"></param>
/// <param name="updDateE"></param>
/// <returns></returns>
public List <RoleFuncHisModel> qryForRoleMgrHis(dbTreasuryEntities db, string cRoleID, string apprStatus, string updDateB, string updDateE)
{
bool bapprStatus = StringUtil.isEmpty(apprStatus);
bool bDateB = StringUtil.isEmpty(updDateB);
bool bDateE = StringUtil.isEmpty(updDateE);
DateTime sB = DateTime.Now.AddDays(1);
if (!bDateB)
{
sB = Convert.ToDateTime(updDateB);
}
DateTime sE = DateTime.Now.AddDays(1);
if (!bDateE)
{
sE = Convert.ToDateTime(updDateE);
}
sE = sE.AddDays(1);
var roleFuncHis = (from m in db.CODE_ROLE_FUNC_HIS
join appr in db.AUTH_APPR on m.APLY_NO equals appr.APLY_NO into psAppr
from xAppr in psAppr.DefaultIfEmpty()
join func in db.CODE_FUNC on m.FUNC_ID equals func.FUNC_ID into psFunc
from xFunc in psFunc.DefaultIfEmpty()
where m.ROLE_ID == cRoleID
& (bapprStatus || xAppr.APPR_STATUS == apprStatus.Trim())
& (bDateB || xAppr.CREATE_DT >= sB)
& (bDateE || xAppr.CREATE_DT <= sE)
select new RoleFuncHisModel
{
aplyNo = m.APLY_NO.Trim(),
apprUid = xAppr.APPR_UID,
apprStatus = xAppr.APPR_STATUS.Trim(),
updateDT = SqlFunctions.DateName("year", xAppr.CREATE_DT) + "/" +
SqlFunctions.DatePart("m", xAppr.CREATE_DT) + "/" +
SqlFunctions.DateName("day", xAppr.CREATE_DT).Trim(),
updateUid = xAppr.CREATE_UID.Trim(),
execAction = m.EXEC_ACTION.Trim(),
cFunctionID = m.FUNC_ID.Trim(),
cFunctionName = xFunc.FUNC_NAME.Trim()
}).ToList <RoleFuncHisModel>();
return(roleFuncHis);
}
/**
* 查詢出所有角色資料(for畫面下拉選單使用)
**/
public SelectList loadSelectList()
{
dbTreasuryEntities context = new dbTreasuryEntities();
var result = context.CODE_ROLE;
var items = new SelectList
(
items: result,
dataValueField: "ROLE_ID",
dataTextField: "ROLE_NAME",
selectedValue: (object)null
);
return(items);
}
/// <summary>
/// 查歷史異動
/// </summary>
/// <param name="db"></param>
/// <param name="userId"></param>
/// <param name="apprStatus"></param>
/// <param name="updDateB"></param>
/// <param name="updDateE"></param>
/// <returns></returns>
public List <CodeUserHisModel> qryForUserMgrHis(dbTreasuryEntities db, string userId, string apprStatus, string updDateB, string updDateE)
{
bool bapprStatus = StringUtil.isEmpty(apprStatus);
bool bDateB = StringUtil.isEmpty(updDateB);
bool bDateE = StringUtil.isEmpty(updDateE);
DateTime sB = DateTime.Now.AddDays(1);
if (!bDateB)
{
sB = Convert.ToDateTime(updDateB);
}
DateTime sE = DateTime.Now.AddDays(1);
if (!bDateE)
{
sE = Convert.ToDateTime(updDateE);
}
sE = sE.AddDays(1);
var userHis = (from m in db.CODE_USER_HIS
join appr in db.AUTH_APPR on m.APLY_NO equals appr.APLY_NO into psAppr
from xAppr in psAppr.DefaultIfEmpty()
where m.USER_ID == userId
& (bapprStatus || xAppr.APPR_STATUS == apprStatus.Trim())
& (bDateB || xAppr.CREATE_DT >= sB)
& (bDateE || xAppr.CREATE_DT <= sE)
select new CodeUserHisModel
{
aplyNo = m.APLY_NO.Trim(),
updateDT = SqlFunctions.DateName("year", xAppr.CREATE_DT) + "/" +
SqlFunctions.DatePart("m", xAppr.CREATE_DT) + "/" +
SqlFunctions.DateName("day", xAppr.CREATE_DT).Trim(),
updateUid = xAppr.CREATE_UID.Trim(),
execAction = m.EXEC_ACTION.Trim(),
isDisabled = m.IS_DISABLED.Trim(),
isDisabledB = m.IS_DISABLED_B.Trim(),
isMail = m.IS_MAIL.Trim(),
isMailB = m.IS_MAIL_B.Trim(),
apprStatus = xAppr.APPR_STATUS.Trim()
}).ToList <CodeUserHisModel>();
return(userHis);
}
/// <summary>
/// 以申請單號查詢使用者異動資料
/// </summary>
/// <param name="aplyNo"></param>
/// <returns></returns>
public CODE_USER_HIS qryByAplyNo(String aplyNo)
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
}))
{
using (dbTreasuryEntities db = new dbTreasuryEntities())
{
CODE_USER_HIS user = db.CODE_USER_HIS.Where(x => x.APLY_NO == aplyNo).FirstOrDefault <CODE_USER_HIS>();
return(user);
}
}
}
/// <summary>
/// 以鍵項查詢使用者資料
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public CODE_USER qryUserByKey(String userId)
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
}))
{
using (dbTreasuryEntities db = new dbTreasuryEntities())
{
CODE_USER codeUser = db.CODE_USER.Where(x => x.USER_ID == userId).FirstOrDefault <CODE_USER>();
return(codeUser);
}
}
}
public AUTH_APPR qryByKey(String aplyNo)
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
}))
{
using (dbTreasuryEntities db = new dbTreasuryEntities())
{
AUTH_APPR authAppr = db.AUTH_APPR.Where(x => x.APLY_NO == aplyNo).FirstOrDefault <AUTH_APPR>();
return(authAppr);
}
}
}
public Dictionary <string, string> qryByTypeDic(String cType)
{
dbTreasuryEntities context = new dbTreasuryEntities();
var result1 = (from type in context.SYS_CODE
where type.CODE_TYPE == cType
orderby type.ISORTBY
select new
{
CCODE = type.CODE.Trim(),
CVALUE = type.CODE_VALUE.Trim()
}
).ToDictionary(x => x.CCODE, x => x.CVALUE);
return(result1);
}
/// <summary>
/// 角色金庫設備報表清單
/// </summary>
/// <returns></returns>
public List <CodeRoleEquipModel> qryRoleEquip()
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = IsolationLevel.ReadUncommitted
}))
{
using (dbTreasuryEntities db = new dbTreasuryEntities())
{
var rows = (from main in db.CODE_ROLE_TREA_ITEM
join role in db.CODE_ROLE on main.ROLE_ID equals role.ROLE_ID
join d in db.TREA_EQUIP on main.TREA_EQUIP_ID equals d.TREA_EQUIP_ID into psEquip
from xEquip in psEquip.DefaultIfEmpty()
join custody in db.SYS_CODE.Where(x => x.CODE_TYPE == "CUSTODY_MODE") on main.CUSTODY_MODE equals custody.CODE into psCustody
from xCustody in psCustody.DefaultIfEmpty()
join control in db.SYS_CODE.Where(x => x.CODE_TYPE == "CONTROL_MODE") on xEquip.CONTROL_MODE equals control.CODE into psControl
from xControl in psControl.DefaultIfEmpty()
where 1 == 1
& role.IS_DISABLED == "N"
select new CodeRoleEquipModel
{
roleId = main.ROLE_ID,
roleName = role.ROLE_NAME.Trim(),
execAction = "",
execActionDesc = "",
treaEquipId = main.TREA_EQUIP_ID,
equipName = xEquip.EQUIP_NAME.Trim(),
controlMode = xEquip.CONTROL_MODE,
controlModeDesc = xControl.CODE_VALUE.Trim(),
custodyMode = main.CUSTODY_MODE,
custodyModeDesc = xCustody.CODE_VALUE.Trim(),
custodyOrder = main.CUSTODY_ORDER.ToString()
}).ToList <CodeRoleEquipModel>();
return(rows);
}
}
}
/**
* 查詢角色檔(以"角色編號"為鍵項)
**/
public CODE_ROLE qryRoleByKey(String roleId)
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
}))
{
using (dbTreasuryEntities db = new dbTreasuryEntities())
{
CODE_ROLE codeRole = db.CODE_ROLE.Where(x => x.ROLE_ID == roleId).FirstOrDefault <CODE_ROLE>();
return(codeRole);
}
}
}
public AUTH_APPR qryByFreeRole(String roleId)
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
}))
{
using (dbTreasuryEntities db = new dbTreasuryEntities())
{
AUTH_APPR authAppr = db.AUTH_APPR.Where(x => x.APPR_MAPPING_KEY == roleId & x.APPR_STATUS == "1").FirstOrDefault <AUTH_APPR>();
return(authAppr);
}
}
}
public List <SelectOption> selectCodeUser(string ROLE_ID = null)
{
List <SelectOption> results = new List <SelectOption>()
{
new SelectOption()
{
Text = "請選擇", Value = " "
}
};
List <string> userIds = new List <string>();
using (dbTreasuryEntities db = new dbTreasuryEntities())
{
if (string.IsNullOrWhiteSpace(ROLE_ID))
{
userIds = db.CODE_USER.AsNoTracking().Where(x => x.IS_DISABLED != "Y").Select(x => x.USER_ID).ToList();
}
else
{
userIds = db.CODE_ROLE.AsNoTracking().Where(x => x.ROLE_ID == ROLE_ID)
//.Join(db.CODE_USER_ROLE.AsNoTracking(),
//x => x.ROLE_ID,
//y => y.ROLE_ID,
//(x, y) => y)
.Join(db.CODE_USER.AsNoTracking().Where(x => x.IS_DISABLED != "Y"),
x => x.LAST_UPDATE_UID,
y => y.USER_ID,
(x, y) => y)
.Select(x => x.USER_ID).ToList();
}
}
using (DB_INTRAEntities intra = new DB_INTRAEntities())
{
var emps = intra.V_EMPLY2.AsNoTracking().ToList();
foreach (var userId in userIds)
{
results.Add(new SelectOption()
{
Value = userId,
Text = $@"{userId} {emps.FirstOrDefault(x => x.USR_ID == userId)?.EMP_NAME}"
});
}
}
return(results);
}
public bool dupRoleName(string roleId, string authType, string roleName)
{
bool bDup = false;
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
}))
{
using (dbTreasuryEntities db = new dbTreasuryEntities())
{
CODE_ROLE role = db.CODE_ROLE
.Where(x => x.ROLE_ID != roleId && x.ROLE_AUTH_TYPE == authType && x.ROLE_NAME == roleName.Trim())
.FirstOrDefault();
if (role != null)
{
if (!"".Equals(StringUtil.toString(role.ROLE_ID)))
{
bDup = true;
}
}
if (!bDup)
{
string roleH = (from roleHis in db.CODE_ROLE_HIS
join aply in db.AUTH_APPR.Where(x => x.AUTH_APLY_TYPE == "R" & x.APPR_STATUS == "1") on roleHis.ROLE_ID equals aply.APPR_MAPPING_KEY
where roleHis.ROLE_ID != roleId
& roleHis.ROLE_AUTH_TYPE == authType
& roleHis.ROLE_NAME == roleName.Trim()
select roleHis.ROLE_ID).FirstOrDefault();
if (!"".Equals(StringUtil.toString(roleH)))
{
bDup = true;
}
}
}
}
return(bDup);
}
/// <summary>
/// 查詢角色已授權的功能
/// </summary>
/// <param name="cRoleId"></param>
public void qryUserFunc(String cRoleId)
{
dbTreasuryEntities context = new dbTreasuryEntities();
var result1 = (from func in context.CODE_FUNC
where 1 == 1
& func.IS_DISABLED == "N"
//& func.CSYSID == "sys006"
orderby(func.FUNC_LEVEL == 1 ? func.FUNC_ID : func.PARENT_FUNC_ID), func.PARENT_FUNC_ID, func.FUNC_ORDER
select new FuncRoleModel
{
cFunctionID = func.FUNC_ID,
cFunctionName = func.FUNC_NAME,
iFunctionLevel = func.FUNC_LEVEL
}
).ToList <FuncRoleModel>();
var authData = new
{
item = (
from func in context.CODE_FUNC
join role in context.CODE_ROLE_FUNC on func.FUNC_ID equals role.FUNC_ID
where 1 == 1
& func.IS_DISABLED == "N"
//& func.CSYSID == "sys006"
& role.ROLE_ID == cRoleId
orderby(func.FUNC_LEVEL == 1 ? func.FUNC_ID : func.PARENT_FUNC_ID), func.PARENT_FUNC_ID, func.FUNC_ORDER
select new
{
cFunctionID = func.FUNC_ID,
cFunctionName = func.FUNC_NAME,
iFunctionLevel = func.FUNC_LEVEL,
menuLevel = func.FUNC_LEVEL == 1 ? func.FUNC_ID : func.PARENT_FUNC_ID
}).ToArray()
};
ViewBag.funcList = result1;
ViewBag.userAuthFuncList = Json(authData, JsonRequestBehavior.AllowGet);
}
public CODE_ROLE_TREA_ITEM getRoleEquipByKey(string roleId, string treaEquipId)
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = IsolationLevel.ReadUncommitted
}))
{
using (dbTreasuryEntities db = new dbTreasuryEntities())
{
CODE_ROLE_TREA_ITEM roleEquip = db.CODE_ROLE_TREA_ITEM.Where(x => x.ROLE_ID == roleId &&
x.TREA_EQUIP_ID == treaEquipId).FirstOrDefault();
return(roleEquip);
}
}
}
public CODE_ROLE_ITEM getRoleItemByKey(string roleId, string itemId, string authType)
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = IsolationLevel.ReadUncommitted
}))
{
using (dbTreasuryEntities db = new dbTreasuryEntities())
{
CODE_ROLE_ITEM roleItem = db.CODE_ROLE_ITEM.Where(x => x.ROLE_ID == roleId &&
x.ITEM_ID == itemId && x.AUTH_TYPE == authType).FirstOrDefault();
return(roleItem);
}
}
}
