<%@ Register TagPrefix="Title" TagName="TitleShow" Src="/aspnetdata/Title.ascx" %>
<%@Page Debug=True %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Web" %>
<html>
<head>
	<script language="vb" runat="server">
	dim Source as SqlConnection
	Dim NowPageCount as Integer
	Dim d as string ="SELECT Staff.*, Category.Content FROM Category INNER JOIN Staff ON Category.ID = Staff.Depart"
	Property NowSort() as String 
		get
			return ViewState("NowSort").ToString()
		end get
		set
			ViewState("NowSort")=value
		end set
	end Property
	sub Page_load(sender as object, E as eventArgs)
		Source = cTYPE(Session("NowEMPSQL"),SqlConnection)
		if IsPostBack=false then
			errMsg.Text=""
			NowSort=""
			Dim HT as Hashtable=new Hashtable()
	        	HT.Add("StaffID", "SID")
        		HT.Add("Name", "cname")
        		HT.Add("PID", "pid")
        		HT.Add("BirthDay", "Bday")
			AllField.DataSource = HT
			AllField.DataValueField="Value"
			AllField.DataTextField="Key"
			AllField.DataBind()
			bindNew(true)
		end if
	end sub
	sub bindNew(tag as Boolean)
		if tag=true then
			DIM DS as DataSet = new DataSet()
			Dim NComm as SqlDataAdapter = new SqlDataAdapter(d, Source)
			NComm.Fill(DS, "staff")
			Session("NowDS")=DS '声明为Session
			staffShow.DataSource=DS.Tables("staff").DefaultView
			staffShow.DataBind()
		else
			Dim DS as DataSet =Ctype(Session("NowDS"),DataSet)
			Dim dv as DataView =DS.Tables("staff").DefaultView
			dv.Sort=NowSort
			staffShow.DataSource=dv
			staffShow.DataBind()
		end if
		NowPageCount=staffShow.PageCount
		CurrentPage.Text = "第" & Convert.ToInt32(staffShow.CurrentPageIndex + 1).ToString() & "页，共" & staffShow.PageCount & "页"
	end sub
	sub SortOK_Click(sender as object,e as EventArgs )
		NowSort=""
		Dim DS as DataSet =Ctype(Session("NowDS"),DataSet)
		Dim dv as DataView =DS.Tables("staff").DefaultView
		dim i as integer
		for i=0 to AllField.Items.Count-1
			if AllField.Items(i).Selected=true then '判断是否选取
				NowSort +=  "," & AllField.Items(i).Value & " " & SortType.SelectedItem.Value
			end if
		next
		if NowSort="" then '若未选取则不处理
			return
		else
			NowSort=NowSort.Remove(0,1)
			bindNew(false)
