public Image GetEmployeePhoto(string employeeNumber)
 {
      AnnualPartyEntities entities=new AnnualPartyEntities();
     var photo = entities.Photo.SingleOrDefault(e => e.EmployeeNumber == employeeNumber);
     if (photo != null)
     {
         byte[] bImg = photo.PhotoData;
          return AnnualPartySqlHelper.GetImage(bImg);
     }
      else
      {
          return GetNoPhotoImage();
      }
     // string sql = "select Photo from Photo where EmployeeNumber=@en";
     //var reader = SqlHelper.ExecuteReader(conn, System.Data.CommandType.Text, sql, new SqlParameter("@en",employeeNumber));
     //try
     //{
     //    if (reader.Read())
     //    {
     //        if (!Convert.IsDBNull(reader["Photo"]))
     //        {
     //            byte[] bImg = (byte[])reader["Photo"];
     //            return AnnualPartySqlHelper.GetImage(bImg);
     //        }
     //        else
     //        {
     //            return GetNoPhotoImage();
     //        }
     //    }
     //    return GetNoPhotoImage();
     //}
     //finally
     //{
     //    reader.Close();
     //}
 }
   /// <summary>
   /// 获得所有的员工,不管是否签到的
   /// </summary>
   /// <returns></returns>
   public Dictionary<string,MyEmployee> GetAllEmployee(bool withPhoto)
   {
       //string sql = "select * from " + (withPhoto ? "vEmployeeFull" : "Employee");
       //var reader = SqlHelper.ExecuteReader(conn, System.Data.CommandType.Text, sql);
       AnnualPartyEntities entities=new AnnualPartyEntities();
       Dictionary<string, MyEmployee> employees = new Dictionary<string, MyEmployee>();
       foreach (var reader in entities.Employee)
       {
           
           MyEmployee emp = new MyEmployee();
           if (withPhoto)
           {
            var p=   entities.Photo.SingleOrDefault(e => e.EmployeeNumber == reader.EmployeeNumber);
               emp.LoadWithPhoto(p);
           }
           else
           {
               emp.LoadBasic(reader);
           }
           employees.Add(emp.EmployeeNumber, emp);
       }
 
       return employees;
   }
 /// <summary>
 /// 插入一个员工信息
 /// </summary>
 /// <param name="e"></param>
 public void InitEmployee(MyEmployee e)
 {
      AnnualPartyEntities entities = new AnnualPartyEntities();
      Employee employee=new Employee();
     employee.EmployeeNumber = e.EmployeeNumber;
     employee.Dept = e.Dept;
     employee.Name = e.Name;
     employee.PinyinFull = e.Pinyin;
     employee.PinyinShort = e.ShortPinyin;
     employee.Alias = e.Alias;
     entities.Employee.Add(employee);
     entities.SaveChanges();
     // string sql =
     //    "insert into Employee(EmployeeNumber,Name,Dept,Alias,PinyinFull,PinyinShort) values(@num,@name,@dept,@alias,@pinyin,@pinyinshort)";
     //SqlParameter p1 = new SqlParameter("@num", e.EmployeeNumber);
     //SqlParameter p2 = new SqlParameter("@name", e.Name);
     //SqlParameter p3 = new SqlParameter("@dept", e.Dept);
     //SqlParameter p4 = new SqlParameter("@alias", e.Alias);
     //SqlParameter p5 = new SqlParameter("@pinyin", e.Pinyin);
     //SqlParameter p6 = new SqlParameter("@pinyinshort", e.ShortPinyin);
     //SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, p1, p2, p3, p4, p5, p6);
 }
 /// <summary>
 /// 插入一个员工照片
 /// </summary>
 /// <param name="number"></param>
 /// <param name="img"></param>
 public void InitPhoto(string number, Image img)
 {
      AnnualPartyEntities entities = new AnnualPartyEntities();
      Photo p=new Photo() {EmployeeNumber = number,PhotoData = ImageToByte(img) };
     entities.Photo.Add(p);
     entities.SaveChanges();
     //string sql = "insert into Photo values(@num,@img)";
     //SqlParameter p1 = new SqlParameter("@num", number);
     //SqlParameter p2 = new SqlParameter("@img", ImageToByte(img));
     //SqlHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql,p1,p2);
 }
 /// <summary>
 /// 获得照片数
 /// </summary>
 /// <returns></returns>
 public int GetPhotoCount()
 {
      AnnualPartyEntities entities = new AnnualPartyEntities();
    return entities.Photo.Count();
     // string sql = "select count(1) from Photo";
     //return (int)SqlHelper.ExecuteScalar(conn, System.Data.CommandType.Text, sql);
 }
 /// <summary>
 /// 获得所有员工人数
 /// </summary>
 /// <returns></returns>
 public int GetTotalUserCount()
 {
      AnnualPartyEntities entities = new AnnualPartyEntities();
      var count = entities.Employee.Count();
      return count;
      //string sql = "select count(1) from Employee";
      //return (int)SqlHelper.ExecuteScalar(conn, System.Data.CommandType.Text, sql);
  }
 /// <summary>
 /// 删除所有员工信息和照片
 /// </summary>
 public void DeleteTableData()
 {
      //string sql = "delete from Employee";
      AnnualPartyEntities entities =new AnnualPartyEntities();
     foreach (var employee in entities.Employee)
     {
         entities.Employee.Remove(employee);
     }
     
     //SqlHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql);
     //sql = "delete from Photo";
     //SqlHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql);
 }
       /// <summary>
       /// 将中奖结果写入数据库
       /// </summary>
       /// <param name="number"></param>
       /// <param name="award"></param>
       /// <returns></returns>
       public bool UpdateAward(string number, int award, string awardName)
       {
           AnnualPartyEntities entities = new AnnualPartyEntities();
           var emp = entities.Employee.SingleOrDefault(e => e.EmployeeNumber == number);
           emp.Award = award;
           emp.AwardTime = DateTime.Now;

           entities.SaveChanges();
           return true;
           // string sql = "update Employee set Award=@award,AwardTime=getdate(),AwardName=@aindex where EmployeeNumber=@number";
           //return SqlHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql, 
           //    new SqlParameter("@award", award), 
           //    new SqlParameter("@number", number),
           //    new SqlParameter("@aindex", awardName)) > 0;
       }
       /// <summary>
       /// 清空中奖结果
       /// </summary>
       /// <returns></returns>
       public bool ClearAward()
       {
            AnnualPartyEntities entities = new AnnualPartyEntities();

            foreach (var employee in entities.Employee)
            {
                employee.Award = -1;
                employee.AwardTime = null;
               
            }
            entities.SaveChanges();
            return true;
            //string sql = "update Employee set Award=-1,AwardTime=null,AwardName=''";
            //return SqlHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql) > 0;
        }
       /// <summary>
       /// 清空签到结果
       /// </summary>
       /// <returns></returns>
       public bool ClearCheckIn()
        {
            AnnualPartyEntities entities = new AnnualPartyEntities();

           foreach (var employee in entities.Employee)
           {
               employee.CheckIn = false;
               employee.CheckinTime = null;
           }
           entities.SaveChanges();
           return true;
            //string sql = "update Employee set checkin=0,CheckinTime=null";
            //return SqlHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql) > 0;
        }
  /// <summary>
  /// 取消签到
  /// </summary>
  /// <param name="employeeNumber"></param>
  /// <returns></returns>
  public bool CheckOut(string employeeNumber)
 {
      AnnualPartyEntities entities = new AnnualPartyEntities();
      var emp = entities.Employee.SingleOrDefault(e => e.EmployeeNumber == employeeNumber);
      emp.CheckIn = false;
      emp.CheckinTime = null;
      entities.SaveChanges();
      return true;
     // string sql = "update Employee set checkin=0 where EmployeeNumber=@number";
     //return SqlHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql, new SqlParameter("@number", employeeNumber)) > 0;
 }
  public bool CheckInAll()
  {
      AnnualPartyEntities entities = new AnnualPartyEntities();
      foreach (var emp in entities.Employee)
      {
 
      emp.CheckIn = true;
      emp.CheckinTime = DateTime.Now;
      }
      entities.SaveChanges();
      return true;
      // string sql = "update Employee set checkin=1,CheckinTime=GETDATE() where EmployeeNumber=@number";
      //return SqlHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql, new SqlParameter("@number", employeeNumber)) > 0;
  }
 /// <summary>
 /// 获得当前已签到的人数
 /// </summary>
 /// <returns></returns>
 public int GetCheckInCount()
 {
      AnnualPartyEntities entities=new AnnualPartyEntities();
     return entities.Employee.Where(e => e.CheckIn).Count();
 
 }
  public List<MyEmployee> SearchEmployee(string word)
  {
 AnnualPartyEntities entities=new AnnualPartyEntities();
     var result= entities.Employee.Where(e=>e.Alias==word||e.PinyinShort==word||e.PinyinFull==word);
      List<MyEmployee> employees = new List<MyEmployee>();
      foreach (var employeeResult in result)
      {
          var e = new MyEmployee();
           e.LoadBasic(employeeResult);
           employees.Add(e);
       }
    
      return employees;
  }