public void GetAllWithPagingTest(int page, EmployeeSearchModel searchModel, List <SearchFieldMutator <Employee, EmployeeSearchModel> > searchFieldMutators, int resultCount) { using (var context = new EmployeeDbContext(_fixure.DbContextOptions)) { context.Database.OpenConnection(); context.Database.EnsureDeleted(); context.Database.EnsureCreated(); // arrange if (!context.Employees.Any()) { context.AddRange(DBSeeder.GetDummyEmployeeList()); context.SaveChanges(); } var employeeRepository = new EmployeeRepository(context); var skipSize = AppConstants.PageSize * (page - 1); var takeSize = AppConstants.PageSize; // act var employees = employeeRepository.GetAll(skipSize, takeSize, searchModel, searchFieldMutators)?.ToList(); // assert Assert.NotNull(employees); Assert.NotEmpty(employees); Assert.Equal(resultCount, employees.Count); } }
/// <inheritdoc/> public List <EmployeeDto> GetEmployees(int?page, EmployeeSearchModel searchModel) { IEnumerable <Employee> employeeEntities; // filter criteria - statregy design pattern var searchFieldMutators = new List <SearchFieldMutator <Employee, EmployeeSearchModel> >(); searchFieldMutators.Add(new SearchFieldMutator <Employee, EmployeeSearchModel>(search => !string.IsNullOrWhiteSpace(search.FirstName), (query, search) => query.Where(e => e.FirstName.Contains(search.FirstName)))); searchFieldMutators.Add(new SearchFieldMutator <Employee, EmployeeSearchModel>(search => !string.IsNullOrWhiteSpace(search.LastName), (query, search) => query.Where(e => e.FirstName.Contains(search.LastName)))); if (page.HasValue && page.Value >= 1) { var skipSize = AppConstants.PageSize * (page.Value - 1); var takeSize = AppConstants.PageSize; employeeEntities = _employeeRepository.GetAll(skipSize, takeSize, searchModel, searchFieldMutators); } else { employeeEntities = _employeeRepository.GetAll(searchModel, searchFieldMutators); } var employeeDtoList = _autoMapper.Map <List <EmployeeDto> >(employeeEntities); return(employeeDtoList); }
public async Task GetBySearchCriteriaAsyncShouldWorkCorrectlyWithMiddleName() { var options = new DbContextOptionsBuilder <ApplicationDbContext>() .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString()).Options; var dbContext = new ApplicationDbContext(options); var userRepo = new EfDeletableEntityRepository <ApplicationUser>(dbContext); var service = new EmployeesService(userRepo); await userRepo.AddAsync(new ApplicationUser { FirstName = "ivan", LastName = "ivanov", MiddleName = "ivanov", EGN = "1234567890", }); await userRepo.SaveChangesAsync(); var model = new EmployeeSearchModel { MiddleName = "ivanov", }; var customers = await service.GetBySearchCriteriaAsync <EmployeeModel, EmployeeSearchModel>(model); Assert.Single(customers); Assert.Collection( customers, x => Assert.Equal("ivanov", x.MiddleName)); }
public async Task <List <EmployeeModel> > GetEmployeesDetailsOnSearch(EmployeeSearchModel employeeSearchModel) { List <EmployeeModel> employeeDetails = new List <EmployeeModel>(); employeeDetails = await Task.Run(() => this._IEmployeeManageRepository .GetEmployeesDetailsOnSearch(employeeSearchModel).Result); return(employeeDetails); }
/// <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="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)); }
/// <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> /// <returns></returns> public static DataTable GetWorkEmplInfo(EmployeeSearchModel model) { //获取登陆用户信息 UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //设置公司代码 model.CompanyCD = userInfo.CompanyCD; //查询并返回人员信息 return(EmployeeInfoDBHelper.GetWorkEmplInfo(model)); }
/// <inheritdoc/> public IEnumerable <Employee> GetAll(EmployeeSearchModel searchModel, List <SearchFieldMutator <Employee, EmployeeSearchModel> > searchFieldMutators) { var query = _context.Employees.AsQueryable(); foreach (var searchFieldMutator in searchFieldMutators) { query = searchFieldMutator.Apply(searchModel, query); } return(query.OrderBy(e => e.Id).AsEnumerable()); }
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.ContractKind = ""; //request.QueryString["ContractKind"].Trim();//工种 model.AdminLevelID = ""; // request.QueryString["AdminLevel"].Trim(); //行政等级 model.QuarterID = ddlQuarter_ddlCodeType.SelectedItem.Value; //岗位 model.PositionID = ddlPosition.SelectedValue; //职称 string BeginDate = txtStartDate.Value.Trim(); //入职开始时间 string EndDate = txtEnterEndDate.Value.Trim(); DataTable dt = EmployeeInfoBus.ExportEmployeeWorkInfo(model, BeginDate, EndDate, ord); 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", "Sex", "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>"); } }
public static DataTable SearchEmplInfo(EmployeeSearchModel model) { //查询并返回人员信息 //获取登陆用户信息 UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //设置公司代码 model.CompanyCD = userInfo.CompanyCD; //执行查询并返回值 return(SalaryItemDBHelper.SearchEmplInfo(model)); }
public IActionResult List() { if (!_permissionService.Authorize(EmployeePermissionProvider.ManageEmployees)) { return(AccessDeniedView()); } var model = new EmployeeSearchModel(); model.SetGridPageSize(); return(View($"{Route}{nameof(List)}.cshtml", model)); }
public ActionResult LoadAllData(DTParameters param, EmployeeSearchModel model) { var SearchValue = Request.Form.GetValues("search[value]")[0]; var parameters = param.GetSearchParameters(); parameters.SearchText = SearchValue; var listResult = _employeeService.GetAll(parameters, model); var result = GetListResult <EmployeeListModel>(param, listResult); return(Json(result)); }
/// <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); }
public async Task <IEnumerable <EmployeeSearchViewModel> > GetEmployeeDetailsForSearch(EmployeeSearchViewModel employeeSearchViewModel) { IEnumerable <EmployeeSearchViewModel> employeeSearchResult = new List <EmployeeSearchViewModel>(); EmployeeSearchModel employeeSearchModel = new EmployeeSearchModel(); employeeSearchModel = _mapper.Map <EmployeeSearchModel>(employeeSearchViewModel); var employeSearchDetails = await this._IEmployeeManageService .GetEmployeesDetailsForSearch(employeeSearchModel); employeeSearchResult = _mapper.Map <List <EmployeeSearchViewModel> >(employeSearchDetails); return(employeeSearchResult); }
/// <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); } }
public async Task <IActionResult> Products_Read([DataSourceRequest] DataSourceRequest request , EmployeeSearchViewModel employeeSearchViewModel) { EmployeeSearchModel employeeSearchModel = new EmployeeSearchModel(); employeeSearchModel = _mapper.Map <EmployeeSearchModel>(employeeSearchViewModel); var employeeDetails = await this._IEmployeeManageService.GetEmployeesDetailsOnSearch(employeeSearchModel); List <EmployeeViewModel> EmployeesViewModel = new List <EmployeeViewModel>(); EmployeesViewModel = _mapper.Map <List <EmployeeViewModel> >(employeeDetails); return(Json(EmployeesViewModel.ToDataSourceResult(request))); }
public static IEnumerable <object[]> GetTestDataForGetAllWithoutPagingTest() { var searchModel = new EmployeeSearchModel() { FirstName = "Foo", LastName = "Bar" }; var searchFieldMutators = new List <SearchFieldMutator <Employee, EmployeeSearchModel> >(); searchFieldMutators.Add(new SearchFieldMutator <Employee, EmployeeSearchModel>(search => true, (query, search) => query.Where(e => e.FirstName.Contains(search.FirstName)))); searchFieldMutators.Add(new SearchFieldMutator <Employee, EmployeeSearchModel>(search => true, (query, search) => query.Where(e => e.LastName.Contains(search.LastName)))); yield return(new object[] { searchModel, searchFieldMutators, 1 }); var searchFieldMutators2 = new List <SearchFieldMutator <Employee, EmployeeSearchModel> >(); yield return(new object[] { searchModel, searchFieldMutators2, DBSeeder.GetDummyEmployeeList().Count }); }
public static IEnumerable <object[]> GetTestDataForGetAllWithPagingTest() { var searchModel = new EmployeeSearchModel() { FirstName = "Foo", LastName = "Bar" }; var searchFieldMutators = new List <SearchFieldMutator <Employee, EmployeeSearchModel> >(); yield return(new object[] { 1, searchModel, searchFieldMutators, AppConstants.PageSize }); var searchFieldMutators2 = new List <SearchFieldMutator <Employee, EmployeeSearchModel> >(); searchFieldMutators2.Add(new SearchFieldMutator <Employee, EmployeeSearchModel>(search => true, (query, search) => query.Where(e => e.FirstName.Contains(search.FirstName)))); searchFieldMutators2.Add(new SearchFieldMutator <Employee, EmployeeSearchModel>(search => true, (query, search) => query.Where(e => e.LastName.Contains(search.LastName)))); yield return(new object[] { 1, searchModel, searchFieldMutators2, 1 }); }
// GET: Employee public ActionResult Index(EmployeeSearchModel searchModel) { //var business = new EmployeeBusinessLogic(); //var model = business.GetStops(searchModel); //return View(model); EmployeeViewModel ev = new EmployeeViewModel(); var stops = db.Services.Single(s => s.ServiceDay.Contains(dateInput) && s.Pickup.Zip.Contains(zipInput) //{ // ServiceDay = stops.ServiceDay; // PickupAddress = stops.Pickup.DisplayAddress; //} ); return(View(stops)); //return View(); }
public ActionResult <IEnumerable <EmployeeDto> > GetEmployees([FromQuery] int?page, [FromQuery] EmployeeSearchModel searchModel) { return(_employeeService.GetEmployees(page, searchModel)); }
/// <summary> /// I can also inject EmployeeService here, It's better for Unit Test and also independent Prensenter layer /// form any services, but it makes more complex the project and I perefer not to inject Employee Service /// for this assessment. /// </summary> /// <param name="EmployeeService"></param> public EmployeePresenter(IEmployee view) { _employeeView = view; _employee = new EmployeeModel(); _employeeSearch = new EmployeeSearchModel(); }
/// <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)); }
public ListResult <EmployeeListModel> GetAll(SearchParameters searchParameters, EmployeeSearchModel model) { try { if (searchParameters == null) { throw new NullReferenceException("Search Parameters Cannot be null"); } var items = employeeRepository.GetAll().AsQueryable(); // Filter records using method implemented in drive class. items = FilterRecords(items, searchParameters, model); // Get total count of Filtered Records var totalRecords = items.Count(); // Apply Sort Order items = CommonHelper.ApplyEmployeePaging(searchParameters, items); // Return Result var returnObject = new ListResult <EmployeeListModel>(); returnObject.TotalRecords = totalRecords; returnObject.ResultData = ConvertToModel(items).ToList();; //add row number int r = searchParameters.PageStart; returnObject.ResultData.ForEach(i => i.SNo = ++r); return(returnObject); } catch (Exception ex) { throw ex; } }
public IQueryable <Employee> FilterRecords(IQueryable <Employee> items, SearchParameters searchParameters, EmployeeSearchModel model) { items = FilterOnSearchText(items, searchParameters); return(items); }
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 B.companyCD =A.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(" 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 '%' + @DeptID + '%' "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID", 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)); }
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)); }
public EmployeeService(EmployeeSearchModel employeeSearch) { _employeeSearch = employeeSearch; }
/// <summary> /// 查询计时工资项信息 /// </summary> /// <param name="model">查询条件</param> /// <param name="itemNo">计时项目编号</param> /// <returns></returns> public static string GetPieceworkSalaryInfo(EmployeeSearchModel model, string itemNo) { //获取登陆用户信息 UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //设置公司代码 model.CompanyCD = userInfo.CompanyCD; //获取满足条件的人员信息查询 DataTable dtEmplInfo = PieceworkSalaryDBHelper.GetEmplInfo(model, itemNo); //变量定义 StringBuilder sbPieceworkSalaryInfo = 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 dtPeiceItemInfo = PieceworkSalaryDBHelper.GetPeiceItemInfo(model.CompanyCD , emplID, itemNo, model.StartDate, model.EndDate); //变量定义 decimal totalMoney = 0; int pieceworkItemCount = 0; int totalRowCount = 0; StringBuilder sbEmplFirstRow = new StringBuilder(); StringBuilder sbEmplNotFirstRow = new StringBuilder(); //获取工资项目总数 if (dtPeiceItemInfo != null && dtPeiceItemInfo.Rows.Count > 0) { pieceworkItemCount = dtPeiceItemInfo.Rows.Count; } #region 生成每个计时项目的具体信息 //遍历工资项 for (int j = 0; j < pieceworkItemCount; j++) { //变量定义 decimal pieceworkTotalMoney = 0; StringBuilder sbPieceworkFirstRow = new StringBuilder(); StringBuilder sbPieceworkNotFirstRow = new StringBuilder(); //获取工资项编号 string pieceworkItemNo = GetSafeData.ValidateDataRow_String(dtPeiceItemInfo.Rows[j], "ItemNo"); //获取工资内容 DataTable dtPieceworkSalaryInfo = PieceworkSalaryDBHelper.GetPieceworkSalaryInfo(model.CompanyCD , emplID, pieceworkItemNo, model.StartDate, model.EndDate); //获取工资内容总数 int PieceworkSalaryCount = 0; if (dtPieceworkSalaryInfo != null && dtPieceworkSalaryInfo.Rows.Count > 0) { PieceworkSalaryCount = dtPieceworkSalaryInfo.Rows.Count; } //单价 string price = StringUtil.TrimZero(GetSafeData.GetStringFromDecimal(dtPeiceItemInfo.Rows[j], "UnitPrice")); StringBuilder sbItemTemp = new StringBuilder(); // //工资编号 sbItemTemp.AppendLine("<td class='tdColInputCenter' rowspan='" + PieceworkSalaryCount.ToString() + "'>" + "<input type='hidden' id='txtSalaryNo_" + (i + 1).ToString() + "_" + (j + 1).ToString() + "' value='" + pieceworkItemNo + "' />" + "<input type='hidden' id='txtSalaryCount_" + (i + 1).ToString() + "_" + (j + 1).ToString() + "' value='" + PieceworkSalaryCount + "' />" + GetSafeData.ValidateDataRow_String(dtPeiceItemInfo.Rows[j], "ItemName") + "</td>"); //单价 sbItemTemp.AppendLine("<td class='tdColInputCenter' rowspan='" + PieceworkSalaryCount.ToString() + "' id='tdUnitPrice_" + (i + 1).ToString() + "_" + (j + 1).ToString() + "' >" + price + "</td>"); //第一行时 if (j == 0) { sbEmplFirstRow.AppendLine(sbItemTemp.ToString()); } else { //行开始 sbEmplNotFirstRow.AppendLine("<tr>"); sbEmplNotFirstRow.AppendLine(sbItemTemp.ToString()); } //遍历所有项目获取具体 for (int x = 0; x < PieceworkSalaryCount; x++) { StringBuilder sbTemp = new StringBuilder(); //数量 string amount = StringUtil.TrimZero(GetSafeData.GetStringFromDecimal(dtPieceworkSalaryInfo.Rows[x], "Amount")); sbTemp.AppendLine("<td class='tdColInputCenter' >" + "<input type='text' class='tdinput' readonly='readonly' id='txtAmount_" + (i + 1).ToString() + "_" + (j + 1).ToString() + "_" + (x + 1).ToString() + "' value='" + amount + "' style='width=95%;background-color:#FFFFE0;'" + " onblur='CalculateTotalSalary(this, \"" + (i + 1).ToString() + "\", \"" + (j + 1).ToString() + "\", \"" + (x + 1).ToString() + "\");' onchange='Number_round(this,\"2\");' maxlength = '8' />" + "</td>"); //金额 string salaryMoney = StringUtil.TrimZero(GetSafeData.GetStringFromDecimal(dtPieceworkSalaryInfo.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(dtPieceworkSalaryInfo.Rows[x], "PieceDate") + "</td>"); //来源 //sbTemp.AppendLine("<td class='tdColInputCenter' id='tdSalaryDate_" + (i + 1).ToString() + "_" + (j + 1).ToString() // + "_" + (x + 1).ToString() + "'>" + GetSafeData.ValidateDataRow_String(dtPieceworkSalaryInfo.Rows[x], "PieceDate") + "</td>"); //计算小计金额 pieceworkTotalMoney += decimal.Parse(salaryMoney); if (x == 0) { //人员的第一行信息时 if (j == 0) { sbEmplFirstRow.AppendLine(sbTemp.ToString()); } //不是人员的第一行信息,但是是计时工资项的第一行信息 else { sbPieceworkFirstRow.AppendLine(sbTemp.ToString()); } } else { //行开始 sbPieceworkNotFirstRow.AppendLine("<tr>"); sbPieceworkNotFirstRow.AppendLine(sbTemp.ToString()); //行结束 sbPieceworkNotFirstRow.AppendLine("</tr>"); } } //工资小计 if (j == 0) { sbEmplFirstRow.AppendLine("<td id='tdAmountTotalMoney_" + (i + 1).ToString() + "_" + (j + 1).ToString() + "' class='tdColInputCenter' rowspan='" + PieceworkSalaryCount.ToString() + "'>" + pieceworkTotalMoney.ToString() + "</td>"); } else { sbPieceworkFirstRow.AppendLine("<td id='tdAmountTotalMoney_" + (i + 1).ToString() + "_" + (j + 1).ToString() + "' class='tdColInputCenter' rowspan='" + PieceworkSalaryCount.ToString() + "'>" + pieceworkTotalMoney.ToString() + "</td></tr>"); } //计算合计金额 totalMoney += pieceworkTotalMoney; //行数统计 totalRowCount += PieceworkSalaryCount; //非第一行时,添加到非第一行的信息里 sbEmplNotFirstRow.AppendLine(sbPieceworkFirstRow.ToString() + sbPieceworkNotFirstRow.ToString()); } #endregion //行开始 sbPieceworkSalaryInfo.AppendLine("<tr>"); //选择 sbPieceworkSalaryInfo.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>"); //人员编号 sbPieceworkSalaryInfo.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='" + pieceworkItemCount + "' />" + GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "EmployeeNo") + "</td>"); //人员姓名 sbPieceworkSalaryInfo.AppendLine("<td class='tdColInputCenter' rowspan='" + totalRowCount.ToString() + "'>" + GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "EmployeeName") + "</td>"); //所在部门 sbPieceworkSalaryInfo.AppendLine("<td class='tdColInputCenter' rowspan='" + totalRowCount.ToString() + "'>" + GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "DeptName") + "</td>"); //岗位名称 sbPieceworkSalaryInfo.AppendLine("<td class='tdColInputCenter' rowspan='" + totalRowCount.ToString() + "'>" + GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "QuarterName") + "</td>"); //岗位职等 sbPieceworkSalaryInfo.AppendLine("<td class='tdColInputCenter' rowspan='" + totalRowCount.ToString() + "'>" + GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "AdminLevelName") + "</td>"); //具体的计时项目信息 sbPieceworkSalaryInfo.AppendLine(sbEmplFirstRow.ToString()); //工资合计 sbPieceworkSalaryInfo.AppendLine("<td class='tdColInputCenter' id='tdTotalMoney_" + (i + 1).ToString() + "' rowspan='" + totalRowCount.ToString() + "'>" + totalMoney.ToString() + "</td>"); //行结束 sbPieceworkSalaryInfo.AppendLine("</tr>"); //具体的计时项目信息 sbPieceworkSalaryInfo.AppendLine(sbEmplNotFirstRow.ToString()); } } return(sbPieceworkSalaryInfo.ToString()); }
/// <inheritdoc/> public IEnumerable <Employee> GetAll(int skipSize, int takeSize, EmployeeSearchModel searchModel, List <SearchFieldMutator <Employee, EmployeeSearchModel> > searchFieldMutators) { var query = GetAll(searchModel, searchFieldMutators); return(query.Skip(skipSize).Take(takeSize).AsEnumerable()); }