<?php

/**
 * @author Antonio Duran
 * @license http://www.gnu.org/copyleft/gpl.html GNU Public License
 * @package moodle multiauth
 *
 * Authentication Plugin: Joomdle
 *
 * Checks against Joomla web services provided my Joomdle
 *
 * 2009-10-25  File created.
 */

if (!defined('MOODLE_INTERNAL')) {
    die('Direct access to this script is forbidden.');    ///  It must be included from a Moodle page
}

require_once($CFG->libdir.'/authlib.php');
require_once($CFG->dirroot.'/auth/db/auth.php');
require_once($CFG->dirroot.'/search/lib.php');
require_once($CFG->dirroot.'/calendar/lib.php');
require_once($CFG->dirroot.'/mod/forum/lib.php');
require_once($CFG->dirroot.'/lib/datalib.php');

/**
 * Joomdle authentication plugin.
 */
class auth_plugin_joomdle extends auth_plugin_db {

    /**
     * Constructor.
     */
    function auth_plugin_joomdle() {
        $this->authtype = 'joomdle';
        $this->config = get_config('', 'joomla_url');
	$this->config = get_config('auth/joomdle'); //XXX
        if (empty($this->config->extencoding)) {
            $this->config->extencoding = 'utf-8';
        }
    }

    /**
     * Returns true if the username and password work and false if they are
     * wrong or don't exist.
     *
     * @param string $username The username (with system magic quotes)
     * @param string $password The password (with system magic quotes)
     *
     * @return bool Authentication success or failure.
     */
    function user_login($username, $password) {
	    return false;

    }

	function can_change_password() {
		return false;
	}

    /**
     * Prints a form for configuring this authentication plugin.
     *
     * This function is called from admin/auth.php, and outputs a full page with
     * a form for configuring this plugin.
     *
     * @param array $page An object containing all the data for this page.
     */
    function config_form($config, $err, $user_fields) {
        $this->config = get_config('', 'joomla_url');
        include "config.html";
    }

    function process_config($config) {
		if (!isset($config->joomla_url)) {
			$config->joomla_url = 'http://localhost/joomla';
				            }

	    set_config('joomla_url', $config->joomla_url); 

	    return true; //XXX
    }


    function test () {
	    return "Moodle web services are working!";
    }

    function my_courses ($username) {
        global $CFG;
	
	$user = get_complete_user_data ('username', $username);
	$c = get_my_courses ($user->id);
        return $c;
    }

    /**
      * Returns course list
      * 
      * @param int $available If true, return only enrollable courses
      */
    function list_courses ($available = 0) {
	global $CFG;

	if ($available) {
		$query =
		    "SELECT
			co.id          AS remoteid,
			ca.id          AS cat_id,
			ca.name        AS cat_name,
			ca.description AS cat_description,
			co.sortorder,
			co.fullname,
			co.shortname,
			co.idnumber,
			co.summary,
			co.startdate,
			co.cost,
			co.currency,
			co.defaultrole AS defaultroleid,
			r.name         AS defaultrolename 
		    FROM
			{$CFG->prefix}course_categories ca
		    JOIN
			{$CFG->prefix}course co ON
			ca.id = co.category
		    LEFT JOIN
			{$CFG->prefix}role r ON
			r.id = co.defaultrole
		    WHERE
			co.visible = '1' AND
			co.enrollable = '1'
		    ORDER BY
			sortorder ASC
			";
	} else {
		$query =
		    "SELECT
			co.id          AS remoteid,
			ca.id          AS cat_id,
			ca.name        AS cat_name,
			ca.description AS cat_description,
			co.sortorder,
			co.fullname,
			co.shortname,
			co.idnumber,
			co.summary,
			co.startdate,
			co.cost,
			co.currency,
			co.defaultrole AS defaultroleid,
			r.name         AS defaultrolename 
		    FROM
			{$CFG->prefix}course_categories ca
		    JOIN
			{$CFG->prefix}course co ON
			ca.id = co.category
		    LEFT JOIN
			{$CFG->prefix}role r ON
			r.id = co.defaultrole
		    WHERE
			co.visible = '1'
		    ORDER BY
			sortorder ASC
			";
	}

	return get_records_sql($query);

    }

