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());
}
