int main() {
SQLiteWrapper sqlite;
if (sqlite.Open("SQLiteWrapper.db")) {
std::cout << "SQLiteWrapper.db created or opened" << std::endl;
}
else {
std::cout << "couldn't open SQLiteWrapper.db" << std::endl;
}
SQLiteStatement* stmt = sqlite.Statement("insert into foo values (?, ?)");
if (stmt->Bind(0, 3)) {
std::cout << "value 3 successfully bound at pos 0" << std::endl;
}
else {
std::cout << "value 3 NOT successfully bound at pos 0: " << sqlite.LastError() << std::endl;
}
if (stmt->Bind(1, 4)) {
std::cout << "value 4 successfully bound at pos 1" << std::endl;
}
else {
std::cout << "value 4 NOT successfully bound at pos 1:" << sqlite.LastError() << std::endl;
}
// ******************************** Executing 1st time
if (stmt->Execute()) {
std::cout << "statement executed" << std::endl;
}
else {
std::cout << "error executing statement: " << sqlite.LastError() << std::endl;
}
if (stmt->Bind(0, 5)) {
std::cout << "value 5 successfully bound at pos 0" << std::endl;
}
else {
std::cout << "value 5 NOT successfully bound at pos 0" << std::endl;
}
if (stmt->Bind(1, 6)) {
std::cout << "value 6 successfully bound at pos 1" << std::endl;
}
else {
std::cout << "value 6 NOT successfully bound at pos 1" << std::endl;
}
// ******************************** Executing 2nd time
if (stmt->Execute()) {
std::cout << "statement executed" << std::endl;
}
else {
std::cout << "error executing statement: " << sqlite.LastError() << std::endl;
}
return 0;
}
//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);
}
//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);
}
//Return a customer from the database based on email address
Customer *Database::getCustomer(string email) {
// create and open database
SQLiteDatabase *pDatabase = this->connect();
SQLiteStatement *pStmt = this->createStatement(pDatabase);
//Query the database for a customer with this specific email address
pStmt->Sql("SELECT customerId, firstName, lastName, emailAddress, PIN FROM Customer where emailAddress = ?");
pStmt->BindString(1, email);
pStmt->Execute();
//Parse customer attributes from returned query
int customerId = pStmt->GetColumnInt(0);
string firstName = pStmt->GetColumnString(1);
string lastName = pStmt->GetColumnString(2);
string emailAddress = pStmt->GetColumnString(3);
int pin = pStmt->GetColumnInt(4);
pStmt->FreeQuery();
//Build and return a customer object
return new Customer(customerId, firstName, lastName, emailAddress, pin);
}
//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.
}
