// /// <summary>
// /// 使用者管理-維護指派單位
// /// </summary>
// /// <param name="userRole"></param>
// /// <param name="keyAgentID"></param>
// /// <param name="keyRoleID"></param>
// /// <param name="keyEnableDate"></param>
// /// <param name="keyDisableDate"></param>
// /// <param name="conn"></param>
// /// <param name="transaction"></param>
// /// <returns></returns>
// public CODEUSERROLE update(CODEUSERROLE userRole, string keyAgentID, string keyRoleID, string keyEnableDate, string keyDisableDate,
// SqlConnection conn, SqlTransaction transaction)
// {
// string[] curDateTime = DateUtil.getCurDateTime("yyyyMMdd HHmmss").Split(' ');
// userRole.COPRDATE = curDateTime[0];
// userRole.COPRTIME = curDateTime[1];
// string sql = @"update CODEUSERROLE set
//CROLEID = @CROLEID,
//CENABLEDATE = @CENABLEDATE,
//CDISABLEDATE = @CDISABLEDATE,
//COPRDATE = @COPRDATE,
//COPRTIME = @COPRTIME
// where CAGENTID = @KEYAGENTID
// and CROLEID = @KEYROLEID
// and CENABLEDATE = @KEYENABLEDATE
// and CDISABLEDATE = @KEYDISABLEDATE
//";
// SqlCommand command = conn.CreateCommand();
// command.Connection = conn;
// command.Transaction = transaction;
// try
// {
// command.CommandText = sql;
// command.Parameters.AddWithValue("@CROLEID", userRole.CROLEID.Trim());
// command.Parameters.AddWithValue("@CENABLEDATE", userRole.CENABLEDATE.Trim());
// command.Parameters.AddWithValue("@CDISABLEDATE", userRole.CDISABLEDATE.Trim());
// command.Parameters.AddWithValue("@COPRDATE", userRole.COPRDATE.Trim());
// command.Parameters.AddWithValue("@COPRTIME", userRole.COPRTIME.Trim());
// command.Parameters.AddWithValue("@KEYAGENTID", keyAgentID);
// command.Parameters.AddWithValue("@KEYROLEID", keyRoleID);
// command.Parameters.AddWithValue("@KEYENABLEDATE", keyEnableDate);
// command.Parameters.AddWithValue("@KEYDISABLEDATE", keyDisableDate);
// int cnt = command.ExecuteNonQuery();
// return userRole;
// }
// catch (Exception e)
// {
// throw e;
// }
// }
/// <summary>
/// 以鍵項刪除使用者角色資料
/// </summary>
/// <param name="userRole"></param>
/// <param name="conn"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public int delete(CODE_USER_ROLE userRole, SqlConnection conn, SqlTransaction transaction)
{
string sql = @"
delete CODE_USER_ROLE
where USER_ID = @USER_ID
and ROLE_ID = @ROLE_ID
";
SqlCommand command = conn.CreateCommand();
command.Connection = conn;
command.Transaction = transaction;
try
{
command.CommandText = sql;
command.Parameters.AddWithValue("@USER_ID", StringUtil.toString(userRole.USER_ID));
command.Parameters.AddWithValue("@ROLE_ID", StringUtil.toString(userRole.ROLE_ID));
int cnt = command.ExecuteNonQuery();
return(cnt);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 處理使用者角色異動
/// </summary>
/// <param name="roleId"></param>
/// <param name="aplyNO"></param>
/// <param name="conn"></param>
/// <param name="transaction"></param>
private void procUserRoleHis(CODE_USER cODEUSERO, string aplyNo, SqlConnection conn, SqlTransaction transaction)
{
CodeUserRoleHisDao codeUserRoleHisDao = new CodeUserRoleHisDao();
List <CodeUserRoleModel> cRoleList = codeUserRoleHisDao.qryByAplyNo(aplyNo);
if (cRoleList != null)
{
if (cRoleList.Count > 0)
{
CodeUserRoleDao codeUserRoleDao = new CodeUserRoleDao();
foreach (CodeUserRoleModel d in cRoleList)
{
CODE_USER_ROLE dRole = new CODE_USER_ROLE();
Log log = new Log();
switch (d.execAction)
{
case "A":
dRole.USER_ID = cODEUSERO.USER_ID;
dRole.ROLE_ID = d.roleId;
dRole.CREATE_UID = cODEUSERO.LAST_UPDATE_UID;
dRole.CREATE_DT = cODEUSERO.LAST_UPDATE_DT;
//新增資料
codeUserRoleDao.insert(dRole, conn, transaction);
//新增LOG
log.CFUNCTION = "使用者管理(角色授權)-新增";
log.CACTION = "A";
log.CCONTENT = codeUserRoleDao.logContent(dRole);
LogDao.Insert(log, Session["UserID"].ToString());
break;
case "D":
dRole = codeUserRoleDao.qryByKey(cODEUSERO.USER_ID, d.roleId);
//新增LOG
log.CFUNCTION = "使用者管理(角色授權)-刪除";
log.CACTION = "D";
log.CCONTENT = codeUserRoleDao.logContent(dRole);
LogDao.Insert(log, Session["UserID"].ToString());
//刪除資料
codeUserRoleDao.delete(dRole, conn, transaction);
break;
default:
break;
}
}
}
}
}
// /// <summary>
// /// 以"身份證字號"為鍵項，刪除使用者角色
// /// </summary>
// /// <param name="cAgentID"></param>
// /// <param name="conn"></param>
// /// <param name="transaction"></param>
// public void delByAgentID(string cAgentID, SqlConnection conn, SqlTransaction transaction)
// {
// string sql = @"
//delete CODEUSERROLE
// where CAGENTID = @CAGENTID
//";
// SqlCommand command = conn.CreateCommand();
// command.Connection = conn;
// command.Transaction = transaction;
// try
// {
// command.CommandText = sql;
// command.Parameters.AddWithValue("@CAGENTID", cAgentID.Trim());
// int cnt = command.ExecuteNonQuery();
// }
// catch (Exception e)
// {
// throw e;
// }
// }
public String logContent(CODE_USER_ROLE userRole)
{
String content = "";
content += StringUtil.toString(userRole.USER_ID) + '|';
content += StringUtil.toString(userRole.ROLE_ID) + '|';
content += StringUtil.toString(userRole.CREATE_UID) + '|';
content += userRole.CREATE_DT == null ? "|" : userRole.CREATE_DT + "|";
return(content);
}
public CODE_USER_ROLE qryByKey(string userId, string roleId)
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = IsolationLevel.ReadUncommitted
}))
{
using (dbTreasuryEntities db = new dbTreasuryEntities())
{
CODE_USER_ROLE userRole = db.CODE_USER_ROLE.Where(x =>
x.USER_ID.Equals(userId) &&
x.ROLE_ID.Equals(roleId)
).FirstOrDefault();
return(userRole);
}
}
}
///// <summary>
///// 查詢使用者角色檔中，仍為有效的資料
///// </summary>
///// <param name="sysDate"></param>
///// <returns></returns>
//public List<SrcOprUnitJobModel> qryValidUserRole(String sysDate)
//{
// using (DbAccountEntities db = new DbAccountEntities())
// {
// SrcOprUnitJobModel srcOprUnitJobModel = new SrcOprUnitJobModel();
// var rows = (from ur in db.CODEUSERROLE.Where(x => x.CDISABLEDATE.CompareTo(sysDate) >= 0)
// join u in db.CODEUSER on ur.CAGENTID equals u.CAGENTID
// join r in db.CODEROLE on ur.CROLEID equals r.CROLEID
// select new SrcOprUnitJobModel
// {
// cAgentID = u.CAGENTID.Trim(),
// cWorkUnitCode = u.CWORKUNITCODE.Trim(),
// cWorkUnitSeq = u.CWORKUNITSEQ.Trim(),
// cRoleID = r.CROLEID.Trim(),
// cOperatorArea = r.COPERATORAREA.Trim(),
// cSearchArea = r.CSEARCHAREA.Trim()
// }).OrderBy(x => x.cAgentID).Distinct().ToList();
// return rows;
// }
//}
/// <summary>
/// 新增使用者角色資料
/// </summary>
/// <param name="userRole"></param>
/// <param name="conn"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public CODE_USER_ROLE insert(CODE_USER_ROLE userRole, SqlConnection conn, SqlTransaction transaction)
{
string sql = @"insert into CODE_USER_ROLE
([USER_ID]
,[ROLE_ID]
,[CREATE_UID]
,[CREATE_DT])
VALUES
(@USER_ID
,@ROLE_ID
,@CREATE_UID
,@CREATE_DT)
";
SqlCommand command = conn.CreateCommand();
command.Connection = conn;
command.Transaction = transaction;
try
{
command.CommandText = sql;
command.Parameters.AddWithValue("@USER_ID", userRole.USER_ID.Trim());
command.Parameters.AddWithValue("@ROLE_ID", userRole.ROLE_ID.Trim());
command.Parameters.AddWithValue("@CREATE_UID", userRole.CREATE_UID.Trim());
command.Parameters.Add("@CREATE_DT", System.Data.SqlDbType.DateTime).Value = (System.Object)userRole.CREATE_DT ?? System.DBNull.Value;
int cnt = command.ExecuteNonQuery();
return(userRole);
}
catch (Exception e)
{
throw e;
}
}
