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