/// <summary> /// Updates to the status of the expense report for the accounts role /// </summary> /// <param name="expenseId">Expense Id of the report to update</param> /// <param name="empId">Employee Id of the accounts person</param> /// <param name="status">New status</param> public void AccountantActionOnExpenseReport(int expenseId, Guid empId, string status) { string query = "update ExpenseHeader set ProcessedById=@ProcessedById, ProcessedDate=@ProcessedDate,Status=@Status where ExpenseId='" + expenseId + "'"; DataAccessFunctions daFunctions = new DataAccessFunctions(); try { daFunctions.Connection.Open(); daFunctions.Command = new SqlCommand(query, daFunctions.Connection); daFunctions.Command.Parameters.AddWithValue("@ProcessedById", empId); daFunctions.Command.Parameters.AddWithValue("@ProcessedDate", DateTime.Now); daFunctions.Command.Parameters.AddWithValue("@Status", status); daFunctions.Command.ExecuteNonQuery(); } catch (Exception ex) { throw new Exception("There was a problem updating the status of the expense: " + ex.Message); } finally { daFunctions.Connection.Close(); } }
/// <summary> /// Get the total amount of expenses approved by individual supervisor, including: /// - already approved by both supervisor and accountant (i.e. Status = ApprovedByAccountant) /// EXCLUDING: /// - pending for accountant approval (i.e. Status = ApprovedBySupervisor) /// - approved by supervisor BUT rejected by accountant (i.e. Status = RejectedByAccountant) /// </summary> /// <param name="status"></param> /// <returns></returns> public List<Employee> GetExpenseReportsBySupervisor(int month) { List<Employee> employees = new List<Employee>(); EmployeeDAL employeeDAL = new EmployeeDAL(); DataAccessFunctions daFunctions = new DataAccessFunctions(); string query = string.Format("SELECT H.ApprovedById AS SupervisorId, COUNT(H.ExpenseId) AS AmountApproved, SUM(I.AudAmount) AS ExpenseApproved FROM ExpenseItem I LEFT OUTER JOIN ExpenseHeader H ON I.ExpenseHeaderId = H.ExpenseId WHERE H.Status ='ApprovedByAccounts' AND DATEPART(month,ProcessedDate)={0} GROUP BY H.ApprovedById", month); //string query = string.Format("SELECT ApprovedById, COUNT(ExpenseId) AS AmountApproved FROM ExpenseHeader WHERE Status ='ApprovedByAccounts' GROUP BY ApprovedById"); daFunctions.Command = new SqlCommand(query, daFunctions.Connection); try { daFunctions.Connection.Open(); SqlDataReader rdr = daFunctions.Command.ExecuteReader(); while (rdr.Read()) { Employee emp = new Employee(); emp = employeeDAL.GetEmployee(rdr["SupervisorId"] as Guid? ?? default(Guid)); emp.AmountApproved = rdr["AmountApproved"] as int? ?? default(int); emp.ExpenseApproved = rdr["ExpenseApproved"] as decimal? ?? default(decimal); employees.Add(emp); } daFunctions.Connection.Close(); } catch (Exception ex) { throw new Exception("There was a problem retrieving expense approved by supervisor reports: " + ex.Message); } return employees; }
/// <summary> /// Inserts expense items into the database /// </summary> /// <param name="expenseId">Expense header Id</param> /// <param name="expenseDate">Date of the transaction</param> /// <param name="location">Location of the transaction</param> /// <param name="description">Reason for transaction</param> /// <param name="amount">Amount in original currency</param> /// <param name="currency">Currency of the transaction</param> /// <param name="audAmount">Amount in AUD</param> /// <param name="receiptFileName">The new file name of the receipt</param> private void InsertExpenseItem(int expenseId, DateTime expenseDate, string location, string description, decimal amount, string currency, decimal audAmount, string receiptFileName) { DataAccessFunctions daFunctions = new DataAccessFunctions(); daFunctions.Command.CommandType = CommandType.StoredProcedure; daFunctions.Command.CommandText = "AddExpenseItem"; //parameters for the expense items daFunctions.Command.Parameters.AddWithValue("@ExpenseHeaderId", expenseId); daFunctions.Command.Parameters.AddWithValue("@ExpenseDate", expenseDate); daFunctions.Command.Parameters.AddWithValue("@Location", location); daFunctions.Command.Parameters.AddWithValue("@Description", description); daFunctions.Command.Parameters.AddWithValue("@Amount", amount); daFunctions.Command.Parameters.AddWithValue("@Currency", currency); daFunctions.Command.Parameters.AddWithValue("@AudAmount", audAmount); daFunctions.Command.Parameters.AddWithValue("@ReceiptFileName", receiptFileName); try { daFunctions.Connection.Open(); daFunctions.Command.ExecuteNonQuery(); } catch (Exception ex) { throw new Exception("Problem inserting expense item to database: " + ex.Message); } finally { daFunctions.Connection.Close(); } }
/// <summary> /// Inserts the expense header into the database /// </summary> /// <param name="createdById">Employee created the expense</param> /// <param name="createDate">The date the expense report was created</param> /// <param name="departmentId">Department the expense report is allocated to</param> /// <param name="status">Status of the expense report</param> /// <returns>Returns the expense header id</returns> private int InsertExpenseHeader(Guid createdById, DateTime createDate, int departmentId, string status) { int expenseId = -1; // store the returned value of the expenseId post insert of new record DataAccessFunctions daFunctions = new DataAccessFunctions(); daFunctions.Command.CommandType = CommandType.StoredProcedure; daFunctions.Command.CommandText = "AddExpenseHeader"; daFunctions.Command.CommandType = CommandType.StoredProcedure; //Parameters for the expense header daFunctions.Command.Parameters.AddWithValue("@CreatedById", createdById); daFunctions.Command.Parameters.AddWithValue("@CreateDate", createDate); daFunctions.Command.Parameters.AddWithValue("@DepartmentId", departmentId); daFunctions.Command.Parameters.AddWithValue("@Status", status); // Will return the value of expense Id daFunctions.Command.Parameters.Add("@Id", SqlDbType.Int); try { daFunctions.Command.Parameters["@Id"].Direction = ParameterDirection.Output; daFunctions.Connection.Open(); daFunctions.Command.ExecuteNonQuery(); expenseId = Convert.ToInt32(daFunctions.Command.Parameters["@Id"].Value); } catch (Exception ex) { throw new Exception("Problem inserting the expense header in database: " + ex.Message); } finally { daFunctions.Connection.Close(); } return expenseId; }
/// <summary> /// Retrieves the expense reports from the database with given query /// </summary> /// <param name="query">sql query</param> /// <returns></returns> private List<ExpenseReport> GetReportsFromDatabase(string query) { List<ExpenseReport> expenseReports = new List<ExpenseReport>(); EmployeeDAL employeeDAL = new EmployeeDAL(); DepartmentDAL departmentDAL = new DepartmentDAL(); DataAccessFunctions daFunctions = new DataAccessFunctions(); daFunctions.Command = new SqlCommand(query, daFunctions.Connection); try { daFunctions.Connection.Open(); SqlDataReader rdr = daFunctions.Command.ExecuteReader(); while (rdr.Read()) { ExpenseReport report = new ExpenseReport(); Employee createdBy = new Employee(); Employee approvedBy = new Employee(); Employee processedBy = new Employee(); decimal expenseTotal; report.ExpenseId = rdr["ExpenseId"] as int? ?? default(int); //report.DepartmentId = rdr["DepartmentId"] as int? ?? default(int); report.CreateDate = (DateTime)rdr["CreateDate"]; report.ExpenseToDept = departmentDAL.GetDepartmentProfile(rdr["DepartmentId"] as int? ?? default(int)); report.Status = (ReportStatus)Enum.Parse(typeof(ReportStatus), (string)rdr["Status"]); report.CreatedBy = employeeDAL.GetEmployee(rdr["CreatedById"] as Guid? ?? default(Guid)); report.ApprovedBy = employeeDAL.GetEmployee(rdr["ApprovedById"] as Guid? ?? default(Guid)); report.ProcessedBy = employeeDAL.GetEmployee(rdr["ProcessedById"] as Guid? ?? default(Guid)); report.ApprovedDate = rdr["ApprovedDate"] as DateTime? ?? default(DateTime); report.ExpenseItems = GetExpenseItemsByExpenseId(report.ExpenseId, out expenseTotal); report.ExpenseTotal = expenseTotal; expenseReports.Add(report); } } catch (Exception ex) { throw new Exception("There was a problem retrieving expense reports: " + ex.Message); } finally { daFunctions.Connection.Close(); } return expenseReports; }
/// <summary> /// Private method to get the individual expense items for a expense report /// </summary> /// <param name="expenseid">Expense Header Id</param> /// <param name="expenseTotal">output the total of the expense report</param> /// <returns>Expense items</returns> private List<ExpenseItem> GetExpenseItemsByExpenseId(int expenseid, out decimal expenseTotal) { expenseTotal = 0; List<ExpenseItem> expenseItems = new List<ExpenseItem>(); DataAccessFunctions daFunctions = new DataAccessFunctions(); string query = String.Format("SELECT * FROM ExpenseItem WHERE ExpenseHeaderId={0}", expenseid); try { daFunctions.Connection.Open(); daFunctions.Command.CommandText = query; SqlDataReader rdr = daFunctions.Command.ExecuteReader(); while (rdr.Read()) { ExpenseItem item = new ExpenseItem(); item.ExpenseHeaderId = rdr["ExpenseHeaderId"] as int? ?? default(int); item.ItemId = rdr["ItemId"] as int? ?? default(int); item.ExpenseDate = (DateTime)rdr["ExpenseDate"]; item.Location = (string)rdr["Location"]; item.Description = (string)rdr["Description"]; item.Currency = (string)rdr["Currency"]; item.Amount = rdr["Amount"] as decimal? ?? default(decimal); item.AudAmount = rdr["AudAmount"] as decimal? ?? default(decimal); item.ReceiptFileName = (string)rdr["ReceiptFileName"]; expenseTotal += item.AudAmount; expenseItems.Add(item); } } catch (Exception ex) { throw new Exception("There was a problem retrieving expense items for expenseid: " + expenseid + ": " + ex.Message); } finally { daFunctions.Connection.Close(); } return expenseItems; }
//Retrieves the expense total from the database with provided query private decimal GetExpenseTotal(string query) { decimal totalExpense = 0; DataAccessFunctions daFunctions = new DataAccessFunctions(); daFunctions.Command.CommandText = query; try { daFunctions.Connection.Open(); totalExpense = daFunctions.Command.ExecuteScalar() as decimal? ?? default(decimal); daFunctions.Connection.Close(); } catch (Exception ex) { throw new Exception("Unable to execute method GetExpenseTotal: " + ex.Message); } return totalExpense; }
public DepartmentDAL() { daFunctions = new DataAccessFunctions(); }
private bool CheckDatabaseForReportStatus(int id) { bool updated; DataAccessFunctions daFunctions = new DataAccessFunctions(); string query = String.Format("SELECT status from ExpenseHeader WHERE ExpenseId={0}", id); daFunctions.Command.CommandText = query; try { daFunctions.Connection.Open(); string status = daFunctions.Command.ExecuteScalar().ToString(); daFunctions.Connection.Close(); if (status == "ApprovedBySupervisor") { updated = true; } else if (status == "RejectedBySupervisor") { updated = true; } else { updated = false; } return updated; } catch (Exception ex) { throw new Exception("There was a problem running method CheckDatabaseForReportStatus: " + ex.Message); } }
private bool CheckDatabaseForExpenseId(int id) { bool exist; DataAccessFunctions daFunctions = new DataAccessFunctions(); string query = String.Format("SELECT ExpenseId from ExpenseHeader WHERE ExpenseId={0}", id); daFunctions.Command.CommandText = query; try { daFunctions.Connection.Open(); int dbExpenseId = (int)daFunctions.Command.ExecuteScalar(); daFunctions.Connection.Close(); if (id == dbExpenseId) { exist = true; } else { exist = false; } return exist; } catch (Exception ex) { throw new Exception("There was a problem running method CheckDatabaseForExpense: " + ex.Message); } }
public EmployeeDAL() { daFunctions = new DataAccessFunctions(); }