<?php
/**
 * TestLink Open Source Project - http://testlink.sourceforge.net/
 * This script is distributed under the GNU General Public License 2 or later.
 *
 * @filesource  tlTestPlanMetrics.class.php
 * @package     TestLink
 * @author      franciscom
 * @copyright   2004-2020, TestLink community 
 * @link        http://testlink.sourceforge.net/
 * @uses        config.inc.php 
 * @uses        common.php 
 *
 **/


/**
 * This class is encapsulates most functionality necessary to query the database
 * for results to publish in reports.  
 * It returns data structures to the gui layer in a manner that are easy to display 
 * in smarty templates.
 * 
 * @package TestLink
 */
class tlTestPlanMetrics extends testplan
{
  /** @var resource references passed in by constructor */
  var  $db = null;

  /** @var object class references passed in by constructor */
  private $tplanMgr = null;
  private $testPlanID = -1;
  private  $tprojectID = -1;
  private  $testCasePrefix='';

  private $priorityLevelsCfg='';
  private $map_tc_status;
  private $tc_status_for_statistics;
  
  private $statusCode;
  

  /** 
   * class constructor 
   * @param resource &$db reference to database handler
   **/    
  function __construct(&$db)
  {
    $this->resultsCfg = config_get('results');
    $this->testCaseCfg = config_get('testcase_cfg');

    $this->db = $db;
    parent::__construct($db);

    $this->map_tc_status = $this->resultsCfg['status_code'];
    
    // This will be used to create dynamically counters if user add new status
    foreach( $this->resultsCfg['status_label_for_exec_ui'] as $tc_status_verbose => $label)
    {
        $this->tc_status_for_statistics[$tc_status_verbose] = $this->map_tc_status[$tc_status_verbose];
    }
    if( !isset($this->resultsCfg['status_label_for_exec_ui']['not_run']) )
    {
        $this->tc_status_for_statistics['not_run'] = $this->map_tc_status['not_run'];  
    }
    // $this->notRunStatusCode = $this->tc_status_for_statistics['not_run'];
      
    $this->statusCode = array_flip(array_keys($this->resultsCfg['status_label_for_exec_ui']));
    foreach($this->statusCode as $key => $dummy)
    {
      $this->statusCode[$key] = $this->resultsCfg['status_code'][$key];  
    }
      
      // $this->execTaskCode = intval($this->assignment_types['testcase_execution']['id']);

  } // end results constructor



  public function getStatusConfig() 
  {
    return $this->tc_status_for_statistics;
  }


  /**
   * Function returns prioritized test result counter
   * 
   * @param timestamp $milestoneTargetDate - (optional) milestone deadline
   * @param timestamp $milestoneStartDate - (optional) milestone start date
   * @return array with three priority counters
   */
  public function getPrioritizedResults($tplanID,$milestoneTargetDate = null, $milestoneStartDate = null)
  {
    $output = array (HIGH=>0,MEDIUM=>0,LOW=>0);

    
    for($urgency=1; $urgency <= 3; $urgency++)
    {
      for($importance=1; $importance <= 3; $importance++)
      {  
        $sql = "SELECT COUNT(DISTINCT(TPTCV.id )) " .
          " FROM {$this->tables['testplan_tcversions']} TPTCV " .
          " JOIN {$this->tables['executions']} E ON " .
          " TPTCV.tcversion_id = E.tcversion_id " .
          " JOIN {$this->tables['tcversions']} TCV ON " .
          " TPTCV.tcversion_id = TCV.id " .
          " WHERE TPTCV.testplan_id = {$tplanID} " .
          " AND TPTCV.platform_id = E.platform_id " .
          " AND E.testplan_id = {$tplanID} " .
          " AND NOT E.status = '{$this->map_tc_status['not_run']}' " . 
          " AND TCV.importance={$importance} AND TPTCV.urgency={$urgency}";
        
        // Milestones did not handle start and target date properly
        $end_of_the_day = " 23:59:59";
        $beginning_of_the_day = " 00:00:00";
        
        if( !is_null($milestoneTargetDate) )
        {
          $sql .= " AND execution_ts < '" . $milestoneTargetDate . $end_of_the_day ."'";
        }
        
        if( !is_null($milestoneStartDate) )
        {
          $sql .= " AND execution_ts > '" . $milestoneStartDate . $beginning_of_the_day ."'";
        }
        
        $tmpResult = $this->db->fetchOneValue($sql);

        // parse results into three levels of priority
        $priority = priority_to_level($urgency*$importance);
        $output[$priority] = $output[$priority] + $tmpResult;
      }
    }
    
    return $output;
  }

  /**
   * Function returns prioritized test case counter (in Test Plan)
   * 
   * @return array with three priority counters
   */
  public function getPrioritizedTestCaseCounters($tplanID)
  {
    $output = array (HIGH=>0,MEDIUM=>0,LOW=>0);
    
    /** @TODO - REFACTOR IS OUT OF STANDARD MAGIC NUMBERS */
    for($urgency=1; $urgency <= 3; $urgency++)
    {
      for($importance=1; $importance <= 3; $importance++)
      {  
        // get total count of related TCs
        $sql = "SELECT COUNT( TPTCV.id ) FROM {$this->tables['testplan_tcversions']} TPTCV " .
            " JOIN {$this->tables['tcversions']} TCV ON TPTCV.tcversion_id = TCV.id " .
            " WHERE TPTCV.testplan_id = " . $tplanID .
              " AND TCV.importance={$importance} AND TPTCV.urgency={$urgency}";

        $tmpResult = $this->db->fetchOneValue($sql);
        
        // clean up priority usage
        $priority = priority_to_level($urgency*$importance);
        $output[$priority] = $output[$priority] + $tmpResult;
      }
    }
          
    return $output;
  }


  /**
   * 
   */
  function getMilestonesMetrics($tplanID, $milestoneSet=null)
  {        
    $results = array();

    // get amount of test cases for each execution result + total amount of test cases
    $planMetrics = $this->getExecCountersByExecStatus($tplanID);

    $milestones =  is_null($milestoneSet) ? $this->get_milestones($tplanID) : $milestoneSet;

    // get amount of test cases for each priority for test plan      
    $priorityCounters = $this->getPrioritizedTestCaseCounters($tplanID);
    $pc = array(LOW => 'result_low_percentage', MEDIUM => 'result_medium_percentage',
                HIGH => 'result_high_percentage' );
        
    $checks = array(LOW => 'low_percentage', MEDIUM => 'medium_percentage',
                    HIGH => 'high_percentage' );

    $on_off = array(LOW => 'low_incomplete', MEDIUM => 'medium_incomplete',
                    HIGH => 'high_incomplete' );
        
    // Important:
    // key already defined on item: high_percentage,medium_percentage,low_percentage
    foreach($milestones as $item)
    {
      $item['tcs_priority'] = $priorityCounters;
      $item['tc_total'] = $planMetrics['total'];

      // get amount of executed test cases for each priority before target_date
      $item['results'] = $this->getPrioritizedResults($tplanID, $item['target_date'], $item['start_date']);
      $item['tc_completed'] = 0;
            
      // calculate percentage of executed test cases for each priority
      foreach( $pc as $key => $item_key)
      {
        $target_key = $checks[$key];
        if( $item[$target_key] == 0 )
        {
          $item[$item_key] = 100;
        } 
        else 
        {
          $item[$item_key] = ($priorityCounters[$key] > 0) ? 
                             $this->get_percentage($priorityCounters[$key], $item['results'][$key]) : 0;
        }  
        $item['tc_completed'] += $item['results'][$key];
      }

      // amount of all executed tc with any priority before target_date / all test cases
      $item['percentage_completed'] = $this->get_percentage($item['tc_total'], $item['tc_completed']);

      foreach( $checks as $key => $item_key) {
        // add 1 decimal places to expected percentages
        $item[$checks[$key]] = number_format($item[$checks[$key]], 1);
              
        // check if target for each priority is reached
        // show target as reached if expected percentage is greater than executed percentage
        $item[$on_off[$key]] = ($item[$checks[$key]] > $item[$pc[$key]]) ? ON : OFF;
      }
      $results[$item['id']] = $item;
    }
    return $results;
  }
  
  
  /**
   * calculate percentage and format
   * 
   * @param int $total Total count
   * @param int $parameter a parameter count
   * @return string formatted percentage
   */
  function get_percentage($total, $parameter)
  {
    $percentCompleted = ($total > 0) ? (($parameter / $total) * 100) : 100;
    return number_format($percentCompleted,1);
  }



  /**
   * @used-by getOverallBuildStatusForRender()
   *          XML-RPC getExecCountersByBuild()
   *
   *
   * No matter we are trying to calculate metrics for BUILDS,
   * we need to consider execution status at Build and Platform level.
   *
   * Why?
   * Let's review help we provide on GUI:
   *
   * The use of platforms has impact on metrics, because
   * a test case that must be executed for N platforms is considered 
   * as N test cases on metrics.
   *
   * Example: Platform X and Y 
   *
   * Test Case  - Tester Assigned 
   *       TC1                 U1 
   *
   * user U1 has to execute TWO test cases, NOT ONE.    
   * This means that we HAVE to consider execution status ON (BUILD,PLATFORM),
   * but we are not going to display results with BUILD and PLATFORM,
   * but ONLY with BUILD indication. 
   *
   *  opt => array('getOnlyAssigned' => false, 'tprojectID' => 0, 
   *               'getPlatformSet' => false, 'processClosedBuilds' => true);
   *  filters => array('buildSet' => null);
   *
   */
  function getExecCountersByBuildExecStatus($id, $filters=null, $opt=null) {
    $debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
    $safe_id = intval($id);
    list($my,$builds,$sqlStm) = 
      $this->helperGetExecCounters($safe_id, $filters, $opt);
    
    $fields = "";    
    if( $my['opt']['groupByPlatform'] ) {
      $fields = ",platform_id";
    }

    // This subquery is BETTER than the VIEW, need to understand why
    // Last Executions By Build and Platform (LEBBP)
    $sqlLEBBP =  $sqlStm['LEBBP'];

    $sqlUnionAB  =  "/* {$debugMsg} sqlUnionAB - executions */" . 
      " SELECT DISTINCT UA.build_id, TPTCV.tcversion_id, TPTCV.platform_id, " . 
      " COALESCE(E.status,'{$this->notRunStatusCode}') AS status " .
      " FROM {$this->tables['testplan_tcversions']} TPTCV " .
      " JOIN {$this->tables['user_assignments']} UA " .
      " ON UA.feature_id = TPTCV.id " .
      " AND UA.build_id IN ({$builds->inClause}) AND UA.type = {$this->execTaskCode} " .

      " /* GO FOR Absolute LATEST exec ID ON BUILD and PLATFORM */ " .
      " JOIN ({$sqlLEBBP}) AS LEBBP " .
      " ON  LEBBP.testplan_id = TPTCV.testplan_id " .
      " AND LEBBP.platform_id = TPTCV.platform_id " .
      " AND LEBBP.tcversion_id = TPTCV.tcversion_id " .
      " AND LEBBP.testplan_id = " . $safe_id .

      " /* Get execution status WRITTEN on DB */ " .
      " JOIN {$this->tables['executions']} E " .
      " ON  E.id = LEBBP.id " .
      " AND E.build_id = UA.build_id " .

      " WHERE TPTCV.testplan_id=" . $safe_id .
      " AND UA.build_id IN ({$builds->inClause}) ";
      
    $sqlUnionBB  =  "/* {$debugMsg} sqlUnionBB - NOT RUN */" . 
      " SELECT DISTINCT UA.build_id, TPTCV.tcversion_id, TPTCV.platform_id, " . 
      " COALESCE(E.status,'{$this->notRunStatusCode}') AS status " .
      " FROM {$this->tables['testplan_tcversions']} TPTCV " .
      " JOIN {$this->tables['user_assignments']} UA " .
      " ON UA.feature_id = TPTCV.id " .
      " AND UA.build_id IN ({$builds->inClause}) AND UA.type = {$this->execTaskCode} " .

      " /* Get REALLY NOT RUN => BOTH LE.id AND E.id ON LEFT OUTER see WHERE  */ " .
      " LEFT OUTER JOIN ({$sqlLEBBP}) AS LEBBP " .
      " ON  LEBBP.testplan_id = TPTCV.testplan_id " .
      " AND LEBBP.platform_id = TPTCV.platform_id " .
      " AND LEBBP.tcversion_id = TPTCV.tcversion_id " .

      // Without this I've created issue 5272
      " AND LEBBP.build_id = UA.build_id " .

      " AND LEBBP.testplan_id = " . $safe_id .
      " LEFT OUTER JOIN {$this->tables['executions']} E " .
      " ON  E.tcversion_id = TPTCV.tcversion_id " .
      " AND E.testplan_id = TPTCV.testplan_id " .
      " AND E.platform_id = TPTCV.platform_id " .

      // Without this I've created issue 5272
      " AND E.build_id = LEBBP.build_id " .

      " /* FILTER BUILDS in set on target test plan (not alway can be applied) */ " .
      " WHERE TPTCV.testplan_id=" . $safe_id . 
      " AND UA.build_id IN ({$builds->inClause}) " .

      " /* Get REALLY NOT RUN => BOTH LE.id AND E.id NULL  */ " .
      " AND E.id IS NULL AND LEBBP.id IS NULL";
      

    // 20140819 - I've not documented why I've use UNION ALL
    // UNION ALL includes duplicates, but now (@20140819) because I've implemented
    // test case exec assignment to MULTIPLE TESTERS, I need to remove duplicates
    // to avoid wrong exec_qty.
    // My choice was: add DISTINCT to each union piece.
    // May be is a wrong choice, but I need to read and test more to understand  
    $sql =  " /* {$debugMsg} UNION WITH ALL CLAUSE */" .
            " SELECT count(0) AS exec_qty, build_id,status $fields " .
            " FROM ($sqlUnionAB UNION ALL $sqlUnionBB ) AS SQBU " .
            " GROUP BY build_id,status $fields";
    // 366
    if( $my['opt']['groupByPlatform'] ) {
      $kol = array('platform_id','build_id','status');
      $exec['with_tester'] = 
        (array)$this->db->fetchRowsIntoMap3l($sql,$kol);

      // Need to Add info regarding:
      // - Add info for ACTIVE BUILD WITHOUT any execution. ???
      //   Hmm, think about Need to check is this way is better that request DBMS to do it.
      // - Execution status that have not happened
      foreach($exec as $dum => &$elem) {                            
        $platSet = array_keys($elem);
        foreach($platSet as $platId ) {
          $itemSet = array_keys($elem[$platId]);
          foreach($itemSet as $itemID) {
            foreach($this->statusCode as $verbose => $code) {
              if(!isset($elem[$platId][$itemID][$code])) {
                $elem[$platId][$itemID][$code] = 
                  array('build_id' => $itemID,'status' => $code, 'exec_qty' => 0);
              }                           
            }
          }
        }
      }
    } else {
      $exec['with_tester'] = 
        (array)$this->db->fetchMapRowsIntoMap($sql,'build_id','status');      
      // Need to Add info regarding:
      // - Add info for ACTIVE BUILD WITHOUT any execution. ???
      //   Hmm, think about Need to check is this way is better that request DBMS to do it.
      // - Execution status that have not happened
      foreach($exec as &$elem) {                             
        $itemSet = array_keys($elem);
        foreach($itemSet as $itemID) {
          foreach($this->statusCode as $verbose => $code) {
            if(!isset($elem[$itemID][$code])) {
              $elem[$itemID][$code] = 
                array('build_id' => $itemID,'status' => $code, 'exec_qty' => 0);
            }                           
          }
        }
      }
    }       
              
    // get total assignments by BUILD ID
    // changes due to test case exec assignment to MULTIPLE TESTERS

    if( $my['opt']['groupByPlatform'] ) {

      $sql = " /* $debugMsg */ 
               SELECT COUNT(0) AS qty, TT.build_id, TT.platform_id 
                FROM ( 
                  SELECT DISTINCT UA.build_id, UA.feature_id,
                  TPTCV.platform_id  
                  FROM {$this->tables['user_assignments']} UA
                  JOIN {$this->tables['testplan_tcversions']} TPTCV 
                  ON TPTCV.id = UA.feature_id 
                  WHERE UA. build_id IN ( {$builds->inClause} )
                  AND UA.type = {$this->execTaskCode} 
                  GROUP BY build_id,feature_id, platform_id
                ) TT 
                GROUP BY build_id, platform_id ";

      $exec['total'] = 
        (array)$this->db->fetchMapRowsIntoMap(
                 $sql,'platform_id','build_id');

    } else {  
      $sql = " /* $debugMsg */ 
               SELECT COUNT(0) AS qty, TT.build_id 
                FROM ( 
                  SELECT DISTINCT UA.build_id, UA.feature_id 
                  FROM {$this->tables['user_assignments']} UA
                  JOIN {$this->tables['testplan_tcversions']} TPTCV 
                  ON TPTCV.id = UA.feature_id 
                  WHERE UA. build_id IN ( {$builds->inClause} )
                  AND UA.type = {$this->execTaskCode} 
                  GROUP BY build_id,feature_id
                ) TT 
                GROUP BY build_id ";

     $exec['total'] = 
       (array)$this->db->fetchRowsIntoMap($sql,'build_id');
    }

    $exec['active_builds'] = $builds->infoSet;

    return $exec;
  }
  
                                      
  /**
   *
   *
   **/
  function getOverallBuildStatusForRender($id, $totalKey='total_assigned') {
    $renderObj = null;
    $code_verbose = $this->getStatusForReports();
    $labels = $this->resultsCfg['status_label'];
      
    $metrics = $this->getExecCountersByBuildExecStatus($id);
    if( !is_null($metrics) ) {
       $renderObj = new stdClass();

      // Creating item list this way will generate a row also for
      // ACTIVE BUILDS were ALL TEST CASES HAVE NO TESTER ASSIGNMENT
      // $buildList = array_keys($metrics['active_builds']);
      
      // Creating item list this way will generate a row ONLY FOR
      // ACTIVE BUILDS were TEST CASES HAVE TESTER ASSIGNMENT
      $buildList = array_keys($metrics['with_tester']);
      $renderObj->info = array();  
      foreach($buildList as $buildID)
      {
        $totalRun = 0;
        $renderObj->info[$buildID]['build_name'] = $metrics['active_builds'][$buildID]['name'];   
        $renderObj->info[$buildID][$totalKey] = $metrics['total'][$buildID]['qty'];   

        $renderObj->info[$buildID]['details'] = array();
        
        $rf = &$renderObj->info[$buildID]['details'];
        foreach($code_verbose as $statusCode => $statusVerbose)
        {
          $rf[$statusVerbose] = array('qty' => 0, 'percentage' => 0);
          $rf[$statusVerbose]['qty'] = $metrics['with_tester'][$buildID][$statusCode]['exec_qty'];   
          
          if( $renderObj->info[$buildID][$totalKey] > 0 ) 
          {
            $rf[$statusVerbose]['percentage'] = number_format(100 * 
                                               ($rf[$statusVerbose]['qty'] / 
                                                $renderObj->info[$buildID][$totalKey]),1);
          }
          
          $totalRun += $statusVerbose == 'not_run' ? 0 : $rf[$statusVerbose]['qty'];
        }
        $renderObj->info[$buildID]['percentage_completed'] =  
          number_format(100 * 
            ($totalRun / $renderObj->info[$buildID][$totalKey]),1);
      }
         
      foreach($code_verbose as $human) {
        $l10n = isset($labels[$human]) ? lang_get($labels[$human]) : lang_get($human); 
        $renderObj->colDefinition[$human]['qty'] = $l10n;
        $renderObj->colDefinition[$human]['percentage'] = '[%]';
      }
  
    }
    return $renderObj;
  }



