<?


function getPoints($userid, $groupid, $matchid){
	
	$sql = @"
		SELECT
			`Bet`.UserID,
			SUM(IF((`Match`.`GoalsRegular1` = `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` = `Bet`.`GoalsRegular2`) OR (`Match`.`GoalsRegular1` > `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` > `Bet`.`GoalsRegular2`) OR (`Match`.`GoalsRegular1` < `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` < `Bet`.`GoalsRegular2`), 1, 0))
			AS Tendenz,
			SUM(IF(`Match`.`GoalsRegular1` - `Match`.`GoalsRegular2` = `Bet`.`GoalsRegular1` - `Bet`.`GoalsRegular2`, 1, 0))
			AS Tordifferenz,
			SUM(IF(`Match`.`GoalsRegular1` = `Bet`.`GoalsRegular1` AND `Match`.`GoalsRegular2` = `Bet`.`GoalsRegular2`, 1, 0))
			AS Torwette,
			SUM(IF(`Match`.`Winner` = `Bet`.`Winner`, 1, 0))
			AS Siegerwette,
			(SUM(IF((`Match`.`GoalsRegular1` = `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` = `Bet`.`GoalsRegular2`) OR (`Match`.`GoalsRegular1` > `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` > `Bet`.`GoalsRegular2`) OR (`Match`.`GoalsRegular1` < `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` < `Bet`.`GoalsRegular2`), 1, 0))
			 + 
			SUM(IF(`Match`.`GoalsRegular1` - `Match`.`GoalsRegular2` = `Bet`.`GoalsRegular1` - `Bet`.`GoalsRegular2`, 1, 0)) 
			 + 
			SUM(IF(`Match`.`GoalsRegular1` = `Bet`.`GoalsRegular1` AND `Match`.`GoalsRegular2` = `Bet`.`GoalsRegular2`, 1, 0))
			 + 
			SUM(IF(`Match`.`Winner` = `Bet`.`Winner`, 1, 0))
			)
			AS Punkte
		FROM `Bet`
			JOIN `Match`
			ON `Bet`.MatchID = `Match`.ID AND `Match`.`Deleted` <> 1 AND `Match`.`Finished` = 1 
		WHERE `Bet`.`Deleted` <> 1 AND `Bet`.`GroupID` = ".$groupid."  AND `Bet`.`UserID` = ".$userid." AND `Bet`.MatchID = ".$matchid."	
	";
	
	$match = getSingle($sql, $matchid);
	
	return $match["Punkte"];

}

