<%@ 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="C#" runat="server">
	SqlConnection Source;
	int NowPageCount;
	string d="SELECT Staff.*, Category.Content FROM Category INNER JOIN Staff ON Category.ID = Staff.Depart";
	string NowSort{  
		get{
			return ViewState["NowSort"].ToString();
		} 
		set{
			ViewState["NowSort"]=value;}
	}
	void Page_load(object sender,EventArgs e){
		Source = (SqlConnection)Session["NowEmpSQL"];
		errMsg.Text="";
		if(IsPostBack==false){
			NowSort="";
        		Hashtable HT=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);
			}
	}
	void bindNew(Boolean tag){//结合至DataGrid
		if(tag==true){
			DataSet DS = new DataSet();
			SqlDataAdapter NComm = new SqlDataAdapter(d, Source);
			NComm.Fill(DS, "staff");
			Session["NowDS"]=DS;//声明为Session
			staffShow.DataSource=DS.Tables["staff"].DefaultView;
			staffShow.DataBind();
		}
		else{
			DataSet DS=(DataSet)Session["NowDS"];
			DataView dv=DS.Tables["staff"].DefaultView;
			dv.Sort=NowSort;
			staffShow.DataSource=dv;
			staffShow.DataBind();
		}
		NowPageCount=staffShow.PageCount;
		CurrentPage.Text = "第" + Convert.ToInt32(staffShow.CurrentPageIndex + 1).ToString() + "页，共" + staffShow.PageCount + "页";
	}
	void SortOK_Click(object sender,EventArgs e){
		NowSort="";
		DataSet DS=(DataSet)Session["NowDS"];
		DataView dv=DS.Tables["staff"].DefaultView;
		for(int i=0;i<AllField.Items.Count-1;i++){//取得每一选项
			if(AllField.Items[i].Selected==true){//判断是否选取
				NowSort +=  "," + AllField.Items[i].Value + " " + SortType.SelectedItem.Value;
			}
		}
		if(NowSort==null){//若未选取则不处理
			return;
		}
		else{
			NowSort=NowSort.Remove(0,1);
			bindNew(false);
//			dv.Sort = NowSort; //执行排序
//			staffShow.DataSource=dv;
		}
	}
	void staffShow_Edit(object Sender,DataGridCommandEventArgs e){
		Label p =(Label)staffShow.Items[e.Item.ItemIndex].Cells[6].Controls[1];
		string NowDepart = p.Text;
		Label y =(Label)staffShow.Items[e.Item.ItemIndex].Cells[5].Controls[1];
		string NowBday=y.Text;
		staffShow.EditItemIndex = e.Item.ItemIndex;//编辑
		bindNew(false);
		Calendar c;
		c=(Calendar)staffShow.Items[e.Item.ItemIndex].Cells[5].FindControl("cal_Bday");
		if(NowBday.Length>0){
			c.SelectedDate=Convert.ToDateTime(NowBday).Date;//设定日期
			c.VisibleDate = Convert.ToDateTime(NowBday).Date;//切换至目前日期
		}
		else{
			c.SelectedDate=DateTime.Today; //月历等于今天
		}
		DataSet NowDS = (DataSet)Session["NowDS"];
		SqlDataAdapter NComm1 = new SqlDataAdapter("select * from Category where Type Like '部门' order by id", Source);
		NComm1.Fill(NowDS, "Category");
		DropDownList dt; //寻找部门控件  
		dt=(DropDownList)staffShow.Items[e.Item.ItemIndex].Cells[6].FindControl("edit_Depart");
		dt.DataSource=NowDS.Tables["Category"].DefaultView;
		dt.DataTextField="Content";
		dt.DataValueField="ID";
		dt.DataBind();//结合至部门
		int f=0;  //逐一比对
		foreach(DataRow Row in NowDS.Tables["Category"].Rows){
			if(Row["Content"].ToString()==NowDepart){
				dt.SelectedIndex=f; //找到及设定选取索引值
				return;
			}
			f+=1;
		}
	}
	void Date_Selected(object s, EventArgs e){
		TextBox t; 
		t=(TextBox)staffShow.Items[staffShow.EditItemIndex].Cells[5].FindControl("edit_Bday");
		Calendar c;
		c=(Calendar)staffShow.Items[staffShow.EditItemIndex].Cells[5].FindControl("cal_Bday");
		t.Text=c.SelectedDate.ToShortDateString();
        }
	void staffShow_Cancel(object Sender,DataGridCommandEventArgs e){
		staffShow.EditItemIndex = -1; //取消
		bindNew(false);
	}
	void staffShow_Update(object Sender,DataGridCommandEventArgs e){
		string Nowsid=staffShow.DataKeys[staffShow.EditItemIndex].ToString();
		string Ustring; //更新的T-SQL
		Ustring="Update Staff Set";
		Ustring+=" cname='" + ((TextBox)e.Item.Cells[3].Controls[0]).Text + "'";
		Ustring+=", pid ='"+ ((TextBox)e.Item.Cells[4].Controls[0]).Text + "'";
		string Inputdate=((TextBox)e.Item.Cells[5].FindControl("edit_Bday")).Text;
		try{
			if(Inputdate.Length>0){
				DateTime b=Convert.ToDateTime(((TextBox)e.Item.Cells[5].FindControl("edit_Bday")).Text);
				Ustring+=", Bday ='" + b.ToString("yyyy/MM/dd") + "'";
			}
			Ustring+=", Depart ="+ Convert.ToInt32(((DropDownList)e.Item.Cells[6].FindControl("edit_Depart")).SelectedItem.Value);
			Ustring+=" where SID ='" + Nowsid + "'";
			SqlCommand UpStaff=new SqlCommand(Ustring,Source);
			UpStaff.Connection.Open();
			UpStaff.ExecuteNonQuery();//执行保存
			UpStaff.Connection.Close();
			staffShow.EditItemIndex = -1;
			bindNew(true);
		}
		catch(Exception ex){
			errMsg.Text="发生错误，无法保存"+ex.ToString();
		}
	}
	void staffShow_Item(Object sender, DataGridCommandEventArgs e){
		if(e.CommandName=="Delete"){
			staffShow.SelectedIndex=e.Item.ItemIndex;
			string Dstring;
			string Nowsid=staffShow.DataKeys[staffShow.SelectedIndex].ToString();		
			Dstring = "Delete From Staff where SID ='" + Nowsid + "'";
			try{
				SqlCommand UpStaff=new SqlCommand(Dstring,Source);
				UpStaff.Connection.Open();
				UpStaff.ExecuteNonQuery();//执行保存
				UpStaff.Connection.Close();
				bindNew(true);
				errMsg.Text="Delete OK";
			}
			catch{
				errMsg.Text="Delete fail";
			}
		}
	}
	void staffShow_Bound(object sender,DataGridItemEventArgs e){
		if(e.Item.ItemIndex > -1){
			HyperLink k=(HyperLink)e.Item.Cells[1].FindControl("DelBtn");
			string x=e.Item.Cells[0].FindControl("DeleteRecord").UniqueID;
			k.NavigateUrl="javascript:doconfirm('Are you sure?','" + x + "','')";
			k.Text="Delete";
		}
	}
	void ChangePage(object sender,DataGridPageChangedEventArgs e){
		staffShow.CurrentPageIndex = e.NewPageIndex;//设定目前页次
		bindNew(false);
	}
	void PagerChange(object sender,CommandEventArgs e){
		if(e.CommandArgument == "next"){//下页
			bindNew(false);
			if(staffShow.CurrentPageIndex < NowPageCount - 1){
				staffShow.CurrentPageIndex = staffShow.CurrentPageIndex + 1;
			}
			bindNew(false);
		}
		else if(e.CommandArgument == "prev"){//前页
			if(staffShow.CurrentPageIndex > 0){
				staffShow.CurrentPageIndex = staffShow.CurrentPageIndex - 1;
			}
			bindNew(false);
		}
		else if(e.CommandArgument == "last" ){//末页
			bindNew(false);
			staffShow.CurrentPageIndex = staffShow.PageCount - 1;
			bindNew(false);
		}
		else if(e.CommandArgument == "fir"){//首页
			staffShow.CurrentPageIndex = 0;
			bindNew(false);
		}
	}
	void AddClick(object sender,EventArgs e){  //添加
		SortOK.Enabled=false;
		DataSet NowDS = (DataSet)Session["NowDS"];
		SqlDataAdapter NComm1 = new SqlDataAdapter("select * from Category where Type Like '部门' order by id", Source);
		NComm1.Fill(NowDS, "Category");
		DropDownList ndt=depart; //取得Panel中的部门  
		ndt.DataSource=NowDS.Tables["Category"].DefaultView;
		ndt.DataTextField="Content";
		ndt.DataValueField="ID";
		ndt.DataBind();//结合至部门
		NewStaff.Visible=true;
		add.Visible=false;
	}
	void NewCancel(object sender,EventArgs e){  //添加时取消
		NewStaff.Visible=false;
		add.Visible=true;
		SortOK.Enabled=true;
	}
	void NewSave(object sender,EventArgs e){
		if(sid.Text.Length==0){
			errMsg.Text="未输入id，无法保存";
			return;
		}
		string Istring; //更新的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{
			SqlCommand UpStaff=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 + "添加失败";
		}
	}
	</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>