/// <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);
}
private void CheckItem()
{
string result = string.Empty;
if (Request.QueryString["expression"] != null &&
Request.QueryString["imgResult"] != null &&
Request.QueryString["paraID"] != null &&
Request.QueryString["rowindexID"] != null &&
Session["AccountSetItemListForCheck"] != null)
{
string rowindexID = Request.QueryString["rowindexID"];
string paraID = Request.QueryString["paraID"];
string expression = Request.QueryString["expression"];
string imgResult = Request.QueryString["imgResult"];
result = imgResult + "|";
AccountSetItem accountSetItem =
new AccountSetItem(-1, new AccountSetPara(Convert.ToInt32(paraID), ""), expression);
try
{
List <AccountSetItem> accountSetItemForCheck =
Session["AccountSetItemListForCheck"] as List <AccountSetItem>;
if (accountSetItemForCheck != null)
{
accountSetItemForCheck[Convert.ToInt32(rowindexID)].CalculateFormula = expression;
}
accountSetItem.CheckItemValidation(accountSetItemForCheck);
}
catch (Exception ex)
{
result += ex.ToString();
}
}
Response.Write(result + "||end");
}
/// <summary>
/// 遍历dt中的第employeeRow行，将dt中的该行的值赋值给salaryHistory
/// </summary>
private static void ValueItemInOneRow(DataTable dt, EmployeeSalaryHistory salaryHistory, int employeeRow)
{
for (int i = 0; i < salaryHistory.EmployeeAccountSet.Items.Count; i++)
{
AccountSetItem item = salaryHistory.EmployeeAccountSet.Items[i];
string answer = GetItem(dt, employeeRow, item.AccountSetPara.AccountSetParaName);
if (answer != "EmptyNull")
{
if (string.IsNullOrEmpty(answer))
{
item.CalculateResult = 0;
}
else
{
decimal tempdecimal;
if (!Decimal.TryParse(answer, out tempdecimal))
{
throw new ApplicationException(string.Format("数据：{0} 不是小数", answer));
}
else
{
item.CalculateResult = Convert.ToDecimal(answer);
}
}
}
}
}
/// <summary>
/// 添加accountSetItems中帐套项newItem
/// </summary>
/// <param name="accountSetItems"></param>
/// <param name="newItem"></param>
private static void AddEmployeeAccountSetItemWithNewAccountSetItem(List <AccountSetItem> accountSetItems, AccountSetItem newItem)
{
AccountSetItem newitem =
new AccountSetItem(newItem.AccountSetItemID, newItem.AccountSetPara, newItem.CalculateFormula);
accountSetItems.Add(newitem);
}
/// <summary>
/// 为accountSetItemList在最后一行添加空的item项，AccountSetParaID为-1
/// </summary>
/// <param name="accountSetItemList"></param>
/// <returns></returns>
public static List <AccountSetItem> AddNullItem(List <AccountSetItem> accountSetItemList)
{
AccountSetItem item = new AccountSetItem(-1, new Model.PayModule.AccountSetPara(-1, ""), "");
accountSetItemList.Add(item);
return(accountSetItemList);
}
/// <summary>
/// 判断accountSetItems中是否有item
/// </summary>
/// <param name="item"></param>
/// <param name="accountSetItems"></param>
/// <returns></returns>
private static bool IsAccountSetInList(AccountSetItem item, List <AccountSetItem> accountSetItems)
{
for (int i = 0; i < accountSetItems.Count; i++)
{
if (item.AccountSetPara.AccountSetParaID == accountSetItems[i].AccountSetPara.AccountSetParaID)
{
return(true);
}
}
return(false);
}
//
#endregion
#region AccountSetItem
/// <summary>
/// 新增帐套项
/// </summary>
/// <param name="accountSetID"></param>
/// <param name="accountSetItem"></param>
/// <param name="conn"></param>
/// <param name="trans"></param>
/// <returns></returns>
private static void InsertAccountSetItem(int accountSetID, AccountSetItem accountSetItem, SqlConnection conn, SqlTransaction trans)
{
int pkid;
SqlCommand cmd = new SqlCommand();
cmd.Parameters.Add(_ParmAccountSetID, SqlDbType.Int).Value = accountSetID;
cmd.Parameters.Add(_ParmAccountSetParaID, SqlDbType.Int).Value = accountSetItem.AccountSetPara.AccountSetParaID;
cmd.Parameters.Add(_ParmFieldAttribute, SqlDbType.Int).Value = accountSetItem.AccountSetPara.FieldAttribute.Id;
cmd.Parameters.Add(_ParmBindItem, SqlDbType.Int).Value = accountSetItem.AccountSetPara.BindItem.Id;
cmd.Parameters.Add(_ParmMantissaRound, SqlDbType.Int).Value = accountSetItem.AccountSetPara.MantissaRound.Id;
cmd.Parameters.Add(_ParmCalculateFormula, SqlDbType.NVarChar, 255).Value = accountSetItem.CalculateFormula;
cmd.Parameters.Add(_ParmPKID, SqlDbType.Int).Direction = ParameterDirection.Output;
SqlHelper.TransExecuteNonQueryReturnPKID("InsertAccountSetItem", cmd, conn, trans, out pkid);
//return pkid;
}
/// <summary>
/// 交换第id行<==>第id+1行
/// </summary>
/// <param name="id"></param>
public void DownItemEvent(string id)
{
List <AccountSetItem> items = _IAccountSetView.AccountSetItemList;
int currRow = Convert.ToInt32(id);
if (currRow + 2 == items.Count)
{
return;
}
AccountSetItem tempItem = items[currRow + 1];
items[currRow + 1] = items[currRow];
items[currRow] = tempItem;
_IAccountSetView.AccountSetItemList = items;
}
/// <summary>
/// 检查帐套项的其他属性是否改变
/// </summary>
/// <param name="item"></param>
/// <param name="items"></param>
/// <returns></returns>
private bool IsAccountSetChanged(AccountSetItem item, List <AccountSetItem> items)
{
for (int i = 0; i < items.Count; i++)
{
if (item.AccountSetPara.AccountSetParaID == items[i].AccountSetPara.AccountSetParaID)
{
if (item.AccountSetPara.FieldAttribute.Id == items[i].AccountSetPara.FieldAttribute.Id &&
item.AccountSetPara.BindItem.Id == items[i].AccountSetPara.BindItem.Id &&
item.AccountSetPara.MantissaRound.Id == items[i].AccountSetPara.MantissaRound.Id &&
item.CalculateFormula == items[i].CalculateFormula)
{
return(false);
}
return(true);
}
}
return(false);
}
/// <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="startDt"></param>
/// <param name="endDt"></param>
/// <param name="departmentID"></param>
/// <param name="companyID"></param>
/// <param name="items"></param>
/// <returns></returns>
/// <param name="loginUser"></param>
public List <EmployeeSalaryStatistics> TimeSpanStatisticsGroupByParameter(DateTime startDt, DateTime endDt,
int departmentID, List <AccountSetPara> items, int companyID, Account loginUser)
{
List <EmployeeSalaryStatistics> iRet = new List <EmployeeSalaryStatistics>();
#region 划分月份
List <DateTime> Months = SplitMonth(startDt, endDt);
//计算每个月
for (int j = 0; j < Months.Count; j++)
{
EmployeeSalaryStatistics employeeSalaryStatistics = new EmployeeSalaryStatistics();
employeeSalaryStatistics.SalaryDay = Months[j];
employeeSalaryStatistics.EmployeeSalaryStatisticsItemList = new List <EmployeeSalaryStatisticsItem>();
for (int i = 0; i < items.Count; i++)
{
EmployeeSalaryStatisticsItem item = new EmployeeSalaryStatisticsItem();
item.ItemID = items[i].AccountSetParaID;
item.ItemName = items[i].AccountSetParaName;
employeeSalaryStatistics.EmployeeSalaryStatisticsItemList.Add(item);
}
iRet.Add(employeeSalaryStatistics);
}
#endregion
for (int j = 0; j < Months.Count; j++)
{
//根据月份、部门获取当时的部门
List <Department> itsSource =
_GetDepartmentHistory.GetDepartmentListStructByDepartmentIDAndDateTime(departmentID, Months[j]);
itsSource =
Tools.RemoteUnAuthDeparetment(itsSource, 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);
//遍历部门，找到当时该部门的员工，然后分别获取当月的发薪历史
foreach (Department department in itsSource)
{
List <Employee> employees = FindEmployee(EmployeesSource, department);
foreach (Employee employee in employees)
{
EmployeeSalaryHistory employeeSalaryHistory =
_GetEmployeeAccountSet.GetEmployeeSalaryHistoryByEmployeeIdAndDateTime(employee.Account.Id,
Months[j]);
if (employeeSalaryHistory == null ||
employeeSalaryHistory.EmployeeAccountSet == null ||
employeeSalaryHistory.EmployeeAccountSet.Items == null)
{
continue;
}
//循环每一个需要统计的项，累加结果
for (int i = 0; i < iRet[j].EmployeeSalaryStatisticsItemList.Count; i++)
{
AccountSetItem accountSetItem =
employeeSalaryHistory.EmployeeAccountSet.FindAccountSetItemByParaID(
iRet[j].EmployeeSalaryStatisticsItemList[i].ItemID);
if (accountSetItem == null)
{
continue;
}
iRet[j].EmployeeSalaryStatisticsItemList[i].CalculateValue += accountSetItem.CalculateResult;
}
}
}
}
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);
}
/// <summary>
/// 更新员工帐套
/// </summary>
/// <param name="items"></param>
/// <param name="item"></param>
private void UpdateEmployeeAccountSetItemWithNewAccountSetItem(List <AccountSetItem> items, AccountSetItem item)
{
foreach (AccountSetItem employeeSetItem in items)
{
if (employeeSetItem.AccountSetPara.AccountSetParaID == item.AccountSetPara.AccountSetParaID)
{
employeeSetItem.AccountSetPara = item.AccountSetPara;
employeeSetItem.CalculateFormula = item.CalculateFormula;
}
}
}