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