    /**
      * Returns course category list
      * 
      * @param int $cat Parent category
      */
    function get_course_categories ($cat = 0) {
	global $CFG;

	$query =
	    "SELECT *
	    FROM
		{$CFG->prefix}course_categories
	    WHERE
		visible = '1' AND
		parent = '$cat'
	    ORDER BY
		sortorder ASC
		";

	return get_records_sql($query);

    }


    /**
      * Returns courses from a specific category
      * 
      * @param string $category Category name
      * @param int $available If true, return only enrollable courses
      */
    function courses_by_category ($category, $available = 0) {
	global $CFG;

	if ($available) {
		$query =
		    "SELECT
			co.id          AS remoteid,
			ca.id          AS cat_id,
			ca.name        AS cat_name,
			ca.description AS cat_description,
			co.sortorder,
			co.fullname,
			co.shortname,
			co.idnumber,
			co.summary,
			co.startdate,
			co.cost,
			co.currency,
			co.defaultrole AS defaultroleid,
			r.name         AS defaultrolename 
		    FROM
			{$CFG->prefix}course_categories ca
		    JOIN
			{$CFG->prefix}course co ON
			ca.id = co.category
		    LEFT JOIN
			{$CFG->prefix}role r ON
			r.id = co.defaultrole
		    WHERE
			co.visible = '1' AND
			co.enrollable = '1' AND
			ca.id = '$category'
		    ORDER BY
			sortorder ASC
			";
	} else {
		$query =
		    "SELECT
			co.id          AS remoteid,
			ca.id          AS cat_id,
			ca.name        AS cat_name,
			ca.description AS cat_description,
			co.sortorder,
			co.fullname,
			co.shortname,
			co.idnumber,
			co.summary,
			co.startdate,
			co.cost,
			co.currency,
			co.defaultrole AS defaultroleid,
			r.name         AS defaultrolename 
		    FROM
			{$CFG->prefix}course_categories ca
		    JOIN
			{$CFG->prefix}course co ON
			ca.id = co.category
		    LEFT JOIN
			{$CFG->prefix}role r ON
			r.id = co.defaultrole
		    WHERE
			co.visible = '1' AND
			ca.id = '$category'
		    ORDER BY
			sortorder ASC
			";
	}

	return get_records_sql($query);

    }

    /**
      * Returns courses available for guests
      * 
      * @param int $guest has the same meaning as in Moodle: 1, for non-registered guests, 2: only users with an account
     */
    function guest_courses ($guest = 1) {
	global $CFG;

	$query =
	    "SELECT
		co.id          AS remoteid,
		ca.id          AS cat_id,
		ca.name        AS cat_name,
		ca.description AS cat_description,
		co.sortorder,
		co.fullname,
		co.shortname,
		co.idnumber,
		co.summary,
		co.startdate,
		co.cost,
		co.currency,
		co.defaultrole AS defaultroleid,
		r.name         AS defaultrolename 
	    FROM
		{$CFG->prefix}course_categories ca
	    JOIN
		{$CFG->prefix}course co ON
		ca.id = co.category
	    LEFT JOIN
		{$CFG->prefix}role r ON
		r.id = co.defaultrole
	    WHERE
		co.visible = '1' AND
		co.enrollable = '1' AND
		co.guest = '$guest'
	    ORDER BY
		sortorder ASC
		";

	return get_records_sql($query);

    }


    /**
      * Returns courses that have their enrolment period between two dates
      * 
      * @param int $start_date Interval start, Unix Timestamp format
      * @param int $end_date Interval end, Unix Timestamp format
      */
    function courses_by_date ($start_date, $end_date) {
	global $CFG;

		$query =
		    "SELECT
			co.id          AS remoteid,
			ca.id          AS cat_id,
			ca.name        AS cat_name,
			ca.description AS cat_description,
			co.sortorder,
			co.fullname,
			co.shortname,
			co.idnumber,
			co.summary,
			co.startdate,
			co.cost,
			co.currency,
			co.defaultrole AS defaultroleid,
			r.name         AS defaultrolename 
		    FROM
			{$CFG->prefix}course_categories ca
		    JOIN
			{$CFG->prefix}course co ON
			ca.id = co.category
		    LEFT JOIN
			{$CFG->prefix}role r ON
			r.id = co.defaultrole
		    WHERE
			co.visible = '1' AND
			co.enrollable = '2' AND
			enrolstartdate >= '$start_date' AND
			enrolenddate <= '$end_date'
		    ORDER BY
			sortorder ASC
			";

	return get_records_sql($query);
    }

