mimirNBAcrappy.txt

//Program: menu for NBACONFERENCES.bitand
//author: Brian Chamberlain
//last update: 12.04.2021
//goal: main menu to search and insert into NBACONFERENCES.bd
// at least have options for viewing the teams in a division; (You can provide
//menu options that allow the user to choose the division). Adding a new team; 
//Adding a player; Viewing the roster for a team; Viewing game information; 
//Adding game information.
/*
void rollback(sqlite3* db)
{
    std::string query = "rollback;";
    sqlite3_exec(db, query.c_str(), NULL, NULL, NULL);
    std::cout << "Transaction rolled back..." << std::endl;
}

void commit(sqlite3* db)
{
    std::string query = "commit;";
    char * err;
    int rc = sqlite3_exec(db, query.c_str(), NULL, NULL, &err);
    if(rc != SQLITE_OK)
    {
        std::cout << "Error committing transaction: " << err << std::endl;
        sqlite3_free(err);
        rollback(db);
        return;
    }
    std::cout << "transaction committed..." << std::endl;
}*/


#include <iostream>
#include <string>
#include <iomanip>
#include "sqlite3.h"
#include <climits>


using namespace std;

int mainMenu();
void printMainMenu();
void viewConference(sqlite3 *);
void viewCustomer(sqlite3 *);
void	displayRoster(sqlite3* db);
void AddaTeam(sqlite3 *db);
void AddaPlayer(sqlite3 *db);
void AddaGame(sqlite3 *db);
void ViewaGame(sqlite3 *db);


int main() {
    int choice;

	sqlite3 *mydb;

	int rc; 

	rc = sqlite3_open_v2("NBDProjectDB.db", &mydb, SQLITE_OPEN_READWRITE, NULL);
	if(rc != SQLITE_OK){
		//if the database cannot be opened
		cout << "Error in connection: " << sqlite3_errmsg(mydb);
		return 1;
	}
	
	cout << "Welcome to NBA CONFERENCES Database by ME!" << endl;
	choice = mainMenu();
	
	while (true)
	{
		switch (choice) 
		{
			case 1: viewConference(mydb); break;
			case 2: AddaTeam(mydb); break;//not working
			case 3: displayRoster(mydb); break;
			case 4: AddaPlayer(mydb); break;
			case 5: AddaGame(mydb); break;
			case 6: ViewaGame(mydb); break; 
			case -1: 
			{
				//don't forget to close.
				sqlite3_close(mydb);
				return 0;
			}
			default: cout << "That is not a valid choice." << endl;
		}
		cout << "\n\n";
		choice = mainMenu();
	}


}
	


void printMainMenu() 
{
	cout << "Please choose an option (enter -1 to quit):  " << endl;
	cout << "1. View a full Conference team list" << endl;
	cout << "2. Add a Team" << endl;
	cout << "3. View a teams Roster" << endl; 
	cout << "4. Add a Player" << endl;
	cout << "5. Add a Game" <<endl;
	cout << "6. View a Game" <<endl;
	cout << "Enter Choice: ";
}

int mainMenu()
{
	int choice = 0;
	
	printMainMenu();
	cin >> choice;
	while ((!cin || choice < 1 || choice > 24) && choice  != -1)
	{		
		if (!cin)
		{
			cin.clear();
			cin.ignore(INT_MAX,'\n');
		}
		cout << "That is not a valid choice." << endl << endl;
		printMainMenu();
		cin >> choice;
	} 
	return choice;
}

/*	
SELECT TEAMS.Name, TEAMS.citystate, DIVISION.Name as Division, DIVISION.Conference
FROM TEAMS JOIN DIVISION ON TEAMS.Name = DIVISION.TeamName;*/
  


void viewConference(sqlite3 *db)
{	//displays teams table
     int columnCount;
     int choice = 0;
     string conference = "NONE";
     cout << "Eastern = 1 or Western = 2" << endl;
     cin >>choice;
     
     if(choice == 1){conference = "\"Eastern\"";}
     
     else if (choice == 2){conference = "\"Western\"";}
     //else{cin.clear();cin.ignore(INT_MAX,'\n');
//		cout << "That is not a valid choice." << endl << endl;
//	     viewInvoice(db);
//		cin >> choice;
//	} 

	string query = "SELECT TEAMS.Name as Team, TEAMS.citystate, DIVISION.Name as Division, DIVISION.Conference ";
	query += "FROM TEAMS JOIN DIVISION ON TEAMS.Name = DIVISION.TeamName ";
     query += "WHERE DIVISION.Conference = ";
     query += conference + ";";

	sqlite3_stmt *pRes, *pRes2;
	string m_strLastError;
	string query2;
	string TeamName;//teamname
	string citystate, playerNum, PlayerName, GameCount;//
	string division;
	if (sqlite3_prepare_v2(db, query.c_str(), -1, &pRes, NULL) != SQLITE_OK)
	{
		m_strLastError = sqlite3_errmsg(db);
		sqlite3_finalize(pRes);
		cout << "There was an error: viewinvoice " << m_strLastError << endl;
		return;
	}
	else {	
	     int i = 1;
	     cout << left;
		while (sqlite3_step(pRes) == SQLITE_ROW)
		{   
			cout << i << ". " << sqlite3_column_text(pRes, 0);
			cout << endl;
			i++;
		}}
}
	//end viewInvoicefunction
	
