void FillLayout()
{
int i = 0;
mainMonthLabel.Text = monthVals[currentMonth].ToString() + " Checkbook Info";
previousTotalTextBox.Text = "$" + previousAmount.ToString("0.00");
for (i=0;i<numTransactions;i++)
{
AddMainTransactions(i);
}
for (i = 0;i<aMnumTransactions;i++)
{
AddAmazonTransaction(i,true,i);
}
float tempTotalAmount = 0.0f;
i = 0;
foreach (KeyValuePair<int, AmazonTransaction> pair in amazonTransList)
{
AmazonTransaction newData = new AmazonTransaction();
newData = pair.Value;
aMbudgetCombo[i].SelectedIndex = GetBudgetIndex(newData.type);//System.Convert.ToInt32(aMpreviousInfo[i,0]);
aMtransactionTextBox[i].Text = newData.description;//aMpreviousInfoText[i];
float tempValue = newData.amount;//aMpreviousInfo[i,1];
tempTotalAmount += tempValue;
aMamountTextBox[i].Text = tempValue.ToString("0.00");
aMtotalTextBox[i].Text = tempTotalAmount.ToString("0.00");
aMkeyListNumText[i].Text = pair.Key.ToString();
i++;
}
ReorderTransactions();
CalculateValues(0);
CalculateValues(1);
}
void aMaddButtonClick(object sender, System.EventArgs e)
{
string tempTransaction = aMtransactionTextBox[aMnumTransactions-1].Text;
float amountVal = System.Convert.ToSingle(aMamountTextBox[aMnumTransactions-1].Text);
if (amountVal != 0.0 && tempTransaction != "")
{
string command = DBConnection.BuildInsertQuery("AmazonTransactions",
null,
tempTransaction,
BudgetInfo.GetBudgetIndexString(aMbudgetCombo[aMnumTransactions-1].Text),
//aMbudgetCombo[aMnumTransactions-1].SelectedIndex.ToString(),
aMamountTextBox[aMnumTransactions-1].Text,
null,
currentMonth.ToString());
DBConnection.DBUpdate(command);
command = "SELECT last_insert_rowid()";
int rowID = 0;
rowID = System.Convert.ToInt32(DBConnection.ReturnTotals(command));
//Add new transaction to Amazon Transaction List
AmazonTransaction newAmazonTrans = new AmazonTransaction();
newAmazonTrans.description = aMtransactionTextBox[aMnumTransactions-1].Text;
newAmazonTrans.amount = System.Convert.ToSingle(aMamountTextBox[aMnumTransactions-1].Text);
newAmazonTrans.type = aMbudgetCombo[aMnumTransactions-1].SelectedIndex;
amazonTransList.Add(rowID, newAmazonTrans);
//amazonListNum++;
AddAmazonTransaction(aMnumTransactions, false,rowID);
aMnumTransactions++;
}
else
{
if (amountVal == 0.0)
{
MessageBox.Show("Amount is not Valid: Please Enter a Valid Amount");
}
else
{
MessageBox.Show("Description is not Valid: Please Enter a Valid Description");
}
}
CalculateValues(1);
}
void ReadInitFiles(int monthVal, bool onStart)
{
#region AccountTransactionDB
//Get Account Transaction Info from database
//SQLiteConnection connector = new SQLiteConnection(dbPath);
//connector.Open();
//SQLiteCommand mycommand = new SQLiteCommand(connector);
string commandValue = "SELECT date,description,category,amount,cleared,id FROM Transactions WHERE Month=";
commandValue += monthVal;
DBConnection.ExecuteQuery(commandValue);
while (DBConnection.QueryStep())
{
addedTrans = new Transaction();
addedTrans.date = DBConnection.QueryReturnInt(0);
addedTrans.type = DBConnection.QueryReturnInt(2);
addedTrans.amount = DBConnection.QueryReturnFloat(3);
addedTrans.verified = DBConnection.QueryReturnBool(4);
addedTrans.description = DBConnection.QueryReturnString(1);
//dbListNum = System.Convert.ToInt32(reader[5]);
dbTransList.Add(DBConnection.QueryReturnInt(5),addedTrans);
dbListNum++;
}
//reader.Close();
if (dbListNum == 0)
{
numTransactions = 1;
}
else
{
numTransactions = dbListNum+1;
}
#endregion
#region AmazonTransactionDB
//Get Amazon Account Transaction Info from database
commandValue = "";
commandValue = "SELECT * FROM AmazonTransactions WHERE Month=";
commandValue += monthVal;
commandValue += " ORDER BY ID";
AmazonTransaction amazonTrans;
DBConnection.ExecuteQuery(commandValue);
while (DBConnection.QueryStep())
{
amazonTrans = new AmazonTransaction();
amazonTrans.type = DBConnection.QueryReturnInt(0);
amazonTrans.description = DBConnection.QueryReturnString(1);
amazonTrans.amount = DBConnection.QueryReturnFloat(2);
amazonTransList.Add(DBConnection.QueryReturnInt(4),amazonTrans);
amazonListNum++;
}
aMnumTransactions = amazonListNum+1;
//Get the monthly start amount
commandValue = "SELECT * FROM StartAmount WHERE Month=";
commandValue += monthVal;
commandValue += " AND TransactionType=0";
DBConnection.ExecuteQuery(commandValue);
while (DBConnection.QueryStep())
{
previousAmount = DBConnection.QueryReturnFloat(1);
}
previousTotalTextBox.Text = previousAmount.ToString("0.00");
#endregion
#region CarOil
commandValue = "SELECT Mileage FROM CarOil WHERE Car='Alan'";
try
{
DBConnection.ExecuteQuery(commandValue);
while (DBConnection.QueryStep())
{
alanCarOilText.Text = DBConnection.QueryReturnString(0);
}
}
catch (System.Data.SQLite.SQLiteException)
{
Console.WriteLine("Table Not Found\n");
}
commandValue = "SELECT Mileage FROM CarOil WHERE Car='Katie'";
try
{
DBConnection.ExecuteQuery(commandValue);
while (DBConnection.QueryStep())
{
katieCarOilText.Text = DBConnection.QueryReturnString(0);
}
}
catch (System.Data.SQLite.SQLiteException)
{
Console.WriteLine("Table Not Found\n");
}
#endregion
}
void aMamountBoxChanged(object sender, System.EventArgs e)
{
System.Windows.Forms.TextBox changedBox = new System.Windows.Forms.TextBox();
changedBox = (System.Windows.Forms.TextBox)sender;
int tempInt = System.Convert.ToInt32(changedBox.Tag);
int keyListNum = System.Convert.ToInt32(aMkeyListNumText[tempInt].Text);
AmazonTransaction newTrans = new AmazonTransaction();
if (amazonTransList.ContainsKey(keyListNum))
{
newTrans = amazonTransList[keyListNum];
if (newTrans.amount != System.Convert.ToSingle(changedBox.Text))
{
amazonTransList.Remove(keyListNum);
newTrans.type = aMbudgetCombo[tempInt].SelectedIndex;
newTrans.description = aMtransactionTextBox[tempInt].Text;
newTrans.amount = System.Convert.ToSingle(aMamountTextBox[tempInt].Text);
amazonTransList.Add(keyListNum, newTrans);
string command = "UPDATE AmazonTransactions SET Amount=";
command += aMamountTextBox[tempInt].Text;
command += " WHERE ID=";
command += keyListNum.ToString();
DBConnection.DBUpdate(command);
ReorderTransactions();
}
}
}
void ReadInitFiles(int monthVal, bool onStart)
{
#region AccountTransactionDB
//Get Account Transaction Info from database
SQLiteConnection connector = new SQLiteConnection(dbPath);
connector.Open();
SQLiteCommand mycommand = new SQLiteCommand(connector);
string commandValue = "SELECT * FROM Transactions WHERE Month=";
commandValue += monthVal;
mycommand.CommandText = commandValue;
SQLiteDataReader reader = mycommand.ExecuteReader();
while (reader.Read())
{
addedTrans = new Transaction();
addedTrans.date = System.Convert.ToInt32(reader[0]);
addedTrans.type = System.Convert.ToInt32(reader[2]);
addedTrans.amount = System.Convert.ToSingle(reader[3]);
addedTrans.verified = System.Convert.ToBoolean(reader[4]);
addedTrans.description = reader[1].ToString();
//dbListNum = System.Convert.ToInt32(reader[5]);
dbTransList.Add(System.Convert.ToInt32(reader[6]),addedTrans);
dbListNum++;
}
reader.Close();
if (dbListNum == 0)
{
numTransactions = 1;
}
else
{
numTransactions = dbListNum+1;
}
#endregion
#region AmazonTransactionDB
//Get Amazon Account Transaction Info from database
commandValue = "";
commandValue = "SELECT * FROM AmazonTransactions WHERE Month=";
commandValue += monthVal;
mycommand.CommandText = commandValue;
reader = mycommand.ExecuteReader();
AmazonTransaction amazonTrans;
while (reader.Read())
{
amazonTrans = new AmazonTransaction();
amazonTrans.type = System.Convert.ToInt32(reader[0]);
amazonTrans.description = reader[1].ToString();
amazonTrans.amount = System.Convert.ToSingle(reader[2]);
amazonTransList.Add(System.Convert.ToInt32(reader[4]),amazonTrans);
amazonListNum++;
}
reader.Close();
aMnumTransactions = amazonListNum+1;
//Get the monthly start amount
commandValue = "SELECT * FROM StartAmount WHERE Month=";
commandValue += monthVal;
commandValue += " AND TransactionType=0";
mycommand.CommandText = commandValue;
reader = mycommand.ExecuteReader();
while (reader.Read())
{
previousAmount = System.Convert.ToSingle(reader[1]);
}
previousTotalTextBox.Text = previousAmount.ToString("0.00");
reader.Close();
#endregion
//Only add the savings transactions on startup
#region SavingsInformation
if (onStart)
{
#region MainSavingsDB
//Get the Start amount for the savings accounts from database
//Get the monthly start amount
int savingsIndexerValue = 0;
for (int i = 0; i < 7; i++)
{
savingsIndexerValue = i + 1;
commandValue = "SELECT * FROM StartAmount WHERE Month=0";
commandValue += " AND TransactionType=";
commandValue += savingsIndexerValue.ToString();
mycommand.CommandText = commandValue;
reader = mycommand.ExecuteReader();
while (reader.Read())
{
MainSavingsInfo[i].StartAmount = System.Convert.ToSingle(reader[1]);
SavingsStartBox[i].Text = MainSavingsInfo[i].StartAmount.ToString("0.00");
}
reader.Close();
}
for (int i = 0; i < 7; i++)
{
commandValue = "SELECT * FROM SavingsTransactions WHERE Type=";
commandValue += i.ToString();
mycommand.CommandText = commandValue;
reader = mycommand.ExecuteReader();
float tempTotalAmount = MainSavingsInfo[i].StartAmount;
while (reader.Read())
{
int savingsType = i;// System.Convert.ToInt32(reader[1]);
float tempAmount = System.Convert.ToSingle(reader[0]);
tempTotalAmount += tempAmount;
MainSavingsInfo[savingsType].AmountValue[MainSavingsInfo[savingsType].Transactions] = tempAmount;
MainSavingsInfo[savingsType].TotalValue[MainSavingsInfo[savingsType].Transactions] = tempTotalAmount;
MainSavingsInfo[savingsType].CurrentTotalAmount = MainSavingsInfo[savingsType].TotalValue[MainSavingsInfo[savingsType].Transactions];
AddSavingsTransaction(savingsType,
MainSavingsInfo[savingsType].AmountValue[MainSavingsInfo[savingsType].Transactions],
MainSavingsInfo[savingsType].TotalValue[MainSavingsInfo[savingsType].Transactions]);
MainSavingsInfo[savingsType].Transactions++;
}
reader.Close();
}
#endregion
#region INGSavingsDB
//Get the Start amount for the savings accounts from database
//Get the monthly start amount
savingsIndexerValue = 0;
for (int i = 0; i < 3; i++)
{
savingsIndexerValue = i + 8;
commandValue = "SELECT * FROM StartAmount WHERE Month=0";
commandValue += " AND TransactionType=";
commandValue += savingsIndexerValue.ToString();
mycommand.CommandText = commandValue;
reader = mycommand.ExecuteReader();
while (reader.Read())
{
INGSavingsInfo[i].StartAmount = System.Convert.ToSingle(reader[1]);
INGSavingsStartBox[i].Text = INGSavingsInfo[i].StartAmount.ToString("0.00");
}
reader.Close();
}
for (int i = 0; i < 3; i++)
{
savingsIndexerValue = i+7;
commandValue = "SELECT * FROM SavingsTransactions WHERE Type=";
commandValue += savingsIndexerValue.ToString();
mycommand.CommandText = commandValue;
reader = mycommand.ExecuteReader();
float tempTotalAmount = INGSavingsInfo[i].StartAmount;
while (reader.Read())
{
int savingsType = i;// System.Convert.ToInt32(reader[1]);
float tempAmount = System.Convert.ToSingle(reader[0]);
tempTotalAmount += tempAmount;
INGSavingsInfo[savingsType].AmountValue[INGSavingsInfo[savingsType].Transactions] = tempAmount;
INGSavingsInfo[savingsType].TotalValue[INGSavingsInfo[savingsType].Transactions] = tempTotalAmount;
INGSavingsInfo[savingsType].CurrentTotalAmount = INGSavingsInfo[savingsType].TotalValue[INGSavingsInfo[savingsType].Transactions];
AddINGSavingsTransaction(savingsType,
INGSavingsInfo[savingsType].AmountValue[INGSavingsInfo[savingsType].Transactions],
INGSavingsInfo[savingsType].TotalValue[INGSavingsInfo[savingsType].Transactions]);
INGSavingsInfo[savingsType].Transactions++;
//AddINGSavingsTransaction(savingsType,INGSavingsInfo[savingsType].AmountValue[INGSavingsInfo[savingsType].Transactions],INGSavingsInfo[savingsType].TotalValue[INGSavingsInfo[savingsType].Transactions]);
}
reader.Close();
}
#endregion
}
#endregion
#region CarOil
commandValue = "SELECT Mileage FROM CarOil WHERE Car='Alan'";
mycommand.CommandText = commandValue;
reader = mycommand.ExecuteReader();
while (reader.Read())
{
alanCarOilText.Text = reader[0].ToString();
}
reader.Close();
commandValue = "SELECT Mileage FROM CarOil WHERE Car='Katie'";
mycommand.CommandText = commandValue;
reader = mycommand.ExecuteReader();
while (reader.Read())
{
katieCarOilText.Text = reader[0].ToString();
}
reader.Close();
#endregion
connector.Close();
}
private void aMaddButtonClick(object sender, System.EventArgs e)
{
string tempTransaction = aMtransactionTextBox[aMnumTransactions-1].Text;
float amountVal = System.Convert.ToSingle(aMamountTextBox[aMnumTransactions-1].Text);
if (amountVal != 0.0 && tempTransaction != "")
{
SQLiteConnection connector = new SQLiteConnection(dbPath);
connector.Open();
SQLiteCommand mycommand = new SQLiteCommand(connector);
string command = "";
command = "INSERT INTO AmazonTransactions (Category,Description,Amount,Month) VALUES (";
command += aMbudgetCombo[aMnumTransactions-1].SelectedIndex.ToString() + ",'";
if (tempTransaction.Contains("'"))
{
tempTransaction = tempTransaction.Replace("'","''");// = Replace(lsLastName, "'", "''");
}
command += tempTransaction + "',";
command += aMamountTextBox[aMnumTransactions-1].Text + ",";
command += currentMonth.ToString();
command += ");";
mycommand.CommandText = command;
mycommand.ExecuteScalar();
command = "SELECT last_insert_rowid()";
mycommand.CommandText = command;
SQLiteDataReader reader = mycommand.ExecuteReader();
int rowID = 0;
while (reader.Read())
{
rowID = System.Convert.ToInt32(reader[0]);
}
connector.Close();
AmazonTransaction newAmazonTrans = new AmazonTransaction();
newAmazonTrans.description = aMtransactionTextBox[aMnumTransactions-1].Text;
newAmazonTrans.amount = System.Convert.ToSingle(aMamountTextBox[aMnumTransactions-1].Text);
newAmazonTrans.type = aMbudgetCombo[aMnumTransactions-1].SelectedIndex;
amazonTransList.Add(rowID, newAmazonTrans);
//amazonListNum++;
AddAmazonTransaction(aMnumTransactions, false,rowID);
aMnumTransactions++;
}
else
{
if (amountVal == 0.0)
{
MessageBox.Show("Amount is not Valid: Please Enter a Valid Amount");
}
else
{
MessageBox.Show("Description is not Valid: Please Enter a Valid Description");
}
}
}
private void aMdescriptionBoxChanged(object sender, System.EventArgs e)
{
System.Windows.Forms.TextBox changedBox = new System.Windows.Forms.TextBox();
changedBox = (System.Windows.Forms.TextBox)sender;
int tempInt = System.Convert.ToInt32(changedBox.Tag);
int keyListNum = System.Convert.ToInt32(aMkeyListNumText[tempInt].Text);
AmazonTransaction newTrans = new AmazonTransaction();
if (amazonTransList.ContainsKey(keyListNum))
{
newTrans = amazonTransList[keyListNum];
if (String.Compare(newTrans.description,changedBox.Text) != 0)
{
amazonTransList.Remove(keyListNum);
newTrans.type = aMbudgetCombo[tempInt].SelectedIndex;
newTrans.description = aMtransactionTextBox[tempInt].Text;
newTrans.amount = System.Convert.ToSingle(aMamountTextBox[tempInt].Text);
amazonTransList.Add(keyListNum,newTrans);
string command = "UPDATE AmazonTransactions SET Description='";
string tempTransaction = aMtransactionTextBox[tempInt].Text;
if (tempTransaction.Contains("'"))
{
tempTransaction = tempTransaction.Replace("'","''");
}
command += tempTransaction;
command += "' WHERE ID=";
command += keyListNum.ToString();
ExecuteQuery(command);
//ReorderTransactions();
}
}
}
void ReorderTransactions()
{
calculationTimer.Enabled = false;
int transCounter = 0;
int dateCounter = 1;
while (transCounter < numTransactions)
{
foreach (KeyValuePair<int,Transaction> pair in dbTransList)
{
Transaction tempTransaction = new Transaction();
tempTransaction = (Transaction)pair.Value;
int tempDate = tempTransaction.date;
if (tempTransaction.date == dateCounter)
{
//float tempValue = previousInfo[i,0];
listNumTextBox[transCounter].Text = pair.Key.ToString();
listNumTextBox[transCounter].Tag = transCounter;
dateTextBox[transCounter].Text = tempTransaction.date.ToString();//previousInfo[i,0].ToString();
dateTextBox[transCounter].Tag = transCounter;
//tempValue = previousInfo[i,1];
budgetCombo[transCounter].SelectedIndex = tempTransaction.type;//System.Convert.ToInt32(tempValue);
budgetCombo[transCounter].Tag = transCounter;
transactionTextBox[transCounter].Text = tempTransaction.description;//previousInfoText[i];
transactionTextBox[transCounter].Tag = transCounter;
//tempValue = previousInfo[i,2];
amountTextBox[transCounter].Text = tempTransaction.amount.ToString("0.00");//previousInfo[i,2].ToString("0.00");
amountTextBox[transCounter].Tag = transCounter;
clearedCB[transCounter].Checked = tempTransaction.verified;//System.Convert.ToBoolean(previousInfo[i,3]);
clearedCB[transCounter].Tag = transCounter;
transCounter++;
}
}
dateCounter++;
if (dateCounter > 40)
{
transCounter = numTransactions+1;
}
}
transCounter = 0;
while (transCounter < aMnumTransactions-1)
{
foreach (KeyValuePair<int,AmazonTransaction> pair in amazonTransList)
{
AmazonTransaction tempTransaction = new AmazonTransaction();
tempTransaction = (AmazonTransaction)pair.Value;
aMkeyListNumText[transCounter].Text = pair.Key.ToString();
aMkeyListNumText[transCounter].Tag = transCounter;
aMbudgetCombo[transCounter].SelectedIndex = tempTransaction.type;//System.Convert.ToInt32(tempValue);
aMbudgetCombo[transCounter].Tag = transCounter;
aMtransactionTextBox[transCounter].Text = tempTransaction.description;//previousInfoText[i];
aMtransactionTextBox[transCounter].Tag = transCounter;
aMamountTextBox[transCounter].Text = tempTransaction.amount.ToString("0.00");//previousInfo[i,2].ToString("0.00");
aMamountTextBox[transCounter].Tag = transCounter;
transCounter++;
}
}
calculationTimer.Enabled = true;
}