[platal.git] / classes / jobterms.php

<?php
/***************************************************************************
 *  Copyright (C) 2003-2011 Polytechnique.org                              *
 *  http://opensource.polytechnique.org/                                   *
 *                                                                         *
 *  This program is free software; you can redistribute it and/or modify   *
 *  it under the terms of the GNU General Public License as published by   *
 *  the Free Software Foundation; either version 2 of the License, or      *
 *  (at your option) any later version.                                    *
 *                                                                         *
 *  This program is distributed in the hope that it will be useful,        *
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of         *
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the          *
 *  GNU General Public License for more details.                           *
 *                                                                         *
 *  You should have received a copy of the GNU General Public License      *
 *  along with this program; if not, write to the Free Software            *
 *  Foundation, Inc.,                                                      *
 *  59 Temple Place, Suite 330, Boston, MA  02111-1307  USA                *
 ***************************************************************************/

class JobTerms {

    const ALL = 'all';
    const ONLY_JOBS = 'jobs';
    const ONLY_MENTORS = 'mentors';

    /** Retreives subterms of a term to display in a tree
     * @param $parent_jtid the id of the parent jobterm
     * @param $filter enable filtering on jobterms that have been used in jobs or in mentors, in
     *  that case, each row as an 'nb' field that counts all profiles that match
     * @param $token_filter a text (usually ending by %) the retreived subterms to jobterms that
     *  contains this tokens or that one of their children contains it.
     * @return an iterator over jobterms with jtid, name, full_name and nb
     */
    static public function getSubTerms($parent_jtid, $filter = self::ALL, $token_filter = null) {
        switch ($filter) {
          case self::ALL:
          default:
            $table = '';
            break;
          case self::ONLY_JOBS:
            $table = 'profile_job_term';
            break;
          case self::ONLY_MENTORS:
            $table = 'profile_mentor_term';
            break;
        }
        // we are in a tree looking for certain job terms:
        $countingterms = false;
        if ($table) {
            $count = ', COUNT(DISTINCT j.pid) AS nb';
            $join = ' INNER JOIN  profile_job_term_relation AS r2 ON (r2.jtid_1 = e.jtid)
                      INNER JOIN  '.$table.' AS j ON (j.jtid = r2.jtid_2)';
            $countingterms = 'j.jtid';
        } else {
            $count = $join = '';
        }
        if (!empty($token_filter)) {
            $join .= ' INNER JOIN  profile_job_term_relation AS rtf ON (rtf.jtid_1 = e.jtid) '.
                self::token_join_query(self::tokenize($token_filter), 'rtf', 'jtid_2');
            if (!$countingterms) {
                $countingterms = 'rtf.jtid_2';
            }
        }
        if (!$countingterms) {
            $select_leaf = 'IF(r_subbranch.jtid_1 IS NULL,1,0)';
            $join .= ' LEFT JOIN  profile_job_term_relation AS r_subbranch ON (r_subbranch.jtid_1 = e.jtid AND r_subbranch.computed = "original") ';
        } else {
            // branches that have counting terms different that
            // main branch will have subbranches
            $select_leaf = 'MIN('.$countingterms.' = e.jtid)';
        }
        return XDB::iterator('SELECT  e.jtid, e.name, e.full_name'.$count.', '.$select_leaf.' AS leaf
                                FROM  profile_job_term_enum AS e
                          INNER JOIN  profile_job_term_relation AS r ON (r.jtid_2 = e.jtid)'.$join.'
                               WHERE  r.jtid_1 = {?} AND r.computed = "original"
                            GROUP BY  e.jtid
                            ORDER BY  e.name',
                       $parent_jtid);
    }