  /** 
   * Important Notice about algorithm
   * We are trying to provide WHOLE Test Plan metrics, 
   * then BUILD INFO will not be IMPORTANT.
   *
   * In addition, Keywords are attributes used on 
   * Test Case specification, for this reason, 
   * our choice is that platforms will be ignored for this metrics.
   *
   * Example: Platform X and Y
   * Test Case: TC1 with one Keyword K1
   *
   * we can develop this data in this way
   *
   * Test Case - Platform - Keyword - Build - Exec. ID - Exec. Status
   *       TC1          X        K1     1.0        11    FAILED
   *       TC1          Y        K1     1.0        13    BLOCKED
   *       TC1          X        K1     2.0        16    PASSED
   *       TC1          Y        K1     2.0        15    BLOCKED
   *
   *
   * We have two choices:
   * OPT 1. Platform multiplication
   *
   * consider (as was done on Builds Overall Status) 
   * TC1 as two test cases.
   * If we proceed this way, may be user will be confused, because
   * when searching test case spec according keyword, we are going to
   * find ONLY ONE.
   *
   * OPT 2. IGNORE PLAFORMS
   * Consider only LATEST execution, means we are going 
   * to count ONE test case no matter how many Platforms 
   * exists on test plan.
   *    
   * Our design choice is on OPT 1
   * 
   * @used-by 
   *
   * 20190711 - things are changing need to relaborate
   *            
   */    
  function getExecCountersByKeywordExecStatus($id, $filters=null, $opt=null) {
    $debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
    $safe_id = intval($id);
    list($my,$builds,$sqlStm) = 
      $this->helperGetExecCounters($safe_id, $filters, $opt);
    
    
    // may be too brute force but ...
    if( ($tprojectID = $my['opt']['tprojectID']) == 0 ) {
      $info = $this->tree_manager->get_node_hierarchy_info($safe_id);
      $tprojectID = $info['parent_id'];
    } 
    $tproject_mgr = new testproject($this->db);
    $keywordSet = $tproject_mgr->get_keywords_map($tprojectID);
    $tproject_mgr = null;
    
    
    // This subquery is BETTER than a VIEW, need to understand why
    // Latest Execution Ignoring Build => Cross Build
    $sqlLEBP = $sqlStm['LEBP'];
    
    // Development Important Notice
    // DISTINCT is needed when you what to get data ONLY FOR test cases with assigned testers,
    // because we are (to make things worst) working on a BUILD SET, not on a SINGLE build,
    // Use of IN clause, will have a NOT wanted multiplier effect on this query.
    //
    // This do not happens with other queries on other metric attributes,
    // be careful before changing other queries.
    // 
    $sqlUnionAK  =  "/* {$debugMsg} sqlUnionAK - executions */" . 
            " SELECT DISTINCT NHTCV.parent_id, TCK.keyword_id, TPTCV.platform_id,KW.keyword," .
            " COALESCE(E.status,'{$this->notRunStatusCode}') AS status " .
            " FROM {$this->tables['testplan_tcversions']} TPTCV " .
            
            $sqlStm['getAssignedFeatures'] .

            " /* GO FOR Absolute LATEST exec ID IGNORE BUILD */ " .
            " JOIN ({$sqlLEBP}) AS LEBP " .
            " ON  LEBP.testplan_id = TPTCV.testplan_id " .
            " AND LEBP.platform_id = TPTCV.platform_id " .
            " AND LEBP.tcversion_id = TPTCV.tcversion_id " .
            " AND LEBP.testplan_id = " . $safe_id .

            " /* Get execution status WRITTEN on DB */ " .
            " JOIN {$this->tables['executions']} E " .
            " ON  E.id = LEBP.id " .

            " /* Get ONLY Test case versions that has AT LEAST one Keyword assigned */ ".
            " JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
            " ON NHTCV.id = TPTCV.tcversion_id " .
            " JOIN {$this->tables['testcase_keywords']} TCK " .
            " ON TCK.testcase_id = NHTCV.parent_id " .

            " JOIN {$this->tables['keywords']} KW " .
            " ON  KW.id = TCK.keyword_id " .
          
            " WHERE TPTCV.testplan_id=" . $safe_id .
            $builds->whereAddExec;

    // See Note about DISTINCT, on sqlUnionAK
    $sqlUnionBK  =  "/* {$debugMsg} sqlUnionBK - NOT RUN */" . 
            " SELECT DISTINCT NHTCV.parent_id, TCK.keyword_id, TPTCV.platform_id,KW.keyword," .
            " COALESCE(E.status,'{$this->notRunStatusCode}') AS status " .
            " FROM {$this->tables['testplan_tcversions']} TPTCV " .
            
            $sqlStm['getAssignedFeatures'] .

            " /* Get REALLY NOT RUN => BOTH LEBP.id AND E.id ON LEFT OUTER see WHERE  */ " .
            " LEFT OUTER JOIN ({$sqlLEBP}) AS LEBP " .
            " ON  LEBP.testplan_id = TPTCV.testplan_id " .
            " AND LEBP.platform_id = TPTCV.platform_id " .
            " AND LEBP.tcversion_id = TPTCV.tcversion_id " .
            " AND LEBP.testplan_id = " . $safe_id .
            " LEFT OUTER JOIN {$this->tables['executions']} E " .
            " ON  E.tcversion_id = TPTCV.tcversion_id " .
            " AND E.testplan_id = TPTCV.testplan_id " .
            " AND E.platform_id = TPTCV.platform_id " .
            $builds->joinAdd .

            " /* Get ONLY Test case versions that has AT LEAST one Keyword assigned */ ".
            " JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
            " ON NHTCV.id = TPTCV.tcversion_id " .

            " JOIN {$this->tables['testcase_keywords']} TCK " .
            " ON TCK.testcase_id = NHTCV.parent_id " .

            " JOIN {$this->tables['keywords']} KW " .
            " ON  KW.id = TCK.keyword_id " .

            " /* FILTER BUILDS in set on target test plan */ " .
            " WHERE TPTCV.testplan_id=" . $safe_id . 
            $builds->whereAddNotRun .
  
            " /* Get REALLY NOT RUN => BOTH E.id AND LEBP.id  NULL  */ " .
            " AND E.id IS NULL AND LEBP.id IS NULL";

    // Due to PLATFORMS we will have MULTIPLIER EFFECT
    $fields = "keyword_id";    
    if( $my['opt']['groupByPlatform'] ) {
      $fields = "platform_id,keyword_id";
    }
    $sql =  
        " /* {$debugMsg} UNION Without ALL CLAUSE => DISCARD Duplicates */" .
        " SELECT status,keyword,$fields,count(0) AS exec_qty " .
        " FROM ($sqlUnionAK UNION $sqlUnionBK ) AS SQK " .
        " GROUP BY status,keyword,$fields 
          ORDER BY keyword ";

    if( $my['opt']['groupByPlatform'] ) {
      $kol = array('platform_id','keyword_id','status');
      $exec['with_tester'] = 
        (array)$this->db->fetchRowsIntoMap3l($sql,$kol);

      $this->helperStatusDomainMatrix($exec,'platform_id','keyword_id');

    } else {
      $exec['with_tester'] = 
        (array)$this->db->fetchMapRowsIntoMap($sql,'keyword_id','status');
      $this->helperCompleteStatusDomain($exec,'keyword_id');
    }
    
    // On next queries:
    // we need to use distinct, because IF NOT we are going 
    // to get one record for each build where test case 
    // has TESTER ASSIGNMENT
    //
    // $exec['total_assigned'] = null;
    $exec['total'] = null;
    $exec['key4total'] = 'total';
    if( $my['opt']['getOnlyAssigned'] ) {
      // $exec['key4total'] = 'total_assigned';
      $sql =   "/* $debugMsg */ ".
          " SELECT COUNT(0) AS qty,$fields " .
          " FROM " . 
          " ( /* Get test case,keyword pairs */ " .
          "  SELECT DISTINCT NHTCV.parent_id, TCK.keyword_id,TPTCV.platform_id " . 
          "  FROM {$this->tables['user_assignments']} UA " .
          "  JOIN {$this->tables['testplan_tcversions']} TPTCV ON TPTCV.id = UA.feature_id " .
          
          "  /* Get ONLY Test case versions that has AT LEAST one Keyword assigned */ ".
          "  JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
          "  ON NHTCV.id = TPTCV.tcversion_id " .
          "  JOIN {$this->tables['testcase_keywords']} TCK " .
          "  ON TCK.testcase_id = NHTCV.parent_id " .
          "  WHERE UA. build_id IN ( " . $builds->inClause . " ) " .
          "  AND UA.type = {$execCode} ) AS SQK ".
          " GROUP BY $fields";
    } else {
      $sql =   "/* $debugMsg */ ".
          " SELECT COUNT(0) AS qty, $fields" .
          " FROM " . 
          " ( /* Get test case,keyword pairs */ " .
          "  SELECT DISTINCT NHTCV.parent_id, TCK.keyword_id,TPTCV.platform_id " . 
          "  FROM {$this->tables['testplan_tcversions']} TPTCV " .
          
          "  /* Get ONLY Test case versions that has AT LEAST one Keyword assigned */ ".
          "  JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
          "  ON NHTCV.id = TPTCV.tcversion_id " .
          "  JOIN {$this->tables['testcase_keywords']} TCK " .
          "  ON TCK.testcase_id = NHTCV.parent_id " .
          "  WHERE TPTCV.testplan_id = " . $safe_id . " ) AS SQK ".
          " GROUP BY $fields";
    }  

    if( $my['opt']['groupByPlatform'] ) {

      $exec[$exec['key4total']] = 
        (array)$this->db->fetchMapRowsIntoMap($sql,
                 'platform_id','keyword_id');
    } else {
      $exec[$exec['key4total']] = (array)$this->db->fetchRowsIntoMap($sql,'keyword_id');      
    }
    $exec['keywords'] = $keywordSet;

    return $exec;
  }


  /**
   *
   */
  function getStatusTotalsByKeywordForRender($id,$filters=null,$opt=null) {
    $renderObj = $this->getStatusTotalsByItemForRender($id,'keyword',$filters,$opt);
    return $renderObj;
  }



  /**
   *
   * @internal revisions
   *
   * @since 1.9.4
   * 20120429 - franciscom - 
   */
  function getExecCountersByPlatformExecStatus($id, $filters=null, $opt=null)
  {
    $debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
    $safe_id = intval($id);  
    list($my,$builds,$sqlStm,$union,$platformSet) = $this->helperBuildSQLExecCounters($id, $filters, $opt);

    $add2key = '';
    $addOnWhere = '';
    $addOnJoin = '';
    if( isset($opt['getOnlyActiveTCVersions']) )
    {
      $add2key='Active';
      $addOnWhere = ' AND TCV.active = 1 '; 
      $addOnJoin = " JOIN {$this->tables['tcversions']} TCV ON TCV.id = TPTCV.tcversion_id ";
    }
    $sqlUnionAP  = $union['exec' . $add2key];  
    $sqlUnionBP  =  $union['not_run' . $add2key];

    $sql =  " /* {$debugMsg} UNION ALL CLAUSE => INCLUDE Duplicates */" .
            " SELECT platform_id,status, count(0) AS exec_qty " .
            " FROM ($sqlUnionAP UNION ALL $sqlUnionBP ) AS SQPL " .
            " GROUP BY platform_id,status ";

    $exec['with_tester'] = (array)$this->db->fetchMapRowsIntoMap($sql,'platform_id','status');              

    $this->helperCompleteStatusDomain($exec,'platform_id');

    // get total test cases by Platform id ON TEST PLAN (With & WITHOUT tester assignment)
    $sql =   "/* $debugMsg */ ".
        " SELECT COUNT(0) AS qty, TPTCV.platform_id " . 
        " FROM {$this->tables['testplan_tcversions']} TPTCV " .
        $addOnJoin .
        " WHERE TPTCV.testplan_id=" . $safe_id . $addOnWhere .
        " GROUP BY platform_id";

    $exec['total'] = (array)$this->db->fetchRowsIntoMap($sql,'platform_id');
    $exec['platforms'] = $platformSet;

    return $exec;
  }



  /**
   *
   * @internal revisions
   *
   */
  function getStatusTotalsByPlatformForRender($id,$filters=null,$opt=null) {
    $renderObj = $this->getStatusTotalsByItemForRender($id,'platform',$filters,$opt);
    return $renderObj;
  }