'			dv.Sort = NowSort; //执行排序
'			staffShow.DataSource=dv;
'			staffShow.DataBind();
		end if
	end sub
	sub staffShow_Edit(sender as object,e as DataGridCommandEventArgs)
		dim p as Label =CType(staffShow.Items(e.Item.ItemIndex).Cells(6).Controls(1),Label)
		dim NowDepart as string = p.Text
		dim y as Label  = Ctype(staffShow.Items(e.Item.ItemIndex).Cells(5).Controls(1),Label)
		dim NowBday as string=y.Text
		staffShow.EditItemIndex = e.Item.ItemIndex '编辑
		bindNew(false)
		dim c as Calendar
		c=Ctype(staffShow.Items(e.Item.ItemIndex).Cells(5).FindControl("cal_Bday"),Calendar)
		if NowBday<>"" then
			c.SelectedDate=Convert.ToDateTime(NowBday).Date '设定日期
			c.VisibleDate = Convert.ToDateTime(NowBday).Date '切换至目前日期
		else
			c.SelectedDate=DateTime.Today  '月历等于今天
		end if
		Dim NowDS as DataSet = Ctype(Session("NowDS"),DataSet)
		Dim NComm1 as SqlDataAdapter = new SqlDataAdapter("select * from Category where Type Like '部门' order by id", Source)
		NComm1.Fill(NowDS, "Category")
		dim dt as DropDownList '寻找部门控件 
		dt=Ctype(staffShow.Items(e.Item.ItemIndex).Cells(6).FindControl("edit_Depart"),DropDownList)
		dt.DataSource=NowDS.Tables("Category").DefaultView
		dt.DataTextField="Content"
		dt.DataValueField="ID"
		dt.DataBind() '结合至部门
		dim f as integer =0   '逐一比对
		dim Row as DataRow
		for each Row in NowDS.Tables("Category").Rows
			if Row("Content").ToString()=NowDepart then
				dt.SelectedIndex=f '找到及设定选取索引值
				return
			end if
			f+=1
		next
	end sub
	sub Date_Selected(s as object , e as EventArgs)
		dim t as TextBox
		t=CType(staffShow.Items(staffShow.EditItemIndex).Cells(5).FindControl("edit_Bday"),TextBox)
		dim c as Calendar
		c=Ctype(staffShow.Items(staffShow.EditItemIndex).Cells(5).FindControl("cal_Bday"),Calendar)
		t.Text=c.SelectedDate.ToShortDateString()
        end sub
	sub staffShow_Cancel(sender as object,e as DataGridCommandEventArgs)
		staffShow.EditItemIndex = -1 '取消
		bindNew(false)
	end sub
	sub staffShow_Update(sender as object,e as DataGridCommandEventArgs)
		dim Nowsid as string=staffShow.DataKeys(staffShow.EditItemIndex).ToString()
		dim Ustring as string '更新的T-SQL
		Ustring="Update Staff Set"
		Ustring+=" cname='" & Ctype(e.Item.Cells(3).Controls(0),TextBox).Text & "'"
		Ustring+=", pid ='" & CType(e.Item.Cells(4).Controls(0),TextBox).Text & "'"
		dim Inputdate as string = Ctype(e.Item.Cells(5).FindControl("edit_Bday"),TextBox).Text
		try
			if Inputdate <> "" then
				dim b as DateTime=Convert.ToDateTime(CType(e.Item.Cells(5).FindControl("edit_Bday"),TextBox).Text)
				Ustring+=", Bday ='" & b.ToString("yyyy/MM/dd") & "'"
			end if
			Ustring+=", Depart =" & Convert.ToInt32(CType(e.Item.Cells(6).FindControl("edit_Depart"),DropDownList).SelectedItem.Value)
			Ustring+=" where SID ='" & Nowsid & "'"
			dim UpStaff as SqlCommand =new SqlCommand(Ustring,Source)
			UpStaff.Connection.Open()
			UpStaff.ExecuteNonQuery() '执行保存
			UpStaff.Connection.Close()
			staffShow.EditItemIndex = -1
			bindNew(true)
		catch ex as Exception
			errMsg.Text="发生错误，无法保存" & ex.ToString()
		end try
	end sub
	sub staffShow_Item(sender as Object,e as DataGridCommandEventArgs )
		if e.CommandName="Delete" then
			staffShow.SelectedIndex=e.Item.ItemIndex
		dim Dstring as string
		dim Nowsid as string =staffShow.DataKeys(staffShow.SelectedIndex).ToString()
		Dstring = "Delete From Staff where SID ='" + Nowsid + "'"
		try
			Dim UpStaff as SqlCommand =new SqlCommand(Dstring,Source)
			UpStaff.Connection.Open()
			UpStaff.ExecuteNonQuery() '执行保存
			UpStaff.Connection.Close()
			bindNew(true)
			errMsg.Text="Delete OK"
		catch
			errMsg.Text="Delete fail"
		end try
		end if
	end sub
	sub staffShow_Bound(sender as object,e as DataGridItemEventArgs )
		if e.Item.ItemIndex > -1 then
			dim k as Hyperlink = Ctype(e.Item.Cells(1).FindControl("DelBtn"),Hyperlink)
			dim x as string =e.Item.Cells(0).FindControl("DeleteRecord").UniqueID
			k.NavigateUrl="javascript:doconfirm('Are you sure?','" & x & "','')"
			k.Text="Delete"
		end if
	end sub
	sub ChangePage(sender as object,e as DataGridPageChangedEventArgs )
		staffShow.CurrentPageIndex = e.NewPageIndex '设定目前页次
		bindNew(false)
	end sub
	sub PagerChange(sender as object,e as CommandEventArgs)
		if e.CommandArgument = "next" Then '下页
			bindNew(false)
			if staffShow.CurrentPageIndex < NowPageCount - 1 then
				staffShow.CurrentPageIndex = staffShow.CurrentPageIndex + 1
			end if
			bindNew(false)
		elseif e.CommandArgument = "prev" then '前页
			if staffShow.CurrentPageIndex > 0 then
				staffShow.CurrentPageIndex = staffShow.CurrentPageIndex - 1
			end if
			bindNew(false)
		elseif e.CommandArgument = "last" then '末页
			bindNew(false)
			staffShow.CurrentPageIndex = staffShow.PageCount - 1
			bindNew(false)
		elseif e.CommandArgument = "fir" then '首页
			staffShow.CurrentPageIndex = 0
			bindNew(false)
		end if
	end sub
	sub AddClick(sender as object,e as EventArgs)  '添加
		SortOK.Enabled=false
		Dim NowDS as DataSet = Ctype(Session("NowDS"),DataSet)
		Dim NComm1 as SqlDataAdapter = new SqlDataAdapter("select * from Category where Type Like '部门' order by id", Source)
		NComm1.Fill(NowDS, "Category")
		Dim ndt as DropDownList=depart '取得Panel中的部门  
		ndt.DataSource=NowDS.Tables("Category").DefaultView
		ndt.DataTextField="Content"
		ndt.DataValueField="ID"
		ndt.DataBind() '结合至部门
		NewStaff.Visible=true
		add.Visible=false
	end sub
	sub NewCancel(sender as object,e as EventArgs)'添加时取消
		NewStaff.Visible=false
		add.Visible=true
		SortOK.Enabled=true
	end sub
	sub NewSave(sender as object,e as EventArgs)
		if sid.Text="" then
			errMsg.Text="未输入id，无法保存"
			return
		end if
		Dim Istring as string '更新的T-SQL
		Istring="Insert Into Staff (SID,Depart,cname,pid,TEL) Values ("
		Istring+= "'" & sid.Text & "'," & depart.SelectedItem.Value
		Istring+=", '" & cname.Text & "', '" & pid.Text & "'"
		Istring+=", '" & tel.Text & "')"
		try
			Dim UpStaff as SqlCommand=new SqlCommand(Istring,Source)
			UpStaff.Connection.Open()
			UpStaff.ExecuteNonQuery() '执行保存
			UpStaff.Connection.Close()
			staffShow.EditItemIndex = -1
			bindNew(true)
			NewStaff.Visible=false
			add.Visible=true
			SortOK.Enabled=true
		catch
			errMsg.Text="以" & Istring & "添加失败"
		end try
	end sub
	</script>
