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