  /**
   *
   * If no build set provided, ONLY ACTIVE BUILDS will be considered
   *
   *
   */
  function getExecCountersByPriorityExecStatus($id, $filters=null, $opt=null) {
    $debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
    $safe_id = intval($id);
    list($my,$builds,$sqlStm) = $this->helperGetExecCounters($safe_id, $filters, $opt);
  
    // Due to PLATFORMS we will have MULTIPLIER EFFECT
    $fields = "";    
    if( $my['opt']['groupByPlatform'] ) {
      $fields = ",platform_id";
    }
  
    $sqlLEBP = $sqlStm['LEBP'];

    $sqlUnionA  =  "/* {$debugMsg} sqlUnionA - executions */" . 
                  " SELECT (TPTCV.urgency * TCV.importance) AS urg_imp, " .
                  " TPTCV.tcversion_id, TPTCV.platform_id," .
                  " COALESCE(E.status,'{$this->notRunStatusCode}') AS status " .
                  " FROM {$this->tables['testplan_tcversions']} TPTCV " .
        
                  $sqlStm['getAssignedFeatures'] .
      
                  " /* Get importance  */ ".
                  " JOIN {$this->tables['tcversions']} TCV " .
                  " ON TCV.id = TPTCV.tcversion_id " .
        
                  " /* GO FOR Absolute LATEST exec ID IGNORE BUILD */ " .
                  " JOIN ({$sqlLEBP}) AS LEBP " .
                  " ON  LEBP.testplan_id = TPTCV.testplan_id " .
                  " AND LEBP.platform_id = TPTCV.platform_id " .
                  " AND LEBP.tcversion_id = TPTCV.tcversion_id " .
                  " AND LEBP.testplan_id = " . $safe_id .
  
                  " /* Get execution statuses that CAN BE WRITTEN TO DB */ " .
                  " JOIN {$this->tables['executions']} E " .
                  " ON  E.id = LEBP.id " .
                  
                  // Without this we get duplicates ?? => 20121121 CONFIRMED at least with NOT RUN WE GET DUPS
                  $builds->joinAdd .
      
                  " /* FILTER BUILD Set on target test plan */ " .
                  " WHERE TPTCV.testplan_id=" . $safe_id . 
                  $builds->whereAddExec;
            

    $sqlUnionB  =  "/* {$debugMsg} sqlUnionB - NOT RUN */" . 
                  " SELECT (TPTCV.urgency * TCV.importance) AS urg_imp, " .
                  " TPTCV.tcversion_id, TPTCV.platform_id," .
                  " COALESCE(E.status,'{$this->notRunStatusCode}') AS status " .
                  " FROM {$this->tables['testplan_tcversions']} TPTCV " .
                  
                  $sqlStm['getAssignedFeatures'] .
      
                  " /* Get importance  */ ".
                  " JOIN {$this->tables['tcversions']} TCV " .
                  " ON TCV.id = TPTCV.tcversion_id " .
        
                  " /* Get REALLY NOT RUN => BOTH LE.id AND E.id ON LEFT OUTER see WHERE  */ " .
                  " LEFT OUTER JOIN ({$sqlLEBP}) AS LEBP " .
                  " ON  LEBP.testplan_id = TPTCV.testplan_id " .
                  " AND LEBP.platform_id = TPTCV.platform_id " .
                  " AND LEBP.tcversion_id = TPTCV.tcversion_id " .
                  " AND LEBP.testplan_id = " . intval($id) .
                  " LEFT OUTER JOIN {$this->tables['executions']} E " .
                  " ON  E.tcversion_id = TPTCV.tcversion_id " .
                  " AND E.testplan_id = TPTCV.testplan_id " .
                  " AND E.platform_id = TPTCV.platform_id " .
                  $builds->joinAdd .
      
                  " /* FILTER BUILDS in set on target test plan */ " .
                  " WHERE TPTCV.testplan_id=" . $safe_id . 
                  $builds->whereAddNotRun .
  
                  " /* Get REALLY NOT RUN => BOTH LEBP.id AND E.id NULL  */ " .
                  " AND E.id IS NULL AND LEBP.id IS NULL";
      
    
    // ATTENTION:
    // Each piece of UNION has 3 fields: urg_imp,status, TPTCV.tcversion_id     
    // There is no way we can get more that ONE record with same TUPLE
    // on sqlUionA or sqlUnionB ?.
    // 
    // If we have PLATFORM we are going to get a MULTIPLIER EFFECT
    //
    $sql =  " /* {$debugMsg} UNION WITHOUT ALL => DISCARD Duplicates */" .
            " SELECT count(0) as exec_qty, urg_imp,status $fields " .
            " FROM ($sqlUnionA UNION $sqlUnionB ) AS SU " .
            " GROUP BY urg_imp,status $fields";
    
    if( $my['opt']['groupByPlatform'] ) {
      $kol = array('platform_id','urg_imp','status');
      $rs = (array)$this->db->fetchRowsIntoMap3l($sql,$kol);
    } else {
      $rs = $this->db->get_recordset($sql);
    }  

    // Now we need to get priority LEVEL from (urgency * importance)
    $out = array();
    $totals = array();
    $priorityCfg = config_get('urgencyImportance');
    if( !is_null($rs) ) {
      $loop2do = count($rs);
      if( $my['opt']['groupByPlatform'] ) {
        // loop2do => platform Qty
        foreach( $rs as $platID => $elem ) {
          foreach( $elem as $urgImpVal => $statusUrgImp ) {

            if ($urgImpVal >= $priorityCfg->threshold['high']) {
              $hitOn = HIGH;
            } else if( $urgImpVal < $priorityCfg->threshold['low']) {
              $hitOn = LOW;
            } else {
              $hitOn = MEDIUM;
            }
            
            $llx = 0;            
            foreach( $statusUrgImp as $statusCode => $dummy ) {
              
              $rz = &$rs[$platID][$urgImpVal][$statusCode];
              $rz['priority_level'] = $hitOn;

              // to improve readability
              if( !isset($out[$hitOn][$statusCode]) ) {
                $out[$platID][$hitOn][$statusCode] = $rz;
              } else {
                $out[$platID][$hitOn][$statusCode]['exec_qty'] += 
                  $rz['exec_qty'];
              }
              if( !isset($totals[$platID][$hitOn]) ) {
                $totals[$platID][$hitOn] = 
                  array('priority_level' => $hitOn, 'qty' => 0);
              }
              $totals[$platID][$hitOn]['qty'] += $rz['exec_qty'];
            }
          }
        }
      } else {
        // The OLD WAY 
        if( !is_null($rs) ) {
          for($jdx=0; $jdx < $loop2do; $jdx++) {
            if ($rs[$jdx]['urg_imp'] >= $priorityCfg->threshold['high']) {            
              $rs[$jdx]['priority_level'] = HIGH;
              $hitOn = HIGH;
            } else if( $rs[$jdx]['urg_imp'] < $priorityCfg->threshold['low']) {
              $rs[$jdx]['priority_level'] = LOW;
              $hitOn = LOW;
            } else {
              $rs[$jdx]['priority_level'] = MEDIUM;
              $hitOn = MEDIUM;
            }
                                          
            // to improve readability
            $status = $rs[$jdx]['status'];
            if( !isset($out[$hitOn][$status]) ) {
              $out[$hitOn][$status] = $rs[$jdx];
            } else {
              $out[$hitOn][$status]['exec_qty'] += $rs[$jdx]['exec_qty'];
            }
            
            if( !isset($totals[$hitOn]) ) {
              $totals[$hitOn] = array('priority_level' => $hitOn, 'qty' => 0);
            }
            $totals[$hitOn]['qty'] += $rs[$jdx]['exec_qty'];
          }
        }
      }
      $exec['with_tester'] = $out;
      $out = null; 
  
    }

    // $this->helperCompleteStatusDomain($exec,'priority_level');
    if( $my['opt']['groupByPlatform'] ) {
      $this->helperStatusDomainMatrix($exec,'platform_id','priority_level');
    } else {
      $this->helperCompleteStatusDomain($exec,'priority_level');
    }

    $exec['total'] = $totals;

    $levels = config_get('urgency');
    foreach($levels['code_label'] as $lc => $lbl) {
      $exec['priority_levels'][$lc] = lang_get($lbl);
    }

    return $exec;
  }



  /**
   *
   * @internal revisions
   *
   * @since 1.9.4
   * 20120429 - franciscom - 
   */
  function getStatusTotalsByPriorityForRender($id,$filters=null,$opt=null) {
    $renderObj = $this->getStatusTotalsByItemForRender($id,'priority_level',$filters,$opt);
    return $renderObj;
  }


  /**
   *
   *
   */
  function getExecCountersByExecStatus($id, $filters=null, $opt=null) {
    $debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
    $safe_id = intval($id);  
    list($my,$builds,$sqlStm,$union,$platformSet) = $this->helperBuildSQLExecCounters($id, $filters, $opt);

    if(is_null($builds) || count($builds) <= 0) {
      return null;  // >>---> Bye!
    }  


    // Latest Executions By Platform (LEBP)
    $add2key = '';
    if( isset($opt['getOnlyActiveTCVersions']) ) {
      $add2key='Active';
    }
    
    $sqlUnionAP  = $union['exec' . $add2key];  //echo 'QD - <br>' . $sqlUnionAP . '<br>';
    $sqlUnionBP  =  $union['not_run' . $add2key]; //echo 'QD - <br>' . $sqlUnionBP . '<br>';
    
    $sql =  " /* {$debugMsg} UNION ALL CLAUSE => INCLUDE Duplicates */" .
            " SELECT status, count(0) AS exec_qty " .
            " FROM ($sqlUnionAP UNION ALL $sqlUnionBP ) AS SQPL " .
            " GROUP BY status ";

    $dummy = (array)$this->db->fetchRowsIntoMap($sql,'status');              

    $statusCounters = array('total' => 0);
    $codeVerbose = array_flip($this->map_tc_status);
    foreach($dummy as $code => $elem)
    {
      
      $statusCounters['total'] += $elem['exec_qty'];
      $statusCounters[$codeVerbose[$code]] = $elem['exec_qty'];
    } 
    
    return $statusCounters;
  }

  /**
   * @internal revisions
   *
   * @since 1.9.6
   * 20130107 - franciscom - TICKET 5457: Incorrect data in "Report by tester per build"
   */
  function getExecCountersByBuildUAExecStatus($id, $filters=null, $opt=null)
  {
    $debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
    $safe_id = intval($id);
    list($my,$builds,$sqlStm) = $this->helperGetExecCounters($safe_id, $filters, $opt);

    // Last Executions By Build and Platform (LEBBP)
    // Please remember that Platforms (when exists) has Multiplier effect on test cases
    //
    $sqlLEBBP = $sqlStm['LEBBP'];
    
    // 20120817 - franciscom -
    // I'm not to happy with DISTINCT I've added to do this work.
    // Do not understand why i get multiple identical records
    //
    // 20130107 - Think I've got the issue:
    // was the missing clause     
    // " AND UA.feature_id = TPTCV.id AND UA.build_id = LEBBP.build_id " .
    //
    // 20140128 - do not understand why I've added DISTINT.
    //            without it I can add also get exec times
    $sqlUnionBU  =  "/* {$debugMsg} */" . 
                    " SELECT /* DISTINCT */ UA.user_id, UA.build_id, TPTCV.tcversion_id, TPTCV.platform_id, " .
                    " COALESCE(E.status,'{$this->notRunStatusCode}') AS status, " .
                    " COALESCE(E.execution_duration,0) AS execution_duration " .
                    " FROM {$this->tables['testplan_tcversions']} TPTCV " .
                    " JOIN {$this->tables['user_assignments']} UA " .
                    " ON UA.feature_id = TPTCV.id " .
                    " AND UA.build_id IN ({$builds->inClause}) AND UA.type = {$this->execTaskCode} " .
                    
                    " /* LEFT OUTER in order to get NOT RUN */ " .
                    " LEFT OUTER JOIN ({$sqlLEBBP}) AS LEBBP " .
                    " ON  LEBBP.testplan_id = TPTCV.testplan_id " .
                    " AND LEBBP.platform_id = TPTCV.platform_id " .
                    " AND LEBBP.tcversion_id = TPTCV.tcversion_id " .
                    " AND LEBBP.testplan_id = " . $safe_id .      
                    
                    " /* Without this piece we are including results for features without tester */ ".
                    " AND UA.feature_id = TPTCV.id AND UA.build_id = LEBBP.build_id " .
                    
                    " LEFT OUTER JOIN {$this->tables['executions']} E " .
                    " ON  E.build_id = UA.build_id " .
                    " AND E.testplan_id = TPTCV.testplan_id " .
                    " AND E.platform_id = TPTCV.platform_id " .
                    " AND E.tcversion_id = TPTCV.tcversion_id " .
                    " AND E.id = LEBBP.id " . // TICKET 5192
                    " WHERE TPTCV.testplan_id=" . $safe_id .
                    " AND UA.build_id IN ({$builds->inClause}) " ;
  
    $sql =  " /* {$debugMsg} */" .
            " SELECT user_id, build_id,status, count(0) AS exec_qty, SUM(execution_duration) AS total_time" .
            " FROM ($sqlUnionBU) AS SQBU " .
            " GROUP BY user_id,build_id,status ";
           
    $keyColumns = array('build_id','user_id','status');
    $exec['with_tester'] = (array)$this->db->fetchRowsIntoMap3l($sql,$keyColumns);              

    $totals = array();
    foreach($exec as &$topLevelElem)
    {                             
      $topLevelItemSet = array_keys($topLevelElem);
      foreach($topLevelItemSet as $topLevelItemID)
      {
        $itemSet = array_keys($topLevelElem[$topLevelItemID]);
        foreach($itemSet as $itemID)
        {
          $elem = &$topLevelElem[$topLevelItemID];
          foreach($this->statusCode as $verbose => $code)
          {
            if(!isset($elem[$itemID][$code]))
            {
              $elem[$itemID][$code] = array('build_id' => $topLevelItemID, 'user_id' => $itemID,
                                            'status' => $code, 'exec_qty' => 0, 'total_time' => 0);      
            }                           

            if( !isset($totals[$topLevelItemID][$itemID]) )
            {
              $totals[$topLevelItemID][$itemID] = array('build_id' => $topLevelItemID, 
                                                        'user_id' => $itemID, 'qty' => 0,'total_time' => 0);
            }
            $totals[$topLevelItemID][$itemID]['qty'] += $elem[$itemID][$code]['exec_qty'];
            $totals[$topLevelItemID][$itemID]['total_time'] += $elem[$itemID][$code]['total_time'];
          }
        }
      }
    }
    $exec['total'] = $totals;

    return $exec;
  }



  /**
   * @see resultsByTesterPerBuild.php
   * @internal revisions
   *
   * @since 1.9.6
   */
  function getStatusTotalsByBuildUAForRender($id,$opt=null)
  {
    $my = array('opt' => array('processClosedBuilds' => true));
    $my['opt'] = array_merge($my['opt'],(array)$opt);
    
    $renderObj = null;
    $code_verbose = $this->getStatusForReports();
    $labels = $this->resultsCfg['status_label'];
    $metrics = $this->getExecCountersByBuildUAExecStatus($id,null,$my['opt']);
    
    if( !is_null($metrics) )
    {
      $renderObj = new stdClass();
      $topItemSet = array_keys($metrics['with_tester']);
      $renderObj->info = array();  
      $out = &$renderObj->info;

      $topElem = &$metrics['with_tester'];
      foreach($topItemSet as $topItemID)
      {
        $itemSet = array_keys($topElem[$topItemID]);
        foreach($itemSet as $itemID)
        {
          $elem = &$topElem[$topItemID][$itemID];

          $out[$topItemID][$itemID]['total'] = $metrics['total'][$topItemID][$itemID]['qty'];
          $progress = 0; 
          foreach($code_verbose as $statusCode => $statusVerbose)
          {
            $out[$topItemID][$itemID][$statusVerbose]['count'] = $elem[$statusCode]['exec_qty'];
            $pc = ($elem[$statusCode]['exec_qty'] / $out[$topItemID][$itemID]['total']) * 100;
            $out[$topItemID][$itemID][$statusVerbose]['percentage'] = number_format($pc, 1);

            if($statusVerbose != 'not_run')
            {
              $progress += $elem[$statusCode]['exec_qty'];
            }
          }  
          $progress = ($progress / $out[$topItemID][$itemID]['total']) * 100;
          $out[$topItemID][$itemID]['progress'] = number_format($progress,1); 
          $out[$topItemID][$itemID]['total_time'] = 
              number_format($metrics['total'][$topItemID][$itemID]['total_time'],2,'.',''); 
        }
      }
    }
    return $renderObj;
  }


  /**
   * 
   * @used-by getStatusTotalsByKeywordForRender()
   *
   */
  function getStatusTotalsByItemForRender($id,$itemType,$filters=null,$opt=null) {
    $renderObj = null;
    $code_verbose = $this->getStatusForReports();
    $labels = $this->resultsCfg['status_label'];

    
    $returnArray = false;
    $byPlatform = false;

    switch($itemType) {  
      case 'keyword':    
        $metrics = $this->getExecCountersByKeywordExecStatus($id,$filters,$opt);
        $setKey = 'keywords';
        $byPlatform = isset($opt['groupByPlatform']) && 
                      $opt['groupByPlatform'];
      break;

      case 'platform':    
        $myOpt = array_merge(array('getPlatformSet' => true),(array)$opt);
        $metrics = $this->getExecCountersByPlatformExecStatus($id,$filters,$myOpt);
        $setKey = 'platforms';
      break;
      
      case 'priority_level':    
        $metrics = $this->getExecCountersByPriorityExecStatus($id,$filters,$opt);
        $setKey = 'priority_levels';
        $byPlatform = isset($opt['groupByPlatform']) && 
                      $opt['groupByPlatform'];
      break;
      
      case 'tsuite':    
        $metrics = $this->getExecCountersByTestSuiteExecStatus($id,$filters,$opt);
        $setKey = 'tsuites';
        $returnArray = true;
        $byPlatform = isset($opt['groupByPlatform']) && 
                      $opt['groupByPlatform'];
      break;
    }

    if( !is_null($metrics) && !is_null($metrics[$setKey]) > 0) {
      $renderObj = new stdClass();
      $renderObj->info = array(); 

      if( $byPlatform == false ) { 
        $itemList = array_keys($metrics[$setKey]);      
        foreach($itemList as $itemID) {
          if( isset($metrics['with_tester'][$itemID]) ) {
            $totalRun = 0;
            $renderObj->info[$itemID]['type'] = $itemType;
            $renderObj->info[$itemID]['name'] = $metrics[$setKey][$itemID];   
            $renderObj->info[$itemID]['total_tc'] = $metrics['total'][$itemID]['qty'];   
            $renderObj->info[$itemID]['details'] = array();
            
            $rf = &$renderObj->info[$itemID]['details'];
            $doPerc = ($renderObj->info[$itemID]['total_tc'] > 0); 
            foreach($code_verbose as $statusCode => $statusVerbose) {
              $rf[$statusVerbose] = array('qty' => 0, 'percentage' => 0);
              $rf[$statusVerbose]['qty'] = $metrics['with_tester'][$itemID][$statusCode]['exec_qty'];   
              
              if($doPerc) {
                $rf[$statusVerbose]['percentage'] = 
                  number_format(100 * ($rf[$statusVerbose]['qty'] / 
                    $renderObj->info[$itemID]['total_tc']),1);
              }
              $totalRun += 
                ($statusVerbose == 'not_run' ? 0 : $rf[$statusVerbose]['qty']);
            }
            if($doPerc) {
              $renderObj->info[$itemID]['percentage_completed'] =  number_format(100 * 
                  ($totalRun/$renderObj->info[$itemID]['total_tc']),1);
            }                                             
          }
        }
      } else {
        // mainKey is Platform ID
        $platList = array_keys($metrics['with_tester']); 
        $mex = &$metrics['with_tester']; 
        foreach($platList as $platID) {
          $itemList = array_keys($mex[$platID]);
          foreach($itemList as $itemID) {
            if( isset($mex[$platID]) ) {
              $totalRun = 0;
              $renderObj->info[$platID][$itemID]['type'] = $itemType;
              $renderObj->info[$platID][$itemID]['name'] = $metrics[$setKey][$itemID];   
              $renderObj->info[$platID][$itemID]['total_tc'] = $metrics['total'][$platID][$itemID]['qty'];   
              $renderObj->info[$platID][$itemID]['details'] = array();
            
              $rf = &$renderObj->info[$platID][$itemID]['details'];
              $doPerc = ($renderObj->info[$platID][$itemID]['total_tc'] > 0); 
              foreach($code_verbose as $statusCode => $statusVerbose) {
                $rf[$statusVerbose] = array('qty' => 0, 'percentage' => 0);
                $rf[$statusVerbose]['qty'] = $mex[$platID][$itemID][$statusCode]['exec_qty'];   
                
                if($doPerc) {
                  $rf[$statusVerbose]['percentage'] = 
                    number_format(100 * ($rf[$statusVerbose]['qty'] / 
                      $renderObj->info[$platID][$itemID]['total_tc']),1);
                }
                $totalRun += 
                  ($statusVerbose == 'not_run' ? 0 : $rf[$statusVerbose]['qty']);
              }
              if($doPerc) {
                $renderObj->info[$platID][$itemID]['percentage_completed'] =  number_format(100 * 
                    ($totalRun/$renderObj->info[$platID][$itemID]['total_tc']),1);
              }                                             
            }
          }
        }
      }

      // Verbosity!   
      foreach($code_verbose as $status_verbose) {
        $l10n = isset($labels[$status_verbose]) ? lang_get($labels[$status_verbose]) : lang_get($status_verbose); 
        
        $renderObj->colDefinition[$status_verbose]['qty'] = $l10n;
        $renderObj->colDefinition[$status_verbose]['percentage'] = '[%]';
      }
    }
    
    // How to return things
    if($returnArray) {
      return array($renderObj,$metrics['staircase']);
    } else {
      unset($metrics);
      return $renderObj;
    }
  }


