using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevComponents.DotNetBar;
using System.Data.SqlClient;
namespace PipeLine.Forms
{
public partial class FrmRoleMgr : Office2007Form
{
private string currentUser;
public FrmRoleMgr(string username)
{
InitializeComponent();
currentUser = username;
}
private void FrmAddRole_Load(object sender, EventArgs e)
{
SqlConnection conn = null;
SqlCommand sqlCmd = null;
SqlDataReader sqlReader = null;
try
{
//创建connection对象
conn = new SqlConnection("Data Source=" + Utility.DBServer + ";Initial Catalog=" + Utility.dbdatabase + ";Persist Security Info=True;User ID=" + Utility.userID + ";pwd=" + Utility.DBPassword + "");
//打开数据库连接
conn.Open();
//创建Transac Sql命令对象
sqlCmd = conn.CreateCommand();
sqlCmd.CommandText = "select rolename from roles";
sqlReader = sqlCmd.ExecuteReader();
bool result = sqlReader.Read();
while (result)
{
listRoles.Items.Add(sqlReader.GetString(0).Trim());
result = sqlReader.Read();
}
sqlReader.Close();
conn.Close();
}
catch (SqlException ex)
{
LogError.PublishError(ex);
sqlReader.Close();
conn.Close();
}
showUser();
}
private void btnDeleteUser_Click(object sender, EventArgs e)
{
TreeNode currentNode=this.tvUserName.SelectedNode;
if (currentNode != null)
{
string sql = "DELETE FROM users WHERE username = '" + currentNode.Text + "' ";
string sqldeluserrole = "DELETE FROM user_role WHERE usernameId=(select id from users where username = '" + currentNode.Text + "') ";
string sqldeluserdepartment = "delete from user_department where usernameId =(select id from users where username='"+currentNode.Text+"')";
if (currentNode != null)
{
if (currentNode.Nodes.Count > 0)
{
if (MessageBox.Show("确定删除"+"\""+ currentNode.Text + "\""+"及其创建的用户吗？", "删除用户", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
{
delUser(currentNode, sql, sqldeluserrole, sqldeluserdepartment);
}
}
else
{
if (MessageBox.Show("确定删除" + "\"" + currentNode.Text + "\"" + "用户吗？", "删除用户", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
{
delUser(currentNode, sql, sqldeluserrole, sqldeluserdepartment);
}
}
}
}
}
private void delUser(TreeNode currentNode,string sql,string sqldeluserrole,string sqldeluserdepartment)
{
if (currentNode != null)
{
try
{
OledbHelper.sqlExecuteNonQuery(sqldeluserdepartment);
OledbHelper.sqlExecuteNonQuery(sqldeluserrole);
OledbHelper.sqlExecuteNonQuery(sql);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
if (currentNode.Nodes.Count > 0)
{
foreach (TreeNode nodechild in currentNode.Nodes)
{
string sqlChild = "DELETE FROM users WHERE username = '" + nodechild.Text + "' ";
string sqlChilddeluserrole = "DELETE FROM user_role WHERE usernameId=(select id from users where username ='" + nodechild.Text + "')";
string sqlChilddeluserdepartment = "delete from user_department where usernameId =(select id from users where username='" + nodechild.Text + "')";
delUser(nodechild, sqlChild, sqlChilddeluserrole, sqlChilddeluserdepartment);
}
}
currentNode.Remove();
return;
}
}
private void btnApply_Click(object sender, EventArgs e)
{
TreeNode currentNode = this.tvUserName.SelectedNode;
if (currentNode == null)
{
return;
}
try
{
string sqlDelUserRole = "delete from user_role WHERE usernameId =(select id from users where username='" + currentNode.Text + "')";
OledbHelper.sqlExecuteNonQuery(sqlDelUserRole);
string sqlusernameid = "select id from users where username='" + currentNode.Text + "'";
string sqlrolenameid = "select id from roles where rolename='" + listRoles.SelectedItem.ToString() + "'";
DataTable dtusernameid = OledbHelper.ExecuteDataTable(sqlusernameid);
int usernameid = Convert.ToInt32(dtusernameid.Rows[0][0]);
DataTable dtrolenameid = OledbHelper.ExecuteDataTable(sqlrolenameid);
int rolenameid = Convert.ToInt32(dtrolenameid.Rows[0][0]);
string insertSql = "insert into user_role (usernameId,rolenameId) values(" + usernameid + "," + rolenameid + ")";
OledbHelper.sqlExecuteNonQuery(insertSql);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
LogError.PublishError(ex);
return;
}
}
private void showUser()
{
string sql = "select username from users where createuser='" + currentUser + "'";
acheiveUser(sql);
}
private void acheiveUser(string sql)
{
DataTable dt = OledbHelper.ExecuteDataTable(sql);
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
TreeNode node = new TreeNode();
node.Text = row[0].ToString().Trim();
string sqlChild = "select username from users where createuser='" + row[0].ToString().Trim() + "'";
DGUser(sqlChild,node);
tvUserName.Nodes.Add(node);
}
}
}
private void DGUser(string sql,TreeNode parentNode)
{
DataTable dt = OledbHelper.ExecuteDataTable(sql);
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
TreeNode nodechild = new TreeNode();
nodechild.Text = row[0].ToString().Trim();
parentNode.Nodes.Add(nodechild);
string sqlchildchild = "select username from users where createuser='" + row[0].ToString().Trim() + "'";
DGUser(sqlchildchild,nodechild);
}
}
}
TreeNode preNode = null;
private void tvUserName_AfterSelect(object sender, TreeViewEventArgs e)
{
TreeNode currentNode = this.tvUserName.SelectedNode;
if (currentNode != null)
{
if (preNode != null)
{
if (preNode != currentNode)
{
preNode.NodeFont = new Font("宋体", 9, FontStyle.Regular);
preNode.ForeColor = Color.Black;
}
}
currentNode.NodeFont = new Font("宋体", 9, FontStyle.Bold);
currentNode.ForeColor = Color.Blue;
preNode = currentNode;
SqlConnection conn = null;
SqlCommand sqlCmd = null;
SqlDataReader sqlReader = null;
try
{
//创建connection对象
conn = new SqlConnection("Data Source=" + Utility.DBServer + ";Initial Catalog=" + Utility.dbdatabase + ";Persist Security Info=True;User ID=" + Utility.userID + ";pwd=" + Utility.DBPassword + "");
//打开数据库连接
conn.Open();
//创建Transac Sql命令对象
sqlCmd = conn.CreateCommand();
//创建SQL查询语句1
sqlCmd.CommandText = "select rolename from roles where id=(select rolenameId from user_role where usernameId=(select id from users where username='" + currentNode.Text + "'))";
//查询记录数
sqlReader = sqlCmd.ExecuteReader();
bool result = sqlReader.Read();
string role = "";
listRoles.SelectedItems.Clear();
if (result)
{
role = sqlReader.GetString(0).Trim();
for (int i = 0; i < listRoles.Items.Count; i++)
{
string temprole = listRoles.Items[i].ToString();
if (temprole == role)
{
listRoles.SetSelected(i, true);
break;
}
}
}
sqlReader.Close();
conn.Close();
string sql = "select person,phone from users where username='"+currentNode.Text.Trim()+"'";
DataTable dt = OledbHelper.ExecuteDataTable(sql);
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
label1.Text = "姓名："+row[0].ToString().Trim();
label2.Text = "联系电话："+row[1].ToString().Trim();
}
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
LogError.PublishError(ex);
sqlReader.Close();
conn.Close();
}
}
else
{
return;
}
}
private void btnCancel_Click(object sender, EventArgs e)
{
this.Close();
}
}
}
