using System;
using System.Data.SqlClient ;
using System.Data ;

namespace AdminGoods
{
	/// <summary>
	/// WorkerDAO 的摘要說明。
	/// </summary>
	public class WorkerDAO
	{
		public WorkerDAO()
		{
			//
			// TODO: 在此處添加構造函數邏輯
			//
		}

		public bool loginCheck(string username,string password)
		{
			
			bool login = false;
			Database db = new Database();
			try
			{
				
				System.Data.SqlClient.SqlConnection conn = db.openConn();

				System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
				cmd.CommandText = "select * from worker where username = '" + username + "' and password = '" + password + "' ";
				cmd.Connection = conn;

				System.Data.SqlClient.SqlDataReader rs = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

				while(rs.Read())
				{
					login =true;
				}
			}
			catch(Exception ex)
			{
				string msg = ex.Message ;
			}
			finally
			{
				db.closeConn ();

			}
			return login;

		}
		public Worker queryWorkerByUsername(string username)
		{

			Worker w = new Worker ();

			Database db = new Database ();

			System.Data .SqlClient .SqlConnection conn;
			System.Data .SqlClient .SqlCommand cmd;
			System.Data .SqlClient .SqlDataReader rs;
			string sql = "";
			try
			{
				conn = db.openConn ();
				cmd = new System.Data.SqlClient.SqlCommand ();
				sql = "select * from worker where username = '"+ username +"'";
				cmd.CommandText = sql;
				cmd.Connection = conn;

				rs = cmd.ExecuteReader (System.Data .CommandBehavior .CloseConnection );

				while(rs.Read ())
				{
					w.ID = Convert.ToInt32 ( rs["ID"].ToString ());
					w.username = rs["username"].ToString ();
					w.password = rs["password"].ToString ();
					w.realname = rs["realname"].ToString ();
					w.workage = Convert.ToInt32 ( rs["workage"].ToString ());
					w.workcode = rs["workcode"].ToString ();
					w.worktype = rs["worktype"].ToString ();
					w.address = rs["address"].ToString ();


				}
			}
			catch(Exception ex)
			{
				string msg = ex.Message ;
				w = null;
			}
			finally
			{
				db.closeConn ();
			}

			return w;
		}

		public Worker queryWorkerByID(int id)
		{

			Worker w = new Worker ();

			Database db = new Database ();

			System.Data .SqlClient .SqlConnection conn;
			System.Data .SqlClient .SqlCommand cmd;
			System.Data .SqlClient .SqlDataReader rs;
			string sql = "";
			try
			{
				conn = db.openConn ();
				cmd = new System.Data.SqlClient.SqlCommand ();
				sql = "select * from worker where ID =  "+ id +"  ";
				cmd.CommandText = sql;
				cmd.Connection = conn;

				rs = cmd.ExecuteReader (System.Data .CommandBehavior .CloseConnection );

				while(rs.Read ())
				{
					w.ID = Convert.ToInt32 ( rs["ID"].ToString ());
					w.username = rs["username"].ToString ();
					w.password = rs["password"].ToString ();
					w.realname = rs["realname"].ToString ();
					w.workage = Convert.ToInt32 ( rs["workage"].ToString ());
					w.workcode = rs["workcode"].ToString ();
					w.worktype = rs["worktype"].ToString ();
					w.address = rs["address"].ToString ();


				}
			}
			catch(Exception ex)
			{
				string sm = ex.Message ;
				w = null;
			}
			finally
			{
				db.closeConn ();
			}

			return w;
		}

		public DataTable listAllAdminWorker()
		{
			System.Data.SqlClient.SqlConnection conn;		

			SqlDataAdapter dp;
			string sql;
			DataSet ds = new DataSet();
			Database db = new Database();

			DataTable last = new DataTable();
			try
			{
				sql = "select id,username,realname ,workcode,workage,address from worker  where worktype = '1'";

				conn = db.openConn();
				
				dp = new System.Data.SqlClient.SqlDataAdapter(sql,conn);	 
 
				dp.Fill(ds,"worker");

				last = ds.Tables["worker"];

			}
			catch
			{
				last = null;
			}
			finally
			{
				db.closeConn();
			}
			return last;
		}

		public DataTable listAllAccountWorker()
		{
			System.Data.SqlClient.SqlConnection conn;		

			SqlDataAdapter dp;
			string sql;
			DataSet ds = new DataSet();
			Database db = new Database();

			DataTable last = new DataTable();
			try
			{
				sql = "select id,username,realname ,workcode,workage,address from worker  worker  where worktype = '2'";

				conn = db.openConn();
				
				dp = new System.Data.SqlClient.SqlDataAdapter(sql,conn);	 
 
				dp.Fill(ds,"worker");

				last = ds.Tables["worker"];

			}
			catch
			{
				last = null;
			}
			finally
			{
				db.closeConn();
			}
			return last;
		}

		public DataTable listAllWorkerWorker()
		{

			System.Data.SqlClient.SqlConnection conn;		

			SqlDataAdapter dp;
			string sql;
			DataSet ds = new DataSet();
			Database db = new Database();

			DataTable last = new DataTable();
			try
			{
				sql = "select id,username,realname ,workcode,workage,address from worker  worker  where worktype = '3'";

				conn = db.openConn();
				
				dp = new System.Data.SqlClient.SqlDataAdapter(sql,conn);	 
 
				dp.Fill(ds,"worker");

				last = ds.Tables["worker"];

			}
			catch
			{
				last = null;
			}
			finally
			{
				db.closeConn();
			}
			return last;
		}
		public System.Data .DataTable  listAllWorker()
		{
			 

			System.Data.SqlClient.SqlConnection conn;		

			SqlDataAdapter dp;
			string sql;
			DataSet ds = new DataSet();
			Database db = new Database();

			DataTable last = new DataTable();
			try
			{
				sql = "select id,username,realname ,workcode,workage,address from worker";

				conn = db.openConn();
				
				dp = new System.Data.SqlClient.SqlDataAdapter(sql,conn);	 
 
				dp.Fill(ds,"worker");

				last = ds.Tables["worker"];

			}
			catch
			{
				last = null;
			}
			finally
			{
				db.closeConn();
			}
			return last;
		}
	}
}