  /**
   *
   * @internal revisions
   *
   * @since 1.9.4
   * 20120429 - franciscom - 
   */
  function getStatusTotalsByTestSuiteForRender($id,$filters=null,$opt=null)
  {
    list($renderObj,$staircase) = $this->getStatusTotalsByItemForRender($id,'tsuite',$filters,$opt);
    unset($staircase);
    return $renderObj;
  }

  /**
   *
   * @internal revisions
   *
   * @since 1.9.4
   */
  function getStatusTotalsByTopLevelTestSuiteForRender($id,$filters=null,$opt=null)
  {

    list($rx,$staircase) = $this->getStatusTotalsByItemForRender($id,'tsuite',$filters,$opt);

    // ??? $key2loop = array_keys($rx->info);
    $template = array('type' => 'tsuite', 'name' => '','total_tc' => 0,
              'percentage_completed' => 0, 'details' => array());  

    foreach($this->statusCode as $verbose => $code) {
      $template['details'][$verbose] = array('qty' => 0, 'percentage' => 0);
    }
    
    $renderObj = new stdClass();
    $renderObj->colDefinition = $rx->colDefinition;
    
    // collect qty
    $topNameCache = null;
    $execQty = null;

    $key2loop = array_keys($staircase);
    $wp = isset($opt['groupByPlatform']) && $opt['groupByPlatform'];

    if( $wp ) {
      $plat2loop = array_keys($rx->info);
      foreach($key2loop as $tsuite_id) {
        // (count() == 1) => is a TOP LEVEL SUITE, 
        // only element contains Root node, is useless for this algorithm
        // 
        
        if( count($staircase[$tsuite_id]) > 1) {
          // element at position 1 is a TOP LEVEL SUITE
          //$topSuiteID = &$staircase[$tsuite_id][1];
          $topSuiteID = $staircase[$tsuite_id][1];
          $initName = false;
        } else {
          $topSuiteID = $tsuite_id;
          $initName = true;
        }     
    
        // Over Platform
        foreach( $plat2loop as $platId ) {
          if( !isset($renderObj->info[$platId][$topSuiteID]) ) {
            $renderObj->info[$platId][$topSuiteID] = $template;
            $execQty[$platId][$topSuiteID] = 0;
            $initName = true;
          }              

          if( $initName ) {
            $dummy = $this->tree_manager->get_node_hierarchy_info($topSuiteID);
            $renderObj->info[$platId][$topSuiteID]['name'] = 
              $dummy['name'];
            unset($dummy);
          }  
          
          // Loop to get executions counters
          $r2d2 = &$rx->info[$platId][$tsuite_id];
          if( null !== $r2d2 ) {
            foreach($r2d2['details'] as $code => &$elem) {
              $renderObj->info[$platId][$topSuiteID]['details'][$code]
                ['qty'] += $elem['qty'];    
              $renderObj->info[$platId][$topSuiteID]['total_tc'] += 
                $elem['qty'];

              if( $code != 'not_run' ) {
                $execQty[$platId][$topSuiteID] += $elem['qty'];
              }
            }
          }  
        }  
      }

      // Last step: get percentages
      foreach($renderObj->info as $platID => &$tsuiteMetrics) {
        foreach($tsuiteMetrics as $tsuite_id => &$elem) {
          if( $execQty[$platID][$tsuite_id] > 0 ) {
            $elem['percentage_completed'] = number_format( 100 * 
              ($execQty[$platID][$tsuite_id] / $elem['total_tc']),1);
          }  
          if( $elem['total_tc'] > 0 ) {
            foreach($elem['details'] as $code => &$yumyum) {
              $yumyum['percentage'] = number_format( 100 * ($yumyum['qty'] / $elem['total_tc']),1);    
            }
          }
        }
      } 

    } else {

      // OLD WAY
      foreach($key2loop as $tsuite_id) {
        // (count() == 1) => is a TOP LEVEL SUITE, 
        // only element contains Root node, is useless for this algorithm
        // 
        
        if( count($staircase[$tsuite_id]) > 1) {
          // element at position 1 is a TOP LEVEL SUITE
          $topSuiteID = &$staircase[$tsuite_id][1];
          $initName = false;
        } else {
          $topSuiteID = $tsuite_id;
          $initName = true;
        }     
        
        if( !isset($renderObj->info[$topSuiteID]) ) {
          $renderObj->info[$topSuiteID] = $template;
          $execQty[$topSuiteID] = 0;
          $initName = true;
        }  

        if( $initName ) {
          $dummy = $this->tree_manager->get_node_hierarchy_info($topSuiteID);
          $renderObj->info[$topSuiteID]['name'] = $dummy['name'];
          unset($dummy);
        }        
          
          
        // Loop to get executions counters
        foreach($rx->info[$tsuite_id]['details'] as $code => &$elem) {
          $renderObj->info[$topSuiteID]['details'][$code]['qty'] += $elem['qty'];    
          $renderObj->info[$topSuiteID]['total_tc'] += $elem['qty'];

          if(  $code != 'not_run' ) {
              $execQty[$topSuiteID] += $elem['qty'];
          }
        }
      }  
         
      // Last step: get percentages
      foreach($renderObj->info as $tsuite_id => &$elem) {
        if( $execQty[$tsuite_id] > 0 ) {
          $elem['percentage_completed'] = number_format( 100 * ($execQty[$tsuite_id] / $elem['total_tc']),1);
        }  

        if( $elem['total_tc'] > 0 ) {
          foreach($elem['details'] as $code => &$yumyum) {
            $yumyum['percentage'] = number_format( 100 * ($yumyum['qty'] / $elem['total_tc']),1);    
          }
        }
      }      
    }


    unset($topNameCache);
    unset($rx);
    unset($staircase);
    unset($template);
    unset($key2loop);
    unset($execQty);

    return $renderObj;
  }

  /** 
   *    
   *    
   *    
   *    
   */    
  function getExecCountersByTestSuiteExecStatus($id, $filters=null, $opt=null)
  {
    $debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
    $safe_id = intval($id);
    list($my,$builds,$sqlStm) = $this->helperGetExecCounters($id, $filters, $opt);

    $fields = "";    
    if( $my['opt']['groupByPlatform'] ) {
      $fields = ",platform_id";
    }


    // Latest Execution Ignoring Build
    $sqlLEBP = $sqlStm['LEBP'];

    $sqlUnionAT  =  "/* {$debugMsg} sqlUnionAT - executions */" . 
      " SELECT NHTC.parent_id AS tsuite_id,TPTCV.platform_id," .
      " COALESCE(E.status,'{$this->notRunStatusCode}') AS status " .
      " FROM {$this->tables['testplan_tcversions']} TPTCV " .

      $sqlStm['getAssignedFeatures'] .
      
      " /* GO FOR Absolute LATEST exec ID IGNORE BUILD */ " .
      " JOIN ({$sqlLEBP}) AS LEBP " .
      " ON  LEBP.testplan_id = TPTCV.testplan_id " .
      " AND LEBP.platform_id = TPTCV.platform_id " .
      " AND LEBP.tcversion_id = TPTCV.tcversion_id " .
      " AND LEBP.testplan_id = " . $safe_id .

      " /* Get execution status WRITTEN on DB */ " .
      " JOIN {$this->tables['executions']} E " .
      " ON  E.id = LEBP.id " .

      " /* Get Test Case info from Test Case Version */ " .
      " JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
      " ON  NHTCV.id = TPTCV.tcversion_id " .

      " /* Get Test Suite info from Test Case  */ " .
      " JOIN {$this->tables['nodes_hierarchy']} NHTC " .
      " ON  NHTC.id = NHTCV.parent_id " .

      " WHERE TPTCV.testplan_id=" . $safe_id .
      $builds->whereAddExec;
            


    $sqlUnionBT  =  "/* {$debugMsg} sqlUnionBK - NOT RUN */" . 
      " SELECT NHTC.parent_id AS tsuite_id,TPTCV.platform_id," .
      " COALESCE(E.status,'{$this->notRunStatusCode}') AS status " .
      " FROM {$this->tables['testplan_tcversions']} TPTCV " .
      
      $sqlStm['getAssignedFeatures'] .

      " /* Get REALLY NOT RUN => BOTH LEBP.id AND E.id ON LEFT OUTER see WHERE  */ " .
      " LEFT OUTER JOIN ({$sqlLEBP}) AS LEBP " .
      " ON  LEBP.testplan_id = TPTCV.testplan_id " .
      " AND LEBP.platform_id = TPTCV.platform_id " .
      " AND LEBP.tcversion_id = TPTCV.tcversion_id " .
      " AND LEBP.testplan_id = " . $safe_id .
      " LEFT OUTER JOIN {$this->tables['executions']} E " .
      " ON  E.tcversion_id = TPTCV.tcversion_id " .
      " AND E.testplan_id = TPTCV.testplan_id " .
      " AND E.platform_id = TPTCV.platform_id " .

      $builds->joinAdd .

      " /* Get Test Case info from Test Case Version */ " .
      " JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
      " ON  NHTCV.id = TPTCV.tcversion_id " .

      " /* Get Test Suite info from Test Case  */ " .
      " JOIN {$this->tables['nodes_hierarchy']} NHTC " .
      " ON  NHTC.id = NHTCV.parent_id " .

      " /* FILTER BUILDS in set on target test plan (not alway can be applied) */ " .
      " WHERE TPTCV.testplan_id=" . $safe_id . 
      $builds->whereAddNotRun .

      " /* Get REALLY NOT RUN => BOTH LE.id AND E.id NULL  */ " .
      " AND E.id IS NULL AND LEBP.id IS NULL";

    
    $sql =  " /* {$debugMsg} UNION ALL DO NOT DISCARD Duplicates */" .
        " SELECT count(0) AS exec_qty, tsuite_id, status $fields" .
        " FROM ($sqlUnionAT UNION ALL $sqlUnionBT ) AS SQT " .
        " GROUP BY tsuite_id ,status $fields";

    if( $my['opt']['groupByPlatform'] ) {
      $kol = array('platform_id','tsuite_id','status');
      $exec['with_tester'] = 
        (array)$this->db->fetchRowsIntoMap3l($sql,$kol);
      $this->helperStatusDomainMatrix($exec,'platform_id','tsuite_id');

    } else {
      $exec['with_tester'] = 
        (array)$this->db->fetchMapRowsIntoMap($sql,'tsuite_id','status');

      // now we need to complete status domain
      $this->helperCompleteStatusDomain($exec,'tsuite_id');
    }
        
  
    // Build item set
    $exec['tsuites_full'] = $this->get_testsuites($safe_id);
    $loop2do = count($exec['tsuites_full']);
    for($idx=0; $idx < $loop2do; $idx++) {
      $keySet[] = $exec['tsuites_full'][$idx]['id'];

    }
    $dx = $this->tree_manager->get_full_path_verbose($keySet,array('output_format' => 'stairway2heaven'));
    for($idx=0; $idx < $loop2do; $idx++) {
      $exec['tsuites'][$exec['tsuites_full'][$idx]['id']] = $dx['flat'][$exec['tsuites_full'][$idx]['id']];
    }
    $exec['staircase'] = $dx['staircase'];
    
    unset($dx);
    unset($keySet);
    return $exec;
  }




  /** 
   *    
   *    
   *    
   *    
   */    
  function getExecStatusMatrix($id, $filters=null, $opt=null) {
    $debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;

    $my = array();
    $my['opt'] = array('getExecutionNotes' => false, 
                       'getTester' => false,
                       'getUserAssignment' => false, 
                       'output' => null,
                       'getExecutionTimestamp' => false, 
                       'getExecutionDuration' => false);

    $my['opt'] = array_merge($my['opt'], (array)$opt);
    $safe_id = intval($id);
    list($my,$builds,$sqlStm,$union) = $this->helperBuildSQLTestSuiteExecCounters($id, $filters, $my['opt']);

    $sql =  " /* {$debugMsg} UNION WITH ALL CLAUSE */ " .
            " {$union['exec']} UNION ALL {$union['not_run']} ";

    
    $keyColumns = array('tsuite_id','tcase_id','platform_id','build_id');
    $cumulative = ($my['opt']['output'] == 'cumulative');
    $dummy = (array)$this->db->fetchRowsIntoMap4l($sql,$keyColumns,$cumulative);              

    unset($sqlStm);
    unset($union);
    unset($my);
    unset($builds);

    // now is time do some decoding
    // Key is a tuple (PARENT tsuite_id, test case id, platform id)
    //
    $item2loop = array_keys($dummy);
    $stairway2heaven = null;
    $pathway = null;
    $latestExec = null;
    $priorityCfg = config_get('urgencyImportance');

    foreach ($item2loop as $item_id) {
      $stairway2heaven = $this->tree_manager->get_path($item_id,null,'name');
      $pathway[$item_id] = implode("/",$stairway2heaven);
      unset($stairway2heaven);

      // go inside test case
      $tcase2loop = array_keys($dummy[$item_id]);
      foreach($tcase2loop as $tcase_id) {
        $platform2loop = array_keys($dummy[$item_id][$tcase_id]);
        foreach($platform2loop as $platform_id) {
          $latestExec[$platform_id][$tcase_id] = 
            array('id' => -1, 'status' => $this->notRunStatusCode);
          $rf = &$dummy[$item_id][$tcase_id][$platform_id];
          foreach($rf as $build_id => &$exec) {
            $exec['suiteName'] = $pathway[$item_id];          
            if($exec['executions_id'] > $latestExec[$platform_id][$tcase_id]['id']) {
              $latestExec[$platform_id][$tcase_id]['id'] = $exec['executions_id'];
              $latestExec[$platform_id][$tcase_id]['status'] = $exec['status'];
              $latestExec[$platform_id][$tcase_id]['build_id'] = $exec['build_id'];
              $latestExec[$platform_id][$tcase_id]['execution_notes'] = $exec['execution_notes'];
            }
            
            // ---------------------------------------------------
            // Now we need to get priority LEVEL from 
            // (urgency * impact)
            // we do not use a function to improve performance
            if ($exec['urg_imp'] >= $priorityCfg->threshold['high']) {
              $exec['priority_level'] = HIGH;
            } else if( $exec['urg_imp'] < $priorityCfg->threshold['low']) {
              $exec['priority_level'] = LOW;
            } else {
              $exec['priority_level'] = MEDIUM;
            }
            // ------------------------------------------------------
          } // $rf
        } // $platform2loop    
      } // $tcase2loop
      
      unset($tcase2loop);
      unset($platform2loop);
    } //
      
    unset($pathway);
    return array('metrics' => $dummy, 'latestExec' => $latestExec);
  }
  
  
    
