private void LoadData()
{
// SQL Queries
string selectExpenses = "SELECT dbo.GeneralExpense.*, dbo.Bill.* FROM GeneralExpense LEFT JOIN Bill ON GeneralExpense.Id = Bill.ExpenseId";
string selectDebts = "SELECT dbo.Debt.* FROM dbo.Debt";
using (SqlConnection loadConn = new SqlConnection(_connectionString))
{
try
{
loadConn.Open();
SqlCommand cmdGetValues = new SqlCommand(selectExpenses, loadConn);
SqlDataReader dataReader = cmdGetValues.ExecuteReader();
while (dataReader.Read())
{
// Determine if data is general expense or bill
if (dataReader.IsDBNull(8))
{
// Create expense objects
GeneralExpense expense = new GeneralExpense();
expense.Id = dataReader.GetInt32(0);
expense.Name = dataReader.GetString(1);
expense.Description = dataReader.GetString(2);
expense.Value = dataReader.GetDouble(3);
expense.DateReceived = dataReader.GetDateTime(4);
expense.IsPaid = dataReader.GetBoolean(5);
expense.DatePaid = dataReader.IsDBNull(6) ? _dtNullDate : dataReader.GetDateTime(6);
expense.IsOutstanding = dataReader.GetBoolean(7);
_lstGeneralExpenses.Add(expense);
}
else
{
// Create bill objects
Bill bill = new Bill();
bill.Id = dataReader.GetInt32(0);
bill.Name = dataReader.GetString(1);
bill.Description = dataReader.GetString(2);
bill.Value = dataReader.GetDouble(3);
bill.DateReceived = dataReader.GetDateTime(4);
bill.IsPaid = dataReader.GetBoolean(5);
bill.DatePaid = dataReader.IsDBNull(6) ? _dtNullDate : dataReader.GetDateTime(6);
bill.IsOutstanding = dataReader.GetBoolean(7);
bill.BillId = dataReader.GetInt32(8);
bill.Issuer = dataReader.GetString(10);
bill.ReferenceNo = dataReader.GetString(11);
_lstBills.Add(bill);
}
}
dataReader.Close();
cmdGetValues.CommandText = selectDebts;
dataReader = cmdGetValues.ExecuteReader();
while (dataReader.Read())
{
// Create debt objects
Debt debt = new Debt();
debt.Id = dataReader.GetInt32(0);
debt.ExpenseId = dataReader.GetInt32(1);
debt.PersonOwing = dataReader.GetString(2);
debt.AmountOutstanding = dataReader.GetDouble(3);
debt.IsPaid = dataReader.GetBoolean(4);
debt.DatePaid = dataReader.IsDBNull(5) ? _dtNullDate : dataReader.GetDateTime(5);
_lstDebts.Add(debt);
}
dataReader.Dispose();
cmdGetValues.Dispose();
loadConn.Close();
}
catch (Exception e)
{
// ### Exception details find someway to pass this to the form
string exceptionMessage = "The following error occured while trying to connect to the database:\n\n";
exceptionMessage += "Name: " + e.Message + "\n\n";
exceptionMessage += "Details: " + e.StackTrace;
// ### Debug
Console.WriteLine(exceptionMessage);
}
}
}
private static void AddNewExpense(ExpenseManager inEM, bool isBill)
{
ExpenseManager em = inEM;
GeneralExpense ge = new GeneralExpense();
string eName = string.Empty;
string eDesc = string.Empty;
double eValue = 0;
DateTime eDateRec = new DateTime(9999, 01, 01);
bool eIsPaid = false;
DateTime eDatePaid = new DateTime(9999, 01, 01);
bool eIsOutstanding = false;
string input = string.Empty;
bool isValid = false;
string invalid = "\t\tInvalid Value.";
// Get Name
while (!isValid)
{
Console.Write("Name: ");
eName = Console.ReadLine();
if (string.IsNullOrEmpty(eName))
{
Console.WriteLine(invalid);
Console.ReadLine();
}
else
{
if (eName.Equals(_cancelChar))
return;
isValid = true;
}
}
isValid = false;
// Get Desc
while (!isValid)
{
Console.Write("Description: ");
eDesc = Console.ReadLine();
if (string.IsNullOrEmpty(eDesc))
{
Console.WriteLine(invalid);
Console.ReadLine();
}
else
{
if (eDesc.Equals(_cancelChar))
return;
isValid = true;
}
}
isValid = false;
// Get value
while (!isValid)
{
Console.Write("Value: ");
input = Console.ReadLine();
if (string.IsNullOrEmpty(input))
{
Console.WriteLine(invalid);
Console.ReadLine();
}
else
{
if (input.Equals(_cancelChar))
return;
if (double.TryParse(input, out eValue))
{
isValid = true;
}
else
{
Console.WriteLine(invalid);
Console.ReadLine();
}
}
}
isValid = false;
input = string.Empty;
// Date recieved
while (!isValid)
{
Console.Write("Date Received: ");
input = Console.ReadLine();
if (string.IsNullOrEmpty(input))
{
Console.WriteLine(invalid);
Console.ReadLine();
}
else
{
if (input.Equals(_cancelChar))
return;
if (DateTime.TryParse(input, out eDateRec))
{
isValid = true;
}
else
{
Console.WriteLine(invalid);
Console.ReadLine();
}
}
}
isValid = false;
input = string.Empty;
// Get isPaid
while (!isValid)
{
Console.Write("Has this expense been paid (Y/N): ");
input = Console.ReadLine();
switch (input.ToUpper())
{
case "Y":
eIsPaid = true;
isValid = true;
break;
case "N":
eIsPaid = false;
isValid = true;
break;
default:
if (input.Equals(_cancelChar))
return;
Console.WriteLine(invalid);
Console.ReadLine();
break;
}
}
isValid = false;
input = string.Empty;
if (eIsPaid)
{
// Date paid
while (!isValid)
{
Console.Write("Date Paid: ");
input = Console.ReadLine();
if (string.IsNullOrEmpty(input))
{
Console.WriteLine(invalid);
Console.ReadLine();
}
else
{
if (input.Equals(_cancelChar))
return;
if (DateTime.TryParse(input, out eDatePaid))
{
isValid = true;
}
else
{
Console.WriteLine(invalid);
Console.ReadLine();
}
}
}
}
else
{
eDatePaid = new DateTime(9999, 01, 01);
}
isValid = false;
input = string.Empty;
// Get isOutstanding
while (!isValid)
{
Console.Write("Is this expense outstanding (Y/N): ");
input = Console.ReadLine();
switch (input.ToUpper())
{
case "Y":
eIsOutstanding = true;
isValid = true;
break;
case "N":
eIsOutstanding = false;
isValid = true;
break;
default:
if (input.Equals(_cancelChar))
return;
Console.WriteLine(invalid);
Console.ReadLine();
break;
}
}
ge.Name = eName;
ge.Description = eDesc;
ge.Value = eValue;
ge.DateReceived = eDateRec;
ge.IsPaid = eIsPaid;
ge.DatePaid = eDatePaid;
ge.IsOutstanding = eIsOutstanding;
if (isBill)
{
AddNewBill(em, ge);
}
else
{
AddGeneralExpense addExpense = new AddGeneralExpense(em);
addExpense.InsertValues(ge);
}
}
public void AddToListGeneralExpense(GeneralExpense newGeneralExpense)
{
_lstGeneralExpenses.Add(newGeneralExpense);
}
private static void AddNewBill(ExpenseManager inEM, GeneralExpense ge)
{
ExpenseManager em = inEM;
Bill bill = new Bill();
string bIssuer = string.Empty;
string bRefNo = string.Empty;
string invalid = "\t\tInvalid Value.";
bool isValid = false;
// Get Issuer
while (!isValid)
{
Console.Write("Bill Isssuer: ");
bIssuer = Console.ReadLine();
if (string.IsNullOrEmpty(bIssuer))
{
Console.WriteLine(invalid);
Console.ReadLine();
}
else
{
if (bIssuer.Equals(_cancelChar))
return;
isValid = true;
}
}
isValid = false;
// Get bill reference number
while (!isValid)
{
Console.Write("Bill Reference No: ");
bRefNo = Console.ReadLine();
if (string.IsNullOrEmpty(bRefNo))
{
Console.WriteLine(invalid);
Console.ReadLine();
}
else
{
if (bRefNo.Equals(_cancelChar))
return;
isValid = true;
}
}
bill.Name = ge.Name;
bill.Description = ge.Description;
bill.Value = ge.Value;
bill.DateReceived = ge.DateReceived;
bill.IsPaid = ge.IsPaid;
bill.DatePaid = ge.DatePaid;
bill.IsOutstanding = ge.IsOutstanding;
bill.Issuer = bIssuer;
bill.ReferenceNo = bRefNo;
AddBill addBill = new AddBill(em);
addBill.InsertValues(bill);
}