/// <summary> /// 通过帐号ID查找相关的帐套项 /// </summary> /// <param name="accountSetID"></param> /// <returns></returns> private static List <AccountSetItem> GetAccountSetItemByAccountSetID(int accountSetID) { List <AccountSetItem> accountSetItemList = new List <AccountSetItem>(); SqlCommand cmd = new SqlCommand(); cmd.Parameters.Add(_ParmAccountSetID, SqlDbType.Int).Value = accountSetID; using (SqlDataReader sdr = SqlHelper.ExecuteReader("GetAccountSetItemByAccountSetID", cmd)) { while (sdr.Read()) { int accountSetParaID = Convert.ToInt32(sdr[_DBAccountSetParaID]); string name = sdr[_DBAccountSetParaName].ToString(); AccountSetPara accountSetPara = new AccountSetPara(accountSetParaID, name); accountSetPara.BindItem = BindItemEnum.ChangeValueToBindItemEnum(Convert.ToInt32(sdr[_DBBindItem])); accountSetPara.FieldAttribute = FieldAttributeEnum.ChangeValueToFieldAttributeEnum(Convert.ToInt32(sdr[_DBFieldAttribute])); accountSetPara.MantissaRound = MantissaRoundEnum.ChangeValueToMantissaRoundEnum(Convert.ToInt32(sdr[_DBMantissaRound])); accountSetPara.Description = sdr[_DBDescription].ToString(); accountSetPara.IsVisibleToEmployee = Convert.ToBoolean(sdr[_DBIsVisibleToEmployee]); accountSetPara.IsVisibleWhenZero = Convert.ToBoolean(sdr[_DBIsVisibleWhenZero]); AccountSetItem accountSetItem = new AccountSetItem(Convert.ToInt32(sdr[_DBPKID]), accountSetPara, sdr[_DBCalculateFormula].ToString()); accountSetItemList.Add(accountSetItem); } } return(accountSetItemList); }
/// <summary> /// 通过条件查询 /// </summary> /// <param name="name"></param> /// <param name="fieldAttribute"></param> /// <param name="mantissaRound"></param> /// <param name="bindItem"></param> /// <returns></returns> public List <AccountSetPara> GetAccountSetParaByCondition(string name, FieldAttributeEnum fieldAttribute, MantissaRoundEnum mantissaRound, BindItemEnum bindItem) { List <AccountSetPara> accountSetParaList = new List <AccountSetPara>(); SqlCommand cmd = new SqlCommand(); cmd.Parameters.Add(_ParmAccountSetParaName, SqlDbType.NVarChar, 255).Value = name; cmd.Parameters.Add(_ParmFieldAttribute, SqlDbType.Int).Value = fieldAttribute.Id; cmd.Parameters.Add(_ParmBindItem, SqlDbType.Int).Value = bindItem.Id; cmd.Parameters.Add(_ParmMantissaRound, SqlDbType.Int).Value = mantissaRound.Id; using (SqlDataReader sdr = SqlHelper.ExecuteReader("GetAccountSetParaByCondition", cmd)) { while (sdr.Read()) { int pkid = Convert.ToInt32(sdr[_DBPKID]); string accountSetParaName = sdr[_DBAccountSetParaName].ToString(); AccountSetPara accountSetPara = new AccountSetPara(pkid, accountSetParaName); accountSetPara.BindItem = BindItemEnum.ChangeValueToBindItemEnum(Convert.ToInt32(sdr[_DBBindItem])); accountSetPara.FieldAttribute = FieldAttributeEnum.ChangeValueToFieldAttributeEnum(Convert.ToInt32(sdr[_DBFieldAttribute])); accountSetPara.MantissaRound = MantissaRoundEnum.ChangeValueToMantissaRoundEnum(Convert.ToInt32(sdr[_DBMantissaRound])); accountSetPara.Description = sdr[_DBDescription].ToString(); accountSetPara.IsVisibleToEmployee = Convert.ToBoolean(sdr[_DBIsVisibleToEmployee]); accountSetPara.IsVisibleWhenZero = Convert.ToBoolean(sdr[_DBIsVisibleWhenZero]); accountSetParaList.Add(accountSetPara); } } return(accountSetParaList); }
private AccountSetPara MakeAccountSetPara() { AccountSetPara retAccountSetPara = new AccountSetPara(0, _AccountSetParaName); retAccountSetPara.BindItem = _BindItem; retAccountSetPara.FieldAttribute = _FieldAttributes; retAccountSetPara.MantissaRound = _MantissaRoundEnum; retAccountSetPara.Description = _Description; retAccountSetPara.IsVisibleToEmployee = _IsVisibleToEmployee; retAccountSetPara.IsVisibleWhenZero = _IsVisibleWhenZero; _AccountSetParaTest = retAccountSetPara; return(retAccountSetPara); }
/// <summary> /// 更新帐套参数 /// </summary> /// <param name="para"></param> /// <returns></returns> public int UpdateAccountSetPara(AccountSetPara para) { SqlCommand cmd = new SqlCommand(); cmd.Parameters.Add(_ParmPKID, SqlDbType.Int).Value = para.AccountSetParaID; cmd.Parameters.Add(_ParmAccountSetParaName, SqlDbType.NVarChar, 255).Value = para.AccountSetParaName; cmd.Parameters.Add(_ParmDescription, SqlDbType.Text).Value = para.Description; cmd.Parameters.Add(_ParmFieldAttribute, SqlDbType.Int).Value = para.FieldAttribute.Id; cmd.Parameters.Add(_ParmBindItem, SqlDbType.Int).Value = para.BindItem.Id; cmd.Parameters.Add(_ParmIsVisibleToEmployee, SqlDbType.Int).Value = para.IsVisibleToEmployee; cmd.Parameters.Add(_ParmIsVisibleWhenZero, SqlDbType.Int).Value = para.IsVisibleWhenZero; cmd.Parameters.Add(_ParmMantissaRound, SqlDbType.Int).Value = para.MantissaRound.Id; return(SqlHelper.ExecuteNonQuery("UpdateAccountSetPara", cmd)); }
protected override void Validation() { //判断是否存在Para _OldAccountSetPara = _DalAccountSet.GetAccountSetParaByPKID(_AccountSetParaID); if (_OldAccountSetPara == null) { BllUtility.ThrowException(BllExceptionConst._AccountSetPara_IsNotExist); } //判断是否有重名 if (_DalAccountSet.CountAccountSetParaByNameDiffPKID(_AccountSetParaID, _AccountSetParaName) > 0) { BllUtility.ThrowException(BllExceptionConst._AccountSetParaName_Repeat); } }
/// <summary> /// 新增帐套参数 /// </summary> /// <param name="para"></param> /// <returns></returns> public int InsertAccountSetPara(AccountSetPara para) { int pkid; SqlCommand cmd = new SqlCommand(); cmd.Parameters.Add(_ParmAccountSetParaName, SqlDbType.NVarChar, 255).Value = para.AccountSetParaName; cmd.Parameters.Add(_ParmDescription, SqlDbType.Text).Value = para.Description; cmd.Parameters.Add(_ParmFieldAttribute, SqlDbType.Int).Value = para.FieldAttribute.Id; cmd.Parameters.Add(_ParmBindItem, SqlDbType.Int).Value = para.BindItem.Id; cmd.Parameters.Add(_ParmIsVisibleToEmployee, SqlDbType.Int).Value = para.IsVisibleToEmployee; cmd.Parameters.Add(_ParmIsVisibleWhenZero, SqlDbType.Int).Value = para.IsVisibleWhenZero; cmd.Parameters.Add(_ParmMantissaRound, SqlDbType.Int).Value = para.MantissaRound.Id; cmd.Parameters.Add(_ParmPKID, SqlDbType.Int).Direction = ParameterDirection.Output; SqlHelper.ExecuteNonQueryReturnPKID("InsertAccountSetPara", cmd, out pkid); return(pkid); }
/// <summary> /// 找到employeeSalaryList职位为position,帐套项为accountSetPara的员工发薪历史来计算 /// </summary> /// <param name="position">职位</param> /// <param name="accountSetPara">帐套项</param> /// <param name="employeeSalaryList">所有符合统计条件的发薪历史</param> /// <returns>计算值</returns> public decimal CalculateByPosition(Position position, AccountSetPara accountSetPara, List <EmployeeSalary> employeeSalaryList) { decimal result = 0; if (employeeSalaryList == null || employeeSalaryList.Count == 0) { return(result); } for (int i = 0; i < employeeSalaryList.Count; i++) { if (employeeSalaryList[i].Employee != null && employeeSalaryList[i].Employee.Account.Position != null && employeeSalaryList[i].Employee.Account.Position.ParameterID == position.ParameterID) { if (employeeSalaryList[i].EmployeeSalaryHistoryList != null && employeeSalaryList[i].EmployeeSalaryHistoryList.Count > 0 && employeeSalaryList[i].EmployeeSalaryHistoryList[0] != null && employeeSalaryList[i].EmployeeSalaryHistoryList[0].EmployeeAccountSet != null && employeeSalaryList[i].EmployeeSalaryHistoryList[0].EmployeeAccountSet.Items != null) { for (int j = 0; j < employeeSalaryList[i].EmployeeSalaryHistoryList[0].EmployeeAccountSet.Items.Count; j++) { if (employeeSalaryList[i].EmployeeSalaryHistoryList[0].EmployeeAccountSet.Items[j]. AccountSetPara.AccountSetParaID == accountSetPara.AccountSetParaID) { result += employeeSalaryList[i].EmployeeSalaryHistoryList[0].EmployeeAccountSet.Items[j]. CalculateResult; } } } } } return(result); }
public AccountSetPara GetAccountSetParaByName(string accountSetParaName) { SqlCommand cmd = new SqlCommand(); cmd.Parameters.Add(_ParmAccountSetParaName, SqlDbType.NVarChar, 255).Value = accountSetParaName; using (SqlDataReader sdr = SqlHelper.ExecuteReader("GetAccountSetParaByName", cmd)) { while (sdr.Read()) { int accountSetParaId = Convert.ToInt32(sdr[_DBPKID]); string name = sdr[_DBAccountSetParaName].ToString(); AccountSetPara accountSetPara = new AccountSetPara(accountSetParaId, name); accountSetPara.BindItem = BindItemEnum.ChangeValueToBindItemEnum(Convert.ToInt32(sdr[_DBBindItem])); accountSetPara.FieldAttribute = FieldAttributeEnum.ChangeValueToFieldAttributeEnum(Convert.ToInt32(sdr[_DBFieldAttribute])); accountSetPara.MantissaRound = MantissaRoundEnum.ChangeValueToMantissaRoundEnum(Convert.ToInt32(sdr[_DBMantissaRound])); accountSetPara.Description = sdr[_DBDescription].ToString(); accountSetPara.IsVisibleToEmployee = Convert.ToBoolean(sdr[_DBIsVisibleToEmployee]); accountSetPara.IsVisibleWhenZero = Convert.ToBoolean(sdr[_DBIsVisibleWhenZero]); return(accountSetPara); } } return(null); }
private AccountSetPara MakeAccountSetPara() { AccountSetPara accountSetPara = new AccountSetPara(_AccountSetParaID, _AccountSetParaName); accountSetPara.Description = _Description; accountSetPara.BindItem = _BindItem; accountSetPara.FieldAttribute = _FieldAttributes; accountSetPara.MantissaRound = _MantissaRoundEnum; accountSetPara.IsVisibleToEmployee = _IsVisibleToEmployee; accountSetPara.IsVisibleWhenZero = _IsVisibleWhenZero; if (_DalAccountSet.CountAccountSetItemByAccountSetParaID(_AccountSetParaID) > 0) { //如果名字、是否对员工可见、当为0时是否显示修改了,则要对员工的帐套进行修改 if (_OldAccountSetPara.AccountSetParaName != accountSetPara.AccountSetParaName || _OldAccountSetPara.IsVisibleToEmployee != accountSetPara.IsVisibleToEmployee || _OldAccountSetPara.IsVisibleWhenZero != accountSetPara.IsVisibleWhenZero) { _IsAccountSetParaChanged = true; } } _AccountSetParaTest = accountSetPara; return(accountSetPara); }
/// <summary> /// 根据部门分组 /// </summary> /// <param name="startDt"></param> /// <param name="endDt"></param> /// <param name="departmentID"></param> /// <param name="item"></param> /// <param name="companyID"></param> /// <param name="isIncludeChildDeptMember"></param> /// <returns></returns> /// <param name="loginUser"></param> public List <EmployeeSalaryStatistics> TimeSpanStatisticsGroupByDepartment(DateTime startDt, DateTime endDt, int departmentID, AccountSetPara item, int companyID, bool isIncludeChildDeptMember, Account loginUser) { List <EmployeeSalaryStatistics> iRet = new List <EmployeeSalaryStatistics>(); //划分月份 List <DateTime> Months = SplitMonth(startDt, endDt); //查出每个月份月底这个时间点的,某一部门及其所有子部门(包括改过名字部门) List <Department> AllDepartment = GetAllDepartment(Months, departmentID); AllDepartment = Tools.RemoteUnAuthDeparetment(AllDepartment, AuthType.HRMIS, loginUser, HrmisPowers.A607); //计算每个月 for (int j = 0; j < Months.Count; j++) { EmployeeSalaryStatistics employeeSalaryStatistics = new EmployeeSalaryStatistics(); employeeSalaryStatistics.SalaryDay = Months[j]; employeeSalaryStatistics.EmployeeSalaryStatisticsItemList = new List <EmployeeSalaryStatisticsItem>(); //得到这个月的所有部门 List <Department> departmentList = _GetDepartmentHistory.GetDepartmentNoStructByDateTime(Months[j]); departmentList = Tools.RemoteUnAuthDeparetment(departmentList, AuthType.HRMIS, loginUser, HrmisPowers.A607); List <Employee> EmployeesSource = _GetEmployee.GetEmployeeWithCurrentMonthDimissionEmployee( new HrmisUtility().StartMonthByYearMonth(Months[j]), companyID); EmployeesSource = HrmisUtility.RemoteUnAuthEmployee(EmployeesSource, AuthType.HRMIS, loginUser, HrmisPowers.A607); List <Account> accountSource = EmployeeUtility.GetAccountListFromEmployeeList(EmployeesSource); for (int k = 0; k < AllDepartment.Count; k++) { EmployeeSalaryStatisticsItem employeeSalaryStatisticsItem = new EmployeeSalaryStatisticsItem(); employeeSalaryStatisticsItem.ItemName = AllDepartment[k].DepartmentName; employeeSalaryStatisticsItem.ItemID = AllDepartment[k].DepartmentID; //查找这个月,这个部门中的所有人,包括子部门 AllDepartment[k].Members = FindAllEmployeeByDepAndTime(departmentList, AllDepartment[k], Months[j], accountSource, isIncludeChildDeptMember); //循环部门里的员工 foreach (Account account in AllDepartment[k].AllMembers) { //查找某时,某人的薪资历史 EmployeeSalaryHistory employeeSalaryHistory = _GetEmployeeAccountSet.GetEmployeeSalaryHistoryByEmployeeIdAndDateTime (account.Id, Months[j]); if (employeeSalaryHistory == null || employeeSalaryHistory.EmployeeAccountSet == null || employeeSalaryHistory.EmployeeAccountSet.Items == null) { continue; } AccountSetItem accountSetItem = employeeSalaryHistory.EmployeeAccountSet.FindAccountSetItemByParaID(item.AccountSetParaID); if (accountSetItem == null) { continue; } employeeSalaryStatisticsItem.CalculateValue = employeeSalaryStatisticsItem.CalculateValue + accountSetItem.CalculateResult; } employeeSalaryStatistics.EmployeeSalaryStatisticsItemList.Add(employeeSalaryStatisticsItem); } iRet.Add(employeeSalaryStatistics); } return(iRet); }
///// <summary> ///// 移除离职人员 ///// </summary> ///// <param name="EmployeeList"></param> ///// <param name="dt"></param> ///// <returns></returns> //public List<Employee> RemoveDimissionhAndBorrowed(List<Employee> EmployeeList, DateTime dt) //{ // List<Employee> returnEmployee = new List<Employee>(); // DateTime dtFrom = new DateTime(dt.Year, dt.Month, 1); // DateTime dtTo = dtFrom.AddMonths(1).AddDays(-1); // for (int i = 0; i < EmployeeList.Count; i++) // { // //外借员工 // if (EmployeeList[i].EmployeeType == EmployeeTypeEnum.BorrowedEmployee) // { // continue; // } // //根据入职离职时间 // Employee employee = _GetEmployee.GetEmployeeBasicInfoByAccountID(EmployeeList[i].Account.Id); // if (employee.EmployeeDetails == null || employee.EmployeeDetails.Work == null) // { // continue; // } // DateTime employeeFromDate = DateTime.Compare(employee.EmployeeDetails.Work.ComeDate, dtFrom) > 0 // ? employee.EmployeeDetails.Work.ComeDate // : dtFrom; // DateTime employeeToDate; // if (employee.EmployeeType == EmployeeTypeEnum.DimissionEmployee && // employee.EmployeeDetails.Work.DimissionInfo != null) // { // employeeToDate = // DateTime.Compare(employee.EmployeeDetails.Work.DimissionInfo.DimissionDate, dtTo) < 0 // ? employee.EmployeeDetails.Work.DimissionInfo.DimissionDate // : dtTo; // } // else // { // employeeToDate = dtTo; // } // if (DateTime.Compare(employeeFromDate, employeeToDate) > 0) // { // continue; // } // returnEmployee.Add(EmployeeList[i]); // } // return returnEmployee; //} /// <summary> /// 统计某个时间段内的某个部门以及其所有子部门的员工的薪资情况 /// </summary> /// <param name="startDt">统计的开始时间</param> /// <param name="endDt">统计的结束时间</param> /// <param name="departmentID">统计的部门编号</param> /// <param name="item">统计项--帐套项的List</param> /// <returns></returns> /// <param name="companyID"></param> /// <param name="isIncludeChildDeptMember"></param> /// <param name="loginUser"></param> public List <EmployeeSalaryAverageStatistics> AverageStatistics(DateTime startDt, DateTime endDt, int departmentID, AccountSetPara item, int companyID, bool isIncludeChildDeptMember, Account loginUser) { List <EmployeeSalaryAverageStatistics> iRet = new List <EmployeeSalaryAverageStatistics>(); //划分月份 List <DateTime> Months = SplitMonth(startDt, endDt); //查出每个月份月底这个时间点的,某一部门及其所有子部门(包括改过名字部门) List <Department> AllDepartment = GetAllDepartment(Months, departmentID); AllDepartment = Tools.RemoteUnAuthDeparetment(AllDepartment, AuthType.HRMIS, loginUser, HrmisPowers.A607); //构建返回的List foreach (Department department in AllDepartment) { EmployeeSalaryAverageStatistics employeeSalaryAverageStatistics = new EmployeeSalaryAverageStatistics(); employeeSalaryAverageStatistics.Department = department; EmployeeSalaryStatisticsItem employeeSalaryStatisticsSumItem = new EmployeeSalaryStatisticsItem(); employeeSalaryStatisticsSumItem.ItemID = item.AccountSetParaID; employeeSalaryStatisticsSumItem.ItemName = item.AccountSetParaName; EmployeeSalaryStatisticsItem employeeSalaryStatisticsAverageItem = new EmployeeSalaryStatisticsItem(); employeeSalaryStatisticsAverageItem.ItemName = item.AccountSetParaName + "均值"; EmployeeSalaryStatisticsItem employeeSalaryStatisticsEmployeeCountItem = new EmployeeSalaryStatisticsItem(); employeeSalaryStatisticsEmployeeCountItem.ItemName = "人数"; employeeSalaryAverageStatistics.SumItem = employeeSalaryStatisticsSumItem; employeeSalaryAverageStatistics.AverageItem = employeeSalaryStatisticsAverageItem; employeeSalaryAverageStatistics.EmployeeCountItem = employeeSalaryStatisticsEmployeeCountItem; iRet.Add(employeeSalaryAverageStatistics); } //计算每个月 for (int j = 0; j < Months.Count; j++) { //得到这个月的所有部门 List <Department> departmentList = _GetDepartmentHistory.GetDepartmentNoStructByDateTime(Months[j]); departmentList = Tools.RemoteUnAuthDeparetment(departmentList, AuthType.HRMIS, loginUser, HrmisPowers.A607); List <Employee> EmployeesSourceTemp = _GetEmployee.GetEmployeeWithCurrentMonthDimissionEmployee( new HrmisUtility().StartMonthByYearMonth(Months[j]), companyID); EmployeesSourceTemp = HrmisUtility.RemoteUnAuthEmployee(EmployeesSourceTemp, AuthType.HRMIS, loginUser, HrmisPowers.A607); //List<Employee> EmployeesSource = RemoveDimissionAndBorrowed(EmployeesSourceTemp, Months[j]); List <Account> accountSource = EmployeeUtility.GetAccountListFromEmployeeList(EmployeesSourceTemp); for (int k = 0; k < AllDepartment.Count; k++) { //查找这个月,这个部门中的所有人,包括子部门 AllDepartment[k].Members = FindAllEmployeeByDepAndTime(departmentList, AllDepartment[k], Months[j], accountSource, isIncludeChildDeptMember); //累计计算每月员工个数 iRet[k].EmployeeCountItem.CalculateValue = iRet[k].EmployeeCountItem.CalculateValue + AllDepartment[k].Members.Count; //循环部门里的员工 foreach (Account account in AllDepartment[k].AllMembers) { //查找某时,某人的薪资历史 EmployeeSalaryHistory employeeSalaryHistory = _GetEmployeeAccountSet.GetEmployeeSalaryHistoryByEmployeeIdAndDateTime (account.Id, Months[j]); if (employeeSalaryHistory == null || employeeSalaryHistory.EmployeeAccountSet == null || employeeSalaryHistory.EmployeeAccountSet.Items == null) { continue; } AccountSetItem accountSetItem = employeeSalaryHistory.EmployeeAccountSet.FindAccountSetItemByParaID(item.AccountSetParaID); if (accountSetItem == null) { continue; } iRet[k].SumItem.CalculateValue = iRet[k].SumItem.CalculateValue + accountSetItem.CalculateResult; } } } for (int k = 0; k < AllDepartment.Count; k++) { //计算平均值 if (iRet[k].EmployeeCountItem.CalculateValue == 0) { iRet[k].AverageItem.CalculateValue = 0; } else { iRet[k].AverageItem.CalculateValue = iRet[k].SumItem.CalculateValue / iRet[k].EmployeeCountItem.CalculateValue; } //计算每月平均员工人数 iRet[k].EmployeeCountItem.CalculateValue = iRet[k].EmployeeCountItem.CalculateValue / Months.Count; iRet[k].AverageItem.CalculateValue = Convert.ToDecimal(iRet[k].AverageItem.CalculateValue.ToString("0.00")); iRet[k].EmployeeCountItem.CalculateValue = Convert.ToDecimal(iRet[k].EmployeeCountItem.CalculateValue.ToString("0.00")); iRet[k].SumItem.CalculateValue = Convert.ToDecimal(iRet[k].SumItem.CalculateValue.ToString("0.00")); } return(iRet); }
public List <EmployeeSalaryStatistics> TimeSpanStatisticsGroupByDepartment(DateTime startDt, DateTime endDt, int departmentID, AccountSetPara item, int companyID, bool isIncludeChildDeptMember, Account loginUser) { GetEmployeeSalaryStatistics target = new GetEmployeeSalaryStatistics(); return (target.TimeSpanStatisticsGroupByDepartment(startDt, endDt, departmentID, item, companyID, isIncludeChildDeptMember, loginUser)); }