  /** 
   *    
   *  @used-by
   *  getExecutionsByStatus()
   *  getNotRunWithTesterAssigned()
   *  getNotRunWOTesterAssigned()
   *  getExecCountersByBuildExecStatus()
   *  getExecCountersByKeywordExecStatus()
   *  getExecCountersByPriorityExecStatus()
   *  getExecCountersByBuildUAExecStatus()
   *  getExecCountersByTestSuiteExecStatus()
   *   
   *    
   *  
   */    
  function helperGetExecCounters($id, $filters, $opt) {
    $sql = array();
    $my = array();
    $my['opt'] = array('getOnlyAssigned' => false, 
                       'tprojectID' => 0, 
                       'getUserAssignment' => false,
                       'getPlatformSet' => false, 
                       'processClosedBuilds' => true,
                       'groupByPlatform' => false);
    $my['opt'] = array_merge($my['opt'], (array)$opt);
    
    $my['filters'] = array('buildSet' => null);
    $my['filters'] = array_merge($my['filters'], (array)$filters);
    
    // Build Info
    $bi = new stdClass();
    $bi->idSet = $my['filters']['buildSet']; 
    $bi->inClause = '';
    $bi->infoSet = null;

    if (is_null($bi->idSet)) {
      $openStatus = $my['opt']['processClosedBuilds'] ? null : 1;

      $bi->infoSet = $this->get_builds($id,testplan::ACTIVE_BUILDS,
                                       $openStatus);
      if (!is_null($bi->infoSet)) {
       $bi->idSet = array_keys($bi->infoSet);
      }
    }
    
    // =========================================================
    // Emergency Exit !!!
    if ( is_null($bi->idSet) ) {
        throw new Exception(__METHOD__ . " - Can not work with empty build set");
    }
    // =========================================================
    
    
    // Things seems to be OK
    $bi->inClause = implode(",",$bi->idSet);
    if( $my['opt']['getOnlyAssigned'] ) {
      $sql['getAssignedFeatures']   =  " /* Get feature id with Tester Assignment */ " .
                                       " JOIN {$this->tables['user_assignments']} UA " .
                                       " ON UA.feature_id = TPTCV.id " .
                                       " AND UA.build_id IN ({$bi->inClause}) " .
                                       " AND UA.type = {$this->execTaskCode} ";
      $bi->source = "UA";
      $bi->joinAdd = " AND E.build_id = UA.build_id ";
      $bi->whereAddExec = " AND {$bi->source}.build_id IN ({$bi->inClause}) "; 
      $bi->whereAddNotRun = $bi->whereAddExec; 
    }            
    else
    {
      $sql['getAssignedFeatures'] = '';
      $bi->source = "E";
      
      // TICKET 5353
      // $bi->joinAdd = "";
      $bi->joinAdd = " AND E.build_id IN ({$bi->inClause}) ";
      
      // Why ?
      // If I'm consider test cases WITH and WITHOUT Tester assignment,
      // I will have no place to go to filter for builds.
      // Well at least when trying to get EXECUTED test case, I will be able
      // to apply filter on Executions table.
      // Why then I choose to have this blank ANYWAY ?
      // Because I will get filtering on Build set through 
      // the Latest Execution queries (see below sql['LE'], sql['LEBP'].
      // 
      // Anyway we need to backup all these thoughts with a long, long test run
      // on test link itself.
      $bi->whereAddExec = " AND {$bi->source}.build_id IN ({$bi->inClause}) "; 
      $bi->whereAddNotRun = ""; 
    }               

    $sql['getUserAssignment']['not_run'] = "";
    $sql['getUserAssignment']['exec'] = "";

    if( $my['opt']['getUserAssignment'] )
    {
      $sql['getUserAssignment']['not_run'] = 
        " LEFT JOIN {$this->tables['user_assignments']} UA " .
        " ON UA.feature_id = TPTCV.id " .
        " AND UA.build_id = BU.id " .
        " AND UA.type = {$this->execTaskCode} ";
   
      $sql['getUserAssignment']['exec'] = 
        " LEFT JOIN {$this->tables['user_assignments']} UA " .
        " ON UA.feature_id = TPTCV.id " .
        " AND UA.build_id = E.build_id " .
        " AND UA.type = {$this->execTaskCode} ";
    }


    // Latest Execution IGNORING Build and Platform
    $sql['LE'] = " SELECT EE.tcversion_id,EE.testplan_id,MAX(EE.id) AS id " .
                 " FROM {$this->tables['executions']} EE " . 
                 " WHERE EE.testplan_id=" . intval($id) . 
                 " AND EE.build_id IN ({$bi->inClause}) " .
                 " GROUP BY EE.tcversion_id,EE.testplan_id ";


    // Latest Execution By Platform (ignore build)
    $sql['LEBP'] =   " SELECT EE.tcversion_id,EE.testplan_id,EE.platform_id,MAX(EE.id) AS id " .
                    " FROM {$this->tables['executions']} EE " . 
                     " WHERE EE.testplan_id=" . intval($id) . 
                    " AND EE.build_id IN ({$bi->inClause}) " .
                     " GROUP BY EE.tcversion_id,EE.testplan_id,EE.platform_id ";

    // Last Executions By Build (LEBB) (ignore platform)
    $sql['LEBB'] =  " SELECT EE.tcversion_id,EE.testplan_id,EE.build_id,MAX(EE.id) AS id " .
                    " FROM {$this->tables['executions']} EE " . 
                    " WHERE EE.testplan_id=" . intval($id) . 
                    " AND EE.build_id IN ({$bi->inClause}) " .
                    " GROUP BY EE.tcversion_id,EE.testplan_id,EE.build_id ";
  

    // Last Executions By Build and Platform (LEBBP)
    $sql['LEBBP'] = " SELECT EE.tcversion_id,EE.testplan_id,EE.platform_id,EE.build_id," .
                    " MAX(EE.id) AS id " .
                    " FROM {$this->tables['executions']} EE " . 
                    " WHERE EE.testplan_id=" . intval($id) . 
                    " AND EE.build_id IN ({$bi->inClause}) " .
                    " GROUP BY EE.tcversion_id,EE.testplan_id,EE.platform_id,EE.build_id ";


    return array($my,$bi,$sql);
  }  



  /** 
   *    
   *    
   *    
   *    
   */    
  function helperCompleteStatusDomain(&$out,$key) {                       
    $totalByItemID = array();
    
    // refence is critic  
    foreach($out as &$elem) {                             
      $itemSet = array_keys($elem);
      foreach($itemSet as $itemID) {             
        $totalByItemID[$itemID]['qty'] = 0;
        foreach($this->statusCode as $verbose => $code) {
          if(!isset($elem[$itemID][$code])) {
            $elem[$itemID][$code] = array($key => $itemID,'status' => $code, 'exec_qty' => 0);      
          }                           
          $totalByItemID[$itemID]['qty'] += $elem[$itemID][$code]['exec_qty'];
        }
      }
    }
    $out['total'] = $totalByItemID;
  }



  /** 
   *    
   *    
   */    
  function helperBuildSQLExecCounters($id, $filters=null, $opt=null)
  {
    $debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
    try
    {
      list($my,$builds,$sqlStm) = $this->helperGetExecCounters($id, $filters, $opt);
    }
    catch(Exception $e)
    {
      return null;
    }


    $safe_id = intval($id);  
    $platformSet = null;
    if( $my['opt']['getPlatformSet'] ) {
      $getOpt = array('outputFormat' => 'mapAccessByID', 'outputDetails' => 'name', 'addIfNull' => true);
      $platformSet = $this->getPlatforms($safe_id,$getOpt);
    }
    
    // Latest Executions By Platform (LEBP)
    $sqlLEBP =   $sqlStm['LEBP'];
    
    
    // 20121121 - franciscom
    // Need to understand if this sentence is right:
    //
    // GO FOR Absolute LATEST exec ID IGNORE BUILD
    // Is this right for each use of this method ?
    //
    $dummy['exec']  =  "/* {$debugMsg} sqlUnion - executions */" . 
                      " SELECT TPTCV.tcversion_id,TPTCV.platform_id, " .
                      " COALESCE(E.status,'{$this->notRunStatusCode}') AS status " .
                      " FROM {$this->tables['testplan_tcversions']} TPTCV " .
            
                      $sqlStm['getAssignedFeatures'] .

                      " /* GO FOR Absolute LATEST exec ID IGNORE BUILD */ " .
                      " JOIN ({$sqlLEBP}) AS LEBP " .
                      " ON  LEBP.testplan_id = TPTCV.testplan_id " .
                      " AND LEBP.platform_id = TPTCV.platform_id " .
                      " AND LEBP.tcversion_id = TPTCV.tcversion_id " .
                      " AND LEBP.testplan_id = " . $safe_id .

                      " /* Get execution status WRITTEN on DB */ " .
                      " JOIN {$this->tables['executions']} E " .
                      " ON  E.id = LEBP.id ";
    
    
    $union['exec'] = $dummy['exec'] . " WHERE TPTCV.testplan_id=" . $safe_id . $builds->whereAddExec;

    $union['execActive'] =  $dummy['exec'] .
                            " /* Used to filter ON ACTIVE TCVersion */ " .
                            " JOIN {$this->tables['tcversions']} TCV " .
                            " ON  TCV.id = TPTCV.tcversion_id " .
                            " WHERE TPTCV.testplan_id=" . $safe_id . $builds->whereAddExec .
                            " AND TCV.active = 1 ";
  

    // 20121121 - An issue was reported in this scenario:
    // Test Plan with Platforms (ONE)
    // Two Build:
    // B1 with TC1 passed, TC2 failed, TC3 not run - BUT B1 INACTIVE
    // B3 ALL TEST CASES NOT RUN
    //
    // we got WRONG figures if build set is NOT USING when trying to access Executions TABLE
    //
    $dummy['not_run'] =  "/* {$debugMsg} sqlUnion - NOT RUN */" . 
              " SELECT TPTCV.tcversion_id,TPTCV.platform_id, " .
              " COALESCE(E.status,'{$this->notRunStatusCode}') AS status " .
              " FROM {$this->tables['testplan_tcversions']} TPTCV " .
              
              $sqlStm['getAssignedFeatures'] .
    
              " /* Get REALLY NOT RUN => BOTH LE.id AND E.id ON LEFT OUTER see WHERE  */ " .
              " LEFT OUTER JOIN ({$sqlLEBP}) AS LEBP " .
              " ON  LEBP.testplan_id = TPTCV.testplan_id " .
              " AND LEBP.tcversion_id = TPTCV.tcversion_id " .
              " AND LEBP.platform_id = TPTCV.platform_id " .
              " AND LEBP.testplan_id = " . $safe_id .
              " LEFT OUTER JOIN {$this->tables['executions']} E " .
              " ON  E.tcversion_id = TPTCV.tcversion_id " .
              " AND E.testplan_id = TPTCV.testplan_id " .
              " AND E.platform_id = TPTCV.platform_id " .
              $builds->joinAdd;
    

              $union['not_run'] = $dummy['not_run'] . 
                                  " /* FILTER BUILDS in set on target test plan (not always can be applied) */ " .
                                  " WHERE TPTCV.testplan_id=" . $safe_id . 
                                  $builds->whereAddNotRun .
                                  " /* Get REALLY NOT RUN => BOTH LE.id AND E.id NULL  */ " .
                                  " AND E.id IS NULL AND LEBP.id IS NULL";
    

              $union['not_runActive'] =  $dummy['not_run'] .
                  " /* Used to filter ON ACTIVE TCVersion */ " .
                  " JOIN {$this->tables['tcversions']} TCV " .
                  " ON  TCV.id = TPTCV.tcversion_id " .
                  " WHERE TPTCV.testplan_id=" . $safe_id . 
                  $builds->whereAddNotRun .
                  " /* Get REALLY NOT RUN => BOTH LE.id AND E.id NULL  */ " .
                  " AND E.id IS NULL AND LEBP.id IS NULL" .
                  " AND TCV.active = 1 ";

    
    //echo 'QD - <br>' . $sqlUnionBP . '<br>';
    return array($my,$builds,$sqlStm,$union,$platformSet);
  }


  /** 
   *    
   *    
   *    
   * @internal revision
   * @since 1.9.8
   * 20130713 - franciscom - 
   * when getting info for executed test cases, RIGHT version number for execution 
   * is on EXECUTIONS TABLE not on testplan_tcversions TABLE.
   *
   * REMEMBER that when we update TCVERSION for executed Test Cases, we HAVE TO UPDATE
   * testplan_tcversions table.
   *
   * We also need to use E.tcversion_id and NOT TPTCV.tcversion_id.
   *
   */    
  function helperBuildSQLTestSuiteExecCounters($id, $filters=null, $opt=null)
  {
    $debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;

    $my['opt'] = array('getExecutionNotes' => false, 
                       'getTester' => false, 
                       'getUserAssignment' => false,
                       'getExecutionTimestamp' => false, 
                       'getExecutionDuration' => false);
    $my['opt'] = array_merge($my['opt'], (array)$opt);


    list($my,$builds,$sqlStm) = $this->helperGetExecCounters($id, $filters, $opt);

    $safe_id = intval($id);  


    // Additional Execution fields
    $moreExecFields = "";
    if ($my['opt']['getExecutionNotes']) {
      $moreExecFields .= "E.notes AS execution_notes,";
    }  
    
    if ($my['opt']['getTester']) {
      $moreExecFields .= "E.tester_id,";
    } 
    
    if ($my['opt']['getExecutionTimestamp']) {
      $moreExecFields .= "E.execution_ts,";
    } 

    if ($my['opt']['getExecutionDuration']) {
      $moreExecFields .= "E.execution_duration,";
    } 

    if ($my['opt']['getUserAssignment']) {
      $moreExecFields .= "UA.user_id,";
    } 
  
    // Latest Executions By Build Platform (LEBBP)
    $sqlLEBBP = $sqlStm['LEBBP'];

    $union['exec']  =  "/* {$debugMsg} sqlUnion Test suites - executions */" . 
              " SELECT NHTC.parent_id AS tsuite_id,NHTC.id AS tcase_id, NHTC.name AS name," .
              " TPTCV.tcversion_id,TPTCV.platform_id," .
              " E.build_id,E.tcversion_number AS version,TCV.tc_external_id AS external_id, " .
              " E.id AS executions_id, E.status AS status, " . 
              $moreExecFields .
              " E.execution_type AS exec_type," .

              " (TPTCV.urgency * TCV.importance) AS urg_imp " .
              " FROM {$this->tables['testplan_tcversions']} TPTCV " .
  
              $sqlStm['getAssignedFeatures'] .
  
              " /* GO FOR Absolute LATEST exec ID On BUILD,PLATFORM */ " .
              " JOIN ({$sqlLEBBP}) AS LEBBP " .
              " ON  LEBBP.testplan_id = TPTCV.testplan_id " .
              " AND LEBBP.platform_id = TPTCV.platform_id " .
              " AND LEBBP.tcversion_id = TPTCV.tcversion_id " .
              " AND LEBBP.testplan_id = " . $safe_id .

              " /* Get execution status WRITTEN on DB */ " .
              " JOIN {$this->tables['executions']} E " .
              " ON  E.id = LEBBP.id " .
              " AND E.build_id = LEBBP.build_id " .

              $sqlStm['getUserAssignment']['exec'] .


              " /* Get Test Case info from Test Case Version */ " .
              " JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
              " ON  NHTCV.id = TPTCV.tcversion_id " .
  
              " /* Get Test Suite info from Test Case  */ " .
              " JOIN {$this->tables['nodes_hierarchy']} NHTC " .
              " ON  NHTC.id = NHTCV.parent_id " .
              
              " /* Get Test Case Version attributes */ " .
              " JOIN {$this->tables['tcversions']} TCV " .
              // " ON  TCV.id = TPTCV.tcversion_id " .
              " ON  TCV.id = E.tcversion_id " . 

              " WHERE TPTCV.testplan_id=" . $safe_id .
              $builds->whereAddExec;

    $union['not_run'] =  
              "/* {$debugMsg} sqlUnion Test suites - not run */" . 
              " SELECT NHTC.parent_id AS tsuite_id,NHTC.id AS tcase_id, NHTC.name AS name," .
              " TPTCV.tcversion_id, TPTCV.platform_id," .
              " BU.id AS build_id,TCV.version,TCV.tc_external_id AS external_id, " .
              " COALESCE(E.id,-1) AS executions_id, " .
              " COALESCE(E.status,'{$this->notRunStatusCode}') AS status, " . 
              $moreExecFields .
              " TCV.execution_type AS exec_type," . 
              " (TPTCV.urgency * TCV.importance) AS urg_imp " .
              " FROM {$this->tables['testplan_tcversions']} TPTCV " .
  
              $sqlStm['getAssignedFeatures'] .
         
              " /* Needed to be able to put a value on build_id on output set  */ " .
              " JOIN {$this->tables['builds']} BU " .
              " ON BU.id IN ({$builds->inClause}) " .

              $sqlStm['getUserAssignment']['not_run'] .
              
              " /* GO FOR Absolute LATEST exec ID On BUILD,PLATFORM */ " .
              " LEFT OUTER JOIN ({$sqlLEBBP}) AS LEBBP " .
              " ON  LEBBP.testplan_id = TPTCV.testplan_id " .
              " AND LEBBP.platform_id = TPTCV.platform_id " .
              " AND LEBBP.tcversion_id = TPTCV.tcversion_id " .
              " AND LEBBP.build_id = BU.id " .
              " AND LEBBP.testplan_id = " . $safe_id .

              " /* Get execution status WRITTEN on DB */ " .
              " LEFT OUTER JOIN {$this->tables['executions']} E " .
              " ON  E.build_id = LEBBP.build_id " .
              " AND E.testplan_id = TPTCV.testplan_id " .
              " AND E.platform_id = TPTCV.platform_id " .
              " AND E.tcversion_id = TPTCV.tcversion_id " .
  
              " /* Get Test Case info from Test Case Version */ " .
              " JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
              " ON  NHTCV.id = TPTCV.tcversion_id " .
  
              " /* Get Test Suite info from Test Case  */ " .
              " JOIN {$this->tables['nodes_hierarchy']} NHTC " .
              " ON  NHTC.id = NHTCV.parent_id " .

              " /* Get Test Case Version attributes */ " .
              " JOIN {$this->tables['tcversions']} TCV " .
              " ON  TCV.id = TPTCV.tcversion_id " .
      
              " WHERE TPTCV.testplan_id=" . $safe_id .
              " AND BU.id IN ({$builds->inClause}) " .
              $builds->whereAddNotRun .
    
              " /* Get REALLY NOT RUN => BOTH LEBBP.id AND E.id NULL  */ " .
              " AND E.id IS NULL AND LEBBP.id IS NULL";
    
    //echo 'QD - <br>' . $union['not_run'] . '<br>';
    return array($my,$builds,$sqlStm,$union);
  }


