<?php

  /**
   * Projects class
   *
   * @package activeCollab.modules.system
   * @subpackage models
   */
  class Projects extends BaseProjects {
    
    /**
     * Check if $user can add new project
     *
     * @param User $user
     * @return boolean
     */
    static function canAdd(User $user) {
      return $user->isAdministrator() || $user->isProjectManager() || $user->getSystemPermission('can_add_project');
    } // canAdd
    
    // ---------------------------------------------------
    //  Finders
    // ---------------------------------------------------
    
    /**
     * Find project by slug
     *
     * @param string $slug
     * @return Project
     */
    static function findBySlug($slug) {
      return Projects::find(array(
        'conditions' => array('slug = ?', $slug),
        'one' => true,
      ));
    } // findBySlug
    
    /**
     * Return projects by ID-s
     *
     * @param array $ids
     * @return DBResult
     */
    static function findByIds($ids) {
      return Projects::find(array(
        'conditions' => array('id IN (?)', $ids),
        'order' => 'name',
      ));
    } // findByIds
    
    /**
     * Return all projects that $user is involved with
     *
     * @param User $user
     * @param boolean $all_for_admins_and_pms
     * @param string $additional_conditions
     * @param string $oder_by
     * @return DBResult
     */
    static function findByUser(User $user, $all_for_admins_and_pms = false, $additional_conditions = null, $order_by = null) {
      return self::_findByUser($user, $all_for_admins_and_pms, $additional_conditions, $order_by);
    } // findByUser
    
    /**
     * Return all project for company
     * 
     * @param Company $company
     * @return DBResult
     */
    static function findByCompany(Company $company) {
      return Projects::find(array(
        'conditions' => array('company_id = ?', $company->getId()),
        'order' => 'name',
      ));
    } //findByCompany

    /**
     * Find project IDs that the two users are working on
     *
     * @param User $first_user
     * @param User $second_user
     * @return array
     */
    static function findCommonProjectIds(User $first_user, User $second_user) {
      $project_users_table = TABLE_PREFIX.'project_users';
      return DB::executeFirstColumn("SELECT t1.project_id FROM $project_users_table t1 INNER JOIN $project_users_table t2 ON t1.project_id = t2.project_id WHERE t1.user_id = ? AND t2.user_id = ?", $first_user->getId(), $second_user->getId());
    } // findCommonProjectIds

    /**
     * Find projects that are common for two users
     *
     * @param User $first_user
     * @param User $second_user
     * @param string|null $additional_conditions
     * @param string|null $order_by
     * @return DBResult
     */
    static function findCommonProjects(User $first_user, User $second_user, $additional_conditions = null, $order_by = null) {
      $common_project_ids = self::findCommonProjectIds($first_user, $second_user);
      if (is_array($common_project_ids)) {
        $conditions = $additional_conditions ? " AND " . $additional_conditions : "";

        return Projects::find(array(
          "conditions" => array("id IN (?) $conditions", $common_project_ids),
          'order_by' => $order_by ? $order_by : "name, ISNULL(completed_on) DESC, completed_on DESC",
        ));
      } // if

      return null;
    } // findCommonProjects
    
    /**
     * Return active projects that $user is involved with
     *
     * @param User $user
     * @param boolean $all_for_admins_and_pms
     * @return DBResult
     */
    static function findActiveByUser(User $user, $all_for_admins_and_pms = false) {
      $projects_table = TABLE_PREFIX . 'projects';
      
      return self::_findByUser($user, $all_for_admins_and_pms, array("$projects_table.state >= ? AND $projects_table.completed_on IS NULL", STATE_VISIBLE));
    } // findActiveByUser
    
    /**
     * Return completed projects that $user is involved with
     *
     * @param User $user
     * @param boolean $all_for_admins_and_pms
     * @return DBResult
     */
    static function findCompletedByUser(User $user, $all_for_admins_and_pms = false) {
      $projects_table = TABLE_PREFIX . 'projects';
      
      return self::_findByUser($user, $all_for_admins_and_pms, array("$projects_table.state >= ? AND $projects_table.completed_on IS NOT NULL", STATE_VISIBLE), "completed_on DESC");
    } // findCompletedByUser
    
    /**
     * Find active projects that have budget property set
     * 
     * @param User $user
     * @param boolean $all_for_admins_and_pms
     * @return DBResult
     */
    static function findActiveByUserWithBudget(User $user, $all_for_admins_and_pms = false) {
      $projects_table = TABLE_PREFIX . 'projects';
      
      return self::_findByUser($user, $all_for_admins_and_pms, array("$projects_table.state >= ? AND $projects_table.completed_on IS NULL AND $projects_table.budget > 0", STATE_VISIBLE));
    } // findActiveByUserWithBudget
    
    /**
     * Return projects that $user belongs to
     *
     * @param User $user
     * @param boolean $all_form_admins_and_pms
     * @param mixed $additional_conditions
     * @param string $order_by
     * @return DBResult
     */
    private static function _findByUser(User $user, $all_for_admins_and_pms = false, $additional_conditions = null, $order_by = null) {
      if($additional_conditions) {
        $additional_conditions = '(' . DB::prepareConditions($additional_conditions) . ')';
      } // if
      
      $projects_table = TABLE_PREFIX . 'projects';
      $project_users_table = TABLE_PREFIX . 'project_users';

      if(empty($order_by)) {
        $order_by = "$projects_table.name";
      } // if
      
      if($all_for_admins_and_pms && $user->isProjectManager()) {
        if($additional_conditions) {
          return Projects::findBySQL("SELECT * FROM $projects_table WHERE $additional_conditions ORDER BY $order_by");
        } else {
          return Projects::findBySQL("SELECT * FROM $projects_table ORDER BY $order_by");
        } // if
      } else {
        if($additional_conditions) {
          return Projects::findBySQL("SELECT $projects_table.* FROM $projects_table, $project_users_table WHERE $project_users_table.user_id = ? AND $project_users_table.project_id = $projects_table.id AND $additional_conditions ORDER BY $order_by", array($user->getId()));
        } else {
          return Projects::findBySQL("SELECT $projects_table.* FROM $projects_table, $project_users_table WHERE $project_users_table.user_id = ? AND $project_users_table.project_id = $projects_table.id ORDER BY $order_by", array($user->getId()));
        } // if
      } // if
    } // _findByUser
    
    /**
     * Finds projects for quick jump for $user
     * 
     * @param User $user
     * @return DBResult
     */
    static function findForQuickJump(User $user) {
      $projects_table = TABLE_PREFIX . 'projects';
      $project_users_table = TABLE_PREFIX . 'project_users';
      $favorites_table = TABLE_PREFIX .'favorites';

      // first we find ids of favorite projects
      $favorite_projects = DB::executeFirstColumn("SELECT parent_id AS project_id from $favorites_table WHERE parent_type = ? AND user_id = ?", 'Project', $user->getId());
      // then we use those ids as condition to determine if project is favorite project
      return Projects::findBySQL("SELECT $projects_table.*, ($projects_table.id IN (?)) as is_favorite FROM $projects_table, $project_users_table WHERE $project_users_table.user_id = ? AND $project_users_table.project_id = $projects_table.id AND $projects_table.state >= ? AND $projects_table.completed_on IS NULL ORDER BY is_favorite DESC, $projects_table.name", array($favorite_projects, $user->getId(), STATE_VISIBLE));
    } // findForQuickJump
    
    /**
     * Return first active project by given user
     *
     * This function is used by start page functionality
     *
     * @param User $user
     * @return Project
     */
    static function findFirstActiveProjectByUser($user) {
      $projects_table = TABLE_PREFIX . 'projects';
      $project_users_table = TABLE_PREFIX . 'project_users';
      
      return Projects::findBySQL("SELECT $projects_table.* FROM $projects_table, $project_users_table WHERE $project_users_table.user_id = ? AND $project_users_table.project_id = $projects_table.id AND $projects_table.completed_on IS NULL ORDER BY $projects_table.name LIMIT 0, 1", array($user->getId()), true);
    } // findFirstActiveProjectByUser
    
    /**
     * Return project ID-s by conditions
     * 
     * @param IUser $user
     * @param string $additional_conditions
     * @param boolean $all_for_admins_and_pms
     */
    static function findIdsByUser(IUser $user, $all_for_admins_and_pms = false, $additional_conditions = null) {
      $projects_table = TABLE_PREFIX . 'projects';
      $project_users_table = TABLE_PREFIX . 'project_users';
      
      if($all_for_admins_and_pms && $user->isProjectManager()) {
        $conditions = $additional_conditions ? "WHERE $additional_conditions" : '';
        
        return DB::executeFirstColumn("SELECT id FROM $projects_table $conditions ORDER BY name");
      } // if
      
      $conditions = array("$project_users_table.user_id = ? AND $project_users_table.project_id = $projects_table.id");
      if($additional_conditions) {
        $conditions[] = "($additional_conditions)";
      } // if
      
      $conditions = implode(' AND ', $conditions);
      
      return DB::executeFirstColumn("SELECT $projects_table.id FROM $projects_table, $project_users_table WHERE $conditions ORDER BY $projects_table.name", $user->getId(), 'Project');
    } // findIdsByUser
    
    /**
     * Return contexts by user
     * 
     * If $project_ids is null, system will return contexts from all projects
     * 
     * @param IUser $user
     * @param array $project_ids
     * @return array
     */
    static function getContextsByUser(IUser $user, &$contexts, &$ignore_contexts, $project_ids = null) {
      if($project_ids && !is_array($project_ids)) {
        $project_ids = array($project_ids);
      } // if
      
      if($user instanceof User) {
        if($user->isProjectManager()) {
          if($project_ids) {
            foreach($project_ids as $project_id) {
              $contexts[] = "projects:projects/$project_id";
              $contexts[] = "projects:projects/$project_id/%";
            } // foreach
          } else {
            $contexts[] = 'projects:projects/%';
          } // if
        } else {
          $projects_table = TABLE_PREFIX . 'projects';
          $project_users_table = TABLE_PREFIX . 'project_users';
          
          if($project_ids) {
            $rows = DB::execute("SELECT $project_users_table.project_id, $projects_table.leader_id, $project_users_table.role_id, $project_users_table.permissions FROM $project_users_table, $projects_table WHERE $project_users_table.project_id = $projects_table.id AND $projects_table.id IN (?) AND $projects_table.state >= ? AND $project_users_table.user_id = ?", $project_ids, STATE_ARCHIVED, $user->getId());
          } else {
            $rows = DB::execute("SELECT $project_users_table.project_id, $projects_table.leader_id, $project_users_table.role_id, $project_users_table.permissions FROM $project_users_table, $projects_table WHERE $project_users_table.project_id = $projects_table.id AND $projects_table.state >= ? AND $project_users_table.user_id = ?", STATE_ARCHIVED, $user->getId());
          } // if
          
          if($rows) {
            $roles = array();
            
            $role_rows = DB::execute('SELECT id, permissions FROM ' . TABLE_PREFIX . 'project_roles');
            if($role_rows) {
              foreach($role_rows as $role_row) {
                $roles[(integer) $role_row['id']] = $role_row['permissions'] ? unserialize($role_row['permissions']) : null;
              } // foreach
            } // if
            
            $context_permission_map = Projects::getProjectSubcontextPermissionsMap();
            $can_see_private = $user->canSeePrivate();
            
            foreach($rows as $row) {
              if($user->getId() == $row['leader_id']) {
                $contexts[] = "projects:projects/$row[project_id]";
                $contexts[] = "projects:projects/$row[project_id]/%";
              } else {
                $visible_project_contexts = array();
                
                if($row['role_id']) {
                  $permissions = isset($roles[$row['role_id']]) ? $roles[$row['role_id']] : null;
                } else {
                  $permissions = $row['permissions'] ? unserialize($row['permissions']) : null;
                } // if
                
                foreach($context_permission_map as $context => $permission) {
                  if($permissions && isset($permissions[$permission]) && $permissions[$permission] >= ProjectRole::PERMISSION_ACCESS) {
                    $visible_project_contexts[] = $can_see_private ? "projects:projects/$row[project_id]/$context/%" : "projects:projects/$row[project_id]/$context/normal/%";
                  } // if
                } // foreach
                
                // All contexts in this project?
                if(count($visible_project_contexts) == count($context_permission_map)) {
                  $contexts[] = "projects:projects/$row[project_id]";
                  $contexts[] = "projects:projects/$row[project_id]/%";

                  if(!$can_see_private) {
                    $ignore_contexts[] = "projects:projects/$row[project_id]/%/private/%";
                  } // if
                  
                // Just specific contexts in this project
                } else {
                  $contexts[] = "projects:projects/$row[project_id]";
                  $contexts = array_merge($contexts, $visible_project_contexts);
                  
                  // Ignore time tracking data
                  if(empty($permissions) || !isset($permissions['tracking']) || $permissions['tracking'] < ProjectRole::PERMISSION_ACCESS) {
                    if($permissions && isset($permissions['task']) && $permissions['task'] >= ProjectRole::PERMISSION_ACCESS) {
                      $ignore_contexts[] = "projects:projects/$row[project_id]/tasks/%/tracking/%";
                    } // if
                    
                    if($permissions && isset($permissions['todo_list']) && $permissions['todo_list'] >= ProjectRole::PERMISSION_ACCESS) {
                      $ignore_contexts[] = "projects:projects/$row[project_id]/todo/%/tracking/%";
                    } // if
                  } // if
                } // if
              } // foreach
            } // if
          } // if
        } // if
      } // if
    } // getContextsByUser
    
    /**
     * Return number of projects that use given currency
     * 
     * @param Currency $currency
     * @return integer
     */
    static function countByCurrency(Currency $currency) {
      if($currency->getIsDefault()) {
        return Projects::count(array('currency_id IS NULL OR currency_id = ?', $currency->getId()));
      } else {
        return Projects::count(array('currency_id = ?', $currency->getId()));
      } // if
    } // countByCurrency
    
    /**
     * Return ID name by given set of project IDs
     * 
     * @param array $ids
     * @return array
     */
    static function getIdNameMapByIds($ids) {
      $result = array();
      
      if($ids) {
        $rows = DB::execute('SELECT id, name FROM ' . TABLE_PREFIX . 'projects ORDER BY name');
        
        if($rows) {
          foreach($rows as $row) {
            $result[(integer) $row['id']] = $row['name'];
          } // foreach
        } // if
      } // if
      
      return $result;
    } // getIdNameMapByIds

    /**
     * Return ID Details map
     *
     * @param array $ids
     * @param array $fields
     * @return array
     */
    static function getIdDetailsMap($fields, $ids = null) {
      $fields = (array) $fields;

      if(!in_array('id', $fields)) {
        $fields[] = 'id';
      } // if

      if($ids) {
        $rows = DB::execute("SELECT " . implode(', ', $fields) . " FROM " . TABLE_PREFIX . "projects WHERE id IN (?) ORDER BY id", $ids);
      } else {
        $rows = DB::execute("SELECT " . implode(', ', $fields) . " FROM " . TABLE_PREFIX . "projects ORDER BY id");
      } // if

      if($rows) {
        $rows->setCasting(array(
          'id' => DBResult::CAST_INT,
        ));

        $result = array();
        foreach ($rows as $row) {
          $project_id = $row['id'];
          unset($row['id']);

          $result[$project_id] = $row;
        } // foreach

        return $result;
      } else {
        return null;
      } // if
    } // getIdDetailsMap
    
    /**
     * Return ID name map for all project that $user is involved with
     *
     * @param User $user
     * @param array $exclude_ids
     * @param boolean $all_for_admins_and_pms
     * @param mixed $additional_conditions
     * @return array
     */
    static function getIdNameMap(User $user, $min_state = STATE_ARCHIVED, $exclude_ids = null, $additional_conditions = null, $all_for_admins_and_pms = false) {
      $conditions = array(DB::prepare('(state >= ?)', array($min_state)));

      if($exclude_ids) {
        $conditions[] = DB::prepare('(projects.id NOT IN (?))', array($exclude_ids));
      } // if

      $conditions = implode(' AND ', $conditions);
      if($additional_conditions) {
        $conditions .= " AND $additional_conditions";
      } // if
      
      return self::_getIdNameMap($user, $all_for_admins_and_pms, $conditions);
    } // getIdNameMap
    
    /**
     * Return ID name map for active project that $user is involved with
     *
     * @param User $user
     * @param array $exclude_ids
     * @param boolean $all_for_admins_and_pms
     * @return array
     */
    static function getActiveIdNameMap(User $user, $exclude_ids = null, $additional_conditions = null, $all_for_admins_and_pms = false) {
      if($additional_conditions) {
        $additional_conditions .= ' AND ' . TABLE_PREFIX . 'projects.completed_on IS NULL';
      } else {
        $additional_conditions = TABLE_PREFIX . 'projects.completed_on IS NULL';
      } // if
      
      if($exclude_ids) {
        $additional_conditions .= ' AND ' . DB::prepare(TABLE_PREFIX . 'projects.id NOT IN (?)', $exclude_ids);
      } // if
      
      return self::_getIdNameMap($user, $all_for_admins_and_pms, $additional_conditions);
    } // getActiveIdNameMap
    
    /**
     * Return ID => name map for a given $user
     *
     * @param User $user
     * @param boolean $all_for_admins_and_pms
     * @param mixed $additional_conditions
     * @return array
     */
    private static function _getIdNameMap(User $user, $all_for_admins_and_pms = false, $additional_conditions = null) {
      $projects_table = TABLE_PREFIX . 'projects';
      $project_users_table = TABLE_PREFIX . 'project_users';
      
      if($additional_conditions) {
        $additional_conditions = DB::prepareConditions($additional_conditions);
      } // if
      
      if($all_for_admins_and_pms && ($user->isAdministrator() || $user->isProjectManager())) {
        if($additional_conditions) {
          $rows = DB::execute("SELECT $projects_table.id, $projects_table.name FROM $projects_table WHERE $additional_conditions ORDER BY $projects_table.name");
        } else {
          $rows = DB::execute("SELECT $projects_table.id, $projects_table.name FROM $projects_table ORDER BY $projects_table.name");
        } // if
      } else {
        if($additional_conditions) {
          $rows = DB::execute("SELECT $projects_table.id, $projects_table.name FROM $projects_table, $project_users_table WHERE $project_users_table.user_id = ? AND $project_users_table.project_id = $projects_table.id AND $additional_conditions ORDER BY $projects_table.name", $user->getId());
        } else {
          $rows = DB::execute("SELECT $projects_table.id, $projects_table.name FROM $projects_table, $project_users_table WHERE $project_users_table.user_id = ? AND $project_users_table.project_id = $projects_table.id ORDER BY $projects_table.name", $user->getId());
        } // if
      } // if
      
      $result = array();
      if(is_foreachable($rows)) {
        foreach($rows as $row) {
          $result[(integer) $row['id']] = $row['name'];
        } // foreach
      } // if
      return $result;
    } // _getIdNameMap
    
    /**
     * Return ID - slug map
     * 
     * @param mixed $ids
     * @return array
     */
    static function getIdSlugMap($ids = null) {
      if($ids) {
        $rows = DB::execute('SELECT id, slug FROM ' . TABLE_PREFIX . 'projects WHERE id IN (?) ORDER BY slug', $ids);
      } else {
        $rows = DB::execute('SELECT id, slug FROM ' . TABLE_PREFIX . 'projects ORDER BY slug', $ids);
      } // if
      
      if($rows) {
        $result = array();
        
        foreach($rows as $row) {
          $result[(integer) $row['id']] = $row['slug'];
        } // foreach
        
        return $result;
      } else {
        return null;
      } // if
    } // getIdSlugMap
    
    /**
     * Return projects by user and company
     *
     * @param User $user
     * @param Company $company
     * @param boolean $all_for_admins_and_pms
     * @param mixed $additional_conditions
     * @param string $order_by
     * @return array
     */
    static function findByUserAndCompany(User $user, Company $company, $all_for_admins_and_pms = false, $additional_conditions = null, $order_by = null) {
      $company_id = $company->getIsOwner() ? array(0, $company->getId()) : $company->getId();

      $conditions = DB::prepare(TABLE_PREFIX . 'projects.company_id IN (?)', array($company_id));

      if($additional_conditions) {
        $conditions = '(' . DB::prepareConditions($additional_conditions) . " AND $conditions)";
      } // if
      
      return self::_findByUser($user, $all_for_admins_and_pms, $conditions, $order_by);
    } // findByUserAndCompany
    
    /**
     * Return active projects by user and company
     *
     * @param User $user
     * @param Company $company
     * @param boolean $all_for_admins_and_pms
     * @return array
     */
    static function findActiveByUserAndCompany(User $user, Company $company, $all_for_admins_and_pms = false, $order_by = null) {
      $projects_table = TABLE_PREFIX . 'projects';
      $company_id = $company->getIsOwner() ? array(0, $company->getId()) : $company->getId();

      return Projects::findByUserAndCompany($user, $company, $all_for_admins_and_pms, DB::prepare("$projects_table.state >= ? AND $projects_table.completed_on IS NULL", array(STATE_VISIBLE, $company_id)), $order_by);
    } // findActiveByUserAndCompany
    
    /**
     * Return completed projects by user and company
     *
     * @param User $user
     * @param Company $company
     * @param boolean $all_for_admins_and_pms
     * @param string|null $order_by
     * @return array
     */
    static function findCompletedByUserAndCompany(User $user, Company $company, $all_for_admins_and_pms = false, $order_by = null) {
      $projects_table = TABLE_PREFIX . 'projects';
      $company_id = $company->getIsOwner() ? array(0, $company->getId()) : $company->getId();

      return Projects::findByUserAndCompany($user, $company, $all_for_admins_and_pms, DB::prepare("$projects_table.state >= ? AND $projects_table.completed_on IS NOT NULL", array(STATE_VISIBLE, $company_id)), $order_by);
    } // findCompletedByUserAndCompany

    /**
     * Return completed projects by user and company
     *
     * @param User $user
     * @param Company $company
     * @param boolean $all_for_admins_and_pms
     * @return array
     */
    static function findArchivedByUserAndCompany(User $user, Company $company, $all_for_admins_and_pms = false) {
      $company_id = $company->getIsOwner() ? array(0, $company->getId()) : $company->getId();
      return self::_findByUser($user, $all_for_admins_and_pms, DB::prepare(TABLE_PREFIX . 'projects.state = ? AND ' . TABLE_PREFIX . 'projects.company_id IN (?)', array(STATE_ARCHIVED, $company_id)), "completed_on DESC");
    } // findCompletedByUserAndCompany
    
    /**
     * Return projects from a sepcific category
     *
     * @param IUser $user
     * @param ProjectCategory $category
     * @return array
     */
    static function findByCategory(IUser $user, ProjectCategory $category) {
      return Projects::find(array(
        'conditions' => array('category_id = ?', $category->getId()),
        'order_by' => 'created_on DESC'
      ));
    } // findByCategory
    
    /**
     * Return number of projects that are in given category
     * 
     * @param IUser $user
     * @param ProjectCategory $category
     * @return integer
     */
    static function countByCategory(IUser $user, ProjectCategory $category) {
      return Projects::count(array('category_id = ?', $category->getId()));
    } // countByCategory
    
    /**
     * Cached search context permission map
     *
     * @var array
     */
    static private $project_subcontext_permission_map = false;
    
    /**
     * Return search context permissions map
     * 
     * This function returns search context as a key and permissions name that 
     * user needs to have in order to be able to search projects in that context
     * 
     * @return array
     */
    static function getProjectSubcontextPermissionsMap() {
      if(self::$project_subcontext_permission_map === false) {
        self::$project_subcontext_permission_map = array('milestones' => 'milestone');
        
        EventsManager::trigger('on_project_subcontext_permission', array(&self::$project_subcontext_permission_map));
      } // if
      
      return self::$project_subcontext_permission_map;
    } // getProjectSubcontextPermissionsMap
    
    // ---------------------------------------------------
    //  Templates
    // ---------------------------------------------------
    
    /**
     * Return all project templates
     *
     * @return array
     */
    static function findTemplates() {
      return Projects::find(array(
        'conditions' => array('is_template = ?', true),
        'order' => 'name',
      ));
    } // findTemplates
    
    /**
     * Find all projects, and prepare them for objects list
     * 
     * @param User $user
     * @return array
     */
    static function findForObjectsList(User $user, $state = STATE_VISIBLE) {
      $project_url_brief = Router::assemble('project', array('project_slug' => '--PROJECTSLUG--', 'brief' => 1));
      $project_url = Router::assemble('project', array('project_slug' => '--PROJECTSLUG--'));

      $projects_table = TABLE_PREFIX . 'projects';
      $project_users_table = TABLE_PREFIX . 'project_users';

      $custom_fields = array();

      foreach(CustomFields::getEnabledCustomFieldsByType('Project') as $field_name => $details) {
        $custom_fields[] = $field_name;
      } // if

      $result = array();
      if($user->isProjectManager()) {
        $projects = DB::execute("SELECT * FROM $projects_table WHERE state = ? ORDER BY name", $state);
      } else {
        $projects = DB::execute("SELECT $projects_table.* FROM $projects_table, $project_users_table WHERE $project_users_table.user_id = ? AND $project_users_table.project_id = $projects_table.id AND $projects_table.state = ? ORDER BY $projects_table.name", $user->getId(), $state);
      } // if

      $labels = Labels::getIdDetailsMap('ProjectLabel');

      if ($projects instanceof DBResult) {
        $projects->setCasting(array(
          'id' => DBResult::CAST_INT,
          'category_id' => DBResult::CAST_INT,
          'label_id' => DBResult::CAST_INT,
          'company_id' => DBResult::CAST_INT,
          'completed_on' => DBResult::CAST_DATETIME,
        ));
        foreach ($projects as $project) {
          list($total_assignments, $open_assignments) = ProjectProgress::getProjectProgress($project['id']);

          $result[] = array(
            'id'                    => $project['id'],
            'name'                  => $project['name'],
            'is_completed'          => $project['completed_on'] instanceof DateTimeValue ? 1 : 0,
            'category_id'           => $project['category_id'],
            'label_id'              => $project['label_id'],
            'company_id'            => $project['company_id'],
            'icon'                  => get_project_icon_url($project['id'], '16x16'),
            'permalink'             => str_replace('--PROJECTSLUG--', $project['slug'], $project_url_brief),
            'goto_url'              => str_replace('--PROJECTSLUG--', $project['slug'], $project_url),
            'is_favorite'           => Favorites::isFavorite(array('Project', $project['id']), $user),
            'total_assignments'     => $total_assignments,
            'open_assignments'      => $open_assignments,
            'label'                 => $project['label_id'] ? array_var($labels, $project['label_id'], null) : null,
          	'is_archived'           => $project['state'] == STATE_ARCHIVED ? 1 : 0
          );

          if(count($custom_fields)) {
            $last_record = count($result) - 1;

            foreach($custom_fields as $custom_field) {
              $result[$last_record][$custom_field] = $project[$custom_field] ? $project[$custom_field] : null;
            } // foreach
          } // if
        } // foreach
      } // if

      return $result;
    } // findForObjectsList
    
    /**
     * Find all projects and prepare them for quick tracking
     * 
     * @param User $user
     * @return array
     */
    function findForQuickTracking(User $user) {
      $projects_table = TABLE_PREFIX . 'projects';
      $project_users_table = TABLE_PREFIX . 'project_users';
          
      $result = array();
      if($user->isProjectManager()) {
        $projects = Projects::findBySQL("SELECT * FROM $projects_table WHERE state >= ? AND $projects_table.completed_on IS NULL ORDER BY name", array(STATE_VISIBLE));
      } else {
        $projects = Projects::findBySQL("SELECT $projects_table.id, $projects_table.slug, $projects_table.name FROM $projects_table, $project_users_table WHERE $project_users_table.user_id = ? AND $project_users_table.project_id = $projects_table.id AND $projects_table.state >= ? AND $projects_table.completed_on IS NULL ORDER BY $projects_table.name", array($user->getId(), STATE_VISIBLE));
      } // if

      if($projects instanceof DBResult) {
        foreach($projects as $project) {
          if($project->tracking()->canAdd($user)) {
            $result[] = array(
              'id' => $project->getId(),
              'slug' => $project->getSlug(),
              'name' => $project->getName(),
              'icon' => $project->avatar()->getUrl(IProjectAvatarImplementation::SIZE_BIG)
            );
          } // if
        } // foreach
      } // if

      return $result;
    } // findForQuickTracking
    
    /**
     * Finds projects for quick jump for $user
     * 
     * @param User $user
     * @param Project $project
     * @return array
     */
    static function findForPhoneProjectUsers(User $user, Project $project) {
    	$return = array();
    	
    	$project_users = $project->users()->describe($user, true, true);
    	if(is_foreachable($project_users)) {
    		foreach($project_users as $project_user) {
    			$return[$project_user['user']['company_id']][] = $project_user;
    		} // foreach
    	} // if
    	
    	return $return;
    } // findForPhoneProjectUsers
    
    /**
     * Find projects for printing by grouping and filtering criteria
     * 
     * @param User $user
     * @param integer $min_state
     * @param string $group_by
     * @param array $filter_by
     * @return DBResult
     */
    public static function findForPrint(User $user, $min_state = STATE_VISIBLE, $group_by = null, $filter_by = null) {

      $visible_project_ids = Projects::findIdsByUser($user, $user->isProjectManager());

      // no point to do anything else if there are no visible projects
      if (!$visible_project_ids) {
        return null;
      } // if

      // initial condition
      $conditions = array(DB::prepare("id IN (?)", array($visible_project_ids)));

      // initial condition
      $conditions[] = DB::prepare('(state >= ?)', array($min_state));
       
      if (!in_array($group_by, array('category_id','company_id','label_id'))) {
        $group_by = null;
      } // if
                
      // filter by completion status
      $filter_is_completed = array_var($filter_by, 'is_completed', null);
      if ($filter_is_completed === '0') {
        $conditions[] = DB::prepare('(completed_on IS NULL)', array(Project::STATUS_ACTIVE));
      } else if ($filter_is_completed === '1') {
        $conditions[] = DB::prepare('(completed_on IS NOT NULL)',array(Project::STATUS_COMPLETED));
      } // if
      
      // do find tasks
      $projects = Projects::find(array(
        'conditions' => implode(' AND ', $conditions),
        'order' => $group_by ? $group_by . ', name' : 'name'
      ));
     
      return $projects;
    } // findForPrint
    
    /**
     * Get trashed map
     * 
     * @param User $user
     * @return array
     */
    static function getTrashedMap($user) {
      return array(
        'project' => DB::executeFirstColumn('SELECT id FROM ' . TABLE_PREFIX . 'projects WHERE state = ? ORDER BY updated_on DESC', STATE_TRASHED)
      );
    } // getTrashedMap
    
    /**
     * Find trashed projects
     * 
     * @param User $user
     * @param array $map
     * @return array
     */
    static function findTrashed(User $user, &$map) {
      $trashed_projects = DB::execute('SELECT id, name, slug FROM ' . TABLE_PREFIX . 'projects WHERE state = ? ORDER BY updated_on DESC', STATE_TRASHED);

      if ($trashed_projects) {
        $view_url = Router::assemble('project', array('project_slug' => '--PROJECT-SLUG--'));

        $items = array();
        foreach ($trashed_projects as $project) {
          $items[] = array(
            'id' => $project['id'],
            'name' => $project['name'],
            'type' => 'Project',
            'permalink' => str_replace('--PROJECT-SLUG--', $project['slug'], $view_url),
            'can_be_parent' => true
          );
        } // foreach

        return $items;
      } else {
        return null;
      } // if
    } // findTrashed

    /**
     * Delete trashed projects
     * 
     * @param User $user
     */
    static function deleteTrashed() {
      $projects = Projects::find(array(
        'conditions' => array('state = ?', STATE_TRASHED)
      ));

      if ($projects) {
        foreach ($projects as $project) {
          $project->state()->delete();
        } // foreach
      } // if

      return true;
    } // deleteTrashed
    
    /**
     * Return number of visible objects
     *
     * @param User $user
     * @return integer
     */
    static function countActive(User $user) {
      return (integer) DB::executeFirstCell('SELECT COUNT(id) FROM ' . TABLE_PREFIX . 'projects WHERE state = ?', STATE_VISIBLE);
    } // countActive
    
  }