public async Task <ApiResponse> Handle(GetEmployeeSalaryTaxDetailQuery request, CancellationToken cancellationToken)
{
ApiResponse response = new ApiResponse();
try
{
var financialYear = await _dbContext.FinancialYearDetail.Where(x => x.IsDeleted == false && request.FinancialYearId.Contains(x.FinancialYearId)).ToListAsync();
if (financialYear.Any())
{
List <SalaryTaxReportModel> salaryTaxReportListFinal = new List <SalaryTaxReportModel>();
foreach (FinancialYearDetail financialYearDetail in financialYear)
{
List <SalaryTaxReportModel> salaryTaxReportList = _dbContext.EmployeePaymentTypes.Where(x => x.IsDeleted == false && x.IsApproved == true && x.OfficeId == request.OfficeId && x.EmployeeID == request.EmployeeId && x.PayrollYear == financialYearDetail.StartDate.Date.Year)
.Select(x => new SalaryTaxReportModel
{
Currency = _dbContext.CurrencyDetails.Where(o => o.CurrencyId == x.CurrencyId).FirstOrDefault().CurrencyName,
CurrencyId = x.CurrencyId,
Office = _dbContext.OfficeDetail.Where(o => o.OfficeId == x.OfficeId).FirstOrDefault().OfficeName,
Date = new DateTime(x.PayrollYear.Value, x.PayrollMonth.Value, 1),
TotalTax = x.SalaryTax
}).OrderBy(x => x.Date).ToList();
foreach (SalaryTaxReportModel item in salaryTaxReportList)
{
ExchangeRateDetail exchangeRate = await _dbContext.ExchangeRateDetail.OrderByDescending(x => x.Date).FirstOrDefaultAsync(x => x.IsDeleted == false && x.FromCurrency == item.CurrencyId && x.ToCurrency == request.CurrencyId);
if (item.CurrencyId != request.CurrencyId)
{
item.TotalTax = item.TotalTax * (double)exchangeRate.Rate;
}
}
if (salaryTaxReportList.Any())
{
salaryTaxReportListFinal.AddRange(salaryTaxReportList);
}
}
response.data.SalaryTaxReportModelList = salaryTaxReportListFinal;
}
else
{
response.data.SalaryTaxReportModelList = null;
}
response.StatusCode = StaticResource.successStatusCode;
response.Message = "Success";
}
catch (Exception ex)
{
response.StatusCode = StaticResource.failStatusCode;
response.Message = ex.Message;
}
return(response);
}
public void DoRefreshCommand()
{
if (!ShowProgressBar)
{
if (ExchangeRateDetail != null)
{
ExchangeRateDetail.Clear();
}
LoadData(true);
}
}
public static async Task <long> SaveAsync(string tenant, int officeId, string baseCurrency,
List <ExchangeRateViewModel> exchangeRates)
{
long exchangeRateId = 0;
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
db.BeginTransaction();
try
{
var sql = new Sql("UPDATE finance.exchange_rates SET status = @0 WHERE office_id=@1", false, officeId);
await db.ExecuteAsync(sql);
var exchangeRate = new ExchangeRate
{
OfficeId = officeId,
Status = true,
UpdatedOn = DateTimeOffset.UtcNow
};
var awaiter =
await db.InsertAsync("finance.exchange_rates", "exchange_rate_id", true, exchangeRate).ConfigureAwait(false);
exchangeRateId = awaiter.To <long>();
foreach (var item in exchangeRates)
{
var detail = new ExchangeRateDetail
{
ExchangeRateId = exchangeRateId,
LocalCurrencyCode = baseCurrency,
ForeignCurrencyCode = item.CurrencyCode,
ExchangeRate = item.Rate,
Unit = 1
};
await db.InsertAsync("finance.exchange_rate_details", "exchange_rate_detail_id", true, detail);
}
db.CompleteTransaction();
}
catch (Exception)
{
db.AbortTransaction();
}
return(exchangeRateId);
}
}
public ExchangeRateDetail AddReverseExchangeRateAfterManipulation(GenerateExchangeRateModel GenerateExchangeRate, string userId)
{
ExchangeRateDetail exchangeRate = new ExchangeRateDetail();
exchangeRate.CreatedById = userId;
exchangeRate.CreatedDate = DateTime.UtcNow;
exchangeRate.Date = GenerateExchangeRate.Date;
exchangeRate.ToCurrency = GenerateExchangeRate.FromCurrencyId;
exchangeRate.FromCurrency = GenerateExchangeRate.ToCurrencyId;
exchangeRate.Rate = 1 / (decimal)GenerateExchangeRate.Rate;
exchangeRate.IsDeleted = false;
return(exchangeRate);
}
public async Task <ApiResponse> Handle(GetProjectProposalAmountSummaryQuery request, CancellationToken cancellationToken)
{
ApiResponse response = new ApiResponse();
List <ProjectProposalAmountSummary> projectProposalAmountSummary = new List <ProjectProposalAmountSummary>();
try
{
string startDate = request.StartDate == null ? string.Empty : request.StartDate.ToString();
string dueDate = request.DueDate == null ? string.Empty : request.DueDate.ToString();
//get GetProjectProposalAmountSummary from sp get_projectproposalreport by passing parameters
var spAmountSummaryInCommonCurrency = await _dbContext.LoadStoredProc("get_projectproposalreportamountsummary")
.WithSqlParam("projectname", request.ProjectName)
.WithSqlParam("startdate", startDate)
.WithSqlParam("enddate", dueDate)
.WithSqlParam("startdatefilteroption", request.StartDateFilterOption)
.WithSqlParam("duedatefilteroption", request.DueDateFilterOption)
.WithSqlParam("currencyid", request.CurrencyId)
.WithSqlParam("amount", request.Amount)
.WithSqlParam("amountfilteroption", request.AmountFilterOption)
.WithSqlParam("iscompleted", request.IsCompleted)
.WithSqlParam("islate", request.IsLate)
.ExecuteStoredProc <SPProjectProposalReportAmountSummaryModel>();
var currencyTask = _dbContext.CurrencyDetails.ToListAsync();
if (spAmountSummaryInCommonCurrency.Any())
{
int amountSummaryCurrencyId = spAmountSummaryInCommonCurrency.FirstOrDefault().ProjectCurrency;
double totalAmount = spAmountSummaryInCommonCurrency.Sum(x => x.ProjectAmount);
List <CurrencyDetails> currencies = await currencyTask;
foreach (CurrencyDetails currency in currencies)
{
ExchangeRateDetail exchangeRate = await _dbContext.ExchangeRateDetail.OrderByDescending(x => x.Date).Where(x => x.FromCurrency == amountSummaryCurrencyId && x.ToCurrency == currency.CurrencyId).FirstOrDefaultAsync();
if (exchangeRate != null)
{
ProjectProposalAmountSummary amountSummary = new ProjectProposalAmountSummary
{
CurrencyId = currency.CurrencyId,
ProposalAmount = totalAmount * (double)exchangeRate.Rate
};
projectProposalAmountSummary.Add(amountSummary);
}
else
{
throw new Exception("Exchange Rate not defined");
}
}
response.data.ProjectProposalAmountSummary = projectProposalAmountSummary;
response.StatusCode = StaticResource.successStatusCode;
response.Message = StaticResource.SuccessText;
}
}
catch (Exception ex)
{
response.StatusCode = StaticResource.failStatusCode;
response.Message = StaticResource.SomethingWrong + ex;
}
return(response);
}
public async Task <ApiResponse> Handle(GetAllDepreciationByFilterQuery request, CancellationToken cancellationToken)
{
ApiResponse response = new ApiResponse();
try
{
if (request.CurrentDate != null)
{
List <DepreciationReportModel> depreciationList = new List <DepreciationReportModel>();
List <StoreItemPurchase> storeItemPurchased = new List <StoreItemPurchase>();
if (request.StoreId != null && request.InventoryId != null && request.ItemId != null)
{
storeItemPurchased = await _dbContext.StoreItemPurchases.Include(x => x.StoreInventoryItem).ThenInclude(x => x.StoreItemGroup).ThenInclude(x => x.StoreInventory).Where(x => x.IsDeleted == false && x.ApplyDepreciation == true && x.InventoryItem == request.ItemId && x.StoreInventoryItem.StoreItemGroup.StoreInventory.AssetType == request.StoreId).ToListAsync();
}
else if (request.StoreId != null && request.InventoryId != null && request.ItemId == null && request.ItemGroupId != 0)
{
storeItemPurchased = await _dbContext.StoreItemPurchases.Include(x => x.StoreInventoryItem).ThenInclude(x => x.StoreItemGroup).ThenInclude(x => x.StoreInventory).Where(x => x.IsDeleted == false && x.ApplyDepreciation == true && x.StoreInventoryItem.StoreItemGroup.StoreInventory.AssetType == request.StoreId && x.StoreInventoryItem.Inventory.InventoryId == request.InventoryId && x.StoreInventoryItem.ItemGroupId == request.ItemGroupId).ToListAsync();
}
else if (request.StoreId != null && request.InventoryId != null && request.ItemId == null && request.ItemGroupId == 0)
{
storeItemPurchased = await _dbContext.StoreItemPurchases.Include(x => x.StoreInventoryItem).ThenInclude(x => x.StoreItemGroup).ThenInclude(x => x.StoreInventory).Where(x => x.IsDeleted == false && x.ApplyDepreciation == true && x.StoreInventoryItem.StoreItemGroup.StoreInventory.AssetType == request.StoreId && x.StoreInventoryItem.ItemInventory == request.InventoryId).ToListAsync();
}
else if (request.StoreId != null && request.InventoryId == null && request.ItemId == null && request.ItemGroupId != 0)
{
storeItemPurchased = await _dbContext.StoreItemPurchases.Include(x => x.StoreInventoryItem).ThenInclude(x => x.Inventory).Where(x => x.IsDeleted == false && x.ApplyDepreciation == true && x.StoreInventoryItem.StoreItemGroup.StoreInventory.AssetType == request.StoreId && x.StoreInventoryItem.ItemGroupId == request.ItemGroupId).ToListAsync();
}
else if (request.StoreId != null && request.InventoryId == null && request.ItemId == null && request.ItemGroupId == 0)
{
storeItemPurchased = await _dbContext.StoreItemPurchases.Include(x => x.StoreInventoryItem).ThenInclude(x => x.Inventory).Where(x => x.IsDeleted == false && x.ApplyDepreciation == true && x.StoreInventoryItem.StoreItemGroup.StoreInventory.AssetType == request.StoreId).ToListAsync();
}
else if (request.StoreId == null && request.InventoryId == null && request.ItemId == null)
{
storeItemPurchased = await _dbContext.StoreItemPurchases.Include(x => x.StoreInventoryItem).ThenInclude(x => x.Inventory).Where(x => x.IsDeleted == false && x.ApplyDepreciation == true).ToListAsync();
}
foreach (var item in storeItemPurchased)
{
ExchangeRateDetail dollarExchangeRate = _dbContext.ExchangeRateDetail.OrderByDescending(x => x.Date)
.FirstOrDefault(x => x.IsDeleted == false && x.FromCurrency == item.Currency &&
x.ToCurrency == (int)Currency.USD && x.Date.Date <= request.CurrentDate.Date);
if (dollarExchangeRate == null)
{
throw new Exception("Exchange Rate not defined!!");
}
DepreciationReportModel obj = new DepreciationReportModel();
obj.ItemName = item.StoreInventoryItem.ItemName;
obj.PurchaseId = item.PurchaseId;
obj.PurchaseDate = item.PurchaseDate;
obj.HoursSincePurchase = Math.Round(Math.Abs(request.CurrentDate.Date.Subtract(item.PurchaseDate).TotalHours), 4);
obj.DepreciationRate = item.DepreciationRate;
obj.DepreciationAmount = Math.Round(((obj.HoursSincePurchase * item.DepreciationRate * item.UnitCost) / 100) * (double)dollarExchangeRate.Rate, 4);
obj.CurrentValue = Math.Round((item.UnitCost - obj.DepreciationAmount) * (double)dollarExchangeRate.Rate, 4);
obj.PurchasedCost = Math.Round(item.UnitCost * (double)dollarExchangeRate.Rate, 4);
depreciationList.Add(obj);
}
response.data.DepreciationReportList = depreciationList.ToList();
response.StatusCode = StaticResource.successStatusCode;
response.Message = "Success";
}
else
{
response.StatusCode = StaticResource.failStatusCode;
response.Message = "Please Select Date";
}
}
catch (Exception ex)
{
response.StatusCode = StaticResource.failStatusCode;
response.Message = StaticResource.SomethingWrong + ex.Message;
return(response);
}
return(response);
}
public async Task <ApiResponse> Handle(GetVoucherTransactionListQuery model, CancellationToken cancellationToken)
{
ApiResponse response = new ApiResponse();
try
{
var data = await _dbContext.VoucherDetail
.Include(x => x.VoucherTransactionDetails)
.ThenInclude(y => y.ChartOfAccountDetail)
.Include(x => x.CurrencyDetail)
.FirstOrDefaultAsync(x => x.IsDeleted == false && x.VoucherNo == model.VoucherNo);
if (data != null)
{
if (data.VoucherTransactionDetails.Any())
{
if (model.RecordType == (int)RECORDTYPE.SINGLE)
{
response.data.VoucherSummaryTransactionList = data.VoucherTransactionDetails.Select(x => new VoucherSummaryTransactionModel
{
AccountCode = x.ChartOfAccountDetail.ChartOfAccountNewCode,
AccountName = x.ChartOfAccountDetail.AccountName,
CurrencyName = data.CurrencyDetail.CurrencyName,
TransactionDescription = x.Description,
Amount = x.Debit == 0 ? x.Credit : x.Debit,
TransactionType = x.Debit == 0 ? "Credit" : "Debit"
}).ToList();
}
else //consolidated
{
response.data.VoucherSummaryTransactionList = new List <VoucherSummaryTransactionModel>();
ExchangeRateDetail exchangeRateDetail = exchangeRateDetail = await _dbContext.ExchangeRateDetail
.OrderByDescending(x => x.Date)
.FirstOrDefaultAsync(x => x.IsDeleted == false &&
x.Date <= data.VoucherDate.Date && x.FromCurrency == data.CurrencyId &&
x.ToCurrency == model.CurrencyId);
foreach (var item in data.VoucherTransactionDetails)
{
VoucherSummaryTransactionModel voucherSummaryTransactionModel = new VoucherSummaryTransactionModel();
if (item.CurrencyId != model.CurrencyId)
{
if (exchangeRateDetail == null)
{
throw new Exception("Exchange Rate Not Defined");
}
if (item.Debit == 0)
{
voucherSummaryTransactionModel.Amount = Math.Round((double)(item.Credit * (double)exchangeRateDetail.Rate), 2);
voucherSummaryTransactionModel.TransactionType = "Credit";
}
else
{
voucherSummaryTransactionModel.Amount = Math.Round((double)(item.Debit * (double)exchangeRateDetail.Rate), 2);
voucherSummaryTransactionModel.TransactionType = "Debit";
}
}
else
{
if (item.Debit == 0)
{
voucherSummaryTransactionModel.Amount = item.Credit;
voucherSummaryTransactionModel.TransactionType = "Credit";
}
else
{
voucherSummaryTransactionModel.Amount = item.Debit;
voucherSummaryTransactionModel.TransactionType = "Debit";
}
}
voucherSummaryTransactionModel.AccountCode = item.ChartOfAccountDetail.ChartOfAccountNewCode;
voucherSummaryTransactionModel.AccountName = item.ChartOfAccountDetail.AccountName;
voucherSummaryTransactionModel.CurrencyName = data.CurrencyDetail.CurrencyName;
voucherSummaryTransactionModel.TransactionDescription = item.Description;
response.data.VoucherSummaryTransactionList.Add(voucherSummaryTransactionModel);
}
}
}
}
response.StatusCode = StaticResource.successStatusCode;
response.Message = "Success";
}
catch (Exception exception)
{
response.StatusCode = StaticResource.failStatusCode;
response.Message = StaticResource.SomethingWrong + exception.Message;
}
return(response);
}
public async Task <ApiResponse> Handle(GetEmployeePensionReportQuery request, CancellationToken cancellationToken)
{
ApiResponse response = new ApiResponse();
EmployeePensionRate pensionRateDetail = _dbContext.EmployeePensionRate.FirstOrDefault(x => x.IsDefault == true && x.IsDeleted == false);
double pensionRate = pensionRateDetail != null ? (double)pensionRateDetail.PensionRate : 4.5;
try
{
EmployeePensionModel epm = new EmployeePensionModel();
List <EmployeePensionReportModel> lst = new List <EmployeePensionReportModel>();
var financialYearList = await _dbContext.FinancialYearDetail.Where(x => request.FinancialYearId.Contains(x.FinancialYearId)).ToListAsync();
var empList = await _dbContext.EmployeePaymentTypes.Where(x => financialYearList.Select(y => y.StartDate.Year).Contains(x.PayrollYear.Value) && x.OfficeId == request.OfficeId && x.EmployeeID == request.EmployeeId && x.IsDeleted == false).ToListAsync();
EmployeeDetail xEmployeeDetail = await _dbContext.EmployeeDetail.FirstOrDefaultAsync(x => x.EmployeeID == request.EmployeeId && x.IsDeleted == false);
var previousPensionList = await _dbContext.EmployeePaymentTypes.Where(x => x.PayrollYear < financialYearList.OrderByDescending(y => y.StartDate).FirstOrDefault().StartDate.Year&& x.IsDeleted == false).ToListAsync();
epm.PreviousPensionRate = previousPensionList.Average(x => x.PensionRate);
foreach (var item in previousPensionList)
{
if (item.CurrencyId == request.CurrencyId)
{
epm.PreviousPensionDeduction += item.PensionAmount;
epm.PreviousProfit += Math.Round(Convert.ToDouble(item.PensionAmount * item.PensionRate), 2);
epm.PreviousTotal += Math.Round(Convert.ToDouble((item.PensionAmount * item.PensionRate) + item.PensionAmount), 2);
}
else
{
ExchangeRateDetail exchangeRate = await _dbContext.ExchangeRateDetail.OrderByDescending(x => x.Date).FirstOrDefaultAsync(x => x.IsDeleted == false && x.FromCurrency == item.CurrencyId && x.ToCurrency == request.CurrencyId);
epm.PreviousPensionDeduction += item.PensionAmount * (double)exchangeRate.Rate;
epm.PreviousProfit += Math.Round(Convert.ToDouble(item.PensionAmount * pensionRate), 2) * (double)exchangeRate.Rate;
epm.PreviousTotal += Math.Round(Convert.ToDouble((item.PensionAmount * pensionRate) + item.PensionAmount), 2) * (double)exchangeRate.Rate;
}
}
foreach (var item in empList)
{
ExchangeRateDetail exchangeRate = await _dbContext.ExchangeRateDetail.OrderByDescending(x => x.Date).FirstOrDefaultAsync(x => x.IsDeleted == false && x.FromCurrency == item.CurrencyId && x.ToCurrency == request.CurrencyId);
EmployeePensionReportModel obj = new EmployeePensionReportModel();
obj.CurrencyId = item.CurrencyId.Value;
obj.Date = new DateTime(item.PayrollYear.Value, item.PayrollMonth.Value, 1);
obj.GrossSalary = Math.Round(Convert.ToDouble(item.GrossSalary), 2) * (double)exchangeRate.Rate;
obj.PensionRate = pensionRate;
obj.PensionDeduction = Math.Round(Convert.ToDouble((item.GrossSalary * pensionRate) / 100), 2) * (double)exchangeRate.Rate;
obj.Profit = Math.Round(Convert.ToDouble((obj.PensionDeduction * pensionRate)) / 100, 2);
obj.Total = obj.Profit + obj.PensionDeduction;
lst.Add(obj);
}
epm.EmployeePensionReportList = lst.OrderBy(x => x.Date.Date).ToList();
epm.PensionTotal = lst.Sum(x => x.Total);
epm.PensionProfitTotal = lst.Sum(x => x.Profit);
epm.PensionDeductionTotal = Math.Round(Convert.ToDouble(lst.Sum(x => x.GrossSalary * pensionRate / 100)), 2);
epm.EmployeeCode = xEmployeeDetail.EmployeeCode;
response.data.EmployeePensionModel = epm;
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(AddExchangeRateCommand exchangeRateModel, CancellationToken cancellationToken)
{
ApiResponse response = new ApiResponse();
using (IDbContextTransaction tran = _dbContext.Database.BeginTransaction())
{
try
{
if (exchangeRateModel.GenerateExchangeRateModel.Any())
{
List <ExchangeRateDetail> exchangeRateDetails = new List <ExchangeRateDetail>();
var exchangeRateDates = _dbContext.ExchangeRateVerifications.Where(x => x.IsDeleted == false && x.Date.Date == exchangeRateModel.GenerateExchangeRateModel.FirstOrDefault().Date.Date).ToList();
List <CurrencyDetails> currencyDetails = _dbContext.CurrencyDetails.Where(x => x.IsDeleted == false).ToList();
if (exchangeRateDates.Any())
{
throw new Exception("Exchange rate already exists for the selected date");
}
GetSystemGeneratedExchangeRates(exchangeRateModel.GenerateExchangeRateModel, ref exchangeRateDetails, currencyDetails, exchangeRateModel.CreatedById);
List <int> OfficeIds = await _dbContext.OfficeDetail.Where(x => x.IsDeleted == false).Select(x => x.OfficeId).ToListAsync();
List <ExchangeRateDetail> exchangeRatesForAllOffices = new List <ExchangeRateDetail>();
foreach (int officeId in OfficeIds)
{
foreach (ExchangeRateDetail item in exchangeRateDetails)
{
ExchangeRateDetail exchangeRateDetail = new ExchangeRateDetail();
exchangeRateDetail.CreatedById = item.CreatedById;
exchangeRateDetail.CreatedDate = item.CreatedDate;
exchangeRateDetail.Date = item.Date;
exchangeRateDetail.FromCurrency = item.FromCurrency;
exchangeRateDetail.ToCurrency = item.ToCurrency;
exchangeRateDetail.IsDeleted = item.IsDeleted;
exchangeRateDetail.OfficeId = officeId;
exchangeRateDetail.Rate = item.Rate;
exchangeRatesForAllOffices.Add(exchangeRateDetail);
}
exchangeRatesForAllOffices.AddRange(exchangeRateDetails);
}
await _dbContext.ExchangeRateDetail.AddRangeAsync(exchangeRatesForAllOffices);
await _dbContext.SaveChangesAsync();
ExchangeRateVerification exchangeRateVerification = new ExchangeRateVerification();
exchangeRateVerification.IsDeleted = false;
exchangeRateVerification.CreatedById = exchangeRateModel.CreatedById;
exchangeRateVerification.CreatedDate = DateTime.UtcNow;
exchangeRateVerification.Date = exchangeRateModel.GenerateExchangeRateModel.FirstOrDefault().Date;
exchangeRateVerification.IsVerified = false;
_dbContext.ExchangeRateVerifications.Add(exchangeRateVerification);
await _dbContext.SaveChangesAsync();
response.StatusCode = StaticResource.successStatusCode;
response.Message = "Exchange rates generated successfully!!!";
tran.Commit();
}
}
catch (Exception ex)
{
tran.Rollback();
response.StatusCode = StaticResource.failStatusCode;
response.Message = StaticResource.SomethingWrong + 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);
}