  /** 
   * get executions (Not Run is not included)   
   *    
   * @param int $id test plan id
   * @param char $status status code (one char)
   * @param mixed $filters
   *        keys: 'buildSet'
   *
   * @param mixed opt    
   *        keys: 'output' elem domain 'map','array'
   *    
   */    
  function getExecutionsByStatus($id,$status,$filters=null,$opt=null)
  {
    $debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
    list($my,$builds,$sqlStm) = $this->helperGetExecCounters($id, $filters, $opt);
    
    // particular options
    $options = array('output' => 'map', 
                     'add2fields' => '');
    $my['opt'] = array_merge($options,$my['opt']);    
    $safe_id = intval($id);  

    $fullEID = $this->helperConcatTCasePrefix($safe_id);

    $addFields = '';
    if ( '' != $my['opt']['add2fields']) {
      $addFields = ',' . $my['opt']['add2fields'];
    }

    $sqlLEBBP = $sqlStm['LEBBP'];
    $sql =  "/* {$debugMsg} executions with status WRITTEN on DB => not run is not present */" . 
            " SELECT NHTC.parent_id AS tsuite_id,NHTC.id AS tcase_id, NHTC.name AS name," .
            " TPTCV.tcversion_id,TPTCV.platform_id," .
            " E.tcversion_number, E.build_id,E.id AS executions_id, E.status AS status, " .
            " E.notes AS execution_notes, E.tester_id,E.execution_ts," .
            " TCV.version,TCV.tc_external_id AS external_id, " .
            " $fullEID AS full_external_id," .
            " (TPTCV.urgency * TCV.importance) AS urg_imp " .
            $addFields .

            " FROM {$this->tables['testplan_tcversions']} TPTCV " .
            
            " /* GO FOR Absolute LATEST exec ID On BUILD,PLATFORM */ " .
            " JOIN ({$sqlLEBBP}) AS LEBBP " .
            " ON  LEBBP.testplan_id = TPTCV.testplan_id " .
            " AND LEBBP.platform_id = TPTCV.platform_id " .
            " AND LEBBP.tcversion_id = TPTCV.tcversion_id " .
            " AND LEBBP.testplan_id = " . $safe_id .

            $sqlStm['getAssignedFeatures'] .
            
            " /* Get execution status WRITTEN on DB */ " .
            " JOIN {$this->tables['executions']} E " .
            " ON  E.id = LEBBP.id " .
            " AND E.build_id = LEBBP.build_id " .
            $builds->joinAdd .


            
            " /* Get Test Case info from Test Case Version */ " .
            " JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
            " ON  NHTCV.id = TPTCV.tcversion_id " .
            
            " /* Get Test Suite info from Test Case  */ " .
            " JOIN {$this->tables['nodes_hierarchy']} NHTC " .
            " ON  NHTC.id = NHTCV.parent_id " .
            
            " /* Get Test Case Version attributes */ " .
            " JOIN {$this->tables['tcversions']} TCV " .
            " ON  TCV.id = TPTCV.tcversion_id " .

            
            " WHERE TPTCV.testplan_id=" . $safe_id .
            " AND E.status='{$status}' " .
            $builds->whereAddExec;
              
                                   
    switch($my['opt']['output'])
    {
      case 'array':
        $dummy = (array)$this->db->get_recordset($sql);              
      break;

      case 'mapByExecID':
        $dummy = (array)$this->db->fetchRowsIntoMap($sql,'executions_id');              
      break;


      case 'map':
      default:
        $keyColumns = array('tsuite_id','tcase_id','platform_id','build_id');
        $dummy = (array)$this->db->fetchRowsIntoMap4l($sql,$keyColumns);              
      break;
    }

    return $dummy;
    
  }


  /** 
   * get just Not Run test case on test plan, but ONLY THESE
   * that has tester assigned.
   * This is critic:
   *
   * example:
   * test plan with 11 test cases linked.
   * two Builds B1, B2
   * 1. Assign tester to all test cases on BUILD B1
   * 2. run getNotRunWithTesterAssigned()
   *    you will get 11 records all for B1
   *
   * 3. Assign tester to 4 test cases on BUILD B2   
   * 4. run getNotRunWithTesterAssigned()
   *    you will get: 15 records
   *    11 records for B1
   *     4 records for B2
     *
   * @param int $id test plan id
   * @param char $status status code (one char)
   * @param mixed $filters
   *        keys: 'buildSet'
   *
   * @param mixed opt    
   *        keys: 'output' elem domain 'map','array'
   *    
   */    
  function getNotRunWithTesterAssigned($id,$filters=null,$opt=null)
  {
    $debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
    list($my,$builds,$sqlStm) = $this->helperGetExecCounters($id, $filters, $opt);

    
    // particular options
    $my['opt'] = array_merge(array('output' => 'map'),$my['opt']);    
    $safe_id = intval($id);  

    $fullEID = $this->helperConcatTCasePrefix($safe_id);
    // $sqlLEBBP = $sqlStm['LEBBP'];

    // Because we now allow assignment of MULTIPLE testers to same test case
    // we need to remove UA.user_id, in order to avoid duplication
    // UA.user_id,
    // we will need a second step to populate this info.
    //
    $sql =  "/* {$debugMsg} Not Run */" . 
        " SELECT DISTINCT NHTC.parent_id AS tsuite_id,NHTC.id AS tcase_id, NHTC.name AS name," .
        " TPTCV.tcversion_id,TPTCV.platform_id,TPTCV.id AS feature_id," .
        " TCV.version AS tcversion_number, B.id AS build_id," . 
        " '{$this->notRunStatusCode}' AS status, " .
        " TCV.version,TCV.tc_external_id AS external_id, " .
        " $fullEID AS full_external_id," .
        " (TPTCV.urgency * TCV.importance) AS urg_imp, TCV.summary " .
        " FROM {$this->tables['testplan_tcversions']} TPTCV " .

        " JOIN {$this->tables['builds']} B " .
        " ON  B.testplan_id = TPTCV.testplan_id " .

        " /* Get Test Case info from Test Case Version */ " .
        " JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
        " ON  NHTCV.id = TPTCV.tcversion_id " .
  
        " /* Get Test Suite info from Test Case  */ " .
        " JOIN {$this->tables['nodes_hierarchy']} NHTC " .
        " ON  NHTC.id = NHTCV.parent_id " .
        
        " /* Get Test Case Version attributes */ " .
        " JOIN {$this->tables['tcversions']} TCV " .
        " ON  TCV.id = TPTCV.tcversion_id " .

        " JOIN {$this->tables['user_assignments']} UA " .
        " ON  UA.feature_id = TPTCV.id " .
        " AND UA.build_id = B.id " .
        " AND UA.type = {$this->execTaskCode} " .

        " LEFT OUTER JOIN {$this->tables['executions']} E " .
        " ON  E.testplan_id = TPTCV.testplan_id " .
        " AND E.platform_id = TPTCV.platform_id " .
        " AND E.tcversion_id = TPTCV.tcversion_id " .
        " AND E.build_id = B.id ".


        " WHERE TPTCV.testplan_id=" . $safe_id .
        " AND E.id IS NULL " .
        " AND B.id IN ({$builds->inClause}) "; 

      
    switch($my['opt']['output'])
    {
      case 'array':
        $dummy = (array)$this->db->get_recordset($sql);              
        
        // Second Loop
        // get features to get testers
        if(!is_null($dummy))
        {
          // will try with a query
              $sql =  "/* {$debugMsg} Not Run */" . 
        " SELECT UA.user_id, UA.feature_id,UA.build_id" .
        " FROM {$this->tables['testplan_tcversions']} TPTCV " .

        " JOIN {$this->tables['builds']} B " .
        " ON  B.testplan_id = TPTCV.testplan_id " .

        " JOIN {$this->tables['user_assignments']} UA " .
        " ON  UA.feature_id = TPTCV.id " .
        " AND UA.build_id = B.id " .
        " AND UA.type = {$this->execTaskCode} " .

        " LEFT OUTER JOIN {$this->tables['executions']} E " .
        " ON  E.testplan_id = TPTCV.testplan_id " .
        " AND E.platform_id = TPTCV.platform_id " .
        " AND E.tcversion_id = TPTCV.tcversion_id " .
        " AND E.build_id = B.id ".

        " WHERE TPTCV.testplan_id=" . $safe_id .
        " AND E.id IS NULL " .
        " AND B.id IN ({$builds->inClause}) "; 

        $dx = $this->db->get_recordset($sql); 

        $l2do = count($dx);
        $loop2do = count($dummy);
        for($vdx=0; $vdx < $l2do; $vdx++)
        { 
          for($fdx=0; $fdx < $loop2do; $fdx++)
          {
            if($dummy[$fdx]['feature_id'] == $dx[$vdx]['feature_id'] &&
               $dummy[$fdx]['build_id'] == $dx[$vdx]['build_id'] 
              )
            {
              $dummy[$fdx]['user_id'][$dx[$vdx]['user_id']] = $dx[$vdx]['user_id'];
              break;
            }  
          }  
        }  
 
        }  


      break;

      case 'map':
      default:
        throw new Exception("NOT REFACTORED YET for output 'map'", 1);
        $keyColumns = array('tsuite_id','tcase_id','platform_id','build_id');
        $dummy = (array)$this->db->fetchRowsIntoMap4l($sql,$keyColumns);              
      break;
    }

    return $dummy;
      
  }


  /*
   *
   * @used-by lib/results/testCasesWithoutTester.php
   *
   * IMPORTANT NOTICE
   * When doing count() with having, if there are platforms defined
   * we have to consider for having clause BuildQty * PlatformQty,
   * or we are going to get WRONG results.
   */
  function getNotRunWOTesterAssigned($id,$buildSet=null,$filters=null,$opt=null)
  {
    $debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
    list($my,$builds,$sqlStm) = $this->helperGetExecCounters($id, $filters, $opt);
    list($safe_id,$buildsCfg,$sqlLEX) = $this->helperGetHits($id,null,$buildSet,
                                                             array('ignorePlatform' => true));
    // particular options
    $my['opt'] = array_merge(array('output' => 'map','ignoreBuild' => false),$my['opt']);    
    $safe_id = intval($id);  

    $fullEID = $this->helperConcatTCasePrefix($safe_id);

    // $sqlLEBBP = $sqlStm['LEBBP'];
    $add2select = ' DISTINCT ';
    $buildInfo = '';
    
    // 20130106 - TICKET 5451 - added A_TPTCV.platform_id on GROUP BY
    // this query try to indentify test cases that has NO ASSIGNMENT ON ALL Builds 
    // for EACH PLATFORM.
    $sqlc = "/* $debugMsg */ " .
            " SELECT count(0) AS TESTER_COUNTER ,A_NHTCV.parent_id AS tcase_id,A_TPTCV.platform_id  " .
            " FROM {$this->tables['testplan_tcversions']} A_TPTCV " .
            " JOIN {$this->tables['builds']} A_B ON A_B.testplan_id = A_TPTCV.testplan_id " .
            str_replace('B.active','A_B.active',$buildsCfg['statusClause']) .
            
            " JOIN {$this->tables['nodes_hierarchy']} A_NHTCV ON " .
            " A_NHTCV.id = A_TPTCV.tcversion_id " .
            
            " LEFT OUTER JOIN {$this->tables['executions']} A_E " .
            " ON  A_E.testplan_id = A_TPTCV.testplan_id " .
            " AND A_E.platform_id = A_TPTCV.platform_id " .
            " AND A_E.tcversion_id = A_TPTCV.tcversion_id " .
            " AND A_E.build_id = A_B.id " .
            
            " LEFT OUTER JOIN {$this->tables['user_assignments']} A_UA " .
            " ON  A_UA.feature_id = A_TPTCV.id " .
            " AND A_UA.build_id = A_B.id " .
            " AND A_UA.type = {$this->execTaskCode} " .
            
            " WHERE A_TPTCV.testplan_id = " . $safe_id  . 
            " AND A_E.status IS NULL " .
            " AND A_UA.user_id IS NULL ";


    // http://stackoverflow.com/questions/7511064/postresql-aliases-column-and-having
    //
    //if( DB_TYPE == 'mssql' )
    //{    
    //  $sqlc .= " GROUP BY tcase_id " .
    //           " HAVING TESTER_COUNTER = " . intval($buildsCfg['count']) ; 
    //}
    //else
    //{
    //  $sqlc .= " GROUP BY A_NHTCV.parent_id " .
    //           " HAVING count(0) = " . intval($buildsCfg['count']) ; 
    //}
    $sqlc .= " GROUP BY A_NHTCV.parent_id, A_TPTCV.platform_id " .                      
             " HAVING count(0) = " . intval($buildsCfg['count']) ; 
    
    
    $sql =  "/* {$debugMsg} Not Run */" . 
        " SELECT $add2select NHTC.parent_id AS tsuite_id,NHTC.id AS tcase_id, NHTC.name AS name," .
        " TPTCV.tcversion_id,TPTCV.platform_id," .
        " TCV.version AS tcversion_number, {$buildInfo}" . 
        " '{$this->notRunStatusCode}' AS status, " .
        " TCV.version,TCV.tc_external_id AS external_id, " .
        " $fullEID AS full_external_id,UA.user_id," .
        " (TPTCV.urgency * TCV.importance) AS urg_imp, TCV.summary  " .
        " FROM {$this->tables['testplan_tcversions']} TPTCV " .

        " JOIN {$this->tables['builds']} B " .
        " ON  B.testplan_id = TPTCV.testplan_id " .

        " /* Get Test Case info from Test Case Version */ " .
        " JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
        " ON  NHTCV.id = TPTCV.tcversion_id " .
  
        " /* Get Test Suite info from Test Case  */ " .
        " JOIN {$this->tables['nodes_hierarchy']} NHTC " .
        " ON  NHTC.id = NHTCV.parent_id " .
        
        " /* Get Test Case Version attributes */ " .
        " JOIN {$this->tables['tcversions']} TCV " .
        " ON  TCV.id = TPTCV.tcversion_id " .

        " JOIN ({$sqlc}) AS NR " .
        " ON  NR.tcase_id = NHTC.id " .

        " LEFT OUTER JOIN {$this->tables['user_assignments']} UA " .
        " ON  UA.feature_id = TPTCV.id " .
        " AND UA.build_id = B.id " .
        " AND UA.type = {$this->execTaskCode} " .

        " LEFT OUTER JOIN {$this->tables['executions']} E " .
        " ON  E.testplan_id = TPTCV.testplan_id " .
        " AND E.platform_id = TPTCV.platform_id " .
        " AND E.tcversion_id = TPTCV.tcversion_id " .
        " AND E.build_id = B.id ".


        " WHERE TPTCV.testplan_id=" . $safe_id .
        " AND E.id IS NULL AND UA.user_id IS NULL " .
        
        // 20130106 - TICKET 5451 - added CONDITION ON NR.platform_id
        " AND B.id IN ({$builds->inClause}) AND TPTCV.platform_id = NR.platform_id "; 

    switch($my['opt']['output'])
    {
      case 'array':
        $dummy = $this->db->get_recordset($sql);  
      break;

      case 'map':
      default:
        $keyColumns = array('tsuite_id','tcase_id','platform_id','build_id');
        $dummy = $this->db->fetchRowsIntoMap4l($sql,$keyColumns);              
      break;
    }
    return (array)$dummy;
  }



  /**
   *
   * @internal revisions
   *
   * @since 1.9.4
   */
  function helperGetUserIdentity($idSet=null)
  {
    $debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
    $sql = " SELECT id,login,first,last " .
         " FROM {$this->tables['users']}";

    $inClause = '';
    if( !is_null($idSet) && ((array)$idSet >0))
    {
      if( ($dummy=implode(',',(array)$idSet)) != '' )
      {
        $inClause = " WHERE id IN ({$dummy}) ";    
      }  
    }

    $rs = $this->db->fetchRowsIntoMap($sql . $inClause,'id');
    return $rs;
  }