void displayRoster(sqlite3 *db){
     
//	viewConference(db);
//displays teams table
    int columnCount;
     int choice = 0;
     string conference = "NONE";
     cout << "Eastern = 1 or Western = 2 : " ;
     cin.clear();
     cin.ignore(INT_MAX, '\n');
     cin >>choice;
     
     if(choice == 1){conference = "\"Eastern\"";}
     
     else if (choice == 2){conference = "\"Western\"";}
     //else{cin.clear();cin.ignore(INT_MAX,'\n');
//		cout << "That is not a valid choice." << endl << endl;
//	     viewInvoice(db);
//		cin >> choice;
//	} 

	string query = "SELECT TEAMS.Name as Team, TEAMS.citystate, DIVISION.Name as Division, DIVISION.Conference ";
	query += "FROM TEAMS JOIN DIVISION ON TEAMS.Name = DIVISION.TeamName ";
     query += "WHERE DIVISION.Conference = ";
     query += conference + ";";

	sqlite3_stmt *pRes, *pRes2, *pRes3;
	string m_strLastError;
	string query2;
	string TeamName;//teamname
	string citystate, playerNum, PlayerName, GameCount;//
	string division;
	
	 int i = 1;
	if (sqlite3_prepare_v2(db, query.c_str(), -1, &pRes, NULL) != SQLITE_OK)
	{
		m_strLastError = sqlite3_errmsg(db);
		sqlite3_finalize(pRes);
		cout << "There was an error: viewrostinvoice " << m_strLastError << endl;
		return;
	}
	else {	
	    
	     cout << left;
		while (sqlite3_step(pRes) == SQLITE_ROW)
		{   
			cout << i << ". " << sqlite3_column_text(pRes, 0);
			cout << endl;
			i++;
			
				
			//	cout << TeamName;
		}
	}
  	
	      columnCount = sqlite3_column_count(pRes);
	
		cout << "Please choose the Team you want to see: ";
	//cin.clear();cin.ignore(INT_MAX, '\n');
	//	cin >> choice;
		
		cout << left;
		
	
		
	while(!(cin>>choice) || choice < 1 || choice > (i - 1)){
			if(!cin){
				cin.clear();
				cin.ignore(INT_MAX, '\n');
			}
			cout << "That is not a valid choice! Try again6!" << endl;
		}
		

		sqlite3_reset(pRes);
		for (int i = 0; i < choice; i++)
			sqlite3_step(pRes);
		TeamName = reinterpret_cast<const char*>(sqlite3_column_text(pRes,0));
	

query = "SELECT * FROM PLAYER JOIN TEAMS ON TEAMS.Name";
	query+= " = ";
	query+= "PLAYER.Team ";
     query += "WHERE TEAMS.Name = \'";
     query += TeamName;
     query += "\' ;";
cout << query<<endl;
	sqlite3_reset(pRes);
	if (sqlite3_prepare_v2(db, query.c_str(), -1, &pRes, NULL) != SQLITE_OK)
	{
		m_strLastError = sqlite3_errmsg(db);
		sqlite3_finalize(pRes);
		cout << "There was an error: viewiteam " << m_strLastError << endl;
		return;
	}
	
	
	else {	
	          int i = 1;
	     cout << left;
	     	cout << TeamName<<endl;
	     
		while (sqlite3_step(pRes) == SQLITE_ROW )
//	for(int i =0; i<3; i++)
	{	 cout << i <<". ";

			cout << "#"<< sqlite3_column_text(pRes, 1);
			cout <<  "  " << sqlite3_column_text(pRes, 0);
			
			cout << endl;
			i++;
	}	
	     
	}
	     

	     
	}

	     
	     
   
	
	

	
     


		
