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