    /**
      * Returns detailed info aboout a course
      * 
      * @param int $id Course identifier
      */
    function get_course_info ($id) {
	global $CFG;

	$query =
	    "SELECT
		co.id          AS remoteid,
		ca.id          AS cat_id,
		ca.name        AS cat_name,
		ca.description AS cat_description,
		co.sortorder,
		co.fullname,
		co.shortname,
		co.idnumber,
		co.summary,
		co.startdate,
		co.cost,
		co.currency,
		co.numsections,
		co.enrolstartdate,
		co.enrolenddate,
		co.enrolperiod,
		co.defaultrole AS defaultroleid,
		r.name         AS defaultrolename 
	    FROM
		{$CFG->prefix}course_categories ca
	    JOIN
		{$CFG->prefix}course co ON
		ca.id = co.category
	    LEFT JOIN
		{$CFG->prefix}role r ON
		r.id = co.defaultrole
	    WHERE
		co.id = '$id'
	    ORDER BY
		sortorder ASC
		";

	return get_record_sql($query);

    }

    /**
      * Returns course topics
      * 
      * @param int $id Course identifier
      */
    function get_course_contents ($id) {
	global $CFG;

	$query =
	    "SELECT
		cs.section,
		cs.summary
	    FROM
		{$CFG->prefix}course_sections cs
	    WHERE
		cs.course = '$id'
		";

	    // get_records_sql necesita un ID unico en la primera columna devuelta
	return get_records_sql($query);

    }

    /**
      * Returns editing teachers
      * 
      * @param int $id Course identifier
      */
    function get_course_editing_teachers ($id) {
	global $CFG;

	$context = get_context_instance(CONTEXT_COURSE, $id);
	/* 3 indica profesores editores (table mdl_role) */
	$profs = get_role_users(3 , $context);

	return $profs;
    }

    /**
      * Returns non-editing teachers
      * 
      * @param int $id Course identifier
      */
    function get_course_non_editing_teachers ($id) {
	global $CFG;

	$context = get_context_instance(CONTEXT_COURSE, $id);
	/* 4 indica profesores no editores (table mdl_role) */
	$profs = get_role_users(4 , $context);

	return $profs;
    }

    /**
      * Returns number of visible courses
      * 
      */
    function get_course_no () {
	global $CFG;

	$query =
	    "SELECT count(*)
	    FROM
		{$CFG->prefix}course_categories ca
	    JOIN
		{$CFG->prefix}course co ON
		ca.id = co.category
	    LEFT JOIN
		{$CFG->prefix}role r ON
		r.id = co.defaultrole
	    WHERE
		co.visible = '1'
		";

	return count_records_sql($query);

    }

    /**
      * Returns number of visible and enrollable courses
      * 
      */
    function get_enrollable_course_no () {
	global $CFG;

	$query =
	    "SELECT count(*)
	    FROM
		{$CFG->prefix}course_categories ca
	    JOIN
		{$CFG->prefix}course co ON
		ca.id = co.category
	    LEFT JOIN
		{$CFG->prefix}role r ON
		r.id = co.defaultrole
	    WHERE
		co.visible = '1' and
		co.enrollable = '1'
		";

	return count_records_sql($query);

    }

    /**
      * Returns student number
      * 
      */
    function get_student_no () {
	global $CFG;

	$query =
		"select count(distinct (userid)) from  {$CFG->prefix}role_assignments where roleid=5;
		";

	return count_records_sql($query);

    }

    /**
      * Returns number of submitted assignments in each task of the course
      *
      * @param int $id Course identifier
      * 
      */
    function get_assignment_submissions ($id) {
        global $CFG;

        /* Obtenemos todas las tareas del curso */
        $query =
                "select id,name from  {$CFG->prefix}assignment where course='$id';
                ";
        /* Para cada una, obtenemos el numero de trabajos entregados */

        $tareas = get_records_sql($query);

        $i = 0;
        foreach ($tareas as $tarea)
        {
                $ass_id = $tarea->id;
                $query =
                        "select count(*) from  {$CFG->prefix}assignment_submissions where assignment='$ass_id';
                        ";
                $n = count_records_sql($query);
                $rdo[$i]['id'] = $tarea->id;
                $rdo[$i]['tarea'] = $tarea->name;
                $rdo[$i]['entregados'] = $n;
                $i++;
        }

        return $rdo;
    }

