//Updates account status to closed Account *Database::CloseAccount(int customerId,string time) { // create and open database SQLiteDatabase *pDatabase = this->connect(); SQLiteStatement *pStmt = this->createStatement(pDatabase); Account *acct = getAccount(customerId); int acctId = acct->getAccountNumber(); //Close account by accountId4 pStmt->Sql("UPDATE Account SET accountStatus = ? where accountId = ?"); pStmt->BindString(1, "Closed"); pStmt->BindInt(2, acctId); pStmt->Execute(); pStmt->FreeQuery(); //Close out Account, set Balance to Zero. Database::createTransaction(acctId, acct->getAccountBalance(), "Withdraw", time, "Account Closed"); Database::updateAccount(0, acctId); //Uses getAccount to return the updated customer object return getAccount(customerId); }
//Create a new customer and return that object to the calling function Customer *Database::createCustomer(string firstName, string lastName, int pin, string emailAddress) { // create a record in the database for the customer // create and open database SQLiteDatabase *pDatabase = this->connect(); SQLiteStatement *pStmt = this->createStatement(pDatabase); //Insert query pStmt->Sql("INSERT INTO Customer (lastName, firstName, emailAddress, PIN) VALUES(?, ?, ?, ?);"); pStmt->BindString(1, lastName); pStmt->BindString(2, firstName); pStmt->BindString(3, emailAddress); pStmt->BindInt(4, pin); // executes the INSERT statement and cleans-up automatically pStmt->ExecuteAndFree(); // get the customer ID (the last row id) pStmt->Sql("SELECT last_insert_rowid();"); pStmt->Execute(); int customerId = pStmt->GetColumnInt(0); // get the int value at the zeroth column pStmt->FreeQuery(); // return the customer to the calling function return new Customer(customerId, firstName, lastName, emailAddress, pin); }
// Account: Create, Update, Retrieve Account *Database::createAccount(int customerId, double balance,string accountStatus, string accountType){ // create and open database SQLiteDatabase *pDatabase = this->connect(); SQLiteStatement *pStmt = this->createStatement(pDatabase); pStmt->Sql("INSERT INTO Account (customerId, balance, accountStatus, accountType) VALUES(?, ?, ?, ?);"); pStmt->BindInt(1, customerId); pStmt->BindDouble(2, balance); pStmt->BindString(3, accountStatus); pStmt->BindString(4, accountType); // executes the INSERT statement and cleans-up automatically pStmt->ExecuteAndFree(); // get the customer ID (the last row id) pStmt->Sql("SELECT last_insert_rowid();"); pStmt->Execute(); int accountId = pStmt->GetColumnInt(0); // get the int value at the zeroth column pStmt->FreeQuery(); // return the account to the calling function Database *dbObject = new Database(); //Write this transaction to the database dbObject->createTransaction(accountId, balance, "Deposit", "Time", "Opening Account Deposit"); //Build and return an account object to the calling function return new Account(accountId, customerId, balance,accountStatus, accountType); }
//Update a customer account (ie deposit, withdrawal) Account *Database::updateAccount(double balance, int accountId){ // create and open database SQLiteDatabase *pDatabase = this->connect(); SQLiteStatement *pStmt = this->createStatement(pDatabase); //Update account balance by accountId pStmt->Sql("UPDATE Account SET balance = ? where accountId = ?"); pStmt->BindDouble(1, balance); pStmt->BindInt(2, accountId); pStmt->Execute(); pStmt->FreeQuery(); //Use the getAccount function to return the updated account object return getAccount(accountId); }
Transaction *Database::createTransaction(int accountId, double amount, string tType, string tDate, string tDescript){ //Open database SQLiteDatabase *pDatabase = this->connect(); SQLiteStatement *pStmt = this->createStatement(pDatabase); //Build new transaction query pStmt->Sql("INSERT INTO Transactions1 (accountId, amount, transactionType, transactionDate, transactionDescription) VALUES(?, ?, ?, ?, ?);"); pStmt->BindInt(1, accountId); pStmt->BindDouble(2, amount); pStmt->BindString(3, tType); pStmt->BindString(4, tDate); pStmt->BindString(5, tDescript); // executes the INSERT statement and cleans-up automatically pStmt->ExecuteAndFree(); return new Transaction(); }
//Polls the database for this customer by ID and returns Customer *Database::getCustomer(int customerId) { // create and open database SQLiteDatabase *pDatabase = this->connect(); SQLiteStatement *pStmt = this->createStatement(pDatabase); pStmt->Sql("SELECT firstName, lastName, emailAddress, PIN FROM Customer where customerId = ?"); pStmt->BindInt(1, customerId); pStmt->Execute(); //Build the customer attributes from the returned query string lastName = pStmt->GetColumnString(0); string firstName = pStmt->GetColumnString(1); string emailAddress = pStmt->GetColumnString(2); int pin = pStmt->GetColumnInt(3); pStmt->FreeQuery(); //Build and return a customer object return new Customer(customerId, firstName, lastName, emailAddress, pin); }
//Get a customer's account by customerId Account *Database::getAccount(int custId){ // create and open database SQLiteDatabase *pDatabase = this->connect(); SQLiteStatement *pStmt = this->createStatement(pDatabase); //Query by CustomerId pStmt->Sql("SELECT * FROM Account where customerId = ?"); pStmt->BindInt(1, custId); pStmt->Execute(); //Account Table: accountId, customerId, balance, accountType int accountId = pStmt->GetColumnInt(0); int customerId = pStmt->GetColumnInt(1); double balance = pStmt->GetColumnDouble(2); string accountStatus = pStmt->GetColumnString(3); string accountType = pStmt->GetColumnString(4); pStmt->FreeQuery(); // return the account to the calling function return new Account(accountId, customerId, balance, accountStatus,accountType); }
//Returns transaction log void Database::getTransactions(int accountId){ //Opens database SQLiteDatabase *pDatabase = this->connect(); SQLiteStatement *pStmt = this->createStatement(pDatabase); //Holds the number of transactions for this account int transactionCount = 0; vector<int> transactionIds; //Query to poll the database for all transactions by accountId pStmt->Sql("Select * FROM Transactions1 where accountId = ?"); pStmt->BindInt(1, accountId); pStmt->Execute(); //Controls the loop to add all transactions to the vector bool moreRows=true; //While there are additional rows of transactions, add them to the transaction log while (moreRows = pStmt->FetchRow()) { // if transactioncount+1=8, then you got to clear the screen. Want 8 transactions at a time. if (transactionCount == 0 || transactionCount % 8 == 0) { //if no transactions have been included, place zero. if (transactionCount == 0) { system("cls"); cout << "Id \t" << "Amount \t" << "Transaction Type \t" << "Transaction Date \t" << endl << endl; } //Control to Display only 10 transactions at a time. if (transactionCount % 8 == 0 && transactionCount > 0) { int choice = 0; if (moreRows == false) { cout << endl << endl << "1) View Specific Transaction?" << endl; cout << endl << endl << "2) Exit Transaction Log?" << endl << endl; cout << "Select Option Number: "; cin >> choice; if (choice == 1) { int selection; cout << "View Transaction Number: "; cin >> selection; //view specific selection getTransaction(transactionIds[selection - 1]); //return to base account screen. pStmt->FreeQuery(); return; } } if (moreRows == true) { cout << endl << "1) View Specific Transaction?"; cout << endl << "2) View More Transactions?"; cout << endl << "3) Exit Transaction Log?" << endl << endl; cout << "Select Option Number: "; cin >> choice; if (choice == 1) { int selection; cout << "View Transaction Number: "; cin >> selection; getTransaction(transactionIds[selection - 1]); pStmt->FreeQuery(); return;//leave loop. }