/functions.php

<?php



require("db_login.php");



error_reporting(0);



/**

 * takes an array with string values and makes them into one comma separated string

 * @param $array an array with strings

 * @return a comma separated string

 */

function commaseparate($array) { // Why not use implode? -- Viktor

   	

	$in_string = "";

	foreach ($array as $string) {

		$in_string .= "'" . $string . "',";

	}

	//the last comma should be removed, this feels wrong :) but works

	$in_string = substr_replace($in_string ,"",-1);

	

	return $in_string;

}



/**

 * Function returns false if one of the skills in $skills is not found (does not exist)

 * 

 * @param $skills array with skillnames (strings)

 * @return array with corresponding skill ids

 */

function get_skill_ids_by_name($skills) {

	

	$number_of_requested_skills = sizeof($skills);

	

	foreach ($skills as $skill) { 

    	$skills2[] = '"' . strtolower($skill) . '"'; // So that we can make a case-insensitive check against skills

    }

	

	$skills2 = implode(",",$skills2);

	

	$result = mysql_query("

		SELECT id, name

		FROM skills

		WHERE LOWER(name) IN (" . $skills2 . ")

	");

	

	$skill_ids = Array();

	

	if(!$result) {

		return false;

	}

	

	while ($skill_id = mysql_fetch_assoc($result)) {

		$skill_ids[] = $skill_id['id'];

	}

	

	if (sizeof($skill_ids) != $number_of_requested_skills) { //We didn't find as many skills as we searched for

		return false;

	}

	

	return $skill_ids;

}



/**

 * 

 * @param $skill_id

 * @return en skill som en assoc array

 */

function get_skill_by_id($skill_id) {

	$result = mysql_query("

		SELECT *

		FROM skills

		WHERE id = {$skill_id}

		LIMIT 1

	");

	

	return mysql_fetch_assoc($result);

}



/**

 * Will return all skills as an array.

 * @return array

 */

function get_skills() {

	

	$skills = Array();

	

	$result = mysql_query("

		SELECT *

		FROM skills

	");

	

	while($row = mysql_fetch_assoc($result)) {

		$skills[] = $row;

	}

	

	return $skills; 

}



/**

 * 

 * @param $skills array with skill names (strings)

 * @return array of people, they're experience and personal rating in a skill like this: $people['skillid']['score'] for example.

 */

function get_people_by_known_skills($skills) {

	

	$number_of_skills = sizeof($skills);

	

    $skills = get_skill_ids_by_name($skills);

    

    $people = Array();

    

    foreach($skills as $skill) {

	    $result = mysql_query("

	    	SELECT  people.id AS 'person_id',

			        skills.name as 'skill_name',

			        skills.id as 'skill_id',

			        person_has_skill.personal_rating,

			        SUM(experiences.hours * skill_used_in_experience.amount) /40 AS 'weeks_of_experience',

			        (SUM(experiences.hours * skill_used_in_experience.amount) /40) * person_has_skill.personal_rating AS 'score',

                 	CONCAT(people.first_name, ' ', people.surname) as name 

			FROM    experiences, 

			        people, 

			        skill_used_in_experience, 

			        person_has_skill,

                 	skills

			WHERE   experiences.person_id=people.id 

			AND     skill_used_in_experience.experience_id=experiences.id

			AND     person_has_skill.person_id = people.id

			AND     person_has_skill.skill_id = {$skill}

         	AND     person_has_skill.skill_id = skills.id

			GROUP BY people.id, skill_used_in_experience.skill_id

			HAVING   skill_used_in_experience.skill_id = {$skill}

			ORDER BY score DESC

	    ");

	    

	    if ($result) {

			while ($person = mysql_fetch_assoc($result)) {

				$people[strval($skill)][] = $person; //strval s? att det inte blir ett index utan en key

				$people_score["{$person['person_id']}"] += $person['score'];

			}

		} else {return false;}

	    

    }

    

	return array($people,$people_score);

}



/**

 * 

 * @param $people_ids array of people ids

 * @param $people_names array of corresponding people names

 */

function print_people($people_ids, $people_names) {

	

	if (!is_array($people_ids) || !is_array($people_names)) {

		echo '<li>Inga resultat</li>';

	} else {

		for ($i = 0; $i < sizeof($people_ids); $i++) {

			echo 	'<li><a href="?page=view&viewperson=' . 

					$people_ids[$i] . 

					'">' . 

					$people_names[$i] . 

					'</a></li>'

					;

		}

	}

}



/**

 * 

 * @param $people array of people ids

 * @return array of people names

 */

function get_people_names_by_id($people) {

	

	$people = implode(",",$people);

	

	$result = mysql_query("

		SELECT first_name, surname

		FROM people

		WHERE id IN (" . $people . ")

	");

	

	$people = Array();

	

	if ($result) {

		while ($person = mysql_fetch_assoc($result)) {

			$people[] = $person['first_name'] . " " . $person['surname'];

		}

	}

	

	return $people;

}



/**

 * 

 * @param $person_id

 * @return associative_array with 'experience_name', 'skills_used', 'experience_description' , 'experience_type' sorted in order of type

 */

function get_experiences_by_person_id($person_id) {

	

	$experiences = Array();

	

	$result = mysql_query("

		SELECT		experiences.`name` AS 'experience_name',

		            skills.`name` AS 'skills_used',

		            experiences.description AS 'experience_description',

		            experiences.experience_type AS 'experience_type'

		 FROM   	experiences, skill_used_in_experience, skills

		 WHERE  	skill_used_in_experience.experience_id = experiences.id

		 AND    	skills.id = skill_used_in_experience.skill_id

		 AND    	experiences.person_id = " . $person_id . "

		 ORDER BY 	experiences.experience_type DESC

	");

	

	if (!$result) {return false;}

	

	$count = 0;

	while($row = mysql_fetch_assoc($result)) {

		if ($count == 0) { // dirty cheap to solve the notice wrong offset blabla /antte & hasse

			$experiences[$count]['experience_name']			=	$row['experience_name'];

			$experiences[$count]['skills_used']				=	$row['skills_used'];

			$experiences[$count]['experience_description']	=	$row['experience_description'];

			$experiences[$count]['experience_type']			=	$row['experience_type'];

			$count++;

		} else {

			if ($experiences[$count-1]['experience_name'] == $row['experience_name']) {

				$experiences[$count-1]['skills_used'] .= ", " . $row['skills_used'];

			} else {

				$experiences[$count]['experience_name']			=	$row['experience_name'];

				$experiences[$count]['skills_used']				=	$row['skills_used'];

				$experiences[$count]['experience_description']	=	$row['experience_description'];

				$experiences[$count]['experience_type']			=	$row['experience_type'];

				$count++;

			}

		}

	}

	

	return $experiences;

	

}



/**

 * 

 * @param $people array of people ids

 * @return array of people names

 */

function get_people_names_by_ids($people) {

	

	if (!is_array($people)) {

		return false;

	}

	

	$people = implode(",", $people);

	

	$result = mysql_query("

		SELECT first_name, surname

		FROM people

		WHERE id IN (" . $people . ")

	");

	

	$people = Array();

	

	if ($result) {

		while ($person = mysql_fetch_assoc($result)) {

			$people[] = $person['first_name'] . " " . $person['surname'];

		}

	}

	

	return $people;

}



/**

 * 

 * @param $person_id

 * @return associative array with X

 */

function get_person($person_id) {

	

	$person_id = mysql_real_escape_string($person_id);

	

	$person = Array();

	

	$result = mysql_query("

		SELECT *

		FROM people

		WHERE id = " . $person_id . "

		LIMIT 1

	");

	

	return mysql_fetch_assoc($result); 

	

}



/**

 * 

 * @return array with all people

 */

function get_people() {

	$people = Array();

	

	$result = mysql_query("

		SELECT *

		FROM people

	");

	

	if (!$result) {return false;}

	

	while ($row = mysql_fetch_assoc($result)) {

		$people[] = $row;

	}

	

	return $people;

}





/**

 * 

 * @param $skill_id1 The id of a skill that relate to another skill($skill_id2)

 * @param $skill_id2 The id of a skill that $skill_id1 is compared to 

 * @param $ratio Float that in percentage represents how skill1 compares to skill2 

 * @return Bool whether the insert succeeded(true) or not(false)

 */

function insert_skill_relation($skill_id1,$skill_id2,$ratio) {

  

   if($ratio<0 || $ratio>1) { 

      // Att n??nting ?¤r mindre ?¤n 0% likt eller mer ?¤n 100% likt g??r ju inte an

      return false;

   }

   else {



     // Replace: finns en rad med samma "unique keypair" sl?¤ngs den. Om det inte finns s?? skapas en ny rad

     $sql = "REPLACE INTO skill_relates_to_skill

     (skill1_id, skill2_id, ratio)

     VALUES($skill_id1, $skill_id2, $ratio)";



     return mysql_query($sql);



   }



}



function add_skill($name,$description) {

	// Replace: finns en rad med samma "unique keypair" sl?¤ngs den. Om det inte finns s?? skapas en ny rad

    $sql = "INSERT INTO skills

    (name, description)

    VALUES('{$name}', '{$description}')";



    return mysql_query($sql);

	

}



function update_skill($id, $description) {

	$sql = "UPDATE skills

    SET description =  '{$description}'

    WHERE id = {$id}

    ";



    mysql_query($sql);

}



function get_skill_relation_by_skill_id($skill_id) {

	$result = mysql_query("

		SELECT *

		FROM skill_relates_to_skill

		WHERE skill1_id = {$skill_id}

	");

	

	$relations = Array();

	

	while($row = mysql_fetch_assoc($result)) {

		$relations[] = $row;

	}

	

	return $relations;

}	

function get_skills_by_person_id($person_id){

	

	$result = mysql_query ("

		SELECT skills.name

		FROM people, skills, person_has_skill

		WHERE people.id = $person_id

		AND person_has_skill.person_id = people.id 

		AND skills.id = person_has_skill.skill_id

		");

	

	$skills = Array();

	

	if ($result) {

		while ($skill = mysql_fetch_assoc($result)) {

			$skills[] = $skill['name'];

		}

	}

	

	return $skills;

}