嚼的絮絮叨叨

Create Table Org(orgid int identity(1,1) primary key,orgname nvarchar(10),orgtype int not null)--0公司1部門2小組

insert into org values('公司',0)
insert into org values('財務部',1)
insert into org values('行政部',1)
insert into org values('業務部',1)
insert into org values('資訊部',1)
insert into org values('會計組',2)
insert into org values('總務組',2)
insert into org values('硬體組',2)
Select * From Org 

Create Table OrgRecord(chgID int identity(1,1),orgid int not null,parentid int,chgDate datetime default getdate())

Select * From Orgrecord

insert into OrgRecord(orgid,parentid) values(1,null)
insert into OrgRecord(orgid,parentid) values(2,1)
insert into OrgRecord(orgid,parentid) values(3,1)
insert into OrgRecord(orgid,parentid) values(4,1)
insert into OrgRecord(orgid,parentid) values(5,1)
insert into OrgRecord(orgid,parentid) values(6,2)
insert into OrgRecord(orgid,parentid) values(7,3)
insert into OrgRecord(orgid,parentid) values(8,5)
insert into OrgRecord(orgid,parentid) values(6,3)
insert into OrgRecord(orgid,parentid) values(7,4)insert into OrgRecord(orgid,parentid) values(8,1)

Select * From OrgRecord

--最新組織圖依時間找資料

Select O.orgid,parentid,O.chgdate,orgname,O.chgid From Orgrecord O

inner join (Select MAX(chgID) as chgID,orgid From Orgrecord

Where chgDate < Getdate() Group by orgid) OM on o.chgID = om.chgID

inner join Org Og on O.orgid=Og.orgid

這樣傳入時間，就可以取得指定時間時，最的組織圖。

--

樹狀圖的產生

使用winform的Treeview控制項。

取得最新的組織圖資料後，接著要生成在treeview中，通常這會使用一個遞迴的技巧，首先先找出最高的組織層，也就是parnetid為null的組織，可以用
DataRow[] rows = dt.Select("parentid is null");

取得一組datarow的陣列，在新增完最高組織後，將自已的orgid傳給遞迴的function，在遞迴中，用

DataRow[] rows = dt.Select("parentid = " PID.Tostring());
依序取得datarow陣列，接著就是遞迴了。

        #region 資料樹生成

        //取得資料樹

        public bool GetMenu(TreeView MenuTree, DateTime Treedate)

        {

            DataTable dt = new DataTable();

            using (SqlConnection cn = new SqlConnection())

            {

                dt.Clear();

                string cmmStr = @"Select O.orgid,parentid,O.chgdate,orgname,O.chgid From Orgrecord O

inner join (Select MAX(chgID) as chgID,orgid From Orgrecord Where chgDate <= @chgDate Group by orgid) OM on o.chgID = om.chgID

inner join Org Og on O.orgid=Og.orgid"; 

                cn.ConnectionString = @"Data Source=.\sqlexpress;Initial Catalog=Org;Integrated Security=True";

                SqlDataAdapter adp = new SqlDataAdapter(cmmStr, cn);

                adp.SelectCommand.Parameters.Add("@chgDate", SqlDbType.DateTime).Value = Treedate;

                adp.Fill(dt);

            } 

            bool result = false;

            if (dt.Rows.Count > 0)

            {

                MenuTree.Nodes.Clear();

                result = AddRoot(ref MenuTree, ref dt);

                dt.Dispose();

                MenuTree.ExpandAll();

            }

                return result

        }

        //取得根節點

        private bool AddRoot(ref TreeView MenuTree, ref DataTable dt)

        {

            try

            {

                int tmpNodeID;

                DataRow[] rows = dt.Select("parentid is null");

                if (rows.GetUpperBound(0) >= 0)

                {

                    TreeNode NewNode;

                    myTreeNode newnode;

                    bool rc;

                    foreach (DataRow row in rows)

                    {

                        NewNode = new TreeNode();

                        tmpNodeID = int.Parse(row["orgid"].ToString());

                        NewNode.Text = row["orgname"].ToString();

                        newnode = new myTreeNode();

                        newnode.orgid = int.Parse(row["orgid"].ToString());

                          newnode.Text = row["orgname"].ToString();

                        newnode.parentid = null;

                        MenuTree.Nodes.Add(NewNode);

                        NewNode.Tag = newnode; 

                        rc = AddNodes(ref NewNode, tmpNodeID, ref dt);

                    }

                }

                rows = null;

                return true;

            }

            catch

            {

                return false;

            }

        }

        //取得子節點，遞廻

        private bool AddNodes(ref TreeNode tNode, int PID, ref DataTable dt)

        {

            try

            {

                int tmpNodeID;

                DataRow[] rows = dt.Select("parentid = " + PID.ToString());

                if (rows.GetUpperBound(0) >= 0)

                {

                    TreeNode NewNode;

                    myTreeNode newnode;

                    bool rc;

                    foreach (DataRow row in rows)

                    {

                        NewNode = new TreeNode();

                        tmpNodeID = int.Parse(row["orgid"].ToString());

                        NewNode.Text = row["orgname"].ToString();

                        newnode = new myTreeNode();

                        newnode.orgid = int.Parse(row["orgid"].ToString());

                        newnode.Text = row["orgname"].ToString();

                        newnode.parentid = int.Parse(row["parentid"].ToString());

                        //tNode.ChildNodes.Add(NewNode); //web端的寫法

                        //tNode.Nodes.Add(NewNode); //form端的寫法

                        tNode.Nodes.Add(NewNode);

                        NewNode.Tag = newnode;

                        rc = AddNodes(ref NewNode, tmpNodeID, ref dt);

                    }

                }

                rows = null;

                return true;

            }

            catch

            {

                return false;

            }

        }

        #endregion

