Example #1
0
/**
 * Generate a filtered map with all fitting requirements in it.
 * 
 * @author Andreas Simon
 * @param Database $db reference to database handler object
 * @param int $testproject_id ID of the project for which the tree shall be generated
 * @param testproject $testproject_mgr reference to testproject manager object
 * @param array $filters Filter settings which shall be applied to the tree
 * @param array $options Further options which shall be applied on generating the tree
 * @return array $filtered_map map with all fitting requirements
 *
 * @internal revisions
 * @since 1.9.4
 * 20120827 - franciscom - TICKET 5178: Requirement Specification->"Req. Spec. Type" Filter-> KO
 *
 */
function get_filtered_req_map(&$db, $testproject_id, &$testproject_mgr, $filters, $options)
{
    $filtered_map = null;
    $tables = tlObjectWithDB::getDBTables(array('nodes_hierarchy', 'requirements', 'req_specs', 'req_relations', 'req_versions', 'req_coverage', 'tcversions', 'cfield_design_values', 'req_specs_revisions'));
    $sql = " SELECT R.id, R.req_doc_id, NH_R.name AS title, R.srs_id, " . "        RS.doc_id AS req_spec_doc_id, NH_RS.name AS req_spec_title, " . "        RV.version, RV.id AS version_id, NH_R.node_order, " . "        RV.expected_coverage, RV.status, RV.type, RV.active, RV.is_open " . " FROM {$tables['requirements']} R " . " JOIN {$tables['nodes_hierarchy']} NH_R ON NH_R.id = R.id " . " JOIN {$tables['nodes_hierarchy']} NH_RV ON NH_RV.parent_id = NH_R.id " . " JOIN {$tables['req_versions']} RV ON RV.id = NH_RV.id " . " JOIN {$tables['req_specs']} RS ON RS.id = R.srs_id " . " JOIN {$tables['req_specs_revisions']} RSPECREV ON RSPECREV.parent_id = RS.id " . " JOIN {$tables['nodes_hierarchy']} NH_RS ON NH_RS.id = RS.id ";
    if (isset($filters['filter_relation'])) {
        $sql .= " JOIN {$tables['req_relations']} RR " . " ON (RR.destination_id = R.id OR RR.source_id = R.id) ";
    }
    if (isset($filters['filter_tc_id'])) {
        $tc_cfg = config_get('testcase_cfg');
        $tc_prefix = $testproject_mgr->getTestCasePrefix($testproject_id);
        $tc_prefix .= $tc_cfg->glue_character;
        $tc_ext_id = $db->prepare_int(str_replace($tc_prefix, '', $filters['filter_tc_id']));
        $sql .= " JOIN {$tables['req_coverage']} RC ON RC.req_id = R.id " . " JOIN {$tables['nodes_hierarchy']} NH_T ON NH_T.id = RC.testcase_id " . " JOIN {$tables['nodes_hierarchy']} NH_TV on NH_TV.parent_id = NH_T.id " . " JOIN {$tables['tcversions']} TV ON TV.id = NH_TV.id " . "                                    AND TV.tc_external_id = {$tc_ext_id} ";
    }
    if (isset($filters['filter_custom_fields'])) {
        $suffix = 1;
        foreach ($filters['filter_custom_fields'] as $cf_id => $cf_value) {
            $sql .= " JOIN {$tables['cfield_design_values']} CF{$suffix} " . " ON CF{$suffix}.node_id = RV.id " . " AND CF{$suffix}.field_id = {$cf_id} ";
            // single value or array?
            if (is_array($cf_value)) {
                $sql .= " AND ( ";
                $count = 1;
                foreach ($cf_value as $value) {
                    if ($count > 1) {
                        $sql .= " OR ";
                    }
                    $sql .= " CF{$suffix}.value LIKE '%{$value}%' ";
                    $count++;
                }
                $sql .= " ) ";
            } else {
                $sql .= " AND CF{$suffix}.value LIKE '%{$cf_value}%' ";
            }
            $suffix++;
        }
    }
    $sql .= " WHERE RS.testproject_id = {$testproject_id} ";
    if (isset($filters['filter_doc_id'])) {
        $doc_id = $db->prepare_string($filters['filter_doc_id']);
        $sql .= " AND R.req_doc_id LIKE '%{$doc_id}%' OR RS.doc_id LIKE '%{$doc_id}%' ";
    }
    if (isset($filters['filter_title'])) {
        $title = $db->prepare_string($filters['filter_title']);
        $sql .= " AND NH_R.name LIKE '%{$title}%' ";
    }
    if (isset($filters['filter_coverage'])) {
        $coverage = $db->prepare_int($filters['filter_coverage']);
        $sql .= " AND expected_coverage = {$coverage} ";
    }
    if (isset($filters['filter_status'])) {
        $statuses = (array) $filters['filter_status'];
        foreach ($statuses as $key => $status) {
            $statuses[$key] = "'" . $db->prepare_string($status) . "'";
        }
        $statuses = implode(",", $statuses);
        $sql .= " AND RV.status IN ({$statuses}) ";
    }
    if (isset($filters['filter_type'])) {
        $types = (array) $filters['filter_type'];
        foreach ($types as $key => $type) {
            $types[$key] = $db->prepare_string($type);
        }
        $types = implode("','", $types);
        $sql .= " AND RV.type IN ('{$types}') ";
    }
    if (isset($filters['filter_spec_type'])) {
        $spec_types = (array) $filters['filter_spec_type'];
        foreach ($spec_types as $key => $type) {
            $spec_types[$key] = $db->prepare_string($type);
        }
        $spec_types = implode("','", $spec_types);
        $sql .= " AND RSPECREV.type IN ('{$spec_types}') ";
    }
    if (isset($filters['filter_relation'])) {
        $sql .= " AND ( ";
        $count = 1;
        foreach ($filters['filter_relation'] as $key => $rel_filter) {
            $relation_info = explode('_', $rel_filter);
            $relation_type = $db->prepare_int($relation_info[0]);
            $relation_side = isset($relation_info[1]) ? $relation_info[1] : null;
            $sql .= $count == 1 ? " ( " : " OR ( ";
            if ($relation_side == "destination") {
                $sql .= " RR.destination_id = R.id ";
            } else {
                if ($relation_side == "source") {
                    $sql .= " RR.source_id = R.id ";
                } else {
                    $sql .= " (RR.destination_id = R.id OR RR.source_id = R.id) ";
                }
            }
            $sql .= " AND RR.relation_type = {$relation_type} ) ";
            $count++;
        }
        $sql .= " ) ";
    }
    $sql .= " ORDER BY RV.version DESC ";
    $filtered_map = $db->fetchRowsIntoMap($sql, 'id');
    return $filtered_map;
}