<?php

  /**
   * TrackingReport class
   *
   * @package activeCollab.modules.tracking
   * @subpackage models
   */
  class TrackingReport extends BaseTrackingReport implements IRoutingContext, IInvoiceBasedOn {
    
    // User filter
    const USER_FILTER_ANYBODY = 'anybody';
    const USER_FILTER_LOGGED_USER = 'logged_user';
    const USER_FILTER_COMPANY = 'company';
    const USER_FILTER_SELECTED = 'selected';
    
    // Date filters
    const DATE_FILTER_ANY = 'any';
    const DATE_FILTER_YESTERDAY = 'yesterday';
    const DATE_FILTER_TODAY = 'today';
    const DATE_FILTER_LAST_WEEK = 'last_week';
    const DATE_FILTER_THIS_WEEK = 'this_week';
    const DATE_FILTER_LAST_MONTH = 'last_month';
    const DATE_FILTER_THIS_MONTH = 'this_month';
    const DATE_FILTER_SELECTED_DATE = 'selected_date';
    const DATE_FILTER_SELECTED_RANGE = 'selected_range';
    
    // Project filter
    const PROJECT_FILTER_ANY = 'any';
    const PROJECT_FILTER_ACTIVE = 'active';
    const PROJECT_FILTER_COMPLETED = 'completed';
    const PROJECT_FILTER_CATEGORY = 'category';
    const PROJECT_FILTER_CLIENT = 'client';
    const PROJECT_FILTER_SELECTED = 'selected';
    
    // Billable filter
    const BILLABLE_FILTER_ALL = 'all';
    const BILLABLE_FILTER_BILLABLE = 'billable';
    const BILLABLE_FILTER_NOT_BILLABLE = 'not_billable';
    const BILLABLE_FILTER_BILLABLE_PAID = 'billable_paid';
    const BILLABLE_FILTER_BILLABLE_NOT_PAID = 'billable_not_paid';
    const BILLABLE_FILTER_PENDING_PAYMENT = 'pending_payment';
    
    // Type filter
    const TYPE_FILTER_ANY = 'any';
    const TYPE_FILTER_TIME = 'time';
    const TYPE_FILTER_EXPENSES = 'expenses';
    
    // Job type filter
    const JOB_TYPE_FILTER_ANY = 'any';
    const JOB_TYPE_FILTER_SELECTED = 'selected';
    
    // Expenses categories filter
    const EXPENSE_CATEGORY_FILTER_ANY = 'any';
    const EXPENSE_CATEGORY_FILTER_SELECTED = 'selected';
    
    // Group
    const DONT_GROUP = 'dont';
    const GROUP_BY_DATE = 'date';
    const GROUP_BY_PROJECT = 'project';
    const GROUP_BY_PROJECT_CLIENT = 'project_client';
    
    /**
     * Return company
     *
     * If all records have one company use it, otherwise use the first that isn't owner
     * 
     * @return Company
     */
    function getCompany(User $user) {
      $this->setGroupBy(TrackingReport::DONT_GROUP);
      $this->setSumByUser(false);
      
      $results = $this->run($user);
      
      if(is_foreachable($results)) {
        $records = $results[0]['records'];
        if(is_foreachable($records)) {
          foreach($records as $record) {
            $client_ids[] = $record['client_id'];
          }//foreach
        }//if
      }//if
      
      $unique_client_ids = array_unique($client_ids);
      
      if(is_foreachable($unique_client_ids)) {
        if(count($unique_client_ids) == 1) {
          return Companies::findById($unique_client_ids[0]);
        } else {
          foreach($unique_client_ids as $client_id) {
            $company = Companies::findById($client_id);
            if(!$company->isOwner()) {
              return $company;
            }//if
          }//foreach
        }//if
      }//if
      
      return Companies::findOwnerCompany();
    } // getCompany
    
    /**
     * Return report total time 
     * 
     * @param $user
     * @param $status
     * @return float
     */
    function getTotalTime(IUser $user, $status = null) {
      $this->setGroupBy(TrackingReport::DONT_GROUP);
      $this->setSumByUser(false);
      
      $results = $this->run($user);
      
      $total = 0;
      
      if(is_foreachable($results)) {
        $records = $results[0]['records'];
        if(is_foreachable($records)) {
          foreach($records as $record) {
            if($record['type'] == 'TimeRecord') {
              if($status) {
                if($record['billable_status'] == $status) {
                  $total += $record['value'];
                }//if
              } else {
                $total += $record['value'];
              }//if
            }//if
          }//foreach
        }//if
      }//if
      
      return $total;
    } // getTotalTime
    
    /**
     * Return report total expenses 
     * 
     * @param $user
     * @param $status
     * 
     * @return float
     */
    function getTotalExpenses(IUser $user, $status = null) {
      $this->setGroupBy(TrackingReport::DONT_GROUP);
      $this->setSumByUser(false);
      
      $results = $this->run($user);
      
      $currency_map = Currencies::getIdDetailsMap();
      if(is_foreachable($results)) {
        $records = $results[0]['records'];
        $total = array();

        if(is_foreachable($records)) {
          foreach($records as $record) {
            if($record['type'] == 'Expense') {
              if($status) {
                if($record['billable_status'] == $status) {
                  $total[$currency_map[$record['currency_id']]['code']] += $record['value'];
                }//if
              } else {
                $total[$currency_map[$record['currency_id']]['code']] += $record['value'];
              }//if
            }//if
          }//foreach
        }//if
      } else {
        $total = null;
      }//if
      
      return $total;
    } // getTotalExpenses
    
    /**
     * Run report
     *
     * @param User $user
     * @return array
     */
    function run(User $user) {
      if($user instanceof User) {
        $conditions = $this->prepareConditions($user);
        
        if($conditions !== false) {
          if($this->getSumByUser()) {
            return $this->runSummarized($user, $conditions);
          } else {
            return $this->runList($user, $conditions);
          } // if
        } else {
          return null;
        } // if
      } else {
        throw new InvalidParamError('user', $user, 'User');
      } // if
    } // run
    
    /**
     * Run report and export content to temporal CSV file
     * 
     * @param User $user
     * @return string
     */
    function runToCsv(User $user) {
      if($user instanceof User) {
        $conditions = $this->prepareConditions($user);
        
        if($conditions !== false) {
          $filename = AngieApplication::getAvailableWorkFileName('assignment-filter-export', 'csv');
          
          $handle = fopen($filename, 'w');
          if($handle) {
            
            // Sumarized by user
            if($this->getSumByUser()) {
              $currencies = Currencies::getIdDetailsMap();

              switch($this->getGroupBy()) {
                case self::GROUP_BY_DATE:
                  $columns = array(lang('Date'), lang('Date, Formatted'));
                  break;
                case self::GROUP_BY_PROJECT:
                  $columns = array(lang('Project ID'), lang('Project Name'));
                  break;
                case self::GROUP_BY_PROJECT_CLIENT:
                  $columns = array(lang('Client ID'), lang('Client Name'));
                  break;
                default:
                  $columns = array();
              } // switch

              $columns[] = 'User ID';
              $columns[] = 'User Name';
              $columns[] = 'User Email';
              
              if($this->queryTimeRecords()) {
                $columns[] = 'Time';
              } // if
              
              if($this->queryExpenses()) {
                foreach($currencies as $details) {
                  $columns[] = 'Expenses (' . $details['code'] . ')';
                } // if
              } // if
              
              fwrite($handle, array_to_csv_row($columns));
              
              $result = $this->runSummarized($user, $conditions);
              if($result) {
                foreach($result as $group_name => $group_data) {
                  if(isset($group_data['records']) && is_array($group_data['records'])) {
                    foreach($group_data['records'] as $record) {
                      if($this->getGroupBy() != self::DONT_GROUP) {
                        $csv_record = array($group_name, $group_data['label']);
                      } else {
                        $csv_record = array();
                      } // if

                      $csv_record[] = $record['user_id'];
                      $csv_record[] = $record['user_name'];
                      $csv_record[] = $record['user_email'];

                      if($this->queryTimeRecords()) {
                        $csv_record[] = $record['time'];
                      } // if
                      
                      if($this->queryExpenses()) {
                        foreach($currencies as $currency_id => $details) {
                          $csv_record[] = $record["expenses_for_{$currency_id}"];
                        } // foreach
                      } // if
                      
                      fwrite($handle, array_to_csv_row($csv_record));
                    } // if
                  } // if
                } // foreach
              } // if
              
            // Plain list
            } else {
              $columns = array(
                'Type', 
                'ID', 
                'Group ID', 
                'Group Name', 
                'Parent Type', 
                'Parent ID', 
                'Parent Name', 
                'Project ID', 
                'Project Name', 
                'Client ID', 
                'Client Name', 
                'Record Date', 
                'User ID', 
                'User Name', 
                'User Email', 
                'Summary', 
                'Value', 
                'Status', 
              );
              
              fwrite($handle, array_to_csv_row($columns));
              
              $old_group_by = $this->getGroupBy();
              $this->setGroupBy(self::DONT_GROUP);
              
              $result = $this->runList($user, $conditions);
              
              $this->setGroupBy($old_group_by);
              if($result) {
                foreach($result as $group_name => $group_data) {
                  if(isset($group_data['records']) && is_array($group_data['records'])) {
                    foreach($group_data['records'] as $record) {
                      switch($record['billable_status']) {
                        case BILLABLE_STATUS_NOT_BILLABLE:
                          $status = 'Not Billable'; break;
                        case BILLABLE_STATUS_BILLABLE:
                          $status = 'Billable'; break;
                        case BILLABLE_STATUS_PENDING_PAYMENT:
                          $status = 'Pending Payment'; break;
                        case BILLABLE_STATUS_PAID:
                          $status = 'Paid'; break;
                        default:
                          $status = 'Unknown';
                      } // if
                      
                      fwrite($handle, array_to_csv_row(array(
                        $record['type'], 
                        $record['id'], 
                        $record['group_id'], 
                        $record['group_name'], 
                        $record['parent_type'], 
                        $record['parent_id'], 
                        $record['parent_name'], 
                        $record['project_id'], 
                        $record['project_name'], 
                        $record['client_id'], 
                        $record['client_name'],  
                        $record['record_date'] instanceof DateValue ? $record['record_date']->toMySQL() : null, 
                        $record['user_id'], 
                        $record['user_name'], 
                        $record['user_email'], 
                        $record['summary'], 
                        $record['value'], 
                        $status, 
                      )));
                    } // if
                  } // if
                } // foreach
              } // if
            } // if
            
            fclose($handle);
            
            return $filename;
          } // if
        } // if
      } else {
        throw new InvalidParamError('user', $user, 'User');
      } // if
      
      return false;
    } // runToCsv
    
    /**
     * Execute report that displays records, instead of summarized data
     *
     * @param User $user
     * @param string $conditions
     * @return array
     */
    private function runList($user, $conditions) {
      $time_records_table = TABLE_PREFIX . 'time_records';
      $expenses_table = TABLE_PREFIX . 'expenses';
      
      $queries = array();
        
      if($this->queryTimeRecords()) {
        if($this->getJobTypeFilter() == self::JOB_TYPE_FILTER_SELECTED) {
          $queries[] = DB::prepare("(SELECT id, 'TimeRecord' AS 'type', parent_type, parent_id, job_type_id AS 'group_id', record_date, user_id, user_name, user_email, summary, value, billable_status FROM $time_records_table WHERE $conditions AND job_type_id IN (?) ORDER BY record_date DESC)", array($this->getJobTypeIds()));
        } else {
          $queries[] = "(SELECT id, 'TimeRecord' AS 'type', parent_type, parent_id, job_type_id AS 'group_id', record_date, user_id, user_name, user_email, summary, value, billable_status FROM $time_records_table WHERE $conditions ORDER BY record_date DESC)";
        } // if
      } // if
      
      if($this->queryExpenses()) {
        if($this->getExpenseCategoryFilter() == self::EXPENSE_CATEGORY_FILTER_SELECTED) {
          $queries[] = DB::prepare("(SELECT id, 'Expense' AS 'type', parent_type, parent_id, category_id AS 'group_id', record_date, user_id, user_name, user_email, summary, value, billable_status FROM $expenses_table WHERE $conditions AND category_id IN (?) ORDER BY record_date DESC)", array($this->getExpenseCategoryIds()));
        } else {
          $queries[] = "(SELECT id, 'Expense' AS 'type', parent_type, parent_id, category_id AS 'group_id', record_date, user_id, user_name, user_email, summary, value, billable_status FROM $expenses_table WHERE $conditions ORDER BY record_date DESC)";
        } // if
      } // if
      
      $rows = count($queries) == 1 ? DB::execute($queries[0]) : DB::execute(implode(' UNION ALL ', $queries));
      if($rows) {
        $rows->setCasting(array(
          'id' => DBResult::CAST_INT, 
          'parent_id' => DBResult::CAST_INT,
          'group_id' => DBResult::CAST_INT, 
          'user_id' => DBResult::CAST_INT, 
          'record_date' => DBResult::CAST_DATE, 
          'billable_status' => DBResult::CAST_INT, 
          'value' => DBResult::CAST_FLOAT, 
        ));
        
        $rows = $rows->toArray();
        $this->populateProjectInfo($rows);
        $this->populateParentInfo($rows);
        
        $user_ids = array();
        foreach($rows as $row) {
          if($row['user_id']) {
            $user_ids[] = $row['user_id'];
          } // if
        } // foreach
        
        $users = Users::getIdNameMap($user_ids);
        
        $job_types = $this->queryTimeRecords() ? JobTypes::getIdNameMap() : array();
        $expense_categories = $this->queryExpenses() ? ExpenseCategories::getIdNameMap() : array();
        
        // Populate date that's general for all grouping methods
        foreach($rows as &$row) {
          if($row['user_id'] && $users && isset($users[$row['user_id']])) {
            $row['user_name'] = $user_name = $users[$row['user_id']];
          } else {
            $row['user_name'] = $row['user_name'] ? $row['user_name'] : $row['user_email'];
          } // if
          
          if($row['type'] == 'TimeRecord') {
            $row['group_name'] = $row['group_id'] && isset($job_types[$row['group_id']]) ? $job_types[$row['group_id']] : '';
          } else {
            $row['group_name'] = $row['group_id'] && isset($expense_categories[$row['group_id']]) ? $expense_categories[$row['group_id']] : '';
          } // if
        } // foreach

        unset($row); // break the reference

        // Group by date
        if($this->getGroupBy() == self::GROUP_BY_DATE) {
          $result = array();
          
          foreach($rows as $row) {
            $record_date = $row['record_date'] instanceof DateValue ? $row['record_date']->formatForUser($user, 0) : lang('Unknown Date');
            
            if(!isset($result[$record_date])) {
              
              $result[$record_date] = array(
                'label' => $record_date, 
                'records' => array(), 
              );
            } // if
            
            $result[$record_date]['records'][] = $row;
          } // foreach
          
          return $result;
          
        // Group by project
        } elseif($this->getGroupBy() == self::GROUP_BY_PROJECT) {
          $result = array();

          foreach($rows as $row) {
            if(!isset($result[$row['project_id']])) {
              $result[$row['project_id']] = array(
                'label' => $row['project_name'], 
                'records' => array(), 
              ); 
            } // if

            $result[$row['project_id']]['records'][] = $row;
          } // foreach
          
          return $result;
        
        // Group by project client  
        } elseif($this->getGroupBy() == self::GROUP_BY_PROJECT_CLIENT) {
          $result = array();
          
          $this->populateProjectClientInfo($rows);
          
          foreach($rows as $row) {
            if(!isset($result[$row['client_id']])) {
              $result[$row['client_id']] = array(
                'label' => $row['client_name'], 
                'records' => array(), 
              ); 
            } // if
            
            $result[$row['client_id']]['records'][] = $row;
          } // foreach
          
          return $result;
          
        // Don't group
        } else {
          return array(
            array(
              'label' => 'All Records', 
              'records' => $rows,
            )
          );
        } // if
      } // if
    } // runList
    
    /**
     * Execute records summarized by user
     *
     * @param User $user
     * @param string $conditions
     * @return array
     */
    private function runSummarized(User $user, $conditions) {
      if($this->getGroupBy() == self::GROUP_BY_DATE) {
        return $this->runSummarizedGroupedByDate($user, $conditions);
      } elseif($this->getGroupBy() == self::GROUP_BY_PROJECT) {
        return $this->runSummarizedGroupedByProject($user, $conditions);
      } elseif($this->getGroupBy() == self::GROUP_BY_PROJECT_CLIENT) {
        return $this->runSummarizedGroupedByProjectClient($user, $conditions);
      } else {
        return $this->runSummarizedButNotGrouped($user, $conditions);
      } // if
    } // runSummarized
    
    /**
     * Run summarized report where records are grouped by date
     * 
     * @parma User $user
     * @param string $conditions
     * @return array
     */
    private function runSummarizedGroupedByDate(User $user, $conditions) {
      $result = array();

      $rows = DB::execute($this->getQueryForSummarizedReport($conditions));
      if($rows instanceof DBResult) {
        $rows->setCasting(array(
          'user_id' => DBResult::CAST_INT, 
          'record_date' => DBResult::CAST_DATE, 
          'value' => DBResult::CAST_FLOAT, 
        ));
        
        $user_ids = array();
        foreach($rows as $row) {
          if($row['user_id']) {
            $user_ids[] = (integer) $row['user_id'];
          } // if
        } // foreach
        
        $users = Users::getIdNameMap($user_ids);
        
        foreach($rows as $row) {
          $record_date = $row['record_date'] instanceof DateValue ? $row['record_date']->format("Y-m-d") : '0000-00-00';
          $currency_id = $row['currency_id'];
          
          if(!isset($result[$record_date])) {
            $result[$record_date] = array(
              'label' => $row['record_date'] instanceof DateValue ? $row['record_date']->formatForUser($user, 0) : lang('Unknown Date'),
              'records' => array(), 
            );
          } // if
          
          // Update row that's already been added (we maybe added time or expenses, but now need to set the other attribute)
          if(isset($result[$record_date]['records'][$row['user_email']])) {
            if($this->queryTimeRecords() && $row['type'] == 'TimeRecord') {
              $result[$record_date]['records'][$row['user_email']]['time'] = $row['value'];
            } elseif($this->queryExpenses() && $row['type'] == 'Expense') {
              $result[$record_date]['records'][$row['user_email']]["expenses_for_{$currency_id}"] = $row['value'];
            } // if
            
          // New row
          } else {
            if($row['user_id'] && $users && isset($users[$row['user_id']])) {
              $user_name = $users[$row['user_id']];
            } else {
              $user_name = $row['user_name'] ? $row['user_name'] : $row['user_email'];
            } // if
            
            $result[$record_date]['records'][$row['user_email']] = array(
              'user_id' => (integer) $row['user_id'], 
              'user_name' => $user_name, 
              'user_email' => $row['user_email'], 
            );
            
            if($this->queryTimeRecords()) {
              $result[$record_date]['records'][$row['user_email']]['time'] = $row['type'] == 'TimeRecord' ? $row['value'] : 0;
            } // if
            
            if($this->queryExpenses()) {
              $this->prepareCurrencyExpenseColumns($result[$record_date]['records'][$row['user_email']]);
              
              if($row['type'] == 'Expense') {
                $result[$record_date]['records'][$row['user_email']]["expenses_for_{$currency_id}"] = $row['value'];
              } // if
            } // if
          } // if
        } // foreach
        
        krsort($result);
      } // if
      
      return $result;
    } // runSummarizedGroupedByDate
    
    /**
     * Run summarized report where records are grouped by project
     * 
     * @param User $user
     * @param string $conditions
     * @return array
     */
    private function runSummarizedGroupedByProject(User $user, $conditions) {
      $result = array();
      
      $rows = DB::execute($this->getQueryForSummarizedReport($conditions));
      if($rows instanceof DBResult) {
        $rows->setCasting(array(
          'parent_id' => DBResult::CAST_INT, 
          'user_id' => DBResult::CAST_INT, 
          'value' => DBResult::CAST_FLOAT, 
        ));
        
        $rows = $rows->toArray();
        
        $this->populateProjectInfo($rows);
        
        $user_ids = array();
        foreach($rows as $row) {
          if($row['user_id']) {
            $user_ids[] = $row['user_id'];
          } // if
        } // foreach
        
        $users = Users::getIdNameMap($user_ids);
        
        foreach($rows as &$row) {
          $project_id = (integer) $row['project_id'];
          $currency_id = $row['currency_id'];
          
          if(!isset($result[$project_id])) {
            $result[$project_id] = array(
              'label' => $row['project_name'] ? $row['project_name'] : lang('Unknown Project'), 
              'records' => array(), 
            );
          } // if
          
          // Update existing
          if(isset($result[$project_id]['records'][$row['user_email']])) {
            if($this->queryTimeRecords() && $row['type'] == 'TimeRecord') {
              $result[$project_id]['records'][$row['user_email']]['time'] += $row['value'];
            } elseif($this->queryExpenses() && $row['type'] == 'Expense') {
              $result[$project_id]['records'][$row['user_email']]["expenses_for_{$currency_id}"] += $row['value'];
            } // if
            
          // New record
          } else {
            if($row['user_id'] && $users && isset($users[$row['user_id']])) {
              $user_name = $users[$row['user_id']];
            } else {
              $user_name = $row['user_name'] ? $row['user_name'] : $row['user_email'];
            } // if
            
            $result[$project_id]['records'][$row['user_email']] = array(
              'user_id' => (integer) $row['user_id'], 
              'user_name' => $user_name, 
              'user_email' => $row['user_email'], 
            );
            
            if($this->queryTimeRecords()) {
              $result[$project_id]['records'][$row['user_email']]['time'] = $row['type'] == 'TimeRecord' ? (float) $row['value'] : 0;
            } // if
            
            if($this->queryExpenses()) {
              $this->prepareCurrencyExpenseColumns($result[$project_id]['records'][$row['user_email']]);
              
              if($row['type'] == 'Expense') {
                $result[$project_id]['records'][$row['user_email']]["expenses_for_{$currency_id}"] = (float) $row['value'];
              } // if
            } // if
          } // if
        } // foreach

        unset($row); // destroy the reference
      } // if
      
      return $result;
    } // runSummarizedGroupedByProject
    
    /**
     * Run summarized report where records are grouped by project client
     * 
     * @param User $user
     * @param string $conditions
     * @return array
     */
    private function runSummarizedGroupedByProjectClient(User $user, $conditions) {
      $result = array();
      
      $rows = DB::execute($this->getQueryForSummarizedReport($conditions));
      if($rows instanceof DBResult) {
        $rows->setCasting(array(
          'parent_id' => DBResult::CAST_INT, 
          'user_id' => DBResult::CAST_INT, 
          'value' => DBResult::CAST_FLOAT, 
        ));
        
        $rows = $rows->toArray();
        
        $this->populateProjectInfo($rows);
        
        $user_ids = array();
        foreach($rows as $row) {
          if($row['user_id']) {
            $user_ids[] = $row['user_id'];
          } // if
        } // foreach
        
        $users = Users::getIdNameMap($user_ids);
        
        $this->populateProjectClientInfo($rows);
            
        foreach($rows as $row) {
          $currency_id = $row['currency_id'];

          $client_id = (integer) $row['client_id'];
          
          if(!isset($result[$client_id])) {
            $result[$client_id] = array(
              'label' => $row['client_name'], 
              'records' => array(), 
            );
          } // if
          
          // Update existing
          if(isset($result[$client_id]['records'][$row['user_email']])) {
            if($this->queryTimeRecords() && $row['type'] == 'TimeRecord') {
              $result[$client_id]['records'][$row['user_email']]['time'] += (float) $row['value'];
            } elseif($this->queryExpenses() && $row['type'] == 'Expense') {
              $result[$client_id]['records'][$row['user_email']]["expenses_for_{$currency_id}"] += (float) $row['value'];
            } // if
            
          // New record
          } else {
            if($row['user_id'] && $users && isset($users[$row['user_id']])) {
              $user_name = $users[$row['user_id']];
            } else {
              $user_name = $row['user_name'] ? $row['user_name'] : $row['user_email'];
            } // if
            
            $result[$client_id]['records'][$row['user_email']] = array(
              'user_id' => (integer) $row['user_id'], 
              'user_name' => $user_name, 
              'user_email' => $row['user_email'], 
            );
            
            if($this->queryTimeRecords()) {
              $result[$client_id]['records'][$row['user_email']]['time'] = $row['type'] == 'TimeRecord' ? (float) $row['value'] : 0;
            } // if
            
            if($this->queryExpenses()) {
              $this->prepareCurrencyExpenseColumns($result[$client_id]['records'][$row['user_email']]);
              
              if($row['type'] == 'Expense') {
                $result[$client_id]['records'][$row['user_email']]["expenses_for_{$currency_id}"] = (float) $row['value'];
              } // if
            } // if
          } // if
        } // foreach
      } // if
      
      return $result;
    } // runSummarizedGroupedByProjectClient
    
    /**
     * Run summarized report where records are not grouped
     * 
     * @param User $user
     * @param string $conditions
     * @return array
     */
    private function runSummarizedButNotGrouped(User $user, $conditions) {
      $result = array(
        'all' => array(
          'label' => lang('All Matched Records'), 
          'records' => array(), 
        ), 
      );
      
      $rows = DB::execute($this->getQueryForSummarizedReport($conditions));
      if($rows) {
        $rows->setCasting(array(
          'parent_id' => DBResult::CAST_INT, 
          'user_id' => DBResult::CAST_INT, 
          'value' => DBResult::CAST_FLOAT, 
        ));
        
        $rows = $rows->toArray();
        
        $user_ids = array();
        foreach($rows as $row) {
          if($row['user_id']) {
            $user_ids[] = (integer) $row['user_id'];
          } // if
        } // foreach
        
        $users = Users::getIdNameMap($user_ids);
        
        foreach($rows as $row) {
          $currency_id = $row['currency_id'];
          
          // Update row that's already been added
          if(isset($result['all']['records'][$row['user_email']])) {
            if($this->queryTimeRecords() && $row['type'] == 'TimeRecord') {
              $result['all']['records'][$row['user_email']]['time'] = $row['value'];
            } elseif($this->queryExpenses() && $row['type'] == 'Expense') {
              $result['all']['records'][$row['user_email']]["expenses_for_{$currency_id}"] = $row['value'];
            } // if
            
          // New row
          } else {
            if($row['user_id'] && isset($users[$row['user_id']])) {
              $user_name = $users[$row['user_id']];
            } else {
              $user_name = $row['user_name'] ? $row['user_name'] : $row['user_email'];
            } // if
            
            $result['all']['records'][$row['user_email']] = array(
              'user_id' => (integer) $row['user_id'], 
              'user_name' => $user_name, 
              'user_email' => $row['user_email'], 
            );
            
            if($this->queryTimeRecords()) {
              $result['all']['records'][$row['user_email']]['time'] = $row['type'] == 'TimeRecord' ? $row['value'] : 0;
            } // if
            
            if($this->queryExpenses()) {
              $this->prepareCurrencyExpenseColumns($result['all']['records'][$row['user_email']]);
              
              if($row['type'] == 'Expense') {
                $result['all']['records'][$row['user_email']]["expenses_for_{$currency_id}"] = $row['value'];
              } // if
            } // if
          } // if
        } // forech
      } // if
      
      return $result;
    } // runSummarizedButNotGrouped
    
    /**
     * Prepare expense columns for all currencies in a given row
     * 
     * @param array $row
     */
    private function prepareCurrencyExpenseColumns(&$row) {
      $currencies = Currencies::getIdNameMap();
      
      if($currencies) {
        foreach($currencies as $currency_id => $currency_name) {
          $row["expenses_for_{$currency_id}"] = 0;
        } // foreach
      } // if
    } // prepareCurrencyExpenseColumns
    
    /**
     * Return queries for summarized reports
     * 
     * @param string $conditions
     * @return array
     */
    private function getQueryForSummarizedReport($conditions) {
      $time_records_table = TABLE_PREFIX . 'time_records';
      $expenses_table = TABLE_PREFIX . 'expenses';
      
      if($this->getGroupBy() == self::GROUP_BY_DATE) {
        $group_by = 'user_id, record_date';
        $extra_fields = ', record_date';
      } elseif($this->getGroupBy() == self::GROUP_BY_PROJECT || $this->getGroupBy() == self::GROUP_BY_PROJECT_CLIENT) {
        $group_by = 'user_id, parent';
        $extra_fields = ", parent_type, parent_id, CONCAT(parent_type, '-', parent_id) AS 'parent'";
      } else {
        $group_by = 'user_id';
        $extra_fields = '';
      } // if
      
      $queries = array();
        
      if($this->queryTimeRecords()) {
        if($this->getJobTypeFilter() == self::JOB_TYPE_FILTER_SELECTED) {
          $queries[] = DB::prepare("(SELECT 'TimeRecord' AS 'type', '0' AS currency_id, user_id, user_name, user_email, SUM(value) AS 'value' $extra_fields FROM $time_records_table WHERE $conditions AND job_type_id IN (?) GROUP BY $group_by ORDER BY CONCAT(user_name, user_email))", array($this->getJobTypeIds()));
        } else {
          $queries[] = "(SELECT 'TimeRecord' AS 'type', '0' AS currency_id, user_id, user_name, user_email, SUM(value) AS 'value' $extra_fields FROM $time_records_table WHERE $conditions GROUP BY $group_by ORDER BY CONCAT(user_name, user_email))";
        } // if
      } // if
      
      if($this->queryExpenses()) {
        $currencies = Currencies::find();
        
        foreach($currencies as $currency) {
          $project_ids = $this->getProjectIdsByProjectFilterAndCurrency($currency);
          
          if(empty($project_ids)) {
            continue;
          } // if
          
          $task_ids = DB::executeFirstColumn('SELECT id FROM ' . TABLE_PREFIX . 'project_objects WHERE type = ? AND project_id IN (?) AND state >= ?', 'Task', $project_ids, STATE_ARCHIVED);
          
          if($task_ids) {
            $currency_context_conditions = DB::prepare("((parent_type = ? AND parent_id IN (?)) OR (parent_type = ? AND parent_id IN (?)))", array('Project', $project_ids, 'Task', $task_ids));
          } else {
            $currency_context_conditions = DB::prepare("(parent_type = ? AND parent_id IN (?))", array('Project', $project_ids));
          } // if
          
          $currency_id = DB::escape($currency->getId());
          
          if($this->getExpenseCategoryFilter() == self::EXPENSE_CATEGORY_FILTER_SELECTED) {
            $queries[] = DB::prepare("(SELECT 'Expense' AS 'type', $currency_id AS currency_id, user_id, user_name, user_email, SUM(value) AS 'value' $extra_fields FROM $expenses_table WHERE $conditions AND $currency_context_conditions AND category_id IN (?) GROUP BY $group_by ORDER BY CONCAT(user_name, user_email))", array($this->getExpenseCategoryIds()));
          } else {
            $queries[] = "(SELECT 'Expense' AS 'type', $currency_id AS currency_id, user_id, user_name, user_email, SUM(value) AS 'value' $extra_fields FROM $expenses_table WHERE $conditions AND $currency_context_conditions GROUP BY $group_by ORDER BY CONCAT(user_name, user_email))";
          } // if
        } // foreach
      } // if
      
      return implode(' UNION ', $queries);
    } // getQueriesForSummarizedReport
    
    /**
     * Return project ID-s based on project filter
     * 
     * @return integer
     */
    private function getProjectIdsByProjectFilter() {
      $conditions = array(DB::prepare('(state >= ?)', array(STATE_ARCHIVED)));
      
      switch($this->getProjectFilter()) {
        case self::PROJECT_FILTER_ACTIVE:
          $conditions[] = '(completed_on IS NULL)';
          break;
          
        case self::PROJECT_FILTER_COMPLETED:
          $conditions[] = '(completed_on IS NOT NULL)';
          break;
        
        case self::PROJECT_FILTER_CATEGORY:
          $category_id = (integer) $this->getProjectCategoryId();
          
          if($category_id) {
            $conditions[] = DB::prepare('(category_id = ?)', array($category_id));
          } else {
            return null;
          } // if
          
          break;
        case self::PROJECT_FILTER_CLIENT:
          $client_id = (integer) $this->getProjectClientId();
          
          if($client_id) {
            $conditions[] = DB::prepare('(company_id = ?)', array($client_id));
          } else {
            return null;
          } // if
          
          break;
        case self::PROJECT_FILTER_SELECTED:
          $project_ids = $this->getProjectIds();
          
          if(is_array($project_ids) && count($project_ids)) {
            $conditions[] = DB::prepare('(id IN (?))', array($project_ids));
          } else {
            return null;
          } // if
          
          break;
      } // swtich
      
      return DB::executeFirstColumn('SELECT id FROM ' . TABLE_PREFIX . 'projects WHERE ' . implode(' AND ', $conditions));
    } // getProjectIdsByProjectFilter
    
    /**
     * Return project ID-s by given currency and project filter settings
     * 
     * @param Currency $currency
     * @return array
     */
    private function getProjectIdsByProjectFilterAndCurrency(Currency $currency) {
      if($currency->getIsDefault()) {
        $conditions = array(DB::prepare('((currency_id = ? OR currency_id = ? OR currency_id IS NULL) AND state >= ?)', array($currency->getId(), 0, STATE_ARCHIVED)));
      } else {
        $conditions = array(DB::prepare('(currency_id = ? AND state >= ?)', array($currency->getId(), STATE_ARCHIVED)));
      } // if
      
      switch($this->getProjectFilter()) {
        case self::PROJECT_FILTER_ACTIVE:
          $conditions[] = '(completed_on IS NULL)';
          break;
          
        case self::PROJECT_FILTER_COMPLETED:
          $conditions[] = '(completed_on IS NOT NULL)';
          break;
        
        case self::PROJECT_FILTER_CATEGORY:
          $category_id = (integer) $this->getProjectCategoryId();
          
          if($category_id) {
            $conditions[] = DB::prepare('(category_id = ?)', array($category_id));
          } else {
            return null;
          } // if
          
          break;
        case self::PROJECT_FILTER_CLIENT:
          $client_id = (integer) $this->getProjectClientId();
          
          if($client_id) {
            $conditions[] = DB::prepare('(company_id = ?)', array($client_id));
          } else {
            return null;
          } // if
          
          break;
        case self::PROJECT_FILTER_SELECTED:
          $project_ids = $this->getProjectIds();
          
          if(is_array($project_ids) && count($project_ids)) {
            $conditions[] = DB::prepare('(id IN (?))', array($project_ids));
          } else {
            return null;
          } // if
          
          break;
      } // swtich
      
      return DB::executeFirstColumn('SELECT id FROM ' . TABLE_PREFIX . 'projects WHERE ' . implode(' AND ', $conditions));
    } // getProjectIdsByProjectFilterAndCurrency
    
    /**
     * Go through rows and load project_id and project_name information based on 
     * parent type and parent_id information
     *
     * @param array $rows
     */
    private function populateProjectInfo(&$rows) {
      $projects_table = TABLE_PREFIX . 'projects';
      $project_objects_table = TABLE_PREFIX . 'project_objects';
      
      $project_objects = array();
      $project_ids = array();
      
      foreach($rows as &$row) {
        if(ProjectObjects::isProjectObjectClass($row['parent_type'])) {
          $row['is_project_object'] = true;
          
          $project_objects[(integer) $row['parent_id']] = false;
        } else {
          $row['is_project'] = true;
          
          $project_ids[] = (integer) $row['parent_id'];
        } // if
      } // foreach

      unset($row); // break the reference
      
      // Load project ID-s for project objects
      if(count($project_objects)) {
        $project_object_rows = DB::execute("SELECT id, name, project_id FROM $project_objects_table WHERE id IN (?)", array_unique(array_keys($project_objects)));
        if(is_foreachable($project_object_rows)) {
          foreach($project_object_rows as $project_object_row) {
            $project_objects[$project_object_row['id']] = array(
              'name' => $project_object_row['name'], 
              'project_id' => $project_object_row['project_id'], 
            );
            
            if(!in_array($project_object_row['project_id'], $project_ids)) {
              $project_ids[] = $project_object_row['project_id'];
            } // if
          } // foreach
        } // if
      } // if
      
      // Get project details
      $projects = array();
      
      $default_currency_id = Currencies::getDefaultId();
      $default_company_id = Companies::findOwnerCompany()->getId();
      $default_company_name = Companies::findOwnerCompany()->getName();
      
      $project_rows = DB::execute("SELECT id, name, company_id AS client_id, currency_id, slug FROM $projects_table WHERE id IN (?)", $project_ids);
      if($project_rows) {
        $company_names = Companies::getIdNameMap();
        
        $project_rows->setCasting(array(
          'id' => DBResult::CAST_INT, 
          'client_id' => DBResult::CAST_INT, 
          'currency_id' => DBResult::CAST_INT, 
        ));
        
        foreach($project_rows as $project_row) {
          $projects[$project_row['id']] = array(
            'name' => $project_row['name'],
            'slug' => $project_row['slug'],
            'currency_id' => $project_row['currency_id'] ? $project_row['currency_id'] : $default_currency_id, 
          );
          
          if($project_row['client_id']) {
            $projects[$project_row['id']]['client_id'] = $project_row['client_id'];
            $projects[$project_row['id']]['client_name'] = isset($company_names[$project_row['client_id']]) ? $company_names[$project_row['client_id']] : lang('Unknown');
          } else {
            $projects[$project_row['id']]['client_id'] = $default_company_id;
            $projects[$project_row['id']]['client_name'] = $default_company_name;
          } // if
        } // foreach
      } // if

      $project_url = Router::assemble('project', array('project_slug' => '--PROJECT_SLUG--'));
      
      // Now, let's populate project ID, name and currency ID fields for records
      foreach($rows as &$row) {
        $row['project_id'] = 0;
        $row['project_name'] = '--';
        $row['project_slug'] = '0';
        $row['project_url'] = '#';
        $row['client_id'] = $default_company_id;
        $row['client_name'] = $default_company_name;
        $row['currency_id'] = $default_currency_id;
        
        if(isset($row['is_project_object']) && $row['is_project_object']) {
          $project_id = $project_objects[$row['parent_id']] && isset($projects[$project_objects[$row['parent_id']]['project_id']]) ? $project_objects[$row['parent_id']]['project_id'] : 0;
        } else {
          $project_id = $row['parent_id'];
        } // if
        
        if($project_id) {
          $row['project_id'] = $project_id;
          
          if(isset($projects[$project_id])) {
            $row['project_name'] =  $projects[$project_id]['name'];
            $row['project_slug'] =  $projects[$project_id]['slug'];
            $row['project_url'] = str_replace('--PROJECT_SLUG--', $projects[$project_id]['slug'], $project_url);
            $row['currency_id'] = $projects[$project_id]['currency_id'];
            
            $row['client_id'] =  $projects[$project_id]['client_id'];
            $row['client_name'] = $projects[$project_id]['client_name'];
          } // if 
        } // if
      } // foreach

      unset($row); // in case...
    } // populateProjectInfo
    
    /**
     * Add project client information to the rows
     * 
     * project_id field is required for rows for this function to work properly
     *
     * @param array $rows
     */
    private function populateProjectClientInfo(&$rows) {
      $project_ids = array();
            
      foreach($rows as &$row) {
        if($row['project_id'] && !in_array($row['project_id'], $project_ids)) {
          $project_ids[] = $row['project_id'];
        } // if
      } // foreach
      unset($row);
      
      $projects = array();
      if(count($project_ids)) {
        $companies_table = TABLE_PREFIX . 'companies';
        $projects_table = TABLE_PREFIX . 'projects';
        
        $project_rows = DB::execute("SELECT $projects_table.id AS 'id', $projects_table.company_id AS 'client_id', $companies_table.name AS 'client_name' FROM $projects_table, $companies_table WHERE $projects_table.company_id = $companies_table.id AND $projects_table.id IN (?)", $project_ids);
        if($project_rows) {
          foreach($project_rows as $project_row) {
            $projects[$project_row['id']] = array(
              'client_id' => $project_row['client_id'], 
              'client_name' => $project_row['client_name'], 
            );
          } // foreach
        } // if
      } // if
      
      $owner_company = Companies::findOwnerCompany();
      
      foreach($rows as &$row) {
        if(isset($projects[$row['project_id']])) {
          $row['client_id'] = $projects[$row['project_id']]['client_id'];
          $row['client_name'] = $projects[$row['project_id']]['client_name'];
        } else {
          $row['client_id'] = $owner_company->getId();
          $row['client_name'] = $owner_company->getName();
        } // if
      } // foreach
      unset($row); // just in case
    } // populateProjectClientInfo
    
    /**
     * Populate parent info
     *
     * @param array $rows
     */
    private function populateParentInfo(&$rows) {
      $task_ids = array();
      
      foreach($rows as &$row) {
        if($row['parent_type'] == 'Task') {
          $task_ids[] = $row['parent_id'];
        } // if
      } // foreach
      unset($row);
      
      $tasks_info = array();

      if(count($task_ids)) {
        $info_rows = DB::execute("SELECT id, name, integer_field_1 AS 'task_id' FROM " . TABLE_PREFIX . 'project_objects WHERE type = ? AND id IN (?)', 'Task', $task_ids);
        
        if($info_rows) {
          foreach($info_rows as $info_row) {
            $tasks_info[(integer) $info_row['id']] = array(
              'name' => $info_row['name'],
              'task_id' => $info_row['task_id'],
            );
          } // foreach
        } // if
      } // if

      $task_url = AngieApplication::isModuleLoaded('tasks') ? Router::assemble('project_task', array(
        'project_slug' => '--PROJECT_SLUG--',
        'task_id' => '--TASK_ID--',
      )) : '#';
      
      foreach($rows as &$row) {
        if($row['parent_type'] == 'Task') {
          $task_info = isset($tasks_info[$row['parent_id']]) ? $tasks_info[$row['parent_id']] : null;

          $name = $task_info ? $task_info['name'] : '';
          $url = $task_info ? str_replace(array('--PROJECT_SLUG--', '--TASK_ID--'), array($row['project_slug'], $task_info['task_id']), $task_url) : '#';
        } elseif($row['parent_type'] == 'Project') {
          $name = isset($row['project_name']) ? $row['project_name'] : '';
          $url = isset($row['project_url']) ? $row['project_url'] : '#';
        } else {
          $name = '';
          $url = '#';
        } // if
        
        $row['parent_name'] = $name;
        $row['parent_url'] = $url;
      } // foreach
      
      unset($row); // just in case
    } // populateParentInfo
    
    /**
     * Returns true if parent report queries time records table
     *
     * @return boolean
     */
    function queryTimeRecords() {
      return $this->getTypeFilter() == self::TYPE_FILTER_ANY || $this->getTypeFilter() == self::TYPE_FILTER_TIME;
    } // queryTimeRecords
    
    /**
     * Returns true if parent report queries expenses table
     *
     * @return boolean
     */
    function queryExpenses() {
      return $this->getTypeFilter() == self::TYPE_FILTER_ANY || $this->getTypeFilter() == self::TYPE_FILTER_EXPENSES;
    } // queryExpenses
    
    /**
     * Prepare result conditions based on report settings
     *
     * @param IUser $user
     * @return array
     */
    function prepareConditions(IUser $user) { 
      $conditions = array(
        DB::prepare('state >= ?', array(STATE_ARCHIVED))
      );
      
      $project_ids = $this->getProjectIdsByProjectFilter();
      if($project_ids) {
        $task_ids = DB::executeFirstColumn('SELECT id FROM ' . TABLE_PREFIX . 'project_objects WHERE type = ? AND project_id IN (?) AND state >= ?', 'Task', $project_ids, STATE_ARCHIVED);
        
        if($task_ids) {
          $conditions[] = DB::prepare('((parent_type = ? AND parent_id IN (?)) OR (parent_type = ? AND parent_id IN (?)))', array('Project', $project_ids, 'Task', $task_ids));
        } else {
          $conditions[] = DB::prepare('(parent_type = ? AND parent_id IN (?))', array('Project', $project_ids));
        } // if
      } else {
        return false; // No projects matching this report
      } // if
      
      switch($this->getUserFilter()) {
        
        // Filter for logged user
        case self::USER_FILTER_LOGGED_USER:
          $conditions[] = DB::prepare('user_id = ?', array($user->getId()));
          break;
          
        // Filter for selected company
        case self::USER_FILTER_COMPANY:
          $company_id = (integer) $this->getUserFilterCompanyId();
          
          if($company_id) {
            $user_ids = null;
            
            $company = Companies::findById($company_id);
            
            if($company instanceof Company) {
              $user_ids = $company->users()->getIds($user);
            } // if
            
            if($user_ids) {
              $conditions[] = DB::prepare('user_id IN (?)', array($user_ids));
            } else {
              return false; // No visible users in selected company
            } // if
          } else {
            return false; // Company not found
          } // if
          
          break;
          
        // Filter for selected users
        case self::USER_FILTER_SELECTED:
          $user_ids = $this->getUserFilterSelectedUsers();
          
          if(is_foreachable($user_ids)) {
            $conditions[] = DB::prepare('user_id IN (?)', array($user_ids));
          } else {
            throw new InvalidParamError('user_ids', $user_ids, 'No visible users selected');
          } // if
          
          break;
      } // switch
      
      $today = new DateValue(time() + get_user_gmt_offset($user)); // Calculate user timezone when determining today
      switch($this->getDateFilter()) {
          
        // List time records posted for today
        case self::DATE_FILTER_TODAY:
          $conditions[] = "(record_date = " . DB::escape($today->toMySQL()) . ')';
          break;
          
        // Time tracked for yesterday
        case self::DATE_FILTER_YESTERDAY:
          $yesterday = $today->advance(-86400, false);
          $conditions[] = "(record_date = " . DB::escape($yesterday->toMySQL()) . ')';
          break;
          
        // List next week records
        case self::DATE_FILTER_LAST_WEEK:
          $first_week_day = (integer) ConfigOptions::getValueFor('time_first_week_day', $user);
          
          $last_week = $today->advance(-604800, false);
          
          $week_start = $last_week->beginningOfWeek($first_week_day);
          $week_end = $last_week->endOfWeek($first_week_day);
          
          $week_start_str = DB::escape($week_start->toMySQL());
          $week_end_str = DB::escape($week_end->toMySQL());
          
          $conditions[] = "(record_date >= $week_start_str AND record_date <= $week_end_str)";
          break;
          
        // List this week records
        case self::DATE_FILTER_THIS_WEEK:
          $first_week_day = (integer) ConfigOptions::getValueFor('time_first_week_day', $user);
          
          $week_start = $today->beginningOfWeek($first_week_day);
          $week_end = $today->endOfWeek($first_week_day);
          
          $week_start_str = DB::escape($week_start->toMySQL());
          $week_end_str = DB::escape($week_end->toMySQL());
          
          $conditions[] = "(record_date >= $week_start_str AND record_date <= $week_end_str)";
          break;
          
        // List this month time records
        case self::DATE_FILTER_LAST_MONTH:
          $month = $today->getMonth() - 1;
          $year = $today->getYear();
          
          if($month == 0) {
            $month = 12;
            $year -= 1;
          } // if
          
          $month_start = DateTimeValue::beginningOfMonth($month, $year);
          $month_end = DateTimeValue::endOfMonth($month, $year);
          
          $month_start_str = DB::escape($month_start->toMySQL());
          $month_end_str = DB::escape($month_end->toMySQL());
          
          $conditions[] = "(record_date >= $month_start_str AND record_date <= $month_end_str)";
          break;
          
        // Last month
        case self::DATE_FILTER_THIS_MONTH:
          $month_start = DateTimeValue::beginningOfMonth($today->getMonth(), $today->getYear());
          $month_end = DateTimeValue::endOfMonth($today->getMonth(), $today->getYear());
          
          $month_start_str = DB::escape($month_start->toMySQL());
          $month_end_str = DB::escape($month_end->toMySQL());
          
          $conditions[] = "(record_date >= $month_start_str AND record_date <= $month_end_str)";
          break;
          
        // Specific date
        case self::DATE_FILTER_SELECTED_DATE:
          $date_from = $this->getDateFilterSelectedDate();
          if($date_from instanceof DateValue) {
            $date_from_str = DB::escape($date_from->toMySql());
            $conditions[] = "(record_date = $date_from_str)";
          } else {
            throw new InvalidParamError('date_filter_on', $date_from, 'Date not selected');
          } // if
          
          break;
          
        // Specific range
        case self::DATE_FILTER_SELECTED_RANGE:
          list($date_from, $date_to) = $this->getDateFilterSelectedRange();
          
          if($date_from instanceof DateValue && $date_to instanceof DateValue) {
            $date_from_str = DB::escape($date_from->toMySQL());
            $date_to_str = DB::escape($date_to->toMySQL());
            
            $conditions[] = "(record_date >= $date_from_str AND record_date <= $date_to_str)";
          } else {
            throw new InvalidParamError('date_filter_from_to', null, 'Date range not selected');
          } // if
          break;
      } // switch
      
      // Billable filter
      switch($this->getBillableStatusFilter()) {
        case self::BILLABLE_FILTER_BILLABLE:
          $conditions[] = DB::prepare("(billable_status = ?)", array(BILLABLE_STATUS_BILLABLE));
          break;
        case self::BILLABLE_FILTER_NOT_BILLABLE:
          $conditions[] = DB::prepare("(billable_status = ? OR billable_status IS NULL)", array(BILLABLE_STATUS_NOT_BILLABLE));
          break;
        case self::BILLABLE_FILTER_BILLABLE_PAID:
          $conditions[] = DB::prepare("(billable_status >= ?)", array(BILLABLE_STATUS_PAID));
          break;
        case self::BILLABLE_FILTER_PENDING_PAYMENT:
          $conditions[] = DB::prepare("(billable_status = ?)", array(BILLABLE_STATUS_PENDING_PAYMENT));
          break;
        case self::BILLABLE_FILTER_BILLABLE_NOT_PAID:
          $conditions[] = DB::prepare("(billable_status IN (?))", array(
            array(BILLABLE_STATUS_BILLABLE, BILLABLE_STATUS_PENDING_PAYMENT), 
          ));
          break;
      } // switch
      
      // Make sure that we can filter by job types
      if($this->getJobTypeFilter() == self::JOB_TYPE_FILTER_SELECTED) {
        $job_type_ids = $this->getJobTypeIds();
        
        if(empty($job_type_ids)) {
          return false;
        } // if
      } // if
      
      // Make sure that we can filter by expense categories
      if($this->getExpenseCategoryFilter() == self::EXPENSE_CATEGORY_FILTER_SELECTED) {
        $expense_category_ids = $this->getExpenseCategoryIds();
        
        if(empty($expense_category_ids)) {
          return false;
        } // if
      } // if
      
      return implode(' AND ', $conditions);
    } // prepareConditions
    
    /**
     * Return array or property => value pairs that describes this object
     *
     * $user is an instance of user who requested description - it's used to get
     * only the data this user can see
     *
     * @param IUser $user
     * @param boolean $detailed
     * @param boolean $for_interface
     * @return array
     */
    function describe(IUser $user, $detailed = false, $for_interface = false) {
      $result = parent::describe($user, $detailed, $for_interface);
      
      // User filter
      $result['user_filter'] = $this->getUserFilter();
      switch($this->getUserFilter()) {
        case TrackingReport::USER_FILTER_COMPANY:
          $result['company_id'] = (integer) $this->getUserFilterCompanyId();
          break;
          
        case TrackingReport::USER_FILTER_SELECTED:
          $result['user_ids'] = $this->getUserFilterSelectedUsers();
          break;
      } // switch
      
      // Date filter
      $result['date_filter'] = $this->getDateFilter();
      switch($this->getDateFilter()) {
        case Trackingreport::DATE_FILTER_SELECTED_DATE:
          $result['date_on'] = $this->getDateFilterSelectedDate() instanceof DateValue ? $this->getDateFilterSelectedDate() : null;
          break;
          
        case Trackingreport::DATE_FILTER_SELECTED_RANGE:
          list($date_from, $date_to) = $this->getDateFilterSelectedRange();
          
          $result['date_from'] = $date_from instanceof DateValue ? $date_from : null;
          $result['date_to'] = $date_to instanceof DateValue ? $date_to : null;
          
          break;
      } // switch
      
      // Project filter
      $result['project_filter'] = $this->getProjectFilter();
      switch($result['project_filter']) {
        case TrackingReport::PROJECT_FILTER_CATEGORY:
          $result['project_category_id'] = $this->getProjectCategoryId();
          break;
        case TrackingReport::PROJECT_FILTER_CLIENT:
          $result['project_client_id'] = $this->getProjectClientId();
          break;
        case TrackingReport::PROJECT_FILTER_SELECTED:
          $result['project_ids'] = $this->getProjectIds();
          break;
      } // switch
      
      $result['billable_status_filter'] = $this->getBillableStatusFilter();
      $result['type_filter'] = $this->getTypeFilter();
      
      $result['job_type_filter'] = $this->getJobTypeFilter();
      if($result['job_type_filter'] == self::JOB_TYPE_FILTER_SELECTED) {
        $result['job_type_ids'] = $this->getJobTypeIds();
      } // if
      
      $result['expense_category_filter'] = $this->getJobTypeFilter();
      if($result['expense_category_filter'] == self::EXPENSE_CATEGORY_FILTER_SELECTED) {
        $result['expense_category_ids'] = $this->getExpenseCategoryIds();
      } // if
      
      $result['sum_by_user'] = $this->getSumByUser();
      $result['group_by'] = $this->getGroupBy();
      
      return $result;
    } // describe

    /**
     * Return array or property => value pairs that describes this object
     *
     * @param IUser $user
     * @param boolean $detailed
     * @return array
     */
    function describeForApi(IUser $user, $detailed = false) {
      throw new NotImplementedError(__METHOD__);
    } // describeForApi
    
    // ---------------------------------------------------
    //  Getters, setters and attributes
    // ---------------------------------------------------
    
    /**
     * Set attributes
     *
     * @param array $attributes
     */
    function setAttributes($attributes) {
      if(isset($attributes['user_filter'])) {
        if($attributes['user_filter'] == self::USER_FILTER_COMPANY) {
          $this->filterByCompany(array_var($attributes, 'company_id'));
        } elseif($attributes['user_filter'] == self::USER_FILTER_SELECTED) {
          $this->filterByUsers(array_var($attributes, 'user_ids'));
        } else {
          $this->setUserFilter($attributes['user_filter']);
        } // if
      } // if
      
      if(isset($attributes['date_filter'])) {
        switch($attributes['date_filter']) {
          case self::DATE_FILTER_SELECTED_DATE:
            $this->filterByDate($attributes['date_on']);
            break;
          case self::DATE_FILTER_SELECTED_RANGE:
            $this->filterByRange($attributes['date_from'], $attributes['date_to']);
            break;
          default:
            $this->setDateFilter($attributes['date_filter']);
        } // switch
      } // if
      
      if(isset($attributes['project_filter'])) {
        if($attributes['project_filter'] == self::PROJECT_FILTER_CATEGORY) {
          $this->filterByProjectCategory(array_var($attributes, 'project_category_id'));
        } elseif($attributes['project_filter'] == self::PROJECT_FILTER_CLIENT) {
          $this->filterByProjectClient(array_var($attributes, 'project_client_id'));
        } elseif($attributes['project_filter'] == self::PROJECT_FILTER_SELECTED) {
          $this->filterByProjects(array_var($attributes, 'project_ids'));
        } else {
          $this->setProjectFilter($attributes['project_filter']);
        } // if
      } // if
      
      if(isset($attributes['type_filter'])) {
        $this->setTypeFilter($attributes['type_filter']);
      } // if
      
      if(isset($attributes['job_type_filter'])) {
        if($attributes['job_type_filter'] == self::JOB_TYPE_FILTER_SELECTED) {
          $this->filterByJobTypes(array_var($attributes, 'job_type_ids'));
        } else {
          $this->setJobTypeFilter($attributes['job_type_filter']);
        } // if
      } // if
      
      if(isset($attributes['expense_category_filter'])) {
        if($attributes['expense_category_filter'] == self::EXPENSE_CATEGORY_FILTER_SELECTED) {
          $this->filterByExpenseCategory(array_var($attributes, 'expense_category_ids'));
        } else {
          $this->setExpenseCategoryFilter($attributes['expense_category_filter']);
        } // if
      } // if
      
      if(isset($attributes['billable_status_filter'])) {
        $this->setBillableStatusFilter($attributes['billable_status_filter']);
      } // if
      
      if(isset($attributes['sum_by_user'])) {
        $this->setSumByUser((boolean) $attributes['sum_by_user']);
      } // if
      
      if(isset($attributes['group_by'])) {
        $this->setGroupBy($attributes['group_by']);
      } // if
      
      parent::setAttributes($attributes);
    } // setAttributes
  
    /**
     * Return user filter value
     *
     * @return string
     */
    function getUserFilter() {
      return $this->getAdditionalProperty('user_filter', self::USER_FILTER_ANYBODY);
    } // getUserFilter
    
    /**
     * Set user filter value
     *
     * @param string $value
     * @return string
     */
    function setUserFilter($value) {
      return $this->setAdditionalProperty('user_filter', $value);
    } // setUserFilter
    
    /**
     * Set filter by company values
     *
     * @param integer $company_id
     */
    function filterByCompany($company_id) {
      $this->setUserFilter(self::USER_FILTER_COMPANY);
      $this->setAdditionalProperty('company_id', $company_id);
    } // filterByCompany
    
    /**
     * Return company ID set for user filter
     *
     * @return integer
     */
    function getUserFilterCompanyId() {
      return $this->getAdditionalProperty('company_id');
    } // getUserFilterCompanyId
    
    /**
     * Set user filter to filter only tracked object for selected users
     *
     * @param array $user_ids
     */
    function filterByUsers($user_ids) {
      $this->setUserFilter(self::USER_FILTER_SELECTED);
      
      if(is_array($user_ids)) {
        foreach($user_ids as $k => $v) {
          $user_ids[$k] = (integer) $v;
        } // foreach
      } else {
        $user_ids = null;
      } // if
      
      $this->setAdditionalProperty('selected_users', $user_ids);
    } // filterByUsers
    
    /**
     * Return array of selected users
     *
     * @return array
     */
    function getUserFilterSelectedUsers() {
      return $this->getAdditionalProperty('selected_users');
    } // getUserFilterSelectedUsers
    
    /**
     * Return date filter value
     *
     * @return string
     */
    function getDateFilter() {
      return $this->getAdditionalProperty('date_filter', self::DATE_FILTER_ANY);
    } // getDateFilter
    
    /**
     * Set date filter to a given $value
     *
     * @param string $value
     * @return string
     */
    function setDateFilter($value) {
      return $this->setAdditionalProperty('date_filter', $value);
    } // setDateFilter
    
    /**
     * Filter objects tracked for a given date
     *
     * @param string $date
     */
    function filterByDate($date) {
      $this->setDateFilter(self::DATE_FILTER_SELECTED_DATE);
      $this->setAdditionalProperty('date_filter_on', (string) $date);
    } // filterByDate
    
    /**
     * Return selected date for date value
     *
     * @return DateValue
     */
    function getDateFilterSelectedDate() {
      $on = $this->getAdditionalProperty('date_filter_on');
      
      return $on ? new DateValue($on) : null;
    } // getDateFilterSelectedDate
    
    /**
     * Filter records by date range
     *
     * @param string $from
     * @param string $to
     */
    function filterByRange($from, $to) {
      $this->setDateFilter(self::DATE_FILTER_SELECTED_RANGE);
      $this->setAdditionalProperty('date_filter_from', (string) $from);
      $this->setAdditionalProperty('date_filter_to', (string) $to);
    } // filterByRange
    
    /**
     * Return selected range for date filter
     *
     * @return array
     */
    function getDateFilterSelectedRange() {
      $from = $this->getAdditionalProperty('date_filter_from');
      $to = $this->getAdditionalProperty('date_filter_to');
      
      return $from && $to ? array(new DateValue($from), new DateValue($to)) : array(null, null);
    } // getDateFilterSelectedRange
    
    /**
     * Return project filter value
     *
     * @return string
     */
    function getProjectFilter() {
      return $this->getAdditionalProperty('project_filter', self::PROJECT_FILTER_ANY);
    } // getProjectFilter
    
    /**
     * Set project filter value
     *
     * @param string $value
     * @return string
     */
    function setProjectFilter($value) {
      return $this->setAdditionalProperty('project_filter', $value);
    } // setProjectFilter
    
    /**
     * Set filter to filter records by project category
     *
     * @param integer $project_category_id
     * @return integer
     */
    function filterByProjectCategory($project_category_id) {
      $this->setProjectFilter(self::PROJECT_FILTER_CATEGORY);
      $this->setAdditionalProperty('project_category_id', (integer) $project_category_id);
    } // filterByProjectCategory
    
    /**
     * Return project category ID
     *
     * @return integer
     */
    function getProjectCategoryId() {
      return (integer) $this->getAdditionalProperty('project_category_id');
    } // getProjectCategoryId
    
    /**
     * Set filter to filter records by project client
     *
     * @param integer $project_client_id
     * @return integer
     */
    function filterByProjectClient($project_client_id) {
      $this->setProjectFilter(self::PROJECT_FILTER_CLIENT);
      $this->setAdditionalProperty('project_client_id', (integer) $project_client_id);
    } // filterByProjectClient
    
    /**
     * Return project client ID
     *
     * @return integer
     */
    function getProjectClientId() {
      return (integer) $this->getAdditionalProperty('project_client_id');
    } // getProjectClientId
    
    /**
     * Set this report to filter records by project ID-s
     *
     * @param array $project_ids
     * @return array
     */
    function filterByProjects($project_ids) {
      $this->setProjectFilter(self::PROJECT_FILTER_SELECTED);
      
      if(is_array($project_ids)) {
        foreach($project_ids as $k => $v) {
          $project_ids[$k] = (integer) $v;
        } // foreach
      } else {
        $project_ids = null;
      } // if
      
      $this->setAdditionalProperty('project_ids', $project_ids);
    } // filterByProjects
    
    /**
     * Return project ID-s
     *
     * @return array
     */
    function getProjectIds() {
      return $this->getAdditionalProperty('project_ids');
    } // getProjectIds
    
    /**
     * Return billable filter value
     *
     * @return string
     */
    function getBillableStatusFilter() {
      return $this->getAdditionalProperty('billable_status_filter', self::BILLABLE_FILTER_ALL);
    } // getBillableStatusFilter
    
    /**
     * Set billable filter to a given value
     *
     * @param string $value
     * @return string
     */
    function setBillableStatusFilter($value) {
      return $this->setAdditionalProperty('billable_status_filter', $value);
    } // setBillableFilter
    
    /**
     * Return type filter
     *
     * @return string
     */
    function getTypeFilter() {
      return $this->getAdditionalProperty('type_filter', self::TYPE_FILTER_ANY);
    } // getTypeFilter
    
    /**
     * Set type filter
     *
     * @param string $value
     * @return string
     */
    function setTypeFilter($value) {
      return $this->setAdditionalProperty('type_filter', $value);
    } // setTypeFilter
    
    /**
     * Return job type filter value
     * 
     * @return string
     */
    function getJobTypeFilter() {
      return $this->getAdditionalProperty('job_type_filter', self::JOB_TYPE_FILTER_ANY);
    } // getJobTypeFilter
    
    /**
     * Set job type filter value
     * 
     * @param string $value
     * @return string
     */
    function setJobTypeFilter($value) {
      return $this->setAdditionalProperty('job_type_filter', $value);
    } // setJobTypeFilter
    
    /**
     * Set job type filter to list of given values
     * 
     * @param array $job_type_ids
     */
    function filterByJobTypes($job_type_ids) {
      if(is_array($job_type_ids)) {
        $this->setJobTypeFilter(self::JOB_TYPE_FILTER_SELECTED);
        
        foreach($job_type_ids as $k => $v) {
          $job_type_ids[$k] = (integer) $v;
        } // foreach
        
        $this->setAdditionalProperty('job_type_ids', $job_type_ids);
      } else {
        throw new InvalidParamError('job_type_ids', $job_type_ids, 'List of job type IDs should be an array');
      } // if
    } // filterByJobTypes
    
    /**
     * Return job type ID-s
     * 
     * @return array
     */
    function getJobTypeIds() {
      return $this->getAdditionalProperty('job_type_ids');
    } // getJobTypeIds
    
    /**
     * Return expense category filter value
     * 
     * @return string
     */
    function getExpenseCategoryFilter() {
      return $this->getAdditionalProperty('expense_category_filter', self::EXPENSE_CATEGORY_FILTER_ANY);
    } // getExpenseCategoryFilter
    
    /**
     * Set expense category filter value
     * 
     * @param string $value
     * @return string
     */
    function setExpenseCategoryFilter($value) {
      return $this->setAdditionalProperty('expense_category_filter', $value);
    } // setExpenseCategoryFilter
    
    /**
     * Set expense category filter to list of selected ID-s
     * 
     * @param array $expense_category_ids
     */
    function filterByExpenseCategory($expense_category_ids) {
      if(is_array($expense_category_ids)) {
        $this->setExpenseCategoryFilter(self::EXPENSE_CATEGORY_FILTER_SELECTED);
        
        foreach($expense_category_ids as $k => $v) {
          $expense_category_ids[$k] = (integer) $v;
        } // foreach
        
        $this->setAdditionalProperty('expense_category_ids', $expense_category_ids);
      } else {
        throw new InvalidParamError('expense_category_ids', $expense_category_ids, 'List of expense category IDs should be an array');
      } // if
    } // filterByExpenseCategory
    
    /**
     * Return selected expense category ID-s
     * 
     * @return array
     */
    function getExpenseCategoryIds() {
      return $this->getAdditionalProperty('expense_category_ids');
    } // getExpenseCategoryIds
    
    /**
     * Return group by setting
     *
     * @return string
     */
    function getGroupBy() {
      return $this->getAdditionalProperty('group_by', self::DONT_GROUP);
    } // getGroupBy
    
    /**
     * Set group by value
     *
     * @param string $value
     * @return string
     */
    function setGroupBy($value) {
      if($value == self::GROUP_BY_DATE || $value == self::GROUP_BY_PROJECT || $value == self::GROUP_BY_PROJECT_CLIENT) {
        return $this->setAdditionalProperty('group_by', $value);
      } else {
        return $this->setAdditionalProperty('group_by', self::DONT_GROUP);
      } // if
    } // setGroupBy
    
    /**
     * Return sum_by_user
     *
     * @return boolean
     */
    function getSumByUser() {
    	return $this->getAdditionalProperty('sum_by_user', false);
    } // getSumByUser
    
    /**
     * Set sum_by_user
     *
     * @param boolean $value
     */
    function setSumByUser($value) {
      return $this->setAdditionalProperty('sum_by_user', (boolean) $value);
    } // setSumByUser
    
    // ---------------------------------------------------
    //  Permissions
    // ---------------------------------------------------
    
    /**
     * Returns true if $user can edit this tracking report
     *
     * @param User $user
     * @return boolean
     */
    function canEdit(User $user) {
      return $user->isProjectManager();
    } // canEdit
    
    /**
     * Returns true if $user can delete this tracking report
     *
     * @param User $user
     * @return boolean
     */
    function canDelete(User $user) {
      return $user->isProjectManager();
    } // canDelete
    
    // ---------------------------------------------------
    //  Interface implementations
    // ---------------------------------------------------
    
    /**
     * Return routing context name
     *
     * @return string
     */
    function getRoutingContext() {
      return 'tracking_report';
    } // getRoutingContext
    
    /**
     * Return routing context parameters
     *
     * @return mixed
     */
    function getRoutingContextParams() {
      return array('tracking_report_id' => $this->getId());
    } // getRoutingContextParams
    
    /**
     * Invoice implementation for this object
     * 
     * @var IInvoiceImplementation
     */
    private $invoice;
    
    /**
     * Return invoice implementation
     * 
     * @return IInvoiceBasedOnImplementation
     */
    function invoice() {
      if(empty($this->invoice)) {
        if(AngieApplication::isModuleLoaded('invoicing')) {
          $this->invoice = new IInvoiceBasedOnTrackingReportImplementation($this);
        } else {
          $this->invoice = new IInvoiceBasedOnImplementationStub($this);
        } // if
      } //if
      return $this->invoice;
    } //invoice
    
    // ---------------------------------------------------
    //  URL-s
    // ---------------------------------------------------
    
    /**
     * Return run report URL
     *
     * @return string
     */
    function getViewUrl() {
      if($this->isLoaded()) {
        $params = array('tracking_report_id' => $this->getId());
      } else {
        $params = $this->getReportParams();
      } // if
      
      return Router::assemble('tracking_reports', $params);
    } // getViewUrl
    
    /**
     * Return report paramters, used for GET in URL-s
     *
     * @return array
     */
    protected function getReportParams() {
      $result = array(
        'report[user_filter]' => $this->getUserFilter(), 
        'report[date_filter]' => $this->getDateFilter(), 
        'report[project_filter]' => $this->getProjectFilter(), 
        'report[billable_status_filter]' => $this->getBillableStatusFilter(), 
        'report[type_filter]' => $this->getTypeFilter(), 
        'report[sum_by_user]' => $this->getSumByUser(), 
        'report[group_by]' => $this->getGroupBy(), 
      );
      
      // User filter
      switch($this->getUserFilter()) {
        case TrackingReport::USER_FILTER_COMPANY:
          $result['report[company_id]'] = (integer) $this->getUserFilterCompanyId();
          break;
          
        case TrackingReport::USER_FILTER_SELECTED:
          $result['report[user_ids]'] = $this->getUserFilterSelectedUsers();
          break;
      } // switch
      
      // Date filter
      switch($this->getDateFilter()) {
        case Trackingreport::DATE_FILTER_SELECTED_DATE:
          $result['report[date_on]'] = $this->getDateFilterSelectedDate() instanceof DateValue ? $this->getDateFilterSelectedDate()->toMySql() : null;
          break;
          
        case Trackingreport::DATE_FILTER_SELECTED_RANGE:
          list($date_from, $date_to) = $this->getDateFilterSelectedRange();
          
          $result['report[date_from]'] = $date_from instanceof DateValue ? $date_from->toMySQL() : null;
          $result['report[date_to]'] = $date_to instanceof DateValue ? $date_to->toMySQL() : null;
          
          break;
      } // switch
      
      // Project filter
      switch($this->getProjectFilter()) {
        case TrackingReport::PROJECT_FILTER_CATEGORY:
          $result['report[project_category_id]'] = $this->getProjectCategoryId();
          break;
        case TrackingReport::PROJECT_FILTER_CLIENT:
          $result['report[project_client_id]'] = $this->getProjectClientId();
          break;
        case TrackingReport::PROJECT_FILTER_SELECTED:
          $result['report[project_ids]'] = $this->getProjectIds();
          break;
      } // switch
      
      return $result;
    } // getReportParams
    
  }