/// <summary>
/// 查询用户
/// </summary>
/// <param name="username"></param>
/// <param name="realname"></param>
/// <returns></returns>
public DataTable RetrieveUserInfo(string status, string value)
{
StringBuilder sql = new StringBuilder("select id,name,password,fullname,sex,identityno,mail,phone,venderid,vendername,remark,isChecked from pacs_user where delflag ='0' and fct_code = '" + PaCSAdminTool.FactoryCode + "' ");
switch (status)
{
case "用户名":
sql.Append(" and name like '%" + value + "%'");
break;
case "真实姓名":
sql.Append(" and fullname like '%" + value + "%'");
break;
case "用户状态":
sql.Append(" and ischecked like '%" + value + "%'");
break;
default:
break;
}
DataTable dt = OracleHelper.ExecuteDataTable(sql.ToString());
return(dt);
}
public User GetUserById(string id)
{
string sql = "select id,name,password,fullname,sex,identityno,mail,phone,venderid,vendername,remark,isChecked from pacs_user where id=:id and delflag ='0' and fct_code = '" + PaCSAdminTool.FactoryCode + "'";
OracleParameter[] cmdParam = new OracleParameter[] {
new OracleParameter(":id", OracleType.VarChar, 20)
};
cmdParam[0].Value = id;
DataTable dt = OracleHelper.ExecuteDataTable(sql, cmdParam);
User user = new User();
if (dt != null && dt.Rows[0] != null)
{
user.Id = dt.Rows[0]["id"].ToString();
user.Name = dt.Rows[0]["name"].ToString();
user.Password = dt.Rows[0]["password"].ToString();
user.FullName = dt.Rows[0]["fullname"].ToString();
user.Sex = dt.Rows[0]["sex"].ToString();
user.IdentityNo = dt.Rows[0]["identityno"].ToString();
user.Mail = dt.Rows[0]["mail"].ToString();
user.Phone = dt.Rows[0]["phone"].ToString();
user.Venderid = dt.Rows[0]["venderid"].ToString();
user.Vendername = dt.Rows[0]["vendername"].ToString();
user.Remark = dt.Rows[0]["remark"].ToString();
user.IsChecked = dt.Rows[0]["isChecked"].ToString();
}
return(user);
}
/// <summary>
/// 登录验证用户
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public User VerifyUser(User user)
{
User loginUser = new User();
string sql = "select id,name,password,fullname,fct_code,usertype from pacs_user where name=:name and password = :password and isChecked = '1' and delflag = '0' ";
OracleParameter[] cmdParam = new OracleParameter[] {
new OracleParameter(":name", OracleType.VarChar, 50),
new OracleParameter(":password", OracleType.VarChar, 50)
};
cmdParam[0].Value = user.Name;
cmdParam[1].Value = user.Password;
DataTable dt = OracleHelper.ExecuteDataTable(sql, cmdParam);
if (dt != null && dt.Rows.Count > 0)
{
loginUser.Id = dt.Rows[0]["id"].ToString();
userid = loginUser.Id;
loginUser.Fct_code = dt.Rows[0]["fct_code"] == null ? "" : dt.Rows[0]["fct_code"].ToString();
loginUser.UserType = dt.Rows[0]["usertype"] == null ? "" : dt.Rows[0]["usertype"].ToString();
}
return(loginUser);
}
public void AddTree(TreeView tv)
{
string sql = "select id,fullname,venderid,vendername from pacs_user where delflag ='0' and fct_code = '" + PaCSAdminTool.FactoryCode + "'";
string sql2 = "select distinct venderid,vendername from pacs_user where delflag ='0' and fct_code = '" + PaCSAdminTool.FactoryCode + "'";
DataTable dt = OracleHelper.ExecuteDataTable(sql);
DataTable dt2 = OracleHelper.ExecuteDataTable(sql2);
DataView dvTree = new DataView(dt);
DataView dvTree2 = new DataView(dt2);
foreach (DataRowView Row2 in dvTree2)
{
TreeNode tn21 = new TreeNode();
tn21.Text = Row2["vendername"].ToString();
tn21.Tag = Row2["venderid"].ToString();
tn21.ImageIndex = 2;
tn21.SelectedImageIndex = 2;
tv.Nodes.Add(tn21);
dvTree.RowFilter = "[venderid] = '" + Row2["venderid"].ToString() + "'";
foreach (DataRowView Row in dvTree)
{
TreeNode tn1 = new TreeNode();
tn1.Text = Row["fullname"].ToString();
tn1.Tag = Row["id"].ToString();
tn1.ImageIndex = 0;
tn1.SelectedImageIndex = 0;
tn21.Nodes.Add(tn1);
}
}
//tv.ExpandAll();
}
public void AddTree(TreeView tv, int ParentID, TreeNode pNode)
{
string sql = "select id, pid, name,controlid,lvl from pacs_function where delflag ='0'";
DataTable dt = OracleHelper.ExecuteDataTable(sql);
TreeNode tn1 = new TreeNode();
DataView dvTree = new DataView(dt);
//过滤ParentID,得到当前的所有子节点
dvTree.RowFilter = "[pid] = " + ParentID;
foreach (DataRowView Row in dvTree)
{
if (pNode == null)
{ //'?添加根节点
tn1.Text = Row["name"].ToString();
tn1.Tag = Row["controlid"].ToString() + "$" + Row["id"].ToString() + "$" + Row["lvl"].ToString();
tv.Nodes.Add(tn1);
tn1.ExpandAll();
AddTree(tv, Int32.Parse(Row["id"].ToString()), tn1); //再次递归
}
else
{ //添加当前节点的子节点
TreeNode tn2 = new TreeNode();
tn2.Text = Row["name"].ToString();
tn2.Tag = Row["controlid"].ToString() + "$" + Row["id"].ToString() + "$" + Row["lvl"].ToString();
pNode.Nodes.Add(tn2);
tn1.ExpandAll();
AddTree(tv, Int32.Parse(Row["id"].ToString()), tn2); //再次递归
}
}
tv.ExpandAll();
}
public void GetParentNode(ComboBox cmbBox)
{
//string sql = "select id,name from pacs_function where id in( select distinct(pid) from pacs_function)";
string sql = "select id,name from pacs_function where delflag ='0'";
DataTable dt = OracleHelper.ExecuteDataTable(sql);
cmbBox.ValueMember = "id";
cmbBox.DisplayMember = "name";
cmbBox.DataSource = dt;
}
public DataTable GetRoleListByUserId(string userid)
{
string sql = "select u.roleid roleid,v.name rolename from pacs_user_role u,pacs_role v where u.userid=:userid and u.roleid = v.id and v.delflag='0' and v.fct_code = '" + PaCSAdminTool.FactoryCode + "' ";
OracleParameter[] cmdParam = new OracleParameter[] {
new OracleParameter(":userid", OracleType.VarChar, 20)
};
cmdParam[0].Value = userid;
DataTable dt = OracleHelper.ExecuteDataTable(sql, cmdParam);
return(dt);
}
public DataTable GetRoleListByFuncId(string funcid)
{
string sql = "select u.roleid roleid,v.name rolename from pacs_role_function u,pacs_role v where u.functionid=:functionid and u.roleid = v.id and v.delflag='0'";
OracleParameter[] cmdParam = new OracleParameter[] {
new OracleParameter(":functionid", OracleType.VarChar, 20)
};
cmdParam[0].Value = funcid;
DataTable dt = OracleHelper.ExecuteDataTable(sql, cmdParam);
return(dt);
}
public DataTable GetUserByRoleId(string roleid)
{
string sql = "select userid,roleid from PACS_USER_ROLE where roleid=:roleid";
OracleParameter[] cmdParam = new OracleParameter[] {
new OracleParameter(":roleid", OracleType.VarChar, 20)
};
cmdParam[0].Value = roleid;
DataTable dt = OracleHelper.ExecuteDataTable(sql, cmdParam);
return(dt);
}
/// <summary>
/// 获取包含用户列表
/// </summary>
/// <param name="roleid"></param>
/// <returns></returns>
public DataTable GetUserList(string roleid)
{
string sql = "select a.roleid roleid,a.userid userid,b.fullname fullname from PACS_USER_ROLE a,pacs_user b where b.delflag != '1' and a.roleid=:roleid and a.userid = b.id";
OracleParameter[] cmdParam = new OracleParameter[] {
new OracleParameter(":roleid", OracleType.VarChar, 20)
};
cmdParam[0].Value = roleid;
DataTable dt = OracleHelper.ExecuteDataTable(sql, cmdParam);
return(dt);
}
public DataTable GetAuthListByRoleId(string roleid)
{
string sql = "select roleid, functionid from pacs_role_function where roleid=:roleid";
OracleParameter[] cmdParam = new OracleParameter[] {
new OracleParameter(":roleid", OracleType.VarChar, 20)
};
cmdParam[0].Value = roleid;
DataTable dt = OracleHelper.ExecuteDataTable(sql, cmdParam);
return(dt);
}
/// <summary>
/// 获取可操作功能列表
/// </summary>
/// <param name="roleid"></param>
/// <returns></returns>
public DataTable GetAuthList(string roleid)
{
string sql = "select a.roleid roleid,a.functionid functionid,b.name name from pacs_role_function a,pacs_function b where a.roleid=:roleid and a.functionid = b.id and b.delflag='0'";
OracleParameter[] cmdParam = new OracleParameter[] {
new OracleParameter(":roleid", OracleType.VarChar, 20)
};
cmdParam[0].Value = roleid;
DataTable dt = OracleHelper.ExecuteDataTable(sql, cmdParam);
return(dt);
}
public void AddTree(TreeView tv)
{
string sql = "select id,name,remark from pacs_role where delflag ='0' and fct_code = '" + PaCSAdminTool.FactoryCode + "'";
DataTable dt = OracleHelper.ExecuteDataTable(sql);
DataView dvTree = new DataView(dt);
foreach (DataRowView Row in dvTree)
{
TreeNode tn1 = new TreeNode();
tn1.Text = Row["name"].ToString();
tn1.Tag = Row["remark"].ToString() + "$" + Row["id"].ToString();
tv.Nodes.Add(tn1);
tn1.ExpandAll();
}
tv.ExpandAll();
}
public DataTable GetAuthListByRoleId(List <string> roleidList)
{
DataTable dtReturn = new DataTable();
foreach (string roleid in roleidList)
{
string sql = "select roleid, functionid from pacs_role_function where roleid=:roleid";
OracleParameter[] cmdParam = new OracleParameter[] {
new OracleParameter(":roleid", OracleType.VarChar, 20)
};
cmdParam[0].Value = roleid;
DataTable dt = OracleHelper.ExecuteDataTable(sql, cmdParam);
dtReturn.Merge(dt);
}
return(dtReturn);
}
/// <summary>
/// 新增用户时，检测用户名是否存在
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public int CheckUser(string name)
{
int flag = 0;
string sql = "select count(*) from pacs_user where name=:name";
OracleParameter[] cmdParam = new OracleParameter[] {
new OracleParameter(":name", OracleType.VarChar, 50)
};
cmdParam[0].Value = name;
DataTable dt = OracleHelper.ExecuteDataTable(sql, cmdParam);
if (dt != null && dt.Rows[0] != null)
{
flag = int.Parse(dt.Rows[0][0].ToString());
}
return(flag);
}
public void AddUserTree(DataTable dtChecked, TreeView tv)
{
//string sql = "select id,fullname,venderid,vendername from pacs_user where delflag ='0' and fct_code = '" + PaCSAdminTool.FactoryCode + "'";
//string sql2 = "select distinct venderid,vendername from pacs_user where delflag ='0' and fct_code = '" + PaCSAdminTool.FactoryCode + "'";
string sql = "select id,fullname,venderid,vendername from pacs_user where delflag ='0' ";
string sql2 = "select distinct venderid,vendername from pacs_user where delflag ='0'";
DataTable dt = OracleHelper.ExecuteDataTable(sql);
DataTable dt2 = OracleHelper.ExecuteDataTable(sql2);
DataView dvTree = new DataView(dt);
DataView dvTree2 = new DataView(dt2);
foreach (DataRowView Row2 in dvTree2)
{
TreeNode tn21 = new TreeNode();
tn21.Text = Row2["vendername"].ToString();
tn21.Tag = Row2["venderid"].ToString();
tv.Nodes.Add(tn21);
dvTree.RowFilter = "[venderid] = '" + Row2["venderid"].ToString() + "'";
foreach (DataRowView Row in dvTree)
{
TreeNode tn1 = new TreeNode();
tn1.Text = Row["fullname"].ToString();
tn1.Tag = Row["id"].ToString();
if (dtChecked.Select("userid='" + Row["id"].ToString() + "'").Length > 0)
{
tn1.Checked = true;
}
tn21.Nodes.Add(tn1);
}
tv.ExpandAll();
}
}