void AddaTeam(sqlite3 *db){
     

     int CoachID, GameCount;


	sqlite3_stmt *pRes, *pRes2, *pRes3;
	string m_strLastError;
	string query2;
	string TeamName;//teamname
	string city, state, citystate, arena, mascot;//
	string division;
      string conference = "NONE";
     cout << "Eastern = 1 or Western = 2 : " ;
     cin.clear();
     cin.ignore(INT_MAX, '\n');
     int choice = 0;
     cin >>choice;
     
     if(choice == 1){conference = "\"Eastern\"";}
     
     else if (choice == 2){conference = "\"Western\"";}
     cout << "Insert Division:";
     cin >> division;
     
     cout<< "Insert team name: ";
     cin>>TeamName;
   
     	//query2 = "Insert into TEAMS values (";
     //	query2 += TeamName.c_str();
     //	query2= ", ";
     	
     cout << "\nInsert CoachID (must exist(1-7), is an INTEGER): ";
    cin>>CoachID;
               while(!cin){cout<<"Please enter and integer: "<<endl; cin.clear();cin.ignore(INT_MAX,'\n'); cin>>CoachID;}
    
     //	query2 += to_string( CoachID);
	//	query2= ", ";
     
	cout << "\nInsert City: "; 
	cin >> city;
	cout << "and State: ";\
	cin >> state;
	//build citystate
	citystate = city;
	citystate+= " ";
	citystate+= state;
	
	cout << "\nInsert arena name: ";
	cin >> arena;
	
		cout << "\nInsert Mascot name: ";
     cin >> mascot;
	
	cout << "\nInsert Game Count(must be INTEGER): ";
		cin>> GameCount;
     	     while(!cin){cout<<"Please enter and integer: "<<endl; cin.clear();cin.ignore(INT_MAX,'\n'); cin>>GameCount;}
	
	//	cout <<query2<<endl;
		
		
//Insert into TEAMS values("TeamName",'CoachID',"citystate","arena",'gamecount')		
	query2 = "Insert into TEAMS values(\"";
	query2+= TeamName;
	query2 += "\"";
	
	query2 += ",\'";
	query2 += to_string(CoachID);
	query2+= "\',\"";
	query2+= citystate;
	query2+= "\",\"";
	query2+= arena;
	query2+="\",\"";
	query2+= mascot;
	query2+="\",\'";
query2+= to_string(GameCount);
query2+= "\');";

query2 = "Insert into DIVISION values(";
	query2+= conference;
//	query2 += "\"";
	
	query2 += ",\"";
	query2 += division;
	query2+= "\",\"";
	query2+= TeamName;
query2+= "\");";

//cout<<"'TEMCOACH:"<<query2<<endl;
	
	

		if (sqlite3_prepare_v2(db, query2.c_str(), -1, &pRes2, NULL) != SQLITE_OK)
		{
			m_strLastError = sqlite3_errmsg(db);
			sqlite3_finalize(pRes2);
			cout << "There was an error: insertteam" << m_strLastError << endl;
			return;
		}
		else
		{
	cout << "Inserted: "<< TeamName << " Team" <<endl;

		     
		}
}



		
void AddaPlayer(sqlite3 *db){
     

     int CoachID, GameCount;


	sqlite3_stmt *pRes, *pRes2, *pRes3;
	string m_strLastError;
	string query2;
	string TeamName;//teamname
	string city, state, citystate, arena, mascot;//
	string division;
     
     cout<< "Insert team name: ";
     cin>>TeamName;
   
     	//query2 = "Insert into TEAMS values (";
     //	query2 += TeamName.c_str();
     //	query2= ", ";
     	
     cout << "\nInsert Player #: (is an INTEGER): ";
    cin>> CoachID;
               while(!cin){cout<<"Please enter and integer: "<<endl; cin.clear();cin.ignore(INT_MAX,'\n'); cin>>CoachID;}
    
     //	query2 += to_string( CoachID);
	//	query2= ", ";
     
	cout << "\nInsert First Name"; 
	cin >> city;
	cout << "and Last Name: ";\
	cin >> state;
	//build citystate
	citystate = city;
	citystate+= " ";
	citystate+= state;

		
//Insert into TEAMS values("TeamName",'CoachID',"citystate","arena",'gamecount')		
	query2 = "Insert into PLAYER values(\"";
	query2+= citystate;
	query2 += "\"";
	
	query2 += ",\'";
	query2 += to_string(CoachID);
	
	query2+= "\',\"";
	query2+= TeamName;
	query2+= "\"";

query2+= ");";

//cout<<"'TEMCOACH:"<<query2<<endl;
	
	

		if (sqlite3_prepare_v2(db, query2.c_str(), -1, &pRes2, NULL) != SQLITE_OK)
		{
			m_strLastError = sqlite3_errmsg(db);
			sqlite3_finalize(pRes2);
			cout << "There was an error: " << m_strLastError << endl;
			return;
		}
		else
		{
	cout << "Inserted: "<< TeamName << citystate<< to_string(CoachID)<<endl;

		     
		}
}


