// GET: Payroll
public ActionResult Index(string date = "")
{
var viewModel = new PayrollViewModel();
var payrolls = new List <PayrollDao>();
var payrollDates = _payrollService.GetPayrollDates(6).Select(x => new SelectListItem
{
Text = x.FormattedDate,
Value = x.SerializedDate
});
viewModel.PayrollDates = payrollDates;
if (!String.IsNullOrEmpty(date))
{
var dates = date.Split('-');
var payrollStartDate = dates[0].DeserializeDate();
var payrollEndDate = dates[1].DeserializeDate();
ViewBag.StartDate = payrollStartDate;
ViewBag.EndDate = payrollEndDate;
GeneratePayroll(payrollStartDate, payrollEndDate);
//populate the viewmodel here from service data
//sort it in the service by surname
var employeePayrollList = _employeePayrollService.GetByDateRange
(payrollStartDate, payrollEndDate);
foreach (EmployeePayrollPerCompany payroll in employeePayrollList)
{
var payrollDto = new PayrollDao
{
PayrollId = payroll.EmployeePayrollPerCompanyId,
FirstName = payroll.Employee.FirstName,
LastName = payroll.Employee.LastName,
MiddleName = payroll.Employee.MiddleName,
TotalDeduction = payroll.TotalDeduction,
TotalGross = payroll.TotalGross,
TotalNet = payroll.TotalNet,
Company = payroll.Company
};
payrolls.Add(payrollDto);
}
viewModel.Date = date;
}
var pagination = _webService.GetPaginationModel(Request, payrolls.Count);
viewModel.Payrolls = _webService.TakePaginationModel(payrolls, pagination);
viewModel.Pagination = pagination;
return(View(viewModel));
}
protected void btnView_Click(object sender, EventArgs e)
{
if (txtEId.Text.Trim() != string.Empty || txtMonth.Text.Trim() != string.Empty && txtYear.Text.Trim() != string.Empty)
{
lblErrormessage.Visible = false;
var connection = SqlConnectionManager.GetConnection();
string query = "SELECT DISTINCT * FROM tblPayroll WHERE 1=1";
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandType = CommandType.Text;
using (SqlDataAdapter adapter = new SqlDataAdapter(query, connection))
{
StringBuilder sbcmd = new StringBuilder(query);
if (txtEId.Text.Trim() != "")
{
sbcmd.Append(" AND EId='" + txtEId.Text.Trim() + "'");
}
if (txtMonth.Text.Trim() != "" && txtYear.Text.Trim() != "")
{
sbcmd.Append(" AND Month='" + txtMonth.Text.Trim() + "' AND Year='" + txtYear.Text.Trim() + "'");
}
cmd.CommandText = sbcmd.ToString();
adapter.SelectCommand = cmd;
connection.Open();
List <PayrollDao> payrollList = new List <PayrollDao>();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
PayrollDao payrollData = new PayrollDao();
payrollData.PId = reader.GetInt32(0);
payrollData.EId = reader.GetInt32(1);
//payrollData.EAccno = reader.IsDBNull(2) ? "" : reader.GetString(2);
payrollData.BasicPay = reader.IsDBNull(3) ? "" : reader.GetString(3);
payrollData.BasicPayPD = reader.GetString(6);
string query2 = "SELECT COUNT(*) FROM tblAttendance WHERE [Month]=month AND [Year]=year AND Status='present' AND [EId]='" + payrollData.EId + "'";
SqlCommand cmd2 = new SqlCommand(query2, connection);
payrollData.DaysWorked = cmd2.ExecuteScalar().ToString();
int basicpay = Convert.ToInt32(payrollData.BasicPayPD) * Convert.ToInt32(payrollData.DaysWorked);
payrollData.BasicPay = Convert.ToString(basicpay);
//int basicpay;
//Int32.TryParse(payrollData.BasicPay, out basicpay);
int da = basicpay * 12 / 100;
int hra = basicpay * 15 / 100;
int pf = basicpay * 5 / 100;
int netsalary = basicpay + da + hra - pf;
payrollData.DA = Convert.ToString(da);
payrollData.HRA = Convert.ToString(hra);
payrollData.PF = Convert.ToString(pf);
payrollData.NetSalary = Convert.ToString(netsalary);
payrollList.Add(payrollData);
}
if (payrollList.ToList().Count > 0)
{
gvPayroll.DataSource = payrollList;
gvPayroll.DataBind();
}
else
{
for (int i = 0; i < gvPayroll.Rows.Count; i++)
{
gvPayroll.Rows[i].Cells.Clear();
gvPayroll.Rows[i].Visible = false;
}
gvPayroll.Rows[0].Visible = true;
gvPayroll.BottomPagerRow.Visible = false;
gvPayroll.Rows[0].Cells.Add(new TableCell());
gvPayroll.Rows[0].Cells[0].ColumnSpan = gvPayroll.Columns.Count;
gvPayroll.Rows[0].Cells[0].Text = "No Data Found!";
gvPayroll.Rows[0].Cells[0].HorizontalAlign = HorizontalAlign.Center;
}
adapter.Dispose();
}
}
else
{
lblErrormessage.Visible = true;
lblErrormessage.Text = "No search criteria found!";
}
}
public List <PayrollDao> PayrollData(string month, string year)
{
List <PayrollDao> payrollList = new List <PayrollDao>();
var connection = SqlConnectionManager.GetConnection();
connection.Open();
string query = "SELECT * FROM tblPayroll WHERE [Month]=@month AND [Year]=@year";
//SELECT tblPayroll.*,tblAttendance.Status FROM tblPayroll JOIN tblAttendance ON tblPayroll.EId=tblAttendance.EId AND tblPayroll.Month=tblAttendance.Month AND tblPayroll.Year=tblAttendance.Year WHERE tblPayroll.[Month]=@month AND tblPayroll.[Year]=@year AND tblAttendance.Status='present'
SqlCommand cmd = new SqlCommand();
cmd.Parameters.Add("month", SqlDbType.VarChar).Value = month;
cmd.Parameters.Add("year", SqlDbType.VarChar).Value = year;
cmd.Connection = connection;
cmd.CommandText = query;
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
PayrollDao payrollData = new PayrollDao();
payrollData.PId = reader.GetInt32(0);
payrollData.EId = reader.GetInt32(1);
payrollData.EAccno = reader.IsDBNull(2) ? "" : reader.GetString(2);
payrollData.BasicPay = reader.IsDBNull(3) ? "" : reader.GetString(3);
payrollData.BasicPayPD = reader.GetString(6);
string query2 = "SELECT COUNT(*) FROM tblAttendance WHERE [Month]=month AND [Year]=year AND Status='present' AND [EId]='" + payrollData.EId + "'";
SqlCommand cmd2 = new SqlCommand(query2, connection);
payrollData.DaysWorked = cmd2.ExecuteScalar().ToString();
int basicpay = Convert.ToInt32(payrollData.BasicPayPD) * Convert.ToInt32(payrollData.DaysWorked);
payrollData.BasicPay = Convert.ToString(basicpay);
//int basicpay =Convert.ToInt32(payrollData.BasicPay);
//Int32.TryParse(payrollData.BasicPay, out basicpay);
int da = basicpay * 12 / 100;
int hra = basicpay * 15 / 100;
int pf = basicpay * 5 / 100;
int netsalary = basicpay + da + hra - pf;
payrollData.DA = Convert.ToString(da);
payrollData.HRA = Convert.ToString(hra);
payrollData.PF = Convert.ToString(pf);
payrollData.NetSalary = Convert.ToString(netsalary);
payrollList.Add(payrollData);
}
connection.Close();
connection.Dispose();
return(payrollList);
}
else
{
int oldmonth = (Convert.ToInt32(month)) - 1;
int currentyear = (Convert.ToInt32(year));
string getQuery = "SELECT * FROM tblPayroll WHERE [Month]='" + oldmonth + "' AND [Year]='" + currentyear + "'";
var connection2 = SqlConnectionManager.GetConnection();
SqlCommand command = new SqlCommand(getQuery, connection2);
command.Connection.Open();
using (SqlDataReader sdr = command.ExecuteReader())
{
SqlDataAdapter sa = new SqlDataAdapter();
int eid;
string accno;
string bp;
int mon = DateTime.Now.Month;
int yea = DateTime.Now.Year;
if (sdr.HasRows)
{
while (sdr.Read())
{
eid = sdr.GetInt32(1);
accno = sdr.GetString(2);
bp = sdr.GetString(3);
string insertQuery = (@"INSERT INTO tblPayroll (EId, EAccno, BasicPay, Month, Year) VALUES ('" + eid + "', '" + accno + "', '" + bp + "', '" + mon + "', '" + yea + "')");
sa.InsertCommand = new SqlCommand(insertQuery, connection2);
sa.InsertCommand.ExecuteNonQuery();
}
}
}
return(null);
}
}