    /**
      * Returns number of submitted assignments in a course
      * 
      * @param int $id Course identifier
      */
    function get_total_assignment_submissions ($id) {
        global $CFG;

        $query =
		"select count(*) from  {$CFG->prefix}assignment_submissions;
                ";

	$n = count_records_sql($query);

        return $n;
    }

    /**
      * Returns average grade for a task
      * 
      * @param int $id Course identifier
      */
    function get_assignment_grades ($id) {
        global $CFG;

	$SQL = "SELECT g.itemid, gi.itemname as iname,SUM(g.finalgrade) AS sum
		      FROM {$CFG->prefix}grade_items gi
			   JOIN {$CFG->prefix}grade_grades g      ON g.itemid = gi.id
		     WHERE gi.courseid = '$id'
			   AND g.finalgrade IS NOT NULL
		  GROUP BY g.itemid";
	    $sum_array = array();
	    if ($sums = get_records_sql($SQL)) {
		foreach ($sums as $itemid => $csum) {
		    $sql = " select count(*) from mdl_grade_grades where itemid=$itemid;";
	    $n = count_records_sql($sql);
		    //$sum_array[$itemid] = $csum->sum / $n;
		    $sum_array[$csum->iname] = $csum->sum / $n;
		}
	    }

	return $sum_array;
  }

    /**
      * Returns stats about student grades
      * FIXME doc return type
      * 
      * @param int $id Course identifier
      */
    function get_assignments_grades ($id) {
        global $CFG;

        /* Obtenemos todas las tareas del curso */
        $query =
                "select id,name from  {$CFG->prefix}assignment where course='$id';
                ";
        /* Para cada una, obtenemos la nota media */

        $tareas = get_records_sql($query);

        $i = 0;
        foreach ($tareas as $tarea)
        {
                $ass_id = $tarea->id;
                $query =
                        "select itemid,avg(finalgrade) as media
			from  {$CFG->prefix}grade_grades 
			where itemid='$ass_id' and
			finalgrade is not NULL
			GROUP BY itemid;
                        ";
                $n = get_records_sql($query);
                $rdo[$i]['tarea'] = $tarea->name;
		foreach ($n as $nn)
			$rdo[$i]['media'] = $nn;
                $i++;
        }

        return $rdo;
    }

    /**
      * Returns grades of a student for each task in a course
      * 
      * @param string $user Username
      * @param int $cid Course identifier
      */
    function get_user_grades ($user,$cid) {
        global $CFG;

        $user = get_complete_user_data ('username', $user);
        $uid = $user->id;


 $SQL = "SELECT g.itemid, g.finalgrade,gi.courseid,gi.itemname
                      FROM {$CFG->prefix}grade_items gi
                           JOIN {$CFG->prefix}grade_grades g      ON g.itemid = gi.id
                           JOIN {$CFG->prefix}user u              ON u.id = g.userid
                           JOIN {$CFG->prefix}role_assignments ra ON ra.userid = u.id
                           $groupsql
                     WHERE g.finalgrade IS NOT NULL
			   AND u.id = '$uid'
			   AND gi.courseid ='$cid'
                  GROUP BY g.itemid";


	    $sum_array = array();
	    if ($sums = get_records_sql($SQL)) {
	return $sums;
		foreach ($sums as $itemid => $csum) {
		    $sum_array[$csum->iname] = $csum->sum;
			
		}
	    }

	return $sum_array;
    }

    /**
      * Retursn number of enrolled students in a course
      * 
      * @param int $id Course identifier
      */
    function get_course_students_no($id) {
	global $CFG;

	$context = get_context_instance(CONTEXT_COURSE, $id);
	/* 5 indica estudiantes (table mdl_role) */
	$alumnos = get_role_users(5 , $context);

	return count($alumnos);
    }

    /**
      * Returns upcoming events for a course
      * 
      * @param int $id Course identifier
      */
    function get_upcoming_events ($id) {
	$courseshown = $id;
	$filtercourse    = array($courseshown => $id);
	$groupeventsfrom = array($courseshown => 1);

        calendar_set_filters($courses, $group, $user, $filtercourse, $groupeventsfrom, true);
        $events = calendar_get_upcoming($courses, $group, $user,
	get_user_preferences('calendar_lookahead', CALENDAR_UPCOMING_DAYS),
	get_user_preferences('calendar_maxevents', CALENDAR_UPCOMING_MAXEVENTS));


	return ($events);
    }

