<?php

require_once(dirname(__FILE__) . '/db.php');


function getPersonData($username, $password) {
	global $db_conn;
	$sql = 'select id, username, fullname, email, manageperson, addappointment, locale from person where username=\'' . pg_escape_string($username) . '\' and password=\'' . md5($password) . '\'';
	$r = pg_u_query($db_conn, $sql);
	if (pg_num_rows($r) != 1) return 0;
	return pg_fetch_assoc($r, 0);
}


// Returns a hash with the keys manageperson and addappointment with the values TRUE or FALSE.
// If the person is not found, FALSE is returned.
function getPersonRights($personid) {
	global $db_conn;
	$personid = (int) $personid;
	$sql = "select manageperson, addappointment from person where id=$personid";
	$r = pg_u_query($db_conn, $sql);
	if (pg_num_rows($r) != 1) return FALSE;
	return array_map('pg_bool', pg_fetch_assoc($r, 0));
}


function getAllPersons() {
	global $db_conn;
	$sql = 'select id, username, password, fullname, email, locale, manageperson, addappointment from person order by fullname';
	return pg_u_query_assoc($db_conn, $sql);
}


function getPerson($personnr) {
	global $db_conn;
	$personnr = (int) $personnr;
	$sql = 'select id, username, password, fullname, email, locale from person where id = ' . $personnr;
	return pg_u_query_assoc($db_conn, $sql);
}


// if $personid==NULL, all appointments will be returned.
// if $only_managed == TRUE, only the appointments will be returned, where the personid
// has the right to edit or delete that appointments. In this case $personid _must_ be specified,
// otherwise $only_managed will be ignored.
function getAppointments($personid=NULL, $only_managed=FALSE) {
	global $db_conn;
	$sql = 'select id, title, description, location, minvalue, maxvalue, case when approvaldesc=\'t\' then 1 else 0 end as approvaldesc, cssname, expire_date, creator_personid from appointment ';
	// $sql = 'select id, title, description, location, minvalue, maxvalue, approvaldesc, cssname, expire_date, creator_personid from appointment '; // ---debug---
	if ($personid) {
		$personid = pg_escape_string($personid);
		$sql .= "where id in (select appointmentid from permission where personid=$personid";
		if ($only_managed) $sql .= " and manageappointment='t'";
		$sql .= ') ';
	}
	$sql .= 'order by creation_date desc';	
	return pg_u_query_assoc($db_conn, $sql);
}


function getAppointment($id) {
	global $db_conn;
	$id = (int) $id;
	// $sql = "select title, description, location, minvalue, maxvalue, approvaldesc, cssname, expire_date from appointment where id = $id"; // ---debug---
	$sql = "select title, description, location, minvalue, maxvalue, case when approvaldesc='t' then 1 else 0 end as approvaldesc, cssname, expire_date from appointment where id = $id";
	$result = pg_u_query_assoc($db_conn, $sql);
	if (!count($result)) die("Appointment with id $id not found");
	return $result[0];
}


// If the person $personid participates in the appointment $appointmentid
// the function returns a hash with the keys 
//   manageperson
//   manageappointment
//   insertproposal
//   updateproposal
//   deleteproposal
// with the boolean values TRUE or FALSE.
// In all other cases, FALSE is returned.
function getAppointmentRights($appointmentid, $personid) {
	global $db_conn;
	$appointmentid = (int) $appointmentid;
	$personid = (int) $personid;
	$sql = "select manageperson, manageappointment, insertproposal, updateproposal, deleteproposal from permission where appointmentid=$appointmentid and personid=$personid";
	$result = pg_u_query_assoc($db_conn, $sql);
	if (count($result) != 1) return FALSE;
	return array_map('pg_bool', $result[0]);
}


function getProposal($proposalid) {
	global $db_conn;
	$proposalid = (int) $proposalid;
	$sql = "select id, to_char(date, 'Dy') as day, date, to_char(time, 'HH24:MI') as time, accepted, appointmentid from proposal where id = $proposalid";
	$resultarray = pg_u_query_assoc($db_conn, $sql);
	if (count($resultarray) == 0) die("Proposal with id $proposalid not found.");
	return $resultarray[0];
}


function getProposalDateTimeStr($proposalId, $formatStr='%Y-%m-%d %H:%M (%a)') {
	$proposal = getProposal($proposalId);
	$timestamp = strtotime($proposal['date'] . ' ' . $proposal['time']);
	return strftime($formatStr, $timestamp);
}


