<link rel="stylesheet" href="http://teawithlucas.com/test/style2.css" type="text/css" media="all">

<div id='MDR'>

<?php

	// gloabal variables
	$debug=FALSE;

	function mysqlconnect($server, $user, $pass, $db) {
		global $debug;	//pulling globals
		$link = mysqli_connect($server, $user, $pass, $db);	// Establish link
		if (!$link) { 	// Check connection
			// There was a problem - display error
			echo "Error: Unable to connect to MySQL." . PHP_EOL;
			echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
			echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
			return FALSE; // Problem return false
		} else {	// Connection established
			if($debug){	// Debug info for connection
				echo "Success: A proper connection to MySQL was made! The my_db database is great." . PHP_EOL;
				echo "Host information: " . mysqli_get_host_info($link) . PHP_EOL;
			}
			return $link; // Conected - return connection link
		}
	}

	function mysqlclose($link) {
		global $debug;	//pulling globals
		mysqli_close($link);	//closing connection
	}

	function myslquery($query, $link) {
		global $debug;	//pulling globals

		$result = mysqli_query($link,$query);	//querying connection

		if (NULL!==$result){ //checking result for NULL
			return $result;
		} else {
			return FALSE;
		}
	}

	function divisioncall($Divi, $link) {
		global $debug;		//pulling globals
		$pos_sc_con = array(array('DC', 'DV', 'TL', 'TM', 'Not Set', '??'), array('Commander', 'Vice', 'Team Leader', 'Memeber', 'Not Set', '??'));
		//Addoulous' sql query
		$query = "SELECT Y.MemberName
,Y.Team
,Y.Rank
,Y.Prefix
,Y.Position
,Y.member_id
FROM (
SELECT Z.*
,CASE WHEN Z.Position = 'DC' THEN 1 WHEN Z.Position = 'DV' THEN 2 WHEN Z.Position = 'TL' THEN 3 WHEN Z.Position = '2IC' THEN 4 WHEN Z.Position = 'TM' THEN 5 ELSE 6 END AS RankID
FROM (
SELECT A.member_id
,A.name AS MemberName
,A.member_title AS Title
,CASE WHEN C.g_title = 'Probation' THEN 'Probation' WHEN C.g_title = 'Away' THEN 'Away' ELSE B.field_18 END AS Division
,CASE WHEN B.field_39 IN ('DC','DV') THEN 'Leadership' WHEN C.g_title = 'Away' THEN 'Away' WHEN C.g_title = 'Probation' THEN 'Probation'  WHEN B.field_39 IS NULL THEN 'Unassigned' WHEN B.field_39 = 'Not Set' THEN 'Unassigned' ELSE B.field_38 END AS Team
,C.g_title AS Rank
,CASE WHEN B.field_39 = 'HQ' THEN 'TM' ELSE B.field_39 END AS Position
,CASE WHEN C.g_title = 'Away' THEN '<span style=''color:#808080''>' ELSE C.prefix END AS prefix
FROM dicore_members AS A
LEFT JOIN dicore_pfields_content AS B ON B.member_id = A.member_id
LEFT JOIN orig_digroups AS C ON C.g_id = A.member_group_id
WHERE CASE WHEN C.g_title = 'Probation' THEN 'Probation' WHEN C.g_title = 'Away' THEN 'Away' ELSE B.field_18 END = '".$Divi."' AND C.g_title NOT IN ('Registered Guest','Elder')
) Z
) Y
ORDER BY CASE WHEN Y.Team = 'Leadership' THEN 1 WHEN Y.Team = 'Team A' THEN 2 WHEN Y.Team = 'Team B' THEN 3 WHEN Y.Team = 'Team C' THEN 4 WHEN Y.Team = 'Team D' THEN 5 WHEN Y.Team = 'Team E' THEN 6 WHEN Y.Team = 'Team F' THEN 7 WHEN Y.Team = 'Casual A' THEN 8 WHEN Y.Team = 'Casual B' THEN 9 WHEN Y.Team = 'Casual C' THEN 10 WHEN Y.Team = 'Casual D' THEN 11 ELSE 12 END, Y.RankID";

		$result = myslquery($query,$link);  //Call custom function
		if (!$result){ //Check for query problem
			echo "Error Bad qury or something";
			exit; //Stop before causing error
		}

		$rowcount=mysqli_num_rows($result); //Get number of records for query



		echo "<div class='Divsion' id='".$Divi."'><div class='headerline'><div class='diviheader'><h2>".$Divi." (".$rowcount.") </h2></div>"; //Start Division and Header

		$previousteam = "#newdiv";
		while($row = mysqli_fetch_assoc($result)){
			$Rank = $row['Rank']; $Team = $row['Team'];	$Prefix = $row["Prefix"]; $MemberName = $row['MemberName'];	$posSC = $row['Position']; $mbrid = $row['member_id']; //setting array to local variables (might remove in future)
			$position=str_replace($pos_sc_con[0], $pos_sc_con[1], $posSC);
			if ($position==""){
				$position='Null';
			}

			if ($previousteam != $Team){	 //Checking if this record is new team
				if ($previousteam != "#newdiv"){ //Checking if first record in array
					if ("Leadership" != $previousteam){ //Checking previous team was not leadership, if so end team
						echo "</ol></div>";
					} else { //If previous team leadership, end header and end leadership group
						echo "</ol></div></div>";
					}
				} else {
					if ("Leadership" != $Team){ //catching error incase of no leaders
						echo "</div>";
					}
				}
				if ("Leadership" != $Team){ //Checking not leadership, if so start new team
					echo "<div class='team team-".$Team."'><h3>".$Team."</h3><ol>";
				} else { //If leadership, if so start leadership group
					echo "<div class='leadership'><ol>";
				}
			}

			echo "<li class='position-".$posSC."'><a href='http://forum.dmg-inc.com/profile/".$mbrid."-".$MemberName."/' target='_blank'>";
			if ($posSC=='DC' || $posSC=='DV' || $posSC=='TL' || $posSC=='2IC'){
				echo "<span style='color:#fff'>".$position.": </span>";
			}
			echo $Prefix." ".$MemberName."</span></b><span style='color:#ddd; font-size:10px'> [".$posSC."]</span></a></li>"; //Adding record

			$previousteam = $Team;
		}
		echo "</ol></div></div><br>"; //End team and division
	}

	$server="localhost";	$user="reader";		$pass="crupR2#U"; 	$db="diforum"; //Set server variables

	$mainlink=mysqlconnect($server,$user,$pass,$db); //Connect to server

	divisioncall("DI-I", $mainlink);
	divisioncall("DI-II", $mainlink);
	divisioncall("DI-III", $mainlink);
	divisioncall("DI-IV", $mainlink);
	divisioncall("DI-V", $mainlink);
	divisioncall("DI-VI", $mainlink);
	divisioncall("DI-VII", $mainlink);
	divisioncall("DI-VIII", $mainlink);
	divisioncall("DI-IX", $mainlink);
	divisioncall("DI-X", $mainlink);
	divisioncall("DI-XI", $mainlink);
	divisioncall("DI-XII", $mainlink);
	divisioncall("DI-Community", $mainlink);
	divisioncall("Away", $mainlink);
	divisioncall("Probation", $mainlink);

	mysqlclose($mysqllink); //Close Server
?>
</div>