/// <summary> /// Retrieve a single template details from list of payroll template details /// </summary> /// <param name="id">Id of that particular entry</param> /// <param name="CompanyId">comapanyid of that particular entry </param> /// <returns>single entry of payroll template details</returns> public static PayRollTemplate GetDetails(int id, int CompanyId) { DBManager db = new DBManager(); try { db.Open(); string query = @"select top 1 p.Salary_Template_Id,p.Grade,isnull(p.Basic_Salary,0)[Basic_Salary],isnull(p.Overtime_Rate,0)[Overtime_Rate], isnull(p.House_Rent_Allowance,0)[House_Rent_Allowance],isnull(p.Medical_Allowance,0)[Medical_Allowance],isnull(p.Travelling_Allowance,0)[Travelling_Allowance], isnull(p.Dearness_Allowance,0)[Dearness_Allowance],isnull(p.Security_Deposit,0)[Security_Deposit],isnull(p.Provident_Fund,0)[Provident_Fund], isnull(p.Tax_Deduction,0)[Tax_Deduction],isnull(p.Gross_Salary,0)[Gross_Salary],isnull(p.Total_Allowance,0)[Total_Allowance], isnull(p.Total_Deduction,0)[Total_Deduction],isnull(p.Net_Salary,0)[Net_Salary],isnull(p.Incentives,0)[Incentives],isnull(p.Status,0)[Status], isnull(p.Company_Id,0)[Company_Id],isnull(c.Name,0)[Company] from TBL_PAYROLL_TEMPLATE_MST p left join TBL_COMPANY_MST c on c.Company_Id=p.Company_Id where p.Company_Id=@Company_Id and p.Salary_Template_Id=@id"; db.CreateParameters(2); db.AddParameters(0, "@Company_Id", CompanyId); db.AddParameters(1, "@id", id); DataTable dt = db.ExecuteQuery(CommandType.Text, query); if (dt != null) { PayRollTemplate pay = new PayRollTemplate(); DataRow item = dt.Rows[0]; pay.ID = item["Salary_Template_Id"] != DBNull.Value ? Convert.ToInt32(item["Salary_Template_Id"]) : 0; pay.Grade = Convert.ToString(item["Grade"]); pay.BasicSalary = item["Basic_Salary"] != DBNull.Value ? Convert.ToDecimal(item["Basic_Salary"]) : 0; pay.OvertimeRate = item["Overtime_Rate"] != DBNull.Value ? Convert.ToDecimal(item["Overtime_Rate"]) : 0; pay.HouseRentAllowance = item["House_Rent_Allowance"] != DBNull.Value ? Convert.ToDecimal(item["House_Rent_Allowance"]) : 0; pay.MedicalAllowance = item["Medical_Allowance"] != DBNull.Value ? Convert.ToDecimal(item["Medical_Allowance"]) : 0; pay.TravellingAllowance = item["Travelling_Allowance"] != DBNull.Value ? Convert.ToDecimal(item["Travelling_Allowance"]) : 0; pay.DearnessAllowance = item["Dearness_Allowance"] != DBNull.Value ? Convert.ToDecimal(item["Dearness_Allowance"]) : 0; pay.SecurityDeposit = item["Security_Deposit"] != DBNull.Value ? Convert.ToDecimal(item["Security_Deposit"]) : 0; pay.ProvidentFund = item["Provident_Fund"] != DBNull.Value ? Convert.ToDecimal(item["Provident_Fund"]) : 0; pay.TaxDeduction = item["Tax_Deduction"] != DBNull.Value ? Convert.ToDecimal(item["Tax_Deduction"]) : 0; pay.GrossSalary = item["Gross_Salary"] != DBNull.Value ? Convert.ToDecimal(item["Gross_Salary"]) : 0; pay.TotalAllowance = item["Total_Allowance"] != DBNull.Value ? Convert.ToDecimal(item["Total_Allowance"]) : 0; pay.TotalDeduction = item["Total_Deduction"] != DBNull.Value ? Convert.ToDecimal(item["Total_Deduction"]) : 0; pay.NetSalary = item["Net_Salary"] != DBNull.Value ? Convert.ToDecimal(item["Net_Salary"]) : 0; pay.Incentives = item["Incentives"] != DBNull.Value ? Convert.ToDecimal(item["Incentives"]) : 0; pay.Status = item["Status"] != DBNull.Value ? Convert.ToInt32(item["Status"]) : 0; pay.CompanyId = item["Company_Id"] != DBNull.Value ? Convert.ToInt32(item["Company_Id"]) : 0; pay.Company = Convert.ToString(item["Company"]); return(pay); } else { return(null); } } catch (Exception ex) { Application.Helper.LogException(ex, "payrolltemplate | GetDetails(int id, int CompanyId)"); return(null); } finally { db.Close(); } }
public static List <PayRollTemplate> GetDetailsForPaySlip(int CompanyId) { DBManager db = new DBManager(); try { db.Open(); string query = @"select p.Grade,isnull(p.Salary_Template_Id,0)[Salary_Template_Id],isnull(p.Basic_Salary,0)[Basic_Salary], isnull(p.House_Rent_Allowance,0)[House_Rent_Allowance],isnull(p.Medical_Allowance,0)[Medical_Allowance], isnull(p.Travelling_Allowance,0)[Travelling_Allowance],isnull(p.Dearness_Allowance,0)[Dearness_Allowance], isnull(p.Provident_Fund,0)[Provident_Fund],isnull(p.Tax_Deduction,0)[Tax_Deduction],isnull(p.Net_Salary,0)[Net_Salary], e.First_Name[Employee] from TBL_PAYROLL_TEMPLATE_MST p inner join TBL_EMPLOYEE_MST e on e.Monthly_Template=p.Salary_Template_Id where p.Company_Id=@Company_Id order by p.Created_Date desc"; db.CreateParameters(1); db.AddParameters(0, "@Company_Id", CompanyId); DataTable dt = db.ExecuteQuery(CommandType.Text, query); List <PayRollTemplate> payroll = new List <PayRollTemplate>(); if (dt != null) { foreach (DataRow item in dt.Rows) { PayRollTemplate pay = new PayRollTemplate(); pay.ID = item["Salary_Template_Id"] != DBNull.Value ? Convert.ToInt32(item["Salary_Template_Id"]) : 0; pay.Grade = Convert.ToString(item["Grade"]); pay.Employee = Convert.ToString(item["Employee"]); pay.BasicSalary = item["Basic_Salary"] != DBNull.Value ? Convert.ToDecimal(item["Basic_Salary"]) : 0; pay.HouseRentAllowance = item["House_Rent_Allowance"] != DBNull.Value ? Convert.ToDecimal(item["House_Rent_Allowance"]) : 0; pay.MedicalAllowance = item["Medical_Allowance"] != DBNull.Value ? Convert.ToDecimal(item["Medical_Allowance"]) : 0; pay.TravellingAllowance = item["Travelling_Allowance"] != DBNull.Value ? Convert.ToDecimal(item["Travelling_Allowance"]) : 0; pay.DearnessAllowance = item["Dearness_Allowance"] != DBNull.Value ? Convert.ToDecimal(item["Dearness_Allowance"]) : 0; pay.ProvidentFund = item["Provident_Fund"] != DBNull.Value ? Convert.ToDecimal(item["Provident_Fund"]) : 0; pay.TaxDeduction = item["Tax_Deduction"] != DBNull.Value ? Convert.ToDecimal(item["Tax_Deduction"]) : 0; pay.NetSalary = item["Net_Salary"] != DBNull.Value ? Convert.ToDecimal(item["Net_Salary"]) : 0; payroll.Add(pay); } return(payroll); } else { return(null); } } catch (Exception ex) { Application.Helper.LogException(ex, "payrollTemplate | GetDetailsForPaySlip(int CompanyId)"); return(null); } finally { db.Close(); } }
public static List <PayRollTemplate> GetDetailsFromPayslip(int CompanyId, DateTime From, DateTime To) { using (DBManager db = new DBManager()) { try { string query = @"select p.PaySlip_Id,isnull(p.Employee_Id,0)[Employee_Id],isnull(p.Basic_Pay,0)[Basic_Pay],isnull(p.Dearness_Allowance,0)[Dearness_Allowance], isnull(p.Gross,0)[Gross],isnull(p.House_Rent_Allowance,0)[House_Rent_Allowance],isnull(p.Incentives,0)[Incentives], isnull(p.Leave_Deduction,0)[Leave_Deduction],isnull(p.Medical_Allowance,0)[Medical_Allowance],isnull(p.Net_Salary,0)[Net_Salary], isnull(p.No_Of_Holidays,0)[No_Of_Holidays],isnull(p.No_Of_Leave,0)[No_Of_Leave],isnull(p.No_Of_Working_Days,0)[No_Of_Working_Days], isnull(p.Provident_Fund,0)[Provident_Fund],p.Salary_Template,isnull(p.Security_Deposit,0)[Security_Deposit],p.Status, isnull(p.Tax_Deduction,0)[Tax_Deduction],isnull(p.Total_Allowance,0)[Total_Allowance],isnull(p.Total_Attendance,0)[Total_Attendance], isnull(p.Travelling_Allowance,0)[Travelling_Allowance],e.First_Name[Employee],e.Last_Name[Emp_LNAme],isnull(p.Total_Deduction,0)[Total_Deduction] from TBL_PAYSLIP p left join TBL_EMPLOYEE_MST e on e.Employee_Id=p.Employee_Id where e.Status<>0 and p.Date>=@From and p.Date<=@To"; db.CreateParameters(3); db.AddParameters(0, "@Company_Id", CompanyId); db.AddParameters(1, "@From", From); db.AddParameters(2, "@To", To); db.Open(); DataTable dt = db.ExecuteQuery(CommandType.Text, query); List <PayRollTemplate> payroll = new List <PayRollTemplate>(); if (dt != null) { foreach (DataRow item in dt.Rows) { PayRollTemplate pay = new PayRollTemplate(); pay.ID = item["PaySlip_Id"] != DBNull.Value ? Convert.ToInt32(item["PaySlip_Id"]) : 0; pay.Employee = Convert.ToString(item["Employee"]); pay.EmployeeLastName = Convert.ToString(item["Emp_LNAme"]); pay.EmployeeId = item["Employee_Id"] != DBNull.Value ? Convert.ToInt32(item["Employee_Id"]) : 0; pay.PaymentStatus = Convert.ToBoolean(item["Status"]); pay.Grade = Convert.ToString(item["Salary_Template"]); pay.BasicSalary = item["Basic_Pay"] != DBNull.Value ? Convert.ToDecimal(item["Basic_Pay"]) : 0; pay.Incentives = item["Incentives"] != DBNull.Value ? Convert.ToDecimal(item["Incentives"]) : 0; pay.DearnessAllowance = item["Dearness_Allowance"] != DBNull.Value ? Convert.ToDecimal(item["Dearness_Allowance"]) : 0; pay.GrossSalary = item["Gross"] != DBNull.Value ? Convert.ToDecimal(item["Gross"]) : 0; pay.HouseRentAllowance = item["House_Rent_Allowance"] != DBNull.Value ? Convert.ToDecimal(item["House_Rent_Allowance"]) : 0; pay.TotalAllowance = item["Total_Allowance"] != DBNull.Value ? Convert.ToDecimal(item["Total_Allowance"]) : 0; pay.TotalDeduction = item["Total_Deduction"] != DBNull.Value ? Convert.ToDecimal(item["Total_Deduction"]) : 0; pay.TravellingAllowance = item["Travelling_Allowance"] != DBNull.Value ? Convert.ToDecimal(item["Travelling_Allowance"]) : 0; pay.GrossSalary = item["Gross"] != DBNull.Value ? Convert.ToDecimal(item["Gross"]) : 0; pay.NetSalary = item["Net_Salary"] != DBNull.Value ? Convert.ToDecimal(item["Net_Salary"]) : 0; pay.MedicalAllowance = item["Medical_Allowance"] != DBNull.Value ? Convert.ToDecimal(item["Medical_Allowance"]) : 0; pay.ProvidentFund = item["Provident_Fund"] != DBNull.Value ? Convert.ToDecimal(item["Provident_Fund"]) : 0; pay.SecurityDeposit = item["Security_Deposit"] != DBNull.Value ? Convert.ToDecimal(item["Security_Deposit"]) : 0; pay.TaxDeduction = item["Tax_Deduction"] != DBNull.Value ? Convert.ToDecimal(item["Tax_Deduction"]) : 0; pay.TotalLeave = item["No_Of_Leave"] != DBNull.Value? Convert.ToInt32(item["No_Of_Leave"]):0; pay.TotalHolidays = item["No_Of_Holidays"] != DBNull.Value? Convert.ToInt32(item["No_Of_Holidays"]):0; pay.TotalWorkingDays = item["No_Of_Working_Days"] != DBNull.Value? Convert.ToInt32(item["No_Of_Working_Days"]):0; pay.TotalAttendance = item["Total_Attendance"] != DBNull.Value? Convert.ToInt32(item["Total_Attendance"]):0; pay.LeaveDeduction = item["Leave_Deduction"] != DBNull.Value? Convert.ToDecimal(item["Leave_Deduction"]):0; payroll.Add(pay); } return(payroll); } else { return(null); } } catch (Exception ex) { Application.Helper.LogException(ex, "payrolltemplate | GetDetailsFromPayslip(int CompanyId,DateTime From,DateTime To)"); return(null); } finally { db.Close(); } } }
public static List <PayRollTemplate> GetDetailsForPayment(int CompanyId, DateTime From, DateTime To) { using (DBManager db = new DBManager()) { try { string query = @"declare @Total_Holiday int select @Total_Holiday=count(Date) from TBL_HOLIDAYS where date>=@From and Date<=@To select isnull(I.Employee_Id,0)[Employee_Id],I.Title,I.First_Name,I.Last_Name,isnull(I.[Status],0)[Status], isnull(I.Company_Id,0)[Company_Id], pay.Grade[Monthly_Template],hou.Grade[Hourly_Template],hou.Hourly_Rate_Id,isnull(pay.Salary_Template_Id,0)[Salary_Template_Id], isnull( pay.Basic_Salary,0)[Monthly_BP],isnull(pay.Dearness_Allowance,0)[Dearness_Allowance],isnull(pay.Gross_Salary,0)[Gross_Salary], isnull(pay.House_Rent_Allowance,0)[House_Rent_Allowance],isnull(pay.Incentives,0)[Incentives], isnull(pay.Medical_Allowance,0)[Medical_Allowance],isnull(pay.Net_Salary,0)[Net_Salary], isnull(pay.Overtime_Rate,0)[Overtime_Rate],isnull(pay.Provident_Fund,0)[Provident_Fund], isnull(pay.Security_Deposit,0)[Security_Deposit],isnull(pay.Tax_Deduction,0)[Tax_Deduction], isnull(pay.Total_Allowance,0)[Total_Allowance],isnull(pay.Total_Deduction,0)[Total_Deduction], isnull(pay.Travelling_Allowance,0)[Travelling_Allowance],isnull(hou.Rate,0)[Hourly_Rate],cmp.Name[Company], (select count(*) from TBL_ATTENDANCE where Attendance_Status=2 and date>=@From and Date<=@To and Employee_Id=i.Employee_Id) as Total_Leave, (@Total_Holiday) as Total_Holidays from TBL_EMPLOYEE_MST I inner join TBL_COMPANY_MST cmp on cmp.Company_Id = i.Company_Id inner join TBL_PAYROLL_TEMPLATE_MST pay on pay.Salary_Template_Id=i.Monthly_Template left join TBL_HOURLY_WAGE_MST hou on hou.Hourly_Rate_Id=i.Hourly_Template where i.Company_Id=@Company_Id and i.Status<>0 order by i.Created_Date desc"; db.CreateParameters(3); db.AddParameters(0, "@Company_Id", CompanyId); db.AddParameters(1, "@From", From); db.AddParameters(2, "@To", To); db.Open(); DataTable dt = db.ExecuteQuery(CommandType.Text, query); List <PayRollTemplate> payroll = new List <PayRollTemplate>(); if (dt != null) { foreach (DataRow item in dt.Rows) { PayRollTemplate pay = new PayRollTemplate(); pay.ID = item["Salary_Template_Id"] != DBNull.Value?Convert.ToInt32(item["Salary_Template_Id"]):0; pay.Employee = Convert.ToString(item["First_Name"]); pay.EmployeeLastName = Convert.ToString(item["Last_Name"]); pay.EmployeeId = item["Employee_Id"] != DBNull.Value? Convert.ToInt32(item["Employee_Id"]):0; pay.Status = item["Status"] != DBNull.Value ? Convert.ToInt32(item["Status"]) : 0; pay.Company = Convert.ToString(item["Company"]); pay.Grade = Convert.ToString(item["Monthly_Template"]); pay.BasicSalary = item["Monthly_BP"] != DBNull.Value ? Convert.ToDecimal(item["Monthly_BP"]):0; pay.Incentives = item["Incentives"] != DBNull.Value ? Convert.ToDecimal(item["Incentives"]):0; pay.DearnessAllowance = item["Dearness_Allowance"] != DBNull.Value ? Convert.ToDecimal(item["Dearness_Allowance"]):0; pay.GrossSalary = item["Gross_Salary"] != DBNull.Value ? Convert.ToDecimal(item["Gross_Salary"]):0; pay.HouseRentAllowance = item["House_Rent_Allowance"] != DBNull.Value ? Convert.ToDecimal(item["House_Rent_Allowance"]):0; pay.Incentives = item["Incentives"] != DBNull.Value ? Convert.ToDecimal(item["Incentives"]) : 0; pay.TotalAllowance = item["Total_Allowance"] != DBNull.Value ? Convert.ToDecimal(item["Total_Allowance"]) : 0; pay.TotalDeduction = item["Total_Deduction"] != DBNull.Value ? Convert.ToDecimal(item["Total_Deduction"]) : 0; pay.TravellingAllowance = item["Travelling_Allowance"] != DBNull.Value ? Convert.ToDecimal(item["Travelling_Allowance"]) : 0; pay.GrossSalary = item["Gross_Salary"] != DBNull.Value ? Convert.ToDecimal(item["Gross_Salary"]) : 0; pay.NetSalary = item["Net_Salary"] != DBNull.Value ? Convert.ToDecimal(item["Net_Salary"]) : 0; pay.MedicalAllowance = item["Medical_Allowance"] != DBNull.Value ? Convert.ToDecimal(item["Medical_Allowance"]) : 0; pay.OvertimeRate = item["Overtime_Rate"] != DBNull.Value ? Convert.ToDecimal(item["Overtime_Rate"]) : 0; pay.ProvidentFund = item["Provident_Fund"] != DBNull.Value ? Convert.ToDecimal(item["Provident_Fund"]) : 0; pay.SecurityDeposit = item["Security_Deposit"] != DBNull.Value ? Convert.ToDecimal(item["Security_Deposit"]) : 0; pay.TaxDeduction = item["Tax_Deduction"] != DBNull.Value ? Convert.ToDecimal(item["Tax_Deduction"]) : 0; pay.TotalLeave = item["Total_Leave"] != DBNull.Value? Convert.ToInt32(item["Total_Leave"]):0; pay.TotalHolidays = item["Total_Holidays"] != DBNull.Value? Convert.ToInt32(item["Total_Holidays"]):0; payroll.Add(pay); } return(payroll); } else { return(null); } } catch (Exception ex) { Application.Helper.LogException(ex, "payrolltemplate | GetDetailsForPayment(int CompanyId,DateTime From,DateTime To)"); return(null); } finally { db.Close(); } } }
public static dynamic GetDetailsForManageSalary(int CompanyId) { DBManager db = new DBManager(); try { dynamic Final = new ExpandoObject(); db.Open(); string query = @"select e.Employee_Id,e.First_Name,isnull(e.Department_Id,0)[Department_Id],isnull(e.Designation_Id,0)[Designation_Id], desg.Designation[Designation],isnull(e.Company_Id,0)[Company_Id],isnull(e.Salary_Type,0)[Salary_Type],isnull(e.Monthly_Template,0)[Monthly_Template], isnull(e.Hourly_Template,0)[Hourly_Template],c.Name[Company],dep.Department[Department],isnull(e.IsHourlyPaid,0)[IsHourlyPaid] from TBL_EMPLOYEE_MST e left join TBL_COMPANY_MST c on c.Company_Id=e.Company_Id left join TBL_DESIGNATION_MST desg on desg.Designation_id=e.Designation_Id left join TBL_DEPARTMENT_MST dep on dep.Department_Id=e.Department_Id where e.Company_Id=@Company_Id; select Hourly_Rate_Id,isnull(Grade,0)[Grade] from TBL_HOURLY_WAGE_MST where Company_Id=@Company_Id and Status=1; select Salary_Template_Id,isnull(Grade,0)[Grade] from [dbo].[TBL_PAYROLL_TEMPLATE_MST] where Company_Id=@Company_Id and Status=1;"; db.CreateParameters(1); db.AddParameters(0, "@Company_Id", CompanyId); DataSet ds = db.ExecuteDataSet(CommandType.Text, query); List <Employee> result = new List <Employee>(); if (ds.Tables[0] != null) { foreach (DataRow item in ds.Tables[0].Rows) { Employee Employee = new Employee(); Employee.ID = item["Employee_Id"] != DBNull.Value ? Convert.ToInt32(item["Employee_Id"]) : 0; Employee.FirstName = Convert.ToString(item["First_Name"]); Employee.DepartmentName = Convert.ToString(item["Department"]); Employee.DepartmentId = item["Department_Id"] != DBNull.Value ? Convert.ToInt32(item["Department_Id"]) : 0; Employee.Designation = Convert.ToString(item["Designation"]); Employee.DesignationId = item["Designation_Id"] != DBNull.Value ? Convert.ToInt32(item["Designation_Id"]) : 0; Employee.Company = Convert.ToString(item["Company"]); Employee.CompanyId = item["Company_Id"] != DBNull.Value ? Convert.ToInt32(item["Company_Id"]) : 0; Employee.SalaryType = item["Salary_Type"] != DBNull.Value ? Convert.ToInt32(item["Salary_Type"]) : 0; Employee.HourlyTemplate = item["Hourly_Template"] != DBNull.Value ? Convert.ToInt32(item["Hourly_Template"]) : 0; Employee.MonthlyTemplate = item["Monthly_Template"] != DBNull.Value ? Convert.ToInt32(item["Monthly_Template"]) : 0; Employee.IsHourlyPaid = Convert.ToBoolean(item["IsHourlyPaid"]); result.Add(Employee); } Final.Employees = result; } List <HourlyTemplate> hourlyTemplates = new List <Payroll.HourlyTemplate>(); if (ds.Tables[1] != null) { foreach (DataRow item in ds.Tables[1].Rows) { HourlyTemplate hourlyTemp = new Payroll.HourlyTemplate(); hourlyTemp.ID = Convert.ToInt32(item["Hourly_Rate_Id"]); hourlyTemp.Title = Convert.ToString(item["Grade"]); hourlyTemplates.Add(hourlyTemp); } Final.HourlyTemplates = hourlyTemplates; } List <PayRollTemplate> payrollTemplate = new List <Payroll.PayRollTemplate>(); if (ds.Tables[2] != null) { foreach (DataRow item in ds.Tables[2].Rows) { PayRollTemplate pay = new PayRollTemplate(); pay.ID = Convert.ToInt32(item["Salary_Template_Id"]); pay.Grade = Convert.ToString(item["Grade"]); payrollTemplate.Add(pay); } Final.PayrollTemplate = payrollTemplate; } return(Final); } catch (Exception ex) { Application.Helper.LogException(ex, "Employee | GetDetailsForManageSalary(int CompanyId)"); return(null); } finally { db.Close(); } }