function getStandingsIf($goals1, $goals2, $winner, $matchid, $groupid, $contestid=-1){

$sql = @"
	SELECT
		`User`.ID AS UserID, 
		`User`.Title AS Title, 
		`User`.Gender AS Gender, 
		`User`.FirstName AS FirstName, 
		`User`.LastName AS LastName, 
		`User`.NickName AS NickName, 
		`User`.Email AS Email, 
		`User`.Phone AS Phone, 
		`User`.ImageSrc AS ImageSrc,
		(PunkteTable.Tendenz + IFNULL(PunkteTable2.Tendenz,0)) AS Tendenz,
		(PunkteTable.Tordifferenz + IFNULL(PunkteTable2.Tordifferenz,0)) AS Tordifferenz,
		(PunkteTable.Torwette + IFNULL(PunkteTable2.Torwette,0)) AS Torwette,
		(PunkteTable.Siegerwette + IFNULL(PunkteTable2.Siegerwette,0)) AS Siegerwette,
        ((PunkteTable.Tendenz + PunkteTable.Tordifferenz + PunkteTable.Torwette + PunkteTable.Siegerwette)+IFNULL((PunkteTable2.Tendenz + PunkteTable2.Tordifferenz + PunkteTable2.Torwette + PunkteTable2.Siegerwette),0)) AS Punkte,
		PunkteTable.MatchDate AS MatchDate
	FROM `User`
	LEFT JOIN `UserHasGroup`
		ON `UserHasGroup`.UserID = `User`.ID
	LEFT JOIN (
			SELECT
				`Bet`.UserID,
        		SUM(IF((`Match`.`GoalsRegular1` = `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` = `Bet`.`GoalsRegular2`) OR (`Match`.`GoalsRegular1` > `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` > `Bet`.`GoalsRegular2`) OR (`Match`.`GoalsRegular1` < `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` < `Bet`.`GoalsRegular2`), 1, 0))
        		AS Tendenz,
        		SUM(IF(`Match`.`GoalsRegular1` - `Match`.`GoalsRegular2` = `Bet`.`GoalsRegular1` - `Bet`.`GoalsRegular2`, 1, 0))
				AS Tordifferenz,
        		SUM(IF(`Match`.`GoalsRegular1` = `Bet`.`GoalsRegular1` AND `Match`.`GoalsRegular2` = `Bet`.`GoalsRegular2`, 1, 0))
				AS Torwette,
				SUM(IF(`Match`.`Winner` = `Bet`.`Winner`, 1, 0))
				AS Siegerwette,
				MAX(`Match`.`MatchDate`) AS MatchDate				
			FROM `Bet`
				JOIN `Match`
				ON `Bet`.MatchID = `Match`.ID AND `Match`.`Deleted` <> 1 AND `Match`.`Finished` = 1 ".($contestid != -1 ? " AND `Match`.ContestID = ".$contestid." " : "")."
			WHERE `Bet`.`Deleted` <> 1 AND `Bet`.`GroupID` = ".$groupid."
			GROUP BY `Bet`.UserID
		) PunkteTable
		ON PunkteTable.UserID = User.ID
	LEFT JOIN (
			SELECT
				`Bet`.UserID,
        		SUM(IF((".$goals1." = ".$goals2." AND `Bet`.`GoalsRegular1` = `Bet`.`GoalsRegular2`) OR (".$goals1." > ".$goals2." AND `Bet`.`GoalsRegular1` > `Bet`.`GoalsRegular2`) OR (".$goals1." < ".$goals2." AND `Bet`.`GoalsRegular1` < `Bet`.`GoalsRegular2`), 1, 0))
        		AS Tendenz,
        		SUM(IF(".$goals1." - ".$goals2." = `Bet`.`GoalsRegular1` - `Bet`.`GoalsRegular2`, 1, 0))
				AS Tordifferenz,
        		SUM(IF(".$goals1." = `Bet`.`GoalsRegular1` AND ".$goals2." = `Bet`.`GoalsRegular2`, 1, 0))
				AS Torwette,
				SUM(IF(".$winner." = `Bet`.`Winner`, 1, 0))
				AS Siegerwette,
				MAX(`Match`.`MatchDate`) AS MatchDate				
			FROM `Bet`
				JOIN `Match`
				ON `Bet`.MatchID = `Match`.ID AND `Match`.`Deleted` <> 1 AND `Match`.`ID` = ".$matchid." ".($contestid != -1 ? " AND `Match`.ContestID = ".$contestid." " : "")."
			WHERE `Bet`.`Deleted` <> 1 AND `Bet`.`GroupID` = ".$groupid."
			GROUP BY `Bet`.UserID
		) PunkteTable2
		ON PunkteTable2.UserID = User.ID		
	WHERE `UserHasGroup`.`GroupID` = ".$groupid." AND `User`.`Deleted` <> 1 AND `User`.`Active` = 1
    ORDER BY Punkte DESC
";	


	try{
		$return = array();
		$db = connection();
		$stmt = $db->prepare($sql);
		$stmt->execute();
		
		$count = 0;
		$position = 0;
		$punkte = -1;
	
		while($row = $stmt->fetch()) {
			$count++;
			if($row["Punkte"] != $punkte){
				$punkte = $row["Punkte"];
				$position = $count;
			}
			$row["Position"] = $position;
			$return["id".$row["UserID"]] = $row;
		}
		return $return;
	}
	catch(PDOException $e)
	{
		echo $e->getMessage();
		echo "<br />".$sql;
	}		
	

}	
	