function getProposals($appointmentid) {
	global $db_conn;
	$appointmentid = (int) $appointmentid;
	$sql = "select id, to_char(date, 'Dy') as day, date, to_char(time, 'HH24:MI') as time, accepted, appointmentid from proposal where appointmentid = $appointmentid and date >= date(now()) order by date, time";
	return pg_u_query_assoc($db_conn, $sql);
}


function getParticipants($appointmentid) {
	global $db_conn;
	$appointmentid = (int) $appointmentid;
	$sql = "select id, fullname, email from person, permission where personid=id and appointmentid=$appointmentid order by fullname";
	return pg_u_query_assoc($db_conn, $sql);
}


function getResponses($proposalid) {
	global $db_conn;
	$proposalid = (int) $proposalid;
	$sql = "select personid, value, comment from (select participant.personid, value, comment from (select * from response where proposalid=$proposalid) as r right join (select personid from permission where appointmentid=(select appointmentid from proposal where id=$proposalid)) as participant on r.personid=participant.personid) as responses, person where id=personid order by fullname"; // select responses for _every_ person that belongs to the appointment (and assign null to value and comment if no response is there (using the right join)).
	return pg_u_query_assoc($db_conn, $sql);
}


/// Returns an array of responses in the form $responseArray[$proposalId][$personId] == $responseValue
/// if the optional parameter $createResponseArrayPerson is TRUE, the following type is created:
/// $responseArray[$personId][$proposalId] == $responseValue
function getResponseArray($appointmentid, $createResponseArrayPerson = FALSE) {
	$responseArray = array();
	$proposals = getProposals($appointmentid);
	foreach ($proposals as $proposal) {
		$proposalid = $proposal['id'];
		$responses = getResponses($proposalid);
		foreach ($responses as $response) {
			$personid = $response['personid'];
			$value = $response['value'];
			if ($createResponseArrayPerson) $responseArray[$personid][$proposalid] = $value;
			else $responseArray[$proposalid][$personid] = $value;
		}
	}
	return $responseArray;
}


/// Takes a responseArray as returned from getResponseArray ( $responseArray[$proposalId][$personId] == $responseValue )
/// and converts it to a responseArrayPerson ( $responseArray[$personId][$proposalId] == $responseValue )
function responseArrayToResponseArrayPerson($responseArray) {
	$responseArrayPerson = array();
	foreach ($responseArray as $proposalId => $responses) {
		foreach ($responses as $personId => $value) {
			$responseArrayPerson[$personId][$proposalId] = $value;
		}
	}
	return $responseArrayPerson;
}


// Analysis

/// Returns an array of proposal ids in the order the algorithm suggests ($proposalIdWinners).
/// In this case: Sorted by mean values.
/// \param $responseArray Array of arrays in this way: $responseArray[$proposalId][$personId] == $responseValue
/// \param $approvaldesc The lower the better
function analyzeMean($responseArray, $approvaldesc) {
	$meanArray = array();
	foreach ($responseArray as $proposalId => $responses) {
		$filtered_responses = array_filter($responses, "is_numeric");
		if (empty($filtered_responses)) continue;
		$meanArray[$proposalId] = array_sum($filtered_responses) / count($filtered_responses);
	}
	if ($approvaldesc) asort($meanArray); else arsort($meanArray); 
	$proposalIdWinners = array_keys($meanArray);
	$proposalIdMeanValues = array_values($meanArray);
	return array($proposalIdWinners, $proposalIdMeanValues);
}


/// Returns an array of proposal ids in the order the algorithm suggests ($proposalIdWinners).
/// In this case: Sorted by points (descending values) accoriding to the Negative Majority algorithm 
/// (see Nitzan and Bahard: The golden voting rule, Economic Theory, 2006 forthcoming. 
/// In the meantime: "Riener, Gerhard" <griene@essex.ac.uk>).
///
/// "Implementierung: die schlechteste alternative bekommt null punkte, alle anderen einen punkt, dann
/// zusammenzählen. Warum gut: Weil gut gegen majority tyranny"
///
/// \param $responseArray Array of arrays in this way: $responseArray[$proposalId][$personId] == $responseValue
/// \param $approvaldesc The lower the better
function analyzeNegativeMajority($responseArray, $approvaldesc) {
	return analyzeNegativeMajorityRap(responseArrayToResponseArrayPerson($responseArray), $approvaldesc);
}


