/// <summary>
/// 根据部门获取所有员工信息
/// </summary>
/// <param name="CompanyId">公司编号</param>
/// <param name="DepartmentId">部门编号</param>
/// <returns></returns>
private IList <EyouSoft.Model.AdminCenterStructure.PersonnelInfo> GetPersonnelList(int CompanyId, int DepartmentId, EyouSoft.Model.AdminCenterStructure.SearchInfo SearchInfo)
{
DateTime SearchAddDate = new DateTime(SearchInfo.Year.Value, SearchInfo.Month.Value, 1);
IList <EyouSoft.Model.AdminCenterStructure.PersonnelInfo> ResultList = null;
string StrSql = string.Format("SELECT ID,UserName,ArchiveNo,(SELECT StaffNo,WorkStatus,AddDate FROM tbl_AttendanceInfo WHERE DATEDIFF(month,AddDate,'{0}')=0 FOR XML RAW,ROOT('ROOT')) AS AttendanceInfoXML FROM tbl_PersonnelInfo WHERE CompanyId={1} AND EXISTS(SELECT 1 FROM dbo.fn_split(DepartmentId,',') WHERE [VALUE]='{2}')", SearchAddDate.ToShortDateString(), CompanyId, DepartmentId);
if (!string.IsNullOrEmpty(SearchInfo.ArchiveNo))
{
StrSql += string.Format(" AND ArchiveNo LIKE '%{0}%'", SearchInfo.ArchiveNo);
}
if (!string.IsNullOrEmpty(SearchInfo.StaffName))
{
StrSql += string.Format(" AND UserName LIKE '%{0}%'", SearchInfo.StaffName);
}
DbCommand dc = this._db.GetSqlStringCommand(StrSql);
using (IDataReader dr = DbHelper.ExecuteReader(dc, this._db))
{
ResultList = new List <EyouSoft.Model.AdminCenterStructure.PersonnelInfo>();
while (dr.Read())
{
EyouSoft.Model.AdminCenterStructure.PersonnelInfo model = new EyouSoft.Model.AdminCenterStructure.PersonnelInfo()
{
UserName = dr.IsDBNull(dr.GetOrdinal("UserName")) ? "" : dr.GetString(dr.GetOrdinal("UserName")),
Id = dr.IsDBNull(dr.GetOrdinal("Id")) ? 0 : dr.GetInt32(dr.GetOrdinal("Id")),
ArchiveNo = dr.IsDBNull(dr.GetOrdinal("ArchiveNo")) ? "" : dr.GetString(dr.GetOrdinal("ArchiveNo")),
AttendanceList = this.GetAttendanceList(dr["AttendanceInfoXML"].ToString())
};
ResultList.Add(model);
model = null;
}
};
return(ResultList);
}
/// <summary>
/// 给职员个人档案信息实体赋值
/// </summary>
/// <param name="DataModel">Linq端的实体</param>
/// <param name="model">职员个人档案信息实体</param>
private void InputModelValue(EyouSoft.Data.PersonnelInfo DataModel, EyouSoft.Model.AdminCenterStructure.PersonnelInfo model)
{
DataModel.ArchiveNo = model.ArchiveNo;
DataModel.BirthDate = model.BirthDate;
DataModel.Birthplace = model.Birthplace;
DataModel.CardId = model.CardId;
DataModel.CompanyId = model.CompanyId;
DataModel.ContactAddress = model.ContactAddress;
DataModel.ContactMobile = model.ContactMobile;
DataModel.ContactSex = (int)model.ContactSex;
DataModel.ContactTel = model.ContactTel;
DataModel.DepartmentId = model.DepartmentId;
DataModel.DutyId = model.DutyId;
DataModel.Email = model.Email;
DataModel.EntryDate = model.EntryDate;
DataModel.IsLeave = model.IsLeave ? 1 : 0;
DataModel.IsMarried = model.IsMarried ? 1 : 0;
DataModel.IssueTime = System.DateTime.Now;
DataModel.LeaveDate = model.LeaveDate;
DataModel.Msn = model.MSN;
DataModel.National = model.National;
DataModel.OperatorId = model.OperatorId;
DataModel.PersonalType = (int)model.PersonalType;
DataModel.PhotoPath = model.PhotoPath;
DataModel.Politic = model.Politic;
DataModel.Qq = model.QQ;
DataModel.Remark = model.Remark;
DataModel.ServiceYear = model.ServiceYear;
DataModel.UserName = model.UserName;
}
/// <summary>
/// 增加
/// </summary>
/// <param name="model">职工档案信息实体</param>
/// <returns></returns>
public bool Add(EyouSoft.Model.AdminCenterStructure.PersonnelInfo model)
{
bool IsTrue = false;
EyouSoft.Data.PersonnelInfo DataModel = new EyouSoft.Data.PersonnelInfo();
InputModelValue(DataModel, model);
DataModel.IssueTime = DateTime.Now;
#region 学历处理
if (model.SchoolList != null && model.SchoolList.Count > 0)
{
((List <EyouSoft.Model.AdminCenterStructure.SchoolInfo>)model.SchoolList).ForEach(item =>
{
EyouSoft.Data.SchoolInfo DataSchoolModel = new EyouSoft.Data.SchoolInfo();
DataSchoolModel.Degree = Convert.ToByte((int)item.Degree);
DataSchoolModel.EndDate = item.EndDate;
DataSchoolModel.PersonId = model.Id;
DataSchoolModel.Professional = item.Professional;
DataSchoolModel.Remark = item.Remark;
DataSchoolModel.SchoolName = item.Remark;
DataSchoolModel.StartDate = item.StartDate;
DataSchoolModel.StudyStatus = (byte)(item.StudyStatus ? 1 : 0);
DataModel.PersonSchoolInfoList.Add(DataSchoolModel);
DataSchoolModel = null;
});
}
#endregion 学历处理
#region 履历处理
if (model.HistoryList != null && model.HistoryList.Count > 0)
{
((List <EyouSoft.Model.AdminCenterStructure.PersonalHistory>)model.HistoryList).ForEach(item =>
{
EyouSoft.Data.PersonalHistory DataHistoryModel = new EyouSoft.Data.PersonalHistory();
DataHistoryModel.EndDate = item.EndDate;
DataHistoryModel.PersonId = model.Id;
DataHistoryModel.Remark = item.Remark;
DataHistoryModel.StartDate = item.StartDate;
DataHistoryModel.TakeUp = item.TakeUp;
DataHistoryModel.WorkPlace = item.WorkPlace;
DataHistoryModel.WorkUnit = item.WorkUnit;
DataModel.PersonPersonalHistoryList.Add(DataHistoryModel);
DataHistoryModel = null;
});
}
#endregion 学历处理
dcDal.PersonnelInfo.InsertOnSubmit(DataModel);
dcDal.SubmitChanges();
if (dcDal.ChangeConflicts.Count == 0)
{
IsTrue = true;
}
DataModel = null;
return(IsTrue);
}
/// <summary>
/// 获取通讯录信息
/// </summary>
/// <param name="PageSize"></param>
/// <param name="PageIndex"></param>
/// <param name="ReCordCount"></param>
/// <param name="CompanyId"></param>
/// <param name="UserName">姓名</param>
/// <param name="DepartmentId">部门编号</param>
/// <returns></returns>
public IList <EyouSoft.Model.AdminCenterStructure.PersonnelInfo> GetList(int PageSize, int PageIndex, ref int ReCordCount, int CompanyId, string UserName, int?DepartmentId)
{
IList <EyouSoft.Model.AdminCenterStructure.PersonnelInfo> ResultList = null;
#region SQL处理
string tableName = "tbl_PersonnelInfo";
string identityColumnName = "Id";
StringBuilder fields = new StringBuilder();
fields.Append("[Id],[UserName],[ContactTel],ContactSex,[ContactMobile],[Email],[QQ],[MSN],");
fields.AppendFormat("(SELECT [Id],[DepartName] FROM tbl_CompanyDepartment a WHERE a.[id] IN(select [value] from dbo.fn_split([tbl_PersonnelInfo].[DepartmentId],',')) AND [CompanyId]={0} FOR XML Raw,Root('Department'))AS [DepartmentXML] ", CompanyId);
StringBuilder query = new StringBuilder();
query.AppendFormat("[CompanyId]={0} AND IsLeave=0", CompanyId);
if (!string.IsNullOrEmpty(UserName))
{
query.AppendFormat(" AND UserName like'%{0}%'", UserName);
}
if (DepartmentId.HasValue && DepartmentId > 0)
{
query.AppendFormat(" AND EXISTS(SELECT 1 FROM dbo.fn_split(DepartmentId,',') WHERE [VALUE]='{0}')", DepartmentId);
}
string orderByString = " IssueTime DESC";
#endregion
using (IDataReader dr = DbHelper.ExecuteReader(_db, PageSize, PageIndex, ref ReCordCount, tableName, identityColumnName, fields.ToString(), query.ToString(), orderByString))
{
ResultList = new List <EyouSoft.Model.AdminCenterStructure.PersonnelInfo>();
while (dr.Read())
{
EyouSoft.Model.AdminCenterStructure.PersonnelInfo model = new EyouSoft.Model.AdminCenterStructure.PersonnelInfo()
{
Id = dr.GetInt32(dr.GetOrdinal("Id")),
UserName = dr.IsDBNull(dr.GetOrdinal("UserName")) ? "" : dr.GetString(dr.GetOrdinal("UserName")),
ContactSex = (EyouSoft.Model.EnumType.CompanyStructure.Sex)Enum.Parse(typeof(EyouSoft.Model.EnumType.CompanyStructure.Sex), dr.GetInt32(dr.GetOrdinal("ContactSex")).ToString()),
DepartmentList = this.GetDepartmentList(dr["DepartmentXML"].ToString()),
ContactTel = dr.IsDBNull(dr.GetOrdinal("ContactTel")) ? "" : dr.GetString(dr.GetOrdinal("ContactTel")),
ContactMobile = dr.IsDBNull(dr.GetOrdinal("ContactMobile")) ? "" : dr.GetString(dr.GetOrdinal("ContactMobile")),
Email = dr.IsDBNull(dr.GetOrdinal("Email")) ? "" : dr.GetString(dr.GetOrdinal("Email")),
QQ = dr.IsDBNull(dr.GetOrdinal("QQ")) ? "" : dr.GetString(dr.GetOrdinal("QQ")),
MSN = dr.IsDBNull(dr.GetOrdinal("MSN")) ? "" : dr.GetString(dr.GetOrdinal("MSN"))
};
ResultList.Add(model);
model = null;
}
};
return(ResultList);
}
/// <summary>
/// 按年份，月份获取员工考勤列表
/// </summary>
/// <param name="CompanyId">公司编号</param>
/// <param name="SearchInfo">考勤查询试题</param>
/// <returns></returns>
public IList <EyouSoft.Model.AdminCenterStructure.PersonnelInfo> GetList(int CompanyId, EyouSoft.Model.AdminCenterStructure.SearchInfo SearchInfo)
{
IList <EyouSoft.Model.AdminCenterStructure.PersonnelInfo> ResultList = new List <EyouSoft.Model.AdminCenterStructure.PersonnelInfo>();
DateTime SearchAddDate = new DateTime(SearchInfo.Year.Value, SearchInfo.Month.Value, 1);
StringBuilder StrSql = new StringBuilder();
StrSql.Append("SELECT Id,UserName,ArchiveNo,");
StrSql.Append(" (SELECT Id,DepartName FROM tbl_CompanyDepartment WHERE ID IN(SELECT [VALUE] from dbo.fn_split(a.DepartmentId,',')) FOR XML RAW,ROOT('ROOT'))AS DepartmentXML,");
StrSql.AppendFormat(" (SELECT StaffNo,WorkStatus,AddDate FROM tbl_AttendanceInfo WHERE DATEDIFF(month,AddDate,'{0}')=0 FOR XML RAW,ROOT('ROOT')) AS AttendanceInfoXML", SearchAddDate.ToShortDateString());
StrSql.AppendFormat(" FROM tbl_PersonnelInfo a WHERE CompanyId={0} ", CompanyId);
if (SearchInfo.DepartMentId.HasValue && SearchInfo.DepartMentId.Value > 0)
{
StrSql.AppendFormat(" AND EXISTS(SELECT 1 FROM dbo.fn_split(DepartmentId,',') WHERE [VALUE]='{0}')", SearchInfo.DepartMentId);
}
if (!string.IsNullOrEmpty(SearchInfo.ArchiveNo))
{
StrSql.AppendFormat(" AND ArchiveNo LIKE '%{0}%'", SearchInfo.ArchiveNo);
}
if (!string.IsNullOrEmpty(SearchInfo.StaffName))
{
StrSql.AppendFormat(" AND UserName LIKE '%{0}%'", SearchInfo.StaffName);
}
StrSql.Append(" ORDER BY IssueTime DESC");
DbCommand dc = this._db.GetSqlStringCommand(StrSql.ToString());
using (IDataReader dr = DbHelper.ExecuteReader(dc, this._db))
{
ResultList = new List <EyouSoft.Model.AdminCenterStructure.PersonnelInfo>();
while (dr.Read())
{
EyouSoft.Model.AdminCenterStructure.PersonnelInfo model = new EyouSoft.Model.AdminCenterStructure.PersonnelInfo()
{
UserName = dr.IsDBNull(dr.GetOrdinal("UserName")) ? "" : dr.GetString(dr.GetOrdinal("UserName")),
Id = dr.IsDBNull(dr.GetOrdinal("Id")) ? 0 : dr.GetInt32(dr.GetOrdinal("Id")),
ArchiveNo = dr.IsDBNull(dr.GetOrdinal("ArchiveNo")) ? "" : dr.GetString(dr.GetOrdinal("ArchiveNo")),
AttendanceList = this.GetAttendanceList(dr["AttendanceInfoXML"].ToString())
};
model.DepartmentList = GetDepartmentList(dr["DepartmentXML"].ToString());
ResultList.Add(model);
model = null;
}
};
return(ResultList);
}
protected void Page_Load(object sender, EventArgs e)
{
int personnelID = Utils.GetInt(Utils.GetQueryStringValue("personnelID"), -1);
//绑定
if (!IsPostBack && personnelID != -1)
{
EyouSoft.BLL.AdminCenterStructure.PersonnelInfo bllPersonnel = new EyouSoft.BLL.AdminCenterStructure.PersonnelInfo();
EyouSoft.Model.AdminCenterStructure.PersonnelInfo modelPersonnel = bllPersonnel.GetModel(CurrentUserCompanyID, personnelID);
this.lblPersonnelTitle.Text = modelPersonnel.UserName;
this.lblFileNo.Text = modelPersonnel.ArchiveNo;
this.lblName.Text = modelPersonnel.UserName;
this.lblSex.Text = modelPersonnel.ContactSex.ToString();
this.imgPersonnelPhoto.ImageUrl = modelPersonnel.PhotoPath;
this.lblCardID.Text = modelPersonnel.CardId;
this.lblBirthday.Text = modelPersonnel.BirthDate == null ? "" : string.Format("{0:yyyy-MM-dd}", modelPersonnel.BirthDate);
this.lblDepartment.Text = GetDepartmentByID(modelPersonnel.DepartmentId); // 所属部门
this.lblPosition.Text = GetDutyNameByID(modelPersonnel.DutyId);
this.lblType.Text = modelPersonnel.PersonalType.ToString();
this.lblState.Text = modelPersonnel.IsLeave ? "离职" : "在职";
this.lblEntryDate.Text = string.Format("{0:yyyy-MM-dd}", modelPersonnel.EntryDate);
this.lblWorkerLife.Text = GetWorkYear(modelPersonnel.EntryDate) + "年";
this.lblNational.Text = modelPersonnel.National;
Birthplace = GetProvinceAndCity(modelPersonnel.Birthplace);
this.lblPolitical.Text = modelPersonnel.Politic;
this.lblMarriageState.Text = modelPersonnel.IsMarried ? "已婚" : "未婚";
this.lblTel.Text = modelPersonnel.ContactTel;
this.lblMobile.Text = modelPersonnel.ContactMobile;
this.lblLeftDate.Text = modelPersonnel.LeaveDate == null ? "" : string.Format("{0:yyyy-MM-dd}", modelPersonnel.LeaveDate);
this.lblQQ.Text = modelPersonnel.QQ;
this.lblMSN.Text = modelPersonnel.MSN;
this.lblEmail.Text = modelPersonnel.Email;
this.lblAddress.Text = modelPersonnel.ContactAddress;
this.lblRemark.Text = modelPersonnel.Remark;
this.rptRecord.DataSource = modelPersonnel.HistoryList;
this.rptRecord.DataBind();
this.rptEducationInfo.DataSource = modelPersonnel.SchoolList;
this.rptEducationInfo.DataBind();
}
}
/// <summary>
/// 增加
/// </summary>
/// <param name="model">职工档案信息实体</param>
/// <returns></returns>
public bool Add(EyouSoft.Model.AdminCenterStructure.PersonnelInfo model)
{
bool IsTrue = false;
IsTrue = idal.Add(model);
#region LGWR
if (IsTrue)
{
EyouSoft.Model.CompanyStructure.SysHandleLogs logInfo = new EyouSoft.Model.CompanyStructure.SysHandleLogs();
logInfo.CompanyId = 0;
logInfo.DepatId = 0;
logInfo.EventCode = EyouSoft.Model.CompanyStructure.SysHandleLogsNO.EventCode;
logInfo.EventIp = string.Empty;
logInfo.EventMessage = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "{0}在" + EyouSoft.Model.EnumType.CompanyStructure.SysPermissionClass.行政中心_人事档案.ToString() + "新增了人事档案信息数据。";
logInfo.EventTime = DateTime.Now;
logInfo.EventTitle = "新增人事档案信息";
logInfo.ModuleId = EyouSoft.Model.EnumType.CompanyStructure.SysPermissionClass.行政中心_人事档案;
logInfo.OperatorId = 0;
this.Logwr(logInfo);
logInfo = null;
}
#endregion
return(IsTrue);
}
/// <summary>
/// 获取认识档案信息实体
/// </summary>
/// <param name="CompanyId">公司编号</param>
/// <param name="PersonId">职员编号</param>
/// <returns></returns>
public EyouSoft.Model.AdminCenterStructure.PersonnelInfo GetModel(int CompanyId, int PersonId)
{
EyouSoft.Model.AdminCenterStructure.PersonnelInfo model = null;
model = (from item in dcDal.PersonnelInfo
where item.CompanyId == CompanyId && item.Id == PersonId
select new EyouSoft.Model.AdminCenterStructure.PersonnelInfo
{
Id = item.Id,
ArchiveNo = item.ArchiveNo,
BirthDate = item.BirthDate,
Birthplace = item.Birthplace,
CardId = item.CardId,
CompanyId = item.CompanyId,
ContactAddress = item.ContactAddress,
ContactMobile = item.ContactMobile,
ContactSex = (EyouSoft.Model.EnumType.CompanyStructure.Sex)Enum.Parse(typeof(EyouSoft.Model.EnumType.CompanyStructure.Sex), item.ContactSex.Value.ToString()),
ContactTel = item.ContactTel,
DepartmentId = item.DepartmentId,
DutyId = item.DutyId,
Email = item.Email,
EntryDate = item.EntryDate,
IsLeave = item.IsLeave == 1 ? true : false,
IsMarried = item.IsMarried == 1 ? true : false,
LeaveDate = item.LeaveDate,
MSN = item.Msn,
National = item.National,
PersonalType = (EyouSoft.Model.EnumType.AdminCenterStructure.PersonalType)Enum.Parse(typeof(EyouSoft.Model.EnumType.AdminCenterStructure.PersonalType), item.PersonalType.Value.ToString()),
PhotoPath = item.PhotoPath,
Politic = item.Politic,
QQ = item.Qq,
Remark = item.Remark,
ServiceYear = item.ServiceYear,
UserName = item.UserName,
SchoolList = (from School in dcDal.SchoolInfo //学历信息
where School.PersonId == item.Id
select new EyouSoft.Model.AdminCenterStructure.SchoolInfo
{
Id = School.Id,
Degree = (EyouSoft.Model.EnumType.AdminCenterStructure.DegreeType)Enum.Parse(typeof(EyouSoft.Model.EnumType.AdminCenterStructure.DegreeType), School.Degree.ToString()),
EndDate = School.EndDate,
Professional = School.Professional,
Remark = School.Remark,
SchoolName = School.SchoolName,
StartDate = School.StartDate,
StudyStatus = Convert.ToBoolean(School.StudyStatus),
}).ToList <EyouSoft.Model.AdminCenterStructure.SchoolInfo>(),
HistoryList = (from History in dcDal.PersonalHistory //履历信息
where History.PersonId == item.Id
select new EyouSoft.Model.AdminCenterStructure.PersonalHistory
{
Id = History.Id,
EndDate = History.EndDate,
Remark = History.Remark,
StartDate = History.StartDate,
TakeUp = History.TakeUp,
WorkPlace = History.WorkPlace,
WorkUnit = History.WorkUnit
}).ToList <EyouSoft.Model.AdminCenterStructure.PersonalHistory>()
}).FirstOrDefault();
return(model);
}
/// <summary>
/// 修改
/// </summary>
/// <param name="model">职工档案信息实体</param>
/// <returns></returns>
public bool Update(EyouSoft.Model.AdminCenterStructure.PersonnelInfo model)
{
bool IsTrue = false;
EyouSoft.Data.PersonnelInfo DataModel = dcDal.PersonnelInfo.FirstOrDefault(item =>
item.Id == model.Id && item.CompanyId == model.CompanyId
);
if (DataModel != null)
{
//实体赋值
InputModelValue(DataModel, model);
#region 学历处理
if (model.SchoolList != null && model.SchoolList.Count > 0)
{
if (this.SchoolDelete(model.Id))
{
((List <EyouSoft.Model.AdminCenterStructure.SchoolInfo>)model.SchoolList).ForEach(item =>
{
EyouSoft.Data.SchoolInfo DataSchoolModel = new EyouSoft.Data.SchoolInfo();
DataSchoolModel.Degree = Convert.ToByte((int)item.Degree);
DataSchoolModel.EndDate = item.EndDate;
DataSchoolModel.PersonId = model.Id;
DataSchoolModel.Professional = item.Professional;
DataSchoolModel.Remark = item.Remark;
DataSchoolModel.SchoolName = item.SchoolName;
DataSchoolModel.StartDate = item.StartDate;
DataSchoolModel.StudyStatus = (byte)(item.StudyStatus ? 1 : 0);
DataModel.PersonSchoolInfoList.Add(DataSchoolModel);
DataSchoolModel = null;
});
}
}
else
{
this.SchoolDelete(model.Id);
}
#endregion 学历处理
#region 履历处理
if (model.HistoryList != null && model.HistoryList.Count > 0)
{
if (this.HistoryDelete(model.Id))
{
((List <EyouSoft.Model.AdminCenterStructure.PersonalHistory>)model.HistoryList).ForEach(item =>
{
EyouSoft.Data.PersonalHistory DataHistoryModel = new EyouSoft.Data.PersonalHistory();
DataHistoryModel.EndDate = item.EndDate;
DataHistoryModel.PersonId = model.Id;
DataHistoryModel.Remark = item.Remark;
DataHistoryModel.StartDate = item.StartDate;
DataHistoryModel.TakeUp = item.TakeUp;
DataHistoryModel.WorkPlace = item.WorkPlace;
DataHistoryModel.WorkUnit = item.WorkUnit;
DataModel.PersonPersonalHistoryList.Add(DataHistoryModel);
DataHistoryModel = null;
});
}
}
else
{
this.HistoryDelete(model.Id);
}
#endregion 学历处理
dcDal.SubmitChanges();
}
if (dcDal.ChangeConflicts.Count == 0)
{
IsTrue = true;
}
DataModel = null;
return(IsTrue);
}
/// <summary>
/// 获取人事档案列表信息
/// </summary>
/// <param name="PageSize"></param>
/// <param name="PageIndex"></param>
/// <param name="ReCordCount"></param>
/// <param name="CompanyId">公司编号</param>
/// <returns></returns>
public IList <EyouSoft.Model.AdminCenterStructure.PersonnelInfo> GetList(int PageSize, int PageIndex, ref int ReCordCount, int CompanyId, EyouSoft.Model.AdminCenterStructure.PersonnelSearchInfo SearchInfo)
{
IList <EyouSoft.Model.AdminCenterStructure.PersonnelInfo> ResultList = null;
#region SQL处理
string tableName = "tbl_PersonnelInfo";
string identityColumnName = "Id";
string orderByString = " IssueTime DESC";
StringBuilder fields = new StringBuilder();
fields.Append("[Id],[ArchiveNo],[BirthDate],[UserName],[ContactSex],MSN,QQ,EntryDate,DepartmentId,");
fields.AppendFormat("(SELECT [Id],[DepartName] FROM tbl_CompanyDepartment a WHERE a.[id] IN(select [value] from dbo.fn_split([tbl_PersonnelInfo].[DepartmentId],',')) AND [CompanyId]={0} FOR XML Raw,Root('Department'))AS [DepartmentXML],", CompanyId);
fields.AppendFormat("(SELECT [JobName] FROM [tbl_DutyManager] b WHERE b.[id]=[tbl_PersonnelInfo].[dutyid] AND [CompanyId]={0}) AS [DutyName],", CompanyId);
fields.Append("(SELECT DATEDIFF(YEAR,ISNULL(EntryDate,getdate()),getdate())) AS [WorkYear],[ContactTel],[ContactMobile],[Email]");
StringBuilder query = new StringBuilder();
query.AppendFormat("[CompanyId]={0}", CompanyId);
if (!string.IsNullOrEmpty(SearchInfo.ArchiveNo))
{
query.AppendFormat(" AND ArchiveNo like'%{0}%'", SearchInfo.ArchiveNo);
}
if (!string.IsNullOrEmpty(SearchInfo.UserName))
{
query.AppendFormat(" AND UserName like'%{0}%'", SearchInfo.UserName);
}
if (SearchInfo.IsLeave.HasValue)
{
query.AppendFormat(" AND IsLeave={0}", SearchInfo.IsLeave == true ? 1 : 0);
}
if (SearchInfo.IsMarried.HasValue)
{
query.AppendFormat(" AND IsMarried={0}", SearchInfo.IsMarried == true ? 1 : 0);
}
if (SearchInfo.DutyId.HasValue)
{
query.AppendFormat(" AND DutyId={0}", SearchInfo.DutyId);
}
if (SearchInfo.BirthDateFrom.HasValue)
{
query.AppendFormat(" AND DATEDIFF(DAY,'{0}',BirthDate)>=0", SearchInfo.BirthDateFrom);
}
if (SearchInfo.BirthDateTo.HasValue)
{
query.AppendFormat(" AND DATEDIFF(DAY,BirthDate,'{0}')>=0", SearchInfo.BirthDateTo);
}
if (SearchInfo.PersonalType.HasValue && ((int)SearchInfo.PersonalType) >= 0)
{
query.AppendFormat(" AND PersonalType={0}", (int)SearchInfo.PersonalType);
}
if (SearchInfo.ContactSex.HasValue && ((int)SearchInfo.ContactSex) > 0)
{
query.AppendFormat(" AND ContactSex={0}", (int)SearchInfo.ContactSex);
}
if (SearchInfo.WorkYearFrom.HasValue && SearchInfo.WorkYearFrom > 0)
{
query.AppendFormat(" AND DATEDIFF(DAY,EntryDate,getdate())>={0}", SearchInfo.WorkYearFrom * 365);
}
if (SearchInfo.WorkYearTo.HasValue && SearchInfo.WorkYearTo > 0)
{
query.AppendFormat(" AND DATEDIFF(DAY,EntryDate,getdate())<={0}", SearchInfo.WorkYearTo * 365);
}
#endregion
using (IDataReader dr = DbHelper.ExecuteReader(_db, PageSize, PageIndex, ref ReCordCount, tableName, identityColumnName, fields.ToString(), query.ToString(), orderByString))
{
ResultList = new List <EyouSoft.Model.AdminCenterStructure.PersonnelInfo>();
while (dr.Read())
{
EyouSoft.Model.AdminCenterStructure.PersonnelInfo model = new EyouSoft.Model.AdminCenterStructure.PersonnelInfo()
{
Id = dr.GetInt32(dr.GetOrdinal("Id")),
ArchiveNo = dr.IsDBNull(dr.GetOrdinal("ArchiveNo")) ? "" : dr.GetString(dr.GetOrdinal("ArchiveNo")),
MSN = dr.IsDBNull(dr.GetOrdinal("MSN")) ? "" : dr.GetString(dr.GetOrdinal("MSN")),
QQ = dr.IsDBNull(dr.GetOrdinal("QQ")) ? "" : dr.GetString(dr.GetOrdinal("QQ")),
UserName = dr.IsDBNull(dr.GetOrdinal("UserName")) ? "" : dr.GetString(dr.GetOrdinal("UserName")),
ContactSex = (EyouSoft.Model.EnumType.CompanyStructure.Sex)Enum.Parse(typeof(EyouSoft.Model.EnumType.CompanyStructure.Sex), dr.GetInt32(dr.GetOrdinal("ContactSex")).ToString()),
DepartmentList = this.GetDepartmentList(dr["DepartmentXML"].ToString()),
DepartmentId = dr["DepartmentId"].ToString(),
DutyName = dr.IsDBNull(dr.GetOrdinal("DutyName")) ? "" : dr.GetString(dr.GetOrdinal("DutyName")),
WorkYear = dr.IsDBNull(dr.GetOrdinal("WorkYear")) ? 0 : Convert.ToInt32(dr["WorkYear"].ToString()),
ContactTel = dr.IsDBNull(dr.GetOrdinal("ContactTel")) ? "" : dr.GetString(dr.GetOrdinal("ContactTel")),
ContactMobile = dr.IsDBNull(dr.GetOrdinal("ContactMobile")) ? "" : dr.GetString(dr.GetOrdinal("ContactMobile")),
Email = dr.IsDBNull(dr.GetOrdinal("Email")) ? "" : dr.GetString(dr.GetOrdinal("Email"))
};
if (dr.IsDBNull(dr.GetOrdinal("EntryDate")))
{
model.EntryDate = null;
}
else
{
model.EntryDate = dr.GetDateTime(dr.GetOrdinal("EntryDate"));
}
if (dr.IsDBNull(dr.GetOrdinal("BirthDate")))
{
model.BirthDate = null;
}
else
{
model.BirthDate = dr.GetDateTime(dr.GetOrdinal("BirthDate"));
}
ResultList.Add(model);
model = null;
}
};
return(ResultList);
}
/// <summary>
/// 得到保存或者修改的对象
/// </summary>
private void GetAddOrUpdatePersonnelInfo(out EyouSoft.BLL.AdminCenterStructure.PersonnelInfo bllPersonnel, out EyouSoft.Model.AdminCenterStructure.PersonnelInfo modelPersonnel)
{
#region "基本信息"
FileNo = Utils.GetFormValue("txt_FileNo"); //档案编号
Name = Utils.GetFormValue("txt_Name"); //姓名
CardID = Utils.GetFormValue("txt_CardID"); //身份证号码
Birthday = Utils.GetDateTimeNullable(Request.Form["txt_Birthday"]); //生日
EntryDate = Utils.GetFormValue("txt_EntryDate"); //入职日期
//dpWorkLife = Utils.GetFormValue("dpWorkLife"); //工龄
LeftDate = Utils.GetFormValue("txt_LeftDate"); //离职日期
National = Utils.GetFormValue("txt_National"); //民族
Political = Utils.GetFormValue("txt_Political"); //政治面貌
Telephone = Utils.GetFormValue("txt_Telephone"); //联系电话
Mobile = Utils.GetFormValue("txt_Mobile"); //手机
QQ = Utils.GetFormValue("txt_QQ");
MSN = Utils.GetFormValue("txt_MSN");
Email = Utils.GetFormValue("txt_Email");
Address = Utils.GetFormValue("txt_Address");
Remark = Utils.GetFormValue("txt_Remark");
#endregion
#region "学历信息"
string[] keysGrade = Utils.GetFormValues("EducationGrade"); //学历
string[] keysState = Utils.GetFormValues("EducationState"); //状态
string[] RecordStartDate = Utils.GetFormValues("txt_RecordStartDate"); //开始时间
string[] RecordEndDate = Utils.GetFormValues("txt_RecordEndDate"); //结束时间
string[] Profession = Utils.GetFormValues("txt_Profession"); //专业
string[] Graduation = Utils.GetFormValues("txt_Graduation"); //毕业院校
string[] RecordRemark = Utils.GetFormValues("txt_RecordRemark"); //备注
#endregion
#region "履历信息"
string[] ResumeStartDate = Utils.GetFormValues("txt_ResumeStartDate"); //开始时间
string[] ResumeEndDate = Utils.GetFormValues("txt_ResumeEndDate"); //结束时间
string[] WorkPlace = Utils.GetFormValues("txt_WorkPlace"); //工作地点
string[] WorkUnit = Utils.GetFormValues("txt_WorkUnit"); //工作单位
string[] Job = Utils.GetFormValues("txt_Job"); //职业
string[] ResumeRemark = Utils.GetFormValues("txt_ResumeRemark"); //备注
#endregion
bllPersonnel = new EyouSoft.BLL.AdminCenterStructure.PersonnelInfo();
modelPersonnel = new EyouSoft.Model.AdminCenterStructure.PersonnelInfo();
#region 保存修改
modelPersonnel.CompanyId = CurrentUserCompanyID;
modelPersonnel.ArchiveNo = FileNo;
modelPersonnel.UserName = Name;
modelPersonnel.ContactSex = (EyouSoft.Model.EnumType.CompanyStructure.Sex) this.dpSex.SelectedIndex;
modelPersonnel.CardId = CardID;
modelPersonnel.BirthDate = Birthday;
modelPersonnel.DepartmentId = this.ucDepartemnt.GetDepartId;
HttpPostedFile hpf = this.Request.Files["FileEmployeePicture"];
bool Result = false;
string fileName = string.Empty;
string oldeName = string.Empty;
if (hpf != null && hpf.ContentLength > 0)
{
Result = UploadFile.FileUpLoad(hpf, "workerPhoto", out fileName, out oldeName);
modelPersonnel.PhotoPath = fileName; //上转照片
}
else
{
modelPersonnel.PhotoPath = this.hiddenPhoto.Value;
}
modelPersonnel.DutyName = ddlJobPostion.SelectedValue;
string m = this.ddlJobPostion.Text;
modelPersonnel.DutyId = Utils.GetIntNull(Utils.GetFormValue(this.ddlJobPostion.UniqueID));//职务ID
modelPersonnel.IsLeave = this.dpWorkerState.Value == "1" ? true : false;
modelPersonnel.PersonalType = (EyouSoft.Model.EnumType.AdminCenterStructure.PersonalType)Utils.GetInt(this.dpWorkerType.Value);
//modelPersonnel.WorkYear = Utils.GetInt(dpWorkLife);
modelPersonnel.EntryDate = Utils.GetDateTimeNullable(EntryDate);
modelPersonnel.IsMarried = this.dpMarriageState.Value == "1" ? true : false;
modelPersonnel.LeaveDate = Utils.GetDateTimeNullable(LeftDate);
modelPersonnel.National = National;
modelPersonnel.Birthplace = this.ucProvince1.ProvinceId + "," + this.ucCity1.CityId;
modelPersonnel.Politic = Political;
modelPersonnel.ContactTel = Telephone;
modelPersonnel.ContactMobile = Mobile;
modelPersonnel.QQ = QQ;
modelPersonnel.Email = Email;
modelPersonnel.MSN = MSN;
modelPersonnel.ContactAddress = Address;
modelPersonnel.Remark = Remark;
#region 学历信息
IList <EyouSoft.Model.AdminCenterStructure.SchoolInfo> listSchool = new List <EyouSoft.Model.AdminCenterStructure.SchoolInfo>();
EyouSoft.Model.AdminCenterStructure.SchoolInfo modelSchool = null;
if (RecordStartDate != null && RecordStartDate.Length > 0)
{
for (int index = 0; index < RecordStartDate.Length; index++)
{
if (!string.IsNullOrEmpty(RecordStartDate[index].Trim()) && !string.IsNullOrEmpty(RecordEndDate[index].Trim()) && (!string.IsNullOrEmpty(Utils.GetFormValue(keysGrade[index]).Trim()) || !string.IsNullOrEmpty(Profession[index].Trim()) || !string.IsNullOrEmpty(Graduation[index].Trim()) || !string.IsNullOrEmpty(Utils.GetFormValue(keysState[index]).Trim()) || !string.IsNullOrEmpty(RecordRemark[index].Trim())))
{
modelSchool = new EyouSoft.Model.AdminCenterStructure.SchoolInfo();
modelSchool.StartDate = Utils.GetDateTimeNullable(RecordStartDate[index].Trim());
modelSchool.EndDate = Utils.GetDateTimeNullable(RecordEndDate[index].Trim());
modelSchool.Degree = (EyouSoft.Model.EnumType.AdminCenterStructure.DegreeType)Utils.GetInt(keysGrade[index]);
modelSchool.Professional = Utils.InputText(Profession[index].Trim());
modelSchool.SchoolName = Utils.InputText(Graduation[index].Trim());
modelSchool.StudyStatus = Convert.ToBoolean(Utils.GetInt(keysState[index]));
modelSchool.Remark = Utils.InputText(RecordRemark[index].Trim());
listSchool.Add(modelSchool);
}
}
if (listSchool.Count > 0)
{
modelPersonnel.SchoolList = listSchool;
}
}
#endregion
#region 履历信息
IList <EyouSoft.Model.AdminCenterStructure.PersonalHistory> listHistory = new List <EyouSoft.Model.AdminCenterStructure.PersonalHistory>();
EyouSoft.Model.AdminCenterStructure.PersonalHistory modelHistory = null;
if (ResumeStartDate != null && ResumeStartDate.Length > 0)
{
for (int index = 0; index < ResumeStartDate.Length; index++)
{
if (!string.IsNullOrEmpty(ResumeStartDate[index].Trim()) && !string.IsNullOrEmpty(ResumeEndDate[index].Trim()) && (!string.IsNullOrEmpty(WorkPlace[index].Trim()) || !string.IsNullOrEmpty(WorkUnit[index].Trim()) || !string.IsNullOrEmpty(Job[index].Trim()) || !string.IsNullOrEmpty(ResumeRemark[index].Trim())))
{
modelHistory = new EyouSoft.Model.AdminCenterStructure.PersonalHistory();
modelHistory.StartDate = Utils.GetDateTimeNullable(ResumeStartDate[index].Trim());
modelHistory.EndDate = Utils.GetDateTimeNullable(ResumeEndDate[index].Trim());
modelHistory.WorkPlace = Utils.InputText(WorkPlace[index].Trim());
modelHistory.WorkUnit = Utils.InputText(WorkUnit[index].Trim());
modelHistory.TakeUp = Utils.InputText(Job[index].Trim());
modelHistory.Remark = Utils.InputText(ResumeRemark[index].Trim());
listHistory.Add(modelHistory);
}
}
if (listHistory != null && listHistory.Count > 0)
{
modelPersonnel.HistoryList = listHistory;
}
}
#endregion
#endregion
}