function getStandings($groupid, $contestid=-1){

$sql = @"
	SELECT
		`User`.ID AS UserID, 
		`User`.Title AS Title, 
		`User`.Gender AS Gender, 
		`User`.FirstName AS FirstName, 
		`User`.LastName AS LastName, 
		`User`.NickName AS NickName, 
		`User`.Email AS Email, 
		`User`.Phone AS Phone, 
		`User`.ImageSrc AS ImageSrc,
		PunkteTable.Tendenz AS Tendenz,
		PunkteTable.Tordifferenz AS Tordifferenz,
		PunkteTable.Torwette AS Torwette,
		PunkteTable.Siegerwette AS Siegerwette,
        (PunkteTable.Tendenz + PunkteTable.Tordifferenz + PunkteTable.Torwette + PunkteTable.Siegerwette) AS Punkte,
		PunkteTable.MatchDate AS MatchDate
	FROM `User`
	LEFT JOIN `UserHasGroup`
		ON `UserHasGroup`.UserID = `User`.ID
	LEFT JOIN (
			SELECT
				`Bet`.UserID,
        		SUM(IF((`Match`.`GoalsRegular1` = `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` = `Bet`.`GoalsRegular2`) OR (`Match`.`GoalsRegular1` > `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` > `Bet`.`GoalsRegular2`) OR (`Match`.`GoalsRegular1` < `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` < `Bet`.`GoalsRegular2`), 1, 0))
        		AS Tendenz,
        		SUM(IF(`Match`.`GoalsRegular1` - `Match`.`GoalsRegular2` = `Bet`.`GoalsRegular1` - `Bet`.`GoalsRegular2`, 1, 0))
				AS Tordifferenz,
        		SUM(IF(`Match`.`GoalsRegular1` = `Bet`.`GoalsRegular1` AND `Match`.`GoalsRegular2` = `Bet`.`GoalsRegular2`, 1, 0))
				AS Torwette,
				SUM(IF(`Match`.`Winner` = `Bet`.`Winner`, 1, 0))
				AS Siegerwette,
				MAX(`Match`.`MatchDate`) AS MatchDate				
			FROM `Bet`
				JOIN `Match`
				ON `Bet`.MatchID = `Match`.ID AND `Match`.`Deleted` <> 1 AND `Match`.`Finished` = 1 ".($contestid != -1 ? " AND `Match`.ContestID = ".$contestid." " : "")."
			WHERE `Bet`.`Deleted` <> 1 AND `Bet`.`GroupID` = ".$groupid."
			GROUP BY `Bet`.UserID
		) PunkteTable
		ON PunkteTable.UserID = User.ID
	WHERE `UserHasGroup`.`GroupID` = ".$groupid." AND `User`.`Deleted` <> 1 AND `User`.`Active` = 1
    ORDER BY Punkte DESC
";	
	
	try{
		$return = array();
		$db = connection();
		$stmt = $db->prepare($sql);
		$stmt->execute();
		
		$count = 0;
		$position = 0;
		$punkte = -1;
	
		while($row = $stmt->fetch()) {
			$count++;
			if($row["Punkte"] != $punkte){
				$punkte = $row["Punkte"];
				$position = $count;
			}
			$row["Position"] = $position;
			$return["id".$row["UserID"]] = $row;
		}
		return $return;
	}
	catch(PDOException $e)
	{
		echo $e->getMessage();
		echo "<br />".$sql;
	}		
	

}

