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, "未入职人员列表"); }