    /**
      * Returns last news for a course
      * 
      * @param int $id Course identifier
      */
    function get_news_items ($id) {
        $COURSE = get_record('course', 'id', $id);

	if (!$forum = forum_get_course_forum($COURSE->id, 'news')) {
		return '';
	}

	$modinfo = get_fast_modinfo($COURSE);
	if (empty($modinfo->instances['forum'][$forum->id])) {
		return '';
	}
	$cm = $modinfo->instances['forum'][$forum->id];

	$context = get_context_instance(CONTEXT_MODULE, $cm->id);

        /// User must have perms to view discussions in that forum
	if (!has_capability('mod/forum:viewdiscussion', $context)) {
		return '';
	}

        /// Get all the recent discussions we're allowed to see

	if (! $discussions = forum_get_discussions($cm, 'p.modified DESC', false,
                                                       $currentgroup, $COURSE->newsitems) ) {
		$text .= '('.get_string('nonews', 'forum').')';
                return '';
	}

	return $discussions;
}


    /**
      * Returns daily stats for a course
      * 
      * @param int $id Course identifier
      */
    function get_course_daily_stats ($id) {
        global $CFG;

        $query =
		"select * from {$CFG->prefix}stats_daily 
		where courseid='$id'
		and roleid='5'
		and stattype='activity';
                ";

	$stats = get_records_sql($query);

	return $stats;
    }

    /**
      * Return access daily stats
      * 
      */
    function get_site_last_week_stats () {
        global $CFG;

        $query =
		"select * from {$CFG->prefix}stats_weekly
		where stattype='logins' 
		order by timeend DESC LIMIT 1;
                ";

	$stats = get_records_sql($query);

	return $stats;
    }

