using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using AccountPlus.DataAccess;
using AccountPlus.Formatting;
namespace AccountPlus.BusinessLogic
{
public class Expense
{
private DBHelper _dbHelper = new DBHelper();
private Arch arch = new Arch();
public bool AddNewExpense(int itemID, string expenseDesc,string expenseAmount, int expenseBy, string expenseDate)
{
string monthYear = DataFormat.GetDateTime(expenseDate).ToString("ddMMyy").Substring(2);
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@itemID", itemID));
paramCollection.Add(new DBParameter("@expenseDesc", expenseDesc));
paramCollection.Add(new DBParameter("@expenseAmount", expenseAmount));
paramCollection.Add(new DBParameter("@expenseBy", expenseBy));
paramCollection.Add(new DBParameter("@expenseDate", expenseDate, DbType.DateTime));
paramCollection.Add(new DBParameter("@monthYear", monthYear));
string Query = "INSERT INTO Expense_Details (Item_Id, Exp_Desc , " +
" Exp_Amount, Exp_By , Exp_Date , MonthYear , " +
"Finalized, IsDeleted ) " +
"VALUES (@itemID, @expenseDesc, @expenseAmount, " +
"@expenseBy, @expenseDate, @monthYear, 0, 0)";
return _dbHelper.ExecuteNonQuery(Query, paramCollection) > 0;
}
public bool ModifyExpenses(int itemId, int expenseID, string expenseDesc, string expenseAmount, string expenseDate)
{
string monthYear = System.DateTime.Now.ToString("ddMMyy").Substring(2);
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@expenseDesc", expenseDesc));
paramCollection.Add(new DBParameter("@expenseAmount", expenseAmount));
paramCollection.Add(new DBParameter("@expDate", expenseDate, DbType.DateTime ));
paramCollection.Add(new DBParameter("@itemId", itemId));
paramCollection.Add(new DBParameter("@monthYear", monthYear));
paramCollection.Add(new DBParameter("@expenseID", expenseID));
string Query = "UPDATE Expense_Details SET Exp_Desc = @expenseDesc , " +
"Exp_Amount = @expenseAmount, " +
"Exp_Date =@expDate, Item_Id =@itemId, MonthYear=@monthYear WHERE Exp_Id=@expenseID";
return _dbHelper.ExecuteNonQuery(Query, paramCollection) > 0;
}
public bool DeleteExpenses(int expenseID)
{
string Query = "UPDATE Expense_Details SET IsDeleted = 1 WHERE Exp_Id=" + expenseID.ToString();
return _dbHelper.ExecuteNonQuery(Query) > 0;
}
public DataTable GetDisplayData(int expenseID)
{
DataTable dt = new DataTable();
string Query = "SELECT Expense_Details.Exp_Id,Item_Details.Item_Name,Expense_Details.Exp_Desc,Expense_Details.Exp_Amount, " +
"Expense_Details.Exp_Date from Expense_Details,Item_Details " +
"where Expense_Details.Item_Id=Item_Details.Item_Id AND Expense_Details.Exp_Id=" + expenseID.ToString();
dt = _dbHelper.ExecuteDataTable(Query);
return dt;
}
public string GetExpensedBy(int expenseID)
{
string Query = "SELECT First_Name + ' ' + Last_Name from User_Info where User_Id IN (Select Exp_By from Expense_Details Where Exp_Id= " + expenseID.ToString() + ")";
return _dbHelper.ExecuteScalar(Query).ToString();
}
/// <summary>
/// Returns User_Id
/// </summary>
/// <param name="expenseID"></param>
/// <returns></returns>
public string GetExpBy(int expenseID)
{
string Query = "Select Exp_By from Expense_Details Where Exp_Id= " + expenseID.ToString() ;
return _dbHelper.ExecuteScalar(Query).ToString();
}
}
}