<script language="javascript">
<!--
function doconfirm(ask,arg1,arg2) {
	if(confirm(ask)) __doPostBack(arg1,arg2);
}
// -->
</script>
</head>
<body background="/aspnetdata/images/winback.gif">
<Title:TitleShow id="BookT" Runat="server" />
<form runat="server">
<asp:Panel id="NewStaff" runat="server"
	BackColor="gainsboro"
	borderwidth="1"
	bordercolor="red"
	Height="150px"
	Width="300px"
	visible="false"
>
<blockquote><br>
请输入编号(主索引)：<asp:textbox id="sid" runat="server" /><br>
请输入姓名：<asp:textbox id="cname" runat="server" /><br>
请输入身份证编号：<asp:textbox id="pid" runat="server" /><br>
请输入电话：<asp:textbox id="tel" runat="server" /><br>
请指定部门：<asp:dropdownlist id="depart" runat="server" /><br>
<asp:linkButton Text="Save" runat="server" OnClick="NewSave" />
<asp:linkButton Text="Cancel" runat="server" OnClick="NewCancel" /><br>
</blockquote>
</asp:Panel>
<table><tr><td valign="top" width="70">
指定欲排序的栏位(可复选)：
</td><td><asp:ListBox id=AllField Width="100px" rows="4" runat="server" selectionmode="Multiple" /></td>
<td valign="top">
　排序方式：<asp:DropDownlist ID="SortType" Runat="Server" >
            <asp:ListItem value="asc" selected>递增</asp:ListItem>
            <asp:ListItem value="desc">递减</asp:ListItem>
