#include "stdafx.h"
#include "song.h"

/************************************************************************/
/* Constructor selects song information from the database based on the ID*/
/************************************************************************/

// Default constructor
song::song() {
	// Init DB class
	db = gcnew dbClass();
}

// Parameterized constructor
song::song( int songId )
{
	// Initializes the class database connection.
	db = gcnew dbClass();

	// Creates a blank System::String pointer.
	String ^query = gcnew String("");

	// Builds the query string from the constructor ID.
	query += " SELECT * FROM songs ";
	query += " WHERE ";
	query += " ID = Int(";
	query +=  db->escape( Convert::ToString( songId ) );
	query += ")";

	// Executes the query
	db->openConnection();
	db->select( query );

	if ( db->getReader()->Read() ) {
		// Sets the title of the object
		title = db->getReader()->GetString( db->getReader()->GetOrdinal( "title") );
		// Sets the artist of the object
		artist = db->getReader()->GetString( db->getReader()->GetOrdinal( "artist" ) );
		// Sets the auto-numbering ID of the object
		id = db->getReader()->GetInt32( db->getReader()->GetOrdinal( "ID" ) );
		// Sets the number of plays of the object
		plays = db->getReader()->GetInt32( db->getReader()->GetOrdinal( "plays" ) );
		// Sets the file path to the song of the object
		filePath = db->getReader()->GetString( db->getReader()->GetOrdinal( "filePath" ) );
		db->closeConnection();
	} else {
		MessageBox::Show("Unable to build song class!");
	}
}

/************************************************************************/
/* Selects from the database to see if the given song combination exists.*/
/************************************************************************/
bool song::songExists( String ^title, String ^artist ) {
	// Instantiates the database object.
	dbClass ^db = gcnew dbClass();

	// Creates blank System::String
	String ^query = gcnew String("");

	// The SQL statement, adding in the strings to search the DB.
	query += " SELECT * FROM songs ";
	query += " WHERE ";
	query += " title = '";
	query += db->escape( title );
	query += "' AND artist = '";
	query += db->escape( artist );
	query += "' ";

	// Selects from the database
	db->openConnection();
	db->select( query );

	// Determines whether any rows were returned.
	if( db->getReader()->HasRows ) {
		db->closeConnection();
		return true;
	}
	db->closeConnection();
	return false;
}

/************************************************************************/
/* Adds a song to the database */
/************************************************************************/

void song::addSong( String ^title, String ^artist, String ^filePath ) {
	// Build database connection object.
	dbClass ^db = gcnew dbClass();

	// Create empty System::String.
	String ^query = gcnew String("");

	// Build query for the database.
	query += " INSERT INTO songs ";
	query += " ( ";
	query += " title, ";
	query += " artist, ";
	query += " filePath ";
	query += " ) ";
	query += " VALUES ";
	query += " ( ";
	query += " '" + db->escape( title ) + "', ";
	query += " '" + db->escape( artist ) + "', ";
	query += " '" + db->escape( filePath ) + "' ";
	query += " ) ";

	db->openConnection();
	db->query( query );
	db->closeConnection();
}

/************************************************************************/
/* Returns the total number of songs in the database                    */
/************************************************************************/

int song::totalSongs() {

	// Create empty query string
	String ^query = gcnew String("");

	// Use the count function in SQL to count the rows.
	query += " SELECT COUNT(*) AS r FROM songs ";

	// Create database class
	dbClass ^db = gcnew dbClass();

	// Ping the database for the information
	db->openConnection();
	db->select( query );

	// Retrieve the field from the row, rows set to 0 in case there aren't any.
	int rows = 0;

	// If the db returned rows, we get our field
	if( db->getReader()->HasRows ) {
		if ( db->getReader()->Read() ) {
			rows = db->getReader()->GetInt32( db->getReader()->GetOrdinal("r") );
		}
	}

	// Close DB connection
	db->closeConnection();

	return rows;
}

/************************************************************************/
/* Removes a certain song id from the database.  */
/************************************************************************/

void song::removeSong( int songId ) {
	// Instantiate DB class.
	dbClass ^db = gcnew dbClass();

	// Create empty string.
	String ^query = gcnew String("");

	// Build query
	query += " DELETE FROM songs ";
	query += " WHERE ";
	query += " ID = Int(" + songId + ") ";

	// Issue Query
	db->openConnection();
	db->query( query );
	db->closeConnection();
}

/************************************************************************/
/* Increments the songs play count                                      */
/************************************************************************/

void song::updatePlays() {
	// Increment plays
	plays += 1;
	String ^query = gcnew String("");

	// Build query string
	query += " UPDATE songs SET ";
	query += " plays = Int(" + db->escape( Convert::ToString( plays ) ) + ") ";
	query += " WHERE ";
	query += " ID = Int(" + db->escape( Convert::ToString( id ) ) + ") ";

	// Send query to the database
	db->openConnection();
	db->query( query );
	db->closeConnection();
}

void song::exportXML( string *filepath ) {
	// File Writer
	ofstream outFile;

	// Database class
	dbClass ^db = gcnew dbClass();

	// Open the given file path
	outFile.open( *filepath );

	// Make sure the file is open before we barf info into it.
	if( outFile.is_open() ) {
		// Create empty query string
		String ^query = gcnew String("");

		// Build query
		query += " SELECT * FROM songs ";

		// Open connection and issue query
		db->openConnection();
		db->select( query );

		// Make sure there are songs in the database before we write
		if( db->getReader()->HasRows ) {
			// Create header, then loop through database and populate the file
			outFile << "<CATALOG>" << endl;
			while ( db->getReader()->Read() )
			{
				outFile << "\t<SONG>" << endl;
				outFile << "\t\t<TITLE>" << systemToStdString( db->getReader()->GetString( db->getReader()->GetOrdinal("title") ) ) << "</TITLE>" << endl;
				outFile << "\t\t<ARTIST>" << systemToStdString( db->getReader()->GetString( db->getReader()->GetOrdinal("artist") ) ) << "</ARTIST>" << endl;
				outFile << "\t\t<FILEPATH>" << systemToStdString( db->getReader()->GetString( db->getReader()->GetOrdinal("filepath") ) ) << "</FILEPATH>" << endl;
				outFile << "\t\t<PLAYS>" << systemToStdString( Convert::ToString( db->getReader()->GetInt32( db->getReader()->GetOrdinal("plays") ) ) ) <<  "</PLAYS>" << endl;
				outFile << "\t</SONG>" << endl;
			}
			// write closing node
			outFile << "</CATALOG>" << endl;
		}
	}
	// Close the database connection
	db->closeConnection();

	// Close the file
	outFile.close();
}