function getStandingsQuoten($groupid, $contestid=-1){

$sql = @"
	SELECT
		`User`.ID AS UserID, 
		`User`.Title AS Title, 
		`User`.Gender AS Gender, 
		`User`.FirstName AS FirstName, 
		`User`.LastName AS LastName, 
		`User`.NickName AS NickName, 
		`User`.Email AS Email, 
		`User`.Phone AS Phone, 
		`User`.ImageSrc AS ImageSrc,
		PunkteTable.Tendenz AS Tendenz,
		PunkteTable.Tordifferenz AS Tordifferenz,
		PunkteTable.Torwette AS Torwette,
		PunkteTable.Siegerwette AS Siegerwette,
        IFNULL((PunkteTable.Tendenz + PunkteTable.Tordifferenz + PunkteTable.Torwette + PunkteTable.Siegerwette),0) AS Punkte,
		PunkteTable.MatchDate AS MatchDate
	FROM `User`
	LEFT JOIN `UserHasGroup`
		ON `UserHasGroup`.UserID = `User`.ID
	LEFT JOIN (
	
	SELECT
				`Bet`.UserID,
        		SUM(IF((`Match`.`GoalsRegular1` = `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` = `Bet`.`GoalsRegular2`) OR (`Match`.`GoalsRegular1` > `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` > `Bet`.`GoalsRegular2`) OR (`Match`.`GoalsRegular1` < `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` < `Bet`.`GoalsRegular2`), IFNULL((1/quotenmatch.quo),0), 0))
        		AS Tendenz,
        		SUM(IF(`Match`.`GoalsRegular1` - `Match`.`GoalsRegular2` = `Bet`.`GoalsRegular1` - `Bet`.`GoalsRegular2`, IFNULL((1/quotenmatch.quo),0), 0))
				AS Tordifferenz,
        		SUM(IF(`Match`.`GoalsRegular1` = `Bet`.`GoalsRegular1` AND `Match`.`GoalsRegular2` = `Bet`.`GoalsRegular2`, IFNULL((1/quotenmatch.quo),0), 0))
				AS Torwette,
				SUM(IF(`Match`.`Winner` = `Bet`.`Winner`, IFNULL((1/quotenmatch.quo),0), 0))
				AS Siegerwette,
				MAX(`Match`.`MatchDate`) AS MatchDate				
			FROM `Bet`
            
            JOIN (
				SELECT `Match`.`ID` as id,
				1-(IFNULL(IF(`Match`.`Winner` = 1, q1.quoten, IF(`Match`.`Winner` = 2, q2.quoten, q3.quoten)), 0)/(IFNULL(q1.quoten, 0) + IFNULL(q2.quoten, 0) + IFNULL(q3.quoten, 0))) as quo
				FROM `Match`
				LEFT JOIN (SELECT COUNT(*) as quoten, `Bet`.`MatchID` as MatchID FROM `Bet` WHERE `Bet`.`Winner` = 1 AND `Bet`.`GroupID` = 2 AND `Bet`.`Deleted` <> 1 AND `Bet`.`Active` = 1 GROUP BY `Bet`.`MatchID`) q1 ON `Match`.`ID` = q1.MatchID 
				LEFT JOIN (SELECT COUNT(*) as quoten, `Bet`.`MatchID` as MatchID FROM `Bet` WHERE `Bet`.`Winner` = 2 AND `Bet`.`GroupID` = 2 AND `Bet`.`Deleted` <> 1 AND `Bet`.`Active` = 1 GROUP BY `Bet`.`MatchID`) q2 ON `Match`.`ID` = q2.MatchID 
				LEFT JOIN (SELECT COUNT(*) as quoten, `Bet`.`MatchID` as MatchID FROM `Bet` WHERE `Bet`.`Winner` = 3 AND `Bet`.`GroupID` = 2 AND `Bet`.`Deleted` <> 1 AND `Bet`.`Active` = 1 GROUP BY `Bet`.`MatchID`) q3 ON `Match`.`ID` = q3.MatchID
				WHERE `Match`.`Deleted` <> 1 AND `Match`.`Finished` = 1
				ORDER BY `Match`.`MatchDate`
				) quotenmatch
				
                ON `Bet`.MatchID = quotenmatch.ID
			
				JOIN `Match`
				ON `Bet`.MatchID = `Match`.ID AND `Match`.`Deleted` <> 1 AND `Match`.`Finished` = 1 ".($contestid != -1 ? " AND `Match`.ContestID = ".$contestid." " : "")."

			WHERE `Bet`.`Deleted` <> 1 AND `Bet`.`GroupID` = ".$groupid."
			GROUP BY `Bet`.UserID
		) PunkteTable
		ON PunkteTable.UserID = User.ID
	WHERE `UserHasGroup`.`GroupID` = ".$groupid." AND `User`.`Deleted` <> 1 AND `User`.`Active` = 1
    ORDER BY Punkte DESC
";	
	
	try{
		$return = array();
		$db = connection();
		$stmt = $db->prepare($sql);
		$stmt->execute();
		
		$count = 0;
		$position = 0;
		$punkte = -1;
	
		while($row = $stmt->fetch()) {
			$count++;
			if($row["Punkte"] != $punkte){
				$punkte = $row["Punkte"];
				$position = $count;
			}
			$row["Position"] = $position;
			$return["id".$row["UserID"]] = $row;
		}
		return $return;
	}
	catch(PDOException $e)
	{
		echo $e->getMessage();
		echo "<br />".$sql;
	}		
	

}

