/// <summary>
/// Returns a count based on the condition.
/// </summary>
/// <param name="employee">A employee value.</param>
/// <param name="category">A category value.</param>
/// <param name="status">A status value.</param>
/// <returns>The record count.</returns>
public int Count(string employee, LeaveCategories?category, LeaveStatuses?status)
{
const string SQL_STATEMENT =
"SELECT COUNT(1) " +
"FROM dbo.Leaves " +
"{0}";
int result = 0;
// Connect to database.
Database db = DatabaseFactory.CreateDatabase(CONNECTION_NAME);
using (DbCommand cmd = db.GetSqlStringCommand(SQL_STATEMENT))
{
// Append filters.
string filter = AppendFilters(db, cmd, employee, category, status);
// Construct final WHERE statement.
if (!string.IsNullOrWhiteSpace(filter))
{
filter = "WHERE " + base.FormatFilterStatement(filter);
}
cmd.CommandText = string.Format(SQL_STATEMENT, filter);
result = Convert.ToInt32(db.ExecuteScalar(cmd));
}
return(result);
}
private List <Leave> ListLeaves(LeaveCategories?category, LeaveStatuses?status,
string employee, int page, string sort, string sortDir)
{
var categories = CreateListItems(typeof(LeaveCategories));
var statuses = CreateListItems(typeof(LeaveStatuses));
// Get selected Category.
if (category.HasValue)
{
categories.Find(x => x.Text == category.Value.ToString()).Selected = true;
}
// Get selected Status.
if (status.HasValue)
{
statuses.Find(x => x.Text == status.Value.ToString()).Selected = true;
}
ViewBag.CategoryList = categories;
ViewBag.StatusList = statuses;
int totalRowCount = 0;
List <Leave> leaves = new List <Leave>();
int startRowIndex = (page - 1) * MAXIMUM_ROWS;
var upc = new LeaveProcessComponent();
leaves = upc.ListLeavesByEmployee(MAXIMUM_ROWS, startRowIndex,
string.Format("{0} {1}", sort, sortDir), employee,
category, status, out totalRowCount);
ViewBag.TotalRowCount = totalRowCount;
return(leaves);
}
public ListLeavesResponse ListLeavesByEmployee(int maximumRows = 10, int startRowIndex = 1,
string sortExpression = null, string employee = null, LeaveCategories?category = null,
LeaveStatuses?status = null)
{
int totalRowCount = 0;
var response = new ListLeavesResponse();
try
{
LeaveComponent bc = new LeaveComponent();
response.Leaves = bc.ListLeavesByEmployee(maximumRows, startRowIndex, sortExpression,
employee, category, status, out totalRowCount);
response.TotalRowCount = totalRowCount;
}
catch (Exception ex)
{
// Repack to Http error.
var httpError = new HttpResponseMessage()
{
StatusCode = (HttpStatusCode)422, // Unprocessable Entity
ReasonPhrase = ex.Message
};
throw new HttpResponseException(httpError);
}
return(response);
}
public ActionResult Approvals(
LeaveCategories?category = null,
LeaveStatuses?status = null,
int page = 1, string sort = "DateSubmitted", string sortDir = "DESC")
{
List <Leave> leaves = ListLeaves(category, status, null, page, sort, sortDir);
return(View(leaves));
}
public ActionResult Leaves(
LeaveCategories?categoryFilter = null,
LeaveStatuses?statusFilter = null,
int page = 1, string sort = "DateSubmitted", string sortDir = "DESC")
{
List <Leave> leaves = ListLeaves(categoryFilter, statusFilter, Environment.UserName, page, sort, sortDir);
return(View(leaves));
}
/// <summary>
/// Calls the ListLeavesByEmployee business method of the LeaveComponent.
/// </summary>
/// <param name="maximumRows"> A maximumRows value.</param>
/// <param name="startRowIndex"> A startRowIndex value.</param>
/// <param name="sortExpression"> A sortExpression value.</param>
/// <param name="employee"> A employee value.</param>
/// <param name="category"> A category value.</param>
/// <param name="status"> A status value.</param>
/// <param name="int"> A int value.</param>
/// <returns>Returns a List<Leave> object.</returns>
public List <Leave> ListLeavesByEmployee(int maximumRows, int startRowIndex,
string sortExpression, string employee, LeaveCategories?category,
LeaveStatuses?status, out int totalRowCount)
{
LeaveComponent bc = new LeaveComponent();
return(bc.ListLeavesByEmployee(maximumRows, startRowIndex, sortExpression,
employee, category, status, out totalRowCount));
}
public List <Leave> leaveGrid_GetData(
[Control] LeaveCategories?category,
[Control] LeaveStatuses?status,
int maximumRows,
int startRowIndex, out int totalRowCount, string sortByExpression
)
{
var upc = new LeaveController();
return(upc.ListLeavesByEmployee(maximumRows, startRowIndex, sortByExpression,
Environment.UserName, category, status, out totalRowCount));
}
private void LoadLeaveRecords()
{
// NOTE: Since this is WinForms, we can actually keep a cache of the
// Leave records in memory and do not need to keep querying the back-end.
LeaveCategories?category = LeaveController.GetEnumValue <LeaveCategories>(categoryFilter.Text);
LeaveStatuses? status = LeaveController.GetEnumValue <LeaveStatuses>(statusFilter.Text);
BindGrid(leaveRecordsGrid, _leaves);
RefreshGrid(leaveRecordsGrid, _leaves, _sortColumn, categoryFilter.Text,
statusFilter.Text, Environment.UserName);
}
/// <summary>
/// Conditionally retrieves one or more rows from the Leaves table with paging and a sort expression.
/// </summary>
/// <param name="maximumRows">The maximum number of rows to return.</param>
/// <param name="startRowIndex">The starting row index.</param>
/// <param name="sortExpression">The sort expression.</param>
/// <param name="employee">A employee value.</param>
/// <param name="category">A category value.</param>
/// <param name="status">A status value.</param>
/// <returns>A collection of Leave objects.</returns>
public List <Leave> Select(int maximumRows, int startRowIndex, string sortExpression,
string employee, LeaveCategories?category, LeaveStatuses?status)
{
const string SQL_STATEMENT =
"WITH SortedLeaves AS " +
"(SELECT ROW_NUMBER() OVER (ORDER BY {1}) AS RowNumber, " +
"[LeaveID], [CorrelationID], [Category], [Employee], [StartDate], [EndDate], [Description]" +
", [Duration], [Status], [IsCompleted], [Remarks], [DateSubmitted] " +
"FROM dbo.Leaves " +
"{0}" +
") SELECT * FROM SortedLeaves " +
"WHERE RowNumber BETWEEN @StartRowIndex AND @EndRowIndex";
startRowIndex++;
long endRowIndex = startRowIndex + maximumRows;
List <Leave> result = new List <Leave>();
// Connect to database.
Database db = DatabaseFactory.CreateDatabase(CONNECTION_NAME);
using (DbCommand cmd = db.GetSqlStringCommand(SQL_STATEMENT))
{
// Append filters.
string filter = AppendFilters(db, cmd, employee, category, status);
// Construct final WHERE statement.
if (!string.IsNullOrWhiteSpace(filter))
{
filter = "WHERE " + base.FormatFilterStatement(filter);
}
cmd.CommandText = string.Format(SQL_STATEMENT, filter, sortExpression);
// Paging Parameters.
db.AddInParameter(cmd, "@StartRowIndex", DbType.Int64, startRowIndex);
db.AddInParameter(cmd, "@EndRowIndex", DbType.Int64, endRowIndex);
using (IDataReader dr = db.ExecuteReader(cmd))
{
while (dr.Read())
{
// Create a new Leave
Leave leave = LoadLeave(dr);
// Add to List.
result.Add(leave);
}
}
}
return(result);
}
/// <summary>
/// Returns a count based on the condition.
/// </summary>
/// <param name="employee">A employee value.</param>
/// <param name="category">A category value.</param>
/// <param name="status">A status value.</param>
/// <returns>The record count.</returns>
public int Count(string employee, LeaveCategories?category,
LeaveStatuses?status)
{
using (var db = new DbContext(CONNECTION_NAME))
{
// Store the query.
IQueryable <Leave> query = db.Set <Leave>();
// Append filters.
query = AppendFilters(query, employee, category, status);
// Return result.
return(query.Count());
}
}
//[Route("{action}/{maximumRows}/{startRowIndex}sortExpression={sortExpression}&employee={employee}&category={category=}&status={status=}")]
public ListLeavesResponse ListLeavesByEmployee(int maximumRows, int startRowIndex,
string sortExpression, string employee, LeaveCategories?category,
LeaveStatuses?status)
{
int totalRowCount = 0;
var response = new ListLeavesResponse();
LeaveComponent bc = new LeaveComponent();
response.Leaves = bc.ListLeavesByEmployee(maximumRows, startRowIndex, sortExpression,
employee, category, status, out totalRowCount);
response.TotalRowCount = totalRowCount;
return(response);
}
public List <Leave> leaveGrid_GetData(
[Control] LeaveCategories?category,
[Control] LeaveStatuses?status,
int maximumRows,
int startRowIndex, out int totalRowCount, string sortByExpression
)
{
if (!Page.IsPostBack && status == null)
{
status = LeaveStatuses.Pending;
}
var upc = new LeaveController();
return(upc.ListLeavesByEmployee(maximumRows, startRowIndex, sortByExpression,
null, category, status, out totalRowCount));
}
/// <summary>
/// Conditionally retrieves one or more rows from the Leaves table with paging and a sort expression.
/// </summary>
/// <param name="maximumRows">The maximum number of rows to return.</param>
/// <param name="startRowIndex">The starting row index.</param>
/// <param name="sortExpression">The sort expression.</param>
/// <param name="employee">A employee value.</param>
/// <param name="category">A category value.</param>
/// <param name="status">A status value.</param>
/// <returns>A collection of Leave objects.</returns>
public List <Leave> Select(int maximumRows, int startRowIndex, string sortExpression,
string employee, LeaveCategories?category, LeaveStatuses?status)
{
using (var db = new DbContext(CONNECTION_NAME))
{
// Store the query.
IQueryable <Leave> query = db.Set <Leave>();
// Append filters.
query = AppendFilters(query, employee, category, status);
// Sort and page.
query = query.OrderBy(sortExpression)
.Skip(startRowIndex).Take(maximumRows);
// Return result.
return(query.ToList());
}
}
/// <summary>
/// Returns a filtered list of Leaves.
/// </summary>
/// <param name="sort">The sort expression.</param>
/// <param name="categoryFilter">The category to filter by.</param>
/// <param name="statusFilter">The status to filter by.</param>
/// <param name="employee">The employee to filter by.</param>
/// <returns></returns>
protected static ObservableCollection <Leave> FilterLeaves(string sort, string categoryFilter, string statusFilter, string employee = null)
{
LeaveCategories?category = null;
LeaveStatuses? status = null;
if (!string.IsNullOrWhiteSpace(categoryFilter) && categoryFilter != "- All -")
{
category = (LeaveCategories?)Enum.Parse(typeof(LeaveCategories), categoryFilter);
}
if (!string.IsNullOrWhiteSpace(statusFilter) && statusFilter != "- All -")
{
status = (LeaveStatuses?)Enum.Parse(typeof(LeaveStatuses), statusFilter);
}
var query = _cacheLeaves.AsQueryable();
// Filter by Category.
if (category != null)
{
query = query.Where(l => l.Category == category);
}
// Filter by Status.
if (status != null)
{
query = query.Where(l => l.Status == status);
}
// Fiilter by Employee.
if (!string.IsNullOrWhiteSpace(employee))
{
query = query.Where(l => l.Employee == employee);
}
// Set sorting column.
query = query.OrderBy(sort);
return(new ObservableCollection <Leave>(query.ToList()));
}
private void RefreshGrid(DataGridView grid, List <Leave> leaves, string sort,
string categoryFilter, string statusFilter, string employee = null)
{
if (leaves == null)
{
return;
}
// Perform all filtering and sorting in memory only. Do not need to call back-end.
LeaveCategories?category = LeaveController.GetEnumValue <LeaveCategories>(categoryFilter);
LeaveStatuses? status = LeaveController.GetEnumValue <LeaveStatuses>(statusFilter);
var query = leaves.AsQueryable();
// Filter by Category.
if (category != null)
{
query = query.Where(l => l.Category == category);
}
// Filter by Status.
if (status != null)
{
query = query.Where(l => l.Status == status);
}
if (!string.IsNullOrWhiteSpace(employee))
{
query = query.Where(l => l.Employee == employee);
}
// Set sorting column.
query = query.OrderBy(sort);
BindGrid(grid, query.ToList());
}
/// <summary>
/// ListLeavesByEmployee business method.
/// </summary>
/// <param name="startRowIndex">A startRowIndex value.</param>
/// <param name="maximumRows">A maximumRows value.</param>
/// <param name="sortExpression">A sortExpression value.</param>
/// <param name="employee">A employee value.</param>
/// <param name="category">A category value.</param>
/// <param name="status">A status value.</param>
/// <returns>Returns a List<Leave> object.</returns>
public List <Leave> ListLeavesByEmployee(int maximumRows, int startRowIndex,
string sortExpression, string employee, LeaveCategories?category, LeaveStatuses?status,
out int totalRowCount)
{
List <Leave> result = default(List <Leave>);
if (string.IsNullOrWhiteSpace(sortExpression))
{
sortExpression = "DateSubmitted DESC";
}
// Data access component declarations.
var leaveDAC = new LeaveDAC();
// Step 1 - Calling Select on LeaveDAC.
result = leaveDAC.Select(maximumRows, startRowIndex, sortExpression,
employee, category, status);
// Step 2 - Get count.
totalRowCount = leaveDAC.Count(employee, category, status);
return(result);
}
/// <summary>
/// Calls the ListLeavesByEmployee operation method in the LeaveService.
/// </summary>
/// <param name="maximumRows">A maximumRows value.</param>
/// <param name="startRowIndex">A startRowIndex value.</param>
/// <param name="sortExpression">A sortExpression value.</param>
/// <param name="employee">A employee value.</param>
/// <param name="category">A category value.</param>
/// <param name="status">A status value.</param>
/// <param name="int">A int value.</param>
/// <returns>Returns a List<Leave> object.</returns>
public ObservableCollection <Leave> ListLeavesByEmployee(int maximumRows, int startRowIndex,
string sortExpression, string employee, LeaveCategories?category, LeaveStatuses?status,
out int totalRowCount)
{
var result = default(ObservableCollection <Leave>);
LeaveServiceClient proxy = new LeaveServiceClient();
try
{
result = proxy.ListLeavesByEmployee(maximumRows, startRowIndex, sortExpression,
employee, category, status, out totalRowCount);
}
catch (FaultException fex)
{
// TODO: Handle your exception here or raise it to the UI.
// Do not display sensitive information to the UI.
throw new ApplicationException(fex.Message);
}
finally
{
proxy.Close();
}
return(result);
}
/// <summary>
/// Conditionally appends filters to the query.
/// </summary>
/// <param name="query">The query object.</param>
/// <param name="employee">The employee to filter by.</param>
/// <param name="category">The category to filter by.</param>
/// <param name="status">The status to filter by.</param>
/// <returns>A query object.</returns>
private static IQueryable <Leave> AppendFilters(IQueryable <Leave> query,
string employee, LeaveCategories?category, LeaveStatuses?status)
{
// Filter employee.
if (!string.IsNullOrWhiteSpace(employee))
{
query = query.Where(l => l.Employee == employee);
}
// Filter category.
if (category != null)
{
query = query.Where(l => l.Category == category);
}
// Filter status.
if (status != null)
{
query = query.Where(l => l.Status == status);
}
return(query);
}
/// <summary>
/// Calls the ListLeavesByEmployee operation method in the LeaveService.
/// </summary>
/// <param name="maximumRows">A maximumRows value.</param>
/// <param name="startRowIndex">A startRowIndex value.</param>
/// <param name="sortExpression">A sortExpression value.</param>
/// <param name="employee">A employee value.</param>
/// <param name="category">A category value.</param>
/// <param name="status">A status value.</param>
/// <param name="int">A int value.</param>
/// <returns>Returns a List<Leave> object.</returns>
public List <Leave> ListLeavesByEmployee(int maximumRows, int startRowIndex,
string sortExpression, string employee, LeaveCategories?category, LeaveStatuses?status,
out int totalRowCount)
{
List <Leave> leaves = null;
var parameters = new Dictionary <string, object>();
parameters.Add("maximumRows", maximumRows);
parameters.Add("startRowIndex", startRowIndex);
parameters.Add("sortExpression", sortExpression);
parameters.Add("employee", employee);
parameters.Add("category", category);
parameters.Add("status", status);
var result = HttpGet <ListLeavesResponse>("Leave/ListLeavesByEmployee", parameters, MediaType.Json);
leaves = result.Leaves;
totalRowCount = result.TotalRowCount;
return(leaves);
}
/// <summary>
/// Conditionally appends filters to the query statememt.
/// </summary>
/// <param name="db">A Database object.</param>
/// <param name="cmd">A DbCommand object.</param>
/// <param name="employee">The employee to filter by.</param>
/// <param name="category">The category to filter by.</param>
/// <param name="status">The status to filter by.</param>
/// <returns>A condition statement.</returns>
private static string AppendFilters(Database db, DbCommand cmd,
string employee, LeaveCategories?category, LeaveStatuses?status)
{
string filter = string.Empty;
// Employee filter.
if (!string.IsNullOrWhiteSpace(employee))
{
db.AddInParameter(cmd, "@Employee", DbType.AnsiString, employee);
filter += "AND [Employee]=@Employee ";
}
// Category filter.
if (category != null)
{
db.AddInParameter(cmd, "@Category", DbType.Byte, category);
filter += "AND [Category]=@Category ";
}
// Status filter.
if (status != null)
{
db.AddInParameter(cmd, "@Status", DbType.Byte, status);
filter += "AND [Status]=@Status ";
}
return(filter);
}