protected void btnImport_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
XBase.Model.Office.HumanManager.EmployeeSearchModel searchModel = new XBase.Model.Office.HumanManager.EmployeeSearchModel(); //获取数据
searchModel.EmployeeNo = txtEmployeeNo.Text; //编号
searchModel.EmployeeName = txtEmployeeName.Text; //姓名
searchModel.SexID = ddlSex.SelectedValue.Trim(); //性别
searchModel.CultureLevel = ((DropDownList)ddlCulture.Controls[0]).SelectedValue.Trim(); //学历
searchModel.SchoolName = txtSchoolName.Text; //毕业院校
searchModel.QuarterID = ddlQuarter.SelectedValue.Trim(); //应聘岗位
searchModel.Flag = DDLFlag.SelectedValue.Trim(); //应聘岗位
int TotalCount = 0;
DataTable dt = XBase.Business.Office.HumanManager.EmployeeInfoBus.SearchWaitEnterInfo(searchModel, 1, 10000000, "EmployeeNo", ref TotalCount);//查询数据
//导出标题
string headerTitle = "人员编号|姓名|性别|应聘岗位|联系方式|学历|毕业院校|专业|人员类型|复试结果";
string[] header = headerTitle.Split('|');
//导出标题所对应的列字段名称
string columnFiled = "EmployeeNo|EmployeeName|SexName|QuarterName|Contact|CultureLevelName|SchoolName|ProfessionalName|Flag|FinalResult";
string[] field = columnFiled.Split('|');
XBase.Common.OutputToExecl.ExportToTable(this.Page, dt, header, field, "未入职人员列表");
}
/// <summary>
/// 查询人才储备列表
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public static DataTable SearchEmployeeReserveInfo(EmployeeSearchModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
return EmployeeInfoDBHelper.SearchEmployeeReserveInfo(model);
}
/// <summary>
/// 查询在职人员列表
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public static DataTable SearchEmployeeWorkInfo(EmployeeSearchModel model, int pageIndex,int pageCount,string ord, ref int TotalCount)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
return EmployeeInfoDBHelper.SearchEmployeeWorkInfo(model, pageIndex, pageCount, ord, ref TotalCount);
}
public static DataTable SearchEmplInfo(EmployeeSearchModel model)
{
//查询并返回人员信息
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
//执行查询并返回值
return SalaryItemDBHelper.SearchEmplInfo(model);
}
protected void btnImport_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
try
{
int TotalCount = 0;
XBase.Model.Office.HumanManager.EmployeeSearchModel model = new XBase.Model.Office.HumanManager.EmployeeSearchModel(); //获取数据
model.EmployeeNo = txtEmployeeNo.Text.Trim(); //编号
model.EmployeeNum = txtEmployeeNum.Text.Trim(); //工号
model.PYShort = ""; //拼音缩写
model.EmployeeName = txtEmployeeName.Text.Trim(); //姓名
model.ContractKind = ""; //request.QueryString["ContractKind"].Trim();//工种
model.AdminLevelID = ""; // request.QueryString["AdminLevel"].Trim(); //行政等级
model.QuarterID = ddlQuarter.SelectedValue; //岗位
model.PositionID = ((DropDownList)ddlPosition.Controls[0]).SelectedValue; //职称
model.EnterDate = txtStartDate.Text.Trim(); //入职时间
DataTable dt = XBase.Business.Office.HumanManager.EmployeeInfoBus.SearchEmployeeWorkInfo(model, 1, 1000000, "EmployeeNo", ref TotalCount); //查询数据
dt.Columns.Add("EntryAge", typeof(String));
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["EntryDate"].ToString() != "")
{
dt.Rows[i]["EntryAge"] = Convert.ToInt32(DateTime.Now.ToString("yyyy")) - Convert.ToInt32(Convert.ToDateTime(dt.Rows[i]["EntryDate"]).ToString("yyyy")) + 1;
}
}
//导出标题
string headerTitle = "编号|工号|姓名|部门|岗位|职称|入职时间|在公司工龄";
string[] header = headerTitle.Split('|');
//导出标题所对应的列字段名称
string columnFiled = "EmployeeNo|EmployeeNum|EmployeeName|DeptName|QuarterName|PositionName|EntryDate|EntryAge";
string[] field = columnFiled.Split('|');
XBase.Common.OutputToExecl.ExportToTable(this.Page, dt, header, field, "快速调职通道");
}
catch { }
}
/// <summary>
/// 通过检索条件查询在职人员相关信息
/// </summary>
/// <param name="model">检索条件</param>
/// <returns></returns>
public static DataTable SearchEmplInfo(EmployeeSearchModel model)
{
#region 查询SQL拼写
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine(" SELECT ");
searchSql.AppendLine(" A.ID ");
searchSql.AppendLine(" ,ISNULL(A.EmployeeNo, '') AS EmployeeNo ");
searchSql.AppendLine(" ,ISNULL(A.EmployeeNum, '') AS EmployeeNum ");
searchSql.AppendLine(" ,ISNULL(A.EmployeeName, '') AS EmployeeName ");
searchSql.AppendLine(" ,ISNULL(C.QuarterName, '') AS QuarterName ");
searchSql.AppendLine(" ,ISNULL(A.QuarterID, '') AS QuarterID ");
searchSql.AppendLine(" ,ISNULL(B.DeptName, '') AS DeptName ");
searchSql.AppendLine(" ,ISNULL(A.DeptID, '') AS Dept ");
searchSql.AppendLine(" ,ISNULL(D.TypeName, '') AS AdminLevelName ");
searchSql.AppendLine(" ,ISNULL(A.AdminLevelID, '') AS AdminLevelID ");
searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),A.EnterDate,21),'') ");
searchSql.AppendLine(" AS EnterDate ");
searchSql.AppendLine(" FROM ");
searchSql.AppendLine(" officedba.EmployeeInfo A ");
searchSql.AppendLine(" LEFT JOIN officedba.DeptInfo B ");
searchSql.AppendLine(" ON A.DeptID = B.ID ");
searchSql.AppendLine(" LEFT JOIN officedba.DeptQuarter C ");
searchSql.AppendLine(" ON A.QuarterID = C.ID ");
searchSql.AppendLine(" LEFT JOIN officedba.CodePublicType D ");
searchSql.AppendLine(" ON A.AdminLevelID = D.ID ");
searchSql.AppendLine(" WHERE ");
searchSql.AppendLine(" A.CompanyCD = @CompanyCD ");
searchSql.AppendLine(" AND A.Flag = @Flag ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//公司代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
//非离职
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Flag", "1"));
//编号
if (!string.IsNullOrEmpty(model.EmployeeNo))
{
searchSql.AppendLine(" AND A.EmployeeNo LIKE '%' + @EmployeeNo + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeNo", model.EmployeeNo));
}
//姓名
if (!string.IsNullOrEmpty(model.EmployeeName))
{
searchSql.AppendLine(" AND A.EmployeeName LIKE '%' + @EmployeeName + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeName", model.EmployeeName));
}
//姓名
if (!string.IsNullOrEmpty(model.Dept))
{
searchSql.AppendLine(" AND B.DeptName LIKE '%' + @DeptName + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptName", model.Dept));
}
//岗位
if (!string.IsNullOrEmpty(model.QuarterID))
{
searchSql.AppendLine(" AND A.QuarterID = @QuarterID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@QuarterID", model.QuarterID));
}
//岗位职等
if (!string.IsNullOrEmpty(model.AdminLevelID))
{
searchSql.AppendLine(" AND A.AdminLevelID = @AdminLevelID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@AdminLevelID", model.AdminLevelID));
}
//设定comm的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
/// <summary>
/// 查询在职部门岗位等相关信息
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public static DataTable GetWorkEmplInfo(EmployeeSearchModel model)
{
#region 查询SQL拼写
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine(" SELECT ");
searchSql.AppendLine(" A.ID ");
searchSql.AppendLine(" ,A.EmployeeNo ");
searchSql.AppendLine(" ,A.EmployeeNum ");
searchSql.AppendLine(" ,A.CompanyCD ");
searchSql.AppendLine(" ,A.EmployeeName ");
searchSql.AppendLine(" ,A.QuarterID ");
searchSql.AppendLine(" ,C.QuarterName ");
searchSql.AppendLine(" ,A.DeptID ");
searchSql.AppendLine(" ,B.DeptName ");
searchSql.AppendLine(" ,A.AdminLevelID ");
searchSql.AppendLine(" ,D.TypeName AS AdminLevelName ");
searchSql.AppendLine(" FROM ");
searchSql.AppendLine(" officedba.EmployeeInfo A ");
searchSql.AppendLine(" LEFT JOIN officedba.DeptInfo B ");
searchSql.AppendLine(" ON B.ID = A.DeptID ");
searchSql.AppendLine(" LEFT JOIN officedba.DeptQuarter C ");
searchSql.AppendLine(" ON C.ID = A.QuarterID ");
searchSql.AppendLine(" LEFT JOIN officedba.CodePublicType D ");
searchSql.AppendLine(" ON D.ID = A.AdminLevelID ");
searchSql.AppendLine(" WHERE ");
searchSql.AppendLine(" A.CompanyCD = @CompanyCD ");
searchSql.AppendLine(" AND A.Flag = @Flag ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//公司代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
//在职标识
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Flag", "1"));
#region 页面查询条件
//编号
if (!string.IsNullOrEmpty(model.EmployeeNo))
{
searchSql.AppendLine(" AND A.EmployeeNo LIKE '%' + @EmployeeNo + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeNo", model.EmployeeNo ));
}
//工号
if (!string.IsNullOrEmpty(model.EmployeeNum))
{
searchSql.AppendLine(" AND A.EmployeeNum LIKE '%' + @EmployeeNum + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeNum", model.EmployeeNum));
}
//姓名
if (!string.IsNullOrEmpty(model.EmployeeName))
{
searchSql.AppendLine(" AND A.EmployeeName LIKE '%' + @EmployeeName + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeName", model.EmployeeName ));
}
//所在岗位
if (!string.IsNullOrEmpty(model.QuarterID))
{
searchSql.AppendLine(" AND A.QuarterID = @QuarterID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@QuarterID", model.QuarterID));
}
//岗位职等
if (!string.IsNullOrEmpty(model.AdminLevelID))
{
searchSql.AppendLine(" AND A.AdminLevelID = @AdminLevelID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@AdminLevelID", model.AdminLevelID));
}
#endregion
//设置排序
searchSql.AppendLine(" ORDER BY A.DeptID, A.EnterDate ");
//设定comm的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
protected void btnImport_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
try
{
string orderString = hiddExpOrder.Value.Trim();//排序
string order = "asc";//排序：降序
string orderBy = (!string.IsNullOrEmpty(orderString)) ? orderString.Substring(0, orderString.Length - 2) : "ID";//要排序的字段，如果为空，默认为"ID"
if (orderString.EndsWith("_d"))
{
order = "desc";//排序：降序
}
string ord = " ORDER BY " + orderBy + " " + order;
//获取数据
EmployeeSearchModel model = new EmployeeSearchModel();
//编号
model.EmployeeNo = txtEmployeeNo.Value.Trim();
//工号
model.EmployeeNum = txtEmployeeNum.Value.Trim();
//拼音缩写
model.PYShort = txtPYShort.Value.Trim();
//姓名
model.EmployeeName = txtEmployeeName.Value.Trim();
//岗位
model.QuarterID = ddlQuarter_ddlCodeType.SelectedItem.Value;
//职称
model.PositionID = ddlPosition.SelectedValue;
//入职时间
model.EnterDate = txtStartDate.Value;
model.EnterEndDate = txtEnteryEndDate.Value;
//离职时间
model.LeaveDate = txtLeaveDate.Value;
model.LeaveEndDate = txtLeaveEndDate.Value;
DataTable dt = EmployeeInfoBus.SearchEmployeeLeaveInfo(model);
OutputToExecl.ExportToTableFormat(this, dt,
new string[] { "编号", "工号", "姓名", "拼音缩写", "曾用名", "部门","英文名", "人员分类",
"证件类型","证件号码","社保卡号","应聘职务","岗位","职称","岗位职等",
"所在部门","入职时间","性别","出生日期","联系电话","手机号码","电子邮件","其他联系方式",
"籍贯","婚姻状况","学历","毕业院校","专业","政治面貌","宗教信仰",
"民族","户口","户口性质","国家地区","身高(厘米)","体重","视力",
"最高学位","健康状况","特长","计算机水平","参加工作时间","家庭住址","外语语种1",
"外语语种2","外语语种3","外语水平1","外语水平2","外语水平3",
"专业描述" },
new string[] { "EmployeeNo", "EmployeeNum", "EmployeeName", "PYShort", "UsedName","DeptName", "NameEn", "Flag",
"DocuType","CardID","SafeguardCard","PositionTitle","QuarterName","PositionName","AdminLevelName",
"DeptName","EnterDate","SexName","Birth","Telephone","Mobile","EMail","OtherContact",
"Origin","MarriageStatus","CultureLevel","GraduateSchool","Professional","Landscape","Religion",
"NationalName","Account","AccountNature","CountryName","Height","Weight","Sight",
"Degree","HealthStatus","Features","ComputerLevel","WorkTime","HomeAddress","ForeignLanguage11",
"ForeignLanguage12","ForeignLanguage13","ForeignLanguageLevel1","ForeignLanguageLevel2","ForeignLanguageLevel3",
"ProfessionalDes" },
"离职人员列表");
}
catch
{
ClientScript.RegisterStartupScript(this.GetType(), "Exp", "<script language=javascript>showPopup('../../../Images/Pic/Close.gif','../../../Images/Pic/note.gif','导出发生异常');</script>");
}
}
protected void btnImport_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
try
{
int TotalCount = 0;
XBase.Model.Office.HumanManager.EmployeeSearchModel model = new XBase.Model.Office.HumanManager.EmployeeSearchModel();//获取数据
model.EmployeeNo = txtEmployeeNo.Text.Trim();//编号
model.EmployeeNum = txtEmployeeNum.Text.Trim();//工号
model.PYShort = "";//拼音缩写
model.EmployeeName = txtEmployeeName.Text.Trim();//姓名
model.ContractKind = "";//request.QueryString["ContractKind"].Trim();//工种
model.AdminLevelID = "";// request.QueryString["AdminLevel"].Trim(); //行政等级
model.QuarterID = ddlQuarter.SelectedValue; //岗位
model.PositionID = ((DropDownList)ddlPosition.Controls[0]).SelectedValue; //职称
model.EnterDate = txtStartDate.Text.Trim();//入职时间
DataTable dt = XBase.Business.Office.HumanManager.EmployeeInfoBus.SearchEmployeeWorkInfo(model, 1, 1000000, "EmployeeNo", ref TotalCount);//查询数据
dt.Columns.Add("EntryAge", typeof(String));
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["EntryDate"].ToString()!="")
dt.Rows[i]["EntryAge"] = Convert.ToInt32(DateTime.Now.ToString("yyyy")) - Convert.ToInt32(Convert.ToDateTime(dt.Rows[i]["EntryDate"]).ToString("yyyy")) + 1;
}
//导出标题
string headerTitle = "编号|工号|姓名|部门|岗位|职称|入职时间|在公司工龄";
string[] header = headerTitle.Split('|');
//导出标题所对应的列字段名称
string columnFiled = "EmployeeNo|EmployeeNum|EmployeeName|DeptName|QuarterName|PositionName|EntryDate|EntryAge";
string[] field = columnFiled.Split('|');
XBase.Common.OutputToExecl.ExportToTable(this.Page, dt, header, field, "快速离职通道");
}
catch { }
}
/// <summary>
/// 获取人才储备信息
/// </summary>
/// <returns></returns>
public static DataTable GetReserveInfo(EmployeeSearchModel model)
{
//获取登陆用户信息
string companyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
model.CompanyCD = companyCD;
//执行查询并返回结果
return RectInterviewDBHelper.GetReserveInfo(model);
}
/// <summary>
/// 查询人力档案回收站
/// </summary>
/// <param name="searchModel">查询条件</param>
/// <returns></returns>
public static DataTable SearchEmployeeCallBack(EmployeeSearchModel searchModel,int pageIndex,int pageCount,string ord, ref int TotalCount)
{
#region 查询SQL拼写
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine("SELECT A.ID ");
searchSql.AppendLine(" ,A.EmployeeNo ");
searchSql.AppendLine(" ,ISNULL(A.PYShort, '') AS PYShort ");
searchSql.AppendLine(" ,A.EmployeeName ");
searchSql.AppendLine(" ,Case A.Sex ");
searchSql.AppendLine(" When '1' then '男' ");
searchSql.AppendLine(" When '2' then '女' ");
searchSql.AppendLine(" else '' ");
searchSql.AppendLine(" End as SexName ");
searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),A.Birth, 21), '') AS Birth ");
searchSql.AppendLine(" ,CASE WHEN A.Birth is null THEN '' ELSE ");
searchSql.AppendLine("CONVERT(VARCHAR(3),DATEDIFF(YEAR,A.Birth,getdate())) + ' 岁' END AS Age ");
searchSql.AppendLine(" ,ISNULL(A.Origin, '') AS Origin ");
searchSql.AppendLine(" ,(case A.Flag when '1' then '在职人员' when '2' then '人才储备' ");
searchSql.AppendLine(" when '3' then '离职人员' end ) Flag ");
searchSql.AppendLine(" ,ISNULL(c.QuarterName, '') AS PositionTitle ");
searchSql.AppendLine(" ,ISNULL(B.TypeName, '') AS ProfessionalName ");
searchSql.AppendLine(" FROM officedba.EmployeeInfo A LEFT outer join officedba.CodePublicType B on");
searchSql.AppendLine(" A.CompanyCD = B.CompanyCD AND A.Professional = B.ID");
searchSql.AppendLine(" LEFT join officedba.DeptQuarter c on c.id = A.QuarterID");
searchSql.AppendLine("WHERE ");
searchSql.AppendLine(" A.CompanyCD = @CompanyCD ");
searchSql.AppendLine(" AND delFlag = @delFlag ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", searchModel.CompanyCD));
////添加人才储备参数
//comm.Parameters.Add(SqlHelper.GetParameterFromString("@Flag", ConstUtil.EMPLOYEE_FLAG_TALENT));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@delFlag", "1"));
#region 页面输入条件
//编号
if (!string.IsNullOrEmpty(searchModel.EmployeeNo))
{
searchSql.AppendLine(" AND A.EmployeeNo LIKE '%' + @EmployeeNo + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeNo", searchModel.EmployeeNo));
}
//拼音缩写
if (!string.IsNullOrEmpty(searchModel.PYShort))
{
searchSql.AppendLine(" AND A.PYShort LIKE '%' + @PYShort + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PYShort", searchModel.PYShort));
}
//姓名
if (!string.IsNullOrEmpty(searchModel.EmployeeName))
{
searchSql.AppendLine(" AND A.EmployeeName LIKE '%' + @EmployeeName + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeName", searchModel.EmployeeName));
}
//性别
if (!string.IsNullOrEmpty(searchModel.SexID))
{
searchSql.AppendLine(" AND A.Sex = @Sex ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Sex", searchModel.SexID));
}
//文化程度
if (!string.IsNullOrEmpty(searchModel.CultureLevel))
{
searchSql.AppendLine(" AND A.CultureLevel = @CultureLevel ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CultureLevel", searchModel.CultureLevel));
}
//专业
if (!string.IsNullOrEmpty(searchModel.ProfessionalID))
{
searchSql.AppendLine(" AND A.Professional = @ProfessionalID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProfessionalID", searchModel.ProfessionalID));
}
//毕业院校
if (!string.IsNullOrEmpty(searchModel.SchoolName))
{
searchSql.AppendLine(" AND A.GraduateSchool LIKE '%' + @GraduateSchool + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@GraduateSchool", searchModel.SchoolName));
}
//应聘岗位
if (!string.IsNullOrEmpty(searchModel.PositionTitle))
{
searchSql.AppendLine(" AND c.QuarterName LIKE '%' + @PositionTitle + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PositionTitle", searchModel.PositionTitle));
}
//工龄
if (!string.IsNullOrEmpty(searchModel.TotalSeniority))
{
searchSql.AppendLine(" AND isnull(datediff(year,WorkTime,GETDATE())+1,'') = @TotalSeniority ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TotalSeniority", searchModel.TotalSeniority));
}
//手机号码
if (!string.IsNullOrEmpty(searchModel.Mobile))
{
searchSql.AppendLine(" AND A.Mobile LIKE '%' + @Mobile + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Mobile", searchModel.Mobile));
}
#endregion
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
//return SqlHelper.ExecuteSearch(comm);
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount);
}
/// <summary>
/// 查询离职人员列表
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public static DataTable SearchEmployeeLeaveInfo(EmployeeSearchModel searchModel)
{
#region 查询SQL拼写
//查询SQL拼写
//StringBuilder searchSql = new StringBuilder();
//searchSql.AppendLine(" SELECT ");
//searchSql.AppendLine(" A.ID ");
//searchSql.AppendLine(" ,A.EmployeeNo ");
//searchSql.AppendLine(" ,ISNULL(A.EmployeeNum, '') AS EmployeeNum ");
//searchSql.AppendLine(" ,ISNULL(A.PYShort, '') AS PYShort ");
//searchSql.AppendLine(" ,A.EmployeeName ");
//searchSql.AppendLine(" ,CASE G.ContractKind ");
//searchSql.AppendLine(" WHEN '1' THEN '合同工' ");
//searchSql.AppendLine(" WHEN '2' THEN '临时工' ");
//searchSql.AppendLine(" WHEN '3' THEN '兼职' ");
//searchSql.AppendLine(" ELSE '' ");
//searchSql.AppendLine(" END AS ContractKind ");
//searchSql.AppendLine(" ,Case A.Sex ");
//searchSql.AppendLine(" When '1' then '男' ");
//searchSql.AppendLine(" When '2' then '女' ");
//searchSql.AppendLine(" else '' ");
//searchSql.AppendLine(" End as SexName ");
//searchSql.AppendLine(" ,ISNULL(E.DeptName, '') AS DeptName ");
////searchSql.AppendLine(" ,ISNULL(H.TypeName, '') AS AdminLevelName ");
//searchSql.AppendLine(" ,ISNULL(I.QuarterName, '') AS QuarterName ");
//searchSql.AppendLine(" ,ISNULL(F.TypeName, '') AS PositionName ");
//searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),A.EnterDate,21),'') AS EntryDate ");
//searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),B.OutDate,21),'') AS LeaveDate ");
////searchSql.AppendLine(" ,CASE WHEN A.EnterDate is null THEN '' ELSE ");
////searchSql.AppendLine(" CONVERT(VARCHAR(2),DATEDIFF(YEAR,A.EnterDate,getdate())+1) + ' 年' ");
////searchSql.AppendLine(" END AS TotalYear ");
//searchSql.AppendLine(" FROM ");
//searchSql.AppendLine(" officedba.EmployeeInfo A ");
//searchSql.AppendLine(" LEFT JOIN (select companycd,EmployeeID,max(outdate) outdate from officedba.MoveNotify where BillStatus = '2' group by EmployeeID,companycd ) B ");
//searchSql.AppendLine(" ON B.EmployeeID = A.ID ");
//searchSql.AppendLine(" AND B.CompanyCD = A.CompanyCD ");
//searchSql.AppendLine(" LEFT JOIN officedba.DeptInfo E ");
//searchSql.AppendLine(" ON E.ID = A.DeptID ");
//searchSql.AppendLine(" LEFT JOIN officedba.CodePublicType F ");
//searchSql.AppendLine(" ON F.ID = A.PositionID ");
////searchSql.AppendLine(" LEFT JOIN officedba.CodePublicType H ");
////searchSql.AppendLine(" ON H.ID = A.AdminID ");
//searchSql.AppendLine(" LEFT JOIN officedba.DeptQuarter I ");
//searchSql.AppendLine(" ON I.ID = A.QuarterID ");
//searchSql.AppendLine(" LEFT JOIN officedba.EmployeeContract G ");
//searchSql.AppendLine(" ON G.EmployeeID = A.ID ");
//searchSql.AppendLine(" AND G.ContractStatus = @ContractStatus ");
//searchSql.AppendLine(" WHERE ");
//searchSql.AppendLine(" A.CompanyCD = @CompanyCD ");
//searchSql.AppendLine(" AND A.Flag = @JobFlag ");
#endregion
#region 查询SQL拼写
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine("SELECT A.ID,ISNULL(CONVERT(VARCHAR(10),r.OutDate,21),'') AS LeaveDate ");
searchSql.AppendLine(" ,ISNULL(A.EmployeeNum, '') AS EmployeeNum,ISNULL(CONVERT(VARCHAR(10),A.EnterDate,21),'') AS EntryDate");
searchSql.AppendLine(" ,A.EmployeeNo ");
searchSql.AppendLine(" ,ISNULL(A.PYShort, '') AS PYShort ");
searchSql.AppendLine(" ,A.CompanyCD ");
searchSql.AppendLine(" ,A.CardID ");
searchSql.AppendLine(" ,A.SafeguardCard ");
searchSql.AppendLine(" ,isnull(A.EmployeeName,'') EmployeeName ");
searchSql.AppendLine(" ,A.UsedName ");
searchSql.AppendLine(" ,A.NameEn ");
searchSql.AppendLine(" ,(case A.Sex when '1' then '男' else '女' end)SexName ");
searchSql.AppendLine(" ,A.Birth ");
searchSql.AppendLine(" ,A.Account ");
searchSql.AppendLine(" ,(case A.AccountNature when '1' then '非农业' when '2' then '农业' else '' end) AccountNature ");
searchSql.AppendLine(" ,A.CountryID ");
searchSql.AppendLine(" ,A.Height ");
searchSql.AppendLine(" ,CONVERT(varchar(8),A.Weight) Weight ");
searchSql.AppendLine(" ,CONVERT(varchar(8),A.Sight) Sight ");
searchSql.AppendLine(" ,A.Degree ");
searchSql.AppendLine(" ,A.DocuType ");
searchSql.AppendLine(" ,A.Origin ");
searchSql.AppendLine(" ,A.Telephone ");
searchSql.AppendLine(" ,A.Mobile ");
searchSql.AppendLine(" ,A.EMail ");
searchSql.AppendLine(" ,A.OtherContact ");
searchSql.AppendLine(" ,A.HomeAddress ");
searchSql.AppendLine(" ,(case A.HealthStatus when '0' then '一般' when '1' then '良好' when '2' then '很好' else '' end) HealthStatus");
searchSql.AppendLine(" ,A.GraduateSchool ");
searchSql.AppendLine(" ,A.Features ");
searchSql.AppendLine(" ,A.ComputerLevel ");
searchSql.AppendLine(" ,(case A.ForeignLanguageLevel1 when '1' then '一般' when '2' then '熟练' when '3' then '精通' end)ForeignLanguageLevel1 ");
searchSql.AppendLine(" ,(case A.ForeignLanguageLevel2 when '1' then '一般' when '2' then '熟练' when '3' then '精通' end)ForeignLanguageLevel2 ");
searchSql.AppendLine(" ,(case A.ForeignLanguageLevel3 when '1' then '一般' when '2' then '熟练' when '3' then '精通' end)ForeignLanguageLevel3 ");
searchSql.AppendLine(" ,A.WorkTime ");
searchSql.AppendLine(" ,A.TotalSeniority,A.PhotoURL,A.PositionTitle,'离职人员' Flag ");
searchSql.AppendLine(" ,A.ModifiedDate ,A.ModifiedUserID,A.QuarterID ");
searchSql.AppendLine(" ,A.AdminLevelID ,A.DeptID ,ISNULL(C.DeptName, '') AS DeptName ");
searchSql.AppendLine(" ,CONVERT(varchar(100), A.EnterDate, 23) EnterDate ");
searchSql.AppendLine(" ,A.Resume,A.ProfessionalDes,q.TypeName NationalName ");
searchSql.AppendLine(" ,ISNULL(d.TypeName, '') AS PositionName,ISNULL(e.QuarterName, '') AS QuarterName,f.TypeName MarriageStatus ");
searchSql.AppendLine(" ,g.TypeName CultureLevel,g.TypeName Professional,i.TypeName Landscape ");
searchSql.AppendLine(" ,j.TypeName Religion,k.TypeName CountryName,l.TypeName ForeignLanguage11 ");
searchSql.AppendLine(" ,m.TypeName ForeignLanguage12,o.TypeName ForeignLanguage13,p.TypeName AdminLevelName ");
searchSql.AppendLine(" FROM officedba.EmployeeInfo A ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.DeptInfo C on C.id = A.DeptID ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType d on d.id = A.PositionID ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.DeptQuarter e on e.id = A.QuarterID ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType f on f.id = A.MarriageStatus ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType g on g.id = A.CultureLevel ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType h on h.id = A.Professional ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType i on i.id = A.Landscape ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType j on j.id = A.Religion ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType k on k.id = A.CountryID ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType l on l.id = A.ForeignLanguage1 ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType m on m.id = A.ForeignLanguage2 ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType o on o.id = A.ForeignLanguage3 ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType p on p.id = A.AdminLevelID ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType q on q.id = A.NationalID ");
searchSql.AppendLine(" LEFT JOIN (select companycd,EmployeeID,max(outdate) outdate from officedba.MoveNotify where BillStatus = '2' group by EmployeeID,companycd ) r ");
searchSql.AppendLine(" ON r.EmployeeID = A.ID ");
searchSql.AppendLine(" AND r.CompanyCD = A.CompanyCD ");
searchSql.AppendLine(" WHERE ");
searchSql.AppendLine(" A.CompanyCD = @CompanyCD ");
searchSql.AppendLine(" AND A.Flag = @JobFlag and (A.delFlag <> @delFlag or A.delFlag is null) ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加离职参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@JobFlag", ConstUtil.JOB_FLAG_LEAVE));
//添加合同状态参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ContractStatus", ConstUtil.CONTRACT_FLAG_ONE));
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", searchModel.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@delFlag", "1"));
#region 页面输入条件
//编号
if (!string.IsNullOrEmpty(searchModel.EmployeeNo))
{
searchSql.AppendLine(" AND A.EmployeeNo LIKE '%' + @EmployeeNo + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeNo", searchModel.EmployeeNo));
}
//工号
if (!string.IsNullOrEmpty(searchModel.EmployeeNum))
{
searchSql.AppendLine(" AND A.EmployeeNum LIKE '%' + @EmployeeNum + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeNum", searchModel.EmployeeNum));
}
//拼音缩写
if (!string.IsNullOrEmpty(searchModel.PYShort))
{
searchSql.AppendLine(" AND A.PYShort LIKE '%' + @PYShort + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PYShort", searchModel.PYShort));
}
//姓名
if (!string.IsNullOrEmpty(searchModel.EmployeeName))
{
searchSql.AppendLine(" AND A.EmployeeName LIKE '%' + @EmployeeName + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeName", searchModel.EmployeeName));
}
//工种
if (!string.IsNullOrEmpty(searchModel.ContractKind))
{
searchSql.AppendLine(" AND G.ContractKind = @ContractKind ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ContractKind", searchModel.ContractKind));
}
//行政等级
if (!string.IsNullOrEmpty(searchModel.AdminLevelID))
{
searchSql.AppendLine(" AND A.AdminID = @NowAdminLevel ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@NowAdminLevel", searchModel.AdminLevelID));
}
//岗位
if (!string.IsNullOrEmpty(searchModel.QuarterID))
{
searchSql.AppendLine(" AND A.QuarterID = @QuarterID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@QuarterID", searchModel.QuarterID));
}
//职称
if (!string.IsNullOrEmpty(searchModel.PositionID))
{
searchSql.AppendLine(" AND A.PositionID = @NowPositionID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@NowPositionID", searchModel.PositionID));
}
//入职时间
if (!string.IsNullOrEmpty(searchModel.EnterDate))
{
searchSql.AppendLine(" AND A.EnterDate >= @EntryDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EntryDate", searchModel.EnterDate));
}
if (!string.IsNullOrEmpty(searchModel.EnterEndDate))
{
searchSql.AppendLine(" AND A.EnterDate <= @EnterEndDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EnterEndDate", searchModel.EnterEndDate));
}
//离职时间
if (!string.IsNullOrEmpty(searchModel.LeaveDate))
{
searchSql.AppendLine(" AND B.OutDate >= @RegisterDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@RegisterDate", searchModel.LeaveDate));
}
if (!string.IsNullOrEmpty(searchModel.LeaveEndDate))
{
searchSql.AppendLine(" AND B.OutDate <= @LeaveEndDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@LeaveEndDate", searchModel.LeaveEndDate));
}
if (!string.IsNullOrEmpty(searchModel.Mobile))
{
searchSql.AppendLine(" AND A.Mobile LIKE '%' + @Mobile + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Mobile", searchModel.Mobile));
}
#endregion
//设定comm的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
/// <summary>
/// 导出在职人员列表
/// </summary>
/// <param name="searchModel"></param>
/// <param name="ord"></param>
/// <returns></returns>
public static DataTable ExportEmployeeWorkInfo(EmployeeSearchModel searchModel,string BeginDate,string EndDate, string ord)
{
#region 查询SQL拼写
//查询SQL拼写
//StringBuilder searchSql = new StringBuilder();
//searchSql.AppendLine(" SELECT ");
//searchSql.AppendLine(" A.ID ");
//searchSql.AppendLine(" ,A.EmployeeNo ");
//searchSql.AppendLine(" ,ISNULL(A.EmployeeNum, '') AS EmployeeNum ");
//searchSql.AppendLine(" ,ISNULL(A.PYShort, '') AS PYShort ");
//searchSql.AppendLine(" ,A.EmployeeName ");
//searchSql.AppendLine(" ,CASE F.ContractKind ");
//searchSql.AppendLine(" WHEN '1' THEN '合同工' ");
//searchSql.AppendLine(" WHEN '2' THEN '临时工' ");
//searchSql.AppendLine(" WHEN '3' THEN '兼职' ");
//searchSql.AppendLine(" ELSE '' ");
//searchSql.AppendLine(" END AS ContractKind ");
//searchSql.AppendLine(" ,ISNULL(B.DeptName, '') AS DeptName ");
//searchSql.AppendLine(" ,ISNULL(C.QuarterName, '') AS QuarterName ");
//searchSql.AppendLine(" ,ISNULL(D.TypeName, '') AS AdminLevelName ");
//searchSql.AppendLine(" ,ISNULL(G.TypeName, '') AS PositionName ");
//searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),A.EnterDate,21),'') AS EntryDate ");
////searchSql.AppendLine(" ,CASE ");
////searchSql.AppendLine(" WHEN A.EnterDate is null THEN '' ");
////searchSql.AppendLine(" ELSE ");
////searchSql.AppendLine(" CONVERT(VARCHAR(2),DATEDIFF(YEAR,A.EnterDate,getdate())+1) + ' 年' ");
////searchSql.AppendLine(" END AS TotalYear ");
////searchSql.AppendLine(" ,CONVERT(VARCHAR(100),A.EnterDate,23) TotalYear ");
//searchSql.AppendLine(" FROM ");
//searchSql.AppendLine(" officedba.EmployeeInfo A ");
//searchSql.AppendLine(" LEFT JOIN officedba.DeptInfo B ");
//searchSql.AppendLine(" ON A.DeptID = B.ID ");
//searchSql.AppendLine(" LEFT JOIN officedba.DeptQuarter C ");
//searchSql.AppendLine(" ON A.QuarterID = C.ID ");
//searchSql.AppendLine(" LEFT JOIN officedba.CodePublicType D ");
//searchSql.AppendLine(" ON A.AdminLevelID = D.ID ");
//searchSql.AppendLine(" LEFT JOIN officedba.CodePublicType G ");
//searchSql.AppendLine(" ON A.PositionID = G.ID ");
//searchSql.AppendLine(" LEFT JOIN officedba.EmployeeContract F ");
//searchSql.AppendLine(" ON A.ID = F.EmployeeID ");
//searchSql.AppendLine(" AND F.ContractStatus = @ContractStatus ");
//searchSql.AppendLine(" WHERE ");
//searchSql.AppendLine(" A.CompanyCD = @CompanyCD ");
//searchSql.AppendLine(" AND A.Flag = @Flag ");
#endregion
#region 查询SQL拼写
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine("SELECT A.ID ");
searchSql.AppendLine(" ,A.EmployeeNum ");
searchSql.AppendLine(" ,A.EmployeeNo ");
searchSql.AppendLine(" ,A.PYShort ");
searchSql.AppendLine(" ,A.CompanyCD ");
searchSql.AppendLine(" ,A.CardID ");
searchSql.AppendLine(" ,A.SafeguardCard ");
searchSql.AppendLine(" ,A.EmployeeName ");
searchSql.AppendLine(" ,A.UsedName ");
searchSql.AppendLine(" ,A.NameEn ");
searchSql.AppendLine(" ,(case A.Sex when '1' then '男' else '女' end)Sex ");
searchSql.AppendLine(" ,A.Birth ");
searchSql.AppendLine(" ,A.Account ");
searchSql.AppendLine(" ,(case A.AccountNature when '1' then '非农业' when '2' then '农业' else '' end) AccountNature ");
searchSql.AppendLine(" ,A.CountryID ");
searchSql.AppendLine(" ,A.Height ");
searchSql.AppendLine(" ,CONVERT(varchar(8),A.Weight) Weight ");
searchSql.AppendLine(" ,CONVERT(varchar(8),A.Sight) Sight ");
searchSql.AppendLine(" ,A.Degree ");
searchSql.AppendLine(" ,A.DocuType ");
searchSql.AppendLine(" ,A.Origin ");
searchSql.AppendLine(" ,A.Telephone ");
searchSql.AppendLine(" ,A.Mobile ");
searchSql.AppendLine(" ,A.EMail ");
searchSql.AppendLine(" ,A.OtherContact ");
searchSql.AppendLine(" ,A.HomeAddress ");
searchSql.AppendLine(" ,(case A.HealthStatus when '0' then '一般' when '1' then '良好' when '2' then '很好' else '' end) HealthStatus");
searchSql.AppendLine(" ,A.GraduateSchool ");
searchSql.AppendLine(" ,A.Features ");
searchSql.AppendLine(" ,A.ComputerLevel ");
searchSql.AppendLine(" ,(case A.ForeignLanguageLevel1 when '1' then '一般' when '2' then '熟练' when '3' then '精通' end)ForeignLanguageLevel1 ");
searchSql.AppendLine(" ,(case A.ForeignLanguageLevel2 when '1' then '一般' when '2' then '熟练' when '3' then '精通' end)ForeignLanguageLevel2 ");
searchSql.AppendLine(" ,(case A.ForeignLanguageLevel3 when '1' then '一般' when '2' then '熟练' when '3' then '精通' end)ForeignLanguageLevel3 ");
searchSql.AppendLine(" ,A.WorkTime ");
searchSql.AppendLine(" ,A.TotalSeniority,A.PhotoURL,A.PositionTitle,'在职人员' Flag ");
searchSql.AppendLine(" ,A.ModifiedDate ,A.ModifiedUserID,A.QuarterID ");
searchSql.AppendLine(" ,A.AdminLevelID ,A.DeptID ,C.DeptName ");
searchSql.AppendLine(" ,CONVERT(varchar(100), A.EnterDate, 23) EnterDate ");
searchSql.AppendLine(" ,A.Resume,A.ProfessionalDes,q.TypeName NationalName ");
searchSql.AppendLine(" ,d.TypeName PositionName,e.QuarterName QuarterName,f.TypeName MarriageStatus ");
searchSql.AppendLine(" ,g.TypeName CultureLevel,g.TypeName Professional,i.TypeName Landscape ");
searchSql.AppendLine(" ,j.TypeName Religion,k.TypeName CountryName,l.TypeName ForeignLanguage11 ");
searchSql.AppendLine(" ,m.TypeName ForeignLanguage12,o.TypeName ForeignLanguage13,p.TypeName AdminLevelName ");
searchSql.AppendLine(" FROM officedba.EmployeeInfo A ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.DeptInfo C on C.id = A.DeptID ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType d on d.id = A.PositionID ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.DeptQuarter e on e.id = A.QuarterID ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType f on f.id = A.MarriageStatus ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType g on g.id = A.CultureLevel ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType h on h.id = A.Professional ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType i on i.id = A.Landscape ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType j on j.id = A.Religion ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType k on k.id = A.CountryID ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType l on l.id = A.ForeignLanguage1 ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType m on m.id = A.ForeignLanguage2 ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType o on o.id = A.ForeignLanguage3 ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType p on p.id = A.AdminLevelID ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType q on q.id = A.NationalID ");
searchSql.AppendLine(" WHERE ");
searchSql.AppendLine(" A.CompanyCD = @CompanyCD ");
searchSql.AppendLine(" AND A.Flag = @Flag and (A.delFlag <> @delFlag or A.delFlag is null) ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加非离职参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Flag", "1"));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@delFlag", "1"));
//添加合同状态参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ContractStatus", ConstUtil.CONTRACT_FLAG_ONE));
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", searchModel.CompanyCD));
#region 页面条件
//编号
if (!string.IsNullOrEmpty(searchModel.EmployeeNo))
{
searchSql.AppendLine(" AND A.EmployeeNo LIKE '%' + @EmployeeNo + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeNo", searchModel.EmployeeNo));
}
//工号
if (!string.IsNullOrEmpty(searchModel.EmployeeNum))
{
searchSql.AppendLine(" AND A.EmployeeNum LIKE '%' + @EmployeeNum + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeNum", searchModel.EmployeeNum));
}
//拼音缩写
if (!string.IsNullOrEmpty(searchModel.PYShort))
{
searchSql.AppendLine(" AND A.PYShort LIKE '%' + @PYShort + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PYShort", searchModel.PYShort));
}
//姓名
if (!string.IsNullOrEmpty(searchModel.EmployeeName))
{
searchSql.AppendLine(" AND A.EmployeeName LIKE '%' + @EmployeeName + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeName", searchModel.EmployeeName));
}
//岗位
if (!string.IsNullOrEmpty(searchModel.QuarterID))
{
searchSql.AppendLine(" AND A.QuarterID = @QuarterID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@QuarterID", searchModel.QuarterID));
}
//职称
if (!string.IsNullOrEmpty(searchModel.PositionID))
{
searchSql.AppendLine(" AND A.PositionID = @PositionID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PositionID", searchModel.PositionID));
}
//入职时间
if (BeginDate != "")
{
searchSql.AppendLine(" AND A.EnterDate >= @BeginDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BeginDate", BeginDate));
}
//入职时间
if (EndDate != "")
{
searchSql.AppendLine(" AND A.EnterDate <= @EndDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndDate", EndDate));
}
#endregion
//设定comm的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
/// <summary>
/// 查询在职人员列表
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public static DataTable SearchEmployeeWorkInfo(EmployeeSearchModel searchModel,int pageIndex,int pageCount,string ord, ref int TotalCount)
{
#region 查询SQL拼写
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine(" SELECT ");
searchSql.AppendLine(" A.ID ");
searchSql.AppendLine(" ,A.EmployeeNo ");
searchSql.AppendLine(" ,ISNULL(A.EmployeeNum, '') AS EmployeeNum ");
searchSql.AppendLine(" ,ISNULL(A.PYShort, '') AS PYShort ");
searchSql.AppendLine(" ,A.EmployeeName ");
//searchSql.AppendLine(" ,CASE F.ContractKind ");
//searchSql.AppendLine(" WHEN '1' THEN '合同工' ");
//searchSql.AppendLine(" WHEN '2' THEN '临时工' ");
//searchSql.AppendLine(" WHEN '3' THEN '兼职' ");
//searchSql.AppendLine(" ELSE '' ");
//searchSql.AppendLine(" END AS ContractKind ");
searchSql.AppendLine(" ,ISNULL(B.DeptName, '') AS DeptName ");
searchSql.AppendLine(" ,ISNULL(C.QuarterName, '') AS QuarterName ");
searchSql.AppendLine(" ,ISNULL(D.TypeName, '') AS AdminLevelName ");
searchSql.AppendLine(" ,ISNULL(G.TypeName, '') AS PositionName ");
searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),A.EnterDate,21),'') AS EntryDate ");
//searchSql.AppendLine(" ,CASE ");
//searchSql.AppendLine(" WHEN A.EnterDate is null THEN '' ");
//searchSql.AppendLine(" ELSE ");
//searchSql.AppendLine(" CONVERT(VARCHAR(2),DATEDIFF(YEAR,A.EnterDate,getdate())+1) + ' 年' ");
//searchSql.AppendLine(" END AS TotalYear ");
searchSql.AppendLine(" ,CONVERT(VARCHAR(100),A.EnterDate,23) TotalYear ");
searchSql.AppendLine(" FROM ");
searchSql.AppendLine(" officedba.EmployeeInfo A ");
searchSql.AppendLine(" LEFT JOIN officedba.DeptInfo B ");
searchSql.AppendLine(" ON A.DeptID = B.ID ");
searchSql.AppendLine(" LEFT JOIN officedba.DeptQuarter C ");
searchSql.AppendLine(" ON A.QuarterID = C.ID ");
searchSql.AppendLine(" LEFT JOIN officedba.CodePublicType D ");
searchSql.AppendLine(" ON A.AdminLevelID = D.ID ");
searchSql.AppendLine(" LEFT JOIN officedba.CodePublicType G ");
searchSql.AppendLine(" ON A.PositionID = G.ID ");
//searchSql.AppendLine(" LEFT JOIN officedba.EmployeeContract F ");
//searchSql.AppendLine(" ON A.ID = F.EmployeeID ");
//searchSql.AppendLine(" AND F.ContractStatus = @ContractStatus ");
searchSql.AppendLine(" WHERE ");
searchSql.AppendLine(" A.CompanyCD = @CompanyCD ");
searchSql.AppendLine(" AND A.Flag = @Flag and (A.delFlag <> @delFlag or A.delFlag is null) ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加非离职参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Flag", "1"));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@delFlag", "1"));
////添加合同状态参数
//comm.Parameters.Add(SqlHelper.GetParameterFromString("@ContractStatus", ConstUtil.CONTRACT_FLAG_ONE));
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", searchModel.CompanyCD));
#region 页面条件
//编号
if (!string.IsNullOrEmpty(searchModel.EmployeeNo))
{
searchSql.AppendLine(" AND A.EmployeeNo LIKE '%' + @EmployeeNo + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeNo", searchModel.EmployeeNo));
}
//工号
if (!string.IsNullOrEmpty(searchModel.EmployeeNum))
{
searchSql.AppendLine(" AND A.EmployeeNum LIKE '%' + @EmployeeNum + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeNum", searchModel.EmployeeNum));
}
//拼音缩写
if (!string.IsNullOrEmpty(searchModel.PYShort))
{
searchSql.AppendLine(" AND A.PYShort LIKE '%' + @PYShort + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PYShort", searchModel.PYShort));
}
//姓名
if (!string.IsNullOrEmpty(searchModel.EmployeeName))
{
searchSql.AppendLine(" AND A.EmployeeName LIKE '%' + @EmployeeName + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeName", searchModel.EmployeeName));
}
//工种
//if (!string.IsNullOrEmpty(searchModel.ContractKind))
//{
// searchSql.AppendLine(" AND F.ContractKind = @ContractKind ");
// comm.Parameters.Add(SqlHelper.GetParameterFromString("@ContractKind", searchModel.ContractKind));
//}
//行政等级
//if (!string.IsNullOrEmpty(searchModel.AdminLevelID))
//{
// searchSql.AppendLine(" AND A.AdminLevelID = @AdminLevelID ");
// comm.Parameters.Add(SqlHelper.GetParameterFromString("@AdminLevelID", searchModel.AdminLevelID));
//}
//岗位
if (!string.IsNullOrEmpty(searchModel.QuarterID))
{
searchSql.AppendLine(" AND A.QuarterID = @QuarterID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@QuarterID", searchModel.QuarterID));
}
//职称
if (!string.IsNullOrEmpty(searchModel.PositionID))
{
searchSql.AppendLine(" AND A.PositionID = @PositionID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PositionID", searchModel.PositionID));
}
//入职时间
if (!string.IsNullOrEmpty(searchModel.EnterDate))
{
searchSql.AppendLine(" AND A.EnterDate >= @EnterDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EnterDate", searchModel.EnterDate));
}
//入职时间
if (!string.IsNullOrEmpty(searchModel.EnterEndDate))
{
searchSql.AppendLine(" AND A.EnterDate <= @EnterEndDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EnterEndDate", searchModel.EnterEndDate));
}
//手机号码
if (!string.IsNullOrEmpty(searchModel.Mobile))
{
searchSql.AppendLine(" AND A.Mobile like '%'+ @Mobile + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Mobile", searchModel.Mobile));
}
#endregion
//设定comm的SQL文
comm.CommandText = searchSql.ToString();
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount);
//执行查询
//return SqlHelper.ExecuteSearch(comm);
}
/// <summary>
/// 生成报表处理
/// </summary>
/// <param name="belongMonth">所属月份</param>
/// <returns></returns>
public static string CreateSalaryReport(SalaryReportModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//获取公司代码
string companyCD = userInfo.CompanyCD;
string userID = userInfo.UserID;
model.CompanyCD = companyCD;
model.ModifiedUserID = userID;
//获取人员信息
EmployeeSearchModel searchEmplModel = new EmployeeSearchModel();
//设置公司代码
searchEmplModel.CompanyCD = companyCD;
searchEmplModel.StartDate = model.StartDate;
searchEmplModel.EndDate = model.EndDate;
//查询人员信息
DataTable dtEmplInfo = EmployeeInfoDBHelper.GetWorkEmplInfo(searchEmplModel);
DataTable dtNew = SalaryItemBus.GetOutEmployeeInfo(searchEmplModel);
dtEmplInfo.Merge(dtNew);
//获取员工固定工资
DataTable dtEmplSalary = SalaryEmployeeDBHelper.GetSalaryEmployeeInfo(companyCD);
DataTable dtFixedSalary = new DataTable();
dtFixedSalary = dtEmplSalary.Clone();
//dtFixedSalary.Columns["SalaryMoney"].DataType.GetType() = "System.Decimal";
//获取员工计件工资
DataTable dtPeiceSalary = PieceworkSalaryDBHelper.GetMonthPieceworkSalary(companyCD, model.ReportMonth,model .StartDate ,model.EndDate );
string spMonth = model.ReportMonth.Substring(0, 4) + "-" + model.ReportMonth.Substring(4);
//获取员工出勤率
DataTable dtAttendance = PieceworkSalaryDBHelper.GetMonthAttendance(companyCD, spMonth );
//获取员工计时工资
DataTable dtTimeSalary = TimeSalaryDBHelper.GetMonthTimeSalary(companyCD, model.ReportMonth, model.StartDate, model.EndDate);
//获取员工产品单品提成工资
DataTable dtCommSalary = CommissionSalaryDBHelper.GetMonthCommSalary(companyCD, model.ReportMonth, model.StartDate, model.EndDate);
//获取公司业务提成
DataTable dtComanySalary = InputCompanyRoyaltyDBHelper.GetMonthCompanySalary(companyCD, model.ReportMonth, model.StartDate, model.EndDate);
//获取公司业务提成设置
DataTable dtCompanySet = InputCompanyRoyaltyDBHelper.GetCompanySetInfo(companyCD);
//获取个人业务信息
DataTable dtPersonCommSalary = InputPersonalRoyaltyDBHelper.GetMonthPersonSalary(companyCD, model.ReportMonth, model.StartDate, model.EndDate);
//获取部门业务提成
DataTable dtDeptSalary = InputDepatmentRoyaltyDBHelper.GetMonthDeptSalary (companyCD, model.ReportMonth, model.StartDate, model.EndDate);
//获取部门业务提成设置
DataTable dtDeptSet = InputDepatmentRoyaltyDBHelper.GetDeptSetInfo(companyCD);
//获取员工整体薪资结构
DataTable dtStructure = SalaryEmployeeStructureSetDBHelper.GetSalaryStructure(companyCD);
//获取绩效工资
DataTable dtPerformanceSalary = InputPerformanceRoyaltyDBHelper.GetMonthPerformanceSalary (companyCD, model.ReportMonth, model.StartDate, model.EndDate);
//获取社会保险信息
DataTable dtInsuSalary = InsuEmployeeDBHelper.GetInsuEmployeeInf(companyCD,model.ReportMonth );
//获取个人所得税信息
DataTable dtPersonSalary = InputPersonTrueIncomeTaxDBHelper.SearchPersonTax (companyCD, model.ReportMonth);
//变量定义
ArrayList lstSummary = new ArrayList();
//遍历所有的员工信息，获取工资总额
for (int i = 0; i < dtEmplInfo.Rows.Count; i++)
{
//变量定义
SalaryReportSummaryModel summaryModel = new SalaryReportSummaryModel();
//变量定义
decimal totalSalary = 0;
decimal salaryMoney = 0;
//公司代码
summaryModel.CompanyCD = companyCD;
//报表编号
summaryModel.ReprotNo = model.ReprotNo;
//获取员工ID
string employeeID = GetSafeData.GetStringFromInt(dtEmplInfo.Rows[i], "ID");
summaryModel.EmployeeID = employeeID;
summaryModel.AdminLevelName = GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "AdminLevelName");
summaryModel.DeptName = GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "DeptName");
summaryModel.EmployeeName = GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "EmployeeName");
summaryModel.EmployeeNo = GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "EmployeeNo");
summaryModel.QuarterName = GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "QuarterName");
string deptID = GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "DeptID");
//获取员工薪资结构
DataRow[] drStructure = dtStructure.Select("EmployeeID=" + employeeID);
if (drStructure.Length > 0)
{
}
else
{
lstSummary.Add(summaryModel);
continue;
}
#region 判断是否设置固定工资
if (GetSafeData.ValidateDataRow_String(drStructure[0], "IsFixSalarySet") == "1")//判断是否设置固定工资
{
//固定工资
DataRow[] drFixedSalary = dtEmplSalary.Select("EmployeeID=" + employeeID);
//遍历所有固定工资项，计算工资额
for (int j = 0; j < drFixedSalary.Length; j++)
{
//导入工资记录
dtFixedSalary.ImportRow(drFixedSalary[j]);
//该项工资额为DBNull时，增加该工资项
if (drFixedSalary[j]["SalaryMoney"] != DBNull.Value)
{
//获取是否扣款
string flag = GetSafeData.ValidateDataRow_String(drFixedSalary[j], "PayFlag");
string cal = GetSafeData.ValidateDataRow_String(drFixedSalary[j], "Calculate");
decimal fina=0;
if (string.IsNullOrEmpty(cal))
{
fina =Convert.ToDecimal(drFixedSalary[j]["SalaryMoney"]);
}
else
{
while (cal.IndexOf('A') != -1)
{
cal = cal.Replace('A', '+');
}
string numlist = GetSafeData.ValidateDataRow_String(drFixedSalary[j], "ParamsList");
string[] numberlist = numlist.Split(',');
string temp = cal;
for (int inde = 0; inde < numberlist.Length; inde++)
{
if (numberlist[inde] == "@")
{
DataRow[] drtem = dtAttendance.Select("EmployeeID=" + employeeID);
decimal atten=0;
if (drtem .Length >0)
{
atten = Math.Round(Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drtem[0], "AttendanceRate")) / 100, 2);
}
if (atten < 1)
{
cal = cal.Replace("{" + numberlist[inde] + "}", Convert.ToString(atten));
}
else
{
cal = cal.Replace("{" + numberlist[inde] + "}", "0");
}
}
else
{
if (!string.IsNullOrEmpty(numberlist[inde]))
{
DataRow[] drtem = dtEmplSalary.Select("EmployeeID=" + employeeID + " and ItemNo=" + numberlist[inde]);
if (drtem.Length > 0)
{
cal = cal.Replace("{" + numberlist[inde] + "}", GetSafeData.ValidateDataRow_String(drtem[0], "SalaryMoney"));
}
else
{
cal = cal.Replace("{" + numberlist[inde] + "}", "0");
}
}
}
}
try
{
// string result = CustomEval.eval(cal).ToString();
// fina = Convert.ToDecimal(result);
}
catch
{
fina = 0;
}
}
if ("0".Equals(flag))
{
//增加工资额
totalSalary += fina;
}
else
{
//减少工资额
// totalSalary -= Convert.ToDecimal(drFixedSalary[j]["SalaryMoney"]);
salaryMoney += fina ;
}
for (int a = 0; a < dtFixedSalary.Rows.Count; a++)
{
string empl=dtFixedSalary .Rows [a]["EmployeeID"]==null ?"":dtFixedSalary .Rows [a]["EmployeeID"].ToString ();
string iten=dtFixedSalary .Rows [a]["ItemNo"]==null ?"":dtFixedSalary .Rows [a]["ItemNo"].ToString ();
string organItem=drFixedSalary[j]["ItemNo"] ==null ?"":drFixedSalary[j]["ItemNo"] .ToString ();
if ((empl == employeeID) && ( iten == organItem))
{
dtFixedSalary.Rows[a]["SalaryMoney"] = fina.ToString();
}
}
}
}
//固定工资
summaryModel.FixedMoney = totalSalary.ToString();
}
else
{
//固定工资
summaryModel.FixedMoney = "0";
}
#endregion
#region 判断是否设置计件
if (GetSafeData.ValidateDataRow_String(drStructure[0], "IsPieceWorkSet") == "1")//判断是否设置计件
{
//计件工资
DataRow[] drPeiceSalary = dtPeiceSalary.Select("EmployeeID=" + employeeID);
if (drPeiceSalary.Length > 0)
{
//设置计件工资
summaryModel.WorkMoney = GetSafeData.GetStringFromDecimal(drPeiceSalary[0], "TotalSalary");
//加到总工资
totalSalary += Convert.ToDecimal(drPeiceSalary[0]["TotalSalary"]);
}
}
else
{
//设置计件工资
summaryModel.WorkMoney = "0";
//加到总工资
totalSalary += 0;
}
#endregion
#region 判断是否设置计时
if (GetSafeData.ValidateDataRow_String(drStructure[0], "IsTimeWorkSet") == "1")//判断是否设置计时
{
//计时工资
DataRow[] drTimeSalary = dtTimeSalary.Select("EmployeeID=" + employeeID);
if (drTimeSalary.Length > 0)
{
//设置计时工资
summaryModel.TimeMoney = GetSafeData.GetStringFromDecimal(drTimeSalary[0], "TotalSalary");
totalSalary += Convert.ToDecimal(drTimeSalary[0]["TotalSalary"]);
}
}
else
{
//设置计时工资
summaryModel.TimeMoney = "0";
totalSalary += 0;
}
#endregion
#region 判断是否设置产品单品提成
if (GetSafeData.ValidateDataRow_String(drStructure[0], "IsProductRoyaltySet") == "1")//判断是否设置产品单品提成
{
//提成工资 dtPersonSalary
DataRow[] drCommSalary = dtCommSalary.Select("EmployeeID=" + employeeID);
if (drCommSalary.Length > 0)
{
//设置产品单品提成工资
//summaryModel.CommissionMoney = GetSafeData.GetStringFromDecimal(drCommSalary[0], "TotalSalary");Convert.ToDecimal(drCommSalary[0]["TotalSalary"]);
summaryModel.CommissionMoney = GetSafeData.GetStringFromDecimal(drCommSalary[0], "TotalSalary");
totalSalary += Convert.ToDecimal(drCommSalary[0]["TotalSalary"]);
}
}
else
{
//设置产品单品提成工资
summaryModel.CommissionMoney = "0";
//commisionMoney = 0;
totalSalary += 0;
}
#endregion
#region 判断是否设置个人业务提成
if (GetSafeData.ValidateDataRow_String(drStructure[0], "IsPersonalRoyaltySet") == "1")//判断是否设置个人业务提成
{
//
DataRow[] drPersonCommSalary = dtPersonCommSalary.Select("EmployeeID=" + employeeID);
if (drPersonCommSalary.Length > 0)
{
//设置个人业务提成
summaryModel.PersonComMoney = GetSafeData.GetStringFromDecimal(drPersonCommSalary[0], "TotalSalary");
totalSalary += Convert.ToDecimal(drPersonCommSalary[0]["TotalSalary"]);
}
}
else
{
//设置个人业务提成
//summaryModel.CommissionMoney = "0";
summaryModel.PersonComMoney ="0";
totalSalary += 0;
}
#endregion
#region 判断是否设置公司业务提成
if (GetSafeData.ValidateDataRow_String(drStructure[0], "IsCompanyRoyaltySet") == "1")//判断是否设置公司业务提成
{
decimal rate = 0;
//提成工资 dtPersonSalary dtComanySalary
DataRow[] drCompanyalary = dtComanySalary.Select("DeptID=" + deptID);
DataRow[] drCompanyalarySet = dtCompanySet.Select("DeptID=" + deptID, "MiniMoney asc");
DataRow[] drCompanyalarySetCommon = dtCompanySet.Select("DeptID=0", "MiniMoney asc");//获取通用规则
if (drCompanyalary.Length > 0)
{
string tem= GetSafeData.ValidateDataRow_String(drCompanyalary[0], "TotalSalary");
decimal temp=Convert .ToDecimal (tem );
bool isCheck = false;
if (drCompanyalarySet.Length > 0)
{
for (int a = 0; a < drCompanyalarySet.Length; a++)
{
decimal minMoney = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drCompanyalarySet[a], "MiniMoney"));
decimal maxMoney = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drCompanyalarySet[a], "MaxMoney"));
if (temp >= minMoney && temp <maxMoney)
{
rate = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drCompanyalarySet[a], "TaxPercent"));
isCheck = true;
break;
}
}
if (!isCheck)
{
rate = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drCompanyalarySet[(drCompanyalarySet.Length - 1)], "TaxPercent"));
}
}
else if (drCompanyalarySetCommon.Length > 0)
{
bool isHave = false;
int len=drCompanyalarySetCommon.Length;
for (int a = 0; a <len ; a++)
{
decimal minMoney = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drCompanyalarySetCommon[a], "MiniMoney"));
decimal maxMoney = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drCompanyalarySetCommon[a], "MaxMoney"));
if (temp >= minMoney && temp < maxMoney)
{
rate = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drCompanyalarySetCommon[a], "TaxPercent"));
isHave = true;
break;
}
}
if (!isHave)
{
rate = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drCompanyalarySetCommon[(len -1)], "TaxPercent"));
}
}
summaryModel .CompanyComMoney = Convert .ToString ( decimal.Round(temp * rate * (Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drStructure[0], "CompanyRatePercent"))) / 10000, 2));
totalSalary += decimal.Round(temp * rate * (Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drStructure[0], "CompanyRatePercent"))) / 10000, 2);
}
}
else
{
summaryModel.CompanyComMoney = "0";
totalSalary += 0;
}
#endregion
#region 判断是否设置部门业务提成
if (GetSafeData.ValidateDataRow_String(drStructure[0], "IsDeptRoyaltySet") == "1")//判断是否设置部门业务提成
{
decimal rate = 0;
//提成工资 dtPersonSalary dtComanySalary
DataRow[] drDeptalary = dtDeptSalary.Select("DeptID=" + deptID);
DataRow[] drDeptalarySet = dtDeptSet.Select("DeptID=" + deptID, "MiniMoney asc");
DataRow[] drDeptalarySetCommon = dtDeptSet.Select("DeptID=0", "MiniMoney asc");//获取通用规则
if (drDeptalary.Length > 0)
{
string tem = GetSafeData.ValidateDataRow_String(drDeptalary[0], "TotalSalary");
decimal temp = Convert.ToDecimal(tem);
bool isCheck = false;
if (drDeptalarySet.Length > 0)
{
for (int a = 0; a < drDeptalarySet.Length; a++)
{
decimal minMoney = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drDeptalarySet[a], "MiniMoney"));
decimal maxMoney = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drDeptalarySet[a], "MaxMoney"));
if (temp >= minMoney && temp < maxMoney)
{
rate = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drDeptalarySet[a], "TaxPercent"));
break;
}
}
if (!isCheck)
{
rate = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drDeptalarySet[(drDeptalarySet.Length - 1)], "TaxPercent"));
}
}
else if (drDeptalarySetCommon.Length > 0)
{
bool isHave = false;
int len = drDeptalarySetCommon.Length;
for (int a = 0; a < len; a++)
{
decimal minMoney = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drDeptalarySetCommon[a], "MiniMoney"));
decimal maxMoney = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drDeptalarySetCommon[a], "MaxMoney"));
if (temp >= minMoney && temp < maxMoney)
{
rate = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drDeptalarySetCommon[a], "TaxPercent"));
isHave = true;
break;
}
}
if (!isHave)
{
rate = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drDeptalarySetCommon[(len- 1)], "TaxPercent"));
}
}
summaryModel .DeptComMoney = Convert .ToString ( decimal.Round(temp * rate * (Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drStructure[0], "DeptRatePercent"))) / 10000, 2));
totalSalary += decimal.Round(temp * rate * (Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drStructure[0], "DeptRatePercent"))) / 10000, 2);
}
}
else
{
summaryModel.DeptComMoney = "0";
totalSalary += 0;
}
#endregion
#region 判断是否设置绩效
if (GetSafeData.ValidateDataRow_String(drStructure[0], "IsPerformanceSet") == "1")//判断是否设置绩效
{
//计时工资
DataRow[] drPerformanceSalary = dtPerformanceSalary.Select("EmployeeID=" + employeeID);
if (drPerformanceSalary.Length > 0)
{
//设置绩效工资
//summaryModel.TimeMoney = GetSafeData.GetStringFromDecimal(drPerformanceSalary[0], "TotalPerformanceMoney");
summaryModel.PerformanceMoney = GetSafeData.GetStringFromDecimal(drPerformanceSalary[0], "TotalPerformanceMoney");//待新建一项绩效工资栏目
totalSalary += Convert.ToDecimal(drPerformanceSalary[0]["TotalPerformanceMoney"]);
}
}
else
{
//设置绩效工资
summaryModel.PerformanceMoney = "0";
totalSalary += 0;
}
#endregion
//工资总额
summaryModel.AllGetMoney = totalSalary.ToString();
decimal insuMoney = 0;
#region 判断是否设置社会保险
if (GetSafeData.ValidateDataRow_String(drStructure[0], "IsInsurenceSet") == "1")//判断是否设置社会保险
{
//社会保险
DataRow[] drInsuSalary = dtInsuSalary.Select("EmployeeID=" + employeeID);
for (int x = 0; x < drInsuSalary.Length; x++)
{
//数据不为空时，计算社会保险总额
if (drInsuSalary[x]["InsuranceBase"] != DBNull.Value
&& drInsuSalary[x]["PersonPayRate"] != DBNull.Value)
{
//保险基数
decimal insuBase = Convert.ToDecimal(drInsuSalary[x]["InsuranceBase"]);
//个人缴纳百分比
decimal insuPercent = Convert.ToDecimal(drInsuSalary[x]["PersonPayRate"]);
//增加社会保险额
insuMoney += insuBase * insuPercent / 100;
}
}
//社会保险
summaryModel.Insurance = StringUtil.TrimZero(insuMoney.ToString());
}
else
{
summaryModel.Insurance = "0";
}
#endregion
decimal minusMoney = 0;
decimal PersonMoney = 0;
#region 判断是否设置个人所得税
if (GetSafeData.ValidateDataRow_String(drStructure[0], "IsPerIncomeTaxSet") == "1")//判断是否设置个人所得税
{
//个人所得税 dtPersonSalary
DataRow[] drPersonSalary = dtPersonSalary.Select("EmployeeID=" + employeeID);
if (drPersonSalary.Length > 0)
{
decimal salryCount = drPersonSalary[0]["SalaryCount"] == null ? 0 : Convert.ToDecimal(drPersonSalary[0]["SalaryCount"]);
decimal TaxPercent = drPersonSalary[0]["TaxPercent"] == null ? 0 : Convert.ToDecimal(drPersonSalary[0]["TaxPercent"]);
decimal TaxCount = drPersonSalary[0]["TaxCount"] == null ? 0 : Convert.ToDecimal(drPersonSalary[0]["TaxCount"]);
decimal te=0;
if ((totalSalary - salryCount - insuMoney) > 0)
{
te = (totalSalary - salryCount - insuMoney) * TaxPercent / 100 - TaxCount;
}
//个人所得税
summaryModel.IncomeTax = te.ToString ();
// totalSalary -= Convert.ToDecimal(drPersonSalary[0]["TaxCount"]);
PersonMoney = te;
}
}
else
{
//个人所得税
summaryModel.IncomeTax = "0" ;
// totalSalary -= Convert.ToDecimal(drPersonSalary[0]["TaxCount"]);
PersonMoney = 0;
}
#endregion
// decimal[] taxInfo = TaxCalculateBus.CalculateTax(totalSalary);
//税额
// decimal minusMoney = taxInfo[1];
// summaryModel.IncomeTax = StringUtil.TrimZero(minusMoney.ToString());
minusMoney = insuMoney + PersonMoney + salaryMoney;
//应扣款合计
//minusMoney =minusMoney +salaryMoney;
summaryModel.AllKillMoney = minusMoney.ToString() ;
//实发工资
summaryModel.SalaryMoney = (totalSalary - minusMoney).ToString();
//
lstSummary.Add(summaryModel);
}
//登陆报表信息
bool isSucc = SalaryReportDBHelper.CreateSalaryReport(model, lstSummary, dtFixedSalary);
if (!isSucc) return null;
else return PageDisplayInfo(dtEmplInfo, dtFixedSalary, lstSummary, companyCD);
}
/// <summary>
/// 入职处理更新数据库
/// </summary>
/// <param name="model">人员信息</param>
/// <returns></returns>
public static bool UpdateEnterInfo(EmployeeSearchModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
//定义返回变量
bool isSucc = false;
/*
* 定义日志内容变量
* 增删改相关的日志，需要输出操作日志，该类型日志插入到数据库
* 其他的 如出现异常时，需要输出系统日志，该类型日志保存到日志文件
*/
//操作日志
LogInfoModel logModel = InitLogInfo(model.EmployeeNo);
//设置操作日志类型 修改
logModel.ObjectName = ConstUtil.LOG_PROCESS_UPDATE;
logModel.Element = ConstUtil.LOG_PROCESS_UPDATE;
try
{
//执行更新
isSucc = EmployeeInfoDBHelper.UpdateEnterInfo(model);
}
catch (Exception ex)
{
//输出日志
WriteSystemLog(userInfo, ex);
}
//更新成功时
if (isSucc)
{
//设置操作成功标识
logModel.Remark = ConstUtil.LOG_PROCESS_SUCCESS;
}
//更新不成功
else
{
//设置操作成功标识
logModel.Remark = ConstUtil.LOG_PROCESS_FAILED;
}
//登陆日志
LogDBHelper.InsertLog(logModel);
//返回
return isSucc;
}
/// <summary>
/// 导出在职人员列表
/// </summary>
/// <param name="searchModel"></param>
/// <param name="ord"></param>
/// <returns></returns>
public static DataTable ExportEmployeeWorkInfo(EmployeeSearchModel model,string BeginDate,string EndDate, string ord)
{
try
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
return EmployeeInfoDBHelper.ExportEmployeeWorkInfo(model,BeginDate,EndDate,ord);
}
catch
{
return null;
}
}
/// <summary>
/// 查询在职部门岗位等相关信息
/// </summary>
/// <returns></returns>
public static DataTable GetWorkEmplInfo(EmployeeSearchModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
//查询并返回人员信息
return EmployeeInfoDBHelper.GetWorkEmplInfo(model);
}
/// <summary>
/// 入职处理更新数据库
/// </summary>
/// <param name="model">人员信息</param>
/// <returns></returns>
public static bool UpdateEnterInfo(EmployeeSearchModel model)
{
#region SQL文拼写
StringBuilder updateSql = new StringBuilder();
updateSql.AppendLine(" UPDATE officedba.EmployeeInfo ");
updateSql.AppendLine(" SET ");
updateSql.AppendLine(" DeptID = @DeptID ");
updateSql.AppendLine(" ,QuarterID = @QuarterID ");
//updateSql.AppendLine(" ,AdminID = @AdminID ");
updateSql.AppendLine(" ,AdminLevelID = @AdminLevelID ");
updateSql.AppendLine(" ,PositionID = @PositionID ");
updateSql.AppendLine(" ,PositionTitle = @PositionTitle ");
updateSql.AppendLine(" ,EnterDate = @EnterDate ");
updateSql.AppendLine(" ,Flag = @Flag ");
updateSql.AppendLine(" ,ModifiedDate = getdate() ");
updateSql.AppendLine(" ,ModifiedUserID = @ModifiedUserID ");
updateSql.AppendLine(" WHERE ");
updateSql.AppendLine(" ID = @EmplID ");
#endregion
//定义更新基本信息的命令
SqlCommand comm = new SqlCommand();
comm.CommandText = updateSql.ToString();
//人员ID
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmplID", model.ID));
//部门
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID", model.Dept));
//岗位
comm.Parameters.Add(SqlHelper.GetParameterFromString("@QuarterID", model.QuarterID));
//行政等级
//comm.Parameters.Add(SqlHelper.GetParameterFromString("@AdminID", model.AdminID));
//岗位职等
comm.Parameters.Add(SqlHelper.GetParameterFromString("@AdminLevelID", model.AdminLevelID));
//职称
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PositionID", model.PositionID));
//职务
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PositionTitle", model.PositionTitle));
//入职时间
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EnterDate", model.EnterDate));
//状态
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Flag", "1"));
//最后修改者
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID));
//执行更新并设置更新结果
return SqlHelper.ExecuteTransWithCommand(comm);
}
/// <summary>
/// 查询提成工资项信息
/// </summary>
/// <param name="model">查询条件</param>
/// <param name="itemNo">提成项目编号</param>
/// <returns></returns>
public static string GetCommissionSalaryInfo(EmployeeSearchModel model, string itemNo)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
//获取满足条件的人员信息查询
DataTable dtEmplInfo = CommissionSalaryDBHelper.GetEmplInfo(model, itemNo);
//变量定义
StringBuilder sbCommissionSalaryInfo = new StringBuilder();
//数据存在时，设置数据
if (dtEmplInfo != null && dtEmplInfo.Rows.Count > 0)
{
//设置记录数
model.RecordCount = dtEmplInfo.Rows.Count.ToString();
//遍历员工获取提成工资信息
for (int i = 0; i < dtEmplInfo.Rows.Count; i++)
{
//获取人员ID
string emplID = GetSafeData.GetStringFromInt(dtEmplInfo.Rows[i], "EmployeeID");
//获取人员提成工资
DataTable dtCommItemInfo = CommissionSalaryDBHelper.GetCommissionItemInfo(model.CompanyCD
, emplID, itemNo, model.StartDate, model.EndDate);
//变量定义
decimal totalMoney = 0;
int commItemCount = 0;
int totalRowCount = 0;
StringBuilder sbEmplFirstRow = new StringBuilder();
StringBuilder sbEmplNotFirstRow = new StringBuilder();
//获取工资项目总数
if (dtCommItemInfo != null && dtCommItemInfo.Rows.Count > 0) commItemCount = dtCommItemInfo.Rows.Count;
#region 生成每个提成项目的具体信息
//遍历工资项
for (int j = 0; j < commItemCount; j++)
{
//变量定义
decimal timeTotalMoney = 0;
StringBuilder sbTimeFirstRow = new StringBuilder();
StringBuilder sbTimeNotFirstRow = new StringBuilder();
//获取工资项编号
string commItemNo = GetSafeData.ValidateDataRow_String(dtCommItemInfo.Rows[j], "ItemNo");
//获取工资内容
DataTable dtCommissionSalaryInfo = CommissionSalaryDBHelper.GetCommissionSalaryInfo(model.CompanyCD
, emplID, commItemNo, model.StartDate, model.EndDate);
//获取工资内容总数
int commSalaryCount = 0;
if (dtCommissionSalaryInfo != null && dtCommissionSalaryInfo.Rows.Count > 0) commSalaryCount = dtCommissionSalaryInfo.Rows.Count;
//提成率
decimal rate = decimal.Parse(StringUtil.TrimZero(GetSafeData.GetStringFromDecimal(dtCommItemInfo.Rows[j], "Rate"))) * 100;
StringBuilder sbItemTemp = new StringBuilder();
//
//工资编号
sbItemTemp.AppendLine("<td class='tdColInputCenter' rowspan='" + commSalaryCount.ToString() + "'>"
+ "<input type='hidden' id='txtSalaryNo_" + (i + 1).ToString() + "_" + (j + 1).ToString() + "' value='" + commItemNo + "' />"
+ "<input type='hidden' id='txtSalaryCount_" + (i + 1).ToString() + "_" + (j + 1).ToString() + "' value='"
+ commSalaryCount + "' />"
+ GetSafeData.ValidateDataRow_String(dtCommItemInfo.Rows[j], "ItemName") + "</td>");
//单价
sbItemTemp.AppendLine("<td class='tdColInputCenter' rowspan='" + commSalaryCount.ToString() + "' id='tdUnitPrice_"
+ (i + 1).ToString() + "_" + (j + 1).ToString() + "' >" + rate + "</td>");
//第一行时
if (j == 0)
{
sbEmplFirstRow.AppendLine(sbItemTemp.ToString());
}
else
{
//行开始
sbEmplNotFirstRow.AppendLine("<tr>");
sbEmplNotFirstRow.AppendLine(sbItemTemp.ToString());
}
//遍历所有项目获取具体
for (int x = 0; x < commSalaryCount; x++)
{
StringBuilder sbTemp = new StringBuilder();
//时长
string timeCount = StringUtil.TrimZero(GetSafeData.GetStringFromDecimal(dtCommissionSalaryInfo.Rows[x], "Amount"));
string FlagName = StringUtil.TrimZero(GetSafeData.ValidateDataRow_String(dtCommissionSalaryInfo.Rows[x], "FlagName"));
sbTemp.AppendLine("<td class='tdColInputCenter' >"
+ "<input type='text' class='tdinput' readonly='readonly' id='txtAmount_" + (i + 1).ToString() + "_" + (j + 1).ToString()
+ "_" + (x + 1).ToString() + "' value='" + timeCount + "' style='width=95%;background-color:#FFFFE0;'"
+ " onchange='Number_round(this,\"2\");' maxlength = '10' />" + "</td>");
//来源
sbTemp.AppendLine("<td class='tdColInputCenter' >"
+ "<input type='text' class='tdinput' id='txtFlag_" + (i + 1).ToString() + "_" + (j + 1).ToString()
+ "_" + (x + 1).ToString() + "' value='" + FlagName + "' style='width=95%'"
+ " readonly />" + "</td>");
//金额
string salaryMoney = StringUtil.TrimZero(GetSafeData.GetStringFromDecimal(dtCommissionSalaryInfo.Rows[x], "SalaryMoney"));
sbTemp.AppendLine("<td class='tdColInputCenter' id='tdAmountMoney_" + (i + 1).ToString() + "_" + (j + 1).ToString()
+ "_" + (x + 1).ToString() + "' >" + salaryMoney + "</td>");
//日期
sbTemp.AppendLine("<td class='tdColInputCenter' id='tdSalaryDate_" + (i + 1).ToString() + "_" + (j + 1).ToString()
+ "_" + (x + 1).ToString() + "'>" + GetSafeData.ValidateDataRow_String(dtCommissionSalaryInfo.Rows[x], "CommDate") + "</td>");
//计算小计金额
timeTotalMoney += decimal.Parse(salaryMoney);
if (x == 0)
{
//人员的第一行信息时
if (j == 0)
{
sbEmplFirstRow.AppendLine(sbTemp.ToString());
}
//不是人员的第一行信息，但是是提成工资项的第一行信息
else
{
sbTimeFirstRow.AppendLine(sbTemp.ToString());
}
}
else
{
//行开始
sbTimeNotFirstRow.AppendLine("<tr>");
sbTimeNotFirstRow.AppendLine(sbTemp.ToString());
//行结束
sbTimeNotFirstRow.AppendLine("</tr>");
}
}
//工资小计
if (j == 0)
{
sbEmplFirstRow.AppendLine("<td id='tdAmountTotalMoney_" + (i + 1).ToString() + "_" + (j + 1).ToString()
+ "' class='tdColInputCenter' rowspan='" + commSalaryCount.ToString() + "'>" + timeTotalMoney.ToString() + "</td>");
}
else
{
sbTimeFirstRow.AppendLine("<td id='tdAmountTotalMoney_" + (i + 1).ToString() + "_" + (j + 1).ToString()
+ "' class='tdColInputCenter' rowspan='" + commSalaryCount.ToString() + "'>" + timeTotalMoney.ToString() + "</td></tr>");
}
//计算合计金额
totalMoney += timeTotalMoney;
//行数统计
totalRowCount += commSalaryCount;
//非第一行时，添加到非第一行的信息里
sbEmplNotFirstRow.AppendLine(sbTimeFirstRow.ToString() + sbTimeNotFirstRow.ToString());
}
#endregion
//行开始
sbCommissionSalaryInfo.AppendLine("<tr>");
//选择
sbCommissionSalaryInfo.AppendLine("<td class='tdColInputCenter' rowspan='" + totalRowCount.ToString() + "'>"
+ "<input id='chkSelect_" + (i + 1).ToString() + "' name='chkSelect' value='" + GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "EmployeeNo") + "' type='checkbox' onpropertychange='getChage(this)' />" + "</td>");
//人员编号
sbCommissionSalaryInfo.AppendLine("<td class='tdColInputCenter' rowspan='" + totalRowCount.ToString() + "'>"
+ "<input type='hidden' id='txtEmplID_" + (i + 1).ToString() + "' value='" + emplID + "' />"
+ "<input type='hidden' id='txtItemCount_" + (i + 1).ToString() + "' value='" + commItemCount + "' />"
+ GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "EmployeeNo") + "</td>");
//人员姓名
sbCommissionSalaryInfo.AppendLine("<td class='tdColInputCenter' rowspan='" + totalRowCount.ToString() + "'>"
+ GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "EmployeeName") + "</td>");
//所在部门
sbCommissionSalaryInfo.AppendLine("<td class='tdColInputCenter' rowspan='" + totalRowCount.ToString() + "'>"
+ GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "DeptName") + "</td>");
//岗位名称
sbCommissionSalaryInfo.AppendLine("<td class='tdColInputCenter' rowspan='" + totalRowCount.ToString() + "'>"
+ GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "QuarterName") + "</td>");
//岗位职等
sbCommissionSalaryInfo.AppendLine("<td class='tdColInputCenter' rowspan='" + totalRowCount.ToString() + "'>"
+ GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "AdminLevelName") + "</td>");
//具体的提成项目信息
sbCommissionSalaryInfo.AppendLine(sbEmplFirstRow.ToString());
//工资合计
sbCommissionSalaryInfo.AppendLine("<td class='tdColInputCenter' id='tdTotalMoney_" + (i + 1).ToString() + "' rowspan='" + totalRowCount.ToString() + "'>" + totalMoney.ToString() + "</td>");
//行结束
sbCommissionSalaryInfo.AppendLine("</tr>");
//具体的提成项目信息
sbCommissionSalaryInfo.AppendLine(sbEmplNotFirstRow.ToString());
}
}
return sbCommissionSalaryInfo.ToString();
}
/// <summary>
/// 导出人员储备列表
/// </summary>
/// <param name="searchModel"></param>
/// <returns></returns>
public static DataTable ExportEmployeeReserve(EmployeeSearchModel searchModel)
{
#region 查询SQL拼写
//查询SQL拼写
//StringBuilder searchSql = new StringBuilder();
//searchSql.AppendLine("SELECT A.ID ");
//searchSql.AppendLine(" ,A.EmployeeNo ");
//searchSql.AppendLine(" ,ISNULL(A.PYShort, '') AS PYShort ");
//searchSql.AppendLine(" ,A.EmployeeName ");
//searchSql.AppendLine(" ,Case A.Sex ");
//searchSql.AppendLine(" When '1' then '男' ");
//searchSql.AppendLine(" When '2' then '女' ");
//searchSql.AppendLine(" else '' ");
//searchSql.AppendLine(" End as SexName ");
//searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),A.Birth, 21), '') AS Birth ");
//searchSql.AppendLine(" ,CASE WHEN A.Birth is null THEN '' ELSE ");
//searchSql.AppendLine("CONVERT(VARCHAR(3),DATEDIFF(YEAR,A.Birth,getdate())) + ' 岁' END AS Age ");
//searchSql.AppendLine(" ,ISNULL(A.Origin, '') AS Origin ");
////searchSql.AppendLine(" ,isnull(datediff(year,WorkTime,GETDATE())+1,'') TotalSeniority ");
//searchSql.AppendLine(" ,CASE ");
//searchSql.AppendLine(" WHEN A.WorkTime is null THEN '' ");
//searchSql.AppendLine(" ELSE ");
//searchSql.AppendLine(" isnull(datediff(year,WorkTime,GETDATE())+1,'') ");
//searchSql.AppendLine(" END AS TotalYear ");
//searchSql.AppendLine(" ,ISNULL(c.QuarterName, '') AS PositionTitle ");
//searchSql.AppendLine(" ,ISNULL(B.TypeName, '') AS ProfessionalName ");
//searchSql.AppendLine(" FROM officedba.EmployeeInfo A LEFT outer join officedba.CodePublicType B on");
//searchSql.AppendLine(" A.CompanyCD = B.CompanyCD AND A.Professional = B.ID");
//searchSql.AppendLine(" LEFT join officedba.DeptQuarter c on c.id = A.QuarterID");
//searchSql.AppendLine("WHERE ");
//searchSql.AppendLine(" A.CompanyCD = @CompanyCD ");
//searchSql.AppendLine(" AND A.Flag = @Flag ");
#endregion
#region 查询SQL拼写
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine("SELECT A.ID ");
searchSql.AppendLine(" ,ISNULL(A.EmployeeNum, '') AS EmployeeNum,ISNULL(CONVERT(VARCHAR(10),A.EnterDate,21),'') AS EnterDate");
searchSql.AppendLine(" ,A.EmployeeNo ");
searchSql.AppendLine(" ,ISNULL(A.PYShort, '') AS PYShort ");
searchSql.AppendLine(" ,A.CompanyCD ");
searchSql.AppendLine(" ,A.CardID ");
searchSql.AppendLine(" ,A.SafeguardCard ");
searchSql.AppendLine(" ,isnull(A.EmployeeName,'') EmployeeName ");
searchSql.AppendLine(" ,A.UsedName ");
searchSql.AppendLine(" ,A.NameEn ");
searchSql.AppendLine(" ,(case A.Sex when '1' then '男' else '女' end)SexName ");
searchSql.AppendLine(" ,isnull(convert(varchar(10),Birth,23),'') Birth ");
searchSql.AppendLine(" ,A.Account ");
searchSql.AppendLine(" ,(case A.AccountNature when '1' then '非农业' when '2' then '农业' else '' end) AccountNature ");
searchSql.AppendLine(" ,A.CountryID ");
searchSql.AppendLine(" ,A.Height ");
searchSql.AppendLine(" ,CASE WHEN A.Birth is null THEN '' ELSE ");
searchSql.AppendLine(" CONVERT(VARCHAR(3),DATEDIFF(YEAR,A.Birth,getdate())) + ' 岁' END AS Age ");
searchSql.AppendLine(" ,CONVERT(varchar(8),A.Weight) Weight ");
searchSql.AppendLine(" ,CONVERT(varchar(8),A.Sight) Sight ");
searchSql.AppendLine(" ,A.Degree ");
searchSql.AppendLine(" ,A.DocuType ");
searchSql.AppendLine(" ,ISNULL(A.Origin, '') AS Origin ");
searchSql.AppendLine(" ,A.Telephone ");
searchSql.AppendLine(" ,A.Mobile ");
searchSql.AppendLine(" ,A.EMail ");
searchSql.AppendLine(" ,A.OtherContact ");
searchSql.AppendLine(" ,A.HomeAddress ");
searchSql.AppendLine(" ,(case A.HealthStatus when '0' then '一般' when '1' then '良好' when '2' then '很好' else '' end) HealthStatus");
searchSql.AppendLine(" ,A.GraduateSchool ");
searchSql.AppendLine(" ,A.Features ");
searchSql.AppendLine(" ,A.ComputerLevel ");
searchSql.AppendLine(" ,(case A.ForeignLanguageLevel1 when '1' then '一般' when '2' then '熟练' when '3' then '精通' end)ForeignLanguageLevel1 ");
searchSql.AppendLine(" ,(case A.ForeignLanguageLevel2 when '1' then '一般' when '2' then '熟练' when '3' then '精通' end)ForeignLanguageLevel2 ");
searchSql.AppendLine(" ,(case A.ForeignLanguageLevel3 when '1' then '一般' when '2' then '熟练' when '3' then '精通' end)ForeignLanguageLevel3 ");
searchSql.AppendLine(" ,A.WorkTime ");
searchSql.AppendLine(" ,A.TotalSeniority,A.PhotoURL,ISNULL(A.PositionTitle, '') AS PositionTitle,'人才储备' Flag ");
searchSql.AppendLine(" ,A.ModifiedDate ,A.ModifiedUserID,A.QuarterID ");
searchSql.AppendLine(" ,A.AdminLevelID ,A.DeptID ,ISNULL(C.DeptName, '') AS DeptName ");
searchSql.AppendLine(" ,A.Resume,A.ProfessionalDes,q.TypeName NationalName ");
searchSql.AppendLine(" ,ISNULL(d.TypeName, '') AS PositionName,ISNULL(e.QuarterName, '') AS QuarterName,f.TypeName MarriageStatus ");
searchSql.AppendLine(" ,g.TypeName CultureLevel,isnull(h.TypeName,'') ProfessionalName,i.TypeName Landscape ");
searchSql.AppendLine(" ,j.TypeName Religion,k.TypeName CountryName,l.TypeName ForeignLanguage11 ");
searchSql.AppendLine(" ,m.TypeName ForeignLanguage12,o.TypeName ForeignLanguage13,p.TypeName AdminLevelName ");
searchSql.AppendLine(" FROM officedba.EmployeeInfo A ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.DeptInfo C on C.id = A.DeptID ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType d on d.id = A.PositionID ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.DeptQuarter e on e.id = A.QuarterID ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType f on f.id = A.MarriageStatus ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType g on g.id = A.CultureLevel ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType h on h.id = A.Professional ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType i on i.id = A.Landscape ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType j on j.id = A.Religion ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType k on k.id = A.CountryID ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType l on l.id = A.ForeignLanguage1 ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType m on m.id = A.ForeignLanguage2 ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType o on o.id = A.ForeignLanguage3 ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType p on p.id = A.AdminLevelID ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.CodePublicType q on q.id = A.NationalID ");
searchSql.AppendLine(" WHERE ");
searchSql.AppendLine(" A.CompanyCD = @CompanyCD ");
searchSql.AppendLine(" AND A.Flag = @Flag and (A.delFlag <> @delFlag or A.delFlag is null) ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", searchModel.CompanyCD));
//添加人才储备参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Flag", ConstUtil.EMPLOYEE_FLAG_TALENT));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@delFlag", "1"));
#region 页面输入条件
//编号
if (!string.IsNullOrEmpty(searchModel.EmployeeNo))
{
searchSql.AppendLine(" AND A.EmployeeNo LIKE '%' + @EmployeeNo + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeNo", searchModel.EmployeeNo));
}
//拼音缩写
if (!string.IsNullOrEmpty(searchModel.PYShort))
{
searchSql.AppendLine(" AND A.PYShort LIKE '%' + @PYShort + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PYShort", searchModel.PYShort));
}
//姓名
if (!string.IsNullOrEmpty(searchModel.EmployeeName))
{
searchSql.AppendLine(" AND A.EmployeeName LIKE '%' + @EmployeeName + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeName", searchModel.EmployeeName));
}
//性别
if (!string.IsNullOrEmpty(searchModel.SexID))
{
searchSql.AppendLine(" AND A.Sex = @Sex ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Sex", searchModel.SexID));
}
//文化程度
if (!string.IsNullOrEmpty(searchModel.CultureLevel))
{
searchSql.AppendLine(" AND A.CultureLevel = @CultureLevel ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CultureLevel", searchModel.CultureLevel));
}
//专业
if (!string.IsNullOrEmpty(searchModel.ProfessionalID))
{
searchSql.AppendLine(" AND A.Professional = @ProfessionalID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProfessionalID", searchModel.ProfessionalID));
}
//毕业院校
if (!string.IsNullOrEmpty(searchModel.SchoolName))
{
searchSql.AppendLine(" AND A.GraduateSchool LIKE '%' + @GraduateSchool + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@GraduateSchool", searchModel.SchoolName));
}
//应聘岗位
if (!string.IsNullOrEmpty(searchModel.PositionTitle))
{
searchSql.AppendLine(" AND c.QuarterName LIKE '%' + @PositionTitle + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PositionTitle", searchModel.PositionTitle));
}
//工龄
if (!string.IsNullOrEmpty(searchModel.TotalSeniority))
{
searchSql.AppendLine(" AND isnull(datediff(year,WorkTime,GETDATE())+1,'') = @TotalSeniority ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TotalSeniority", searchModel.TotalSeniority));
}
#endregion
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
public static DataTable GetOutEmployeeInfo(EmployeeSearchModel model)
{
//int year = DateTime.Now.Year;
//int month = DateTime.Now.Month;
//if (month == 1)
//{
// year = year - 1;
// month = 12;
//}
//else
//{
// month = month - 1;
//}
//DateTime nowll=new DateTime (year ,month ,1);
#region 查询SQL拼写
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
//searchSql.AppendLine(" SELECT ");
searchSql.AppendLine(" select A.ID ");
searchSql.AppendLine(",A.EmployeeNo ");
searchSql.AppendLine(",A.EmployeeNum ");
searchSql.AppendLine(",A.CompanyCD ");
searchSql.AppendLine(",A.EmployeeName ");
searchSql.AppendLine(",A.QuarterID ");
searchSql.AppendLine(",C.QuarterName ");
searchSql.AppendLine(" ,A.DeptID ");
searchSql.AppendLine(" ,B.DeptName ");
searchSql.AppendLine(" ,A.AdminLevelID ");
searchSql.AppendLine(" ,D.TypeName AS AdminLevelName ");
//searchSql.AppendLine(" ,e.OutDate ");
searchSql.AppendLine("from officedba.EmployeeInfo A ");
searchSql.AppendLine("left outer join officedba.MoveNotify e ");
searchSql.AppendLine("on E.CompanyCD=A.CompanyCD and A.id=e.EmployeeID and e.BillStatus='2' ");
searchSql.AppendLine("LEFT JOIN officedba.DeptInfo B ");
searchSql.AppendLine(" ON B.companyCD=A.companyCD AND B.ID = A.DeptID ");
searchSql.AppendLine("LEFT JOIN officedba.DeptQuarter C ");
searchSql.AppendLine(" ON C.companyCD=A.companyCD AND C.ID = A.QuarterID ");
searchSql.AppendLine("LEFT JOIN officedba.CodePublicType D ");
searchSql.AppendLine(" ON D.companyCD=A.companyCD AND D.ID = A.AdminLevelID ");
searchSql.AppendLine(" WHERE ");
searchSql.AppendLine(" A.CompanyCD = @CompanyCD ");
searchSql.AppendLine(" AND A.Flag = @Flag and e.OutDate > @OutStartDate and e.OutDate < @OutEndDate ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//公司代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
//comm.Parameters.Add(SqlHelper.GetParameterFromString("@OutDate", nowll.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@OutStartDate", model .StartDate ));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@OutEndDate", model .EndDate ));
//在职标识
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Flag", "3"));
#region 页面查询条件
//编号
if (!string.IsNullOrEmpty(model.EmployeeNo))
{
searchSql.AppendLine(" AND A.EmployeeNo LIKE '%' + @EmployeeNo + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeNo", model.EmployeeNo));
}
//工号
if (!string.IsNullOrEmpty(model.EmployeeNum))
{
searchSql.AppendLine(" AND A.EmployeeNum = @EmployeeNum ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeNum", model.EmployeeNum));
}
//姓名
if (!string.IsNullOrEmpty(model.EmployeeName))
{
searchSql.AppendLine(" AND A.EmployeeName LIKE '%' + @EmployeeName + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeName", model.EmployeeName));
}
//所在岗位
if (!string.IsNullOrEmpty(model.QuarterID))
{
searchSql.AppendLine(" AND A.QuarterID = @QuarterID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@QuarterID", model.QuarterID));
}
//岗位职等
if (!string.IsNullOrEmpty(model.AdminLevelID))
{
searchSql.AppendLine(" AND A.AdminLevelID = @AdminLevelID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@AdminLevelID", model.AdminLevelID));
}
#endregion
//设置排序
searchSql.AppendLine(" ORDER BY A.DeptID, A.EnterDate ");
//设定comm的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
/// <summary>
/// 查询待入职人员列表
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public static DataTable SearchWaitEnterInfo(EmployeeSearchModel searchModel, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
#region 查询SQL拼写
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine(" SELECT ");
searchSql.AppendLine(" A.ID ");
searchSql.AppendLine(" ,A.EmployeeNo ");
searchSql.AppendLine(" ,ISNULL(A.CardID, '') AS CardID ");
searchSql.AppendLine(" ,A.EmployeeName ");
searchSql.AppendLine(" ,CASE A.Sex ");
searchSql.AppendLine(" WHEN '1' THEN '男' ");
searchSql.AppendLine(" WHEN '2' THEN '女' ");
searchSql.AppendLine(" ELSE '' ");
searchSql.AppendLine(" END AS SexName ");
searchSql.AppendLine(" ,CASE A.Flag WHEN '2' THEN '人才储备' WHEN '3' THEN '离职' END Flag ");
searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR,A.QuarterID), '') AS QuarterID ");
searchSql.AppendLine(" ,ISNULL(C.QuarterName, '') AS QuarterName ");
searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),A.Birth, 21), '') AS Birth ");
searchSql.AppendLine(" ,CASE WHEN A.Birth IS NULL THEN '' ");
searchSql.AppendLine(" ELSE CONVERT(VARCHAR(3),DATEDIFF(YEAR,A.Birth,getdate())) + ' 岁' ");
searchSql.AppendLine(" END AS Age ");
searchSql.AppendLine(" ,ISNULL(A.HomeAddress, '') AS HomeAddress ");
searchSql.AppendLine(" ,ISNULL(A.Telephone, '') + ' ' + ISNULL(A.Mobile, '') AS Contact ");
searchSql.AppendLine(" ,ISNULL(D.TypeName, '') AS CultureLevelName ");
searchSql.AppendLine(" ,ISNULL(A.GraduateSchool, '') AS SchoolName ");
searchSql.AppendLine(" ,ISNULL(E.TypeName, '') AS ProfessionalName ");
searchSql.AppendLine(" ,CASE B.FinalResult ");
searchSql.AppendLine(" WHEN '0' THEN '不予考虑' WHEN '1' THEN '拟予试用' ");
searchSql.AppendLine(" ELSE '' ");
searchSql.AppendLine(" END AS FinalResult ");
searchSql.AppendLine(" ,A.ModifiedDate ");
searchSql.AppendLine(" FROM ");
searchSql.AppendLine(" officedba.EmployeeInfo A ");
searchSql.AppendLine(" LEFT JOIN officedba.RectInterview B ");
searchSql.AppendLine(" ON B.StaffName = A.ID ");
searchSql.AppendLine(" LEFT JOIN officedba.DeptQuarter C ");
searchSql.AppendLine(" ON A.QuarterID = C.ID ");
searchSql.AppendLine(" LEFT JOIN officedba.CodePublicType D ");
searchSql.AppendLine(" ON A.CultureLevel = D.ID ");
searchSql.AppendLine(" LEFT JOIN officedba.CodePublicType E ");
searchSql.AppendLine(" ON A.Professional = E.ID ");
searchSql.AppendLine(" WHERE ");
searchSql.AppendLine(" (A.Flag = '2' OR A.Flag='3') ");
searchSql.AppendLine(" AND A.CompanyCD = @CompanyCD ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//公司代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", searchModel.CompanyCD));
//编号
if (!string.IsNullOrEmpty(searchModel.EmployeeNo))
{
searchSql.AppendLine(" AND A.EmployeeNo LIKE '%' + @EmployeeNo + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeNo", searchModel.EmployeeNo));
}
//姓名
if (!string.IsNullOrEmpty(searchModel.EmployeeName))
{
searchSql.AppendLine(" AND A.EmployeeName LIKE '%' + @EmployeeName + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeName", searchModel.EmployeeName));
}
//性别
if (!string.IsNullOrEmpty(searchModel.SexID))
{
searchSql.AppendLine(" AND A.Sex = @Sex ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Sex", searchModel.SexID));
}
//学历
if (!string.IsNullOrEmpty(searchModel.CultureLevel))
{
searchSql.AppendLine(" AND A.CultureLevel = @CultureLevel ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CultureLevel", searchModel.CultureLevel));
}
//毕业院校
if (!string.IsNullOrEmpty(searchModel.SchoolName))
{
searchSql.AppendLine(" AND A.GraduateSchool LIKE '%' + @GraduateSchool + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@GraduateSchool", searchModel.SchoolName));
}
//应聘岗位
if (!string.IsNullOrEmpty(searchModel.QuarterID))
{
searchSql.AppendLine(" AND A.QuarterID = @QuarterID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@QuarterID", searchModel.QuarterID));
}
//人员类型
if (!string.IsNullOrEmpty(searchModel.Flag))
{
searchSql.AppendLine(" AND A.Flag = @Flag ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Flag", searchModel.Flag));
}
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
//return SqlHelper.ExecuteSearch(comm);
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount);
}
/// <summary>
/// 获取人才储备信息
/// </summary>
/// <returns></returns>
public static DataTable GetReserveInfo(EmployeeSearchModel model)
{
#region 查询语句
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine("SELECT ");
searchSql.AppendLine(" A.ID ");
searchSql.AppendLine(" ,A.EmployeeNo ");
searchSql.AppendLine(" ,A.EmployeeName ");
searchSql.AppendLine(" ,A.ModifiedDate ");
searchSql.AppendLine(" ,ISNULl(B.QuarterName, '') AS QuarterName ");
searchSql.AppendLine(" ,ISNULl(C.TypeName, '') AS ProfessionalName ");
searchSql.AppendLine(" ,ISNULl(D.TypeName, '') AS CultureLevelName ");
searchSql.AppendLine(" ,ISNULl(A.GraduateSchool, '') AS SchoolName ");
searchSql.AppendLine(" ,CASE ");
searchSql.AppendLine(" WHEN A.TotalSeniority IS NULL ");
searchSql.AppendLine(" THEN '' ");
searchSql.AppendLine(" ELSE ");
searchSql.AppendLine(" CONVERT(VARCHAR,A.TotalSeniority) + ' 年' ");
searchSql.AppendLine(" END ");
searchSql.AppendLine(" AS TotalSeniority ");
searchSql.AppendLine(" ,Case A.Sex ");
searchSql.AppendLine(" WHEN '1' then '男' ");
searchSql.AppendLine(" WHEN '2' then '女' ");
searchSql.AppendLine(" ELSE '' ");
searchSql.AppendLine(" End AS SexName ");
searchSql.AppendLine("FROM ");
searchSql.AppendLine(" officedba.EmployeeInfo A ");
searchSql.AppendLine(" LEFT JOIN officedba.DeptQuarter B ");
searchSql.AppendLine(" ON B.companyCD=A.companyCD AND A.QuarterID = B.ID ");
searchSql.AppendLine(" LEFT JOIN officedba.CodePublicType C ");
searchSql.AppendLine(" ON C.companyCD=A.companyCD AND A.Professional = C.ID ");
searchSql.AppendLine(" LEFT JOIN officedba.CodePublicType D ");
searchSql.AppendLine(" ON D.companyCD=A.companyCD AND A.CultureLevel = D.ID ");
searchSql.AppendLine("WHERE ");
searchSql.AppendLine(" A.CompanyCD = @CompanyCD ");
searchSql.AppendLine(" AND A.Flag = @Flag ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//公司代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
//人才储备
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Flag", ConstUtil.EMPLOYEE_FLAG_TALENT));
//编号
if (!string.IsNullOrEmpty(model.EmployeeNo))
{
searchSql.AppendLine(" AND A.EmployeeNo LIKE '%' + @EmployeeNo + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeNo", model.EmployeeNo));
}
//姓名
if (!string.IsNullOrEmpty(model.EmployeeName))
{
searchSql.AppendLine(" AND A.EmployeeName LIKE '%' + @EmployeeName + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeName", model.EmployeeName));
}
//应聘岗位
if (!string.IsNullOrEmpty(model.QuarterID))
{
searchSql.AppendLine(" AND A.QuarterID = @QuarterID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@QuarterID", model.QuarterID));
}
//工作年限
if (!string.IsNullOrEmpty(model.TotalSeniority))
{
searchSql.AppendLine(" AND A.TotalSeniority >= @TotalSeniority ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TotalSeniority", model.TotalSeniority));
}
//学历
if (!string.IsNullOrEmpty(model.CultureLevel))
{
searchSql.AppendLine(" AND A.CultureLevel = @CultureLevel ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CultureLevel", model.CultureLevel));
}
//专业
if (!string.IsNullOrEmpty(model.ProfessionalID))
{
searchSql.AppendLine(" AND A.Professional = @ProfessionalID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProfessionalID", model.ProfessionalID));
}
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
/// <summary>
/// 查询人员信息
/// </summary>
/// <param name="model">查询条件</param>
/// <param name="itemNo">计件项目编号</param>
/// <returns></returns>
public static DataTable GetEmplInfo(EmployeeSearchModel model, string itemNo)
{
#region 查询语句
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine(" SELECT DISTINCT ");
searchSql.AppendLine(" E.EmployeeID AS EmployeeID ");
searchSql.AppendLine(" ,A.EmployeeNo AS EmployeeNo ");
searchSql.AppendLine(" ,A.EmployeeNum AS EmployeeNum ");
searchSql.AppendLine(" ,A.EmployeeName AS EmployeeName ");
searchSql.AppendLine(" ,C.QuarterName AS QuarterName ");
searchSql.AppendLine(" ,B.DeptName AS DeptName ");
searchSql.AppendLine(" ,D.TypeName AS AdminLevelName ");
searchSql.AppendLine(" FROM ");
searchSql.AppendLine(" officedba.PieceworkSalary E ");
searchSql.AppendLine(" LEFT JOIN officedba.EmployeeInfo A ");
searchSql.AppendLine(" ON A.CompanyCD=E.CompanyCD AND E.EmployeeID = A.ID ");
searchSql.AppendLine(" LEFT JOIN officedba.DeptInfo B ");
searchSql.AppendLine(" ON B.CompanyCD=E.CompanyCD AND A.DeptID = B.ID ");
searchSql.AppendLine(" LEFT JOIN officedba.DeptQuarter C ");
searchSql.AppendLine(" ON C.CompanyCD=A.CompanyCD AND A.QuarterID = C.ID ");
searchSql.AppendLine(" LEFT JOIN officedba.CodePublicType D ");
searchSql.AppendLine(" ON D.CompanyCD=A.CompanyCD AND A.AdminLevelID = D.ID ");
searchSql.AppendLine(" WHERE ");
searchSql.AppendLine(" E.CompanyCD = @CompanyCD ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//公司代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
#region 页面条件
//编号
if (!string.IsNullOrEmpty(model.EmployeeNo))
{
searchSql.AppendLine(" AND A.EmployeeNo LIKE @EmployeeNo ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeNo", "%" + model.EmployeeNo + "%"));
}
//姓名
if (!string.IsNullOrEmpty(model.EmployeeName))
{
searchSql.AppendLine(" AND A.EmployeeName LIKE @EmployeeName");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeName", "%"+model.EmployeeName+"%"));
}
//岗位
if (!string.IsNullOrEmpty(model.QuarterID))
{
searchSql.AppendLine(" AND A.QuarterID = @QuarterID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@QuarterID", model.QuarterID));
}
//计件项目
if (!string.IsNullOrEmpty(itemNo))
{
searchSql.AppendLine(" AND E.ItemNo = @ItemNo ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ItemNo", itemNo));
}
//开始时间
if (!string.IsNullOrEmpty(model.StartDate))
{
searchSql.AppendLine(" AND CONVERT(VARCHAR(10), E.PieceDate,21) >= @StartDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartDate", model.StartDate));
}
//结束时间
if (!string.IsNullOrEmpty(model.EndDate))
{
searchSql.AppendLine(" AND CONVERT(VARCHAR(10), E.PieceDate,21) <= @EndDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndDate", model.EndDate));
}
#endregion
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
protected void btnImport_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
XBase.Model.Office.HumanManager.EmployeeSearchModel searchModel = new XBase.Model.Office.HumanManager.EmployeeSearchModel();//获取数据
searchModel.EmployeeNo = txtEmployeeNo.Text;//编号
searchModel.EmployeeName = txtEmployeeName.Text;//姓名
searchModel.SexID = ddlSex.SelectedValue.Trim();//性别
searchModel.CultureLevel =((DropDownList)ddlCulture.Controls[0]).SelectedValue.Trim();//学历
searchModel.SchoolName =txtSchoolName.Text;//毕业院校
searchModel.QuarterID = ddlQuarter.SelectedValue.Trim();//应聘岗位
searchModel.Flag = DDLFlag.SelectedValue.Trim();//应聘岗位
int TotalCount = 0;
DataTable dt = XBase.Business.Office.HumanManager.EmployeeInfoBus.SearchWaitEnterInfo(searchModel, 1, 10000000, "EmployeeNo", ref TotalCount);//查询数据
//导出标题
string headerTitle = "人员编号|姓名|性别|应聘岗位|联系方式|学历|毕业院校|专业|人员类型|复试结果";
string[] header = headerTitle.Split('|');
//导出标题所对应的列字段名称
string columnFiled = "EmployeeNo|EmployeeName|SexName|QuarterName|Contact|CultureLevelName|SchoolName|ProfessionalName|Flag|FinalResult";
string[] field = columnFiled.Split('|');
XBase.Common.OutputToExecl.ExportToTable(this.Page, dt, header, field, "未入职人员列表");
}
