staffquery.cs

工资结算系统 拥有权限控制 临时工资表 自动导入导出Excel 以及邮件群发功能

			this.totalNumber.Location = new System.Drawing.Point(352, 312);
			this.totalNumber.Name = "totalNumber";
			this.totalNumber.ReadOnly = true;
			this.totalNumber.Size = new System.Drawing.Size(152, 21);
			this.totalNumber.TabIndex = 3;
			this.totalNumber.Text = "";
			// 
			// groupBox2
			// 
			this.groupBox2.BackColor = System.Drawing.Color.AliceBlue;
			this.groupBox2.Controls.Add(this.Excel_button8);
			this.groupBox2.Controls.Add(this.totalNumber);
			this.groupBox2.Controls.Add(this.label1);
			this.groupBox2.Location = new System.Drawing.Point(8, 72);
			this.groupBox2.Name = "groupBox2";
			this.groupBox2.Size = new System.Drawing.Size(688, 352);
			this.groupBox2.TabIndex = 4;
			this.groupBox2.TabStop = false;
			// 
			// Excel_button8
			// 
			this.Excel_button8.Location = new System.Drawing.Point(512, 312);
			this.Excel_button8.Name = "Excel_button8";
			this.Excel_button8.Size = new System.Drawing.Size(104, 23);
			this.Excel_button8.TabIndex = 15;
			this.Excel_button8.Text = "导出Excel";
			this.Excel_button8.Click += new System.EventHandler(this.Excel_button8_Click);
			// 
			// StaffQuery
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
			this.BackColor = System.Drawing.Color.AliceBlue;
			this.ClientSize = new System.Drawing.Size(704, 437);
			this.Controls.Add(this.groupBox1);
			this.Controls.Add(this.dataGrid1);
			this.Controls.Add(this.groupBox2);
			this.Location = new System.Drawing.Point(140, 0);
			this.Name = "StaffQuery";
			this.StartPosition = System.Windows.Forms.FormStartPosition.Manual;
			this.Text = "员工信息查询";
			this.Load += new System.EventHandler(this.StaffQuery_Load);
			this.groupBox1.ResumeLayout(false);
			((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
			this.groupBox2.ResumeLayout(false);
			this.ResumeLayout(false);

		}
		#endregion

		private void StaffQuery_Load(object sender, System.EventArgs e)
		{
			this.loadStaff();
			this.contextMenu1.Popup += new System.EventHandler(this.contextMenu1_Popup);		
		}
		public void loadStaff()
		{   
			string sql = "select 编号,姓名,Email,银行卡号 from employee where 状态=0 order by  编号 asc";
			showData(sql);
		}
		//执行sql语句把查询出的条件
		public void showData(string sql)
		{
			try
			{
				if(dbCon==null) dbCon = MainForm.getConnection();
				dbAdapter = new OleDbDataAdapter(sql , dbCon);
				dbAdapter.TableMappings.Add("Table","employee");
				ds=new DataSet("employee");
				ds.Clear();
				dbAdapter.Fill(ds);
				this.dataGrid1.SetDataBinding(ds,"employee");
				this.totalNumber.Text = ds.Tables["employee"].Rows.Count.ToString();
			}
			catch(Exception ee)
			{
				MessageBox.Show(ee.ToString());
			}			
		}
        //单击查询员工信息按钮后的事件处理
		private void button1_Click(object sender, System.EventArgs e)
		{
			string sql = "select 编号,姓名,Email,银行卡号 from employee  where 状态=0  ";
			//string sql = "select e.autoid,e.name,e.email,e.sex,e.workdate,e.personid,e.homephone,e.officephone,e.mobile,e.address,e.workstatus,e.residence,e.buziness,e.bargainstart,e.bargainend,d.depname,e.mess from employee e,t_dep d where e.depcode=d.depcode and e.status=0 ";
		   query(sql);
			this.contextMenu1.Popup += new System.EventHandler(this.contextMenu1_Popup);
		}
		//检查输入的查询条件中的用户银行卡号是否合法
		private  string  number_Input_Check(string caption,string  text)
		{
			string error="";
			text=text.Trim();
			if(text=="" )
			{
				error=caption+"不能为空！！\n";
				return(error);
			}
			
			int i=0;
			for(i=0;i<text.Length;i++)
			{

				if(text[i]>'9' || text[i]<'0')            
					break;
			}
			if(i<text.Length)
			{
				error=caption+"输入格式不正确，请修正！！！\n";
				return(error);
			}
	
			return(error);
		}
        //根据条件从数据库中检索出 符合条件的员工信息
		private void query(string sql)
		{
			try
			{
			  string error="";
				if(this.idCheck.Checked)//按编号查询
				{
					
					error+=number_Input_Check("编号",this.empid.Text);
					if(!error.Trim().Equals(""))
					{
						MessageBox.Show(error);
						return;
					}
					long autoid = long.Parse(this.empid.Text);
					sql+=" and  编号="+autoid;
				
				}
				if(this.nameCheck.Checked)//按员工姓名查询
				{
					if(this.empname.Text.Trim().Equals(""))
					{
						MessageBox.Show("姓名不能为空！！！\n");
						return;
					}
					sql+=" and 姓名='"+this.empname.Text+"' ";
				}
				if(this.emailcheck.Checked)//按员工Email查询
				{
					error+=email_Input_Check();
					if(!error.Trim().Equals(""))
					{
						MessageBox.Show(error);
						return;
					}
					sql+=" and Email='"+this.email.Text+"' ";
				}
				if(this.bankcardcheck.Checked)//按员工银行卡帐号查询
				{
					error+=number_Input_Check("银行卡号",this.bankcard.Text);
					if(!error.Trim().Equals(""))
					{
						MessageBox.Show(error);
						return;
					}
					
					sql+=" and 银行卡号='"+this.bankcard.Text+"'";
				}
				 sql+="  order by  编号 asc";
				this.showData(sql);
			}
			catch(Exception ee)
			{
				MessageBox.Show(ee.ToString());
			}
		}

		private void dataGrid1_MouseUp(object sender, System.Windows.Forms.MouseEventArgs e)
		{
			
			System.Drawing.Point pt = new Point(e.X, e.Y); 
 
			DataGrid.HitTestInfo hti = dataGrid1.HitTest(pt); 
 
			if(hti.Type == DataGrid.HitTestType.Cell) 
			{ 
				dataGrid1.CurrentCell = new DataGridCell(hti.Row, hti.Column); 
				dataGrid1.Select(hti.Row); 
			} 
		}

		private void contextMenu1_Popup(object sender, System.EventArgs e)
		{
			if(ds==null || ds.Tables["employee"].Rows.Count==0)
			{
				this.menuItem1.Visible = false;
				this.menuItem2.Visible = false;
				this.menuItem4.Visible = false;
			}
			else
			{
				this.menuItem1.Visible = true;
				this.menuItem2.Visible = true;
				this.menuItem4.Visible = false;
			}
		}
		private void contextMenu1_Popup1(object sender, System.EventArgs e)
		{
			if(ds==null || ds.Tables["employee"].Rows.Count==0)
			{
				this.menuItem1.Visible = false;
				this.menuItem2.Visible = false;
				this.menuItem4.Visible = false;
			}
			else
			{
				this.menuItem1.Visible = false;
				this.menuItem2.Visible = false;
				this.menuItem4.Visible = true;
			}
		}

        /// <summary>
        /// 单击ContextMenu中的 删除该条员工信息菜单项 将
        /// 把该条员工信息的状态置成删除状态1
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
		private void menuItem2_Click(object sender, System.EventArgs e)
		{
			long empid = long.Parse(this.dataGrid1[this.dataGrid1.CurrentRowIndex,0].ToString());
			//取得用户选择的员工信息的编号
			try
			{
				if(dbCon==null) dbCon = MainForm.getConnection();
				OleDbCommand cmd = dbCon.CreateCommand();
			    cmd.CommandText = "update employee set 状态=1 where 状态=0 and 编号="+empid;
				//该条员工信息 置删除状态
				int result = cmd.ExecuteNonQuery();				
				if(result==1)
				{  
					string sql = "select 编号,姓名,Email,银行卡号 from employee where 状态=0 ";
					query(sql);//重新加载员工信息
				}
				cmd.Dispose();
			}
			catch(Exception ee)
			{
				MessageBox.Show(ee.ToString());
			}
		}
		
        /// <summary>
        /// 单击ContextMenu中的 修改该条员工信息菜单项 将打开修改员工信息窗口
        /// 修改完成后 本窗口显示的员工信息将被刷新
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
		private void menuItem1_Click(object sender, System.EventArgs e)
		{			
			StaffModify em = new StaffModify(this.dataGrid1);
			em.Owner = this;
			em.ShowDialog();
			string sql = "select 编号,姓名,Email,银行卡号 from employee where 状态=0 ";
			query(sql);			
		}
        /// <summary>
        /// 单击ContextMenu中的 恢复该条员工信息菜单项 将打开恢复员工信息窗口
        /// 将把数据库中该条员工信息状态改成有销状态 1
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
		private void menuItem4_Click(object sender, System.EventArgs e)
		{
			employee.RenewStaff rwk= new employee.RenewStaff(this.dataGrid1,0);
			rwk.Owner = this;
			rwk.ShowDialog();	
		}
        /// <summary>
        /// 单击 查询已删除信息 按钮时 从书库取出所有状态为 已删除状态1的员工信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
		private void button3_Click(object sender, System.EventArgs e)
		{
			string sql = "select 编号,姓名,Email,银行卡号 from employee  where 状态=1 order by  编号 asc";
			this.showData(sql);
			this.contextMenu1.Popup += new System.EventHandler(this.contextMenu1_Popup1);
		}
		//检查输入的查询条件的Email地址是否合法
		private string email_Input_Check()
		{
			string error="";
			if(this.email.Text=="" || this.email.Text.Trim().Equals(""))
			{
				error="Email不能为空！！！\n      ";
				return error;
			}
			if((this.email.Text.IndexOf("@")==-1) || (this.email.Text.IndexOf(".")==-1))
			{
				error+="Email格式不正确！！！\n    ";
				return error;
			}
			return error;			
		}
        //单击导出Excel按钮 将把dataGrid1中的数据导出到Excel
		private void Excel_button8_Click(object sender, System.EventArgs e)
		{
			System.Data.DataSet ds = (System.Data.DataSet)this.dataGrid1.DataSource;
			if(ds==null|| ds.Tables.Count<=0) 
			{
				MessageBox.Show("您没查询，不能导出Excel！！！\n  请进行查询！！");
				return;
			}
			Excel.Application ExcelObj = null;
			ExcelObj = new Excel.Application();
			ExcelObj.Visible =false;
			SaveFileDialog sf = new SaveFileDialog();
			HrSalary.util.GridUtil   gridutil=new HrSalary.util.GridUtil();
			gridutil.GridXls(sf,this.dataGrid1,ExcelObj);
			ExcelObj.Quit();
		}

	}
}