<?
 /* DB CONNECT FUNCTIONS */

	//CONNECT AND OPEN DB CONNECTION
 	function connect_and_open($user, $db, $pass) {
		$ERR_DB_NOCONNECT	= "ERROR: DB_01 - Cannot connect to the database. Reason: ";
		$ERR_DB_NOSELECT	= "ERROR: DB_02 - Cannot select the database. Reason: ";


			//initialise the db connection for this session
			$link = mysql_connect("localhost", $user, $pass) or
				die($this->ERR_DB_NOCONNECT.mysql_error());

			mysql_select_db($db, $link) or
				die($this->ERR_DB_NOSELECT.mysql_error());

			return $link;
	}


	// EXECUTE A SPECIFIED SQL QUERY
	function run_query($query, $link)
	{
		$result_set = mysql_query($query, $link);
			if (!$result_set) {
    			$message  = 'Invalid query: ' . mysql_error() . "\n";
    			$message .= 'Whole query: ' . $query;
    			die($message);
			}
			return $result_set;
	}


	//DISCONNECT AND CLOSE DB CONNECTION
	function dcon_and_close($link)
	{
		mysql_close($link);
	}

	function drop_tables($link)
	{
		$sql = sprintf("DROP TABLE PaperTitle");
		if(run_query($sql, $link))
			echo $sql . " --- TABLE DELETED: <br />";
		else
			echo "Error Dropping Table: PaperTitle <br />";

		$sql = sprintf("DROP TABLE PaperContact");
		if(run_query($sql, $link))
			echo $sql . " --- TABLE DELETED: <br />";
		else
			echo "Error Dropping Table: PaperContact <br />";

		$sql = sprintf("DROP TABLE PaperDetails");
		if(run_query($sql, $link))
			echo $sql . " --- TABLE DELETED: <br />";
		else
			echo "Error Dropping Table: PaperDetails <br />";

		$sql = sprintf("DROP TABLE PaperAbstract");
		if(run_query($sql, $link))
			echo $sql . " --- TABLE DELETED: <br />";
		else
			echo "Error Dropping Table: PaperAbstract <br />";

		$sql = sprintf("DROP TABLE DelRegistration");
		if(run_query($sql, $link))
			echo $sql . " --- TABLE DELETED: <br />";
		else
			echo "Error Dropping Table: DelRegistration <br />";

		$sql = sprintf("DROP TABLE PartRegistration");
		if(run_query($sql, $link))
			echo $sql . " --- TABLE DELETED: <br />";
		else
			echo "Error Dropping Table: PartRegistration <br />";

	}

	function build_tables($link)
	{
		// BUILD PaperAbstract
		$sql = sprintf( "CREATE TABLE IF NOT EXISTS `PaperAbstract` (
  											`PaperID` int(6) NOT NULL default '0',
  											`FileName` varchar(100) collate utf8_unicode_ci default NULL,
  											`Location` varchar(100) collate utf8_unicode_ci default NULL,
  											`Submitted` tinyint(1) NOT NULL default '0',
  											PRIMARY KEY  (`PaperID`)
												) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci" );
		if(run_query($sql, $link))
			echo " --- TABLE CREATED: PaperAbstract ";
		else
			echo " ERROR CREATING TABLE PaperAbstract " . mysql_error() . "<br />";


		// BUILD PaperContact
		$sql = sprintf( "CREATE TABLE IF NOT EXISTS `PaperContact` (
										  `PaperID` int(6) NOT NULL default '0',
										  `GName` varchar(100) collate utf8_unicode_ci NOT NULL default '',
										  `LName` varchar(100) collate utf8_unicode_ci NOT NULL default '',
										  `Title` varchar(6) collate utf8_unicode_ci default NULL,
										  `Email` varchar(100) collate utf8_unicode_ci NOT NULL default '',
										  `Password` varchar(6) collate utf8_unicode_ci NOT NULL default '',
										  `Token` varchar(6) collate utf8_unicode_ci NOT NULL default '',
										  `verified` tinyint(1) NOT NULL default '0',
										  PRIMARY KEY  (`PaperID`)
										) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci" );
		if(run_query($sql, $link))
			echo " --- TABLE CREATED: PaperContact ";
		else
			echo " ERROR CREATING TABLE PaperContact " . mysql_error() . "<br />";


		// BUILD PaperDetails
		$sql = sprintf( "CREATE TABLE IF NOT EXISTS `PaperDetails` (
									  `PaperID` int(6) NOT NULL default '0',
									  `PresType` enum('Poster','Presentation') collate utf8_unicode_ci NOT NULL default 'Poster',
									  `Authors` text collate utf8_unicode_ci NOT NULL,
									  `Affiliations` text collate utf8_unicode_ci NOT NULL,
									  `Presenter` varchar(100) collate utf8_unicode_ci NOT NULL default '',
									  `PaperSession` varchar(50) collate utf8_unicode_ci default NULL,
									  `Submitted` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
									  PRIMARY KEY  (`PaperID`)
									) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;" );
		if(run_query($sql, $link))
			echo " --- TABLE CREATED: PaperDetails ";
		else
			echo " ERROR CREATING TABLE PaperDetails " . mysql_error() . "<br />";


		// BUILD PaperTitle
		$sql = sprintf( "CREATE TABLE IF NOT EXISTS `PaperTitle` (
										  `PaperID` int(6) NOT NULL auto_increment,
										  `PTitle` varchar(255) collate utf8_unicode_ci NOT NULL default '',
										  PRIMARY KEY  (`PaperID`),
										  UNIQUE KEY `PaperID` (`PaperID`,`PTitle`),
										  KEY `PTitle` (`PTitle`)
										) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1000 ;" );
		if(run_query($sql, $link))
			echo " --- TABLE CREATED: PaperTitle ";
		else
			echo " ERROR CREATING TABLE PaperTitle " . mysql_error() . "<br />";


		// BUILD DelRegistration
		$sql = sprintf( "CREATE TABLE IF NOT EXISTS `DelRegistration` (
										  `DelID` int(11) NOT NULL auto_increment,
										  `FName` varchar(100) collate utf8_unicode_ci NOT NULL default '',
										  `LName` varchar(100) collate utf8_unicode_ci NOT NULL default '',
										  `Address` varchar(255) collate utf8_unicode_ci NOT NULL default '',
										  `Email` varchar(50) collate utf8_unicode_ci NOT NULL default '',
										  `Telephone` varchar(20) collate utf8_unicode_ci default NULL,
										  `Fax` varchar(20) collate utf8_unicode_ci default NULL,
										  `Accomodation` tinyint(1) NOT NULL default '0',
										  `Accomodation_Other` text collate utf8_unicode_ci,
										  `Diet` tinyint(1) NOT NULL default '0',
										  `Diet_Other` text collate utf8_unicode_ci,
										  `SpecReq` text collate utf8_unicode_ci NOT NULL,
										  `RegistrationOption` tinyint(1) NOT NULL default '0',
										  `Reg_Full_Type` tinyint(1) default '0',
										  `Reg_Day_Sun` tinyint(1) default '0',
										  `Reg_Day_Mon` tinyint(1) default '0',
										  `Reg_Day_Tue` tinyint(1) default '0',
										  `Reg_Day_Wed` tinyint(1) default '0',
										  `Reg_Day_Thu` tinyint(1) default '0',
										  `Reg_Day_Fri` tinyint(1) default '0',
										  `SpecWorkshopReq` text collate utf8_unicode_ci,
										  `PaymentOption` tinyint(4) NOT NULL default '0',
										  PRIMARY KEY  (`DelID`)
										) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=0 ;" );
		if(run_query($sql, $link))
			echo " --- TABLE CREATED: DelRegistration ";
		else
			echo " ERROR CREATING TABLE DelRegistration " . mysql_error() . "<br />";

		// BUILD PartRegistration
		$sql = sprintf( "CREATE TABLE IF NOT EXISTS `PartRegistration` (
										  `ParID` int(11) NOT NULL auto_increment,
										  `DFName` varchar(100) collate utf8_unicode_ci NOT NULL default '',
										  `DLName` varchar(100) collate utf8_unicode_ci NOT NULL default '',
										  `DEmail` varchar(50) collate utf8_unicode_ci NOT NULL default '',
										  `Accomp_Persons` text collate utf8_unicode_ci NOT NULL,
										  `Accomodation` tinyint(1) NOT NULL default '0',
										  `Accomodation_Other` text collate utf8_unicode_ci,
										  `Diet` tinyint(1) NOT NULL default '0',
										  `Diet_Other` text collate utf8_unicode_ci,
										  `SpecReq` text collate utf8_unicode_ci,
										  `RegistrationOption` tinyint(1) NOT NULL default '0',
										  `Reg_Full` tinyint(1) default '0',
										  `Reg_Sel_Sun` tinyint(1) default '0',
										  `Reg_Sel_Break` tinyint(1) default '0',
										  `Reg_Sel_Tue` tinyint(1) default '0',
										  `Reg_Sel_Thu` tinyint(1) default '0',
										  `SpecPartReq` text collate utf8_unicode_ci,
										  `PaymentOption` tinyint(1) NOT NULL default '0',
										  PRIMARY KEY  (`ParID`)
										) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=0 ;" );
		if(run_query($sql, $link))
			echo " --- TABLE CREATED: PartRegistration ";
		else
			echo " ERROR CREATING TABLE PartRegistration " . mysql_error() . "<br />";


	}

 ?>