using System;
using System.Collections.Generic;
using System.Text;
using Common;
using System.Data;
using System.Data.SqlClient;



namespace DA
{
   public class DBEmployees: IDisposable 
    {

       string sql;
       SqlHelpers sqlh;
       DataSet ds;
       SqlDataReader dr;
       public DBEmployees()
       {
           sqlh = new SqlHelpers();
       }
       public DBEmployees(SqlHelpers sh)
       {
           sqlh = sh;
       }
       public void Dispose()
       {
           sqlh.Dispose();
       }

       /// <summary>
       /// DB  查询所有服务生信息的方法
       /// </summary>
       /// <returns></returns>
       public List<CEmployees> DBEmployeesQuery()
       {
           List<CEmployees> le = new List<CEmployees>();
           sql = "select * from Employees where etId=" + 5 + "";
           dr = sqlh.RQuery(sql,null , CommandType.Text);
           while (dr.Read())
           {
               CEmployees cel = new CEmployees();
               cel.CeId1 = Convert.ToInt32(dr["eid"]);
               cel.CeName1 = dr["eName"].ToString();
               le.Add(cel);

           }
           dr.Close();
           return le;
       }
        /// <summary>
        /// DB  查询所有收银员的信息
        /// </summary>
        /// <returns></returns>
       public List<CEmployees> DBEmployeeSYYQuery()
             {
           List<CEmployees> le = new List<CEmployees>();
           sql = "select * from Employees where etId=" + 3 + "";
           dr = sqlh.RQuery(sql,null , CommandType.Text);
           while (dr.Read())
           {
               CEmployees cel = new CEmployees();
               cel.CeId1 = Convert.ToInt32(dr["eid"]);
               cel.CeName1 = dr["eName"].ToString();
               le.Add(cel);

           }
           dr.Close();
           return le;
       }

       /// <summary>
       /// DB  查询所有经理的信息
       /// </summary>
       /// <returns></returns>
       public List<CEmployees> DBEmployeeJLQuery()
       {
           List<CEmployees> le = new List<CEmployees>();
           sql = "select * from Employees where etId=" + 1 + "";
           dr = sqlh.RQuery(sql, null, CommandType.Text);
           while (dr.Read())
           {
               CEmployees cel = new CEmployees();
               cel.CeId1 = Convert.ToInt32(dr["eid"]);
               cel.CeName1 = dr["eName"].ToString();
               le.Add(cel);

           }
           dr.Close();
           return le;
       }


       /// <summary>
       /// DB  查询所有采购员的信息
       /// </summary>
       /// <returns></returns>
       public List<CEmployees> DBEmployeesCGYQuery()
       {
           List<CEmployees> le = new List<CEmployees>();
           sql = "select * from Employees where etId=" + 6 + "";
           dr = sqlh.RQuery(sql, null, CommandType.Text);
           while (dr.Read())
           {
               CEmployees cel = new CEmployees();
               cel.CeId1 = Convert.ToInt32(dr["eid"]);
               cel.CeName1 = dr["eName"].ToString();
               le.Add(cel);

           }
           dr.Close();
           return le;
       }

       /// <summary>
       /// DB  查询所有员工信息的方法
       /// </summary>
       /// <returns></returns>
       public DataSet DBEmployeesQuerySY()
       {
           sql = "select e.eId,e.eName,e.eSpell,e.eSex,et.etName,e.eLinkphone,e.eIDCard,e.eWorkDate,e.eImage,e.eRemark from Employees e inner join EmployeeType et on e.etId=et.etId";
           ds = sqlh.ExcuteSelect (sql, "EET", null);
           return ds;
       }

       /// <summary>
       /// DB   根据员工姓名查询员工信息的方法
       /// </summary>
       /// <param name="eName"></param>
       /// <returns></returns>
       public DataSet DBEmployeesNameQuery(string eName)
       {
           List<SqlParameter> pars = new List<SqlParameter>();
           pars.Add(new SqlParameter("@eName", SqlDbType.VarChar, 20));
           pars[0].Value = eName;
           sql = "select e.eId,e.eName,e.eSpell,e.eSex,et.etName,e.eLinkphone,e.eIDCard,e.eWorkDate,e.eImage,e.eRemark from Employees e inner join EmployeeType et on e.etId=et.etId where e.eSpell like @eName+'%'";
           ds = sqlh.ExcuteSelect (sql, "EETN", pars);
           return ds;
       }

       /// <summary>
       /// DB  根据员工类型查询员工信息的方法
       /// </summary>
       /// <param name="eName"></param>
       /// <returns></returns>
       public DataSet DBEmployeesTypeName(string etName)
       {
           List<SqlParameter> pars = new List<SqlParameter>();
           pars.Add(new SqlParameter("@etName", SqlDbType.VarChar, 10));
           pars[0].Value = etName;
           sql = "select e.eId,e.eName,e.eSpell,e.eSex,et.etName,e.eLinkphone,e.eIDCard,e.eWorkDate,e.eImage,e.eRemark from Employees e inner join EmployeeType et on e.etId=et.etId where et.etName=@etName";
           ds = sqlh.ExcuteSelect(sql, "EETN", pars);
           return ds;
       }