function getStandingsDate($groupid, $date, $contestid=-1){

$sql =@"

SELECT
		`User`.ID AS UserID, 
		`User`.Title AS Title, 
		`User`.Gender AS Gender, 
		`User`.FirstName AS FirstName, 
		`User`.LastName AS LastName, 
		`User`.NickName AS NickName, 
		`User`.Email AS Email, 
		`User`.Phone AS Phone, 
		`User`.ImageSrc AS ImageSrc,
		PunkteTable.Tendenz AS Tendenz,
		PunkteTable.Tordifferenz AS Tordifferenz,
		PunkteTable.Torwette AS Torwette,
		PunkteTable.Siegerwette AS Siegerwette,
        (PunkteTable.Tendenz + PunkteTable.Tordifferenz + PunkteTable.Torwette + PunkteTable.Siegerwette) AS Punkte,
		PunkteTable.MatchDate AS MatchDate
	FROM `User`
	LEFT JOIN `UserHasGroup`
		ON `UserHasGroup`.UserID = `User`.ID
        
	LEFT JOIN (
			SELECT
				`Bet`.UserID,
        		SUM(IF((`Match`.`GoalsRegular1` = `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` = `Bet`.`GoalsRegular2`) OR (`Match`.`GoalsRegular1` > `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` > `Bet`.`GoalsRegular2`) OR (`Match`.`GoalsRegular1` < `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` < `Bet`.`GoalsRegular2`), 1, 0))
        		AS Tendenz,
        		SUM(IF(`Match`.`GoalsRegular1` - `Match`.`GoalsRegular2` = `Bet`.`GoalsRegular1` - `Bet`.`GoalsRegular2`, 1, 0))
				AS Tordifferenz,
        		SUM(IF(`Match`.`GoalsRegular1` = `Bet`.`GoalsRegular1` AND `Match`.`GoalsRegular2` = `Bet`.`GoalsRegular2`, 1, 0))
				AS Torwette,
				SUM(IF(`Match`.`Winner` = `Bet`.`Winner`, 1, 0))
				AS Siegerwette,
				MAX(`Match`.`MatchDate`) AS MatchDate
			FROM `Bet`
				JOIN `Match`
				ON `Bet`.MatchID = `Match`.ID AND `Match`.`Deleted` <> 1 AND `Match`.`Finished` = 1 ".($contestid != -1 ? " AND `Match`.ContestID = ".$contestid." " : "")." AND `Match`.`MatchDate` <= '".$date."'
			WHERE `Bet`.`Deleted` <> 1 AND `Bet`.`GroupID` = ".$groupid."
        	GROUP BY `Bet`.UserID
        
		) PunkteTable
		ON PunkteTable.UserID = User.ID
        
	WHERE `UserHasGroup`.`GroupID` = ".$groupid." AND `User`.`Deleted` <> 1 AND `User`.`Active` = 1
    ORDER BY Punkte DESC	
	";
	
	
	try{
		$return = array();
		$db = connection();
		$stmt = $db->prepare($sql);
		$stmt->execute();
		
		$count = 0;
		$position = 0;
		$punkte = -1;
	
		while($row = $stmt->fetch()) {
			$count++;
			if($row["Punkte"] != $punkte){
				$punkte = $row["Punkte"];
				$position = $count;
			}
			$row["Position"] = $position;
			$return["id".$row["UserID"]] = $row;
		}
		return $return;
	}
	catch(PDOException $e)
	{
		echo $e->getMessage();
		echo "<br />".$sql;
	}		

}


function getStandingsBack($groupid, $back, $contestid=-1){

$back = $back - 1;	
if($back < 0 ) $back = 0;

$sql =@"

SELECT
		`User`.ID AS UserID, 
		`User`.Title AS Title, 
		`User`.Gender AS Gender, 
		`User`.FirstName AS FirstName, 
		`User`.LastName AS LastName, 
		`User`.NickName AS NickName, 
		`User`.Email AS Email, 
		`User`.Phone AS Phone, 
		`User`.ImageSrc AS ImageSrc,
		PunkteTable.Tendenz AS Tendenz,
		PunkteTable.Tordifferenz AS Tordifferenz,
		PunkteTable.Torwette AS Torwette,
		PunkteTable.Siegerwette AS Siegerwette,
        (PunkteTable.Tendenz + PunkteTable.Tordifferenz + PunkteTable.Torwette + PunkteTable.Siegerwette) AS Punkte,
		PunkteTable.MatchDate AS MatchDate
	FROM `User`
	LEFT JOIN `UserHasGroup`
		ON `UserHasGroup`.UserID = `User`.ID
        
	LEFT JOIN (
			SELECT
				`Bet`.UserID,
        		SUM(IF((`Match`.`GoalsRegular1` = `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` = `Bet`.`GoalsRegular2`) OR (`Match`.`GoalsRegular1` > `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` > `Bet`.`GoalsRegular2`) OR (`Match`.`GoalsRegular1` < `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` < `Bet`.`GoalsRegular2`), 1, 0))
        		AS Tendenz,
        		SUM(IF(`Match`.`GoalsRegular1` - `Match`.`GoalsRegular2` = `Bet`.`GoalsRegular1` - `Bet`.`GoalsRegular2`, 1, 0))
				AS Tordifferenz,
        		SUM(IF(`Match`.`GoalsRegular1` = `Bet`.`GoalsRegular1` AND `Match`.`GoalsRegular2` = `Bet`.`GoalsRegular2`, 1, 0))
				AS Torwette,
				SUM(IF(`Match`.`Winner` = `Bet`.`Winner`, 1, 0))
				AS Siegerwette,
				MAX(`Match`.`MatchDate`) AS MatchDate
 			FROM `Bet`
				JOIN `Match`
				ON `Bet`.MatchID = `Match`.ID AND `Match`.`Deleted` <> 1 AND `Match`.`Finished` = 1 ".($contestid != -1 ? " AND `Match`.ContestID = ".$contestid." " : "")." AND `Match`.`MatchDate` < (
                    SELECT `Match`.`MatchDate` FROM `Match` WHERE `Match`.`Deleted` <> 1 AND `Match`.`Finished` = 1 ORDER BY `Match`.`MatchDate` DESC LIMIT ".$back.",1
                    )
			WHERE `Bet`.`Deleted` <> 1 AND `Bet`.`GroupID` = ".$groupid."
        	GROUP BY `Bet`.UserID
        
		) PunkteTable
		ON PunkteTable.UserID = User.ID
		
        
	WHERE `UserHasGroup`.`GroupID` = ".$groupid." AND `User`.`Deleted` <> 1 AND `User`.`Active` = 1
    ORDER BY Punkte DESC	
	";
	
	
	try{
		$return = array();
		$db = connection();
		$stmt = $db->prepare($sql);
		$stmt->execute();
		
		$count = 0;
		$position = 0;
		$punkte = -1;
	
		while($row = $stmt->fetch()) {
			$count++;
			if($row["Punkte"] != $punkte){
				$punkte = $row["Punkte"];
				$position = $count;
			}
			$row["Position"] = $position;
			$return["id".$row["UserID"]] = $row;
		}
		return $return;
	}
	catch(PDOException $e)
	{
		echo $e->getMessage();
		echo "<br />".$sql;
	}		

}


