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