       /// <summary>
       ///DB   插入员工信息的方法
       /// </summary>
       /// <param name="e"></param>
       public void DBEmployeesAdd(CEmployees e)
       {
           List<SqlParameter> pars = new List<SqlParameter>();
           pars.Add(new SqlParameter("@eName", SqlDbType.VarChar, 20));
           pars[0].Value = e.CeName1 ;
           pars.Add(new SqlParameter("@eSpell", SqlDbType.VarChar, 20));
           pars[1].Value = e.ESpell ;
           pars.Add(new SqlParameter("@eSex", SqlDbType.Char, 2));
           pars[2].Value = e.CeSex1 ;
           pars.Add(new SqlParameter("@etId", SqlDbType.Int));
           pars[3].Value = e.CetId1 ;
           pars.Add(new SqlParameter("@eLinkphone", SqlDbType.VarChar, 30));
           pars[4].Value = e.CeLinkphone1 ;
           pars.Add(new SqlParameter("@eIDCard", SqlDbType.VarChar, 18));
           pars[5].Value = e.CeIDCard1 ;
           pars.Add(new SqlParameter("@eWorkDate", SqlDbType.DateTime));
           pars[6].Value = e.CeWorkDate1 ;
           pars.Add(new SqlParameter("@eImage", SqlDbType.Image));
           pars[7].Value = e.CeImage1 ;
           pars.Add(new SqlParameter("@eRemark", SqlDbType.VarChar, 50));
           pars[8].Value = e.CeRemark1;
           sql = "insert into Employees values(@eName,@eSpell,@eSex,@etId,@eLinkphone,@eIDCard,@eWorkDate,@eImage,@eRemark)";
           sqlh.NonQuery(sql, pars, CommandType.Text);
       }

       /// <summary>
       ///DB  修改员工信息的方法
       /// </summary>
       /// <param name="e"></param>
       public void DBEmployeesUpdate(CEmployees e)
       {
           List<SqlParameter> pars = new List<SqlParameter>();
           pars.Add(new SqlParameter("@eId", SqlDbType.Int));
           pars[0].Value = e.CeId1 ;
           pars.Add(new SqlParameter("@eName", SqlDbType.VarChar, 20));
           pars[1].Value = e.CeName1 ;
           pars.Add(new SqlParameter("@eSpell", SqlDbType.VarChar, 20));
           pars[2].Value = e.CeName1 ;
           pars.Add(new SqlParameter("@eSex", SqlDbType.Char, 2));
           pars[3].Value = e.CeSex1 ;
           pars.Add(new SqlParameter("@etId", SqlDbType.Int));
           pars[4].Value = e.CetId1 ;
           pars.Add(new SqlParameter("@eLinkphone", SqlDbType.VarChar, 30));
           pars[5].Value = e.CeLinkphone1 ;
           pars.Add(new SqlParameter("@eIDCard", SqlDbType.VarChar, 18));
           pars[6].Value = e.CeIDCard1 ;
           pars.Add(new SqlParameter("@eWorkDate", SqlDbType.DateTime));
           pars[7].Value = e.CeWorkDate1 ;
           pars.Add(new SqlParameter("@eImage", SqlDbType.Image));
           pars[8].Value = e.CeImage1 ;
           pars.Add(new SqlParameter("@eRemark", SqlDbType.VarChar, 50));
           pars[9].Value = e.CeRemark1 ;
           sql = "update Employees set eName=@eName,eSpell=@eSpell,eSex=@eSex,etId=@etId,eLinkphone=@eLinkphone,eIDCard=@eIDCard,eWorkDate=@eWorkDate,eImage=@eImage,eRemark=@eRemark where eId=@eId";
           sqlh.NonQuery(sql, pars, CommandType.Text);
       }

       /// <summary>
       ///DB   删除员工信息的方法
       /// </summary>
       /// <param name="eId">员工编号</param>
       public void DBEmployeesDelete(int eId)
       {
           List<SqlParameter> pars = new List<SqlParameter>();
           pars.Add(new SqlParameter("@eId", SqlDbType.Int));
           pars[0].Value = eId;
           sql = "delete from Employees where eId=@eId";
           sqlh.NonQuery(sql, pars, CommandType.Text);
       }
       /// <summary>
       /// 根据员工编号查询员工信息的方法
       /// </summary>
       /// <param name="eId"></param>
       /// <returns></returns>
       public DataSet IDEmployeesIdQ(int eId)
       {
           List<SqlParameter> pars = new List<SqlParameter>();
           pars.Add(new SqlParameter("@eId", SqlDbType.Int));
           pars[0].Value = eId;
           sql = "select e.eId,e.eName,e.eSpell,e.eSex,et.etName,e.eLinkphone,e.eIDCard,e.eWorkDate,e.eImage,e.eRemark from Employees e inner join EmployeeType et on e.etId=et.etId where e.eId=@eId";
           ds = sqlh.ExcuteSelect (sql, "EET", pars);
           return ds;

       }
    }
}