/// Same as analyzeNegativeMajority but take a $responseArrayPerson array.
function analyzeNegativeMajorityRap($responseArrayPerson, $approvaldesc) {
	$nmArray = array();
	foreach ($responseArrayPerson as $responses) {
		$filtered_responses = array_filter($responses, "is_numeric");
		if (empty($filtered_responses)) continue;
		$worstValue = $approvaldesc ? max($filtered_responses) : min($filtered_responses);
		foreach($filtered_responses as $proposalId => $value) {
			if (!isset($nmArray[$proposalId])) $nmArray[$proposalId] = 0;
			if ($approvaldesc) $nmArray[$proposalId] += ($value < $worstValue ? 1 : 0);
			else               $nmArray[$proposalId] += ($value > $worstValue ? 1 : 0);
		}
	}
	arsort($nmArray); 
	$proposalIdWinners = array_keys($nmArray);
	$proposalIdValues = array_values($nmArray);
	return array($proposalIdWinners, $proposalIdValues);
}


function analyzeCondorcet($responseArray, $approvaldesc) {
	return analyzeCondorcetRap(responseArrayToResponseArrayPerson($responseArray), $approvaldesc);
}


// private helper function of analyzeCondorcetRap
// - $currentPath is an array, starting with $proposalIdFrom and hopefully reaches $proposalIdTo sometimes.
// - $maxPower is the maximum power reached until now.
// - The function returns the new power value if it is higher than $maxPower or $maxPower otherwise.
function analyzeCondorcetRap_calcPower($currentPath, $proposalIdTo, $cm, $maxPower = 0) {
	foreach (array_keys($cm) as $proposalIdCandidate) {
		// We have a candidate for the next step in the path

		// Special case: The candidate was already used -> continue
		if (in_array($proposalIdCandidate, $currentPath)) continue;

		// Check if we are allowed to use the candidate
		$proposalIdCurrent = end($currentPath);
		if ($cm[$proposalIdCurrent][$proposalIdCandidate] < $cm[$proposalIdCandidate][$proposalIdCurrent]) continue;

		// Ok, we are allowed. So is the candidate the "To" proposalId?
		$currentPath[] = $proposalIdCandidate;
		if ($proposalIdCandidate == $proposalIdTo) {
			// yeah, we have a valid path!
			// calculate power of this path
			for ($i = 0; $i < count($currentPath)-1; $i++) $h[] = $cm[$currentPath[$i]][$currentPath[$i+1]];
			$power = min($h);
			// if we have a new power high score, then remember it.
			if ($power > $maxPower) $maxPower = $power;
		} else {
			$maxPower = analyzeCondorcetRap_calcPower($currentPath, $proposalIdTo, $cm, $maxPower);
		}
	}
	return $maxPower;
}


// http://de.wikipedia.org/wiki/Schulze-Methode
//
// Abbreviations:
// - $cm ... condorcet matrix with integer entries: $cm[x][y]: x wins $cm[x][y] times over y.
// - $pm ... integer matrix with the power (Stärke) of the strongest path from x to y: $pm[x][y]: The strongest path from x to y has the power $pm[x][y].
//
// Return values
// Array with two entries:
// - array of winner-proposalIds (hopefully only one but we could have more than one winner)
// - the $cm matrix
function analyzeCondorcetRap($responseArrayPerson, $approvaldesc) {
	// Build condorcet matrix $cm
	foreach ($responseArrayPerson as $responses) {
		foreach ($responses as $proposalIdX => $valueX) {
			foreach ($responses as $proposalIdY => $valueY) {
				if (!isset($cm[$proposalIdX][$proposalIdY])) $cm[$proposalIdX][$proposalIdY] = 0;
				if ($approvaldesc) {
					if ($valueX < $valueY) $cm[$proposalIdX][$proposalIdY] += 1;
				} else {
					if ($valueX > $valueY) $cm[$proposalIdX][$proposalIdY] += 1;
				}
			}
		}
	}
	/*
	echo "<pre>";
	var_dump($cm);
	echo "</pre>";
	*/

	// Build $pm matrix of strongest winners
	foreach ($cm as $proposalIdX => $cmc) {
		foreach (array_keys($cmc) as $proposalIdY) {
			if ($proposalIdX == $proposalIdY) continue;
			$pm[$proposalIdX][$proposalIdY] = analyzeCondorcetRap_calcPower(array($proposalIdX), $proposalIdY, $cm);
		}
	}
	/*
	echo "<pre>";
	var_dump($pm);
	echo "</pre>";
	*/

	// Build array of possible winners
	$winners = $pm;
	foreach ($pm as $proposalIdX => $pmc) {
		foreach (array_keys($pmc) as $proposalIdY) {
			if ($pm[$proposalIdX][$proposalIdY] < $pm[$proposalIdY][$proposalIdX]) {unset($winners[$proposalIdX]); break;}
		}
	}
	return array(array_keys($winners), $cm);
}


?>