/// <summary>
/// 根据用户所属的角色ID和每页显示多少条数据返回用户信息
/// 创建：李东峰 日期：2014-03-10
/// </summary>
/// <param name="_grpId">用户所属的角色ID</param>
/// <param name="sCount">起始条数</param>
/// <param name="eCount">结束条数</param>
/// <returns>返回查询结果集</returns>
public DataTable GetUserByRoleId(int sCount, int eCount)
{
int t1 = eCount - sCount + 1;
int t2 = sCount - 1;
string rlDBType = dl.init();
string sqlSQL = "select top " + t1 + " * from (select ID_KEY,T_USERID,T_USERNAME from(select T_SYS_MEMBERINFO.ID_KEY,T_SYS_MEMBERINFO.T_USERID,T_SYS_MEMBERINFO.T_USERNAME,T_SYS_MEMBERGRP.T_GRPID from T_SYS_MEMBERINFO left JOIN T_SYS_MEMBERGRP ON T_SYS_MEMBERGRP.T_USERID=T_SYS_MEMBERINFO.T_USERID)as a where a.T_GRPID='" + RoleId + "')as b where (b.ID_KEY not in ( select top " + t2 + " ID_KEY from(select T_SYS_MEMBERINFO.ID_KEY,T_SYS_MEMBERINFO.T_USERID,T_SYS_MEMBERINFO.T_USERNAME,T_SYS_MEMBERGRP.T_GRPID from T_SYS_MEMBERINFO left JOIN T_SYS_MEMBERGRP ON T_SYS_MEMBERGRP.T_USERID=T_SYS_MEMBERINFO.T_USERID)as a where a.T_GRPID='" + RoleId + "'))";
string sqlDB2 = "select * from ( select a.ID_KEY,a.T_USERID,a.T_USERNAME,rownumber() over(order by ID_KEY asc ) as rowid from (select T_SYS_MEMBERINFO.ID_KEY,T_SYS_MEMBERINFO.T_USERID,T_SYS_MEMBERINFO.T_USERNAME,T_SYS_MEMBERGRP.T_GRPID from T_SYS_MEMBERINFO left JOIN T_SYS_MEMBERGRP ON T_SYS_MEMBERGRP.T_USERID=T_SYS_MEMBERINFO.T_USERID ORDER BY T_SYS_MEMBERINFO.ID_KEY)as a where a.T_GRPID='" + RoleId + "') as b where b.rowid between " + sCount + " and " + eCount + "";
string sqlORC = "select * from(select ID_KEY,T_USERID,T_USERNAME,ROWNUM rn from(select T_SYS_MEMBERINFO.ID_KEY,T_SYS_MEMBERINFO.T_USERID,T_SYS_MEMBERINFO.T_USERNAME,T_SYS_MEMBERGRP.T_GRPID from T_SYS_MEMBERINFO left JOIN T_SYS_MEMBERGRP ON T_SYS_MEMBERGRP.T_USERID=T_SYS_MEMBERINFO.T_USERID ORDER BY T_SYS_MEMBERINFO.ID_KEY) where T_GRPID='" + RoleId + "' and ROWNUM <= " + eCount + ")WHERE rn >= " + sCount + "";
string errMsg;
DataTable _dt = null;
if (rlDBType == "SQL")
{
try
{
_dt = dl.RunDataTable(sqlSQL, out errMsg);
}
catch (Exception ex)
{
LogHelper.WriteLog(LogHelper.EnLogType.Run, "发生时间：" + DateTime.Now.ToString("yyyy-MM-dd H:mm:ss") + "/n错误信息：" + ex.Message);
}
}
else if (rlDBType == "DB2")
{
try
{
_dt = dl.RunDataTable(sqlDB2, out errMsg);
}
catch (Exception ex)
{
LogHelper.WriteLog(LogHelper.EnLogType.Run, "发生时间：" + DateTime.Now.ToString("yyyy-MM-dd H:mm:ss") + "/n错误信息：" + ex.Message);
}
}
else if (rlDBType == "ORACLE")
{
try
{
_dt = dl.RunDataTable(sqlORC, out errMsg);
}
catch (Exception ex)
{
LogHelper.WriteLog(LogHelper.EnLogType.Run, "发生时间：" + DateTime.Now.ToString("yyyy-MM-dd H:mm:ss") + "/n错误信息：" + ex.Message);
}
}
return(_dt);
}
/// <summary>
/// 添加人员
/// 创建：李东峰 日期：2014-03-10
/// </summary>
/// <param name="_userName">用户真实姓名</param>
/// <param name="_passWord">用户密码</param>
/// <param name="_attachMent">用户照片</param>
/// <param name="_classId">用户班组</param>
/// <param name="_posId">用户岗位</param>
/// <param name="_posId">用户所属角色ID</param>
/// <returns>返回是否添加成功</returns>
public bool AddMember(string _userName, string _passWord, byte[] _attachMent, string _classId, string _posId, string _grpId)
{
string rlDBType = dl.init();
string _errMsg;
string sql1 = "";
string sql2 = "";
bool result = false;
if (rlDBType == "SQL")
{
if (_attachMent != null && _attachMent.Length > 0)
{
sql1 = "insert into T_SYS_MEMBERINFO(T_USERID,T_USERNAME,T_PASSWD,B_ATTACHMENT,T_CLASSID,T_POSID) values(@T_USERID,@T_USERNAME,@T_PASSWD,@B_ATTACHMENT,@T_CLASSID,@T_POSID);";
}
else
{
sql1 = "insert into T_SYS_MEMBERINFO(T_USERID,T_USERNAME,T_PASSWD,T_CLASSID,T_POSID) values(@T_USERID,@T_USERNAME,@T_PASSWD,@T_CLASSID,@T_POSID);";
}
sql2 = "insert into T_SYS_MEMBERGRP(T_USERID,T_GRPID) values('" + UserId + "','" + _grpId + "')";
try
{
SqlConnection sqlconn = SAC.DBOperations.DBsql.GetConnection();
SqlCommand sqlcmd = new SqlCommand(sql1, sqlconn);
if (_attachMent != null && _attachMent.Length > 0)
{
sqlcmd.Parameters.Add("@T_USERID", UserId);
sqlcmd.Parameters.Add("@T_USERNAME", _userName);
sqlcmd.Parameters.Add("@T_PASSWD", _passWord);
sqlcmd.Parameters.Add("@B_ATTACHMENT", _attachMent);
sqlcmd.Parameters.Add("@T_PASSWD", _classId);
sqlcmd.Parameters.Add("@T_POSID", _posId);
}
else
{
sqlcmd.Parameters.Add("@T_USERID", UserId);
sqlcmd.Parameters.Add("@T_USERNAME", _userName);
sqlcmd.Parameters.Add("@T_PASSWD", _passWord);
sqlcmd.Parameters.Add("@T_PASSWD", _classId);
sqlcmd.Parameters.Add("@T_POSID", _posId);
}
if (sqlcmd.ExecuteNonQuery() > 0)
{
result = true;
}
sqlconn.Close();
dl.RunNonQuery(sql2, out _errMsg);
}
catch (Exception ex)
{
LogHelper.WriteLog(LogHelper.EnLogType.Run, "发生时间：" + DateTime.Now.ToString("yyyy-MM-dd H:mm:ss") + "/n错误信息：" + ex.Message);
result = false;
}
}
else if (rlDBType == "DB2")
{
if (_attachMent != null && _attachMent.Length > 0)
{
sql1 = "insert into T_SYS_MEMBERINFO(T_USERID,T_USERNAME,T_PASSWD,B_ATTACHMENT,T_CLASSIS,T_POSID) values(?,?,?,?,?,?);";
}
else
{
sql1 = "insert into T_SYS_MEMBERINFO(T_USERID,T_USERNAME,T_PASSWD,T_CLASSIS,T_POSID) values(?,?,?,?,?);";
}
sql2 = "insert into T_SYS_MEMBERGRP(T_USERID,T_GRPID) values('" + UserId + "','" + _grpId + "')";
try
{
OleDbConnection con = new OleDbConnection(SAC.DBOperations.DBdb2.SetConString());
con.Open();
OleDbCommand oledbcom = new OleDbCommand(sql1, con);
if (_attachMent != null && _attachMent.Length > 0)
{
oledbcom.Parameters.Add("?", UserId);
oledbcom.Parameters.Add("?", _userName);
oledbcom.Parameters.Add("?", _passWord);
oledbcom.Parameters.Add("?", _attachMent);
oledbcom.Parameters.Add("?", _classId);
oledbcom.Parameters.Add("?", _posId);
}
else
{
oledbcom.Parameters.Add("?", UserId);
oledbcom.Parameters.Add("?", _userName);
oledbcom.Parameters.Add("?", _passWord);
oledbcom.Parameters.Add("?", _classId);
oledbcom.Parameters.Add("?", _posId);
}
if (oledbcom.ExecuteNonQuery() > 0)
{
result = true;
}
con.Close();
dl.RunNonQuery(sql2, out _errMsg);
}
catch (Exception ex)
{
LogHelper.WriteLog(LogHelper.EnLogType.Run, "发生时间：" + DateTime.Now.ToString("yyyy-MM-dd H:mm:ss") + "/n错误信息：" + ex.Message);
result = false;
}
}
else if (rlDBType == "ORACLE")
{
if (_attachMent != null && _attachMent.Length > 0)
{
sql1 = "insert into T_SYS_MEMBERINFO(T_USERID,T_USERNAME,T_PASSWD,B_ATTACHMENT,T_CLASSIS,T_POSID) values(:blobtodb,:blobtodb,:blobtodb,:blobtodb,:blobtodb,:blobtodb);";
}
else
{
sql1 = "insert into T_SYS_MEMBERINFO(T_USERID,T_USERNAME,T_PASSWD,T_CLASSIS,T_POSID) values(:blobtodb,:blobtodb,:blobtodb,:blobtodb,:blobtodb);";
}
sql2 = "insert into T_SYS_MEMBERGRP(T_USERID,T_GRPID) values('" + UserId + "','" + _grpId + "')";
try
{
OracleConnection con = new OracleConnection(SAC.DBOperations.OracleHelper.retStr());
con.Open();
OracleCommand oledbcom = new OracleCommand(sql1, con);
if (_attachMent != null && _attachMent.Length > 0)
{
oledbcom.Parameters.Add("blobtodb", UserId);
oledbcom.Parameters.Add("blobtodb", _userName);
oledbcom.Parameters.Add("blobtodb", _passWord);
oledbcom.Parameters.Add("blobtodb", _attachMent);
oledbcom.Parameters.Add("blobtodb", _classId);
oledbcom.Parameters.Add("blobtodb", _posId);
}
else
{
oledbcom.Parameters.Add("blobtodb", UserId);
oledbcom.Parameters.Add("blobtodb", _userName);
oledbcom.Parameters.Add("blobtodb", _passWord);
oledbcom.Parameters.Add("blobtodb", _classId);
oledbcom.Parameters.Add("blobtodb", _posId);
}
if (oledbcom.ExecuteNonQuery() > 0)
{
result = true;
}
con.Close();
dl.RunNonQuery(sql2, out _errMsg);
}
catch (Exception ex)
{
LogHelper.WriteLog(LogHelper.EnLogType.Run, "发生时间：" + DateTime.Now.ToString("yyyy-MM-dd H:mm:ss") + "/n错误信息：" + ex.Message);
result = false;
}
}
return(result);
}