void AddaGame(sqlite3 *db){
     

     int CoachID, GameCount;


	sqlite3_stmt *pRes, *pRes2, *pRes3;
	string m_strLastError;
	string query2;
	string TeamName;//teamname
	string city, state, citystate, arena, mascot;//
	string division;
     
     cout<< "Insert Home team name: ";
     cin>>TeamName;//acts as home team name temp
      cout<< "Insert Away team name: ";
     cin>>mascot;//acts as away team name temp
   
     	//query2 = "Insert into TEAMS values (";
     //	query2 += TeamName.c_str();
     //	query2= ", ";
     	
     cout << "\nInsert Date (in form mm-dd-yyy): ";
    cin>> arena;//acts as date temp
      cout << "\nInsert Final Score: ";
    cin>> citystate;//acts as score temp
    cout << " insert Arena Played: ";
    cin >> state;//acts as arena temp
    
//use more direct insert(below) so primary key autoincrements, remove or apply teamsTEAMS alias
//insert into TEAMS(soulumn_name,col_nam,col_int) values ("Brian", "Chamber", '3')
		
//Insert into TEAMS values("TeamName",'CoachID',"citystate","arena",'gamecount')		
	query2 = "Insert into GAME values(\'";
	query2 += "999\',\"";//hard coded cus i dunno how to check last row #
	query2+= arena;
	query2+= "\",\"";
	query2+= citystate;
	query2 += "\"";
	
	query2 += ",\"";
	query2 += TeamName;
	
	query2+= "\",\"";
	query2+= mascot;
	query2+= "\",\"";
	query2+= state;
	query2+=" \"";

query2+= ");";

cout<<"'TEMCOACH:"<<query2<<endl;
	
	

		if (sqlite3_prepare_v2(db, query2.c_str(), -1, &pRes2, NULL) != SQLITE_OK)
		{
			m_strLastError = sqlite3_errmsg(db);
			sqlite3_finalize(pRes2);
			cout << "There was an error: " << m_strLastError << endl;
			return;
		}
		else
		{
	cout << "Inserted: "<< TeamName << citystate<< to_string(CoachID)<<endl;

		     
		}
}



void ViewaGame(sqlite3 *db){
     
     


	sqlite3_stmt *pRes, *pRes2, *pRes3;
	string m_strLastError;
	string query2;
	string TeamName;//teamname
	string city, state, citystate, arena, mascot;//
	string division;
     
     	
int choice;
string query = "SELECT * FROM GAME; ";
  //cout << query<<endl;

   
   
   	if (sqlite3_prepare_v2(db, query.c_str(), -1, &pRes, NULL) != SQLITE_OK)
	{
		m_strLastError = sqlite3_errmsg(db);
		sqlite3_finalize(pRes);
		cout << "There was an error: viewgame " << m_strLastError << endl;
		return;
	}
	else {	
	     int i = 1;
	     cout << left;
		while (sqlite3_step(pRes) == SQLITE_ROW)
		{   cout << "ID: " << (sqlite3_column_text(pRes,0));
     cout <<  "  Date: " << (sqlite3_column_text(pRes,1));
     cout <<  "  Finale Score: " << (sqlite3_column_text(pRes,2));
     cout <<  "\n  Home Team: " << (sqlite3_column_text(pRes,3));
     cout <<  "  Away Team:" << (sqlite3_column_text(pRes,4));
     cout <<  "  Arena: " << (sqlite3_column_text(pRes,5));
     cout <<  endl;
		
			i++;
		}}
//end display games
      cout << "\nSelect a game"<<endl;
   cin >> query2;
  query = "SELECT * FROM GAME ";
  query += "WHERE GameID = \'";
  query += query2;
  query += "\' ;";
  
 // cout << query<<endl;
  // cout << "Select a game"<<endl;
  // cin >> query2;
   
   
   	if (sqlite3_prepare_v2(db, query.c_str(), -1, &pRes, NULL) != SQLITE_OK)
	{
		m_strLastError = sqlite3_errmsg(db);
		sqlite3_finalize(pRes);
		cout << "There was an error: viewgame " << m_strLastError << endl;
		return;
	}
	else {	
	     int i = 1;
	     cout << left;
		while (sqlite3_step(pRes) == SQLITE_ROW)
		{   cout << "ID: " << (sqlite3_column_text(pRes,0));
     cout <<  "  Date: " << (sqlite3_column_text(pRes,1));
     cout <<  "  Finale Score: " << (sqlite3_column_text(pRes,2));
     cout <<  "\n  Home Team: " << (sqlite3_column_text(pRes,3));
     cout <<  "  Away Team:" << (sqlite3_column_text(pRes,4));
     cout <<  "  Arena: " << (sqlite3_column_text(pRes,5));
     cout <<  endl;
		
			i++;
		}}
}//end display games