function getStandingsGender($groupid, $contestid=-1){

	$sql = @"

		SELECT
			`User`.Gender AS Gender, 
			COUNT(`User`.`Gender`) as countGender,
			SUM(PunkteTable.Tendenz) AS Tendenz,
			SUM(PunkteTable.Tordifferenz) AS Tordifferenz,
			SUM(PunkteTable.Torwette) AS Torwette,
			SUM(PunkteTable.Siegerwette) AS Siegerwette,
			SUM((PunkteTable.Tendenz + PunkteTable.Tordifferenz + PunkteTable.Torwette + PunkteTable.Siegerwette)) AS Punkte,
			(SUM((PunkteTable.Tendenz + PunkteTable.Tordifferenz + PunkteTable.Torwette + PunkteTable.Siegerwette))/COUNT(`User`.`Gender`)) AS PunkteRelativ,
			SUM(PunkteTable.MatchDate) AS MatchDate
		FROM `User`
		LEFT JOIN `UserHasGroup`
			ON `UserHasGroup`.UserID = `User`.ID
			
		LEFT JOIN (
				SELECT
					`Bet`.UserID,
					SUM(IF((`Match`.`GoalsRegular1` = `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` = `Bet`.`GoalsRegular2`) OR (`Match`.`GoalsRegular1` > `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` > `Bet`.`GoalsRegular2`) OR (`Match`.`GoalsRegular1` < `Match`.`GoalsRegular2` AND `Bet`.`GoalsRegular1` < `Bet`.`GoalsRegular2`), 1, 0))
					AS Tendenz,
					SUM(IF(`Match`.`GoalsRegular1` - `Match`.`GoalsRegular2` = `Bet`.`GoalsRegular1` - `Bet`.`GoalsRegular2`, 1, 0))
					AS Tordifferenz,
					SUM(IF(`Match`.`GoalsRegular1` = `Bet`.`GoalsRegular1` AND `Match`.`GoalsRegular2` = `Bet`.`GoalsRegular2`, 1, 0))
					AS Torwette,
					SUM(IF(`Match`.`Winner` = `Bet`.`Winner`, 1, 0))
					AS Siegerwette,
					MAX(`Match`.`MatchDate`) AS MatchDate				
				FROM `Bet`
					JOIN `Match`
					ON `Bet`.MatchID = `Match`.ID AND `Match`.`Deleted` <> 1 AND `Match`.`Finished` = 1 ".($contestid != -1 ? " AND `Match`.ContestID = ".$contestid." " : "")."
				WHERE `Bet`.`Deleted` <> 1 AND `Bet`.`GroupID` = ".$groupid."
				GROUP BY `Bet`.UserID
			) PunkteTable
			ON PunkteTable.UserID = User.ID
			
			
		WHERE `UserHasGroup`.`GroupID` = ".$groupid." AND `User`.`Deleted` <> 1 AND `User`.`Active` = 1
		
		GROUP BY `User`.Gender
		
		ORDER BY Punkte DESC	
	
	";	
	
	try{
		$return = array();
		$db = connection();
		$stmt = $db->prepare($sql);
		$stmt->execute();
		
		$count = 0;
		$position = 0;
		$punkte = -1;
	
		while($row = $stmt->fetch()) {
			$count++;
			if($row["Punkte"] != $punkte){
				$punkte = $row["Punkte"];
				$position = $count;
			}
			$row["Position"] = $position;
			array_push($return, $row);
		}
		return $return;
	}
	catch(PDOException $e)
	{
		echo $e->getMessage();
		echo "<br />".$sql;
	}		
	
}

