public IEnumerable <PayrollSalaryViewModel> GetYearlyPayrollSalaryTable(int fyId, int officeId)
{
SqlConnection conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_GetPayrollSalaryTable", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@fiscalYearId", fyId);
cmd.Parameters.AddWithValue("@officeId", officeId);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
IEnumerable <PayrollSalaryViewModel> payrollSalaryTableDTO = new DataTableToEntityMapper().Map <PayrollSalaryViewModel>(dt).ToList();
foreach (var item in payrollSalaryTableDTO)
{
item.AllowanceViewModel = GetAllowanceData(item.EmployeeCode, fyId);
item.TaxModel = GetTaxData(item.EmployeeCode, fyId);
item.EmployeeName = _unitOfWork.EmployeeRepository.GetById(item.EmployeeCode).EmpName;
item.OfficeName = _unitOfWork.OfficeRepository.GetById(officeId).OfficeName;
item.FiscalYear = _unitOfWork.FiscalRepository.GetById(fyId).FyName;
int dsgID = _unitOfWork.EmployeeRepository.GetById(item.EmployeeCode).EmpDesgId;
item.EmpPost = _unitOfWork.DesignationRepository.GetById(dsgID).DsgName;
item.GradeNo = (int)_unitOfWork.EmployeeRepository.GetById(item.EmployeeCode).CurrentGrade;
item.PANNo = _employeeService.GetEmployeeByID(item.EmployeeCode).PANNumber;
item.CitNo = _employeeService.GetEmployeeByID(item.EmployeeCode).CitNumber;
item.InsurancePremium = _insuranceService.GetInsuranceInfobyEmpCode(item.EmployeeCode, fyId);
item.LoanIntIncome = _intGainService.GetInterestGainByEmpCode(item.EmployeeCode).InterestGain;
if (item.LoanIntIncome == null)
{
item.LoanIntIncome = 0;
}
//foreach (var items in item.InsurancePremium)
//{
// item.InsuranceID = items.IsuranceClaimId;
// item.InsuranceCompanyName = items.InsuranceCompany.CompanyName;
// item.PolicyNumber = items.InsurancePolicyNumber;
// item.InsuredAmount = (double)items.InsuredAmount;
// item.InsuranceEffectedDate = (DateTime)items.StartDate;
// item.MaturityDate = (DateTime)items.EndDate;
// item.AnnualPremium = (double)items.PremiumAmount;
//}
}
return(payrollSalaryTableDTO);
}
public IEnumerable <TaxViewModel> GetTaxData(int empcode, int fyId)
{
SqlConnection conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("select d.OrderNumber,sum(d.DeductedAmount)as totalValue from PayrollSalaryMasterSheet m,PayrollEmployeeTaxDetail d,PayrollSalaryTable s where m.Id=d.PayrollMasterSheetId and s.Id = m.PayrollSalaryTableId AND m.EmployeeCode = " + empcode + " AND s.FyId=" + fyId + " GROUP BY d.OrderNumber", conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@fiscalYearId", fyId);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
IEnumerable <TaxViewModel> TaxData = new DataTableToEntityMapper().Map <TaxViewModel>(dt).ToList();
return(TaxData);
}
public IEnumerable <AllowanceViewModel> GetAllowanceData(int empcode, int fyId)
{
SqlConnection conn = DbConnectHelper.GetConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("select d.AllowanceId,sum(d.CalculatedValue)as totalValue from PayrollSalaryMasterSheet m, PayrollSalaryDetailSheet d, PayrollSalaryTable s where m.Id = d.PayrollSalaryMasterId and s.Id = m.PayrollSalaryTableId AND m.EmployeeCode =" + empcode + " AND s.FyId = " + fyId + " AND d.AllowanceType <> 'R' GROUP BY d.AllowanceId", conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@fiscalYearId", fyId);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
IEnumerable <AllowanceViewModel> AllowanceData = new DataTableToEntityMapper().Map <AllowanceViewModel>(dt).ToList();
return(AllowanceData);
}
