public async Task <ApiResponse> Handle(GetApprovedPayrollByMonthQuery request, CancellationToken cancellationToken) { ApiResponse response = new ApiResponse(); try { List <EmployeePaymentTypes> userdetail = await _dbContext .EmployeePaymentTypes .Where(x => x.PayrollMonth == request.Month && x.PayrollYear == request.Year && x.OfficeId == request.OfficeId && x.IsDeleted == false) .Include(x => x.EmployeeDetail).ToListAsync(); List <EmployeeMonthlyPayrollModel> userList = new List <EmployeeMonthlyPayrollModel>(); foreach (EmployeePaymentTypes item in userdetail) { EmployeeMonthlyPayrollModel obj = new EmployeeMonthlyPayrollModel() { EmployeeId = Convert.ToInt32(item.EmployeeID), EmployeeName = item.EmployeeDetail?.EmployeeName, PaymentType = Convert.ToInt32(item.PaymentType), WorkingDays = item.Attendance, PresentDays = item.Attendance, AbsentDays = item.Absent, LeaveHours = item.LeaveDays == null ? 0 : item.LeaveDays.Value, TotalWorkHours = item.TotalDuration, HourlyRate = item.BasicPay, TotalGeneralAmount = item.BasicPay, TotalAllowance = item.TotalAllowance, TotalDeduction = item.TotalDeduction, GrossSalary = item.GrossSalary, OverTimeHours = item.OverTimeHours, PensionRate = item.PensionRate, SalaryTax = item.SalaryTax, PensionAmount = Math.Round(Convert.ToDouble(item.PensionAmount), 2), NetSalary = item.NetSalary, AdvanceAmount = item.AdvanceAmount, IsAdvanceApproved = item.IsAdvanceApproved == null ? false : item.IsAdvanceApproved.Value, AdvanceRecoveryAmount = item.AdvanceRecoveryAmount ?? 0, IsAdvanceRecovery = item.IsAdvanceRecovery == true ? false : true, CurrencyCode = item.CurrencyCode, CurrencyId = item.CurrencyId, EmployeeCode = item.EmployeeDetail.EmployeeCode }; obj.EmployeePayrollList = new List <EmployeePayrollModel>(); List <EmployeePayrollModel> EmployeePayrollMonthList = await _dbContext.EmployeePayrollMonth .Include(x => x.SalaryHeadDetails) .Where(x => x.EmployeeID == item.EmployeeID && x.Date.Year == item.PayrollYear && x.Date.Month == item.PayrollMonth && x.IsDeleted == false) .Select(x => new EmployeePayrollModel { CurrencyId = x.CurrencyId, EmployeeId = x.EmployeeID, MonthlyAmount = x.MonthlyAmount, PayrollId = x.MonthlyPayrollId, SalaryHeadId = x.SalaryHeadId, PensionRate = item.PensionRate, HeadTypeId = x.HeadTypeId == null ? 0 : x.HeadTypeId.Value, SalaryHeadType = x.SalaryHeadDetails.HeadTypeId == (int)SalaryHeadType.ALLOWANCE ? "Allowance" : x.SalaryHeadDetails.HeadTypeId == (int)SalaryHeadType.DEDUCTION ? "Deduction" : x.SalaryHeadDetails.HeadTypeId == (int)SalaryHeadType.GENERAL ? "General" : "", SalaryHead = x.SalaryHeadDetails.HeadName, AccountNo = x.AccountNo, TransactionTypeId = x.TransactionTypeId }).OrderBy(x => x.TransactionTypeId).ThenBy(x => x.SalaryHeadType).ToListAsync(); obj.EmployeePayrollList.AddRange(EmployeePayrollMonthList); userList.Add(obj); } response.data.EmployeeMonthlyPayrollApprovedList = userList; response.StatusCode = StaticResource.successStatusCode; response.Message = "Success"; } catch (Exception ex) { response.StatusCode = StaticResource.failStatusCode; response.Message = ex.Message; } return(response); }
public async Task <ApiResponse> Handle(GetEmployeesMonthlyPayrollQuery request, CancellationToken cancellationToken) { ApiResponse response = new ApiResponse(); try { ICollection <EmployeeMonthlyAttendance> empPayrollAttendanceList = await _dbContext.EmployeeMonthlyAttendance .Include(x => x.EmployeeDetails) .Where(x => x.OfficeId == request.OfficeId && x.Month == request.Month && x.Year == request.Year && x.IsDeleted == false && x.IsApproved == false && x.EmployeeDetails.IsDeleted == false) .ToListAsync(); //Note: default 0.045 i.e. (4.5 %) double?pensionRate = _dbContext.EmployeePensionRate.FirstOrDefault(x => x.IsDefault == true && x.IsDeleted == false)?.PensionRate; List <EmployeeMonthlyPayrollModel> payrollFinal = new List <EmployeeMonthlyPayrollModel>(); foreach (EmployeeMonthlyAttendance payrollAttendance in empPayrollAttendanceList) { List <EmployeePayrollModel> payrollDetail = new List <EmployeePayrollModel>(); var payroll = await _dbContext.EmployeePayroll.Include(x => x.SalaryHeadDetails).Where(x => x.EmployeeID == payrollAttendance.EmployeeId && x.IsDeleted == false).ToListAsync(); if (payroll.Any(x => x.AccountNo == null)) { throw new Exception($"Payroll details not set for Employee code: {payrollAttendance.EmployeeDetails.EmployeeCode}"); } payrollDetail = payroll.Select(x => new EmployeePayrollModel { PayrollId = x.PayrollId, CurrencyId = x.CurrencyId ?? 0, EmployeeId = x.EmployeeID, HeadTypeId = x.SalaryHeadDetails.HeadTypeId, IsDeleted = x.IsDeleted, MonthlyAmount = x.MonthlyAmount ?? 0, PaymentType = 2, //hourly PensionRate = pensionRate != null ? pensionRate : DefaultValues.DefaultPensionRate, SalaryHeadId = x.SalaryHeadId ?? 0, SalaryHeadType = x.SalaryHeadDetails.HeadTypeId == (int)SalaryHeadType.ALLOWANCE ? "Allowance" : x.SalaryHeadDetails.HeadTypeId == (int)SalaryHeadType.DEDUCTION ? "Deduction" : x.SalaryHeadDetails.HeadTypeId == (int)SalaryHeadType.GENERAL ? "General" : "", SalaryHead = x.SalaryHeadDetails.HeadName, AccountNo = x.AccountNo, TransactionTypeId = x.TransactionTypeId }).OrderBy(x => x.TransactionTypeId).ThenBy(x => x.SalaryHeadType).ToList(); if (payrollDetail.Count > 0) { if (payrollAttendance.GrossSalary == 0 || payrollAttendance.GrossSalary == null) { int iCurrencyId = payrollDetail.FirstOrDefault(x => x.HeadTypeId == 3).CurrencyId; EmployeeMonthlyPayrollModel obj = new EmployeeMonthlyPayrollModel(); obj.EmployeeId = payrollAttendance.EmployeeId.Value; obj.EmployeeCode = payrollAttendance.EmployeeDetails.EmployeeCode; obj.EmployeeName = payrollAttendance.EmployeeDetails.EmployeeName; obj.PaymentType = 2; obj.AbsentDays = payrollAttendance.AbsentHours == null ? 0 : payrollAttendance.AbsentHours.Value; obj.LeaveDays = payrollAttendance.LeaveHours == null ? 0 : payrollAttendance.LeaveHours.Value; obj.PresentDays = payrollAttendance.AttendanceHours == null ? 0 : payrollAttendance.AttendanceHours.Value + Convert.ToInt32(Math.Floor((float)payrollAttendance.AttendanceMinutes / 60f)); obj.LeaveHours = payrollAttendance.LeaveHours == null ? 0 : payrollAttendance.LeaveHours.Value; obj.WorkingDays = payrollAttendance.AttendanceHours == null ? 0 : payrollAttendance.AttendanceHours.Value + Convert.ToInt32(Math.Floor((float)payrollAttendance.AttendanceMinutes / 60f)); obj.TotalWorkHours = payrollAttendance.TotalDuration == null ? 0 : payrollAttendance.TotalDuration.Value; obj.OverTimeHours = payrollAttendance.OvertimeHours == null ? 0 : payrollAttendance.OvertimeHours.Value + Math.Floor(((float)payrollAttendance.OverTimeMinutes / 60f)); obj.IsAdvanceRecovery = payrollAttendance.IsAdvanceRecovery; obj.AdvanceAmount = payrollAttendance.AdvanceAmount; obj.CurrencyId = payrollDetail.FirstOrDefault().CurrencyId; obj.TotalAllowance = payrollDetail.Where(x => x.HeadTypeId == (int)SalaryHeadType.ALLOWANCE).Sum(s => s.MonthlyAmount); obj.TotalDeduction = payrollDetail.Where(x => x.HeadTypeId == (int)SalaryHeadType.DEDUCTION).Sum(s => s.MonthlyAmount); obj.TotalGeneralAmount = payrollDetail.Where(x => x.HeadTypeId == (int)SalaryHeadType.GENERAL).Sum(s => s.MonthlyAmount); if (obj.TotalGeneralAmount == 0) { throw new Exception($"Basic Pay not defined for Employee Code-{payrollAttendance.EmployeeDetails.EmployeeCode}"); } double convertMinutesToHours = Math.Round(((double)(payrollAttendance.OverTimeMinutes + payrollAttendance.AttendanceMinutes) / 60d), 2); obj.GrossSalary = Math.Round((double)(obj.TotalGeneralAmount * (payrollAttendance.AttendanceHours.Value + obj.LeaveHours + payrollAttendance.OvertimeHours.Value + convertMinutesToHours) + obj.TotalAllowance), 2); obj.PensionAmount = Math.Round(((double)(obj.GrossSalary * payrollDetail.FirstOrDefault().PensionRate) / 100), 2); // i.e. 4.5 % => 0.045 // eliminate hours and only show minutes if minutes is 60 we already added them to overtime hours so minutes = 0 decimal overtimeHour = Math.Round((decimal)((float)payrollAttendance.OverTimeMinutes / 60f), 2); obj.OvertimeMinutes = Convert.ToInt32((overtimeHour - Math.Truncate(overtimeHour)) * 60); // eliminate hours and only show minutes if minutes is 60 we already added them to AttendanceHours so minutes = 0 decimal attendanceMinutes = Math.Round((decimal)((float)payrollAttendance.AttendanceMinutes / 60f), 2); obj.WorkingMinutes = Convert.ToInt32((attendanceMinutes - Math.Truncate(attendanceMinutes)) * 60); if (obj.GrossSalary > 5000) { double? dExchangeRate1 = 0.0; ExchangeRateDetail exchangeRateDetail = _dbContext.ExchangeRateDetail.OrderByDescending(x => x.Date).FirstOrDefault(x => x.FromCurrency == iCurrencyId && x.ToCurrency == (int)Currency.AFG); if (exchangeRateDetail == null) { string currencyCode = _dbContext.CurrencyDetails.FirstOrDefault(x => x.IsDeleted == false && x.CurrencyId == iCurrencyId).CurrencyCode; throw new Exception($"Exchange Rate Not Defined from {currencyCode} to AFG"); } else { dExchangeRate1 = (double)exchangeRateDetail.Rate; } obj.SalaryTax = obj.SalaryTax == null ? 0 : obj.SalaryTax; obj.SalaryTax = Math.Round(Convert.ToDouble((StaticFunctions.SalaryCalculate(obj.GrossSalary.Value, dExchangeRate1.Value))), 2); } else { obj.SalaryTax = 0; } //Net Salary = (Gross + Allowances) - Deductions obj.NetSalary = Math.Round((double)(obj.GrossSalary - (obj.TotalDeduction != null ? obj.TotalDeduction : 0) - (obj.SalaryTax != null ? obj.SalaryTax : 0) - payrollAttendance.AdvanceRecoveryAmount - (obj.PensionAmount != null ? obj.PensionAmount : 0)), 2); obj.EmployeePayrollList.AddRange(payrollDetail); payrollFinal.Add(obj); Advances xAdvances = await _dbContext.Advances.FirstOrDefaultAsync(x => x.IsDeleted == false && x.IsApproved == true && x.EmployeeId == payrollAttendance.EmployeeId && x.OfficeId == payrollAttendance.OfficeId && x.AdvanceDate < DateTime.Now && x.IsDeducted == false); if (xAdvances != null) { if (xAdvances.RecoveredAmount == 0) { if (xAdvances.NumberOfInstallments == 0) { xAdvances.NumberOfInstallments = 1; } obj.AdvanceRecoveryAmount = Math.Round((Convert.ToDouble(xAdvances.AdvanceAmount / xAdvances.NumberOfInstallments ?? 1)), 2); obj.AdvanceAmount = xAdvances.AdvanceAmount; obj.IsAdvanceApproved = xAdvances.IsApproved; } else { Double iBalanceAmount = xAdvances.AdvanceAmount - xAdvances.RecoveredAmount; obj.AdvanceRecoveryAmount = Math.Round((Convert.ToDouble(iBalanceAmount / xAdvances.NumberOfInstallments)), 2); obj.IsAdvanceApproved = xAdvances.IsApproved; obj.AdvanceAmount = iBalanceAmount; } } else { obj.AdvanceRecoveryAmount = 0; obj.AdvanceAmount = 0; obj.IsAdvanceApproved = false; } } else { EmployeeMonthlyPayrollModel obj = new EmployeeMonthlyPayrollModel(); obj.AbsentDays = payrollAttendance.AbsentHours == null ? 0 : payrollAttendance.AbsentHours.Value; obj.OverTimeHours = payrollAttendance.OvertimeHours; obj.AdvanceAmount = payrollAttendance.AdvanceAmount; obj.AdvanceRecoveryAmount = payrollAttendance.AdvanceRecoveryAmount; obj.CurrencyId = payrollDetail[0].CurrencyId; obj.EmployeeId = payrollAttendance.EmployeeId.Value; obj.EmployeeCode = payrollAttendance.EmployeeDetails.EmployeeCode; obj.EmployeeName = payrollAttendance.EmployeeDetails.EmployeeName; obj.GrossSalary = payrollAttendance.GrossSalary == null ? 0 : payrollAttendance.GrossSalary; obj.IsAdvanceApproved = payrollAttendance.IsAdvanceApproved; obj.IsAdvanceRecovery = payrollAttendance.IsAdvanceRecovery; obj.LeaveDays = payrollAttendance.LeaveHours == null ? 0 : payrollAttendance.LeaveHours.Value; obj.LeaveHours = payrollAttendance.LeaveHours == null ? 0 : payrollAttendance.LeaveHours.Value; obj.NetSalary = payrollAttendance.NetSalary == null ? 0 : payrollAttendance.NetSalary; obj.PaymentType = 2; obj.PensionAmount = payrollAttendance.PensionAmount == null ? 0 : payrollAttendance.PensionAmount; obj.PresentDays = payrollAttendance.AttendanceHours == null ? 0 : payrollAttendance.AttendanceHours.Value; obj.SalaryTax = payrollAttendance.SalaryTax == null ? 0 : payrollAttendance.SalaryTax.Value; obj.TotalAllowance = payrollAttendance.TotalAllowance == null ? 0 : payrollAttendance.TotalAllowance.Value; obj.TotalDeduction = payrollAttendance.TotalDeduction == null ? 0 : payrollAttendance.TotalDeduction.Value; obj.TotalGeneralAmount = payrollAttendance.TotalGeneralAmount == null ? 0 : payrollAttendance.TotalGeneralAmount.Value; obj.WorkingDays = payrollAttendance.AttendanceHours == null ? 0 : payrollAttendance.AttendanceHours.Value; obj.TotalWorkHours = payrollAttendance.TotalDuration == null ? 0 : payrollAttendance.TotalDuration.Value; obj.OvertimeMinutes = payrollAttendance.OverTimeMinutes; // eliminate hours and only show minutes obj.WorkingMinutes = payrollAttendance.AttendanceMinutes; // eliminate hours and only show minutes obj.EmployeePayrollList.AddRange(payrollDetail.Where(x => x.EmployeeId == obj.EmployeeId)); payrollFinal.Add(obj); } } else { throw new Exception($"Employee Payroll not defined for Employee Code-{payrollAttendance.EmployeeDetails.EmployeeCode}"); } } response.data.EmployeeMonthlyPayrolllist = payrollFinal; response.StatusCode = StaticResource.successStatusCode; response.Message = "Success"; } catch (Exception ex) { response.StatusCode = StaticResource.failStatusCode; response.Message = ex.Message; } return(response); }