function getStandingsBravery($groupid, $contestid=-1){

$sql = @"

SELECT
		`User`.ID AS UserID, 
		`User`.Title AS Title, 
		`User`.Gender AS Gender, 
		`User`.FirstName AS FirstName, 
		`User`.LastName AS LastName, 
		`User`.NickName AS NickName, 
		`User`.Email AS Email, 
		`User`.Phone AS Phone, 
		`User`.ImageSrc AS ImageSrc,
		PunkteTable.S1 AS P1,
		PunkteTable.S2 AS P2,
		PunkteTable.S3 AS P3,
        (PunkteTable.S1 + PunkteTable.S2 + PunkteTable.S3) AS Punkte,
		PunkteTable.MatchDate AS MatchDate
	FROM `User`
	LEFT JOIN `UserHasGroup`
		ON `UserHasGroup`.UserID = `User`.ID
	LEFT JOIN (
	
	SELECT
				`Bet`.UserID,
				SUM(IF(`Bet`.`Winner` = 1, anzahlmatch.punkte1, 0))
				AS S1,
				SUM(IF(`Bet`.`Winner` = 2, anzahlmatch.punkte2, 0))
				AS S2,
				SUM(IF(`Bet`.`Winner` = 3, anzahlmatch.punkte3, 0))
				AS S3,
				MAX(`Match`.`MatchDate`) AS MatchDate				
			FROM `Bet`
            
            JOIN (
SELECT `Match`.`ID` as id,
				IFNULL(((IFNULL(a2.anzahl, 0)+IFNULL(a3.anzahl, 0))/IFNULL(a1.anzahl, 0)),0) as punkte1,
				IFNULL(((IFNULL(a1.anzahl, 0)+IFNULL(a3.anzahl, 0))/IFNULL(a2.anzahl, 0)),0) as punkte2,
				IFNULL(((IFNULL(a1.anzahl, 0)+IFNULL(a2.anzahl, 0))/IFNULL(a3.anzahl, 0)),0) as punkte3
				FROM `Match`
				LEFT JOIN (SELECT COUNT(*) as anzahl, `Bet`.`MatchID` as MatchID FROM `Bet` WHERE `Bet`.`Winner` = 1 AND `Bet`.`GroupID` = 2 AND `Bet`.`Deleted` <> 1 AND `Bet`.`Active` = 1 GROUP BY `Bet`.`MatchID`) a1 ON `Match`.`ID` = a1.MatchID 
				LEFT JOIN (SELECT COUNT(*) as anzahl, `Bet`.`MatchID` as MatchID FROM `Bet` WHERE `Bet`.`Winner` = 2 AND `Bet`.`GroupID` = 2 AND `Bet`.`Deleted` <> 1 AND `Bet`.`Active` = 1 GROUP BY `Bet`.`MatchID`) a2 ON `Match`.`ID` = a2.MatchID 
				LEFT JOIN (SELECT COUNT(*) as anzahl, `Bet`.`MatchID` as MatchID FROM `Bet` WHERE `Bet`.`Winner` = 3 AND `Bet`.`GroupID` = 2 AND `Bet`.`Deleted` <> 1 AND `Bet`.`Active` = 1 GROUP BY `Bet`.`MatchID`) a3 ON `Match`.`ID` = a3.MatchID
				WHERE `Match`.`Deleted` <> 1 AND `Match`.`Finished` = 1
				ORDER BY `Match`.`MatchDate`
				) anzahlmatch
				
                ON `Bet`.MatchID = anzahlmatch.ID
			
				JOIN `Match`
				ON `Bet`.MatchID = `Match`.ID AND `Match`.`Deleted` <> 1 AND `Match`.`Finished` = 1 ".($contestid != -1 ? " AND `Match`.ContestID = ".$contestid." " : "")."

			WHERE `Bet`.`Deleted` <> 1 AND `Bet`.`GroupID` = ".$groupid."
			GROUP BY `Bet`.UserID
		) PunkteTable
		ON PunkteTable.UserID = User.ID
	WHERE `UserHasGroup`.`GroupID` = ".$groupid." AND `User`.`Deleted` <> 1 AND `User`.`Active` = 1
    ORDER BY Punkte DESC
";	
	
	try{
		$return = array();
		$db = connection();
		$stmt = $db->prepare($sql);
		$stmt->execute();
		
		$count = 0;
		$position = 0;
		$punkte = -1;
	
		while($row = $stmt->fetch()) {
			$count++;
			if($row["Punkte"] != $punkte){
				$punkte = $row["Punkte"];
				$position = $count;
			}
			$row["Position"] = $position;
			$return["id".$row["UserID"]] = $row;
		}
		return $return;
	}
	catch(PDOException $e)
	{
		echo $e->getMessage();
		echo "<br />".$sql;
	}		
	

}