--

樹狀圖拖放

先開啟treeview的allowdrag，註冊Dragover /DragEnter / ItemDrag / DragDrop 事件

在DragDrop中，先判斷是否來源與目標不同，接著來來源加入目標，然後刪除來源，就完成介面上的操作，

然後要將異動放入List中，這樣在存檔時，可以將異動寫回資料庫。

        #region 資料樹移動

        //移動中

        void treeView1_DragOver(object sender, DragEventArgs e)

        {

            e.Effect = DragDropEffects.Move;

        }

        //移到上方

        void treeView1_DragEnter(object sender, DragEventArgs e)

        {           

            e.Effect = DragDropEffects.Move;

        }

        //移動開始

        void treeView1_ItemDrag(object sender, ItemDragEventArgs e)

        {

            TreeNode tn = e.Item as TreeNode; 

            //根節點不能動，非根節點可以動

            if (e.Button == MouseButtons.Left && tn != null && tn.Parent != null)

            {

                DoDragDrop(e.Item, DragDropEffects.Move);

            }

        }

        //放下節點

        void treeView1_DragDrop(object sender, DragEventArgs e)

        {

            //判斷數據是否是 TreeNode 的格式

            if (e.Data.GetDataPresent("System.Windows.Forms.TreeNode", false))

            {

                //由拖拉資料中取得來源node

                TreeNode dragedTreeNode = (TreeNode)e.Data.GetData("System.Windows.Forms.TreeNode");

                //獲取鼠標落點處 TreeNode

                Point pt = ((TreeView)sender).PointToClient(new Point(e.X, e.Y));

                TreeNode targetTreeNode = ((TreeView)sender).GetNodeAt(pt);

                if (dragedTreeNode.Equals(targetTreeNode))

                    return;

                targetTreeNode.Nodes.Add(dragedTreeNode.Clone() as TreeNode);

                dragedTreeNode.Remove();

                //紀錄節點的變化

                myTreeNode newnode = dragedTreeNode.Tag as myTreeNode;

                myTreeNode parantnode = targetTreeNode.Tag as myTreeNode;

                if (newnode != null)

                {

                    newnode.parentid = parantnode.orgid;

                    lst.Add(newnode);

                    dataGridView1.DataSource = lst.ToArray();

                    toolStripStatusLabel1.Text = "組織已改變，請按下存檔";

                    isChanged = true;

                }

                (sender as TreeView).ExpandAll();

            }

            else

            {

                MessageBox.Show("不是有效的節點！");

            }

        }

        #endregion