using System;
using System.Data .SqlClient ;
using System.Windows .Forms ;
using System.Data ;

namespace BlueHill.BlueHillWindows.SalaryManagement
{
	
	/// Salary 的摘要說明。
	
	public class Salary
	{
		//所有員工的基本工資
		public DataTable GetBasicSalary()
		{

			string conn="Data Source=localhost; Initial Catalog=BlueHill; Trusted_Connection=Yes; ";
			string com="SELECT EmployeeID , [Name], BasicSalary  FROM tblEmployee";
	
 
			SqlDataAdapter dap=new SqlDataAdapter (com,conn);
			DataSet ds=new DataSet ();
			dap.Fill (ds,"tblEmployee");
			DataTable dt=ds.Tables["tblEmployee"];
			return dt;
		}
		//  顯示某個員工薪資歷史記錄
		public DataTable HistorySalary(string id)
		{
			string con="Data Source=.; Initial Catalog=BlueHill; Trusted_Connection=Yes";	
			string com="select e.Name, s.SalaryTime , s.BasicSalary, s.OvertimeSalary, s.AbsenceSalary, s.OtherSalary ";
			string m="from tblSalary as s join tblEmployee as e on s.EmployeeID=e.EmployeeID where s.EmployeeID=@empId";
			string comm=com+m;
			
			SqlConnection conn=new SqlConnection (con);
			SqlCommand command=new SqlCommand (comm,conn);
			command.Parameters.Add ("@empId",SqlDbType.Int);
			command.Parameters ["@empId"].Value =id;
			
			SqlDataAdapter dap=new SqlDataAdapter (command);
			DataSet ds=new DataSet ();
			dap.Fill (ds,"History");
			DataTable dt=ds.Tables ["History"];
			return dt;


		
		}

		//基本工資的設定
		public DataTable FormSettingSalary(int id ,int setSalary,DateTime nowDate)
		{    
			string con="Data Source=localhost; Initial Catalog=BlueHill; Trusted_Connection=Yes";
			SqlConnection conn=new SqlConnection (con);
			conn.Open();
			
			string comm="INSERT INTO tblSalary (EmployeeID,BasicSalary,SalaryTime) VALUES (@id,@setSalary,@nowDate)";
			SqlCommand command=new SqlCommand (comm,conn);
			command.Parameters .Add ("@id",SqlDbType.Int );
			command.Parameters ["@id"].Value =id;
			command.Parameters .Add ("@setSalary",SqlDbType.Int  );
			command.Parameters ["@setSalary"].Value =setSalary;
			command.Parameters .Add ("@nowDate",SqlDbType.DateTime);
			command.Parameters ["@nowDate"].Value =nowDate;
			DataTable dt=null;
			
		
			if (command.ExecuteNonQuery()>0)
			{
				//設定基本薪資
				SqlDataAdapter dap=new SqlDataAdapter ("spwinSetBasicSalary",conn);
				dap.SelectCommand .CommandType =CommandType.StoredProcedure ;

				
				SqlParameter pa1=new SqlParameter ("@EmpID",SqlDbType.Int );
				pa1.Value =id;
				dap.SelectCommand.Parameters.Add (pa1);
				SqlParameter pa2=new SqlParameter ("@BasicSalary",SqlDbType.Int );
				pa2.Value =setSalary;
				dap.SelectCommand.Parameters .Add (pa2);
				
				
				DataSet ds=new DataSet ();
				dap.Fill (ds,"BasicSalary");
				dt=ds.Tables["BasicSalary"];

				MessageBox.Show ("修改成功","提示",MessageBoxButtons.OK ,MessageBoxIcon.Information );
			}
			else
			{
				MessageBox.Show ("出錯");
			}
			conn.Close();
			
			return dt;
			

			
		}
		//查詢員工
		public DataTable  SearchEmployee(string name,string email,string deptName)
		{
			string con=@"Data Source=.;Initial Catalog=BlueHill; Trusted_Connection=Yes";
			string comm;
			SqlDataAdapter dap=null;
			DataTable dt=null;


			if(name!="")
			{
				comm="select EmployeeID,[Name],BasicSalary from tblEmployee where Name=@name";
				SqlConnection conn=new SqlConnection (con);
				SqlCommand command=new SqlCommand (comm,conn);
				command.Parameters .Add ("@name",SqlDbType.NVarChar ,15);
				command.Parameters ["@name"].Value =name;
				dap=new SqlDataAdapter (command);

				DataSet ds=new DataSet ();
				dap.Fill (ds,"tblEmployee");
				dt=ds.Tables ["tblEmployee"];
				if (dt.Rows .Count <1)
				{
					MessageBox.Show ("該員工不存在，找不到符合的記錄","注意",MessageBoxButtons.OK ,MessageBoxIcon.Error );
					dt=this.GetBasicSalary ();

				}
			}
			else if (deptName!="")
			{
				comm="SELECT EmployeeID, [Name], BasicSalary FROM tblEmployee WHERE Deptid=(SELECT DeptID FROM  tblDepartment WHERE DeptName=@deptName)";
				SqlConnection conn=new SqlConnection (con);
				SqlCommand command =new SqlCommand (comm,conn);
				command.Parameters .Add ("@deptName",SqlDbType.NChar ,15);
				command.Parameters ["@deptName"].Value =deptName;
				dap=new SqlDataAdapter (command);


				DataSet ds=new DataSet ();
				dap.Fill (ds,"tblEmployee");
				dt=ds.Tables ["tblEmployee"];
			}
			else
			{
				MessageBox.Show ("請輸入查詢條件","錯誤",MessageBoxButtons.OK ,MessageBoxIcon.Error );
				dt=this.GetBasicSalary ();
			}
			return dt;
		}
	}
}