//****************Income Statement*******************
public static void IncomeStatement(string tableName)
{
LeapLogDBManager sqlTables = new LeapLogDBManager();
string IncomeStatementName = tableName + "IncomeStatement";
string dbString = @"CREATE TABLE " + IncomeStatementName + "( ID INT IDENTITY(1, 1) NOT NULL, Revenue_Account_Name NVARCHAR(50) NULL, Revenue_Account_Balance FLOAT NULL, Total_Revenue FLOAT NULL, Expense_Account_Name NVARCHAR(50) NULL, Expense_Account_Balance FLOAT NULL, Total_Expense FLOAT NULL," +
"Net_Income FLOAT NULL,PRIMARY KEY CLUSTERED(Id ASC))";
sqlTables.WriteData(dbString);
}
//****************Statement of Owner Equity*******************
public static void StatementOfOwnerEquity(string tableName)
{
LeapLogDBManager sqlTables = new LeapLogDBManager();
string StatementOfOEName = tableName + "StatementOfOE";
string dbString = @"CREATE TABLE " + StatementOfOEName + "( ID INT IDENTITY(1, 1) NOT NULL,Start_Capital FLOAT NULL, Net_Income FLOAT NULL, Total_Withdrawals FLOAT NULL," +
"Final_Capital FLOAT NULL,PRIMARY KEY CLUSTERED(Id ASC))";
sqlTables.WriteData(dbString);
}
//****************Balance sheet*******************
public static void BalanceSheet(string tableName)
{
LeapLogDBManager sqlTables = new LeapLogDBManager();
string BalanceSheetName = tableName + "BalanceSheet";
string dbString = @"CREATE TABLE " + BalanceSheetName + "( ID INT IDENTITY(1, 1) NOT NULL, Asset_Account_Name NVARCHAR(50) NULL, Asset_Account_Balance FLOAT NULL, Total_Assets FLOAT NULL," +
"Liability_Account_Name NVARCHAR(50) NULL, Liability_Account_Balance FLOAT NULL, " +
"Total_Liability FLOAT NULL,PRIMARY KEY CLUSTERED(Id ASC))";
sqlTables.WriteData(dbString);
}
//****************T Accounts *******************
public static void TAccounts(string tableName)
{
LeapLogDBManager sqlTables = new LeapLogDBManager();
string TaccountName = tableName + "Taccount";
string dbString = @"CREATE TABLE " + TaccountName + "( ID INT IDENTITY(1, 1) NOT NULL, Account NVARCHAR(50) NULL, Type NVARCHAR(50) NULL," +
"DebitList NVARCHAR(50) NULL, CreditList NVARCHAR(50) NULL, " +
"TotalDebit FLOAT NULL, TotalCredit FLOAT NULL, Balance FLOAT NULL, PRIMARY KEY CLUSTERED(Id ASC))";
sqlTables.WriteData(dbString);
}
//**************Save All table to DB*******************
// This used to be the save button event but I turned it into a save function.
private bool Save_DB()
{
LeapLogDBManager sqlTables = new LeapLogDBManager();
MessageBoxButton button = MessageBoxButton.OK;
MessageBoxImage icon = MessageBoxImage.Warning;
string tableName = user_Input.Text.Replace(" ", "");
string TaccountName = tableName + "Taccount";
string BalanceSheetName = tableName + "BalanceSheet";
string IncomeStatementName = tableName + "IncomeStatement";
string StatementOfOEName = tableName + "StatementOfOE";
//**********insert into T accounts table************
if (user_Input.Text == "")
{
MessageBox.Show("No table name selected.", "Error", button, icon);
return(false);
}
else
{
try
{
foreach (var i in Database.TEntries)
{
sqlTables.WriteData("INSERT INTO " + TaccountName + " VALUES ('" + i.Account + "','" + i.Type + "','" + string.Join("\n", i.Debit.ToArray()) + "','" + string.Join("\n", i.Credit.ToArray()) + "','" + i.TotalDebit + "','" + i.TotalCredit + "','" + i.Balance + "')");
}
//**********insert into Balance Sheet************
//iterate through list of all asset accounts
for (int i = 0; i < Database.TEntries.Count; i++)
{
string assetAccountName = "";
double assetBalance = 0;
string loeAccountName = "";
double loeBalance = 0;
if (i < Database.BalanceData.assetsList.Count)
{
assetAccountName = Database.BalanceData.assetsList[i].Account;
assetBalance = Database.BalanceData.assetsList[i].Balance;
}
if (i < Database.BalanceData.loeList.Count)
{
loeAccountName = Database.BalanceData.loeList[i].Account;
loeBalance = Database.BalanceData.loeList[i].Balance;
}
sqlTables.WriteData("INSERT INTO " + BalanceSheetName + " VALUES ('" + assetAccountName + "','" + assetBalance + "','" + Database.BalanceData.total_assets + "','" + loeAccountName + "','" + loeBalance + "','" + Database.BalanceData.total_loe + "')");
}
//**********insert into Income Statement************
for (int i = 0; i < Database.TEntries.Count; i++)
{
string revenueAccountName = "";
double revenueBalance = 0;
string expenseAccountName = "";
double expenseBalance = 0;
if (i < Database.IncomeData.revenueList.Count)
{
revenueAccountName = Database.IncomeData.revenueList[i].Account;
revenueBalance = Database.IncomeData.revenueList[i].Balance;
}
if (i < Database.IncomeData.expenseList.Count)
{
expenseAccountName = Database.IncomeData.expenseList[i].Account;
expenseBalance = Database.IncomeData.expenseList[i].Balance;
}
sqlTables.WriteData("INSERT INTO " + IncomeStatementName + " VALUES ('" + revenueAccountName + "','" + revenueBalance + "','" + Database.IncomeData.total_revenue + "','" + expenseAccountName + "','" + expenseBalance + "','" + Database.IncomeData.total_expenses + "','" + Database.IncomeData.net_income + "')");
}
//**********insert into Stetemtent of Owner Equity************
sqlTables.WriteData("INSERT INTO " + StatementOfOEName + " VALUES ('" + Database.SoeData.start_capital + "','" + Database.SoeData.net_income + "','" + Database.SoeData.total_withdrawals + "','" + Database.SoeData.final_capital + "')");
//MessageBox.Show("Table data saved to database.", "Saved", button, icon);
return(true);
}
catch
{
MessageBox.Show("Add a database first before attempting to export.", "Error", button, icon);
return(false);
}
}
}
//Add new entry button
private void addEntryBtn_Click(object sender, RoutedEventArgs e)
{
//new entry is created
Entry tempEntry = new Entry();
//clear any warnings, if necessary
warningTB.Visibility = Visibility.Hidden;
warningAT.Visibility = Visibility.Hidden;
warningAN.Visibility = Visibility.Hidden;
//populates new entry object with user data given
try
{
//process account names to make sure no quotation marks are entered
if (account1TB.Text.Contains("'") || account2TB.Text.Contains("'"))
{
throw new Exception("quotation");
}
// Check for account types.
if (type1CB.SelectedItem == null || type2CB.SelectedItem == null)
{
throw new Exception("null_account_types");
}
// Check for blank account names.
if (account1TB.Text.Replace(" ", "") == string.Empty || account2TB.Text.Replace(" ", "") == string.Empty)
{
throw new Exception("blank_account_names");
}
tempEntry.Account1 = account1TB.Text;
tempEntry.Account2 = " " + account2TB.Text;
tempEntry.Debit = Double.Parse(debitTB.Text);
tempEntry.Credit = Double.Parse(creditTB.Text);
tempEntry.Type1 = type1CB.Text;
tempEntry.Type2 = type2CB.Text;
//add entry into entryGrid
entryGrid.Items.Add(tempEntry);
//separate entry into two t-accounts
List <Entry_tacc> tempAccounts = T_Accounts.get_taccs(tempEntry);
//process t-accounts
T_Accounts.add_taccs(tempAccounts);
//<<-------------inserting data to the database-------------------->>
LeapLogDBManager sqlTables = new LeapLogDBManager();
//<<------- this chooses the table where the data will be added to-------->>
string tableName = user_Input.Text.Replace(" ", "");
//**********insert into journal table************
sqlTables.WriteData("INSERT INTO " + tableName + " VALUES ('" + DateTime.Now + "','" + account1TB.Text + "','" + account2TB.Text + "','" + type1CB.Text + "','" + type2CB.Text + "','" + double.Parse(debitTB.Text) + "','" + double.Parse(creditTB.Text) + "')");
}
catch (Exception error) {
switch (error.Message)
{
case "quotation":
//if incorrect data entered, warning given
//and entry not saved
warningTB.Visibility = Visibility.Visible;
break;
case "null_account_types":
// if the account types are null, display a warning.
warningAT.Visibility = Visibility.Visible;
break;
case "blank_account_names":
// if account names are blank display warning.
warningAN.Visibility = Visibility.Visible;
break;
}
}
//clear textboxes
account1TB.Text = "";
account2TB.Text = "";
debitTB.Text = "";
creditTB.Text = "";
type1CB.SelectedItem = null;
type2CB.SelectedItem = null;
account1TB.Focus();
}
private void enter_button_Click(object sender, RoutedEventArgs e)
{
string messageBoxText = "Database field cannot be null or empty";
string caption = "Wrong Input";
MessageBoxButton button = MessageBoxButton.OK;
MessageBoxImage icon = MessageBoxImage.Warning;
//**********************Check if table name already exists**********************
List <String> list = new List <String>(); // to save the table names
string connetionString = null;
SqlConnection connection;
SqlCommand command;
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet ds = new DataSet();
int counterV = 0;
string sql = null;
connetionString = $@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename={Environment.CurrentDirectory}\Database1.MDF;Integrated Security=True";
sql = "Select DISTINCT(name) FROM sys.Tables";
connection = new SqlConnection(connetionString);
try
{
connection.Open();
command = new SqlCommand(sql, connection);
adapter.SelectCommand = command;
adapter.Fill(ds);
adapter.Dispose();
command.Dispose();
connection.Close();
for (counterV = 0; counterV <= ds.Tables[0].Rows.Count - 1; counterV++)
{
list.Add(ds.Tables[0].Rows[counterV].ItemArray[0].ToString());
}
}
catch (Exception)
{
Console.WriteLine("Can not open connection ! ");
}
//******************end of block of code*********************************
//******************Control if statement******************
if (list.FindIndex(x => x.Equals(user_Input.Text.Trim().Replace(" ", ""), StringComparison.CurrentCultureIgnoreCase)) != -1)
{
MessageBox.Show("Database name already taken.", "Try again", button, icon);
}
else
{
string tableName = user_Input.Text.Replace(" ", "");
// If the tableName is empty quit this function.
if (tableName.Length < 1)
{
MessageBox.Show("No name was given to the table.", "Try again", button, icon);
return;
}
// Fail safe for if the user inputs an number as the first word.
Match match = Regex.Match(tableName[0].ToString(), "[0-9]");
if (match.Success)
{
MessageBox.Show("Letters only as the first character.", "Try again", button, icon);
return;
}
//<<--------this creates the datatable into the database------->>
LeapLogDBManager sqlTables = new LeapLogDBManager();
//string journalName = tableName + "Journal";
string dbString = @"CREATE TABLE " + tableName + "( ID INT IDENTITY(1, 1) NOT NULL,Date DATE NULL, Account_1 NVARCHAR(50) NULL, Account_2 NVARCHAR(50) NULL," +
"Type_1 NVARCHAR(50) NULL, Type_2 NVARCHAR(50) NULL, " +
"Debit MONEY NULL, Credit MONEY NULL,PRIMARY KEY CLUSTERED(Id ASC))";
if (String.IsNullOrEmpty(user_Input.Text) || user_Input.Text == "")
{
MessageBox.Show(messageBoxText, caption, button, icon);
}
else
{
sqlTables.WriteData(dbString);
MessageBox.Show("Tables " + user_Input.Text + " added to Database. ", "Table added", button, icon);
AddDBTable.TAccounts(tableName);
AddDBTable.BalanceSheet(tableName);
AddDBTable.IncomeStatement(tableName);
AddDBTable.StatementOfOwnerEquity(tableName);
}
}
}