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