private void btnImportEmployee_Click(object sender, EventArgs e)
{
string txt = "";
try
{
StreamReader sr = new StreamReader(txbEmployeeListFile.Text);
txt = sr.ReadToEnd();
sr.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
string[] empList = txt.Split(new string[] {"\r\n"}, StringSplitOptions.RemoveEmptyEntries);
foreach (string empstr in empList)
{
string[] empinfo = empstr.Split(',');
MyEmployee emp = new MyEmployee() {EmployeeNumber = empinfo[0], Name = empinfo[1], Dept = empinfo[2]};
string shortpy = "";
emp.Pinyin = GetPinyin(emp.Name, out shortpy);
emp.ShortPinyin = shortpy;
AnnualPartySqlHelper.Instance.InitEmployee(emp);
}
MessageBox.Show("成功导入员工" + empList.Length + "个");
}
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);
}
// public bool UpdateShortPinyin(string number, string shortPy)
//{
// string sql = "update Employee set PinyinShort=@short where EmployeeNumber=@number";
// return SqlHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql,new SqlParameter("@short",shortPy), new SqlParameter("@number", number)) > 0;
//}
#endregion
#region DrawLotteryUsing
/// <summary>
/// 获得所有签到的员工，用于抽奖
/// </summary>
/// <returns></returns>
public Dictionary <string, MyEmployee> GetAllCheckInEmployee(bool withPhoto)
{
//string sql = "select * from " + (withPhoto ? "vEmployeeFull" : "Employee")+" where CheckIn=1 and Award=-1";
//var reader = SqlHelper.ExecuteReader(conn, System.Data.CommandType.Text, sql);
Dictionary <string, MyEmployee> employees = new Dictionary <string, MyEmployee>();
AnnualPartyEntities entities = new AnnualPartyEntities();
foreach (var reader in entities.Employee)
{
MyEmployee emp = new MyEmployee();
emp.LoadBasic(reader);
if (withPhoto)
{
var pho = entities.Photo.SingleOrDefault(e => e.EmployeeNumber == reader.EmployeeNumber);
emp.LoadWithPhoto(pho);
}
employees.Add(emp.EmployeeNumber, emp);
}
return(employees);
}
/// <summary>
/// 从HR系统中获取员工信息和照片信息
/// </summary>
/// <param name="conn"></param>
/// <param name="sql"></param>
/// <returns></returns>
public List <MyEmployee> GetAllEmployeeFromHrDB(string conn, string sql)
{
SqlDataReader reader = SqlHelper.ExecuteReader(conn, CommandType.Text, sql);
List <MyEmployee> empList = new List <MyEmployee>();
while (reader.Read())
{
MyEmployee e = new MyEmployee();
e.EmployeeNumber = (string)reader["EmployeeID"];
e.Name = (string)reader["ChineseName"];
e.Dept = (string)reader["Department"];
if (!Convert.IsDBNull(reader["alias"]))
{
e.Alias = (string)reader["alias"];
}
string py = "";
if (!Convert.IsDBNull(reader["lastname"]))
{
py += (string)reader["lastname"];
}
if (!Convert.IsDBNull(reader["firstname"]))
{
py += (string)reader["firstname"];
}
e.Pinyin = py;
if (!Convert.IsDBNull(reader["Photo"]))
{
byte[] bImg = (byte[])reader["Photo"];
e.Photo = GetImage(bImg);
}
else
{
e.Photo = null;
}
empList.Add(e);
}
return(empList);
}
/// <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>
/// <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>
/// 从HR系统中获取员工信息和照片信息
/// </summary>
/// <param name="conn"></param>
/// <param name="sql"></param>
/// <returns></returns>
public List<MyEmployee> GetAllEmployeeFromHrDB(string conn,string sql)
{
SqlDataReader reader = SqlHelper.ExecuteReader(conn, CommandType.Text, sql);
List<MyEmployee> empList = new List<MyEmployee>();
while (reader.Read())
{
MyEmployee e = new MyEmployee();
e.EmployeeNumber = (string)reader["EmployeeID"];
e.Name = (string) reader["ChineseName"];
e.Dept = (string) reader["Department"];
if (!Convert.IsDBNull(reader["alias"]))
{
e.Alias = (string) reader["alias"];
}
string py = "";
if (!Convert.IsDBNull(reader["lastname"]))
{
py += (string)reader["lastname"];
}
if (!Convert.IsDBNull(reader["firstname"]))
{
py += (string)reader["firstname"];
}
e.Pinyin = py;
if (!Convert.IsDBNull(reader["Photo"]))
{
byte[] bImg = (byte[])reader["Photo"];
e.Photo = GetImage(bImg);
}
else
{
e.Photo = null;
}
empList.Add(e);
}
return empList;
}
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;
}