  /**
   *
   * @used-by /lib/results/resultsMoreBuilds.php
   *
   * @internal revisions
   *
   * @since 1.9.4
   */
  function queryMetrics($id,$filters=null,$options=null)
  {
    $debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;

    $safe = array();
    $safe['tplan_id'] = intval($id);

    $my = array();
    list($my,$sqlLEX) = $this->initQueryMetrics($safe['tplan_id'],$filters,$options);
  

    // -------------------------------------------------------------------------------------------    
    // We will work always using last execution result as filter criteria.
    // -------------------------------------------------------------------------------------------
  
    // we will need a union to manage 'not run' (remember this status is NEVER WRITTEN to DB)
    // and other statuses
    // This logic have been borrowed from testplan.class.php - getLinkedForExecTree().
    //
    $key2check = array('builds' => 'build_id', 'platforms' => 'platform_id');
    $ejoin = array();
    foreach($key2check as $check => $field)
    {
      $ejoin[$check] = is_null($my['filters'][$check]) ? '' : 
               " AND E.$field IN (" . implode(',',(array)$my['filters'][$check]) . ')';  
    }
    
  
    
    $union['not_run'] = "/* {$debugMsg} sqlUnion - not run */" .
              " SELECT NH_TCASE.id AS tcase_id,TPTCV.tcversion_id,TCV.version," .
              " TCV.tc_external_id AS external_id, " .
              " COALESCE(E.status,'" . $this->notRunStatusCode . "') AS exec_status " .
              
                 " FROM {$this->tables['testplan_tcversions']} TPTCV " .                          
                 " JOIN {$this->tables['tcversions']} TCV ON TCV.id = TPTCV.tcversion_id " .
                 " JOIN {$this->tables['nodes_hierarchy']} NH_TCV ON NH_TCV.id = TPTCV.tcversion_id " .
                 " JOIN {$this->tables['nodes_hierarchy']} NH_TCASE ON NH_TCASE.id = NH_TCV.parent_id " .
              $my['join']['ua'] .
              $my['join']['keywords'] .
              " LEFT OUTER JOIN {$this->tables['platforms']} PLAT ON PLAT.id = TPTCV.platform_id " .
              
              " /* Get REALLY NOT RUN => BOTH LE.id AND E.id ON LEFT OUTER see WHERE  */ " .
              " LEFT OUTER JOIN ({$sqlLEX}) AS LEX " .
              " ON  LEX.testplan_id = TPTCV.testplan_id " .
              " AND LEX.tcversion_id = TPTCV.tcversion_id " .
              " AND LEX.platform_id = TPTCV.platform_id " .
              " AND LEX.testplan_id = " . $safe['tplan_id'] .
              " LEFT OUTER JOIN {$this->tables['executions']} E " .
              " ON  E.tcversion_id = TPTCV.tcversion_id " .
              " AND E.testplan_id = TPTCV.testplan_id " .
              " AND E.platform_id = TPTCV.platform_id " .
              " AND E.build_id = " . $my['filters']['build_id'] .

              " WHERE TPTCV.testplan_id =" . $safe['tplan_id'] .
              $my['where']['where'] .
              " /* Get REALLY NOT RUN => BOTH LE.id AND E.id NULL  */ " .
              " AND E.id IS NULL AND LEX.id IS NULL";

    
    // executions
    $sex = "/* $debugMsg */" .
         "SELECT E.status,E.notes,E.tcversion_number,E.execution_ts,E.build_id,E.platform_id " .
         "FROM {$this->tables['testplan_tcversions']} TPTCV " .
         "JOIN {$this->tables['executions']} E " .
         "ON E.tcversion_id = TPTCV.tcversion_id " .
         "AND E.testplan_id = TPTCV.testplan_id " .
         "AND E.platform_id = TPTCV.platform_id ";
    
    
         
    // build up where clause
    $where = "WHERE TPTCV.testplan_id = " . $safe['tplan_id'];

    $key2check = array('builds' => 'build_id', 'platforms' => 'platform_id');
    foreach($key2check as $check => $field)
    {
      if( !is_null($my['filters'][$check]) )
      {
        $where .= " AND E.$field IN (" . implode(',',(array)$my['filters'][$check]) . ')';  
      }
    }
        
    $sql = $sex . $where;

    // echo $sql;
    $rs = $this->db->get_recordset($sql);           
    return $rs;
  }
  
  
  
  /*
   *
   * @used-by 
   *            
   *
   * @internal revisions
   * @since 1.9.4
   */
  function initQueryMetrics($tplanID,$filtersCfg,$optionsCfg)
  {
    $ic = array();

    $ic['join'] = array();
    $ic['join']['ua'] = '';

    $ic['where'] = array();
    $ic['where']['where'] = '';
    $ic['where']['platforms'] = '';

    $ic['green_light'] = true;
  
    $ic['filters'] = array('exec_ts_from' => null, 'exec_ts_to' => null,
                 'assigned_to' => null, 'tester_id' => null,
                 'keywords' => null, 'builds' => null,
                 'platforms' => null, 'top_level_tsuites' => null);

    $ic['filters'] = array_merge($ic['filters'],(array)$filtersCfg);

    
    // ---------------------------------------------------------------------------------------------
    $sqlLEX = " SELECT EE.tcversion_id,EE.testplan_id,EE.platform_id,EE.build_id," .
          " MAX(EE.id) AS id " .
          " FROM {$this->tables['executions']} EE " . 
          " WHERE EE.testplan_id = " . $tplanID;
  
    $key2check = array('builds' => 'build_id', 'platforms' => 'platform_id');
    foreach($key2check as $check => $field)
    {
      $ic['where'][$check] = '';
      if( !is_null($ic['filters'][$check]) )
      {
        $sqlLEX .= " AND EE.$field IN (" . implode(',',(array)$ic['filters'][$check]) . ')';  
        $ic['where'][$check] = " AND TPTCV.$field IN (" . implode(',',(array)$ic['filters'][$check]) . ')';  
      }
    }
    $sqlLEX  .= " GROUP BY EE.tcversion_id,EE.testplan_id,EE.platform_id,EE.build_id ";
    // ---------------------------------------------------------------------------------------------

    if( !is_null($ic['filters']['keywords']) )
    {    
      list($ic['join']['keywords'],$ic['where']['keywords']) = 
        $this->helper_keywords_sql($ic['filters']['keywords'],array('output' => 'array'));

      $ic['where']['where'] .= $ic['where']['keywords']; // **** // CHECK THIS CAN BE NON OK
    }


    return array($ic,$sqlLEX);    
  }

  /** 
   *    
   *    
   */    
  function getExecStatusMatrixFlat($id, $filters=null, $opt=null) {
    $debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;

    $my = array();
    $my['opt'] = array('getExecutionNotes' => false, 'getTester' => false,
                       'getUserAssignment' => false, 'output' => null,
                       'getExecutionTimestamp' => false, 'getExecutionDuration' => false);

    $my['opt'] = array_merge($my['opt'], (array)$opt);
    $safe_id = intval($id);
    list($my,$builds,$sqlStm,$union) = $this->helperBuildSQLTestSuiteExecCounters($id, $filters, $my['opt']);

    $sql =  " /* {$debugMsg} UNION WITH ALL CLAUSE */ " .
            " {$union['exec']} UNION ALL {$union['not_run']} ";

    //echo $sql;
    $rs = $this->db->get_recordset($sql);              
    // new dBug($rs);

    $ltx = null;
    if(!is_null($rs))
    {
      $priorityCfg = config_get('urgencyImportance');
      $cache = array('tsuite' => null, 'tcase' => null);

      $loop2do = count($rs);
      $gnOpt = array('fields' => 'name');

      for($adx=0; $adx < $loop2do; $adx++)
      {
        if(!isset($cache['tsuite'][$rs[$adx]['tsuite_id']]))
        {
          $stairway2heaven = $this->tree_manager->get_path($rs[$adx]['tsuite_id'],null,'name');
          $cache['tsuite'][$rs[$adx]['tsuite_id']] = implode("/",$stairway2heaven);
        }
        $rs[$adx]['suiteName'] = $cache['tsuite'][$rs[$adx]['tsuite_id']];

        if($rs[$adx]['urg_imp'] >= $priorityCfg->threshold['high']) 
        {            
          $rs[$adx]['priority_level'] = HIGH;
        } 
        else if( $rs[$adx]['urg_imp'] < $priorityCfg->threshold['low']) 
        {
          $rs[$adx]['priority_level'] = LOW;
        }        
        else
        {
          $rs[$adx]['priority_level'] = MEDIUM;
        }

        $kyy = $rs[$adx]['platform_id'] . '-' . $rs[$adx]['tcase_id'];
      
        // $keyExists = isset($ltx[$kyy]);
        $keyExists = isset($ltx[$rs[$adx]['platform_id']][$rs[$adx]['tcase_id']]);
        $doSet = !$keyExists;
        if( $keyExists )
        {
          $doSet = ($ltx[$rs[$adx]['platform_id']][$rs[$adx]['tcase_id']]['id'] < 
                    $rs[$adx]['executions_id']);
        }  
        if( $doSet )
        {
          $ltx[$rs[$adx]['platform_id']][$rs[$adx]['tcase_id']] = 
             array('id' => $rs[$adx]['executions_id'],'build_id' => $rs[$adx]['build_id'],
                   'status' => $rs[$adx]['status']);
        }
      }  
    }  
    //new dBug($cache);
    //new dBug($rs);
    //new dBug($ltx);
 
    return array('metrics' => $rs, 'latestExec' => $ltx);
  }


 /** 
   *    
   *    
   *    
   *    
   */    
  function helperStatusDomainMatrix(&$out,$rowKey,$colKey) {                       
    $totalByMatrix = array();

    foreach($out as &$elem) {   

      $rowSet = array_keys($elem);
      foreach($rowSet as $rowID) {

        $colSet = array_keys($elem[$rowID]);
        foreach($colSet as $colID) {             
          $totalByMatrix[$rowID][$colID]['qty'] = 0;
          foreach($this->statusCode as $verbose => $code) {
            if(!isset($elem[$rowID][$colID][$code])) {
              $elem[$rowID][$colID][$code] = 
                array($rowKey => $rowID, $colKey => $colID,
                      'status' => $code, 'exec_qty' => 0);      
            }                           
            $totalByMatrix[$rowID][$colID]['qty'] += 
              $elem[$rowID][$colID][$code]['exec_qty'];
          }
        }
      }
    }
    $out['total'] = $totalByMatrix;
  }
  
  /**
   *
   * NEWZ
   **/
  function getBuildByPlatStatusForRender($id, $totalKey='total_assigned') {

    $renderObj = null;
    $codeSet = $this->getStatusForReports();
    $labels = $this->resultsCfg['status_label'];
    
    $opt = array('groupByPlatform' => true);  
    $metrics = $this->getExecCountersByBuildExecStatus($id,null,$opt);

    // Creating item list this way will generate a row also for
    // ACTIVE BUILDS were ALL TEST CASES HAVE NO TESTER ASSIGNMENT
    // $buildList = array_keys($metrics['active_builds']);
    
    // Creating item list this way will generate a row ONLY FOR
    // ACTIVE BUILDS were TEST CASES HAVE TESTER ASSIGNMENT
    if( !is_null($metrics) ) {
      $renObj = new stdClass();
      $renObj->info = array();  

      $platList = array_keys($metrics['with_tester']);
      $mwt = &$metrics['with_tester'];
      foreach( $mwt as $platID => $buildMetrics ) {
        foreach($buildMetrics as $buildID => $met ) {
          $totalRun = 0;
          $yo = &$renObj->info[$platID][$buildID];
          $yo['build_name'] = 
            $metrics['active_builds'][$buildID]['name']; 
          $yo[$totalKey] = 
            $metrics['total'][$platID][$buildID]['qty'];   
          $yo['details'] = array();

          $rf = &$yo['details'];
          foreach($codeSet as $cCode => $code4h) {
            $rf[$code4h] = array('qty' => 0, 'percentage' => 0);
            $rf[$code4h]['qty'] = $buildMetrics[$buildID][$cCode]['exec_qty'];   
            
            if( $yo[$totalKey] > 0 ) {
              $rf[$code4h]['percentage'] = 
                number_format(100 * ($rf[$code4h]['qty'] / $yo[$totalKey]),1);
            }
            //$totalRun += 
            //  $code4h == 'not_run' ? 0 : $rf[$code4h]['qty'];
          }        
        }
      }  

      foreach($codeSet as $human) {
        $l10n = isset($labels[$human]) ? lang_get($labels[$human]) : lang_get($human); 
        $renObj->colDefinition[$human]['qty'] = $l10n;
        $renObj->colDefinition[$human]['percentage'] = '[%]';
      }
  
      // Last step: get completness percentages
      $platList = array_keys($renObj->info); 
      $tk = 'total_assigned';
      foreach($platList as $platID) {
        $itemList = array_keys($renObj->info[$platID]);
        foreach($itemList as $itemID) {
          if( isset($renObj->info[$platID]) ) {
            $totalRun = 0;
            $rf = &$renObj->info[$platID][$itemID]['details'];
            $doPerc = ($renObj->info[$platID][$itemID][$tk] > 0); 
            foreach($codeSet as $sCode => $c4human) {
              $totalRun += ($c4human == 'not_run' ? 0 : 
                            $rf[$c4human]['qty']);
            }
            if($doPerc) {
              $renObj->info[$platID][$itemID]['percentage_completed'] = 
                number_format(100 * 
                  ($totalRun/$renObj->info[$platID][$itemID][$tk]),1);
            }                                             
          }
        }
      }
    }
    return $renObj;
  }

  /**
   * 
   *
   */
  function getNeverRunByPlatform($tplanID,$platformSet=null) 
  {

    $debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
    $safeID = intval($tplanID);

    $fullEID = $this->helperConcatTCasePrefix($safeID);

    // Because we now allow assignment of MULTIPLE testers to same test case
    // we need to remove UA.user_id, in order to avoid duplication
    // UA.user_id,
    // we will need a second step to populate this info.
    //
    $sql =  "/* {$debugMsg} Not Run */" . 
          "SELECT COUNT(0) AS qty,
          NHTC.parent_id AS tsuite_id,
          NHTC.id AS tcase_id, TPTCV.platform_id,
          NHTC.name AS name, PLAT.name AS platform_name,
          $fullEID AS full_external_id
          FROM {$this->tables['testplan_tcversions']} TPTCV 
          
          JOIN {$this->tables['builds']} B
          ON  B.testplan_id = TPTCV.testplan_id 

          /* Get Test Case info from Test Case Version */ 
          JOIN {$this->tables['nodes_hierarchy']} NHTCV 
          ON  NHTCV.id = TPTCV.tcversion_id
  
          /* Get Test Suite info from Test Case  */         
          JOIN {$this->tables['nodes_hierarchy']} NHTC
          ON  NHTC.id = NHTCV.parent_id
        
          /* Get Test Case Version attributes */
          JOIN {$this->tables['tcversions']} TCV
          ON  TCV.id = TPTCV.tcversion_id 

          JOIN {$this->tables['platforms']} PLAT
          ON  PLAT.id = TPTCV.platform_id 


          LEFT OUTER JOIN {$this->tables['executions']} E 
          ON  E.testplan_id = TPTCV.testplan_id
          AND E.platform_id = TPTCV.platform_id
          AND E.tcversion_id = TPTCV.tcversion_id
          AND E.build_id = B.id
          
          WHERE TPTCV.testplan_id=$safeID
          AND E.id IS NULL";

    if( null != $platformSet ) {
      $sql .= " AND TPTCV.platform_id IN (" . 
                implode(",", $platformSet) . ")";
    }      
    $sql .= " GROUP BY tsuite_id, tcase_id, 
              TPTCV.platform_id,NHTC.name,platform_name,
              full_external_id ";
    
    $buildSet = $this->get_builds($safeID,testplan::ACTIVE_BUILDS,
                                  testplan::OPEN_BUILDS);

    $sql .= " HAVING COUNT(0) = " . count($buildSet);
    $sql .= " ORDER BY platform_name,full_external_id ";
    
    return $this->db->get_recordset($sql);      
  }


  /** 
   *    
   *    
   *    
   *    
   */    
  function getLatestExecOnSinglePlatformMatrix($id, $platform_id,
    $filters=null, $opt=null) {
    $debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;

    $my = array();
    $my['opt'] = array('getExecutionNotes' => TRUE, 
                       'getTester' => false,
                       'getUserAssignment' => false, 
                       'output' => null,
                       'getExecutionTimestamp' => false, 
                       'getExecutionDuration' => false);

    $my['opt'] = array_merge($my['opt'], (array)$opt);
    $safe_id = intval($id);
    list($my,$builds,$sqlStm,$union) = $this->buildSQLTSLatestExecOneSinglePlatform($id, $platform_id,$filters, $my['opt']);

    $sql =  " /* {$debugMsg} {$union['exec']} ";

    $keyColumns = array('tsuite_id','tcase_id','platform_id');
    $cumulative = true;
    $dummy = (array)$this->db->fetchRowsIntoMap3l($sql,$keyColumns,$cumulative);              

    unset($sqlStm);
    unset($union);
    unset($my);
    unset($builds);

    // now is time do some decoding
    // Key is a tuple (PARENT tsuite_id, test case id, platform id)
    //
    $item2loop = array_keys($dummy);
    $stairway2heaven = null;
    $pathway = null;
    $latestExec = null;
    $priorityCfg = config_get('urgencyImportance');

    foreach ($item2loop as $item_id) {
      $stairway2heaven = $this->tree_manager->get_path($item_id,null,'name');
      $pathway[$item_id] = implode("/",$stairway2heaven);
      unset($stairway2heaven);

      // go inside test case
      $tcase2loop = array_keys($dummy[$item_id]);
      foreach($tcase2loop as $tcase_id) {
        $platform2loop = array_keys($dummy[$item_id][$tcase_id]);
        foreach($platform2loop as $platform_id) {
          $rf = &$dummy[$item_id][$tcase_id][$platform_id][0];
          $rf['suiteName'] = $pathway[$item_id];

          // ---------------------------------------------------
          // Now we need to get priority LEVEL from 
          // (urgency * impact)
          // we do not use a function to improve performance
          if ($rf['urg_imp'] >= $priorityCfg->threshold['high']) {
            $rf['priority_level'] = HIGH;
          } else if($rf['urg_imp'] < $priorityCfg->threshold['low']) {
            $rf['priority_level'] = LOW;
          } else {
            $rf['priority_level'] = MEDIUM;
          }
        } // $platform2loop    
      } // $tcase2loop
      
      unset($tcase2loop);
      unset($platform2loop);
    } //
      
    unset($pathway);
    return $dummy;
  }