</asp:DropDownList>
<asp:Button id="SortOK" Runat="Server" Onclick="SortOK_Click" Text="执行排序" />
</td>
</tr></table>
<asp:linkbutton Text="Add" id="add" runat="server" OnClick="AddClick" />
      <asp:LinkButton id="MoveFirst" runat="server"
        Text="首页"
        CommandArgument="fir"
        ForeColor="navy"
        Font-Name="verdana" Font-size="8pt"
        OnCommand="PagerChange"
        />
      &nbsp;
      <asp:LinkButton id="MovePrev" runat="server"
        Text="上页"
        CommandArgument="prev"
        ForeColor="navy"
        Font-Name="verdana" Font-size="8pt"
        OnCommand="PagerChange"
        />
      &nbsp;
      <asp:LinkButton id="MoveNext" runat="server"
        Text="下页"
        CommandArgument="next"
        ForeColor="navy"
        Font-Name="verdana" Font-size="8pt"
        OnCommand="PagerChange"
        />
      &nbsp;
      <asp:LinkButton id="MoveLast" runat="server"
        Text="末页"
        CommandArgument="last"
        ForeColor="navy"
        Font-Name="verdana" Font-size="8pt"
        OnCommand="PagerChange"
        />   <asp:label id="CurrentPage" runat="server" />
<asp:label id="errMsg" Runat="server" />
      <ASP:DataGrid id="staffShow" runat="server"
        BorderColor="black"
        BorderWidth="1"
        GridLines="Both"
        CellPadding="3"
        CellSpacing="0"
        Font-Name="Verdana"
        Font-Size="9pt"
        HeaderStyle-BackColor="Yellow"
	DataKeyField="SID"
	SelectedItemStyle-BackColor="#FFFFC0"
	aUTOgENERATEcOLUMNS="FALSE"
	OnEditCommand="staffShow_Edit"
	OnCancelCommand="staffShow_Cancel"
	OnUpdateCommand="staffShow_Update"
	OnItemCommand="staffShow_Item"
	OnItemDataBound="staffShow_Bound"
        AllowPaging="True"
        pagesize="5"
PagerStyle-HorizontalAlign="Right"
PagerStyle-PageButtonCount="3"
        PagerStyle-Mode="NumericPages"
        OnPageIndexChanged="ChangePage"
>
<HeaderStyle Font-Bold="true"></HeaderStyle>
      <Columns>
        <asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Save" ItemStyle-Wrap="false"/>
	<asp:TemplateColumn>
	<itemtemplate >
	<asp:Hyperlink id="DelBtn" Runat="server" />
	<asp:LinkButton id="DeleteRecord" runat="server" Visible="true" text="" CommandName="Delete" />
	</ItemTEMPLATE>
	</asp:TemplateColumn>
	<asp:BoundColumn HeaderText="SID" ReadOnly="True" DataField="SID" ItemStyle-Wrap="false"/>
	<asp:BoundColumn HeaderText="Name" DataField="cname"/>
	<asp:BoundColumn HeaderText="PID" DataField="pid"/>
	<asp:TemplateColumn HeaderText="Birthday" >
		<ItemTemplate>
			<asp:Label runat="server" Text=<%# DataBinder.Eval(Container.DataItem, "Bday","{0:yyyy/MM/dd}") %> id="Bday"/>
		</ItemTemplate>
		<EditItemTemplate>
			<asp:Textbox id="edit_Bday" Text=<%# DataBinder.Eval(Container.DataItem, "Bday","{0:yyyy/MM/dd}") %> runat="server" />
			<asp:Calendar id="cal_Bday" Font-Size="11px" onselectionchanged="Date_Selected" runat="server" />
		</EditItemTemplate>
	</asp:TemplateColumn>
	<asp:TemplateColumn HeaderText="Department" >
		<ItemTemplate>
			<asp:Label runat="server" Text=<%# DataBinder.Eval(Container.DataItem, "Content") %> id="Content"/>
		</ItemTemplate>
		<EditItemTemplate>
			<asp:DropDownList runat="server" id="edit_Depart" />
		</EditItemTemplate>
	</asp:TemplateColumn>
	</Columns>
</ASP:DataGrid>
</form>