public bool DeleteReport(PayrollTable report)
{
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand("DELETE FROM Payroll WHERE payroll_Id = @Id AND startDate = @startDate AND endDate = @endDate;", connection);
cmd.Parameters.AddWithValue("@Id", report.UserId);
cmd.Parameters.AddWithValue("@startDate", report.StartDate);
cmd.Parameters.AddWithValue("@endDate", report.EndDate);
cmd.ExecuteNonQuery();
if (report.UserId == null)
{
return(false);
}
else
{
return(true);
}
}
}
catch (SqlException E)
{
Console.Write(E);
throw;
}
}
public bool CreatePayReport(PayrollTable report)
{
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(@"INSERT INTO PayrollTable (UserId, StartDate, EndDate, IsApproved,isSubmitted) VALUES(@UserId, @startdate, @enddate, @isapproved,@isSubmitted);", connection);
command.Parameters.AddWithValue("@UserId", report.UserId);
command.Parameters.AddWithValue("@startdate", report.StartDate);
command.Parameters.AddWithValue("@enddate", report.EndDate);
command.Parameters.AddWithValue("@isapproved", report.IsApproved);
command.Parameters.AddWithValue("@isSubmitted", report.IsSubmitted);
command.ExecuteNonQuery();
if (report.UserId == null || report.StartDate == null || report.EndDate == null || report.IsApproved == false)
{
return(false);
}
else
{
return(true);
}
}
}
catch (SqlException E)
{
Console.Write(E);
throw;
}
}
public IList <PayrollTable> GetTimeReport(int userid)
{
IList <PayrollTable> payrollLog = new List <PayrollTable>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(@"SELECT userId, startDate, endDate, isSubmitted, isApproved FROM payroll
WHERE userId = @userId
ORDER BY startDate DESC;", connection);
command.Parameters.AddWithValue("@userid", userid);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
PayrollTable report = new PayrollTable
{
UserId = Convert.ToInt32(reader["userId"]),
StartDate = Convert.ToDateTime(reader["startDate"]),
EndDate = Convert.ToDateTime(reader["endDate"]),
IsApproved = Convert.ToBoolean(reader["isApproved"]),
IsSubmitted = Convert.ToBoolean(reader["isSubmitted"]),
};
payrollLog.Add(report);
}
}
return(payrollLog);
}
public IActionResult SubmitTimeCard(PayrollTable payrollLine)
{
payrollLine.IsSubmitted = true;
bool success = payrollDAL.SubmitTime(payrollLine);
return(RedirectToAction("SubmitTimeCard", "Account"));
}
public IActionResult ApproveTimeCard(PayrollTable payrollLine)
{
payrollLine.IsApproved = true;
bool success = payrollDAL.ApproveTime(payrollLine);
return(RedirectToAction("ApproveHoursHub", "Account"));
}
public IActionResult PeriodTimeCard(PayrollTable UserPeriod)
{
ViewBag.TimeCard = hoursDAL.GetTimeCard(UserPeriod.UserId, UserPeriod.StartDate, UserPeriod.EndDate);
ViewBag.PayrollLine = UserPeriod;
return(View());
}
public IActionResult ApproveHoursHub(PayrollTable payrollTable)
{
if (payrollTable.StartDate < new DateTime(1753, 1, 1))
{
payrollTable.StartDate = new DateTime(1753, 1, 1);
}
ViewBag.PayPeriods = payrollDAL.GetListOfPayPeriods();
ViewBag.TimeCards = payrollDAL.GetListOfTimeCards(payrollTable.StartDate);
return(View());
}
public bool SubmitTime(PayrollTable pay)
{
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
if (pay.UserId != null && pay.StartDate != null && pay.EndDate != null && pay.IsSubmitted != false)
{
updatePayroll = @"UPDATE Payroll
SET isSubmitted = 1
WHERE
userId = @UserId
AND
startDate = @StartDate
AND
endDate = @EndDate
AND isApproved = 0;";
}
SqlCommand command = connection.CreateCommand();
command.CommandText = updatePayroll;
command.Parameters.AddWithValue("@UserId", pay.UserId);
command.Parameters.AddWithValue("@StartDate", pay.StartDate);
command.Parameters.AddWithValue("@EndDate", pay.EndDate);
connection.Open();
command.ExecuteNonQuery();
}
if (pay.UserId == null)
{
return(false);
}
else
{
return(true);
}
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(false);
}
}
private List <PayrollTable> MapPayrollReader(SqlDataReader reader)
{
List <PayrollTable> reports = new List <PayrollTable>();
while (reader.Read())
{
PayrollTable report = new PayrollTable
{
UserId = Convert.ToInt32(reader["userId"]),
StartDate = Convert.ToDateTime(reader["startDate"]),
EndDate = Convert.ToDateTime(reader["endDate"]),
IsApproved = Convert.ToBoolean(reader["isApproved"]),
IsSubmitted = Convert.ToBoolean(reader["isSubmitted"]),
Name = Convert.ToString(reader["first_Last_Name"])
};
reports.Add(report);
}
return(reports);
}
public IList <PayrollTable> GetListOfPayPeriods()
{
IList <PayrollTable> payrollLog = new List <PayrollTable>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(@"SELECT DISTINCT startDate, endDate FROM payroll ORDER BY startDate DESC", connection);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
PayrollTable report = new PayrollTable
{
StartDate = Convert.ToDateTime(reader["startDate"]),
EndDate = Convert.ToDateTime(reader["endDate"]),
};
payrollLog.Add(report);
}
}
return(payrollLog);
}