    /**
     * Display a JSon page containing the sub-branches of a branch in the job terms tree.
     * @param $page the Platal page
     * @param $filter filter helps to display only jobterms that are contained in jobs or in mentors
     *
     * @param Env::i('jtid') job term id of the parent branch, if none trunk will be used
     * @param Env::v('attrfunc') the name of a javascript function that will be called when a branch
     * is chosen
     * @param Env::v('treeid') tree id that will be given as first argument of attrfunc function
     * the second argument will be the chosen job term id and the third one the chosen job's full name.
     * @param Env::v('text_filter') a string (usually ending by %) that will be used to filter
     * subbranches, keeping only the one containing this text in its title or in the title of one of
     * its subbranches.
     */
    static public function ajaxGetBranch($page, $filter = self::ALL) {
        pl_content_headers('application/json');
        $page->changeTpl('include/jobterms.branch.tpl', NO_SKIN);
        $subTerms = self::getSubTerms(Env::v('jtid'), $filter, Env::v('text_filter'));
        $page->assign('subTerms', $subTerms);
        switch ($filter) {
          case self::ONLY_JOBS:
            $page->assign('filter', 'camarade');
            break;
          case self::ONLY_MENTORS:
            $page->assign('filter', 'mentor');
            break;
        }
        $page->assign('jtid', Env::v('jtid'));
        $page->assign('text_filter', Env::v('text_filter'));
        $page->assign('attrfunc', Env::v('attrfunc'));
        $page->assign('treeid', Env::v('treeid'));
    }

    static public function jsAddTree($platalpage, $domElement = '.term_tree', $treeid = '', $attrfunc = '') {
        return '$("'.addslashes($domElement).'").jstree({
            "core" : {"strings":{"loading":"Chargement ..."}},
            "plugins" : ["themes","json_data"],
            "themes" : { "url" : $.plURL("css/jstree.css") },
            "json_data" : { "ajax" : {
                "url" : $.plURL("'.addslashes($platalpage).'"),
                "data" : function(nod) {
                    var jtid = 0;
                    if (nod != -1) {
                        jtid = nod.attr("id").replace(/^.*_([0-9]+)$/, "$1");
                    }
                    return {
                        "jtid" : jtid,
                        "treeid" : "'.addslashes($treeid).'",
                        "attrfunc" : "'.addslashes($attrfunc).'"
                     }
                }
            }} });';
    }

    /**
     * Extract search token from term
     * @param $term a utf-8 string that can contain any char
     * @return an array of elementary tokens
     */
    static public function tokenize($term)
    {
        $term = mb_strtoupper(replace_accent($term));
        $term = str_replace(array('/', ',', '(', ')', '"', '&', '»', '«'), ' ', $term);
        $tokens = explode(' ', $term);
        static $not_tokens = array('ET','AND','DE','DES','DU','D\'','OU','L\'','LA','LE','LES','PAR','AU','AUX','EN','SUR','UN','UNE','IN');
        foreach ($tokens as &$t) {
            $t = preg_replace(array('/^-+/','/-+$/'), '', $t);
            if (substr($t, 1, 1) == '\'' && in_array(substr($t, 0, 2), $not_tokens)) {
                $t = substr($t, 2);
            }
            if (strlen($t) == 1 || in_array($t, $not_tokens)) {
                $t = false;
                continue;
            }
        }
        return array_filter($tokens);
    }

    /**
     * Create the INNER JOIN query to restrict search to some job terms
     * @param $tokens an array of the job terms to look for (LIKE comp)
     * @param $table_alias the alias or name of the table with a jtid field to restrict
     * @param $table_field the name of the field to restrict in table_alias, usually jtid
     * @return a partial SQL query
     */
    static public function token_join_query(array $tokens, $table_alias, $table_field = 'jtid') {
        $joins = '';
        $i = 0;
        foreach ($tokens as $t) {
            ++$i;
             $joins .= ' INNER JOIN  profile_job_term_search AS s'.$i.' ON(s'.$i.'.jtid = '.$table_alias.'.'.$table_field.' AND s'.$i.'.search LIKE '.XDB::escape($t).')';
        }
        return $joins;
    }
}

// vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
?>
Commit	Line	Data
3ac45f10 PC	1	<?php
3ac45f10 PC	2	/***************************************************************************
5e1513f6	3	* Copyright (C) 2003-2011 Polytechnique.org *
3ac45f10 PC	4	* http://opensource.polytechnique.org/ *
	5	* *
	6	* This program is free software; you can redistribute it and/or modify *
	7	* it under the terms of the GNU General Public License as published by *
	8	* the Free Software Foundation; either version 2 of the License, or *
	9	* (at your option) any later version. *
	10	* *
	11	* This program is distributed in the hope that it will be useful, *
	12	* but WITHOUT ANY WARRANTY; without even the implied warranty of *
	13	* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *
	14	* GNU General Public License for more details. *
	15	* *
	16	* You should have received a copy of the GNU General Public License *
	17	* along with this program; if not, write to the Free Software *
	18	* Foundation, Inc., *
	19	* 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA *
	20	***************************************************************************/
	21
	22	class JobTerms {
	23
	24	const ALL = 'all';
	25	const ONLY_JOBS = 'jobs';
	26	const ONLY_MENTORS = 'mentors';
	27
f31c2a24 PC	28	/** Retreives subterms of a term to display in a tree
	29	* @param $parent_jtid the id of the parent jobterm
	30	* @param $filter enable filtering on jobterms that have been used in jobs or in mentors, in
	31	* that case, each row as an 'nb' field that counts all profiles that match
	32	* @param $token_filter a text (usually ending by %) the retreived subterms to jobterms that
	33	* contains this tokens or that one of their children contains it.
	34	* @return an iterator over jobterms with jtid, name, full_name and nb
	35	*/
	36	static public function getSubTerms($parent_jtid, $filter = self::ALL, $token_filter = null) {
3ac45f10 PC	37	switch ($filter) {
	38	case self::ALL:
	39	default:
	40	$table = '';
	41	break;
	42	case self::ONLY_JOBS:
	43	$table = 'profile_job_term';
	44	break;
	45	case self::ONLY_MENTORS:
	46	$table = 'profile_mentor_term';
	47	break;
	48	}
abd508e0 PC	49	// we are in a tree looking for certain job terms:
abd508e0 PC	50	$countingterms = false;
3ac45f10 PC	51	if ($table) {
	52	$count = ', COUNT(DISTINCT j.pid) AS nb';
	53	$join = ' INNER JOIN profile_job_term_relation AS r2 ON (r2.jtid_1 = e.jtid)
	54	INNER JOIN '.$table.' AS j ON (j.jtid = r2.jtid_2)';
abd508e0	55	$countingterms = 'j.jtid';
3ac45f10 PC	56	} else {
	57	$count = $join = '';
	58	}
f31c2a24 PC	59	if (!empty($token_filter)) {
	60	$join .= ' INNER JOIN profile_job_term_relation AS rtf ON (rtf.jtid_1 = e.jtid) '.
	61	self::token_join_query(self::tokenize($token_filter), 'rtf', 'jtid_2');
abd508e0 PC	62	if (!$countingterms) {
	63	$countingterms = 'rtf.jtid_2';
	64	}
f31c2a24	65	}
abd508e0 PC	66	if (!$countingterms) {
	67	$select_leaf = 'IF(r_subbranch.jtid_1 IS NULL,1,0)';
	68	$join .= ' LEFT JOIN profile_job_term_relation AS r_subbranch ON (r_subbranch.jtid_1 = e.jtid AND r_subbranch.computed = "original") ';
	69	} else {
	70	// branches that have counting terms different that
	71	// main branch will have subbranches
	72	$select_leaf = 'MIN('.$countingterms.' = e.jtid)';
	73	}
	74	return XDB::iterator('SELECT e.jtid, e.name, e.full_name'.$count.', '.$select_leaf.' AS leaf
3ac45f10 PC	75	FROM profile_job_term_enum AS e
	76	INNER JOIN profile_job_term_relation AS r ON (r.jtid_2 = e.jtid)'.$join.'
	77	WHERE r.jtid_1 = {?} AND r.computed = "original"
	78	GROUP BY e.jtid
	79	ORDER BY e.name',
	80	$parent_jtid);
	81	}
	82
	83	/**
	84	* Display a JSon page containing the sub-branches of a branch in the job terms tree.
	85	* @param $page the Platal page
	86	* @param $filter filter helps to display only jobterms that are contained in jobs or in mentors
	87	*
	88	* @param Env::i('jtid') job term id of the parent branch, if none trunk will be used
	89	* @param Env::v('attrfunc') the name of a javascript function that will be called when a branch
	90	* is chosen
	91	* @param Env::v('treeid') tree id that will be given as first argument of attrfunc function
	92	* the second argument will be the chosen job term id and the third one the chosen job's full name.
f31c2a24 PC	93	* @param Env::v('text_filter') a string (usually ending by %) that will be used to filter
	94	* subbranches, keeping only the one containing this text in its title or in the title of one of
	95	* its subbranches.
3ac45f10	96	*/
26ba053e	97	static public function ajaxGetBranch($page, $filter = self::ALL) {
3ac45f10 PC	98	pl_content_headers('application/json');
3ac45f10 PC	99	$page->changeTpl('include/jobterms.branch.tpl', NO_SKIN);
f31c2a24	100	$subTerms = self::getSubTerms(Env::v('jtid'), $filter, Env::v('text_filter'));
3ac45f10 PC	101	$page->assign('subTerms', $subTerms);
	102	switch ($filter) {
	103	case self::ONLY_JOBS:
	104	$page->assign('filter', 'camarade');
	105	break;
	106	case self::ONLY_MENTORS:
	107	$page->assign('filter', 'mentor');
	108	break;
	109	}
f2d397df	110	$page->assign('jtid', Env::v('jtid'));
f31c2a24	111	$page->assign('text_filter', Env::v('text_filter'));
3ac45f10 PC	112	$page->assign('attrfunc', Env::v('attrfunc'));
	113	$page->assign('treeid', Env::v('treeid'));
	114	}
	115
	116	static public function jsAddTree($platalpage, $domElement = '.term_tree', $treeid = '', $attrfunc = '') {
	117	return '$("'.addslashes($domElement).'").jstree({
	118	"core" : {"strings":{"loading":"Chargement ..."}},
	119	"plugins" : ["themes","json_data"],
63c89179	120	"themes" : { "url" : $.plURL("css/jstree.css") },
3ac45f10	121	"json_data" : { "ajax" : {
63c89179	122	"url" : $.plURL("'.addslashes($platalpage).'"),
3ac45f10 PC	123	"data" : function(nod) {
	124	var jtid = 0;
	125	if (nod != -1) {
	126	jtid = nod.attr("id").replace(/^.*_([0-9]+)$/, "$1");
	127	}
f31c2a24 PC	128	return {
	129	"jtid" : jtid,
	130	"treeid" : "'.addslashes($treeid).'",
	131	"attrfunc" : "'.addslashes($attrfunc).'"
	132	}
3ac45f10 PC	133	}
	134	}} });';
	135	}
	136
	137	/**
	138	* Extract search token from term
	139	* @param $term a utf-8 string that can contain any char
f31c2a24	140	* @return an array of elementary tokens
3ac45f10 PC	141	*/
	142	static public function tokenize($term)
	143	{
	144	$term = mb_strtoupper(replace_accent($term));
	145	$term = str_replace(array('/', ',', '(', ')', '"', '&', '»', '«'), ' ', $term);
	146	$tokens = explode(' ', $term);
	147	static $not_tokens = array('ET','AND','DE','DES','DU','D\'','OU','L\'','LA','LE','LES','PAR','AU','AUX','EN','SUR','UN','UNE','IN');
	148	foreach ($tokens as &$t) {
	149	$t = preg_replace(array('/^-+/','/-+$/'), '', $t);
	150	if (substr($t, 1, 1) == '\'' && in_array(substr($t, 0, 2), $not_tokens)) {
	151	$t = substr($t, 2);
	152	}
	153	if (strlen($t) == 1 \|\| in_array($t, $not_tokens)) {
	154	$t = false;
	155	continue;
	156	}
	157	}
	158	return array_filter($tokens);
	159	}
	160
	161	/**
	162	* Create the INNER JOIN query to restrict search to some job terms
	163	* @param $tokens an array of the job terms to look for (LIKE comp)
	164	* @param $table_alias the alias or name of the table with a jtid field to restrict
f31c2a24	165	* @param $table_field the name of the field to restrict in table_alias, usually jtid
3ac45f10 PC	166	* @return a partial SQL query
3ac45f10 PC	167	*/
f31c2a24	168	static public function token_join_query(array $tokens, $table_alias, $table_field = 'jtid') {
3ac45f10 PC	169	$joins = '';
	170	$i = 0;
	171	foreach ($tokens as $t) {
	172	++$i;
f31c2a24	173	$joins .= ' INNER JOIN profile_job_term_search AS s'.$i.' ON(s'.$i.'.jtid = '.$table_alias.'.'.$table_field.' AND s'.$i.'.search LIKE '.XDB::escape($t).')';
3ac45f10 PC	174	}
	175	return $joins;
	176	}
	177	}
	178
	179	// vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
	180	?>