using System;
using System.Collections.Generic;
using System.Linq;
using Ylbtech.Model;
using Microsoft.DBUtility;
using System.Text;
using System.Data.SqlClient;
using Ylbtech.Common;
namespace Ylbtech.DAL
{
/// <summary>
///Employee 的摘要说明
///desc:员工操作类
///author:yuanbo
///date:2013-6-12
/// </summary>
public class Employee
{
/// <summary>
/// ylb:查询员工信息
/// </summary>
/// <param name="dalEmployee"></param>
/// <returns></returns>
public static IList<EmployeeInfo> GetAllSearch(EmployeeInfo dalEmployee)
{
IList<EmployeeInfo> dals = new List<EmployeeInfo>();
string sql = "select employeeId,[id],username,sex,cardNo"
+ ",hireDate,e.departmentId,state,ToRegularDate,d.departmentName from Employee e inner join Department d on e.departmentId=d.departmentId";
StringBuilder sbSql = new StringBuilder();
sbSql.Append(sql);
IList<SqlParameter> sqlParamI = new List<SqlParameter>();
if (dalEmployee.EmployeeId != 0) /*单一查询条件*/
{
sqlParamI.Add(new SqlParameter("@employeeId", dalEmployee.EmployeeId));
sbSql.Append(" where employeeId=@employeeId");
}
else if (dalEmployee.Id.Length > 0) /*单一查询条件*/
{
sqlParamI.Add(new SqlParameter("@id", dalEmployee.Id));
sbSql.Append(" where [id]=@id");
}
else /*复合条件查询*/
{
sbSql.Append(" where 1=1");
if (dalEmployee.Username.Length > 0)
{
sbSql.Append(" and username=@username");
sqlParamI.Add(new SqlParameter("@username", dalEmployee.Username));
}
if (dalEmployee.Sex != "-1")
{
sbSql.Append(" and sex=@sex");
sqlParamI.Add(new SqlParameter("@sex", dalEmployee.Sex));
}
if (dalEmployee.CardNo.Length > 0)
{
sbSql.Append(" and cardNo=@cardNo");
sqlParamI.Add(new SqlParameter("@cardNo", dalEmployee.CardNo));
}
DateTime hireDate;
if (DateTime.TryParse(dalEmployee.HireDate, out hireDate))
{
sbSql.Append(" and hiredate=@hiredate");
sqlParamI.Add(new SqlParameter("@hiredate", hireDate));
}
if (dalEmployee.DepartmentId != -1)
{
sbSql.Append(" and e.departmentId=@departmentId");
sqlParamI.Add(new SqlParameter("@departmentId", dalEmployee.DepartmentId));
}
if (dalEmployee.State != "-1")
{
sbSql.Append(" and state=@state");
sqlParamI.Add(new SqlParameter("@state", dalEmployee.State));
}
DateTime toRegularDate;
if (DateTime.TryParse(dalEmployee.ToRegularDate, out toRegularDate))
{
sbSql.Append(" and toRegularDate=@toRegularDate");
sqlParamI.Add(new SqlParameter("@toRegularDate", dalEmployee.ToRegularDate));
}
}
sbSql.Append(" order by employeeId desc");
SqlParameter[] sqlParam = new SqlParameter[sqlParamI.Count];
//A:方式一
//for (int i = 0; i < sqlParamI.Count; i++)
//{
// sqlParam[i] = sqlParamI[i];
//}
//A:方式二
sqlParam = sqlParamI.ToArray();
using (SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.ConnStr_WelfareSystem, System.Data.CommandType.Text, sbSql.ToString(), sqlParam))
{
while (sdr.Read())
{
EmployeeInfo dal = new EmployeeInfo()
{
EmployeeId = sdr.GetInt32(0),
Id = sdr[1] == System.DBNull.Value ? "" : sdr.GetString(1),
Username = sdr[2] == System.DBNull.Value ? "" : sdr.GetString(2),
Sex = sdr[3] == System.DBNull.Value ? "" : sdr.GetString(3),
CardNo = sdr[4] == System.DBNull.Value ? "" : sdr.GetString(4),
HireDate = DateTimeFun.DateTimeConvertToyyyy_MM_dd(sdr[5] == System.DBNull.Value ? "" : sdr.GetString(5)),
DepartmentId = sdr.GetInt32(6),
State = sdr[7] == System.DBNull.Value ? "" : sdr.GetString(7),
ToRegularDate = DateTimeFun.DateTimeConvertToyyyy_MM_dd(sdr[8] == System.DBNull.Value ? "" : sdr.GetString(8)),
DepartmentName = sdr[9] == System.DBNull.Value ? "" : sdr.GetString(9)
};
dals.Add(dal);
}
}
return dals;
}
public Employee()
{
//
//TODO: 在此处添加构造函数逻辑
//
}
}
}