// GET api/<controller>/5 public Sys_LockObjectModel Get(Guid id) { string status = string.Empty; var model = new Sys_LockObjectModel(); ActionService service = new ActionService(UserLogin); var cutOfServices = new Att_CutOffDurationServices(); var orgServices = new Cat_OrgStructureServices(); var entity = service.GetByIdUseStore<Sys_LockObjectEntity>(id, ConstantSql.hrm_sys_sp_get_LockObjectByID, ref status); List<object> listModel = new List<object>(); listModel.AddRange(new object[3]); listModel[1] = 1; listModel[2] = Int32.MaxValue - 1; List<Att_CutOffDurationEntity> listCutoffduration_All = cutOfServices.GetData<Att_CutOffDurationEntity>(listModel, ConstantSql.hrm_att_sp_get_CutOffDurations, UserLogin, ref status).ToList(); var objOrg = new List<object>(); objOrg.Add(null); objOrg.Add(null); objOrg.Add(null); objOrg.Add(1); objOrg.Add(int.MaxValue -1); var lstOrg = orgServices.GetData<Cat_OrgStructureEntity>(objOrg, ConstantSql.hrm_cat_sp_get_OrgStructure, UserLogin, ref status).ToList(); if (entity != null) { model = entity.CopyData<Sys_LockObjectModel>(); if (model.PayrollGroups != null) { model.lstPayrollGroupID = Common.GetListNumbersFromBinary(model.PayrollGroups); model.PayrollGroupID = string.Join(",", model.lstPayrollGroupID); } if (model.OrgStructures != null) { model.lstOrgStructureID = Common.GetListNumbersFromBinary(model.OrgStructures); var lstOrgName = lstOrg.Where(s => model.lstOrgStructureID.Contains(s.OrderNumber)).Select(s => s.OrgStructureName).ToList(); model.OrgStructureID = string.Join(",", model.lstOrgStructureID); model.OrgStructureName = string.Join(",", lstOrgName); } var cutOfDurationEntity = listCutoffduration_All.Where(s => s.DateEnd == model.DateEnd && s.DateStart == model.DateStart).FirstOrDefault(); if (cutOfDurationEntity != null) { model.CutOffDurationID = cutOfDurationEntity.ID; model.CutOffDurationName = cutOfDurationEntity.CutOffDurationName; } } model.ActionStatus = status; return model; }
public List<Sal_HoldSalaryEntity> ComputeHoldSalary(Guid? cutOffDurationID, Guid[] ProfileIDs, string orgStructureID, Guid TimeAnalyze, string userLoginName) { using (var context = new VnrHrmDataContext()) { #region Get Data string status = string.Empty; List<Sal_HoldSalaryEntity> lstHoldSalaryEntity = new List<Sal_HoldSalaryEntity>(); //ds kỳ công var cutOffServies = new Att_CutOffDurationServices(); var lstObjCutOff = new List<object>(); lstObjCutOff.Add(null); lstObjCutOff.Add(1); lstObjCutOff.Add(int.MaxValue - 1); var lstCutOff = cutOffServies.GetData<Att_CutOffDurationEntity>(Common.DotNetToOracle(cutOffDurationID.Value.ToString()), ConstantSql.hrm_att_sp_get_CutOffDurationById, userLoginName, ref status).FirstOrDefault(); //ds nv var hrService = new Hre_ProfileServices(); List<object> strOrgIDs = new List<object>(); strOrgIDs.AddRange(new object[3]); strOrgIDs[0] = (object)orgStructureID; var lstProfile = hrService.GetData<Hre_ProfileEntity>(strOrgIDs, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, userLoginName, ref status); if (ProfileIDs != null) { lstProfile = lstProfile.Where(s => ProfileIDs.Contains(s.ID)).ToList(); } //ds ngày nghỉ var leaveDayServices = new Att_LeavedayServices(); var lstObjLeaveDay = new List<object>(); lstObjLeaveDay.AddRange(new object[11]); lstObjLeaveDay[4] = lstCutOff.DateStart; lstObjLeaveDay[5] = lstCutOff.DateEnd; lstObjLeaveDay[9] = 1; lstObjLeaveDay[10] = int.MaxValue - 1; var lstLeaveDay = leaveDayServices.GetData<Att_LeaveDayEntity>(lstObjLeaveDay, ConstantSql.hrm_att_sp_get_Leaveday,userLoginName, ref status); //ds loại ngày nghỉ var leavedayTypeServices = new Cat_LeaveDayTypeServices(); var lstObjLeavedayType = new List<object>(); lstObjLeavedayType.Add(null); lstObjLeavedayType.Add(null); lstObjLeavedayType.Add(1); lstObjLeavedayType.Add(int.MaxValue - 1); var lstLeavedayType = leavedayTypeServices.GetData<Cat_LeaveDayTypeEntity>(lstObjLeavedayType, ConstantSql.hrm_cat_sp_get_LeaveDayType,userLoginName, ref status); lstObjLeavedayType = new List<object>(); lstObjLeavedayType.Add(null); lstObjLeavedayType.Add(EnumDropDown.EntityType.E_CountAnalyzeHoldSalary.ToString()); lstObjLeavedayType.Add(1); lstObjLeavedayType.Add(int.MaxValue - 1); var ListNameEntity = leavedayTypeServices.GetData<Cat_NameEntityEntity>(lstObjLeavedayType, ConstantSql.hrm_cat_sp_get_NameEntityByKPI,userLoginName, ref status); ////ds chế độ lương //var Sal_GradeServices = new Sal_GradeServices(); //var lstObjSal_Grade = new List<object>(); //lstObjSal_Grade.AddRange(new object[7]); //lstObjSal_Grade[3] = lstCutOff.DateStart; //lstObjSal_Grade[4] = lstCutOff.DateEnd;//sửa lại store lấy lên tất cả các grade mà ko có ngày kết thúc //lstObjSal_Grade[5] = 1; //lstObjSal_Grade[6] = int.MaxValue - 1; //var lstSal_Grade = Sal_GradeServices.GetData<Sal_GradeEntity>(lstObjSal_Grade, ConstantSql.hrm_sal_sp_get_Sal_Grade, ref status).ToList(); Sys_AttOvertimePermitConfigServices Sys_Services = new Sys_AttOvertimePermitConfigServices(); double DATECLOSE_SALARY = Sys_Services.GetConfigValue<double>(AppConfig.HRM_SAL_DATECLOSE_SALARY); foreach (var profile in lstProfile) { Sal_HoldSalaryEntity entity = new Sal_HoldSalaryEntity(); entity.ProfileID = profile.ID; entity.CodeEmp = profile.CodeEmp; entity.ProfileName = profile.ProfileName; entity.CodeAttendance = profile.CodeAttendance; entity.EmployeeTypeName = profile.EmployeeTypeName; entity.WorkPlaceName = profile.WorkPlaceName; entity.IDNo = profile.IDNo; entity.MonthSalary = lstCutOff.MonthYear; entity.TimeAnalyzeID = TimeAnalyze; entity.CatNameEntity = ListNameEntity.FirstOrDefault(m => m.ID == TimeAnalyze) != null ? ListNameEntity.FirstOrDefault(m => m.ID == TimeAnalyze).NameEntityName : ""; var lstLeavedayByProfileID = lstLeaveDay.Where(s => profile.ID == s.ProfileID).ToList(); #region NV Nghỉ việc trước ngày 18 if (profile.DateQuit != null) { if (profile.DateQuit.Value <= lstCutOff.DateEnd && profile.IsSettlement != true) { //if (profile.DateQuit.Value.Day <= 18) //{ entity.Terminate = true; lstHoldSalaryEntity.Add(entity); continue; //} } } #endregion #region kiểm tra nghỉ quá 12 ngày if (lstLeavedayByProfileID.Count > 0) { var lstLeavedayTypeBy12 = lstLeavedayType.Where(m => m.Code == "SP" || m.Code == "SC" || m.Code == "DSP" || m.Code == "DL" || m.Code == "M").ToList(); var _tmp = lstLeavedayByProfileID.Where(m => !lstLeavedayTypeBy12.Any(t => t.ID == m.LeaveDayTypeID)).ToList(); double count = 0; foreach (var i in _tmp) { count += i.LeaveDays != null ? (double)i.LeaveDays : 0; } if (count >= 12) { entity.DayLeave = (int)count; lstHoldSalaryEntity.Add(entity); continue; } } #endregion #region trường hợp đăng ký thai sản trước ngày 18 if (lstLeavedayByProfileID.Count > 0) { var lstLeavedayTypeBy12 = lstLeavedayType.Where(m => m.Code == "M").ToList(); var _tmp = lstLeavedayByProfileID.FirstOrDefault(m => lstLeavedayTypeBy12.Any(t => t.ID == m.LeaveDayTypeID) && m.DateStart.Day < 18); if (_tmp != null) { entity.IsLeaveM = true; lstHoldSalaryEntity.Add(entity); continue; } } #endregion #region Nghỉ liên tục 3 ngày if (lstLeavedayByProfileID.Count > 0) { var lstLeavedayTypeBy3 = lstLeavedayType.Where(m => m.Code == "D").ToList(); //lấy ngày nghỉ theo loại ngày nghỉ D var lstLeavedayByProfileID3 = lstLeavedayByProfileID.Where(m => lstLeavedayTypeBy3.Any(t => t.ID == m.LeaveDayTypeID)).ToList(); lstLeavedayByProfileID3 = lstLeavedayByProfileID3.Where(m => m.DateEnd.Day <= DATECLOSE_SALARY).ToList(); double count = 0; //còn 1 bug đó là khi đăng ký 3 dòng liền kế ngày nhau foreach (var i in lstLeavedayByProfileID3) { count = i.LeaveDays != null ? (double)i.LeaveDays : 0; if (count >= 3) { entity.IsLeaveContinuous = true; break; } } if (entity.IsLeaveContinuous != null && entity.IsLeaveContinuous == true) { lstHoldSalaryEntity.Add(entity); continue; } } #endregion } return lstHoldSalaryEntity; #endregion } }
public DataTable GetReportComparePayroll(Guid[] cutofDurationIds, string UserLogin, string orderNumber, Guid? workPlaceID) { using (var context = new VnrHrmDataContext()) { var status = string.Empty; DataTable tb = GetReportComparePayrollSchema(cutofDurationIds, UserLogin); var objProfile = new List<object>(); objProfile.AddRange(new object[17]); objProfile[2] = orderNumber; objProfile[15] = 1; objProfile[16] = int.MaxValue - 1; var lstProfile = GetData<Hre_ProfileEntity>(objProfile, ConstantSql.hrm_hr_sp_get_ProfileAll, UserLogin, ref status).ToList(); var attCutOffDurationServices = new Att_CutOffDurationServices(); var lstObjCutOff = new List<object>(); lstObjCutOff.Add(null); lstObjCutOff.Add(1); lstObjCutOff.Add(int.MaxValue - 1); var lstCutOffDuration = attCutOffDurationServices.GetData<Att_CutOffDurationEntity>(lstObjCutOff, ConstantSql.hrm_att_sp_get_CutOffDurations, UserLogin, ref status).ToList(); if (cutofDurationIds != null) { lstCutOffDuration = lstCutOffDuration.Where(s => cutofDurationIds.Contains(s.ID)).ToList(); } if (workPlaceID != null) { lstProfile = lstProfile.Where(s => s.WorkPlaceID != null && workPlaceID.Value == s.WorkPlaceID.Value).ToList(); } List<Guid> lstProfileIds = new List<Guid>(); if (lstProfile.Count > 0) { lstProfileIds = lstProfile.Select(s => s.ID).ToList(); } List<Guid> lstAttTableIds = new List<Guid>(); var obAttTable = new List<object>(); obAttTable.AddRange(new object[7]); obAttTable[5] = 1; obAttTable[6] = int.MaxValue - 1; var lstAttTable = GetData<Att_AttendanceTableEntity>(obAttTable, ConstantSql.hrm_att_sp_get_attdancetable, UserLogin, ref status).ToList(); var objPayroll = new List<object>(); objPayroll.AddRange(new object[6]); objPayroll[4] = 1; objPayroll[5] = int.MaxValue - 1; var lstPayroll = GetData<Sal_PayrollTableEntity>(objPayroll, ConstantSql.hrm_sal_sp_get_PayrollTable, UserLogin, ref status).ToList(); List<Guid> lstPayrollIds = new List<Guid>(); if (cutofDurationIds != null) { lstAttTable = lstAttTable.Where(s => s.CutOffDurationID != null && cutofDurationIds.Contains(s.CutOffDurationID.Value) && !string.IsNullOrEmpty(s.CutOffDurationName) && lstProfileIds.Contains(s.ProfileID)).OrderByDescending(s => s.DateUpdate).ToList(); lstAttTableIds = lstAttTable.Select(s => s.ID).ToList(); lstPayroll = lstPayroll.Where(s => s.CutOffDurationID != null && cutofDurationIds.Contains(s.CutOffDurationID.Value) && !string.IsNullOrEmpty(s.CutOffDurationName) && lstProfileIds.Contains(s.ProfileID)).OrderByDescending(s => s.DateUpdate).Distinct().ToList(); lstPayrollIds = lstPayroll.Select(s => s.ID).ToList(); } var obiPayrollItem = new List<object>(); obiPayrollItem.AddRange(new object[9]); obiPayrollItem[7] = 1; obiPayrollItem[8] = int.MaxValue - 1; var lstPayrollTableItem = GetData<Sal_PayrollTableItemEntity>(obiPayrollItem, ConstantSql.hrm_sal_sp_get_PayrollTableItem, UserLogin, ref status).Where(s => s.ValueType == EnumDropDown.ElementDataType.Double.ToString()).ToList(); var objAttTableItem = new List<object>(); objAttTableItem.AddRange(new object[5]); objAttTableItem[3] = 1; objAttTableItem[4] = int.MaxValue - 1; var lstAttTableItem = GetData<Att_AttendanceTableItemEntity>(objAttTableItem, ConstantSql.hrm_att_sp_get_AttendanceTableItem, UserLogin, ref status); if (lstAttTableIds.Count > 0) { lstAttTableItem = lstAttTableItem.Where(s => lstAttTableIds.Contains(s.AttendanceTableID)).ToList(); } List<string> lstElementCode = new List<string>(); if (lstPayrollIds.Count > 0) { lstPayrollTableItem = lstPayrollTableItem.Where(s => lstPayrollIds.Contains(s.PayrollTableID)).ToList(); lstElementCode = lstPayrollTableItem.Select(s => s.Code).Distinct().ToList(); } //lấy dữ liệu bên công List<string> lstAttElement = new List<string>(); lstAttElement.Add("No. of empl"); lstAttElement.Add("No. of working days_office"); lstAttElement.Add("Overtime (hours)"); lstAttElement.Add("Overtime (convert to 100%)"); //foreach (var item in lstAttElement) //{ // DataRow dr = tb.NewRow(); // dr["ElementName"] = item; // foreach (var cutOf in lstCutOffDuration) // { // var lstAttIdsByCutOf = lstAttTable.Where(s => s.CutOffDurationID != null && s.CutOffDurationID.Value == cutOf.ID && lstProfileIds.Contains(s.ProfileID)).Select(s => s.ID).ToList(); // var lstItemByAttId = lstAttTableItem.Where(s => lstAttIdsByCutOf.Contains(s.AttendanceTableID)).ToList(); // if(tb.Columns.Contains(cutOf.CutOffDurationName)) // { // dr[cutOf.CutOffDurationName] = lstItemByAttId.Count(); // } // } //} foreach (var item in lstElementCode) { DataRow dr = tb.NewRow(); dr["ElementName"] = item; foreach (var cutOf in lstCutOffDuration) { var lstPayrollIdsByCutOf = lstPayroll.Where(s => s.CutOffDurationID != null && s.CutOffDurationID.Value == cutOf.ID && lstProfileIds.Contains(s.ProfileID)).Select(s => s.ID).ToList(); var lstItemByPayrollId = lstPayrollTableItem.Where(s => lstPayrollIdsByCutOf.Contains(s.PayrollTableID) && item == s.Code).ToList(); if (tb.Columns.Contains(cutOf.CutOffDurationName)) { dr[cutOf.CutOffDurationName] = lstItemByPayrollId.Sum(s => double.Parse(s.Value)); } } tb.Rows.Add(dr); } return tb; } }
public DataTable GetReportComparePayrollSchema(Guid[] _cutOffDurationIDs, string UserLogin) { using (var context = new VnrHrmDataContext()) { string status = string.Empty; DataTable tb = new DataTable("Sal_ReportComparePayrollEntity"); tb.Columns.Add("ElementName"); var attCutOffDurationServices = new Att_CutOffDurationServices(); var lstObjCutOff = new List<object>(); lstObjCutOff.Add(null); lstObjCutOff.Add(1); lstObjCutOff.Add(int.MaxValue - 1); var lstCutOffDuration = attCutOffDurationServices.GetData<Att_CutOffDurationEntity>(lstObjCutOff, ConstantSql.hrm_att_sp_get_CutOffDurations, UserLogin, ref status).ToList(); lstCutOffDuration = lstCutOffDuration.Where(s => _cutOffDurationIDs.Contains(s.ID)).ToList(); foreach (var item in lstCutOffDuration) { tb.Columns.Add(item.CutOffDurationName); } return tb; } }
public DataTable ComparePayroll(Guid[] _cutOffDurationIDs, Guid[] _elementIDs, string OrgOrderNumber, Guid? OrgTypeID, bool isCreateTemplate, string CompareType, string ShowDataType, Guid[] WorkingPlace, string UserLogin) { using (var context = new VnrHrmDataContext()) { string status = string.Empty; DataTable table = GetSchemaComparePayroll(_cutOffDurationIDs, _elementIDs, CompareType, OrgOrderNumber, OrgTypeID, ShowDataType, UserLogin); var E_ORGSTRUCTURE = EnumDropDown.CompareType.E_ORGSTRUCTURE.ToString(); var E_PROFILE = EnumDropDown.CompareType.E_PROFILE.ToString(); var column = EnumDropDown.ShowDataType.E_COLUMN.ToString(); var row = EnumDropDown.ShowDataType.E_ROW.ToString(); if (isCreateTemplate) { return table.ConfigTable(); } #region Group Theo Nhân Viên if (CompareType == E_PROFILE || string.IsNullOrEmpty(CompareType)) { // Xử Lý Group Phòng Ban var orgsService = new Cat_OrgStructureServices(); var lstObjOrgByOrderNumber = new List<object>(); lstObjOrgByOrderNumber.Add(OrgOrderNumber); var lstOrgByOrderNumber = orgsService.GetData<Cat_OrgStructure>(lstObjOrgByOrderNumber, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, UserLogin, ref status).Select(s => new { s.ID, s.OrgStructureTypeID, s.OrderNumber }).ToList(); if (OrgTypeID != null) { lstOrgByOrderNumber = lstOrgByOrderNumber.Where(s => s.OrgStructureTypeID.Value == OrgTypeID.Value).ToList(); } var strOrderNumer = string.Empty; var OrderNumber = string.Empty; OrderNumber = null; foreach (var OrgOrderNumer in lstOrgByOrderNumber) { strOrderNumer += OrgOrderNumer.OrderNumber + ","; } if (!string.IsNullOrEmpty(strOrderNumer)) { OrderNumber = strOrderNumer.Substring(0, strOrderNumer.Length - 1); } var profileServices = new Hre_ProfileServices(); var lstObjProfile = new List<object>(); lstObjProfile.Add(OrderNumber); lstObjProfile.Add(null); lstObjProfile.Add(null); var listProfile = profileServices.GetData<Hre_ProfileEntity>(lstObjProfile, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, UserLogin, ref status).ToList(); if (WorkingPlace != null) { listProfile = listProfile.Where(m => m.WorkPlaceID != null && WorkingPlace.Contains((Guid)m.WorkPlaceID)).ToList(); } var attCutOffDurationServices = new Att_CutOffDurationServices(); var lstObjCutOff = new List<object>(); lstObjCutOff.Add(null); lstObjCutOff.Add(1); lstObjCutOff.Add(int.MaxValue - 1); var lstCutOffDuration = attCutOffDurationServices.GetData<Att_CutOffDurationEntity>(lstObjCutOff, ConstantSql.hrm_att_sp_get_CutOffDurations, UserLogin, ref status).ToList(); lstCutOffDuration = lstCutOffDuration.Where(s => _cutOffDurationIDs.Contains(s.ID)).ToList(); var lstCutOffDurationID = lstCutOffDuration.Select(s => s.ID).ToList(); var elementServices = new Cat_ElementServices(); var lstObjElement = new List<object>(); lstObjElement.Add(null); lstObjElement.Add(null); lstObjElement.Add(null); lstObjElement.Add(null); lstObjElement.Add(null); lstObjElement.Add(1); lstObjElement.Add(int.MaxValue - 1); var lstElement = elementServices.GetData<Cat_ElementEntity>(lstObjElement, ConstantSql.hrm_cat_sp_get_Element, UserLogin, ref status).ToList(); lstElement = lstElement.Where(s => _elementIDs.Contains(s.ID)).ToList(); var payrollTableServices = new Sal_PayrollTableServices(); var lstObjPayrollTable = new List<object>(); lstObjPayrollTable.Add(null); lstObjPayrollTable.Add(null); lstObjPayrollTable.Add(null); lstObjPayrollTable.Add(null); lstObjPayrollTable.Add(1); lstObjPayrollTable.Add(int.MaxValue - 1); var lstPayrollTable = payrollTableServices.GetData<Sal_PayrollTableEntity>(lstObjPayrollTable, ConstantSql.hrm_sal_sp_get_PayrollTable, UserLogin, ref status).ToList(); var payrollTableItemServices = new Sal_PayrollTableItemServices(); var lstObjPayrollTableItem = new List<object>(); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(1); lstObjPayrollTableItem.Add(int.MaxValue - 1); var lstPayrollTableItem = payrollTableItemServices.GetData<Sal_PayrollTableItemEntity>(lstObjPayrollTableItem, ConstantSql.hrm_sal_sp_get_PayrollTableItem, UserLogin, ref status).ToList(); foreach (var profile in listProfile) { DataRow dr = table.NewRow(); dr[Sal_ComparePayrollEntity.FieldNames.CodeEmp] = profile != null ? profile.CodeEmp : string.Empty; dr[Sal_ComparePayrollEntity.FieldNames.ProfileName] = profile != null ? profile.ProfileName : string.Empty; dr[Sal_ComparePayrollEntity.FieldNames.OrgStructureName] = profile != null ? profile.OrgStructureName : string.Empty; foreach (var element in lstElement) { foreach (var cutOff in lstCutOffDuration) { var lstPayrollTableByProfileID = lstPayrollTable.Where(s => s.ProfileID == profile.ID && cutOff.ID == s.CutOffDurationID.Value).ToList(); foreach (var payrollTable in lstPayrollTableByProfileID) { var payrollTableItemData = lstPayrollTableItem.Where(s => s.PayrollTableID == payrollTable.ID).ToList(); foreach (var payrollTableItem in payrollTableItemData) { var elementData = payrollTableItemData.Where(s => element.ElementCode == s.Code).FirstOrDefault(); double value = 0; var elementName = element.ElementName + "_" + (lstCutOffDuration.IndexOf(cutOff) + 1).ToString(); if (table.Columns.Contains(elementName)) { if (elementData != null) { if (elementData.ValueType == typeof(Double).Name) { Double.TryParse(elementData.Value, out value); } } dr[elementName] = value; } } } } } table.Rows.Add(dr); } } #endregion #region Group Theo Phòng Ban if (CompareType == E_ORGSTRUCTURE) { #region Show dữ liệu theo dạng cột if (ShowDataType == column) { // Xử Lý Group Phòng Ban var orgsService = new Cat_OrgStructureServices(); var lstallorgs = orgsService.GetDataNotParam<Cat_OrgStructure>(ConstantSql.hrm_cat_sp_get_AllOrg, UserLogin, ref status).ToList(); var lstObjOrgByOrderNumber = new List<object>(); lstObjOrgByOrderNumber.Add(OrgOrderNumber); var lstOrgByOrderNumber = orgsService.GetData<Cat_OrgStructure>(lstObjOrgByOrderNumber, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, UserLogin, ref status).Select(s => new { s.ID, s.OrgStructureTypeID, s.OrderNumber, s.OrgStructureName, s.Code }).ToList(); if (OrgTypeID != null) { lstOrgByOrderNumber = lstOrgByOrderNumber.Where(s => s.OrgStructureTypeID.Value == OrgTypeID.Value).ToList(); } var profileServices = new Hre_ProfileServices(); var lstObjProfile = new List<object>(); lstObjProfile.AddRange(new object[18]); lstObjProfile[16] = 1; lstObjProfile[17] = int.MaxValue - 1; var listProfile = profileServices.GetData<Hre_ProfileEntity>(lstObjProfile, ConstantSql.hrm_hr_sp_get_Profile, UserLogin, ref status).ToList(); if (WorkingPlace != null) { listProfile = listProfile.Where(m => m.WorkPlaceID != null && WorkingPlace.Contains((Guid)m.WorkPlaceID)).ToList(); } var attCutOffDurationServices = new Att_CutOffDurationServices(); var lstObjCutOff = new List<object>(); lstObjCutOff.Add(null); lstObjCutOff.Add(1); lstObjCutOff.Add(int.MaxValue - 1); var lstCutOffDuration = attCutOffDurationServices.GetData<Att_CutOffDurationEntity>(lstObjCutOff, ConstantSql.hrm_att_sp_get_CutOffDurations, UserLogin, ref status).ToList(); lstCutOffDuration = lstCutOffDuration.Where(s => _cutOffDurationIDs.Contains(s.ID)).ToList(); var lstCutOffDurationID = lstCutOffDuration.Select(s => s.ID).ToList(); var elementServices = new Cat_ElementServices(); var lstObjElement = new List<object>(); lstObjElement.Add(null); lstObjElement.Add(null); lstObjElement.Add(null); lstObjElement.Add(null); lstObjElement.Add(null); lstObjElement.Add(1); lstObjElement.Add(int.MaxValue - 1); var lstElement = elementServices.GetData<Cat_ElementEntity>(lstObjElement, ConstantSql.hrm_cat_sp_get_Element, UserLogin, ref status).ToList(); lstElement = lstElement.Where(s => _elementIDs.Contains(s.ID)).ToList(); var payrollTableServices = new Sal_PayrollTableServices(); var lstObjPayrollTable = new List<object>(); lstObjPayrollTable.Add(null); lstObjPayrollTable.Add(null); lstObjPayrollTable.Add(null); lstObjPayrollTable.Add(null); lstObjPayrollTable.Add(1); lstObjPayrollTable.Add(int.MaxValue - 1); var lstPayrollTable = payrollTableServices.GetData<Sal_PayrollTableEntity>(lstObjPayrollTable, ConstantSql.hrm_sal_sp_get_PayrollTable, UserLogin, ref status).ToList(); var payrollTableItemServices = new Sal_PayrollTableItemServices(); var lstObjPayrollTableItem = new List<object>(); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(1); lstObjPayrollTableItem.Add(int.MaxValue - 1); var lstPayrollTableItem = payrollTableItemServices.GetData<Sal_PayrollTableItemEntity>(lstObjPayrollTableItem, ConstantSql.hrm_sal_sp_get_PayrollTableItem, UserLogin, ref status).ToList(); foreach (var org in lstOrgByOrderNumber) { DataRow dr = table.NewRow(); double total = 0; //xử lý đếm nhân viên của phòng ban con orderNumber = string.Empty; orderNumber += org.OrderNumber.ToString() + ","; getChildOrgStructure(lstallorgs, org.ID); // orderNumber += "," + org.OrderNumber.ToString(); if (orderNumber.IndexOf(',') > 0) orderNumber = orderNumber.Substring(0, orderNumber.Length - 1); var lstObjProfileByOrgOrderNumer = new List<object>(); lstObjProfileByOrgOrderNumer.Add(orderNumber); lstObjProfileByOrgOrderNumer.Add(null); lstObjProfileByOrgOrderNumer.Add(null); var listProfileByOrderNumber = profileServices.GetData<Hre_ProfileEntity>(lstObjProfileByOrgOrderNumer, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, UserLogin, ref status).Select(s => s.ID).ToList(); dr[Sal_ComparePayrollEntity.FieldNames.OrgStructureCode] = org != null ? org.Code : string.Empty; dr[Sal_ComparePayrollEntity.FieldNames.OrgStructureName] = org != null ? org.OrgStructureName : string.Empty; dr[Sal_ComparePayrollEntity.FieldNames.GroupNumberProfile] = listProfile.Where(m => m.OrgStructureID == org.ID).Count(); var lstProfileByOrgID = listProfile.Where(s => listProfileByOrderNumber.Contains(s.ID)).ToList(); foreach (var profile in lstProfileByOrgID) { foreach (var element in lstElement) { foreach (var cutOff in lstCutOffDuration) { var lstPayrollTableByProfileID = lstPayrollTable.Where(s => s.ProfileID == profile.ID && cutOff.ID == s.CutOffDurationID.Value).ToList(); foreach (var payrollTable in lstPayrollTableByProfileID) { var payrollTableItemData = lstPayrollTableItem.Where(s => s.PayrollTableID == payrollTable.ID).ToList(); foreach (var payrollTableItem in payrollTableItemData) { var elementData = payrollTableItemData.Where(s => element.ElementCode == s.Code).FirstOrDefault(); double value = 0; var elementName = element.ElementName + "_" + (lstCutOffDuration.IndexOf(cutOff) + 1).ToString(); if (table.Columns.Contains(elementName)) { if (elementData != null) { if (elementData.ValueType == typeof(Double).Name) { Double.TryParse(elementData.Value, out value); } } total += value; dr[elementName] = total; } } } } } } table.Rows.Add(dr); } } #endregion #region Show dữ liệu theo dạng Dòng if (ShowDataType == row) { // Xử Lý Group Phòng Ban var orgsService = new Cat_OrgStructureServices(); var lstallorgs = orgsService.GetDataNotParam<Cat_OrgStructure>(ConstantSql.hrm_cat_sp_get_AllOrg, UserLogin, ref status).ToList(); var lstObjOrgByOrderNumber = new List<object>(); lstObjOrgByOrderNumber.Add(OrgOrderNumber); var lstOrgByOrderNumber = orgsService.GetData<Cat_OrgStructure>(lstObjOrgByOrderNumber, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, UserLogin, ref status).Select(s => new { s.ID, s.OrgStructureTypeID, s.OrderNumber, s.OrgStructureName, s.Code }).ToList(); if (OrgTypeID != null) { lstOrgByOrderNumber = lstOrgByOrderNumber.Where(s => s.OrgStructureTypeID.Value == OrgTypeID.Value).ToList(); } var profileServices = new Hre_ProfileServices(); var lstObjProfile = new List<object>(); lstObjProfile.AddRange(new object[18]); lstObjProfile[16] = 1; lstObjProfile[17] = int.MaxValue - 1; var listProfile = profileServices.GetData<Hre_ProfileEntity>(lstObjProfile, ConstantSql.hrm_hr_sp_get_Profile, UserLogin, ref status).ToList(); var attCutOffDurationServices = new Att_CutOffDurationServices(); var lstObjCutOff = new List<object>(); lstObjCutOff.Add(null); lstObjCutOff.Add(1); lstObjCutOff.Add(int.MaxValue - 1); var lstCutOffDuration = attCutOffDurationServices.GetData<Att_CutOffDurationEntity>(lstObjCutOff, ConstantSql.hrm_att_sp_get_CutOffDurations, UserLogin, ref status).ToList(); lstCutOffDuration = lstCutOffDuration.Where(s => _cutOffDurationIDs.Contains(s.ID)).ToList(); var lstCutOffDurationID = lstCutOffDuration.Select(s => s.ID).ToList(); var elementServices = new Cat_ElementServices(); var lstObjElement = new List<object>(); lstObjElement.Add(null); lstObjElement.Add(null); lstObjElement.Add(null); lstObjElement.Add(null); lstObjElement.Add(null); lstObjElement.Add(1); lstObjElement.Add(int.MaxValue - 1); var lstElement = elementServices.GetData<Cat_ElementEntity>(lstObjElement, ConstantSql.hrm_cat_sp_get_Element, UserLogin, ref status).ToList(); lstElement = lstElement.Where(s => _elementIDs.Contains(s.ID)).ToList(); var payrollTableServices = new Sal_PayrollTableServices(); var lstObjPayrollTable = new List<object>(); lstObjPayrollTable.Add(null); lstObjPayrollTable.Add(null); lstObjPayrollTable.Add(null); lstObjPayrollTable.Add(null); lstObjPayrollTable.Add(1); lstObjPayrollTable.Add(int.MaxValue - 1); var lstPayrollTable = payrollTableServices.GetData<Sal_PayrollTableEntity>(lstObjPayrollTable, ConstantSql.hrm_sal_sp_get_PayrollTable, UserLogin, ref status).ToList(); var payrollTableItemServices = new Sal_PayrollTableItemServices(); var lstObjPayrollTableItem = new List<object>(); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(null); lstObjPayrollTableItem.Add(1); lstObjPayrollTableItem.Add(int.MaxValue - 1); var lstPayrollTableItem = payrollTableItemServices.GetData<Sal_PayrollTableItemEntity>(lstObjPayrollTableItem, ConstantSql.hrm_sal_sp_get_PayrollTableItem, UserLogin, ref status).ToList(); foreach (var org in lstOrgByOrderNumber) { DataRow dr = table.NewRow(); //xử lý đếm nhân viên của phòng ban con orderNumber = string.Empty; orderNumber += org.OrderNumber.ToString() + ","; getChildOrgStructure(lstallorgs, org.ID); // orderNumber += "," + org.OrderNumber.ToString(); if (orderNumber.IndexOf(',') > 0) orderNumber = orderNumber.Substring(0, orderNumber.Length - 1); var lstObjProfileByOrgOrderNumer = new List<object>(); lstObjProfileByOrgOrderNumer.Add(orderNumber); lstObjProfileByOrgOrderNumer.Add(null); lstObjProfileByOrgOrderNumer.Add(null); var listProfileByOrderNumber = profileServices.GetData<Hre_ProfileEntity>(lstObjProfileByOrgOrderNumer, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, UserLogin, ref status).Select(s => s.ID).ToList(); dr[Sal_ComparePayrollEntity.FieldNames.OrgStructureCode] = org != null ? org.Code : string.Empty; dr[Sal_ComparePayrollEntity.FieldNames.OrgStructureName] = org != null ? org.OrgStructureName : string.Empty; dr[Sal_ComparePayrollEntity.FieldNames.GroupNumberProfile] = listProfile.Where(m => m.OrgStructureID == org.ID).Count(); table.Rows.Add(dr); var lstProfileByOrgID = listProfile.Where(s => listProfileByOrderNumber.Contains(s.ID)).ToList(); var lstProfileID = lstProfileByOrgID.Select(s => s.ID).ToList(); foreach (var element in lstElement) { DataRow dr1 = table.NewRow(); double total = 0; dr1[Sal_ComparePayrollEntity.FieldNames.ElementName] = element.ElementCode; foreach (var cutOff in lstCutOffDuration) { var lstPayrollTableByProfileID = lstPayrollTable.Where(s => lstProfileID.Contains(s.ProfileID) && cutOff.ID == s.CutOffDurationID.Value).ToList(); foreach (var payrollTable in lstPayrollTableByProfileID) { var payrollTableItemData = lstPayrollTableItem.Where(s => s.PayrollTableID == payrollTable.ID && element.ElementCode == s.Code).ToList(); foreach (var payrollTableItem in payrollTableItemData) { double value = 0; var elementName = element.ElementName + "_" + (lstCutOffDuration.IndexOf(cutOff) + 1).ToString(); if (table.Columns.Contains(cutOff.CutOffDurationName)) { if (payrollTableItem.ValueType == typeof(Double).Name) { Double.TryParse(payrollTableItem.Value, out value); } total += value; dr1[cutOff.CutOffDurationName] = total; } } } } if (total > 0) { table.Rows.Add(dr1); } } } } #endregion } #endregion //Dictionary<string, Dictionary<string, object>> configs = new Dictionary<string, Dictionary<string, object>>(); //Dictionary<string, object> config = new Dictionary<string, object>(); //config.Add("locked", true); //configs.Add("CodeEmp", config); //configs.Add("ProfileName", config); //configs.Add("OrgStructureCode", config); //configs.Add("OrgStructureName", config); return table.ConfigTable(true); } }
public DataTable GetSchemaComparePayroll(Guid[] _cutOffDurationIDs, Guid[] _elementIDs, string CompareType, string orderNumber, Guid? OrgTypeID, string ShowDataType, string UserLogin) { using (var context = new VnrHrmDataContext()) { string status = string.Empty; DataTable tb = new DataTable("Sal_ComparePayrollModel"); var E_ORGSTRUCTURE = EnumDropDown.CompareType.E_ORGSTRUCTURE.ToString(); var E_PROFILE = EnumDropDown.CompareType.E_PROFILE.ToString(); var column = EnumDropDown.ShowDataType.E_COLUMN.ToString(); var row = EnumDropDown.ShowDataType.E_ROW.ToString(); #region Group Theo Nhân Viên if (CompareType == E_PROFILE || string.IsNullOrEmpty(CompareType)) { tb.Columns.Add(Sal_ComparePayrollEntity.FieldNames.CodeEmp, typeof(string)); tb.Columns.Add(Sal_ComparePayrollEntity.FieldNames.ProfileName, typeof(string)); tb.Columns.Add(Sal_ComparePayrollEntity.FieldNames.OrgStructureName, typeof(string)); var attCutOffDurationServices = new Att_CutOffDurationServices(); var lstObjCutOff = new List<object>(); lstObjCutOff.Add(null); lstObjCutOff.Add(1); lstObjCutOff.Add(int.MaxValue - 1); var lstCutOffDuration = attCutOffDurationServices.GetData<Att_CutOffDurationEntity>(lstObjCutOff, ConstantSql.hrm_att_sp_get_CutOffDurations, UserLogin, ref status).ToList(); lstCutOffDuration = lstCutOffDuration.Where(s => _cutOffDurationIDs.Contains(s.ID)).ToList(); var elementServices = new Cat_ElementServices(); var lstObjElement = new List<object>(); lstObjElement.Add(null); lstObjElement.Add(null); lstObjElement.Add(null); lstObjElement.Add(null); lstObjElement.Add(null); lstObjElement.Add(1); lstObjElement.Add(int.MaxValue - 1); var lstElement = elementServices.GetData<Cat_ElementEntity>(lstObjElement, ConstantSql.hrm_cat_sp_get_Element, UserLogin, ref status).ToList(); lstElement = lstElement.Where(s => _elementIDs.Contains(s.ID)).ToList(); foreach (var item in lstElement) { foreach (var cutOff in lstCutOffDuration) { var elementName = item.ElementName + "_" + (lstCutOffDuration.IndexOf(cutOff) + 1).ToString(); if (!tb.Columns.Contains(elementName)) { tb.Columns.Add(elementName, typeof(double)); } } } } #endregion #region Group Theo Phòng Ban if (CompareType == E_ORGSTRUCTURE) { if (ShowDataType == column) { tb.Columns.Add(Sal_ComparePayrollEntity.FieldNames.OrgStructureCode, typeof(string)); tb.Columns.Add(Sal_ComparePayrollEntity.FieldNames.OrgStructureName, typeof(string)); tb.Columns.Add(Sal_ComparePayrollEntity.FieldNames.GroupNumberProfile, typeof(int)); var attCutOffDurationServices = new Att_CutOffDurationServices(); var lstObjCutOff = new List<object>(); lstObjCutOff.Add(null); lstObjCutOff.Add(1); lstObjCutOff.Add(int.MaxValue - 1); var lstCutOffDuration = attCutOffDurationServices.GetData<Att_CutOffDurationEntity>(lstObjCutOff, ConstantSql.hrm_att_sp_get_CutOffDurations, UserLogin, ref status).ToList(); lstCutOffDuration = lstCutOffDuration.Where(s => _cutOffDurationIDs.Contains(s.ID)).ToList(); var elementServices = new Cat_ElementServices(); var lstObjElement = new List<object>(); lstObjElement.Add(null); lstObjElement.Add(null); lstObjElement.Add(null); lstObjElement.Add(null); lstObjElement.Add(null); lstObjElement.Add(1); lstObjElement.Add(int.MaxValue - 1); var lstElement = elementServices.GetData<Cat_ElementEntity>(lstObjElement, ConstantSql.hrm_cat_sp_get_Element, UserLogin, ref status).ToList(); lstElement = lstElement.Where(s => _elementIDs.Contains(s.ID)).ToList(); foreach (var item in lstElement) { foreach (var cutOff in lstCutOffDuration) { var elementName = item.ElementName + "_" + (lstCutOffDuration.IndexOf(cutOff) + 1).ToString(); if (!tb.Columns.Contains(elementName)) { tb.Columns.Add(elementName, typeof(double)); } } } } if (ShowDataType == row) { tb.Columns.Add(Sal_ComparePayrollEntity.FieldNames.OrgStructureCode, typeof(string)); tb.Columns.Add(Sal_ComparePayrollEntity.FieldNames.OrgStructureName, typeof(string)); tb.Columns.Add(Sal_ComparePayrollEntity.FieldNames.ElementName, typeof(string)); tb.Columns.Add(Sal_ComparePayrollEntity.FieldNames.GroupNumberProfile, typeof(int)); var attCutOffDurationServices = new Att_CutOffDurationServices(); var lstObjCutOff = new List<object>(); lstObjCutOff.Add(null); lstObjCutOff.Add(1); lstObjCutOff.Add(int.MaxValue - 1); var lstCutOffDuration = attCutOffDurationServices.GetData<Att_CutOffDurationEntity>(lstObjCutOff, ConstantSql.hrm_att_sp_get_CutOffDurations, UserLogin, ref status).ToList(); lstCutOffDuration = lstCutOffDuration.Where(s => _cutOffDurationIDs.Contains(s.ID)).ToList(); foreach (var item in lstCutOffDuration) { if (!tb.Columns.Contains(item.CutOffDurationName)) { tb.Columns.Add(item.CutOffDurationName, typeof(double)); } } } } #endregion return tb; } }