function getStandingsBadLuck($groupid, $contestid=-1){

$sql = @"
SELECT
		`User`.ID AS UserID, 
		`User`.Title AS Title, 
		`User`.Gender AS Gender, 
		`User`.FirstName AS FirstName, 
		`User`.LastName AS LastName, 
		`User`.NickName AS NickName, 
		`User`.Email AS Email, 
		`User`.Phone AS Phone, 
		`User`.ImageSrc AS ImageSrc,
		IFNULL(PunkteTable.punkte,0) AS Punkte,
		PunkteTable.MatchDate AS MatchDate
	FROM `User`
	LEFT JOIN `UserHasGroup`
		ON `UserHasGroup`.UserID = `User`.ID
	LEFT JOIN (
	
	SELECT
				`Bet`.UserID AS UserID,
                
                SUM(IF(
                    (
                    (IF(`Bet`.`GoalsRegular1` >= `Match`.`GoalsRegular1`, `Bet`.`GoalsRegular1`-`Match`.`GoalsRegular1`, `Match`.`GoalsRegular1`-`Bet`.`GoalsRegular1`) = 1) 
                    AND 
                    (`Bet`.`GoalsRegular2` = `Match`.`GoalsRegular2`)
                    )
                OR
                    (
                    (IF(`Bet`.`GoalsRegular2` >= `Match`.`GoalsRegular2`, `Bet`.`GoalsRegular2`-`Match`.`GoalsRegular2`, `Match`.`GoalsRegular2`-`Bet`.`GoalsRegular2`) = 1) 
                    AND 
                    (`Bet`.`GoalsRegular1` = `Match`.`GoalsRegular1`)    
                    )
                    , 
                    IF(`Bet`.`Winner` = `Match`.`Winner`, 1, 3)
                    , 0)) AS punkte,                 
                    
				MAX(`Match`.`MatchDate`) AS MatchDate				
			FROM `Bet`

				JOIN `Match`
				ON `Bet`.MatchID = `Match`.ID AND `Match`.`Deleted` <> 1 AND `Match`.`Finished` = 1 ".($contestid != -1 ? " AND `Match`.ContestID = ".$contestid." " : "")."

			WHERE `Bet`.`Deleted` <> 1 AND `Bet`.`GroupID` = ".$groupid."
            GROUP BY `Bet`.UserID
		) PunkteTable
		ON PunkteTable.UserID = User.ID
	WHERE `UserHasGroup`.`GroupID` = ".$groupid." AND `User`.`Deleted` <> 1 AND `User`.`Active` = 1
    ORDER BY Punkte DESC
";	
	
	try{
		$return = array();
		$db = connection();
		$stmt = $db->prepare($sql);
		$stmt->execute();
		
		$count = 0;
		$position = 0;
		$punkte = -1;
	
		while($row = $stmt->fetch()) {
			$count++;
			if($row["Punkte"] != $punkte){
				$punkte = $row["Punkte"];
				$position = $count;
			}
			$row["Position"] = $position;
			$return["id".$row["UserID"]] = $row;
		}
		return $return;
	}
	catch(PDOException $e)
	{
		echo $e->getMessage();
		echo "<br />".$sql;
	}		
	

}

function getGroupsByUser($userid){
	
	$sql =@"	
	SELECT 
		`GroupID` AS GroupID, 
		`Group`.`Name` AS Name 
	FROM `UserHasGroup` 
		LEFT JOIN `Group` 
		ON `ID` = `GroupID` 
		AND `Group`.`Active` = 1 
		AND `Group`.`Deleted` <> 1 
	WHERE `UserID` = ".$userid."
	";
	
	return getMulti($sql);
	
}


?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<title>phifux - wetten</title>
<link rel="stylesheet" type="text/css" href="css/style.css?v=5"/>
<link rel="stylesheet" type="text/css" href="css/jquery-ui.css"/>
<script src="scripts/jquery-1.12.3.min.js"></script>
<script src="scripts/jquery-ui.js"></script>
<script src="../scripts/masonry.pkgd.min.js"></script>
<script src="scripts/main.js"></script>
</head>
<body>

<!-- Section Login Start -->

<div id="loginwrapper">
    <div id="loginrow">
        <div id="logincontainer">
			            
            <form method="POST" name="loginform" id="loginform">
                <input type="text" name="loginuser" placeholder="Benutzername" /><br />
                <input type="password" name="loginpassword" placeholder="Passwort" /><br />
 				<input type="checkbox" name="stayloggedin" />Remember me<br />               
                <button type="submit" name="login" value="login" >Login</button><br />
            </form> 
        </div>
    </div>
</div>

<!-- Section Login End -->


</body>
</html>