    /**
      * Returns grading system for a course
      * 
      * @param int $id Course identifier
      */
    function get_course_grade_categories ($id) {
        global $CFG;
        $query =
		"select mdl_grade_categories.fullname, mdl_grade_items.grademin, mdl_grade_items.grademax 
		from mdl_grade_categories, mdl_grade_items 
		where mdl_grade_categories.id = mdl_grade_items.iteminstance 
		and mdl_grade_items.courseid='$id' and itemtype='category';
                ";
		
	$cats = get_records_sql($query);

	return $cats;
    }

/*
   XXX DISCARD
function joomdle_login ($username, $token)
{
	$request = xmlrpc_encode_request("joomdle.confirmJoomlaSession", array ($username, $token));
	$context = stream_context_create(array('http' => array(
	    'method' => "POST",
	    'header' => "Content-Type: text/xml ",
	    'content' => $request
	)));
	$joomla_xmlrpc_url = get_config (NULL, 'joomla_url').'/xmlrpc/index.php';
	$response = file_get_contents($joomla_xmlrpc_url, false, $context);
	$logged = xmlrpc_decode($response);

	if (xmlrpc_is_fault($logged))
		return 0;
	if (!$logged)
		return 0;

	$user = get_complete_user_data('username', $username);
	
	if (!$user)
		return 0;

	$a = complete_user_login($user);

	return 1;
}
*/

/**
* Creates a new Joomdle user
* XXX Also used to update user profile if the user already exists
* 
* @param string $username Joomla username
*/
function create_joomdle_user ($username)
{
	/* Creamos el nuevo usuario de Moodle si no está creado */
	$user = get_record('user','username',$username);
	if (!$user)
		$user = create_user_record($username, "", "joomdle");

	/* Obtenemos la información del usuario en Joomla */
	$joomla_xmlrpc_url = get_config (NULL, 'joomla_url').'/xmlrpc/index.php';
	$request = xmlrpc_encode_request("joomdle.getUserInfo", array ($username));

	$context = stream_context_create(array('http' => array(
	    'method' => "POST",
	    'header' => "Content-Type: text/xml ",
	    'content' => $request
	)));
	$response = file_get_contents($joomla_xmlrpc_url, false, $context);
	$juser_info = xmlrpc_decode($response);

	$email = $juser_info['email'];
	$firstname = $juser_info['firstname'];
	$lastname = $juser_info['lastname'];
	$city = $juser_info['city'];
	$country = $juser_info['country'];
	$lang = $juser_info['lang'];
	$timezone = $juser_info['timezone'];
	$phone1 = $juser_info['phone1'];
	$phone2 = $juser_info['phone2'];
	$address = $juser_info['address'];
	$description = $juser_info['description'];
	$institution = $juser_info['institution'];
	$url = $juser_info['url'];

	if (!xmlrpc_is_fault($response)) {

		/* Actualizamos la informacion del usuario recien creado con los datos de Joomla */
		set_field('user', 'firstname', utf8_encode($firstname), 'id', $user->id);
		set_field('user', 'lastname', utf8_encode($lastname), 'id', $user->id);
		set_field('user', 'email', $email, 'id', $user->id);
		/* Optional data in Joomla, only fill if has a value */
		if ($city)
			set_field('user', 'city', utf8_encode($city), 'id', $user->id);
		if ($country)
			set_field('user', 'country', $country, 'id', $user->id);
		if ($lang)
			set_field('user', 'lang', $lang, 'id', $user->id);
		if ($timezone)
			set_field('user', 'timezone', $timezone, 'id', $user->id);
		if ($phone1)
			set_field('user', 'phone1', utf8_encode($phone1), 'id', $user->id);
		if ($phone2)
			set_field('user', 'phone2', utf8_encode($phone2), 'id', $user->id);
		if ($address)
			set_field('user', 'address', utf8_encode($address), 'id', $user->id);
		if ($description)
			set_field('user', 'description', utf8_encode($description), 'id', $user->id);
		if ($institution)
			set_field('user', 'institution', utf8_encode($institution), 'id', $user->id);
		if ($url)
			set_field('user', 'url', $url, 'id', $user->id);
	}

	return $user;
}

function search_courses ($text, $phrase, $ordering, $limit)
{
        global $CFG;

      $wheres = array();
        switch ($phrase) {
                case 'exact':
                        $text           = '\'%'.search_escape_string( $text).'%\'';
                        $wheres2        = array();
                        $wheres2[]      = 'co.fullname LIKE '.$text;
                        $wheres2[]      = 'co.shortname LIKE '.$text;
                        $wheres2[]      = 'co.summary LIKE '.$text;
                        $where          = '(' . implode( ') OR (', $wheres2 ) . ')';
                        break;

                case 'all':
                case 'any':
                default:
                        $words = explode( ' ', $text );
                        $wheres = array();
                        foreach ($words as $word) {
				$word           = '\'%'.search_escape_string( $word).'%\'';
                                $wheres2        = array();
				$wheres2[]      = 'co.fullname LIKE '.$word;
				$wheres2[]      = 'co.shortname LIKE '.$word;
				$wheres2[]      = 'co.summary LIKE '.$word;
                                $wheres[]       = implode( ' OR ', $wheres2 );
                        }
                        $where = '(' . implode( ($phrase == 'all' ? ') AND (' : ') OR ('), $wheres ) . ')';
                        break;
        }

	switch ( $ordering ) {
                case 'alpha':
                        $order = 'co.fullname ASC';
                        break;

                case 'category':
                        $order = 'ca.name ASC, co.fullname ASC';
                        break;

                case 'newest':
                        $order = 'co.startdate DESC';
                        break;
                case 'oldest':
                        $order = 'co.startdate ASC';
                        break;
                case 'popular':
                default:
                        $order = 'co.fullname DESC';
        }

	$query =
	    "SELECT
		co.id          AS remoteid,
		ca.id          AS cat_id,
		ca.name        AS cat_name,
		ca.description AS cat_description,
		co.sortorder,
		co.fullname,
		co.shortname,
		co.idnumber,
		co.summary,
		co.startdate,
		co.cost,
		co.currency,
		co.defaultrole AS defaultroleid,
		r.name         AS defaultrolename 
	    FROM
		{$CFG->prefix}course_categories ca
	    JOIN
		{$CFG->prefix}course co ON
		ca.id = co.category
	    LEFT JOIN
		{$CFG->prefix}role r ON
		r.id = co.defaultrole
	    WHERE
		co.visible = '1' AND
		$where
	    ORDER BY
		$order
	    LIMIT $limit
		";

	return get_records_sql($query);
}

function search_categories ($text, $phrase, $ordering, $limit)
{
        global $CFG;

      $wheres = array();
        switch ($phrase) {
                case 'exact':
                        $text           = '\'%'.search_escape_string( $text).'%\'';
                        $wheres2        = array();
                        $wheres2[]      = 'ca.name LIKE '.$text;
                        $wheres2[]      = 'ca.description LIKE '.$text;
                        $where          = '(' . implode( ') OR (', $wheres2 ) . ')';
                        break;

                case 'all':
                case 'any':
                default:
                        $words = explode( ' ', $text );
                        $wheres = array();
                        foreach ($words as $word) {
				$word           = '\'%'.search_escape_string( $word).'%\'';
                                $wheres2        = array();
				$wheres2[]      = 'ca.name LIKE '.$word;
				$wheres2[]      = 'ca.description LIKE '.$word;
                                $wheres[]       = implode( ' OR ', $wheres2 );
                        }
                        $where = '(' . implode( ($phrase == 'all' ? ') AND (' : ') OR ('), $wheres ) . ')';
                        break;
        }

	switch ( $ordering ) {
                case 'alpha':
                case 'category':
                        $order = 'ca.name ASC';
                        break;
                case 'newest':
                case 'oldest':
                case 'popular':
                default:
                        $order = 'ca.name DESC';
        }

	$query =
	    "SELECT
		ca.id          AS cat_id,
		ca.name        AS cat_name,
		ca.description AS cat_description
	    FROM
		{$CFG->prefix}course_categories ca
	    WHERE
		$where
	    ORDER BY
		$order
	    LIMIT $limit
		";

	return get_records_sql($query);
}

/* Esta seria para enlazar a moodle, con una consulta mas ligera a la BD */
function search_topicsX ($text, $phrase, $ordering, $limit)
{
        global $CFG;

      $wheres = array();
        switch ($phrase) {
                case 'exact':
                        $text           = '\'%'.search_escape_string( $text).'%\'';
                        $where      = 'cs.summary LIKE '.$text;
                        break;

                case 'all':
                case 'any':
                default:
                        $words = explode( ' ', $text );
                        $wheres = array();
                        foreach ($words as $word) {
				$word           = '\'%'.search_escape_string( $word).'%\'';
				$wheres[]      = 'cs.summary LIKE '.$word;
                        }
                        $where = '(' . implode( ($phrase == 'all' ? ') AND (' : ') OR ('), $wheres ) . ')';
                        break;
        }

	switch ( $ordering ) {
                case 'alpha':
                        $order = 'cs.summary ASC';
                        break;
                case 'category':
                        $order = 'co.id ASC';
                        break;
                case 'newest':
                        $order = 'co.id ASC, cs.section DESC';
                        break;
                case 'oldest':
                        $order = 'co.id ASC, cs.section ASC';
                        break;
                case 'popular':
                default:
                        $order = 'cs.summary DESC';
        }

	/* REMEMBER: For get_records_sql First field in query must be UNIQUE!!!!! */
	$query =
	    "SELECT cs.id,
		co.id          AS remoteid,
		co.fullname,
		cs.course,
		cs.section,
		cs.summary
	    FROM
		{$CFG->prefix}course_sections cs 
	JOIN {$CFG->prefix}course co  ON
		co.id = cs.course 
	    WHERE
		$where
	    ORDER BY
		$order
	    LIMIT $limit
		";
	
	return get_records_sql($query);
}

function search_topics ($text, $phrase, $ordering, $limit = 50)
{
        global $CFG;

      $wheres = array();
        switch ($phrase) {
                case 'exact':
                        $text           = '\'%'.search_escape_string( $text).'%\'';
                        $where      = 'cs.summary LIKE '.$text;
                        break;

                case 'all':
                case 'any':
                default:
                        $words = explode( ' ', $text );
                        $wheres = array();
                        foreach ($words as $word) {
				$word           = '\'%'.search_escape_string( $word).'%\'';
				$wheres[]      = 'cs.summary LIKE '.$word;
                        }
                        $where = '(' . implode( ($phrase == 'all' ? ') AND (' : ') OR ('), $wheres ) . ')';
                        break;
        }

	switch ( $ordering ) {
                case 'alpha':
                        $order = 'cs.summary ASC';
                        break;
                case 'category':
                        $order = 'co.id ASC';
                        break;
                case 'newest':
                        $order = 'co.id ASC, cs.section DESC';
                        break;
                case 'oldest':
                        $order = 'co.id ASC, cs.section ASC';
                        break;
                case 'popular':
                default:
                        $order = 'cs.summary DESC';
        }

	/* REMEMBER: For get_records_sql First field in query must be UNIQUE!!!!! */
	$query =
	    "SELECT cs.id,
		co.id          AS remoteid,
		co.fullname,
		cs.course,
		cs.section,
		cs.summary,
		ca.id as cat_id,
		ca.name as cat_name
	    FROM
		{$CFG->prefix}course_sections cs 
	JOIN {$CFG->prefix}course co  ON
		co.id = cs.course 
	LEFT JOIN {$CFG->prefix}course_categories ca  ON
		ca.id = co.category
	    WHERE
		$where
	    ORDER BY
		$order
	    LIMIT $limit
		";
	
	return get_records_sql($query);
}


function enrol_user ($username, $course_id)
{
	/* Create the user before if it is not created yet */
	$user = get_record('user','username',$username);
	if (!$user)
		$this->create_joomdle_user ($username);

	$user = get_record('user','username',$username);
	$course = get_record('course', 'id', $course_id);

	enrol_into_course ($course, $user, 'manual');
}


function get_cat_name ($cat_id)
{
        global $CFG;

	$query = "SELECT name
		FROM  {$CFG->prefix}course_categories
		WHERE id = '$cat_id';";

	$rdo = get_records_sql($query);
	$row = (reset ($rdo));
	return $row->name;
}

function get_my_courses_grades ($username)
{
	$i = 0;
	$rdo = array ();
	$user = get_complete_user_data ('username', $username);
	$cursos = get_my_courses ($user->id);
	foreach ($cursos as $curso)
	{
		$tareas = $this->get_user_grades ($username, $curso->id);
		$sum = 0;
		$n = count ($tareas);
		$rdo[$i]['id'] = $curso->id;
		$rdo[$i]['fullname'] = $curso->fullname;
		$rdo[$i]['cat_id'] = $curso->category;
		$rdo[$i]['cat_name'] = $this->get_cat_name ($curso->category);
		if ($n)
		{
			foreach ($tareas as  $tarea)
				$sum += $tarea->finalgrade;
			$rdo[$i]['avg'] = $sum/$n;
		}
		else $rdo[$i]['avg'] = 0;
		$i++; 
	}
	return $rdo;
}

