<?php
/**
 * Project : Memento Plus
 * Developped for TAEMA, Air Liquide Santé
 * 
 * @since : August 2004
 * @author david.han-sze-chuen
filename : class_MPFile.inc.php
Description : the MPFile class definition 
 */

/**
 * Role : The File class is a toolkit to access file tables
 * It can adapt to any structure of file table providing
 * that it can read the field configuration table.
 * 
 * Abilities :
 * - access to file fields and return their values
 * - can alter a file table structure
 * - future improvment : implement a cache to save DB acces
 */
class MPFile {
    // //////////////////////////////////////////////////////////////////////////////
    // Definitions
    // //////////////////////////////////////////////////////////////////////////////
    /**
     * Store the project data (found in the project table)
     */
    var $project_data = null;
    /**
     * Project field properties
     */
    var $project_field_properties = null;
    /**
     * * This array of files is used like a memory cache.
     * When a file is accessed using this object, the result is stored.
     * Thus the next access to the same file will be enhanced.
     */
    var $file_cache = null;
    /**
     * This array of selectbox data is used like a memory cache.
     * When a file is accessed using this object, found selectbox data is stored in it.
     * Thus the next access to the same file will be enhanced.
	* Ex : sb_cache[sv_id]
     */
    var $sb_cache = null;
    /**
     * This array of subproperties objects is used like a memory cache.
     * When a file is accessed using this object, found subproperties objects are stored in it.
     * Thus the next access to the same file will be enhanced.
	* Ex : $this->sp_cache[$field_name];
     */
    var $sp_cache = null;
    // //////////////////////////////////////////////////////////////////////////////
    // Methods
    // //////////////////////////////////////////////////////////////////////////////
    /**
     * Constructor of MPFile
     * 
     * @param integer $project_id the id of the project that the MPFile object has to access
     * @param string $new_tbl_field_conf A new field conf table name
     * @param string $new_tbl_file_index A new file index table name
     */
	function MPFile($project_id = null, $verbose = false) {	
		global $db; 
        // should try to update the table cache for this project
        /* ... */
        // get the project data
        $this->project_data = MPFile::getProjectData($project_id);
        $this->loadCache($project_id, $verbose); 
        // DEBUG
        if ($verbose) {
            echo "MPFile->project_data : <br>";
            printarray($this->project_data);
        }
    } // end of MPFile($project_id = null)
    // //////////////////////////////////////////////////////////////////////////////
	/**
     * get some info from the project
     * 
     * @param mixed $fields Either an array of the fields to get, or a string of the field to get. If null, return all project fields
     * @return array An associative array of field values
     * @return string The value of the unique field requested
     * @access public 
     */
    function getProjectData($project_id = null, $fields = null) {
        global $db, $QUERY; 
        // project_id
        $project_id = MPFile::checkID($project_id);
        if ($project_id === false) {
            death("ERR_InvalidParameter", "Bad parameter type, 01 MPFile::getProjectData(). \$project_id type must be integer.");
        } 
        // analyse param
        if (!empty($fields)) {
            switch (gettype($fields)) {
                case 'string' :
                    if (trim($fields) == '*') { // if the user put a '*', we'd rather return an array for convenience
                        $fields = null;
                    } 
                    break;
                case 'array' :
                    break;
                default :
                    death("ERR_InvalidParameter", "Bad parameter type, 02 MPFile::getPublicProjectData(\$fields : " . gettype($fields) . ") accepts only array, string or null.");
            } 
        } 
        $f = buildSelect($fields);
        if ($project_id != null) {
            $sql = sprintf($QUERY['get_project_info'], $f, $project_id);
        } else
            $sql = sprintf($QUERY['get_public_project_info'], $f); 
        // DEBUG
        // echo "<br>sql = '$sql'<br>";
        $res = $db->query($sql);
        if (DB::isError($res)) {
            die($res->getMessage());
        } 
        $ret = &$res->fetchRow(DB_FETCHMODE_ASSOC);
		if ($res->numRows() == 0) {
			death('ERROR', "03 MPFile::getProjectData() : no project found with project_id = " . $project_id);
		}
        if (is_string($fields))
            return $ret[$fields];
        else
            return $ret;
    } // end of getPublicProjectData($fields = null)                                                                                 
    // //////////////////////////////////////////////////////////////////////////////
    /**
     * MPFile::getFieldsProperties()
     * Function which returns an associative array of all the column definitions of a project
     * It refers to the field configuration table.
     * if project_id = null, look for the public project
     * 
     * FetchAll() is used to return data. 
     * Returned array would look like this : 
     * [row_number]=>array(<properties>)
     * 
     * ['0'] 	['project_name']  	=	 _PUBLIC or ROOT
     * ['0'] 	['field_conf_id'] 	=	1
     * ['0'] 	['project_id'] 	=	17
     * ['0'] 	['field_type_id'] 	=	2
     * ['0'] 	['field_name'] 	=	title
     * ['0'] 	['title'] 	=	Title
     * ['0'] 	['description'] 	=	The title of the file.
     * ['0'] 	['edit_order'] 	=	13
     * ['0'] 	['list_order'] 	=	2
     * ['0'] 	['default_value'] 	=	null
     * ['0'] 	['properties'] 	=	maxlen=255;
     * ['0'] 	['created_by'] 	=	38
     * ['0'] 	['created_date'] 	=	0
     * ['0'] 	['last_modified_by'] 	=	38
     * ['0'] 	['last_modified_date'] 	=	0
     * ['0'] 	['field_type_title'] = String
	* ['0'] 	['field_type_description'] = Accepts either a length limited or unlimited string
	*
     * Options are activated if they're properly set.
     * - $options['countRows'] : count the number of rows, so the number of columns of a file table defined in field_conf. The value returned is an integer.
     * - $options['link'] = array()
     * 		'created_by' : get the name of the creator of the row (stored as '_username1')
     * 		'last_modified_by' : get the name of the last modifier of the row (stored as '_username2')
     * - $options['sort'] : sort by the given sort order (asc or desc)
     * - $options['order'] : order by the given column name
     * - $options['assoc'] : instead of using row_number as array index, use one value of the result array. 
     * 	                     If the association value isn't in the result array, the function will fail and exit.
     * 						 This option is not used if 'countRows' is set.
     *                                                       Ex: $options['assoc']='field_name'
     * 	        [<field_name>]['project_name']  	=	 _PUBLIC
     * 	        [<field_name>]['field_conf_id'] 	=	1
     * 	        [<field_name>]['project_id'] 	     =	17
     * 			...
     * - $options['returnQuery'] : the function will only return the sql query instead of its result
     * 
     * @param integer $project_id The project ID, if null, use the project_id of this MPFile instance
     * @param array $options The options
     * @param boolean $verbose Print debug info
     * @return mixed Usually, a row array. See the options
     */
    function getFieldsProperties($project_id = null, $options = null, $verbose = false) {
        global $db, $QUERY; 
        // DEFINITIONS
        // default options
        $countRows = false;
        $link_created_by = false;
        $link_last_modified_by = false;
        $assoc = false;
        $sort = null;
        $order = null;
        $returnQuery = false;
		$field_conf_id = null;
		
		$isPublic_project = false;
        // CODE
        // project_id		
		$project_id = MPFile::checkID($project_id);
		
        if ($project_id === false) {
            death("ERR_InvalidParameter", "Bad parameter type, 01 MPFile::getFieldsProperties(). \$project_id type must be integer.");
        } 
        // does the project exist ?
        if (($project_id != null) && !MPFile::projectExist($project_id)) {
            death("ERROR", "MPFile::getFieldsProperties() : Project ($project_id) does not exist.");
        } 
        // gather options
        if ($options != null) {
            if (is_array($options)) {
                if (isset($options['countRows'])) {
                    $countRows = $options['countRows'] === true;
                } 
                if (isset($options['link']['created_by'])) {
                    $link_created_by = $options['link']['created_by'] === true;
                } 
                if (isset($options['link']['last_modified_by'])) {
					$link_last_modified_by = $options['link']['last_modified_by'] === true;
                } 
                if (!empty($options['assoc'])) {
                    $assoc = trim($options['assoc']);
                } 
                if (isset($options['order']) && isset($options['sort'])) {
                    $order = $options['order'];
                    $sort = $options['sort'];
                } 
                if (isset($options['returnQuery']))
                    $returnQuery = $options['returnQuery'] === true;
				if (isset($options['field_conf_id'])) {
					$field_conf_id = intval($options['field_conf_id']);
				}
            } else {
                death("ERR_InvalidParameter", "Bad parameter type, 02 MPFile::getFieldsProperties(\$options : " . gettype($options) . "). \$options must be array type.");
            } 
        } 
        // build SELECT
        $select = "SELECT ";
        if ($countRows) {
            $select .= "count(*) as row_count";
        } else {
            // $select .= "*";
            $select_fields = array();
            $select_fields[] = 'fc.*';
            $select_fields[] = 'ty.*';
			if ($link_created_by)
                $select_fields[] = "u1.email as _username1";
            if ($link_last_modified_by)
                $select_fields[] = "u2.email as _username2";
            $select .= buildSelect($select_fields, null, $prefix = '', $suffix = null);
        } 
        // build FROM
        // tables to import, with their nicknames
        $tables = array();
        $tables["fc"] = TBL_FIELD_CONF;
        $tables["pj"] = TBL_PROJECT;
        $tables["ty"] = TBL_FIELD_TYPE;

        if (!$countRows) {
            if ($link_created_by)
                $tables["u1"] = TBL_AUTH_USER;
            if ($link_last_modified_by)
                $tables["u2"] = TBL_AUTH_USER;
        } 
        $from = buildFrom($tables, array_keys($tables), "FROM");
        // build WHERE
        // the WHERE conditions
        $wherePublicProject = "pj.project_name = '" . PUBLICFIELDS_PROJECT_NAME . "'";
	$select_project_id=$project_id;
	$whereUsualProject = "";
	while ($select_project_id!=0)
	{
		if ($select_project_id!=$project_id)
		{
			$whereUsualProject .= " OR "; 
		}
		$whereUsualProject .= " pj.project_id = '" . $select_project_id . "' ";
		$select_project_id=$db->getOne("SELECT parent_project_id FROM " . TBL_PROJECT . " WHERE project_id = $select_project_id " );
	}
        $whereJoinProjectWithFieldConf = "pj.project_id = fc.project_id";
        $whereJoinFieldType = "ty.field_type_id = fc.field_type_id";
        $whereJoinCreatedBy = "u1.user_id = fc.created_by";
        $whereJoinLastModifiedBy = "u2.user_id = fc.last_modified_by"; 
		$whereSpecificFieldConf = "fc.field_conf_id = " . $db->quote($field_conf_id);
        // concat the "where"s
        $where = "WHERE $whereJoinProjectWithFieldConf AND ";
        $where .= (empty($project_id)) ? $wherePublicProject : "( $wherePublicProject OR $whereUsualProject ) ";
        $where .= " AND $whereJoinFieldType ";
        if (!$countRows) {
            if ($link_created_by) {
                $where .= " AND $whereJoinCreatedBy ";
            } 
            if ($link_last_modified_by) {
                $where .= " AND $whereJoinLastModifiedBy ";
            } 
			if ($field_conf_id != null) {
				$where .= " AND $whereSpecificFieldConf";
			}
            if ($order && $sort) {
                $where .= " ORDER BY $order $sort ";
			}
        } 
        // reassemble the sql query
        $sql = "$select $from $where"; 
        // DEBUG
        if ($verbose)
            echo "project_id vaut $project_id et MPFile::getFieldsProperties query : '$sql'<br>";
        if (!$returnQuery) {
            $res = $db->query($sql);
            if (DB::isError($res)) {
                death("DB ERROR", "01 MPFile::getFieldsProperties()<br>" . $res->getMessage());
            } 
            $result = fetchAll($res); 
            // DEBUG
            // println("MPFile::getFieldsProperties() : result :");
            // printarray($result);
            if ($countRows) {
                return $result[0]['row_count'];
            } else {
                // associate result with one of its values
                if (!empty($assoc) && count($result)) {
                    // test if assoc name exist
                    $temp = current($result);
                    if (isset($temp[$assoc])) {
                        // associate
                        $newArray = array();
                        foreach($result as $k => $v) {
                            $newArray[$v[$assoc]] = $v;
                        } 
                        $result = &$newArray;
                    } else {
                        death("ERROR", "MPFile::getFieldsProperties() : cannot associate with given field name.");
                    } 
                } 
		
                return $result;
            } 
        } else
            return $sql;
    } // end of getFieldsProperties($project_id = null)                                                                                
    // //////////////////////////////////////////////////////////////////////////////
    /**
     * MPFile::checkId()
     * Check the validity of a project_id variable
     * Accept only integer or string representing an integer or null value
     * 
     * @param mixed $ID 
     * @return mixed integer value of project_id, can convert from string -- null If it's a null value -- false If uncorrect value
     */
    function checkId($ID) {
        if ($ID == null)
            return null;
        elseif (is_string($ID)) {
            $ID = intval($ID);
        } 
        if (is_int($ID) && ($ID >= 0)) {
            return $ID;
        } 
        return false;
    } // end of checkId($ID)                
    // //////////////////////////////////////////////////////////////////////////////
    /**
     * * Check if a project exists
     * 
     * @param integer $project_id 
     * @return boolean 
     */
    function projectExist($project_id) {
        global $db, $QUERY;
        $project_id = MPFile::checkID($project_id);
        if ($project_id === false) {
            death("ERR_InvalidParameter", "Bad parameter type, 01 MPFile::projectExist(). \$project_id type must be an integer.");
        } 
        $sql = sprintf($QUERY['get_project_info'], "*", $project_id);
        $res = $db->query($sql);
        if (DB::isError($res)) {
            death("DB ERROR", "MPFile::projectExist()<br>" . $res->getMessage());
        } 
        // DEBUG
        // println("MPFile::projectExist() : project_id = $project_id");
        // println("MPFile::projectExist() : \$res->numRows() = ". $res->numRows());
        return ($res->numRows())? true:false;
    } 
    // //////////////////////////////////////////////////////////////////////////////
    /**
     * If bug_id is null, the function is supposed to be called from a valid MPFile object and return the current project_id. 
     * If not null, the function will look for the project_id corresponding to the bug_id in TBL_FILE_INDEX
     * 
     * @param integer $bug_id The id of the file. If null, the function is supposed to be called from a valid MPFile object and return the current project_id. If not null, the function will
     * @return mixed The project id or false if not found.
     */
    function getProject_id($bug_id = null) {
        global $db, $QUERY;

        if (empty($bug_id)) { // if null, return the object project_id
            return $this->project_data['project_id'];
        } else { // the caller wants to get the project_id for a given bug_id
            $bug_id = intval($bug_id); 
            // is it a right integer ?
            if (is_int($bug_id)) {
                // make SQL query
                $sql = sprintf($QUERY["get_project_id_with_bug_id"], $bug_id);
                $res = $db->getOne($sql);
                if (DB::isError($res)) {
                    death("DB ERROR", "MPFile::getProject_id()<br>" . $res->getMessage());
                } 
                if ($res == null) {
                    return false;
                } 
                return $res;
            } else {
                death("WARNING", "MPFile::getProject_id() : the bug_id given to getProject_id is incorrect. It must be integer.");
            } 
        } 
        $res = $db->query($sql);
    } // end of getProject_id($bug_id = null)                                                                    
	// //////////////////////////////////////////////////////////////////////////////
	/**
	 * Return the project_id of a field_conf 
	 * return false if no project_id found
	 * 
	 * @param integer $field_conf_id The ID of a field_conf
	 * @return integer project_id
	 * @access public 
	*/
	function getProject_id_from_Field_Conf ($field_conf_id) {
		global $db;
		
		$field_conf_id = intval($field_conf_id);
		$sql = "SELECT project_id FROM " . TBL_FIELD_CONF . " WHERE field_conf_id = " . $db->quote($field_conf_id);
		
		$res = $db->getOne($sql);
		if (DB::isError($res)) {
			death("DB ERROR", "MPFile::getProject_id_from_Field_Conf()<br>" . $res->getMessage());
		}
		return ($res == null)? false : $res;
	} // end of getProject_id_from_Field_Conf ($field_conf_id)	
    // //////////////////////////////////////////////////////////////////////////////
    /**
     * Retrieve file data (values and fields configuration) of some files and arrange it in an array.
     * This function use the variable $project_data, so it has to be called from an object.
     * The returned array would look like this :  
     * $return['file_id']['field_name']['value'] = val
     * $return['file_id']['field_name']['field_conf'] = array_of_field_conf (see getFieldsProperties())
     * $return['file_id']['field_name']['selectbox'] = array(value, field_conf_id, order, description, options), is null if the field type is not a select box
     * $return['file_id']['field_name']['subproperties'] = subproperty obj
     * 
     * Special cases :
     * $return['file_id'] == null if the file_id doesn't exist in database.
     * $return['file_id']['field_name'] == null if no field conf has been found. In other words, you can't get data from a field that hasn't been declared. This also means there's a big problem !
     * $return['assigned_to'] = array of assigned user ids - this field doesn't exist actually in the file table. Its data is stored in TBL_ASSIGNATION
     * $return['project_id'] = id of project id - this field doesn't exist actually in the file table. Its data is stored in TBL_FILE_INDEX
     * 
     * $return == false if $file_ids is null, not array or empty.
     * 
     * Before returning the results, this function saves them in memory in $file_cache
     * 
     * @access public 
     * @param mixed $file_ids The file ids to look for : array or integer
     * @param array $fields The field names to get. If null, we will search all fields
     * @param boolean $reloadCache Reload cached data before executing this function.
	* @param boolean $skip_cache Skip the cache verification to get data directly from db. The results are still stored in cache.
     * @param boolean $verbose Print debug info, not used yet
     * @return array File data is arranged in nested arrays, see the previous comments for more info.
     */
    function getFile($file_ids, $reloadCache = false, $skip_cache = false, $verbose = false) {
        global $db, $QUERY; 
        // check parameters
        // check $file_ids
		if (is_int($file_ids)) {
            $file_ids = array($file_ids);
        } elseif ((!is_array($file_ids)) || !count($file_ids)) {
            // DEBUG
            println("WARNING : MPFile::getFile() \$file_ids has to be a non-empty array.");
            return false;
        } 
		
		if (MPFile::getProject_id($file_id) == null) {
			death('ERROR', "MPFile::getFile() : file_id#$file_id does not exist.");
		}
		
        $return = array();
        $file_ids_toSearch = $file_ids; 
        // if caches of several properties are empty
        if (($this->project_field_properties == null) || ($reloadCache === true)) {
            $this->reloadCache($verbose);
        } 
        // look into file cache for previous results
        // if no cache or reload cache, make it empty
        if (($reloadCache === true) || ($this->file_cache == null)) {
            $this->file_cache = array();
        } elseif (!$skip_cache && count($this->file_cache) != 0) { // if there's something in it
            // DEBUG
            // if ($verbose)
            // println("Cache available.");
            /* Check in the File cache : $file_cache
			* Add found fields data in $return
			* Remove the found ids from $file_ids_toSearch			
			*/
            // find the common ids
            $cached_ids = array_keys($this->file_cache);
            $common_ids = array_intersect($cached_ids, $file_ids_toSearch); 
            // add it in $return
            foreach($common_ids as $k => $cached_file_id) {
                $return[$cached_file_id] = $this->file_cache[$cached_file_id];
            } 
            // remove found ids from $file_ids_toSearch
            $file_ids_toSearch = array_diff($file_ids_toSearch, $common_ids); 
            // DEBUG
            if ($verbose) {
                $nbFound = count($common_ids);
                if ($nbFound)
                    println("Found " . $nbFound . " file(s) into cached files.");
            } 
            // if there's no more to search
            if (count($file_ids_toSearch) == 0)
                return $return;
        } 
        // Search data of the uncached ids
        $str_ids = implode(', ', $file_ids_toSearch); 
        // DEBUG
        /**
         * println("Project data :");
         * printarray($this->project_data); 
         * // DEBUG
         * println("Field properties :");
         * printarray($this->project_field_properties);
         */
        // assemble query
        $sql = sprintf($QUERY['get_file_by_id'], $this->project_data['file_table_name'], $str_ids);
		if ( $verbose) {
			println("MPFile::getFile() : get_file_by_id : sql = '$sql'");
		}

        $res = $db->query($sql); // do query
        if (DB::isError($res)) {
            death("DB ERROR", "02 MPFile::getFile()<br>" . $res->getMessage());
        } 

        $tab = fetchAll($res); 
        // for each found file
        foreach($tab as $row_num => $file) {
            // store one file by its bug_id
            $return[$file['bug_id']] = array();
            $r = &$return[$file['bug_id']]; 
			
			// now, link each field with data
            foreach($file as $field_name => $val) {
                // get value
                $r[$field_name]['value'] = $val;
                if (!isset($this->project_field_properties[$field_name])) {
                    $r[$field_name]['field_conf'] = null;					
					// printarray(array_keys($this->project_field_properties));
					// die();
                    println("WARNING : 03 MPFile::getFile() : the field name '$field_name' cannot be found in field conf properties !!");
                } else {
                    $curField_conf = &$this->project_field_properties[$field_name]; 
                    // get field conf
                    $r[$field_name]['field_conf'] = &$curField_conf; // store the configuration data                                      
                    // get subproperty
                    $r[$field_name]['subproperty'] = &$this->sp_cache[$field_name];
                    // must be a select box, and be in cache
					
					//DEBUG
					// println("val = $val");
					// printarray($this->sb_cache);
					// println("sb cache = " . $this->sb_cache[intval($val)]);					
					// die();
					
                    if ($curField_conf['field_type_title'] == SELECTBOX_TYPE)
		    {
		    	if ( isset($this->sb_cache[intval($val)]))
			{
				$r[$field_name]['selectbox'] = &$this->sb_cache[intval($val)];
			}			
			$r[$field_name]['selectbox_values'] = array();
			foreach ($this->sb_cache as $k => $v) 
			{
				if ($v['field_conf_id']==$curField_conf['field_conf_id']) 
				{
					$r[$field_name]['selectbox_values'][$k] =$v['value'];
				}
			}
		    } 
                    if ($curField_conf['field_type_title'] == SELECT_TWINBOX_TYPE) 
		    {
		    	if (isset($this->sb_cache[intval($val)])) {
						$r[$field_name]['selectbox'] = &$this->sb_cache[intval($val)];
			}			
						$r[$field_name]['selectbox_values'] = array();
						foreach ($this->sb_cache as $k => $v) {
							if ($v['field_conf_id']==$r[$field_name]['subproperty']->SB_twin_id) {
								$r[$field_name]['selectbox_values'][$k] =$v['value'];
							}
						}
					} 
                } 
            }
			
			// affect some special data :
			// assigned_to
			// $r['assigned_to']['value'] = array();
			// foreach($db->getAll(sprintf($QUERY['get_assignated_user_id'], $file['bug_id'])) as $v) {
			//	$r['assigned_to']['value'][] = $v['user_id'];
			// }
			$r['assigned_to']['value'] = $this->getAssignation($file['bug_id']);
			$r['assigned_to']['field_conf'] = &$this->project_field_properties['assigned_to'];
			$r['assigned_to']['subproperty'] = &$this->sp_cache['assigned_to'];
			// project_id
			$r['project_id']['value'] = intval($this->project_data['project_id']); 
			$r['project_id']['field_conf'] = &$this->project_field_properties['project_id'];
			$r['project_id']['subproperty'] = &$this->sp_cache['project_id'];
			
            $this->file_cache[$file['bug_id']] = $r;
        } 
        // for unfound files
        $not_found_files = array_diff($file_ids_toSearch, array_keys($return));
        foreach($not_found_files as $k => $not_found_id) {
            $return[$not_found_id] = null;
        } 

        return $return;
    } // end of getFile($file_ids, $reloadCache = false, $verbose = false)                         
    // //////////////////////////////////////////////////////////////////////////////
    /**
     * Gather selectbox data from an array of Field_conf_id and return an array :
     * Structure of this array : $sb
     * $sb[<sb_value_id>]['value']
     * $sb[<sb_value_id>]['field_conf_id']
     * $sb[<sb_value_id>]['order']
     * $sb[<sb_value_id>]['description']
     * $sb[<sb_value_id>]['options'] = array of values (ex: 'closed', 'reserved')
     * 
     * @access public 
     * @param array $field_conf_ids Array of field_conf ids
     * @param boolean $verbose Print debug text if true
     * @return array Array of result, see the previous doc
     */
    function getSelectBoxData($field_conf_ids, $verbose = false) {
        global $db, $QUERY; 
        // check parameter
        if (!is_array($field_conf_ids) || !count($field_conf_ids)) {
            death("ERR_InvalidParameter", "Bad parameter, 01 MPFile::getSelectBoxData(). \$field_conf_ids must be a non empty array of field conf ids.");
        } 
        // definitions
        $ids = &$field_conf_ids;
        $sb = array();
        $sql = '';
        $str_ids = implode(", ", $ids); 
        // this query allows to look for several fields at once
        $sql = sprintf($QUERY['get_select_box_values'], $str_ids); 
        // DEBUG
        if ($verbose)
            println("MPFile::getSelectBoxData() : get_select_box_values : sql = '$sql'");

        $res = $db->query($sql); // do query
        if (DB::isError($res)) {
            death("DB ERROR", "02 MPFile::getSelectBoxData())<br>" . $res->getMessage());
        } 
        $tab = fetchAll($res); // build a result array                                                           
        // DEBUG
        // if ($verbose) {
        // println("MPFile::getFieldsProperties() : selectbox properties : ");
        // printarray($tab);
        // }
        // if no selectbox has been found
        if (count($tab) == 0) {
            return $sb; // this will be an empty array
        } 
        // rearrange selectbox data array
        foreach($tab as $k => $v) {
            // DEBUG
            // println("k = $k, v = $v");
            $sb_id = $v['sv_id'];

            $sb[$sb_id] = array();
            $sb[$sb_id]['value'] = $v['sv_value'];
            $sb[$sb_id]['field_conf_id'] = $v['field_conf_id'];
            $sb[$sb_id]['order'] = $v['sv_order'];
            $sb[$sb_id]['description'] = $v['sv_description'];
            $sb[$sb_id]['options'] = array();
        } 
        // now, let's get the options
        $sb_ids = array_keys($sb);
        $str_sb_ids = implode(', ', $sb_ids);
        $sql = sprintf($QUERY['get_select_box_options'], $str_sb_ids); 
        // DEBUG
        if ($verbose)
            println("MPFile::getSelectBoxData() : get_select_box_options : sql = '$sql'");

        $res = $db->query($sql); // do query
        if (DB::isError($res)) {
            death("DB ERROR", "03 MPFile::getSelectBoxData()<br>" . $res->getMessage());
        } 
        $tab = fetchAll($res); // build result array                                                         
        // add found info to $sb
	//println("les options valent");
	//printarray($tab);
        foreach($tab as $k => $v) {
            $sb_id = $v['sv_id'];
            $sb[$sb_id]['options'][] = $v['option_name'];
            $sb[$sb_id]['options'][$v['option_name']][$v['sv_id']]=true;
        } 
        // DEBUG
        if ($verbose) {
            println("MPFile::getSelectBoxData() : arranged selectbox properties : ");
            printarray($sb);
        } 
        return $sb;
    } // end of getSelectBoxData($field_conf_ids)                                                            
    // //////////////////////////////////////////////////////////////////////////////
    /**
     * Gather field_conf_ids from an array of field properties
     * where field type is the given type.
     * 
     * @param array $field_properties Array of field properties (see the result of getFieldsProperties())
     * @param string $searched_type A string of the searched field type (see config.php)
     * @verbose boolean $verbose Print debug info if true
     * @return array Array of field_conf_id
     * @access public 
     */
    function getField_Conf_id_by_type($field_properties, $searched_type, $verbose = false) {
        // check params
        if (!is_array($field_properties) || !count($field_properties)) {
            death("ERR_InvalidParameter", "Bad parameter, 01 MPFile::getField_Conf_id_by_type(). Field properties must be a non empty array of field properties.");
        } 
        if ((!is_string($searched_type)) || (trim($searched_type) == "")) {
            death("ERR_InvalidParameter", "Bad parameter, 02 MPFile::getField_Conf_id_by_type(). Searched type must be a field type in string format.");
        } 
        // we have all the field conf, now let's look for the selectboxes
        $ids = array();
        foreach($field_properties as $k => $v) {
            if ($v['field_type_title'] == $searched_type) {
                // when we have found one, gather id
                $ids[] = $v['field_conf_id'];
            } 
        } 
        // DEBUG
        if ($verbose) {
            println("MPFile::getField_Conf_id_by_type() : ids : ");
            printarray($ids);
        } 
        return $ids;
    } // end of getField_Conf_id_by_type($field_properties, $type_searched, $verbose = false)                                                     
    // //////////////////////////////////////////////////////////////////////////////
    /**
     * MPFile::getSubProperties()
     * Gather the properties of a field properties array and put them in an array of MPSubProperty objects.
     * return array : $sp_cache['field_name'] = MPSubProperty object
     * @param array $field_properties Array of field properties (see the result of getFieldsProperties())
     * @verbose boolean $verbose Print debug info if true
     * @return array An array of MPSubProperty objects
     */
    function getSubProperties($field_properties, $verbose = false) {
        // check params
        if ((!is_array($field_properties)) || (!count($field_properties))) {
            death("ERR_InvalidParameter", "Bad parameter, 01 MPFile::getSubProperties(). \$field_propertiesmust be a non empty array of field properties.");
        } 
        // we have all the field conf, now let's build the subproperty objects
        $sp = array();
        foreach($field_properties as $k => $v) {
            $sp[$k] = new MPSubProperty($v['properties']);
        } 
        // DEBUG
        if ($verbose) {
            println("MPFile::getSubProperties() : ");
            foreach($sp as $k => $v) {
                $v->toString(false, true);
            } 
        } 
        return $sp;
    } // end of getSubProperties($field_properties, $verbose = false)                                  
    // //////////////////////////////////////////////////////////////////////////////
    /**
     * MPFile::loadCache()
     * Load project data, field properties, selectboxes, analyse subproperties from a project_id
     * 
     * @param  $project_id the project_id
     * @param boolean $verbose print debug info
     * @return void 
     */
    function loadCache($project_id, $verbose = false) {
        if (is_string($project_id))
            $project_id = intval($project_id);

        if (($project_id != null) && (!is_int($project_id) || ($project_id < 0))) {
            death("ERR_InvalidParameter", "Bad parameter, 01 MPFile::loadCache(). \$project_id = '$project_id' must be a positive integer.");
        } 

        $this->project_data = $this->getProjectData($project_id); 
        // DEBUG
        if ($verbose)
            println("MPFile::loadCache() Cache building."); 
        // get all field properties
        $options = array();
        $options['link'] = true;
        $options['assoc'] = 'field_name';
        $this->project_field_properties = $this->getFieldsProperties($this->project_data['project_id'], $options); 
        // gather subproperties
        $this->sp_cache = $this->getSubProperties($this->project_field_properties); 
        // get the selectbox data
        $selectbox_ids = $this->getField_Conf_id_by_type($this->project_field_properties, SELECTBOX_TYPE); 
        // if there are select boxes
        if (count($selectbox_ids)) {
            $this->sb_cache = $this->getSelectBoxData($selectbox_ids);
        } else {
            $this->sb_cache = array(); // if not, just make an empty array
        } 
        if ($verbose)
            println("MPFile::loadCache() Cache built.");
    } // end of loadCache($verbose = false)                                
    // //////////////////////////////////////////////////////////////////////////////
    /**
     * MPFile::reloadCache()
     * Reload cache, using loadCache
     * 
     * @param  $verbose print debug data
     * @return void 
     */
    function reloadCache($verbose) {
        $this->loadCache($this->project_data['project_id'], $verbose);
    } // end of reloadCache($verbose)                                
    // //////////////////////////////////////////////////////////////////////////////
    /**
     * MPFile::getField()
     * Return an array of field values from given field name and a given file number.
     * Ex : $return[<field_name>] = value;
     * To call on an object.
     * 
     * @param array $fields field names. Set to '*' or array('*') to get all fields.
     * @param integer $file_id The file id
     * @param boolean $verbose print debug info
     * @return array Result array, or false if no file found.
     */
    function getField($fields = '*', $file_id, $verbose = false) {
        // check params
        if (empty($fields)) {
            death("ERR_InvalidParameter", "Bad parameter, 01 MPFile::getField(). \$fields must be a non empty array of field names.");
        } 

        if (is_string($file_id))
            $file_id = intval($file_id);
        if (!is_int($file_id) || ($file_id < 0)) {
            death("ERR_InvalidParameter", "Bad parameter, 02 MPFile::getField(). \$file_id must be a positive integer.");
        } 
        // if user still put '*', we can still manage that
        if ((trim($fields) == '*') || in_array('*', $fields)) {
            $fields = 'all';
        } 
        // get the file with the inner method
        $files_values = $this->getFile($file_id, false, $verbose); 
        // if it wasn't found
        if ((!isset($files_values[$file_id])) || ($files_values[$file_id] == null)) {
            return false;
        } 
        // just return the field values
        $return = array(); // prepare result
        $file = &$files_values[$file_id]; // create a ref link to make it easier
        $field_keys = array_keys($file); // get the file field names
        if ($fields != 'all') { // if we don't want all fields
            $return_keys = array_intersect($field_keys, $fields); // keep only the wanted field
        } else {
            $return_keys = &$field_keys; // or just take all the field names
        } 
        foreach($return_keys as $k => $fname) {
            $return[$fname] = $file[$fname]['value']; // copy value
        } 
        return $return;
    } // end of getField($fields = '*', $file_id, $verbose = false)                        
    // //////////////////////////////////////////////////////////////////////////////
    /**
     * MPFile::alterField()
     * Can modify a field in a file table :
     * Create, change or drop a new field in the project file table of the current MPFile object.
     * It uses the field properties found in $project_field_properties.
     * 
     * @param integer $field_conf_id Field conf identifier
     * @param string $action Type of action to do : change, create or drop
     * @param boolean $verbose Print debug info
     * @return boolean true if creation successful
     */
    function alterField($field_conf_id, $action = "change", $verbose = false) {
        global $db, $QUERY, $STRING; 
		// check params
        $field_conf_id = $this->checkId($field_conf_id);
        if (($field_conf_id == false) || ($field_conf_id == null)) {
            death("ERR_InvalidParameter", "Bad parameter, 01 MPFile::alterField(). \$field_conf_id must be a positive integer.");
        } 
        // find the good field conf
        $field_conf = null;
        foreach($this->project_field_properties as $k => $v) {
            if ($v['field_conf_id'] == $field_conf_id) {
                $field_conf = &$v;
                break;
            } 
        } 
        if ($field_conf == null) {
            death("ERROR FIELD_CONF UNKNOWN", "02 MPFile::alterField() : the field conf data cannot be found.");
        } 
		if ($this->project_data['project_id'] == PUBLIC_PROJECT_ID) {
			death("ERROR", "03 MPFile::alterField() : it is forbidden to try to modify the public project file table.");
		}
        // some query parameters must be initialised
        $tbl_name = $this->project_data['file_table_name'];
        $field_name = $field_conf['field_name']; 
        // prepare the sql query
        // db data
        $prepared = null;
        $params = null;
        switch ($action) {
            case "create":
                $prepared = $db->prepare($QUERY['alter_table_create_field']);
                break;
            case "change":
                $prepared = $db->prepare($QUERY['alter_table_change_field']);
                break;
            case "drop": 
                // check if it's a system field
                if (isset($this->sp_cache[$field_name]->internal_system_use) && ($this->sp_cache[$field_name]->internal_system_use == true)) {
                    // it's forbidden to drop a system field
					death("ERROR CANNOT DROP SYSTEM FIELD", "03.1 MPFile::alterField() : cannot drop internal system field.");
                } 

                $prepared = $db->prepare($QUERY['alter_table_drop_field']);
                $res = $db->execute($prepared, array($tbl_name, $field_name));
				if (DB::isError($res)) {
					death("DB ERROR", "03.2 MPFile::alterField()<br>" . $res->getMessage());
				} 
				return true;
            default:
                death("ERROR UNKNOWN ACTION", "04 MPFile::alterField() : the action is unknown.");
        } 
        // other parameters
        $default_value = $field_conf['default_value'];
        $field_type = null;
        $sp = &$this->sp_cache[$field_name]; 
        // if type is known
        switch ($field_conf['field_type_title']) {
            case INTEGER_TYPE :
                if (!isset($sp->int_size))
                    death("ERROR MISSING INFO", "05 MPFile::alterField() : missing subproperty.");
                $field_type = $QUERY['field_type_name'][INTEGER_TYPE] . "(" . $sp->int_size . ")";
                break;
            case FLOAT_TYPE :
                if (!isset($sp->int_size))
                    death("ERROR MISSING INFO", "06 MPFile::alterField() : missing subproperty.");
                if (!isset($sp->decimal_size))
                    death("ERROR MISSING INFO", "07 MPFile::alterField() : missing subproperty.");

                $field_type = $QUERY['field_type_name'][FLOAT_TYPE];
                $int_size = intval($sp->int_size); // intval(null) == 0
                $decimal_size = intval($sp->decimal_size);
                $field_type .= "(" . ($int_size + $decimal_size) . ",$decimal_size)";
                break;
            case STRING_TYPE :
                if (!isset($sp->maxlen))
                    death("ERROR MISSING INFO", "08 MPFile::alterField() : missing subproperty.");
                if ($sp->maxlen == 0) {
                    $field_type = $QUERY['field_type_name'][STRING_TYPE]["TEXT"];
					$default_value = '';
				} else {
                    $field_type = $QUERY['field_type_name'][STRING_TYPE]["VARCHAR"] . "({$sp->maxlen})";
				}
                break;
            case SELECTBOX_TYPE :
				/*
				// the default value of a selectbox, at creation time is not a sv_id, but a sv_value
				// we have to create thie selectbox value before creating the actual field
				$nb = $db->getOne("SELECT count(*) FROM " . TBL_SELECTBOX_VALUE . " WHERE field_conf_id = " . $db->quote($field_conf_id));
				if (($action == 'create') && ($nb == 0)) {
					$sv_array = array(
						array(
							'field_conf_id' => $field_conf_id,
							'value' => ((strlen(trim($default_value)) == 0)? $STRING['FIELD_CONF']['Title']['default_value'] : $default_value),
							'order' => 1,
							'description' => $STRING['FIELD_CONF']['Title']['default_value']
						)
					);
					$treated_sv = $this->alterSelectBoxValue($sv_array, "insert", $verbose);
					
					printarray($treated_sv);
					$default_value = current($treated_sv);					
				}
				*/
				$field_type = $QUERY['field_type_name'][SELECTBOX_TYPE];
                break;
            case SELECT_TWINBOX_TYPE :
                $field_type = $QUERY['field_type_name'][SELECT_TWINBOX_TYPE];
                $default_value = 0;
	          break;
            case TIMESTAMP_TYPE :
                $field_type = $QUERY['field_type_name'][TIMESTAMP_TYPE];
                break;
            default:
                death("ERROR UNKNOWN FIELD TYPE", "10 MPFile::alterField() : the field type ('{$field_conf['field_type_title']}')to create is unknown.");
                break;
        } 

        switch ($action) {
            case "create":
                $params = array($tbl_name, $field_name, $field_type, $default_value);
                break;
            case "change": 
                // note : we don't change field name
                $params = array($tbl_name, $field_name, $field_name, $field_type, $default_value);
                break;
        } 

        $res = $db->execute($prepared, $params);
        if (DB::isError($res)) {
            death("DB ERROR", "11 MPFile::alterField()<br>" . $res->getMessage());
        } 

        return true;
    } // end of alterField($field_conf_id, $action = "create", $verbose = false)
    // //////////////////////////////////////////////////////////////////////////////
	/**
	* MPFile::alterFieldForAll()
	* Same as alterField but applies the field_conf action to every file table involved.
	* Actually, if it's a public field, it will apply the action to all file tables.
	* If not, it just run alterField for one file table.
	* If the field is project_id, nothing is done
	 * This function is static.
	 * 
	 * @access public 
	* @param integer $field_conf_id Field conf identifier
     * @param string $action Type of action to do : change, create or drop
     * @param boolean $verbose Print debug info
     * @return array $return[<project_id>] = true/false : creation successful/failed
	* @return boolean false if the field conf id isn't associated to one project
	 */
	 function alterFieldForAll($field_conf_id, $action = "create", $verbose = false) {
		 global $db;
		 $return = array();
		 // get the field conf project_id
		 $fc_project_id = MPFile::getProject_id_from_Field_Conf($field_conf_id);
		 if ($fc_project_id === false) {
			 return false;
		 }
		 
		 $public_mpfile = &new MPFile();
		 // println("fid = $field_conf_id et = " . $public_mpfile->project_field_properties['project_id']['field_conf_id']);
		 // is it a special system field : project_id or assigned_to
		if (($field_conf_id == $public_mpfile->project_field_properties['project_id']['field_conf_id']) 
			or ($field_conf_id == $public_mpfile->project_field_properties['assigned_to']['field_conf_id'])) {
			 return;
		}
		
		 // is it a public field conf ?
		 if ($fc_project_id == PUBLIC_PROJECT_ID) {
			 // if true, just run it on every project
			$sql = "SELECT DISTINCT(project_id) FROM " . TBL_PROJECT . " WHERE project_id != " . PUBLIC_PROJECT_ID . " GROUP BY file_table_name";
			$res = $db->getAll($sql);
			if (DB::isError($res)) {
				death("DB ERROR", "01 MPFile::alterFieldForAll() : cannot find all the project_id<br>" . $res->getMessage());
			}
			$project_ids = &$res;
			// DEBUG
			if ($verbose)
			{
				println("MPFile::alterFieldForAll() : project_ids");
				printarray($project_ids);
			}
			// for each project
			foreach($project_ids as $k => $v) {
				$temp_mpfile = &new MPFile($v['project_id']); // create a mpfile and apply the action				
				$return[$v['project_id']] = $temp_mpfile->alterField($field_conf_id, $action, $verbose);
			}
		} else { // a private field, apply on one project
			$temp_mpfile = &new MPFile($fc_project_id);
			$return[$fc_project_id] = $temp_mpfile->alterField($field_conf_id, $action, $verbose);
		}
		return $return;
	 } // end of alterFieldForAll($field_conf_id, $action = "create", $verbose = false)	
	// //////////////////////////////////////////////////////////////////////////////
    /**
     * MPFile::dropField()
     * Delete a field from a file table
     * 
     * @param integer $field_conf_id the field conf id pointing to the file field to delete
     * @param boolean $verbose print debug info
     * @return boolean True if no error
     */
    function dropField($field_conf_id, $verbose = false) {
        return $this->alterField($field_conf_id, "drop", $verbose);
    } // end of dropField($field_conf_id, $returnSQL = false, $verbose = false)          
    // //////////////////////////////////////////////////////////////////////////////
    /**
     * MPFile::createField()
     * Create a field from a file table
     * 
     * @param integer $field_conf_id the field conf id pointing to the file field to delete
     * @param boolean $verbose print debug info
     * @return boolean True if no error
     */
    function createField($field_conf_id, $verbose = false) {		
        return $this->alterField($field_conf_id, "create", $verbose);
    } // end of dropField($field_conf_id, $returnSQL = false, $verbose = false)          
    // //////////////////////////////////////////////////////////////////////////////
    /**
	* MPFile::insertFile()
	* Insert a file into the file table of the current project
	* $field_values[<field_name>] = value
	* 
	* NOTES :
	* $field_values['assigned_to'] = array(<user_ids>)  it's inserted or updated in TBL_ASSIGNATION
	* $field_values['project_id'] : this function use the project_id found in the MPFile object
	* $field_values['bug_id'] : set automatically
	* $field_values['browser_string'] : set automatically
	* $field_values['closed_date'] : set automatically to DB default value, because a file should be closed at creation
	* $field_values['created_date'] : set automatically to now
	* $field_values['last_modified_date'] : set automatically to now
	* $field_values['created_by'] : set to $field_values' value if set, or automatically to current session user if unset
	* $field_values['last_modified_by'] : set automatically to $field_values['created_by']
	*
	* @param array $field_values 
	* @param boolean $verbose 
	* @return integer the file id of the created file
	*/
	function insertFile($field_values, $verbose = false, $change_project = false) {
        global $db, $QUERY, $now, $u; 
        // check params
        if (!is_array($field_values['assigned_to'])) {
            death("ERROR MISSING PARAMETER", "01 MPFile::insertFile() : \$field_values['assigned_to'] must be an array of user_id.");
        } 
    		// if (!isset($field_values['created_date'])) {
        //    death("ERROR MISSING PARAMETER", "02 MPFile::insertFile() : \$field_values['created_date'] must be set.");
		    // }
		// a file shouldn't change of project
		if (($change_project == false) && isset($field_values['project_id']) && (intval($field_values['project_id']) != $this->project_data['project_id'])) {
            death("ERROR CONFLICTING PARAMETER", "03.1 MPFile::insertFile() : \$field_values['project_id'] is not equal to the project_id of this MPFile object.");
        } 
        // if the user tries to add a file to the public project, refuse
        if ($this->project_data['project_name'] == PUBLICFIELDS_PROJECT_NAME) {
			  	death("ERROR INSERT", "03.2 MPFile::insertFile() : it is forbidden to add a file to the public project.");
        }
        // the sql insert query structure :
        // 'insert_file' => "INSERT INTO !(!) VALUES(!);",
		// the sql update query structure :
        // update <table_name> set <column_name>=<insert_value>
		// table name, insert columns, insert values
        // some vars
        $tbl_name = $this->project_data['file_table_name'];
        $project_id = $this->project_data['project_id'];
        $assigned = &$field_values['assigned_to'];
	$insert_columns = array(); // key = field_name, value = field value
	$all_fields=$this->getFieldsProperties($project_id);	
		// check field_values
		foreach($all_fields as $row_nb => $field_conf) {
			// choose actions to do for each field name
			switch ($field_conf['field_name']) {
				// some special actions for system fields
				case 'assigned_to' : // do nothing, because it's stored in ASSIGNATION
				case 'browser_string' : // do nothing, because it will be set automatically
				case 'project_id' :
				case 'closed_date' : // do nothing, because it will be set automatically
				case 'created_by' : // do nothing, because it will be set automatically
				case 'created_date' : // do nothing, because it will be set automatically
				// case 'created_by' : either set by $field_values, or set automatically if absent
				case 'last_modified_by' : // do nothing, because it will be set with created_by
				case 'last_modified_date' : // do nothing, because it will be set automatically
					break;
				case 'bug_id' :
					if ($change_project && isset($field_values[$field_conf['field_name']]))
					{
						$file_id=intval($field_values[$field_conf['field_name']]);
						$insert_columns[$field_conf['field_name']] = $file_id;
					}
					break;

				default : // if it's a known field name
					if (isset($this->project_field_properties[$field_conf['field_name']])) 
					{
						if (isset($field_values[$field_conf['field_name']]))
						{
							$insert_columns[$field_conf['field_name']] = $field_values[$field_conf['field_name']];
						}
						else
						{
							if ($field_conf['field_type_title']==SELECT_TWINBOX_TYPE)
							{
								//print "fieldname " . $field_conf['field_name'] . "<br>" ;
								$sp = $this->sp_cache[$field_conf['field_name']]; 
								$fid = $sp->SB_twin_id;
								$fid = intval($fid);
								$temp_default=$this->getField_property_by_another_property('default_value', $searched_data = array('field_conf_id'=> $fid));
								$insert_columns[$field_conf['field_name']] = $temp_default;
								//print "fid " . $fid . "<br>" ;
								//print "default " . $temp_default . "<br>" ;
							}
							else
							{
								$insert_columns[$field_conf['field_name']] = $field_conf['default_value'];
							}
						}
					} 
					else 
					{ // if it's an unknown field, just ignore it
						println("WARNING UNKNOWN FIELD NAME : 04 MPFile::insertFile() : the field name '" . htmlspecialchars($field_name) . "' is unknown.");
					}
					break;
			}
		}
		//TODO : for MySQL : the type TEXT does not accept a default value definition. (but VARCHAR does)
		// So we should handle it manually.
		// - look for TEXT fields in field_conf, if their value isn't specified, use the default value as written in the field_conf
		// ------------------
        // some special actions
        // save the creation date
        $insert_columns['created_date'] = $now;
        $insert_columns['last_modified_date'] = $now; 
        // created_by and last_modified_by are equal
	$insert_columns['created_by'] = (isset($insert_columns['created_by'])? intval($insert_columns['created_by']) : $u);
        $insert_columns['last_modified_by'] = $insert_columns['created_by']; 
        // browser string
        $insert_columns['browser_string'] = stripslashes($_SERVER['HTTP_USER_AGENT']); 
//printarray ($insert_columns);
//printarray ($field_values);
	// close date
        // $insert_columns['closed_date'] = 0;
        // insert new row in FILE_INDEX
        // first, get the new ID
	if ($change_project==false)
	{
        	$file_id = $db->nextId(TBL_FILE_INDEX);
        	if (DB::isError($file_id)) 
		{
            		death("DB ERROR", "05 MPFile::insertFile()<br>" . $res->getMessage());
        	} 
        if ($verbose)
	{
		println("MPFile::insertFile() : nextId = $file_id"); 
	}
        // prepare insert in file_index
        $sql = $QUERY['insert_file_index'];
        $params = array($file_id, $project_id); 
        // insert in file index
        $prepared = $db->prepare($sql); 
        $res = $db->execute($prepared, $params);
        //println("MPFile::insertFile() : nextId = $file_id"); 
        // prepare insert in file_index
        }
	else
	{
		$sql = "UPDATE " . TBL_FILE_INDEX . " SET `project_id` = $project_id WHERE `file_id` = $file_id " ;
		$res = $db->query($sql);
	}
        // DEBUG
        // println(nl2br(htmltruespace(var_export($db, true))));
        if (DB::isError($res)) {
            death("DB ERROR", "06 MPFile::insertFile()<br>" . $res->getMessage());
        } 
        if ($verbose) {
            println("MPFile::insertFile() : insert in file_index : '$db->last_query'");
        } 
        $insert_columns['bug_id'] = $db->quote($file_id);
		// DEBUG
		if ($verbose) {
			println("insert_columns = ");
			printarray($insert_columns);
		}
	
	$prepared = autoprepare($db, $tbl_name, array_keys($insert_columns), "insert");
	$params = array_values($insert_columns); 
        // insert in file table
        // $prepared = $db->prepare($sql);
        $res = $db->execute($prepared, $params);
	if (DB::isError($res)) {
            death("DB ERROR", "07 MPFile::insertFile()<br>" . $res->getMessage());
        } 
        if ($verbose) {
            println("MPFile::insertFile() : insert in file table : '$db->last_query'");
        }
//die;
        // insert assignation rows
	if ($change_project==false)
	{
		$this->setAssignation($file_id, $assigned, $verbose);
	}
	/*
        foreach($assigned as $k => $v) {
            $prepared = autoprepare($db, TBL_ASSIGNATION, array("user_id", "file_id", "assigned_date"), "insert");
            $params = array($v, $file_id, $now);
            $res = $db->execute($prepared, $params);
            if (DB::isError($res)) {
                death("DB ERROR", "08 MPFile::insertFile() for assignation of user_id.<br>" . $res->getMessage());
            } 
            if ($verbose) {
                println("MPFile::insertFile() : insert in assignation table : '$db->last_query'");
            } 
        } 
		*/
		return $file_id;
    } // end of insertFile($field_values, $verbose = false)
    // //////////////////////////////////////////////////////////////////////////////
	/**
	 * MPFile::updateFile()
	 * Update a file into the file table of the current project
	 * $field_values[<field_name>] = value
	 * 
	 * NOTES :
	 * $field_values['assigned_to'] = array(<user_ids>)  it's updated in TBL_ASSIGNATION
	 * $field_values['project_id'] : this function use the project_id found in the MPFile object
	 * $field_values['last_modified_by'] : this function always use $u as the last modifier id
	 * 
	 * @param array $field_values 
	 * @param boolean $verbose 
	 * @return integer the file id of the created file
	 */
	function updateFile($file_id, $field_values, $verbose = false) {
		global $db, $QUERY, $now, $u; 
		// check params
		if (isset($field_values['assigned_to']) && !is_array($field_values['assigned_to'])) {
			death("ERROR MISSING PARAMETER", "01 MPFile::updateFile() : \$field_values['assigned_to'] must be an array of user_id.");
		} 
		/*
		if (!isset($field_values['status_id'])) {
			death("ERROR MISSING PARAMETER", "02 MPFile::updateFile() : \$field_values['status_id'] must be set.");
		} 
		*/
		// a file shouldn't change of project
		if (isset($field_values['project_id']) && (intval($field_values['project_id']) != $this->project_data['project_id'])) {
			death("ERROR CONFLICTING PARAMETER", "03.1 MPFile::updateFile() : \$field_values['project_id'] is not equal to the project_id of this MPFile object.");
		} 
		// if the user tries to update a file to the public project, refuse because the public project shouldn't have any file
		if ($this->project_data['project_name'] == PUBLICFIELDS_PROJECT_NAME) {
			death("ERROR INSERT", "03.2 MPFile::updateFile() : it is forbidden to update a file in the public project.");
		} 
		// the sql update query structure :
		// update <table_name> set <column_name>=<insert_value>
		// some vars
		$tbl_name = $this->project_data['file_table_name'];
		$project_id = $this->project_data['project_id'];
		$assigned = &$field_values['assigned_to'];

		$update_columns = array(); // key = field_name, value = field value   
		// check field_values
		foreach($field_values as $field_name => $value) {
			// choose actions to do for each field name
			switch ($field_name) {
				// some special actions for system fields
				case 'assigned_to' : // do nothing, because it's stored in ASSIGNATION
				case 'bug_id' : // do nothing, because it shouldn't change
				case 'project_id' : // do nothing, because it's stored in FILE_INDEX
				case 'browser_string' : // do nothing, because it will be set automatically
				case 'closed_date' : // do nothing, because it will be set automatically if needed
				case 'created_date' : // do nothing, because it will be left untouched
				case 'created_by' : // do nothing, because it will be left untouched
				// case 'last_modified_by' : // use it
				case 'last_modified_date' : // do nothing, because it will be set automatically
					break;

				default : // if it's a known field name
					if (isset($this->project_field_properties[$field_name])) {
						$update_columns[$field_name] = $value;
					} else {//comment because in case of domain change
						//death("ERROR UNKNOWN FIELD NAME", "04 MPFile::updateFile() : the field name '" . htmlspecialchars($field_name) . "' is unknown.");
					} 
					break;
			}
		}
		// some special actions
		// save the last modified date
		$update_columns['last_modified_date'] = $now; 
		// if 'last_modified_by' is not set, set it automatically
		$update_columns['last_modified_by'] = $u; // FM1491 23/09/2004
		// println("test = " . $update_columns['last_modified_by']);		
		
		// browser string
		$update_columns['browser_string'] = stripslashes($_SERVER['HTTP_USER_AGENT']);
		if (isset($update_columns['status_id'])) { 
			// close date
			// check that the selectbox value for status exists
			if (!isset($this->sb_cache[$update_columns['status_id']])) {
				death('ERROR', '04 MPFile::updateFile() : unable to find the selectbox value for this file.');
			} 
			
			// println("status options for selectboxes");
			// printarray($this->sb_cache);
			// println("status options for 'status_id'");
			// printarray($this->sb_cache[$update_columns[SELECTBOX_STATUS]]);			
			
			// check if the status of the file is closed
			if (in_array(OPTION_CLOSED, $this->sb_cache[$update_columns[SELECTBOX_STATUS]]['options'])) {
				$update_columns['closed_date'] = $now;
				$assigned = array(); // assign nobody to a closed file
			}
			
			// println('closed_date = ' . $update_columns['closed_date']);			
			//die();
		}
		// DEBUG
		if ($verbose) {
			println("update_columns = ");
			printarray($update_columns);
		} 

		//$file_id = intval($update_columns['bug_id']);
		if ($verbose) {
			println("MPFile::updateFile() : file_id = $file_id"); 
		}
		// prepare update in file_index
		$prepared = autoprepare($db, $tbl_name, array_keys($update_columns), "update", "WHERE bug_id = " . $db->quote($file_id));
		$params = array_values($update_columns); 
		// update in file table
		$res = $db->execute($prepared, $params);
		if (DB::isError($res)) {
			death("DB ERROR", "07 MPFile::updateFile()<br>" . $res->getMessage());
		} 
		if ($verbose) {
			println("MPFile::updateFile() : update in file table : '$db->last_query'");
		} 
		
		// insert assignation rows
		if (is_array($assigned)) {
			// println("assigned = $assigned");
			// printarray($assigned);			
			$this->setAssignation($file_id, $assigned, $verbose);
		}
		// foreach($assigned as $k => $v) {
		// $prepared = autoprepare($db, TBL_ASSIGNATION, array("user_id", "file_id", "assigned_date"), "insert");
		// $params = array($v, $file_id, $now);
		// $res = $db->execute($prepared, $params);
		// if (DB::isError($res)) {
		// death("DB ERROR", "08 MPFile::updateFile() for assignation of user_id.<br>" . $res->getMessage());
		// }
		// if ($verbose) {
		// println("MPFile::updateFile() : insert in assignation table : '$db->last_query'");
		// }
		// }
		
		// get data from the modified file so we can update the cache.
		$this->getFile(array($file_id), $reloadCache = false, $skip_cache = true, $verbose);
		return $file_id;
	} // end of updateFile($field_values, $verbose = false)   
    // //////////////////////////////////////////////////////////////////////////////
	/**
	 * MPFile::deleteFile()
	 * remove a file into the file table of the current project
	 * 
	 * NOTES :
	 * 
	 * @param integer $file_id 
	 * @param boolean $verbose 
	 * @return integer the file id of the created file
	 */
function deleteFile($file_id, $verbose = false) 
{
   global $db;
   $tbl_name = $this->project_data['file_table_name'];
   $res = $db->query("DELETE FROM " . $tbl_name . " WHERE bug_id=$file_id");
   if (DB::isError($res)) 
   {
      death("DB ERROR", "01 MPFile::deleteFile()<br>" . $res->getMessage());
   } 
   
}

// //////////////////////////////////////////////////////////////////////////////
	/**
	 * * Insert or update a field configuration row in the field conf table.
	 * If action is 'insert', selectbox data is also inserted.
	 * If it's 'update', selectbox data will replace the previous data.    	
	 * Usually :
	 * $field_conf[<column name>] = value;
	 * Here are the column names of Field_conf :
	 * field_conf_id  project_id  field_type_id  field_name  
	 * title  description  edit_order  list_order  default_value  
	 * properties  created_by  created_date  last_modified_by  last_modified_date
	 * 
	 * For a selectbox, values are also put in an array this way :
	 * $selectbox_values[0] = array of the following elements :
	 * $selectbox_values[0]['value'] = value
	 * $selectbox_values[0]['field_conf_id] = value of the current field_conf_id
	 * $selectbox_values[0]['order'] = value
	 * $selectbox_values[0]['description'] = value
	 * $selectbox_values[0]['options'] = array(option1, option2, ...)
	 * option1, option2, ... are defined in the SELECTBOX_OPTION table
	 * 
	 * Notes :
	 * General : 
	 * $field_conf['project_id'] =  the project_id of the current MPFile object, so you don't have to set this element.
	 * $field_conf['properties'] = either a SubProperty object or a string representation of it.
	 * $field_conf['field_type_id'] = <the selectbox type id> : automatic, if $selectbox_values != null
	 * 
	 * On insert : 
	 * $field_conf['field_conf_id'] = the next field_conf_id is automatically used. You don't have to set this element.
	 * $field_conf['created_by'] = $field_conf['last_modified_by'] : So it's useless to set 'last_modified_by' element.
	 * $field_conf['created_date'] = $field_conf['last_modified_date'] = NOW : these properties are set automatically. 
	 * 
	 * On update :
	 * $field_conf['field_conf_id'] = is used normally.
	 * $field_conf['created_by'] = ignored 
	 * $field_conf['last_modified_by'] : is used normally
	 * $field_conf['created_date'] = ignored 
	 * $field_conf['last_modified_date'] = NOW
	 * 
	 * @param array $field_conf 
	 * @param string $action 
	 * @param array $selectbox_values 
	 * @param boolean $verbose 
	 * @return array the field_conf_id of the created or modified field_conf_id and the selectbox ids : $return['selectbox_ids'] = array and $return['field_conf_id'] = integer
	 * @access public 
    */
	function alterFieldConf($field_conf, $selectbox_values = null, $action, $dynamicFieldName = false, $verbose = false) {
		global $db, $QUERY, $now;
		// definitions
		$field_conf_id = null;
		$prop = null;
		$insert_columns = array();
		$return = array();
		
		$action = strtolower($action);
		if (($action != 'insert') && ($action != 'update')) {
			death("ERR_InvalidParameter", "Bad parameter, 01 MPFile::alterFieldConf(). " . "\$action must be either 'insert' or 'update'.");
		}
		// check params
		// check field_conf
		if (!is_array($field_conf) || (count($field_conf) == 0)) {
			death("ERR_InvalidParameter", "Bad parameter, 01 MPFile::alterFieldConf(). " . "\$field_conf must be a non empty array of field conf properties.");
		}
		// check subproperties
		if (is_string($field_conf['properties'])) { // is string properties
			// verify that subproperties are correct
			// if incorrect, the program will fail
			$test = &new MPSubProperty(trim($field_conf['properties']));
		} elseif (strtolower(get_class($field_conf['properties'])) != "mpsubproperty") { // is not a subproperty object
			// is invalid properties
			// $prop = & $field_conf['properties'];
			$prop = $prop->toString();
		}
		// check the field type
		$sql = "SELECT count(*) FROM " . TBL_FIELD_TYPE . " ft WHERE ft.field_type_id = {$field_conf['field_type_id']}";
		$isKnownFieldType = $db->getOne($sql);
		if (DB::isError($isKnownFieldType)) {
			death("DB ERROR", "08 MPFile::insertFieldConf() when trying to check field type.<br>" . $isKnownFieldType->getMessage());
		}
		if (intval($isKnownFieldType) <= 0) {
			death("INCORRECT VALUE", "08 MPFile::insertFieldConf() : the value of field_type_id does not match with an existing field_type.");
		}
		// column names : 
		// field_conf_id  project_id  field_type_id  field_name  
		// title  description  edit_order  list_order  default_value  
		// properties  created_by  created_date  last_modified_by  last_modified_date
		// filter the column names
		$insert_columns = array();
		$insert_columns['properties'] = $prop;
		$table_info = $db->tableinfo(TBL_FIELD_CONF);
		foreach($table_info as $k => $v) {
			// if a Field_conf column data is in $field_conf
			if (isset($field_conf[$v['name']])) // $v['table'] == table column name
				$insert_columns[$v['name']] = $field_conf[$v['name']];
		}
		// field_conf_id
		$field_conf_id = null;
		// apply special modifications
		$insert_columns['project_id'] = $this->project_data['project_id'];
		
		if (isset($insert_columns['field_type_title']))
			unset($insert_columns['field_type_title']);
		if (isset($insert_columns['field_type_description']))
			unset($insert_columns['field_type_description']);
		
		switch ($action) {
			case 'insert' :
				$field_conf_id = $db->nextId(TBL_FIELD_CONF);
				if (DB::isError($field_conf_id)) {
					death("DB ERROR", "08 MPFile::insertFieldConf() when trying to get the next field_conf_id.<br>" . $field_conf_id->getMessage());
				}
				
				if ($dynamicFieldName) {
					// field name, build dynamically
					$insert_columns['field_name'] = "field_{$field_conf_id}"; // TODO note : we should create a field name filter function based on the field title
					// Note : this new field name shouldn't exist before as its name is made with field_conf_id
				}
				$insert_columns['field_conf_id'] = $field_conf_id;
				$insert_columns['last_modified_by'] = $insert_columns['created_by'];
				$insert_columns['created_date'] = $insert_columns['last_modified_date'] = $now;
				break;
			case 'update' :
				// $insert_columns['field_conf_id'] is used normally
				$field_conf_id = &$insert_columns['field_conf_id'];
				unset($insert_columns['created_by']);
				// $insert_columns['last_modified_by'] is used normally
				unset($insert_columns['created_date']);
				$insert_columns['last_modified_date'] = $now;
				break;
		}		
		// insert new field_conf
		// prepare query parameters
		$query_tail = ($action == 'update')? "WHERE field_conf_id = " . $db->quote($field_conf_id) : null; 
		$prepared = autoprepare($db, TBL_FIELD_CONF, array_keys($insert_columns), $action, $query_tail);
		$params = array_values($insert_columns);
		// DEBUG
		// println(htmltruespace(nl2br(var_export($db, true))));
		// die();
		// execute query
		$res = $db->execute($prepared, $params); 
		if (DB::isError($res)) {
			death("DB ERROR", "08 MPFile::insertFieldConf() when trying to insert a new field_conf row.<br>" . $res->getMessage());
		}
		if ($verbose) {
            println("MPFile::insertFieldConf() : insert in field_conf table : '$db->last_query'");
        } 
		// if it's a selectbox, we have to add values into TBL_SELECTBOX_VALUE and options into TBL_SELECTBOX_OPTION
		if ($selectbox_values != null) {
			foreach($selectbox_values as $sv_num => $sv) {
				$return['selectbox_ids'] = alterSelectBoxValue($selectbox_values, $action, $verbose);				
			} // end of insert of selectbox values
		}
		$return['field_conf_id'] = $field_conf_id;
		// return field_conf_id of the new row and the selectbox ids
		return $return; 
	} // end of alterFieldConf($field_conf, $action, $selectbox_values = null, $verbose = false)
	// //////////////////////////////////////////////////////////////////////////////
	/**
	 * Insert or update some selectbox values.
	 * - Insert : the selectbox values are inserted (sv_id is ignored)
	 * - Update : the selectbox values are updated (sv_id is used)
	 * 
	 * For a selectbox, values are also put in an array this way :
	 * $selectbox_values[0] = array of the following elements :
	 * $selectbox_values[0]['value'] = value
	 * $selectbox_values[0]['sv_id'] = value - not used for insert
	 * $selectbox_values[0]['field_conf_id] = value of the current field_conf_id
	 * $selectbox_values[0]['order'] = value
	 * $selectbox_values[0]['description'] = value
	 * $selectbox_values[0]['options'] = array(option1, option2, ...)
	 * option1, option2, ... are defined in the SELECTBOX_OPTION table
	 * 
	 * @param array $selectbox_values 
	 * @param string $action 
	 * @param boolean $verbose 
	 * @return array An array of the selectbox treated ids
	*/
	function alterSelectBoxValue($selectbox_values, $action = "insert", $verbose = false) {
		global $db;
		
		$action = strtolower($action);
		// check action
		if (($action != 'insert') && ($action != 'update')) {
			death("ERR_InvalidParameter", "Bad parameter, 01 MPFile::alterSelectBoxValue(). " . "\$action must be either 'insert' or 'update'.");
		}
		// check selectbox_values
		if (($selectbox_values != null) && (!is_array($selectbox_values) || (count($selectbox_values) == 0))) {
			death("ERR_InvalidParameter", "Bad parameter, 02 MPFile::alterSelectBoxValue(). " . "\$selectbox_values must be a non empty array of select box values.");
		}
		$sv_ids = array();
		// check some values in selectbox
		foreach($selectbox_values as $k => $v) {
			if (!is_array($v)) {
				death("ERR_InvalidParameter", "Bad parameter, 03.1 MPFile::alterSelectBoxValue() : \$selectbox_values must be an array of arrays.");
			}
			if (($action == 'update') && (!is_numeric($v['sv_id'])))
				death("ERR_InvalidParameter", "Bad parameter, 03 MPFile::alterSelectBoxValue(). sv_id = '$v[sv_id]'" . "A selectbox property (sv_id) must be numeric.");
			if (!isset($v['options']) || $v['options'] == null) {
				$selectbox_values[$k]['options'] = array();
			} elseif (!is_array($v['options'])) {
				death("ERR_InvalidParameter", "Bad parameter, 04 MPFile::alterSelectBoxValue(). " . "A selectbox option must be either an array or null.");
			}
			$sv_ids[] = $db->quote(intval($v['sv_id']));
		}
		
		if ($verbose) {
			println("MPFile::alterSelectBoxValue() : selectbox_values = ");
			printarray($selectbox_values);
		
			println("MPFile::alterSelectBoxValue() : sv_ids = ");
			printarray($sv_ids);
		}
		// if it's an update, we just remove all the options and insert it again later
		if ($action == 'update') {
			$sql = "DELETE FROM " . TBL_SELECTBOX_OPTION . " WHERE sv_id IN (" . implode(', ', $sv_ids) . ")";
			$res = $db->query($sql);
			if (DB::isError($res)) {
				death("DB ERROR", "05 MPFile::alterSelectBoxValue() when trying to delete old selectbox options.<br>" . $res->getMessage());
			}
		}
		
		$treated_sv_ids = array();
		foreach($selectbox_values as $sv_num => $sv) {
			$sv_id = null;
			if ($action == 'insert') {
				$sv_id = $db->nextId(TBL_SELECTBOX_VALUE);
				if (DB::isError($sv_id)) {
					death("DB ERROR", "06 MPFile::alterSelectBoxValue() when trying to get the next selectobx_id.<br>" . $sv_id->getMessage());
				}
			} else {
				$sv_id = intval($sv['sv_id']);
			}
				
			$insert_columns = array();
			$insert_columns['sv_id'] = $sv_id;
			$insert_columns['field_conf_id'] = $sv['field_conf_id'];
			$insert_columns['sv_value'] = $sv['value'];
			$insert_columns['sv_order'] = $sv['order'];
			$insert_columns['sv_description'] = $sv['description'];
			// insert new selectbox_value
			// prepare query parameters
			$query_tail = ($action == 'update')? "WHERE sv_id = " . $db->quote($sv_id) : null;
			$prepared = autoprepare($db, TBL_SELECTBOX_VALUE, array_keys($insert_columns), $action, $query_tail);
			$params = array_values($insert_columns);
			// execute query
			$res = $db->execute($prepared, $params); 
			if (DB::isError($res)) {
				death("DB ERROR", "07 MPFile::alterSelectBoxValue() when trying to insert a new selectbox value.<br>" . $res->getMessage());
			}
			if ($verbose) {
				println("MPFile::alterSelectBoxValue() : insert in selectbox_value table : '$db->last_query'");
			}
			// insert the options
			foreach($sv['options'] as $k => $option_name) {
				$insert_columns = array();
				$insert_columns['option_name'] = $option_name;
				$insert_columns['sv_id'] = $sv_id;
				// insert new option row
				// prepare query parameters
				// $query_tail = ($action == 'update')? "WHERE sv_id = " . $db->quote($sv_id) . " AND option_name = " . $db->quote($option_name) : null;
				$prepared = autoprepare($db, TBL_SELECTBOX_OPTION, array_keys($insert_columns), 'insert', $query_tail = null);
				$params = array_values($insert_columns);
				// execute query
				$res = $db->execute($prepared, $params);
				if (DB::isError($res)) {
					death("DB ERROR", "08 MPFile::alterSelectBoxValue() when trying to insert a new option to a selectbox value.<br>" . $res->getMessage());
				}
				if ($verbose) {
					println("MPFile::alterSelectBoxValue() : insert in selectbox_option table : '$db->last_query'");
				}
			} // end of insert options
			$treated_sv_ids[] = $sv_id;
		} // end of insert of selectbox values
		return $treated_sv_ids;
	} // end of alterSelectBoxValue($field_conf_id, $selectbox_values, $action="insert", $verbose=false)
	// //////////////////////////////////////////////////////////////////////////////
	/**
	 * This function search in the sp_cache for the fields that match the searched property value.
	 * 
	 * @param string $prop_name The name of the compared property
	 * @param mixed $prop_value The value of the wanted property
	 * @param string $cmp_type Type of comparison
	 * @return array An array of field names
   */
	function getFieldName_by_SubProperty($prop_name, $prop_value, $cmp_type = 'bool') {
		$return = array();
		
		foreach($this->sp_cache as $k => $v) {
			if (isset($v->$prop_name)) {
				switch ($cmp_type) {
					// case 'string' :
					case 'bool' :
					case 'int' :
						if ($v->$prop_name === $prop_value)
							$return[] = $k;
						break;
					case 'enum' :
						if (in_array($prop_value, $v->$prop_name))
							$return[] = $k;
						break;
					default : 
						death('INVALID PARAMETER VALUE', 'MPFile::getFieldName_by_SubProperty() : \$cmd_type value cannot be handled.');
				}
			}
		}
		return $return;
	} // end of getFieldName_by_SubProperty($prop_name, $prop_value, $cmp_type='bool')
	// //////////////////////////////////////////////////////////////////////////////
	/**
	 * MPFile::setAssignation()
	 * Assign the file of the given $file_id to users (using the users' ids in $to_assign)
	 * Assignation is stored in TBL_ASSIGNATION
	 * If there's already assigned users in the DB who are not in the $assigned array, they'll be unassigned
	 * Users already assigned are unmodified.
	 * 
	 * @param integer $file_id the file id to assign to users
	 * @param array $to_assign user ids of assigned users
	 * @param boolean $verbose print debug info
	 * @return integer the number of affected rows
	 */
	function setAssignation($file_id, $to_assign, $verbose) {
		global $db, $now; 
		
		// check params
		if (!is_int($file_id) || ($file_id < 0)) {
			death("ERR_InvalidParameter", "Bad parameter, 01 MPFile::setAssignation(). " . "\$file_id must be a positive integer.");
		} 
		if (!is_array($to_assign)) {
			death("ERR_InvalidParameter", "Bad parameter, 02 MPFile::setAssignationsignation(). " . "\$to_assign must be an array of ids.");
		} 
		// get the current assigned users
		$sql = "SELECT user_id FROM " . TBL_ASSIGNATION . " WHERE file_id = " . $db->quote($file_id);
		if ($verbose) {
			println("MPFile::setAssignation() : get current assigned users : sql = '$sql'");
		}
		$res = $db->query($sql);
		if (DB::isError($res)) {
			death("DB ERROR", "03 MPFile::setAssignation() when trying to find the users assigned to the current field ($file_id).<br>" . $res->getMessage());
		}
		$res = &fetchAll($res); 
		$already_assigned = array();
		foreach($res as $v) {
			$already_assigned[] = $v['user_id'];			
		}		
		// who must be unassigned ?
		$to_remove = array_diff($already_assigned, $to_assign);
		$to_add = array_diff($to_assign, $already_assigned);

		if ($verbose) {
			println("MPFile::setAssignation() : to assign :");
			printarray($to_assign);
			println("MPFile::setAssignation() : currently assigned :");
			printarray($already_assigned);
			println("MPFile::setAssignation() : to_remove :");
			printarray($to_remove);
			println("MPFile::setAssignation() : to_add ");
			printarray($to_add);
		}
		
		if (count($to_remove) > 0) {
			// remove the unassigned users
			$to_remove_quoted = array();
			foreach($to_remove as $v) {
				$to_remove_quoted[] = $db->quote($v);
			} 

			$sql = "DELETE FROM " . TBL_ASSIGNATION . " WHERE file_id = " . $db->quote($file_id) 
				. " AND user_id IN (" . implode(', ', $to_remove_quoted) . ")";
			if ($verbose) {
				println("MPFile::setAssignation() : remove a user in assignation table : '$sql'");
			} 
			$res = $db->query($sql);
			if (DB::isError($res)) {
				death("DB ERROR", "04 MPFile::setAssignation() when trying to unassign an user.<br>" . $res->getMessage());
			} 
		} 

		if (count($to_add) > 0) {
			// insert the rest of users
			foreach($to_add as $k => $v) {
				$prepared = autoprepare($db, TBL_ASSIGNATION, array("user_id", "file_id", "assigned_date"), "insert");
				$params = array($v, $file_id, $now);
				$res = $db->execute($prepared, $params);
				if (DB::isError($res)) {
					death("DB ERROR", "08 MPFile::setAssignation() for assignation of user_id.<br>" . $res->getMessage());
				} 
				if ($verbose) {
					println("MPFile::setAssignation() : insert in assignation table : '$db->last_query'");
				} 
			} 
		} 		
	} // end of setAssignation($file_id, $assigned)
	// //////////////////////////////////////////////////////////////////////////////
	/**
	MPFile::getAssignation()
	Returns an array of user_ids assigned to the submitted file_id.
	It's a static function.
	
	@param integer $file_id the file id
	@access public
	@return array
	*/
	function getAssignation($file_id) {
		global $db, $QUERY;
		$return = array();
		foreach($db->getAll(sprintf($QUERY['get_assignated_user_id'], $file_id)) as $v) {
			$return[] = $v['user_id'];
		}
		
		return $return;
	} // end of getAssignation($file_id)
	//	//////////////////////////////////////////////////////////////////////////////
	/**
	MPFile::getSelectboxValues()
	Return an array of selectbox properties for a selectbox field
     $return[<sb_value_id>]['value']
     $return[<sb_value_id>]['field_conf_id']
     $return[<sb_value_id>]['order']
     $return[<sb_value_id>]['description']
     $return[<sb_value_id>]['options'] = array of values (ex: 'closed', 'reserved')
	
	@param mixed $field_conf_id Integer : the field conf id, String : the field name of the field
	@return array Array of selectbox properties	
	*/
	function getSelectboxValues($field_conf_id,$hide=true) {
		// CHECK PARAM
		// if it's a field_name instead of a field_conf_id, search for its id
		if (is_string($field_conf_id)) {
			//if (($field_conf_id = $this->getField_conf_id_by('field_name', $field_conf_id)) === null) {
			if (($field_conf_id = $this->getField_property_by_another_property('field_name', $searched_data = array('field_name'=> $field_conf_id))) === null) {
				death('ERROR', "01 MPFile::getSelectboxValues() : unable to determine the field_conf_id for the field_name = " . htmlspecialchars($field_conf_id));
			}
		}
		
		if (!is_int($field_conf_id)) {
			death("ERR_InvalidParameter", "Bad parameter, 02 MPFile::getSelectboxValues(). " . "\$field_conf_id must be a positive integer.");
		}
		// init vars
		$return = array();
		// gather the selectbox values for this field_conf_id
		foreach($this->sb_cache as $sv_id => $sv) {
			if ($sv['field_conf_id'] == $field_conf_id) 
			{
				$hidden_selectbox_sv_ids = $this->getSv_ids_by_option('hidden');
				if (($hide==false) || (($hide==true) && !in_array($sv_id, $hidden_selectbox_sv_ids)))
				{
					$return[$sv_id] = $sv;
				}
			}
		}		
		return $return;
	} // end of getSelectboxValues($field_conf_id)
	// //////////////////////////////////////////////////////////////////////////////
	/**
	MPFile::getField_property_by_another_property()
	Search in $this->project_field_properties the fields whose properties matches the $searched_data and return one the field's property
	Ex:
		$wanted_property_name = 'field_conf_id'
		$searched_data = array('edit_order' => 1, 'created_by' => 38)
	It will return the field_conf_id of a field which has been created by user_id == 38 and edit_order == 1.
	
	If $return_array is false, it returns only the first matching field_conf
	Else, return an array of matching field results.
	
	Return NULL or an empty array if nothing has been found.

	@param String $field_property_name Field property name, ex : 'field_name'
	@param mixed $searched_value the searched value
	@param boolean $return_array set 'true' to return all field conf ids in an array
	@return mixed one or more field_conf_id
	*/
	function getField_property_by_another_property($wanted_property_name, $searched_data, $return_array = false) {
		if ($return_array) {
			$return = array();
		} else {
			$return = null;
		}
		
		// check if all searched properties are available
		$count_searched_data = count($searched_data);
		reset($this->project_field_properties);
		$fc = current($this->project_field_properties);
		if (!isset($fc[$wanted_property_name])) {
			death('ERROR', "01 MPFile::getField_property_by_another_property() : \$wanted_property_name = " . htmlspecialchars($wanted_property_name) . " is not a property of field_conf");
		}
		$common_keys = array_intersect(array_keys($fc), array_keys($searched_data));
		if (count($common_keys) != $count_searched_data) {
			death('ERROR', '02 MPFile::getField_property_by_another_property() : some searched properties cannot be found in project field properties.');
		}
		
		// for each field		
		foreach($this->project_field_properties as $field_name=>$field_conf) {			
			$counter = 0;
			// for each searched property, compare
			foreach($searched_data as $k => $v) {
				if ($field_conf[$k] == $v) {
					$counter++; // increment counter if it's OK
				}
			}
			// if we found as many property matches as expected, the current field is OK
			if ($counter == $count_searched_data) {
				if ($return_array) {
					$return[] = $field_conf[$wanted_property_name];
				} else {
					return $field_conf[$wanted_property_name];
				}
			}
		}
		return $return;		
	} // end of getField_property_by_another_property($wanted_property_name, $searched_data, $return_array = false)
	// //////////////////////////////////////////////////////////////////////////////
	/**
		Return the sv_id of the status selectbox with a given option value.
		$return[] = array of sv_ids
		@param string $option_value
		$return array 
	*/
	function getSv_ids_by_option($option_value) {
		$return = array();		
		foreach($this->sb_cache as $sv_id => $sv) {
			if (in_array($option_value, $sv['options']))
				$return[] = $sv_id;
		}		
		return $return;
	} // end of getSv_ids_by_option($option_value)
	//	//////////////////////////////////////////////////////////////////////////////	
	/**
	MPFile::getFile_kit()
	This function returns several useful variables to edit a file. 
	Can be called statically.
	Returns an array :
		0 - mpfile object
		1 - field_conf array
		2 - filtered field_conf
		3 - file array of data made with MPFile::getFile()
	@param integer $file_id
	@param array $fieldnames_to_filter array of field names to filter, considering that the hidden fields are automatically removed in the filtered field_conf
	@param string $order the name of the field property which is used to order fields
	@param string $sort the sort order
	@return array
	@access public
	*/
	function getFile_kit($file_id, $fieldnames_to_filter = null, $order = 'edit_order', $sort = 'asc') {
		$file_id = intval($file_id);
		$file_id = abs($file_id);
		$project_id = MPFile::getProject_id($file_id);	
		if ($project_id == null) {
			death('ERROR', 'MPFile::MPFile_kit() : this file does not exist : file_id = ' . $file_id);
		}	
		$mpfile = &new MPFile($project_id);
		$field_conf = $mpfile->project_field_properties; 
		// some system field must be hidden
		if ($fieldnames_to_filter == null) {
			$fieldnames_to_filter = array();
		}
		foreach ($field_conf as $FieldName)
		{
			// subproperties
			$prop = & $mpfile->sp_cache[$FieldName['field_name']];
			if ($prop->hidden==true)
			{
				$fieldnames_to_filter[]=$FieldName['field_name'];
			}
		}
		$file = $mpfile->getFile(array($file_id)); 
		$file = current($file);
		$fc_filtered = filter_fields($field_conf, $fieldnames_to_filter, $order, $sort);
		
		return array(
			$mpfile,
			$field_conf,
			$fc_filtered,
			$file
		);
	} // end of getFile_kit($file_id, $fieldnames_to_filter = null, $order = 'edit_order', $sort = 'asc')
} // end of class MPFile

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/**
	This function logs changed fields values into the TBL_FILE_HISTORY table.
	NOTE : this function is launched after the modification 
	@param array $original An array of a file, from MPFile::getFile()
	@param array $changed_fields an array of changed fields : $changed_fields['field_name'] = new_value;
	@param string $dependency_type 'add', 'del' or null. Indicates the type of modification on dependencies
	@param integer $dependency_id the file id for a dependency
	@param string $cc_type 'add', 'del' or null. Indicates the type of modification on contacts
	@param array $contact_ids the user ids for contacts modification
	@return void
*/
function log_into_history($original, $changed_fields, $dependency_type=null, $dependency_id=null, $cc_type=null, $contact_ids=null, $verbose=false) {
	global $db, $u, $now, $STRING;
	//	require_once 'Date.php';
	//	$now = new Date();
	if (!is_array($original) || !is_array($changed_fields)) {
		death('ERROR', '01 log_into_history() : invalid argument.');
	}
	/*
	 if (!is_object($mpfile)) {
		death('ERROR', '01 log_into_history() : invalid mpfile argument.');
		}
		*/

	$file_id = null;
	// check that needed data is given
	//printarray($original);
	//printarray($changed_fields);
	if (!isset($original['bug_id']['value'])) {
		death('ERROR', '02 log_into_history() : cannot find the original file_id.');
	}
	$file_id = $original['bug_id']['value'];

	$sql = "INSERT INTO ! ( bug_id, changed_field, old_value, new_value, created_by, created_date) "
			. "VALUES (?, ?, ?, ?, ?, ?)";

	$prepared = $db->prepare($sql);

	// println("changed_fields : ");
	// printarray($changed_fields);

	foreach($changed_fields as $field_name => $new_val) {
		$old_val = null;
		$fid=$original[$field_name]['field_conf']['field_conf_id'];
		switch ($original[$field_name]['field_conf']['field_type_title']) { // for each type of data
			case TIMESTAMP_TYPE :
				$old_val = timestamp_to_string($original[$field_name]['value'], $original[$field_name]['subproperty']->time_type);
				$new_val = timestamp_to_string($new_val, $original[$field_name]['subproperty']->time_type);
				break;
			case SELECT_TWINBOX_TYPE :
				$sp = $original[$field_name]['subproperty'];
				if (isset($sp->SB_twin_id))
				{
					$fid=$sp->SB_twin_id;
					$fid = intval($fid);
				}
			case SELECTBOX_TYPE :
				//NOTE the project shouldn't change, so there's not handling for this
				// if it's the assigned_to field
				if ($field_name == 'assigned_to') {
					$old_assigned = $original[$field_name]['value'];
					$new_assigned = $new_val;
						
					if (count($old_assigned)) { // old assignation
						$old_val = array();
						foreach($db->getAll("SELECT * FROM " . TBL_AUTH_USER . " WHERE user_id IN (" . implode(', ', $old_assigned) . ") order by user_id") as $v) {
							$old_val[] = "$v[first_name] $v[last_name] <$v[email]>";
						}
						$old_val = implode("\n", $old_val); // gather all assigned user names
					} else {
						$old_val = $STRING['nobody'];
					}
						
					if (count($new_assigned)) { // new assignation
						$new_val = array();
						foreach($db->getAll("SELECT * FROM " . TBL_AUTH_USER . " WHERE user_id IN (" . implode(', ', $new_assigned) . ") order by user_id") as $v) {
							$new_val[] = "$v[first_name] $v[last_name] <$v[email]>";
						}
						$new_val = implode("\n", $new_val); // gather all assigned user names
					} else {
						$new_val = $STRING['nobody'];
					}
				}
				else if ($field_name=='project_id')
				{
					$old_val=$db->getOne("SELECT project_name FROM " . TBL_PROJECT . " WHERE project_id=" . $original[$field_name]['value']);
					$new_val=$db->getOne("SELECT project_name FROM " . TBL_PROJECT . " WHERE project_id=" . $new_val);
						
				}
				else if ($fid==22 || $fid==23)
				{
					$old_value = $original[$field_name]['value'];
					$res=$db->getRow("SELECT * FROM " . TBL_AUTH_USER . " WHERE user_id IN ($old_value)");
					$old_val = "$res[first_name] $res[last_name] <$res[email]>";
					$res=$db->getRow("SELECT * FROM " . TBL_AUTH_USER . " WHERE user_id IN ($new_val)");
					$new_val = "$res[first_name] $res[last_name] <$res[email]>";
						
				}
				else
				{
					//		println( "fieldname : $field_name");
					//		println( "old val : $old_val");
					//		println( "new val : $new_val");
					//		printarray($original[$field_name]);
					$old_val = $original[$field_name]['selectbox']['value']; // get the old value "real value"
					$new_val = $original[$field_name]['selectbox_values'][intval($new_val)]; // get the new value "real value"
				}
				break;
			default :
				$old_val = $original[$field_name]['value'];
				break;
		}

		$params = array(
				TBL_FILE_HISTORY, // table name
				$file_id,
				$original[$field_name]['field_conf']['title'],
				$old_val,
				$new_val,
				$u, // current user is the modifier
				$now // modification date
		);

		if ($verbose) {
			println("log_into_history() : params : ");
			printarray($params);
		}

		$db->execute($prepared, $params);
	}

	// if dependencies are modified
	if ($dependency_type != null) {
		if ($dependency_type == 'add') {
			$new_val = $STRING['add_dep#'] . "$dependency_id";
		} else {
			$new_val = $STRING['del_dep#'] . "$dependency_id";
		}
		$params = array(
				TBL_FILE_HISTORY, // table name
				$file_id,
				'File_dependencies',
				'',
				$new_val,
				$u, // current user is the modifier
				$now // modification date
		);
		$db->execute($prepared, $params);
	}

	// if contacts are modified
	if ($cc_type != null) {
		$ids = (is_array($contact_ids))? implode(', ', $contact_ids) : $contact_ids;

		$str = array();
		$res = $db->getAll("SELECT * FROM " . TBL_AUTH_USER . " WHERE user_id IN (" . $ids . ") ORDER BY user_id");
		foreach($res as $v) {
			$str[] = "$v[first_name] $v[last_name] <$v[email]>";
		}
		if (count($str) == 0) {
			death('ERROR', 'log_into_history() : unable to find contact names.');
		}
		$str = implode("\n", $str);

		if ($cc_type == 'add') {
			$new_val = "$STRING[add_contacts] :\n $str";
		} else {
			$new_val = "$STRING[del_contacts] :\n $str";
		}
		$params = array(
				TBL_FILE_HISTORY, // table name
				$file_id,
				'Contact_list',
				'',
				$new_val,
				$u, // current user is the modifier
				$now // modification date
		);
		$db->execute($prepared, $params);
	}

} // end of function log_into_history($original, $changed_fields)



?>
