开发平台:vs2005,sqlServer2K
语言:C#,javascript,html,jQuery,Css
日期:2011.9.26
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Deptment.aspx.cs" Inherits="manager" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>无标题页</title>
<link rel="Stylesheet" type="text/css" href="Css/movingTab.css" />
<script type="text/javascript" src="Js/jquery-1.4.2.min.js"></script>
<script type="text/javascript" src="Js/movingTab.js">
</script>
</head>
<body style="font-family: 楷体_GB2312">
<form id="form1" runat="server">
<asp:HiddenField ID="hdnChoose" runat="server" />
<div style="float:left; width:100px; background-color: #d4eaff;height:1000px;; font-size: 12pt;">
<asp:TreeView ID="treeview" runat="server" ImageSet="Simple" NodeIndent="10" DataSourceID="SiteMapDataSource" >
<ParentNodeStyle Font-Bold="False" />
<HoverNodeStyle Font-Underline="True" ForeColor="#DD5555" />
<SelectedNodeStyle Font-Underline="True" ForeColor="#DD5555" HorizontalPadding="0px"
VerticalPadding="0px" />
<NodeStyle Font-Names="Verdana" Font-Size="8pt" ForeColor="Black" HorizontalPadding="0px"
NodeSpacing="0px" VerticalPadding="0px" />
</asp:TreeView>
</div>
<div style="margin-left:100px;">
<div style=" display:table;width:800px;position:relative;">
<div style="background:#F9F7F3;width:800px;height:38px; font-size: 12pt;position:relative;">
<span style=" position:absolute;top:25%; font-size:12pt;">
<strong>
部门权限设置
</strong>
</span>
<a href="" title="Longer Link Text" style="position:absolute;top:35%;font-size:9pt;padding-left:260px;">
链接:系统设置
</a>
</div>
<table class="basic"
style="background-color:White;border-color:#4169E1;
border-width:1px;border-style:None;font-size:Small;
color :#000080;border-collapse:collapse;
width: 541px; height: 400px;"
>
<tr><td>
设置一级部门
</td><td>
设置二级部门
</td><td>
设置三级部门
</td></tr>
<tr><td>
<asp:TextBox ID="txtAdvance" runat="server" Width="96%"></asp:TextBox>
</td><td>
<asp:TextBox ID="txtInterdiate" runat="server" Width="96%"></asp:TextBox>
</td><td>
<asp:TextBox ID="txtBase" runat="server" Width="96%"></asp:TextBox>
</td></tr>
<tr><td style=" text-align:center;">
<asp:Button ID="btnAdavanceAdd" runat="server" Text="添加" Height="21px"
Width="50px" OnClick="btnAdavanceAdd_Click" />
<asp:Button ID="btnAdavanceDel" runat="server" Text="删除" Height="21px"
Width="50px" OnClick="btnAdavanceDel_Click" />
<input id="btnAdavanceChs" type="button" value="选择" style="height:21px;width:50px;" />
</td><td style=" text-align:center;">
<asp:Button ID="btnIntermediateAdd" runat="server" Text="添加" Height="21px"
Width="50px" OnClick="btnIntermediateAdd_Click" />
<asp:Button ID="btnIntermediateDel" runat="server" Text="删除" Height="21px"
Width="50px" OnClick="btnIntermediateDel_Click" />
<input id="btnIntermediateChs" type="button" value="选择" style="height:21px;width:50px;" />
</td><td style=" text-align:center;">
<asp:Button ID="btnBaseAdd" runat="server" Text="添加" Height="21px" Width="50px" OnClick="btnBaseAdd_Click" />
<asp:Button ID="btnBaseDel" runat="server" Text="删除" Height="21px" Width="50px" OnClick="btnBaseDel_Click" />
<input id="btnBaseChs" type="button" value="选择" style="height:21px;width:50px;"/>
</td></tr>
<tr><td style="height: 306px">
<asp:ListBox ID="lstAdavance" runat="server" Height="300px" Width="96%" AutoPostBack="True" OnSelectedIndexChanged="lstAdavance_SelectedIndexChanged" ></asp:ListBox>
</td><td style="height: 306px">
<asp:ListBox ID="lstIntermediate" runat="server" Height="300px" Width="96%" AutoPostBack="True" OnSelectedIndexChanged="lstIntermediate_SelectedIndexChanged" ></asp:ListBox>
</td><td style="height: 306px">
<asp:ListBox ID="lstBase" runat="server" Height="300px" Width="96%" ></asp:ListBox>
</td></tr>
</table>
</div>
<div id="move_div" class="moveDiv" style="z-index: 9999;width:258px;height:429px; ">
<div class="headDiv" style="cursor:move;height: 25px; line-height:25px; background-color: #b0d0e6;width: 258px; text-align:left;">
<strong> 请选择部门</strong>
</div>
<div class="bodyDiv" style="text-align: center;">
<table border="1" cellpadding="0" cellspacing="0" style="border-right: #4169e1 1px;
border-top: #4169e1 1px; font-size: small; border-left: #4169e1 1px; width: 258px;
color: #000080; border-bottom: #4169e1 1px; border-collapse: collapse; height: 429px;
background-color: white; cursor:pointer;">
<tr><td align="left" valign="top" class="width:256px;hight:400x;">
<div style="width:100%;height:400px; overflow:scroll; overflow-x:hidden;">
<asp:TreeView ID="trvDept" runat="server" ImageSet="Simple" NodeIndent="10"
style="font-size: medium">
<ParentNodeStyle Font-Bold="False" />
<HoverNodeStyle Font-Underline="True" ForeColor="#DD5555" />
<SelectedNodeStyle Font-Underline="True" ForeColor="#DD5555" HorizontalPadding="0px"
VerticalPadding="0px" />
<NodeStyle Font-Names="Verdana" Font-Size="8pt" ForeColor="Black" HorizontalPadding="0px"
NodeSpacing="0px" VerticalPadding="0px" />
</asp:TreeView>
</div>
</td></tr>
<tr><td align="right">
<asp:Button ID="btnSure" runat="server" Height="21px" Text="确 定" Width="57px" OnClick="btnSure_Click" />
<input id="btnClose" type="button" value="关 闭" style="width:57px; height: 21px;" />
</td></tr>
</table>
</div>
</div>
</div>
</form>
</body>
</html>
//movingTab.js// JScript 文件
$(document).ready(function () {
$("#move_div").hide();
//弹出定位,并将部门级别传送回后台
$(".headDiv").mousedown(
function (event) {
var offset = $("#move_div").offset();
x1 = event.clientX - offset.left;
y1 = event.clientY - offset.top;
//alert(y1);
var witchButton = false;
if (document.all && event.button == 1) { witchButton = true; } //document.all = 1时候,为ie浏览器
else { if (event.button == 0) witchButton = true; }
if (witchButton)//按左键,FF是0,IE是1
{
$(document).mousemove
(
function (event) {
$(".moveDiv").css("left", (event.clientX - x1) + "px");
$(".moveDiv").css("top", (event.clientY - y1) + "px");
}
)
}
})
$(".headDiv").mouseup(
function (event) {
$(document).unbind("mousemove");
})
$("#btnClose").click(
function (event) {
$(".moveDiv").css("display", "none");
}) $("#btnAdavanceChs").click(
function () {
position("#btnAdavanceChs", 100, 50);
setHiddenFieldValue("Model:Dept/Index:Adavance");
})
$("#btnIntermediateChs").click(
function () {
position("#btnIntermediateChs", 100, 50);
setHiddenFieldValue("Model:Dept/Index:Intermediate");
})
$("#btnBaseChs").click(
function () {
position("#btnBaseChs", 100, 50);
setHiddenFieldValue("Model:Dept/Index:Base");
})
function position(id, nTop, nLeft) {
var gtop = $(id).offset().top - nTop;
var gleft = $(id).offset().left + nLeft;
$("#move_div").css({ top: gtop, left: gleft }).show();
}
function setHiddenFieldValue(value)
{
var hdnId = document.getElementById("hdnChoose");//<%=hdnChoose.ClientID%>
hdnId.value = value;
}
})
CSS:movingTab.css
.moveDiv{position:absolute;}
.basic td {
border-right: silver 1px solid;
border-top: silver 1px solid;
border-left: silver 1px solid;
border-bottom: silver 1px solid;
}
后台代码:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Xml;
public partial class manager : System.Web.UI.Page
{
OperationXml xml;
baseInformation inf;
protected void Page_Load(object sender, EventArgs e)
{
if (xml == null)
{
xml = new OperationXml();
}
if (inf == null)
{
inf = new baseInformation();
}
if (!IsPostBack)
{
if (xml.IsLoginer("/config/SystemAdmin", inf.systemName))
{
loadStairDepartment(trvDept, true);
loadAdvanceDept(lstAdavance);
}
else
{
Response.Redirect("Error.htm");
}
}
}
private void loadStairDepartment(TreeView trvDept, bool TreeBoxShow)
{
string path = @"./Xml/config.xml";
XmlDocument xml = new XmlDocument();
xml.Load(System.Web.HttpContext.Current.Server.MapPath(path));
XmlNode Nodes = xml.SelectSingleNode("/config/department");
foreach (XmlElement nds in Nodes.ChildNodes)
{
addtrvDepartment(nds.GetAttribute("name").Trim(), trvDept, TreeBoxShow);
}
xml = null;
}
//从数据库中搜索出部门的数据
private void addtrvDepartment(string Department, TreeView tree, bool TreeBoxShow)
{
string sql = "SELECT DISTINCT b04 FROM table2 WHERE (b04 LIKE '%" + Department + "%')";
SqlDataAdapter Adapter = new SqlDataAdapter(sql, inf.cmisStr);
DataSet ds = new DataSet();
Adapter.Fill(ds);
TreeNode root = new TreeNode();
root.Text = Department.Trim();
root.Value = Department.Trim();
root.SelectAction = TreeNodeSelectAction.None;
root.ShowCheckBox = TreeBoxShow;
tree.Nodes.Add(root);
TreeNode node;
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
node = new TreeNode(dr["b04"].ToString().Trim(), dr["b04"].ToString().Trim());
node.ShowCheckBox = TreeBoxShow;
root.ChildNodes.Add(node);
node = null;
}
}
tree.CollapseAll();
ds.Dispose();
Adapter.Dispose();
}
protected void lstAdavance_SelectedIndexChanged(object sender, EventArgs e)
{
if (lstAdavance.SelectedIndex > -1)
{
selectAdvanceDept(lstIntermediate, lstAdavance.SelectedValue.Trim());
}
}
protected void lstIntermediate_SelectedIndexChanged(object sender, EventArgs e)
{
if (lstIntermediate.SelectedIndex > -1)
{
selectIntermediateDept(lstBase, lstIntermediate.SelectedValue.Trim());
}
}
private void loadAdvanceDept(ListBox lst)
{
string sql = "SELECT Name, relation FROM table1 WHERE (layer = 1)";
SqlConnection sqlCon = new SqlConnection(inf.cmisStr);
SqlCommand sqlCom = new SqlCommand(sql, sqlCon);
lst.Items.Clear();
sqlCon.Open();
SqlDataReader reader = sqlCom.ExecuteReader();
int i=0;
while (reader.Read())
{
lst.Items.Add(reader["Name"].ToString().Trim());
lst.Items[i].Value = reader["relation"].ToString().Trim();
++i;
}
reader.Close();
sqlCon.Close();
}
private void selectAdvanceDept(ListBox lst, string relation)
{
string likeExpression = relation + "/%";
string notLikeExp = relation + "/%/%";
string sql = " SELECT Name, relation FROM table1" +
" WHERE (relation LIKE @likeExp) AND (relation NOT LIKE @notLikeExp)";
SqlConnection sqlcon = new SqlConnection(inf.cmisStr);
SqlCommand sqlcom = new SqlCommand(sql, sqlcon);
sqlcom.Parameters.AddWithValue("likeExp", likeExpression);
sqlcom.Parameters.AddWithValue("notLikeExp", notLikeExp);
lst.Items.Clear();
sqlcon.Open();
SqlDataReader reader = sqlcom.ExecuteReader();
int i = 0;
while (reader.Read())
{
lst.Items.Add(reader["Name"].ToString().Trim());
lst.Items[i].Value = reader["relation"].ToString().Trim();
++i;
}
reader.Close();
sqlcon.Close();
sqlcom.Dispose();
sqlcon.Dispose();
}
private void selectIntermediateDept(ListBox lst, string relation)
{
relation += "/%";
string sql = "SELECT Name, relation FROM table1 WHERE (relation LIKE @relation)"; //'1/%'
SqlConnection sqlCon = new SqlConnection(inf.cmisStr);
SqlCommand sqlCom = new SqlCommand(sql, sqlCon);
sqlCom.Parameters.AddWithValue("relation", relation);
lst.Items.Clear();
sqlCon.Open();
SqlDataReader reader = sqlCom.ExecuteReader();
int i = 0;
while (reader.Read())
{
lst.Items.Add(reader["Name"].ToString().Trim());
lst.Items[i].Value = reader["relation"].ToString().Trim();
++i;
}
reader.Close();
sqlCon.Close();
}
protected void btnAdavanceAdd_Click(object sender, EventArgs e)
{
if ((txtAdvance.Text.Trim() != "") && (txtAdvance.Text.Trim() != null))
{
string dept = txtAdvance.Text.Trim();
string value = insertDept(dept);
int count = lstAdavance.Items.Count;
lstAdavance.Items.Add(dept);
lstAdavance.Items[count].Value = value;
}
}
protected void btnIntermediateAdd_Click(object sender, EventArgs e)
{
if ((txtInterdiate.Text.Trim() != "") && (txtInterdiate.Text.Trim() != null))
{
if (lstAdavance.SelectedIndex > -1)
{
string dept = txtInterdiate.Text.Trim();
string previous = lstAdavance.SelectedValue.Trim();
string value = insertDept(dept, previous, 2);
int count = lstIntermediate.Items.Count;
lstIntermediate.Items.Add(dept);
lstIntermediate.Items[count].Value = value;
}
}
}
protected void btnBaseAdd_Click(object sender, EventArgs e)
{
if ((txtBase.Text.Trim() != "") && txtBase.Text.Trim() != null)
{
if ((lstIntermediate.SelectedIndex > -1) && (lstAdavance.SelectedIndex > -1))
{
string dept = txtBase.Text.Trim();
//string previousAd = lstAdavance.SelectedValue.Trim();
string previousIm = lstIntermediate.SelectedValue.Trim();
string value = insertDept(dept, previousIm, 3);
int count = lstBase.Items.Count;
lstBase.Items.Add(dept);
lstBase.Items[count].Value = value;
}
}
}
private string insertDept(string Name)
{
string identity = "";
string sql = " INSERT table1(Name, layer) VALUES (@Name, 1);" +
" UPDATE table1 SET relation = @@identity WHERE num = @@identity;" +
" select @@identity";
SqlConnection sqlCon = new SqlConnection(inf.cmisStr);
SqlCommand sqlCom = new SqlCommand(sql, sqlCon);
sqlCom.Parameters.AddWithValue("Name", Name);
//sqlCom.Parameters.AddWithValue("idt", "@@identity");
sqlCon.Open();
identity = sqlCom.ExecuteScalar().ToString().Trim();
sqlCon.Close();
sqlCon.Dispose();
sqlCom.Dispose();
return identity;
}
private string insertDept(string dept, string previous,Int16 layer)
{
string identity = "";
identity = previous + "/";
identity = insertDept(dept,layer,identity);
return identity;
}
private string insertDept(string dept, string previousAd, string previousIm)
{
string identity = "";
identity = previousAd + "/" + previousIm + "/";
identity = insertDept(dept, 3, identity);
return identity;
}
private string insertSubDept(string name, string identity,int level)
{
string value = "";
string sql = " INSERT table1(Name, layer) VALUES (@Name, @level);" +
" UPDATE table1SET relation = @idt WHERE num = @@identity;" +
" Select relation from table1 where num = @@identity;";
SqlConnection sqlCon = new SqlConnection(inf.cmisStr);
SqlCommand sqlCom = new SqlCommand(sql, sqlCon);
sqlCom.Parameters.AddWithValue("Name", name);
sqlCom.Parameters.AddWithValue("level", level);
sqlCom.Parameters.AddWithValue("idt", identity);
sqlCon.Open();
identity = sqlCom.ExecuteScalar().ToString().Trim();
sqlCon.Close();
sqlCon.Dispose();
sqlCom.Dispose();
return value;
}
private string insertDept(string dept, Int16 layer, string previous)
{
string value = "";
SqlConnection sqlCon = new SqlConnection(inf.cmisStr);
SqlCommand sqlCom = new SqlCommand("dbo.gyjl_DeptInert", sqlCon);
sqlCom.CommandType = CommandType.StoredProcedure;
//sqlCom.Parameters.Add("@Prev", SqlDbType.Char, 12);
//value = ParameterDirection.Output.ToString();
//设置存储过程的output值,存储过程output和返回值是两个概念
SqlParameter param = new SqlParameter();
param.ParameterName = "@Prev";
param.SqlDbType = SqlDbType.Char;
param.Size = 12;
param.Direction = ParameterDirection.Output;
sqlCom.Parameters.Add(param);
sqlCom.Parameters.Add("@dept", SqlDbType.Char, 30);
sqlCom.Parameters["@dept"].Value = dept;
sqlCom.Parameters.Add("@layer", SqlDbType.TinyInt);
sqlCom.Parameters["@layer"].Value = layer;
sqlCom.Parameters.Add("@previous", SqlDbType.Char, 12);
sqlCom.Parameters["@previous"].Value = previous;
sqlCon.Open();
sqlCom.ExecuteNonQuery();
sqlCon.Close();
sqlCon.Dispose();
sqlCom.Dispose();
value = param.Value.ToString().Trim();
return value;
}
protected void btnAdavanceDel_Click(object sender, EventArgs e)
{
if (lstAdavance.SelectedIndex > -1)
{
DelDept(lstAdavance.SelectedValue.Trim());
lstAdavance.Items.Remove(lstAdavance.SelectedItem);
lstIntermediate.Items.Clear();
lstBase.Items.Clear();
}
}
protected void btnIntermediateDel_Click(object sender, EventArgs e)
{
if (lstIntermediate.SelectedIndex > -1)
{
DelDept(lstIntermediate.SelectedValue.Trim());
lstIntermediate.Items.Remove(lstIntermediate.SelectedItem);
lstBase.Items.Clear();
}
}
protected void btnBaseDel_Click(object sender, EventArgs e)
{
if (lstBase.SelectedIndex > -1)
{
DelDept(lstBase.SelectedValue.Trim());
lstBase.Items.Remove(lstBase.SelectedItem);
}
}
private void DelDept(string previous)
{
previous = previous + "%";
//string sql = "DELETE FROM table1 WHERE (relation LIKE @previous)";
string sql = "DELETE FROM table1 WHERE (relation LIKE @previous)";
SqlConnection sqlcon = new SqlConnection(inf.cmisStr);
SqlCommand sqlcom = new SqlCommand(sql, sqlcon);
sqlcom.Parameters.AddWithValue("@previous", previous);
//-----只影响一条数据,晕,什么原因...
//sqlcom.Parameters.Add("@previous", SqlDbType.Char, 12);
//sqlcom.Parameters["@previous"].Value = previous;
sqlcon.Open();
sqlcom.ExecuteNonQuery();
sqlcon.Close();
sqlcom.Dispose();
sqlcon.Dispose();
}
#region "Add Item from DeptBox"
private void getHiddenValue(out string model, out string index)
{
string hdn = hdnChoose.Value;
string[] hdnStr = hdn.Split('/');
string[] modelStr = hdnStr[0].Split(':');
model = modelStr[1];
string[] indexStr = hdnStr[1].Split(':');
index = indexStr[1];
}
private ListBox selectListBox()
{
ListBox lst;
string model = "";
string index = "";
getHiddenValue(out model, out index);
if (index == "Adavance")
{
lst = lstAdavance;
}
else if (index == "Intermediate")
{
lst = lstIntermediate;
}
else
{
lst = lstBase;
}
return lst;
}
private void AddDeptFromTreeview(TreeView tree, ListBox lst)
{
foreach (TreeNode root in tree.Nodes)
{
AddTreeNode(root, lst);
foreach (TreeNode node in root.ChildNodes)
{
AddTreeNode(node, lst);
}
}
}
private void AddTreeNode(TreeNode node, ListBox lst)
{
if (node.Checked)
{
string Dept = node.Text;
if (!haveSameItem(Dept, lst))
{
string Value;
if (lst == lstAdavance)
{
Value = insertDept(Dept);
int index = lst.Items.Count;
lst.Items.Add(Dept);
lst.Items[index].Value = Value;
node.Checked = false;
}
else if (lst == lstIntermediate)
{
if (lstAdavance.SelectedIndex > -1)
{
string previous = lstAdavance.SelectedValue.Trim();
Value = insertDept(Dept, previous, 2);
int index = lst.Items.Count;
lst.Items.Add(Dept);
lst.Items[index].Value = Value;
node.Checked = false;
}
}
else if (lst == lstBase)
{
if (lstIntermediate.SelectedIndex > -1)
{
string previousIm = lstIntermediate.SelectedValue.Trim();
Value = insertDept(Dept, previousIm, 3);
int index = lst.Items.Count;
lst.Items.Add(Dept);
lst.Items[index].Value = Value;
node.Checked = false;
}
}
}
else
{
node.Checked = false;
}
}
}
private bool haveSameItem(string member, ListBox lst)
{
for (int i = 0; i < lst.Items.Count; i++)
{
if (string.Compare(member.Trim(), lst.Items[i].Text.Trim()) == 0)
{
return true;
}
}
return false;
}
#endregion
protected void btnSure_Click(object sender, EventArgs e)
{
ListBox lst = selectListBox();
AddDeptFromTreeview(trvDept, lst);
}
table1:数据结构
存储过程:
CREATE PROC gyjl_DeptInert
@Prev CHAR(20) OUTPUT,
@Dept CHAR(30),
@Layer TINYINT,
@Previous CHAR(12)
AS
BEGIN
DECLARE @SQL NVARCHAR(1000);
--带参数的字符串语句
SET @SQL =
/*插入部门和部门级别 */
'INSERT table1' +
' (Name, layer)' +
' VALUES (rtrim(@Dept), @Layer);' +
/*创建部门之间关联*/
' UPDATE table1' +
' SET relation = rtrim(@Previous) + rtrim(cast(@@identity AS char(5)))' +
' WHERE num = @@identity;' +
/*返回部门关联的字符串*/
' SELECT @Prev = relation' +
' FROM table1' +
' WHERE num = @@identity;';
-- 执行带参数的字符串语句
EXEC sp_executesql @SQL,
N'@Dept CHAR(30),@Layer TINYINT,@Previous CHAR(12),@Prev CHAR(20) OUTPUT',
@Dept,@Layer,@Previous,@Prev OUTPUT
End
--return @Prev 和 output 是两个概念,return只是返回值,而output可以向外输出...
--一般来说output只需要一个就够了...
总结:
1,数据结构
2,弹出的tab,并且可以拖动
3,在存储使用带参数的执行语句: exec sp_executesql @sql
4,存储过程返回值,取得并使用
5,这个过程并不适合用Ajax