<?php

  /**
   * Tracking objects manager
   *
   * @package activeCollab.modules.tracking
   * @subpackage models
   */
  final class TrackingObjects {
    
    /**
     * Returns true if $user can access tracking section of $project
     * 
     * @param IUser $user
     * @param Project $project
     * @param boolean $check_tab
     * @return boolean
     */
    static function canAccess(IUser $user, Project $project, $check_tab = true) {
      return ProjectObjects::canAccess($user, $project, 'tracking', ($check_tab ? 'time' : null));
    } // canAccess
    
    /**
     * Returns true if $user can track time and expanses in $project
     * 
     * @param IUser $user
     * @param Project $project
     * @param boolean $check_tab
     * @return boolean
     */
    static function canAdd(IUser $user, Project $project, $check_tab = true) {
      return ProjectObjects::canAdd($user, $project, 'tracking', ($check_tab ? 'time' : null));
    } // canAdd
    
    /**
     * Returns true if $user can manage time and expenses in $project
     * 
     * @param IUser $user
     * @param Project $project
     * @param boolean $check_tab
     * @return boolean
     */
    static function canManage(IUser $user, Project $project, $check_tab = true) {
      return ProjectObjects::canManage($user, $project, 'tracking', ($check_tab ? 'time' : null));
    } // canManage

    /**
     * Returns true if user can log time/expenses as other user
     *
     * @param User $user
     * @param Project $project
     * @return boolean
     */
    static function canTrackForOthers(User $user, Project $project) {
      return $user->isProjectManager() || $project->getLeaderId() == $user->getId() || $user->projects()->getPermission('tracking', $project) >= ProjectRole::PERMISSION_MANAGE;
    } // canTrackForOthers
    
    // ---------------------------------------------------
    //  Finders
    // ---------------------------------------------------
    
    /**
     * Return tracking objects by parent
     *
     * @param ITracking $parent
     * @param integer $min_state
     * @return array
     */
    static function findByParent(ITracking $parent, $min_state = STATE_VISIBLE) {
      $time_records_table = TABLE_PREFIX . 'time_records';
      $expenses_table = TABLE_PREFIX . 'expenses';
      
      $parent_type = get_class($parent);
      $parent_id = $parent->getId();
      
      $rows = DB::execute("(SELECT 'TimeRecord' AS 'type', $time_records_table.id, $time_records_table.parent_type, $time_records_table.parent_id, $time_records_table.job_type_id AS 'type_id', $time_records_table.state, $time_records_table.original_state, $time_records_table.record_date, $time_records_table.value, $time_records_table.user_id, $time_records_table.user_name, $time_records_table.user_email, $time_records_table.summary, $time_records_table.billable_status, $time_records_table.created_on, $time_records_table.created_by_id, $time_records_table.created_by_name, $time_records_table.created_by_email FROM $time_records_table WHERE parent_type = ? AND parent_id = ? AND state >= ?) UNION ALL
                           (SELECT 'Expense' AS 'type', $expenses_table.id, $expenses_table.parent_type, $expenses_table.parent_id, $expenses_table.category_id AS type_id, $expenses_table.state, $expenses_table.original_state, $expenses_table.record_date, $expenses_table.value, $expenses_table.user_id, $expenses_table.user_name, $expenses_table.user_email, $expenses_table.summary, $expenses_table.billable_status, $expenses_table.created_on, $expenses_table.created_by_id, $expenses_table.created_by_name, $expenses_table.created_by_email FROM $expenses_table WHERE parent_type = ? AND parent_id = ? AND state >= ?) ORDER BY record_date DESC, created_on DESC", $parent_type, $parent_id, $min_state, $parent_type, $parent_id, $min_state);
      
      if($rows) {
        $result = array();
        
        foreach($rows as $row) {
          if($row['type'] == 'TimeRecord') {
            $item = new TimeRecord();
            $row['job_type_id'] = $row['type_id'];
          } else {
            $item = new Expense();
            $row['category_id'] = $row['type_id'];
          } // if
          
          unset($row['type_id']);
          
          $item->loadFromRow($row);
          
          $result[] = $item;
        } // foreach
        
        return $result;
      } else {
        return null;
      } // if
    } // findByParent
    
    /**
     * Return tracking items by project
     *
     * @param IUser $user
     * @param Project $project
     * @param integer $min_state
     * @param integer $min_visibility
     * @return DBResult
     */
    static function findByProject(IUser $user, Project $project, $min_state = STATE_VISIBLE, $min_visibility = VISIBILITY_NORMAL) {
      $time_records_table = TABLE_PREFIX . 'time_records';
      $expenses_table = TABLE_PREFIX . 'expenses';
      
      $parent_conditions = self::prepareParentTypeFilter($user, $project, true, $min_state, $min_visibility);
      
      $result = DB::execute("(SELECT 'TimeRecord' AS 'type', $time_records_table.id, $time_records_table.parent_type, $time_records_table.parent_id, $time_records_table.job_type_id, $time_records_table.state, $time_records_table.original_state, $time_records_table.record_date, $time_records_table.value, $time_records_table.user_id, $time_records_table.user_name, $time_records_table.user_email, $time_records_table.summary, $time_records_table.billable_status, $time_records_table.created_on, $time_records_table.created_by_id, $time_records_table.created_by_name, $time_records_table.created_by_email FROM $time_records_table WHERE $parent_conditions AND state >= $min_state) UNION ALL
                   		       (SELECT 'Expense' AS 'type', $expenses_table.id, $expenses_table.parent_type, $expenses_table.parent_id, '0' AS job_type_id, $expenses_table.state, $expenses_table.original_state, $expenses_table.record_date, $expenses_table.value, $expenses_table.user_id, $expenses_table.user_name, $expenses_table.user_email, $expenses_table.summary, $expenses_table.billable_status, $expenses_table.created_on, $expenses_table.created_by_id, $expenses_table.created_by_name, $expenses_table.created_by_email FROM $expenses_table WHERE $parent_conditions AND state >= $min_state) ORDER BY record_date DESC, created_on DESC");
      
      if($result instanceof DBResult) {
        $result->returnObjectsByField('type');
      } // if
      
      return $result;
    } // findByProject

    /**
     * Find recent tracking records for a given project
     *
     * @param IUser $user
     * @param Project $project
     * @param int $min_state
     * @param int $min_visibility
     * @param int $limit
     * @return DbResult
     */
    static function findRecentByProject(IUser $user, Project $project, $min_state = STATE_VISIBLE, $min_visibility = VISIBILITY_NORMAL, $limit = 300) {
      if($limit === null) {
        $limit_string = '';
      } else {
        $limit = (integer) $limit;

        if($limit < 1) {
          $limit = 300;
        } // if

        $limit_string = "LIMIT 0, $limit";
      } // if

      $time_records_table = TABLE_PREFIX . 'time_records';
      $expenses_table = TABLE_PREFIX . 'expenses';

      $parent_conditions = self::prepareParentTypeFilter($user, $project, true, $min_state, $min_visibility);

      $result = DB::execute("(SELECT 'TimeRecord' AS 'type', $time_records_table.id, $time_records_table.parent_type, $time_records_table.parent_id, $time_records_table.job_type_id, $time_records_table.state, $time_records_table.original_state, $time_records_table.record_date, $time_records_table.value, $time_records_table.user_id, $time_records_table.user_name, $time_records_table.user_email, $time_records_table.summary, $time_records_table.billable_status, $time_records_table.created_on, $time_records_table.created_by_id, $time_records_table.created_by_name, $time_records_table.created_by_email FROM $time_records_table WHERE $parent_conditions AND state >= $min_state) UNION ALL
                   		       (SELECT 'Expense' AS 'type', $expenses_table.id, $expenses_table.parent_type, $expenses_table.parent_id, '0' AS job_type_id, $expenses_table.state, $expenses_table.original_state, $expenses_table.record_date, $expenses_table.value, $expenses_table.user_id, $expenses_table.user_name, $expenses_table.user_email, $expenses_table.summary, $expenses_table.billable_status, $expenses_table.created_on, $expenses_table.created_by_id, $expenses_table.created_by_name, $expenses_table.created_by_email FROM $expenses_table WHERE $parent_conditions AND state >= $min_state) ORDER BY record_date DESC, created_on DESC $limit_string");

      if($result instanceof DBResult) {
        $result->returnObjectsByField('type');
      } // if

      return $result;
    } // findRecentByProject
    
    /**
     * Return all tracked objects for a given project
     *
     * @param Project $project
     * @param integer $min_state
     * @param integer $min_visibility
     * @return DBResult
     */
    static function findAllByProject(Project $project, $min_state = STATE_VISIBLE, $min_visibility = VISIBILITY_NORMAL) {
      $time_records_table = TABLE_PREFIX . 'time_records';
      $expenses_table = TABLE_PREFIX . 'expenses';
      
      $parent_conditions = self::prepareParentTypeFilter(null, $project, true, $min_state, $min_visibility);
      
      $result = DB::execute("(SELECT 'TimeRecord' AS 'type', $time_records_table.id, $time_records_table.parent_type, $time_records_table.parent_id, $time_records_table.job_type_id, $time_records_table.state, $time_records_table.original_state, $time_records_table.record_date, $time_records_table.value, $time_records_table.user_id, $time_records_table.user_name, $time_records_table.user_email, $time_records_table.summary, $time_records_table.billable_status, $time_records_table.created_on, $time_records_table.created_by_id, $time_records_table.created_by_name, $time_records_table.created_by_email FROM $time_records_table WHERE $parent_conditions AND state >= $min_state) UNION ALL
                   		       (SELECT 'Expense' AS 'type', $expenses_table.id, $expenses_table.parent_type, $expenses_table.parent_id, '0' AS job_type_id, $expenses_table.state, $expenses_table.original_state, $expenses_table.record_date, $expenses_table.value, $expenses_table.user_id, $expenses_table.user_name, $expenses_table.user_email, $expenses_table.summary, $expenses_table.billable_status, $expenses_table.created_on, $expenses_table.created_by_id, $expenses_table.created_by_name, $expenses_table.created_by_email FROM $expenses_table WHERE $parent_conditions AND state >= $min_state) ORDER BY record_date DESC, created_on DESC");
      
      if($result instanceof DBResult) {
        $result->returnObjectsByField('type');
      } // if
      
      return $result;
    } // findAllByProject
    
    /**
     * Return list of tracking object IDs for a given project
     * 
     * Id's are grouped by tracking object type
     * 
     * @param Project $project
     * @param integer $min_state
     * @param integer $min_visibility
     * @return array|null
     */
    static function findIdsByProject(Project $project, $min_state = STATE_VISIBLE, $min_visibility = VISIBILITY_NORMAL) {
      $time_records_table = TABLE_PREFIX . 'time_records';
      $expenses_table = TABLE_PREFIX . 'expenses';
      
      $parent_conditions = self::prepareParentTypeFilter(null, $project, true, $min_state, $min_visibility);
      
      $records = DB::execute("(SELECT 'TimeRecord' AS 'type', $time_records_table.id FROM $time_records_table WHERE $parent_conditions AND state >= $min_state) UNION ALL
                              (SELECT 'Expense' AS 'type', $expenses_table.id FROM $expenses_table WHERE $parent_conditions AND state >= $min_state)");
      
      $result = array(
        'TimeRecord' => array(), 
        'Expense' => array(), 
      );
      
      if($records) {
        foreach($records as $record) {
          $result[$record['type']][] = (integer) $record['id'];
        } // foreach
      } // if
      
      return $result;
    } // findIdsByProject
    
    /**
     * Return amount of money spent on given project
     * 
     * Cost is calculated based on job types and expenses
     * 
     * @param Project $project
     * @return float
     */
    static function sumCostByProject(IUser $user, Project $project) {
      $min_state = STATE_VISIBLE;
      $min_visibility = VISIBILITY_PRIVATE;
      
      $billable = DB::escape(BILLABLE_STATUS_BILLABLE);
      
      $time_records_table = TABLE_PREFIX . 'time_records';
      $expenses_table = TABLE_PREFIX . 'expenses';
      
      $parent_conditions = self::prepareParentTypeFilter($user, $project, true, $min_state, $min_visibility);
      
      $rows = DB::execute("(SELECT $time_records_table.value AS 'value', $time_records_table.job_type_id AS 'job_type_id', 'TimeRecord' AS 'type' FROM $time_records_table WHERE $parent_conditions AND state >= $min_state AND billable_status >= $billable) UNION ALL
                           (SELECT $expenses_table.value AS 'value', '0' AS 'job_type_id', 'Expense' AS 'type' FROM $expenses_table WHERE $parent_conditions AND state >= $min_state AND billable_status >= $billable)");
      
      if($rows) {
        $job_types = JobTypes::getIdRateMapFor($project);
        
        $result = 0;
        
        foreach($rows as $row) {
          if($row['type'] == 'TimeRecord') {
            $job_type_id = (integer) $row['job_type_id'];
            
            if(isset($job_types[$job_type_id])) {
              $result += (float) $row['value'] * $job_types[$job_type_id];
            } // if
          } else {
            $result += $row['value'];
          } // if
        } // foreach
        
        return $result;
      } else {
        return 0;
      } // if
    } // sumCostByProject
    
    /**
     * Summarize project cost by job type (expenses are added as Other Expenses)
     * 
     * @param IUser $user
     * @param Project $project
     * @return array
     */
    static function sumCostByTypeAndProject(IUser $user, Project $project) {
      $min_state = STATE_VISIBLE;
      $min_visibility = VISIBILITY_PRIVATE;
      
      $billable = DB::escape(BILLABLE_STATUS_BILLABLE);
      
      $time_records_table = TABLE_PREFIX . 'time_records';
      $expenses_table = TABLE_PREFIX . 'expenses';
      
      $parent_conditions = self::prepareParentTypeFilter($user, $project, true, $min_state, $min_visibility);
      
      $rows = DB::execute("(SELECT $time_records_table.value AS 'value', $time_records_table.job_type_id AS 'job_type_id', 'TimeRecord' AS 'type' FROM $time_records_table WHERE $parent_conditions AND state >= $min_state AND billable_status >= $billable) UNION ALL
                           (SELECT $expenses_table.value AS 'value', '0' AS 'job_type_id', 'Expense' AS 'type' FROM $expenses_table WHERE $parent_conditions AND state >= $min_state AND billable_status >= $billable)");
      
      if($rows) {
        $job_types = JobTypes::getIdRateMapFor($project);
        $job_type_names = JobTypes::getIdNameMap();
        
        $result = array();

        foreach($job_types as $k => $v) {
          $result[$k] = array(
            'name' => $job_type_names[$k],
            'hours' => 0,
            'value' => 0,
            'is_time' => true,
          );
        } // foreach
        
        $result['expenses'] = array(
          'name' => lang('Other Expenses'), 
          'value' => 0,
          'is_time' => false,
        );
        
        foreach($rows as $row) {
          if($row['type'] == 'TimeRecord') {
            $job_type_id = (integer) $row['job_type_id'];
            
            if(isset($job_types[$job_type_id])) {
              if(!isset($result[$job_type_id]['rate'])) {
                $result[$job_type_id]['rate'] = $job_types[$job_type_id];
              } // if

              $result[$job_type_id]['hours'] += (float) $row['value'];
              $result[$job_type_id]['value'] += (float) $row['value'] * $job_types[$job_type_id];
            } // if
          } else {
            $result['expenses']['value'] += (float) $row['value'];
          } // if
        } // foreach
        
        return $result;
      } else {
        return null;
      } // if
    } // sumCostByTypeAndProject
    
    /**
     * Returns summarized time data for given user in a given project
     *
     * @param Project $project
     * @param IUser $for
     * @param IUser $user
     * @param integer $min_state
     * @return array
     */
    static function sumUserTimeRecordsByDate(Project $project, IUser $for, IUser $user, $min_state = STATE_VISIBLE) {
      $parent_conditions = self::prepareParentTypeFilter($user, $project);
      
      $result = array();
      
      $rows = DB::execute("SELECT record_date, SUM(value) AS 'value' FROM " . TABLE_PREFIX . "time_records WHERE $parent_conditions AND user_id = ? AND state >= ? GROUP BY record_date ORDER BY record_date", $for->getId(), $min_state);
      if($rows) {
        foreach($rows as $row) {
          $result[$row['record_date']] = (float) $row['value'];
        } // foreach
      } // if
      
      return $result;
    } // sumUserTimeRecordsByDate
    
    /**
     * Return time records logged for a given user ($for) for a given day in a 
     * given project
     *
     * @param Project $project
     * @param DateValue $day
     * @param IUser $for
     * @param IUser $user
     * @param unknown_type $min_state
     */
    static function findUserTimeRecordsByDate(Project $project, DateValue $day, IUser $for, IUser $user, $min_state = STATE_VISIBLE) {
      return TimeRecords::find(array(
        'conditions' => DB::prepare('user_id = ? AND record_date = ? AND state >= ?', array($for->getId(), $day, $min_state)) . ' AND ' . self::prepareParentTypeFilter($user, $project), 
        'order' => 'created_on DESC', 
      ));
    } // findUserTimeRecordsByDate
    
    /**
     * Return tracking items by project for time expenses log
     *
     * @param IUser $user
     * @param Project $project
     * @param integer $min_state
     * @param integer $min_visibility
     * @return DBResult
     */
    static function findForTimeExpensesLog(IUser $user, Project $project, $min_state = STATE_VISIBLE, $min_visibility = VISIBILITY_NORMAL) {
      $time_records_table = TABLE_PREFIX . 'time_records';
      $expenses_table = TABLE_PREFIX . 'expenses';
      
      $parent_conditions = self::prepareParentTypeFilter($user, $project, true, $min_state, $min_visibility);
      
      $result = DB::execute("(SELECT 'TimeRecord' AS 'type', $time_records_table.id, $time_records_table.parent_type, $time_records_table.parent_id, $time_records_table.job_type_id, $time_records_table.state, $time_records_table.original_state, $time_records_table.record_date, $time_records_table.value, $time_records_table.user_id, $time_records_table.user_name, $time_records_table.user_email, $time_records_table.summary, $time_records_table.billable_status, $time_records_table.created_on, $time_records_table.created_by_id, $time_records_table.created_by_name, $time_records_table.created_by_email FROM $time_records_table WHERE $parent_conditions AND state >= $min_state) UNION ALL
                   		       (SELECT 'Expense' AS 'type', $expenses_table.id, $expenses_table.parent_type, $expenses_table.parent_id, '0' AS job_type_id, $expenses_table.state, $expenses_table.original_state, $expenses_table.record_date, $expenses_table.value, $expenses_table.user_id, $expenses_table.user_name, $expenses_table.user_email, $expenses_table.summary, $expenses_table.billable_status, $expenses_table.created_on, $expenses_table.created_by_id, $expenses_table.created_by_name, $expenses_table.created_by_email FROM $expenses_table WHERE $parent_conditions AND state >= $min_state) ORDER BY record_date DESC, created_on DESC LIMIT 0, 300");

      if($result instanceof DBResult) {
        $result->returnObjectsByField('type');
        
        // get needed users
        $user_ids = DB::executeFirstColumn("(SELECT $time_records_table.user_id FROM $time_records_table WHERE $parent_conditions AND state >= $min_state) UNION (SELECT $expenses_table.user_id FROM $expenses_table WHERE $parent_conditions AND state >= $min_state)");
        $users = Users::getIdDetailsMap($user_ids, array('first_name', 'last_name', 'email', 'company_id'));
        $user_url = Router::assemble('people_company_user', array('company_id' => '--COMPANY--ID--', 'user_id' => '--USER--ID--'));
        
        $objects = array();
        foreach ($result as $tracking_object) {
        	$summarized_tracking_object = array(
  					'id' => $tracking_object->getId(),
  					'class' => get_class($tracking_object),
            'parent_type' => $tracking_object->getParentType(),
            'parent_id' => $tracking_object->getParentId(),
  					'value' => $tracking_object->getValue(),
  					'summary' => $tracking_object->getSummary(),
  					'record_date' => $tracking_object->getRecordDate(),
            'billable_status' => $tracking_object->getBillableStatus(),
        		'permissions' => array(
        			'can_edit' => $tracking_object->canEdit($user),
        			'can_trash' => $tracking_object->state()->canTrash($user)
        		),
        		'urls' => array(
        			'edit' => $tracking_object->getEditUrl(),
        			'trash' => $tracking_object->state()->getTrashUrl()
        		)
  				);
  				
  				$object_user = array_var($users, $tracking_object->getUserId());
  				if ($object_user) {
  					$display_name_params = array(
  						'first_name' => array_var($object_user, 'first_name'),
  						'last_name' => array_var($object_user, 'last_name'),
  						'email' => array_var($object_user, 'email')
  					);
  					
  					$summarized_tracking_object['user'] = array(
  						'id' => $object_user['id'],
  						'display_name' => Users::getUserDisplayName($display_name_params),
  						'short_display_name' => Users::getUserDisplayName($display_name_params, true),
  						'permalink' => str_replace(array('--COMPANY--ID--', '--USER--ID--'), array($object_user['company_id'], $object_user['id']), $user_url)
  					);
  				} else {
  					$display_name_params = array(
  						'full_name' => $tracking_object->getUserName(),
  						'email' => $tracking_object->getUserEmail()
  					);
  					
  					$summarized_tracking_object['user'] = array(
  						'display_name' => Users::getUserDisplayName($display_name_params),
  						'short_display_name' => Users::getUserDisplayName($display_name_params, true),
  						'permalink' => 'mailto:' . $tracking_object->getUserEmail()
  					);
  				} // if
  				
  				$objects[] = $summarized_tracking_object;
        } // foreach
        
        return $objects;
      } // if
      
      return null;
    } // findByProject
    
    /**
     * Prepare tracking items by project for phone list
     *
     * @param IUser $user
     * @param Project $project
     * @param integer $min_state
     * @param integer $min_visibility
     * @return DBResult
     */
    static function findForPhoneList(IUser $user, Project $project, $min_state = STATE_VISIBLE, $min_visibility = VISIBILITY_NORMAL) {
    	$time_records_table = TABLE_PREFIX . 'time_records';
      $expenses_table = TABLE_PREFIX . 'expenses';
      
      $result = array();
    	
      $parent_conditions = self::prepareParentTypeFilter($user, $project);
      
      $rows = DB::execute("(SELECT 'TimeRecord' AS 'type', $time_records_table.id, $time_records_table.parent_type, $time_records_table.parent_id, $time_records_table.job_type_id, $time_records_table.state, $time_records_table.original_state, $time_records_table.record_date, $time_records_table.value, $time_records_table.user_id, $time_records_table.user_name, $time_records_table.user_email, $time_records_table.summary, $time_records_table.billable_status, $time_records_table.created_on, $time_records_table.created_by_id, $time_records_table.created_by_name, $time_records_table.created_by_email FROM $time_records_table WHERE $parent_conditions AND state >= $min_state) UNION ALL
      										 (SELECT 'Expense' AS 'type', $expenses_table.id, $expenses_table.parent_type, $expenses_table.parent_id, '0' AS job_type_id, $expenses_table.state, $expenses_table.original_state, $expenses_table.record_date, $expenses_table.value, $expenses_table.user_id, $expenses_table.user_name, $expenses_table.user_email, $expenses_table.summary, $expenses_table.billable_status, $expenses_table.created_on, $expenses_table.created_by_id, $expenses_table.created_by_name, $expenses_table.created_by_email FROM $expenses_table WHERE $parent_conditions AND state >= $min_state) ORDER BY record_date DESC, created_on ASC");
      
      if($rows instanceof DBResult) {
        $rows->returnObjectsByField('type');
      } // if
      
      if($rows) {
        foreach($rows as $row) {
          $result[$row->getRecordDate()->toMySQL()][] = $row;
        } // foreach
      } // if
      
      return $result;
    } // findForPhoneList
    
    /**
     * Return tracking objects by parent for phone list
     *
     * @param ITracking $parent
     * @return array
     */
    static function findForPhoneListByParent(ITracking $parent) {
    	$result = array();
    	
    	$tracking_objects = TrackingObjects::findByParent($parent);
      if(is_foreachable($tracking_objects)) {
      	foreach($tracking_objects as $tracking_object) {
      		$result[$tracking_object->getRecordDate()->toMySQL()][] = $tracking_object;
      	} // foreach
      } // if
      
      return $result;
    } // findForPhoneListByParent
    
    /**
     * Prepare tracking items by project for print list
     *
     * @param IUser $user
     * @param Project $project
     * @param integer $min_state
     * @param integer $min_visibility
     * @return DBResult
     */
    static function findForPrintList(IUser $user, Project $project, $min_state = STATE_VISIBLE, $min_visibility = VISIBILITY_NORMAL) {
      $time_records_table = TABLE_PREFIX . 'time_records';
      $expenses_table = TABLE_PREFIX . 'expenses';
      
      $result = array();
    	
      $parent_conditions = self::prepareParentTypeFilter($user, $project);
      
      $rows = DB::execute("(SELECT 'TimeRecord' AS 'type', $time_records_table.id, $time_records_table.parent_type, $time_records_table.parent_id, $time_records_table.record_date, $time_records_table.value, $time_records_table.user_id, $time_records_table.user_name, $time_records_table.user_email, $time_records_table.summary, $time_records_table.billable_status, $time_records_table.created_on FROM $time_records_table WHERE $parent_conditions AND state >= $min_state) UNION ALL
      	(SELECT 'Expense' AS 'type', $expenses_table.id, $expenses_table.parent_type, $expenses_table.parent_id, $expenses_table.record_date, $expenses_table.value, $expenses_table.user_id, $expenses_table.user_name, $expenses_table.user_email, $expenses_table.summary, $expenses_table.billable_status, $expenses_table.created_on FROM $expenses_table WHERE $parent_conditions AND state >= $min_state) ORDER BY record_date DESC, created_on ASC LIMIT 0, 300");

      if($rows) {
        $rows->setCasting(array(
          'id' => DBResult::CAST_INT,
          'parent_id' => DBResult::CAST_INT,
          'user_id' => DBResult::CAST_INT,
          'billable_status' => DBResult::CAST_INT,
          'record_date' => DBResult::CAST_DATE,
        ));

        $rows = $rows->toArray();

        $task_ids = array();
        $user_ids = array();

        foreach($rows as $row) {
          if($row['parent_type'] == 'Task' && !in_array($row['parent_id'], $task_ids)) {
            $task_ids[] = $row['parent_id'];
          } // if

          if($row['user_id'] && !in_array($row['user_id'], $user_ids)) {
            $user_ids[] = $row['user_id'];
          } // if
        } // foreach

        // Get task data
        $tasks = array();
        if($task_ids) {
          $task_rows = DB::execute('SELECT id, name FROM ' . TABLE_PREFIX . 'project_objects WHERE type = ? AND id IN (?) AND state >= ?', 'Task', $task_ids, STATE_ARCHIVED);
          if($task_rows) {
            foreach($task_rows as $task_row) {
              $tasks[(integer) $task_row['id']] = $task_row['name'];
            } // foreach
          } // if
        } // if

        // Get user data
        $users = array();
        if($user_ids) {
          $user_rows = DB::execute('SELECT id, first_name, last_name, email FROM ' . TABLE_PREFIX . 'users WHERE id IN (?) AND state >= ?', $user_ids, STATE_ARCHIVED);
          if($user_rows) {
            foreach($user_rows as $user_row) {
              $users[(integer) $user_row['id']] = Users::getUserDisplayName($user_row, true);
            } // foreach
          } // if
        } // if

        $project_name = $project->getName();
        foreach($rows as $k => $row) {
          $task_id = $row['parent_type'] == 'Task' ? $row['parent_id'] : null;
          $user_id = $row['user_id'];

          if($task_id && isset($tasks[$task_id])) {
            $rows[$k]['parent_name'] = $tasks[$task_id];
          } else {
            $rows[$k]['parent_name'] = $project_name;
          } // if

          if($user_id && isset($users[$user_id])) {
            $rows[$k]['user_display_name'] = $users[$user_id];
          } else {
            $rows[$k]['user_display_name'] = Users::getUserDisplayName(array(
              'full_name' => $row['user_name'],
              'email' => $row['user_email']
            ), true);
          } // if
        } // foreach
      } // if

      return group_by_date($rows, $user, 'record_date', true);
    } // findForPhoneList
    
    // ---------------------------------------------------
    //  Type filter
    // ---------------------------------------------------
    
    /**
     * Cached parent type filters
     *
     * @var array
     */
    static private $prepared_parent_type_filters = array();
    
    /**
     * Prepare parent type filter
     * 
     * $user is optional
     * 
     * When $min_state is not provided, system will use state of parent object 
     * as minimal state
     * 
     * When $min_visibility is not provided, system will use user's min 
     * visibility (or VISIBILITY_PRIVATE when user is not provided) as min 
     * visibility
     * 
     * @param IUser $user
     * @param ITracking $parent
     * @param boolean $include_subojects
     * @param integer $min_state
     * @param integer $min_visibility
     * @return string
     */
    static function prepareParentTypeFilter($user, ITracking $parent, $include_subobjects = true, $min_state = null, $min_visibility = null) {
      if($parent instanceof Project || $parent instanceof Task) {
        $parent_type = get_class($parent);
        $parent_id = $parent->getId();
        
        $cache_key = $include_subobjects ? "{$parent_id}_with_subojects" : "{$parent_id}_without_subojects";
        
        if(isset(self::$prepared_parent_type_filters[$parent_type]) && isset(self::$prepared_parent_type_filters[$parent_type][$cache_key])) {
          return self::$prepared_parent_type_filters[$parent_type][$cache_key];
        } // if
        
        if($min_state === null) {
          $min_state = $parent instanceof IState ? $parent->getState() : STATE_VISIBLE;
        } // if
        
        if($min_visibility === null) {
          $min_visibility = $user instanceof IUser ? $user->getMinVisibility() : VISIBILITY_PRIVATE;
        } // if
        
        $types = array($parent_type => array($parent_id));
        
        if($include_subobjects && $parent instanceof Project) {
          $project_objects_table = TABLE_PREFIX . 'project_objects';
          
          // Tasks
          $project_objects = DB::execute("SELECT id, type FROM $project_objects_table WHERE type = 'Task' AND project_id = ? AND state >= ? AND visibility >= ?", $parent->getId(), $min_state, $min_visibility);
          
          if($project_objects) {
            foreach($project_objects as $project_object) {
              $project_object_id = (integer) $project_object['id'];
              
              if(!isset($types[$project_object['type']])) {
                $types[$project_object['type']] = array();
              } // if
              
              $types[$project_object['type']][] = $project_object_id;
            } // foreach
          } // if
        } // if
        
        // Prepare conditions
        $result = array();
        
        foreach($types as $type_name => $object_ids) {
          $result[] = DB::prepare('(parent_type = ? AND parent_id IN (?))', array($type_name, array_unique($object_ids)));
        } // if
        
        if(isset(self::$prepared_parent_type_filters[$parent_type])) {
          self::$prepared_parent_type_filters[$parent_type][$cache_key] = '(' . implode(' OR ', $result) . ')';
        } else {
          self::$prepared_parent_type_filters[$parent_type] = array($cache_key => '(' . implode(' OR ', $result) . ')');
        } // if
        
        return self::$prepared_parent_type_filters[$parent_type][$cache_key];
      } else {
        throw new InvalidInstanceError('parent', $parent, array('Project', 'ProjectObject'));
      } // if
    } // prepareParentTypeFilter
    
  }