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