    function mnet_publishes() {


        $joomla_sp = array();
        $joomla_sp['name']         = 'joomla_sp'; // Name & Description go in lang file
        $joomla_sp['apiversion']   = 1;
        $joomla_sp['methods']      = array('test', 'my_courses', 'list_courses', 'guest_courses', 'get_course_info', 'get_course_contents', 
			'courses_by_category', 'get_course_editing_teachers', 'get_course_non_editing_teachers', 'courses_by_date',
			'get_course_no', 'get_enrollable_course_no', 'get_student_no', 'get_assignment_submissions', 'get_course_students_no',
			'get_upcoming_events', 'get_news_items', 'get_total_assignment_submissions', 'get_assignment_grades', 'get_assignments_grades',
			'get_course_categories', 'get_user_grades', 'get_course_daily_stats', 'get_site_last_week_stats', 'get_course_grade_categories',
			'create_joomdle_user', 'search_courses', 'search_categories', 'search_topics', 'enrol_user', 'get_my_courses_grades'
	);

        return array($joomla_sp);
    }

   
	function logoutpage_hook() {
		global $redirect, $USER;

		if ($USER->auth != 'joomdle')
			return;

		$request = xmlrpc_encode_request("joomdle.logout", array ($USER->username));
		$context = stream_context_create(array('http' => array(
		    'method' => "POST",
		    'header' => "Content-Type: text/xml ",
		    'content' => $request
		)));
		$joomla_xmlrpc_url = get_config (NULL, 'joomla_url').'/xmlrpc/index.php';
		$response = file_get_contents($joomla_xmlrpc_url, false, $context);
		$loggedout = xmlrpc_decode($response);

		$redirect = get_config (NULL, 'joomla_url').'/components/com_joomdle/views/wrapper/getout.php';
	}

/* XXX Failed first attempt to update sessions via WS
	function update_joomla_sessions ()
	{
		$request = xmlrpc_encode_request("joomdle.updateSessions", array ($usernames));
		$context = stream_context_create(array('http' => array(
		    'method' => "POST",
		    'header' => "Content-Type: text/xml ",
		    'content' => $request
		)));
		$joomla_xmlrpc_url = get_config (NULL, 'joomla_url').'/xmlrpc/index.php';
		$response = file_get_contents($joomla_xmlrpc_url, false, $context);
		$updates = xmlrpc_decode($response);
	}

	function cron() {
		$this->update_joomla_sessions();
	}
*/


} //class

?>