public bool UpdataUserInfo(Users user)
{
string strSql = "Update Users set RealName = '{1}',Sex = '{2}', TelePhone = '{3}' where UserId = {0}";
strSql = string.Format(strSql, user.UserId, user.RealName, user.Sex ? "0" : "1", user.TelePhone);
return OracleHelper.ExecuteCommand(strSql);
}
public bool ManageUsers(Users user)
{
ArrayList strSql = new ArrayList();
string tmpSql = string.Empty;
string[] roleAry = null;
switch (user.Action)
{
case ActionEnum.Insert:
roleAry = user.Role.RoleId.Split(new char[] { ',' });
tmpSql = "select seq_user.nextval from dual";
string userId = string.Format("{0}{1}", user.CompanyCode,
OracleHelper.ExecuteDataTable(tmpSql).Rows[0][0].ToString());
tmpSql = "Insert into Users(UserId,CCODE,DeptCode,UserName,PassWord,RealName,Sex,Telephone,State,CreateDate)"
+ " Values ({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',to_date('{9}','yyyy-mm-dd hh24:mi:ss'))";
tmpSql = string.Format(tmpSql, userId, user.CompanyCode, user.DepartCode, user.UserName, user.PassWord, user.RealName,
user.Sex ? "0" : "1", user.TelePhone, user.State ? "0" : "1", user.CreateDate.ToString());
strSql.Add(tmpSql);
for (int i = 0; i < roleAry.Length; i++)
{
if (!string.IsNullOrEmpty(roleAry[i]))
{
tmpSql = "Insert into usersandrole values ({0},'{1}')";
tmpSql = string.Format(tmpSql, userId, roleAry[i]);
strSql.Add(tmpSql);
}
}
break;
case ActionEnum.Update:
roleAry = user.Role.RoleId.Split(new char[] { ',' });
tmpSql = "Update Users Set CCODE = '{1}',DeptCode = '{2}',RealName = '{3}',Sex = '{4}',Telephone = '{5}',State = '{6}' Where UserId = {0}";
tmpSql = string.Format(tmpSql, user.UserId, user.CompanyCode, user.DepartCode, user.RealName, user.Sex ? "0" : "1", user.TelePhone, user.State ? "0" : "1");
strSql.Add(tmpSql);
tmpSql = "delete from usersandrole where UserId = " + user.UserId;
strSql.Add(tmpSql);
for (int i = 0; i < roleAry.Length; i++)
{
if (!string.IsNullOrEmpty(roleAry[i]))
{
tmpSql = "Insert into usersandrole values ({0},'{1}')";
tmpSql = string.Format(tmpSql, user.UserId, roleAry[i]);
strSql.Add(tmpSql);
}
}
break;
case ActionEnum.Delete:
tmpSql = "delete from Users Where UserId = {0}";
tmpSql = string.Format(tmpSql, user.UserId);
strSql.Add(tmpSql);
tmpSql = "delete from usersandrole where UserId = " + user.UserId;
strSql.Add(tmpSql);
break;
}
return OracleHelper.ExecuteCommand(strSql);
}
public DataTable GetUsers(Users user, out int recordCount)
{
string strWhere = string.Empty;
//if (!string.IsNullOrEmpty(user.FilterUser)) strWhere = "upper(userName) not in ('" + user.FilterUser + "') And ";
if (!string.IsNullOrEmpty(user.CompanyCode)) strWhere += "a.cCode = '" + user.CompanyCode + "' And ";
if (!string.IsNullOrEmpty(user.DepartCode)) strWhere += "a.DeptCode = '" + user.DepartCode + "' And ";
if (!string.IsNullOrEmpty(user.UserName)) strWhere += "userName like '%" + user.UserName + "%' And ";
if (!string.IsNullOrEmpty(user.RealName)) strWhere += "realName like '%" + user.RealName + "%' And ";
string strSql = "select a.*,b.cname,c.deptname from users a left join company b on a.cCode = b.cCode"
+ " left join department c on a.deptcode = c.deptcode"
+ " where {0} a.username <> b.ccode order by a.cCode,a.createDate desc";
strSql = string.Format(strSql, strWhere);
#region 设置参数
OracleParameter pageSize = new OracleParameter("p_PageSize", OracleType.Number, 10);
pageSize.Value = user.PageSize;
OracleParameter pageIndex = new OracleParameter("p_PageIndex", OracleType.Number, 10);
pageIndex.Value = user.PageIndex;
OracleParameter sqlSelect = new OracleParameter("p_SqlSelect", OracleType.VarChar, 4000);
sqlSelect.Value = strSql;
OracleParameter recount = new OracleParameter("p_OutRecordCount", OracleType.Number, 5);
recount.Direction = ParameterDirection.Output;
OracleParameter returnCursor = new OracleParameter("p_OutCursor", OracleType.Cursor);
returnCursor.Direction = ParameterDirection.Output;
OracleParameter[] oracleParameters = { pageSize, pageIndex, sqlSelect, recount, returnCursor };
#endregion
DataTable dt = OracleHelper.ExecuteDataTable("CommonPackages.sp_Page", oracleParameters);
recordCount = int.Parse(recount.Value.ToString());
return dt;
}
public IList<Users> GetUsers(Users user)
{
IList<Users> datalist = new List<Users>();
string strWhere = string.Empty;
if (!string.IsNullOrEmpty(user.CompanyCode)) strWhere += "a.ccode like '" + user.CompanyCode + "%' And ";
if (!user.IsFilterSYSUser) strWhere += "a.username <> b.ccode And ";
if (!string.IsNullOrEmpty(user.UserId)) strWhere += "a.userId In (" + user.UserId + ") And ";
if (!string.IsNullOrEmpty(user.QueryUserLevel))
{
if (user.QueryUserLevel.IndexOf(",") == -1)
{
if (user.QueryUserLevel.Equals(((int)CompanyTypeEnum.SHI).ToString()))
{
strWhere += "length(b.ShortCCode) = 4 And ";
}
if (user.QueryUserLevel.Equals(((int)CompanyTypeEnum.XIAN).ToString()))
{
strWhere += "length(b.ShortCCode) = 6 And ";
}
}
}
string strSql = "select * from Users a left join company b on a.cCode = b.cCode where {0} 1 = 1 order by a.cCode, a.RealName";
strSql = string.Format(strSql, strWhere);
IDataReader dr = OracleHelper.ExecuteReader(strSql);
while (dr.Read())
{
Users userEntity = new Users();
userEntity.UserId = dr["UserId"].ToString();
userEntity.UserName = dr["UserName"].ToString();
userEntity.RealName = dr["RealName"].ToString();
userEntity.FullName = string.Format("{0}--{1}({2})", dr["cName"].ToString(), dr["RealName"].ToString(), dr["UserName"].ToString());
datalist.Add(userEntity);
}
dr.Close();
return datalist;
}
public Users GetItemUser(string itemCode, WorkFlowNode node)
{
Users userInfo = new Users();
string strSql = "select b.ccode, b.realname, b.telephone from wf_instance a"
+" left join users b on a.userid = b.userid"
+" where a.itemcode = '{0}' and a.nodeid = '{1}' order by a.orderno desc";
strSql = string.Format(strSql, itemCode, ((int)node).ToString());
DataTable dt = OracleHelper.ExecuteDataTable(strSql);
if (dt.Rows.Count > 0)
{
userInfo.CompanyCode = dt.Rows[0]["ccode"].ToString();
userInfo.RealName = dt.Rows[0]["realname"].ToString();
userInfo.TelePhone = dt.Rows[0]["telephone"].ToString();
}
return userInfo;
}