  /**
   * 
   *
   */
  function getNeverRunOnTestPlanWithoutPlatforms($tplanID) 
  {

    $debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
    $safeID = intval($tplanID);

    $fullEID = $this->helperConcatTCasePrefix($safeID);

    // Because we now allow assignment of MULTIPLE testers to same test case
    // we need to remove UA.user_id, in order to avoid duplication
    // UA.user_id,
    // we will need a second step to populate this info.
    //
    $sql =  "/* {$debugMsg} Not Run */
            SELECT COUNT(0) AS qty,
            NHTC.parent_id AS tsuite_id,
            NHTC.id AS tcase_id,
            NHTC.name AS name,
            $fullEID AS full_external_id
            FROM {$this->tables['testplan_tcversions']} TPTCV 
            
            JOIN {$this->tables['builds']} B
            ON  B.testplan_id = TPTCV.testplan_id 

            /* Get Test Case info from Test Case Version */ 
            JOIN {$this->tables['nodes_hierarchy']} NHTCV 
            ON  NHTCV.id = TPTCV.tcversion_id
    
            /* Get Test Suite info from Test Case  */         
            JOIN {$this->tables['nodes_hierarchy']} NHTC
            ON  NHTC.id = NHTCV.parent_id
          
            /* Get Test Case Version attributes */
            JOIN {$this->tables['tcversions']} TCV
            ON  TCV.id = TPTCV.tcversion_id 

            LEFT OUTER JOIN {$this->tables['executions']} E 
            ON  E.testplan_id = TPTCV.testplan_id
            AND E.tcversion_id = TPTCV.tcversion_id
            AND E.build_id = B.id
            
            WHERE TPTCV.testplan_id=$safeID
            AND E.id IS NULL";

    $sql .= " GROUP BY tsuite_id, tcase_id, NHTC.name,
              full_external_id ";
    
    $buildSet = $this->get_builds($safeID,testplan::ACTIVE_BUILDS,
                                  testplan::OPEN_BUILDS);

    $sql .= " HAVING COUNT(0) = " . count($buildSet);
    
    // echo $sql;
    return $this->db->get_recordset($sql);      
  }


 /**
   * 
   *
   */
  function getNeverRunOnSinglePlatform($tplanID,$platformID,$opt=null) 
  {

    $debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
    $safeID = intval($tplanID);

    $fullEID = $this->helperConcatTCasePrefix($safeID);
    $notRunCode = $this->resultsCfg['status_code']['not_run'];

    // Because we now allow assignment of MULTIPLE testers to same test case
    // we need to remove UA.user_id, in order to avoid duplication
    // UA.user_id,
    // we will need a second step to populate this info.
    //
    $sql =  "/* {$debugMsg} Not Run */" . 
          "SELECT COUNT(0) AS qty,
          NHTC.parent_id AS tsuite_id,
          NHTC.id AS tcase_id, TPTCV.platform_id,
          NHTC.name AS name, PLAT.name AS platform_name,
          $fullEID AS full_external_id,
          TCV.tc_external_id AS external_id,
          TCV.version,TCV.execution_type AS exec_type,
          (TPTCV.urgency * TCV.importance) AS urg_imp,
          '{$notRunCode}' AS status
          FROM {$this->tables['testplan_tcversions']} TPTCV 
          
          JOIN {$this->tables['builds']} B
          ON  B.testplan_id = TPTCV.testplan_id 

          /* Get Test Case info from Test Case Version */ 
          JOIN {$this->tables['nodes_hierarchy']} NHTCV 
          ON  NHTCV.id = TPTCV.tcversion_id
  
          /* Get Test Suite info from Test Case  */         
          JOIN {$this->tables['nodes_hierarchy']} NHTC
          ON  NHTC.id = NHTCV.parent_id
        

          /* Get Test Case Version attributes */
          JOIN {$this->tables['tcversions']} TCV
          ON  TCV.id = TPTCV.tcversion_id 

          JOIN {$this->tables['platforms']} PLAT
          ON  PLAT.id = TPTCV.platform_id 


          LEFT OUTER JOIN {$this->tables['executions']} E 
          ON  E.testplan_id = TPTCV.testplan_id
          AND E.platform_id = TPTCV.platform_id
          AND E.tcversion_id = TPTCV.tcversion_id
          AND E.build_id = B.id
          
          WHERE TPTCV.testplan_id=$safeID
          AND E.id IS NULL";

    $sql .= " AND TPTCV.platform_id=" . intval($platformID);
    $sql .= " GROUP BY tsuite_id, tcase_id, 
              TPTCV.platform_id,NHTC.name,platform_name,
              full_external_id,external_id,
              TCV.version,exec_type,urg_imp,status";
    
    //echo $sql;
    $buildSet = $this->get_builds($safeID,testplan::ACTIVE_BUILDS,
                                  testplan::OPEN_BUILDS);

    $sql .= " HAVING COUNT(0) = " . count($buildSet);
    // $sql .= " ORDER BY platform_name,full_external_id ";
    
    // echo $sql;
    $keyColumns = array('tsuite_id','tcase_id','platform_id');
    $cumulative = true;
    $dummy = (array)$this->db->fetchRowsIntoMap3l($sql,$keyColumns,$cumulative);              

    return $dummy;      
  }


  /** 
   *    
   * when getting info for executed test cases, RIGHT version number 
   * for execution is on EXECUTIONS TABLE not on testplan_tcversions TABLE.
   *
   * REMEMBER that when we update TCVERSION for executed Test Cases, 
   * we HAVE TO UPDATE testplan_tcversions table.
   *
   * We also need to use E.tcversion_id and NOT TPTCV.tcversion_id.
   *
   */    
  function buildSQLTSLatestExecOneSinglePlatform($id, $platform_id, $filters=null, $opt=null)
  {
    $debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;

    $my['opt'] = array('getExecutionNotes' => false, 
                       'getTester' => false, 
                       'getUserAssignment' => false,
                       'getExecutionTimestamp' => false, 
                       'getExecutionDuration' => false);
    $my['opt'] = array_merge($my['opt'], (array)$opt);


    list($my,$builds,$sqlStm) = $this->helperGetExecCounters($id, $filters, $opt);

    $safe_id = intval($id);  


    // Additional Execution fields
    $moreExecFields = "";
    if ($my['opt']['getExecutionNotes']) {
      $moreExecFields .= "E.notes AS execution_notes,";
    }  
    
    if ($my['opt']['getTester']) {
      $moreExecFields .= "E.tester_id,";
    } 
    
    if ($my['opt']['getExecutionTimestamp']) {
      $moreExecFields .= "E.execution_ts,";
    } 

    if ($my['opt']['getExecutionDuration']) {
      $moreExecFields .= "E.execution_duration,";
    } 

    if ($my['opt']['getUserAssignment']) {
      $moreExecFields .= "UA.user_id,";
    } 
  
    // Latest Executions By Platform (LEBP)
    $sqlLEBP = $sqlStm['LEBP'];

    $union['exec']  =  
       "/* {$debugMsg} sqlUnion Test suites - executions */  
       SELECT NHTC.parent_id AS tsuite_id,NHTC.id AS tcase_id, 
              NHTC.name AS name,
              TPTCV.tcversion_id,TPTCV.platform_id,
              E.build_id,E.tcversion_number AS version,
              TCV.tc_external_id AS external_id,
              E.id AS executions_id, E.status AS status,  
              {$moreExecFields}
              E.execution_type AS exec_type,
              (TPTCV.urgency * TCV.importance) AS urg_imp
              FROM {$this->tables['testplan_tcversions']} TPTCV " .
  
              $sqlStm['getAssignedFeatures'] .
  
              " /* GO FOR Absolute LATEST exec ID On PLATFORM */ 
                JOIN ({$sqlLEBP}) AS LEBP 
                ON  LEBP.testplan_id = TPTCV.testplan_id
                AND LEBP.platform_id = TPTCV.platform_id
                AND LEBP.tcversion_id = TPTCV.tcversion_id
                AND LEBP.testplan_id = " . $safe_id .

              " /* Get execution status WRITTEN on DB */
                JOIN {$this->tables['executions']} E
                ON  E.id = LEBP.id " .
 
              " /* getUserAssignment */ " .
              $sqlStm['getUserAssignment']['exec'] .

              " /* Get Test Case info from Test Case Version */ " .
              " JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
              " ON  NHTCV.id = TPTCV.tcversion_id " .
  
              " /* Get Test Suite info from Test Case  */ " .
              " JOIN {$this->tables['nodes_hierarchy']} NHTC " .
              " ON  NHTC.id = NHTCV.parent_id " .
              
              " /* Get Test Case Version attributes */ " .
              " JOIN {$this->tables['tcversions']} TCV " .
              " ON  TCV.id = E.tcversion_id " . 

              " WHERE TPTCV.testplan_id={$safe_id} " . 
              " AND TPTCV.platform_id=" . intval($platform_id);

    return array($my,$builds,$sqlStm,$union);
  }


 /**
  *
  * getStatusTotalsTSuiteDepth2ForRender
  *
  */
  function getStatusTotalsTSuiteDepth2ForRender($id,$filters=null,$opt=null)
  {
    list($rx,$staircase) = 
      $this->getStatusTotalsByItemForRender($id,'tsuite',$filters,$opt);

    // ??? $key2loop = array_keys($rx->info);
    $template = array('type' => 'tsuite', 
                      'name' => '',
                      'parent_id' => 0,
                      'total_tc' => 0,
                      'percentage_completed' => 0, 
                      'details' => array());  

    foreach($this->statusCode as $verbose => $code) {
      $template['details'][$verbose] = array('qty' => 0, 'percentage' => 0);
    }
    
    $renderObj = new stdClass();
    $renderObj->colDefinition = $rx->colDefinition;
    $renderObj->info = array();
    $renderObj->infoL2 = array();
    $renderObj->idNameMap = array();
    
    // collect qty
    $execQty = null;
    $execQtyL2 = null;

    $key2loop = array_keys($staircase);
    $wp = isset($opt['groupByPlatform']) && $opt['groupByPlatform'];

    if( $wp ) {
      $tsNameCache = array();
      $plat2loop = array_keys($rx->info);

      // In order to get SUM() for each Top (Level 1) Test Suite
      // using the specific test suite we get the Level 1
      // test suite of it's branch
      //
      foreach($key2loop as $tsuite_id) {
        /* staircase
          array(2) {
            // ELEMENT #1
            [33934]=>  (Test Suite -> Elenco agenda)
            array(1) {
              [0]=> string(5) "33933" (Test Project - Academy)
            }
            
            // ELEMENT #2
            [49238]=> (Test Suite -> SOTTO Elenco agenda)
             array(2) {
               [0]=> string(5) "33933" (Test Project - Academy)
               [1]=> string(5) "33934" (Test Suite -> Elenco agenda)
            }
           }
        */

        $tsuiteDepth = count($staircase[$tsuite_id]);

        $l2id = -1;
        if ($tsuiteDepth > 1) {
          // element at position 1 on starcaise 
          // is a TOP LEVEL SUITE
          $topSuiteID = $staircase[$tsuite_id][1];
          $initName = false;
          if ($tsuiteDepth > 2) {
            $l2id = $staircase[$tsuite_id][2];
            $initNameL2 = false;            
          } else {
            $l2id = $tsuite_id;
            $initNameL2 = true;
          }
        } else {
          $topSuiteID = $tsuite_id;
          $initName = true;
        }     
    
        // Over Platform
        foreach( $plat2loop as $platId ) {
          
          // Level 1
          if (!isset($renderObj->info[$platId][$topSuiteID])) {
            $renderObj->info[$platId][$topSuiteID] = $template;
            $execQty[$platId][$topSuiteID] = 0;
            $initName = true;
          }              

          // Level 2
          if ($l2id > 0 && !isset($renderObj->infoL2[$platId][$l2id])) {
            $renderObj->infoL2[$platId][$l2id] = $template;
            $renderObj->infoL2[$platId][$l2id]['parent_id'] = $topSuiteID;
            $execQtyL2[$platId][$l2id] = 0;
            $initNameL2 = true;
          }              


          if ($initName) {
            if (!isset($tsNameCache[$topSuiteID])) {
              $dummy = $this->tree_manager->get_node_hierarchy_info($topSuiteID);
              $tsNameCache[$topSuiteID] = $dummy['name'];
              unset($dummy);
            }
            $renderObj->info[$platId][$topSuiteID]['name'] = 
              $tsNameCache[$topSuiteID];
          }  

          if ($l2id > 0 && $initNameL2) {
            if (!isset($tsNameCache[$l2id])) {
              // , array('l2CutFirst' => 10)
              // $dummy = $this->tree_manager->getNameL2($l2id);
              $tsNameCache[$l2id] = 
                $this->tree_manager->getNameL2($l2id);
            }
            $renderObj->infoL2[$platId][$l2id]['name'] = 
              $tsNameCache[$l2id];
            $renderObj->idNameMap[$l2id] = $tsNameCache[$l2id];
          }  



          // Loop to get executions counters
          $r2d2 = &$rx->info[$platId][$tsuite_id];
          if( null !== $r2d2 ) {
            foreach($r2d2['details'] as $code => &$elem) {
              $renderObj->info[$platId][$topSuiteID]['details'][$code]
                ['qty'] += $elem['qty'];    
              $renderObj->info[$platId][$topSuiteID]['total_tc'] += 
                $elem['qty'];

              if ($l2id > 0) {
                $renderObj->infoL2[$platId][$l2id]['details'][$code]
                  ['qty'] += $elem['qty'];    
                $renderObj->infoL2[$platId][$l2id]['total_tc'] += 
                  $elem['qty'];
              }

              if( $code != 'not_run' ) {
                $execQty[$platId][$topSuiteID] += $elem['qty'];
                if ($l2id > 0) {
                 $execQtyL2[$platId][$l2id] += $elem['qty'];
                } 
              }
            }
          }  
        }  
      }

      // Last step: get percentages
      foreach($renderObj->info as $platID => &$tsuiteMetrics) {
        foreach($tsuiteMetrics as $tsuite_id => &$elem) {
          if( $execQty[$platID][$tsuite_id] > 0 ) {
            $elem['percentage_completed'] = number_format( 100 * 
              ($execQty[$platID][$tsuite_id] / $elem['total_tc']),1);
          }  
          if( $elem['total_tc'] > 0 ) {
            foreach($elem['details'] as $code => &$yumyum) {
              $yumyum['percentage'] = number_format( 100 * ($yumyum['qty'] / $elem['total_tc']),1);    
            }
          }
        }
      } 

      // Level 2
      foreach($renderObj->infoL2 as $platID => &$tsuiteMetrics) {
        foreach($tsuiteMetrics as $tsuite_id => &$elem) {
          if( $execQtyL2[$platID][$tsuite_id] > 0 ) {
            $elem['percentage_completed'] = number_format( 100 * 
              ($execQtyL2[$platID][$tsuite_id] / $elem['total_tc']),1);
          }  
          if( $elem['total_tc'] > 0 ) {
            foreach($elem['details'] as $code => &$yumyum) {
              $yumyum['percentage'] = number_format( 100 * ($yumyum['qty'] / $elem['total_tc']),1);    
            }
          }
        }
      } 



    } 
    return $renderObj;
  }


 /**
  *
  * getExecTimelineStats
  *
  */
  function getExecTimelineStats($id,$filters=null,$opt=null)
  {
    $flt = array('yyyy_mm_dd' => null);
    $flt = array_merge($flt,(array)$filters);
    
    // timeline
    // day -> sum by date
    // day_hour -> sum by date & hour
    // month -> sum by month
    //
    $options = array('timeline' => 'day', 'workforce' => false);
    $options = array_merge($options,(array)$opt);

    switch ($options['timeline']) {
      case 'day_hour':
        $fields = 'yyyy_mm_dd,hh';
      break;

      case 'month':
        $fields = 'yyyy_mm';
      break;

      case 'day':
      default:
        $fields = 'yyyy_mm_dd';
      break;
    }

    $safeID = intval($id);
    $sqlX = " SELECT COUNT(0) as qty,{$fields},tester_id
              FROM {$this->views['exec_by_date_time']} EBDT
              WHERE EBDT.testplan_id = {$safeID}
              GROUP BY {$fields},tester_id";
    $sqlA = str_ireplace(',tester_id','', $sqlX);
    $sql = $sqlA . " ORDER BY {$fields}";

    switch ($options['timeline']) {
      case 'day_hour':
        $rs = $this->db->fetchMapRowsIntoMap($sql,'yyyy_mm_dd','hh');
      break;

      case 'month':
        $rs = $this->db->fetchRowsIntoMap($sql,'yyyy_mm');
      break;

      case 'day':
      default:
        $rs = $this->db->fetchRowsIntoMap($sql,'yyyy_mm_dd');
      break;
    }

    $rswf = null;
    if ($options['workforce']) {
      $sqlwf = " SELECT COUNT(0) AS testers, {$fields} 
                 FROM ($sqlX) SQLBASE 
                 GROUP BY {$fields}";

      switch ($options['timeline']) {
        case 'day_hour':
          $rswf = $this->db->fetchMapRowsIntoMap($sqlwf,'yyyy_mm_dd','hh');
        break;

        case 'month':
          $rswf = $this->db->fetchRowsIntoMap($sqlwf,'yyyy_mm');
        break;

        case 'day':
        default:
          $rswf = $this->db->fetchRowsIntoMap($sqlwf,'yyyy_mm_dd');
        break;
      }

      foreach ($rswf as $rt => $elem) {
        $rs[$rt]['testers'] = $elem['testers'];
      }
    }

    return array($rs,$rswf);
  }

  /**
   *  get execution time span for context:
   *  Test Plan
   *  Platform (if any)
   *  Build
   *  
   *
   */
  function getExecTimeSpan($id,$context) {

    $fieldList .= implode(',', $context);
 
    $sql = "SELECT MIN(execution_ts) AS begin,
            MAX(execution_ts) AS end, {$fieldList}
            FROM {$this->tables['executions']}
            WHERE testplan_id = $id 
            GROUP BY {$fieldList}";
   
    $levels = count($context);
    switch ($levels) {
      case 1:
        $rs = $this->db->fetchRowsIntoMap($sql,'testplan_id');
      break;

      case 2:
        $rs = $this->db->fetchRowsIntoMap2l($sql,$context);
      break;

      case 3:
        $rs = $this->db->fetchRowsIntoMap3l($sql,$context);
      break;
    }

    return $rs;        
  }


}